1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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;
|