1 files changed, 17 insertions, 0 deletions
diff --git a/sqitch/deploy/equivalent_derivations.sql b/sqitch/deploy/equivalent_derivations.sql
new file mode 100644
index 0000000..3ec6ca0
--- /dev/null
+++ b/sqitch/deploy/equivalent_derivations.sql
@@ -0,0 +1,17 @@
+-- Deploy guix-data-service:equivalent_derivations to pg
+
+BEGIN;
+
+CREATE TABLE equivalent_derivations AS
+  SELECT ARRAY_AGG(derivation_id ORDER BY derivation_id ASC) AS derivation_ids,
+         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
+  GROUP BY derivation_output_groups.derivation_output_details_ids;
+
+CREATE INDEX equivalent_derivations_derivation_ids ON equivalent_derivations USING GIN (derivation_ids);
+
+COMMIT;