Introduce an outputs table

8 files changed, 187 insertions, 47 deletions

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 <mail@cbaines.n
 fix_allocated_builds 2021-03-30T08:19:18Z Christopher Baines <mail@cbaines.net> # Fix allocated_builds
 fix_allocated_builds_attempt_2 2021-03-30T08:27:18Z Christopher Baines <mail@cbaines.net> # Fix allocated_builds
 fix_setup_failure_missing_inputs 2021-04-08T19:31:49Z Christopher Baines <mail@cbaines.net> # Fix setup_failure_missing_inputs
+create_outputs 2021-05-21T13:37:49Z Christopher Baines <mail@cbaines.net> # 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;