database: Merge Derivations into Builds table.

Fixes <https://bugs.gnu.org/32190>.

* Makefile.am (dist_sql_DATA): Add 'src/sql/upgrade-2.sql'.
* doc/cuirass.texi (Derivations): Remove section.
(Builds): Update accordingly.  Add columns from the Derivations table.
(Outputs): Replace 'id' with 'derivation'.
* src/cuirass/base.scm (evaluate): Don't add jobs to the Derivations table.
(build-packages): Add columns that were in the Derivations table before.  Only
build the derivations that were successfully registered, that is, those that
didn't exist in the Builds table.  Give a derivation instead of a build id to
DB-GET-BUILD.  Compute the number of failed jobs based on the derivations that
were added to the table, instead of the jobs.
* src/cuirass/database.scm (db-add-derivation, db-get-derivation): Remove
exported procedures.
(db-add-build): Catch SQLITE_CONSTRAINT_PRIMARYKEY error, which means that two
jobs produced the same derivation, and return #f in that case.  Add columns
that were in the Derivations table before.  Use 'derivation' as primary key
for the Outputs table.
(db-get-outputs): Use 'derivation' as identifier, instead of 'build-id'.
(filters->order): Replace 'id' with 'rowid'.
(db-get-builds): Add a 'derivation' filter.  Replace 'id' with 'rowid'.
Remove the 'INNER JOIN Derivations'.  Replace Derivations with Builds.  Return
'derivation' in first position to make it clear that it's the primary key.
Pass DERIVATION instead of ID to DB-GET-OUTPUTS.
(db-get-build): Allow to take a derivation as argument.  Use NUMBER? to
differentiate between derivations and ids.
(db-get-pending-derivations): Remove the 'SELECT DISTINCT' clause now that
derivations are unique.  Remove the 'INNER JOIN Builds'.
(db-get-evaluations-build-summary, db-get-builds-min, db-get-builds-max):
Replace 'id' with 'rowid'.
* src/schema.sql (Derivations): Remove table.
(Outputs): Replace Builds.id with Builds.derivation.
(Builds): Use 'derivation' as primary key.  Remove the 'id' column.  Add
'job_name', 'system', 'nix_name' columns that were in the Derivations table
before.
(Builds_Derivations_index): Rename to Builds_index.  Update accordingly.
(Derivations_index): Remove index.
* src/sql/upgrade-2.sql: New file with SQL queries to upgrade the database.
* tests/database.scm (make-dummy-job, make-dummy-derivation): Remove
procedures.
(make-dummy-build): Add columns that were in MAKE-DUMMY-DERIVATION.  Get the
DRV parameter to be mandatory because it's a primary key.
(%id): Remove parameter.
("db-add-derivation", "db-get-derivation"): Remove tests.
("db-add-build"): Expect #f, because it adds twice the same derivation.  Pass
the derivation argument to MAKE-DUMMY-BUILD.
("db-update-build-status!"): Rename 'id' to 'derivation'.  Pass the derivation
argument to MAKE-DUMMY-BUILD.  Remove the DB-ADD-DERIVATION call.
("db-get-builds", "db-get-pending-derivations"): Pass the derivation argument
to MAKE-DUMMY-BUILD.  Remove the DB-ADD-DERIVATION calls.
* tests/http.scm ("fill-db"): Remove DERIVATION1 and DERIVATION2, and put
their content in BUILD1 and BUILD2.  Remove the DB-ADD-DERIVATION calls.

2 files changed, 29 insertions, 69 deletions

