Deduplicate builds and add a unique index

Duplicate builds could creep in if the code to create them ran concurrently. I
didn't exclude them initially, as I was unsure if there should be such a
restriction, but at least for now, Cuirass builds map exactly to a single
derivation, so use the same restriction here.

1 files changed, 33 insertions, 0 deletions

diff --git a/sqitch/deploy/sort_out_duplicate_builds.sql b/sqitch/deploy/sort_out_duplicate_builds.sql
new file mode 100644
index 0000000..92bd2c5
--- /dev/null
+++ b/sqitch/deploy/sort_out_duplicate_builds.sql
@@ -0,0 +1,33 @@
+-- Deploy guix-data-service:sort_out_duplicate_builds to pg
+
+BEGIN;
+
+DELETE FROM build_status WHERE build_id IN (
+  SELECT builds.id
+  FROM builds
+  INNER JOIN (
+    SELECT derivation_file_name, MIN(id) AS id
+    FROM builds
+    GROUP BY build_server_id, derivation_file_name
+    HAVING COUNT(DISTINCT id) > 1
+  ) AS min_ids
+    ON min_ids.derivation_file_name = builds.derivation_file_name AND
+       min_ids.id != builds.id
+);
+
+DELETE FROM builds WHERE id IN (
+  SELECT builds.id
+  FROM builds
+  INNER JOIN (
+    SELECT derivation_file_name, MIN(id) AS id
+    FROM builds
+    GROUP BY build_server_id, derivation_file_name
+    HAVING COUNT(DISTINCT id) > 1
+  ) AS min_ids
+    ON min_ids.derivation_file_name = builds.derivation_file_name AND
+       min_ids.id != builds.id
+);
+
+CREATE UNIQUE INDEX ON builds (build_server_id, derivation_file_name);
+
+COMMIT;