sqitch/deploy/sort_out_duplicate_builds.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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;