# HG changeset patch # User hodgestar # Date 1256685600 0 # Node ID 0d7d6552477eac50efa402774768e65403bfdfa5 # Parent d2e965fc31d4a5f6a3f89cde5ef305b8351f0662 Database upgrade cleanup: * Adds database server specific sequence updating and index dropping support. * Fixes numerous upgrade scripts that relied on bitten.model being in the same state it was when the upgrade scripts were written. * Avoids temporary table name clashes by appending _vX to temporary table names where X is the upgrade version number. * Adds sequence fixing upgrade function to version 10 upgrade (Fixes #452). * Corrects dropping and creating of indexes in add_config_platform_rev_index_to_build (Fixes #461). * Commits database at each step of the upgrade process (alleviates some issues with upgrades that fail part way, see #462). Adds basic unit tests for upgrade functions. New upgrades tested against MySQL, PostGreSQL and SQLite. diff --git a/bitten/main.py b/bitten/main.py --- a/bitten/main.py +++ b/bitten/main.py @@ -71,11 +71,12 @@ for function in upgrades.map.get(version): print textwrap.fill(inspect.getdoc(function)) function(self.env, db) - print 'Done.' - cursor.execute("UPDATE system SET value=%s WHERE " - "name='bitten_version'", (schema_version,)) - self.log.info('Upgraded Bitten tables from version %d to %d', - current_version, schema_version) + cursor.execute("UPDATE system SET value=%s WHERE " + "name='bitten_version'", (version,)) + db.commit() + print "Bitten upgrade to version %d done." % version + self.log.info('Upgraded Bitten tables to version %d', + version) class BuildSystem(Component): diff --git a/bitten/tests/__init__.py b/bitten/tests/__init__.py --- a/bitten/tests/__init__.py +++ b/bitten/tests/__init__.py @@ -12,7 +12,8 @@ def master_suite(): - from bitten.tests import admin, master, model, queue, web_ui, notify + from bitten.tests import admin, master, model, queue, web_ui, notify, \ + upgrades from bitten.report import tests as report suite = unittest.TestSuite() suite.addTest(admin.suite()) @@ -22,6 +23,7 @@ suite.addTest(web_ui.suite()) suite.addTest(report.suite()) suite.addTest(notify.suite()) + suite.addTest(upgrades.suite()) return suite def suite(): diff --git a/bitten/tests/upgrades.py b/bitten/tests/upgrades.py new file mode 100644 --- /dev/null +++ b/bitten/tests/upgrades.py @@ -0,0 +1,278 @@ +# -*- coding: utf-8 -*- +# +# Copyright (C) 2009 Edgewall Software +# All rights reserved. +# +# This software is licensed as described in the file COPYING, which +# you should have received as part of this distribution. The terms +# are also available at http://bitten.edgewall.org/wiki/License. + +import unittest + +import warnings +warnings.filterwarnings('ignore', '^Unknown table') +warnings.filterwarnings('ignore', '^the sets module is deprecated') + +from trac.test import EnvironmentStub +from trac.db import Table, Column, Index, DatabaseManager +from bitten.upgrades import update_sequence, drop_index +from bitten import upgrades, main, model + +import os +import shutil +import tempfile + + +class BaseUpgradeTestCase(unittest.TestCase): + + schema = None + other_tables = [] + + def setUp(self): + self.env = EnvironmentStub() + self.env.config.set('trac', 'database', self.env.dburi) + self.env.path = tempfile.mkdtemp() + logs_dir = self.env.config.get("bitten", "logs_dir") + if os.path.isabs(logs_dir): + raise ValueError("Should not have absolute logs directory for temporary test") + logs_dir = os.path.join(self.env.path, logs_dir) + if not os.path.isdir(logs_dir): + os.makedirs(logs_dir) + + db = self.env.get_db_cnx() + cursor = db.cursor() + + for table_name in self.other_tables: + cursor.execute("DROP TABLE IF EXISTS %s" % (table_name,)) + + connector, _ = DatabaseManager(self.env)._get_connector() + for table in self.schema: + cursor.execute("DROP TABLE IF EXISTS %s" % (table.name,)) + for stmt in connector.to_sql(table): + cursor.execute(stmt) + + db.commit() + + def tearDown(self): + shutil.rmtree(self.env.path) + del self.env + + +class UpgradeHelperTestCase(BaseUpgradeTestCase): + + schema = [ + Table('test_update_sequence', key='id')[ + Column('id', auto_increment=True), Column('name'), + ], + Table('test_drop_index', key='id')[ + Column('id', type='int'), Column('name', size=20), + Index(['name']) + ], + ] + + def test_update_sequence(self): + db = self.env.get_db_cnx() + cursor = db.cursor() + + for rowid, name in [(1, 'a'), (2, 'b'), (3, 'c')]: + cursor.execute("INSERT INTO test_update_sequence (id, name)" + " VALUES (%s, %s)", (rowid, name)) + update_sequence(self.env, db, 'test_update_sequence', 'id') + + cursor.execute("INSERT INTO test_update_sequence (name)" + " VALUES (%s)", ('d',)) + + cursor.execute("SELECT id FROM test_update_sequence WHERE name = %s", + ('d',)) + row = cursor.fetchone() + self.assertEqual(row[0], 4) + + def test_drop_index(self): + db = self.env.get_db_cnx() + cursor = db.cursor() + + cursor.execute("INSERT INTO test_drop_index (id, name)" + " VALUES (%s, %s)", (1, 'a')) + + def do_drop(): + drop_index(self.env, db, 'test_drop_index', 'test_drop_index_name_idx') + + # dropping the index should succeed the first time and fail the next + do_drop() + self.assertRaises(Exception, do_drop) + + +class UpgradeScriptsTestCase(BaseUpgradeTestCase): + + schema = [ + # Sytem + Table('system', key='name')[ + Column('name'), Column('value') + ], + # Config + Table('bitten_config', key='name')[ + Column('name'), Column('path'), Column('label'), + Column('active', type='int'), Column('description') + ], + # Platform + Table('bitten_platform', key='id')[ + Column('id', auto_increment=True), Column('config'), Column('name') + ], + Table('bitten_rule', key=('id', 'propname'))[ + Column('id'), Column('propname'), Column('pattern'), + Column('orderno', type='int') + ], + # Build + Table('bitten_build', key='id')[ + Column('id', auto_increment=True), Column('config'), Column('rev'), + Column('rev_time', type='int'), Column('platform', type='int'), + Column('slave'), Column('started', type='int'), + Column('stopped', type='int'), Column('status', size=1), + Index(['config', 'rev', 'slave']) + ], + Table('bitten_slave', key=('build', 'propname'))[ + Column('build', type='int'), Column('propname'), Column('propvalue') + ], + # Build Step + Table('bitten_step', key=('build', 'name'))[ + Column('build', type='int'), Column('name'), Column('description'), + Column('status', size=1), Column('log'), + Column('started', type='int'), Column('stopped', type='int') + ], + ] + + other_tables = [ + 'bitten_log', + 'bitten_log_message', + 'bitten_report', + 'bitten_report_item', + 'bitten_error', + 'old_step', + 'old_config', + 'old_log_v5', + 'old_log_v8', + 'old_rule', + ] + + basic_data = [ + ['system', + ('name', 'value'), [ + ('bitten_version', '1'), + ] + ], + ['bitten_config', + ('name',), [ + ('test_config',), + ] + ], + ['bitten_platform', + ('config', 'name'), [ + ('test_config', 'test_plat'), + ] + ], + ['bitten_build', + ('config', 'rev', 'platform', 'rev_time'), [ + ('test_config', '123', 1, 456), + ] + ], + ['bitten_step', + ('build', 'name', 'log'), [ + (1, 'step1', None), + (1, 'step2', "line1\nline2"), + ] + ], + ] + + def _do_upgrade(self): + """Do an full upgrade.""" + import inspect + db = self.env.get_db_cnx() + + versions = sorted(upgrades.map.keys()) + for version in versions: + for function in upgrades.map.get(version): + self.assertTrue(inspect.getdoc(function)) + function(self.env, db) + + db.commit() + + def _insert_data(self, data): + """Insert data for upgrading.""" + db = self.env.get_db_cnx() + cursor = db.cursor() + + for table, cols, vals in data: + cursor.executemany("INSERT INTO %s (%s) VALUES (%s)" + % (table, ','.join(cols), + ','.join(['%s' for c in cols])), + vals) + + db.commit() + + def _check_basic_upgrade(self): + """Check the results of an upgrade of basic data.""" + db = self.env.get_db_cnx() + + configs = list(model.BuildConfig.select(self.env, + include_inactive=True)) + platforms = list(model.TargetPlatform.select(self.env)) + builds = list(model.Build.select(self.env)) + steps = list(model.BuildStep.select(self.env)) + logs = list(model.BuildLog.select(self.env)) + + self.assertEqual(len(configs), 1) + self.assertEqual(configs[0].name, 'test_config') + + self.assertEqual(len(platforms), 1) + self.assertEqual(platforms[0].config, 'test_config') + self.assertEqual(platforms[0].name, 'test_plat') + + self.assertEqual(len(builds), 1) + self.assertEqual(builds[0].config, 'test_config') + self.assertEqual(builds[0].rev, '123') + self.assertEqual(builds[0].platform, 1) + self.assertEqual(builds[0].rev_time, 456) + + self.assertEqual(len(steps), 2) + self.assertEqual(steps[0].build, 1) + self.assertEqual(steps[0].name, 'step1') + self.assertEqual(steps[1].build, 1) + self.assertEqual(steps[1].name, 'step2') + + self.assertEqual(len(logs), 1) + self.assertEqual(logs[0].build, 1) + self.assertEqual(logs[0].step, 'step2') + log_file = logs[0].get_log_file(logs[0].filename) + self.assertEqual(file(log_file, "rU").read(), "line1\nline2\n") + + def test_null_upgrade(self): + self._do_upgrade() + + def test_basic_upgrade(self): + self._insert_data(self.basic_data) + self._do_upgrade() + self._check_basic_upgrade() + + def test_upgrade_via_buildsetup(self): + self._insert_data(self.basic_data) + db = self.env.get_db_cnx() + build_setup = main.BuildSetup(self.env) + self.assertTrue(build_setup.environment_needs_upgrade(db)) + build_setup.upgrade_environment(db) + self._check_basic_upgrade() + + # check bitten table version + cursor = db.cursor() + cursor.execute("SELECT value FROM system WHERE name='bitten_version'") + rows = cursor.fetchall() + self.assertEqual(rows, [(str(model.schema_version),)]) + + +def suite(): + suite = unittest.TestSuite() + suite.addTest(unittest.makeSuite(UpgradeHelperTestCase, 'test')) + suite.addTest(unittest.makeSuite(UpgradeScriptsTestCase, 'test')) + return suite + +if __name__ == '__main__': + unittest.main(defaultTest='suite') diff --git a/bitten/upgrades.py b/bitten/upgrades.py --- a/bitten/upgrades.py +++ b/bitten/upgrades.py @@ -21,26 +21,85 @@ __docformat__ = 'restructuredtext en' +# database abstraction functions + +def _parse_scheme(env): + """Retrieve the environment database scheme.""" + connection_uri = DatabaseManager(env).connection_uri + parts = connection_uri.split(':', 1) + scheme = parts[0].lower() + return scheme + +def update_sequence(env, db, tbl, col): + """Update a sequence associated with an autoincrement column.""" + # Hopefully Trac will eventually implement its own version + # of this function. + scheme = _parse_scheme(env) + if scheme == "postgres": + seq = '%s_%s_seq' % (tbl, col) + cursor = db.cursor() + cursor.execute("SELECT setval('%s', (SELECT MAX(%s) FROM %s))" + % (seq, col, tbl)) + +def drop_index(env, db, tbl, idx): + """Drop an index associated with a table.""" + # Hopefully Trac will eventually implement its own version + # of this function. + scheme = _parse_scheme(env) + cursor = db.cursor() + if scheme == "mysql": + cursor.execute("DROP INDEX %s ON %s" % (idx, tbl)) + else: + cursor.execute("DROP INDEX %s" % (idx,)) + +# upgrade scripts + def add_log_table(env, db): """Add a table for storing the builds logs.""" - from bitten.model import BuildLog, BuildStep + from trac.db import Table, Column + INFO_LEVEL = 'I' + cursor = db.cursor() + build_log_schema_v3 = [ + Table('bitten_log', key='id')[ + Column('id', auto_increment=True), Column('build', type='int'), + Column('step'), Column('type') + ], + Table('bitten_log_message', key=('log', 'line'))[ + Column('log', type='int'), Column('line', type='int'), + Column('level', size=1), Column('message') + ] + ] + + build_step_schema_v3 = [ + Table('bitten_step', key=('build', 'name'))[ + Column('build', type='int'), Column('name'), Column('description'), + Column('status', size=1), Column('started', type='int'), + Column('stopped', type='int') + ] + ] + connector, _ = DatabaseManager(env)._get_connector() - for table in BuildLog._schema: + for table in build_log_schema_v3: for stmt in connector.to_sql(table): cursor.execute(stmt) + update_cursor = db.cursor() cursor.execute("SELECT build,name,log FROM bitten_step " "WHERE log IS NOT NULL") for build, step, log in cursor: - build_log = BuildLog(env, build, step) - build_log.messages = [(BuildLog.INFO, msg) for msg in log.splitlines()] - build_log.insert(db) + update_cursor.execute("INSERT INTO bitten_log (build, step) " + "VALUES (%s,%s)", (build, step)) + log_id = db.get_last_id(update_cursor, 'bitten_log') + messages = [(log_id, line, INFO_LEVEL, msg) + for line, msg in enumerate(log.splitlines())] + update_cursor.executemany("INSERT INTO bitten_log_message (log, line, level, message) " + "VALUES (%s, %s, %s, %s)", messages) cursor.execute("CREATE TEMPORARY TABLE old_step AS SELECT * FROM bitten_step") cursor.execute("DROP TABLE bitten_step") - for table in BuildStep._schema: + for table in build_step_schema_v3: for stmt in connector.to_sql(table): cursor.execute(stmt) cursor.execute("INSERT INTO bitten_step (build,name,description,status," @@ -117,7 +176,7 @@ from bitten.model import BuildLog cursor = db.cursor() - cursor.execute("CREATE TEMPORARY TABLE old_log AS " + cursor.execute("CREATE TEMPORARY TABLE old_log_v5 AS " "SELECT * FROM bitten_log") cursor.execute("DROP TABLE bitten_log") @@ -126,7 +185,7 @@ cursor.execute(stmt) cursor.execute("INSERT INTO bitten_log (id,build,step,generator,orderno) " - "SELECT id,build,step,type,0 FROM old_log") + "SELECT id,build,step,type,0 FROM old_log_v5") def add_report_tables(env, db): """Add database tables for report storage.""" @@ -289,7 +348,7 @@ from bitten.model import BuildLog cursor = db.cursor() - cursor.execute("CREATE TEMPORARY TABLE old_log AS " + cursor.execute("CREATE TEMPORARY TABLE old_log_v8 AS " "SELECT * FROM bitten_log") cursor.execute("DROP TABLE bitten_log") @@ -298,7 +357,7 @@ cursor.execute(stmt) cursor.execute("INSERT INTO bitten_log (id,build,step,generator,orderno,filename) " - "SELECT id,build,step,generator,orderno,'' FROM old_log") + "SELECT id,build,step,generator,orderno,'' FROM old_log_v8") def migrate_logs_to_files(env, db): """Migrates logs that are stored in the bitten_log_messages table into files.""" @@ -309,7 +368,7 @@ if not os.path.isabs(logs_dir): logs_dir = os.path.join(env.path, logs_dir) if not os.path.exists(logs_dir): - os.mkdir(logs_dir) + os.makedirs(logs_dir) message_table = Table('bitten_log_message', key=('log', 'line'))[ Column('log', type='int'), Column('line', type='int'), @@ -345,7 +404,6 @@ def recreate_rule_with_int_id(env, db): """Recreates the bitten_rule table with an integer id column rather than a text one.""" - from trac.db import Table, Column from bitten.model import TargetPlatform cursor = db.cursor() connector, _ = DatabaseManager(env)._get_connector() @@ -393,11 +451,27 @@ if not duplicates_exist: cursor = db.cursor() - cursor.execute("CREATE UNIQUE INDEX bitten_build_config_rev_platform_idx ON bitten_build (config,rev,platform)") - cursor.execute("DROP INDEX bitten_build_config_rev_slave_idx") + scheme = _parse_scheme(env) + if scheme == "mysql": + # 111 = 333 / len(columns in index) -- this is the Trac default + cursor.execute("CREATE UNIQUE INDEX bitten_build_config_rev_platform_idx ON bitten_build (config(111), rev(111), platform)") + else: + cursor.execute("CREATE UNIQUE INDEX bitten_build_config_rev_platform_idx ON bitten_build (config,rev,platform)") + drop_index(env, db, 'bitten_build', 'bitten_build_config_rev_slave_idx') else: raise TracError('') +def fix_sequences(env, db): + """Fixes any auto increment sequences that might have been left in an inconsistent state. + + Upgrade scripts for schema versions > 10 should handle sequence updates correctly themselves. + """ + update_sequence(env, db, 'bitten_build', 'id') + update_sequence(env, db, 'bitten_log', 'id') + update_sequence(env, db, 'bitten_platform', 'id') + update_sequence(env, db, 'bitten_report', 'id') + + map = { 2: [add_log_table], 3: [add_recipe_to_config], @@ -407,5 +481,5 @@ 7: [add_error_table], 8: [add_filename_to_logs,migrate_logs_to_files], 9: [recreate_rule_with_int_id], - 10: [add_config_platform_rev_index_to_build], + 10: [add_config_platform_rev_index_to_build, fix_sequences], }