Create a table for systems

And use it for the systems in the derivations and package derivations tables.

The primary motivation here is to allow quickly working out what systems the
database contains, and having a small table with just the right data seems a
good way to do that.

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;