Add support for identity columns

Added the :class:`_schema.Identity` construct that can be used to
configure identity columns rendered with GENERATED { ALWAYS |
BY DEFAULT } AS IDENTITY. Currently the supported backends are
PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax
and a subset of functionalities).

Fixes: #5362
Fixes: #5324
Fixes: #5360

Change-Id: Iecea6f3ceb36821e8b96f0b61049b580507a1875

1 files changed, 58 insertions, 3 deletions

diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 7cb9aae57..2e5ce2581 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -18,10 +18,47 @@ Auto Increment Behavior
 
 SQLAlchemy Table objects which include integer primary keys are usually
 assumed to have "autoincrementing" behavior, meaning they can generate their
-own primary key values upon INSERT.  Since Oracle has no "autoincrement"
+own primary key values upon INSERT. For use within Oracle, two options are
+available, which are the use of IDENTITY columns (Oracle 12 and above only)
+or the association of a SEQUENCE with the column.
+
+Specifying GENERATED AS IDENTITY (Oracle 12 and above)
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+Starting from version 12 Oracle can make use of identity columns using
+the :class:`_sql.Identity` to specify the autoincrementing behavior::
+
+    t = Table('mytable', metadata,
+        Column('id', Integer, Identity(start=3), primary_key=True),
+        Column(...), ...
+    )
+
+The CREATE TABLE for the above :class:`_schema.Table` object would be:
+
+.. sourcecode:: sql
+
+    CREATE TABLE mytable (
+        id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 3) NOT NULL,
+        ...,
+        PRIMARY KEY (id)
+    )
+
+The :class:`_schema.Identity` object support many options to control the
+"autoincrementing" behavior of the column, like the starting value, the
+incrementing value, etc.
+In addition to the standard options, Oracle supports setting
+:paramref:`_schema.Identity.always` to ``None`` to use the default
+generated mode, rendering GENERATED AS IDENTITY in the DDL. It also supports
+setting :paramref:`_schema.Identity.on_null` to ``True`` to specify ON NULL
+in conjunction with a 'BY DEFAULT' identity column.
+
+Using a SEQUENCE (all Oracle versions)
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+Older version of Oracle had no "autoincrement"
 feature, SQLAlchemy relies upon sequences to produce these values.   With the
-Oracle dialect, *a sequence must always be explicitly specified to enable
-autoincrement*.  This is divergent with the majority of documentation
+older Oracle versions, *a sequence must always be explicitly specified to
+enable autoincrement*.  This is divergent with the majority of documentation
 examples which assume the usage of an autoincrement-capable database.   To
 specify sequences, use the sqlalchemy.schema.Sequence object which is passed
 to a Column construct::
@@ -38,6 +75,10 @@ This step is also required when using table reflection, i.e. autoload=True::
         autoload=True
   )
 
+.. versionchanged::  1.4   Added :class:`_schema.Identity` construct
+   in a :class:`_schema.Column` to specify the option of an autoincrementing
+   column.
+
 Transaction Isolation Level / Autocommit
 ----------------------------------------
 
@@ -1252,6 +1293,20 @@ class OracleDDLCompiler(compiler.DDLCompiler):
             text += " VIRTUAL"
         return text
 
+    def visit_identity_column(self, identity, **kw):
+        if identity.always is None:
+            kind = ""
+        else:
+            kind = "ALWAYS" if identity.always else "BY DEFAULT"
+        text = "GENERATED %s" % kind
+        if identity.on_null:
+            text += " ON NULL"
+        text += " AS IDENTITY"
+        options = self.get_identity_options(identity)
+        if options:
+            text += " (%s)" % options
+        return text
+
 
 class OracleIdentifierPreparer(compiler.IdentifierPreparer):