Adding context table

Commit includes updated schema and update script.

Reviewed-by: jasplin

2 files changed, 94 insertions, 14 deletions

diff --git a/database/scripts/add-context-table.sql b/database/scripts/add-context-table.sql
new file mode 100644
index 0000000..507a580
--- /dev/null
+++ b/database/scripts/add-context-table.sql
@@ -0,0 +1,66 @@
+--
+-- Upgrade script from previous context-less schema
+--
+
+-- Add new tables
+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,
+
+    UNIQUE(hostId, platformId, branchId, sha1Id)
+) WITH (OIDS=FALSE);
+ALTER TABLE context OWNER TO postgres;
+
+-- Import existing data
+INSERT INTO context (hostId, platformId, branchId, sha1Id, timestamp)
+    SELECT hostId, platformId, branchId, sha1Id, min(startTime) AS timestamp
+        FROM
+            (SELECT DISTINCT hostId, platformId, branchId, sha1Id, uploadId FROM result) AS dummy
+            JOIN upload ON uploadId = upload.id
+        GROUP BY hostId, platformId, branchId, sha1Id;
+
+-- Add new indexes
+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);
+
+-- Update result table schema -- step 1 of 2
+ALTER TABLE result ADD COLUMN contextId BIGINT REFERENCES context ON DELETE CASCADE;
+
+UPDATE result
+    SET contextId = context.id
+    FROM context
+    WHERE context.hostId = hostId
+        AND context.platformId = platformId
+        AND context.branchId = branchId
+        AND context.sha1Id = sha1Id;
+
+-- Drop obsolete indexes
+DROP INDEX result_host_idx;
+DROP INDEX result_platform_idx;
+DROP INDEX result_branch_idx;
+DROP INDEX result_sha1_idx;
+
+-- Update result table schema -- step 2 of 2
+ALTER TABLE result
+    ALTER COLUMN contextId SET NOT NULL,
+    DROP COLUMN hostId,
+    DROP COLUMN platformId,
+    DROP COLUMN branchId,
+    DROP COLUMN sha1Id;
+
+-- Add new indexes
+CREATE INDEX result_context_idx ON result (contextId);
+
diff --git a/database/scripts/tabledefs.sql b/database/scripts/tabledefs.sql
index ec0252c..9d1fb2c 100644
--- a/database/scripts/tabledefs.sql
+++ b/database/scripts/tabledefs.sql
@@ -1,3 +1,23 @@
+-- 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,
+
+    UNIQUE(hostId, platformId, branchId, sha1Id)
+) WITH (OIDS=FALSE);
+ALTER TABLE context OWNER TO postgres;
+
 -- Table to record upload events:
 CREATE TABLE upload
 (
@@ -5,7 +25,7 @@ CREATE TABLE upload
     startTime TIMESTAMP NOT NULL DEFAULT now(),
     clientIPAddr TEXT NOT NULL DEFAULT inet_client_addr()
 ) WITH (OIDS=FALSE);
-ALTER TABLE result OWNER TO postgres;
+ALTER TABLE upload OWNER TO postgres;
 
 -- Tables to store texts referred to by the main result table:
 CREATE TABLE host (
@@ -38,15 +58,7 @@ CREATE TABLE result
 (
     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,
-
-    -- Benchmark:
+    contextId BIGINT NOT NULL REFERENCES context ON DELETE CASCADE,
     benchmarkId BIGINT NOT NULL REFERENCES benchmark ON DELETE CASCADE,
 
     -- Value:
@@ -70,10 +82,12 @@ 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_host_idx ON result (hostId);
-CREATE INDEX result_platform_idx ON result (platformId);
-CREATE INDEX result_branch_idx ON result (branchId);
-CREATE INDEX result_sha1_idx ON result (sha1Id);
+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);