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
|
--
-- 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);
|
