diff options
Diffstat (limited to 'sqitch/sqlite')
3 files changed, 81 insertions, 0 deletions
diff --git a/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..67cc872 --- /dev/null +++ b/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,67 @@ +-- Deploy guix-build-coordinator:create_unprocessed_builds_with_derived_priorities to sqlite + +BEGIN; + +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_outputs + ON builds_with_derived_priority.derivation_id = + derivation_outputs.derivation_id + INNER JOIN derivation_outputs AS all_derivation_outputs + ON all_derivation_outputs.output_id = derivation_outputs.output_id + INNER JOIN derivation_inputs + ON derivation_inputs.derivation_output_id = all_derivation_outputs.id + INNER JOIN builds + ON builds.processed = 0 + AND builds.derivation_id = derivation_inputs.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/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..51b7f92 --- /dev/null +++ b/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:create_unprocessed_builds_with_derived_priorities from sqlite + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..3a17113 --- /dev/null +++ b/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:create_unprocessed_builds_with_derived_priorities on sqlite + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; |