Use numeric ids for the derivations and builds

Using natural IDs was nice at the start, but just doesn't scale. This
migration cuts the database size, and potentially speeds up queries as well.

7 files changed, 255 insertions, 0 deletions

diff --git a/sqitch/pg/deploy/use_numeric_primary_keys.sql b/sqitch/pg/deploy/use_numeric_primary_keys.sql
new file mode 100644
index 0000000..e659bb0
--- /dev/null
+++ b/sqitch/pg/deploy/use_numeric_primary_keys.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-build-coordinator:use_numeric_primary_keys to pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/revert/use_numeric_primary_keys.sql b/sqitch/pg/revert/use_numeric_primary_keys.sql
new file mode 100644
index 0000000..df77446
--- /dev/null
+++ b/sqitch/pg/revert/use_numeric_primary_keys.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:use_numeric_primary_keys from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/verify/use_numeric_primary_keys.sql b/sqitch/pg/verify/use_numeric_primary_keys.sql
new file mode 100644
index 0000000..8a5674d
--- /dev/null
+++ b/sqitch/pg/verify/use_numeric_primary_keys.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:use_numeric_primary_keys on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 19e2b11..c373470 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -28,3 +28,4 @@ add_builds_deferred_until 2020-12-26T19:26:32Z Christopher Baines <mail@cbaines.
 add_agent_tags 2021-01-17T11:13:23Z Christopher Baines <mail@cbaines.net> # Add tags for agents
 add_agent_names 2021-02-28T14:04:24Z Christopher Baines <mail@cbaines.net> # Add agent.names
 add_dynamic_auth_tokens 2021-02-28T15:21:24Z Christopher Baines <mail@cbaines.net> # Add dynamic_auth_tokens
