Record what systems agents fetch builds for

This can then be used by allocators to avoid allocating builds to agents that
they're never going to fetch.

9 files changed, 121 insertions, 0 deletions

diff --git a/guix-build-coordinator/datastore.scm b/guix-build-coordinator/datastore.scm
index eecff9c..547d56b 100644
--- a/guix-build-coordinator/datastore.scm
+++ b/guix-build-coordinator/datastore.scm
@@ -53,6 +53,8 @@
 (re-export datastore-count-build-allocation-plan-entries)
 (re-export datastore-replace-build-allocation-plan)
 (re-export datastore-count-allocated-builds)
+(re-export datastore-agent-requested-systems)
+(re-export datastore-update-agent-requested-systems)
 (re-export datastore-allocate-builds-to-agent)
 (re-export datastore-list-allocation-plan-builds)
 
diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm
index 9c9172a..c1a0836 100644
--- a/guix-build-coordinator/datastore/sqlite.scm
+++ b/guix-build-coordinator/datastore/sqlite.scm
@@ -55,6 +55,8 @@
             datastore-count-build-allocation-plan-entries
             datastore-replace-build-allocation-plan
             datastore-count-allocated-builds
+            datastore-agent-requested-systems
+            datastore-update-agent-requested-systems
             datastore-allocate-builds-to-agent
             datastore-list-allocation-plan-builds))
 
@@ -1371,6 +1373,76 @@ SELECT agent_id, COUNT(*) FROM allocated_builds GROUP BY agent_id")))
 
          result)))))
 
+(define-method (datastore-agent-requested-systems
+                (datastore <sqlite-datastore>)
+                agent-id)
+  (call-with-worker-thread
+   (slot-ref datastore 'worker-reader-thread-channel)
+   (lambda (db)
+     (let ((statement
+            (sqlite-prepare
+             db
+             "
+SELECT system
+FROM build_allocation_agent_requested_systems
+WHERE agent_id = :agent_id
+ORDER BY system ASC")))
+
+       (sqlite-bind-arguments
+        statement
+        #:agent_id agent-id)
+
+       (let ((result
+              (sqlite-map
+               (match-lambda (#(system) system))
+               statement)))
+         (sqlite-reset statement)
+
+         result)))))
+
+(define-method (datastore-update-agent-requested-systems
+                (datastore <sqlite-datastore>)
+                agent-id
+                systems)
+  (define update-not-needed?
+    (equal? (sort systems
+                  string<?)
+            (datastore-agent-requested-systems
+             datastore
+             agent-id)))
+
+  (if update-not-needed?
+      #f
+      (datastore-call-with-transaction
+       datastore
+       (lambda (db)
+         (sqlite-exec
+          db
+          (simple-format
+           #f
+           "
+DELETE FROM build_allocation_agent_requested_systems
+WHERE agent_id = '~A'"
+           agent-id))
+
+         (sqlite-exec
+          db
+          (string-append
+           "
+INSERT INTO build_allocation_agent_requested_systems (agent_id, system) VALUES "
+           (string-join
+            (map (lambda (system)
+                   (simple-format
+                    #f
+                    "('~A', '~A')"
+                    agent-id
+                    system))
+                 systems)
+            ", ")
+           ";"))
+
+         #t))))
+
 (define-method (datastore-allocate-builds-to-agent
                 (datastore <sqlite-datastore>)
                 agent-id
diff --git a/sqitch/pg/deploy/build_allocation_agent_requested_systems.sql b/sqitch/pg/deploy/build_allocation_agent_requested_systems.sql
new file mode 100644
index 0000000..b71f55d
--- /dev/null
+++ b/sqitch/pg/deploy/build_allocation_agent_requested_systems.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-build-coordinator:build_allocation_agent_requested_systems to pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/revert/build_allocation_agent_requested_systems.sql b/sqitch/pg/revert/build_allocation_agent_requested_systems.sql
new file mode 100644
index 0000000..4372b86
--- /dev/null
+++ b/sqitch/pg/revert/build_allocation_agent_requested_systems.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:build_allocation_agent_requested_systems from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/verify/build_allocation_agent_requested_systems.sql b/sqitch/pg/verify/build_allocation_agent_requested_systems.sql
new file mode 100644
index 0000000..3223254
--- /dev/null
+++ b/sqitch/pg/verify/build_allocation_agent_requested_systems.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:build_allocation_agent_requested_systems on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 213f01c..a793617 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -19,3 +19,4 @@ build_tags 2020-05-31T13:54:41Z Christopher Baines <mail@cbaines.net> # Support
 builds_created_at 2020-06-27T18:30:32Z Christopher Baines <mail@cbaines.net> # Add builds.created_at
 build_starts 2020-06-27T20:23:38Z Christopher Baines <mail@cbaines.net> # Add build_starts table
 add_builds_end_time 2020-06-27T20:58:01Z Christopher Baines <mail@cbaines.net> # Add builds.end_time field
+build_allocation_agent_requested_systems 2020-07-02T19:45:02Z Christopher Baines <mail@cbaines.net> # Add new build_allocation_agent_requested_systems table
diff --git a/sqitch/sqlite/deploy/build_allocation_agent_requested_systems.sql b/sqitch/sqlite/deploy/build_allocation_agent_requested_systems.sql
new file mode 100644
index 0000000..c0d99f9
--- /dev/null
+++ b/sqitch/sqlite/deploy/build_allocation_agent_requested_systems.sql
@@ -0,0 +1,11 @@
+-- Deploy guix-build-coordinator:build_allocation_agent_requested_systems to sqlite
+
+BEGIN;
+
+CREATE TABLE build_allocation_agent_requested_systems (
+       agent_id NOT NULL REFERENCES agents (id),
+       system TEXT NOT NULL,
+       PRIMARY KEY (agent_id, system)
+);
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/build_allocation_agent_requested_systems.sql b/sqitch/sqlite/revert/build_allocation_agent_requested_systems.sql
new file mode 100644
index 0000000..1bfe6c8
--- /dev/null
+++ b/sqitch/sqlite/revert/build_allocation_agent_requested_systems.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:build_allocation_agent_requested_systems from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/build_allocation_agent_requested_systems.sql b/sqitch/sqlite/verify/build_allocation_agent_requested_systems.sql
new file mode 100644
index 0000000..4a2502b
--- /dev/null
+++ b/sqitch/sqlite/verify/build_allocation_agent_requested_systems.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:build_allocation_agent_requested_systems on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;