4 files changed, 53 insertions, 0 deletions
diff --git a/sqitch/deploy/systems_table.sql b/sqitch/deploy/systems_table.sql
new file mode 100644
index 0000000..f7259f0
--- /dev/null
+++ b/sqitch/deploy/systems_table.sql
@@ -0,0 +1,38 @@
+-- Deploy guix-data-service:systems_table to pg
+
+BEGIN;
+
+CREATE TABLE systems (
+  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+  system character varying UNIQUE NOT NULL
+);
+
+INSERT INTO systems (system) SELECT DISTINCT system FROM derivations;
+
+ALTER TABLE derivations
+  ADD COLUMN system_id integer REFERENCES systems (id);
+
+UPDATE derivations
+  SET system_id = (
+    SELECT id FROM systems WHERE systems.system = derivations.system
+  );
+
+ALTER TABLE derivations
+  ALTER COLUMN system_id SET NOT NULL;
+
+ALTER TABLE derivations DROP COLUMN system;
+
+ALTER TABLE package_derivations
+  ADD COLUMN system_id integer REFERENCES systems (id);
+
+UPDATE package_derivations
+  SET system_id = (
+    SELECT id FROM systems WHERE systems.system = package_derivations.system
+  );
+
+ALTER TABLE package_derivations
+  ALTER COLUMN system_id SET NOT NULL;
+
+ALTER TABLE package_derivations DROP COLUMN system;
+
+COMMIT;
diff --git a/sqitch/revert/systems_table.sql b/sqitch/revert/systems_table.sql
new file mode 100644
index 0000000..63fb42a
--- /dev/null
+++ b/sqitch/revert/systems_table.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:systems_table from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index b5af91d..76d574b 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -78,3 +78,4 @@ regenerate_latest_build_status 2020-10-21T18:39:03Z Christopher Baines <mail@cba
 guix_revision_package_derivations_add_package_derivation_index 2020-10-27T16:58:08Z Christopher Baines <mail@cbaines.net> # Add index for guix_revision_package_derivations.package_derivation_id
 increase_derivation_inputs_statistics_targets 2020-12-27T10:34:58Z Christopher Baines <mail@cbaines.net> # Increase stats targets on derivation_inputs fields
 remove_guix_revisions_store_path 2021-02-02T20:06:18Z Christopher Baines <mail@cbaines.net> # Drop guix_revisions.store_path
+systems_table 2021-04-22T08:12:10Z Christopher Baines <mail@cbaines.net> # Add a systems table
diff --git a/sqitch/verify/systems_table.sql b/sqitch/verify/systems_table.sql
new file mode 100644
index 0000000..c734717
--- /dev/null
+++ b/sqitch/verify/systems_table.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:systems_table on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;