Differential D396

Added indexes and alembic support
ClosedPublic
Actions

Authored by jskladan on Jun 17 2015, 12:59 PM.

Details

Reviewers
tflink
mkrizek
Maniphest Tasks
T510: execdb is missing indices, is very slow
Commits
rEXECDB98647d241358: Added indexes and alembic support
Summary

Added indexes to the relevant columns, and Alembic support to be able to track the db changes.

Test Plan

None

Diff Detail

Repository
rEXECDB execdb
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.
jskladan retitled this revision from to Added indexes and alembic support.Jun 17 2015, 12:59 PM
jskladan updated this object.
jskladan edited the test plan for this revision. (Show Details)
jskladan added reviewers: tflink, mkrizek.
tflink requested changes to this revision.Jun 25 2015, 4:18 PM

a few nits but overall, looks good

alembic/env.py
23

a bit nitpicky but this can be removed :)

28

This block seems a bit copy-pasta to me. Do we really need it?

execdb.spec
63

I suspect that's supposed to be "Jun" and not Jub

This revision now requires changes to proceed.Jun 25 2015, 4:18 PM
jskladan updated this revision to Diff 1081.Jul 1 2015, 11:19 AM
  • Fixed according to review
tflink accepted this revision.Jul 2 2015, 6:10 AM
This revision is now accepted and ready to land.Jul 2 2015, 6:10 AM
Closed by commit rEXECDB98647d241358: Added indexes and alembic support (authored by Josef Skladanka <jskladan@redhat.com>). · Explain WhyJul 2 2015, 11:57 AM
This revision was automatically updated to reflect the committed changes.

Revision Contents

Diff 1088

View Options

alembic.ini

  • This file was added.
1​# A generic, single database configuration.
2​
3​[alembic]
4​# path to migration scripts
5​script_location = alembic
6​
7​# template used to generate migration files
8​# file_template = %%(rev)s_%%(slug)s
9​
10​# max length of characters to apply to the
11​# "slug" field
12​#truncate_slug_length = 40
13​
14​# set to 'true' to run the environment during
15​# the 'revision' command, regardless of autogenerate
16​# revision_environment = false
17​
18​# set to 'true' to allow .pyc and .pyo files without
19​# a source .py file to be detected as revisions in the
20​# versions/ directory
21​# sourceless = false
22​
23​# version location specification; this defaults
24​# to alembic/versions. When using multiple version
25​# directories, initial revisions must be specified with --version-path
26​# version_locations = %(here)s/bar %(here)s/bat alembic/versions
27​
28​# the output encoding used when revision files
29​# are written from script.py.mako
30​# output_encoding = utf-8
31​
32​sqlalchemy.url = driver://user:pass@localhost/dbname
33​
34​[alembic-packaged]
35​# path to migration scripts on a packaged install
36​script_location = /usr/share/execdb/alembic
37​
38​sqlalchemy.url = driver://user:pass@localhost/dbname
39​
40​
41​# Logging configuration
42​[loggers]
43​keys = root,sqlalchemy,alembic
44​
45​[handlers]
46​keys = console
47​
48​[formatters]
49​keys = generic
50​
51​[logger_root]
52​level = WARN
53​handlers = console
54​qualname =
55​
56​[logger_sqlalchemy]
57​level = WARN
58​handlers =
59​qualname = sqlalchemy.engine
60​
61​[logger_alembic]
62​level = INFO
63​handlers =
64​qualname = alembic
65​
66​[handler_console]
67​class = StreamHandler
68​args = (sys.stderr,)
69​level = NOTSET
70​formatter = generic
71​
72​[formatter_generic]
73​format = %(levelname)-5.5s [%(name)s] %(message)s
74​datefmt = %H:%M:%S
View Options

alembic/env.py

  • This file was added.
1​from __future__ import with_statement
2​from alembic import context
3​from sqlalchemy import engine_from_config, pool
4​from logging.config import fileConfig
5​
6​# add '.' to the pythonpath to support migration inside development env
7​import sys
8​sys.path.append('.')
9​
10​# this is the Alembic Config object, which provides
11​# access to the values within the .ini file in use.
12​config = context.config
13​
14​# Interpret the config file for Python logging.
15​# This line sets up loggers basically.
16​fileConfig(config.config_file_name)
17​
18​# add your model's MetaData object here
19​# for 'autogenerate' support
20​from execdb import db
21​target_metadata = db.metadata
22​
23​
tflinkUnsubmitted
Not Done

