- use provide_metadata for new unique constraint / index tests

- add a test for PG reflection of unique index without any unique
constraint
- for PG, don't include 'duplicates_constraint' in the entry
if the index does not actually mirror a constraint
- use a distinct method for unique constraint reflection within table
- catch unique constraint not implemented condition; this may
be within some dialects and also is expected to be supported by
Alembic tests
- migration + changelogs for #3184
- add individual doc notes as well to MySQL, Postgreql
fixes #3184

7 files changed, 237 insertions, 55 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index a746abeac..69b5b29c1 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -22,6 +22,38 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: feature, sql
+        :tickets: 3184
+        :pullreq: bitbucket:30
+
+        The :class:`.UniqueConstraint` construct is now included when
+        reflecting a :class:`.Table` object, for databases where this
+        is applicable.  In order to achieve this
+        with sufficient accuracy, MySQL and Postgresql now contain features
+        that correct for the duplication of indexes and unique constraints
+        when reflecting tables, indexes, and constraints.
+        In the case of MySQL, there is not actually a "unique constraint"
+        concept independent of a "unique index", so for this backend
+        :class:`.UniqueConstraint` continues to remain non-present for a
+        reflected :class:`.Table`.  For Postgresql, the query used to
+        detect indexes against ``pg_index`` has been improved to check for
+        the same construct in ``pg_constraint``, and the implicitly
+        constructed unique index is not included with a
+        reflected :class:`.Table`.
+
+        In both cases, the  :meth:`.Inspector.get_indexes` and the
+        :meth:`.Inspector.get_unique_constraints` methods return both
+        constructs individually, but include a new token
+        ``duplicates_constraint`` in the case of Postgresql or
+        ``duplicates_index`` in the case
+        of MySQL to indicate when this condition is detected.
+        Pull request courtesy Johannes Erdfelt.
+
+        .. seealso::
+
+            :ref:`feature_3184`
+
+    .. change::
         :tags: feature, postgresql
         :pullreq: github:134
 
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index b0ac868ec..439ec4c67 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -50,6 +50,62 @@ wishes to support the new feature should now call upon the ``._limit_clause``
 and ``._offset_clause`` attributes to receive the full SQL expression, rather
 than the integer value.
 
+.. _feature_3184:
+
+UniqueConstraint is now part of the Table reflection process
+------------------------------------------------------------
+
+A :class:`.Table` object populated using ``autoload=True`` will now
+include :class:`.UniqueConstraint` constructs as well as
+:class:`.Index` constructs.  This logic has a few caveats for
+Postgresql and Mysql:
+
+Postgresql
+^^^^^^^^^^
+
+Postgresql has the behavior such that when a UNIQUE constraint is
+created, it implicitly creates a UNIQUE INDEX corresponding to that
+constraint as well. The :meth:`.Inspector.get_indexes` and the
+:meth:`.Inspector.get_unique_constraints` methods will continue to
+**both** return these entries distinctly, where
+:meth:`.Inspector.get_indexes` now features a token
+``duplicates_constraint`` within the index entry  indicating the
+corresponding constraint when detected.   However, when performing
+full table reflection using  ``Table(..., autoload=True)``, the
+:class:`.Index` construct is detected as being linked to the
+:class:`.UniqueConstraint`, and is **not** present within the
+:attr:`.Table.indexes` collection; only the :class:`.UniqueConstraint`
+will be present in the :attr:`.Table.constraints` collection.   This
+deduplication logic works by joining to the ``pg_constraint`` table
+when querying ``pg_index`` to see if the two constructs are linked.
+
+MySQL
+^^^^^
+
+MySQL does not have separate concepts for a UNIQUE INDEX and a UNIQUE
+constraint.  While it supports both syntaxes when creating tables and indexes,
+it does not store them any differently. The
+:meth:`.Inspector.get_indexes`
+and the :meth:`.Inspector.get_unique_constraints` methods will continue to
+**both** return an entry for a UNIQUE index in MySQL,
+where :meth:`.Inspector.get_unique_constraints` features a new token
+``duplicates_index`` within the constraint entry indicating that this is a
+dupe entry corresponding to that index.  However, when performing
+full table reflection using ``Table(..., autoload=True)``,
+the :class:`.UniqueConstraint` construct is
+**not** part of the fully reflected :class:`.Table` construct under any
+circumstances; this construct is always represented by a :class:`.Index`
+with the ``unique=True`` setting present in the :attr:`.Table.indexes`
+collection.
+
+.. seealso::
+
+    :ref:`postgresql_index_reflection`
+
+    :ref:`mysql_unique_constraints`
+
+:ticket:`3184`
+
 
 Behavioral Improvements
 =======================
