Add support for "real" sequences in mssql

Added support for "CREATE SEQUENCE" and full :class:`.Sequence` support for
Microsoft SQL Server.  This removes the deprecated feature of using
:class:`.Sequence` objects to manipulate IDENTITY characteristics which
should now be performed using ``mssql_identity_start`` and
``mssql_identity_increment`` as documented at :ref:`mssql_identity`. The
change includes a new parameter :paramref:`.Sequence.data_type` to
accommodate SQL Server's choice of datatype, which for that backend
includes INTEGER and BIGINT.   The default starting value for SQL Server's
version of :class:`.Sequence` has been set at 1; this default is now
emitted within the CREATE SEQUENCE DDL for all backends.

Fixes: #4235
Fixes: #4633
Change-Id: I6aa55c441e8146c2f002e2e201a7f645e667b916

10 files changed, 148 insertions, 51 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 5e0704597..9a38a7881 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -22,7 +22,8 @@ behavior for an integer primary key column, described at
 the first
 integer primary key column in a :class:`_schema.Table`
 will be considered to be the
-identity column and will generate DDL as such::
+identity column - unless it is associated with a :class:`.Sequence` - and will
+generate DDL as such::
 
     from sqlalchemy import Table, MetaData, Column, Integer
 
@@ -76,6 +77,10 @@ is set to ``False`` on any integer primary key column::
    the ``mssql_identity_start`` and ``mssql_identity_increment`` parameters
    documented at :ref:`mssql_identity`.
 
+.. versionchanged::  1.4   Removed the ability to use a :class:`.Sequence`
+   object to modify IDENTITY characteristics. :class:`.Sequence` objects
+   now only manipulate true T-SQL SEQUENCE types.
+
 .. note::
 
     There can only be one IDENTITY column on the table.  When using
@@ -216,6 +221,17 @@ how SQLAlchemy handles this:
 This
 is an auxiliary use case suitable for testing and bulk insert scenarios.
 
+SEQUENCE support
+----------------
+
+The :class:`.Sequence` object now creates "real" sequences, i.e.,
+``CREATE SEQUENCE``. To provide compatibility with other dialects,
+:class:`.Sequence` defaults to a data type of Integer and a start value of 1,
+even though the T-SQL defaults are BIGINT and -9223372036854775808,
+respectively.
+
+.. versionadded:: 1.4.0
+
 MAX on VARCHAR / NVARCHAR
 -------------------------
 
@@ -701,6 +717,7 @@ import re
 from . import information_schema as ischema
 from ... import exc
 from ... import schema as sa_schema
+from ... import Sequence
 from ... import sql
 from ... import types as sqltypes
 from ... import util
@@ -713,6 +730,7 @@ from ...sql import expression
 from ...sql import func
 from ...sql import quoted_name
 from ...sql import util as sql_util
+from ...sql.type_api import to_instance
 from ...types import BIGINT
 from ...types import BINARY
 from ...types import CHAR
@@ -1465,18 +1483,20 @@ class MSExecutionContext(default.DefaultExecutionContext):
 
         if self.isinsert:
             tbl = self.compiled.statement.table
-            seq_column = tbl._autoincrement_column
-            insert_has_sequence = seq_column is not None
+            id_column = tbl._autoincrement_column
+            insert_has_identity = (id_column is not None) and (
+                not isinstance(id_column.default, Sequence)
+            )
 
-            if insert_has_sequence:
+            if insert_has_identity:
                 compile_state = self.compiled.compile_state
                 self._enable_identity_insert = (
-                    seq_column.key in self.compiled_parameters[0]
+                    id_column.key in self.compiled_parameters[0]
                 ) or (
                     compile_state._dict_parameters
                     and (
-                        seq_column.key in compile_state._dict_parameters
-                        or seq_column in compile_state._dict_parameters
+                        id_column.key in compile_state._dict_parameters
+                        or id_column in compile_state._dict_parameters
                     )
                 )
 
@@ -1485,7 +1505,7 @@ class MSExecutionContext(default.DefaultExecutionContext):
 
             self._select_lastrowid = (
                 not self.compiled.inline
-                and insert_has_sequence
+                and insert_has_identity
                 and not self.compiled.returning
                 and not self._enable_identity_insert
                 and not self.executemany
@@ -1570,6 +1590,23 @@ class MSExecutionContext(default.DefaultExecutionContext):
             except Exception:
                 pass
 
+    def get_result_cursor_strategy(self, result):
+        if self._result_strategy:
+            return self._result_strategy
+        else:
+            return super(MSExecutionContext, self).get_result_cursor_strategy(
+                result
+            )
+
+    def fire_sequence(self, seq, type_):
+        return self._execute_scalar(
+            (
+                "SELECT NEXT VALUE FOR %s"
+                % self.dialect.identifier_preparer.format_sequence(seq)
+            ),
+            type_,
+        )
+
 
 class MSSQLCompiler(compiler.SQLCompiler):
     returning_precedes_values = True
@@ -1972,6 +2009,9 @@ class MSSQLCompiler(compiler.SQLCompiler):
             self.process(binary.right),
         )
 
