Check if an index on given columns exists

We have many cases to check if an index exists on a given columns. Like:

"https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/
migrate_repo/versions/347_add_updated_at_index.py#L30-L37"

In addition, It's very frequent to get all indexes from a table, so we
need a reusable function to avoid duplicate effort.

Change-Id: Iadd9b6ac7586e9b50357ca799eb605f4772e0ece

2 files changed, 48 insertions, 12 deletions

diff --git a/oslo_db/sqlalchemy/utils.py b/oslo_db/sqlalchemy/utils.py
index 8ce7c2a..4930831 100644
--- a/oslo_db/sqlalchemy/utils.py
+++ b/oslo_db/sqlalchemy/utils.py
@@ -534,8 +534,7 @@ def _get_default_deleted_value(table):
 def _restore_indexes_on_deleted_columns(migrate_engine, table_name, indexes):
     table = get_table(migrate_engine, table_name)
 
-    insp = reflection.Inspector.from_engine(migrate_engine)
-    real_indexes = insp.get_indexes(table_name)
+    real_indexes = get_indexes(migrate_engine, table_name)
     existing_index_names = dict(
         [(index['name'], index['column_names']) for index in real_indexes])
 
@@ -559,8 +558,7 @@ def change_deleted_column_type_to_boolean(migrate_engine, table_name,
     if migrate_engine.name == "sqlite":
         return _change_deleted_column_type_to_boolean_sqlite(
             migrate_engine, table_name, **col_name_col_instance)
-    insp = reflection.Inspector.from_engine(migrate_engine)
-    indexes = insp.get_indexes(table_name)
+    indexes = get_indexes(migrate_engine, table_name)
 
     table = get_table(migrate_engine, table_name)
 
@@ -580,7 +578,6 @@ def change_deleted_column_type_to_boolean(migrate_engine, table_name,
 
 def _change_deleted_column_type_to_boolean_sqlite(migrate_engine, table_name,
                                                   **col_name_col_instance):
-    insp = reflection.Inspector.from_engine(migrate_engine)
     table = get_table(migrate_engine, table_name)
 
     columns = []
@@ -604,7 +601,7 @@ def _change_deleted_column_type_to_boolean_sqlite(migrate_engine, table_name,
     new_table.create()
 
     indexes = []
-    for index in insp.get_indexes(table_name):
+    for index in get_indexes(migrate_engine, table_name):
         column_names = [new_table.c[c] for c in index['column_names']]
         indexes.append(Index(index["name"], *column_names,
                              unique=index["unique"]))
@@ -635,8 +632,7 @@ def change_deleted_column_type_to_id_type(migrate_engine, table_name,
     if migrate_engine.name == "sqlite":
         return _change_deleted_column_type_to_id_type_sqlite(
             migrate_engine, table_name, **col_name_col_instance)
-    insp = reflection.Inspector.from_engine(migrate_engine)
-    indexes = insp.get_indexes(table_name)
+    indexes = get_indexes(migrate_engine, table_name)
 
     table = get_table(migrate_engine, table_name)
 
@@ -679,7 +675,6 @@ def _change_deleted_column_type_to_id_type_sqlite(migrate_engine, table_name,
     #                 2) Copy all data from old to new table.
     #                 3) Drop old table.
     #                 4) Rename new table to old table name.
-    insp = reflection.Inspector.from_engine(migrate_engine)
     meta = MetaData(bind=migrate_engine)
     table = Table(table_name, meta, autoload=True)
     default_deleted_value = _get_default_deleted_value(table)
@@ -708,7 +703,7 @@ def _change_deleted_column_type_to_id_type_sqlite(migrate_engine, table_name,
     new_table.create()
 
     indexes = []
-    for index in insp.get_indexes(table_name):
+    for index in get_indexes(migrate_engine, table_name):
         column_names = [new_table.c[c] for c in index['column_names']]
         indexes.append(Index(index["name"], *column_names,
                              unique=index["unique"]))
@@ -797,6 +792,18 @@ def get_db_connection_info(conn_pieces):
     return (user, password, database, host)
 
 
+def get_indexes(engine, table_name):
+    """Get all index list from a given table.
+
+    :param engine: sqlalchemy engine
+    :param table_name: name of the table
+    """
+
+    inspector = reflection.Inspector.from_engine(engine)
+    indexes = inspector.get_indexes(table_name)
+    return indexes
+
+
 def index_exists(migrate_engine, table_name, index_name):
     """Check if given index exists.
 
@@ -804,12 +811,26 @@ def index_exists(migrate_engine, table_name, index_name):
     :param table_name:     name of the table
     :param index_name:     name of the index
     """
-    inspector = reflection.Inspector.from_engine(migrate_engine)
-    indexes = inspector.get_indexes(table_name)
+    indexes = get_indexes(migrate_engine, table_name)
     index_names = [index['name'] for index in indexes]
     return index_name in index_names
 
 
+def index_exists_on_columns(engine, table_name, columns):
+    """Check if an index on given columns exists.
+
+    :param engine: sqlalchemy engine
+    :param table_name: name of the table
+    :param columns: a list type of columns that will be checked
+    """
+    if not isinstance(columns, list):
+        columns = list(columns)
+    for index in get_indexes(engine, table_name):
+        if index['column_names'] == columns:
+            return True
+    return False
+
+
 def add_index(migrate_engine, table_name, index_name, idx_columns):
     """Create an index for given columns.
 
diff --git a/oslo_db/tests/sqlalchemy/test_utils.py b/oslo_db/tests/sqlalchemy/test_utils.py
index 906321c..f0ace00 100644
--- a/oslo_db/tests/sqlalchemy/test_utils.py
+++ b/oslo_db/tests/sqlalchemy/test_utils.py
@@ -1071,6 +1071,14 @@ class TestUtils(db_test_base.DbTestCase):
         self.test_table.create()
         self.addCleanup(meta.drop_all)
 
+    def test_get_indexes(self):
+        Index('index_a', self.test_table.c.a).create(self.engine)
+        Index('index_b', self.test_table.c.b).create(self.engine)
+        indexes = utils.get_indexes(self.engine, "test_table")
+        indexes = [(index['name'], index['column_names']) for index in indexes]
+        self.assertIn(('index_a', ['a']), indexes)
+        self.assertIn(('index_b', ['b']), indexes)
+
     def test_index_exists(self):
         self.assertFalse(utils.index_exists(self.engine, 'test_table',
                                             'new_index'))
@@ -1078,6 +1086,13 @@ class TestUtils(db_test_base.DbTestCase):
         self.assertTrue(utils.index_exists(self.engine, 'test_table',
                                            'new_index'))
 
+    def test_index_exists_on_columns(self):
+        columns = [self.test_table.c.a, self.test_table.c.b]
+        Index('new_index', *columns).create(self.engine)
+        self.assertTrue(utils.index_exists_on_columns(self.engine,
+                                                      'test_table',
+                                                      ('a', 'b')))
+
     def test_add_index(self):
         self.assertFalse(utils.index_exists(self.engine, 'test_table',
                                             'new_index'))