Add downgrade support to migrate. This is really necessary for doing real migration development. Modified all existing migrations to support it.

Signed-off-by: Steve Howard <showard@google.com>



git-svn-id: svn://test.kernel.org/autotest/trunk@1336 592f7852-d20e-0410-864c-8624ca9c26a4

3 files changed, 154 insertions, 6 deletions

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;
+"""