Add a function to rebuild the package_derivations_by_guix_revision

At the moment, some data is missing, but this code will fix that.

1 files changed, 64 insertions, 18 deletions

diff --git a/guix-data-service/model/package-derivation-by-guix-revision-range.scm b/guix-data-service/model/package-derivation-by-guix-revision-range.scm
index 70b1103..50762da 100644
--- a/guix-data-service/model/package-derivation-by-guix-revision-range.scm
+++ b/guix-data-service/model/package-derivation-by-guix-revision-range.scm
@@ -18,7 +18,9 @@
 (define-module (guix-data-service model package-derivation-by-guix-revision-range)
   #:use-module (ice-9 match)
   #:use-module (squee)
-  #:export (update-package-derivations-table))
+  #:use-module (guix-data-service database)
+  #:export (update-package-derivations-table
+            rebuild-package-derivations-table))
 
 (define (log-time action f)
   (simple-format #t "debug: Starting ~A\n" action)
@@ -51,13 +53,13 @@ WHERE git_repository_id = $1 AND
          guix-revision-id
          branch-name)))
 
-(define (insert-guix-revision-package-derivation-entries conn
-                                                         git-repository-id
-                                                         guix-revision-id
-                                                         branch-name)
-  (exec-query
-   conn
-   "
+(define* (insert-guix-revision-package-derivation-entries conn
+                                                          git-repository-id
+                                                          branch-name
+                                                          #:key guix-revision-id)
+  (define query
+    (string-append
+     "
 INSERT INTO package_derivations_by_guix_revision_range
 SELECT DISTINCT
        $1::integer AS git_repository_id,
@@ -84,23 +86,33 @@ INNER JOIN (
 ) AS revision_packages ON packages.id = revision_packages.package_id
 INNER JOIN guix_revisions ON revision_packages.revision_id = guix_revisions.id
 INNER JOIN git_branches ON guix_revisions.commit = git_branches.commit
-WHERE git_branches.name = $2 AND
+WHERE git_branches.name = $2"
+     (if guix-revision-id
+         " AND
       revision_packages.derivation_id IN (
         SELECT package_derivations.derivation_id
         FROM package_derivations
         INNER JOIN guix_revision_package_derivations
           ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
         WHERE revision_id = $3
-      )
+      )"
+         "")
+     "
 WINDOW package_version AS (
   PARTITION BY packages.name, packages.version, revision_packages.derivation_id
   ORDER BY git_branches.datetime
   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 )
-ORDER BY packages.name, packages.version"
-   (list git-repository-id
-         branch-name
-         guix-revision-id)))
+ORDER BY packages.name, packages.version"))
+
+  (exec-query
+   conn
+   query
+   `(,git-repository-id
+     ,branch-name
+     ,@(if guix-revision-id
+           (list guix-revision-id)
+           '()))))
 
 (define (update-package-derivations-table conn
                                           git-repository-id
@@ -127,13 +139,47 @@ LOCK TABLE ONLY package_derivations_by_guix_revision_range
       (log-time
        (simple-format #f "inserting package derivation entries for ~A" branch-name)
        (lambda ()
-         (insert-guix-revision-package-derivation-entries conn
-                                                          git-repository-id
-                                                          guix-revision-id
-                                                          branch-name)))))
+         (insert-guix-revision-package-derivation-entries
+          conn
+          git-repository-id
+          branch-name
+          #:guix-revision-id guix-revision-id)))))
    (exec-query
     conn
     "SELECT name FROM git_branches WHERE commit = $1 AND git_repository_id = $2"
     (list commit git-repository-id)))
 
   #t)
+
+(define (rebuild-package-derivations-table conn)
+  (with-postgresql-transaction
+   conn
+   (lambda (conn)
+     ;; Lock the table to wait for other transactions to commit before updating
+     ;; the table
+     (exec-query
+      conn
+      "
+LOCK TABLE ONLY package_derivations_by_guix_revision_range
+  IN SHARE ROW EXCLUSIVE MODE")
+
+     (log-time
+      (simple-format #f "deleting all package derivation entries")
+      (lambda ()
+        (exec-query conn "DELETE FROM package_derivations_by_guix_revision_range")))
+
+     (let ((git-branches-and-repository-ids
+            (exec-query
+             conn
+             "SELECT DISTINCT name, git_repository_id FROM git_branches")))
+       (for-each
+        (match-lambda
+          ((branch-name git-repository-id)
+           (log-time
+            (simple-format #f "inserting package derivation entries for ~A" branch-name)
+            (lambda ()
+              (insert-guix-revision-package-derivation-entries
+               conn
+               git-repository-id
+               branch-name)))))
+        git-branches-and-repository-ids)))))