[PATCH 1/2] database: Don't return rowid from db-add-input.

[PATCH 1/2] database: Don't return rowid from db-add-input.

[Christopher Baines]

As it is unused from where db-add-input is called.

* src/cuirass/database.scm (db-add-input): Don't call and
return (last-insert-rowid).
---
 src/cuirass/database.scm | 3 +--
 1 file changed, 1 insertion(+), 2 deletions(-)

diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index 308b5c3..1643916 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -303,8 +303,7 @@ tag, revision, no_compile_p) VALUES ("
                  (assq-ref input #:branch) ", "
                  (assq-ref input #:tag) ", "
                  (assq-ref input #:commit) ", "
-                 (if (assq-ref input #:no-compile?) 1 0) ");")
-    (last-insert-rowid db)))
+                 (if (assq-ref input #:no-compile?) 1 0) ");")))
 
 (define (db-add-checkout spec-name eval-id checkout)
   "Insert CHECKOUT associated with SPEC-NAME and EVAL-ID.  If a checkout with
-- 
2.24.1

[PATCH 2/2] Alter the Builds table to have an id field

[Christopher Baines]

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/sql/upgrade-6.sql: New file.
* Makefile.am (dist_sql_DATA): Add it.
---
 Makefile.am           |  3 ++-
 src/schema.sql        |  3 ++-
 src/sql/upgrade-6.sql | 47 +++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 51 insertions(+), 2 deletions(-)
 create mode 100644 src/sql/upgrade-6.sql

diff --git a/Makefile.am b/Makefile.am
index 5448420..bc0e90c 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -70,7 +70,8 @@ dist_sql_DATA = 				\
   src/sql/upgrade-2.sql				\
   src/sql/upgrade-3.sql				\
   src/sql/upgrade-4.sql				\
-  src/sql/upgrade-5.sql
+  src/sql/upgrade-5.sql				\
+  src/sql/upgrade-6.sql
 
 dist_css_DATA =					\
   src/static/css/bootstrap.css			\
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;
-- 
2.24.1

Re: [PATCH 2/2] Alter the Builds table to have an id field

[Ludovic Courtès]

Christopher Baines <mail@cbaines.net> skribis:

> 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/sql/upgrade-6.sql: New file.
> * Makefile.am (dist_sql_DATA): Add it.

This and the previous one LGTM!  :-)

Ludo'.

Re: [PATCH 2/2] Alter the Builds table to have an id field

[Christopher Baines]

[-- Attachment #1: Type: text/plain, Size: 954 bytes --]


Ludovic Courtès <ludo@gnu.org> writes:

> Christopher Baines <mail@cbaines.net> skribis:
>
>> 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/sql/upgrade-6.sql: New file.
>> * Makefile.am (dist_sql_DATA): Add it.
>
> This and the previous one LGTM!  :-)

So I went to push this, but luckily I ran the tests before pushing, and
there was one thing I had missed. The db-add-build function depended on
a primary key constraint exception for duplicate builds, but now it's a
unique exception.

I've got the tests passing now, and pushed this to master as
fa412cdb5985ec4199f89510d8d8cde9b7664e2d.

Thanks,

Chris

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 962 bytes --]