Fixed database migration script issues

The db downgrade scripts are currently not dropping foreign key
constraints, causing errors when the script tries to drop related
tables on downgrade.

This commit address issues in the migration scripts, and also
introduces a new test script to test the migration scripts, so that
issues can be prevented in the future. The new test script is based
on the existing migration test script implementated in Nova.

Change-Id: I240d81afc3e43fd3711de8c156cfb43fd14850bf
Closes-Bug: #1347114

6 files changed, 97 insertions, 36 deletions

diff --git a/trove/db/sqlalchemy/migrate_repo/versions/016_add_datastore_type.py b/trove/db/sqlalchemy/migrate_repo/versions/016_add_datastore_type.py
index a2a633a1..4f90e8f0 100644
--- a/trove/db/sqlalchemy/migrate_repo/versions/016_add_datastore_type.py
+++ b/trove/db/sqlalchemy/migrate_repo/versions/016_add_datastore_type.py
@@ -22,6 +22,7 @@ from trove.db.sqlalchemy.migrate_repo.schema import create_tables
 from trove.db.sqlalchemy.migrate_repo.schema import drop_tables
 from trove.db.sqlalchemy.migrate_repo.schema import String
 from trove.db.sqlalchemy.migrate_repo.schema import Table
+from trove.db.sqlalchemy import utils as db_utils
 
 
 meta = MetaData()
@@ -65,9 +66,19 @@ def upgrade(migrate_engine):
 
 def downgrade(migrate_engine):
     meta.bind = migrate_engine
-    drop_tables([datastores, datastore_versions])
     instances = Table('instances', meta, autoload=True)
+    constraint_names = db_utils.get_foreign_key_constraint_names(
+        engine=migrate_engine,
+        table='instances',
+        columns=['datastore_version_id'],
+        ref_table='datastore_versions',
+        ref_columns=['id'])
+    db_utils.drop_foreign_key_constraints(
+        constraint_names=constraint_names,
+        columns=[instances.c.datastore_version_id],
+        ref_columns=[datastore_versions.c.id])
     instances.drop_column('datastore_version_id')
     service_type = Column('service_type', String(36))
     instances.create_column(service_type)
     instances.update().values({'service_type': 'mysql'}).execute()
+    drop_tables([datastore_versions, datastores])
diff --git a/trove/db/sqlalchemy/migrate_repo/versions/020_configurations.py b/trove/db/sqlalchemy/migrate_repo/versions/020_configurations.py
index 8942a573..279180d9 100644
--- a/trove/db/sqlalchemy/migrate_repo/versions/020_configurations.py
+++ b/trove/db/sqlalchemy/migrate_repo/versions/020_configurations.py
@@ -14,17 +14,19 @@
 #    under the License.
 
 from sqlalchemy import ForeignKey
-from sqlalchemy.exc import OperationalError
 from sqlalchemy.schema import Column
 from sqlalchemy.schema import MetaData
 
 from trove.db.sqlalchemy.migrate_repo.schema import create_tables
+from trove.db.sqlalchemy.migrate_repo.schema import drop_tables
 from trove.db.sqlalchemy.migrate_repo.schema import DateTime
 from trove.db.sqlalchemy.migrate_repo.schema import Boolean
 from trove.db.sqlalchemy.migrate_repo.schema import String
 from trove.db.sqlalchemy.migrate_repo.schema import Table
+from trove.db.sqlalchemy import utils as db_utils
 from trove.openstack.common import log as logging
 
+
 logger = logging.getLogger('trove.db.sqlalchemy.migrate_repo.schema')
 
 meta = MetaData()
