1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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;
|