branch master updated: Use the Build id as Jobs foreign key.

[Mathieu Othacehe]

This is an automated email from the git hooks/post-receive script.

mothacehe pushed a commit to branch master
in repository guix-cuirass.

The following commit(s) were added to refs/heads/master by this push:
     new 2c3440d  Use the Build id as Jobs foreign key.
2c3440d is described below

commit 2c3440de5c9962bac0b81e893f004dd3440b1651
Author: Mathieu Othacehe <othacehe@gnu.org>
AuthorDate: Thu Apr 8 10:49:41 2021 +0200

    Use the Build id as Jobs foreign key.
    
    Join Build and Jobs tables using the build id instead of the build 
derivation
    to speed queries.
    
    * src/sql/upgrade-3.sql: New file.
    * Makefile.am (dist_sql_DATA): Add it.
    * src/schema.sql (Jobs)[derivation]: Replace it by ...
    [build]: this new column.
    * src/cuirass/database.scm (db-add-job, db-get-jobs): Adapt them.
---
 Makefile.am              |  3 ++-
 src/cuirass/database.scm | 19 +++++++++++--------
 src/schema.sql           | 19 ++++++++++---------
 src/sql/upgrade-3.sql    | 10 ++++++++++
 4 files changed, 33 insertions(+), 18 deletions(-)

diff --git a/Makefile.am b/Makefile.am
index fd62500..9a599a2 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -89,7 +89,8 @@ dist_pkgdata_DATA = src/schema.sql
 
 dist_sql_DATA =                                \
   src/sql/upgrade-1.sql                                \
-  src/sql/upgrade-2.sql
+  src/sql/upgrade-2.sql                                \
+  src/sql/upgrade-3.sql
 
 dist_css_DATA =                                        \
   src/static/css/choices.min.css               \
diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index ec1e121..47340d3 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -694,10 +694,11 @@ JOB derivation."
     (pk output derivation)
     (with-db-worker-thread db
       (exec-query/bind db "\
-INSERT INTO Jobs (name, evaluation, derivation, system)
+INSERT INTO Jobs (name, evaluation, build, system)
 (SELECT " name ", " eval-id ",
-COALESCE((SELECT derivation FROM Outputs WHERE
-PATH = " output "), " derivation ")," system ")
+(SELECT id FROM Builds WHERE derivation =
+(SELECT COALESCE((SELECT derivation FROM Outputs WHERE
+PATH = " output "), " derivation ")))," system ")
 ON CONFLICT ON CONSTRAINT jobs_pkey DO NOTHING;"))))
 
 (define (db-get-jobs eval-id filters)
@@ -710,7 +711,7 @@ the symbols system and names."
   (with-db-worker-thread db
     (let ((query "
 SELECT Builds.id, Builds.status, Jobs.name FROM Jobs
-INNER JOIN Builds ON Jobs.derivation = Builds.derivation
+INNER JOIN Builds ON Jobs.build = Builds.id
 WHERE Jobs.evaluation = :evaluation
 AND ((Jobs.system = :system) OR :system IS NULL)
 AND ((Jobs.name = ANY(:names)) OR :names IS NULL)
@@ -758,9 +759,6 @@ ORDER BY Jobs.name")
            (timeout    (assq-ref job #:timeout))
            (outputs    (assq-ref job #:outputs))
            (cur-time   (time-second (current-time time-utc))))
-      ;; Always register JOB inside the Jobs table.  If it triggers a new
-      ;; build, also register it into the Builds table below.
-      (db-add-job job eval-id)
       (and (new-outputs? outputs)
            (let ((build `((#:derivation . ,drv)
                           (#:eval-id . ,eval-id)
@@ -789,7 +787,12 @@ ORDER BY Jobs.name")
                                      ((> time period) #t)
                                      (else #f))))
                    (and add-build? (db-add-build build)))
-                 (db-add-build build))))))
+                 (db-add-build build))))
+
+      ;; Always register JOB inside the Jobs table.  If there are new outputs,
+      ;; JOB will refer to the newly created build.  Otherwise, it will refer
+      ;; to the last build with the same build outputs.
+      (db-add-job job eval-id)))
 
   (with-db-worker-thread db
     (log-message "Registering builds for evaluation ~a." eval-id)
diff --git a/src/schema.sql b/src/schema.sql
index 3ce2bf1..1c050d8 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -24,15 +24,6 @@ CREATE TABLE Evaluations (
   FOREIGN KEY (specification) REFERENCES Specifications(name) ON DELETE CASCADE
 );
 
-CREATE TABLE Jobs (
-  name          TEXT NOT NULL,
-  evaluation    INTEGER NOT NULL,
-  derivation    TEXT NOT NULL,
-  system        TEXT NOT NULL,
-  PRIMARY KEY (evaluation, derivation),
-  FOREIGN KEY (evaluation) REFERENCES Evaluations(id) ON DELETE CASCADE
-);
-
 CREATE TABLE Checkouts (
   specification TEXT NOT NULL,
   revision      TEXT NOT NULL,
@@ -66,6 +57,16 @@ CREATE TABLE Builds (
   FOREIGN KEY (evaluation) REFERENCES Evaluations(id) ON DELETE CASCADE
 );
 
+CREATE TABLE Jobs (
+  name          TEXT NOT NULL,
+  evaluation    INTEGER NOT NULL,
+  build         INTEGER NOT NULL,
+  system        TEXT NOT NULL,
+  PRIMARY KEY (evaluation, build),
+  FOREIGN KEY (build) REFERENCES Builds(id) ON DELETE CASCADE,
+  FOREIGN KEY (evaluation) REFERENCES Evaluations(id) ON DELETE CASCADE
+);
+
 CREATE TABLE Outputs (
   derivation TEXT NOT NULL,
   name TEXT NOT NULL,
diff --git a/src/sql/upgrade-3.sql b/src/sql/upgrade-3.sql
new file mode 100644
index 0000000..32c6243
--- /dev/null
+++ b/src/sql/upgrade-3.sql
@@ -0,0 +1,10 @@
+BEGIN TRANSACTION;
+
+ALTER TABLE Jobs ADD COLUMN build INTEGER;
+ALTER TABLE Jobs ADD CONSTRAINT fk_builds
+   FOREIGN KEY (build)
+   REFERENCES Builds(id);
+UPDATE Jobs SET build = b.id FROM (SELECT Jobs.evaluation, Jobs.derivation, 
Builds.id FROM jobs JOIN Builds ON Jobs.derivation = Builds.derivation) b WHERE 
jobs.evaluation = b.evaluation and jobs.derivation = b.derivation;
+ALTER TABLE Jobs DROP COLUMN derivation;
+
+COMMIT;