Support showing blocking builds

This is useful to find builds that have failed, and in failing blocked other
builds from being attempted.

1 files changed, 55 insertions, 0 deletions

diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm
index 4f43c5b..5c1fddc 100644
--- a/guix-build-coordinator/datastore/sqlite.scm
+++ b/guix-build-coordinator/datastore/sqlite.scm
@@ -13,6 +13,7 @@
             datastore-update
             datastore-store-derivation
             datastore-list-related-derivations-with-no-build-for-outputs
+            datastore-list-failed-builds-with-blocking-count
             datastore-list-builds-for-derivation-recursive-inputs
             datastore-store-build
             datastore-count-builds
@@ -291,6 +292,60 @@ WHERE related_derivations.name != :derivation
 
          result)))))
 
+(define-method (datastore-list-failed-builds-with-blocking-count
+                (datastore <sqlite-datastore>))
+  (call-with-worker-thread
+   (slot-ref datastore 'worker-reader-thread-channel)
+   (lambda (db)
+     (let ((statement
+            (sqlite-prepare
+             db
+             "
+SELECT
+  builds.uuid,
+  builds.derivation_name,
+  (
+    SELECT COUNT(DISTINCT blocked_builds.uuid)
+    FROM derivation_outputs
+    INNER JOIN derivation_outputs AS matching_derivation_outputs
+      ON derivation_outputs.output = matching_derivation_outputs.output
+    INNER JOIN derivation_inputs
+     ON matching_derivation_outputs.id =
+        derivation_inputs.derivation_output_id
+    INNER JOIN builds AS blocked_builds
+      ON derivation_inputs.derivation_name = blocked_builds.derivation_name
+     AND blocked_builds.processed = 0
+    WHERE derivation_outputs.derivation_name = builds.derivation_name
+  )
+FROM builds
+INNER JOIN build_results
+  ON builds.uuid = build_results.build_id
+WHERE builds.processed = 1
+  AND build_results.result = 'failure'
+  AND NOT EXISTS (
+    SELECT 1
+    FROM derivation_outputs
+    INNER JOIN derivation_outputs AS other_build_derivation_outputs
+      ON derivation_outputs.output = other_build_derivation_outputs.output
+    INNER JOIN builds AS other_builds
+      ON other_build_derivation_outputs.derivation_name = other_builds.derivation_name
+    INNER JOIN build_results AS other_build_results
+      ON other_builds.uuid = other_build_results.build_id
+    WHERE derivation_outputs.derivation_name = builds.derivation_name
+  AND other_build_results.result = 'success'
+) ORDER BY 3 DESC")))
+
+       (let ((result (sqlite-map
+                      (match-lambda
+                        (#(uuid derivation-name blocked-count)
+                         `((uuid            . ,uuid)
+                           (derivation_name . ,derivation-name)
+                           (blocked_count   . ,blocked-count))))
+                      statement)))
+         (sqlite-reset statement)
+
+         result)))))
+
 (define-method (datastore-list-builds-for-derivation-recursive-inputs
                 (datastore <sqlite-datastore>)
                 derivation)