+    def visit_sequence(self, seq, **kw):
+        return "NEXT VALUE FOR %s" % self.preparer.format_sequence(seq)
+
 
 class MSSQLStrictCompiler(MSSQLCompiler):
 
@@ -2050,41 +2090,16 @@ class MSDDLCompiler(compiler.DDLCompiler):
                 "in order to generate DDL"
             )
 
-        # install an IDENTITY Sequence if we either a sequence or an implicit
-        # IDENTITY column
-        if isinstance(column.default, sa_schema.Sequence):
-
-            if (
-                column.default.start is not None
-                or column.default.increment is not None
-                or column is not column.table._autoincrement_column
-            ):
-                util.warn_deprecated(
-                    "Use of Sequence with SQL Server in order to affect the "
-                    "parameters of the IDENTITY value is deprecated, as "
-                    "Sequence "
-                    "will correspond to an actual SQL Server "
-                    "CREATE SEQUENCE in "
-                    "a future release.  Please use the mssql_identity_start "
-                    "and mssql_identity_increment parameters.",
-                    version="1.3",
-                )
-            if column.default.start == 0:
-                start = 0
-            else:
-                start = column.default.start or 1
-
-            colspec += " IDENTITY(%s,%s)" % (
-                start,
-                column.default.increment or 1,
-            )
-        elif (
+        if (
             column is column.table._autoincrement_column
             or column.autoincrement is True
         ):
-            start = column.dialect_options["mssql"]["identity_start"]
-            increment = column.dialect_options["mssql"]["identity_increment"]
-            colspec += " IDENTITY(%s,%s)" % (start, increment)
+            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)
         else:
             default = self.get_column_default_string(column)
             if default is not None:
@@ -2203,6 +2218,18 @@ class MSDDLCompiler(compiler.DDLCompiler):
             text += " PERSISTED"
         return text
 
+    def visit_create_sequence(self, create, **kw):
+
+        if create.element.data_type is not None:
+            data_type = create.element.data_type
+        else:
+            data_type = to_instance(self.dialect.sequence_default_column_type)
+
+        prefix = " AS %s" % self.type_compiler.process(data_type)
+        return super(MSDDLCompiler, self).visit_create_sequence(
+            create, prefix=prefix, **kw
+        )
+
 
 class MSIdentifierPreparer(compiler.IdentifierPreparer):
     reserved_words = RESERVED_WORDS
@@ -2363,6 +2390,12 @@ class MSDialect(default.DefaultDialect):
 
     ischema_names = ischema_names
 
+    supports_sequences = True
+    # T-SQL's actual default is BIGINT
+    sequence_default_column_type = INTEGER
+    # T-SQL's actual default is -9223372036854775808
+    default_sequence_base = 1
+
     supports_native_boolean = False
     non_native_boolean_check_constraint = False
     supports_unicode_binds = True
@@ -2565,6 +2598,21 @@ class MSDialect(default.DefaultDialect):
 
         return c.first() is not None
 
