From ee613cdb305cc1e443135d311aead6c799902b8a Mon Sep 17 00:00:00 2001 From: Danjela Lura Date: Fri, 31 Jul 2020 16:35:28 +0200 Subject: Add the package_metadata_tsvectors table Signed-off-by: Christopher Baines --- sqitch/deploy/add-tsvectors-per-locale.sql | 71 ++++++++++++++++++++++++++++++ sqitch/revert/add-tsvectors-per-locale.sql | 7 +++ sqitch/sqitch.plan | 1 + sqitch/verify/add-tsvectors-per-locale.sql | 7 +++ 4 files changed, 86 insertions(+) create mode 100644 sqitch/deploy/add-tsvectors-per-locale.sql create mode 100644 sqitch/revert/add-tsvectors-per-locale.sql create mode 100644 sqitch/verify/add-tsvectors-per-locale.sql (limited to 'sqitch') diff --git a/sqitch/deploy/add-tsvectors-per-locale.sql b/sqitch/deploy/add-tsvectors-per-locale.sql new file mode 100644 index 0000000..963a7d1 --- /dev/null +++ b/sqitch/deploy/add-tsvectors-per-locale.sql @@ -0,0 +1,71 @@ +-- Deploy guix-data-service:add-tsvectors-per-locale to pg + +BEGIN; + +CREATE TABLE package_metadata_tsvectors( + package_metadata_id integer NOT NULL REFERENCES package_metadata(id), + locale varchar NOT NULL, + synopsis_and_description tsvector NOT NULL, + package_synopsis_id integer NOT NULL, + package_description_id integer NOT NULL, + PRIMARY KEY(locale, package_metadata_id) +); + + +INSERT INTO package_metadata_tsvectors (package_metadata_id, locale, synopsis_and_description, + package_synopsis_id, package_description_id) +SELECT DISTINCT ON (package_metadata.id, locale) + package_metadata.id, + CASE WHEN translated_package_synopsis.locale != 'en_US.utf8' + THEN translated_package_synopsis.locale + ELSE translated_package_descriptions.locale + END AS locale, + ( setweight(to_tsvector(translated_package_synopsis.synopsis), 'B') || + setweight(to_tsvector(translated_package_descriptions.description), 'C') + ), + translated_package_synopsis.id, + translated_package_descriptions.id +FROM package_metadata +INNER JOIN ( + SELECT package_description_sets.id AS package_description_set_id, + package_descriptions.id, package_descriptions.description, + package_descriptions.locale + FROM package_description_sets + INNER JOIN package_descriptions + ON package_descriptions.id = ANY (package_description_sets.description_ids) + ORDER BY package_description_sets.id, + CASE WHEN package_descriptions.locale = 'en_US.utf8' THEN 1 + ELSE 2 + END DESC +) AS translated_package_descriptions + ON package_metadata.package_description_set_id = + translated_package_descriptions.package_description_set_id +INNER JOIN ( + SELECT package_synopsis_sets.id AS package_synopsis_set_id, + package_synopsis.id, package_synopsis.synopsis, + package_synopsis.locale + FROM package_synopsis_sets + INNER JOIN package_synopsis + ON package_synopsis.id = ANY (package_synopsis_sets.synopsis_ids) + ORDER BY package_synopsis_sets.id, + CASE WHEN package_synopsis.locale = 'en_US.utf8' THEN 1 + ELSE 2 + END DESC +) AS translated_package_synopsis + ON package_metadata.package_synopsis_set_id = + translated_package_synopsis.package_synopsis_set_id + AND (translated_package_descriptions.locale = translated_package_synopsis.locale + OR translated_package_descriptions.locale = 'en_US.utf8') +ORDER BY package_metadata.id, locale, + CASE WHEN translated_package_synopsis.locale = + translated_package_descriptions.locale THEN 1 + ELSE 0 + END DESC; + +CREATE INDEX tsv_idx ON package_metadata_tsvectors USING gin(synopsis_and_description); + +CREATE INDEX package_metadata_id_packages_idx ON packages USING btree(package_metadata_id); + +CREATE INDEX package_metadata_id_package_metadata_tsvectors_idx ON package_metadata_tsvectors USING btree(package_metadata_id); + +COMMIT; diff --git a/sqitch/revert/add-tsvectors-per-locale.sql b/sqitch/revert/add-tsvectors-per-locale.sql new file mode 100644 index 0000000..3e542db --- /dev/null +++ b/sqitch/revert/add-tsvectors-per-locale.sql @@ -0,0 +1,7 @@ +-- Revert guix-data-service:add-tsvectors-per-locale from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index 8b165c1..5cfe31b 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -66,3 +66,4 @@ build_add_build_server_build_id 2020-06-27T12:48:57Z Christopher Baines # Add a unique index to the build_status table builds_remove_build_server_id_derivation_unique_constraint 2020-06-30T20:17:48Z Christopher Baines # Allow multiple builds of the same derivation per build server derivation_output_details_sets_derivation_output_details_ids_index 2020-07-04T07:56:49Z Christopher Baines # Add GIN index on derivation_output_details_ids +add-tsvectors-per-locale 2020-07-16T18:19:44Z daniela # Add tsvectors per locale diff --git a/sqitch/verify/add-tsvectors-per-locale.sql b/sqitch/verify/add-tsvectors-per-locale.sql new file mode 100644 index 0000000..cab810a --- /dev/null +++ b/sqitch/verify/add-tsvectors-per-locale.sql @@ -0,0 +1,7 @@ +-- Verify guix-data-service:add-tsvectors-per-locale on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- cgit v1.2.3