Merge "Add support for identity columns"

3 files changed, 185 insertions, 46 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index ab6e19cf4..d18fb7299 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -38,7 +38,7 @@ The above example will generate DDL as:
 .. sourcecode:: sql
 
     CREATE TABLE t (
-        id INTEGER NOT NULL IDENTITY(1,1),
+        id INTEGER NOT NULL IDENTITY,
         x INTEGER NULL,
         PRIMARY KEY (id)
     )
@@ -65,17 +65,25 @@ is set to ``False`` on any integer primary key column::
             Column('x', Integer, autoincrement=True))
     m.create_all(engine)
 
-.. versionchanged::  1.3   Added ``mssql_identity_start`` and
-   ``mssql_identity_increment`` parameters to :class:`_schema.Column`.
-   These replace
+.. versionchanged::  1.4   Added :class:`_schema.Identity` construct
+   in a :class:`_schema.Column` to specify the start and increment
+   parameters of an IDENTITY. These replace
    the use of the :class:`.Sequence` object in order to specify these values.
 
+.. deprecated:: 1.4
+
+   The ``mssql_identity_start`` and ``mssql_identity_increment`` parameters
+   to :class:`_schema.Column` are deprecated and should we replaced by
+   an :class:`_schema.Identity` object. Specifying both ways of configuring
+   an IDENTITY will result in a compile error.
+
 .. deprecated:: 1.3
 
    The use of :class:`.Sequence` to specify IDENTITY characteristics is
    deprecated and will be removed in a future release.   Please use
-   the ``mssql_identity_start`` and ``mssql_identity_increment`` parameters
-   documented at :ref:`mssql_identity`.
+   the :class:`_schema.Identity` object parameters
+   :paramref:`_schema.Identity.start` and
+   :paramref:`_schema.Identity.increment`.
 
 .. versionchanged::  1.4   Removed the ability to use a :class:`.Sequence`
    object to modify IDENTITY characteristics. :class:`.Sequence` objects
@@ -108,16 +116,18 @@ Controlling "Start" and "Increment"
 
 Specific control over the "start" and "increment" values for
 the ``IDENTITY`` generator are provided using the
-``mssql_identity_start`` and ``mssql_identity_increment`` parameters
-passed to the :class:`_schema.Column` object::
+:paramref:`_schema.Identity.start` and :paramref:`_schema.Identity.increment`
+parameters passed to the :class:`_schema.Identity` object::
 
-    from sqlalchemy import Table, Integer, Column
+    from sqlalchemy import Table, Integer, Column, Identity
 
     test = Table(
         'test', metadata,
         Column(
-            'id', Integer, primary_key=True, mssql_identity_start=100,
-             mssql_identity_increment=10
+            'id',
+            Integer,
+            primary_key=True,
+            Identity(start=100, increment=10)
         ),
         Column('name', String(20))
     )
@@ -131,12 +141,18 @@ The CREATE TABLE for the above :class:`_schema.Table` object would be:
      name VARCHAR(20) NULL,
      )
 
-.. versionchanged:: 1.3  The ``mssql_identity_start`` and
-   ``mssql_identity_increment`` parameters are now used to affect the
+.. note::
+
+   The :class:`_schema.Identity` object supports many other parameter in
+   addition to ``start`` and ``increment``. These are not supported by
+   SQL Server and will be ignored when generating the CREATE TABLE ddl.
+
+.. versionchanged:: 1.3.19  The :class:`_schema.Identity` object is
+   now used to affect the
    ``IDENTITY`` generator for a :class:`_schema.Column` under  SQL Server.
    Previously, the :class:`.Sequence` object was used.  As SQL Server now
    supports real sequences as a separate construct, :class:`.Sequence` will be
-   functional in the normal way in a future SQLAlchemy version.
+   functional in the normal way starting from SQLAlchemy version 1.4.
 
 INSERT behavior
 ^^^^^^^^^^^^^^^^
@@ -720,6 +736,7 @@ from .json import JSON
 from .json import JSONIndexType
 from .json import JSONPathType
 from ... import exc
+from ... import Identity
 from ... import schema as sa_schema
 from ... import Sequence
 from ... import sql
@@ -2146,6 +2163,7 @@ class MSDDLCompiler(compiler.DDLCompiler):
                 or column.primary_key
                 or isinstance(column.default, sa_schema.Sequence)
                 or column.autoincrement is True
+                or column.identity
             ):
                 colspec += " NOT NULL"
             elif column.computed is None:
@@ -2158,16 +2176,33 @@ class MSDDLCompiler(compiler.DDLCompiler):
                 "in order to generate DDL"
             )
 
