aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--sqitch/deploy/derivation_output_sets.sql49
-rw-r--r--sqitch/revert/derivation_output_sets.sql8
-rw-r--r--sqitch/sqitch.plan1
-rw-r--r--sqitch/verify/derivation_output_sets.sql7
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;