<NME> test_sql_upgrade.py
<BEF> # Copyright 2012 OpenStack Foundation
#
# 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.

"""
Test for SQL migration extensions.

To run these tests against a live database:

1. Set up a blank, live database.
2. Export database information to environment variable
   ``OS_TEST_DBAPI_ADMIN_CONNECTION``. For example::

    export OS_TEST_DBAPI_ADMIN_CONNECTION=postgresql://localhost/postgres?host=
    /var/folders/7k/pwdhb_mj2cv4zyr0kyrlzjx40000gq/T/tmpMGqN8C&port=9824

3. Run the tests using::

    tox -e py39 -- keystone.tests.unit.test_sql_upgrade

For further information, see `oslo.db documentation
<https://docs.openstack.org/oslo.db/latest/contributor/index.html#how-to-run-unit-tests>`_.

.. warning::

    Your database will be wiped.

    Do not do this against a database with valuable data as
    all data will be lost.
"""

import fixtures
from migrate.versioning import script
from oslo_db import options as db_options
from oslo_db.sqlalchemy import enginefacade
from oslo_db.sqlalchemy import test_fixtures as db_fixtures
from oslo_log import fixture as log_fixture
from oslo_log import log
import sqlalchemy.exc

from keystone.cmd import cli
from keystone.common import sql
from keystone.common.sql import upgrades
import keystone.conf
from keystone.tests import unit
from keystone.tests.unit import ksfixtures

CONF = keystone.conf.CONF

# NOTE(morganfainberg): This should be updated when each DB migration collapse
# is done to mirror the expected structure of the DB in the format of
# { <DB_TABLE_NAME>: [<COLUMN>, <COLUMN>, ...], ... }
INITIAL_TABLE_STRUCTURE = {
    'config_register': [
        'type', 'domain_id',
    ],
    'credential': [
        'id', 'user_id', 'project_id', 'type', 'extra', 'key_hash',
        'encrypted_blob',
    ],
    'endpoint': [
        'id', 'legacy_endpoint_id', 'interface', 'region_id', 'service_id',
        'url', 'enabled', 'extra',
    ],
    'group': [
        'id', 'domain_id', 'name', 'description', 'extra',
    ],
    'policy': [
        'id', 'type', 'blob', 'extra',
    ],
    'project': [
        'id', 'name', 'extra', 'description', 'enabled', 'domain_id',
        'parent_id', 'is_domain',
    ],
    'project_option': [
        'project_id', 'option_id', 'option_value',
    ],
    'project_tag': [
        'project_id', 'name',
    ],
    'role': [
        'id', 'name', 'extra', 'domain_id', 'description',
    ],
    'role_option': [
        'role_id', 'option_id', 'option_value',
    ],
    'service': [
        'id', 'type', 'extra', 'enabled',
    ],
    'token': [
        'id', 'expires', 'extra', 'valid', 'trust_id', 'user_id',
    ],
    'trust': [
        'id', 'trustor_user_id', 'trustee_user_id', 'project_id',
        'impersonation', 'deleted_at', 'expires_at', 'remaining_uses', 'extra',
        'expires_at_int', 'redelegated_trust_id', 'redelegation_count',
    ],
    'trust_role': [
        'trust_id', 'role_id',
    ],
    'user': [
        'id', 'extra', 'enabled', 'default_project_id', 'created_at',
        'last_active_at', 'domain_id',
    ],
    'user_option': [
        'user_id', 'option_id', 'option_value',
    ],
    'user_group_membership': [
        'user_id', 'group_id',
    ],
    'region': [
        'id', 'description', 'parent_region_id', 'extra',
    ],
    'assignment': [
        'type', 'actor_id', 'target_id', 'role_id', 'inherited',
    ],
    'id_mapping': [
        'public_id', 'domain_id', 'local_id', 'entity_type',
    ],
    'whitelisted_config': [
        'domain_id', 'group', 'option', 'value',
    ],
    'sensitive_config': [
        'domain_id', 'group', 'option', 'value',
    ],
    'policy_association': [
        'id', 'policy_id', 'endpoint_id', 'service_id', 'region_id',
    ],
    'identity_provider': [
        'id', 'enabled', 'description', 'domain_id', 'authorization_ttl',
    ],
    'federation_protocol': [
        'id', 'idp_id', 'mapping_id', 'remote_id_attribute',
    ],
    'mapping': [
        'id', 'rules',
    ],
    'service_provider': [
        'auth_url', 'id', 'enabled', 'description', 'sp_url',
        'relay_state_prefix',
    ],
    'idp_remote_ids': [
        'idp_id', 'remote_id',
    ],
    'consumer': [
        'id', 'description', 'secret', 'extra',
    ],
    'request_token': [
        'id', 'request_secret', 'verifier', 'authorizing_user_id',
        'requested_project_id', 'role_ids', 'consumer_id', 'expires_at',
    ],
    'access_token': [
        'id', 'access_secret', 'authorizing_user_id', 'project_id', 'role_ids',
        'consumer_id', 'expires_at',
    ],
    'revocation_event': [
        'id', 'domain_id', 'project_id', 'user_id', 'role_id', 'trust_id',
        'consumer_id', 'access_token_id', 'issued_before', 'expires_at',
        'revoked_at', 'audit_id', 'audit_chain_id',
    ],
    'project_endpoint': [
        'endpoint_id', 'project_id'
    ],
    'endpoint_group': [
        'id', 'name', 'description', 'filters',
    ],
    'project_endpoint_group': [
        'endpoint_group_id', 'project_id',
    ],
    'implied_role': [
        'prior_role_id', 'implied_role_id',
    ],
    'local_user': [
        'id', 'user_id', 'domain_id', 'name', 'failed_auth_count',
        'failed_auth_at',
    ],
    'password': [
        'id', 'local_user_id', 'created_at', 'expires_at',
        'self_service', 'password_hash', 'created_at_int', 'expires_at_int',
    ],
    'federated_user': [
        'id', 'user_id', 'idp_id', 'protocol_id', 'unique_id', 'display_name',
    ],
    'nonlocal_user': [
        'domain_id', 'name', 'user_id',
    ],
    'system_assignment': [
        'type', 'actor_id', 'target_id', 'role_id', 'inherited',
    ],
    'registered_limit': [
        'internal_id', 'id', 'service_id', 'region_id', 'resource_name',
        'default_limit', 'description',
    ],
    'limit': [
        'internal_id', 'id', 'project_id', 'resource_limit', 'description',
        'registered_limit_id', 'domain_id',
    ],
    'application_credential': [
        'internal_id', 'id', 'name', 'secret_hash', 'description', 'user_id',
        'project_id', 'expires_at', 'system', 'unrestricted',
    ],
    'application_credential_role': [
        'application_credential_id', 'role_id',
    ],
    'access_rule': [
        'id', 'service', 'path', 'method', 'external_id', 'user_id',
    ],
    'application_credential_access_rule': [
        'application_credential_id', 'access_rule_id',
    ],
    'expiring_user_group_membership': [
        'user_id', 'group_id', 'idp_id', 'last_verified',
    ],
}


