4 files changed, 66 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..258b9f2
--- /dev/null
+++ b/sqitch/deploy/derivation_output_sets.sql
@@ -0,0 +1,50 @@
+-- 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);
+
+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 58b272d..2f5b512 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -36,3 +36,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;