1 files changed, 49 insertions, 0 deletions
diff --git a/src/sql/upgrade-2.sql b/src/sql/upgrade-2.sql
new file mode 100644
index 0000000..dfb919b
--- /dev/null
+++ b/src/sql/upgrade-2.sql
@@ -0,0 +1,49 @@
+BEGIN TRANSACTION;
+
+DROP INDEX Derivations_index;
+DROP INDEX Builds_Derivations_index;
+
+ALTER TABLE Outputs RENAME TO tmp_Outputs;
+ALTER TABLE Builds RENAME TO tmp_Builds;
+
+CREATE TABLE Builds (
+  derivation    TEXT NOT NULL PRIMARY KEY,
+  evaluation    INTEGER NOT NULL,
+  job_name      TEXT NOT NULL,
+  system        TEXT NOT NULL,
+  nix_name      TEXT NOT NULL,
+  log           TEXT NOT NULL,
+  status        INTEGER NOT NULL,
+  timestamp     INTEGER NOT NULL,
+  starttime     INTEGER NOT NULL,
+  stoptime      INTEGER NOT NULL,
+  FOREIGN KEY (evaluation) REFERENCES Evaluations (id)
+);
+
+CREATE TABLE Outputs (
+  derivation TEXT NOT NULL,
+  name TEXT NOT NULL,
+  path TEXT NOT NULL,
+  PRIMARY KEY (derivation, name),
+  FOREIGN KEY (derivation) REFERENCES Builds (derivation)
+);
+
+INSERT OR IGNORE INTO Builds (derivation, evaluation, job_name, system, nix_name, log, status, timestamp, starttime, stoptime)
+SELECT Derivations.derivation, Derivations.evaluation, Derivations.job_name, Derivations.system, Derivations.nix_name,
+       tmp_Builds.log, tmp_Builds.status, tmp_Builds.timestamp, tmp_Builds.starttime, tmp_Builds.stoptime
+FROM Derivations
+INNER JOIN tmp_Builds ON tmp_Builds.derivation = Derivations.derivation
+                     AND tmp_Builds.evaluation = Derivations.evaluation;
+
+INSERT OR IGNORE INTO Outputs (derivation, name, path)
+SELECT tmp_Builds.derivation, tmp_Outputs.name, tmp_Outputs.path
+FROM tmp_Outputs
+INNER JOIN tmp_Builds on tmp_Builds.id = tmp_Outputs.build;
+
+CREATE INDEX Builds_index ON Builds(job_name, system, status ASC, timestamp ASC, derivation, evaluation, stoptime DESC);
+
+DROP TABLE tmp_Builds;
+DROP TABLE tmp_Outputs;
+DROP TABLE Derivations;
+
+COMMIT;