tko/create_db

-- drop all views (since they depend on some or all of the following tables)
DROP VIEW IF EXISTS test_view;

-- kernel versions
DROP TABLE IF EXISTS kernels;
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
DROP TABLE IF EXISTS machines;
CREATE TABLE machines (
machine_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
hostname VARCHAR(100),			-- hostname
machine_group VARCHAR(80),		-- group name
owner VARCHAR(80)			-- owner name
) TYPE=InnoDB;

-- main jobs table
DROP TABLE IF EXISTS jobs;
CREATE TABLE jobs (
job_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY,		-- index number
tag VARCHAR(100),			-- job key
label VARCHAR(100),                     -- job label assigned by user
username VARCHAR(80),			-- user name
machine_idx int(10)			-- reference to machine table
) TYPE=InnoDB;

-- One entry per patch used, anywhere
DROP TABLE IF EXISTS patches;
CREATE TABLE patches (
kernel_idx INTEGER,			-- index number
name VARCHAR(80),			-- short name
url VARCHAR(300),			-- full URL
hash VARCHAR(35)
) TYPE=InnoDB;

-- test functional results
DROP TABLE IF EXISTS tests;
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 INTEGER,			-- kernel test was AGAINST
status INTEGER,				-- test status
reason VARCHAR(100),			-- reason for test status
machine_idx int(10)			-- reference to machine table
) TYPE=InnoDB;

-- test functional results
DROP TABLE IF EXISTS test_attributes;
CREATE TABLE test_attributes (
test_idx INTEGER,			-- ref to test table
attribute VARCHAR(30),			-- attribute name (e.g. 'throughput')
value VARCHAR(100)			-- attribute value
) TYPE=InnoDB;

-- test functional results
DROP TABLE IF EXISTS iteration_result;
CREATE TABLE iteration_result(
test_idx INTEGER,			-- ref to test table
iteration INTEGER,			-- integer
attribute VARCHAR(30),			-- attribute name (e.g. 'throughput')
value FLOAT				-- attribute value (eg 700.1)
) TYPE=InnoDB;

-- status key
DROP TABLE IF EXISTS status;
CREATE TABLE status (
status_idx int(10) unsigned NOT NULL auto_increment PRIMARY KEY ,		-- numerical status
word VARCHAR(10)			-- status word
) TYPE=InnoDB;

-- BRRD syncronization
DROP TABLE IF EXISTS brrd_sync;
CREATE TABLE brrd_sync (
test_idx INTEGER			-- ref to test table
) 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;


INSERT INTO status (word)
VALUES ('NOSTATUS'), ('ERROR'), ('ABORT'), ('FAIL'), ('WARN'), ('GOOD'), ('ALERT');