diff options
| author | Federico Caselli <cfederico87@gmail.com> | 2022-09-24 15:50:26 +0200 |
|---|---|---|
| committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2022-10-17 15:36:25 -0400 |
| commit | 974b1bd0fc40e11fc2886b5a9fc333feeeebf546 (patch) | |
| tree | 421f0545c13a203f40435c4646a0de664e0e9756 | |
| parent | 665c94cc2f0340735515c4f4477e11b556d2bcd8 (diff) | |
| download | sqlalchemy-974b1bd0fc40e11fc2886b5a9fc333feeeebf546.tar.gz | |
Revert automatic set of sequence start to 1
The :class:`.Sequence` construct restores itself to the DDL behavior it
had prior to the 1.4 series, where creating a :class:`.Sequence` with
no additional arguments will emit a simple ``CREATE SEQUENCE`` instruction
**without** any additional parameters for "start value". For most backends,
this is how things worked previously in any case; **however**, for
MS SQL Server, the default value on this database is
``-2**63``; to prevent this generally impractical default
from taking effect on SQL Server, the :paramref:`.Sequence.start` parameter
should be provided. As usage of :class:`.Sequence` is unusual
for SQL Server which for many years has standardized on ``IDENTITY``,
it is hoped that this change has minimal impact.
Fixes: #7211
Change-Id: I1207ea10c8cb1528a1519a0fb3581d9621c27b31
36 files changed, 480 insertions, 175 deletions
diff --git a/doc/build/changelog/migration_14.rst b/doc/build/changelog/migration_14.rst index de047d85b..df7080d0a 100644 --- a/doc/build/changelog/migration_14.rst +++ b/doc/build/changelog/migration_14.rst @@ -2898,7 +2898,9 @@ integer primary key column of a table:: Table( "some_table", metadata, - Column("id", Integer, Sequence("some_seq", optional=True), primary_key=True), + Column( + "id", Integer, Sequence("some_seq", start=1, optional=True), primary_key=True + ), ) The above :class:`.Sequence` is only used for DDL and INSERT statements if the diff --git a/doc/build/changelog/unreleased_20/7211.rst b/doc/build/changelog/unreleased_20/7211.rst new file mode 100644 index 000000000..4979e2e92 --- /dev/null +++ b/doc/build/changelog/unreleased_20/7211.rst @@ -0,0 +1,19 @@ +.. change:: + :tags: schema, mssql + :tickets: 7211 + + The :class:`.Sequence` construct restores itself to the DDL behavior it + had prior to the 1.4 series, where creating a :class:`.Sequence` with + no additional arguments will emit a simple ``CREATE SEQUENCE`` instruction + **without** any additional parameters for "start value". For most backends, + this is how things worked previously in any case; **however**, for + MS SQL Server, the default value on this database is + ``-2**63``; to prevent this generally impractical default + from taking effect on SQL Server, the :paramref:`.Sequence.start` parameter + should be provided. As usage of :class:`.Sequence` is unusual + for SQL Server which for many years has standardized on ``IDENTITY``, + it is hoped that this change has minimal impact. + + .. seealso:: + + :ref:`change_7211` diff --git a/doc/build/changelog/whatsnew_20.rst b/doc/build/changelog/whatsnew_20.rst index 061f8d4ce..04c1be3e0 100644 --- a/doc/build/changelog/whatsnew_20.rst +++ b/doc/build/changelog/whatsnew_20.rst @@ -1468,6 +1468,7 @@ automatically. :ref:`postgresql_psycopg` + .. _ticket_8054: Dialect support for oracledb @@ -1566,6 +1567,60 @@ as ``False``:: :ticket:`8567` +.. _change_7211: + +The ``Sequence`` construct reverts to not having any explicit default "start" value; impacts MS SQL Server +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +Prior to SQLAlchemy 1.4, the :class:`.Sequence` construct would emit only +simple ``CREATE SEQUENCE`` DDL, if no additional arguments were specified:: + + >>> # SQLAlchemy 1.3 (and 2.0) + >>> from sqlalchemy import Sequence + >>> from sqlalchemy.schema import CreateSequence + >>> print(CreateSequence(Sequence("my_seq"))) + CREATE SEQUENCE my_seq + +However, as :class:`.Sequence` support was added for MS SQL Server, where the +default start value is inconveniently set to ``-2**63``, +version 1.4 decided to default the DDL to emit a start value of 1, if +:paramref:`.Sequence.start` were not otherwise provided:: + + >>> # SQLAlchemy 1.4 (only) + >>> from sqlalchemy import Sequence + >>> from sqlalchemy.schema import CreateSequence + >>> print(CreateSequence(Sequence("my_seq"))) + CREATE SEQUENCE my_seq START WITH 1 + +This change has introduced other complexities, including that when +the :paramref:`.Sequence.min_value` parameter is included, this default of +``1`` should in fact default to what :paramref:`.Sequence.min_value` +states, else a min_value that's below the start_value may be seen as +contradictory. As looking at this issue started to become a bit of a +rabbit hole of other various edge cases, we decided to instead revert this +change and restore the original behavior of :class:`.Sequence` which is +to have no opinion, and just emit CREATE SEQUENCE, allowing the database +itself to make its decisions on how the various parameters of ``SEQUENCE`` +should interact with each other. + +Therefore, to ensure that the start value is 1 on all backends, +**the start value of 1 may be indicated explicitly**, as below:: + + >>> # All SQLAlchemy versions + >>> from sqlalchemy import Sequence + >>> from sqlalchemy.schema import CreateSequence + >>> print(CreateSequence(Sequence("my_seq", start=1))) + CREATE SEQUENCE my_seq START WITH 1 + +Beyond all of that, for autogeneration of integer primary keys on modern +backends including PostgreSQL, Oracle, SQL Server, the :class:`.Identity` +construct should be preferred, which also works the same way in 1.4 and 2.0 +with no changes in behavior. + + +:ticket:`7211` + + .. _change_6980: "with_variant()" clones the original TypeEngine rather than changing the type diff --git a/doc/build/core/defaults.rst b/doc/build/core/defaults.rst index 62cd0623b..0989d742f 100644 --- a/doc/build/core/defaults.rst +++ b/doc/build/core/defaults.rst @@ -485,7 +485,7 @@ object, it can be invoked with its "next value" instruction by passing it directly to a SQL execution method:: with my_engine.connect() as conn: - seq = Sequence("some_sequence") + seq = Sequence("some_sequence", start=1) nextid = conn.execute(seq) In order to embed the "next value" function of a :class:`.Sequence` @@ -493,7 +493,7 @@ inside of a SQL statement like a SELECT or INSERT, use the :meth:`.Sequence.next method, which will render at statement compilation time a SQL function that is appropriate for the target backend:: - >>> my_seq = Sequence("some_sequence") + >>> my_seq = Sequence("some_sequence", start=1) >>> stmt = select(my_seq.next_value()) >>> print(stmt.compile(dialect=postgresql.dialect())) SELECT nextval('some_sequence') AS next_value_1 @@ -529,8 +529,8 @@ allows for the following behaviors: * The :class:`.Sequence` will inherit the :paramref:`_schema.MetaData.schema` parameter specified to the target :class:`_schema.MetaData`, which - affects the production of CREATE / DROP DDL, if any. - + affects the production of CREATE / DROP DDL as well as how the + :meth:`.Sequence.next_value` function is rendered in SQL statements. * The :meth:`_schema.MetaData.create_all` and :meth:`_schema.MetaData.drop_all` methods will emit CREATE / DROP for this :class:`.Sequence`, @@ -548,7 +548,10 @@ The preceding sections illustrate how to associate a :class:`.Sequence` with a :class:`_schema.Column` as the **Python side default generator**:: Column( - "cart_id", Integer, Sequence("cart_id_seq", metadata=metadata_obj), primary_key=True + "cart_id", + Integer, + Sequence("cart_id_seq", metadata=metadata_obj, start=1), + primary_key=True, ) In the above case, the :class:`.Sequence` will automatically be subject @@ -565,7 +568,7 @@ we illustrate the same :class:`.Sequence` being associated with the :class:`_schema.Column` both as the Python-side default generator as well as the server-side default generator:: - cart_id_seq = Sequence("cart_id_seq", metadata=metadata_obj) + cart_id_seq = Sequence("cart_id_seq", metadata=metadata_obj, start=1) table = Table( "cartitems", metadata_obj, @@ -585,7 +588,7 @@ or with the ORM:: class CartItem(Base): __tablename__ = "cartitems" - cart_id_seq = Sequence("cart_id_seq", metadata=Base.metadata) + cart_id_seq = Sequence("cart_id_seq", metadata=Base.metadata, start=1) cart_id = Column( Integer, cart_id_seq, server_default=cart_id_seq.next_value(), primary_key=True ) diff --git a/doc/build/orm/persistence_techniques.rst b/doc/build/orm/persistence_techniques.rst index f98f2bb1e..04aee4a41 100644 --- a/doc/build/orm/persistence_techniques.rst +++ b/doc/build/orm/persistence_techniques.rst @@ -373,7 +373,7 @@ the :class:`.Sequence` construct:: class MyOracleModel(Base): __tablename__ = "my_table" - id = mapped_column(Integer, Sequence("my_sequence"), primary_key=True) + id = mapped_column(Integer, Sequence("my_sequence", start=1), primary_key=True) data = mapped_column(String(50)) The INSERT for a model as above on Oracle looks like: diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 085a2c27b..fdb0704f6 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -335,12 +335,31 @@ 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 start value of 1, even though the -T-SQL defaults is -9223372036854775808. +The :class:`.Sequence` object creates "real" sequences, i.e., +``CREATE SEQUENCE``:: -.. versionadded:: 1.4.0 + >>> from sqlalchemy import Sequence + >>> from sqlalchemy.schema import CreateSequence + >>> from sqlalchemy.dialects import mssql + >>> print(CreateSequence(Sequence("my_seq", start=1)).compile(dialect=mssql.dialect())) + CREATE SEQUENCE my_seq START WITH 1 + +For integer primary key generation, SQL Server's ``IDENTITY`` construct should +generally be preferred vs. sequence. + +..tip:: + + The default start value for T-SQL is ``-2**63`` instead of 1 as + in most other SQL databases. Users should explicitly set the + :paramref:`.Sequence.start` to 1 if that's the expected default:: + + seq = Sequence("my_sequence", start=1) + +.. versionadded:: 1.4 added SQL Server support for :class:`.Sequence` + +.. versionchanged:: 2.0 The SQL Server dialect will no longer implicitly + render "START WITH 1" for ``CREATE SEQUENCE``, which was the behavior + first implemented in version 1.4. MAX on VARCHAR / NVARCHAR ------------------------- @@ -2907,7 +2926,9 @@ class MSDialect(default.DefaultDialect): supports_sequences = True sequences_optional = True - # T-SQL's actual default is -9223372036854775808 + # This is actually used for autoincrement, where itentity is used that + # starts with 1. + # for sequences T-SQL's actual default is -9223372036854775808 default_sequence_base = 1 supports_native_boolean = False diff --git a/lib/sqlalchemy/dialects/mssql/provision.py b/lib/sqlalchemy/dialects/mssql/provision.py index f307c7140..a7ecf4aa3 100644 --- a/lib/sqlalchemy/dialects/mssql/provision.py +++ b/lib/sqlalchemy/dialects/mssql/provision.py @@ -14,6 +14,7 @@ from ...testing.provision import drop_all_schema_objects_pre_tables from ...testing.provision import drop_db from ...testing.provision import get_temp_table_name from ...testing.provision import log +from ...testing.provision import normalize_sequence from ...testing.provision import run_reap_dbs from ...testing.provision import temp_table_keyword_args @@ -116,3 +117,10 @@ def drop_all_schema_objects_pre_tables(cfg, eng): ) ) ) + + +@normalize_sequence.for_db("mssql") +def normalize_sequence(cfg, sequence): + if sequence.start is None: + sequence.start = 1 + return sequence diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index c06da6ffe..6481ae483 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -66,14 +66,14 @@ specify sequences, use the sqlalchemy.schema.Sequence object which is passed to a Column construct:: t = Table('mytable', metadata, - Column('id', Integer, Sequence('id_seq'), primary_key=True), + Column('id', Integer, Sequence('id_seq', start=1), primary_key=True), Column(...), ... ) This step is also required when using table reflection, i.e. autoload_with=engine:: t = Table('mytable', metadata, - Column('id', Integer, Sequence('id_seq'), primary_key=True), + Column('id', Integer, Sequence('id_seq', start=1), primary_key=True), autoload_with=engine ) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index f3db1a95e..5eab8f47c 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -27,9 +27,13 @@ default corresponding to the column. To specify a specific named sequence to be used for primary key generation, use the :func:`~sqlalchemy.schema.Sequence` construct:: - Table('sometable', metadata, - Column('id', Integer, Sequence('some_id_seq'), primary_key=True) + Table( + "sometable", + metadata, + Column( + "id", Integer, Sequence("some_id_seq", start=1), primary_key=True ) + ) When SQLAlchemy issues a single INSERT statement, to fulfill the contract of having the "last insert identifier" available, a RETURNING clause is added to diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index cb3d0528f..71ceb3301 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -129,8 +129,7 @@ class DefaultDialect(Dialect): include_set_input_sizes: Optional[Set[Any]] = None exclude_set_input_sizes: Optional[Set[Any]] = None - # the first value we'd get for an autoincrement - # column. + # the first value we'd get for an autoincrement column. default_sequence_base = 1 # most DBAPIs happy with this for execute(). diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index efe0ea2b4..5b7238a07 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -5674,8 +5674,6 @@ class DDLCompiler(Compiled): if prefix: text += prefix - if create.element.start is None: - create.element.start = self.dialect.default_sequence_base options = self.get_identity_options(create.element) if options: text += " " + options diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py index 5bfbd37c7..5d5f76c75 100644 --- a/lib/sqlalchemy/sql/schema.py +++ b/lib/sqlalchemy/sql/schema.py @@ -3323,7 +3323,7 @@ class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator): some_table = Table( 'some_table', metadata, - Column('id', Integer, Sequence('some_table_seq'), + Column('id', Integer, Sequence('some_table_seq', start=1), primary_key=True) ) @@ -3375,9 +3375,16 @@ class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator): :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 + as the value of the "START WITH" clause. If ``None``, the clause is omitted, which on most platforms indicates a starting value of 1. + + .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter + is required in order to have DDL emit "START WITH". This is a + reversal of a change made in version 1.4 which would implicitly + render "START WITH 1" if the :paramref:`.Sequence.start` were + not included. See :ref:`change_7211` for more detail. + :param increment: the increment value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the "INCREMENT BY" clause. If ``None``, diff --git a/lib/sqlalchemy/testing/provision.py b/lib/sqlalchemy/testing/provision.py index a8650f222..43821854c 100644 --- a/lib/sqlalchemy/testing/provision.py +++ b/lib/sqlalchemy/testing/provision.py @@ -475,3 +475,13 @@ def upsert(cfg, table, returning, set_lambda=None): raise NotImplementedError( f"backend does not include an upsert implementation: {cfg.db.url}" ) + + +@register.init +def normalize_sequence(cfg, sequence): + """Normalize sequence parameters for dialect that don't start with 1 + by default. + + The default implementation does nothing + """ + return sequence diff --git a/lib/sqlalchemy/testing/suite/test_sequence.py b/lib/sqlalchemy/testing/suite/test_sequence.py index e15fad642..a605e4f42 100644 --- a/lib/sqlalchemy/testing/suite/test_sequence.py +++ b/lib/sqlalchemy/testing/suite/test_sequence.py @@ -5,6 +5,7 @@ from .. import fixtures from ..assertions import eq_ from ..assertions import is_true from ..config import requirements +from ..provision import normalize_sequence from ..schema import Column from ..schema import Table from ... import inspect @@ -29,7 +30,7 @@ class SequenceTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("tab_id_seq"), + normalize_sequence(config, Sequence("tab_id_seq")), primary_key=True, ), Column("data", String(50)), @@ -41,7 +42,10 @@ class SequenceTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("tab_id_seq", data_type=Integer, optional=True), + normalize_sequence( + config, + Sequence("tab_id_seq", data_type=Integer, optional=True), + ), primary_key=True, ), Column("data", String(50)), @@ -53,7 +57,7 @@ class SequenceTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("noret_id_seq"), + normalize_sequence(config, Sequence("noret_id_seq")), primary_key=True, ), Column("data", String(50)), @@ -67,7 +71,12 @@ class SequenceTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("noret_sch_id_seq", schema=config.test_schema), + normalize_sequence( + config, + Sequence( + "noret_sch_id_seq", schema=config.test_schema + ), + ), primary_key=True, ), Column("data", String(50)), @@ -118,7 +127,9 @@ class SequenceTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("noret_sch_id_seq", schema="alt_schema"), + normalize_sequence( + config, Sequence("noret_sch_id_seq", schema="alt_schema") + ), primary_key=True, ), Column("data", String(50)), @@ -134,7 +145,9 @@ class SequenceTest(fixtures.TablesTest): @testing.requires.schemas def test_nextval_direct_schema_translate(self, connection): - seq = Sequence("noret_sch_id_seq", schema="alt_schema") + seq = normalize_sequence( + config, Sequence("noret_sch_id_seq", schema="alt_schema") + ) connection = connection.execution_options( schema_translate_map={"alt_schema": config.test_schema} ) @@ -151,7 +164,9 @@ class SequenceCompilerTest(testing.AssertsCompiledSQL, fixtures.TestBase): table = Table( "x", MetaData(), - Column("y", Integer, Sequence("y_seq")), + Column( + "y", Integer, normalize_sequence(config, Sequence("y_seq")) + ), Column("q", Integer), ) @@ -159,7 +174,7 @@ class SequenceCompilerTest(testing.AssertsCompiledSQL, fixtures.TestBase): seq_nextval = connection.dialect.statement_compiler( statement=None, dialect=connection.dialect - ).visit_sequence(Sequence("y_seq")) + ).visit_sequence(normalize_sequence(config, Sequence("y_seq"))) self.assert_compile( stmt, "INSERT INTO x (y, q) VALUES (%s, 5)" % (seq_nextval,), @@ -176,16 +191,28 @@ class HasSequenceTest(fixtures.TablesTest): @classmethod def define_tables(cls, metadata): - Sequence("user_id_seq", metadata=metadata) - Sequence( - "other_seq", metadata=metadata, nomaxvalue=True, nominvalue=True + normalize_sequence(config, Sequence("user_id_seq", metadata=metadata)) + normalize_sequence( + config, + Sequence( + "other_seq", + metadata=metadata, + nomaxvalue=True, + nominvalue=True, + ), ) if testing.requires.schemas.enabled: - Sequence( - "user_id_seq", schema=config.test_schema, metadata=metadata + normalize_sequence( + config, + Sequence( + "user_id_seq", schema=config.test_schema, metadata=metadata + ), ) - Sequence( - "schema_seq", schema=config.test_schema, metadata=metadata + normalize_sequence( + config, + Sequence( + "schema_seq", schema=config.test_schema, metadata=metadata + ), ) Table( "user_id_table", @@ -199,7 +226,7 @@ class HasSequenceTest(fixtures.TablesTest): def test_has_sequence_cache(self, connection, metadata): insp = inspect(connection) eq_(insp.has_sequence("user_id_seq"), True) - ss = Sequence("new_seq", metadata=metadata) + ss = normalize_sequence(config, Sequence("new_seq", metadata=metadata)) eq_(insp.has_sequence("new_seq"), False) ss.create(connection) try: diff --git a/test/dialect/mssql/test_sequence.py b/test/dialect/mssql/test_sequence.py index 5afe9c075..c03607e7a 100644 --- a/test/dialect/mssql/test_sequence.py +++ b/test/dialect/mssql/test_sequence.py @@ -8,8 +8,10 @@ from sqlalchemy import select from sqlalchemy import Sequence from sqlalchemy import String from sqlalchemy import Table +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures +from sqlalchemy.testing.provision import normalize_sequence class SequenceTest(fixtures.TablesTest): @@ -24,6 +26,18 @@ class SequenceTest(fixtures.TablesTest): Column( "id", Integer, default=Sequence("int_seq", data_type=Integer()) ), + Column( + "id_provision", + Integer, + default=normalize_sequence( + config, Sequence("id_provision", data_type=Integer()) + ), + ), + Column( + "id_start", + Integer, + default=Sequence("id_start", data_type=Integer(), start=42), + ), Column("txt", String(50)), ) @@ -56,8 +70,10 @@ class SequenceTest(fixtures.TablesTest): def test_int_seq(self, connection): t = self.tables.int_seq_t connection.execute(t.insert().values({"txt": "int_seq test"})) - result = connection.scalar(select(t.c.id)) - eq_(result, 1) + result = connection.execute(select(t)).first() + eq_(result.id, -(2**31)) + eq_(result.id_provision, 1) + eq_(result.id_start, 42) def test_bigint_seq(self, connection): t = self.tables.bigint_seq_t diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py index 96969b459..2973c6e39 100644 --- a/test/dialect/oracle/test_compiler.py +++ b/test/dialect/oracle/test_compiler.py @@ -1622,7 +1622,7 @@ class SequenceTest(fixtures.TestBase, AssertsCompiledSQL): ddl.CreateSequence( Sequence("my_seq", nomaxvalue=True, nominvalue=True) ), - "CREATE SEQUENCE my_seq START WITH 1 NOMINVALUE NOMAXVALUE", + "CREATE SEQUENCE my_seq NOMINVALUE NOMAXVALUE", dialect=oracle.OracleDialect(), ) diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index c763dbeac..96a8e7d5a 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -91,11 +91,11 @@ class SequenceTest(fixtures.TestBase, AssertsCompiledSQL): (SmallInteger, "AS SMALLINT "), (BigInteger, "AS BIGINT "), ) - def test_create_index_concurrently(self, type_, text): + def test_compile_type(self, type_, text): s = Sequence("s1", data_type=type_) self.assert_compile( schema.CreateSequence(s), - "CREATE SEQUENCE s1 %sSTART WITH 1" % text, + f"CREATE SEQUENCE s1 {text}".strip(), dialect=postgresql.dialect(), ) diff --git a/test/engine/test_ddlevents.py b/test/engine/test_ddlevents.py index 1b57895dc..1fa17c889 100644 --- a/test/engine/test_ddlevents.py +++ b/test/engine/test_ddlevents.py @@ -18,9 +18,11 @@ from sqlalchemy.schema import DropConstraint from sqlalchemy.schema import ForeignKeyConstraint from sqlalchemy.schema import Sequence from sqlalchemy.testing import AssertsCompiledSQL +from sqlalchemy.testing import config from sqlalchemy.testing import engines from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table @@ -542,7 +544,7 @@ class SequenceDDLEventTest(DDLEventWCreateHarness, fixtures.TestBase): @testing.fixture def produce_subject(self): - return Sequence("my_seq") + return normalize_sequence(config, Sequence("my_seq")) @testing.fixture def produce_table_integrated_subject(self, metadata, produce_subject): diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py index 2940a1e7f..c1fe3140e 100644 --- a/test/engine/test_execute.py +++ b/test/engine/test_execute.py @@ -53,6 +53,7 @@ from sqlalchemy.testing import is_false from sqlalchemy.testing import is_not from sqlalchemy.testing import is_true from sqlalchemy.testing.assertsql import CompiledSQL +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table from sqlalchemy.testing.util import gc_collect @@ -1222,7 +1223,7 @@ class MockStrategyTest(fixtures.TestBase): Column( "pk", Integer, - Sequence("testtable_pk_seq"), + normalize_sequence(config, Sequence("testtable_pk_seq")), primary_key=True, ), ) @@ -2408,7 +2409,7 @@ class EngineEventsTest(fixtures.TestBase): Column( "x", Integer, - Sequence("t_id_seq"), + normalize_sequence(config, Sequence("t_id_seq")), primary_key=True, ), implicit_returning=False, diff --git a/test/engine/test_reflection.py b/test/engine/test_reflection.py index 7c5672066..2aefecaef 100644 --- a/test/engine/test_reflection.py +++ b/test/engine/test_reflection.py @@ -38,6 +38,7 @@ from sqlalchemy.testing import is_true from sqlalchemy.testing import mock from sqlalchemy.testing import not_in from sqlalchemy.testing import skip +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table @@ -1391,7 +1392,9 @@ class CreateDropTest(fixtures.TablesTest): Column( "user_id", sa.Integer, - sa.Sequence("user_id_seq", optional=True), + normalize_sequence( + config, sa.Sequence("user_id_seq", optional=True) + ), primary_key=True, ), Column("user_name", sa.String(40)), @@ -1403,7 +1406,9 @@ class CreateDropTest(fixtures.TablesTest): Column( "address_id", sa.Integer, - sa.Sequence("address_id_seq", optional=True), + normalize_sequence( + config, sa.Sequence("address_id_seq", optional=True) + ), primary_key=True, ), Column("user_id", sa.Integer, sa.ForeignKey("users.user_id")), @@ -1416,7 +1421,9 @@ class CreateDropTest(fixtures.TablesTest): Column( "order_id", sa.Integer, - sa.Sequence("order_id_seq", optional=True), + normalize_sequence( + config, sa.Sequence("order_id_seq", optional=True) + ), primary_key=True, ), Column("user_id", sa.Integer, sa.ForeignKey("users.user_id")), @@ -1429,7 +1436,9 @@ class CreateDropTest(fixtures.TablesTest): Column( "item_id", sa.INT, - sa.Sequence("items_id_seq", optional=True), + normalize_sequence( + config, sa.Sequence("items_id_seq", optional=True) + ), primary_key=True, ), Column("order_id", sa.INT, sa.ForeignKey("orders")), @@ -1597,7 +1606,7 @@ class UnicodeReflectionTest(fixtures.TablesTest): Column( "id", sa.Integer, - sa.Sequence(cname + "_id_seq"), + normalize_sequence(config, sa.Sequence(cname + "_id_seq")), primary_key=True, ), Column(cname, Integer), diff --git a/test/ext/asyncio/test_session_py3k.py b/test/ext/asyncio/test_session_py3k.py index 48c5e60ab..b34578dcc 100644 --- a/test/ext/asyncio/test_session_py3k.py +++ b/test/ext/asyncio/test_session_py3k.py @@ -20,6 +20,7 @@ from sqlalchemy.orm import selectinload from sqlalchemy.orm import Session from sqlalchemy.orm import sessionmaker from sqlalchemy.testing import async_test +from sqlalchemy.testing import config from sqlalchemy.testing import engines from sqlalchemy.testing import eq_ from sqlalchemy.testing import expect_raises_message @@ -28,6 +29,7 @@ from sqlalchemy.testing import is_true from sqlalchemy.testing import mock from sqlalchemy.testing.assertions import expect_deprecated from sqlalchemy.testing.assertions import is_false +from sqlalchemy.testing.provision import normalize_sequence from .test_engine_py3k import AsyncFixture as _AsyncFixture from ...orm import _fixtures @@ -76,7 +78,9 @@ class AsyncSessionTest(AsyncFixture): async def test_sequence_execute( self, async_session: AsyncSession, metadata, use_scalar ): - seq = Sequence("some_sequence", metadata=metadata) + seq = normalize_sequence( + config, Sequence("some_sequence", metadata=metadata) + ) sync_connection = (await async_session.connection()).sync_connection diff --git a/test/orm/inheritance/test_assorted_poly.py b/test/orm/inheritance/test_assorted_poly.py index c11dd1bf7..3096984f1 100644 --- a/test/orm/inheritance/test_assorted_poly.py +++ b/test/orm/inheritance/test_assorted_poly.py @@ -27,10 +27,12 @@ from sqlalchemy.orm import with_polymorphic from sqlalchemy.orm.interfaces import MANYTOONE from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import AssertsExecutionResults +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing.fixtures import ComparableEntity from sqlalchemy.testing.fixtures import fixture_session +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table @@ -48,7 +50,9 @@ class RelationshipTest1(fixtures.MappedTest): Column( "person_id", Integer, - Sequence("person_id_seq", optional=True), + normalize_sequence( + config, Sequence("person_id_seq", optional=True) + ), primary_key=True, ), Column( diff --git a/test/orm/inheritance/test_manytomany.py b/test/orm/inheritance/test_manytomany.py index a1305ac43..9a3556bfa 100644 --- a/test/orm/inheritance/test_manytomany.py +++ b/test/orm/inheritance/test_manytomany.py @@ -6,9 +6,11 @@ from sqlalchemy import String from sqlalchemy import Table from sqlalchemy.orm import class_mapper from sqlalchemy.orm import relationship +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing.fixtures import fixture_session +from sqlalchemy.testing.provision import normalize_sequence class InheritTest(fixtures.MappedTest): @@ -27,7 +29,9 @@ class InheritTest(fixtures.MappedTest): Column( "principal_id", Integer, - Sequence("principal_id_seq", optional=False), + normalize_sequence( + config, Sequence("principal_id_seq", optional=False) + ), primary_key=True, ), Column("name", String(50), nullable=False), @@ -131,7 +135,9 @@ class InheritTest2(fixtures.MappedTest): Column( "id", Integer, - Sequence("foo_id_seq", optional=True), + normalize_sequence( + config, Sequence("foo_id_seq", optional=True) + ), primary_key=True, ), Column("data", String(20)), @@ -239,7 +245,7 @@ class InheritTest3(fixtures.MappedTest): Column( "id", Integer, - Sequence("foo_seq", optional=True), + normalize_sequence(config, Sequence("foo_seq", optional=True)), primary_key=True, ), Column("data", String(20)), diff --git a/test/orm/test_generative.py b/test/orm/test_generative.py index dd8a4fb4b..7c9876aec 100644 --- a/test/orm/test_generative.py +++ b/test/orm/test_generative.py @@ -4,9 +4,11 @@ from sqlalchemy import func from sqlalchemy import Integer from sqlalchemy import testing from sqlalchemy.orm import relationship +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing.fixtures import fixture_session +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table from test.orm import _fixtures @@ -21,7 +23,12 @@ class GenerativeQueryTest(fixtures.MappedTest): Table( "foo", metadata, - Column("id", Integer, sa.Sequence("foo_id_seq"), primary_key=True), + Column( + "id", + Integer, + normalize_sequence(config, sa.Sequence("foo_id_seq")), + primary_key=True, + ), Column("bar", Integer), Column("range", Integer), ) diff --git a/test/orm/test_session.py b/test/orm/test_session.py index 520ff48dc..d13c29ea6 100644 --- a/test/orm/test_session.py +++ b/test/orm/test_session.py @@ -42,6 +42,7 @@ from sqlalchemy.testing import is_true from sqlalchemy.testing import mock from sqlalchemy.testing import pickleable from sqlalchemy.testing.fixtures import fixture_session +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table from sqlalchemy.testing.util import gc_collect @@ -61,7 +62,9 @@ class ExecutionTest(_fixtures.FixtureTest): def test_sequence_execute( self, connection, metadata, add_do_orm_execute_event, use_scalar ): - seq = Sequence("some_sequence", metadata=metadata) + seq = normalize_sequence( + config, Sequence("some_sequence", metadata=metadata) + ) metadata.create_all(connection) sess = Session(connection) diff --git a/test/orm/test_unitofwork.py b/test/orm/test_unitofwork.py index fc452dc9c..f9eff448b 100644 --- a/test/orm/test_unitofwork.py +++ b/test/orm/test_unitofwork.py @@ -23,6 +23,7 @@ from sqlalchemy.orm import Session from sqlalchemy.orm.persistence import _sort_states from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_true @@ -31,6 +32,7 @@ from sqlalchemy.testing.assertsql import AllOf from sqlalchemy.testing.assertsql import CompiledSQL from sqlalchemy.testing.assertsql import Conditional from sqlalchemy.testing.fixtures import fixture_session +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table from sqlalchemy.util import OrderedDict @@ -1110,7 +1112,7 @@ class DefaultTest(fixtures.MappedTest): Column( "secondary_id", Integer, - sa.Sequence("sec_id_seq"), + normalize_sequence(config, sa.Sequence("sec_id_seq")), unique=True, ) ) diff --git a/test/orm/test_unitofworkv2.py b/test/orm/test_unitofworkv2.py index 855b44e81..0fac7ccda 100644 --- a/test/orm/test_unitofworkv2.py +++ b/test/orm/test_unitofworkv2.py @@ -38,6 +38,7 @@ from sqlalchemy.testing.assertsql import AllOf from sqlalchemy.testing.assertsql import CompiledSQL from sqlalchemy.testing.assertsql import Conditional from sqlalchemy.testing.fixtures import fixture_session +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table from test.orm import _fixtures @@ -3446,7 +3447,7 @@ class ORMOnlyPrimaryKeyTest(fixtures.TestBase): @testing.requires.insert_returning def test_b(self, base, run_test): - seq = Sequence("x_seq") + seq = normalize_sequence(config, Sequence("x_seq")) class A(base): __tablename__ = "a" diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 1d3d17326..97b1b9124 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -5452,7 +5452,7 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( schema.CreateSequence(s1), - "CREATE SEQUENCE __[SCHEMA__none].s1 START WITH 1", + "CREATE SEQUENCE __[SCHEMA__none].s1", schema_translate_map=schema_translate_map, ) @@ -5465,7 +5465,7 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( schema.CreateSequence(s2), - "CREATE SEQUENCE __[SCHEMA_foo].s2 START WITH 1", + "CREATE SEQUENCE __[SCHEMA_foo].s2", schema_translate_map=schema_translate_map, ) @@ -5478,7 +5478,7 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( schema.CreateSequence(s3), - "CREATE SEQUENCE __[SCHEMA_bar].s3 START WITH 1", + "CREATE SEQUENCE __[SCHEMA_bar].s3", schema_translate_map=schema_translate_map, ) diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py index 08911a6c5..1249529f3 100644 --- a/test/sql/test_defaults.py +++ b/test/sql/test_defaults.py @@ -19,11 +19,13 @@ from sqlalchemy.sql import select from sqlalchemy.sql import text from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import expect_warnings from sqlalchemy.testing import fixtures from sqlalchemy.testing import mock from sqlalchemy.testing.assertions import expect_deprecated +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table from sqlalchemy.types import TypeDecorator @@ -1030,7 +1032,9 @@ class PKIncrementTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("ai_id_seq", optional=True), + normalize_sequence( + config, Sequence("ai_id_seq", optional=True) + ), primary_key=True, ), Column("int1", Integer), @@ -1171,7 +1175,7 @@ class AutoIncrementTest(fixtures.TestBase): # and autoincrement=False. Using a ForeignKey # would have the same effect - some_seq = Sequence("some_seq") + some_seq = normalize_sequence(config, Sequence("some_seq")) dataset_no_autoinc = Table( "x", @@ -1313,7 +1317,7 @@ class SpecialTypePKTest(fixtures.TestBase): self._run_test(default=literal_column("1", type_=self.MyInteger)) def test_sequence(self): - self._run_test(Sequence("foo_seq")) + self._run_test(normalize_sequence(config, Sequence("foo_seq"))) def test_text_clause_default_no_type(self): self._run_test(default=text("1")) diff --git a/test/sql/test_deprecations.py b/test/sql/test_deprecations.py index 82a54d20a..ae34b0c0f 100644 --- a/test/sql/test_deprecations.py +++ b/test/sql/test_deprecations.py @@ -32,11 +32,13 @@ from sqlalchemy.sql import visitors from sqlalchemy.sql.selectable import SelectStatementGrouping from sqlalchemy.testing import assertions from sqlalchemy.testing import AssertsCompiledSQL +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_ from sqlalchemy.testing import is_true from sqlalchemy.testing import mock +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table @@ -603,7 +605,9 @@ class PKIncrementTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("ai_id_seq", optional=True), + normalize_sequence( + config, Sequence("ai_id_seq", optional=True) + ), primary_key=True, ), Column("int1", Integer), diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index a48d42f50..f92dd8496 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -44,11 +44,13 @@ from sqlalchemy.sql.functions import GenericFunction from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_ from sqlalchemy.testing.assertions import expect_warnings from sqlalchemy.testing.engines import all_dialects +from sqlalchemy.testing.provision import normalize_sequence table1 = table( @@ -1070,7 +1072,7 @@ class ExecuteTest(fixtures.TestBase): Column( "id", Integer, - Sequence("t1idseq", optional=True), + normalize_sequence(config, Sequence("t1idseq", optional=True)), primary_key=True, ), Column("value", Integer), @@ -1081,7 +1083,7 @@ class ExecuteTest(fixtures.TestBase): Column( "id", Integer, - Sequence("t2idseq", optional=True), + normalize_sequence(config, Sequence("t2idseq", optional=True)), primary_key=True, ), Column("value", Integer, default=7), diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py index 23a850f08..395fe16d3 100644 --- a/test/sql/test_insert.py +++ b/test/sql/test_insert.py @@ -26,10 +26,12 @@ from sqlalchemy.sql import crud from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import expect_raises_message from sqlalchemy.testing import expect_warnings from sqlalchemy.testing import fixtures +from sqlalchemy.testing.provision import normalize_sequence class ORMExpr: @@ -143,7 +145,13 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): ) def test_insert_literal_binds_sequence_notimplemented(self): - table = Table("x", MetaData(), Column("y", Integer, Sequence("y_seq"))) + table = Table( + "x", + MetaData(), + Column( + "y", Integer, normalize_sequence(config, Sequence("y_seq")) + ), + ) dialect = default.DefaultDialect() dialect.supports_sequences = True @@ -407,7 +415,12 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): t1 = Table( "t", m, - Column("id", Integer, Sequence("id_seq"), primary_key=True), + Column( + "id", + Integer, + normalize_sequence(config, Sequence("id_seq")), + primary_key=True, + ), Column("data", String), ) @@ -428,7 +441,12 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): t1 = Table( "t", m, - Column("id", Integer, Sequence("id_seq"), primary_key=True), + Column( + "id", + Integer, + normalize_sequence(config, Sequence("id_seq")), + primary_key=True, + ), Column("data", String), ) @@ -453,7 +471,9 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): "t", m, Column("id", Integer, primary_key=True), - Column("counter", Sequence("counter_seq")), + Column( + "counter", normalize_sequence(config, Sequence("counter_seq")) + ), Column("data", String), ) @@ -534,7 +554,12 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): t1 = Table( "t", m, - Column("id", Integer, Sequence("id_seq"), primary_key=True), + Column( + "id", + Integer, + normalize_sequence(config, Sequence("id_seq")), + primary_key=True, + ), Column("data", String), ) diff --git a/test/sql/test_insert_exec.py b/test/sql/test_insert_exec.py index 429ebf163..d9dac75b3 100644 --- a/test/sql/test_insert_exec.py +++ b/test/sql/test_insert_exec.py @@ -17,11 +17,13 @@ from sqlalchemy import testing from sqlalchemy import VARCHAR from sqlalchemy.engine import cursor as _cursor from sqlalchemy.testing import assert_raises_message +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import expect_raises_message from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_ from sqlalchemy.testing import mock +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table @@ -268,7 +270,9 @@ class InsertExecTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("t4_id_seq", optional=True), + normalize_sequence( + config, Sequence("t4_id_seq", optional=True) + ), primary_key=True, ), Column("foo", String(30), primary_key=True), @@ -296,7 +300,7 @@ class InsertExecTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("t4_id_seq"), + normalize_sequence(config, Sequence("t4_id_seq")), primary_key=True, ), Column("foo", String(30)), @@ -471,7 +475,7 @@ class TableInsertTest(fixtures.TablesTest): Column( "id", Integer, - Sequence("t_id_seq"), + normalize_sequence(config, Sequence("t_id_seq")), primary_key=True, ), Column("data", String(50)), @@ -547,7 +551,11 @@ class TableInsertTest(fixtures.TablesTest): self._test( connection, t.insert().values( - id=func.next_value(Sequence("t_id_seq")), data="data", x=5 + id=func.next_value( + normalize_sequence(config, Sequence("t_id_seq")) + ), + data="data", + x=5, ), (testing.db.dialect.default_sequence_base, "data", 5), ) diff --git a/test/sql/test_returning.py b/test/sql/test_returning.py index c26f825c2..32d4c7740 100644 --- a/test/sql/test_returning.py +++ b/test/sql/test_returning.py @@ -591,7 +591,7 @@ class SequenceReturningTest(fixtures.TablesTest): @classmethod def define_tables(cls, metadata): - seq = Sequence("tid_seq") + seq = provision.normalize_sequence(config, Sequence("tid_seq")) Table( "returning_tbl", metadata, diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py index 72c8dc28d..05847b1d0 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -50,6 +50,7 @@ from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import AssertsExecutionResults +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing import in_ @@ -57,6 +58,7 @@ from sqlalchemy.testing import is_ from sqlalchemy.testing import is_not from sqlalchemy.testing import ne_ from sqlalchemy.testing.assertions import expect_raises_message +from sqlalchemy.testing.provision import normalize_sequence metadata = MetaData() @@ -2705,7 +2707,9 @@ class ReduceTest(fixtures.TestBase, AssertsExecutionResults): Column( "person_id", Integer, - Sequence("person_id_seq", optional=True), + normalize_sequence( + config, Sequence("person_id_seq", optional=True) + ), primary_key=True, ), Column("name", String(50)), diff --git a/test/sql/test_sequences.py b/test/sql/test_sequences.py index 457aeb960..8acf030d0 100644 --- a/test/sql/test_sequences.py +++ b/test/sql/test_sequences.py @@ -10,6 +10,7 @@ from sqlalchemy.schema import CreateSequence from sqlalchemy.schema import DropSequence from sqlalchemy.sql import select from sqlalchemy.testing import assert_raises_message +from sqlalchemy.testing import config from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_false @@ -18,6 +19,7 @@ from sqlalchemy.testing.assertions import expect_deprecated from sqlalchemy.testing.assertsql import AllOf from sqlalchemy.testing.assertsql import CompiledSQL from sqlalchemy.testing.assertsql import EachOf +from sqlalchemy.testing.provision import normalize_sequence from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table @@ -26,75 +28,104 @@ class SequenceDDLTest(fixtures.TestBase, testing.AssertsCompiledSQL): __dialect__ = "default" __backend__ = True - def test_create_drop_ddl(self): - self.assert_compile( - CreateSequence(Sequence("foo_seq")), - "CREATE SEQUENCE foo_seq START WITH 1", - ) - - self.assert_compile( - CreateSequence(Sequence("foo_seq", start=5)), - "CREATE SEQUENCE foo_seq START WITH 5", - ) - - self.assert_compile( - CreateSequence(Sequence("foo_seq", increment=2)), - "CREATE SEQUENCE foo_seq INCREMENT BY 2 START WITH 1", - ) - - self.assert_compile( - CreateSequence(Sequence("foo_seq", increment=2, start=5)), - "CREATE SEQUENCE foo_seq INCREMENT BY 2 START WITH 5", - ) - - self.assert_compile( - CreateSequence( - Sequence("foo_seq", increment=2, start=0, minvalue=0) - ), - "CREATE SEQUENCE foo_seq INCREMENT BY 2 START WITH 0 MINVALUE 0", - ) - - self.assert_compile( - CreateSequence( - Sequence("foo_seq", increment=2, start=1, maxvalue=5) - ), - "CREATE SEQUENCE foo_seq INCREMENT BY 2 START WITH 1 MAXVALUE 5", - ) - - self.assert_compile( - CreateSequence( - Sequence("foo_seq", increment=2, start=1, nomaxvalue=True) - ), - "CREATE SEQUENCE foo_seq INCREMENT BY 2 START WITH 1 NO MAXVALUE", - ) - - self.assert_compile( - CreateSequence( - Sequence("foo_seq", increment=2, start=0, nominvalue=True) - ), - "CREATE SEQUENCE foo_seq INCREMENT BY 2 START WITH 0 NO MINVALUE", - ) - - self.assert_compile( - CreateSequence( - Sequence("foo_seq", start=1, maxvalue=10, cycle=True) - ), - "CREATE SEQUENCE foo_seq START WITH 1 MAXVALUE 10 CYCLE", - ) - - self.assert_compile( - CreateSequence(Sequence("foo_seq", cache=1000, order=True)), - "CREATE SEQUENCE foo_seq START WITH 1 CACHE 1000 ORDER", - ) - + @testing.combinations( + (Sequence("foo_seq"), ""), + (Sequence("foo_seq", start=5), "START WITH 5"), + (Sequence("foo_seq", increment=2), "INCREMENT BY 2"), + ( + Sequence("foo_seq", increment=2, start=5), + "INCREMENT BY 2 START WITH 5", + ), + ( + Sequence("foo_seq", increment=2, start=0, minvalue=0), + "INCREMENT BY 2 START WITH 0 MINVALUE 0", + ), + ( + Sequence("foo_seq", increment=2, start=1, maxvalue=5), + "INCREMENT BY 2 START WITH 1 MAXVALUE 5", + ), + ( + Sequence("foo_seq", increment=2, start=1, nomaxvalue=True), + "INCREMENT BY 2 START WITH 1 NO MAXVALUE", + ), + ( + Sequence("foo_seq", increment=2, start=0, nominvalue=True), + "INCREMENT BY 2 START WITH 0 NO MINVALUE", + ), + ( + Sequence("foo_seq", start=1, maxvalue=10, cycle=True), + "START WITH 1 MAXVALUE 10 CYCLE", + ), + ( + Sequence("foo_seq", cache=1000, order=True), + "CACHE 1000 ORDER", + ), + (Sequence("foo_seq", order=True), "ORDER"), + (Sequence("foo_seq", minvalue=42), "MINVALUE 42"), + (Sequence("foo_seq", minvalue=-42), "MINVALUE -42"), + ( + Sequence("foo_seq", minvalue=42, increment=2), + "INCREMENT BY 2 MINVALUE 42", + ), + ( + Sequence("foo_seq", minvalue=-42, increment=2), + "INCREMENT BY 2 MINVALUE -42", + ), + ( + Sequence("foo_seq", minvalue=42, increment=-2), + "INCREMENT BY -2 MINVALUE 42", + ), + ( + Sequence("foo_seq", minvalue=-42, increment=-2), + "INCREMENT BY -2 MINVALUE -42", + ), + (Sequence("foo_seq", maxvalue=99), "MAXVALUE 99"), + (Sequence("foo_seq", maxvalue=-99), "MAXVALUE -99"), + ( + Sequence("foo_seq", maxvalue=99, increment=2), + "INCREMENT BY 2 MAXVALUE 99", + ), + ( + Sequence("foo_seq", maxvalue=99, increment=-2), + "INCREMENT BY -2 MAXVALUE 99", + ), + ( + Sequence("foo_seq", maxvalue=-99, increment=-2), + "INCREMENT BY -2 MAXVALUE -99", + ), + ( + Sequence("foo_seq", minvalue=42, maxvalue=99), + "MINVALUE 42 MAXVALUE 99", + ), + ( + Sequence("foo_seq", minvalue=42, maxvalue=99, increment=2), + "INCREMENT BY 2 MINVALUE 42 MAXVALUE 99", + ), + ( + Sequence("foo_seq", minvalue=-42, maxvalue=-9, increment=2), + "INCREMENT BY 2 MINVALUE -42 MAXVALUE -9", + ), + ( + Sequence("foo_seq", minvalue=42, maxvalue=99, increment=-2), + "INCREMENT BY -2 MINVALUE 42 MAXVALUE 99", + ), + ( + Sequence("foo_seq", minvalue=-42, maxvalue=-9, increment=-2), + "INCREMENT BY -2 MINVALUE -42 MAXVALUE -9", + ), + ) + def test_create_ddl(self, sequence, sql): + before = sequence.start self.assert_compile( - CreateSequence(Sequence("foo_seq", order=True)), - "CREATE SEQUENCE foo_seq START WITH 1 ORDER", + CreateSequence(sequence), + ("CREATE SEQUENCE foo_seq " + sql).strip(), ) + eq_(sequence.start, before) + def test_drop_ddl(self): self.assert_compile( CreateSequence(Sequence("foo_seq"), if_not_exists=True), - "CREATE SEQUENCE IF NOT EXISTS foo_seq START WITH 1", + "CREATE SEQUENCE IF NOT EXISTS foo_seq", ) self.assert_compile( @@ -113,7 +144,7 @@ class SequenceExecTest(fixtures.TestBase): @classmethod def setup_test_class(cls): - cls.seq = Sequence("my_sequence") + cls.seq = normalize_sequence(config, Sequence("my_sequence")) cls.seq.create(testing.db) @classmethod @@ -127,12 +158,12 @@ class SequenceExecTest(fixtures.TestBase): assert ret >= testing.db.dialect.default_sequence_base def test_execute(self, connection): - s = Sequence("my_sequence") + s = normalize_sequence(config, Sequence("my_sequence")) self._assert_seq_result(connection.scalar(s)) def test_execute_deprecated(self, connection): - s = Sequence("my_sequence", optional=True) + s = normalize_sequence(config, Sequence("my_sequence", optional=True)) with expect_deprecated( r"Using the .execute\(\) method to invoke a " @@ -145,27 +176,27 @@ class SequenceExecTest(fixtures.TestBase): """test dialect executes a Sequence, returns nextval, whether or not "optional" is set""" - s = Sequence("my_sequence", optional=True) + s = normalize_sequence(config, Sequence("my_sequence", optional=True)) self._assert_seq_result(connection.scalar(s)) def test_execute_next_value(self, connection): """test func.next_value().execute()/.scalar() works with connectionless execution.""" - s = Sequence("my_sequence") + s = normalize_sequence(config, Sequence("my_sequence")) self._assert_seq_result(connection.scalar(s.next_value())) def test_execute_optional_next_value(self, connection): """test func.next_value().execute()/.scalar() works with connectionless execution.""" - s = Sequence("my_sequence", optional=True) + s = normalize_sequence(config, Sequence("my_sequence", optional=True)) self._assert_seq_result(connection.scalar(s.next_value())) def test_func_embedded_select(self, connection): """test can use next_value() in select column expr""" - s = Sequence("my_sequence") + s = normalize_sequence(config, Sequence("my_sequence")) self._assert_seq_result(connection.scalar(select(s.next_value()))) @testing.requires.sequences_in_other_clauses @@ -177,7 +208,7 @@ class SequenceExecTest(fixtures.TestBase): t1 = Table("t", metadata, Column("x", Integer)) t1.create(testing.db) connection.execute(t1.insert(), [{"x": 1}, {"x": 300}, {"x": 301}]) - s = Sequence("my_sequence") + s = normalize_sequence(config, Sequence("my_sequence")) eq_( list( connection.execute(t1.select().where(t1.c.x > s.next_value())) @@ -196,7 +227,7 @@ class SequenceExecTest(fixtures.TestBase): Column("x", Integer), ) t1.create(testing.db) - s = Sequence("my_sequence") + s = normalize_sequence(config, Sequence("my_sequence")) connection.execute(t1.insert().values(x=s.next_value())) self._assert_seq_result(connection.scalar(t1.select())) @@ -212,7 +243,9 @@ class SequenceExecTest(fixtures.TestBase): Column("x", Integer, primary_key=True), implicit_returning=False, ) - s = Sequence("my_sequence_here", metadata=metadata) + s = normalize_sequence( + config, Sequence("my_sequence_here", metadata=metadata) + ) conn = connection t1.create(conn) @@ -245,7 +278,12 @@ class SequenceExecTest(fixtures.TestBase): t1 = Table( "t", metadata, - Column("x", Integer, Sequence("my_seq"), primary_key=True), + Column( + "x", + Integer, + normalize_sequence(config, Sequence("my_seq")), + primary_key=True, + ), Column("data", String(50)), implicit_returning=_implicit_returning, ) @@ -302,7 +340,12 @@ class SequenceExecTest(fixtures.TestBase): t1 = Table( "t", metadata, - Column("x", Integer, Sequence("my_seq"), primary_key=True), + Column( + "x", + Integer, + normalize_sequence(config, Sequence("my_seq")), + primary_key=True, + ), Column("data", String(50)), implicit_returning=_implicit_returning, ) @@ -339,7 +382,7 @@ class SequenceExecTest(fixtures.TestBase): """test inserted_primary_key contains the result when pk_col=next_value(), when implicit returning is used.""" - s = Sequence("my_sequence") + s = normalize_sequence(config, Sequence("my_sequence")) t1 = Table( "t", metadata, @@ -366,6 +409,7 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): (Sequence("foo_seq", increment=5),), ) def test_start_increment(self, seq): + seq = normalize_sequence(config, seq) seq.create(testing.db) try: with testing.db.connect() as conn: @@ -384,7 +428,7 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): """test next_value() used on non-sequence platform raises NotImplementedError.""" - s = Sequence("my_seq") + s = normalize_sequence(config, Sequence("my_seq")) d = sqlite.dialect() assert_raises_message( NotImplementedError, @@ -394,7 +438,7 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): ) def test_checkfirst_sequence(self, connection): - s = Sequence("my_sequence") + s = normalize_sequence(config, Sequence("my_sequence")) s.create(connection, checkfirst=False) assert self._has_sequence(connection, "my_sequence") s.create(connection, checkfirst=True) @@ -414,7 +458,7 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): def test_checkfirst_table(self, connection): m = MetaData() - s = Sequence("my_sequence") + s = normalize_sequence(config, Sequence("my_sequence")) t = Table("t", m, Column("c", Integer, s, primary_key=True)) t.create(connection, checkfirst=False) assert self._has_sequence(connection, "my_sequence") @@ -426,9 +470,9 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): @testing.provide_metadata def test_table_overrides_metadata_create(self, connection): metadata = self.metadata - Sequence("s1", metadata=metadata) - s2 = Sequence("s2", metadata=metadata) - s3 = Sequence("s3") + normalize_sequence(config, Sequence("s1", metadata=metadata)) + s2 = normalize_sequence(config, Sequence("s2", metadata=metadata)) + s3 = normalize_sequence(config, Sequence("s3")) t = Table("t", metadata, Column("c", Integer, s3, primary_key=True)) assert s3.metadata is metadata @@ -463,8 +507,8 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): Integer, autoincrement=True, primary_key=True, - default=Sequence( - "my_sequence", metadata=self.metadata + default=normalize_sequence( + config, Sequence("my_sequence", metadata=self.metadata) ).next_value(), ), ) @@ -477,7 +521,9 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): @testing.provide_metadata def test_shared_sequence(self, connection): # test case for #6071 - common_seq = Sequence("common_sequence", metadata=self.metadata) + common_seq = normalize_sequence( + config, Sequence("common_sequence", metadata=self.metadata) + ) Table( "table_1", self.metadata, @@ -511,7 +557,9 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): is_false(testing.db.dialect.has_table(connection, "table_2")) def test_next_value_type(self): - seq = Sequence("my_sequence", data_type=BigInteger) + seq = normalize_sequence( + config, Sequence("my_sequence", data_type=BigInteger) + ) assert isinstance(seq.next_value().type, BigInteger) @@ -528,7 +576,7 @@ class TableBoundSequenceTest(fixtures.TablesTest): Column( "cart_id", Integer, - Sequence("cart_id_seq"), + normalize_sequence(config, Sequence("cart_id_seq")), primary_key=True, autoincrement=False, ), @@ -544,13 +592,15 @@ class TableBoundSequenceTest(fixtures.TablesTest): Column( "obj_id", Integer, - Sequence("obj_id_seq"), + normalize_sequence(config, Sequence("obj_id_seq")), ), Column("name", String(128)), Column( "id", Integer, - Sequence("Manager_id_seq", optional=True), + normalize_sequence( + config, Sequence("Manager_id_seq", optional=True) + ), primary_key=True, ), implicit_returning=implicit_returning, @@ -647,7 +697,7 @@ class SequenceAsServerDefaultTest( def define_tables(cls, metadata): m = metadata - s = Sequence("t_seq", metadata=m) + s = normalize_sequence(config, Sequence("t_seq", metadata=m)) Table( "t_seq_test", m, @@ -655,7 +705,7 @@ class SequenceAsServerDefaultTest( Column("data", String(50)), ) - s2 = Sequence("t_seq_2", metadata=m) + s2 = normalize_sequence(config, Sequence("t_seq_2", metadata=m)) Table( "t_seq_test_2", m, |