+    @_db_plus_owner
+    def has_sequence(self, connection, sequencename, dbname, owner, schema):
+        sequences = ischema.sequences
+
+        s = sql.select([sequences.c.sequence_name]).where(
+            sequences.c.sequence_name == sequencename
+        )
+
+        if owner:
+            s = s.where(sequences.c.sequence_schema == owner)
+
+        c = connection.execute(s)
+
+        return c.first() is not None
+
     @reflection.cache
     def get_schema_names(self, connection, **kw):
         s = sql.select(
diff --git a/lib/sqlalchemy/dialects/mssql/information_schema.py b/lib/sqlalchemy/dialects/mssql/information_schema.py
index e9ab6f4f3..6cdde8386 100644
--- a/lib/sqlalchemy/dialects/mssql/information_schema.py
+++ b/lib/sqlalchemy/dialects/mssql/information_schema.py
@@ -173,3 +173,12 @@ computed_columns = Table(
     Column("definition", CoerceUnicode),
     schema="sys",
 )
+
+sequences = Table(
+    "SEQUENCES",
+    ischema,
+    Column("SEQUENCE_CATALOG", CoerceUnicode, key="sequence_catalog"),
+    Column("SEQUENCE_SCHEMA", CoerceUnicode, key="sequence_schema"),
+    Column("SEQUENCE_NAME", CoerceUnicode, key="sequence_name"),
+    schema="INFORMATION_SCHEMA",
+)
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index a85a36bb7..441e77a37 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2446,6 +2446,7 @@ class PGDialect(default.DefaultDialect):
 
     supports_sequences = True
     sequences_optional = True
+    sequence_default_column_type = INTEGER
     preexecute_autoincrement_sequences = True
     postfetch_lastrowid = False
 
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index d0f5cfe96..c682a8ee1 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -23,6 +23,7 @@ from . import cursor as _cursor
 from . import interfaces
 from .. import event
 from .. import exc
+from .. import Integer
 from .. import pool
 from .. import processors
 from .. import types as sqltypes
@@ -58,6 +59,8 @@ class DefaultDialect(interfaces.Dialect):
     # not cx_oracle.
     execute_sequence_format = tuple
 
+    sequence_default_column_type = Integer
+
     supports_views = True
     supports_sequences = False
     sequences_optional = False
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index fc66ca517..4bd19e04b 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -3743,14 +3743,17 @@ class DDLCompiler(Compiled):
             drop.element, use_table=True
         )
 
-    def visit_create_sequence(self, create, **kw):
+    def visit_create_sequence(self, create, prefix=None, **kw):
         text = "CREATE SEQUENCE %s" % self.preparer.format_sequence(
             create.element
         )
+        if prefix:
+            text += prefix
         if create.element.increment is not None:
             text += " INCREMENT BY %d" % create.element.increment
-        if create.element.start is not None:
-            text += " START WITH %d" % create.element.start
+        if create.element.start is None:
+            create.element.start = self.dialect.default_sequence_base
+        text += " START WITH %d" % create.element.start
         if create.element.minvalue is not None:
             text += " MINVALUE %d" % create.element.minvalue
         if create.element.maxvalue is not None:
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 263f579de..ee411174c 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -49,6 +49,7 @@ from .elements import ColumnElement
 from .elements import quoted_name
 from .elements import TextClause
 from .selectable import TableClause
+from .type_api import to_instance
 from .visitors import InternalTraversal
 from .. import event
 from .. import exc
@@ -2312,6 +2313,7 @@ class IdentityOptions(object):
         cycle=None,
         cache=None,
         order=None,
+        data_type=None,
     ):
         """Construct a :class:`.IdentityOptions` object.
 
@@ -2330,7 +2332,8 @@ class IdentityOptions(object):
          sequence which are calculated in advance.
         :param order: optional boolean value; if true, renders the
          ORDER keyword.
-         name.
+        :param data_type: The type to be returned by the sequence.
+
         """
         self.start = start
         self.increment = increment
@@ -2341,6 +2344,10 @@ class IdentityOptions(object):
         self.cycle = cycle
         self.cache = cache
         self.order = order
+        if data_type is not None:
+            self.data_type = to_instance(data_type)
+        else:
+            self.data_type = None
 
 
 class Sequence(IdentityOptions, roles.StatementRole, DefaultGenerator):
@@ -2393,6 +2400,7 @@ class Sequence(IdentityOptions, roles.StatementRole, DefaultGenerator):
         schema=None,
         cache=None,
         order=None,
+        data_type=None,
         optional=False,
         quote=None,
         metadata=None,
