Add generic UC dropper

There is a lot of tables where we should add unique constraints
on upgrade and drop on downgrade, to avoid race conditions.
But there is a problem with SQLite. SQLite does not allow to drop unique constraints.
There is only one way to drop UC in SQLite:
1) create new table, with same columns and UC (but without UC that we want to drop)
2) copy data from old table
3) drop old table
4) create indexes in new table from old
5) rename new table to old table name

For other DB backends is used 'alter table'.

This patch add generic method for dropping UC.

blueprint db-unique-keys

Change-Id: I3e60578d3902559996943868abeb17b41153769b

3 files changed, 254 insertions, 7 deletions

diff --git a/nova/db/sqlalchemy/utils.py b/nova/db/sqlalchemy/utils.py
new file mode 100644
index 000000000..2faa5021f
--- /dev/null
+++ b/nova/db/sqlalchemy/utils.py
@@ -0,0 +1,117 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright (c) 2013 Boris Pavlovic (boris@pavlovic.me).
+# 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 import UniqueConstraint
+from sqlalchemy.engine import reflection
+from sqlalchemy.ext.compiler import compiles
+from sqlalchemy import MetaData, Table, Column, Index
+from sqlalchemy.sql.expression import UpdateBase
+from sqlalchemy.types import NullType
+
+from nova import exception
+
+
+class InsertFromSelect(UpdateBase):
+    def __init__(self, table, select):
+        self.table = table
+        self.select = select
+
+
+@compiles(InsertFromSelect)
+def visit_insert_from_select(element, compiler, **kw):
+    return "INSERT INTO %s %s" % (
+        compiler.process(element.table, asfrom=True),
+        compiler.process(element.select))
+
+
+def _drop_unique_constraint_in_sqlite(migrate_engine, table_name, uc_name,
+                                      **col_name_col_instance):
+    insp = reflection.Inspector.from_engine(migrate_engine)
+    meta = MetaData(bind=migrate_engine)
+
+    table = Table(table_name, meta, autoload=True)
+    columns = []
+    for column in table.columns:
+        if isinstance(column.type, NullType):
+            try:
+                new_column = col_name_col_instance.get(column.name)
+            except Exception as e:
+                msg = _("Please specify column %s in col_name_col_instance "
+                        "param. It is required because column has unsupported "
+                        "type by sqlite).")
+                raise exception.NovaException(msg % column.name)
+
+            if not isinstance(new_column, Column):
+                msg = _("col_name_col_instance param has wrong type of "
+                        "column instance for column %s It should be instance "
+                        "of sqlalchemy.Column.")
+                raise exception.NovaException(msg % column.name)
+            columns.append(new_column)
+        else:
+            columns.append(column.copy())
+
+    constraints = [constraint for constraint in table.constraints
+                    if not constraint.name == uc_name]
+
+    new_table = Table(table_name + "__tmp__", meta, *(columns + constraints))
+    new_table.create()
+
+    indexes = []
+    for index in insp.get_indexes(table_name):
+        column_names = [new_table.c[c] for c in index['column_names']]
+        indexes.append(Index(index["name"],
+                             *column_names,
+                             unique=index["unique"]))
+
+    ins = InsertFromSelect(new_table, table.select())
+    migrate_engine.execute(ins)
+    table.drop()
+
+    [index.create(migrate_engine) for index in indexes]
+    new_table.rename(table_name)
+
+
+def drop_unique_constraint(migrate_engine, table_name, uc_name, *columns,
+                           **col_name_col_instance):
+    """
+    This method drops UC from table and works for mysql, postgresql and sqlite.
+    In mysql and postgresql we are able to use "alter table" constuction. In
+    sqlite is only one way to drop UC:
+        1) Create new table with same columns, indexes and constraints
+           (except one that we want to drop).
+        2) Copy data from old table to new.
+        3) Drop old table.
+        4) Rename new table to the name of old table.
+
+    :param migrate_engine: sqlalchemy engine
+    :oaram table_name:     name of table that contains uniq constarint.
+    :param uc_name:        name of uniq constraint that will be dropped.
+    :param columns:        columns that are in uniq constarint.
+    :param col_name_col_instance:   constains pair column_name=column_instance.
+                            column_instance is instance of Column. These params
+                            are required only for columns that have unsupported
+                            types by sqlite. For example BigInteger.
+    """
+    if migrate_engine.name in ["mysql", "postgresql"]:
+        meta = MetaData()
+        meta.bind = migrate_engine
+        t = Table(table_name, meta, autoload=True)
+        uc = UniqueConstraint(*fields, table=t, name=uc_name)
+        uc.drop()
+    else:
+        _drop_unique_constraint_in_sqlite(migrate_engine, table_name, uc_name,
+                                          **col_name_col_instance)
diff --git a/nova/tests/test_migration_utils.py b/nova/tests/test_migration_utils.py
new file mode 100644
index 000000000..45b6d86d4
--- /dev/null
+++ b/nova/tests/test_migration_utils.py
@@ -0,0 +1,126 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright (c) 2013 Boris Pavlovic (boris@pavlovic.me).
+# 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 import UniqueConstraint
+from sqlalchemy import MetaData, Table, Column, Integer, BigInteger
+
+from nova.db.sqlalchemy import utils
+from nova import exception
+from nova.tests import test_migrations
+
+
+class TestMigrationUtils(test_migrations.BaseMigrationTestCase):
+    """Class for testing utils that are used in db migrations."""
+
+    def test_utils_drop_unique_constraint(self):
+        table_name = "__test_tmp_table__"
+        uc_name = 'uniq_foo'
+        values = [
+            {'id': 1, 'a': 3, 'foo': 10},
+            {'id': 2, 'a': 2, 'foo': 20},
+            {'id': 3, 'a': 1, 'foo': 30}
+        ]
+        for key, engine in self.engines.items():
+            meta = MetaData()
+            meta.bind = engine
+            test_table = Table(table_name, meta,
+                               Column('id', Integer, primary_key=True,
+                                      nullable=False),
+                               Column('a', Integer),
+                               Column('foo', Integer),
+                               UniqueConstraint('a', name='uniq_a'),
+                               UniqueConstraint('foo', name=uc_name))
+            test_table.create()
+
+            engine.execute(test_table.insert(), values)
+            # NOTE(boris-42): This method is generic UC dropper.
+            utils.drop_unique_constraint(engine, table_name, uc_name, 'foo')
+
+            s = test_table.select().order_by(test_table.c.id)
+            rows = engine.execute(s).fetchall()
+
+            for i in xrange(0, len(values)):
+                v = values[i]
+                self.assertEqual((v['id'], v['a'], v['foo']), rows[i])
+
+            # NOTE(boris-42): Update data about Table from DB.
+            meta = MetaData()
+            meta.bind = engine
+            test_table = Table(table_name, meta, autoload=True)
+            constraints = filter(lambda c: c.name == uc_name,
+                                 test_table.constraints)
+            self.assertEqual(len(constraints), 0)
+            self.assertEqual(len(test_table.constraints), 1)
+
+            test_table.drop()
+
+    def test_util_drop_unique_constraint_with_not_supported_sqlite_type(self):
+        table_name = "__test_tmp_table__"
+        uc_name = 'uniq_foo'
+        values = [
+            {'id': 1, 'a': 3, 'foo': 10},
+            {'id': 2, 'a': 2, 'foo': 20},
+            {'id': 3, 'a': 1, 'foo': 30}
+        ]
+
+        for key, engine in self.engines.items():
+            meta = MetaData()
+            meta.bind = engine
+            test_table = Table(table_name, meta,
+                               Column('id', Integer, primary_key=True,
+                                      nullable=False),
+                               Column('a', Integer),
+                               Column('foo', BigInteger, default=0),
+                               UniqueConstraint('a', name='uniq_a'),
+                               UniqueConstraint('foo', name=uc_name))
+            test_table.create()
+
+            engine.execute(test_table.insert(), values)
+            if key == "sqlite":
+                # NOTE(boris-42): Missing info about column `foo` that has
+                #                 unsupported type BigInteger.
+                self.assertRaises(exception.NovaException,
+                                  utils.drop_unique_constraint,
+                                  engine, table_name, uc_name, 'foo')
+
+                # NOTE(boris-42): Wrong type of foo instance. it should be
+                #                 instance of sqlalchemy.Column.
+                self.assertRaises(exception.NovaException,
+                                  utils.drop_unique_constraint,
+                                  engine, table_name, uc_name, 'foo',
+                                  foo=Integer())
+
+            foo = Column('foo', BigInteger, default=0)
+            utils.drop_unique_constraint(engine, table_name, uc_name, 'foo',
+                                         foo=foo)
+
+            s = test_table.select().order_by(test_table.c.id)
+            rows = engine.execute(s).fetchall()
+
+            for i in xrange(0, len(values)):
+                v = values[i]
+                self.assertEqual((v['id'], v['a'], v['foo']), rows[i])
+
+            # NOTE(boris-42): Update data about Table from DB.
+            meta = MetaData()
+            meta.bind = engine
+            test_table = Table(table_name, meta, autoload=True)
+            constraints = filter(lambda c: c.name == uc_name,
+                                 test_table.constraints)
+            self.assertEqual(len(constraints), 0)
+            self.assertEqual(len(test_table.constraints), 1)
+            test_table.drop()
diff --git a/nova/tests/test_migrations.py b/nova/tests/test_migrations.py
index 02d7462d2..4be7b83f7 100644
--- a/nova/tests/test_migrations.py
+++ b/nova/tests/test_migrations.py
@@ -116,8 +116,8 @@ def get_table(engine, name):
     return sqlalchemy.Table(name, metadata, autoload=True)
 
 
