Many changes

A large proportion of these changes relate to changing the way
packages relate to derivations. Previously, a package at a given
revision had a single derivation. This was OK, but didn't account for
multiple architectures.

Therefore, these changes mean that a package has multiple derivations,
depending on the system of the derivation, and the target system.

There are multiple changes, small and large to the web interface as
well. More pages link to each other, and the visual display has been
improved somewhat.

6 files changed, 446 insertions, 128 deletions

diff --git a/guix-data-service/model/derivation.scm b/guix-data-service/model/derivation.scm
index 305c260..b38efc7 100644
--- a/guix-data-service/model/derivation.scm
+++ b/guix-data-service/model/derivation.scm
@@ -5,16 +5,18 @@
   #:use-module (squee)
   #:use-module (guix base32)
   #:use-module (guix inferior)
+  #:use-module (guix memoization)
   #:use-module (guix derivations)
   #:use-module (guix-data-service model utils)
   #:export (select-derivation-by-file-name
             select-derivation-outputs-by-derivation-id
             select-derivation-by-output-filename
             select-derivations-using-output
+            select-derivations-by-revision-name-and-version
             select-derivation-inputs-by-derivation-id
             select-existing-derivations
             select-derivations-by-id
-            select-derivations-and-build-status-by-id
+            select-derivations-and-build-status-by-file-name
             insert-into-derivations
             derivations->derivation-ids))
 
@@ -62,6 +64,36 @@
 
   (exec-query conn query (list output-id)))
 
+(define (select-derivations-by-revision-name-and-version
+         conn revision-commit-hash name version)
+  (define query "
+SELECT derivations.system, package_derivations.target, derivations.file_name,
+  latest_build_status.status
+FROM derivations
+INNER JOIN package_derivations
+  ON derivations.id = package_derivations.derivation_id
+INNER JOIN packages
+  ON package_derivations.package_id = packages.id
+INNER JOIN guix_revision_package_derivations
+  ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
+INNER JOIN guix_revisions
+  ON guix_revision_package_derivations.revision_id = guix_revisions.id
+LEFT OUTER JOIN builds ON derivations.id = builds.derivation_id
+LEFT OUTER JOIN (
+  SELECT DISTINCT ON (internal_build_id) *
+  FROM build_status
+  ORDER BY internal_build_id, status_fetched_at DESC
+) AS latest_build_status
+  ON builds.internal_id = latest_build_status.internal_build_id
+WHERE guix_revisions.commit = $1
+  AND packages.name = $2
+  AND packages.version = $3
+ORDER BY derivations.system DESC,
+         package_derivations.target DESC,
+         derivations.file_name")
+
+  (exec-query conn query (list revision-commit-hash name version)))
+
 (define (insert-derivation-outputs conn
                                    derivation-id
                                    names-and-derivation-outputs)
@@ -166,21 +198,22 @@
     ((result)
      result)))
 
