WIP derivation output sets

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;