Add the package_metadata_tsvectors table

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

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;