Coverage for master/buildbot/db/migrate/versions/005_add_indexes : 100%

# This file is part of Buildbot.  Buildbot is free software: you can 

# redistribute it and/or modify it under the terms of the GNU General Public 

# License as published by the Free Software Foundation, version 2. 

# 

# This program is distributed in the hope that it will be useful, but WITHOUT 

# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS 

# FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more 

# details. 

# 

# You should have received a copy of the GNU General Public License along with 

# this program; if not, write to the Free Software Foundation, Inc., 51 

# Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. 

# 

# Copyright Buildbot Team Members 

import sqlalchemy as sa 

def upgrade(migrate_engine): 

    metadata = sa.MetaData() 

    metadata.bind = migrate_engine 

    # note that all of the tables defined here omit the ForeignKey constraints; 

    # this just lets this code specify the tables in any order; the tables are 

    # not re-created here, so this omission causes no problems - the key 

    # constraints are still defined in the table 

    def add_index(table_name, col_name): 

        idx_name = "%s_%s" % (table_name, col_name) 

        idx = sa.Index(idx_name, metadata.tables[table_name].c[col_name]) 

        idx.create(migrate_engine) 

    sa.Table('buildrequests', metadata, 

        sa.Column('id', sa.Integer,  primary_key=True), 

        sa.Column('buildsetid', sa.Integer, nullable=False), 

        sa.Column('buildername', sa.String(length=None), nullable=False), 

        sa.Column('priority', sa.Integer, nullable=False), 

        sa.Column('claimed_at', sa.Integer, server_default=sa.DefaultClause("0")), 

        sa.Column('claimed_by_name', sa.String(length=None)), 

        sa.Column('claimed_by_incarnation', sa.String(length=None)), 

        sa.Column('complete', sa.Integer, server_default=sa.DefaultClause("0")), 

        sa.Column('results', sa.SmallInteger), 

        sa.Column('submitted_at', sa.Integer, nullable=False), 

        sa.Column('complete_at', sa.Integer), 

    ) 

    add_index("buildrequests", "buildsetid") 

    add_index("buildrequests", "buildername") 

    add_index("buildrequests", "complete") 

    add_index("buildrequests", "claimed_at") 

    add_index("buildrequests", "claimed_by_name") 

    sa.Table('builds', metadata, 

        sa.Column('id', sa.Integer,  primary_key=True), 

        sa.Column('number', sa.Integer, nullable=False), 

        sa.Column('brid', sa.Integer, nullable=False), 

        sa.Column('start_time', sa.Integer, nullable=False), 

        sa.Column('finish_time', sa.Integer), 

    ) 

    add_index("builds", "number") 

    add_index("builds", "brid") 

    sa.Table('buildsets', metadata, 

        sa.Column('id', sa.Integer,  primary_key=True), 

        sa.Column('external_idstring', sa.String(256)), 

        sa.Column('reason', sa.String(256)), 

        sa.Column('sourcestampid', sa.Integer, nullable=False), 

        sa.Column('submitted_at', sa.Integer, nullable=False), 

        sa.Column('complete', sa.SmallInteger, nullable=False, server_default=sa.DefaultClause("0")), 

        sa.Column('complete_at', sa.Integer), 

        sa.Column('results', sa.SmallInteger), 

    ) 

    add_index("buildsets", "complete") 

    add_index("buildsets", "submitted_at") 

    sa.Table('buildset_properties', metadata, 

        sa.Column('buildsetid', sa.Integer, nullable=False), 

        sa.Column('property_name', sa.String(256), nullable=False), 

        sa.Column('property_value', sa.String(1024), nullable=False), 

    ) 

    add_index("buildset_properties", "buildsetid") 

    sa.Table('changes', metadata, 

        sa.Column('changeid', sa.Integer,  primary_key=True), 

        sa.Column('author', sa.String(256), nullable=False), 

        sa.Column('comments', sa.String(1024), nullable=False), 

        sa.Column('is_dir', sa.SmallInteger, nullable=False), 

        sa.Column('branch', sa.String(256)), 

        sa.Column('revision', sa.String(256)), 

        sa.Column('revlink', sa.String(256)), 

        sa.Column('when_timestamp', sa.Integer, nullable=False), 

        sa.Column('category', sa.String(256)), 

        sa.Column('repository', sa.Text, nullable=False, server_default=''), 

        sa.Column('project', sa.Text, nullable=False, server_default=''), 

    ) 

    add_index("changes", "branch") 

    add_index("changes", "revision") 

    add_index("changes", "author") 

    add_index("changes", "category") 

    add_index("changes", "when_timestamp") 

    sa.Table('change_files', metadata, 

        sa.Column('changeid', sa.Integer, nullable=False), 

        sa.Column('filename', sa.String(1024), nullable=False), 

    ) 

    add_index("change_files", "changeid") 

    sa.Table('change_links', metadata, 

        sa.Column('changeid', sa.Integer, nullable=False), 

        sa.Column('link', sa.String(1024), nullable=False), 

    ) 

    add_index("change_links", "changeid") 

    sa.Table('change_properties', metadata, 

        sa.Column('changeid', sa.Integer, nullable=False), 

        sa.Column('property_name', sa.String(256), nullable=False), 

        sa.Column('property_value', sa.String(1024), nullable=False), 

    ) 

    add_index("change_properties", "changeid") 

    # schedulers already has an index 

    sa.Table('scheduler_changes', metadata, 

        sa.Column('schedulerid', sa.Integer), 

        sa.Column('changeid', sa.Integer), 

        sa.Column('important', sa.SmallInteger), 

    ) 

    add_index("scheduler_changes", "schedulerid") 

    add_index("scheduler_changes", "changeid") 

    sa.Table('scheduler_upstream_buildsets', metadata, 

        sa.Column('buildsetid', sa.Integer), 

        sa.Column('schedulerid', sa.Integer), 

        sa.Column('active', sa.SmallInteger), 

    ) 

    add_index("scheduler_upstream_buildsets", "buildsetid") 

    add_index("scheduler_upstream_buildsets", "schedulerid") 

    add_index("scheduler_upstream_buildsets", "active") 

    # sourcestamps are only queried by id, no need for additional indexes 

    sa.Table('sourcestamp_changes', metadata, 

        sa.Column('sourcestampid', sa.Integer, nullable=False), 

        sa.Column('changeid', sa.Integer, nullable=False), 

    ) 

    add_index("sourcestamp_changes", "sourcestampid")