· 8 years ago · Apr 22, 2018, 03:36 PM
1BEGIN;
2
3CREATE EXTENSION pg_trgm;
4CREATE EXTENSION unaccent;
5CREATE EXTENSION btree_gin;
6
7-- Column to cache tokenized alias names
8ALTER TABLE bookbrainz.alias
9 ADD COLUMN IF NOT EXISTS tokens TSVECTOR;
10
11
12-- Cache tokens of all previously added alias names
13UPDATE bookbrainz.alias
14 SET tokens = to_tsvector('pg_catalog.simple', unaccent(name));
15
16
17-- Function to unaccent the name row before tokenizing it
18CREATE FUNCTION bookbrainz.token_creator() RETURNS trigger
19 AS $token_creator$
20 begin
21 new.tokens := to_tsvector('pg_catalog.simple', unaccent(new.name));
22 return new;
23 end
24$token_creator$ LANGUAGE plpgsql;
25
26
27-- Trigger to automatically unaccent and insert tokens of an added alias name
28CREATE TRIGGER alias_token_update BEFORE INSERT OR UPDATE
29 ON bookbrainz.alias FOR EACH ROW EXECUTE PROCEDURE
30 bookbrainz.token_creator();
31
32
33-- View to extract all the master entities
34CREATE VIEW bookbrainz.master_entities AS
35 SELECT bbid as entity_id, default_alias_id as alias_id, 'creator' AS entity_type
36 FROM bookbrainz.creator
37 WHERE master=true
38 UNION
39 SELECT bbid as entity_id, default_alias_id as alias_id, 'edition' AS entity_type
40 FROM bookbrainz.edition
41 WHERE master=true
42 UNION
43 SELECT bbid as entity_id, default_alias_id as alias_id, 'publication' AS entity_type
44 FROM bookbrainz.publication
45 WHERE master=true
46 UNION
47 SELECT bbid as entity_id, default_alias_id as alias_id, 'publisher' AS entity_type
48 FROM bookbrainz.publisher
49 WHERE master=true
50 UNION
51 SELECT bbid as entity_id, default_alias_id as alias_id, 'work' AS entity_type
52 FROM bookbrainz.work
53 WHERE master=true;
54
55
56-- The primary materialized view which holds all the relevant information about master entities.
57-- Token field is indexed for quick FTS.
58-- A unique index is created to help execute REFRESH CONCURRENTLY command.
59-- This unique indexing can be modified later when imports are added.
60-- Sample query:
61-- SELECT * FROM search_mv
62-- WHERE tokens @@ to_tsquery('simple', 'Cha:* | Dar:*')
63-- ORDER BY ts_rank(search_mv.tokens, to_tsquery('english', 'Cha:* | Dar:*')) DESC;
64CREATE MATERIALIZED VIEW bookbrainz.search_mv AS
65 SELECT items.entity_id, alias.name, alias.tokens, items.entity_type
66 FROM bookbrainz.alias AS alias
67 JOIN (
68 SELECT *
69 FROM bookbrainz.master_entities
70 ) AS items
71 ON alias.id = items.alias_id;
72
73CREATE INDEX fts_search_idx ON bookbrainz.search_mv USING gin(tokens);
74CREATE UNIQUE INDEX unique_search_idx ON bookbrainz.search_mv (entity_id);
75
76
77-- This view holds all the master entities with empty tokens, signifying unsupported alphabet.
78-- It can be removed and operations carried out on search_mv instead, but it's helpful as an abstract concept
79-- An index is added on the name field of search_mv to facilitate quicker ILIKE operations
80CREATE VIEW bookbrainz.untokenized_names AS
81 SELECT * from bookbrainz.search_mv where tokens = '';
82
83CREATE INDEX untokenized_names_idx ON bookbrainz.search_mv USING gin(name);
84
85
86-- This materialized view stores all unique tokens, used to suggest the next best query.
87-- Words have been trigram indexed for quick fuzzy search.
88-- A unique index has been created on words to help execute REFRESH CONCURRENTLY command.
89-- Sample query:
90-- select word from search_words_mv ORDER BY similarity(word, 'chales') DESC limit 10;
91CREATE MATERIALIZED VIEW bookbrainz.search_words_mv AS
92 SELECT word FROM ts_stat($$
93 SELECT alias.tokens
94 FROM bookbrainz.alias AS alias
95 JOIN (
96 SELECT alias_id
97 FROM bookbrainz.master_entities
98 ) AS items
99 ON alias.id = items.alias_id;
100 $$);
101
102CREATE INDEX search_words_mv_idx ON search_words_mv USING gin(word gin_trgm_ops);
103CREATE UNIQUE INDEX unique_word_idx ON bookbrainz.search_words_mv (word);
104
105
106-- Refresh search_mv and search_words_mv materialized views
107CREATE FUNCTION bookbrainz.refresh_mv() RETURNS TRIGGER
108 AS $refresh_mv$
109 begin
110 REFRESH MATERIALIZED VIEW CONCURRENTLY bookbrainz.search_mv;
111 REFRESH MATERIALIZED VIEW CONCURRENTLY bookbrainz.search_words_mv;
112 RETURN NULL;
113 end
114$refresh_mv$ LANGUAGE plpgsql;
115
116-- Should it be left only for insert?
117CREATE TRIGGER alias_update
118 AFTER INSERT OR UPDATE OR DELETE
119 ON bookbrainz.alias
120 FOR EACH ROW
121 EXECUTE PROCEDURE bookbrainz.refresh_mv();
122
123END;