Merge "Add generic UC dropper"

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 86a6339ff..a0c5db9c4 100644
--- a/nova/tests/test_migrations.py
+++ b/nova/tests/test_migrations.py
@@ -118,8 +118,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')
@@ -132,18 +132,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
@@ -167,7 +167,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):
@@ -234,6 +234,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