branch master updated: Remove odd package derivations with mismatched systems

[Christopher Baines]

This is an automated email from the git hooks/post-receive script.

cbaines pushed a commit to branch master
in repository data-service.

The following commit(s) were added to refs/heads/master by this push:
     new 111576e  Remove odd package derivations with mismatched systems
111576e is described below

commit 111576e3f93b07cca6f2634bbc87116aae5e537b
Author: Christopher Baines <address@hidden>
AuthorDate: Fri Apr 24 21:47:06 2020 +0100

    Remove odd package derivations with mismatched systems
    
    This follows on from 79993bfaeff3097d37838bc0636fc20c6fc4283d, 
disassociating
    the relevant derivations from the revisions.
---
 sqitch/deploy/remove_odd_package_derivations.sql | 23 +++++++++++++++++++++++
 sqitch/revert/remove_odd_package_derivations.sql |  7 +++++++
 sqitch/sqitch.plan                               |  1 +
 sqitch/verify/remove_odd_package_derivations.sql |  7 +++++++
 4 files changed, 38 insertions(+)

diff --git a/sqitch/deploy/remove_odd_package_derivations.sql 
b/sqitch/deploy/remove_odd_package_derivations.sql
new file mode 100644
index 0000000..6cee32f
--- /dev/null
+++ b/sqitch/deploy/remove_odd_package_derivations.sql
@@ -0,0 +1,23 @@
+-- Deploy guix-data-service:remove_odd_package_derivations to pg
+
+BEGIN;
+
+DELETE FROM guix_revision_package_derivations
+WHERE package_derivation_id IN (
+  SELECT package_derivations.id
+  FROM package_derivations
+  INNER JOIN derivations
+    ON package_derivations.derivation_id = derivations.id
+  WHERE package_derivations.system != derivations.system
+);
+
+DELETE FROM package_derivations
+WHERE id IN (
+  SELECT package_derivations.id
+  FROM package_derivations
+  INNER JOIN derivations
+    ON package_derivations.derivation_id = derivations.id
+  WHERE package_derivations.system != derivations.system
+);
+
+COMMIT;
diff --git a/sqitch/revert/remove_odd_package_derivations.sql 
b/sqitch/revert/remove_odd_package_derivations.sql
new file mode 100644
index 0000000..f854233
--- /dev/null
+++ b/sqitch/revert/remove_odd_package_derivations.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:remove_odd_package_derivations from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index a92062c..ab6ce17 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -56,3 +56,4 @@ guix_revision_system_test_derivations_add_system 
2020-03-19T21:30:33Z Christophe
 drop_package_versions_by_guix_revision_range 2020-03-24T20:40:38Z Christopher 
Baines <address@hidden> # Drop package_versions_by_guix_revision_range
 create_narinfo_fetch_records_index 2020-03-25T19:07:28Z Christopher Baines 
<address@hidden> # Create an index on narinfo_fetch_records
 load_new_guix_revision_jobs_make_commits_unique 2020-03-27T21:38:42Z 
Christopher Baines <address@hidden> # Make load_new_guix_revision_jobs commits 
unique
+remove_odd_package_derivations 2020-04-24T20:36:06Z Christopher Baines 
<address@hidden> # Remove odd package derivations
diff --git a/sqitch/verify/remove_odd_package_derivations.sql 
b/sqitch/verify/remove_odd_package_derivations.sql
new file mode 100644
index 0000000..d0b0d4b
--- /dev/null
+++ b/sqitch/verify/remove_odd_package_derivations.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:remove_odd_package_derivations on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;