Add a monkey-patching util for sqlalchemy-migrate

Nova and other projects use sqlalchemy-migrate for DB schema
migrations. Unfortunately, this project looks like to be dead,
but have some important bugs which makes lives of OpenStack
developers harder (e. g. creation of a new unique constraint
in SQLite leads to deletion of all existing unique constraints).

Nova has some workarounds for bugs and limitations of sqlalchemy-migrate,
though it would be nice to have those directly in sqlalchemy-migrate
(at least in form of a monkey-patch for now). Oslo seems to be
a good place to store this monkey-patch, so Nova and other projects
could reuse it.

This patch:
  - makes it possible to use the unified drop_unique_constraint()
    function for SQLite backend
  - fixes a bug in sqlalchemy-migrate that leads to deletion of
    existing unique constraints of a table when a new one is added
    (SQLite backend)

Blueprint: oslo-sqlalchemy-migrate-uc-fixes

Change-Id: Ifac07abac3814b3ea4dea5840b17a711f4b24b8d

3 files changed, 255 insertions, 0 deletions

diff --git a/openstack/common/db/sqlalchemy/migration.py b/openstack/common/db/sqlalchemy/migration.py
new file mode 100644
index 0000000..e643d8e
--- /dev/null
+++ b/openstack/common/db/sqlalchemy/migration.py
@@ -0,0 +1,159 @@
+# coding: utf-8
+#
+# Copyright (c) 2013 OpenStack Foundation
+# All Rights Reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License"); you may
+# not use this file except in compliance with the License. You may obtain
+# a copy of the License at
+#
+#      http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+# License for the specific language governing permissions and limitations
+# under the License.
+#
+# Base on code in migrate/changeset/databases/sqlite.py which is under
+# the following license:
+#
+# The MIT License
+#
+# Copyright (c) 2009 Evan Rosson, Jan Dittberner, Domen Kožar
+#
+# Permission is hereby granted, free of charge, to any person obtaining a copy
+# of this software and associated documentation files (the "Software"), to deal
+# in the Software without restriction, including without limitation the rights
+# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+# copies of the Software, and to permit persons to whom the Software is
+# furnished to do so, subject to the following conditions:
+# The above copyright notice and this permission notice shall be included in
+# all copies or substantial portions of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE
+
+import re
+
+from migrate.changeset import ansisql
+from migrate.changeset.databases import sqlite
+from sqlalchemy.schema import UniqueConstraint
+
+
+def _get_unique_constraints(self, table):
+    """Retrieve information about existing unique constraints of the table
+
+    This feature is needed for _recreate_table() to work properly.
+    Unfortunately, it's not available in sqlalchemy 0.7.x/0.8.x.
+
+    """
+
+    data = table.metadata.bind.execute(
+        """SELECT sql
+           FROM sqlite_master
+           WHERE
+               type='table' AND
+               name=:table_name""",
+        table_name=table.name
+    ).fetchone()[0]
+
+    UNIQUE_PATTERN = "CONSTRAINT (\w+) UNIQUE \(([^\)]+)\)"
+    return [
+        UniqueConstraint(
+            *[getattr(table.columns, c.strip(' "')) for c in cols.split(",")],
+            name=name
+        )
+        for name, cols in re.findall(UNIQUE_PATTERN, data)
+    ]
+
+
+def _recreate_table(self, table, column=None, delta=None, omit_uniques=None):
+    """Recreate the table properly
+
+    Unlike the corresponding original method of sqlalchemy-migrate this one
+    doesn't drop existing unique constraints when creating a new one.
+
+    """
+
+    table_name = self.preparer.format_table(table)
+
+    # we remove all indexes so as not to have
+    # problems during copy and re-create
+    for index in table.indexes:
+        index.drop()
+
+    # reflect existing unique constraints
+    for uc in self._get_unique_constraints(table):
+        table.append_constraint(uc)
+    # omit given unique constraints when creating a new table if required
+    table.constraints = set([
+        cons for cons in table.constraints
+        if omit_uniques is None or cons.name not in omit_uniques
+    ])
+
+    self.append('ALTER TABLE %s RENAME TO migration_tmp' % table_name)
+    self.execute()
+
+    insertion_string = self._modify_table(table, column, delta)
+
+    table.create(bind=self.connection)
+    self.append(insertion_string % {'table_name': table_name})
+    self.execute()
+    self.append('DROP TABLE migration_tmp')
+    self.execute()
+
+
+def _visit_migrate_unique_constraint(self, *p, **k):
+    """Drop the given unique constraint
+
+    The corresponding original method of sqlalchemy-migrate just
+    raises NotImplemented error
+
+    """
+
+    self.recreate_table(p[0].table, omit_uniques=[p[0].name])
+
+
+def patch_migrate():
+    """A workaround for SQLite's inability to alter things
+
+    SQLite abilities to alter tables are very limited (please read
+    http://www.sqlite.org/lang_altertable.html for more details).
+    E. g. one can't drop a column or a constraint in SQLite. The
+    workaround for this is to recreate the original table omitting
+    the corresponding constraint (or column).
+
+    sqlalchemy-migrate library has recreate_table() method that
+    implements this workaround, but it does it wrong:
+
+        - information about unique constraints of a table
+          is not retrieved. So if you have a table with one
+          unique constraint and a migration adding another one
+          you will end up with a table that has only the
+          latter unique constraint, and the former will be lost
+
+        - dropping of unique constraints is not supported at all
+
+    The proper way to fix this is to provide a pull-request to
+    sqlalchemy-migrate, but the project seems to be dead. So we
+    can go on with monkey-patching of the lib at least for now.
+
+    """
+
+    # this patch is needed to ensure that recreate_table() doesn't drop
+    # existing unique constraints of the table when creating a new one
+    helper_cls = sqlite.SQLiteHelper
+    helper_cls.recreate_table = _recreate_table
+    helper_cls._get_unique_constraints = _get_unique_constraints
+
+    # this patch is needed to be able to drop existing unique constraints
+    constraint_cls = sqlite.SQLiteConstraintDropper
+    constraint_cls.visit_migrate_unique_constraint = \
+        _visit_migrate_unique_constraint
+    constraint_cls.__bases__ = (ansisql.ANSIColumnDropper,
+                                sqlite.SQLiteConstraintGenerator)
diff --git a/requirements.txt b/requirements.txt
index 0a2d1c6..bea6557 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -11,6 +11,7 @@ anyjson>=0.3.3
 kombu>2.4.7
 argparse
 stevedore
