bug#32190: Cuirass doesn't check if two subsequent jobs yield the same derivation

[Clément Lassieur]

Consider the following table:

--8<---------------cut here---------------start------------->8---
CREATE TABLE Derivations (
  derivation    TEXT NOT NULL,
  evaluation    INTEGER NOT NULL,
  job_name      TEXT NOT NULL,
  system        TEXT NOT NULL,
  nix_name      TEXT NOT NULL,
  PRIMARY KEY (derivation, evaluation),
  FOREIGN KEY (evaluation) REFERENCES Evaluations (id)
);
--8<---------------cut here---------------end--------------->8---

And the following code:

--8<---------------cut here---------------start------------->8---
(define (db-add-derivation db job)
  "Store a derivation result in database DB and return its ID."
  (catch 'sqlite-error
    (lambda ()
      (sqlite-exec db "\
INSERT INTO Derivations (derivation, job_name, system, nix_name, evaluation)\
  VALUES ("
                   (assq-ref job #:derivation) ", "
                   (assq-ref job #:job-name) ", "
                   (assq-ref job #:system) ", "
                   (assq-ref job #:nix-name) ", "
                   (assq-ref job #:eval-id) ");")
      (last-insert-rowid db))
    (lambda (key who code message . rest)
      ;; If we get a unique-constraint-failed error, that means we have
      ;; already inserted the same (derivation,eval-id) tuple.  That happens
      ;; when several jobs produce the same derivation, and we can ignore it.
      (if (= code SQLITE_CONSTRAINT_PRIMARYKEY)
          (sqlite-exec db "SELECT * FROM Derivations WHERE derivation="
                       (assq-ref job #:derivation) ";")
          (apply throw key who code rest)))))
--8<---------------cut here---------------end--------------->8---

I think the above constraint can't happen because by definition a new
job (for the same job_name) is produced at each evaluation.  So eval-id
will be incremented every time.

Also, the docs (and a comment in schema.sql) says:

    Builds are not in a one to one relationship with derivations in
    order to keep track of non deterministic compilations.

But I think it doesn't make sense, because Guix won't try to build twice
the same thing unless '--check' is used (which obviously isn't the
case).

So not only we have a huge Derivations table full of identical items,
but we also ask Guix to build them and we store the results in the
Builds table...

Maybe the solution is to replace the (derivation, evaluation) primary
key with (derivation), and only build the newly added derivations.
WDYT?

Clément