From cadfc608d63f4e0df46c0daaa28902423fd88d71 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 23 Mar 2020 14:52:05 -0400 Subject: Convert schema_translate to a post compile Revised the :paramref:`.Connection.execution_options.schema_translate_map` feature such that the processing of the SQL statement to receive a specific schema name occurs within the execution phase of the statement, rather than at the compile phase. This is to support the statement being efficiently cached. Previously, the current schema being rendered into the statement for a particular run would be considered as part of the cache key itself, meaning that for a run against hundreds of schemas, there would be hundreds of cache keys, rendering the cache much less performant. The new behavior is that the rendering is done in a similar manner as the "post compile" rendering added in 1.4 as part of :ticket:`4645`, :ticket:`4808`. Fixes: #5004 Change-Id: Ia5c89eb27cc8dc2c5b8e76d6c07c46290a7901b6 --- test/dialect/postgresql/test_compiler.py | 3 ++ test/engine/test_execute.py | 61 +++++++++++++++----------------- test/sql/test_compiler.py | 49 ++++++++++++++++++++++--- test/sql/test_ddlemit.py | 3 +- 4 files changed, 79 insertions(+), 37 deletions(-) (limited to 'test') diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index aabbc3ac3..316f0c240 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -229,12 +229,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): postgresql.CreateEnumType(e1), "CREATE TYPE foo.somename AS ENUM ('x', 'y', 'z')", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) self.assert_compile( postgresql.CreateEnumType(e2), "CREATE TYPE bar.somename AS ENUM ('x', 'y', 'z')", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) def test_create_table_with_schema_type_schema_translate(self): @@ -251,6 +253,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): CreateTable(table), "CREATE TABLE foo.some_table (q foo.somename, p bar.somename)", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) def test_create_table_with_tablespace(self): diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py index 0b5b1b16d..566cf0654 100644 --- a/test/engine/test_execute.py +++ b/test/engine/test_execute.py @@ -949,6 +949,13 @@ class CompiledCacheTest(fixtures.TestBase): conn.execute(ins, {"q": 2}) eq_(conn.scalar(stmt), 2) + with config.db.connect().execution_options( + compiled_cache=cache, schema_translate_map={None: None}, + ) as conn: + # should use default schema again even though statement + # was compiled with test_schema in the map + eq_(conn.scalar(stmt), 1) + with config.db.connect().execution_options( compiled_cache=cache ) as conn: @@ -1014,12 +1021,12 @@ class SchemaTranslateTest(fixtures.TestBase, testing.AssertsExecutionResults): t1.drop(conn) asserter.assert_( - CompiledSQL("CREATE TABLE %s.t1 (x INTEGER)" % config.test_schema), - CompiledSQL("CREATE TABLE %s.t2 (x INTEGER)" % config.test_schema), - CompiledSQL("CREATE TABLE t3 (x INTEGER)"), - CompiledSQL("DROP TABLE t3"), - CompiledSQL("DROP TABLE %s.t2" % config.test_schema), - CompiledSQL("DROP TABLE %s.t1" % config.test_schema), + CompiledSQL("CREATE TABLE [SCHEMA__none].t1 (x INTEGER)"), + CompiledSQL("CREATE TABLE [SCHEMA_foo].t2 (x INTEGER)"), + CompiledSQL("CREATE TABLE [SCHEMA_bar].t3 (x INTEGER)"), + CompiledSQL("DROP TABLE [SCHEMA_bar].t3"), + CompiledSQL("DROP TABLE [SCHEMA_foo].t2"), + CompiledSQL("DROP TABLE [SCHEMA__none].t1"), ) def _fixture(self): @@ -1099,34 +1106,27 @@ class SchemaTranslateTest(fixtures.TestBase, testing.AssertsExecutionResults): conn.execute(t3.delete()) asserter.assert_( + CompiledSQL("INSERT INTO [SCHEMA__none].t1 (x) VALUES (:x)"), + CompiledSQL("INSERT INTO [SCHEMA_foo].t2 (x) VALUES (:x)"), + CompiledSQL("INSERT INTO [SCHEMA_bar].t3 (x) VALUES (:x)"), CompiledSQL( - "INSERT INTO %s.t1 (x) VALUES (:x)" % config.test_schema - ), - CompiledSQL( - "INSERT INTO %s.t2 (x) VALUES (:x)" % config.test_schema + "UPDATE [SCHEMA__none].t1 SET x=:x WHERE " + "[SCHEMA__none].t1.x = :x_1" ), - CompiledSQL("INSERT INTO t3 (x) VALUES (:x)"), CompiledSQL( - "UPDATE %s.t1 SET x=:x WHERE %s.t1.x = :x_1" - % (config.test_schema, config.test_schema) + "UPDATE [SCHEMA_foo].t2 SET x=:x WHERE " + "[SCHEMA_foo].t2.x = :x_1" ), CompiledSQL( - "UPDATE %s.t2 SET x=:x WHERE %s.t2.x = :x_1" - % (config.test_schema, config.test_schema) + "UPDATE [SCHEMA_bar].t3 SET x=:x WHERE " + "[SCHEMA_bar].t3.x = :x_1" ), - CompiledSQL("UPDATE t3 SET x=:x WHERE t3.x = :x_1"), - CompiledSQL( - "SELECT %s.t1.x FROM %s.t1" - % (config.test_schema, config.test_schema) - ), - CompiledSQL( - "SELECT %s.t2.x FROM %s.t2" - % (config.test_schema, config.test_schema) - ), - CompiledSQL("SELECT t3.x FROM t3"), - CompiledSQL("DELETE FROM %s.t1" % config.test_schema), - CompiledSQL("DELETE FROM %s.t2" % config.test_schema), - CompiledSQL("DELETE FROM t3"), + CompiledSQL("SELECT [SCHEMA__none].t1.x FROM [SCHEMA__none].t1"), + CompiledSQL("SELECT [SCHEMA_foo].t2.x FROM [SCHEMA_foo].t2"), + CompiledSQL("SELECT [SCHEMA_bar].t3.x FROM [SCHEMA_bar].t3"), + CompiledSQL("DELETE FROM [SCHEMA__none].t1"), + CompiledSQL("DELETE FROM [SCHEMA_foo].t2"), + CompiledSQL("DELETE FROM [SCHEMA_bar].t3"), ) @testing.provide_metadata @@ -1147,10 +1147,7 @@ class SchemaTranslateTest(fixtures.TestBase, testing.AssertsExecutionResults): conn = eng.connect() conn.execute(select([t2.c.x])) asserter.assert_( - CompiledSQL( - "SELECT %s.t2.x FROM %s.t2" - % (config.test_schema, config.test_schema) - ) + CompiledSQL("SELECT [SCHEMA_foo].t2.x FROM [SCHEMA_foo].t2") ) diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 033da10a3..ef3e5d26e 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -3958,23 +3958,42 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL): schema_translate_map = {None: "z", "bar": None, "foo": "bat"} + self.assert_compile( + schema.CreateTable(t1), + "CREATE TABLE [SCHEMA__none].t1 (q INTEGER)", + schema_translate_map=schema_translate_map, + ) self.assert_compile( schema.CreateTable(t1), "CREATE TABLE z.t1 (q INTEGER)", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) + self.assert_compile( + schema.CreateTable(t2), + "CREATE TABLE [SCHEMA_foo].t2 (q INTEGER)", + schema_translate_map=schema_translate_map, + ) self.assert_compile( schema.CreateTable(t2), "CREATE TABLE bat.t2 (q INTEGER)", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) self.assert_compile( schema.CreateTable(t3), - "CREATE TABLE t3 (q INTEGER)", + "CREATE TABLE [SCHEMA_bar].t3 (q INTEGER)", schema_translate_map=schema_translate_map, ) + self.assert_compile( + schema.CreateTable(t3), + "CREATE TABLE main.t3 (q INTEGER)", + schema_translate_map=schema_translate_map, + render_schema_translate=True, + default_schema_name="main", + ) def test_schema_translate_map_sequence(self): s1 = schema.Sequence("s1") @@ -3985,19 +4004,19 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( schema.CreateSequence(s1), - "CREATE SEQUENCE z.s1", + "CREATE SEQUENCE [SCHEMA__none].s1", schema_translate_map=schema_translate_map, ) self.assert_compile( schema.CreateSequence(s2), - "CREATE SEQUENCE bat.s2", + "CREATE SEQUENCE [SCHEMA_foo].s2", schema_translate_map=schema_translate_map, ) self.assert_compile( schema.CreateSequence(s3), - "CREATE SEQUENCE s3", + "CREATE SEQUENCE [SCHEMA_bar].s3", schema_translate_map=schema_translate_map, ) @@ -4135,6 +4154,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "bar.mytable.description FROM bar.mytable " "WHERE bar.mytable.name = :name_1", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) self.assert_compile( @@ -4143,6 +4163,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "foob.remotetable.value FROM foob.remotetable " "WHERE foob.remotetable.value = :value_1", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) schema_translate_map = {"remote_owner": "foob"} @@ -4155,6 +4176,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "foob.remotetable.value FROM mytable JOIN foob.remotetable " "ON mytable.myid = foob.remotetable.rem_id", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) def test_schema_translate_aliases(self): @@ -4183,6 +4205,18 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): .where(alias.c.name == "foo") ) + self.assert_compile( + stmt, + "SELECT [SCHEMA__none].myothertable.otherid, " + "[SCHEMA__none].myothertable.othername, " + "mytable_1.myid, mytable_1.name, mytable_1.description " + "FROM [SCHEMA__none].myothertable JOIN " + "[SCHEMA__none].mytable AS mytable_1 " + "ON [SCHEMA__none].myothertable.otherid = mytable_1.myid " + "WHERE mytable_1.name = :name_1", + schema_translate_map=schema_translate_map, + ) + self.assert_compile( stmt, "SELECT bar.myothertable.otherid, bar.myothertable.othername, " @@ -4191,6 +4225,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "ON bar.myothertable.otherid = mytable_1.myid " "WHERE mytable_1.name = :name_1", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) def test_schema_translate_crud(self): @@ -4209,6 +4244,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): table1.insert().values(description="foo"), "INSERT INTO bar.mytable (description) VALUES (:description)", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) self.assert_compile( @@ -4218,17 +4254,20 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "UPDATE bar.mytable SET description=:description " "WHERE bar.mytable.name = :name_1", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) self.assert_compile( table1.delete().where(table1.c.name == "hi"), "DELETE FROM bar.mytable WHERE bar.mytable.name = :name_1", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) self.assert_compile( table4.insert().values(value="there"), "INSERT INTO foob.remotetable (value) VALUES (:value)", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) self.assert_compile( @@ -4238,6 +4277,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "UPDATE foob.remotetable SET value=:value " "WHERE foob.remotetable.value = :value_1", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) self.assert_compile( @@ -4245,6 +4285,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "DELETE FROM foob.remotetable WHERE " "foob.remotetable.value = :value_1", schema_translate_map=schema_translate_map, + render_schema_translate=True, ) def test_alias(self): diff --git a/test/sql/test_ddlemit.py b/test/sql/test_ddlemit.py index 13300f0b5..667891236 100644 --- a/test/sql/test_ddlemit.py +++ b/test/sql/test_ddlemit.py @@ -28,7 +28,8 @@ class EmitDDLTest(fixtures.TestBase): has_index=Mock(side_effect=has_index), supports_comments=True, inline_comments=False, - ) + ), + _schema_translate_map=None, ) def _mock_create_fixture( -- cgit v1.2.1