Fix unprocessed_builds_with_derived_priorities

A couple of the queries were wrong, this fixes it.

3 files changed, 84 insertions, 0 deletions

diff --git a/sqitch/sqlite/deploy/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/deploy/recreate_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..dc56ce8
--- /dev/null
+++ b/sqitch/sqlite/deploy/recreate_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,70 @@
+-- Deploy guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities to sqlite
+
+BEGIN;
+
+DROP TABLE unprocessed_builds_with_derived_priorities;
+
+CREATE TABLE unprocessed_builds_with_derived_priorities (
+  build_id INTEGER PRIMARY KEY REFERENCES builds (id),
+  derived_priority INTEGER NOT NULL,
+  all_inputs_built BOOLEAN NOT NULL
+);
+
+INSERT INTO unprocessed_builds_with_derived_priorities WITH RECURSIVE unprocessed_builds AS (
+  SELECT id
+  FROM builds
+  WHERE processed = 0
+    AND canceled = 0
+    AND (
+      deferred_until IS NULL
+      OR
+      deferred_until < datetime('now')
+    )
+), builds_with_derived_priority(
+                 id, derivation_id, derived_priority
+               ) AS (
+  SELECT builds.id, builds.derivation_id, builds.priority
+  FROM builds
+  INNER JOIN unprocessed_builds
+    ON builds.id = unprocessed_builds.id
+UNION
+  SELECT builds.id, builds.derivation_id,
+         max(builds.priority, builds_with_derived_priority.derived_priority)
+  FROM builds_with_derived_priority
+  INNER JOIN derivation_inputs
+    ON builds_with_derived_priority.derivation_id = derivation_inputs.derivation_id
+  INNER JOIN derivation_outputs
+    ON derivation_inputs.derivation_output_id = derivation_outputs.id
+  INNER JOIN unbuilt_outputs
+    ON unbuilt_outputs.output_id = derivation_outputs.output_id
+  INNER JOIN derivation_outputs AS all_derivation_outputs
+    ON all_derivation_outputs.output_id = unbuilt_outputs.output_id
+  INNER JOIN builds
+    ON builds.processed = 0
+   AND builds.derivation_id = all_derivation_outputs.derivation_id
+), data AS (
+  SELECT builds.id AS build_id, MAX(derived_priority)
+  FROM builds_with_derived_priority
+  INNER JOIN builds
+    ON builds.id = builds_with_derived_priority.id
+  WHERE builds.processed = 0
+  GROUP BY builds.id
+)
+SELECT data.*,
+       NOT EXISTS (
+         SELECT 1
+         FROM derivation_inputs
+         INNER JOIN derivation_outputs
+           ON derivation_inputs.derivation_output_id = derivation_outputs.id
+         INNER JOIN unbuilt_outputs
+           ON unbuilt_outputs.output_id = derivation_outputs.output_id
+         WHERE builds.derivation_id = derivation_inputs.derivation_id
+       )
+FROM data
+INNER JOIN builds ON builds.id = data.build_id;
+
+CREATE INDEX unprocessed_builds_with_derived_priorities_derived_priorities_index_for_all_inputs_built
+  ON unprocessed_builds_with_derived_priorities (derived_priority)
+  WHERE all_inputs_built = 1;
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/revert/recreate_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..652a2df
--- /dev/null
+++ b/sqitch/sqlite/revert/recreate_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/recreate_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/verify/recreate_unprocessed_builds_with_derived_priorities.sql
new file mode 100644
index 0000000..fcb94c6
--- /dev/null
+++ b/sqitch/sqlite/verify/recreate_unprocessed_builds_with_derived_priorities.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:recreate_unprocessed_builds_with_derived_priorities on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;