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

2 files changed, 23 insertions, 4 deletions

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