From 26e8d3b5bdee50192e3426fba48e6b326e428e0b Mon Sep 17 00:00:00 2001
From: Federico Caselli <cfederico87@gmail.com>
Date: Sat, 30 May 2020 14:45:00 +0200
Subject: 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
---
 lib/sqlalchemy/dialects/postgresql/base.py | 82 ++++++++++++++++++++++--------
 1 file changed, 60 insertions(+), 22 deletions(-)

(limited to 'lib/sqlalchemy/dialects/postgresql/base.py')

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 7717a2526..db90e5056 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
@@ -1959,6 +1994,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 (
@@ -1985,6 +2021,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"
-- 
cgit v1.2.1