From 668872fe0108c3885adcf6cb10b653b812dc258f Mon Sep 17 00:00:00 2001 From: Gord Thompson Date: Sun, 19 Apr 2020 11:47:19 -0600 Subject: 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 --- lib/sqlalchemy/testing/plugin/plugin_base.py | 2 ++ lib/sqlalchemy/testing/requirements.py | 11 +++++++++++ lib/sqlalchemy/testing/suite/test_insert.py | 8 ++++++-- lib/sqlalchemy/testing/suite/test_sequence.py | 10 +++++----- 4 files changed, 24 insertions(+), 7 deletions(-) (limited to 'lib/sqlalchemy/testing') 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): -- cgit v1.2.1