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.

1 files changed, 50 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;