blob: fb77eb4d8c9cb1a428184702c386a38ace7418e7 (
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
|
-- 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);
-- Maybe initially populate these tables?
-- 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;
|