From ce4c3c6ed3979e54a8d5db6514bf4ed87de8b707 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Sun, 5 May 2019 13:35:48 +0100 Subject: Switch to storing Git repositories in a table Rather than just storing the URL in the guix_revisions and load_new_guix_revision_jobs tables. This will help when storing more information like tags and branches in the future. --- sqitch/deploy/git_repositories.sql | 41 ++++++++++++++++++++++++++++++++++++++ sqitch/revert/git_repositories.sql | 27 +++++++++++++++++++++++++ sqitch/sqitch.plan | 1 + sqitch/verify/git_repositories.sql | 8 ++++++++ 4 files changed, 77 insertions(+) create mode 100644 sqitch/deploy/git_repositories.sql create mode 100644 sqitch/revert/git_repositories.sql create mode 100644 sqitch/verify/git_repositories.sql (limited to 'sqitch') diff --git a/sqitch/deploy/git_repositories.sql b/sqitch/deploy/git_repositories.sql new file mode 100644 index 0000000..e61c25b --- /dev/null +++ b/sqitch/deploy/git_repositories.sql @@ -0,0 +1,41 @@ +-- Deploy guix-data-service:git_repositories to pg +-- requires: initial_import + +BEGIN; + +CREATE TABLE git_repositories ( + id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + label character varying, + url character varying NOT NULL UNIQUE +); + +INSERT INTO git_repositories (url) +SELECT DISTINCT url FROM guix_revisions; + +-- Change the guix_revisions table + +ALTER TABLE guix_revisions ADD COLUMN git_repository_id integer +REFERENCES git_repositories (id); + +UPDATE guix_revisions SET git_repository_id = ( + SELECT id FROM git_repositories WHERE guix_revisions.url = git_repositories.url +); + +ALTER TABLE guix_revisions ALTER COLUMN git_repository_id SET NOT NULL; + +ALTER TABLE guix_revisions DROP COLUMN url; + +-- Change the load_new_guix_revision_jobs table + +ALTER TABLE load_new_guix_revision_jobs ADD COLUMN git_repository_id integer +REFERENCES git_repositories (id); + +UPDATE load_new_guix_revision_jobs SET git_repository_id = ( + SELECT id FROM git_repositories WHERE load_new_guix_revision_jobs.url = git_repositories.url +); + +ALTER TABLE load_new_guix_revision_jobs ALTER COLUMN git_repository_id SET NOT NULL; + +ALTER TABLE load_new_guix_revision_jobs DROP COLUMN url; + +COMMIT; diff --git a/sqitch/revert/git_repositories.sql b/sqitch/revert/git_repositories.sql new file mode 100644 index 0000000..25fca13 --- /dev/null +++ b/sqitch/revert/git_repositories.sql @@ -0,0 +1,27 @@ +-- Revert guix-data-service:git_repositories from pg + +BEGIN; + +ALTER TABLE guix_revisions ADD COLUMN url character varying; + +UPDATE guix_revisions SET url = ( + SELECT url FROM git_repositories WHERE guix_revisions.git_repository_id = git_repositories.id +); + +ALTER TABLE guix_revisions ALTER COLUMN url SET NOT NULL; + +ALTER TABLE guix_revisions DROP COLUMN git_repository_id; + +ALTER TABLE load_new_guix_revision_jobs ADD COLUMN url character varying; + +UPDATE load_new_guix_revision_jobs SET url = ( + SELECT url FROM git_repositories WHERE load_new_guix_revision_jobs.git_repository_id = git_repositories.id +); + +ALTER TABLE load_new_guix_revision_jobs ALTER COLUMN url SET NOT NULL; + +ALTER TABLE load_new_guix_revision_jobs DROP COLUMN git_repository_id; + +DROP TABLE git_repositories; + +COMMIT; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index 61fd9a9..97c152b 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -5,3 +5,4 @@ appschema 2019-04-13T11:43:59Z Christopher Baines # Add schema for the Guix Data Service buildstatus_enum [appschema] 2019-04-13T11:56:37Z Christopher Baines # Creates the buildstatus enum initial_import 2019-04-13T13:06:28Z Christopher Baines # Import the manually managed database schema +git_repositories 2019-05-04T19:03:38Z Christopher Baines # Add a git_repositories table diff --git a/sqitch/verify/git_repositories.sql b/sqitch/verify/git_repositories.sql new file mode 100644 index 0000000..b01ef94 --- /dev/null +++ b/sqitch/verify/git_repositories.sql @@ -0,0 +1,8 @@ +-- Verify guix-data-service:git_repositories on pg + +BEGIN; + +SELECT id, label, url + FROM git_repositories WHERE FALSE; + +ROLLBACK; -- cgit v1.2.3