diff options
author | Danjela Lura <danielaluraa@gmail.com> | 2020-07-31 16:35:28 +0200 |
---|---|---|
committer | Christopher Baines <mail@cbaines.net> | 2020-08-12 08:53:35 +0100 |
commit | ee613cdb305cc1e443135d311aead6c799902b8a (patch) | |
tree | 3fb6465ac4ae7ba98450652e69a225dad6509759 /sqitch/deploy/add-tsvectors-per-locale.sql | |
parent | a6a4a84b207c364d0c0e419039a6e4225a5ba196 (diff) | |
download | data-service-ee613cdb305cc1e443135d311aead6c799902b8a.tar data-service-ee613cdb305cc1e443135d311aead6c799902b8a.tar.gz |
Add the package_metadata_tsvectors table
Signed-off-by: Christopher Baines <mail@cbaines.net>
Diffstat (limited to 'sqitch/deploy/add-tsvectors-per-locale.sql')
-rw-r--r-- | sqitch/deploy/add-tsvectors-per-locale.sql | 71 |
1 files changed, 71 insertions, 0 deletions
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; |