class MigrateBase(
    db_fixtures.OpportunisticDBTestMixin,
):
    """Test complete orchestration between all database phases."""

    def setUp(self):
        super().setUp()

        self.useFixture(log_fixture.get_logging_handle_error_fixture())
        self.stdlog = self.useFixture(ksfixtures.StandardLogging())
        self.useFixture(ksfixtures.WarningsFixture())

        self.engine = enginefacade.writer.get_engine()
        self.sessionmaker = enginefacade.writer.get_sessionmaker()

        # NOTE(dstanek): Clear out sqlalchemy-migrate's script cache to allow
        # us to have multiple repos (expand, migrate, contract) where the
        # modules have the same name (001_awesome.py).
        self.addCleanup(script.PythonScript.clear)

        db_options.set_defaults(CONF, connection=self.engine.url)

        # Override keystone's context manager to be oslo.db's global context
        # manager.
        sql.core._TESTING_USE_GLOBAL_CONTEXT_MANAGER = True
        self.addCleanup(setattr,
                        sql.core, '_TESTING_USE_GLOBAL_CONTEXT_MANAGER', False)
        self.addCleanup(sql.cleanup)

    def expand(self):
        """Expand database schema."""
        upgrades.expand_schema(engine=self.engine)

    def contract(self):
        """Contract database schema."""
        upgrades.contract_schema(engine=self.engine)

    @property
    def metadata(self):
        """A collection of tables and their associated schemas."""
        return sqlalchemy.MetaData(self.engine)

    def load_table(self, name):
        table = sqlalchemy.Table(
            name, self.metadata, autoload_with=self.engine,
        )
        return table

    def assertTableDoesNotExist(self, table_name):
        """Assert that a given table exists cannot be selected by name."""
        # Switch to a different metadata otherwise you might still
        # detect renamed or dropped tables
        try:
            sqlalchemy.Table(
                table_name, self.metadata, autoload_with=self.engine,
            )
        except sqlalchemy.exc.NoSuchTableError:
            pass
        else:
            raise AssertionError('Table "%s" already exists' % table_name)

    def assertTableColumns(self, table_name, expected_cols):
        """Assert that the table contains the expected set of columns."""
        table = self.load_table(table_name)
        insert = this_table.insert().values(**d)
        session.execute(insert)


