01/03: Stop queries being able to insert systems

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit 84e78ef3ede745269a027db981a306a5517cf1b3
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Wed Nov 13 09:55:03 2024 +0000

    Stop queries being able to insert systems
---
 guix-data-service/comparison.scm           |  2 +-
 guix-data-service/model/blocked-builds.scm |  4 ++--
 guix-data-service/model/system.scm         | 17 +++++++++++++++++
 3 files changed, 20 insertions(+), 3 deletions(-)

diff --git a/guix-data-service/comparison.scm b/guix-data-service/comparison.scm
index 20945db..727c515 100644
--- a/guix-data-service/comparison.scm
+++ b/guix-data-service/comparison.scm
@@ -271,7 +271,7 @@ GROUP BY derivation_source_files.store_path"))
      (if system
          (string-append
           " AND package_derivations.system_id = "
-          (number->string (system->system-id conn system)))
+          (number->string (lookup-system-id conn system)))
          "")
      (if targets
          (string-append
diff --git a/guix-data-service/model/blocked-builds.scm 
b/guix-data-service/model/blocked-builds.scm
index 888e842..2dcbee1 100644
--- a/guix-data-service/model/blocked-builds.scm
+++ b/guix-data-service/model/blocked-builds.scm
@@ -353,7 +353,7 @@ WHERE status IN ('failed', 'failed-dependency', 
'failed-other', 'canceled')
       (get-sql-to-select-package-and-related-derivations-for-revision
        conn
        (commit->revision-id conn revision-commit)
-       #:system-id (system->system-id conn system)
+       #:system-id (lookup-system-id conn system)
        #:target target)
       (string-append
        "
@@ -369,7 +369,7 @@ WITH RECURSIVE all_derivations AS (
          (simple-format
           #f "
       AND system_id = ~A\n"
-          (system->system-id conn system))
+          (lookup-system-id conn system))
          "")
      (if target
          (simple-format
diff --git a/guix-data-service/model/system.scm 
b/guix-data-service/model/system.scm
index e64e7f5..9939a73 100644
--- a/guix-data-service/model/system.scm
+++ b/guix-data-service/model/system.scm
@@ -22,6 +22,7 @@
   #:use-module (squee)
   #:use-module (guix-data-service model utils)
   #:export (system->system-id
+            lookup-system-id
             list-systems))
 
 (define system->system-id-cache
@@ -45,6 +46,22 @@
            (set! systems-cache #f)
            id)))))
 
+(define (lookup-system-id conn system)
+  (let ((cached-value (hash-ref system->system-id-cache
+                                system)))
+    (or cached-value
+        (match (exec-query
+                conn
+                "SELECT id FROM systems WHERE system = $1"
+                (list system))
+          (((id-string))
+           (let ((id (string->number id-string)))
+             (hash-set! system->system-id-cache
+                        system
+                        id)
+             id))
+          (() #f)))))
+
 (define (list-systems conn)
   (if systems-cache
       systems-cache