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 commits CASCADE;
8 DROP TABLE build_commits CASCADE;
9 DROP TABLE builders CASCADE;
10 DROP TABLE repositories CASCADE;
11 DROP TABLE platforms CASCADE;
12 DROP TABLE test_metrics CASCADE;
13 DROP TABLE tests 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_bug_url varchar(1024),
33 tracker_new_bug_url varchar(1024));
35 CREATE TABLE tracker_repositories (
36 tracrepo_tracker integer NOT NULL REFERENCES bug_trackers ON DELETE CASCADE,
37 tracrepo_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE);
39 CREATE TABLE builders (
40 builder_id serial PRIMARY KEY,
41 builder_name varchar(64) NOT NULL UNIQUE,
42 builder_password_hash character(64) NOT NULL,
43 builder_build_url varchar(1024));
46 build_id serial PRIMARY KEY,
47 build_builder integer REFERENCES builders ON DELETE CASCADE,
48 build_number integer NOT NULL,
49 build_time timestamp NOT NULL,
50 build_latest_revision timestamp,
51 CONSTRAINT builder_build_time_tuple_must_be_unique UNIQUE(build_builder, build_number, build_time));
52 CREATE INDEX build_builder_index ON builds(build_builder);
54 CREATE TABLE commits (
55 commit_id serial PRIMARY KEY,
56 commit_repository integer NOT NULL REFERENCES repositories ON DELETE CASCADE,
57 commit_revision varchar(64) NOT NULL,
58 commit_parent integer REFERENCES commits ON DELETE CASCADE,
59 commit_time timestamp,
60 commit_author_name varchar(128),
61 commit_author_email varchar(320),
63 commit_reported boolean NOT NULL DEFAULT FALSE,
64 CONSTRAINT commit_in_repository_must_be_unique UNIQUE(commit_repository, commit_revision));
65 CREATE INDEX commit_time_index ON commits(commit_time);
66 CREATE INDEX commit_author_name_index ON commits(commit_author_name);
67 CREATE INDEX commit_author_email_index ON commits(commit_author_email);
69 CREATE TABLE build_commits (
70 commit_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
71 build_commit integer NOT NULL REFERENCES commits ON DELETE CASCADE,
72 PRIMARY KEY (commit_build, build_commit));
74 CREATE TABLE aggregators (
75 aggregator_id serial PRIMARY KEY,
76 aggregator_name varchar(64),
77 aggregator_definition text);
80 test_id serial PRIMARY KEY,
81 test_name varchar(255) NOT NULL,
82 test_parent integer REFERENCES tests ON DELETE CASCADE,
83 test_url varchar(1024) DEFAULT NULL,
84 CONSTRAINT parent_test_must_be_unique UNIQUE(test_parent, test_name));
86 CREATE TABLE test_metrics (
87 metric_id serial PRIMARY KEY,
88 metric_test integer NOT NULL REFERENCES tests ON DELETE CASCADE,
89 metric_name varchar(64) NOT NULL,
90 metric_aggregator integer REFERENCES aggregators ON DELETE CASCADE);
92 CREATE TYPE test_configuration_type as ENUM ('current', 'baseline', 'target');
93 CREATE TABLE test_configurations (
94 config_id serial PRIMARY KEY,
95 config_metric integer NOT NULL REFERENCES test_metrics ON DELETE CASCADE,
96 config_platform integer NOT NULL REFERENCES platforms ON DELETE CASCADE,
97 config_type test_configuration_type NOT NULL,
98 config_is_in_dashboard boolean NOT NULL DEFAULT FALSE,
99 CONSTRAINT configuration_must_be_unique UNIQUE(config_metric, config_platform, config_type));
100 CREATE INDEX config_platform_index ON test_configurations(config_platform);
102 CREATE TABLE test_runs (
103 run_id serial PRIMARY KEY,
104 run_config integer NOT NULL REFERENCES test_configurations ON DELETE CASCADE,
105 run_build integer NOT NULL REFERENCES builds ON DELETE CASCADE,
106 run_iteration_count_cache smallint,
107 run_mean_cache double precision,
108 run_sum_cache double precision,
109 run_square_sum_cache double precision,
110 CONSTRAINT test_config_build_must_be_unique UNIQUE(run_config, run_build));
111 CREATE INDEX run_config_index ON test_runs(run_config);
112 CREATE INDEX run_build_index ON test_runs(run_build);
114 CREATE TABLE run_iterations (
115 iteration_run integer NOT NULL REFERENCES test_runs ON DELETE CASCADE,
116 iteration_order smallint NOT NULL CHECK(iteration_order >= 0),
117 iteration_group smallint CHECK(iteration_group >= 0),
118 iteration_value double precision,
119 iteration_relative_time float,
120 PRIMARY KEY (iteration_run, iteration_order));
122 CREATE TABLE reports (
123 report_id serial PRIMARY KEY,
124 report_builder integer NOT NULL REFERENCES builders ON DELETE RESTRICT,
125 report_build_number integer,
126 report_build integer REFERENCES builds,
127 report_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
128 report_committed_at timestamp,
130 report_failure varchar(64),
131 report_failure_details text);
133 CREATE TABLE analysis_tasks (
134 task_id serial PRIMARY KEY,
135 task_name varchar(256) NOT NULL,
136 task_author varchar(256),
137 task_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
138 task_platform integer REFERENCES platforms NOT NULL,
139 task_metric integer REFERENCES test_metrics NOT NULL,
140 task_start_run integer REFERENCES test_runs,
141 task_end_run integer REFERENCES test_runs,
142 CONSTRAINT analysis_task_should_be_unique_for_range UNIQUE(task_start_run, task_end_run)
143 CONSTRAINT analysis_task_should_not_be_associated_with_single_run
144 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)));
147 bug_id serial PRIMARY KEY,
148 bug_task integer REFERENCES analysis_tasks NOT NULL,
149 bug_tracker integer REFERENCES bug_trackers NOT NULL,
150 bug_number integer NOT NULL,
151 CONSTRAINT bug_task_and_tracker_must_be_unique UNIQUE(bug_task, bug_tracker));
153 CREATE TABLE analysis_test_groups (
154 testgroup_id serial PRIMARY KEY,
155 testgroup_task integer REFERENCES analysis_tasks NOT NULL,
156 testgroup_name varchar(256),
157 testgroup_author varchar(256) NOT NULL,
158 testgroup_created_at timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'));
159 CREATE INDEX testgroup_task_index ON analysis_test_groups(testgroup_task);
161 CREATE TABLE root_sets (
162 rootset_id serial PRIMARY KEY);
164 CREATE TABLE build_requests (
165 request_id serial PRIMARY KEY,
166 request_group integer REFERENCES analysis_test_groups NOT NULL,
167 request_order integer NOT NULL,
168 request_root_set integer REFERENCES root_sets NOT NULL,
169 request_build integer REFERENCES builds,
170 CONSTRAINT build_request_order_must_be_unique_in_group UNIQUE(request_group, request_order));