tko/create_db-postgres

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

-- kernel versions
DROP SEQUENCE IF EXISTS kernel_idx_seq;
CREATE SEQUENCE kernel_idx_seq
	INCREMENT BY 1
	NO MAXVALUE
	NO MINVALUE
	CACHE 1;

DROP TABLE IF EXISTS kernels;
CREATE TABLE kernels (
kernel_idx int NOT NULL DEFAULT nextval('kernel_idx_seq'::TEXT) PRIMARY KEY,
kernel_hash TEXT,		-- Hash of base + all patches
base TEXT,			-- Base version without patches
printable TEXT			-- Full version with patches
);

-- main jobs table
DROP SEQUENCE IF EXISTS machine_idx_seq;
CREATE SEQUENCE machine_idx_seq
	INCREMENT BY 1
	NO MAXVALUE
	NO MINVALUE
	CACHE 1;

DROP TABLE IF EXISTS machines;
CREATE TABLE machines (
machine_idx int NOT NULL DEFAULT nextval('machine_idx_seq'::TEXT) PRIMARY KEY,
hostname TEXT,			-- hostname
machine_group TEXT,		-- group name
owner TEXT			-- owner name
);

-- main jobs table
DROP SEQUENCE IF EXISTS job_idx_seq;
CREATE SEQUENCE job_idx_seq
	INCREMENT BY 1
	NO MAXVALUE
	NO MINVALUE
	CACHE 1;

DROP TABLE IF EXISTS jobs;
CREATE TABLE jobs (
job_idx int NOT NULL DEFAULT nextval('job_idx_seq'::TEXT) PRIMARY KEY,
tag TEXT,			-- job key
label TEXT,                     -- job label assigned by user
username TEXT,			-- user name
machine_idx int			-- reference to machine table
);

-- One entry per patch used, anywhere
DROP TABLE IF EXISTS patches;
CREATE TABLE patches (
kernel_idx INTEGER,			-- index number
name TEXT,			-- short name
url TEXT,			-- full URL
hash TEXT
);

-- test functional results
DROP SEQUENCE IF EXISTS test_idx_seq;
CREATE SEQUENCE test_idx_seq
	INCREMENT BY 1
	NO MAXVALUE
	NO MINVALUE
	CACHE 1;

DROP TABLE IF EXISTS tests;
CREATE TABLE tests (
test_idx int NOT NULL DEFAULT nextval('test_idx_seq'::TEXT) PRIMARY KEY,
job_idx INTEGER,			-- ref to job table
test TEXT,			-- name of test
subdir TEXT,			-- subdirectory name
kernel_idx INTEGER,			-- kernel test was AGAINST
status INTEGER,				-- test status
reason TEXT,			-- reason for test status
machine_idx int			-- reference to machine table
);

-- test functional results
DROP TABLE IF EXISTS test_attributes;
CREATE TABLE test_attributes (
test_idx INTEGER,			-- ref to test table
attribute TEXT,			-- attribute name (e.g. 'throughput')
value TEXT			-- attribute value
);

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

-- status key
DROP SEQUENCE IF EXISTS status_idx_seq;
CREATE SEQUENCE status_idx_seq
	INCREMENT BY 1
	NO MAXVALUE
	NO MINVALUE
	CACHE 1;

DROP TABLE IF EXISTS status;
CREATE TABLE status (
status_idx int NOT NULL DEFAULT nextval('status_idx_seq'::TEXT) PRIMARY KEY,
word TEXT			-- status word
);

-- BRRD syncronization
DROP TABLE IF EXISTS brrd_sync;
CREATE TABLE brrd_sync (
test_idx INTEGER			-- ref to test table
);

-- 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, jobs.machine_idx,
       jobs.tag, jobs.label, jobs.username, machines.hostname,
       machines.machine_group, machines.owner, kernels.kernel_hash,
       kernels.base, kernels.printable
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;

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