Add new derivation_output_details_set_id column to the builds table

As this will hopefully provide a faster way of associating derivations with
builds.

6 files changed, 118 insertions, 15 deletions

diff --git a/guix-data-service/jobs/load-new-guix-revision.scm b/guix-data-service/jobs/load-new-guix-revision.scm
index 9d0e313..e6755c5 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 @@
   #:use-module (guix build utils)
   #:use-module (guix-data-service config)
   #:use-module (guix-data-service database)
+  #:use-module (guix-data-service model build)
   #:use-module (guix-data-service model channel-news)
   #:use-module (guix-data-service model package)
   #:use-module (guix-data-service model git-repository)
@@ -877,6 +878,9 @@ WHERE job_id = $1"
                      (inferior-data->package-derivation-ids
                       conn inf inferior-package-id->package-database-id
                       inferior-data-4-tuples)))
+                (update-builds-derivation-output-details-set-id
+                 conn
+                 (map fourth inferior-data-4-tuples))
 
                 (insert-guix-revision-package-derivations conn
                                                           guix-revision-id
diff --git a/guix-data-service/model/build.scm b/guix-data-service/model/build.scm
index ee83c30..ebdfd45 100644
--- a/guix-data-service/model/build.scm
+++ b/guix-data-service/model/build.scm
@@ -6,6 +6,7 @@
             select-builds-with-context
             select-builds-with-context-by-derivation-file-name
             select-build-by-build-server-and-derivation-file-name
+            update-builds-derivation-output-details-set-id
             insert-builds
             insert-build
             ensure-build-exists))
@@ -110,25 +111,86 @@ WHERE build_server_id = $1 AND derivation_file_name = $2")
     (_
      #f)))
 
-(define (insert-builds conn build-server-id derivation-file-names)
-  (insert-missing-data-and-return-all-ids
+(define (update-builds-derivation-output-details-set-id conn derivation-file-names)
+  (exec-query
    conn
-   "builds"
-   '(build_server_id derivation_file_name)
-   (map (lambda (derivation-file-name)
-          (list build-server-id
-                derivation-file-name))
-        derivation-file-names)
-   #:delete-duplicates? #t))
+   (string-append
+    "
+UPDATE builds SET derivation_output_details_set_id = (
+  SELECT derivations_by_output_details_set.derivation_output_details_set_id
+  FROM derivations_by_output_details_set
+  INNER JOIN derivations
+    ON derivations.file_name = builds.derivation_file_name
+  WHERE derivations_by_output_details_set.derivation_id = derivations.id
+) WHERE builds.derivation_output_details_set_id IS NULL AND
+        builds.derivation_file_name IN ("
+    (string-join (map quote-string derivation-file-names)
+                 ",")
+    ")")))
+
+(define (select-derivations-by-output-details-set-id-by-derivation-file-name
+         conn
+         derivation-file-name)
+  (match (exec-query
+          conn
+          "
+SELECT derivation_output_details_set_id
+FROM derivations_by_output_details_set
+INNER JOIN derivations
+  ON derivations.id = derivations_by_output_details_set.derivation_id
+WHERE derivations.file_name = $1"
+          (list derivation-file-name))
+    (((id))
+     (string->number id))
+    (_
+     #f)))
+
+(define (insert-builds conn build-server-id derivation-file-names)
+  (let ((build-ids
+         (insert-missing-data-and-return-all-ids
+          conn
+          "builds"
+          '(build_server_id derivation_file_name)
+          (map (lambda (derivation-file-name)
+                 (list build-server-id
+                       derivation-file-name))
+               derivation-file-names)
+          #:delete-duplicates? #t)))
+
+    (exec-query
+     conn
+     (string-append
+      "
+UPDATE builds SET derivation_output_details_set_id = (
+  SELECT derivations_by_output_details_set.derivation_output_details_set_id
+  FROM derivations_by_output_details_set
+  INNER JOIN derivations
+    ON derivations.file_name = builds.derivation_file_name
+  WHERE derivations_by_output_details_set.derivation_id = derivations.id
+) WHERE builds.derivation_output_details_set_id IS NULL AND builds.id IN ("
+      (string-join (map number->string
+                        build-ids)
+                   ",")
+      ")"))
+
+    build-ids))
 
 (define (insert-build conn build-server-id derivation-file-name)
-  (match (exec-query conn
-                     "
-INSERT INTO builds (build_server_id, derivation_file_name)
-VALUES ($1, $2)
+  (match (exec-query
+          conn
+          "
+INSERT INTO builds
+  (build_server_id, derivation_file_name, derivation_output_details_set_id)
+VALUES ($1, $2, $3)
 RETURNING (id)"
-                     (list (number->string build-server-id)
-                           derivation-file-name))
+          (list (number->string build-server-id)
+                derivation-file-name
+                (or
+                 (and=> (select-derivations-by-output-details-set-id-by-derivation-file-name
+                         conn
+                         derivation-file-name)
+                        number->string)
+                 "NULL")))
     (((id))
      (string->number id))))
 
diff --git a/sqitch/deploy/builds_add_derivation_output_details_set_id.sql b/sqitch/deploy/builds_add_derivation_output_details_set_id.sql
new file mode 100644
index 0000000..692e25c
--- /dev/null
+++ b/sqitch/deploy/builds_add_derivation_output_details_set_id.sql
@@ -0,0 +1,22 @@
+-- Deploy guix-data-service:builds_add_derivation_output_details_set_id to pg
+
+BEGIN;
+
+ALTER TABLE builds
+  ADD COLUMN derivation_output_details_set_id integer
+  NULL
+  DEFAULT NULL
+  REFERENCES derivation_output_details_sets (id);
+
+UPDATE builds SET derivation_output_details_set_id = (
+  SELECT derivations_by_output_details_set.derivation_output_details_set_id
+  FROM derivations_by_output_details_set
+  INNER JOIN derivations
+    ON derivations.file_name = builds.derivation_file_name
+  WHERE derivations_by_output_details_set.derivation_id = derivations.id
+);
+
+CREATE INDEX builds_derivation_output_details_set_id ON
+  builds (derivation_output_details_set_id);
+
+COMMIT;
diff --git a/sqitch/revert/builds_add_derivation_output_details_set_id.sql b/sqitch/revert/builds_add_derivation_output_details_set_id.sql
new file mode 100644
index 0000000..97a5954
--- /dev/null
+++ b/sqitch/revert/builds_add_derivation_output_details_set_id.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:builds_add_derivation_output_details_set_id from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 2f5b512..c047adf 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -37,3 +37,4 @@ change_nar_urls_size_to_bigint 2019-12-04T21:49:07Z   <chris@phact> # Change nar
 sort_out_duplicate_builds 2019-12-05T12:43:53Z   <chris@phact> # Sort out duplicate builds
 add_some_database_indexes 2019-12-05T15:53:04Z Christopher Baines <mail@cbaines.net> # Add some indexes
 derivation_output_sets 2019-12-05T23:19:05Z Christopher Baines <mail@cbaines.net> # Describe the sets of derivation outputs
+builds_add_derivation_output_details_set_id 2019-12-07T18:25:38Z Christopher Baines <mail@cbaines.net> # Add a derivation_output_details_set_id column to builds
diff --git a/sqitch/verify/builds_add_derivation_output_details_set_id.sql b/sqitch/verify/builds_add_derivation_output_details_set_id.sql
new file mode 100644
index 0000000..fc91296
--- /dev/null
+++ b/sqitch/verify/builds_add_derivation_output_details_set_id.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:builds_add_derivation_output_details_set_id on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;