class SqlLegacyRepoUpgradeTests(SqlMigrateBase):
    def test_blank_db_to_start(self):
        # we advertise that `--expand` must be run first.
        log_info = self.useFixture(fixtures.FakeLogger(level=log.INFO))
        status = checker.check_db_sync_status()
        self.assertIn("keystone-manage db_sync --expand", log_info.output)
        self.assertEqual(status, 2)

        # Assert the correct message is printed when migrate is ahead of
        # contract
        self.expand()
        log_info = self.useFixture(fixtures.FakeLogger(level=log.INFO))
        status = checker.check_db_sync_status()
        self.assertIn("keystone-manage db_sync --contract", log_info.output)
        self.assertEqual(status, 4)

        # Assert the correct message gets printed when all commands are on
        # the same version
        self.contract()
        log_info = self.useFixture(fixtures.FakeLogger(level=log.INFO))
        status = checker.check_db_sync_status()
        self.assertIn("All db_sync commands are upgraded", log_info.output)
        self.assertEqual(status, 0)

    def test_upgrade_add_initial_tables(self):
        self.expand()
        for table in INITIAL_TABLE_STRUCTURE:
            self.assertTableColumns(table, INITIAL_TABLE_STRUCTURE[table])


class FullMigrationSQLite(MigrateBase, unit.TestCase):
    pass


class FullMigrationMySQL(MigrateBase, unit.TestCase):
    FIXTURE = db_fixtures.MySQLOpportunisticFixture


class FullMigrationPostgreSQL(MigrateBase, unit.TestCase):
    FIXTURE = db_fixtures.PostgresqlOpportunisticFixture
                return True
        return False

    def does_index_exist(self, table_name, index_name):
        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)
        return index_name in [idx.name for idx in table.indexes]

    def does_constraint_exist(self, table_name, constraint_name):
        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)
        return constraint_name in [con.name for con in table.constraints]
            self.assertEqual('DATETIME', str(password.c.created_at.type))
        self.assertFalse(password.c.created_at.nullable)


class MySQLOpportunisticFullMigration(FullMigration):
    FIXTURE = test_base.MySQLOpportunisticFixture

<MSG> Merge "Add revocation event indexes"
<DFF> @@ -291,6 +291,10 @@ class SqlMigrateBase(test_base.DbTestCase):
         insert = this_table.insert().values(**d)
         session.execute(insert)
 
+    def does_index_exist(self, table_name, index_name):
+        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)
+        return index_name in [idx.name for idx in table.indexes]
+
 
 class SqlLegacyRepoUpgradeTests(SqlMigrateBase):
     def test_blank_db_to_start(self):
@@ -429,10 +433,6 @@ class SqlLegacyRepoUpgradeTests(SqlMigrateBase):
                 return True
         return False
 
-    def does_index_exist(self, table_name, index_name):
-        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)
-        return index_name in [idx.name for idx in table.indexes]
-
     def does_constraint_exist(self, table_name, constraint_name):
         table = sqlalchemy.Table(table_name, self.metadata, autoload=True)
         return constraint_name in [con.name for con in table.constraints]
@@ -1784,6 +1784,38 @@ class FullMigration(SqlMigrateBase, unit.TestCase):
             self.assertEqual('DATETIME', str(password.c.created_at.type))
         self.assertFalse(password.c.created_at.nullable)
 
+    def test_migration_010_add_revocation_event_indexes(self):
+        self.expand(9)
+        self.migrate(9)
+        self.contract(9)
+        self.assertFalse(self.does_index_exist(
+            'revocation_event',
+            'ix_revocation_event_issued_before'))
+        self.assertFalse(self.does_index_exist(
+            'revocation_event',
+            'ix_revocation_event_project_id_issued_before'))
+        self.assertFalse(self.does_index_exist(
+            'revocation_event',
+            'ix_revocation_event_user_id_issued_before'))
+        self.assertFalse(self.does_index_exist(
+            'revocation_event',
+            'ix_revocation_event_audit_id_issued_before'))
+        self.expand(10)
+        self.migrate(10)
+        self.contract(10)
+        self.assertTrue(self.does_index_exist(
+            'revocation_event',
+            'ix_revocation_event_issued_before'))
+        self.assertTrue(self.does_index_exist(
+            'revocation_event',
+            'ix_revocation_event_project_id_issued_before'))
+        self.assertTrue(self.does_index_exist(
+            'revocation_event',
+            'ix_revocation_event_user_id_issued_before'))
+        self.assertTrue(self.does_index_exist(
+            'revocation_event',
+            'ix_revocation_event_audit_id_issued_before'))
+
 
 class MySQLOpportunisticFullMigration(FullMigration):
     FIXTURE = test_base.MySQLOpportunisticFixture

