1. Lee alayii
  2. sqlalchemy

Commits

Mike Bayer  committed 2a13915

- [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 Por Briem.
[ticket:2531].

  • Participants
  • Parent commits 0fd3650
  • Branches rel_0_7

Comments (0)

Files changed (3)

File CHANGES

View file
     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,

File lib/sqlalchemy/dialects/postgresql/base.py

View file
         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)
         primary_keys = [r[0] for r in c.fetchall()]
         return primary_keys

File test/dialect/test_postgresql.py

View file
 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