diff options
| -rw-r--r-- | guix-build-coordinator/datastore/sqlite.scm | 407 | ||||
| -rw-r--r-- | sqitch/pg/deploy/use_numeric_primary_keys.sql | 7 | ||||
| -rw-r--r-- | sqitch/pg/revert/use_numeric_primary_keys.sql | 7 | ||||
| -rw-r--r-- | sqitch/pg/verify/use_numeric_primary_keys.sql | 7 | ||||
| -rw-r--r-- | sqitch/sqitch.plan | 1 | ||||
| -rw-r--r-- | sqitch/sqlite/deploy/use_numeric_primary_keys.sql | 219 | ||||
| -rw-r--r-- | sqitch/sqlite/revert/use_numeric_primary_keys.sql | 7 | ||||
| -rw-r--r-- | sqitch/sqlite/verify/use_numeric_primary_keys.sql | 7 |
8 files changed, 496 insertions, 166 deletions
diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm index c60d5d5..fdc2427 100644 --- a/guix-build-coordinator/datastore/sqlite.scm +++ b/guix-build-coordinator/datastore/sqlite.scm @@ -682,33 +682,33 @@ INSERT INTO agent_tags (agent_id, tag_id) VALUES (:agent_id, :tag_id)" (sqlite-prepare db " -WITH RECURSIVE related_derivations(name) AS ( - VALUES(:derivation) +WITH RECURSIVE related_derivations(id) AS ( + VALUES(:derivation_id) UNION - SELECT derivation_outputs.derivation_name + SELECT derivation_outputs.derivation_id FROM derivation_outputs INNER JOIN derivation_inputs ON derivation_outputs.id = derivation_inputs.derivation_output_id INNER JOIN related_derivations - ON related_derivations.name = derivation_inputs.derivation_name + ON related_derivations.id = derivation_inputs.derivation_id ) -SELECT related_derivations.name +SELECT derivations.name FROM related_derivations INNER JOIN derivations - ON related_derivations.name = derivations.name -WHERE related_derivations.name != :derivation + ON related_derivations.id = derivations.id +WHERE related_derivations.id != :derivation_id AND NOT EXISTS ( SELECT 1 FROM builds INNER JOIN derivation_outputs AS other_derivation_derivation_outputs - ON other_derivation_derivation_outputs.derivation_name = - builds.derivation_name + ON other_derivation_derivation_outputs.derivation_id = + builds.derivation_id INNER JOIN derivations AS other_derivations - ON other_derivation_derivation_outputs.derivation_name = - other_derivations.name + ON other_derivation_derivation_outputs.derivation_id = + other_derivations.id INNER JOIN derivation_outputs ON derivation_outputs.output = other_derivation_derivation_outputs.output - WHERE derivation_outputs.derivation_name = related_derivations.name + WHERE derivation_outputs.derivation_id = related_derivations.id AND other_derivations.system = derivations.system AND builds.canceled = 0 ) @@ -717,7 +717,7 @@ WHERE related_derivations.name != :derivation (sqlite-bind-arguments statement - #:derivation derivation) + #:derivation (db-find-derivation-id db derivation)) (let ((result (sqlite-map (match-lambda @@ -742,29 +742,29 @@ SELECT * FROM ( SELECT builds.uuid, - builds.derivation_name, + derivations.name, ( - WITH RECURSIVE related_derivations(name) AS ( - VALUES(builds.derivation_name) + WITH RECURSIVE related_derivations(id) AS ( + VALUES(builds.derivation_id) UNION - SELECT derivation_inputs.derivation_name + SELECT derivation_inputs.derivation_id FROM derivation_outputs INNER JOIN related_derivations - ON derivation_outputs.derivation_name = related_derivations.name + ON derivation_outputs.derivation_id = related_derivations.id INNER JOIN derivation_inputs ON derivation_outputs.id = derivation_inputs.derivation_output_id ) SELECT COUNT(DISTINCT blocked_builds.uuid) FROM related_derivations INNER JOIN builds AS blocked_builds - ON related_derivations.name = blocked_builds.derivation_name + ON related_derivations.id = blocked_builds.derivation_id AND blocked_builds.processed = 0 AND blocked_builds.canceled = 0 ) AS blocking_count FROM builds" (if system " - INNER JOIN derivations ON derivations.name = builds.derivation_name" + INNER JOIN derivations ON derivations.id = builds.derivation_id" "") " LEFT JOIN build_results @@ -789,10 +789,10 @@ FROM ( 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 + ON other_build_derivation_outputs.derivation_id = other_builds.derivation_id 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 + WHERE derivation_outputs.derivation_id = builds.derivation_id AND other_build_results.result = 'success' ) ) AS data @@ -825,25 +825,25 @@ ORDER BY 3 DESC, 2, 1") (sqlite-prepare db " -WITH RECURSIVE related_derivations(name) AS ( - VALUES(:derivation) +WITH RECURSIVE related_derivations(id) AS ( + VALUES(:derivation_id) UNION - SELECT derivation_outputs.derivation_name + SELECT derivation_outputs.derivation_id FROM derivation_outputs INNER JOIN derivation_inputs ON derivation_outputs.id = derivation_inputs.derivation_output_id INNER JOIN related_derivations - ON related_derivations.name = derivation_inputs.derivation_name + ON related_derivations.id = derivation_inputs.derivation_id ) SELECT builds.uuid FROM builds INNER JOIN related_derivations - ON related_derivations.name = builds.derivation_name" + ON related_derivations.id = builds.derivation_id" #:cache? #t))) (sqlite-bind-arguments statement - #:derivation derivation) + #:derivation (db-find-derivation-id db derivation)) (let ((result (sqlite-map (match-lambda @@ -954,9 +954,10 @@ UPDATE builds SET canceled = 1 WHERE uuid = :uuid" " DELETE FROM build_allocation_plan WHERE build_id = :build_id" #:cache? #t))) + (sqlite-bind-arguments statement - #:build_id uuid) + #:build_id (db-find-build-id db uuid)) (sqlite-step statement) (sqlite-reset statement) @@ -989,7 +990,7 @@ SELECT agent_id, result, COUNT(*) FROM build_results GROUP BY agent_id, result" (define-method (datastore-insert-build-result (datastore <sqlite-datastore>) - build-id agent-id result failure-reason) + build-uuid agent-id result failure-reason) (call-with-worker-thread (slot-ref datastore 'worker-writer-thread-channel) (lambda (db) @@ -1000,17 +1001,18 @@ SELECT agent_id, result, COUNT(*) FROM build_results GROUP BY agent_id, result" INSERT INTO build_results ( build_id, agent_id, result, failure_reason ) VALUES ('" - build-id "', '" + (db-find-build-id db build-uuid) "', '" agent-id "', '" result "', " (if failure-reason (string-append "'" failure-reason "'") "NULL") - ")"))))) + ")")))) + #t) (define-method (datastore-remove-build-allocation (datastore <sqlite-datastore>) - build-id agent-id) + build-uuid agent-id) (call-with-worker-thread (slot-ref datastore 'worker-writer-thread-channel) (lambda (db) @@ -1019,14 +1021,15 @@ INSERT INTO build_results ( (string-append " DELETE FROM allocated_builds WHERE build_id = '" - build-id + (db-find-build-id db build-uuid) "' AND agent_id = '" agent-id - "'"))))) + "'")))) + #t) (define-method (datastore-mark-build-as-processed (datastore <sqlite-datastore>) - build-id end-time) + build-uuid end-time) (call-with-worker-thread (slot-ref datastore 'worker-writer-thread-channel) (lambda (db) @@ -1039,11 +1042,12 @@ SET processed = 1 " (if end-time (string-append ", end_time = '" end-time "'") "") " -WHERE uuid = '" build-id "'"))))) +WHERE uuid = '" (db-find-build-id db build-uuid) "'")))) + #t) (define-method (datastore-delete-relevant-outputs-from-unbuilt-outputs (datastore <sqlite-datastore>) - build-id) + build-uuid) (call-with-worker-thread (slot-ref datastore 'worker-writer-thread-channel) (lambda (db) @@ -1056,14 +1060,14 @@ WHERE output IN ( SELECT derivation_outputs.output FROM derivation_outputs INNER JOIN builds - ON builds.derivation_name = derivation_outputs.derivation_name - WHERE builds.uuid = :build_id + ON builds.derivation_id = derivation_outputs.derivation_id + WHERE builds.id = :build_id )" #:cache? #t))) (sqlite-bind-arguments statement - #:build_id build-id) + #:build_id (db-find-build-id db build-uuid)) (sqlite-step statement) (sqlite-reset statement) @@ -1071,7 +1075,7 @@ WHERE output IN ( (define-method (datastore-store-output-metadata (datastore <sqlite-datastore>) - build-id + build-uuid output-metadata) (call-with-worker-thread (slot-ref datastore 'worker-writer-thread-channel) @@ -1084,13 +1088,13 @@ WHERE output IN ( SELECT derivation_outputs.id FROM derivation_outputs INNER JOIN builds - ON builds.derivation_name = derivation_outputs.derivation_name -WHERE builds.uuid = :build_id AND derivation_outputs.name = :name" + ON builds.derivation_id = derivation_outputs.derivation_id +WHERE builds.uuid = :build_uuid AND derivation_outputs.name = :name" #:cache? #t))) (sqlite-bind-arguments statement - #:build_id build-id + #:build_uuid build-uuid #:name name) (match (sqlite-step statement) @@ -1110,7 +1114,7 @@ VALUES " (map (lambda (output) (simple-format #f "('~A', ~A, '~A', ~A, '~A')" - build-id + (db-find-build-id db build-uuid) (name->output-id (assoc-ref output "name")) (assoc-ref output "hash") (assoc-ref output "size") @@ -1123,14 +1127,14 @@ VALUES " (define-method (datastore-store-build-start (datastore <sqlite-datastore>) - build-id + build-uuid agent-id) - (define (handle-inserting-unprocessed-hook-event db build-id agent-id) + (define (handle-inserting-unprocessed-hook-event db build-uuid agent-id) (insert-unprocessed-hook-event db "build-started" - (list build-id agent-id))) + (list build-uuid agent-id))) (datastore-call-with-transaction datastore @@ -1142,18 +1146,18 @@ VALUES " INSERT INTO build_starts ( build_id, agent_id, start_time ) VALUES ('" - build-id "', '" + (db-find-build-id db build-uuid) "', '" agent-id "', " "datetime('now')" ")")) - (handle-inserting-unprocessed-hook-event db build-id agent-id))) + (handle-inserting-unprocessed-hook-event db build-uuid agent-id))) #t) (define-method (datastore-find-build-starts (datastore <sqlite-datastore>) - build-id) + build-uuid) (call-with-worker-thread (slot-ref datastore 'worker-reader-thread-channel) (lambda (db) @@ -1169,7 +1173,7 @@ ORDER BY start_time DESC" (sqlite-bind-arguments statement - #:build_id build-id) + #:build_id (db-find-build-id db build-uuid)) (let ((result (sqlite-map @@ -1214,7 +1218,7 @@ INSERT INTO setup_failures ( (define-method (datastore-store-setup-failure/missing-inputs (datastore <sqlite-datastore>) - build-id + build-uuid agent-id missing-inputs) (define (insert-missing-inputs db setup-failure-id missing-inputs) @@ -1232,25 +1236,26 @@ INSERT INTO setup_failure_missing_inputs ( missing-inputs) ", ")))) - (define (handle-inserting-unprocessed-hook-event db build-id missing-inputs) + (define (handle-inserting-unprocessed-hook-event db build-uuid missing-inputs) (insert-unprocessed-hook-event db "build-missing-inputs" - (list build-id + (list build-uuid missing-inputs))) (datastore-call-with-transaction datastore (lambda (db) - (let ((setup-failure-id - (insert-setup-failure-and-remove-allocation db - build-id - agent-id - "missing_inputs"))) + (let* ((build-id (db-find-build-id db build-uuid)) + (setup-failure-id + (insert-setup-failure-and-remove-allocation db + build-id + agent-id + "missing_inputs"))) (insert-missing-inputs db setup-failure-id missing-inputs)) - ;; This logic should be part of the coordinator, but it's here to be + ;; TODO This logic should be part of the coordinator, but it's here to be ;; inside the transaction - (handle-inserting-unprocessed-hook-event db build-id missing-inputs)) + (handle-inserting-unprocessed-hook-event db build-uuid missing-inputs)) #:duration-metric-name "store_setup_failure_missing_inputs") (metric-increment @@ -1289,14 +1294,14 @@ WHERE setup_failure_id = :id" (define-method (datastore-store-setup-failure (datastore <sqlite-datastore>) - build-id + build-uuid agent-id failure-reason) (call-with-worker-thread (slot-ref datastore 'worker-writer-thread-channel) (lambda (db) (insert-setup-failure-and-remove-allocation db - build-id + (db-find-build-id db build-uuid) agent-id failure-reason) @@ -1320,7 +1325,7 @@ WHERE setup_failure_id = :id" SELECT derivations.system, COUNT(*) FROM builds INNER JOIN derivations - ON builds.derivation_name = derivations.name + ON builds.derivation_id = derivations.id GROUP BY derivations.system" #:cache? #t))) @@ -1344,7 +1349,7 @@ GROUP BY derivations.system" (sqlite-prepare db " -SELECT uuid FROM builds ORDER BY uuid" +SELECT uuid FROM builds ORDER BY id" #:cache? #t))) (let loop ((row (sqlite-step statement))) @@ -1358,6 +1363,26 @@ SELECT uuid FROM builds ORDER BY uuid" #t))))))) +(define (db-find-build-id db uuid) + (let ((statement + (sqlite-prepare + db + " +SELECT id FROM builds WHERE uuid = :uuid" + #:cache? #t))) + + (sqlite-bind-arguments + statement + #:uuid uuid) + + (let ((result + (match (sqlite-step statement) + (#f #f) + (#(id) id)))) + (sqlite-reset statement) + + result))) + (define-method (datastore-find-build (datastore <sqlite-datastore>) uuid) @@ -1368,8 +1393,10 @@ SELECT uuid FROM builds ORDER BY uuid" (sqlite-prepare db " -SELECT uuid, derivation_name, priority, processed, canceled, created_at, end_time +SELECT uuid, derivations.name, priority, processed, canceled, created_at, end_time FROM builds +INNER JOIN derivations + ON derivations.id = builds.derivation_id WHERE uuid = :uuid" #:cache? #t))) @@ -1505,20 +1532,20 @@ SELECT id FROM tags WHERE key = :key" db (string-append " -SELECT uuid, derivation_name, priority, processed, canceled, created_at, end_time +SELECT uuid, derivations.name, priority, processed, canceled, created_at, end_time FROM builds" (if (and (null? systems) (null? not-systems)) "" " -INNER JOIN derivations ON builds.derivation_name = derivations.name") +INNER JOIN derivations ON builds.derivation_id = derivations.id") " LEFT JOIN ( SELECT build_id, (',' || group_concat(tag_id) || ',') AS tag_string FROM build_tags GROUP BY build_id ) AS all_build_tags - ON builds.uuid = all_build_tags.build_id + ON builds.id = all_build_tags.build_id " (if where-needed? (string-append @@ -1612,7 +1639,7 @@ LEFT JOIN ( (define-method (datastore-fetch-build-tags (datastore <sqlite-datastore>) - build-id) + build-uuid) (call-with-worker-thread (slot-ref datastore 'worker-reader-thread-channel) (lambda (db) @@ -1629,7 +1656,7 @@ WHERE build_tags.build_id = :build_id" (sqlite-bind-arguments statement - #:build_id build-id) + #:build_id (db-find-build-id db build-uuid)) (let ((result (list->vector @@ -1647,7 +1674,7 @@ WHERE build_tags.build_id = :build_id" (define-method (datastore-find-build-result (datastore <sqlite-datastore>) - build-id) + build-uuid) (call-with-worker-thread (slot-ref datastore 'worker-reader-thread-channel) (lambda (db) @@ -1662,7 +1689,7 @@ WHERE build_id = :build_id" (sqlite-bind-arguments statement - #:build_id build-id) + #:build_id (db-find-build-id db build-uuid)) (let ((result (match (sqlite-step statement) @@ -1677,7 +1704,7 @@ WHERE build_id = :build_id" (define-method (datastore-find-build-derivation-system (datastore <sqlite-datastore>) - build-id) + build-uuid) (call-with-worker-thread (slot-ref datastore 'worker-reader-thread-channel) (lambda (db) @@ -1687,13 +1714,13 @@ WHERE build_id = :build_id" " SELECT derivations.system FROM builds -INNER JOIN derivations ON builds.derivation_name = derivations.name -WHERE builds.uuid = :build_id" +INNER JOIN derivations ON builds.derivation_id = derivations.id +WHERE builds.id = :build_id" #:cache? #t))) (sqlite-bind-arguments statement - #:build_id build-id) + #:build_id (db-find-build-id db build-uuid)) (let ((system (match (sqlite-step statement) @@ -1716,12 +1743,14 @@ WHERE builds.uuid = :build_id" (sqlite-prepare db " -SELECT uuid, builds.derivation_name, priority, processed, canceled, result +SELECT uuid, derivations.name, priority, processed, canceled, result FROM builds +INNER JOIN derivations + ON builds.derivation_id = derivations.id INNER JOIN derivation_outputs - ON builds.derivation_name = derivation_outputs.derivation_name + ON builds.derivation_id = derivation_outputs.derivation_id LEFT JOIN build_results - ON builds.uuid = build_results.build_id + ON builds.id = build_results.build_id WHERE derivation_outputs.output = :output" #:cache? #t))) @@ -1764,12 +1793,12 @@ WHERE derivation_outputs.output = :output" db (string-append " -SELECT uuid, builds.derivation_name +SELECT uuid, derivations.name FROM builds INNER JOIN derivation_outputs - ON builds.derivation_name = derivation_outputs.derivation_name + ON builds.derivation_id = derivation_outputs.derivation_id INNER JOIN derivations - ON builds.derivation_name = derivations.name + ON builds.derivation_id = derivations.id WHERE derivation_outputs.output = :output AND derivations.system = :system" (if include-canceled? @@ -1811,7 +1840,9 @@ WHERE derivation_outputs.output = :output " SELECT COUNT(*) FROM builds -WHERE derivation_name = :derivation" +INNER JOIN derivations + ON builds.derivation_id = derivations.id +WHERE derivations.name = :derivation" (if include-canceled? "" " @@ -1863,7 +1894,7 @@ GROUP BY agent_id, failure_reason" (define-method (datastore-list-setup-failures-for-build (datastore <sqlite-datastore>) - build-id) + build-uuid) (call-with-worker-thread (slot-ref datastore 'worker-reader-thread-channel) (lambda (db) @@ -1878,7 +1909,7 @@ WHERE build_id = :build_id" (sqlite-bind-arguments statement - #:build_id build-id) + #:build_id (db-find-build-id db build-uuid)) (let ((result (sqlite-map (match-lambda @@ -1900,13 +1931,13 @@ WHERE build_id = :build_id" (sqlite-prepare db " -SELECT id, build_id, agent_id, failure_reason +SELECT setup_failures.id, builds.uuid, agent_id, failure_reason FROM setup_failures INNER JOIN builds - ON builds.uuid = setup_failures.build_id + ON builds.id = setup_failures.build_id WHERE builds.processed = 0 AND builds.canceled = 0 - AND uuid NOT IN ( + AND builds.id NOT IN ( SELECT build_id FROM allocated_builds )" #:cache? #t))) @@ -1941,7 +1972,11 @@ WHERE builds.processed = 0 (sqlite-prepare db " -SELECT uuid, derivation_name, priority FROM builds WHERE processed = 1" +SELECT uuid, derivations.name, priority +FROM builds +INNER JOIN derivations + ON builds.derivation_id = derivations.id +WHERE processed = 1" #:cache? #t))) (let ((builds (sqlite-map @@ -1964,8 +1999,10 @@ SELECT uuid, derivation_name, priority FROM builds WHERE processed = 1" (sqlite-prepare db " -SELECT uuid, derivation_name, priority +SELECT uuid, derivations.name, priority FROM builds +INNER JOIN derivations + ON builds.derivation_id = derivations.id WHERE processed = 0 AND canceled = 0 AND ( @@ -1973,7 +2010,7 @@ WHERE processed = 0 OR deferred_until < datetime('now') ) - AND uuid NOT IN ( + AND builds.id NOT IN ( SELECT build_id FROM allocated_builds ) ORDER BY priority DESC" @@ -1999,12 +2036,14 @@ ORDER BY priority DESC" (sqlite-prepare db " -SELECT uuid, derivation_name, priority, created_at, deferred_until +SELECT uuid, derivations.name, priority, created_at, deferred_until FROM builds +INNER JOIN derivations + ON builds.derivation_id = derivations.id WHERE processed = 0 AND canceled = 0 AND deferred_until IS NOT NULL - AND uuid NOT IN (SELECT build_id FROM build_allocation_plan) + AND builds.id NOT IN (SELECT build_id FROM build_allocation_plan) ORDER BY deferred_until ASC LIMIT 1" #:cache? #t))) @@ -2043,7 +2082,7 @@ DROP TABLE IF EXISTS temp.unprocessed_builds") (string-append " CREATE TEMP TABLE unprocessed_builds AS -SELECT uuid +SELECT id FROM builds WHERE processed = 0 AND canceled = 0 @@ -2066,7 +2105,7 @@ AND NOT EXISTS ( ON derivation_inputs.derivation_output_id = derivation_outputs.id INNER JOIN unbuilt_outputs ON unbuilt_outputs.output = derivation_outputs.output - WHERE builds.derivation_name = derivation_inputs.derivation_name + WHERE builds.derivation_id = derivation_inputs.derivation_id ) EXCEPT SELECT build_id FROM allocated_builds @@ -2097,32 +2136,32 @@ EXCEPT db " WITH RECURSIVE builds_with_derived_priority( - uuid, derivation_name, derived_priority + id, derivation_id, derived_priority ) AS ( - SELECT builds.uuid, builds.derivation_name, builds.priority + SELECT builds.id, builds.derivation_id, builds.priority FROM builds INNER JOIN unprocessed_builds - ON builds.uuid = unprocessed_builds.uuid + ON builds.id = unprocessed_builds.id UNION - SELECT builds.uuid, builds.derivation_name, + 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_name = - derivation_outputs.derivation_name + ON builds_with_derived_priority.derivation_id = + derivation_outputs.derivation_id INNER JOIN derivation_outputs AS all_derivation_outputs ON all_derivation_outputs.output = derivation_outputs.output INNER JOIN derivation_inputs ON derivation_inputs.derivation_output_id = all_derivation_outputs.id INNER JOIN builds ON builds.processed = 0 - AND builds.derivation_name = derivation_inputs.derivation_name + AND builds.derivation_id = derivation_inputs.derivation_id ) -SELECT builds_with_derived_priority.uuid, MAX(derived_priority) +SELECT builds.uuid, MAX(derived_priority) FROM builds_with_derived_priority -INNER JOIN builds ON builds.uuid = builds_with_derived_priority.uuid -WHERE builds.processed = 0 -GROUP BY builds_with_derived_priority.uuid" +INNER JOIN builds + ON builds.id = builds_with_derived_priority.id +WHERE builds.processed = 0" #:cache? #t))) (let ((result (sqlite-fold @@ -2295,11 +2334,11 @@ GROUP BY agent_id" INSERT INTO build_allocation_plan (build_id, agent_id, ordering) VALUES " (string-join (map (match-lambda - ((build-id agent-id ordering) + ((build-uuid agent-id ordering) (simple-format #f "('~A', '~A', ~A)" - build-id + (db-find-build-id db build-uuid) agent-id ordering))) planned-builds) @@ -2450,19 +2489,19 @@ INSERT INTO build_allocation_agent_requested_systems (agent_id, system) VALUES " db ;; This needs to guard against the plan being out of date " -SELECT builds.uuid, builds.derivation_name +SELECT builds.uuid, derivations.name FROM builds INNER JOIN build_allocation_plan - ON builds.uuid = build_allocation_plan.build_id + ON builds.id = build_allocation_plan.build_id INNER JOIN derivations - ON builds.derivation_name = derivations.name + ON builds.derivation_id = derivations.id INNER JOIN build_allocation_agent_requested_systems ON build_allocation_agent_requested_systems.agent_id = :agent_id AND build_allocation_agent_requested_systems.system = derivations.system WHERE build_allocation_plan.agent_id = :agent_id AND builds.processed = 0 AND builds.canceled = 0 - AND builds.uuid NOT IN (SELECT build_id FROM allocated_builds) + AND builds.id NOT IN (SELECT build_id FROM allocated_builds) ORDER BY build_allocation_plan.ordering DESC" #:cache? #t)) (output-conflicts-statement @@ -2474,11 +2513,11 @@ FROM derivation_outputs AS build_derivation_outputs INNER JOIN allocated_builds ON allocated_builds.agent_id = :agent_id INNER JOIN builds AS allocated_build_details - ON allocated_build_details.uuid = allocated_builds.build_id + ON allocated_build_details.id = allocated_builds.build_id INNER JOIN derivation_outputs AS allocated_builds_derivation_outputs - ON allocated_build_details.derivation_name = - allocated_builds_derivation_outputs.derivation_name -WHERE build_derivation_outputs.derivation_name = :derivation_name + ON allocated_build_details.derivation_id = + allocated_builds_derivation_outputs.derivation_id +WHERE build_derivation_outputs.derivation_id = :derivation_id AND build_derivation_outputs.output = allocated_builds_derivation_outputs.output" #:cache? #t))) @@ -2505,8 +2544,10 @@ WHERE build_derivation_outputs.derivation_name = :derivation_name (find (lambda (build-details) (sqlite-bind-arguments output-conflicts-statement - #:derivation_name (assq-ref build-details - 'derivation_name)) + #:derivation_name + (db-find-derivation-id db + (assq-ref build-details + 'derivation_name))) (let ((result (sqlite-step output-conflicts-statement))) (sqlite-reset output-conflicts-statement) @@ -2520,7 +2561,7 @@ WHERE build_derivation_outputs.derivation_name = :derivation_name (define-method (datastore-insert-to-allocated-builds (datastore <sqlite-datastore>) agent-id - build-ids) + build-uuids) (call-with-worker-thread (slot-ref datastore 'worker-writer-thread-channel) (lambda (db) @@ -2530,19 +2571,19 @@ WHERE build_derivation_outputs.derivation_name = :derivation_name " INSERT INTO allocated_builds (build_id, agent_id) VALUES " (string-join - (map (lambda (build-id) + (map (lambda (build-uuid) (simple-format #f "('~A', '~A')" - build-id + (db-find-build-id db build-uuid) agent-id)) - build-ids) + build-uuids) ", ") ";"))))) (define-method (datastore-remove-builds-from-plan (datastore <sqlite-datastore>) - build-ids) + build-uuids) (call-with-worker-thread (slot-ref datastore 'worker-writer-thread-channel) (lambda (db) @@ -2553,9 +2594,11 @@ INSERT INTO allocated_builds (build_id, agent_id) VALUES " DELETE FROM build_allocation_plan WHERE build_id IN (" (string-join - (map (lambda (build-id) - (string-append "'" build-id "'")) - build-ids) + (map (lambda (build-uuid) + (string-append "'" + (db-find-build-id db build-uuid) + "'")) + build-uuids) ", ") ")"))))) @@ -2569,10 +2612,12 @@ WHERE build_id IN (" (sqlite-prepare db " -SELECT builds.uuid, builds.derivation_name +SELECT builds.uuid, derivations.name FROM allocated_builds INNER JOIN builds - ON allocated_builds.build_id = builds.uuid + ON allocated_builds.build_id = builds.id +INNER JOIN derivations + ON builds.derivation_id = derivations.id WHERE agent_id = :agent_id" #:cache? #t))) @@ -2609,13 +2654,15 @@ WHERE agent_id = :agent_id" db ;; This needs to guard against the plan being out of date " -SELECT builds.uuid, builds.derivation_name +SELECT builds.uuid, derivations.name FROM builds +INNER JOIN derivations + ON builds.derivation_id = derivations.id INNER JOIN build_allocation_plan - ON builds.uuid = build_allocation_plan.build_id + ON builds.id = build_allocation_plan.build_id WHERE build_allocation_plan.agent_id = :agent_id AND builds.processed = 0 - AND builds.uuid NOT IN (SELECT build_id FROM allocated_builds) + AND builds.id NOT IN (SELECT build_id FROM allocated_builds) ORDER BY build_allocation_plan.ordering ASC LIMIT :limit" #:cache? #t))) @@ -2645,10 +2692,12 @@ LIMIT :limit" (sqlite-prepare db " -SELECT builds.uuid, builds.derivation_name, builds.priority +SELECT builds.uuid, derivations.name, builds.priority FROM builds +INNER JOIN derivations + ON builds.derivation_id = derivations.id INNER JOIN allocated_builds - ON builds.uuid = allocated_builds.build_id + ON builds.id = allocated_builds.build_id WHERE allocated_builds.agent_id = :agent_id" #:cache? #t))) @@ -2670,7 +2719,7 @@ WHERE allocated_builds.agent_id = :agent_id" (define-method (datastore-agent-for-build (datastore <sqlite-datastore>) - build-id) + build-uuid) (call-with-worker-thread (slot-ref datastore 'worker-reader-thread-channel) (lambda (db) @@ -2688,7 +2737,7 @@ WHERE build_results.build_id = :build_id" (sqlite-bind-arguments statement - #:build_id build-id) + #:build_id (db-find-build-id db build-uuid)) (let ((result (match (sqlite-step statement) @@ -2777,12 +2826,12 @@ WHERE build_results.build_id = :build_id" (sqlite-prepare db " -SELECT name, id FROM derivation_outputs WHERE derivation_name = :derivation_name" +SELECT name, id FROM derivation_outputs WHERE derivation_id = :derivation_id" #:cache? #t))) (sqlite-bind-arguments statement - #:derivation_name derivation-name) + #:derivation_id (db-find-derivation-id db derivation-name)) (let ((outputs (sqlite-map (match-lambda @@ -2793,6 +2842,26 @@ SELECT name, id FROM derivation_outputs WHERE derivation_name = :derivation_name outputs))) +(define (db-find-derivation-id db name) + (let ((statement + (sqlite-prepare + db + " +SELECT id FROM derivations WHERE name = :name" + #:cache? #t))) + + (sqlite-bind-arguments + statement + #:name name) + + (let ((result + (match (sqlite-step statement) + (#f #f) + (#(id) id)))) + (sqlite-reset statement) + + result))) + (define (db-find-derivation db name) (let ((statement (sqlite-prepare @@ -2840,12 +2909,12 @@ WHERE name = :name" " SELECT name, output FROM derivation_outputs -WHERE derivation_name = :derivation_name" +WHERE derivation_id = :derivation_id" #:cache? #t))) (sqlite-bind-arguments statement - #:derivation_name derivation-name) + #:derivation_id (db-find-derivation-id db derivation-name)) (let ((result (sqlite-map @@ -2872,12 +2941,12 @@ SELECT derivation_outputs.name, derivation_outputs.output FROM derivation_outputs INNER JOIN unbuilt_outputs ON derivation_outputs.output = unbuilt_outputs.output -WHERE derivation_name = :derivation_name" +WHERE derivation_id = :derivation_id" #:cache? #t))) (sqlite-bind-arguments statement - #:derivation_name derivation-name) + #:derivation_id (db-find-derivation-id db derivation-name)) (let ((result (sqlite-map @@ -2892,7 +2961,7 @@ WHERE derivation_name = :derivation_name" (define-method (datastore-list-build-outputs (datastore <sqlite-datastore>) - build-id) + build-uuid) (call-with-worker-thread (slot-ref datastore 'worker-reader-thread-channel) (lambda (db) @@ -2903,16 +2972,16 @@ WHERE derivation_name = :derivation_name" SELECT name, output, hash, size, store_references FROM builds INNER JOIN derivation_outputs - ON builds.derivation_name = derivation_outputs.derivation_name + ON builds.derivation_id = derivation_outputs.derivation_id LEFT JOIN output_metadata ON output_metadata.derivation_output_id = derivation_outputs.id AND output_metadata.build_id = builds.uuid -WHERE builds.uuid = :build_id" +WHERE builds.id = :build_id" #:cache? #t))) (sqlite-bind-arguments statement - #:build_id build-id) + #:build_id (db-find-build-id db build-uuid)) (let ((result (sqlite-map @@ -2967,17 +3036,21 @@ WHERE name = :name" (sqlite-prepare db " -SELECT derivation_outputs.derivation_name, +SELECT output_derivations.name, derivation_outputs.name, derivation_outputs.output -FROM derivation_inputs +FROM derivations +INNER JOIN derivation_inputs + ON derivation_inputs.derivation_id = derivations.id INNER JOIN derivation_outputs ON derivation_inputs.derivation_output_id = derivation_outputs.id -WHERE derivation_inputs.derivation_name = :derivation_name" +INNER JOIN derivations AS output_derivations + ON output_derivations.id = derivation_outputs.derivation_id +WHERE derivations.id = :derivation_id" #:cache? #t))) (sqlite-bind-arguments statement - #:derivation_name derivation-name) + #:derivation_id (db-find-derivation-id db derivation-name)) (let ((result (sqlite-map @@ -3002,20 +3075,22 @@ WHERE derivation_inputs.derivation_name = :derivation_name" (sqlite-prepare db " -WITH RECURSIVE related_derivations(name) AS ( - VALUES(:derivation) +WITH RECURSIVE related_derivations(id) AS ( + VALUES(:derivation_id) UNION - SELECT derivation_outputs.derivation_name + SELECT derivation_outputs.derivation_id FROM derivation_outputs INNER JOIN derivation_inputs ON derivation_outputs.id = derivation_inputs.derivation_output_id INNER JOIN related_derivations - ON related_derivations.name = derivation_inputs.derivation_name + ON related_derivations.id = derivation_inputs.derivation_id ) -SELECT related_derivations.name +SELECT derivations.name FROM related_derivations +INNER JOIN derivations + ON derivations.id = related_derivations.id INNER JOIN derivation_outputs - ON related_derivations.name = derivation_outputs.derivation_name + ON related_derivations.id = derivation_outputs.derivation_id WHERE output = :output " #:cache? #t))) @@ -3103,13 +3178,13 @@ INSERT OR IGNORE INTO derivations (name, system, fixed_output) db (string-append " -INSERT INTO derivation_inputs (derivation_name, derivation_output_id) VALUES " +INSERT INTO derivation_inputs (derivation_id, derivation_output_id) VALUES " (string-join (map (lambda (derivation-output-id) (simple-format #f "('~A', ~A)" - derivation-name + (db-find-derivation-id db derivation-name) derivation-output-id)) derivation-output-ids) ", ") @@ -3124,7 +3199,7 @@ INSERT INTO derivation_inputs (derivation_name, derivation_output_id) VALUES " SELECT build_results.result FROM derivation_outputs INNER JOIN builds - ON builds.derivation_name = derivation_outputs.derivation_name + ON builds.derivation_id = derivation_outputs.derivation_id INNER JOIN build_results ON builds.uuid = build_results.build_id WHERE derivation_outputs.output = :output" @@ -3164,14 +3239,14 @@ INSERT OR IGNORE INTO unbuilt_outputs (output) VALUES (:output)" db (string-append " -INSERT INTO derivation_outputs (derivation_name, name, output) VALUES " +INSERT INTO derivation_outputs (derivation_id, name, output) VALUES " (string-join (map (match-lambda ((name . derivation-output) (simple-format #f "('~A', '~A', '~A')" - derivation-name + (db-find-derivation-id db derivation-name) name (derivation-output-path derivation-output)))) derivation-outputs) @@ -3198,14 +3273,14 @@ INSERT INTO derivation_outputs (derivation_name, name, output) VALUES " (sqlite-prepare db " -INSERT INTO builds (uuid, derivation_name, priority, created_at, deferred_until) -VALUES (:uuid, :derivation_name, :priority, datetime('now'), :deferred_until)" +INSERT INTO builds (uuid, derivation_id, priority, created_at, deferred_until) +VALUES (:uuid, :derivation_id, :priority, datetime('now'), :deferred_until)" #:cache? #t))) (sqlite-bind-arguments statement #:uuid uuid - #:derivation_name derivation-name + #:derivation_id (db-find-derivation-id db derivation-name) #:priority priority #:deferred_until (and=> defer-until diff --git a/sqitch/pg/deploy/use_numeric_primary_keys.sql b/sqitch/pg/deploy/use_numeric_primary_keys.sql new file mode 100644 index 0000000..e659bb0 --- /dev/null +++ b/sqitch/pg/deploy/use_numeric_primary_keys.sql @@ -0,0 +1,7 @@ +-- Deploy guix-build-coordinator:use_numeric_primary_keys to pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/revert/use_numeric_primary_keys.sql b/sqitch/pg/revert/use_numeric_primary_keys.sql new file mode 100644 index 0000000..df77446 --- /dev/null +++ b/sqitch/pg/revert/use_numeric_primary_keys.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:use_numeric_primary_keys from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/verify/use_numeric_primary_keys.sql b/sqitch/pg/verify/use_numeric_primary_keys.sql new file mode 100644 index 0000000..8a5674d --- /dev/null +++ b/sqitch/pg/verify/use_numeric_primary_keys.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:use_numeric_primary_keys on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index 19e2b11..c373470 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -28,3 +28,4 @@ add_builds_deferred_until 2020-12-26T19:26:32Z Christopher Baines <mail@cbaines. add_agent_tags 2021-01-17T11:13:23Z Christopher Baines <mail@cbaines.net> # Add tags for agents add_agent_names 2021-02-28T14:04:24Z Christopher Baines <mail@cbaines.net> # Add agent.names add_dynamic_auth_tokens 2021-02-28T15:21:24Z Christopher Baines <mail@cbaines.net> # Add dynamic_auth_tokens +use_numeric_primary_keys 2021-03-28T09:55:40Z Christopher Baines <mail@cbaines.net> # Use numeric primary keys diff --git a/sqitch/sqlite/deploy/use_numeric_primary_keys.sql b/sqitch/sqlite/deploy/use_numeric_primary_keys.sql new file mode 100644 index 0000000..7d5fda7 --- /dev/null +++ b/sqitch/sqlite/deploy/use_numeric_primary_keys.sql @@ -0,0 +1,219 @@ +-- Deploy guix-build-coordinator:use_numeric_primary_keys to sqlite + +BEGIN; + +ALTER TABLE derivations RENAME TO old_derivations; + +CREATE TABLE derivations ( + id INTEGER PRIMARY KEY ASC, + name TEXT NOT NULL, + system TEXT NOT NULL, + fixed_output BOOLEAN + CHECK (fixed_output IN (0,1)) +); + +INSERT INTO derivations (name, system, fixed_output) + SELECT name, system, fixed_output FROM old_derivations; + +DROP TABLE old_derivations; + +CREATE UNIQUE INDEX derivations_name_idx ON derivations (name); + +-- derivation_outputs + +ALTER TABLE derivation_outputs RENAME TO old_derivation_outputs; + +CREATE TABLE derivation_outputs ( + id INTEGER PRIMARY KEY ASC, + derivation_id INTEGER NOT NULL REFERENCES derivations (id), + name TEXT NOT NULL, + output TEXT NOT NULL +); + +INSERT INTO derivation_outputs (id, derivation_id, name, output) + SELECT old_derivation_outputs.id, + derivations.id, + old_derivation_outputs.name, + old_derivation_outputs.output + FROM old_derivation_outputs + INNER JOIN derivations + ON derivations.name = old_derivation_outputs.derivation_name; + +DROP TABLE old_derivation_outputs; + +CREATE INDEX derivation_outputs_output_idx + ON derivation_outputs (output); +CREATE INDEX derivation_outputs_unique_idx + ON derivation_outputs (derivation_id, name); + +-- derivation_inputs + +ALTER TABLE derivation_inputs RENAME TO old_derivation_inputs; + +CREATE TABLE derivation_inputs ( + derivation_id INTEGER REFERENCES derivations (id), + derivation_output_id INTEGER REFERENCES derivation_outputs (id), + PRIMARY KEY (derivation_id, derivation_output_id) +); + +INSERT INTO derivation_inputs (derivation_id, derivation_output_id) + SELECT derivations.id, derivation_output_id + FROM old_derivation_inputs + INNER JOIN derivations + ON derivations.name = old_derivation_inputs.derivation_name; + +DROP TABLE old_derivation_inputs; + +CREATE INDEX derivation_inputs_derivation_output_id + ON derivation_inputs (derivation_output_id); + +-- builds + +ALTER TABLE builds RENAME TO old_builds; + +CREATE TABLE builds ( + id INTEGER PRIMARY KEY ASC, + uuid TEXT NOT NULL, + derivation_id NUMBER NOT NULL REFERENCES derivations (id), + priority INTEGER NOT NULL, + processed BOOLEAN NOT NULL DEFAULT 0, + created_at TEXT, + end_time TEXT, + canceled BOOLEAN NOT NULL DEFAULT 0, + deferred_until TEXT +); + +INSERT INTO builds (uuid, derivation_id, priority, processed, created_at, end_time, canceled, deferred_until) + SELECT uuid, derivations.id, priority, processed, created_at, end_time, canceled, deferred_until + FROM old_builds + INNER JOIN derivations ON derivations.name = old_builds.derivation_name; + +DROP TABLE old_builds; + +CREATE UNIQUE INDEX builds_uuid ON builds (uuid); +CREATE INDEX builds_derivation_id_idx ON builds (derivation_id); +CREATE INDEX builds_unprocessed ON builds (processed) WHERE processed = 0; + +-- allocated_builds + +ALTER TABLE allocated_builds RENAME TO old_allocated_builds; + +CREATE TABLE allocated_builds ( + build_id PRIMARY KEY NOT NULL REFERENCES builds (id), + agent_id NOT NULL REFERENCES agents (id) +); + +INSERT INTO allocated_builds (build_id, agent_id) + SELECT builds.id, agent_id + FROM old_allocated_builds + INNER JOIN builds + ON builds.uuid = old_allocated_builds.build_id; + +DROP TABLE old_allocated_builds; + +-- build_allocation_plan + +DROP TABLE build_allocation_plan; + +CREATE TABLE build_allocation_plan ( + build_id NOT NULL REFERENCES builds (id), + agent_id NOT NULL REFERENCES agents (id), + ordering INTEGER NOT NULL, + PRIMARY KEY (agent_id, build_id) +); + +-- build_results + +ALTER TABLE build_results RENAME TO old_build_results; + +CREATE TABLE build_results ( + build_id INTEGER PRIMARY KEY ASC REFERENCES builds (id), + agent_id TEXT NOT NULL REFERENCES agents (id), + result TEXT NOT NULL, + failure_reason +); + +INSERT INTO build_results (build_id, agent_id, result, failure_reason) + SELECT builds.id, agent_id, result, failure_reason + FROM old_build_results + INNER JOIN builds ON builds.uuid = old_build_results.build_id; + +DROP TABLE old_build_results; + +CREATE INDEX build_results_result_idx ON build_results (result); + +-- setup_failures + +ALTER TABLE setup_failures RENAME TO old_setup_failures; + +CREATE TABLE setup_failures ( + id INTEGER PRIMARY KEY ASC, + build_id INTEGER NOT NULL REFERENCES builds (id), + agent_id TEXT NOT NULL REFERENCES agents (id), + failure_reason TEXT NOT NULL +); + +INSERT INTO setup_failures (id, build_id, agent_id, failure_reason) + SELECT old_setup_failures.id, builds.id, old_setup_failures.agent_id, old_setup_failures.failure_reason + FROM old_setup_failures + INNER JOIN builds ON builds.uuid = old_setup_failures.build_id; + +DROP TABLE old_setup_failures; + +-- output_metadata + +ALTER TABLE output_metadata RENAME TO old_output_metadata; + +CREATE TABLE output_metadata ( + build_id INTEGER NOT NULL REFERENCES builds (id), + derivation_output_id INTEGER NOT NULL REFERENCES derivation_outputs (id), + hash TEXT NOT NULL, + size INTEGER NOT NULL, + store_references TEXT NOT NULL +); + +INSERT INTO output_metadata + (build_id, derivation_output_id, hash, size, store_references) + SELECT builds.id, old_output_metadata.derivation_output_id, old_output_metadata.hash, old_output_metadata.size, old_output_metadata.store_references + FROM old_output_metadata + INNER JOIN builds ON builds.uuid = old_output_metadata.build_id; + +DROP TABLE old_output_metadata; + +-- build_tags + +ALTER TABLE build_tags RENAME TO old_build_tags; + +CREATE TABLE build_tags ( + build_id INTEGER NOT NULL REFERENCES builds (id), + tag_id INTEGER NOT NULL REFERENCES tags (id) +); + +INSERT INTO build_tags (build_id, tag_id) + SELECT builds.id, tag_id + FROM old_build_tags + INNER JOIN builds ON builds.uuid = old_build_tags.build_id; + +DROP TABLE old_build_tags; + +CREATE INDEX build_tags_build_id_idx ON build_tags (build_id); + +-- build_starts + +ALTER TABLE build_starts RENAME TO old_build_starts; + +CREATE TABLE build_starts ( + id INTEGER PRIMARY KEY ASC, + build_id INTEGER NOT NULL REFERENCES builds (id), + agent_id TEXT NOT NULL REFERENCES agents (id), + start_time TEXT NOT NULL +); + +INSERT INTO build_starts (id, build_id, agent_id, start_time) + SELECT old_build_starts.id, builds.id, old_build_starts.agent_id, old_build_starts.start_time + FROM old_build_starts + INNER JOIN builds ON builds.uuid = old_build_starts.build_id; + +DROP TABLE old_build_starts; + +COMMIT; diff --git a/sqitch/sqlite/revert/use_numeric_primary_keys.sql b/sqitch/sqlite/revert/use_numeric_primary_keys.sql new file mode 100644 index 0000000..fdb22de --- /dev/null +++ b/sqitch/sqlite/revert/use_numeric_primary_keys.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:use_numeric_primary_keys from sqlite + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqlite/verify/use_numeric_primary_keys.sql b/sqitch/sqlite/verify/use_numeric_primary_keys.sql new file mode 100644 index 0000000..86818aa --- /dev/null +++ b/sqitch/sqlite/verify/use_numeric_primary_keys.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:use_numeric_primary_keys on sqlite + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; |