Implement get_unique_constraints, get_check_constraints for Oracle

Pull-request: https://github.com/zzzeek/sqlalchemy/pull/326
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/342

Fixes: #4002
Change-Id: I221fe8ba305fc455a03e3a5d15f803bf8ee2e8fb

6 files changed, 185 insertions, 6 deletions

diff --git a/doc/build/changelog/changelog_12.rst b/doc/build/changelog/changelog_12.rst
index 314fe675c..c34b9a0e5 100644
--- a/doc/build/changelog/changelog_12.rst
+++ b/doc/build/changelog/changelog_12.rst
@@ -13,6 +13,22 @@
 .. changelog::
     :version: 1.2.0b1
 
+    .. change:: 4003
+        :tags: feature, oracle
+
+        The Oracle dialect now inspects unique and check constraints when using
+        :meth:`.Inspector.get_unique_constraints`,
+        :meth:`.Inspector.get_check_constraints`.
+        As Oracle does not have unique constraints that are separate from a unique
+        :class:`.Index`, a :class:`.Table` that's reflected will still continue
+        to not have :class:`.UniqueConstraint` objects associated with it.
+        Pull requests courtesy Eloy Felix.
+
+        .. seealso::
+
+            :ref:`change_4003`
+
+
     .. change:: 3996
         :tags: bug, orm
         :tickets: 3996
diff --git a/doc/build/changelog/migration_12.rst b/doc/build/changelog/migration_12.rst
index c25bdaced..0f06dd199 100644
--- a/doc/build/changelog/migration_12.rst
+++ b/doc/build/changelog/migration_12.rst
@@ -926,6 +926,27 @@ Dialect Improvements and Changes - SQLite
 Dialect Improvements and Changes - Oracle
 =========================================
 
+.. _change_4003:
+
+Oracle Unique, Check constraints now reflected
+----------------------------------------------
+
+UNIQUE and CHECK constraints now reflect via
+:meth:`.Inspector.get_unique_constraints` and
+:meth:`.Inspector.get_check_constraints`.  A :class:`.Table` object  that's
+reflected will now include :class:`.CheckConstraint` objects as well.
+See the notes at :ref:`oracle_constraint_reflection` for information
+on behavioral quirks here, including that most :class:`.Table` objects
+will still not include any :class:`.UniqueConstraint` objects as these
+usually represent via :class:`.Index`.
+
+.. seealso::
+
+    :ref:`oracle_constraint_reflection`
+
+
+:ticket:`4003`
+
 .. _change_3276:
 
 Oracle foreign key constraint names are now "name normalized"
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 06565a1c0..d9fa80df1 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -195,6 +195,51 @@ accepted, including methods such as :meth:`.MetaData.reflect` and
 
 If synonyms are not in use, this flag should be left disabled.
 
+.. _oracle_constraint_reflection:
+
+Constraint Reflection
+---------------------
+
+The Oracle dialect can return information about foreign key, unique, and
+CHECK constraints, as well as indexes on tables.
+
+Raw information regarding these constraints can be acquired using
+:meth:`.Inspector.get_foreign_keys`, :meth:`.Inspector.get_unique_constraints`,
+:meth:`.Inspector.get_check_constraints`, and :meth:`.Inspector.get_indexes`.
+
+.. versionchanged:: 1.2  The Oracle dialect can now reflect UNIQUE and
+   CHECK constraints.
+
+When using reflection at the :class:`.Table` level, the :class:`.Table`
+will also include these constraints.
+
+Note the following caveats:
+
+* When using the :meth:`.Inspector.get_check_constraints` method, Oracle
+  builds a special "IS NOT NULL" constraint for columns that specify
+  "NOT NULL".  This constraint is **not** returned by default; to include
+  the "IS NOT NULL" constraints, pass the flag ``include_all=True``::
+
+      from sqlalchemy import create_engine, inspect
+
+      engine = create_engine("oracle+cx_oracle://s:t@dsn")
+      inspector = inspect(engine)
+      all_check_constraints = inspector.get_check_constraints(
+          "some_table", include_all=True)
+
+* in most cases, when reflecting a :class:`.Table`, a UNIQUE constraint will
+  **not** be available as a :class:`.UniqueConstraint` object, as Oracle
+  mirrors unique constraints with a UNIQUE index in most cases (the exception
+  seems to be when two or more unique constraints represent the same columns);
+  the :class:`.Table` will instead represent these using :class:`.Index`
+  with the ``unique=True`` flag set.
+
+* Oracle creates an implicit index for the primary key of a table; this index
+  is **excluded** from all index results.
+
+* the list of columns reflected for an index will not include column names
+  that start with SYS_NC.
+
 Table names with SYSTEM/SYSAUX tablespaces
 -------------------------------------------
 
