1 files changed, 46 insertions, 0 deletions
diff --git a/src/sql/upgrade-3.sql b/src/sql/upgrade-3.sql
new file mode 100644
index 0000000..8e4a1bd
--- /dev/null
+++ b/src/sql/upgrade-3.sql
@@ -0,0 +1,46 @@
+BEGIN TRANSACTION;
+
+ALTER TABLE Evaluations RENAME TO tmp_Evaluations;
+
+CREATE TABLE Evaluations (
+  id            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  specification TEXT NOT NULL,
+  in_progress   INTEGER NOT NULL,
+  FOREIGN KEY (specification) REFERENCES Specifications (name)
+);
+
+CREATE TABLE Checkouts (
+  specification TEXT NOT NULL,
+  revision      TEXT NOT NULL,
+  evaluation    INTEGER NOT NULL,
+  input         TEXT NOT NULL,
+  directory     TEXT NOT NULL,
+  PRIMARY KEY (specification, revision),
+  FOREIGN KEY (evaluation) REFERENCES Evaluations (id),
+  FOREIGN KEY (specification) REFERENCES Specifications (name),
+  FOREIGN KEY (input) REFERENCES Inputs (name)
+);
+
+INSERT INTO Evaluations (id, specification, in_progress)
+SELECT id, specification, false
+FROM tmp_Evaluations;
+
+-- Copied from https://www.samuelbosch.com/2018/02/split-into-rows-sqlite.html.
+INSERT OR IGNORE INTO Checkouts (specification, revision, evaluation, input, directory)
+WITH RECURSIVE split(id, specification, revision, rest) AS (
+  SELECT id, specification, '', commits || ' ' FROM tmp_Evaluations
+   UNION ALL
+  SELECT id,
+         specification,
+         substr(rest, 0, instr(rest, ' ')),
+         substr(rest, instr(rest, ' ') + 1)
+    FROM split
+   WHERE rest <> '')
+SELECT specification, revision, id, 'unknown', 'unknown'
+  FROM split
+ WHERE revision <> '';
+
+DROP TABLE tmp_Evaluations;
+DROP TABLE Stamps;
+
+COMMIT;