Merge "Add migration for adding missing indexes"

releasenotes/notes/add-missing-metadata-id-indexes-dec66ab17c5c76c9.yaml

@@ -0,0 +1,8 @@
+---
+upgrade:
+  - Contains a DB schema migration to add indexes that were incorrectly
+    dropped as part of 2822a408bdd0
+fixes:
+  - Fixes performance issue on queries involving the metadata tables by
+    re-adding indexes that were accidently dropped
+

subunit2sql/db/models.py

@@ -60,6 +60,7 @@ class Test(BASE, SubunitBase):
 
 class Run(BASE, SubunitBase):
     __tablename__ = 'runs'
+    __table_args__ = (sa.Index('ix_run_at', 'run_at'),)
     uuid = sa.Column(sa.String(36),
                      default=lambda: six.text_type(uuid.uuid4()))
     id = sa.Column(sa.BigInteger, primary_key=True)
@@ -93,7 +94,8 @@ class TestRun(BASE, SubunitBase):
 
 class RunMetadata(BASE, SubunitBase):
     __tablename__ = 'run_metadata'
-    __table_args__ = (sa.Index('ix_run_metadata_run_id', 'run_id'),)
+    __table_args__ = (sa.Index('ix_run_key_value', 'key', 'value'),
+                      sa.Index('ix_run_id', 'run_id'))
 
     id = sa.Column(sa.BigInteger, primary_key=True)
     key = sa.Column(sa.String(255))
@@ -103,8 +105,8 @@ class RunMetadata(BASE, SubunitBase):
 
 class TestRunMetadata(BASE, SubunitBase):
     __tablename__ = 'test_run_metadata'
-    __table_args__ = (sa.Index('ix_test_run_metadata_test_run_id',
-                               'test_run_id'),)
+    __table_args__ = (sa.Index('ix_test_run_key_value', 'key', 'value'),
+                      sa.Index('ix_test_run_id', 'test_run_id'))
 
     id = sa.Column(sa.BigInteger, primary_key=True)
     key = sa.Column(sa.String(255))
@@ -114,8 +116,8 @@ class TestRunMetadata(BASE, SubunitBase):
 
 class TestMetadata(BASE, SubunitBase):
     __tablename__ = 'test_metadata'
-    __table_args__ = (sa.Index('ix_test_metadata_test_id',
-                               'test_id'),)
+    __table_args__ = (sa.Index('ix_test_key_value', 'key', 'value'),
+                      sa.Index('ix_test_id', 'test_id'))
 
     id = sa.Column(sa.BigInteger, primary_key=True)
     key = sa.Column(sa.String(255))
@@ -125,8 +127,7 @@ class TestMetadata(BASE, SubunitBase):
 
 class Attachments(BASE, SubunitBase):
     __tablename__ = 'attachments'
-    __table_args__ = (sa.Index('ix_attachemnts_id',
-                               'test_run_id'),)
+    __table_args__ = (sa.Index('ix_attach_test_run_id', 'test_run_id'),)
     id = sa.Column(sa.BigInteger, primary_key=True)
     test_run_id = sa.Column(sa.BigInteger)
     label = sa.Column(sa.String(255))

subunit2sql/migrations/versions/35cd45895e56_add_missing_fk_indexes.py

@@ -0,0 +1,60 @@
+# Copyright (c) 2015 Hewlett-Packard Development Company, L.P.
+#
+# 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.
+
+"""Add missing fk indexes
+
+Revision ID: 35cd45895e56
+Revises: 2822a408bdd0
+Create Date: 2015-11-30 15:32:37.218171
+
+"""
+
+# revision identifiers, used by Alembic.
+revision = '35cd45895e56'
+down_revision = '2822a408bdd0'
+
+from alembic import context
+from alembic import op
+from sqlalchemy.engine import reflection
+
+
+def upgrade():
+    migration_context = context.get_context()
+    insp = reflection.Inspector(migration_context.bind)
+    test_run_meta_indx = insp.get_indexes('test_run_metadata')
+    run_meta_indx = insp.get_indexes('run_metadata')
+    test_meta_indx = insp.get_indexes('test_metadata')
+    runs_indx = insp.get_indexes('runs')
+    attach_indx = insp.get_indexes('attachments')
+    if 'run_id' not in [
+        x['column_names'][0] for x in run_meta_indx if len(x) == 1]:
+        op.create_index('ix_run_id', 'run_metadata', ['run_id'])
+    if 'test_id' not in [
+        x['column_names'][0] for x in test_meta_indx if len(x) == 1]:
+        op.create_index('ix_test_id', 'test_metadata', ['test_id'])
+    if 'test_run_id' not in [
+        x['column_names'][0] for x in test_run_meta_indx if len(x) == 1]:
+        op.create_index('ix_test_run_id', 'test_run_metadata', ['test_run_id'])
+    if 'run_at' not in [
+        x['column_names'][0] for x in runs_indx if len(x) == 1]:
+        op.create_index('ix_run_at', 'runs', ['run_at'])
+    if 'test_run_id' not in [
+        x['column_names'][0] for x in attach_indx if len(x) == 1]:
+        op.create_index('ix_attach_test_run_id', 'attachments',
+                        ['test_run_id'])
+
+
+def downgrade():
+    NotImplementedError()