Merge "ModelsMigrationsSync: Add check for foreign keys"

2 files changed, 85 insertions, 2 deletions

diff --git a/oslo/db/sqlalchemy/test_migrations.py b/oslo/db/sqlalchemy/test_migrations.py
index 28b613f..c6bd146 100644
--- a/oslo/db/sqlalchemy/test_migrations.py
+++ b/oslo/db/sqlalchemy/test_migrations.py
@@ -15,6 +15,7 @@
 #    under the License.
 
 import abc
+import collections
 import logging
 import pprint
 
@@ -510,6 +511,73 @@ class ModelsMigrationsSync(object):
             for table in tbs:
                 conn.execute(schema.DropTable(table))
 
+    FKInfo = collections.namedtuple('fk_info', ['constrained_columns',
+                                                'referred_table',
+                                                'referred_columns'])
+
+    def check_foreign_keys(self, metadata, bind):
+        """Compare foreign keys between model and db table.
+
+        :returns: a list that contains information about:
+
+         * should be a new key added or removed existing,
+         * name of that key,
+         * source table,
+         * referred table,
+         * constrained columns,
+         * referred columns
+
+         Output::
+
+             [('drop_key',
+               'testtbl_fk_check_fkey',
+               'testtbl',
+               fk_info(constrained_columns=(u'fk_check',),
+                       referred_table=u'table',
+                       referred_columns=(u'fk_check',)))]
+
+        """
+
+        diff = []
+        insp = sqlalchemy.engine.reflection.Inspector.from_engine(bind)
+        # Get all tables from db
+        db_tables = insp.get_table_names()
+        # Get all tables from models
+        model_tables = metadata.tables
+        for table in db_tables:
+            if table not in model_tables:
+                continue
+            # Get all necessary information about key of current table from db
+            fk_db = dict((self._get_fk_info_from_db(i), i['name'])
+                         for i in insp.get_foreign_keys(table))
+            fk_db_set = set(fk_db.keys())
+            # Get all necessary information about key of current table from
+            # models
+            fk_models = dict((self._get_fk_info_from_model(fk), fk)
+                             for fk in model_tables[table].foreign_keys)
+            fk_models_set = set(fk_models.keys())
+            for key in (fk_db_set - fk_models_set):
+                diff.append(('drop_key', fk_db[key], table, key))
+                LOG.info(("Detected removed foreign key %(fk)r on "
+                          "table %(table)r"), {'fk': fk_db[key],
+                                               'table': table})
+            for key in (fk_models_set - fk_db_set):
+                diff.append(('add_key', fk_models[key], key))
+                LOG.info((
+                    "Detected added foreign key for column %(fk)r on table "
+                    "%(table)r"), {'fk': fk_models[key].column.name,
+                                   'table': table})
+        return diff
+
+    def _get_fk_info_from_db(self, fk):
+        return self.FKInfo(tuple(fk['constrained_columns']),
+                           fk['referred_table'],
+                           tuple(fk['referred_columns']))
+
+    def _get_fk_info_from_model(self, fk):
+        return self.FKInfo((fk.parent.name,), fk.column.table.name,
+                           (fk.column.name,))
+
     def test_models_sync(self):
         # recent versions of sqlalchemy and alembic are needed for running of
         # this test, but we already have them in requirements
@@ -534,8 +602,11 @@ class ModelsMigrationsSync(object):
             mc = alembic.migration.MigrationContext.configure(conn, opts=opts)
 
             # compare schemas and fail with diff, if it's not empty
-            diff = alembic.autogenerate.compare_metadata(mc,
-                                                         self.get_metadata())
+            diff1 = alembic.autogenerate.compare_metadata(mc,
+                                                          self.get_metadata())
+            diff2 = self.check_foreign_keys(self.get_metadata(),
+                                            self.get_engine())
+            diff = diff1 + diff2
             if diff:
                 msg = pprint.pformat(diff, indent=2, width=20)
                 self.fail(
diff --git a/tests/sqlalchemy/test_migrations.py b/tests/sqlalchemy/test_migrations.py
index abb5f8a..db6c9c8 100644
--- a/tests/sqlalchemy/test_migrations.py
+++ b/tests/sqlalchemy/test_migrations.py
@@ -194,6 +194,7 @@ class ModelsMigrationSyncMixin(test.BaseTestCase):
             sa.Column('defaulttest4', sa.Enum('first', 'second',
                                               name='testenum'),
                       server_default="first"),
+            sa.Column('fk_check', sa.String(36), nullable=False),
             sa.UniqueConstraint('spam', 'eggs', name='uniq_cons'),
         )
 
@@ -210,6 +211,7 @@ class ModelsMigrationSyncMixin(test.BaseTestCase):
             eggs = sa.Column('eggs', sa.DateTime)
             foo = sa.Column('foo', sa.Boolean,
                             server_default=sa.sql.expression.true())
+            fk_check = sa.Column('fk_check', sa.String(36), nullable=False)
             bool_wo_default = sa.Column('bool_wo_default', sa.Boolean)
             defaulttest = sa.Column('defaulttest',
                                     sa.Integer, server_default='5')
@@ -243,6 +245,12 @@ class ModelsMigrationSyncMixin(test.BaseTestCase):
     def _test_models_not_sync(self):
         self.metadata_migrations.clear()
         sa.Table(
+            'table', self.metadata_migrations,
+            sa.Column('fk_check', sa.String(36), nullable=False),
+            sa.PrimaryKeyConstraint('fk_check'),
+            mysql_engine='InnoDB'
+        )
+        sa.Table(
             'testtbl', self.metadata_migrations,
             sa.Column('id', sa.Integer, primary_key=True),
             sa.Column('spam', sa.String(8), nullable=True),
@@ -257,7 +265,10 @@ class ModelsMigrationSyncMixin(test.BaseTestCase):
             sa.Column('defaulttest4',
                       sa.Enum('first', 'second', name='testenum'),
                       server_default="first"),
+            sa.Column('fk_check', sa.String(36), nullable=False),
             sa.UniqueConstraint('spam', 'foo', name='uniq_cons'),
+            sa.ForeignKeyConstraint(['fk_check'], ['table.fk_check']),
+            mysql_engine='InnoDB'
         )
 
         msg = six.text_type(self.assertRaises(AssertionError,
@@ -276,6 +287,7 @@ class ModelsMigrationSyncMixin(test.BaseTestCase):
         self.assertIn('bool_wo_default', msg)
         self.assertIn('defaulttest', msg)
         self.assertIn('defaulttest3', msg)
+        self.assertIn('drop_key', msg)
 
 
 class ModelsMigrationsSyncMysql(ModelsMigrationSyncMixin,