From 1b1acad676efc2f1fa616d1421b544219e65e953 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 8 Dec 2009 02:27:35 +0000 Subject: [PATCH] - multi-part schema names, i.e. with dots such as "dbo.master", are now rendered in select() labels with underscores for dots, i.e. "dbo_master_table_column". This is a "friendly" label that behaves better in result sets. [ticket:1428] --- CHANGES | 30 +++++++------ lib/sqlalchemy/sql/expression.py | 2 +- test/sql/test_select.py | 72 ++++++++++++++++++++++++-------- 3 files changed, 73 insertions(+), 31 deletions(-) diff --git a/CHANGES b/CHANGES index 1e7d759ff8..0cfc4f4631 100644 --- a/CHANGES +++ b/CHANGES @@ -224,15 +224,6 @@ CHANGES keys showing up in the VALUES or SET clause of the generated SQL. [ticket:1579] - - Databases which rely upon postfetch of "last inserted id" - to get at a generated sequence value (i.e. MySQL, MS-SQL) - now work correctly when there is a composite primary key - where the "autoincrement" column is not the first primary - key column in the table. - - - the last_inserted_ids() method has been renamed to the - descriptor "inserted_primary_key". - - the Binary type now returns data as a Python string (or a "bytes" type in Python 3), instead of the built- in "buffer" type. This allows symmetric round trips @@ -244,7 +235,13 @@ CHANGES of bind parameters currently being processed. This dict is available in the same way regardless of single-execute or executemany-style statement execution. - + + - multi-part schema names, i.e. with dots such as + "dbo.master", are now rendered in select() labels + with underscores for dots, i.e. "dbo_master_table_column". + This is a "friendly" label that behaves better + in result sets. [ticket:1428] + - Deprecated or removed: * "scalar" flag on select() is removed, use select.as_scalar(). @@ -276,6 +273,15 @@ CHANGES improvement when fetching large result sets with no unicode conversion as tuples. Many thanks to Elixir's Gaëtan de Menten for this dramatic improvement ! [ticket:1586] + + - Databases which rely upon postfetch of "last inserted id" + to get at a generated sequence value (i.e. MySQL, MS-SQL) + now work correctly when there is a composite primary key + where the "autoincrement" column is not the first primary + key column in the table. + + - the last_inserted_ids() method has been renamed to the + descriptor "inserted_primary_key". - setting echo=False on create_engine() now sets the loglevel to WARN instead of NOTSET. This so that logging can be @@ -346,9 +352,7 @@ CHANGES DDL() event system. A side effect of this refactor is that ForeignKeyConstraint objects with use_alter=True will *not* be emitted on SQLite, which does not support - ALTER for foreign keys. This has no effect on SQLite's - behavior since SQLite does not actually honor FOREIGN KEY - constraints. + ALTER for foreign keys. - ForeignKey and ForeignKeyConstraint objects now correctly copy() all their public keyword arguments. [ticket:1605] diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index eb36558ce8..1b9f89ae13 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -3015,7 +3015,7 @@ class ColumnClause(_Immutable, ColumnElement): elif self.table is not None and self.table.named_with_column: if getattr(self.table, 'schema', None): - label = self.table.schema + "_" + \ + label = self.table.schema.replace('.', '_') + "_" + \ _escape_for_generated(self.table.name) + "_" + \ _escape_for_generated(self.name) else: diff --git a/test/sql/test_select.py b/test/sql/test_select.py index 1db2559bc6..42998ee02f 100644 --- a/test/sql/test_select.py +++ b/test/sql/test_select.py @@ -27,6 +27,8 @@ table3 = table( ) metadata = MetaData() + +# table with a schema table4 = Table( 'remotetable', metadata, Column('rem_id', Integer, primary_key=True), @@ -35,6 +37,15 @@ table4 = Table( schema = 'remote_owner' ) +# table with a 'multipart' schema +table5 = Table( + 'remotetable', metadata, + Column('rem_id', Integer, primary_key=True), + Column('datatype_id', Integer), + Column('value', String(20)), + schema = 'dbo.remote_owner' +) + users = table('users', column('user_id'), column('user_name'), @@ -1631,28 +1642,55 @@ class InlineDefaultTest(TestBase, AssertsCompiledSQL): class SchemaTest(TestBase, AssertsCompiledSQL): def test_select(self): - # these tests will fail with the MS-SQL compiler since it will alias schema-qualified tables - self.assert_compile(table4.select(), "SELECT remote_owner.remotetable.rem_id, remote_owner.remotetable.datatype_id, remote_owner.remotetable.value FROM remote_owner.remotetable") + self.assert_compile(table4.select(), + "SELECT remote_owner.remotetable.rem_id, remote_owner.remotetable.datatype_id," + " remote_owner.remotetable.value FROM remote_owner.remotetable") + self.assert_compile(table4.select(and_(table4.c.datatype_id==7, table4.c.value=='hi')), - "SELECT remote_owner.remotetable.rem_id, remote_owner.remotetable.datatype_id, remote_owner.remotetable.value FROM remote_owner.remotetable WHERE "\ - "remote_owner.remotetable.datatype_id = :datatype_id_1 AND remote_owner.remotetable.value = :value_1") - - s = table4.select(and_(table4.c.datatype_id==7, table4.c.value=='hi')) - s.use_labels = True - self.assert_compile(s, "SELECT remote_owner.remotetable.rem_id AS remote_owner_remotetable_rem_id, remote_owner.remotetable.datatype_id AS remote_owner_remotetable_datatype_id, remote_owner.remotetable.value "\ - "AS remote_owner_remotetable_value FROM remote_owner.remotetable WHERE "\ - "remote_owner.remotetable.datatype_id = :datatype_id_1 AND remote_owner.remotetable.value = :value_1") - + "SELECT remote_owner.remotetable.rem_id, remote_owner.remotetable.datatype_id," + " remote_owner.remotetable.value FROM remote_owner.remotetable WHERE " + "remote_owner.remotetable.datatype_id = :datatype_id_1 AND" + " remote_owner.remotetable.value = :value_1") + + s = table4.select(and_(table4.c.datatype_id==7, table4.c.value=='hi'), use_labels=True) + self.assert_compile(s, "SELECT remote_owner.remotetable.rem_id AS" + " remote_owner_remotetable_rem_id, remote_owner.remotetable.datatype_id AS" + " remote_owner_remotetable_datatype_id, remote_owner.remotetable.value " + "AS remote_owner_remotetable_value FROM remote_owner.remotetable WHERE " + "remote_owner.remotetable.datatype_id = :datatype_id_1 AND " + "remote_owner.remotetable.value = :value_1") + + # multi-part schema name + self.assert_compile(table5.select(), + 'SELECT "dbo.remote_owner".remotetable.rem_id, ' + '"dbo.remote_owner".remotetable.datatype_id, "dbo.remote_owner".remotetable.value ' + 'FROM "dbo.remote_owner".remotetable' + ) + + # multi-part schema name labels - convert '.' to '_' + self.assert_compile(table5.select(use_labels=True), + 'SELECT "dbo.remote_owner".remotetable.rem_id AS' + ' dbo_remote_owner_remotetable_rem_id, "dbo.remote_owner".remotetable.datatype_id' + ' AS dbo_remote_owner_remotetable_datatype_id,' + ' "dbo.remote_owner".remotetable.value AS dbo_remote_owner_remotetable_value FROM' + ' "dbo.remote_owner".remotetable' + ) + def test_alias(self): a = alias(table4, 'remtable') - self.assert_compile(a.select(a.c.datatype_id==7), "SELECT remtable.rem_id, remtable.datatype_id, remtable.value FROM remote_owner.remotetable AS remtable "\ - "WHERE remtable.datatype_id = :datatype_id_1") + self.assert_compile(a.select(a.c.datatype_id==7), + "SELECT remtable.rem_id, remtable.datatype_id, remtable.value FROM" + " remote_owner.remotetable AS remtable " + "WHERE remtable.datatype_id = :datatype_id_1") def test_update(self): - self.assert_compile(table4.update(table4.c.value=='test', values={table4.c.datatype_id:12}), "UPDATE remote_owner.remotetable SET datatype_id=:datatype_id "\ - "WHERE remote_owner.remotetable.value = :value_1") + self.assert_compile( + table4.update(table4.c.value=='test', values={table4.c.datatype_id:12}), + "UPDATE remote_owner.remotetable SET datatype_id=:datatype_id " + "WHERE remote_owner.remotetable.value = :value_1") def test_insert(self): - self.assert_compile(table4.insert(values=(2, 5, 'test')), "INSERT INTO remote_owner.remotetable (rem_id, datatype_id, value) VALUES "\ - "(:rem_id, :datatype_id, :value)") + self.assert_compile(table4.insert(values=(2, 5, 'test')), + "INSERT INTO remote_owner.remotetable (rem_id, datatype_id, value) VALUES " + "(:rem_id, :datatype_id, :value)") -- 2.47.3