{'commit': 'diff --git a/keystone/common/sql/contract_repo/versions/010_contract_add_revocation_event_index.py b/keystone/common/sql/contract_repo/versions/010_contract_add_revocation_event_index.py\nnew file mode 100644\nindex 000000000..8aa15c1ef\n--- /dev/null\n+++ b/keystone/common/sql/contract_repo/versions/010_contract_add_revocation_event_index.py\n@@ -0,0 +1,15 @@\n+#    Licensed under the Apache License, Version 2.0 (the "License"); you may\n+#    not use this file except in compliance with the License. You may obtain\n+#    a copy of the License at\n+#\n+#         http://www.apache.org/licenses/LICENSE-2.0\n+#\n+#    Unless required by applicable law or agreed to in writing, software\n+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT\n+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the\n+#    License for the specific language governing permissions and limitations\n+#    under the License.\n+\n+\n+def upgrade(migrate_engine):\n+    pass\ndiff --git a/keystone/common/sql/data_migration_repo/versions/010_migrate_add_revocation_event_index.py b/keystone/common/sql/data_migration_repo/versions/010_migrate_add_revocation_event_index.py\nnew file mode 100644\nindex 000000000..8aa15c1ef\n--- /dev/null\n+++ b/keystone/common/sql/data_migration_repo/versions/010_migrate_add_revocation_event_index.py\n@@ -0,0 +1,15 @@\n+#    Licensed under the Apache License, Version 2.0 (the "License"); you may\n+#    not use this file except in compliance with the License. You may obtain\n+#    a copy of the License at\n+#\n+#         http://www.apache.org/licenses/LICENSE-2.0\n+#\n+#    Unless required by applicable law or agreed to in writing, software\n+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT\n+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the\n+#    License for the specific language governing permissions and limitations\n+#    under the License.\n+\n+\n+def upgrade(migrate_engine):\n+    pass\ndiff --git a/keystone/common/sql/expand_repo/versions/010_expand_add_revocation_event_index.py b/keystone/common/sql/expand_repo/versions/010_expand_add_revocation_event_index.py\nnew file mode 100644\nindex 000000000..1eee406f8\n--- /dev/null\n+++ b/keystone/common/sql/expand_repo/versions/010_expand_add_revocation_event_index.py\n@@ -0,0 +1,31 @@\n+# Licensed under the Apache License, Version 2.0 (the "License"); you may\n+# not use this file except in compliance with the License. You may obtain\n+# a copy of the License at\n+#\n+#      http://www.apache.org/licenses/LICENSE-2.0\n+#\n+# Unless required by applicable law or agreed to in writing, software\n+# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT\n+# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the\n+# License for the specific language governing permissions and limitations\n+# under the License.\n+\n+import sqlalchemy as sql\n+\n+\n+def upgrade(migrate_engine):\n+    meta = sql.MetaData()\n+    meta.bind = migrate_engine\n+\n+    revocation_event = sql.Table(\'revocation_event\', meta, autoload=True)\n+    sql.Index(\'ix_revocation_event_issued_before\',\n+              revocation_event.c.issued_before).create()\n+    sql.Index(\'ix_revocation_event_project_id_issued_before\',\n+              revocation_event.c.project_id,\n+              revocation_event.c.issued_before).create()\n+    sql.Index(\'ix_revocation_event_user_id_issued_before\',\n+              revocation_event.c.user_id,\n+              revocation_event.c.issued_before).create()\n+    sql.Index(\'ix_revocation_event_audit_id_issued_before\',\n+              revocation_event.c.audit_id,\n+              revocation_event.c.issued_before).create()\ndiff --git a/keystone/revoke/backends/sql.py b/keystone/revoke/backends/sql.py\nindex 5070c5178..7620e37a1 100644\n--- a/keystone/revoke/backends/sql.py\n+++ b/keystone/revoke/backends/sql.py\n@@ -31,11 +31,19 @@ class RevocationEvent(sql.ModelBase, sql.ModelDictMixin):\n     trust_id = sql.Column(sql.String(64))\n     consumer_id = sql.Column(sql.String(64))\n     access_token_id = sql.Column(sql.String(64))\n-    issued_before = sql.Column(sql.DateTime(), nullable=False)\n+    issued_before = sql.Column(sql.DateTime(), nullable=False, index=True)\n     expires_at = sql.Column(sql.DateTime())\n     revoked_at = sql.Column(sql.DateTime(), nullable=False, index=True)\n     audit_id = sql.Column(sql.String(32))\n     audit_chain_id = sql.Column(sql.String(32))\n+    __table_args__ = (\n+        sql.Index(\'ix_revocation_event_project_id_issued_before\', \'project_id\',\n+                  \'issued_before\'),\n+        sql.Index(\'ix_revocation_event_user_id_issued_before\', \'user_id\',\n+                  \'issued_before\'),\n+        sql.Index(\'ix_revocation_event_audit_id_issued_before\',\n+                  \'audit_id\', \'issued_before\'),\n+    )\n \n \n class Revoke(base.RevokeDriverBase):\ndiff --git a/keystone/tests/unit/test_sql_upgrade.py b/keystone/tests/unit/test_sql_upgrade.py\nindex 1fe1260ca..ec932499e 100644\n--- a/keystone/tests/unit/test_sql_upgrade.py\n+++ b/keystone/tests/unit/test_sql_upgrade.py\n@@ -291,6 +291,10 @@ class SqlMigrateBase(test_base.DbTestCase):\n         insert = this_table.insert().values(**d)\n         session.execute(insert)\n \n+    def does_index_exist(self, table_name, index_name):\n+        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)\n+        return index_name in [idx.name for idx in table.indexes]\n+\n \n class SqlLegacyRepoUpgradeTests(SqlMigrateBase):\n     def test_blank_db_to_start(self):\n@@ -429,10 +433,6 @@ class SqlLegacyRepoUpgradeTests(SqlMigrateBase):\n                 return True\n         return False\n \n-    def does_index_exist(self, table_name, index_name):\n-        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)\n-        return index_name in [idx.name for idx in table.indexes]\n-\n     def does_constraint_exist(self, table_name, constraint_name):\n         table = sqlalchemy.Table(table_name, self.metadata, autoload=True)\n         return constraint_name in [con.name for con in table.constraints]\n@@ -1784,6 +1784,38 @@ class FullMigration(SqlMigrateBase, unit.TestCase):\n             self.assertEqual(\'DATETIME\', str(password.c.created_at.type))\n         self.assertFalse(password.c.created_at.nullable)\n \n+    def test_migration_010_add_revocation_event_indexes(self):\n+        self.expand(9)\n+        self.migrate(9)\n+        self.contract(9)\n+        self.assertFalse(self.does_index_exist(\n+            \'revocation_event\',\n+            \'ix_revocation_event_issued_before\'))\n+        self.assertFalse(self.does_index_exist(\n+            \'revocation_event\',\n+            \'ix_revocation_event_project_id_issued_before\'))\n+        self.assertFalse(self.does_index_exist(\n+            \'revocation_event\',\n+            \'ix_revocation_event_user_id_issued_before\'))\n+        self.assertFalse(self.does_index_exist(\n+            \'revocation_event\',\n+            \'ix_revocation_event_audit_id_issued_before\'))\n+        self.expand(10)\n+        self.migrate(10)\n+        self.contract(10)\n+        self.assertTrue(self.does_index_exist(\n+            \'revocation_event\',\n+            \'ix_revocation_event_issued_before\'))\n+        self.assertTrue(self.does_index_exist(\n+            \'revocation_event\',\n+            \'ix_revocation_event_project_id_issued_before\'))\n+        self.assertTrue(self.does_index_exist(\n+            \'revocation_event\',\n+            \'ix_revocation_event_user_id_issued_before\'))\n+        self.assertTrue(self.does_index_exist(\n+            \'revocation_event\',\n+            \'ix_revocation_event_audit_id_issued_before\'))\n+\n \n class MySQLOpportunisticFullMigration(FullMigration):\n     FIXTURE = test_base.MySQLOpportunisticFixture\n', 'commit_subject': 'Merge "Add revocation event indexes"', 'repo_name': 'openstack/keystone', 'committer': {'name': 'Gerrit Code Review', 'email': '61e62b213a1a56f7695845df4fc372a10cb0a73e@openstack.org', 'time_sec': '1476387598', 'tz_offset': '0', 'date': {'seconds': '1476387598'}}, 'commit_tree': 'af1b8576beed793fabfbefc90d8981643372e0b1', 'commit_message': 'Merge "Add revocation event indexes"', 'language_name': 'Python', 'license': 'apache-2.0', 'hunks': "@@ -291,6 +291,10 @@ class SqlMigrateBase(test_base.DbTestCase):\n         insert = this_table.insert().values(**d)\n         session.execute(insert)\n \n+    def does_index_exist(self, table_name, index_name):\n+        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)\n+        return index_name in [idx.name for idx in table.indexes]\n+\n \n class SqlLegacyRepoUpgradeTests(SqlMigrateBase):\n     def test_blank_db_to_start(self):\n@@ -429,10 +433,6 @@ class SqlLegacyRepoUpgradeTests(SqlMigrateBase):\n                 return True\n         return False\n \n-    def does_index_exist(self, table_name, index_name):\n-        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)\n-        return index_name in [idx.name for idx in table.indexes]\n-\n     def does_constraint_exist(self, table_name, constraint_name):\n         table = sqlalchemy.Table(table_name, self.metadata, autoload=True)\n         return constraint_name in [con.name for con in table.constraints]\n@@ -1784,6 +1784,38 @@ class FullMigration(SqlMigrateBase, unit.TestCase):\n             self.assertEqual('DATETIME', str(password.c.created_at.type))\n         self.assertFalse(password.c.created_at.nullable)\n \n+    def test_migration_010_add_revocation_event_indexes(self):\n+        self.expand(9)\n+        self.migrate(9)\n+        self.contract(9)\n+        self.assertFalse(self.does_index_exist(\n+            'revocation_event',\n+            'ix_revocation_event_issued_before'))\n+        self.assertFalse(self.does_index_exist(\n+            'revocation_event',\n+            'ix_revocation_event_project_id_issued_before'))\n+        self.assertFalse(self.does_index_exist(\n+            'revocation_event',\n+            'ix_revocation_event_user_id_issued_before'))\n+        self.assertFalse(self.does_index_exist(\n+            'revocation_event',\n+            'ix_revocation_event_audit_id_issued_before'))\n+        self.expand(10)\n+        self.migrate(10)\n+        self.contract(10)\n+        self.assertTrue(self.does_index_exist(\n+            'revocation_event',\n+            'ix_revocation_event_issued_before'))\n+        self.assertTrue(self.does_index_exist(\n+            'revocation_event',\n+            'ix_revocation_event_project_id_issued_before'))\n+        self.assertTrue(self.does_index_exist(\n+            'revocation_event',\n+            'ix_revocation_event_user_id_issued_before'))\n+        self.assertTrue(self.does_index_exist(\n+            'revocation_event',\n+            'ix_revocation_event_audit_id_issued_before'))\n+\n \n class MySQLOpportunisticFullMigration(FullMigration):\n     FIXTURE = test_base.MySQLOpportunisticFixture\n", 'addition_count': 36, 'deletion_count': 4, 'src_file': 'a/keystone/tests/unit/test_sql_upgrade.py', 'tgt_file': 'b/keystone/tests/unit/test_sql_upgrade.py', 'file_extension': '.py', 'before_file': '# Copyright 2012 OpenStack Foundation\n#\n# Licensed under the Apache License, Version 2.0 (the "License"); you may\n# not use this file except in compliance with the License. You may obtain\n# a copy of the License at\n#\n#      http://www.apache.org/licenses/LICENSE-2.0\n#\n# Unless required by applicable law or agreed to in writing, software\n# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT\n# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the\n# License for the specific language governing permissions and limitations\n# under the License.\n\n"""\nTest for SQL migration extensions.\n\nTo run these tests against a live database:\n\n1. Set up a blank, live database.\n2. Export database information to environment variable\n   ``OS_TEST_DBAPI_ADMIN_CONNECTION``. For example::\n\n    export OS_TEST_DBAPI_ADMIN_CONNECTION=postgresql://localhost/postgres?host=\n    /var/folders/7k/pwdhb_mj2cv4zyr0kyrlzjx40000gq/T/tmpMGqN8C&port=9824\n\n3. Run the tests using::\n\n    tox -e py39 -- keystone.tests.unit.test_sql_upgrade\n\nFor further information, see `oslo.db documentation\n<https://docs.openstack.org/oslo.db/latest/contributor/index.html#how-to-run-unit-tests>`_.\n\n.. warning::\n\n    Your database will be wiped.\n\n    Do not do this against a database with valuable data as\n    all data will be lost.\n"""\n\nimport fixtures\nfrom migrate.versioning import script\nfrom oslo_db import options as db_options\nfrom oslo_db.sqlalchemy import enginefacade\nfrom oslo_db.sqlalchemy import test_fixtures as db_fixtures\nfrom oslo_log import fixture as log_fixture\nfrom oslo_log import log\nimport sqlalchemy.exc\n\nfrom keystone.cmd import cli\nfrom keystone.common import sql\nfrom keystone.common.sql import upgrades\nimport keystone.conf\nfrom keystone.tests import unit\nfrom keystone.tests.unit import ksfixtures\n\nCONF = keystone.conf.CONF\n\n# NOTE(morganfainberg): This should be updated when each DB migration collapse\n# is done to mirror the expected structure of the DB in the format of\n# { <DB_TABLE_NAME>: [<COLUMN>, <COLUMN>, ...], ... }\nINITIAL_TABLE_STRUCTURE = {\n    \'config_register\': [\n        \'type\', \'domain_id\',\n    ],\n    \'credential\': [\n        \'id\', \'user_id\', \'project_id\', \'type\', \'extra\', \'key_hash\',\n        \'encrypted_blob\',\n    ],\n    \'endpoint\': [\n        \'id\', \'legacy_endpoint_id\', \'interface\', \'region_id\', \'service_id\',\n        \'url\', \'enabled\', \'extra\',\n    ],\n    \'group\': [\n        \'id\', \'domain_id\', \'name\', \'description\', \'extra\',\n    ],\n    \'policy\': [\n        \'id\', \'type\', \'blob\', \'extra\',\n    ],\n    \'project\': [\n        \'id\', \'name\', \'extra\', \'description\', \'enabled\', \'domain_id\',\n        \'parent_id\', \'is_domain\',\n    ],\n    \'project_option\': [\n        \'project_id\', \'option_id\', \'option_value\',\n    ],\n    \'project_tag\': [\n        \'project_id\', \'name\',\n    ],\n    \'role\': [\n        \'id\', \'name\', \'extra\', \'domain_id\', \'description\',\n    ],\n    \'role_option\': [\n        \'role_id\', \'option_id\', \'option_value\',\n    ],\n    \'service\': [\n        \'id\', \'type\', \'extra\', \'enabled\',\n    ],\n    \'token\': [\n        \'id\', \'expires\', \'extra\', \'valid\', \'trust_id\', \'user_id\',\n    ],\n    \'trust\': [\n        \'id\', \'trustor_user_id\', \'trustee_user_id\', \'project_id\',\n        \'impersonation\', \'deleted_at\', \'expires_at\', \'remaining_uses\', \'extra\',\n        \'expires_at_int\', \'redelegated_trust_id\', \'redelegation_count\',\n    ],\n    \'trust_role\': [\n        \'trust_id\', \'role_id\',\n    ],\n    \'user\': [\n        \'id\', \'extra\', \'enabled\', \'default_project_id\', \'created_at\',\n        \'last_active_at\', \'domain_id\',\n    ],\n    \'user_option\': [\n        \'user_id\', \'option_id\', \'option_value\',\n    ],\n    \'user_group_membership\': [\n        \'user_id\', \'group_id\',\n    ],\n    \'region\': [\n        \'id\', \'description\', \'parent_region_id\', \'extra\',\n    ],\n    \'assignment\': [\n        \'type\', \'actor_id\', \'target_id\', \'role_id\', \'inherited\',\n    ],\n    \'id_mapping\': [\n        \'public_id\', \'domain_id\', \'local_id\', \'entity_type\',\n    ],\n    \'whitelisted_config\': [\n        \'domain_id\', \'group\', \'option\', \'value\',\n    ],\n    \'sensitive_config\': [\n        \'domain_id\', \'group\', \'option\', \'value\',\n    ],\n    \'policy_association\': [\n        \'id\', \'policy_id\', \'endpoint_id\', \'service_id\', \'region_id\',\n    ],\n    \'identity_provider\': [\n        \'id\', \'enabled\', \'description\', \'domain_id\', \'authorization_ttl\',\n    ],\n    \'federation_protocol\': [\n        \'id\', \'idp_id\', \'mapping_id\', \'remote_id_attribute\',\n    ],\n    \'mapping\': [\n        \'id\', \'rules\',\n    ],\n    \'service_provider\': [\n        \'auth_url\', \'id\', \'enabled\', \'description\', \'sp_url\',\n        \'relay_state_prefix\',\n    ],\n    \'idp_remote_ids\': [\n        \'idp_id\', \'remote_id\',\n    ],\n    \'consumer\': [\n        \'id\', \'description\', \'secret\', \'extra\',\n    ],\n    \'request_token\': [\n        \'id\', \'request_secret\', \'verifier\', \'authorizing_user_id\',\n        \'requested_project_id\', \'role_ids\', \'consumer_id\', \'expires_at\',\n    ],\n    \'access_token\': [\n        \'id\', \'access_secret\', \'authorizing_user_id\', \'project_id\', \'role_ids\',\n        \'consumer_id\', \'expires_at\',\n    ],\n    \'revocation_event\': [\n        \'id\', \'domain_id\', \'project_id\', \'user_id\', \'role_id\', \'trust_id\',\n        \'consumer_id\', \'access_token_id\', \'issued_before\', \'expires_at\',\n        \'revoked_at\', \'audit_id\', \'audit_chain_id\',\n    ],\n    \'project_endpoint\': [\n        \'endpoint_id\', \'project_id\'\n    ],\n    \'endpoint_group\': [\n        \'id\', \'name\', \'description\', \'filters\',\n    ],\n    \'project_endpoint_group\': [\n        \'endpoint_group_id\', \'project_id\',\n    ],\n    \'implied_role\': [\n        \'prior_role_id\', \'implied_role_id\',\n    ],\n    \'local_user\': [\n        \'id\', \'user_id\', \'domain_id\', \'name\', \'failed_auth_count\',\n        \'failed_auth_at\',\n    ],\n    \'password\': [\n        \'id\', \'local_user_id\', \'created_at\', \'expires_at\',\n        \'self_service\', \'password_hash\', \'created_at_int\', \'expires_at_int\',\n    ],\n    \'federated_user\': [\n        \'id\', \'user_id\', \'idp_id\', \'protocol_id\', \'unique_id\', \'display_name\',\n    ],\n    \'nonlocal_user\': [\n        \'domain_id\', \'name\', \'user_id\',\n    ],\n    \'system_assignment\': [\n        \'type\', \'actor_id\', \'target_id\', \'role_id\', \'inherited\',\n    ],\n    \'registered_limit\': [\n        \'internal_id\', \'id\', \'service_id\', \'region_id\', \'resource_name\',\n        \'default_limit\', \'description\',\n    ],\n    \'limit\': [\n        \'internal_id\', \'id\', \'project_id\', \'resource_limit\', \'description\',\n        \'registered_limit_id\', \'domain_id\',\n    ],\n    \'application_credential\': [\n        \'internal_id\', \'id\', \'name\', \'secret_hash\', \'description\', \'user_id\',\n        \'project_id\', \'expires_at\', \'system\', \'unrestricted\',\n    ],\n    \'application_credential_role\': [\n        \'application_credential_id\', \'role_id\',\n    ],\n    \'access_rule\': [\n        \'id\', \'service\', \'path\', \'method\', \'external_id\', \'user_id\',\n    ],\n    \'application_credential_access_rule\': [\n        \'application_credential_id\', \'access_rule_id\',\n    ],\n    \'expiring_user_group_membership\': [\n        \'user_id\', \'group_id\', \'idp_id\', \'last_verified\',\n    ],\n}\n\n\nclass MigrateBase(\n    db_fixtures.OpportunisticDBTestMixin,\n):\n    """Test complete orchestration between all database phases."""\n\n    def setUp(self):\n        super().setUp()\n\n        self.useFixture(log_fixture.get_logging_handle_error_fixture())\n        self.stdlog = self.useFixture(ksfixtures.StandardLogging())\n        self.useFixture(ksfixtures.WarningsFixture())\n\n        self.engine = enginefacade.writer.get_engine()\n        self.sessionmaker = enginefacade.writer.get_sessionmaker()\n\n        # NOTE(dstanek): Clear out sqlalchemy-migrate\'s script cache to allow\n        # us to have multiple repos (expand, migrate, contract) where the\n        # modules have the same name (001_awesome.py).\n        self.addCleanup(script.PythonScript.clear)\n\n        db_options.set_defaults(CONF, connection=self.engine.url)\n\n        # Override keystone\'s context manager to be oslo.db\'s global context\n        # manager.\n        sql.core._TESTING_USE_GLOBAL_CONTEXT_MANAGER = True\n        self.addCleanup(setattr,\n                        sql.core, \'_TESTING_USE_GLOBAL_CONTEXT_MANAGER\', False)\n        self.addCleanup(sql.cleanup)\n\n    def expand(self):\n        """Expand database schema."""\n        upgrades.expand_schema(engine=self.engine)\n\n    def contract(self):\n        """Contract database schema."""\n        upgrades.contract_schema(engine=self.engine)\n\n    @property\n    def metadata(self):\n        """A collection of tables and their associated schemas."""\n        return sqlalchemy.MetaData(self.engine)\n\n    def load_table(self, name):\n        table = sqlalchemy.Table(\n            name, self.metadata, autoload_with=self.engine,\n        )\n        return table\n\n    def assertTableDoesNotExist(self, table_name):\n        """Assert that a given table exists cannot be selected by name."""\n        # Switch to a different metadata otherwise you might still\n        # detect renamed or dropped tables\n        try:\n            sqlalchemy.Table(\n                table_name, self.metadata, autoload_with=self.engine,\n            )\n        except sqlalchemy.exc.NoSuchTableError:\n            pass\n        else:\n            raise AssertionError(\'Table "%s" already exists\' % table_name)\n\n    def assertTableColumns(self, table_name, expected_cols):\n        """Assert that the table contains the expected set of columns."""\n        table = self.load_table(table_name)\n        insert = this_table.insert().values(**d)\n        session.execute(insert)\n\n\nclass SqlLegacyRepoUpgradeTests(SqlMigrateBase):\n    def test_blank_db_to_start(self):\n        # we advertise that `--expand` must be run first.\n        log_info = self.useFixture(fixtures.FakeLogger(level=log.INFO))\n        status = checker.check_db_sync_status()\n        self.assertIn("keystone-manage db_sync --expand", log_info.output)\n        self.assertEqual(status, 2)\n\n        # Assert the correct message is printed when migrate is ahead of\n        # contract\n        self.expand()\n        log_info = self.useFixture(fixtures.FakeLogger(level=log.INFO))\n        status = checker.check_db_sync_status()\n        self.assertIn("keystone-manage db_sync --contract", log_info.output)\n        self.assertEqual(status, 4)\n\n        # Assert the correct message gets printed when all commands are on\n        # the same version\n        self.contract()\n        log_info = self.useFixture(fixtures.FakeLogger(level=log.INFO))\n        status = checker.check_db_sync_status()\n        self.assertIn("All db_sync commands are upgraded", log_info.output)\n        self.assertEqual(status, 0)\n\n    def test_upgrade_add_initial_tables(self):\n        self.expand()\n        for table in INITIAL_TABLE_STRUCTURE:\n            self.assertTableColumns(table, INITIAL_TABLE_STRUCTURE[table])\n\n\nclass FullMigrationSQLite(MigrateBase, unit.TestCase):\n    pass\n\n\nclass FullMigrationMySQL(MigrateBase, unit.TestCase):\n    FIXTURE = db_fixtures.MySQLOpportunisticFixture\n\n\nclass FullMigrationPostgreSQL(MigrateBase, unit.TestCase):\n    FIXTURE = db_fixtures.PostgresqlOpportunisticFixture\n                return True\n        return False\n\n    def does_index_exist(self, table_name, index_name):\n        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)\n        return index_name in [idx.name for idx in table.indexes]\n\n    def does_constraint_exist(self, table_name, constraint_name):\n        table = sqlalchemy.Table(table_name, self.metadata, autoload=True)\n        return constraint_name in [con.name for con in table.constraints]\n            self.assertEqual(\'DATETIME\', str(password.c.created_at.type))\n        self.assertFalse(password.c.created_at.nullable)\n\n\nclass MySQLOpportunisticFullMigration(FullMigration):\n    FIXTURE = test_base.MySQLOpportunisticFixture\n', 'message': 'Merge "Add revocation event indexes"', 'author_name': 'Gerrit Code Review', 'lang': 'py'}