1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
|
-- Upload events:
CREATE TABLE upload
(
id BIGSERIAL PRIMARY KEY,
startTime TIMESTAMP NOT NULL DEFAULT now(),
clientIPAddr TEXT NOT NULL DEFAULT inet_client_addr()
) WITH (OIDS=FALSE);
ALTER TABLE upload OWNER TO postgres;
CREATE INDEX upload_startTime_idx ON upload (startTime);
CREATE INDEX upload_clientIPAddr_idx ON upload (clientIPAddr);
-- Texts referred to by other tables:
CREATE TABLE host (
id BIGSERIAL PRIMARY KEY, value TEXT UNIQUE NOT NULL) WITH (OIDS=FALSE);
ALTER TABLE host OWNER TO postgres;
CREATE INDEX host_value_idx ON host (value);
--
CREATE TABLE platform (
id BIGSERIAL PRIMARY KEY, value TEXT UNIQUE NOT NULL) WITH (OIDS=FALSE);
ALTER TABLE platform OWNER TO postgres;
CREATE INDEX platform_value_idx ON platform (value);
--
CREATE TABLE branch (
id BIGSERIAL PRIMARY KEY, value TEXT UNIQUE NOT NULL) WITH (OIDS=FALSE);
ALTER TABLE branch OWNER TO postgres;
CREATE INDEX branch_value_idx ON branch (value);
--
CREATE TABLE sha1 (
id BIGSERIAL PRIMARY KEY, value TEXT UNIQUE NOT NULL) WITH (OIDS=FALSE);
ALTER TABLE sha1 OWNER TO postgres;
CREATE INDEX sha1_value_idx ON sha1 (value);
--
CREATE TABLE testCase (
id BIGSERIAL PRIMARY KEY, value TEXT UNIQUE NOT NULL) WITH (OIDS=FALSE);
ALTER TABLE testCase OWNER TO postgres;
CREATE INDEX testCase_value_idx ON testCase (value);
--
CREATE TABLE benchmark (
id BIGSERIAL PRIMARY KEY,
value TEXT UNIQUE NOT NULL,
testCaseId BIGINT NOT NULL REFERENCES testCase ON DELETE CASCADE
) WITH (OIDS=FALSE);
ALTER TABLE benchmark OWNER TO postgres;
CREATE INDEX benchmark_value_idx ON benchmark (value);
CREATE INDEX benchmark_testCase_idx ON benchmark (testCaseId);
--
CREATE TABLE metric (
id BIGSERIAL PRIMARY KEY, value TEXT UNIQUE NOT NULL,
lowerIsBetter BOOLEAN NOT NULL) WITH (OIDS=FALSE);
ALTER TABLE metric OWNER TO postgres;
CREATE INDEX metric_value_idx ON metric (value);
-- Common data for a set of results.
CREATE TABLE context
(
id BIGSERIAL PRIMARY KEY,
hostId BIGINT NOT NULL REFERENCES host ON DELETE CASCADE,
platformId BIGINT NOT NULL REFERENCES platform ON DELETE CASCADE,
branchId BIGINT NOT NULL REFERENCES branch ON DELETE CASCADE,
sha1Id BIGINT NOT NULL REFERENCES sha1 ON DELETE CASCADE,
-- The timestamp is approximated with earliest known startTime:
timestamp TIMESTAMP NOT NULL DEFAULT now(),
UNIQUE(hostId, platformId, branchId, sha1Id)
) WITH (OIDS=FALSE);
ALTER TABLE context OWNER TO postgres;
CREATE INDEX context_host_idx ON context (hostId);
CREATE INDEX context_platform_idx ON context (platformId);
CREATE INDEX context_branch_idx ON context (branchId);
CREATE INDEX context_sha1_idx ON context (sha1Id);
CREATE INDEX context_timestamp_idx ON context (timestamp);
--Benchmark results (i.e. raw values produced from executing benchmarks):
CREATE TABLE result
(
id BIGSERIAL PRIMARY KEY,
contextId BIGINT NOT NULL REFERENCES context ON DELETE CASCADE,
benchmarkId BIGINT NOT NULL REFERENCES benchmark ON DELETE CASCADE,
value REAL NOT NULL,
valid BOOLEAN NOT NULL,
metricId BIGINT NOT NULL REFERENCES metric ON DELETE CASCADE,
uploadId BIGINT NOT NULL REFERENCES upload ON DELETE CASCADE
) WITH (OIDS=FALSE);
ALTER TABLE result OWNER TO postgres;
CREATE INDEX result_context_idx ON result (contextId);
CREATE INDEX result_benchmark_idx ON result (benchmarkId);
CREATE INDEX result_metric_idx ON result (metricId);
CREATE INDEX result_valid_idx ON result (valid);
-- Ranking statistic types:
CREATE TABLE rankingStat (
id BIGSERIAL PRIMARY KEY, value TEXT UNIQUE NOT NULL) WITH (OIDS=FALSE);
ALTER TABLE rankingStat OWNER TO postgres;
INSERT INTO rankingStat (value) VALUES
('QS'), -- Quality score
('LCSSR'), -- Last chance stability score (last change regressing)
('LCSSI'), -- Last change stability score (last change improving)
('LCSS1R'), -- Last change stability score not considering history after
-- last change (last change regressing)
('LCSS1I'); -- Last change stability score not considering history after
-- last change (last change improving)
-- Rankings:
CREATE TABLE ranking
(
id BIGSERIAL PRIMARY KEY,
-- Contexts refering to start and end of ranking computation interval:
context1Id BIGINT NOT NULL REFERENCES context ON DELETE CASCADE,
context2Id BIGINT NOT NULL REFERENCES context ON DELETE CASCADE,
benchmarkId BIGINT NOT NULL REFERENCES benchmark ON DELETE CASCADE,
metricId BIGINT NOT NULL REFERENCES metric ON DELETE CASCADE,
lastChangeTimestamp INTEGER NOT NULL,
-- Ranking statistic type:
statId BIGINT NOT NULL REFERENCES rankingStat ON DELETE CASCADE,
-- Ranking statistic value:
value REAL NOT NULL,
-- Ranking position:
pos INTEGER NOT NULL,
UNIQUE (context2Id, benchmarkId, metricId, statId)
) WITH (OIDS=FALSE);
ALTER TABLE ranking OWNER TO postgres;
CREATE INDEX ranking_context1_idx ON ranking (context1Id);
CREATE INDEX ranking_context2_idx ON ranking (context2Id);
CREATE INDEX ranking_benchmark_idx ON ranking (benchmarkId);
CREATE INDEX ranking_metric_idx ON ranking (metricId);
CREATE INDEX ranking_lctimestamp_idx ON ranking (lastChangeTimestamp);
CREATE INDEX ranking_stat_idx ON ranking (statId);
CREATE INDEX ranking_pos_idx ON ranking (pos);
-- Time series annotations:
CREATE TABLE timeSeriesAnnotation
(
id BIGSERIAL PRIMARY KEY,
-- Time series key:
hostId BIGINT NOT NULL REFERENCES host ON DELETE CASCADE,
platformId BIGINT NOT NULL REFERENCES platform ON DELETE CASCADE,
branchId BIGINT NOT NULL REFERENCES branch ON DELETE CASCADE,
benchmarkId BIGINT NOT NULL REFERENCES benchmark ON DELETE CASCADE,
metricId BIGINT NOT NULL REFERENCES metric ON DELETE CASCADE,
-- Annotations:
note TEXT NOT NULL,
UNIQUE (hostId, platformId, branchId, benchmarkId, metricId)
) WITH (OIDS=FALSE);
ALTER TABLE ranking OWNER TO postgres;
CREATE INDEX tsanno_host_idx ON timeSeriesAnnotation (hostId);
CREATE INDEX tsanno_platform_idx ON timeSeriesAnnotation (platformId);
CREATE INDEX tsanno_branch_idx ON timeSeriesAnnotation (branchId);
CREATE INDEX tsanno_benchmark_idx ON timeSeriesAnnotation (benchmarkId);
CREATE INDEX tsanno_metric_idx ON timeSeriesAnnotation (metricId);
--Time series changes:
CREATE TABLE change
(
id BIGSERIAL PRIMARY KEY,
benchmarkId BIGINT NOT NULL REFERENCES benchmark ON DELETE CASCADE,
testCaseId BIGINT NOT NULL REFERENCES testCase ON DELETE CASCADE,
metricId BIGINT NOT NULL REFERENCES metric ON DELETE CASCADE,
hostId BIGINT NOT NULL REFERENCES host ON DELETE CASCADE,
platformId BIGINT NOT NULL REFERENCES platform ON DELETE CASCADE,
branchId BIGINT NOT NULL REFERENCES branch ON DELETE CASCADE,
sha1Id BIGINT NOT NULL REFERENCES sha1 ON DELETE CASCADE,
timestamp INTEGER NOT NULL, -- First upload timestamp (UTC)
regression BOOLEAN NOT NULL, -- Regression or improvement
score REAL NOT NULL,
premature_score REAL NOT NULL,
UNIQUE (benchmarkId, metricId, hostId, platformId, branchId, sha1id)
) WITH (OIDS=FALSE);
ALTER TABLE change OWNER TO postgres;
CREATE INDEX change_benchmark_idx ON change (benchmarkId);
CREATE INDEX change_testcase_idx ON change (testCaseId);
CREATE INDEX change_metric_idx ON change (metricId);
CREATE INDEX change_host_idx ON change (hostId);
CREATE INDEX change_platform_idx ON change (platformId);
CREATE INDEX change_branch_idx ON change (branchId);
CREATE INDEX change_sha1_idx ON change (sha1Id);
CREATE INDEX change_timestamp_idx ON change (timestamp);
CREATE INDEX change_regression_idx ON change (regression);
CREATE INDEX change_score_idx ON change (score);
CREATE INDEX change_premature_score_idx ON change (premature_score);
|
