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

1 files changed, 109 insertions, 2 deletions

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'