-(define (select-derivation-output-id conn name path)
-  (match (exec-query
-          conn
-          (string-append
-           "SELECT derivation_outputs.id FROM derivation_outputs "
-           "INNER JOIN derivations ON "
-           "derivation_outputs.derivation_id = derivations.id "
-           "WHERE derivations.file_name = '" path "' "
-           "AND derivation_outputs.name = '" name "';"))
-    (((id))
-     id)
-    (()
-     (error (simple-format
-             #f "cannot find derivation-output with name ~A and path ~A"
-             name path)))))
+(define select-derivation-output-id
+  (mlambda (conn name path)
+    (match (exec-query
+            conn
+            (string-append
+             "SELECT derivation_outputs.id FROM derivation_outputs "
+             "INNER JOIN derivations ON "
+             "derivation_outputs.derivation_id = derivations.id "
+             "WHERE derivations.file_name = '" path "' "
+             "AND derivation_outputs.name = '" name "';"))
+      (((id))
+       id)
+      (()
+       (error (simple-format
+               #f "cannot find derivation-output with name ~A and path ~A"
+               name path))))))
 
 (define (select-derivation-outputs-by-derivation-id conn id)
   (define query
@@ -211,7 +244,7 @@
 
   (exec-query conn query (list id)))
 
-(define (insert-derivation-input conn derivation-id derivation-input)
+(define (insert-derivation-inputs conn derivation-id derivation-inputs)
   (define (insert-into-derivation-inputs output-ids)
     (string-append "INSERT INTO derivation_inputs "
                    "(derivation_id, derivation_output_id) VALUES "
@@ -224,16 +257,19 @@
                     ",")
                    ";"))
 
-  (match derivation-input
-    (($ <derivation-input> path sub-derivations)
-     (exec-query
-      conn
-      (insert-into-derivation-inputs
-       (map (lambda (sub-derivation)
-              (select-derivation-output-id conn
-                                           sub-derivation
-                                           path))
-            sub-derivations))))))
+  (unless (null? derivation-inputs)
+    (exec-query
+     conn
+     (insert-into-derivation-inputs
+      (append-map
+       (match-lambda
+         (($ <derivation-input> path sub-derivations)
+          (map (lambda (sub-derivation)
+                 (select-derivation-output-id conn
+                                              sub-derivation
+                                              path))
+               sub-derivations)))
+       derivation-inputs)))))
 
 (define (select-from-derivation-source-files store-paths)
   (string-append
@@ -304,7 +340,34 @@
     (exec-query conn
                 (insert-into-derivation-sources sources-ids))))
 
