diff options
Diffstat (limited to 'sqitch/deploy/derivation_output_sets.sql')
-rw-r--r-- | sqitch/deploy/derivation_output_sets.sql | 49 |
1 files changed, 49 insertions, 0 deletions
diff --git a/sqitch/deploy/derivation_output_sets.sql b/sqitch/deploy/derivation_output_sets.sql new file mode 100644 index 0000000..fb77eb4 --- /dev/null +++ b/sqitch/deploy/derivation_output_sets.sql @@ -0,0 +1,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; |