a bit nitpicky but this can be removed :)

24​def run_migrations_offline():
25​ """Run migrations in 'offline' mode.
26​
27​ This configures the context with just a URL
28​ and not an Engine, though an Engine is acceptable
tflinkUnsubmitted
Not Done

This block seems a bit copy-pasta to me. Do we really need it?

29​ here as well. By skipping the Engine creation
30​ we don't even need a DBAPI to be available.
31​
32​ Calls to context.execute() here emit the given string to the
33​ script output.
34​
35​ """
36​ url = config.get_main_option("sqlalchemy.url")
37​ context.configure(url=url)
38​
39​ with context.begin_transaction():
40​ context.run_migrations()
41​
42​
43​def run_migrations_online():
44​ """Run migrations in 'online' mode.
45​
46​ In this scenario we need to create an Engine
47​ and associate a connection with the context.
48​
49​ """
50​
51​ alembic_config = config.get_section(config.config_ini_section)
52​ from execdb import app
53​ alembic_config['sqlalchemy.url'] = app.config['SQLALCHEMY_DATABASE_URI']
54​
55​ engine = engine_from_config(
56​ alembic_config,
57​ prefix='sqlalchemy.',
58​ poolclass=pool.NullPool)
59​
60​ connection = engine.connect()
61​ context.configure(
62​ connection=connection,
63​ target_metadata=target_metadata
64​ )
65​
66​ try:
67​ with context.begin_transaction():
68​ context.run_migrations()
69​ finally:
70​ connection.close()
71​
72​if context.is_offline_mode():
73​ run_migrations_offline()
74​else:
75​ run_migrations_online()
View Options

alembic/script.py.mako

  • This file was added.
1​"""${message}
2​
3​Revision ID: ${up_revision}
4​Revises: ${down_revision | comma,n}
5​Create Date: ${create_date}
6​
7​"""
8​
9​# revision identifiers, used by Alembic.
10​revision = ${repr(up_revision)}
11​down_revision = ${repr(down_revision)}
12​branch_labels = ${repr(branch_labels)}
13​depends_on = ${repr(depends_on)}
14​
15​from alembic import op
16​import sqlalchemy as sa
17​${imports if imports else ""}
18​
19​def upgrade():
20​ ${upgrades if upgrades else "pass"}
21​
22​
23​def downgrade():
24​ ${downgrades if downgrades else "pass"}
View Options

alembic/versions/1cefaba53e0_initial_revision.py

  • This file was added.
1​"""Initial revision
2​
3​Revision ID: 1cefaba53e0
4​Revises:
5​Create Date: 2015-06-17 14:43:12.385094
6​
7​"""
8​
9​# revision identifiers, used by Alembic.
10​revision = '1cefaba53e0'
11​down_revision = None
12​branch_labels = None
13​depends_on = None
14​
15​from alembic import op
16​import sqlalchemy as sa
17​
18​
19​def upgrade():
20​ ### commands auto generated by Alembic - please adjust! ###
21​ pass
22​ ### end Alembic commands ###
23​
24​
25​def downgrade():
26​ ### commands auto generated by Alembic - please adjust! ###
27​ pass
28​ ### end Alembic commands ###
View Options

alembic/versions/3b51d15f0f87_added_indexes.py

  • This file was added.
