01/04: Add the package_metadata_tsvectors table

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit ee613cdb305cc1e443135d311aead6c799902b8a
Author: Danjela Lura <danielaluraa@gmail.com>
AuthorDate: Fri Jul 31 16:35:28 2020 +0200

    Add the package_metadata_tsvectors table
    
    Signed-off-by: Christopher Baines <mail@cbaines.net>
---
 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(+)

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 <mail@cb
 build_status_add_unique_index 2020-06-30T17:19:30Z Christopher Baines 
<mail@cbaines.net> # Add a unique index to the build_status table
 builds_remove_build_server_id_derivation_unique_constraint 
2020-06-30T20:17:48Z Christopher Baines <mail@cbaines.net> # 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 <mail@cbaines.net> # Add GIN index on 
derivation_output_details_ids
+add-tsvectors-per-locale 2020-07-16T18:19:44Z daniela <daniela@linux-ijv5> # 
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;