add missing tko/migrations
Signed-off-by: Martin J. Bligh <mbligh@google.com>
git-svn-id: http://test.kernel.org/svn/autotest/trunk@1243 592f7852-d20e-0410-864c-8624ca9c26a4
diff --git a/tko/migrations/001_initial_db.py b/tko/migrations/001_initial_db.py
new file mode 100755
index 0000000..cf237d6
--- /dev/null
+++ b/tko/migrations/001_initial_db.py
@@ -0,0 +1,190 @@
+import os
+
+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
+ for table in required_tables:
+ if table not in tables:
+ db_initialized = False
+ break
+ if not db_initialized:
+ response = raw_input(
+ 'Your tko database does not appear to be initialized. Do '
+ 'you want to recreate it (this will result in loss of any '
+ 'existing data) (yes/No)? ')
+ if response != 'yes':
+ raise Exception('User has chosen to abort migration')
+
+ manager.execute_script(CREATE_DB_SQL)
+
+ manager.create_migrate_table()
+
+
+CREATE_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;
+
+DROP TABLE IF EXISTS brrd_sync;
+DROP TABLE IF EXISTS iteration_result;
+DROP TABLE IF EXISTS test_attributes;
+DROP TABLE IF EXISTS tests;
+DROP TABLE IF EXISTS patches;
+DROP TABLE IF EXISTS jobs;
+DROP TABLE IF EXISTS machines;
+DROP TABLE IF EXISTS kernels;
+DROP TABLE IF EXISTS status;
+
+-- status key
+CREATE TABLE status (
+status_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY , -- numerical status
+word VARCHAR(10) -- status word
+) TYPE=InnoDB;
+
+-- kernel versions
+CREATE TABLE kernels (
+kernel_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
+kernel_hash VARCHAR(35), -- Hash of base + all patches
+base VARCHAR(30), -- Base version without patches
+printable VARCHAR(100) -- Full version with patches
+) TYPE=InnoDB;
+
+-- machines/hosts table
+CREATE TABLE machines (
+machine_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
+hostname VARCHAR(100) unique KEY, -- hostname
+machine_group VARCHAR(80), -- group name
+owner VARCHAR(80) -- owner name
+) TYPE=InnoDB;
+
+-- main jobs table
+CREATE TABLE jobs (
+job_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY, -- index number
+tag VARCHAR(100) unique KEY, -- job key
+label VARCHAR(100), -- job label assigned by user
+KEY (label),
+username VARCHAR(80), -- user name
+KEY (username),
+machine_idx INT(10) unsigned NOT NULL, -- reference to machine table
+KEY (machine_idx),
+FOREIGN KEY (machine_idx) REFERENCES machines(machine_idx) ON DELETE CASCADE
+) TYPE=InnoDB;
+
+-- One entry per patch used, anywhere
+CREATE TABLE patches (
+kernel_idx INT(10) unsigned NOT NULL, -- index number
+name VARCHAR(80), -- short name
+url VARCHAR(300), -- full URL
+hash VARCHAR(35),
+KEY (kernel_idx),
+FOREIGN KEY (kernel_idx) REFERENCES kernels(kernel_idx) ON DELETE CASCADE
+) TYPE=InnoDB;
+
+-- test functional results
+CREATE TABLE tests (
+test_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY, -- index number
+job_idx INTEGER, -- ref to job table
+test VARCHAR(30), -- name of test
+subdir VARCHAR(60), -- subdirectory name
+kernel_idx INT(10) unsigned NOT NULL, -- kernel test was AGAINST
+KEY (kernel_idx),
+FOREIGN KEY (kernel_idx) REFERENCES kernels(kernel_idx) ON DELETE CASCADE,
+status int(10) unsigned NOT NULL, -- test status
+KEY (status),
+FOREIGN KEY (status) REFERENCES status(status_idx) ON DELETE CASCADE,
+reason VARCHAR(100), -- reason for test status
+machine_idx INT(10) unsigned NOT NULL, -- reference to machine table
+KEY (machine_idx),
+FOREIGN KEY (machine_idx) REFERENCES machines(machine_idx) ON DELETE CASCADE,
+invalid BOOL NOT NULL
+) TYPE=InnoDB;
+
+-- test attributes (key value pairs at a test level)
+CREATE TABLE test_attributes (
+test_idx int(10) unsigned NOT NULL, -- ref to test table
+FOREIGN KEY (test_idx) REFERENCES tests(test_idx) ON DELETE CASCADE,
+attribute VARCHAR(30), -- attribute name (e.g. 'version')
+value VARCHAR(100), -- attribute value
+KEY `test_idx` (`test_idx`)
+) TYPE=InnoDB;
+
+-- test performance results
+CREATE TABLE iteration_result(
+test_idx int(10) unsigned NOT NULL, -- ref to test table
+FOREIGN KEY (test_idx) REFERENCES tests(test_idx) ON DELETE CASCADE,
+iteration INTEGER, -- integer
+attribute VARCHAR(30), -- attribute name (e.g. 'throughput')
+value FLOAT, -- attribute value (eg 700.1)
+KEY `test_idx` (`test_idx`)
+) TYPE=InnoDB;
+
+-- BRRD syncronization
+CREATE TABLE brrd_sync (
+test_idx int(10) unsigned NOT NULL, -- ref to test table
+FOREIGN KEY (test_idx) REFERENCES tests(test_idx) ON DELETE CASCADE
+) TYPE=InnoDB;
+
+-- test_view (to make life easier for people trying to mine data)
+CREATE 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)
+CREATE 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;
+
+INSERT INTO status (word)
+VALUES ('NOSTATUS'), ('ERROR'), ('ABORT'), ('FAIL'), ('WARN'), ('GOOD'), ('ALERT');
+"""