+sqlalchemy-migrate>=0.7
 SQLAlchemy>=0.7.8,<=0.7.9
 http://tarballs.openstack.org/oslo.config/oslo.config-1.2.0a2.tar.gz#egg=oslo.config-1.2.0a2
 qpid-python
diff --git a/tests/unit/db/sqlalchemy/test_migrate.py b/tests/unit/db/sqlalchemy/test_migrate.py
new file mode 100644
index 0000000..6724b5c
--- /dev/null
+++ b/tests/unit/db/sqlalchemy/test_migrate.py
@@ -0,0 +1,95 @@
+# Copyright (c) 2013 OpenStack Foundation
+# All Rights Reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License"); you may
+# not use this file except in compliance with the License. You may obtain
+# a copy of the License at
+#
+#      http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+# License for the specific language governing permissions and limitations
+# under the License.
+
+
+from migrate.changeset.constraint import UniqueConstraint
+from migrate.changeset.databases import sqlite
+import sqlalchemy as sa
+
+from openstack.common.db.sqlalchemy import migration
+from openstack.common.db.sqlalchemy import session
+from tests.unit.db.sqlalchemy import base as test_base
+
+
+def uniques(*constraints):
+    """Make a sequence of UniqueConstraint instances easily comparable
+
+        Convert a sequence of UniqueConstraint instances into a set of
+        tuples of form (constraint_name, (constraint_columns)) so that
+        assertEquals() will be able to compare sets of unique constraints
+
+    """
+
+    return set((uc.name, tuple(uc.columns.keys())) for uc in constraints)
+
+
+class TestSqliteUniqueConstraints(test_base.DbTestCase):
+    def setUp(self):
+        super(TestSqliteUniqueConstraints, self).setUp()
+
+        migration.patch_migrate()
+
+        self.helper = sqlite.SQLiteHelper()
+
+        sa.Table(
+            'test_table',
+            sa.schema.MetaData(bind=session.get_engine()),
+            sa.Column('a', sa.Integer),
+            sa.Column('b', sa.String(10)),
+            sa.Column('c', sa.Integer),
+            sa.UniqueConstraint('a', 'b', name='unique_a_b'),
+            sa.UniqueConstraint('b', 'c', name='unique_b_c')
+        ).create()
+
+        # NOTE(rpodolyaka): it's important to use the reflected table here
+        #                   rather than original one because this is what
+        #                   we actually do in db migrations code
+        self.reflected_table = sa.Table(
+            'test_table',
+            sa.schema.MetaData(bind=session.get_engine()),
+            autoload=True
+        )
+
+    def test_get_unique_constraints(self):
+        table = self.reflected_table
+
+        existing = uniques(*self.helper._get_unique_constraints(table))
+        should_be = uniques(
+            sa.UniqueConstraint(table.c.a, table.c.b, name='unique_a_b'),
+            sa.UniqueConstraint(table.c.b, table.c.c, name='unique_b_c'),
+        )
+        self.assertEquals(should_be, existing)
+
+    def test_add_unique_constraint(self):
+        table = self.reflected_table
+        UniqueConstraint(table.c.a, table.c.c, name='unique_a_c').create()
+
+        existing = uniques(*self.helper._get_unique_constraints(table))
+        should_be = uniques(
+            sa.UniqueConstraint(table.c.a, table.c.b, name='unique_a_b'),
+            sa.UniqueConstraint(table.c.b, table.c.c, name='unique_b_c'),
+            sa.UniqueConstraint(table.c.a, table.c.c, name='unique_a_c'),
+        )
+        self.assertEquals(should_be, existing)
+
+    def test_drop_unique_constraint(self):
+        table = self.reflected_table
+        UniqueConstraint(table.c.a, table.c.b, name='unique_a_b').drop()
+
+        existing = uniques(*self.helper._get_unique_constraints(table))
+        should_be = uniques(
+            sa.UniqueConstraint(table.c.b, table.c.c, name='unique_b_c'),
+        )
+        self.assertEquals(should_be, existing)