From 8d9a4d105c6bda93e75609ac09102a6953c28c9e Mon Sep 17 00:00:00 2001 From: Danjela Lura Date: Thu, 11 Jun 2020 19:04:01 +0200 Subject: Get the translated package synopsis and descriptions into the database Signed-off-by: Christopher Baines --- ...tions_for_package_synopsis_and_descriptions.sql | 69 ++++++++++++++++++++++ ...tions_for_package_synopsis_and_descriptions.sql | 7 +++ sqitch/sqitch.plan | 1 + ...tions_for_package_synopsis_and_descriptions.sql | 7 +++ 4 files changed, 84 insertions(+) create mode 100644 sqitch/deploy/translations_for_package_synopsis_and_descriptions.sql create mode 100644 sqitch/revert/translations_for_package_synopsis_and_descriptions.sql create mode 100644 sqitch/verify/translations_for_package_synopsis_and_descriptions.sql (limited to 'sqitch') 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 # Add build_servers.lookup_builds make_nar_urls_file_size_optional 2020-06-03T05:27:29Z Christopher Baines # Make the nar_urls.file_size optional translations_for_lint_checker_descriptions 2020-05-22T19:49:37Z daniela # Support translations for lint checker descriptions +translations_for_package_synopsis_and_descriptions 2020-06-09T12:42:54Z daniela # 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; -- cgit v1.2.3