diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-03-25 19:16:15 -0400 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-03-25 19:16:15 -0400 |
commit | efecc6c178d7b62f3d5ee35f2bf51ab79d310cdd (patch) | |
tree | 4834b10f7c3437671482e8449965c796bebc23b6 | |
parent | b2978071990407cf6593042beda619972c62ea87 (diff) | |
download | alembic-efecc6c178d7b62f3d5ee35f2bf51ab79d310cdd.tar.gz |
- Added quoting to the table name when the special EXEC is run to
drop any existing server defaults or constraints when the
:paramref:`.drop_column.mssql_drop_check` or
:paramref:`.drop_column.mssql_drop_default`
arguments are used. fix #186
-rw-r--r-- | alembic/ddl/mssql.py | 41 | ||||
-rw-r--r-- | docs/build/changelog.rst | 10 | ||||
-rw-r--r-- | tests/test_mssql.py | 6 |
3 files changed, 46 insertions, 11 deletions
diff --git a/alembic/ddl/mssql.py b/alembic/ddl/mssql.py index 58b57cf..7bfe293 100644 --- a/alembic/ddl/mssql.py +++ b/alembic/ddl/mssql.py @@ -5,6 +5,7 @@ from .impl import DefaultImpl from .base import alter_table, AddColumn, ColumnName, \ format_table_name, format_column_name, ColumnNullable, alter_column,\ format_server_default,ColumnDefault, format_type, ColumnType +from sqlalchemy.sql.expression import ClauseElement, Executable class MSSQLImpl(DefaultImpl): __dialect__ = 'mssql' @@ -70,7 +71,7 @@ class MSSQLImpl(DefaultImpl): if existing_server_default is not False or \ server_default is None: self._exec( - _exec_drop_col_constraint(self, + _ExecDropConstraint( table_name, column_name, 'sys.default_constraints') ) @@ -105,48 +106,66 @@ class MSSQLImpl(DefaultImpl): drop_default = kw.pop('mssql_drop_default', False) if drop_default: self._exec( - _exec_drop_col_constraint(self, + _ExecDropConstraint( table_name, column, 'sys.default_constraints') ) drop_check = kw.pop('mssql_drop_check', False) if drop_check: self._exec( - _exec_drop_col_constraint(self, + _ExecDropConstraint( table_name, column, 'sys.check_constraints') ) drop_fks = kw.pop('mssql_drop_foreign_key', False) if drop_fks: self._exec( - _exec_drop_col_fk_constraint(self, - table_name, column) + _ExecDropFKConstraint(table_name, column) ) super(MSSQLImpl, self).drop_column(table_name, column) -def _exec_drop_col_constraint(impl, tname, colname, type_): +class _ExecDropConstraint(ClauseElement, Executable): + def __init__(self, tname, colname, type_): + self.tname = tname + self.colname = colname + self.type_ = type_ + +class _ExecDropFKConstraint(ClauseElement, Executable): + def __init__(self, tname, colname): + self.tname = tname + self.colname = colname + + +@compiles(_ExecDropConstraint, 'mssql') +def _exec_drop_col_constraint(element, compiler, **kw): + tname, colname, type_ = element.tname, element.colname, element.type_ # from http://www.mssqltips.com/sqlservertip/1425/working-with-default-constraints-in-sql-server/ # TODO: needs table formatting, etc. return """declare @const_name varchar(256) select @const_name = [name] from %(type)s where parent_object_id = object_id('%(tname)s') and col_name(parent_object_id, parent_column_id) = '%(colname)s' -exec('alter table %(tname)s drop constraint ' + @const_name)""" % { +exec('alter table %(tname_quoted)s drop constraint ' + @const_name)""" % { 'type': type_, 'tname': tname, - 'colname': colname + 'colname': colname, + 'tname_quoted': format_table_name(compiler, tname, None), } -def _exec_drop_col_fk_constraint(impl, tname, colname): +@compiles(_ExecDropFKConstraint, 'mssql') +def _exec_drop_col_fk_constraint(element, compiler, **kw): + tname, colname = element.tname, element.colname + return """declare @const_name varchar(256) select @const_name = [name] from sys.foreign_keys fk join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id where fkc.parent_object_id = object_id('%(tname)s') and col_name(fkc.parent_object_id, fkc.parent_column_id) = '%(colname)s' -exec('alter table %(tname)s drop constraint ' + @const_name)""" % { +exec('alter table %(tname_quoted)s drop constraint ' + @const_name)""" % { 'tname': tname, - 'colname': colname + 'colname': colname, + 'tname_quoted': format_table_name(compiler, tname, None), } diff --git a/docs/build/changelog.rst b/docs/build/changelog.rst index 8ba2855..a2ab35a 100644 --- a/docs/build/changelog.rst +++ b/docs/build/changelog.rst @@ -6,6 +6,16 @@ Changelog :version: 0.6.4 .. change:: + :tags: bug, mssql + :tickets: 186 + + Added quoting to the table name when the special EXEC is run to + drop any existing server defaults or constraints when the + :paramref:`.drop_column.mssql_drop_check` or + :paramref:`.drop_column.mssql_drop_default` + arguments are used. + + .. change:: :tags: bug, mysql :tickets: 103 diff --git a/tests/test_mssql.py b/tests/test_mssql.py index d87f62a..5fa6c4b 100644 --- a/tests/test_mssql.py +++ b/tests/test_mssql.py @@ -113,6 +113,12 @@ class OpTest(TestCase): context.assert_contains("exec('alter table t1 drop constraint ' + @const_name)") context.assert_contains("ALTER TABLE t1 DROP COLUMN c1") + def test_drop_column_w_check_quoting(self): + context = op_fixture('mssql') + op.drop_column('table', 'column', mssql_drop_check=True) + context.assert_contains("exec('alter table [table] drop constraint ' + @const_name)") + context.assert_contains("ALTER TABLE [table] DROP COLUMN [column]") + def test_alter_column_nullable_w_existing_type(self): context = op_fixture('mssql') op.alter_column("t", "c", nullable=True, existing_type=Integer) |