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.

1 files changed, 38 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;