blob: 3706b64f652a88c1cf6c659a0868ee8ce5857121 [file] [log] [blame]
-- 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');