1 files changed, 81 insertions, 50 deletions
diff --git a/database/scripts/tabledefs.sql b/database/scripts/tabledefs.sql
index 1964011..c6f94a2 100644
--- a/database/scripts/tabledefs.sql
+++ b/database/scripts/tabledefs.sql
@@ -1,24 +1,4 @@
--- A context stores common data for a set of results.
-CREATE TABLE context
-(
-    id BIGSERIAL PRIMARY KEY,
-
-    -- Environment:
-    hostId BIGINT NOT NULL REFERENCES host ON DELETE CASCADE,
-    platformId BIGINT NOT NULL REFERENCES platform ON DELETE CASCADE,
-
-    -- Revision:
-    branchId BIGINT NOT NULL REFERENCES branch ON DELETE CASCADE,
-    sha1Id BIGINT NOT NULL REFERENCES sha1 ON DELETE CASCADE,
-
-    -- Timestamp: (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;
-
--- Table to record upload events:
+-- Upload events:
 CREATE TABLE upload
 (
     id BIGSERIAL PRIMARY KEY,
@@ -26,68 +6,119 @@ CREATE TABLE upload
     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);
+
 
--- Tables to store texts referred to by the main result table:
+-- 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 benchmark (
     id BIGSERIAL PRIMARY KEY, value TEXT UNIQUE NOT NULL) WITH (OIDS=FALSE);
 ALTER TABLE benchmark OWNER TO postgres;
-
+CREATE INDEX benchmark_value_idx ON benchmark (value);
+--
 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);
 
--- Table to store results:
-CREATE TABLE result
+
+-- 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:
     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;
-
--- Indexes:
-CREATE INDEX upload_startTime_idx ON upload (startTime);
-CREATE INDEX upload_clientIPAddr_idx ON upload (clientIPAddr);
---
-CREATE INDEX host_value_idx ON host (value);
-CREATE INDEX platform_value_idx ON platform (value);
-CREATE INDEX branch_value_idx ON branch (value);
-CREATE INDEX sha1_value_idx ON sha1 (value);
-CREATE INDEX benchmark_value_idx ON benchmark (value);
-CREATE INDEX metric_value_idx ON metric (value);
---
 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);
---
-CREATE INDEX context_host_idx ON result (hostId);
-CREATE INDEX context_platform_idx ON result (platformId);
-CREATE INDEX context_branch_idx ON result (branchId);
-CREATE INDEX context_sha1_idx ON result (sha1Id);
+
+
+-- 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,
+    contextId 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,
+
+    -- 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 (contextId, benchmarkId, metricId, statId)
+) WITH (OIDS=FALSE);
+ALTER TABLE ranking OWNER TO postgres;
+CREATE INDEX ranking_context_idx ON ranking (contextId);
+CREATE INDEX ranking_benchmark_idx ON ranking (benchmarkId);
+CREATE INDEX ranking_metric_idx ON ranking (metricId);
+CREATE INDEX ranking_stat_idx ON ranking (statId);
+CREATE INDEX ranking_pos_idx ON ranking (pos);