12/14: Speed up loading package metadata

[Christopher Baines]

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

commit 0468bc43211755d1767db2be04925d4910e82384
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Thu Feb 1 13:15:30 2024 +0100

    Speed up loading package metadata
    
    By batching the SQL queries.
---
 guix-data-service/model/package-metadata.scm | 90 +++++++++++++---------------
 1 file changed, 40 insertions(+), 50 deletions(-)

diff --git a/guix-data-service/model/package-metadata.scm 
b/guix-data-service/model/package-metadata.scm
index 77f24c6..6eca089 100644
--- a/guix-data-service/model/package-metadata.scm
+++ b/guix-data-service/model/package-metadata.scm
@@ -292,18 +292,6 @@ WHERE packages.id IN (
 
 (prevent-inlining-for-tests 
inferior-packages->translated-package-descriptions-and-synopsis)
 
-(define (package-synopsis-data->package-synopsis-ids
-         conn synopsis-by-locale)
-  (insert-missing-data-and-return-all-ids
-   conn
-   "package_synopsis"
-   '(locale synopsis)
-   (map (match-lambda
-          ((locale . synopsis)
-           (list locale synopsis)))
-        synopsis-by-locale)
-   #:delete-duplicates? #t))
-
 (define (insert-package-synopsis-set conn package-synopsis-ids)
   (let ((query
          (string-append
@@ -320,38 +308,22 @@ WHERE packages.id IN (
       (((id)) id))))
 
 (define (package-synopsis-data->package-synopsis-set-id
-         conn synopsis-by-locale)
-  (let* ((package-synopsis-ids
-          (package-synopsis-data->package-synopsis-ids
-           conn
-           synopsis-by-locale))
-         (package-synopsis-set-id
-          (exec-query
-           conn
-           (string-append
-             "SELECT id FROM package_synopsis_sets"
-            " WHERE synopsis_ids = ARRAY["
-            (string-join (map number->string
-                              (sort package-synopsis-ids <)) ", ")
-            "]"))))
+         conn package-synopsis-ids)
+  (let ((package-synopsis-set-id
+         (exec-query
+          conn
+          (string-append
+           "SELECT id FROM package_synopsis_sets"
+           " WHERE synopsis_ids = ARRAY["
+           (string-join (map number->string
+                             (sort package-synopsis-ids <)) ", ")
+           "]"))))
     (string->number
      (match package-synopsis-set-id
        (((id)) id)
        (()
         (insert-package-synopsis-set conn package-synopsis-ids))))))
 
-(define (package-description-data->package-description-ids
-         conn descriptions-by-locale)
-  (insert-missing-data-and-return-all-ids
-   conn
-   "package_descriptions"
-   '(locale description)
-   (map (match-lambda
-          ((locale . description)
-           (list locale description)))
-        descriptions-by-locale)
-   #:delete-duplicates? #t))
-
 (define (insert-package-description-set conn package-description-ids)
   (let ((query
          (string-append
@@ -419,19 +391,37 @@ WHERE packages.id IN (
               conn
               package-description-ids))
            (with-time-logging "preparing package description ids"
-             (map (match-lambda
-                    ((_ _ package-description-data _)
-                     (package-description-data->package-description-ids
-                      conn
-                      package-description-data)))
-                  package-metadata))))
+             (insert-missing-data-and-return-all-ids
+              conn
+              "package_descriptions"
+              '(locale description)
+              (map (match-lambda
+                     ((_ _ package-description-data _)
+                      (map (match-lambda
+                             ((locale . description)
+                              (list locale description)))
+                           package-description-data)))
+                   package-metadata)
+              #:delete-duplicates? #t
+              #:sets-of-data? #t))))
     (with-time-logging "preparing package synopsis set ids"
-      (map (match-lambda
-             ((_ _ _ package-synopsis-data)
-              (package-synopsis-data->package-synopsis-set-id
-               conn
-               package-synopsis-data)))
-           package-metadata)))
+      (map (lambda (package-synopsis-ids)
+             (package-synopsis-data->package-synopsis-set-id
+              conn
+              package-synopsis-ids))
+           (insert-missing-data-and-return-all-ids
+            conn
+            "package_synopsis"
+            '(locale synopsis)
+            (map (match-lambda
+                   ((_ _ _ package-synopsis-data)
+                    (map (match-lambda
+                           ((locale . synopsis)
+                            (list locale synopsis)))
+                         package-synopsis-data)))
+                 package-metadata)
+            #:delete-duplicates? #t
+            #:sets-of-data? #t))))
    ;; There can be duplicated entires in package-metadata, for example where
    ;; you have one package definition which interits from another, and just
    ;; overrides the version and the source, the package_metadata entries for