@@ -1043,6 +1099,7 @@ by Postgresql as of 9.4.  SQLAlchemy allows this using
 
     :class:`.FunctionFilter`
 
+
 MySQL internal "no such table" exceptions not passed to event handlers
 ----------------------------------------------------------------------
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 2f85a3626..793e6566d 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -341,6 +341,29 @@ reflection will not include foreign keys.  For these tables, you may supply a
 
     :ref:`mysql_storage_engines`
 
+.. _mysql_unique_constraints:
+
+MySQL Unique Constraints and Reflection
+---------------------------------------
+
+SQLAlchemy supports both the :class:`.Index` construct with the
+flag ``unique=True``, indicating a UNIQUE index, as well as the
+:class:`.UniqueConstraint` construct, representing a UNIQUE constraint.
+Both objects/syntaxes are supported by MySQL when emitting DDL to create
+these constraints.  However, MySQL does not have a unique constraint
+construct that is separate from a unique index; that is, the "UNIQUE"
+constraint on MySQL is equivalent to creating a "UNIQUE INDEX".
+
+When reflecting these constructs, the :meth:`.Inspector.get_indexes`
+and the :meth:`.Inspector.get_unique_constraints` methods will **both**
+return an entry for a UNIQUE index in MySQL.  However, when performing
+full table reflection using ``Table(..., autoload=True)``,
+the :class:`.UniqueConstraint` construct is
+**not** part of the fully reflected :class:`.Table` construct under any
+circumstances; this construct is always represented by a :class:`.Index`
+with the ``unique=True`` setting present in the :attr:`.Table.indexes`
+collection.
+
 
 .. _mysql_timestamp_null:
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 556493b3c..baa640eaa 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -402,6 +402,28 @@ underlying CREATE INDEX command, so it *must* be a valid index type for your
 version of PostgreSQL.
 
 
+.. _postgresql_index_reflection:
+
+Postgresql Index Reflection
+---------------------------
+
+The Postgresql database creates a UNIQUE INDEX implicitly whenever the
+UNIQUE CONSTRAINT construct is used.   When inspecting a table using
+:class:`.Inspector`, the :meth:`.Inspector.get_indexes`
+and the :meth:`.Inspector.get_unique_constraints` will report on these
+two constructs distinctly; in the case of the index, the key
+``duplicates_constraint`` will be present in the index entry if it is
+detected as mirroring a constraint.   When performing reflection using
+``Table(..., autoload=True)``, the UNIQUE INDEX is **not** returned
+in :attr:`.Table.indexes` when it is detected as mirroring a
+:class:`.UniqueConstraint` in the :attr:`.Table.constraints` collection.
+
+.. versionchanged:: 1.0.0 - :class:`.Table` reflection now includes
+   :class:`.UniqueConstraint` objects present in the :attr:`.Table.constraints`
+   collection; the Postgresql backend will no longer include a "mirrored"
+   :class:`.Index` construct in :attr:`.Table.indexes` if it is detected
+   as corresponding to a unique constraint.
+
 Special Reflection Options
 --------------------------
 
@@ -2523,21 +2545,27 @@ class PGDialect(default.DefaultDialect):
                     % idx_name)
                 sv_idx_name = idx_name
 
+            has_idx = idx_name in indexes
             index = indexes[idx_name]
             if col is not None:
                 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']],
-             'duplicates_constraint': idx['duplicates_constraint']}
-            for name, idx in indexes.items()
-        ]
+            if not has_idx:
+                index['key'] = [int(k.strip()) for k in idx_key.split()]
+                index['unique'] = unique
+                if conrelid is not None:
+                    index['duplicates_constraint'] = idx_name
+
+        result = []
+        for name, idx in indexes.items():
+            entry = {
+                'name': name,
+                'unique': idx['unique'],
+                'column_names': [idx['cols'][i] for i in idx['key']]
+            }
+            if 'duplicates_constraint' in idx:
+                entry['duplicates_constraint'] = idx['duplicates_constraint']
+            result.append(entry)
+        return result
 
     @reflection.cache
     def get_unique_constraints(self, connection, table_name,
diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py
index 15c2dd195..2a1def86a 100644
--- a/lib/sqlalchemy/engine/reflection.py
+++ b/lib/sqlalchemy/engine/reflection.py
@@ -508,6 +508,10 @@ class Inspector(object):
             table_name, schema, table, cols_by_orig_name,
             include_columns, exclude_columns, reflection_options)
 
+        self._reflect_unique_constraints(
+            table_name, schema, table, cols_by_orig_name,
+            include_columns, exclude_columns, reflection_options)
+
     def _reflect_column(
         self, table, col_d, include_columns,
             exclude_columns, cols_by_orig_name):
