Start storing and identifying sets of derivation outputs

Derivations are effectively equivalent if they produce the same set of
outputs, which is possible because of the equivalence of fixed output
derivations. A fixed output derivation can be different, but equivalent,
because it produces the same fixed output.

To better allow tracking equivalent derivations, primarily to allow working
out what derivations might correspond to a build, store the sets of derivation
outputs, and which derivations they relate to.

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;