db: Forbid inserting the same specification twice.

* src/schema.sql (Specifications): Make 'repo_name' the primary key.
(Stamps, Evaluations): Adapt.
* src/cuirass/database.scm (db-get-specifications): Likewise.
(db-add-specification): Ignore if SPEC has already been added.
* tests/database.scm (example-spec): Adapt.

2 files changed, 9 insertions, 11 deletions

diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index f1505e8..870cdc0 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -115,8 +115,8 @@ database object."
 (define (db-add-specification db spec)
   "Store specification SPEC in database DB and return its ID."
   (apply sqlite-exec db "\
-INSERT INTO Specifications (repo_name, url, load_path, file, proc, arguments, \
-                            branch, tag, revision, no_compile_p) \
+INSERT OR IGNORE INTO Specifications (repo_name, url, load_path, file, \
+                  proc, arguments, branch, tag, revision, no_compile_p) \
   VALUES ('~A', '~A', '~A', '~A', '~S', '~S', '~A', '~A', '~A', ~A);"
          (append
           (assq-refs spec '(#:name #:url #:load-path #:file #:proc #:arguments))
@@ -129,11 +129,10 @@ INSERT INTO Specifications (repo_name, url, load_path, file, proc, arguments, \
              (specs '()))
     (match rows
       (() specs)
-      ((#(id name url load-path file proc args branch tag rev no-compile?)
+      ((#(name url load-path file proc args branch tag rev no-compile?)
         . rest)
        (loop rest
-             (cons `((#:id . ,id)
-                     (#:name . ,name)
+             (cons `((#:name . ,name)
                      (#:url . ,url)
                      (#:load-path . ,load-path)
                      (#:file . ,file)
diff --git a/src/schema.sql b/src/schema.sql
index f8042d1..4aeebb7 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -1,8 +1,7 @@
 BEGIN TRANSACTION;
 
 CREATE TABLE Specifications (
-  id            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  repo_name     TEXT NOT NULL,
+  repo_name     TEXT NOT NULL PRIMARY KEY,
   url           TEXT NOT NULL,
   load_path     TEXT NOT NULL,
   file          TEXT NOT NULL,
@@ -16,16 +15,16 @@ CREATE TABLE Specifications (
 );
 
 CREATE TABLE Stamps (
-  specification INTEGER NOT NULL PRIMARY KEY,
+  specification TEXT NOT NULL PRIMARY KEY,
   stamp         TEXT NOT NULL,
-  FOREIGN KEY (specification) REFERENCES Specifications (id)
+  FOREIGN KEY (specification) REFERENCES Specifications (repo_name)
 );
 
 CREATE TABLE Evaluations (
   id            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  specification INTEGER NOT NULL,
+  specification TEXT NOT NULL,
   revision      TEXT NOT NULL,
-  FOREIGN KEY (specification) REFERENCES Specifications (id)
+  FOREIGN KEY (specification) REFERENCES Specifications (repo_name)
 );
 
 CREATE TABLE Derivations (