diff options
| -rw-r--r-- | frontend/migrations/001_initial_db.py | 20 | ||||
| -rw-r--r-- | frontend/migrations/002_cleanup_fields.py | 7 | ||||
| -rwxr-xr-x | migrate/migrate.py | 88 | ||||
| -rwxr-xr-x | tko/migrations/001_initial_db.py | 10 | ||||
| -rw-r--r-- | tko/migrations/002_add_job_timestamps.py | 72 | ||||
| -rw-r--r-- | tko/migrations/003_add_test_timestamps.py | 78 |
6 files changed, 241 insertions, 34 deletions
diff --git a/frontend/migrations/001_initial_db.py b/frontend/migrations/001_initial_db.py index 0773ea06..f16e6ec7 100644 --- a/frontend/migrations/001_initial_db.py +++ b/frontend/migrations/001_initial_db.py @@ -6,7 +6,6 @@ required_tables = ('acl_groups', 'acl_groups_hosts', 'acl_groups_users', def migrate_up(manager): - assert not manager.check_migrate_table_exists() manager.execute("SHOW TABLES") tables = [row[0] for row in manager.cursor.fetchall()] db_initialized = True @@ -27,6 +26,10 @@ def migrate_up(manager): manager.create_migrate_table() +def migrate_down(manager): + manager.execute_script(DROP_DB_SQL) + + CREATE_DB_SQL = """\ -- -- Table structure for table `acl_groups` @@ -173,3 +176,18 @@ CREATE TABLE `users` ( PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; """ + + +DROP_DB_SQL = """\ +DROP TABLE IF EXISTS `acl_groups`; +DROP TABLE IF EXISTS `acl_groups_hosts`; +DROP TABLE IF EXISTS `acl_groups_users`; +DROP TABLE IF EXISTS `autotests`; +DROP TABLE IF EXISTS `host_queue_entries`; +DROP TABLE IF EXISTS `hosts`; +DROP TABLE IF EXISTS `hosts_labels`; +DROP TABLE IF EXISTS `ineligible_host_queues`; +DROP TABLE IF EXISTS `jobs`; +DROP TABLE IF EXISTS `labels`; +DROP TABLE IF EXISTS `users`; +""" diff --git a/frontend/migrations/002_cleanup_fields.py b/frontend/migrations/002_cleanup_fields.py index ccd8c1c9..a3656ca9 100644 --- a/frontend/migrations/002_cleanup_fields.py +++ b/frontend/migrations/002_cleanup_fields.py @@ -3,3 +3,10 @@ def migrate_up(manager): manager.execute('ALTER TABLE jobs DROP kernel_url, DROP status, ' 'DROP submitted_on') manager.execute('ALTER TABLE host_queue_entries DROP created_on') + +def migrate_down(manager): + manager.execute('ALTER TABLE autotests ADD params VARCHAR(255)') + manager.execute('ALTER TABLE jobs ADD kernel_url VARCHAR(255), ' + 'ADD status VARCHAR(255), ADD submitted_on datetime') + manager.execute('ALTER TABLE host_queue_entries ADD created_on ' + 'datetime') diff --git a/migrate/migrate.py b/migrate/migrate.py index b622a7ca..840b4e45 100755 --- a/migrate/migrate.py +++ b/migrate/migrate.py @@ -40,7 +40,6 @@ class MigrationManager(object): def read_db_info(self): # grab the config file and parse for info c = global_config.global_config - print "database = %s\n" % (self.database) self.db_host = c.get_config_value(self.database, "host") self.db_name = c.get_config_value(self.database, "database") self.username = c.get_config_value(self.database, "user") @@ -55,6 +54,7 @@ class MigrationManager(object): def open_connection(self): self.connection = self.connect(self.db_host, self.db_name, self.username, self.password) + self.connection.autocommit(True) self.cursor = self.connection.cursor() @@ -87,8 +87,11 @@ class MigrationManager(object): def create_migrate_table(self): - self.execute("CREATE TABLE %s (`version` integer)" % - MIGRATE_TABLE) + if not self.check_migrate_table_exists(): + self.execute("CREATE TABLE %s (`version` integer)" % + MIGRATE_TABLE) + else: + self.execute("DELETE FROM %s" % MIGRATE_TABLE) self.execute("INSERT INTO %s VALUES (0)" % MIGRATE_TABLE) assert self.cursor.rowcount == 1 @@ -105,11 +108,13 @@ class MigrationManager(object): return 0 self.execute("SELECT * FROM %s" % MIGRATE_TABLE) rows = self.cursor.fetchall() + if len(rows) == 0: + return 0 assert len(rows) == 1 and len(rows[0]) == 1 return rows[0][0] - def get_migrations(self, minimum_version=None): + def get_migrations(self, minimum_version=None, maximum_version=None): migrate_files = [filename for filename in os.listdir(self.migrations_dir) if re.match(r'^\d\d\d_.*\.py$', filename)] @@ -118,28 +123,46 @@ class MigrationManager(object): if minimum_version is not None: migrations = [migration for migration in migrations if migration.version >= minimum_version] + if maximum_version is not None: + migrations = [migration for migration in migrations + if migration.version <= maximum_version] return migrations - def do_migration(self, migration): - assert self.get_db_version() == migration.version - 1 - print 'Applying migration', migration.module + def do_migration(self, migration, migrate_up=True): + if migrate_up: + assert self.get_db_version() == migration.version - 1 + direction = 'up' + method_name = 'migrate_up' + new_version = migration.version + else: + assert self.get_db_version() == migration.version + direction = 'down' + method_name = 'migrate_down' + new_version = migration.version - 1 + print 'Applying migration %s %s' % (migration.module, direction) module_object = __import__(migration.module, globals(), locals(), []) - up_method = getattr(module_object, 'migrate_up', None) - assert up_method is not None - up_method(self) - self.set_db_version(migration.version) + method = getattr(module_object, method_name, None) + assert method is not None + method(self) + self.set_db_version(new_version) def migrate_to_version(self, version): current_version = self.get_db_version() - # no support for migrating down right now - assert current_version <= version + if current_version < version: + lower, upper = current_version, version + migrate_up = True + else: + lower, upper = version, current_version + migrate_up = False - migrations = self.get_migrations(current_version + 1) + migrations = self.get_migrations(lower + 1, upper) + if not migrate_up: + migrations.reverse() for migration in migrations: - self.do_migration(migration) + self.do_migration(migration, migrate_up) assert self.get_db_version() == version print 'At version', version @@ -178,22 +201,29 @@ class MigrationManager(object): 'db' : self.db_name}) - def do_sync_db(self): + def migrate_to_version_or_latest(self, version): + if version is None: + self.migrate_to_latest() + else: + self.migrate_to_version(version) + + + def do_sync_db(self, version=None): self.read_db_info() self.open_connection() print 'Migration starting for database', self.db_name - self.migrate_to_latest() + self.migrate_to_version_or_latest(version) print 'Migration complete' - def test_sync_db(self): + def test_sync_db(self, version=None): """\ Create a fresh DB and run all migrations on it. """ self.initialize_test_db() try: print 'Starting migration test on DB', self.db_name - self.migrate_to_latest() + self.migrate_to_version_or_latest(version) # show schema to the user os.system('mysqldump %s --no-data=true ' '--add-drop-table=false' % @@ -203,7 +233,7 @@ class MigrationManager(object): print 'Test finished successfully' - def simulate_sync_db(self): + def simulate_sync_db(self, version=None): """\ Create a fresh DB, copy the existing DB to it, and then try to synchronize it. @@ -222,13 +252,13 @@ class MigrationManager(object): os.remove(dump_file) try: print 'Starting migration test on DB', self.db_name - self.migrate_to_latest() + self.migrate_to_version_or_latest(version) finally: self.remove_test_db() print 'Test finished successfully' -USAGE = 'must specify one of [sync|test|simulate|safesync]' +USAGE = '%s sync|test|simulate|safesync [version]' % sys.argv[0] def main(): @@ -242,17 +272,21 @@ def main(): manager = MigrationManager(options.database) if len(args) > 0: + if len(args) > 1: + version = int(args[1]) + else: + version = None if args[0] == 'sync': - manager.do_sync_db() + manager.do_sync_db(version) elif args[0] == 'test': - manager.test_sync_db() + manager.test_sync_db(version) elif args[0] == 'simulate': - manager.simulate_sync_db() + manager.simulate_sync_db(version) elif args[0] == 'safesync': print 'Simluating migration' - manager.simulate_sync_db() + manager.simulate_sync_db(version) print 'Performing real migration' - manager.do_sync_db() + manager.do_sync_db(version) else: print USAGE return diff --git a/tko/migrations/001_initial_db.py b/tko/migrations/001_initial_db.py index cf237d68..2c19abc1 100755 --- a/tko/migrations/001_initial_db.py +++ b/tko/migrations/001_initial_db.py @@ -4,7 +4,6 @@ required_tables = ('machines', 'jobs', 'patches', 'tests', 'test_attributes', 'iteration_result') def migrate_up(manager): - assert not manager.check_migrate_table_exists() manager.execute("SHOW TABLES") tables = [row[0] for row in manager.cursor.fetchall()] db_initialized = True @@ -25,7 +24,11 @@ def migrate_up(manager): manager.create_migrate_table() -CREATE_DB_SQL = """\ +def migrate_down(manager): + manager.execute_script(DROP_DB_SQL) + + +DROP_DB_SQL = """\ -- drop all views (since they depend on some or all of the following tables) DROP VIEW IF EXISTS test_view; DROP VIEW IF EXISTS perf_view; @@ -39,7 +42,10 @@ DROP TABLE IF EXISTS jobs; DROP TABLE IF EXISTS machines; DROP TABLE IF EXISTS kernels; DROP TABLE IF EXISTS status; +""" + +CREATE_DB_SQL = DROP_DB_SQL + """\ -- status key CREATE TABLE status ( status_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY , -- numerical status diff --git a/tko/migrations/002_add_job_timestamps.py b/tko/migrations/002_add_job_timestamps.py index 73a1cccc..8bbb83a3 100644 --- a/tko/migrations/002_add_job_timestamps.py +++ b/tko/migrations/002_add_job_timestamps.py @@ -1,6 +1,11 @@ def migrate_up(manager): manager.execute_script(ADD_COLUMNS_SQL) - manager.execute_script(ALTER_VIEWS_SQL) + manager.execute_script(ALTER_VIEWS_UP_SQL) + + +def migrate_down(manager): + manager.execute_script(DROP_COLUMNS_SQL) + manager.execute_script(ALTER_VIEWS_DOWN_SQL) ADD_COLUMNS_SQL = """\ @@ -10,7 +15,12 @@ ALTER TABLE jobs ADD COLUMN finished_time datetime NULL; """ -ALTER_VIEWS_SQL = """\ +DROP_COLUMNS_SQL = """\ +ALTER TABLE jobs DROP queued_time, DROP started_time, DROP finished_time; +""" + + +ALTER_VIEWS_UP_SQL = """\ ALTER VIEW test_view AS SELECT tests.test_idx, tests.job_idx, @@ -72,3 +82,61 @@ INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx INNER JOIN status ON status.status_idx = tests.status INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx; """ + + +ALTER_VIEWS_DOWN_SQL = """\ +ALTER VIEW test_view AS +SELECT tests.test_idx, + tests.job_idx, + tests.test, + tests.subdir, + tests.kernel_idx, + tests.status, + tests.reason, + tests.machine_idx, + jobs.tag AS job_tag, + jobs.label AS job_label, + jobs.username AS job_username, + machines.hostname AS machine_hostname, + machines.machine_group, + machines.owner AS machine_owner, + kernels.kernel_hash, + kernels.base AS kernel_base, + kernels.printable AS kernel_printable, + status.word AS status_word +FROM tests +INNER JOIN jobs ON jobs.job_idx = tests.job_idx +INNER JOIN machines ON machines.machine_idx = jobs.machine_idx +INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx +INNER JOIN status ON status.status_idx = tests.status; + +-- perf_view (to make life easier for people trying to mine performance data) +ALTER VIEW perf_view AS +SELECT tests.test_idx, + tests.job_idx, + tests.test, + tests.subdir, + tests.kernel_idx, + tests.status, + tests.reason, + tests.machine_idx, + jobs.tag AS job_tag, + jobs.label AS job_label, + jobs.username AS job_username, + machines.hostname AS machine_hostname, + machines.machine_group, + machines.owner AS machine_owner, + kernels.kernel_hash, + kernels.base AS kernel_base, + kernels.printable AS kernel_printable, + status.word AS status_word, + iteration_result.iteration, + iteration_result.attribute AS iteration_key, + iteration_result.value AS iteration_value +FROM tests +INNER JOIN jobs ON jobs.job_idx = tests.job_idx +INNER JOIN machines ON machines.machine_idx = jobs.machine_idx +INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx +INNER JOIN status ON status.status_idx = tests.status +INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx; +""" diff --git a/tko/migrations/003_add_test_timestamps.py b/tko/migrations/003_add_test_timestamps.py index 9055f50f..e9148cb4 100644 --- a/tko/migrations/003_add_test_timestamps.py +++ b/tko/migrations/003_add_test_timestamps.py @@ -1,12 +1,22 @@ def migrate_up(manager): manager.execute_script(ADD_COLUMN_SQL) - manager.execute_script(ALTER_VIEWS_SQL) + manager.execute_script(ALTER_VIEWS_UP_SQL) + + +def migrate_down(manager): + manager.execute_script(DROP_COLUMN_SQL) + manager.execute_script(ALTER_VIEWS_DOWN_SQL) + ADD_COLUMN_SQL = """\ ALTER TABLE tests ADD COLUMN finished_time datetime NULL; """ -ALTER_VIEWS_SQL = """\ +DROP_COLUMN_SQL = """\ +ALTER TABLE tests DROP finished_time; +""" + +ALTER_VIEWS_UP_SQL = """\ ALTER VIEW test_view AS SELECT tests.test_idx, tests.job_idx, @@ -70,3 +80,67 @@ INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx INNER JOIN status ON status.status_idx = tests.status INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx; """ + + +ALTER_VIEWS_DOWN_SQL = """\ +ALTER VIEW test_view AS +SELECT tests.test_idx, + tests.job_idx, + tests.test, + tests.subdir, + tests.kernel_idx, + tests.status, + tests.reason, + tests.machine_idx, + jobs.tag AS job_tag, + jobs.label AS job_label, + jobs.username AS job_username, + jobs.queued_time AS job_queued_time, + jobs.started_time AS job_started_time, + jobs.finished_time AS job_finished_time, + machines.hostname AS machine_hostname, + machines.machine_group, + machines.owner AS machine_owner, + kernels.kernel_hash, + kernels.base AS kernel_base, + kernels.printable AS kernel_printable, + status.word AS status_word +FROM tests +INNER JOIN jobs ON jobs.job_idx = tests.job_idx +INNER JOIN machines ON machines.machine_idx = jobs.machine_idx +INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx +INNER JOIN status ON status.status_idx = tests.status; + +-- perf_view (to make life easier for people trying to mine performance data) +ALTER VIEW perf_view AS +SELECT tests.test_idx, + tests.job_idx, + tests.test, + tests.subdir, + tests.kernel_idx, + tests.status, + tests.reason, + tests.machine_idx, + jobs.tag AS job_tag, + jobs.label AS job_label, + jobs.username AS job_username, + jobs.queued_time AS job_queued_time, + jobs.started_time AS job_started_time, + jobs.finished_time AS job_finished_time, + machines.hostname AS machine_hostname, + machines.machine_group, + machines.owner AS machine_owner, + kernels.kernel_hash, + kernels.base AS kernel_base, + kernels.printable AS kernel_printable, + status.word AS status_word, + iteration_result.iteration, + iteration_result.attribute AS iteration_key, + iteration_result.value AS iteration_value +FROM tests +INNER JOIN jobs ON jobs.job_idx = tests.job_idx +INNER JOIN machines ON machines.machine_idx = jobs.machine_idx +INNER JOIN kernels ON kernels.kernel_idx = tests.kernel_idx +INNER JOIN status ON status.status_idx = tests.status +INNER JOIN iteration_result ON iteration_result.test_idx = tests.kernel_idx; +""" |