From 3782b81da0ed258c10acb7ffc43b18d5af50e28b Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Fri, 21 May 2021 18:07:29 +0100 Subject: Introduce an outputs table --- guix-build-coordinator/datastore/sqlite.scm | 139 ++++++++++++++++++---------- sqitch/pg/deploy/create_outputs.sql | 7 ++ sqitch/pg/revert/create_outputs.sql | 7 ++ sqitch/pg/verify/create_outputs.sql | 7 ++ sqitch/sqitch.plan | 1 + sqitch/sqlite/deploy/create_outputs.sql | 59 ++++++++++++ sqitch/sqlite/revert/create_outputs.sql | 7 ++ sqitch/sqlite/verify/create_outputs.sql | 7 ++ 8 files changed, 187 insertions(+), 47 deletions(-) create mode 100644 sqitch/pg/deploy/create_outputs.sql create mode 100644 sqitch/pg/revert/create_outputs.sql create mode 100644 sqitch/pg/verify/create_outputs.sql create mode 100644 sqitch/sqlite/deploy/create_outputs.sql create mode 100644 sqitch/sqlite/revert/create_outputs.sql create mode 100644 sqitch/sqlite/verify/create_outputs.sql diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm index 0bcde1e..7e70718 100644 --- a/guix-build-coordinator/datastore/sqlite.scm +++ b/guix-build-coordinator/datastore/sqlite.scm @@ -752,7 +752,8 @@ WHERE related_derivations.id != :derivation_id ON other_derivation_derivation_outputs.derivation_id = other_derivations.id INNER JOIN derivation_outputs - ON derivation_outputs.output = other_derivation_derivation_outputs.output + 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 builds.canceled = 0 @@ -836,7 +837,7 @@ FROM ( SELECT 1 FROM derivation_outputs INNER JOIN derivation_outputs AS other_build_derivation_outputs - ON derivation_outputs.output = other_build_derivation_outputs.output + ON derivation_outputs.output_id = other_build_derivation_outputs.output_id INNER JOIN builds AS other_builds ON other_build_derivation_outputs.derivation_id = other_builds.derivation_id INNER JOIN build_results AS other_build_results @@ -1105,8 +1106,8 @@ WHERE id = " (number->string (db-find-build-id db build-uuid)))))) db " DELETE FROM unbuilt_outputs -WHERE output IN ( - SELECT derivation_outputs.output +WHERE output_id IN ( + SELECT derivation_outputs.output_id FROM derivation_outputs INNER JOIN builds ON builds.derivation_id = derivation_outputs.derivation_id @@ -1796,12 +1797,12 @@ INNER JOIN derivation_outputs ON builds.derivation_id = derivation_outputs.derivation_id LEFT JOIN build_results ON builds.id = build_results.build_id -WHERE derivation_outputs.output = :output" +WHERE derivation_outputs.output_id = :output_id" #:cache? #t))) (sqlite-bind-arguments statement - #:output output) + #:output_id (db-output->output-id db output)) (let ((result (sqlite-map @@ -1844,7 +1845,7 @@ INNER JOIN derivation_outputs ON builds.derivation_id = derivation_outputs.derivation_id INNER JOIN derivations ON builds.derivation_id = derivations.id -WHERE derivation_outputs.output = :output +WHERE derivation_outputs.output_id = :output_id AND derivations.system = :system" (if include-canceled? "" @@ -1854,7 +1855,7 @@ WHERE derivation_outputs.output = :output (sqlite-bind-arguments statement - #:output output + #:output_id (db-output->output-id db output) #:system system) (let ((result @@ -2149,7 +2150,7 @@ AND NOT EXISTS ( INNER JOIN derivation_outputs ON derivation_inputs.derivation_output_id = derivation_outputs.id INNER JOIN unbuilt_outputs - ON unbuilt_outputs.output = derivation_outputs.output + ON unbuilt_outputs.output_id = derivation_outputs.output_id WHERE builds.derivation_id = derivation_inputs.derivation_id ) EXCEPT @@ -2199,7 +2200,7 @@ UNION 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 + 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 @@ -2568,8 +2569,8 @@ INNER JOIN derivation_outputs AS allocated_builds_derivation_outputs 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" + AND build_derivation_outputs.output_id = + allocated_builds_derivation_outputs.output_id" #:cache? #t))) (sqlite-bind-arguments @@ -2955,8 +2956,9 @@ WHERE name = :name" (sqlite-prepare db " -SELECT name, output +SELECT name, outputs.output FROM derivation_outputs +INNER JOIN outputs ON derivation_outputs.output_id = outputs.id WHERE derivation_id = :derivation_id" #:cache? #t))) @@ -2985,10 +2987,10 @@ WHERE derivation_id = :derivation_id" (sqlite-prepare db " -SELECT derivation_outputs.name, derivation_outputs.output +SELECT derivation_outputs.name, derivation_outputs.output_id FROM derivation_outputs INNER JOIN unbuilt_outputs - ON derivation_outputs.output = unbuilt_outputs.output + ON derivation_outputs.output_id = unbuilt_outputs.output_id WHERE derivation_id = :derivation_id" #:cache? #t))) @@ -3085,7 +3087,8 @@ WHERE name = :name" db " SELECT output_derivations.name, - derivation_outputs.name, derivation_outputs.output + derivation_outputs.name, + outputs.output FROM derivations INNER JOIN derivation_inputs ON derivation_inputs.derivation_id = derivations.id @@ -3093,6 +3096,8 @@ INNER JOIN derivation_outputs ON derivation_inputs.derivation_output_id = derivation_outputs.id INNER JOIN derivations AS output_derivations ON output_derivations.id = derivation_outputs.derivation_id +INNER JOIN outputs + ON derivation_outputs.output_id = outputs.id WHERE derivations.id = :derivation_id" #:cache? #t))) @@ -3238,6 +3243,39 @@ INSERT INTO derivation_inputs (derivation_id, derivation_output_id) VALUES " ", ") ";"))))) +(define (db-insert-output db output) + (let ((statement + (sqlite-prepare + db + " +INSERT INTO outputs (output) VALUES (:output)" + #:cache? #t))) + + (sqlite-bind-arguments statement #:output output) + + (sqlite-step statement) + (let ((id (last-insert-rowid db))) + (sqlite-reset statement) + + id))) + +(define (db-output->output-id db output) + (let ((statement + (sqlite-prepare + db + " +SELECT id FROM outputs WHERE output = :output" + #:cache? #t))) + + (sqlite-bind-arguments statement #:output output) + + (match (sqlite-step statement) + (#f #f) + (#(id) + (sqlite-reset statement) + + id))) + (define (insert-derivation-outputs db derivation-name derivation-outputs) (define output-has-successful-build? (let ((statement @@ -3250,12 +3288,12 @@ INNER JOIN builds ON builds.derivation_id = derivation_outputs.derivation_id INNER JOIN build_results ON builds.id = build_results.build_id -WHERE derivation_outputs.output = :output" +WHERE derivation_outputs.output_id = :output_id" #:cache? #t))) - (lambda (output) + (lambda (output-id) (sqlite-bind-arguments statement - #:output output) + #:output_id output-id) (let* ((build-results (sqlite-map (match-lambda @@ -3273,41 +3311,48 @@ WHERE derivation_outputs.output = :output" (sqlite-prepare db " -INSERT OR IGNORE INTO unbuilt_outputs (output) VALUES (:output)" +INSERT OR IGNORE INTO unbuilt_outputs (output_id) VALUES (:output_id)" #:cache? #t))) - (lambda (output) + (lambda (output-id) (sqlite-bind-arguments statement - #:output output) + #:output_id output-id) (sqlite-step statement) (sqlite-reset statement) #t))) - (sqlite-exec - db - (string-append - " -INSERT INTO derivation_outputs (derivation_id, name, output) VALUES " - (string-join - (map (match-lambda - ((name . derivation-output) - (simple-format - #f - "('~A', '~A', '~A')" - (db-find-derivation-id db derivation-name) - name - (derivation-output-path derivation-output)))) - derivation-outputs) - ", ") - ";")) - - (for-each (lambda (output) - (unless (output-has-successful-build? output) - (insert-into-unbuilt-outputs output))) - (map (match-lambda - ((_ . derivation-output) - (derivation-output-path derivation-output))) - derivation-outputs)) + (let ((derivation-outputs-with-ids + (map (match-lambda + ((name . derivation-output) + (let ((output + (derivation-output-path derivation-output))) + (cons name + (or + (db-output->output-id db output) + (db-insert-output db output)))))) + derivation-outputs))) + (sqlite-exec + db + (string-append + " +INSERT INTO derivation_outputs (derivation_id, name, output_id) VALUES " + (string-join + (map (match-lambda + ((name . output-id) + (simple-format + #f + "('~A', '~A', ~A)" + (db-find-derivation-id db derivation-name) + name + output-id))) + derivation-outputs-with-ids) + ", ") + ";")) + + (for-each (lambda (output-id) + (unless (output-has-successful-build? output-id) + (insert-into-unbuilt-outputs output-id))) + (map cdr derivation-outputs-with-ids))) #t) (define-method (datastore-insert-build diff --git a/sqitch/pg/deploy/create_outputs.sql b/sqitch/pg/deploy/create_outputs.sql new file mode 100644 index 0000000..5b15bc8 --- /dev/null +++ b/sqitch/pg/deploy/create_outputs.sql @@ -0,0 +1,7 @@ +-- Deploy guix-build-coordinator:create_outputs to pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/revert/create_outputs.sql b/sqitch/pg/revert/create_outputs.sql new file mode 100644 index 0000000..84e302e --- /dev/null +++ b/sqitch/pg/revert/create_outputs.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:create_outputs from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/verify/create_outputs.sql b/sqitch/pg/verify/create_outputs.sql new file mode 100644 index 0000000..3215509 --- /dev/null +++ b/sqitch/pg/verify/create_outputs.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:create_outputs on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index f9e28fd..a7fed94 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -32,3 +32,4 @@ use_numeric_primary_keys 2021-03-28T09:55:40Z Christopher Baines # Fix allocated_builds 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 diff --git a/sqitch/sqlite/deploy/create_outputs.sql b/sqitch/sqlite/deploy/create_outputs.sql new file mode 100644 index 0000000..03f0b42 --- /dev/null +++ b/sqitch/sqlite/deploy/create_outputs.sql @@ -0,0 +1,59 @@ +-- Deploy guix-build-coordinator:create_outputs to sqlite + +PRAGMA foreign_keys = OFF; + +BEGIN; + +CREATE TABLE outputs ( + id INTEGER PRIMARY KEY NOT NULL, + output TEXT NOT NULL +); + +INSERT INTO outputs (output) SELECT DISTINCT output FROM derivation_outputs; + +CREATE INDEX outputs_output ON outputs (output); + + + + +CREATE TABLE derivation_outputs_new ( + id INTEGER PRIMARY KEY ASC, + derivation_id INTEGER NOT NULL REFERENCES derivations (id), + name TEXT NOT NULL, + output_id INTEGER NOT NULL REFERENCES outputs (id) +); + +INSERT INTO derivation_outputs_new + SELECT derivation_outputs.id, derivation_id, name, outputs.id + FROM derivation_outputs + INNER JOIN outputs + ON derivation_outputs.output = outputs.output; + +DROP TABLE derivation_outputs; +ALTER TABLE derivation_outputs_new RENAME TO derivation_outputs; + +CREATE INDEX derivation_outputs_output_id_idx + ON derivation_outputs (output_id); +CREATE UNIQUE INDEX derivation_outputs_unique_idx + ON derivation_outputs (derivation_id, name); + + +CREATE TABLE unbuilt_outputs_new ( + output_id INTEGER PRIMARY KEY REFERENCES outputs (id) +); + +UPDATE unbuilt_outputs_new SET output_id = ( + SELECT id + FROM unbuilt_outputs + INNER JOIN outputs + ON unbuilt_outputs.output = outputs.output +); + +DROP TABLE unbuilt_outputs; +ALTER TABLE unbuilt_outputs_new RENAME TO unbuilt_outputs; + +PRAGMA foreign_key_check; + +COMMIT; + +PRAGMA foreign_keys = ON; diff --git a/sqitch/sqlite/revert/create_outputs.sql b/sqitch/sqlite/revert/create_outputs.sql new file mode 100644 index 0000000..2fa2cba --- /dev/null +++ b/sqitch/sqlite/revert/create_outputs.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:create_outputs from sqlite + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqlite/verify/create_outputs.sql b/sqitch/sqlite/verify/create_outputs.sql new file mode 100644 index 0000000..4398410 --- /dev/null +++ b/sqitch/sqlite/verify/create_outputs.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:create_outputs on sqlite + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- cgit v1.2.3