diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-11-30 18:55:29 -0500 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2014-11-30 18:55:29 -0500 |
commit | 735889f068c58f8a278dcfe5bda7496624339026 (patch) | |
tree | fab1db301776b4e5065da60dc523819fdc27e4e4 | |
parent | 0a47420a6156cb21b5a00c3f8dabb1a809381576 (diff) | |
download | alembic-735889f068c58f8a278dcfe5bda7496624339026.tar.gz |
- Added :paramref:`~.Operations.batch_alter_table.naming_convention`
argument to :meth:`.Operations.batch_alter_table`, as this is necessary
in order to drop foreign key constraints; these are often unnamed
on the target database, and in the case that they are named, SQLAlchemy
is as of the 0.9 series not including these names yet.
- rework the docs on batch + constraints, which remains subject
to a lot of caveats and problems, some to be resolved in SQLAlchemy 1.0
-rw-r--r-- | alembic/batch.py | 8 | ||||
-rw-r--r-- | alembic/operations.py | 19 | ||||
-rw-r--r-- | docs/build/batch.rst | 120 | ||||
-rw-r--r-- | docs/build/changelog.rst | 13 | ||||
-rw-r--r-- | tests/test_batch.py | 24 |
5 files changed, 152 insertions, 32 deletions
diff --git a/alembic/batch.py b/alembic/batch.py index f5c1f31..f3ac734 100644 --- a/alembic/batch.py +++ b/alembic/batch.py @@ -9,7 +9,7 @@ from .ddl.base import _columns_for_constraint, _is_type_bound class BatchOperationsImpl(object): def __init__(self, operations, table_name, schema, recreate, copy_from, table_args, table_kwargs, - reflect_args, reflect_kwargs): + reflect_args, reflect_kwargs, naming_convention): if not util.sqla_08: raise NotImplementedError( "batch mode requires SQLAlchemy 0.8 or greater.") @@ -25,6 +25,7 @@ class BatchOperationsImpl(object): self.table_kwargs = table_kwargs self.reflect_args = reflect_args self.reflect_kwargs = reflect_kwargs + self.naming_convention = naming_convention self.batch = [] @property @@ -51,7 +52,10 @@ class BatchOperationsImpl(object): fn = getattr(self.operations.impl, opname) fn(*arg, **kw) else: - m1 = MetaData() + if self.naming_convention: + m1 = MetaData(naming_convention=self.naming_convention) + else: + m1 = MetaData() existing_table = Table( self.table_name, m1, diff --git a/alembic/operations.py b/alembic/operations.py index 4ba6fe5..6a10f38 100644 --- a/alembic/operations.py +++ b/alembic/operations.py @@ -193,7 +193,8 @@ class Operations(object): def batch_alter_table( self, table_name, schema=None, recreate="auto", copy_from=None, table_args=(), table_kwargs=util.immutabledict(), - reflect_args=(), reflect_kwargs=util.immutabledict()): + reflect_args=(), reflect_kwargs=util.immutabledict(), + naming_convention=None): """Invoke a series of per-table migrations in batch. Batch mode allows a series of operations specific to a table @@ -290,6 +291,19 @@ class Operations(object): .. versionadded:: 0.7.0 + :param naming_convention: a naming convention dictionary of the form + described at :ref:`autogen_naming_conventions` which will be applied + to the :class:`~sqlalchemy.schema.MetaData` during the reflection + process. This is typically required if one wants to drop SQLite + constraints, as these constraints will not have names when + reflected on this backend. + + .. seealso:: + + :ref:`dropping_sqlite_foreign_keys` + + .. versionadded:: 0.7.1 + .. note:: batch mode requires SQLAlchemy 0.8 or above. .. seealso:: @@ -299,7 +313,8 @@ class Operations(object): """ impl = batch.BatchOperationsImpl( self, table_name, schema, recreate, - copy_from, table_args, table_kwargs, reflect_args, reflect_kwargs) + copy_from, table_args, table_kwargs, reflect_args, + reflect_kwargs, naming_convention) batch_op = BatchOperations(self.migration_context, impl=impl) yield batch_op impl.flush() diff --git a/docs/build/batch.rst b/docs/build/batch.rst index 4c03a66..c48fe64 100644 --- a/docs/build/batch.rst +++ b/docs/build/batch.rst @@ -110,25 +110,89 @@ pre-fabricated :class:`~sqlalchemy.schema.Table` object; see added :paramref:`.Operations.batch_alter_table.reflect_args` and :paramref:`.Operations.batch_alter_table.reflect_kwargs` options. - Dealing with Constraints ------------------------ -One area of difficulty with "move and copy" is that of constraints. If -the SQLite database is enforcing referential integrity with -``PRAGMA FOREIGN KEYS``, this pragma may need to be disabled when the workflow -mode proceeds, else remote constraints which refer to this table may prevent -it from being dropped; additionally, for referential integrity to be -re-enabled, it may be necessary to recreate the -foreign keys on those remote tables to refer again to the new table (this -is definitely the case on other databases, at least). SQLite is normally used -without referential integrity enabled so this won't be a problem for most -users. +There are a variety of issues when using "batch" mode with constraints, +such as FOREIGN KEY, CHECK and UNIQUE constraints. This section +will attempt to detail many of these scenarios. + +.. _dropping_sqlite_foreign_keys: + +Dropping Unnamed or Named Foreign Key Constraints +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +SQLite, unlike any other database, allows constraints to exist in the +database that have no identifying name. On all other backends, the +target database will always generate some kind of name, if one is not +given. + +The first challenge this represents is that an unnamed constraint can't +by itself be targeted by the :meth:`.BatchOperations.drop_constraint` method. +An unnamed FOREIGN KEY constraint is implicit whenever the +:class:`~sqlalchemy.schema.ForeignKey` +or :class:`~sqlalchemy.schema.ForeignKeyConstraint` objects are used without +passing them a name. Only on SQLite will these constraints remain entirely +unnamed when they are created on the target database; an automatically generated +name will be assigned in the case of all other database backends. + +A second issue is that SQLAlchemy itself has inconsistent behavior in +dealing with SQLite constraints as far as names. Prior to version 1.0, +SQLAlchemy omits the name of foreign key constraints when reflecting them +against the SQLite backend. So even if the target application has gone through +the steps to apply names to the constraints as stated in the database, +they still aren't targetable within the batch reflection process prior +to SQLAlchemy 1.0. + +Within the scope of batch mode, this presents the issue that the +:meth:`.BatchOperations.drop_constraint` method requires a constraint name +in order to target the correct constraint. + +In order to overcome this, the :meth:`.Operations.batch_alter_table` method supports a +:paramref:`~.Operations.batch_alter_table.naming_convention` argument, so that +all reflected constraints, including foreign keys that are unnamed, or +were named but SQLAlchemy isn't loading this name, may be given a name, +as described in :ref:`autogen_naming_conventions`. Usage is as follows:: + + naming_convention = { + "fk": + "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", + } + with self.op.batch_alter_table( + "bar", naming_convention=naming_convention) as batch_op: + batch_op.drop_constraint( + "fk_bar_foo_id_foo", type_="foreignkey") + +.. versionadded:: 0.7.1 + added :paramref:`~.Operations.batch_alter_table.naming_convention` to + :meth:`.Operations.batch_alter_table`. -"Move and copy" also currently does not account for CHECK constraints, assuming -table reflection is used. If the table being recreated has any CHECK -constraints, they need to be specified explicitly, such as using -:paramref:`.Operations.batch_alter_table.table_args`:: +Including unnamed UNIQUE constraints +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +A similar, but frustratingly slightly different, issue is that in the +case of UNIQUE constraints, we again have the issue that SQLite allows +unnamed UNIQUE constraints to exist on the database, however in this case, +SQLAlchemy prior to version 1.0 doesn't reflect these constraints at all. +It does properly reflect named unique constraints with their names, however. + +So in this case, the workaround for foreign key names is still not sufficient +prior to SQLAlchemy 1.0. If our table includes unnamed unique constraints, +and we'd like them to be re-created along with the table, we need to include +them directly, which can be via the +:paramref:`~.Operations.batch_alter_table.table_args` argument:: + + with self.op.batch_alter_table( + "bar", table_args=(UniqueConstraint('username'),) + ): + batch_op.add_column(Column('foo', Integer)) + +Including CHECK constraints +^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +SQLAlchemy currently doesn't reflect CHECK constraints on any backend. +So again these must be stated explicitly if they are to be included in the +recreated table:: with op.batch_alter_table("some_table", table_args=[ CheckConstraint('x > 5') @@ -136,19 +200,19 @@ constraints, they need to be specified explicitly, such as using batch_op.add_column(Column('foo', Integer)) batch_op.drop_column('bar') -For UNIQUE constraints, SQLite unlike any other database supports the concept -of a UNIQUE constraint that has no name at all; all other backends always -assign a name of some kind to all constraints that are otherwise not named -when they are created. In SQLAlchemy, an unnamed UNIQUE constraint is -implicit when the ``unique=True`` flag is present on a -:class:`~sqlalchemy.schema.Column`, so on SQLite these constraints will -remain unnamed. - -The issue here is that SQLAlchemy until version 1.0 does not report on these -SQLite-only unnamed constraints when the table is reflected. So to support -the recreation of unnamed UNIQUE constraints, either they should be named -in the first place, or again specified within -:paramref:`.Operations.batch_alter_table.table_args`. + +Dealing with Referencing Foreign Keys +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +If the SQLite database is enforcing referential integrity with +``PRAGMA FOREIGN KEYS``, this pragma may need to be disabled when the workflow +mode proceeds, else remote constraints which refer to this table may prevent +it from being dropped; additionally, for referential integrity to be +re-enabled, it may be necessary to recreate the +foreign keys on those remote tables to refer again to the new table (this +is definitely the case on other databases, at least). SQLite is normally used +without referential integrity enabled so this won't be a problem for most +users. .. _batch_offline_mode: diff --git a/docs/build/changelog.rst b/docs/build/changelog.rst index 6d19992..7c66f58 100644 --- a/docs/build/changelog.rst +++ b/docs/build/changelog.rst @@ -17,6 +17,19 @@ Changelog Big thanks to Ann Kamyshnikova for doing the heavy lifting here. .. change:: + :tags: feature, batch + + Added :paramref:`~.Operations.batch_alter_table.naming_convention` + argument to :meth:`.Operations.batch_alter_table`, as this is necessary + in order to drop foreign key constraints; these are often unnamed + on the target database, and in the case that they are named, SQLAlchemy + is as of the 0.9 series not including these names yet. + + .. seealso:: + + :ref:`dropping_sqlite_foreign_keys` + + .. change:: :tags: bug, batch :pullreq: bitbucket:34 diff --git a/tests/test_batch.py b/tests/test_batch.py index 7f88a80..bce262c 100644 --- a/tests/test_batch.py +++ b/tests/test_batch.py @@ -12,6 +12,7 @@ from alembic.migration import MigrationContext from sqlalchemy import Integer, Table, Column, String, MetaData, ForeignKey, \ UniqueConstraint, ForeignKeyConstraint, Index, Boolean, CheckConstraint, \ Enum +from sqlalchemy.engine.reflection import Inspector from sqlalchemy.sql import column from sqlalchemy.schema import CreateTable, CreateIndex @@ -674,6 +675,29 @@ class BatchRoundTripTest(TestBase): {"id": 5, "x": 9} ]) + def test_drop_foreign_key(self): + bar = Table( + 'bar', self.metadata, + Column('id', Integer, primary_key=True), + Column('foo_id', Integer, ForeignKey('foo.id')), + mysql_engine='InnoDB' + ) + bar.create(self.conn) + self.conn.execute(bar.insert(), {'id': 1, 'foo_id': 3}) + + naming_convention = { + "fk": + "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", + } + with self.op.batch_alter_table( + "bar", naming_convention=naming_convention) as batch_op: + batch_op.drop_constraint( + "fk_bar_foo_id_foo", type_="foreignkey") + eq_( + Inspector.from_engine(self.conn).get_foreign_keys('bar'), + [] + ) + def test_drop_column_fk_recreate(self): with self.op.batch_alter_table("foo", recreate='always') as batch_op: batch_op.drop_column('data') |