diff options
| -rw-r--r-- | sqitch/deploy/derivation_output_sets.sql | 49 | ||||
| -rw-r--r-- | sqitch/revert/derivation_output_sets.sql | 8 | ||||
| -rw-r--r-- | sqitch/sqitch.plan | 1 | ||||
| -rw-r--r-- | sqitch/verify/derivation_output_sets.sql | 7 |
4 files changed, 65 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; diff --git a/sqitch/revert/derivation_output_sets.sql b/sqitch/revert/derivation_output_sets.sql new file mode 100644 index 0000000..6df4efa --- /dev/null +++ b/sqitch/revert/derivation_output_sets.sql @@ -0,0 +1,8 @@ +-- Revert guix-data-service:derivation_output_sets from pg + +BEGIN; + +DROP TABLE derivations_by_output_details_set; +DROP TABLE derivation_output_details_sets; + +COMMIT; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index bded932..cb630e1 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -37,3 +37,4 @@ change_nars_size_to_bigint 2019-12-04T21:24:21Z <chris@phact> # Change nars.si change_nar_urls_size_to_bigint 2019-12-04T21:49:07Z <chris@phact> # Change nar_urls.size to bigint sort_out_duplicate_builds 2019-12-05T12:43:53Z <chris@phact> # Sort out duplicate builds add_some_database_indexes 2019-12-05T15:53:04Z Christopher Baines <mail@cbaines.net> # Add some indexes +derivation_output_sets 2019-12-05T23:19:05Z Christopher Baines <mail@cbaines.net> # Describe the sets of derivation outputs diff --git a/sqitch/verify/derivation_output_sets.sql b/sqitch/verify/derivation_output_sets.sql new file mode 100644 index 0000000..daa0dd0 --- /dev/null +++ b/sqitch/verify/derivation_output_sets.sql @@ -0,0 +1,7 @@ +-- Verify guix-data-service:derivation_output_sets on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; |