Fix query in select-derivations-with-no-known-build

Rather than eliminating any derivation that has a known build, eliminate those
derivations, but also equivalent derivations as well.

For selecting the derivations in a revision, join on the equivalent
derivations, as all the equilalent derivations need checking as well, as it's
unknown which one Cuirass would have associated the build against.

Also, filter for x86_64-linux, to avoid checking for crossbuilt things. This
should be replaced by some way of describing what derivations a build server
has.

1 files changed, 20 insertions, 6 deletions

diff --git a/guix-data-service/builds.scm b/guix-data-service/builds.scm
index 9abc5eb..be9f421 100644
--- a/guix-data-service/builds.scm
+++ b/guix-data-service/builds.scm
@@ -193,20 +193,34 @@ LIMIT 1000")
      "
 SELECT derivations.id, derivations.file_name
 FROM derivations
-WHERE derivations.file_name NOT IN (
-  SELECT derivation_file_name FROM builds
+WHERE derivations.id NOT IN (
+  SELECT unnest(equivalent_derivations.derivation_ids)
+  FROM equivalent_derivations
+  WHERE ARRAY[(
+    SELECT derivations.id WHERE derivations.file_name IN (SELECT derivation_file_name FROM builds)
+  )] <@ equivalent_derivations.derivation_ids
 ) AND derivations.id IN (
-  SELECT derivation_id FROM package_derivations"
+  SELECT unnest(derivation_ids)
+  FROM package_derivations"
      (if (null? revision-commits)
          "\n"
          (string-append
-          "  INNER JOIN guix_revision_package_derivations ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
-  INNER JOIN guix_revisions ON guix_revisions.id = guix_revision_package_derivations.revision_id
+          "
+  INNER JOIN guix_revision_package_derivations
+    ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
+  INNER JOIN guix_revisions
+    ON guix_revisions.id = guix_revision_package_derivations.revision_id
+  INNER JOIN equivalent_derivations
+    ON ARRAY[derivation_id] <@ equivalent_derivations.derivation_ids
   WHERE guix_revisions.commit IN ("
           (string-join (map quote-string revision-commits) ",")
           ")"
           ))
-     ")
+     "
+  -- TODO: Filter better on what systems and targets build servers use
+  AND package_derivations.system = 'x86_64-linux'
+  AND package_derivations.target = 'x86_64-linux'
+)
 LIMIT 15000"))
 
   (exec-query conn query))