@@ -665,8 +669,17 @@ class Inspector(object):
 
             sa_schema.Index(name, *idx_cols, **dict(unique=unique))
 
+    def _reflect_unique_constraints(
+        self, table_name, schema, table, cols_by_orig_name,
+            include_columns, exclude_columns, reflection_options):
+
         # Unique Constraints
-        constraints = self.get_unique_constraints(table_name, schema)
+        try:
+            constraints = self.get_unique_constraints(table_name, schema)
+        except NotImplementedError:
+            # optional dialect feature
+            return
+
         for const_d in constraints:
             conname = const_d['name']
             columns = const_d['column_names']
diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py
index b8f72b942..99733e397 100644
--- a/test/dialect/mysql/test_reflection.py
+++ b/test/dialect/mysql/test_reflection.py
@@ -283,36 +283,37 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
         view_names = dialect.get_view_names(connection, "information_schema")
         self.assert_('TABLES' in view_names)
 
+    @testing.provide_metadata
     def test_reflection_with_unique_constraint(self):
         insp = inspect(testing.db)
 
-        uc_table = Table('mysql_uc', MetaData(testing.db),
+        meta = self.metadata
+        uc_table = Table('mysql_uc', meta,
                          Column('a', String(10)),
                          UniqueConstraint('a', name='uc_a'))
 
-        try:
-            uc_table.create()
+        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'))
+        # MySQL converts unique constraints into unique indexes.
+        # separately we get both
+        indexes = dict((i['name'], i) 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)
+        self.assert_('uc_a' in indexes)
+        self.assert_(indexes['uc_a']['unique'])
+        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)
+        # reflection here favors the unique index, as that's the
+        # more "official" MySQL construct
+        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)
+        indexes = dict((i.name, i) 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()
+        self.assert_('uc_a' in indexes)
+        self.assert_(indexes['uc_a'].unique)
+        self.assert_('uc_a' not in constraints)
 
 
 class RawReflectionTest(fixtures.TestBase):
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index fc013c72a..8de71216e 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -7,7 +7,8 @@ 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, UniqueConstraint
+    PrimaryKeyConstraint, ForeignKey, join, Sequence, UniqueConstraint, \
+    Index
 from sqlalchemy import exc
 import sqlalchemy as sa
 from sqlalchemy.dialects.postgresql import base as postgresql
@@ -656,8 +657,7 @@ 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, 'duplicates_constraint': None,
-                   'column_names': ['y'], 'name': 'idx1'}])
+        eq_(ind, [{'unique': False, 'column_names': ['y'], 'name': 'idx1'}])
         conn.close()
 
     @testing.provide_metadata
@@ -804,37 +804,65 @@ class ReflectionTest(fixtures.TestBase):
                 'labels': ['sad', 'ok', 'happy']
             }])
 
+    @testing.provide_metadata
     def test_reflection_with_unique_constraint(self):
         insp = inspect(testing.db)
 
-        uc_table = Table('pgsql_uc', MetaData(testing.db),
+        meta = self.metadata
+        uc_table = Table('pgsql_uc', meta,
                          Column('a', String(10)),
                          UniqueConstraint('a', name='uc_a'))
 
-        try:
-            uc_table.create()
+        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'))
+        # PostgreSQL will create an implicit index for a unique
+        # constraint.   Separately we get both
+        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)
+        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)
+        # reflection corrects for the dupe
+        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)
+        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()
+        self.assert_('uc_a' not in indexes)
+        self.assert_('uc_a' in constraints)
+
+    @testing.provide_metadata
+    def test_reflect_unique_index(self):
+        insp = inspect(testing.db)
+
+        meta = self.metadata
+
+        # a unique index OTOH we are able to detect is an index
+        # and not a unique constraint
+        uc_table = Table('pgsql_uc', meta,
+                         Column('a', String(10)),
+                         Index('ix_a', 'a', unique=True))
+
+        uc_table.create()
+
+        indexes = dict((i['name'], i) for i in insp.get_indexes('pgsql_uc'))
+        constraints = set(i['name']
+                          for i in insp.get_unique_constraints('pgsql_uc'))
+
+        self.assert_('ix_a' in indexes)
+        assert indexes['ix_a']['unique']
+        self.assert_('ix_a' not in constraints)
+
+        reflected = Table('pgsql_uc', MetaData(testing.db), autoload=True)
+
+        indexes = dict((i.name, i) for i in reflected.indexes)
+        constraints = set(uc.name for uc in reflected.constraints)
+
+        self.assert_('ix_a' in indexes)
+        assert indexes['ix_a'].unique
+        self.assert_('ix_a' not in constraints)
 
 
 class CustomTypeReflectionTest(fixtures.TestBase):