Introduce a systems table

7 files changed, 97 insertions, 0 deletions

diff --git a/sqitch/pg/deploy/create_systems.sql b/sqitch/pg/deploy/create_systems.sql
new file mode 100644
index 0000000..2b34f76
--- /dev/null
+++ b/sqitch/pg/deploy/create_systems.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-build-coordinator:create_systems to pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/revert/create_systems.sql b/sqitch/pg/revert/create_systems.sql
new file mode 100644
index 0000000..45ae7a3
--- /dev/null
+++ b/sqitch/pg/revert/create_systems.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:create_systems from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/verify/create_systems.sql b/sqitch/pg/verify/create_systems.sql
new file mode 100644
index 0000000..797a5c5
--- /dev/null
+++ b/sqitch/pg/verify/create_systems.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:create_systems on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index a7fed94..198b4de 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -33,3 +33,4 @@ fix_allocated_builds 2021-03-30T08:19:18Z Christopher Baines <mail@cbaines.net>
 fix_allocated_builds_attempt_2 2021-03-30T08:27:18Z Christopher Baines <mail@cbaines.net> # Fix allocated_builds
 fix_setup_failure_missing_inputs 2021-04-08T19:31:49Z Christopher Baines <mail@cbaines.net> # Fix setup_failure_missing_inputs
 create_outputs 2021-05-21T13:37:49Z Christopher Baines <mail@cbaines.net> # Create the outputs table
+create_systems 2021-05-21T17:22:52Z Christopher Baines <mail@cbaines.net> # Create the systems table
diff --git a/sqitch/sqlite/deploy/create_systems.sql b/sqitch/sqlite/deploy/create_systems.sql
new file mode 100644
index 0000000..a4dd941
--- /dev/null
+++ b/sqitch/sqlite/deploy/create_systems.sql
@@ -0,0 +1,61 @@
+-- Deploy guix-build-coordinator:create_systems to sqlite
+
+PRAGMA foreign_keys = OFF;
+
+BEGIN;
+
+CREATE TABLE systems (
+  id INTEGER PRIMARY KEY,
+  system TEXT NOT NULL
+);
+
+INSERT INTO systems (system)
+  SELECT system FROM derivations
+  UNION
+  SELECT system FROM build_allocation_agent_requested_systems;
+
+
+
+CREATE TABLE derivations_new (
+       id INTEGER PRIMARY KEY ASC,
+       name TEXT NOT NULL,
+       system_id INTEGER NOT NULL REFERENCES systems (id),
+       fixed_output BOOLEAN
+       CHECK (fixed_output IN (0,1))
+);
+
+INSERT INTO derivations_new
+  SELECT derivations.id, name, systems.id, fixed_output
+  FROM derivations
+  INNER JOIN systems ON derivations.system = systems.system;
+
+DROP TABLE derivations;
+ALTER TABLE derivations_new RENAME TO derivations;
+
+CREATE UNIQUE INDEX derivations_name_idx ON derivations (name);
+
+
+
+CREATE TABLE build_allocation_agent_requested_systems_new (
+       agent_id INTEGER NOT NULL REFERENCES agents (id),
+       system_id INTEGER NOT NULL,
+       PRIMARY KEY (agent_id, system_id)
+);
+
+INSERT INTO build_allocation_agent_requested_systems_new
+  SELECT agent_id, systems.id
+  FROM build_allocation_agent_requested_systems
+  INNER JOIN systems
+    ON build_allocation_agent_requested_systems.system = systems.system;
+
+DROP TABLE build_allocation_agent_requested_systems;
+ALTER TABLE build_allocation_agent_requested_systems_new
+  RENAME TO build_allocation_agent_requested_systems;
+
+
+
+PRAGMA foreign_key_check;
+
+COMMIT;
+
+PRAGMA foreign_keys = ON;
diff --git a/sqitch/sqlite/revert/create_systems.sql b/sqitch/sqlite/revert/create_systems.sql
new file mode 100644
index 0000000..8208b52
--- /dev/null
+++ b/sqitch/sqlite/revert/create_systems.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:create_systems from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/create_systems.sql b/sqitch/sqlite/verify/create_systems.sql
new file mode 100644
index 0000000..b70d7a5
--- /dev/null
+++ b/sqitch/sqlite/verify/create_systems.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:create_systems on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;