@@ -310,6 +355,7 @@ from sqlalchemy.sql.elements import quoted_name
 from sqlalchemy import types as sqltypes, schema as sa_schema
 from sqlalchemy.types import VARCHAR, NVARCHAR, CHAR, \
     BLOB, CLOB, TIMESTAMP, FLOAT
+from itertools import groupby
 
 RESERVED_WORDS = \
     set('SHARE RAW DROP BETWEEN FROM DESC OPTION PRIOR LONG THEN '
@@ -1472,12 +1518,13 @@ class OracleDialect(default.DefaultDialect):
             "\nrem.column_name AS remote_column,"\
             "\nrem.owner AS remote_owner,"\
             "\nloc.position as loc_pos,"\
-            "\nrem.position as rem_pos"\
+            "\nrem.position as rem_pos,"\
+            "\nac.search_condition"\
             "\nFROM all_constraints%(dblink)s ac,"\
             "\nall_cons_columns%(dblink)s loc,"\
             "\nall_cons_columns%(dblink)s rem"\
             "\nWHERE ac.table_name = :table_name"\
-            "\nAND ac.constraint_type IN ('R','P')"
+            "\nAND ac.constraint_type IN ('R','P', 'U', 'C')"
 
         if schema is not None:
             params['owner'] = schema
@@ -1605,6 +1652,40 @@ class OracleDialect(default.DefaultDialect):
         return list(fkeys.values())
 
     @reflection.cache
+    def get_unique_constraints(self, connection, table_name, schema=None, **kw):
+        resolve_synonyms = kw.get('oracle_resolve_synonyms', False)
+        dblink = kw.get('dblink', '')
+        info_cache = kw.get('info_cache')
+
+        (table_name, schema, dblink, synonym) = \
+            self._prepare_reflection_args(connection, table_name, schema,
+                                          resolve_synonyms, dblink,
+                                          info_cache=info_cache)
+
+        constraint_data = self._get_constraint_data(
+            connection, table_name, schema, dblink,
+            info_cache=kw.get('info_cache'))
+
+        unique_keys = filter(lambda x: x[1] == 'U', constraint_data)
+        uniques_group = groupby(unique_keys, lambda x: x[0])
+
+        index_names = set([ix['name'] for ix in self.get_indexes(connection, table_name, schema=schema)])
+        return [
+            {
+                'name': name,
+                'column_names': cols,
+                'duplicates_index': name if name in index_names else None
+            }
+            for name, cols in
+            [
+                [
+                    self.normalize_name(i[0]),
+                    [self.normalize_name(x[2]) for x in i[1]]
+                ] for i in uniques_group
+            ]
+        ]
+
+    @reflection.cache
     def get_view_definition(self, connection, view_name, schema=None,
                             resolve_synonyms=False, dblink='', **kw):
         info_cache = kw.get('info_cache')
@@ -1628,6 +1709,32 @@ class OracleDialect(default.DefaultDialect):
         else:
             return None
 
+    @reflection.cache
+    def get_check_constraints(self, connection, table_name, schema=None,
+                              include_all=False, **kw):
+        resolve_synonyms = kw.get('oracle_resolve_synonyms', False)
+        dblink = kw.get('dblink', '')
+        info_cache = kw.get('info_cache')
+
+        (table_name, schema, dblink, synonym) = \
+            self._prepare_reflection_args(connection, table_name, schema,
+                                          resolve_synonyms, dblink,
+                                          info_cache=info_cache)
+
+        constraint_data = self._get_constraint_data(
+            connection, table_name, schema, dblink,
+            info_cache=kw.get('info_cache'))
+
+        check_constraints = filter(lambda x: x[1] == 'C', constraint_data)
+
+        return [
+            {
+                'name': self.normalize_name(cons[0]),
+                'sqltext': cons[8],
+            }
+            for cons in check_constraints if include_all or
+            not re.match(r'..+?. IS NOT NULL$', cons[8])]
+
 
 class _OuterJoinColumn(sql.ClauseElement):
     __visit_name__ = 'outer_join_column'
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index f47b34bf4..1c4fd7d4d 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -677,12 +677,36 @@ class ComponentReflectionTest(fixtures.TablesTest):
             key=operator.itemgetter('name')
         )
 
