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

1 files changed, 16 insertions, 14 deletions

diff --git a/test/sql/test_insert_exec.py b/test/sql/test_insert_exec.py
index 9f9525f3c..a081766bc 100644
--- a/test/sql/test_insert_exec.py
+++ b/test/sql/test_insert_exec.py
@@ -350,7 +350,12 @@ class TableInsertTest(fixtures.TablesTest):
         Table(
             "foo",
             metadata,
-            Column("id", Integer, Sequence("t_id_seq"), primary_key=True),
+            Column(
+                "id",
+                testing.db.dialect.sequence_default_column_type,
+                Sequence("t_id_seq"),
+                primary_key=True,
+            ),
             Column("data", String(50)),
             Column("x", Integer),
         )
@@ -396,7 +401,7 @@ class TableInsertTest(fixtures.TablesTest):
             t.insert().values(
                 id=func.next_value(Sequence("t_id_seq")), data="data", x=5
             ),
-            (1, "data", 5),
+            (testing.db.dialect.default_sequence_base, "data", 5),
         )
 
     def test_uppercase(self):
@@ -431,8 +436,8 @@ class TableInsertTest(fixtures.TablesTest):
         t = self.tables.foo
         self._test(
             t.insert().values(data="data", x=5),
-            (1, "data", 5),
-            inserted_primary_key=[1],
+            (testing.db.dialect.default_sequence_base, "data", 5),
+            inserted_primary_key=[testing.db.dialect.default_sequence_base],
         )
 
     def test_uppercase_direct_params(self):
@@ -452,9 +457,6 @@ class TableInsertTest(fixtures.TablesTest):
             returning=(1, 5),
         )
 
-    @testing.fails_on(
-        "mssql", "lowercase table doesn't support identity insert disable"
-    )
     def test_direct_params(self):
         t = self._fixture()
         self._test(
@@ -463,27 +465,26 @@ class TableInsertTest(fixtures.TablesTest):
             inserted_primary_key=[],
         )
 
-    @testing.fails_on(
-        "mssql", "lowercase table doesn't support identity insert disable"
-    )
     @testing.requires.returning
     def test_direct_params_returning(self):
         t = self._fixture()
         self._test(
             t.insert().values(id=1, data="data", x=5).returning(t.c.id, t.c.x),
-            (1, "data", 5),
-            returning=(1, 5),
+            (testing.db.dialect.default_sequence_base, "data", 5),
+            returning=(testing.db.dialect.default_sequence_base, 5),
         )
 
+    @testing.requires.emulated_lastrowid_even_with_sequences
     @testing.requires.emulated_lastrowid
     def test_implicit_pk(self):
         t = self._fixture()
         self._test(
             t.insert().values(data="data", x=5),
-            (1, "data", 5),
+            (testing.db.dialect.default_sequence_base, "data", 5),
             inserted_primary_key=[],
         )
 
+    @testing.requires.emulated_lastrowid_even_with_sequences
     @testing.requires.emulated_lastrowid
     def test_implicit_pk_multi_rows(self):
         t = self._fixture()
@@ -497,11 +498,12 @@ class TableInsertTest(fixtures.TablesTest):
             [(1, "d1", 5), (2, "d2", 6), (3, "d3", 7)],
         )
 
+    @testing.requires.emulated_lastrowid_even_with_sequences
     @testing.requires.emulated_lastrowid
     def test_implicit_pk_inline(self):
         t = self._fixture()
         self._test(
             t.insert().inline().values(data="data", x=5),
-            (1, "data", 5),
+            (testing.db.dialect.default_sequence_base, "data", 5),
             inserted_primary_key=[],
         )