@@ -2402,6 +2410,7 @@ class Sequence(IdentityOptions, roles.StatementRole, DefaultGenerator):
         """Construct a :class:`.Sequence` object.
 
         :param name: the name of the sequence.
+
         :param start: the starting index of the sequence.  This value is
          used when the CREATE SEQUENCE command is emitted to the database
          as the value of the "START WITH" clause.   If ``None``, the
@@ -2478,6 +2487,12 @@ class Sequence(IdentityOptions, roles.StatementRole, DefaultGenerator):
 
          .. versionadded:: 1.1.12
 
+        :param data_type: The type to be returned by the sequence, for
+         dialects that allow us to choose between INTEGER, BIGINT, etc.
+         (e.g., mssql).
+
+         .. versionadded:: 1.4.0
+
         :param optional: boolean value, when ``True``, indicates that this
          :class:`.Sequence` object only needs to be explicitly generated
          on backends that don't provide another way to generate primary
@@ -2542,6 +2557,7 @@ class Sequence(IdentityOptions, roles.StatementRole, DefaultGenerator):
             cycle=cycle,
             cache=cache,
             order=order,
+            data_type=data_type,
         )
         self.name = quoted_name(name, quote)
         self.optional = optional
diff --git a/lib/sqlalchemy/testing/plugin/plugin_base.py b/lib/sqlalchemy/testing/plugin/plugin_base.py
index bf168efce..b31a4ff3e 100644
--- a/lib/sqlalchemy/testing/plugin/plugin_base.py
+++ b/lib/sqlalchemy/testing/plugin/plugin_base.py
@@ -477,6 +477,8 @@ def _prep_testing_database(options, file_config):
                         )
                     )
 
+            # TODO: need to do a get_sequences and drop them also after tables
+
 
 @post
 def _reverse_topological(options, file_config):
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 31011a970..2d51e7c9b 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -19,6 +19,7 @@ import platform
 import sys
 
 from . import exclusions
+from . import fails_on_everything_except
 from .. import util
 
 
@@ -1189,3 +1190,13 @@ class SuiteRequirements(Requirements):
             lambda config: not config.db.dialect.supports_is_distinct_from,
             "driver doesn't support an IS DISTINCT FROM construct",
         )
+
+    @property
+    def emulated_lastrowid_even_with_sequences(self):
+        """"target dialect retrieves cursor.lastrowid or an equivalent
+        after an insert() construct executes, even if the table has a
+        Sequence on it..
+        """
+        return fails_on_everything_except(
+            "mysql", "sqlite+pysqlite", "sqlite+pysqlcipher", "sybase",
+        )
diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py
index 92e38ab20..65741941f 100644
--- a/lib/sqlalchemy/testing/suite/test_insert.py
+++ b/lib/sqlalchemy/testing/suite/test_insert.py
@@ -41,7 +41,9 @@ class LastrowidTest(fixtures.TablesTest):
 
     def _assert_round_trip(self, table, conn):
         row = conn.execute(table.select()).first()
-        eq_(row, (conn.engine.dialect.default_sequence_base, "some data"))
+        eq_(
+            row, (conn.dialect.default_sequence_base, "some data",),
+        )
 
     def test_autoincrement_on_insert(self, connection):
 
@@ -282,7 +284,9 @@ class ReturningTest(fixtures.TablesTest):
 
     def _assert_round_trip(self, table, conn):
         row = conn.execute(table.select()).first()
-        eq_(row, (conn.engine.dialect.default_sequence_base, "some data"))
+        eq_(
+            row, (conn.dialect.default_sequence_base, "some data",),
+        )
 
     @classmethod
     def define_tables(cls, metadata):
diff --git a/lib/sqlalchemy/testing/suite/test_sequence.py b/lib/sqlalchemy/testing/suite/test_sequence.py
index db5582c21..dda447c0d 100644
--- a/lib/sqlalchemy/testing/suite/test_sequence.py
+++ b/lib/sqlalchemy/testing/suite/test_sequence.py
@@ -23,7 +23,7 @@ class SequenceTest(fixtures.TablesTest):
         Table(
             "seq_pk",
             metadata,
-            Column("id", Integer, Sequence("tab_id_seq"), primary_key=True),
+            Column("id", Integer, Sequence("tab_id_seq"), primary_key=True,),
             Column("data", String(50)),
         )
 
@@ -33,7 +33,7 @@ class SequenceTest(fixtures.TablesTest):
             Column(
                 "id",
                 Integer,
-                Sequence("tab_id_seq", optional=True),
+                Sequence("tab_id_seq", data_type=Integer, optional=True),
                 primary_key=True,
             ),
             Column("data", String(50)),
@@ -45,11 +45,11 @@ class SequenceTest(fixtures.TablesTest):
 
     def test_insert_lastrowid(self, connection):
         r = connection.execute(self.tables.seq_pk.insert(), data="some data")
-        eq_(r.inserted_primary_key, [1])
+        eq_(r.inserted_primary_key, [testing.db.dialect.default_sequence_base])
 
     def test_nextval_direct(self, connection):
         r = connection.execute(self.tables.seq_pk.c.id.default)
-        eq_(r, 1)
+        eq_(r, testing.db.dialect.default_sequence_base)
 
     @requirements.sequences_optional
     def test_optional_seq(self, connection):
@@ -60,7 +60,7 @@ class SequenceTest(fixtures.TablesTest):
 
     def _assert_round_trip(self, table, conn):
         row = conn.execute(table.select()).first()
-        eq_(row, (1, "some data"))
+        eq_(row, (testing.db.dialect.default_sequence_base, "some data"))
 
 
 class SequenceCompilerTest(testing.AssertsCompiledSQL, fixtures.TestBase):