blob: 258b9f27d20a866cd625f17fb426cd97bf4ff92f (
plain)
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
|
-- Deploy guix-data-service:derivation_output_sets to pg
BEGIN;
CREATE TABLE derivation_output_details_sets (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
derivation_output_details_ids integer[] NOT NULL,
UNIQUE (derivation_output_details_ids)
);
CREATE TABLE derivations_by_output_details_set (
derivation_id integer REFERENCES derivations (id),
derivation_output_details_set_id integer REFERENCES derivation_output_details_sets (id),
PRIMARY KEY (derivation_id, derivation_output_details_set_id),
UNIQUE (derivation_id)
);
CREATE INDEX derivations_by_output_details_set_id_idx
ON derivations_by_output_details_set (derivation_output_details_set_id);
INSERT INTO derivation_output_details_sets (derivation_output_details_ids) (
SELECT DISTINCT ARRAY_AGG(
derivation_output_details_id
ORDER BY derivation_output_details_id
)
FROM derivation_outputs
GROUP BY derivation_id
);
INSERT INTO derivations_by_output_details_set (
SELECT derivation_id, derivation_output_details_sets.id
FROM (
SELECT derivation_id,
derivation_output_details_ids
FROM (
SELECT derivation_id,
ARRAY_AGG(
derivation_output_details_id
ORDER BY derivation_output_details_id
) AS derivation_output_details_ids
FROM derivation_outputs
GROUP BY derivation_id
) AS derivation_output_groups
) data
INNER JOIN derivation_output_details_sets
ON data.derivation_output_details_ids =
derivation_output_details_sets.derivation_output_details_ids
);
COMMIT;
|