From f89f70c5a7a4f5b66bf7d2a749297479e043141a Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Fri, 21 May 2021 19:44:23 +0100 Subject: Introduce a systems table --- guix-build-coordinator/datastore/sqlite.scm | 120 +++++++++++++++++++++------- sqitch/pg/deploy/create_systems.sql | 7 ++ sqitch/pg/revert/create_systems.sql | 7 ++ sqitch/pg/verify/create_systems.sql | 7 ++ sqitch/sqitch.plan | 1 + sqitch/sqlite/deploy/create_systems.sql | 61 ++++++++++++++ sqitch/sqlite/revert/create_systems.sql | 7 ++ sqitch/sqlite/verify/create_systems.sql | 7 ++ 8 files changed, 190 insertions(+), 27 deletions(-) create mode 100644 sqitch/pg/deploy/create_systems.sql create mode 100644 sqitch/pg/revert/create_systems.sql create mode 100644 sqitch/pg/verify/create_systems.sql create mode 100644 sqitch/sqlite/deploy/create_systems.sql create mode 100644 sqitch/sqlite/revert/create_systems.sql create mode 100644 sqitch/sqlite/verify/create_systems.sql diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm index 7e70718..4f3ad34 100644 --- a/guix-build-coordinator/datastore/sqlite.scm +++ b/guix-build-coordinator/datastore/sqlite.scm @@ -755,7 +755,7 @@ WHERE related_derivations.id != :derivation_id ON derivation_outputs.output_id = other_derivation_derivation_outputs.output_id WHERE derivation_outputs.derivation_id = related_derivations.id - AND other_derivations.system = derivations.system + AND other_derivations.system_id = derivations.system_id AND builds.canceled = 0 ) " @@ -830,7 +830,7 @@ FROM ( )" (if system " - AND derivations.system = :system" + AND derivations.system_id = :system_id" "") " AND NOT EXISTS ( @@ -851,7 +851,7 @@ FROM ( #:cache? #t))) (when system (sqlite-bind-arguments statement - #:system system)) + #:system (db-system->system-id db system))) (let ((result (sqlite-map (match-lambda @@ -1372,18 +1372,18 @@ WHERE setup_failure_id = :id" (sqlite-prepare db " -SELECT derivations.system, COUNT(*) +SELECT derivations.system_id, COUNT(*) FROM builds INNER JOIN derivations ON builds.derivation_id = derivations.id -GROUP BY derivations.system" +GROUP BY derivations.system_id" #:cache? #t))) (let ((result (sqlite-map (match-lambda - (#(system count) - (cons system count))) + (#(system-id count) + (cons (db-system-id->system db system-id) count))) statement))) (sqlite-reset statement) @@ -1613,14 +1613,15 @@ LEFT JOIN ( (map (lambda (system) (simple-format #f - "derivations.system = '~A'" - system)) + "derivations.system_id = ~A" + (db-system->system-id db system))) systems) " OR ") ")"))) (map (lambda (system) - (simple-format #f "derivations.system != '~A'" - system)) + (simple-format + #f "derivations.system_id != ~A" + (db-system->system-id db system))) not-systems) (cond ((eq? processed #t) '("processed = 1")) @@ -1758,9 +1759,10 @@ WHERE build_id = :build_id" (sqlite-prepare db " -SELECT derivations.system +SELECT systems.system FROM builds INNER JOIN derivations ON builds.derivation_id = derivations.id +INNER JOIN systems ON derivations.system_id = systems.id WHERE builds.id = :build_id" #:cache? #t))) @@ -1846,7 +1848,7 @@ INNER JOIN derivation_outputs INNER JOIN derivations ON builds.derivation_id = derivations.id WHERE derivation_outputs.output_id = :output_id - AND derivations.system = :system" + AND derivations.system_id = :system_id" (if include-canceled? "" " @@ -1856,7 +1858,7 @@ WHERE derivation_outputs.output_id = :output_id (sqlite-bind-arguments statement #:output_id (db-output->output-id db output) - #:system system) + #:system_id (db-system->system-id db system)) (let ((result (sqlite-map @@ -2468,10 +2470,10 @@ SELECT agent_id, COUNT(*) FROM allocated_builds GROUP BY agent_id" (sqlite-prepare db " -SELECT system +SELECT system_id FROM build_allocation_agent_requested_systems WHERE agent_id = :agent_id -ORDER BY system ASC" +ORDER BY system_id ASC" #:cache? #t))) (sqlite-bind-arguments @@ -2480,7 +2482,9 @@ ORDER BY system ASC" (let ((result (sqlite-map - (match-lambda (#(system) system)) + (match-lambda + (#(system-id) + (db-system-id->system db system-id))) statement))) (sqlite-reset statement) @@ -2515,14 +2519,16 @@ WHERE agent_id = '~A'" db (string-append " -INSERT INTO build_allocation_agent_requested_systems (agent_id, system) VALUES " +INSERT INTO build_allocation_agent_requested_systems (agent_id, system_id) VALUES " (string-join (map (lambda (system) (simple-format #f - "('~A', '~A')" + "('~A', ~A)" agent-id - system)) + (or + (db-system->system-id db system) + (db-insert-system db system)))) systems) ", ") ";")) @@ -2548,7 +2554,7 @@ INNER JOIN derivations 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 + AND build_allocation_agent_requested_systems.system_id = derivations.system_id WHERE build_allocation_plan.agent_id = :agent_id AND builds.processed = 0 AND builds.canceled = 0 @@ -2916,8 +2922,10 @@ SELECT id FROM derivations WHERE name = :name" (sqlite-prepare db " -SELECT system, fixed_output +SELECT systems.system, fixed_output FROM derivations +INNER JOIN systems + ON systems.id = derivations.system_id WHERE name = :name" #:cache? #t))) @@ -3060,8 +3068,9 @@ WHERE builds.id = :build_id" (sqlite-prepare db " -SELECT system +SELECT systems.system FROM derivations +INNER JOIN systems ON systems.id = derivations.system_id WHERE name = :name" #:cache? #t))) @@ -3161,6 +3170,59 @@ WHERE output = :output result))))) +(define (db-insert-system db system) + (let ((statement + (sqlite-prepare + db + " +INSERT INTO systems (system) VALUES (:system)" + #:cache? #t))) + (sqlite-bind-arguments + statement + #:system system) + + (sqlite-step statement) + (let ((id (last-insert-rowid db))) + (sqlite-reset statement) + + id))) + +(define (db-system->system-id db system) + (let ((statement + (sqlite-prepare + db + " +SELECT id FROM systems WHERE system = :system" + #:cache? #t))) + (sqlite-bind-arguments + statement + #:system system) + + (match (sqlite-step statement) + (#f #f) + (#(id) + (sqlite-reset statement) + + id)))) + +(define (db-system-id->system db system-id) + (let ((statement + (sqlite-prepare + db + " +SELECT system FROM systems WHERE id = :id" + #:cache? #t))) + (sqlite-bind-arguments + statement + #:id system-id) + + (match (sqlite-step statement) + (#f #f) + (#(id) + (sqlite-reset statement) + + id)))) + (define (insert-derivation-and-return-outputs db derivation) (define derivation-name (derivation-file-name derivation)) @@ -3184,14 +3246,18 @@ UPDATE derivations SET fixed_output = ~A WHERE name = '~A'" (sqlite-prepare db " -INSERT OR IGNORE INTO derivations (name, system, fixed_output) - VALUES (:name, :system, :fixed_output)" +INSERT OR IGNORE INTO derivations (name, system_id, fixed_output) + VALUES (:name, :system_id, :fixed_output)" #:cache? #t))) (sqlite-bind-arguments statement #:name derivation-name - #:system (derivation-system derivation) + #:system_id + (let ((system (derivation-system derivation))) + (or + (db-system->system-id db system) + (db-insert-system db system))) #:fixed_output (if fixed-output? 1 0)) (sqlite-step statement) @@ -3274,7 +3340,7 @@ SELECT id FROM outputs WHERE output = :output" (#(id) (sqlite-reset statement) - id))) + id)))) (define (insert-derivation-outputs db derivation-name derivation-outputs) (define output-has-successful-build? diff --git a/sqitch/pg/deploy/create_systems.sql b/sqitch/pg/deploy/create_systems.sql new file mode 100644 index 0000000..2b34f76 --- /dev/null +++ b/sqitch/pg/deploy/create_systems.sql @@ -0,0 +1,7 @@ +-- Deploy guix-build-coordinator:create_systems to pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/revert/create_systems.sql b/sqitch/pg/revert/create_systems.sql new file mode 100644 index 0000000..45ae7a3 --- /dev/null +++ b/sqitch/pg/revert/create_systems.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:create_systems from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/verify/create_systems.sql b/sqitch/pg/verify/create_systems.sql new file mode 100644 index 0000000..797a5c5 --- /dev/null +++ b/sqitch/pg/verify/create_systems.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:create_systems on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index a7fed94..198b4de 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -33,3 +33,4 @@ fix_allocated_builds 2021-03-30T08:19:18Z Christopher Baines fix_allocated_builds_attempt_2 2021-03-30T08:27:18Z Christopher Baines # Fix allocated_builds fix_setup_failure_missing_inputs 2021-04-08T19:31:49Z Christopher Baines # Fix setup_failure_missing_inputs create_outputs 2021-05-21T13:37:49Z Christopher Baines # Create the outputs table +create_systems 2021-05-21T17:22:52Z Christopher Baines # Create the systems table diff --git a/sqitch/sqlite/deploy/create_systems.sql b/sqitch/sqlite/deploy/create_systems.sql new file mode 100644 index 0000000..a4dd941 --- /dev/null +++ b/sqitch/sqlite/deploy/create_systems.sql @@ -0,0 +1,61 @@ +-- Deploy guix-build-coordinator:create_systems to sqlite + +PRAGMA foreign_keys = OFF; + +BEGIN; + +CREATE TABLE systems ( + id INTEGER PRIMARY KEY, + system TEXT NOT NULL +); + +INSERT INTO systems (system) + SELECT system FROM derivations + UNION + SELECT system FROM build_allocation_agent_requested_systems; + + + +CREATE TABLE derivations_new ( + id INTEGER PRIMARY KEY ASC, + name TEXT NOT NULL, + system_id INTEGER NOT NULL REFERENCES systems (id), + fixed_output BOOLEAN + CHECK (fixed_output IN (0,1)) +); + +INSERT INTO derivations_new + SELECT derivations.id, name, systems.id, fixed_output + FROM derivations + INNER JOIN systems ON derivations.system = systems.system; + +DROP TABLE derivations; +ALTER TABLE derivations_new RENAME TO derivations; + +CREATE UNIQUE INDEX derivations_name_idx ON derivations (name); + + + +CREATE TABLE build_allocation_agent_requested_systems_new ( + agent_id INTEGER NOT NULL REFERENCES agents (id), + system_id INTEGER NOT NULL, + PRIMARY KEY (agent_id, system_id) +); + +INSERT INTO build_allocation_agent_requested_systems_new + SELECT agent_id, systems.id + FROM build_allocation_agent_requested_systems + INNER JOIN systems + ON build_allocation_agent_requested_systems.system = systems.system; + +DROP TABLE build_allocation_agent_requested_systems; +ALTER TABLE build_allocation_agent_requested_systems_new + RENAME TO build_allocation_agent_requested_systems; + + + +PRAGMA foreign_key_check; + +COMMIT; + +PRAGMA foreign_keys = ON; diff --git a/sqitch/sqlite/revert/create_systems.sql b/sqitch/sqlite/revert/create_systems.sql new file mode 100644 index 0000000..8208b52 --- /dev/null +++ b/sqitch/sqlite/revert/create_systems.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:create_systems from sqlite + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqlite/verify/create_systems.sql b/sqitch/sqlite/verify/create_systems.sql new file mode 100644 index 0000000..b70d7a5 --- /dev/null +++ b/sqitch/sqlite/verify/create_systems.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:create_systems on sqlite + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- cgit v1.2.3