1 DROP TABLE run_iterations CASCADE;
2 DROP TABLE test_runs CASCADE;
3 DROP TABLE test_configurations CASCADE;
4 DROP TYPE test_configuration_type CASCADE;
5 DROP TABLE aggregators CASCADE;
6 DROP TABLE build_revisions CASCADE;
7 DROP TABLE builds CASCADE;
8 DROP TABLE builders CASCADE;
9 DROP TABLE repositories CASCADE;
10 DROP TABLE platforms CASCADE;
11 DROP TABLE test_metrics CASCADE;
12 DROP TABLE tests CASCADE;
13 DROP TABLE jobs CASCADE;
14 DROP TABLE reports CASCADE;
15 DROP TABLE tracker_repositories CASCADE;
16 DROP TABLE bug_trackers CASCADE;
18 CREATE TABLE platforms (
19 platform_id serial PRIMARY KEY,
20 platform_name varchar(64) NOT NULL,
21 platform_hidden boolean NOT NULL DEFAULT FALSE);
23 CREATE TABLE repositories (
24 repository_id serial PRIMARY KEY,
25 repository_name varchar(64) NOT NULL,
26 repository_url varchar(1024),
27 repository_blame_url varchar(1024));
29 CREATE TABLE bug_trackers (
30 tracker_id serial PRIMARY KEY,
31 tracker_name varchar(64) NOT NULL,
32 tracker_new_bug_url varchar(1024));
34 CREATE TABLE tracker_repositories (
35 tracrepo_tracker integer NOT NULL REFERENCES bug_trackers ON DELETE CASCADE,
36 tracrepo_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE);
38 CREATE TABLE builders (
39 builder_id serial PRIMARY KEY,
40 builder_name varchar(64) NOT NULL UNIQUE,
41 builder_password_hash character(64) NOT NULL,
42 builder_build_url varchar(1024));
45 build_id serial PRIMARY KEY,
46 build_builder integer REFERENCES builders ON DELETE CASCADE,
47 build_number integer NOT NULL,
48 build_time timestamp NOT NULL,
49 build_latest_revision timestamp,
50 CONSTRAINT builder_build_time_tuple_must_be_unique UNIQUE(build_builder, build_number, build_time));
51 CREATE INDEX build_builder_index ON builds(build_builder);
53 CREATE TABLE build_revisions (
54 revision_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
55 revision_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE,
56 revision_value varchar(64) NOT NULL,
57 revision_time timestamp,
58 PRIMARY KEY (revision_repository, revision_build));
59 CREATE INDEX revision_build_index ON build_revisions(revision_build);
60 CREATE INDEX revision_repository_index ON build_revisions(revision_repository);
62 CREATE TABLE aggregators (
63 aggregator_id serial PRIMARY KEY,
64 aggregator_name varchar(64),
65 aggregator_definition text);
68 test_id serial PRIMARY KEY,
69 test_name varchar(255) NOT NULL,
70 test_parent integer REFERENCES tests ON DELETE CASCADE,
71 test_url varchar(1024) DEFAULT NULL,
72 CONSTRAINT parent_test_must_be_unique UNIQUE(test_parent, test_name));
74 CREATE TABLE test_metrics (
75 metric_id serial PRIMARY KEY,
76 metric_test integer NOT NULL REFERENCES tests ON DELETE CASCADE,
77 metric_name varchar(64) NOT NULL,
78 metric_aggregator integer REFERENCES aggregators ON DELETE CASCADE);
80 CREATE TYPE test_configuration_type as ENUM ('current', 'baseline', 'target');
81 CREATE TABLE test_configurations (
82 config_id serial PRIMARY KEY,
83 config_metric integer NOT NULL REFERENCES test_metrics ON DELETE CASCADE,
84 config_platform integer NOT NULL REFERENCES platforms ON DELETE CASCADE,
85 config_type test_configuration_type NOT NULL,
86 config_is_in_dashboard boolean NOT NULL DEFAULT FALSE,
87 CONSTRAINT configuration_must_be_unique UNIQUE(config_metric, config_platform, config_type));
88 CREATE INDEX config_platform_index ON test_configurations(config_platform);
90 CREATE TABLE test_runs (
91 run_id serial PRIMARY KEY,
92 run_config integer NOT NULL REFERENCES test_configurations ON DELETE CASCADE,
93 run_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
94 run_iteration_count_cache smallint,
95 run_mean_cache double precision,
96 run_sum_cache double precision,
97 run_square_sum_cache double precision,
98 CONSTRAINT test_config_build_must_be_unique UNIQUE(run_config, run_build));
99 CREATE INDEX run_config_index ON test_runs(run_config);
100 CREATE INDEX run_build_index ON test_runs(run_build);
102 CREATE TABLE run_iterations (
103 iteration_run integer NOT NULL REFERENCES test_runs ON DELETE CASCADE,
104 iteration_order smallint NOT NULL CHECK(iteration_order >= 0),
105 iteration_group smallint CHECK(iteration_group >= 0),
106 iteration_value double precision,
107 iteration_relative_time float,
108 PRIMARY KEY (iteration_run, iteration_order));
111 job_id serial PRIMARY KEY,
112 job_type varchar(64) NOT NULL,
113 job_created_at timestamp NOT NULL DEFAULT NOW(),
114 job_started_at timestamp,
115 job_started_by_pid integer,
116 job_completed_at timestamp,
117 job_attempts integer NOT NULL DEFAULT 0,
121 CREATE TABLE reports (
122 report_id serial PRIMARY KEY,
123 report_builder integer NOT NULL REFERENCES builders ON DELETE RESTRICT,
124 report_build_number integer,
125 report_build integer REFERENCES builds,
126 report_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
127 report_committed_at timestamp,
129 report_failure varchar(64),
130 report_failure_details text);