diff options
author | int32bit <krystism@gmail.com> | 2016-11-24 20:19:06 +0800 |
---|---|---|
committer | int32bit <krystism@gmail.com> | 2016-12-01 09:11:16 +0000 |
commit | bc015d7d742e7e108c409468ef407102d3b2c16a (patch) | |
tree | 37bd5a4bb2cefe5512c77c547ca15f370e82be9e | |
parent | 34f9a3ac7a56883f8a2cd2a9a93bc42e5194bc1e (diff) | |
download | oslo-db-bc015d7d742e7e108c409468ef407102d3b2c16a.tar.gz |
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
-rw-r--r-- | oslo_db/sqlalchemy/utils.py | 45 | ||||
-rw-r--r-- | oslo_db/tests/sqlalchemy/test_utils.py | 15 |
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')) |