@@ -55,22 +57,25 @@ configuration_parameters = Table(
 
 def upgrade(migrate_engine):
     meta.bind = migrate_engine
-
-    # since the downgrade is a no-op, an upgrade after a downgrade will
-    # cause an exception because the tables already exist
-    # we will catch that case and log an info message
-    try:
-        create_tables([configurations])
-        create_tables([configuration_parameters])
-
-        instances = Table('instances', meta, autoload=True)
-        instances.create_column(Column('configuration_id', String(36),
-                                       ForeignKey("configurations.id")))
-    except OperationalError as e:
-        logger.info(e)
+    create_tables([configurations])
+    create_tables([configuration_parameters])
+    instances = Table('instances', meta, autoload=True)
+    instances.create_column(Column('configuration_id', String(36),
+                                   ForeignKey("configurations.id")))
 
 
 def downgrade(migrate_engine):
     meta.bind = migrate_engine
-    # Not dropping the tables for concern if rollback needed would cause
-    # consumers to recreate configurations.
+    instances = Table('instances', meta, autoload=True)
+    constraint_names = db_utils.get_foreign_key_constraint_names(
+        engine=migrate_engine,
+        table='instances',
+        columns=['configuration_id'],
+        ref_table='configurations',
+        ref_columns=['id'])
+    db_utils.drop_foreign_key_constraints(
+        constraint_names=constraint_names,
+        columns=[instances.c.configuration_id],
+        ref_columns=[configurations.c.id])
+    instances.drop_column('configuration_id')
+    drop_tables([configuration_parameters, configurations])
diff --git a/trove/db/sqlalchemy/migrate_repo/versions/027_add_datastore_capabilities.py b/trove/db/sqlalchemy/migrate_repo/versions/027_add_datastore_capabilities.py
index 082e8852..9f964253 100644
--- a/trove/db/sqlalchemy/migrate_repo/versions/027_add_datastore_capabilities.py
+++ b/trove/db/sqlalchemy/migrate_repo/versions/027_add_datastore_capabilities.py
@@ -58,4 +58,4 @@ def upgrade(migrate_engine):
 
 def downgrade(migrate_engine):
     meta.bind = migrate_engine
-    drop_tables([capabilities, capability_overrides])
+    drop_tables([capability_overrides, capabilities])
diff --git a/trove/db/sqlalchemy/migrate_repo/versions/029_add_backup_datastore.py b/trove/db/sqlalchemy/migrate_repo/versions/029_add_backup_datastore.py
index dc1fcdd4..6f376471 100644
--- a/trove/db/sqlalchemy/migrate_repo/versions/029_add_backup_datastore.py
+++ b/trove/db/sqlalchemy/migrate_repo/versions/029_add_backup_datastore.py
@@ -18,6 +18,7 @@ from sqlalchemy.schema import MetaData
 
 from trove.db.sqlalchemy.migrate_repo.schema import String
 from trove.db.sqlalchemy.migrate_repo.schema import Table
+from trove.db.sqlalchemy import utils as db_utils
 
 
 def upgrade(migrate_engine):
@@ -34,4 +35,15 @@ def downgrade(migrate_engine):
     meta = MetaData()
     meta.bind = migrate_engine
     backups = Table('backups', meta, autoload=True)
+    datastore_versions = Table('datastore_versions', meta, autoload=True)
+    constraint_names = db_utils.get_foreign_key_constraint_names(
+        engine=migrate_engine,
+        table='backups',
+        columns=['datastore_version_id'],
+        ref_table='datastore_versions',
+        ref_columns=['id'])
+    db_utils.drop_foreign_key_constraints(
+        constraint_names=constraint_names,
+        columns=[backups.c.datastore_version_id],
+        ref_columns=[datastore_versions.c.id])
     backups.drop_column('datastore_version_id')
diff --git a/trove/db/sqlalchemy/migrate_repo/versions/030_add_master_slave.py b/trove/db/sqlalchemy/migrate_repo/versions/030_add_master_slave.py
index e7e692a7..2a7491ed 100644
--- a/trove/db/sqlalchemy/migrate_repo/versions/030_add_master_slave.py
+++ b/trove/db/sqlalchemy/migrate_repo/versions/030_add_master_slave.py
@@ -18,6 +18,8 @@ from sqlalchemy.schema import ForeignKey
 
 from trove.db.sqlalchemy.migrate_repo.schema import String
 from trove.db.sqlalchemy.migrate_repo.schema import Table
+from trove.db.sqlalchemy import utils as db_utils
+
 
 COLUMN_NAME = 'slave_of_id'
 
@@ -35,4 +37,14 @@ def downgrade(migrate_engine):
     meta = MetaData()
     meta.bind = migrate_engine
     instances = Table('instances', meta, autoload=True)
+    constraint_names = db_utils.get_foreign_key_constraint_names(
+        engine=migrate_engine,
+        table='instances',
+        columns=[COLUMN_NAME],
+        ref_table='instances',
+        ref_columns=['id'])
+    db_utils.drop_foreign_key_constraints(
+        constraint_names=constraint_names,
+        columns=[instances.c.slave_of_id],
+        ref_columns=[instances.c.id])
     instances.drop_column(COLUMN_NAME)
diff --git a/trove/db/sqlalchemy/migrate_repo/versions/032_clusters.py b/trove/db/sqlalchemy/migrate_repo/versions/032_clusters.py
index 8d7d7855..21c96e39 100644
--- a/trove/db/sqlalchemy/migrate_repo/versions/032_clusters.py
+++ b/trove/db/sqlalchemy/migrate_repo/versions/032_clusters.py
@@ -14,19 +14,21 @@
 #    under the License.
 
 from sqlalchemy import ForeignKey
-from sqlalchemy.exc import OperationalError
 from sqlalchemy.schema import Column
 from sqlalchemy.schema import Index
 from sqlalchemy.schema import MetaData
 
 from trove.db.sqlalchemy.migrate_repo.schema import Boolean
 from trove.db.sqlalchemy.migrate_repo.schema import create_tables
+from trove.db.sqlalchemy.migrate_repo.schema import drop_tables
 from trove.db.sqlalchemy.migrate_repo.schema import DateTime
 from trove.db.sqlalchemy.migrate_repo.schema import Integer
 from trove.db.sqlalchemy.migrate_repo.schema import String
 from trove.db.sqlalchemy.migrate_repo.schema import Table
+from trove.db.sqlalchemy import utils as db_utils
 from trove.openstack.common import log as logging
 
+
 logger = logging.getLogger('trove.db.sqlalchemy.migrate_repo.schema')
 
 meta = MetaData()
@@ -53,25 +55,44 @@ def upgrade(migrate_engine):
     Table('datastores', meta, autoload=True)
     Table('datastore_versions', meta, autoload=True)
     instances = Table('instances', meta, autoload=True)
+    create_tables([clusters])
+    instances.create_column(Column('cluster_id', String(36),
+                                   ForeignKey("clusters.id")))
+    instances.create_column(Column('shard_id', String(36)))
+    instances.create_column(Column('type', String(64)))
+    cluster_id_idx = Index("instances_cluster_id", instances.c.cluster_id)
+    cluster_id_idx.create()
 
-    # since the downgrade is a no-op, an upgrade after a downgrade will
-    # cause an exception because the tables already exist
-    # we will catch that case and log an info message
-    try:
-        create_tables([clusters])
 
-        instances.create_column(Column('cluster_id', String(36),
-                                       ForeignKey("clusters.id")))
-        instances.create_column(Column('shard_id', String(36)))
-        instances.create_column(Column('type', String(64)))
+def downgrade(migrate_engine):
+    meta.bind = migrate_engine
 
-        cluster_id_idx = Index("instances_cluster_id", instances.c.cluster_id)
-        cluster_id_idx.create()
-    except OperationalError as e:
-        logger.info(e)
+    datastore_versions = Table('datastore_versions', meta, autoload=True)
+    constraint_names = db_utils.get_foreign_key_constraint_names(
+        engine=migrate_engine,
+        table='clusters',
+        columns=['datastore_version_id'],
+        ref_table='datastore_versions',
+        ref_columns=['id'])
+    db_utils.drop_foreign_key_constraints(
+        constraint_names=constraint_names,
+        columns=[clusters.c.datastore_version_id],
+        ref_columns=[datastore_versions.c.id])
 
+    instances = Table('instances', meta, autoload=True)
+    constraint_names = db_utils.get_foreign_key_constraint_names(
+        engine=migrate_engine,
+        table='instances',
+        columns=['cluster_id'],
+        ref_table='clusters',
+        ref_columns=['id'])
+    db_utils.drop_foreign_key_constraints(
+        constraint_names=constraint_names,
+        columns=[instances.c.cluster_id],
+        ref_columns=[clusters.c.id])
 
-def downgrade(migrate_engine):
-    meta.bind = migrate_engine
-    # not dropping the table on a rollback because the cluster
-    # assets will still exist
+    instances.drop_column('cluster_id')
+    instances.drop_column('shard_id')
+    instances.drop_column('type')
+
+    drop_tables([clusters])