From 1d40a4ae159086b9ff6452a310d4cd57ef850813 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Fri, 7 Aug 2020 23:38:02 +0200 Subject: [PATCH] Do not specify type on mssql by default Make optional sequences render as identity in mssql Remove unused dialect option sequence_default_column_type Change-Id: I821eeffcb442f8d1b69186a9b798b15c3d8d6ff3 --- lib/sqlalchemy/dialects/mssql/base.py | 34 ++++++++++++---------- lib/sqlalchemy/dialects/mysql/base.py | 2 +- lib/sqlalchemy/dialects/postgresql/base.py | 1 - lib/sqlalchemy/engine/default.py | 3 -- test/dialect/mssql/test_sequence.py | 10 +++---- test/dialect/mssql/test_types.py | 5 ++-- test/engine/test_execute.py | 7 +---- test/sql/test_defaults.py | 4 +-- test/sql/test_functions.py | 4 +-- test/sql/test_insert_exec.py | 7 +---- 10 files changed, 31 insertions(+), 46 deletions(-) diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index d18fb72990..d265993cb9 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -242,9 +242,8 @@ 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. +:class:`.Sequence` defaults to a start value of 1, even though the +T-SQL defaults is -9223372036854775808. .. versionadded:: 1.4.0 @@ -751,7 +750,6 @@ 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 @@ -1633,6 +1631,16 @@ class MSExecutionContext(default.DefaultExecutionContext): type_, ) + def get_insert_default(self, column): + if ( + isinstance(column, sa_schema.Column) + and column is column.table._autoincrement_column + and isinstance(column.default, sa_schema.Sequence) + and column.default.optional + ): + return None + return super(MSExecutionContext, self).get_insert_default(column) + class MSSQLCompiler(compiler.SQLCompiler): returning_precedes_values = True @@ -2198,11 +2206,10 @@ class MSDDLCompiler(compiler.DDLCompiler): elif ( column is column.table._autoincrement_column or column.autoincrement is True + ) and ( + not isinstance(column.default, Sequence) or column.default.optional ): - if not isinstance(column.default, Sequence): - colspec += self.process( - Identity(start=start, increment=increment) - ) + colspec += self.process(Identity(start=start, increment=increment)) else: default = self.get_column_default_string(column) if default is not None: @@ -2322,13 +2329,10 @@ class MSDDLCompiler(compiler.DDLCompiler): return text def visit_create_sequence(self, create, **kw): - + prefix = None 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) + prefix = " AS %s" % self.type_compiler.process(data_type) return super(MSDDLCompiler, self).visit_create_sequence( create, prefix=prefix, **kw ) @@ -2537,8 +2541,7 @@ class MSDialect(default.DefaultDialect): ischema_names = ischema_names supports_sequences = True - # T-SQL's actual default is BIGINT - sequence_default_column_type = INTEGER + sequences_optional = True # T-SQL's actual default is -9223372036854775808 default_sequence_base = 1 @@ -3088,7 +3091,6 @@ class MSDialect(default.DefaultDialect): RR = ischema.ref_constraints C = ischema.key_constraints.alias("C") R = ischema.key_constraints.alias("R") - # Foreign key constraints s = ( sql.select( diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 1d032b600f..670c16c2b2 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -2369,7 +2369,7 @@ class MySQLDialect(default.DefaultDialect): supports_sequences = False # default for MySQL ... # ... may be updated to True for MariaDB 10.3+ in initialize() - sequences_optional = True + sequences_optional = False supports_for_update_of = False # default for MySQL ... # ... may be updated to True for MySQL 8+ in initialize() diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 53c54ab65e..21223a1dbc 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2576,7 +2576,6 @@ 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 ec0f2ed9f0..d5d8eabf4a 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -22,7 +22,6 @@ 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 @@ -64,8 +63,6 @@ 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/test/dialect/mssql/test_sequence.py b/test/dialect/mssql/test_sequence.py index 36e2edd6dd..2b1db0b413 100644 --- a/test/dialect/mssql/test_sequence.py +++ b/test/dialect/mssql/test_sequence.py @@ -21,7 +21,9 @@ class SequenceTest(fixtures.TablesTest): Table( "int_seq_t", metadata, - Column("id", Integer, default=Sequence("int_seq")), + Column( + "id", Integer, default=Sequence("int_seq", data_type=Integer()) + ), Column("txt", String(50)), ) @@ -29,11 +31,7 @@ class SequenceTest(fixtures.TablesTest): "bigint_seq_t", metadata, Column( - "id", - BIGINT, - default=Sequence( - "bigint_seq", data_type=BIGINT, start=3000000000 - ), + "id", BIGINT, default=Sequence("bigint_seq", start=3000000000), ), Column("txt", String(50)), ) diff --git a/test/dialect/mssql/test_types.py b/test/dialect/mssql/test_types.py index 399e0ca905..6d4d3f55ca 100644 --- a/test/dialect/mssql/test_types.py +++ b/test/dialect/mssql/test_types.py @@ -561,10 +561,9 @@ class TypeRoundTripTest( metadata, Column( "id", - testing.db.dialect.sequence_default_column_type, + Integer, Sequence("numeric_id_seq", optional=True), primary_key=True, - autoincrement=False, ), Column( "numericcol", Numeric(precision=38, scale=20, asdecimal=True) @@ -652,7 +651,7 @@ class TypeRoundTripTest( metadata, Column( "id", - testing.db.dialect.sequence_default_column_type, + Integer, Sequence("numeric_id_seq", optional=True), primary_key=True, ), diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py index 5b922a97d8..417bcf5607 100644 --- a/test/engine/test_execute.py +++ b/test/engine/test_execute.py @@ -1931,12 +1931,7 @@ class EngineEventsTest(fixtures.TestBase): t = Table( "t", self.metadata, - Column( - "x", - testing.db.dialect.sequence_default_column_type, - Sequence("t_id_seq"), - primary_key=True, - ), + Column("x", Integer, Sequence("t_id_seq"), primary_key=True,), implicit_returning=False, ) self.metadata.create_all(engine) diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py index aa1c0d48d7..e23c58cec4 100644 --- a/test/sql/test_defaults.py +++ b/test/sql/test_defaults.py @@ -998,7 +998,7 @@ class PKIncrementTest(fixtures.TablesTest): metadata, Column( "id", - testing.db.dialect.sequence_default_column_type, + Integer, Sequence("ai_id_seq", optional=True), primary_key=True, ), @@ -1229,7 +1229,7 @@ class SpecialTypePKTest(fixtures.TestBase): @classmethod def setup_class(cls): class MyInteger(TypeDecorator): - impl = testing.db.dialect.sequence_default_column_type + impl = Integer def process_bind_param(self, value, dialect): if value is None: diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index e0a0dd8a53..ddb43871e2 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -993,7 +993,7 @@ class ExecuteTest(fixtures.TestBase): meta, Column( "id", - testing.db.dialect.sequence_default_column_type, + Integer, Sequence("t1idseq", optional=True), primary_key=True, ), @@ -1004,7 +1004,7 @@ class ExecuteTest(fixtures.TestBase): meta, Column( "id", - testing.db.dialect.sequence_default_column_type, + Integer, Sequence("t2idseq", optional=True), primary_key=True, ), diff --git a/test/sql/test_insert_exec.py b/test/sql/test_insert_exec.py index e27decd6f1..85471e7eb0 100644 --- a/test/sql/test_insert_exec.py +++ b/test/sql/test_insert_exec.py @@ -352,12 +352,7 @@ class TableInsertTest(fixtures.TablesTest): Table( "foo", metadata, - Column( - "id", - testing.db.dialect.sequence_default_column_type, - Sequence("t_id_seq"), - primary_key=True, - ), + Column("id", Integer, Sequence("t_id_seq"), primary_key=True,), Column("data", String(50)), Column("x", Integer), ) -- 2.47.3