Get the translated package synopsis and descriptions into the database

Signed-off-by: Christopher Baines <mail@cbaines.net>

4 files changed, 84 insertions, 0 deletions

diff --git a/sqitch/deploy/translations_for_package_synopsis_and_descriptions.sql b/sqitch/deploy/translations_for_package_synopsis_and_descriptions.sql
new file mode 100644
index 0000000..627ea93
--- /dev/null
+++ b/sqitch/deploy/translations_for_package_synopsis_and_descriptions.sql
@@ -0,0 +1,69 @@
+-- Deploy guix-data-service:translations_for_package_synopsis_and_descriptions to pg
+
+BEGIN;
+
+CREATE TABLE package_descriptions (
+  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+  locale varchar NOT NULL,
+  description varchar NOT NULL,
+  UNIQUE (locale, description)
+);
+
+CREATE TABLE package_description_sets (
+  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+  description_ids integer[] NOT NULL,
+  UNIQUE (description_ids)
+);
+
+CREATE TABLE package_synopsis (
+  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+  locale varchar NOT NULL,
+  synopsis varchar NOT NULL,
+  UNIQUE (locale, synopsis)
+);
+
+CREATE TABLE package_synopsis_sets (
+  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+  synopsis_ids integer[] NOT NULL,
+  UNIQUE (synopsis_ids)
+);
+
+ALTER TABLE package_metadata ADD COLUMN package_description_set_id integer REFERENCES package_description_sets (id);
+
+ALTER TABLE package_metadata ADD COLUMN package_synopsis_set_id integer REFERENCES package_synopsis_sets (id);
+
+INSERT INTO package_descriptions (locale, description)
+SELECT DISTINCT 'en_US.utf8', description
+  FROM package_metadata;
+
+INSERT INTO package_description_sets (description_ids)
+SELECT ARRAY[id] FROM package_descriptions;
+
+INSERT INTO package_synopsis (locale, synopsis)
+SELECT DISTINCT 'en_US.utf8', synopsis
+  FROM package_metadata;
+
+INSERT INTO package_synopsis_sets (synopsis_ids)
+SELECT ARRAY[id] FROM package_synopsis;
+
+UPDATE package_metadata
+   SET package_description_set_id =
+       package_description_sets.id
+       FROM package_description_sets
+       INNER JOIN package_descriptions
+       ON package_description_sets.description_ids[1] = package_descriptions.id
+       WHERE package_descriptions.description = package_metadata.description;
+
+UPDATE package_metadata
+   SET package_synopsis_set_id =
+       package_synopsis_sets.id
+       FROM package_synopsis_sets
+       INNER JOIN package_synopsis
+       ON package_synopsis_sets.synopsis_ids[1] = package_synopsis.id
+       WHERE package_synopsis.synopsis = package_metadata.synopsis;
+
+ALTER TABLE package_metadata DROP COLUMN description;
+
+ALTER TABLE package_metadata DROP COLUMN synopsis;
+
+COMMIT;
diff --git a/sqitch/revert/translations_for_package_synopsis_and_descriptions.sql b/sqitch/revert/translations_for_package_synopsis_and_descriptions.sql
new file mode 100644
index 0000000..00a6fc3
--- /dev/null
+++ b/sqitch/revert/translations_for_package_synopsis_and_descriptions.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:translations_for_package_synopsis_and_descriptions from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 911c43b..dc0d147 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -60,3 +60,4 @@ remove_odd_package_derivations 2020-04-24T20:36:06Z Christopher Baines <mail@cba
 build_servers_lookup_builds 2020-05-24T15:18:09Z Christopher Baines <mail@cbaines.net> # Add build_servers.lookup_builds
 make_nar_urls_file_size_optional 2020-06-03T05:27:29Z Christopher Baines <mail@cbaines.net> # Make the nar_urls.file_size optional
 translations_for_lint_checker_descriptions 2020-05-22T19:49:37Z daniela <daniela@linux-ijv5> # Support translations for lint checker descriptions
+translations_for_package_synopsis_and_descriptions 2020-06-09T12:42:54Z daniela <daniela@linux-ijv5> # Support translations for package synopsis and descriptions
diff --git a/sqitch/verify/translations_for_package_synopsis_and_descriptions.sql b/sqitch/verify/translations_for_package_synopsis_and_descriptions.sql
new file mode 100644
index 0000000..a93c379
--- /dev/null
+++ b/sqitch/verify/translations_for_package_synopsis_and_descriptions.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:translations_for_package_synopsis_and_descriptions on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;