-class TestMigrations(test.TestCase):
-    """Test sqlalchemy-migrate migrations."""
+class BaseMigrationTestCase(test.TestCase):
+    """Base class fort testing migrations and migration utils."""
 
     DEFAULT_CONFIG_FILE = os.path.join(os.path.dirname(__file__),
                                        'test_migrations.conf')
@@ -130,18 +130,18 @@ class TestMigrations(test.TestCase):
                                 os.path.abspath(os.path.dirname(MIGRATE_FILE)))
 
     def setUp(self):
-        super(TestMigrations, self).setUp()
+        super(BaseMigrationTestCase, self).setUp()
 
         self.snake_walk = False
         self.test_databases = {}
 
         # Load test databases from the config file. Only do this
         # once. No need to re-run this on each test...
-        LOG.debug('config_path is %s' % TestMigrations.CONFIG_FILE_PATH)
-        if os.path.exists(TestMigrations.CONFIG_FILE_PATH):
+        LOG.debug('config_path is %s' % BaseMigrationTestCase.CONFIG_FILE_PATH)
+        if os.path.exists(BaseMigrationTestCase.CONFIG_FILE_PATH):
             cp = ConfigParser.RawConfigParser()
             try:
-                cp.read(TestMigrations.CONFIG_FILE_PATH)
+                cp.read(BaseMigrationTestCase.CONFIG_FILE_PATH)
                 defaults = cp.defaults()
                 for key, value in defaults.items():
                     self.test_databases[key] = value
@@ -165,7 +165,7 @@ class TestMigrations(test.TestCase):
         # and recreate it, which ensures that we have no side-effects
         # from the tests
         self._reset_databases()
-        super(TestMigrations, self).tearDown()
+        super(BaseMigrationTestCase, self).tearDown()
 
     def _reset_databases(self):
         def execute_cmd(cmd=None):
@@ -232,6 +232,10 @@ class TestMigrations(test.TestCase):
                 os.unsetenv('PGPASSWORD')
                 os.unsetenv('PGUSER')
 
+
+class TestMigrations(BaseMigrationTestCase):
+    """Test sqlalchemy-migrate migrations."""
+
     def test_walk_versions(self):
         """
         Walks all version scripts for each tested database, ensuring