1​"""Added indexes
2​
3​Revision ID: 3b51d15f0f87
4​Revises: 1cefaba53e0
5​Create Date: 2015-06-17 14:45:54.988716
6​
7​"""
8​
9​# revision identifiers, used by Alembic.
10​revision = '3b51d15f0f87'
11​down_revision = '1cefaba53e0'
12​branch_labels = None
13​depends_on = None
14​
15​from alembic import op
16​import sqlalchemy as sa
17​
18​
19​def upgrade():
20​ ### commands auto generated by Alembic - please adjust! ###
21​ op.create_index('buildstep_fk_job_id', 'build_step', ['job_id'], unique=False)
22​ op.create_index(
23​ 'buildstep_idx_status',
24​ 'build_step',
25​ ['status'],
26​ unique=False,
27​ postgresql_ops={
28​ 'status': 'text_pattern_ops'})
29​ op.create_index(
30​ 'job_idx_item',
31​ 'job',
32​ ['item'],
33​ unique=False,
34​ postgresql_ops={
35​ 'item': 'text_pattern_ops'})
36​ op.create_index(
37​ 'job_idx_taskname',
38​ 'job',
39​ ['taskname'],
40​ unique=False,
41​ postgresql_ops={
42​ 'taskname': 'text_pattern_ops'})
43​ ### end Alembic commands ###
44​
45​
46​def downgrade():
47​ ### commands auto generated by Alembic - please adjust! ###
48​ op.drop_index('job_idx_taskname', table_name='job')
49​ op.drop_index('job_idx_item', table_name='job')
50​ op.drop_index('buildstep_idx_status', table_name='build_step')
51​ op.drop_index('buildstep_fk_job_id', table_name='build_step')
52​ ### end Alembic commands ###
View Options

execdb.spec

