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;