Reflect unique constraints when reflecting a Table object

Calls to reflect a table did not create any UniqueConstraint objects.
The reflection core made no calls to get_unique_constraints and as
a result, the sqlite dialect would never reflect any unique constraints.

MySQL transparently converts unique constraints into unique indexes, but
SQLAlchemy would reflect those as an Index object and as a
UniqueConstraint. The reflection core will now deduplicate the unique
constraints.

PostgreSQL would reflect unique constraints as an Index object and as
a UniqueConstraint object. The reflection core will now deduplicate
the unique indexes.

6 files changed, 124 insertions, 12 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 7ccd59abb..2f85a3626 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2590,7 +2590,8 @@ class MySQLDialect(default.DefaultDialect):
         return [
             {
                 'name': key['name'],
-                'column_names': [col[0] for col in key['columns']]
+                'column_names': [col[0] for col in key['columns']],
+                'duplicates_index': key['name'],
             }
             for key in parsed_state.keys
             if key['type'] == 'UNIQUE'
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index b9a0d461b..556493b3c 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2471,14 +2471,19 @@ class PGDialect(default.DefaultDialect):
           SELECT
               i.relname as relname,
               ix.indisunique, ix.indexprs, ix.indpred,
-              a.attname, a.attnum, ix.indkey%s
+              a.attname, a.attnum, c.conrelid, ix.indkey%s
           FROM
               pg_class t
                     join pg_index ix on t.oid = ix.indrelid
-                    join pg_class i on i.oid=ix.indexrelid
+                    join pg_class i on i.oid = ix.indexrelid
                     left outer join
                         pg_attribute a
-                        on t.oid=a.attrelid and %s
+                        on t.oid = a.attrelid and %s
+                    left outer join
+                        pg_constraint c
+                        on (ix.indrelid = c.conrelid and
+                            ix.indexrelid = c.conindid and
+                            c.contype in ('p', 'u', 'x'))
           WHERE
               t.relkind IN ('r', 'v', 'f', 'm')
               and t.oid = :table_oid
@@ -2501,7 +2506,7 @@ class PGDialect(default.DefaultDialect):
 
         sv_idx_name = None
         for row in c.fetchall():
-            idx_name, unique, expr, prd, col, col_num, idx_key = row
+            idx_name, unique, expr, prd, col, col_num, conrelid, idx_key = row
 
             if expr:
                 if idx_name != sv_idx_name:
@@ -2523,11 +2528,14 @@ class PGDialect(default.DefaultDialect):
                 index['cols'][col_num] = col
             index['key'] = [int(k.strip()) for k in idx_key.split()]
             index['unique'] = unique
+            index['duplicates_constraint'] = (None if conrelid is None
+                                                   else idx_name)
 
         return [
             {'name': name,
              'unique': idx['unique'],
-             'column_names': [idx['cols'][i] for i in idx['key']]}
+             'column_names': [idx['cols'][i] for i in idx['key']],
+             'duplicates_constraint': idx['duplicates_constraint']}
             for name, idx in indexes.items()
         ]
 
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index c0a3240a5..330fc2b19 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -603,12 +603,15 @@ class Inspector(object):
             columns = index_d['column_names']
             unique = index_d['unique']
             flavor = index_d.get('type', 'index')
+            duplicates = index_d.get('duplicates_constraint')
             if include_columns and \
                     not set(columns).issubset(include_columns):
                 util.warn(
                     "Omitting %s key for (%s), key covers omitted columns." %
                     (flavor, ', '.join(columns)))
                 continue
+            if duplicates:
+                continue
             # look for columns by orig name in cols_by_orig_name,
             # but support columns that are in-Python only as fallback
             idx_cols = []
@@ -626,3 +629,34 @@ class Inspector(object):
                     idx_cols.append(idx_col)
 
             sa_schema.Index(name, *idx_cols, **dict(unique=unique))
+
+        # Unique Constraints
+        constraints = self.get_unique_constraints(table_name, schema)
+        for const_d in constraints:
+            conname = const_d['name']
+            columns = const_d['column_names']
+            duplicates = const_d.get('duplicates_index')
+            if include_columns and \
+                    not set(columns).issubset(include_columns):
+                util.warn(
+                    "Omitting unique constraint key for (%s), "
+                    "key covers omitted columns." %
+                    ', '.join(columns))
+                continue
+            if duplicates:
+                continue
+            # look for columns by orig name in cols_by_orig_name,
+            # but support columns that are in-Python only as fallback
+            constrained_cols = []
+            for c in columns:
+                try:
+                    constrained_col = cols_by_orig_name[c] \
+                        if c in cols_by_orig_name else table.c[c]
+                except KeyError:
+                    util.warn(
+                        "unique constraint key '%s' was not located in "
+                        "columns for table '%s'" % (c, table_name))
+                else:
+                    constrained_cols.append(constrained_col)
+            table.append_constraint(
+                sa_schema.UniqueConstraint(*constrained_cols, name=conname))
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index 690a880bb..bd0be5738 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -487,10 +487,12 @@ class ComponentReflectionTest(fixtures.TablesTest):
     @testing.requires.temp_table_reflection
     def test_get_temp_table_unique_constraints(self):
         insp = inspect(self.metadata.bind)
