diff options
| -rw-r--r-- | CHANGES | 7 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 35 | ||||
| -rw-r--r-- | test/dialect/test_postgresql.py | 15 |
3 files changed, 46 insertions, 11 deletions
@@ -888,6 +888,13 @@ are also present in 0.8. old SQLite versions that don't deliver default info as a string. [ticket:2265] +- postgresql + - [bug] Columns in reflected primary key constraint + are now returned in the order in which the constraint + itself defines them, rather than how the table + orders them. Courtesy Gunnlaugur Þór Briem. + [ticket:2531]. + - mysql - [bug] Updated mysqlconnector interface to use updated "client flag" and "charset" APIs, diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 4e30a76c4..d23920c5e 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1603,20 +1603,33 @@ class PGDialect(default.DefaultDialect): table_oid = self.get_table_oid(connection, table_name, schema, info_cache=kw.get('info_cache')) - PK_SQL = """ - SELECT a.attname + if self.server_version_info < (8, 4): + # unnest() and generate_subscripts() both introduced in + # version 8.4 + PK_SQL = """ + SELECT a.attname FROM pg_class t join pg_index ix on t.oid = ix.indrelid join pg_attribute a on t.oid=a.attrelid and a.attnum=ANY(ix.indkey) - WHERE - t.oid = :table_oid and - ix.indisprimary = 't' - ORDER BY - a.attnum - """ - t = sql.text(PK_SQL, typemap={'attname':sqltypes.Unicode}) + WHERE + t.oid = :table_oid and ix.indisprimary = 't' + ORDER BY a.attnum + """ + else: + PK_SQL = """ + SELECT a.attname + FROM pg_attribute a JOIN ( + SELECT unnest(ix.indkey) attnum, + generate_subscripts(ix.indkey, 1) ord + FROM pg_index ix + WHERE ix.indrelid = :table_oid AND ix.indisprimary + ) k ON a.attnum=k.attnum + WHERE a.attrelid = :table_oid + ORDER BY k.ord + """ + t = sql.text(PK_SQL, typemap={'attname': sqltypes.Unicode}) c = connection.execute(t, table_oid=table_oid) cols = [r[0] for r in c.fetchall()] @@ -1626,11 +1639,11 @@ class PGDialect(default.DefaultDialect): WHERE r.conrelid = :table_oid AND r.contype = 'p' ORDER BY 1 """ - t = sql.text(PK_CONS_SQL, typemap={'conname':sqltypes.Unicode}) + t = sql.text(PK_CONS_SQL, typemap={'conname': sqltypes.Unicode}) c = connection.execute(t, table_oid=table_oid) name = c.scalar() - return {'constrained_columns':cols, 'name':name} + return {'constrained_columns': cols, 'name': name} @reflection.cache def get_foreign_keys(self, connection, table_name, schema=None, **kw): diff --git a/test/dialect/test_postgresql.py b/test/dialect/test_postgresql.py index 0bd6666e2..73633c128 100644 --- a/test/dialect/test_postgresql.py +++ b/test/dialect/test_postgresql.py @@ -1439,6 +1439,21 @@ class DistinctOnTest(fixtures.TestBase, AssertsCompiledSQL): class ReflectionTest(fixtures.TestBase): __only_on__ = 'postgresql' + @testing.fails_if(('postgresql', '<', (8, 4)), + "newer query is bypassed due to unsupported SQL functions") + @testing.provide_metadata + def test_reflected_primary_key_order(self): + meta1 = self.metadata + subject = Table('subject', meta1, + Column('p1', Integer, primary_key=True), + Column('p2', Integer, primary_key=True), + PrimaryKeyConstraint('p2', 'p1') + ) + meta1.create_all() + meta2 = MetaData(testing.db) + subject = Table('subject', meta2, autoload=True) + eq_(subject.primary_key.columns.keys(), [u'p2', u'p1']) + @testing.provide_metadata def test_pg_weirdchar_reflection(self): meta1 = self.metadata |