From b430c632b75e5e90e36a855599e3f91302720d54 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Fri, 23 Apr 2021 11:14:51 +0100 Subject: 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. --- sqitch/deploy/systems_table.sql | 38 ++++++++++++++++++++++++++++++++++++++ sqitch/revert/systems_table.sql | 7 +++++++ sqitch/sqitch.plan | 1 + sqitch/verify/systems_table.sql | 7 +++++++ 4 files changed, 53 insertions(+) create mode 100644 sqitch/deploy/systems_table.sql create mode 100644 sqitch/revert/systems_table.sql create mode 100644 sqitch/verify/systems_table.sql (limited to 'sqitch') 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 # Add index for guix_revision_package_derivations.package_derivation_id increase_derivation_inputs_statistics_targets 2020-12-27T10:34:58Z Christopher Baines # Increase stats targets on derivation_inputs fields remove_guix_revisions_store_path 2021-02-02T20:06:18Z Christopher Baines # Drop guix_revisions.store_path +systems_table 2021-04-22T08:12:10Z Christopher Baines # 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; -- cgit v1.2.3