schema: Separate 'Evaluations' from 'Builds'.

Adapt src/cuirass/database.scm and its tests.

2 files changed, 49 insertions, 20 deletions

diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index c9c106e..101a02d 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -25,6 +25,7 @@
             db-init
             db-open
             db-close
+            db-add-specification
             db-add-evaluation
             db-get-evaluation
             db-delete-evaluation
@@ -33,6 +34,7 @@
             sqlite-exec
             ;; Parameters.
             %package-database
+            %package-schema-file
             ;; Macros.
             with-database))
 
@@ -102,20 +104,33 @@ database object."
   (vector-ref (car (sqlite-exec db "SELECT last_insert_rowid();"))
               0))
 
+(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)\
+    VALUES ('~A', '~A', '~A', '~A', '~S', '~S', '~A', '~A', '~A');"
+         (append
+          (assq-refs spec '(#:name #:url #:load-path #:file #:proc #:arguments))
+          (assq-refs spec '(#:branch #:tag #:commit) "NULL")))
+  (last-insert-rowid db))
+
 (define (db-add-evaluation db job)
   "Store a derivation result in database DB and return its ID."
-  (sqlite-exec db "insert into build (job_spec, drv) values ('~A', '~A');"
+  (sqlite-exec db "\
+INSERT INTO Evaluations (derivation, job_name, specification)\
+  VALUES ('~A', '~A', '~A');"
+               (assq-ref job #:derivation)
                (assq-ref job #:job-name)
-               (assq-ref job #:derivation))
-  (last-insert-rowid db))
+               (assq-ref job #:spec-id)))
 
 (define (db-get-evaluation db id)
   "Retrieve a job in database DB which corresponds to ID."
-  (car (sqlite-exec db "select * from build where id=~A;" id)))
+  (car (sqlite-exec db "select * from Evaluations where derivation='~A';" id)))
 
 (define (db-delete-evaluation db id)
   "Delete a job in database DB which corresponds to ID."
-  (sqlite-exec db "delete from build where id=~A;" id))
+  (sqlite-exec db "delete from Evaluations where derivation='~A';" id))
 
 (define-syntax-rule (with-database db body ...)
   "Run BODY with a connection to the database which is bound to DB in BODY."
diff --git a/src/schema.sql b/src/schema.sql
index 9786064..9cc7167 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -1,18 +1,32 @@
-create table job_spec (
-  name            text not null,
-  url             text not null,
-  branch          text not null,
-  file            text not null,
-  proc            text not null,
-  arguments       text not null,
-  primary key (name)
+BEGIN TRANSACTION;
+
+CREATE TABLE Specifications (
+  id            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  repo_name     TEXT NOT NULL,
+  url           TEXT NOT NULL,
+  load_path     TEXT NOT NULL,
+  file          TEXT NOT NULL,
+  proc          TEXT NOT NULL,
+  arguments     TEXT NOT NULL,
+  -- The following columns are optional.
+  branch        TEXT,
+  tag           TEXT,
+  revision      TEXT
+);
+
+CREATE TABLE Evaluations (
+  derivation    TEXT NOT NULL PRIMARY KEY,
+  job_name      TEXT NOT NULL,
+  specification INTEGER NOT NULL,
+  FOREIGN KEY (specification) REFERENCES Specifications (id)
 );
 
-create table build (
-  id              integer primary key autoincrement not null,
-  job_spec        text not null,
-  drv             text not null,
-  log             text,
-  output          text
-  -- foreign key (job_spec) references job_spec(name)
+CREATE TABLE Builds (
+  id              INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  derivation      TEXT NOT NULL,
+  log             TEXT NOT NULL,
+  output          TEXT,         -- NULL if build failed
+  FOREIGN KEY (derivation) REFERENCES Evaluations (derivation)
 );
+
+COMMIT;