02/04: Update the package_metadata_tsvectors with new entries

[Christopher Baines]

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

commit 5b3e5d9441948fe621c495a36556ec1011f65b41
Author: Danjela Lura <danielaluraa@gmail.com>
AuthorDate: Fri Aug 7 17:16:44 2020 +0200

    Update the package_metadata_tsvectors with new entries
    
    Signed-off-by: Christopher Baines <mail@cbaines.net>
---
 guix-data-service/jobs/load-new-guix-revision.scm | 30 ++++++-----
 guix-data-service/model/package-metadata.scm      | 64 +++++++++++++++++++++++
 guix-data-service/model/utils.scm                 | 52 +++++++++---------
 3 files changed, 109 insertions(+), 37 deletions(-)

diff --git a/guix-data-service/jobs/load-new-guix-revision.scm 
b/guix-data-service/jobs/load-new-guix-revision.scm
index 65fa705..258568e 100644
--- a/guix-data-service/jobs/load-new-guix-revision.scm
+++ b/guix-data-service/jobs/load-new-guix-revision.scm
@@ -17,6 +17,7 @@
 
 (define-module (guix-data-service jobs load-new-guix-revision)
   #:use-module (srfi srfi-1)
+  #:use-module (srfi srfi-11)
   #:use-module (ice-9 match)
   #:use-module (ice-9 threads)
   #:use-module (ice-9 textual-ports)
@@ -732,18 +733,23 @@ WHERE job_id = $1"
              conn
              (inferior-packages->license-id-lists
               conn
-              (inferior-packages->license-data inf packages)))))
-         (packages-metadata-ids
-          (with-time-logging "fetching inferior package metadata"
-            (inferior-packages->package-metadata-ids
-             conn inf packages package-license-set-ids))))
-
-    (with-time-logging "getting package-ids"
-      (inferior-packages->package-ids
-       conn
-       (zip (map inferior-package-name packages)
-            (map inferior-package-version packages)
-            packages-metadata-ids)))))
+              (inferior-packages->license-data inf packages))))))
+    (let*-values
+        (((all-package-metadata-ids new-package-metadata-ids)
+           (with-time-logging "fetching inferior package metadata"
+             (inferior-packages->package-metadata-ids
+              conn inf packages package-license-set-ids))))
+
+      (with-time-logging "fetching package metadata tsvector entries"
+        (insert-package-metadata-tsvector-entries
+         conn new-package-metadata-ids))
+
+      (with-time-logging "getting package-ids"
+        (inferior-packages->package-ids
+         conn
+         (zip (map inferior-package-name packages)
+              (map inferior-package-version packages)
+              all-package-metadata-ids))))))
 
 (define (insert-lint-warnings conn inferior-package-id->package-database-id
                               lint-checker-ids
diff --git a/guix-data-service/model/package-metadata.scm 
b/guix-data-service/model/package-metadata.scm
index c906fa4..0598fc7 100644
--- a/guix-data-service/model/package-metadata.scm
+++ b/guix-data-service/model/package-metadata.scm
@@ -17,6 +17,7 @@
 
 (define-module (guix-data-service model package-metadata)
   #:use-module (srfi srfi-1)
+  #:use-module (srfi srfi-11)
   #:use-module (ice-9 vlist)
   #:use-module (ice-9 match)
   #:use-module (squee)
@@ -33,6 +34,7 @@
   #:export (select-package-metadata-by-revision-name-and-version
             inferior-packages->package-metadata-ids
             inferior-packages->translated-package-descriptions-and-synopsis
+            insert-package-metadata-tsvector-entries
 
             package-description-and-synopsis-locale-options-guix-revision
 
@@ -493,3 +495,65 @@ GROUP BY package_descriptions.locale;
       `(,locale . ,(string->number description-counts))))
    (exec-query conn description-counts
                (list revision-id))))
+
+(define (insert-package-metadata-tsvector-entries conn
+                                                  package-metadata-ids)
+  (define query
+    (string-append
+     "
+INSERT INTO package_metadata_tsvectors (package_metadata_id, locale,
+            synopsis_and_description, package_synopsis_id, 
package_description_id)
+SELECT DISTINCT ON (package_metadata.id, locale)
+         package_metadata.id,
+         CASE WHEN translated_package_synopsis.locale != 'en_US.utf8'
+              THEN translated_package_synopsis.locale
+              ELSE translated_package_descriptions.locale
+         END AS locale,
+         (
+            setweight(to_tsvector(translated_package_synopsis.synopsis), 'B') 
||
+            
setweight(to_tsvector(translated_package_descriptions.description), 'C')
+         ),
+         translated_package_synopsis.id,
+         translated_package_descriptions.id
+FROM package_metadata
+INNER JOIN (
+  SELECT package_description_sets.id AS package_description_set_id,
+         package_descriptions.id, package_descriptions.description,
+         package_descriptions.locale
+  FROM package_description_sets
+  INNER JOIN package_descriptions
+    ON package_descriptions.id = ANY (package_description_sets.description_ids)
+  ORDER BY package_description_sets.id,
+           CASE WHEN package_descriptions.locale = 'en_US.utf8' THEN 1
+                ELSE 2
+           END DESC
+) AS translated_package_descriptions
+  ON package_metadata.package_description_set_id =
+     translated_package_descriptions.package_description_set_id
+INNER JOIN (
+  SELECT package_synopsis_sets.id AS package_synopsis_set_id,
+         package_synopsis.id, package_synopsis.synopsis, 
package_synopsis.locale
+  FROM package_synopsis_sets
+  INNER JOIN package_synopsis
+    ON package_synopsis.id = ANY (package_synopsis_sets.synopsis_ids)
+  ORDER BY package_synopsis_sets.id,
+           CASE WHEN package_synopsis.locale = 'en_US.utf8' THEN 1
+                ELSE 2
+           END DESC
+) AS translated_package_synopsis
+  ON package_metadata.package_synopsis_set_id =
+     translated_package_synopsis.package_synopsis_set_id
+  AND (translated_package_descriptions.locale =
+       translated_package_synopsis.locale
+       OR translated_package_descriptions.locale = 'en_US.utf8')
+WHERE package_metadata.id IN ("
+         (string-join
+          (map number->string
+               package-metadata-ids) ", ") ")"
+"
+ORDER BY package_metadata.id, locale,
+         CASE WHEN translated_package_synopsis.locale =
+                   translated_package_descriptions.locale THEN 1
+              ELSE 0
+         END DESC;"))
+  (exec-query conn query))
diff --git a/guix-data-service/model/utils.scm 
b/guix-data-service/model/utils.scm
index 79aa7da..e6c4a2b 100644
--- a/guix-data-service/model/utils.scm
+++ b/guix-data-service/model/utils.scm
@@ -365,28 +365,30 @@ WHERE table_name = $1"
                    (exec-query conn (insert-sql missing-entries)))))
          (new-entries-lookup-vhash
           (two-lists->vhash missing-entries
-                            new-entries)))
-
-    (if sets-of-data?
-        (map (lambda (field-value-lists)
-               ;; Normalise the result at this point, ensuring that the id's
-               ;; in the set are sorted
-               (sort
-                (map (lambda (field-values)
-                       (cdr
-                        (or (vhash-assoc (normalise-values field-values)
-                                         existing-entries)
-                            (vhash-assoc field-values
-                                         new-entries-lookup-vhash)
-                            (error "missing entry" field-values))))
-                     field-value-lists)
-                <))
-             data)
-        (map (lambda (field-values)
-               (cdr
-                (or (vhash-assoc (normalise-values field-values)
-                                 existing-entries)
-                    (vhash-assoc field-values
-                                 new-entries-lookup-vhash)
-                    (error "missing entry" field-values))))
-             data))))
+                            new-entries))
+         (all-ids
+          (if sets-of-data?
+              (map (lambda (field-value-lists)
+                     ;; Normalise the result at this point, ensuring that the 
id's
+                     ;; in the set are sorted
+                     (sort
+                      (map (lambda (field-values)
+                             (cdr
+                              (or (vhash-assoc (normalise-values field-values)
+                                               existing-entries)
+                                  (vhash-assoc field-values
+                                               new-entries-lookup-vhash)
+                                  (error "missing entry" field-values))))
+                           field-value-lists)
+                      <))
+                   data)
+              (map (lambda (field-values)
+                     (cdr
+                      (or (vhash-assoc (normalise-values field-values)
+                                       existing-entries)
+                          (vhash-assoc field-values
+                                       new-entries-lookup-vhash)
+                          (error "missing entry" field-values))))
+                   data))))
+    (values all-ids
+            new-entries)))