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 builds CASCADE;
7 DROP TABLE committers CASCADE;
8 DROP TABLE commits CASCADE;
9 DROP TABLE build_commits CASCADE;
10 DROP TABLE build_slaves CASCADE;
11 DROP TABLE builders CASCADE;
12 DROP TABLE repositories CASCADE;
13 DROP TABLE platforms CASCADE;
14 DROP TABLE test_metrics CASCADE;
15 DROP TABLE tests CASCADE;
16 DROP TABLE reports CASCADE;
17 DROP TABLE tracker_repositories CASCADE;
18 DROP TABLE bug_trackers CASCADE;
19 DROP TABLE analysis_tasks CASCADE;
20 DROP TABLE build_triggerables CASCADE;
21 DROP TABLE triggerable_configurations CASCADE;
22 DROP TABLE triggerable_repositories CASCADE;
23 DROP TABLE bugs CASCADE;
24 DROP TABLE analysis_test_groups CASCADE;
25 DROP TABLE root_sets CASCADE;
26 DROP TABLE roots CASCADE;
27 DROP TABLE build_requests CASCADE;
28 DROP TYPE build_request_status_type CASCADE;
31 CREATE TABLE platforms (
32 platform_id serial PRIMARY KEY,
33 platform_name varchar(64) NOT NULL,
34 platform_hidden boolean NOT NULL DEFAULT FALSE);
36 CREATE TABLE repositories (
37 repository_id serial PRIMARY KEY,
38 repository_parent integer REFERENCES repositories ON DELETE CASCADE,
39 repository_name varchar(64) NOT NULL,
40 repository_url varchar(1024),
41 repository_blame_url varchar(1024),
42 CONSTRAINT repository_name_must_be_unique UNIQUE(repository_parent, repository_name));
44 CREATE TABLE bug_trackers (
45 tracker_id serial PRIMARY KEY,
46 tracker_name varchar(64) NOT NULL,
47 tracker_bug_url varchar(1024),
48 tracker_new_bug_url varchar(1024));
50 CREATE TABLE tracker_repositories (
51 tracrepo_tracker integer NOT NULL REFERENCES bug_trackers ON DELETE CASCADE,
52 tracrepo_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE);
54 CREATE TABLE builders (
55 builder_id serial PRIMARY KEY,
56 builder_name varchar(64) NOT NULL UNIQUE,
57 builder_password_hash character(64),
58 builder_build_url varchar(1024));
60 CREATE TABLE build_slaves (
61 slave_id serial PRIMARY KEY,
62 slave_name varchar(64) NOT NULL UNIQUE,
63 slave_password_hash character(64));
66 build_id serial PRIMARY KEY,
67 build_builder integer REFERENCES builders ON DELETE CASCADE,
68 build_slave integer REFERENCES build_slaves ON DELETE CASCADE,
69 build_number integer NOT NULL,
70 build_time timestamp NOT NULL,
71 build_latest_revision timestamp,
72 CONSTRAINT builder_build_time_tuple_must_be_unique UNIQUE(build_builder, build_number, build_time));
73 CREATE INDEX build_builder_index ON builds(build_builder);
75 CREATE TABLE committers (
76 committer_id serial PRIMARY KEY,
77 committer_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE,
78 committer_account varchar(320) NOT NULL,
79 committer_name varchar(128),
80 CONSTRAINT committer_in_repository_must_be_unique UNIQUE(committer_repository, committer_account));
81 CREATE INDEX committer_account_index ON committers(committer_account);
82 CREATE INDEX committer_name_index ON committers(committer_name);
84 CREATE TABLE commits (
85 commit_id serial PRIMARY KEY,
86 commit_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE,
87 commit_revision varchar(64) NOT NULL,
88 commit_parent integer REFERENCES commits ON DELETE CASCADE,
89 commit_time timestamp,
90 commit_committer integer REFERENCES committers ON DELETE CASCADE,
92 commit_reported boolean NOT NULL DEFAULT FALSE,
93 CONSTRAINT commit_in_repository_must_be_unique UNIQUE(commit_repository, commit_revision));
94 CREATE INDEX commit_time_index ON commits(commit_time);
96 CREATE TABLE build_commits (
97 commit_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
98 build_commit integer NOT NULL REFERENCES commits ON DELETE CASCADE,
99 PRIMARY KEY (commit_build, build_commit));
101 CREATE TABLE aggregators (
102 aggregator_id serial PRIMARY KEY,
103 aggregator_name varchar(64),
104 aggregator_definition text);
107 test_id serial PRIMARY KEY,
108 test_name varchar(255) NOT NULL,
109 test_parent integer REFERENCES tests ON DELETE CASCADE,
110 test_url varchar(1024) DEFAULT NULL,
111 CONSTRAINT parent_test_must_be_unique UNIQUE(test_parent, test_name));
113 CREATE TABLE test_metrics (
114 metric_id serial PRIMARY KEY,
115 metric_test integer NOT NULL REFERENCES tests ON DELETE CASCADE,
116 metric_name varchar(64) NOT NULL,
117 metric_aggregator integer REFERENCES aggregators ON DELETE CASCADE);
119 CREATE TYPE test_configuration_type as ENUM ('current', 'baseline', 'target');
120 CREATE TABLE test_configurations (
121 config_id serial PRIMARY KEY,
122 config_metric integer NOT NULL REFERENCES test_metrics ON DELETE CASCADE,
123 config_platform integer NOT NULL REFERENCES platforms ON DELETE CASCADE,
124 config_type test_configuration_type NOT NULL,
125 config_is_in_dashboard boolean NOT NULL DEFAULT FALSE,
126 config_runs_last_modified timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
127 CONSTRAINT configuration_must_be_unique UNIQUE(config_metric, config_platform, config_type));
128 CREATE INDEX config_platform_index ON test_configurations(config_platform);
130 CREATE TABLE test_runs (
131 run_id serial PRIMARY KEY,
132 run_config integer NOT NULL REFERENCES test_configurations ON DELETE CASCADE,
133 run_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
134 run_iteration_count_cache smallint,
135 run_mean_cache double precision,
136 run_sum_cache double precision,
137 run_square_sum_cache double precision,
138 CONSTRAINT test_config_build_must_be_unique UNIQUE(run_config, run_build));
139 CREATE INDEX run_config_index ON test_runs(run_config);
140 CREATE INDEX run_build_index ON test_runs(run_build);
142 CREATE TABLE run_iterations (
143 iteration_run integer NOT NULL REFERENCES test_runs ON DELETE CASCADE,
144 iteration_order smallint NOT NULL CHECK(iteration_order >= 0),
145 iteration_group smallint CHECK(iteration_group >= 0),
146 iteration_value double precision,
147 iteration_relative_time float,
148 PRIMARY KEY (iteration_run, iteration_order));
150 CREATE OR REPLACE FUNCTION update_config_last_modified() RETURNS TRIGGER AS $update_config_last_modified$
152 IF TG_OP != 'DELETE' THEN
153 UPDATE test_configurations SET config_runs_last_modified = (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') WHERE config_id = NEW.run_config;
155 UPDATE test_configurations SET config_runs_last_modified = (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') WHERE config_id = OLD.run_config;
159 $update_config_last_modified$ LANGUAGE plpgsql;
161 CREATE TRIGGER update_config_last_modified AFTER INSERT OR UPDATE OR DELETE ON test_runs
162 FOR EACH ROW EXECUTE PROCEDURE update_config_last_modified();
164 CREATE TABLE reports (
165 report_id serial PRIMARY KEY,
166 report_builder integer NOT NULL REFERENCES builders ON DELETE RESTRICT,
167 report_slave integer REFERENCES build_slaves ON DELETE RESTRICT,
168 report_build_number integer,
169 report_build integer REFERENCES builds,
170 report_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
171 report_committed_at timestamp,
173 report_failure varchar(64),
174 report_failure_details text);
176 CREATE TABLE analysis_tasks (
177 task_id serial PRIMARY KEY,
178 task_name varchar(256) NOT NULL,
179 task_author varchar(256),
180 task_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
181 task_platform integer REFERENCES platforms NOT NULL,
182 task_metric integer REFERENCES test_metrics NOT NULL,
183 task_start_run integer REFERENCES test_runs,
184 task_end_run integer REFERENCES test_runs,
185 CONSTRAINT analysis_task_should_be_unique_for_range UNIQUE(task_start_run, task_end_run),
186 CONSTRAINT analysis_task_should_not_be_associated_with_single_run
187 CHECK ((task_start_run IS NULL AND task_end_run IS NULL) OR (task_start_run IS NOT NULL AND task_end_run IS NOT NULL)));
190 bug_id serial PRIMARY KEY,
191 bug_task integer REFERENCES analysis_tasks NOT NULL,
192 bug_tracker integer REFERENCES bug_trackers NOT NULL,
193 bug_number integer NOT NULL,
194 CONSTRAINT bug_task_and_tracker_must_be_unique UNIQUE(bug_task, bug_tracker));
196 CREATE TABLE build_triggerables (
197 triggerable_id serial PRIMARY KEY,
198 triggerable_name varchar(64) NOT NULL UNIQUE);
200 CREATE TABLE triggerable_repositories (
201 trigrepo_triggerable integer REFERENCES build_triggerables NOT NULL,
202 trigrepo_repository integer REFERENCES repositories NOT NULL,
203 trigrepo_sub_roots boolean NOT NULL DEFAULT FALSE);
205 CREATE TABLE triggerable_configurations (
206 trigconfig_test integer REFERENCES tests NOT NULL,
207 trigconfig_platform integer REFERENCES platforms NOT NULL,
208 trigconfig_triggerable integer REFERENCES build_triggerables NOT NULL,
209 CONSTRAINT triggerable_must_be_unique_for_test_and_platform UNIQUE(trigconfig_test, trigconfig_platform));
211 CREATE TABLE analysis_test_groups (
212 testgroup_id serial PRIMARY KEY,
213 testgroup_task integer REFERENCES analysis_tasks NOT NULL,
214 testgroup_name varchar(256),
215 testgroup_author varchar(256),
216 testgroup_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
217 CONSTRAINT testgroup_name_must_be_unique_for_each_task UNIQUE(testgroup_task, testgroup_name));
218 CREATE INDEX testgroup_task_index ON analysis_test_groups(testgroup_task);
220 CREATE TABLE root_sets (
221 rootset_id serial PRIMARY KEY);
224 root_set integer REFERENCES root_sets NOT NULL,
225 root_commit integer REFERENCES commits NOT NULL);
227 CREATE TYPE build_request_status_type as ENUM ('pending', 'scheduled', 'running', 'failed', 'completed');
228 CREATE TABLE build_requests (
229 request_id serial PRIMARY KEY,
230 request_triggerable integer REFERENCES build_triggerables NOT NULL,
231 request_platform integer REFERENCES platforms NOT NULL,
232 request_test integer REFERENCES tests NOT NULL,
233 request_group integer REFERENCES analysis_test_groups NOT NULL,
234 request_order integer NOT NULL,
235 request_root_set integer REFERENCES root_sets NOT NULL,
236 request_status build_request_status_type NOT NULL DEFAULT 'pending',
237 request_url varchar(1024),
238 request_build integer REFERENCES builds,
239 request_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
240 CONSTRAINT build_request_order_must_be_unique_in_group UNIQUE(request_group, request_order));
241 CREATE INDEX build_request_triggerable ON build_requests(request_triggerable);
242 CREATE INDEX build_request_build ON build_requests(request_build);