Add revocation event indexes

This patch adds indexes to the revocation_event table as part of an
effort to improve performance during token validation.

Partial-Bug: 1524030
Change-Id: I73ff077bb6dc3ca8821f8cc14639bf986517d158
This commit is contained in:
Ronald De Rose 2016-09-26 13:59:46 +00:00
parent 2c4dc457aa
commit 477189d0c5
5 changed files with 106 additions and 5 deletions

View File

@ -0,0 +1,15 @@
# 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.
def upgrade(migrate_engine):
pass

View File

@ -0,0 +1,15 @@
# 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.
def upgrade(migrate_engine):
pass

View File

@ -0,0 +1,31 @@
# 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.
import sqlalchemy as sql
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
revocation_event = sql.Table('revocation_event', meta, autoload=True)
sql.Index('ix_revocation_event_issued_before',
revocation_event.c.issued_before).create()
sql.Index('ix_revocation_event_project_id_issued_before',
revocation_event.c.project_id,
revocation_event.c.issued_before).create()
sql.Index('ix_revocation_event_user_id_issued_before',
revocation_event.c.user_id,
revocation_event.c.issued_before).create()
sql.Index('ix_revocation_event_audit_id_issued_before',
revocation_event.c.audit_id,
revocation_event.c.issued_before).create()

View File

@ -31,11 +31,19 @@ class RevocationEvent(sql.ModelBase, sql.ModelDictMixin):
trust_id = sql.Column(sql.String(64))
consumer_id = sql.Column(sql.String(64))
access_token_id = sql.Column(sql.String(64))
issued_before = sql.Column(sql.DateTime(), nullable=False)
issued_before = sql.Column(sql.DateTime(), nullable=False, index=True)
expires_at = sql.Column(sql.DateTime())
revoked_at = sql.Column(sql.DateTime(), nullable=False, index=True)
audit_id = sql.Column(sql.String(32))
audit_chain_id = sql.Column(sql.String(32))
__table_args__ = (
sql.Index('ix_revocation_event_project_id_issued_before', 'project_id',
'issued_before'),
sql.Index('ix_revocation_event_user_id_issued_before', 'user_id',
'issued_before'),
sql.Index('ix_revocation_event_audit_id_issued_before',
'audit_id', 'issued_before'),
)
class Revoke(base.RevokeDriverBase):

View File

@ -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