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.

1 files changed, 16 insertions, 39 deletions

diff --git a/doc/cuirass.texi b/doc/cuirass.texi
index d4c6ae1..b51cfad 100644
--- a/doc/cuirass.texi
+++ b/doc/cuirass.texi
@@ -249,9 +249,8 @@ Cuirass uses a SQLite database to store information about jobs and past
 build results, but also to coordinate the execution of jobs.
 
 The database contains the following tables: @code{Specifications},
-@code{Inputs}, @code{Stamps}, @code{Evaluations}, @code{Derivations},
-@code{Builds} and @code{Outputs}.  The purpose of each of these tables is
-explained below.
+@code{Inputs}, @code{Stamps}, @code{Evaluations}, @code{Builds} and
+@code{Outputs}.  The purpose of each of these tables is explained below.
 
 @section Specifications
 @cindex specifications, database
@@ -349,9 +348,8 @@ and @code{stamp}, which holds the revisions (space separated commit hashes).
 @section Evaluations
 @cindex evaluations, database
 
-An evaluation relates a specification with the revision of the
-repository specified therein.  Derivations and builds (see below) each
-belong to a specific evaluation.
+An evaluation relates a specification with the revision of the repository
+specified therein.  Builds (see below) belong to a specific evaluation.
 
 The @code{Evaluations} table has the following columns:
 
@@ -368,20 +366,21 @@ This text field holds the revisions (space separated commit hashes) of the
 repositories specified as inputs of the related specification.
 @end table
 
-@section Derivations
-@cindex derivations, database
+@section Builds
+@cindex builds, database
 
-This table associates a tuple of the absolute derivation file name and
-evaluation identifier with a job name.
+This table holds records of the derivations and their build status.  Note that
+a job will be registered here only if its derivation doesn't already exist.
 
 @table @code
 @item derivation
-This column holds the absolute file name of the Guix derivation that is
-supposed to be evaluated for this job.
+This text field holds the absolute name of the derivation file that
+resulted in this build.
 
 @item evaluation
-This field holds the @code{id} of an evaluation from the
-@code{Evaluations} table.
+This integer field references the evaluation identifier from the
+@code{Evaluations} table, indicating to which evaluation this build
+belongs.
 
 @item job_name
 This text field holds the name of the job.
@@ -393,28 +392,6 @@ This text field holds the system name of the derivation.
 This text field holds the name of the derivation ---e.g.,
 @code{coreutils-8.24}.
 
-@end table
-
-@section Builds
-@cindex builds, database
-
-This table holds records of completed or failed package builds.  Note
-that builds are not in a one to one relationship with derivations in
-order to keep track of non-deterministic compilations.
-
-@table @code
-@item id
-This is an automatically incrementing numeric identifier.
-
-@item derivation
-This text field holds the absolute name of the derivation file that
-resulted in this build.
-
-@item evaluation
-This integer field references the evaluation identifier from the
-@code{Evaluations} table, indicating to which evaluation this build
-belongs.
-
 @item log
 This text field holds the absolute file name of the build log file.
 
@@ -442,9 +419,9 @@ stored in @code{Builds} table may have zero (if it has failed), one or
 multiple outputs.
 
 @table @code
-@item build
-This field holds the @code{id} of a build from the
-@code{Builds} table.
+@item derivation
+This field holds the @code{derivation} of a build from the @code{Builds}
+table.
 
 @item name
 This text field holds the name of the output.