Introduce a systems table

8 files changed, 190 insertions, 27 deletions

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 <mail@cbaines.net>
 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
+create_systems 2021-05-21T17:22:52Z Christopher Baines <mail@cbaines.net> # 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;