+        names_that_duplicate_index = set()
+
         for orig, refl in zip(uniques, reflected):
             # Different dialects handle duplicate index and constraints
             # differently, so ignore this flag
-            refl.pop('duplicates_index', None)
+            dupe = refl.pop('duplicates_index', None)
+            if dupe:
+                names_that_duplicate_index.add(dupe)
             eq_(orig, refl)
 
+        reflected_metadata = MetaData()
+        reflected = Table(
+            'testtbl', reflected_metadata, autoload_with=orig_meta.bind,
+            schema=schema)
+
+        # test "deduplicates for index" logic.   MySQL and Oracle
+        # "unique constraints" are actually unique indexes (with possible
+        # exception of a unique that is a dupe of another one in the case
+        # of Oracle).  make sure # they aren't duplicated.
+        idx_names = set([idx.name for idx in reflected.indexes])
+        uq_names = set([
+            uq.name for uq in reflected.constraints
+            if isinstance(uq, sa.UniqueConstraint)]).difference(
+            ['unique_c_a_b'])
+
+        assert not idx_names.intersection(uq_names)
+        if names_that_duplicate_index:
+            eq_(names_that_duplicate_index, idx_names)
+            eq_(uq_names, set())
+
     @testing.provide_metadata
     def _test_get_view_definition(self, schema=None):
         meta = self.metadata
diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py
index 32b39e883..5ca95aea9 100644
--- a/test/dialect/test_oracle.py
+++ b/test/dialect/test_oracle.py
@@ -1224,6 +1224,14 @@ class ConstraintTest(fixtures.TablesTest):
                                          onupdate='CASCADE'))
         assert_raises(exc.SAWarning, bat.create)
 
+    def test_reflect_check_include_all(self):
+        insp = inspect(testing.db)
+        eq_(insp.get_check_constraints('foo'), [])
+        eq_(
+            [rec['sqltext']
+             for rec in insp.get_check_constraints('foo', include_all=True)],
+            ['"ID" IS NOT NULL'])
+
 
 class TwoPhaseTest(fixtures.TablesTest):
     """test cx_oracle two phase, which remains in a semi-broken state
diff --git a/test/requirements.py b/test/requirements.py
index 0f854d269..7bc9f7dbe 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -340,18 +340,21 @@ class DefaultRequirements(SuiteRequirements):
         return fails_on_everything_except(
                     "postgresql",
                     "mysql",
-                    "sqlite"
+                    "sqlite",
+                    "oracle"
                 )
 
     @property
     def unique_constraint_reflection_no_index_overlap(self):
-        return self.unique_constraint_reflection + skip_if("mysql")
+        return self.unique_constraint_reflection + \
+            skip_if("mysql")  + skip_if("oracle")
 
     @property
     def check_constraint_reflection(self):
         return fails_on_everything_except(
                     "postgresql",
-                    "sqlite"
+                    "sqlite",
+                    "oracle"
                 )
 
     @property