diff --git a/tests/database.scm b/tests/database.scm
index 17d48f5..af518bd 100644
--- a/tests/database.scm
+++ b/tests/database.scm
@@ -50,27 +50,14 @@
   `((#:specification . "guix")
     (#:commits . ,commits)))
 
-(define* (make-dummy-job #:optional (name "foo"))
-  `((#:name . ,name)
-    (#:job-name . "job")
-    (#:system . "x86_64-linux")
-    (#:derivation . ,(string-append name ".drv"))
-    (#:nix-name . "foo")
-    (#:specification 0)
-    (#:eval-id . 42)))
-
-(define* (make-dummy-derivation drv #:optional (eval-id 0))
+(define* (make-dummy-build drv
+                           #:optional (eval-id 42)
+                           #:key (outputs '(("foo" . "/foo"))))
   `((#:derivation . ,drv)
+    (#:eval-id . ,eval-id)
     (#:job-name . "job")
     (#:system . "x86_64-linux")
-    (#:nix-name . ,(basename drv ".drv"))
-    (#:eval-id . ,eval-id)))
-
-(define* (make-dummy-build #:optional (eval-id 42)
-                           #:key (drv "/foo.drv")
-                           (outputs '(("foo" . "/foo"))))
-  `((#:derivation . ,drv)
-    (#:eval-id . ,eval-id)
+    (#:nix-name . "foo")
     (#:log . "log")
     (#:outputs . (("foo" . "/foo")))))
 
@@ -86,10 +73,6 @@
   ;; Global Slot for a database object.
   (make-parameter #t))
 
-(define %id
-  ;; Global Slot for a job ID in the database.
-  (make-parameter #t))
-
 (define database-name
   ;; Use an empty and temporary database for the tests.
   (string-append (getcwd) "/" (number->string (getpid)) "-tmp.db"))
@@ -114,21 +97,13 @@ INSERT INTO Evaluations (specification, commits) VALUES (3, 3);")
       (db-add-specification (%db) example-spec)
       (car (db-get-specifications (%db)))))
 
-  (test-assert "db-add-derivation"
-    (let* ((job (make-dummy-job))
-           (key (assq-ref job #:derivation)))
-      (db-add-derivation (%db) job)
-      (db-add-derivation (%db) job)               ;idempotent
-      (%id key)))
-
-  (test-assert "db-get-derivation"
-    (db-get-derivation (%db) (%id)))
-
-  (test-assert "db-add-build"
-    (let ((build (make-dummy-build)))
+  (test-equal "db-add-build"
+    #f
+    (let ((build (make-dummy-build "/foo.drv")))
       (db-add-build (%db) build)
 
-      ;; This should be idempotent, see <https://bugs.gnu.org/28094>.
+      ;; Should return #f when adding a build whose derivation is already
+      ;; there, see <https://bugs.gnu.org/28094>.
       (db-add-build (%db) build)))
 
   (test-equal "db-update-build-status!"
@@ -137,13 +112,12 @@ INSERT INTO Evaluations (specification, commits) VALUES (3, 3);")
           (build-status succeeded)
           "/foo.drv.log")
     (with-temporary-database db
-      (let* ((id (db-add-build
-                  db
-                  (make-dummy-build 1 #:drv "/foo.drv"
-                                    #:outputs '(("out" . "/foo")))))
+      (let* ((derivation (db-add-build
+                          db
+                          (make-dummy-build "/foo.drv" 1
+                                            #:outputs '(("out" . "/foo")))))
              (get-status (lambda* (#:optional (key #:status))
-                           (assq-ref (db-get-build db id) key))))
-        (db-add-derivation db (make-dummy-derivation "/foo.drv" 1))
+                           (assq-ref (db-get-build db derivation) key))))
         (db-add-evaluation db (make-dummy-eval))
         (db-add-specification db example-spec)
 
@@ -171,18 +145,15 @@ INSERT INTO Evaluations (specification, commits) VALUES (3, 3);")
       ((3 "/baz.drv"))                               ;nr = 1
       ((2 "/bar.drv") (1 "/foo.drv") (3 "/baz.drv"))) ;status+submission-time
     (with-temporary-database db
-      ;; Populate the 'Builds', 'Derivations', 'Evaluations', and
+      ;; Populate the 'Builds'', 'Evaluations', and
       ;; 'Specifications' tables in a consistent way, as expected by the
       ;; 'db-get-builds' query.
-      (db-add-build db (make-dummy-build 1 #:drv "/foo.drv"
+      (db-add-build db (make-dummy-build "/foo.drv" 1
                                          #:outputs `(("out" . "/foo"))))
-      (db-add-build db (make-dummy-build 2 #:drv "/bar.drv"
+      (db-add-build db (make-dummy-build "/bar.drv" 2
                                          #:outputs `(("out" . "/bar"))))
-      (db-add-build db (make-dummy-build 3 #:drv "/baz.drv"
+      (db-add-build db (make-dummy-build "/baz.drv" 3
                                          #:outputs `(("out" . "/baz"))))
-      (db-add-derivation db (make-dummy-derivation "/foo.drv" 1))
-      (db-add-derivation db (make-dummy-derivation "/bar.drv" 2))
-      (db-add-derivation db (make-dummy-derivation "/baz.drv" 3))
       (db-add-evaluation db (make-dummy-eval))
       (db-add-evaluation db (make-dummy-eval))
       (db-add-evaluation db (make-dummy-eval))
@@ -206,19 +177,16 @@ INSERT INTO Evaluations (specification, commits) VALUES (3, 3);")
   (test-equal "db-get-pending-derivations"
     '("/bar.drv" "/foo.drv")
     (with-temporary-database db
-      ;; Populate the 'Builds', 'Derivations', 'Evaluations', and
+      ;; Populate the 'Builds', 'Evaluations', and
       ;; 'Specifications' tables.  Here, two builds map to the same derivation
       ;; but the result of 'db-get-pending-derivations' must not contain any
       ;; duplicate.
-      (db-add-build db (make-dummy-build 1 #:drv "/foo.drv"
+      (db-add-build db (make-dummy-build "/foo.drv" 1
                                          #:outputs `(("out" . "/foo"))))
-      (db-add-build db (make-dummy-build 2 #:drv "/bar.drv"
+      (db-add-build db (make-dummy-build "/bar.drv" 2
                                          #:outputs `(("out" . "/bar"))))
-      (db-add-build db (make-dummy-build 3 #:drv "/foo.drv"
+      (db-add-build db (make-dummy-build "/foo.drv" 3
                                          #:outputs `(("out" . "/foo"))))
-      (db-add-derivation db (make-dummy-derivation "/foo.drv" 1))
-      (db-add-derivation db (make-dummy-derivation "/bar.drv" 2))
-      (db-add-derivation db (make-dummy-derivation "/foo.drv" 3))
       (db-add-evaluation db (make-dummy-eval))
       (db-add-evaluation db (make-dummy-eval))
       (db-add-evaluation db (make-dummy-eval))
diff --git a/tests/http.scm b/tests/http.scm
index e05fdc5..a9fc3ef 100644
--- a/tests/http.scm
+++ b/tests/http.scm
@@ -142,6 +142,9 @@
     (let* ((build1
             `((#:derivation . "/gnu/store/fake.drv")
               (#:eval-id . 1)
+              (#:job-name . "fake-job")
+              (#:system . "x86_64-linux")
+              (#:nix-name . "fake-1.0")
               (#:log . "unused so far")
               (#:status . ,(build-status succeeded))
               (#:outputs . (("out" . "/gnu/store/fake-1.0")))
@@ -151,24 +154,15 @@
            (build2
             `((#:derivation . "/gnu/store/fake2.drv")
               (#:eval-id . 1)
+              (#:job-name . "fake-job")
+              (#:system . "x86_64-linux")
+              (#:nix-name . "fake-2.0")
               (#:log . "unused so far")
               (#:status . ,(build-status scheduled))
               (#:outputs . (("out" . "/gnu/store/fake-2.0")))
               (#:timestamp . 1501347493)
               (#:starttime . 0)
               (#:stoptime . 0)))
-           (derivation1
-            '((#:derivation . "/gnu/store/fake.drv")
-              (#:job-name . "fake-job")
-              (#:system . "x86_64-linux")
-              (#:nix-name . "fake-1.0")
-              (#:eval-id . 1)))
-           (derivation2
-            '((#:derivation . "/gnu/store/fake2.drv")
-              (#:job-name . "fake-job")
-              (#:system . "x86_64-linux")
-              (#:nix-name . "fake-2.0")
-              (#:eval-id . 1)))
            (specification
             '((#:name . "guix")
               (#:load-path-inputs . ("savannah"))
@@ -192,8 +186,6 @@
               (#:commits . ("fakesha2" "fakesha3")))))
       (db-add-build (%db) build1)
       (db-add-build (%db) build2)
-      (db-add-derivation (%db) derivation1)
-      (db-add-derivation (%db) derivation2)
       (db-add-specification (%db) specification)
       (db-add-evaluation (%db) evaluation1)
       (db-add-evaluation (%db) evaluation2)))