summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMike Bayer <mike_mp@zzzcomputing.com>2014-11-30 18:55:29 -0500
committerMike Bayer <mike_mp@zzzcomputing.com>2014-11-30 18:55:29 -0500
commit735889f068c58f8a278dcfe5bda7496624339026 (patch)
treefab1db301776b4e5065da60dc523819fdc27e4e4
parent0a47420a6156cb21b5a00c3f8dabb1a809381576 (diff)
downloadalembic-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.py8
-rw-r--r--alembic/operations.py19
-rw-r--r--docs/build/batch.rst120
-rw-r--r--docs/build/changelog.rst13
-rw-r--r--tests/test_batch.py24
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')