aboutsummaryrefslogtreecommitdiff
path: root/sqitch
diff options
context:
space:
mode:
authorChristopher Baines <mail@cbaines.net>2019-10-05 09:14:40 +0100
committerChristopher Baines <mail@cbaines.net>2019-10-05 09:14:40 +0100
commit9fc6dd08e681ee8ce12c6d1d5036710d05b5e9db (patch)
tree2df53fdfe072f36f647997108dcf06a2b4ed75be /sqitch
parenta104fbc5e1cd1485c2ce97e555d91d28f3c6eb67 (diff)
downloaddata-service-9fc6dd08e681ee8ce12c6d1d5036710d05b5e9db.tar
data-service-9fc6dd08e681ee8ce12c6d1d5036710d05b5e9db.tar.gz
Remove duplicates from the guix_revisions table
Diffstat (limited to 'sqitch')
-rw-r--r--sqitch/deploy/remove_guix_revision_duplicates.sql37
-rw-r--r--sqitch/revert/remove_guix_revision_duplicates.sql7
-rw-r--r--sqitch/sqitch.plan1
-rw-r--r--sqitch/verify/remove_guix_revision_duplicates.sql7
4 files changed, 52 insertions, 0 deletions
diff --git a/sqitch/deploy/remove_guix_revision_duplicates.sql b/sqitch/deploy/remove_guix_revision_duplicates.sql
new file mode 100644
index 0000000..913f8be
--- /dev/null
+++ b/sqitch/deploy/remove_guix_revision_duplicates.sql
@@ -0,0 +1,37 @@
+-- Deploy guix-data-service:remove_guix_revision_duplicates to pg
+
+BEGIN;
+
+WITH data AS (
+ SELECT unnest(old_ids) AS old, id FROM (
+ SELECT MIN(id) AS id, ARRAY_AGG(id) AS old_ids
+ FROM guix_revisions
+ GROUP BY commit, git_repository_id
+ HAVING COUNT(id) > 1
+ ) AS d2
+)
+DELETE FROM guix_revision_package_derivations
+WHERE revision_id IN (SELECT old FROM data WHERE old != id);
+
+WITH data AS (
+ SELECT unnest(old_ids) AS old, id FROM (
+ SELECT MIN(id) AS id, ARRAY_AGG(id) AS old_ids
+ FROM guix_revisions
+ GROUP BY commit, git_repository_id
+ HAVING COUNT(id) > 1
+ ) AS d2
+)
+UPDATE package_versions_by_guix_revision_range
+SET first_guix_revision_id = data.id
+FROM data
+WHERE first_guix_revision_id = data.old;
+
+DELETE FROM guix_revisions AS g WHERE id NOT IN (
+ SELECT MIN(id)
+ FROM guix_revisions
+ GROUP BY (commit, git_repository_id)
+);
+
+CREATE UNIQUE INDEX ON guix_revisions (commit, git_repository_id);
+
+COMMIT;
diff --git a/sqitch/revert/remove_guix_revision_duplicates.sql b/sqitch/revert/remove_guix_revision_duplicates.sql
new file mode 100644
index 0000000..d7abe11
--- /dev/null
+++ b/sqitch/revert/remove_guix_revision_duplicates.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:remove_guix_revision_duplicates from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 0b16d72..14a5478 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -24,3 +24,4 @@ package_versions_by_guix_revision_range 2019-09-26T20:23:15Z Christopher Baines
index_on_load_new_guix_revision_job_events_job_id 2019-09-29T10:39:04Z Christopher Baines <mail@cbaines.net> # Add index on load_new_guix_revision_job_events.job_id
fix_null_values_in_git_branches 2019-09-29T11:06:12Z Christopher Baines <mail@cbaines.net> # Fix the NULL values in git_branches
add_retry_value_to_job_event_enum 2019-10-02T19:13:52Z Christopher Baines <mail@cbaines.net> # Add retry value to job_event enum
+remove_guix_revision_duplicates 2019-10-05T08:00:14Z Christopher Baines <mail@cbaines.net> # Remove duplicates in the guix_revisions table
diff --git a/sqitch/verify/remove_guix_revision_duplicates.sql b/sqitch/verify/remove_guix_revision_duplicates.sql
new file mode 100644
index 0000000..f9098bb
--- /dev/null
+++ b/sqitch/verify/remove_guix_revision_duplicates.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:remove_guix_revision_duplicates on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;