aboutsummaryrefslogtreecommitdiff
path: root/sqitch/deploy/add-tsvectors-per-locale.sql
diff options
context:
space:
mode:
authorDanjela Lura <danielaluraa@gmail.com>2020-07-31 16:35:28 +0200
committerChristopher Baines <mail@cbaines.net>2020-08-12 08:53:35 +0100
commitee613cdb305cc1e443135d311aead6c799902b8a (patch)
tree3fb6465ac4ae7ba98450652e69a225dad6509759 /sqitch/deploy/add-tsvectors-per-locale.sql
parenta6a4a84b207c364d0c0e419039a6e4225a5ba196 (diff)
downloaddata-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.sql71
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;