From fa412cdb5985ec4199f89510d8d8cde9b7664e2d Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Fri, 24 Jan 2020 19:47:43 +0000 Subject: Alter the Builds table to have an id field The internal rowid's are used for builds as you can request builds by using the rowid in the URL. The motivation here is to enable running VACUUM operations in SQLite, without risking the rowid's for Builds changing. It would be bad if they change, as they're used in the URL's for builds. * src/schema.sql (Builds): Add id column. * src/curiass/dataabse.scm (db-add-build): Change PRIMARYKEY constraint to UNIQUE constraint. * src/sql/upgrade-6.sql: New file. * Makefile.am (dist_sql_DATA): Add it. --- src/cuirass/database.scm | 2 +- src/schema.sql | 3 ++- src/sql/upgrade-6.sql | 47 +++++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 50 insertions(+), 2 deletions(-) create mode 100644 src/sql/upgrade-6.sql (limited to 'src') diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm index 1643916..14cbbda 100644 --- a/src/cuirass/database.scm +++ b/src/cuirass/database.scm @@ -540,7 +540,7 @@ VALUES (" ;; If we get a unique-constraint-failed error, that means we have ;; already inserted the same build. That happens when several jobs ;; produce the same derivation, and we can ignore it. - (on SQLITE_CONSTRAINT_PRIMARYKEY + (on SQLITE_CONSTRAINT_UNIQUE => (sqlite-exec db "ROLLBACK;") #f)))) diff --git a/src/schema.sql b/src/schema.sql index cd67530..1104551 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -51,7 +51,8 @@ CREATE TABLE Outputs ( ); CREATE TABLE Builds ( - derivation TEXT NOT NULL PRIMARY KEY, + id INTEGER NOT NULL PRIMARY KEY, + derivation TEXT NOT NULL UNIQUE, evaluation INTEGER NOT NULL, job_name TEXT NOT NULL, system TEXT NOT NULL, diff --git a/src/sql/upgrade-6.sql b/src/sql/upgrade-6.sql new file mode 100644 index 0000000..0b25aa5 --- /dev/null +++ b/src/sql/upgrade-6.sql @@ -0,0 +1,47 @@ +BEGIN TRANSACTION; + +ALTER TABLE Builds RENAME TO OldBuilds; + +CREATE TABLE Builds ( + id INTEGER NOT NULL PRIMARY KEY, + derivation TEXT NOT NULL UNIQUE, + 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) +); + +INSERT INTO Builds( + id, + derivation, + evaluation, + job_name, + system, + nix_name, + log, + status, + timestamp, + starttime, + stoptime +) SELECT rowid, + derivation, + evaluation, + job_name, + system, + nix_name, + log, + status, + timestamp, + starttime, + stoptime + FROM OldBuilds; + +DROP TABLE OldBuilds; + +COMMIT; -- cgit v1.2.3