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.

4 files changed, 77 insertions, 0 deletions

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 <mail@cbaines.net> # Add schema for the Guix Data Service
 buildstatus_enum [appschema] 2019-04-13T11:56:37Z Christopher Baines <mail@cbaines.net> # Creates the buildstatus enum
 initial_import 2019-04-13T13:06:28Z Christopher Baines <mail@cbaines.net> # Import the manually managed database schema
+git_repositories 2019-05-04T19:03:38Z Christopher Baines <mail@cbaines.net> # 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;