-        if (
+        d_opt = column.dialect_options["mssql"]
+        start = d_opt["identity_start"]
+        increment = d_opt["identity_increment"]
+        if start is not None or increment is not None:
+            if column.identity:
+                raise exc.CompileError(
+                    "Cannot specify options 'mssql_identity_start' and/or "
+                    "'mssql_identity_increment' while also using the "
+                    "'Identity' construct."
+                )
+            util.warn_deprecated(
+                "The dialect options 'mssql_identity_start' and "
+                "'mssql_identity_increment' are deprecated. "
+                "Use the 'Identity' object instead.",
+                "1.4",
+            )
+
+        if column.identity:
+            colspec += self.process(column.identity, **kwargs)
+        elif (
             column is column.table._autoincrement_column
             or column.autoincrement is True
         ):
             if not isinstance(column.default, Sequence):
-                start = column.dialect_options["mssql"]["identity_start"]
-                increment = column.dialect_options["mssql"][
-                    "identity_increment"
-                ]
-                colspec += " IDENTITY(%s,%s)" % (start, increment)
+                colspec += self.process(
+                    Identity(start=start, increment=increment)
+                )
         else:
             default = self.get_column_default_string(column)
             if default is not None:
@@ -2298,6 +2333,14 @@ class MSDDLCompiler(compiler.DDLCompiler):
             create, prefix=prefix, **kw
         )
 
+    def visit_identity_column(self, identity, **kw):
+        text = " IDENTITY"
+        if identity.start is not None or identity.increment is not None:
+            start = 1 if identity.start is None else identity.start
+            increment = 1 if identity.increment is None else identity.increment
+            text += "(%s,%s)" % (start, increment)
+        return text
+
 
 class MSIdentifierPreparer(compiler.IdentifierPreparer):
     reserved_words = RESERVED_WORDS
@@ -2517,7 +2560,10 @@ class MSDialect(default.DefaultDialect):
         (sa_schema.PrimaryKeyConstraint, {"clustered": None}),
         (sa_schema.UniqueConstraint, {"clustered": None}),
         (sa_schema.Index, {"clustered": None, "include": None, "where": None}),
-        (sa_schema.Column, {"identity_start": 1, "identity_increment": 1}),
+        (
+            sa_schema.Column,
+            {"identity_start": None, "identity_increment": None},
+        ),
     ]
 
     def __init__(
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):
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index c56cccd8d..53c54ab65 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -43,40 +43,75 @@ case.
 To force the usage of RETURNING by default off, specify the flag
 ``implicit_returning=False`` to :func:`_sa.create_engine`.
 
-PostgreSQL 10 IDENTITY columns
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+PostgreSQL 10 and above IDENTITY columns
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 
-PostgreSQL 10 has a new IDENTITY feature that supersedes the use of SERIAL.
-Built-in support for rendering of IDENTITY is not available yet, however the
-following compilation hook may be used to replace occurrences of SERIAL with
-IDENTITY::
+PostgreSQL 10 and above have a new IDENTITY feature that supersedes the use
+of SERIAL. The :class:`_schema.Identity` construct in a
+:class:`_schema.Column` can be used to control its behavior::
 
-    from sqlalchemy.schema import CreateColumn
-    from sqlalchemy.ext.compiler import compiles
+    from sqlalchemy import Table, Column, MetaData, Integer, Computed
 
+    metadata = MetaData()
 
-    @compiles(CreateColumn, 'postgresql')
-    def use_identity(element, compiler, **kw):
-        text = compiler.visit_create_column(element, **kw)
-        text = text.replace("SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY")
-        return text
-
-Using the above, a table such as::
-
-    t = Table(
-        't', m,
-        Column('id', Integer, primary_key=True),
+    data = Table(
+        "data",
+        metadata,
+        Column(
+            'id', Integer, Identity(start=42, cycle=True), primary_key=True
+        ),
         Column('data', String)
     )
 
-Will generate on the backing database as::
+The CREATE TABLE for the above :class:`_schema.Table` object would be:
+
+.. sourcecode:: sql
 
-    CREATE TABLE t (
-        id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+    CREATE TABLE data (
+        id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE)
+            NOT NULL,
         data VARCHAR,
         PRIMARY KEY (id)
     )
 
+.. versionchanged::  1.4   Added :class:`_schema.Identity` construct
+   in a :class:`_schema.Column` to specify the option of an autoincrementing
+   column.
+
+.. note::
+
+   Previous versions of SQLAlchemy did not have built-in support for rendering
+   of IDENTITY, and could use the following compilation hook to replace
+   occurrences of SERIAL with IDENTITY::
+
+       from sqlalchemy.schema import CreateColumn
+       from sqlalchemy.ext.compiler import compiles
+
+
+       @compiles(CreateColumn, 'postgresql')
+       def use_identity(element, compiler, **kw):
+           text = compiler.visit_create_column(element, **kw)
+           text = text.replace(
+               "SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY"
+            )
+           return text
+
+   Using the above, a table such as::
+
+       t = Table(
+           't', m,
+           Column('id', Integer, primary_key=True),
+           Column('data', String)
+       )
+
+   Will generate on the backing database as::
+
+       CREATE TABLE t (
+           id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+           data VARCHAR,
+           PRIMARY KEY (id)
+       )
+
 .. _postgresql_isolation_level:
 
 Transaction Isolation Level
@@ -1996,6 +2031,7 @@ class PGDDLCompiler(compiler.DDLCompiler):
                 self.dialect.supports_smallserial
                 or not isinstance(impl_type, sqltypes.SmallInteger)
             )
+            and column.identity is None
             and (
                 column.default is None
                 or (
@@ -2022,6 +2058,8 @@ class PGDDLCompiler(compiler.DDLCompiler):
 
         if column.computed is not None:
             colspec += " " + self.process(column.computed)
+        if column.identity is not None:
+            colspec += " " + self.process(column.identity)
 
         if not column.nullable:
             colspec += " NOT NULL"