-(define (insert-missing-derivations conn derivations)
+(define (insert-missing-derivations conn
+                                    derivation-ids-hash-table
+                                    derivations)
+  (define (ensure-input-derivations-exist input-derivation-file-names)
+    (unless (null? input-derivation-file-names)
+      (simple-format
+       #t "debug: ensure-input-derivations-exist: processing ~A derivations\n"
+       (length input-derivation-file-names))
+      (force-output)
+      (let* ((existing-derivation-entries
+              (derivation-file-names->vhash conn
+                                            derivation-ids-hash-table
+                                            input-derivation-file-names))
+
+             (missing-derivations-filenames
+              (filter (lambda (derivation-file-name)
+                        (not (vhash-assoc derivation-file-name
+                                          existing-derivation-entries)))
+                      input-derivation-file-names)))
+
+        (unless (null? missing-derivations-filenames)
+          ;; Ensure all the input derivations exist
+          (insert-missing-derivations
+           conn
+           derivation-ids-hash-table
+           (map read-derivation-from-file
+                missing-derivations-filenames))))))
+
   (define (insert-into-derivations)
     (string-append
      "INSERT INTO derivations "
@@ -331,24 +394,60 @@
      " RETURNING id"
      ";"))
 
-  (map (lambda (derivation-id derivation)
-         (insert-derivation-outputs conn
-                                    derivation-id
-                                    (derivation-outputs derivation))
-
-         (insert-derivation-sources conn
-                                    derivation-id
-                                    (derivation-sources derivation))
-
-         (for-each (lambda (derivation-input)
-                     (insert-derivation-input conn
-                                              derivation-id
-                                              derivation-input))
-                   (derivation-inputs derivation))
-
-         derivation-id)
-       (map car (exec-query conn (insert-into-derivations)))
-       derivations))
+  (simple-format
+   #t "debug: insert-missing-derivations: inserting ~A derivations\n"
+   (length derivations))
+  (let ((derivation-ids
+         (map car (exec-query conn (insert-into-derivations)))))
+
+    (simple-format
+     #t "debug: insert-missing-derivations: updating hash table\n")
+    (for-each (lambda (derivation derivation-id)
+                (hash-set! derivation-ids-hash-table
+                           (derivation-file-name derivation)
+                           derivation-id))
+              derivations
+              derivation-ids)
+
+    (simple-format
+     #t "debug: insert-missing-derivations: inserting outputs\n")
+    (for-each (lambda (derivation-id derivation)
+                (insert-derivation-outputs conn
+                                           derivation-id
+                                           (derivation-outputs derivation)))
+              derivation-ids
+              derivations)
+
+    (simple-format
+     #t "debug: insert-missing-derivations: inserting sources\n")
+    (for-each (lambda (derivation-id derivation)
+                (insert-derivation-sources conn
+                                           derivation-id
+                                           (derivation-sources derivation)))
+              derivation-ids
+              derivations)
+
+    (simple-format
+     #t "debug: insert-missing-derivations: ensure-input-derivations-exist\n")
+    (force-output)
+
+    (ensure-input-derivations-exist (deduplicate-strings
+                                     (map derivation-input-path
+                                          (append-map
+                                           derivation-inputs
+                                           derivations))))
+
+    (simple-format
+     #t "debug: insert-missing-derivations: inserting inputs\n")
+    (for-each (lambda (derivation-id derivation)
+                (insert-derivation-inputs conn
+                                          derivation-id
+                                          (derivation-inputs derivation)))
+
+              derivation-ids
+              derivations)
+
+    derivation-ids))
 
 (define (select-derivations-by-id conn ids)
   (define query
@@ -363,10 +462,10 @@
 
   (exec-query conn query))
 
-(define (select-derivations-and-build-status-by-id conn ids)
+(define (select-derivations-and-build-status-by-file-name conn file-names)
   (define query
     (string-append
-     "SELECT derivations.id, derivations.file_name, latest_build_status.status "
+     "SELECT derivations.file_name, latest_build_status.status "
      "FROM derivations "
      "LEFT OUTER JOIN builds ON derivations.id = builds.derivation_id "
      "LEFT OUTER JOIN "
@@ -375,60 +474,124 @@
      "ORDER BY internal_build_id, status_fetched_at DESC"
      ") AS latest_build_status "
      "ON builds.internal_id = latest_build_status.internal_build_id "
-     "WHERE derivations.id IN "
-     "(" (string-join (map (lambda (id)
-                             (simple-format #f "'~A'" id))
-                           ids)
+     "WHERE derivations.file_name IN "
+     "(" (string-join (map (lambda (file-name)
+                             (simple-format #f "'~A'" file-name))
+                           file-names)
                       ",")
      ");"))
 
   (exec-query conn query))
 
-(define (derivations->derivation-ids conn derivations)
-  (define (ensure-input-derivations-exist)
-    (let* ((missing-derivation-file-names (map derivation-file-name
-                                               derivations))
-
-           (input-derivation-file-names (delete-duplicates
-                                         (map derivation-input-path
-                                              (append-map
-                                               derivation-inputs
-                                               derivations)))))
-
-      ;; Ensure all the input derivations exist
-      (derivations->derivation-ids
-       conn
-       (map read-derivation-from-file
-            input-derivation-file-names))))
+(define (deduplicate-strings strings)
+  (pair-fold
+   (lambda (pair result)
+     (if (null? (cdr pair))
+         (cons (first pair) result)
+         (if (string=? (first pair) (second pair))
+             result
+             (cons (first pair) result))))
+   '()
+   (sort strings
+         (lambda (a b)
+           (string<? a b)))))
+
+(define (deduplicate-derivations derivations)
+  (define sorted-derivations
+    (sort derivations
+          (lambda (a b)
+            (string<? (derivation-file-name a)
+                      (derivation-file-name b)))))
+
+  (pair-fold
+   (match-lambda*
+     (((x) result)
+      (cons x result))
+     (((x y rest ...) result)
+      (if (string=? (derivation-file-name x)
+                    (derivation-file-name y))
+          result
+          (cons x result))))
+   '()
+   sorted-derivations))
+
+(define (derivation-file-names->vhash conn derivation-ids-hash-table file-names)
+  (simple-format #t "debug: derivation-file-names->vhash: ~A file-names\n"
+                 (length file-names))
+  (match (fold (match-lambda*
+                 ((file-name (result . missing-file-names))
+                  (let ((cached-id (hash-ref derivation-ids-hash-table
+                                             file-name)))
+                    (if cached-id
+                        (cons (vhash-cons file-name cached-id result)
+                              missing-file-names)
+                        (cons result
+                              (cons file-name missing-file-names))))))
+               (cons vlist-null '())
+               file-names)
+    ((result)
+     (simple-format
+      #t "debug: derivation-file-names->vhash: lookup ~A file-names, all found\n"
+      (length file-names))
+     result)
+    ((result . missing-file-names)
+     (simple-format
+      #t "debug: derivation-file-names->vhash: lookup ~A file-names, ~A not cached\n"
+      (length file-names) (length missing-file-names))
+     (let ((result-for-missing-file-names
+            (exec-query->vhash
+             conn
+             (select-existing-derivations missing-file-names)
+             second ;; file_name
+             first))) ;; id
+       (simple-format
+        #t "debug: derivation-file-names->vhash: adding ~A entries to the cache\n"
+        (vlist-length result-for-missing-file-names))
+       (vhash-fold (lambda (key value _)
+                     (hash-set! derivation-ids-hash-table key value))
+                   '()
+                   result-for-missing-file-names)
+
+       (vhash-fold
+        (lambda (key value combined)
+          (vhash-cons key value combined))
+        result
+        result-for-missing-file-names)))))
 
+(define (derivations->derivation-ids conn derivations)
   (if (null? derivations)
       '()
-      (begin
-        (ensure-input-derivations-exist)
+      (let* ((derivations-count (length derivations))
+             (derivation-ids-hash-table (make-hash-table derivations-count)))
+        (simple-format
+         #t "debug: derivations->derivation-ids: processing ~A derivations\n"
+         derivations-count)
         (let* ((derivation-file-names (map derivation-file-name
                                            derivations))
 
-               (existing-derivation-entries (exec-query->vhash
-                                             conn
-                                             (select-existing-derivations
-                                              derivation-file-names)
-                                             second ;; file_name
-                                             first)) ;; id
+               (existing-derivation-entries
+                (derivation-file-names->vhash conn
+                                              derivation-ids-hash-table
+                                              derivation-file-names))
 
                (missing-derivations
-                (filter (lambda (derivation)
-                          (not (vhash-assoc (derivation-file-name derivation)
-                                            existing-derivation-entries)))
-                        derivations))
+                (deduplicate-derivations
+                 (filter (lambda (derivation)
+                           (not (vhash-assoc (derivation-file-name derivation)
+                                             existing-derivation-entries)))
+                         derivations)))
 
                (new-derivation-entries
                 (if (null? missing-derivations)
                     '()
-                    (insert-missing-derivations conn missing-derivations)))
+                    (insert-missing-derivations conn
+                                                derivation-ids-hash-table
+                                                missing-derivations)))
 
                (new-entries-id-lookup-vhash
                 (two-lists->vhash (map derivation-file-name missing-derivations)
                                   new-derivation-entries)))
+
           (map (lambda (derivation-file-name)
                  (cdr
                   (or (vhash-assoc derivation-file-name
diff --git a/guix-data-service/model/guix-revision-package-derivation.scm b/guix-data-service/model/guix-revision-package-derivation.scm
new file mode 100644
index 0000000..733dad2
--- /dev/null
+++ b/guix-data-service/model/guix-revision-package-derivation.scm
@@ -0,0 +1,20 @@
+(define-module (guix-data-service model guix-revision-package-derivation)
+  #:use-module (squee)
+  #:export (insert-guix-revision-package-derivations))
+
+(define (insert-guix-revision-package-derivations
+         conn guix-revision-id package-derivation-ids)
+  (define insert
+    (string-append "INSERT INTO guix_revision_package_derivations "
+                   "(revision_id, package_derivation_id) "
+                   "VALUES "
+                   (string-join (map (lambda (package-derivation-id)
+                                       (simple-format
+                                        #f "(~A, ~A)"
+                                        guix-revision-id
+                                        package-derivation-id))
+                                     package-derivation-ids)
+                                ", ")
+                   ";"))
+
+  (exec-query conn insert))
diff --git a/guix-data-service/model/guix-revision-package.scm b/guix-data-service/model/guix-revision-package.scm
deleted file mode 100644
index 2f710a4..0000000
--- a/guix-data-service/model/guix-revision-package.scm
+++ /dev/null
@@ -1,19 +0,0 @@
-(define-module (guix-data-service model guix-revision-package)
-  #:use-module (squee)
-  #:export (insert-guix-revision-packages))
-
-(define (insert-guix-revision-packages conn guix-revision-id package-ids)
-  (define insert
-    (string-append "INSERT INTO guix_revision_packages "
-                   "(revision_id, package_id) "
-                   "VALUES "
-                   (string-join (map (lambda (package-id)
-                                       (simple-format
-                                        #f "(~A, ~A)"
-                                        guix-revision-id
-                                        package-id))
-                                     package-ids)
-                                ", ")
-                   ";"))
-
-  (exec-query conn insert))
diff --git a/guix-data-service/model/package-derivation.scm b/guix-data-service/model/package-derivation.scm
new file mode 100644
index 0000000..6e87765
--- /dev/null
+++ b/guix-data-service/model/package-derivation.scm
@@ -0,0 +1,112 @@
+(define-module (guix-data-service model package-derivation)
+  #:use-module (srfi srfi-1)
+  #:use-module (ice-9 vlist)
+  #:use-module (ice-9 match)
+  #:use-module (squee)
+  #:use-module (guix-data-service model utils)
+  #:export (insert-package-derivations
+            count-packages-derivations-in-revision))
+
+(define (insert-missing-package-derivations conn entries)
+  (define query
+    (string-append
+     "INSERT INTO package_derivations "
+     "(package_id, derivation_id, system, target) VALUES "
+     (string-join
+      (map
+       (lambda (entry)
+         (apply simple-format
+                #f "(~A, ~A, '~A', '~A')"
+                entry))
+       entries)
+      ", ")
+     " RETURNING id"))
+
+  (exec-query conn query))
+
+(define (insert-package-derivations conn
+                                    package-ids-systems-and-targets
+                                    derivation-ids)
+  (define select-existing-package-derivation-entries
+    (string-append
+     "SELECT id, package_derivations.package_id,"
+     " package_derivations.derivation_id, package_derivations.system,"
+     " package_derivations.target "
+     "FROM package_derivations "
+     "JOIN (VALUES "
+     (string-join (map (match-lambda*
+                         (((package-id system target) derivation-id)
+                          (simple-format
+                           #f "(~A, ~A, '~A', '~A')"
+                           package-id
+                           derivation-id
+                           system
+                           target)))
+                       package-ids-systems-and-targets
+                       derivation-ids)
+                  ", ")
+     ") AS vals (package_id, derivation_id, system, target) "
+     "ON package_derivations.package_id = vals.package_id "
+     "AND package_derivations.derivation_id = vals.derivation_id "
+     "AND package_derivations.system = vals.system "
+     "AND package_derivations.target = vals.target"))
+
+  (define data-4-tuples
+    (map (match-lambda*
+           (((package-id system target) derivation-id)
+            (list package-id
+                  derivation-id
+                  system
+                  target)))
+         package-ids-systems-and-targets
+         derivation-ids))
+
+  (if (null? data-4-tuples)
+      '()
+      (begin
+        (let* ((existing-entries
+                (exec-query->vhash
+                 conn
+                 select-existing-package-derivation-entries
+                 cdr
+                 first)) ;; id
+
+               (missing-entries
+                (filter (lambda (4-tuple)
+                          (not (vhash-assoc 4-tuple existing-entries)))
+                        data-4-tuples))
+
+               (new-entry-ids
+                (if (null? missing-entries)
+                    '()
+                    (begin
+                      (vlist->list existing-entries)
+                      (insert-missing-package-derivations conn missing-entries))))
+
+               (new-entries-id-lookup-vhash
+                (two-lists->vhash missing-entries
+                                  new-entry-ids)))
+          (map (lambda (4-tuple)
+                 (cdr
+                  (or (vhash-assoc 4-tuple existing-entries)
+                      (vhash-assoc 4-tuple new-entries-id-lookup-vhash)
+                      (error "Missing entry"))))
+               data-4-tuples)))))
+
+(define (count-packages-derivations-in-revision conn commit-hash)
+  (define query
+    "
+SELECT package_derivations.system, package_derivations.target,
+COUNT(DISTINCT package_derivations.derivation_id)
+FROM package_derivations
+WHERE package_derivations.id IN (
+ SELECT guix_revision_package_derivations.package_derivation_id
+ FROM guix_revision_package_derivations
+ INNER JOIN guix_revisions
+   ON guix_revision_package_derivations.revision_id = guix_revisions.id
+ WHERE guix_revisions.commit = $1
+)
+GROUP BY package_derivations.system, package_derivations.target
+ORDER BY package_derivations.system DESC, package_derivations.target DESC")
+
+  (exec-query conn query (list commit-hash)))
diff --git a/guix-data-service/model/package-metadata.scm b/guix-data-service/model/package-metadata.scm
index 429538b..bd6cbe6 100644
--- a/guix-data-service/model/package-metadata.scm
+++ b/guix-data-service/model/package-metadata.scm
@@ -9,6 +9,7 @@
   #:use-module (guix inferior)
   #:use-module (guix-data-service model utils)
   #:export (select-package-metadata
+            select-package-metadata-by-revision-name-and-version
             insert-package-metadata
             inferior-packages->package-metadata-ids))
 
@@ -22,6 +23,29 @@
                               ",")
                  ");"))
 
+(define (select-package-metadata-by-revision-name-and-version
+         conn revision-commit-hash name version)
+  (define query "
+SELECT package_metadata.synopsis, package_metadata.description,
+  package_metadata.home_page
+FROM package_metadata
+INNER JOIN packages
+  ON package_metadata.id = packages.package_metadata_id
+WHERE packages.id IN (
+  SELECT package_derivations.package_id
+  FROM package_derivations
+  INNER JOIN guix_revision_package_derivations
+    ON package_derivations.id =
+    guix_revision_package_derivations.package_derivation_id
+  INNER JOIN guix_revisions
+    ON guix_revision_package_derivations.revision_id = guix_revisions.id
+  WHERE guix_revisions.commit = $1
+)
+  AND packages.name = $2
+  AND packages.version = $3")
+
+  (exec-query conn query (list revision-commit-hash name version)))
+
 (define (insert-package-metadata metadata-rows)
   (string-append "INSERT INTO package_metadata "
                  "(sha1_hash, synopsis, description, home_page) "
diff --git a/guix-data-service/model/package.scm b/guix-data-service/model/package.scm
index b5a38fa..bb01986 100644
--- a/guix-data-service/model/package.scm
+++ b/guix-data-service/model/package.scm
@@ -7,75 +7,93 @@
   #:use-module (guix-data-service model utils)
   #:export (select-existing-package-entries
             select-packages-in-revision
+            count-packages-in-revision
             insert-into-package-entries
             inferior-packages->package-ids))
 
 (define (select-existing-package-entries package-entries)
   (string-append "SELECT id, packages.name, packages.version, "
-                 "packages.package_metadata_id, packages.derivation_id "
+                 "packages.package_metadata_id "
                  "FROM packages "
                  "JOIN (VALUES "
                  (string-join (map (lambda (package-entry)
                                      (apply
                                       simple-format
-                                      #f "('~A', '~A', ~A, ~A)"
+                                      #f "('~A', '~A', ~A)"
                                       package-entry))
                                    package-entries)
                               ", ")
-                 ") AS vals (name, version, package_metadata_id, derivation_id) "
+                 ") AS vals (name, version, package_metadata_id) "
                  "ON packages.name = vals.name AND "
                  "packages.version = vals.version AND "
-                 "packages.package_metadata_id = vals.package_metadata_id AND "
-                 "packages.derivation_id = vals.derivation_id"
-                 ";"))
+                 "packages.package_metadata_id = vals.package_metadata_id"))
 
 (define (select-packages-in-revision conn commit-hash)
   (define query
-    (string-append
-     "SELECT packages.name, packages.version, packages.derivation_id "
-     "FROM packages "
-     "INNER JOIN guix_revision_packages"
-     " ON packages.id = guix_revision_packages.package_id "
-     "INNER JOIN guix_revisions"
-     " ON guix_revision_packages.revision_id = guix_revisions.id "
-     "WHERE guix_revisions.commit = $1 "
-     "ORDER BY packages.name, packages.version"))
+    "
+SELECT packages.name, packages.version, package_metadata.synopsis
+FROM packages
+INNER JOIN package_metadata
+  ON packages.package_metadata_id = package_metadata.id
+WHERE packages.id IN (
+ SELECT package_derivations.package_id
+ FROM package_derivations
+ INNER JOIN guix_revision_package_derivations
+   ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
+ INNER JOIN guix_revisions
+   ON guix_revision_package_derivations.revision_id = guix_revisions.id
+ WHERE guix_revisions.commit = $1
+)
+ORDER BY packages.name, packages.version")
+
+  (exec-query conn query (list commit-hash)))
+
+(define (count-packages-in-revision conn commit-hash)
+  (define query
+    "
+SELECT COUNT(DISTINCT packages.name)
+FROM packages
+WHERE packages.id IN (
+ SELECT package_derivations.package_id
+ FROM package_derivations
+ INNER JOIN guix_revision_package_derivations
+   ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
+ INNER JOIN guix_revisions
+   ON guix_revision_package_derivations.revision_id = guix_revisions.id
+ WHERE guix_revisions.commit = $1
+)")
 
   (exec-query conn query (list commit-hash)))
 
 (define (insert-into-package-entries package-entries)
   (string-append "INSERT INTO packages "
-                 "(name, version, package_metadata_id, derivation_id) VALUES "
+                 "(name, version, package_metadata_id) VALUES "
                  (string-join
                   (map
                    (match-lambda
-                     ((name version package_metadata_id derivation_id)
-                      (simple-format #f "('~A', '~A', ~A, ~A)"
+                     ((name version package_metadata_id)
+                      (simple-format #f "('~A', '~A', ~A)"
                                      name
                                      version
-                                     package_metadata_id
-                                     derivation_id)))
+                                     package_metadata_id)))
                    package-entries)
                   ",")
                  " RETURNING id"
                  ";"))
 
-(define (inferior-packages->package-ids conn packages metadata-ids derivation-ids)
+(define (inferior-packages->package-ids conn packages metadata-ids)
   (define package-entries
-    (map (lambda (package metadata-id derivation-id)
+    (map (lambda (package metadata-id)
            (list (inferior-package-name package)
                  (inferior-package-version package)
-                 metadata-id
-                 derivation-id))
+                 metadata-id))
          packages
-         metadata-ids
-         derivation-ids))
+         metadata-ids))
 
   (let* ((existing-package-entry-ids
           (exec-query->vhash conn
                              (select-existing-package-entries package-entries)
-                             ;; name, version, package_metadata_id and
-                             ;; derivation_id
+                             ;; name, version and package_metadata_id
                              cdr
                              first)) ;;id
          (missing-package-entries