-        eq_(
-            insp.get_unique_constraints('user_tmp'),
-            [{'column_names': ['name'], 'name': 'user_tmp_uq'}]
-        )
+        reflected = insp.get_unique_constraints('user_tmp')
+        for refl in reflected:
+            # Different dialects handle duplicate index and constraints
+            # differently, so ignore this flag
+            refl.pop('duplicates_index', None)
+        eq_(reflected, [{'column_names': ['name'], 'name': 'user_tmp_uq'}])
 
     @testing.requires.temp_table_reflection
     def test_get_temp_table_indexes(self):
@@ -544,6 +546,9 @@ class ComponentReflectionTest(fixtures.TablesTest):
         )
 
         for orig, refl in zip(uniques, reflected):
+            # Different dialects handle duplicate index and constraints
+            # differently, so ignore this flag
+            refl.pop('duplicates_index', None)
             eq_(orig, refl)
 
     @testing.provide_metadata
diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py
index bf35a2c6b..b8f72b942 100644
--- a/test/dialect/mysql/test_reflection.py
+++ b/test/dialect/mysql/test_reflection.py
@@ -283,6 +283,37 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
         view_names = dialect.get_view_names(connection, "information_schema")
         self.assert_('TABLES' in view_names)
 
+    def test_reflection_with_unique_constraint(self):
+        insp = inspect(testing.db)
+
+        uc_table = Table('mysql_uc', MetaData(testing.db),
+                         Column('a', String(10)),
+                         UniqueConstraint('a', name='uc_a'))
+
+        try:
+            uc_table.create()
+
+            # MySQL converts unique constraints into unique indexes and
+            # the 0.9 API returns it as both an index and a constraint
+            indexes = set(i['name'] for i in insp.get_indexes('mysql_uc'))
+            constraints = set(i['name']
+                              for i in insp.get_unique_constraints('mysql_uc'))
+
+            self.assert_('uc_a' in indexes)
+            self.assert_('uc_a' in constraints)
+
+            # However, upon creating a Table object via reflection, it should
+            # only appear as a unique index and not a constraint
+            reflected = Table('mysql_uc', MetaData(testing.db), autoload=True)
+
+            indexes = set(i.name for i in reflected.indexes)
+            constraints = set(uc.name for uc in reflected.constraints)
+
+            self.assert_('uc_a' in indexes)
+            self.assert_('uc_a' not in constraints)
+        finally:
+            uc_table.drop()
+
 
 class RawReflectionTest(fixtures.TestBase):
     def setup(self):
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index b8b9be3de..fc013c72a 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -7,7 +7,7 @@ from sqlalchemy.testing import fixtures
 from sqlalchemy import testing
 from sqlalchemy import inspect
 from sqlalchemy import Table, Column, MetaData, Integer, String, \
-    PrimaryKeyConstraint, ForeignKey, join, Sequence
+    PrimaryKeyConstraint, ForeignKey, join, Sequence, UniqueConstraint
 from sqlalchemy import exc
 import sqlalchemy as sa
 from sqlalchemy.dialects.postgresql import base as postgresql
@@ -656,7 +656,8 @@ class ReflectionTest(fixtures.TestBase):
         conn.execute("ALTER TABLE t RENAME COLUMN x to y")
 
         ind = testing.db.dialect.get_indexes(conn, "t", None)
-        eq_(ind, [{'unique': False, 'column_names': ['y'], 'name': 'idx1'}])
+        eq_(ind, [{'unique': False, 'duplicates_constraint': None,
+                   'column_names': ['y'], 'name': 'idx1'}])
         conn.close()
 
     @testing.provide_metadata
@@ -803,6 +804,38 @@ class ReflectionTest(fixtures.TestBase):
                 'labels': ['sad', 'ok', 'happy']
             }])
 
+    def test_reflection_with_unique_constraint(self):
+        insp = inspect(testing.db)
+
+        uc_table = Table('pgsql_uc', MetaData(testing.db),
+                         Column('a', String(10)),
+                         UniqueConstraint('a', name='uc_a'))
+
+        try:
+            uc_table.create()
+
+            # PostgreSQL will create an implicit index for a unique
+            # constraint. As a result, the 0.9 API returns it as both
+            # an index and a constraint
+            indexes = set(i['name'] for i in insp.get_indexes('pgsql_uc'))
+            constraints = set(i['name']
+                              for i in insp.get_unique_constraints('pgsql_uc'))
+
+            self.assert_('uc_a' in indexes)
+            self.assert_('uc_a' in constraints)
+
+            # However, upon creating a Table object via reflection, it should
+            # only appear as a unique constraint and not an index
+            reflected = Table('pgsql_uc', MetaData(testing.db), autoload=True)
+
+            indexes = set(i.name for i in reflected.indexes)
+            constraints = set(uc.name for uc in reflected.constraints)
+
+            self.assert_('uc_a' not in indexes)
+            self.assert_('uc_a' in constraints)
+        finally:
+            uc_table.drop()
+
 
 class CustomTypeReflectionTest(fixtures.TestBase):