1%if ! (0%{?fedora} > 12 || 0%{?rhel} > 5) 1​%if ! (0%{?fedora} > 12 || 0%{?rhel} > 5)
2%{!?python_sitelib: %global python_sitelib %(%{__python} -c "from distutils.sysconfig import get_python_lib; print(get_python_lib())")} 2​%{!?python_sitelib: %global python_sitelib %(%{__python} -c "from distutils.sysconfig import get_python_lib; print(get_python_lib())")}
3%{!?python_sitearch: %global python_sitearch %(%{__python} -c "from distutils.sysconfig import get_python_lib; print(get_python_lib(1))")} 3​%{!?python_sitearch: %global python_sitearch %(%{__python} -c "from distutils.sysconfig import get_python_lib; print(get_python_lib(1))")}
4%endif 4​%endif
5 5​
6Name: execdb 6​Name: execdb
7Version: 0.0.6 7​Version: 0.0.7
8Release: 1%{?dist} 8​Release: 1%{?dist}
9Summary: Execution status database for Taskotron 9​Summary: Execution status database for Taskotron
10 10​
11License: GPLv2+ 11​License: GPLv2+
12URL: https://bitbucket.org/fedoraqa/execdb 12​URL: https://bitbucket.org/fedoraqa/execdb
13Source0: https://qadevel.cloud.fedoraproject.org/releases/%{name}/%{name}-%{version}.tar.gz 13​Source0: https://qadevel.cloud.fedoraproject.org/releases/%{name}/%{name}-%{version}.tar.gz
14 14​
15BuildArch: noarch 15​BuildArch: noarch
16 16​
17Requires: python-flask 17​Requires: python-flask
18Requires: python-flask-sqlalchemy 18​Requires: python-flask-sqlalchemy
19Requires: python-flask-wtf 19​Requires: python-flask-wtf
20Requires: python-flask-login 20​Requires: python-flask-login
21Requires: python-flask-restful 21​Requires: python-flask-restful
22Requires: python-six 22​Requires: python-six
23​Requires: python-alembic
23BuildRequires: python2-devel python-setuptools 24​BuildRequires: python2-devel python-setuptools
24 25​
25%description 26​%description
26ExecDB is a execution status database for Taskotron 27​ExecDB is a execution status database for Taskotron
27 28​
28%prep 29​%prep
29%setup -q 30​%setup -q
30 31​
31%build 32​%build
32%{__python2} setup.py build 33​%{__python2} setup.py build
33 34​
34%install 35​%install
35%{__python2} setup.py install --skip-build --root %{buildroot} 36​%{__python2} setup.py install --skip-build --root %{buildroot}
36 37​
37# apache and wsgi settings 38​# apache and wsgi settings
38mkdir -p %{buildroot}%{_datadir}/execdb/conf 39​mkdir -p %{buildroot}%{_datadir}/execdb/conf
39cp conf/execdb.conf %{buildroot}%{_datadir}/execdb/conf/. 40​cp conf/execdb.conf %{buildroot}%{_datadir}/execdb/conf/.
40cp conf/execdb.wsgi %{buildroot}%{_datadir}/execdb/. 41​cp conf/execdb.wsgi %{buildroot}%{_datadir}/execdb/.
41 42​
43​# alembic config and data
44​cp -r alembic %{buildroot}%{_datadir}/execdb/.
45​install alembic.ini %{buildroot}%{_datadir}/execdb/.
46​
47​# exedb config
42mkdir -p %{buildroot}%{_sysconfdir}/execdb 48​mkdir -p %{buildroot}%{_sysconfdir}/execdb
43install conf/settings.py.example %{buildroot}%{_sysconfdir}/execdb/settings.py.example 49​install conf/settings.py.example %{buildroot}%{_sysconfdir}/execdb/settings.py.example
44 50​
45%files 51​%files
46%doc README.md conf/* 52​%doc README.md conf/*
47%{python_sitelib}/execdb 53​%{python_sitelib}/execdb
48%{python_sitelib}/*.egg-info 54​%{python_sitelib}/*.egg-info
49 55​
50%attr(755,root,root) %{_bindir}/execdb 56​%attr(755,root,root) %{_bindir}/execdb
51%dir %{_sysconfdir}/execdb 57​%dir %{_sysconfdir}/execdb
52%{_sysconfdir}/execdb/* 58​%{_sysconfdir}/execdb/*
53%dir %{_datadir}/execdb 59​%dir %{_datadir}/execdb
54%{_datadir}/execdb/* 60​%{_datadir}/execdb/*
55 61​
56%changelog 62​%changelog
63​* Wed Jun 17 2015 Josef Skladanka <jskladan@fedoraproject.org> - 0.0.7
tflinkUnsubmitted
Not Done

I suspect that's supposed to be "Jun" and not Jub

64​- added alembic config and data to package
65​- added requires python-alembic
66​
57* Mon Mar 30 2015 Tim Flink <tflink@fedoraproject.org> - 0.0.6 67​* Mon Mar 30 2015 Tim Flink <tflink@fedoraproject.org> - 0.0.6
58- bumped version for initial release 68​- bumped version for initial release
59 69​
60* Thu Feb 12 2015 Josef Skladanka <jskladan@redhat.com> - 0.0.1 70​* Thu Feb 12 2015 Josef Skladanka <jskladan@redhat.com> - 0.0.1
61- initial packaging 71​- initial packaging
View Options

execdb/cli.py

Show All 19 Lines
20# This is required for running on EL6 20​# This is required for running on EL6
21import __main__ 21​import __main__
22__main__.__requires__ = ['SQLAlchemy >= 0.7', 'Flask >= 0.9', 'jinja2 >= 2.6'] 22​__main__.__requires__ = ['SQLAlchemy >= 0.7', 'Flask >= 0.9', 'jinja2 >= 2.6']
23import pkg_resources 23​import pkg_resources
24 24​
25 25​
26from optparse import OptionParser 26​from optparse import OptionParser
27import sys 27​import sys
28​import os
28 29​
29from execdb import db 30​from execdb import db
30from execdb.models.user import User 31​from execdb.models.user import User
31from execdb.models.job import Job, BuildStep 32​from execdb.models.job import Job, BuildStep
32 33​
34​from alembic.config import Config
35​from alembic import command as al_command
36​from alembic.migration import MigrationContext
37​
38​from sqlalchemy.engine import reflection
39​
40​
41​def get_alembic_config():
42​ # the location of the alembic ini file and alembic scripts changes when
43​ # installed via package
44​ if os.path.exists("./alembic.ini"):
45​ alembic_cfg = Config("./alembic.ini")
46​ else:
47​ alembic_cfg = Config("/usr/share/execdb/alembic.ini",
48​ ini_section='alembic-packaged')
49​ return alembic_cfg
50​
51​
52​def upgrade_db(*args):
53​ print "Upgrading Database to Latest Revision"
54​ alembic_cfg = get_alembic_config()
55​ al_command.upgrade(alembic_cfg, "head")
56​
57​
58​def init_alembic(*args):
59​ alembic_cfg = get_alembic_config()
60​
61​ # check to see if the db has already been initialized by checking for an
62​ # alembic revision
63​ context = MigrationContext.configure(db.engine.connect())
64​ current_rev = context.get_current_revision()
65​
66​ if not current_rev:
67​ print "Initializing alembic"
68​ print " - Setting the current version to the first revision"
69​ al_command.stamp(alembic_cfg, "1cefaba53e0")
70​ else:
71​ print "Alembic already initialized"
72​
33 73​
34def initialize_db(destructive): 74​def initialize_db(destructive):
75​ alembic_cfg = get_alembic_config()
76​
35 print "Initializing database" 77​ print "Initializing database"
78​
36 if destructive: 79​ if destructive:
37 print " - Dropping all tables" 80​ print " - Dropping all tables"
38 db.drop_all() 81​ db.drop_all()
39 print " - Creating tables" 82​
40 db.create_all() 83​ # check whether the table 'job' exists
84​ # if it doesn't, we assume that the database is empty
85​ insp = reflection.Inspector.from_engine(db.engine)
86​ table_names = insp.get_table_names()
87​ if 'job' not in table_names and 'Job' not in table_names:
88​ print " - Creating tables"
89​ db.create_all()
90​ print " - Stamping alembic's current version to 'head'"
91​ al_command.stamp(alembic_cfg, "head")
92​
93​ # check to see if the db has already been initialized by checking for an
94​ # alembic revision
95​ context = MigrationContext.configure(db.engine.connect())
96​ current_rev = context.get_current_revision()
97​ if current_rev:
98​ print " - Database is currently at rev %s" % current_rev
99​ upgrade_db(destructive)
100​ else:
101​ print "WARN: You need to have your db stamped with an alembic revision"
102​ print " Run 'init_alembic' sub-command first."
41 103​
42 104​
43def mock_data(destructive): 105​def mock_data(destructive):
44 print "Populating tables with mock-data" 106​ print "Populating tables with mock-data"
45 107​
46 if destructive or not db.session.query(User).count(): 108​ if destructive or not db.session.query(User).count():
47 print " - User" 109​ print " - User"
48 data_users = [('admin', 'admin'), ('user', 'user')] 110​ data_users = [('admin', 'admin'), ('user', 'user')]
49 111​
50 for d in data_users: 112​ for d in data_users:
51 u = User(*d) 113​ u = User(*d)
52 db.session.add(u) 114​ db.session.add(u)
53 115​
54 db.session.commit() 116​ db.session.commit()
55 else: 117​ else:
56 print " - skipped User" 118​ print " - skipped User"
57 119​
58 120​
59def main(): 121​def main():
60 possible_commands = ['init_db', 'mock_data'] 122​ possible_commands = ['init_db', 'mock_data', 'upgrade_db', 'init_alembic']
61 123​
62 usage = 'usage: [DEV=true] %prog ' + "(%s)" % ' | '.join(possible_commands) 124​ usage = 'usage: [DEV=true] %prog ' + "(%s)" % ' | '.join(possible_commands)
63 parser = OptionParser(usage=usage) 125​ parser = OptionParser(usage=usage)
64 parser.add_option("-d", "--destructive", 126​ parser.add_option("-d", "--destructive",
65 action="store_true", dest="destructive", default=False, 127​ action="store_true", dest="destructive", default=False,
66 help="Drop tables in `init_db`; Store data in `mock_data` " 128​ help="Drop tables in `init_db`; Store data in `mock_data` "
67 "even if the tables are not empty") 129​ "even if the tables are not empty")
68 130​
69 (options, args) = parser.parse_args() 131​ (options, args) = parser.parse_args()
70 132​
71 if len(args) != 1 or args[0] not in possible_commands: 133​ if len(args) != 1 or args[0] not in possible_commands:
72 print usage 134​ print usage
73 print 135​ print
74 print 'Please use one of the following commands: %s' % str(possible_commands) 136​ print 'Please use one of the following commands: %s' % str(possible_commands)
75 sys.exit(1) 137​ sys.exit(1)
76 138​
77 command = { 139​ command = {
78 'init_db': initialize_db, 140​ 'init_db': initialize_db,
79 'mock_data': mock_data, 141​ 'mock_data': mock_data,
142​ 'upgrade_db': upgrade_db,
143​ 'init_alembic': init_alembic,
80 }[args[0]] 144​ }[args[0]]
81 ​
82 if not options.destructive: 145​ if not options.destructive:
83 print "Proceeding with non-destructive init. To perform destructive "\ 146​ print "Proceeding in non-destructive mode. To perform destructive "\
84 "steps use -d option." 147​ "steps use -d option."
85 148​
86 command(options.destructive) 149​ command(options.destructive)
87 150​
151​ sys.exit(0)
152​
153​
88if __name__ == '__main__': 154​if __name__ == '__main__':
89 main() 155​ main()
View Options

execdb/models/job.py

Show First 20 Lines • Show All 43 Lines • ▼ Show 20 Line(s)26class Job(db.Model):
44 # task_git_hash = db.Column(db.String(40)) # ??? 44​ # task_git_hash = db.Column(db.String(40)) # ???
45 45​
46 # link_machine_log = db.Column(db.Text) # ??? 46​ # link_machine_log = db.Column(db.Text) # ???
47 link_build_log = db.Column(db.Text) # $buildbot_url/builders/$builder_name/builds/$build_id 47​ link_build_log = db.Column(db.Text) # $buildbot_url/builders/$builder_name/builds/$build_id
48 # link_resultsdb = db.Column(db.Text) # Should be UUID-based 48​ # link_resultsdb = db.Column(db.Text) # Should be UUID-based
49 49​
50 build_steps = db.relation('BuildStep', backref='job', order_by="BuildStep.id") 50​ build_steps = db.relation('BuildStep', backref='job', order_by="BuildStep.id")
51 51​
52​ __table_args__ = (
53​ db.Index('job_idx_item', 'item',
54​ postgresql_ops={'item': 'text_pattern_ops'},
55​ ),
56​ db.Index('job_idx_taskname', 'taskname',
57​ postgresql_ops={'taskname': 'text_pattern_ops'},
58​ ),
59​ )
60​
52 def __init__(self, fedmsg_data=None): 61​ def __init__(self, fedmsg_data=None):
53 self.uuid = str(uuid.uuid1()) 62​ self.uuid = str(uuid.uuid1())
54 self.fedmsg_data = fedmsg_data 63​ self.fedmsg_data = fedmsg_data
55 64​
56 def start(self): 65​ def start(self):
57 self.t_build_started = datetime.datetime.utcnow() 66​ self.t_build_started = datetime.datetime.utcnow()
58 67​
59 def finish(self): 68​ def finish(self):
Show All 32 Lines100class BuildStep(db.Model):
92 id = db.Column(db.Integer, primary_key=True) 101​ id = db.Column(db.Integer, primary_key=True)
93 job_id = db.Column(db.Integer, db.ForeignKey('job.id')) 102​ job_id = db.Column(db.Integer, db.ForeignKey('job.id'))
94 name = db.Column(db.String(20)) 103​ name = db.Column(db.String(20))
95 status = db.Column(db.String(10)) 104​ status = db.Column(db.String(10))
96 started_at = db.Column(db.DateTime) 105​ started_at = db.Column(db.DateTime)
97 finished_at = db.Column(db.DateTime) 106​ finished_at = db.Column(db.DateTime)
98 data = db.Column(db.Text) 107​ data = db.Column(db.Text)
99 108​
109​ __table_args__ = (
110​ db.Index('buildstep_idx_status', 'status',
111​ postgresql_ops={'status': 'text_pattern_ops'},
112​ ),
113​ db.Index('buildstep_fk_job_id', 'job_id'),
114​ )
115​
100 def __repr__(self): 116​ def __repr__(self):
101 return "(%s, %s)" % (self.name, self.status) 117​ return "(%s, %s)" % (self.name, self.status)
102 118​
103 def __init__(self, name): 119​ def __init__(self, name):
104 self.name = name 120​ self.name = name
105 121​
106 def start(self): 122​ def start(self):
107 self.started_at = datetime.datetime.utcnow() 123​ self.started_at = datetime.datetime.utcnow()
108 124​
109 def finish(self): 125​ def finish(self):
110 self.finished_at = datetime.datetime.utcnow() 126​ self.finished_at = datetime.datetime.utcnow()
111 127​
112 @property 128​ @property
113 def log_url(self): 129​ def log_url(self):
114 return "%s/steps/%s" % (self.job.link_build_log, self.name) 130​ return "%s/steps/%s" % (self.job.link_build_log, self.name)