+use_numeric_primary_keys 2021-03-28T09:55:40Z Christopher Baines <mail@cbaines.net> # Use numeric primary keys
diff --git a/sqitch/sqlite/deploy/use_numeric_primary_keys.sql b/sqitch/sqlite/deploy/use_numeric_primary_keys.sql
new file mode 100644
index 0000000..7d5fda7
--- /dev/null
+++ b/sqitch/sqlite/deploy/use_numeric_primary_keys.sql
@@ -0,0 +1,219 @@
+-- Deploy guix-build-coordinator:use_numeric_primary_keys to sqlite
+
+BEGIN;
+
+ALTER TABLE derivations RENAME TO old_derivations;
+
+CREATE TABLE derivations (
+       id INTEGER PRIMARY KEY ASC,
+       name TEXT NOT NULL,
+       system TEXT NOT NULL,
+       fixed_output BOOLEAN
+       CHECK (fixed_output IN (0,1))
+);
+
+INSERT INTO derivations (name, system, fixed_output)
+  SELECT name, system, fixed_output FROM old_derivations;
+
+DROP TABLE old_derivations;
+
+CREATE UNIQUE INDEX derivations_name_idx ON derivations (name);
+
+-- derivation_outputs
+
+ALTER TABLE derivation_outputs RENAME TO old_derivation_outputs;
+
+CREATE TABLE derivation_outputs (
+       id INTEGER PRIMARY KEY ASC,
+       derivation_id INTEGER NOT NULL REFERENCES derivations (id),
+       name TEXT NOT NULL,
+       output TEXT NOT NULL
+);
+
+INSERT INTO derivation_outputs (id, derivation_id, name, output)
+  SELECT old_derivation_outputs.id,
+         derivations.id,
+         old_derivation_outputs.name,
+         old_derivation_outputs.output
+  FROM old_derivation_outputs
+  INNER JOIN derivations
+    ON derivations.name = old_derivation_outputs.derivation_name;
+
+DROP TABLE old_derivation_outputs;
+
+CREATE INDEX derivation_outputs_output_idx
+  ON derivation_outputs (output);
+CREATE INDEX derivation_outputs_unique_idx
+  ON derivation_outputs (derivation_id, name);
+
+-- derivation_inputs
+
+ALTER TABLE derivation_inputs RENAME TO old_derivation_inputs;
+
+CREATE TABLE derivation_inputs (
+       derivation_id INTEGER REFERENCES derivations (id),
+       derivation_output_id INTEGER REFERENCES derivation_outputs (id),
+       PRIMARY KEY (derivation_id, derivation_output_id)
+);
+
+INSERT INTO derivation_inputs (derivation_id, derivation_output_id)
+  SELECT derivations.id, derivation_output_id
+  FROM old_derivation_inputs
+  INNER JOIN derivations
+    ON derivations.name = old_derivation_inputs.derivation_name;
+
+DROP TABLE old_derivation_inputs;
+
+CREATE INDEX derivation_inputs_derivation_output_id
+  ON derivation_inputs (derivation_output_id);
+
+-- builds
+
+ALTER TABLE builds RENAME TO old_builds;
+
+CREATE TABLE builds (
+       id INTEGER PRIMARY KEY ASC,
+       uuid TEXT NOT NULL,
+       derivation_id NUMBER NOT NULL REFERENCES derivations (id),
+       priority INTEGER NOT NULL,
+       processed BOOLEAN NOT NULL DEFAULT 0,
+       created_at TEXT,
+       end_time TEXT,
+       canceled BOOLEAN NOT NULL DEFAULT 0,
+       deferred_until TEXT
+);
+
+INSERT INTO builds (uuid, derivation_id, priority, processed, created_at, end_time, canceled, deferred_until)
+  SELECT uuid, derivations.id, priority, processed, created_at, end_time, canceled, deferred_until
+  FROM old_builds
+  INNER JOIN derivations ON derivations.name = old_builds.derivation_name;
+
+DROP TABLE old_builds;
+
+CREATE UNIQUE INDEX builds_uuid ON builds (uuid);
+CREATE INDEX builds_derivation_id_idx ON builds (derivation_id);
+CREATE INDEX builds_unprocessed ON builds (processed) WHERE processed = 0;
+
+-- allocated_builds
+
+ALTER TABLE allocated_builds RENAME TO old_allocated_builds;
+
+CREATE TABLE allocated_builds (
+       build_id PRIMARY KEY NOT NULL REFERENCES builds (id),
+       agent_id NOT NULL REFERENCES agents (id)
+);
+
+INSERT INTO allocated_builds (build_id, agent_id)
+  SELECT builds.id, agent_id
+  FROM old_allocated_builds
+  INNER JOIN builds
+    ON builds.uuid = old_allocated_builds.build_id;
+
+DROP TABLE old_allocated_builds;
+
+-- build_allocation_plan
+
+DROP TABLE build_allocation_plan;
+
+CREATE TABLE build_allocation_plan (
+       build_id NOT NULL REFERENCES builds (id),
+       agent_id NOT NULL REFERENCES agents (id),
+       ordering INTEGER NOT NULL,
+       PRIMARY KEY (agent_id, build_id)
+);
+
+-- build_results
+
+ALTER TABLE build_results RENAME TO old_build_results;
+
+CREATE TABLE build_results (
+       build_id INTEGER PRIMARY KEY ASC REFERENCES builds (id),
+       agent_id TEXT NOT NULL REFERENCES agents (id),
+       result TEXT NOT NULL,
+       failure_reason
+);
+
+INSERT INTO build_results (build_id, agent_id, result, failure_reason)
+  SELECT builds.id, agent_id, result, failure_reason
+  FROM old_build_results
+  INNER JOIN builds ON builds.uuid = old_build_results.build_id;
+
+DROP TABLE old_build_results;
+
+CREATE INDEX build_results_result_idx ON build_results (result);
+
+-- setup_failures
+
+ALTER TABLE setup_failures RENAME TO old_setup_failures;
+
+CREATE TABLE setup_failures (
+       id INTEGER PRIMARY KEY ASC,
+       build_id INTEGER NOT NULL REFERENCES builds (id),
+       agent_id TEXT NOT NULL REFERENCES agents (id),
+       failure_reason TEXT NOT NULL
+);
+
+INSERT INTO setup_failures (id, build_id, agent_id, failure_reason)
+  SELECT old_setup_failures.id, builds.id, old_setup_failures.agent_id, old_setup_failures.failure_reason
+  FROM old_setup_failures
+  INNER JOIN builds ON builds.uuid = old_setup_failures.build_id;
+
+DROP TABLE old_setup_failures;
+
+-- output_metadata
+
+ALTER TABLE output_metadata RENAME TO old_output_metadata;
+
+CREATE TABLE output_metadata (
+       build_id INTEGER NOT NULL REFERENCES builds (id),
+       derivation_output_id INTEGER NOT NULL REFERENCES derivation_outputs (id),
+       hash TEXT NOT NULL,
+       size INTEGER NOT NULL,
+       store_references TEXT NOT NULL
+);
+
+INSERT INTO output_metadata
+  (build_id, derivation_output_id, hash, size, store_references)
+  SELECT builds.id, old_output_metadata.derivation_output_id, old_output_metadata.hash, old_output_metadata.size, old_output_metadata.store_references
+  FROM old_output_metadata
+  INNER JOIN builds ON builds.uuid = old_output_metadata.build_id;
+
+DROP TABLE old_output_metadata;
+
+-- build_tags
+
+ALTER TABLE build_tags RENAME TO old_build_tags;
+
+CREATE TABLE build_tags (
+       build_id INTEGER NOT NULL REFERENCES builds (id),
+       tag_id INTEGER NOT NULL REFERENCES tags (id)
+);
+
+INSERT INTO build_tags (build_id, tag_id)
+  SELECT builds.id, tag_id
+  FROM old_build_tags
+  INNER JOIN builds ON builds.uuid = old_build_tags.build_id;
+
+DROP TABLE old_build_tags;
+
+CREATE INDEX build_tags_build_id_idx ON build_tags (build_id);
+
+-- build_starts
+
+ALTER TABLE build_starts RENAME TO old_build_starts;
+
+CREATE TABLE build_starts (
+       id INTEGER PRIMARY KEY ASC,
+       build_id INTEGER NOT NULL REFERENCES builds (id),
+       agent_id TEXT NOT NULL REFERENCES agents (id),
+       start_time TEXT NOT NULL
+);
+
+INSERT INTO build_starts (id, build_id, agent_id, start_time)
+  SELECT old_build_starts.id, builds.id, old_build_starts.agent_id, old_build_starts.start_time
+  FROM old_build_starts
+  INNER JOIN builds ON builds.uuid = old_build_starts.build_id;
+
+DROP TABLE old_build_starts;
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/use_numeric_primary_keys.sql b/sqitch/sqlite/revert/use_numeric_primary_keys.sql
new file mode 100644
index 0000000..fdb22de
--- /dev/null
+++ b/sqitch/sqlite/revert/use_numeric_primary_keys.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:use_numeric_primary_keys from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/use_numeric_primary_keys.sql b/sqitch/sqlite/verify/use_numeric_primary_keys.sql
new file mode 100644
index 0000000..86818aa
--- /dev/null
+++ b/sqitch/sqlite/verify/use_numeric_primary_keys.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:use_numeric_primary_keys on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;