diff options
author | Christopher Baines <mail@cbaines.net> | 2019-12-05 14:10:09 +0100 |
---|---|---|
committer | Christopher Baines <mail@cbaines.net> | 2019-12-05 15:42:58 +0100 |
commit | 80043e00596da1f872149465d642cb2bf8bceddb (patch) | |
tree | 57fec608f574f83fb244021d2db0ef3761ac4e67 /sqitch/deploy/sort_out_duplicate_builds.sql | |
parent | 9c71dd3ae38d6ee46fd11385c0670d4876f33972 (diff) | |
download | data-service-80043e00596da1f872149465d642cb2bf8bceddb.tar data-service-80043e00596da1f872149465d642cb2bf8bceddb.tar.gz |
WIP sort out deduplicate builds
Diffstat (limited to 'sqitch/deploy/sort_out_duplicate_builds.sql')
-rw-r--r-- | sqitch/deploy/sort_out_duplicate_builds.sql | 33 |
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; |