Added Top Changes page to replace ranking feature.

This commit introduces the Top Changes page. This page retrieves
'top 10' changes for all host/platform/branch combinations from
a new 'change' database table. The script that updates this table
for a given host/platform/branch combination is run automatically
after uploading a new set of results (since this is when new
changes may potentially arise).

This commit also removes the ranking feature as this is obsoleted
(more or less) by the new feature.

1 files changed, 47 insertions, 1 deletions

diff --git a/database/scripts/tabledefs.sql b/database/scripts/tabledefs.sql
index 31c25fc..07f2f84 100644
--- a/database/scripts/tabledefs.sql
+++ b/database/scripts/tabledefs.sql
@@ -31,10 +31,19 @@ CREATE TABLE sha1 (
 ALTER TABLE sha1 OWNER TO postgres;
 CREATE INDEX sha1_value_idx ON sha1 (value);
 --
-CREATE TABLE benchmark (
+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,
@@ -155,3 +164,40 @@ 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);