47 lines
2.2 KiB
MySQL
47 lines
2.2 KiB
MySQL
|
|
||
|
|
-- as a result of these migrations, you can do the following:
|
||
|
|
|
||
|
|
-- find a band with a word starting with 'Par'
|
||
|
|
-- select name from bands where name_tsv @@ to_tsquery('jamenglish', 'Par:*');
|
||
|
|
-- find a user with first or last name starting with 'Cal'
|
||
|
|
-- select first_name FROM users where name_tsv @@ to_tsquery('jamenglish', 'Cal:*');
|
||
|
|
-- find a recording with descriptio start with 'Fu'
|
||
|
|
-- select description FROM descriptions where description_tsv @@ to_tsquery('jamenglish', 'Fu:*');
|
||
|
|
|
||
|
|
CREATE TEXT SEARCH DICTIONARY english_stem (
|
||
|
|
TEMPLATE = snowball,
|
||
|
|
Language = english,
|
||
|
|
StopWords = english);
|
||
|
|
|
||
|
|
-- create a new configuration based on the standard 'english' configuration
|
||
|
|
CREATE TEXT SEARCH CONFIGURATION public.jamenglish ( COPY = pg_catalog.english );
|
||
|
|
-- use snowball for word-y things
|
||
|
|
ALTER TEXT SEARCH CONFIGURATION public.jamenglish ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
|
||
|
|
word, hword, hword_part WITH english_stem;
|
||
|
|
-- don't worry about parsing character junk
|
||
|
|
ALTER TEXT SEARCH CONFIGURATION public.jamenglish DROP MAPPING FOR email, url, url_path, sfloat, float;
|
||
|
|
|
||
|
|
-- add relevant rows to users, bands, recordings
|
||
|
|
ALTER TABLE users ADD COLUMN name_tsv tsvector;
|
||
|
|
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
|
||
|
|
ON users FOR EACH ROW EXECUTE PROCEDURE
|
||
|
|
tsvector_update_trigger(name_tsv, 'public.jamenglish', first_name, last_name);
|
||
|
|
CREATE INDEX users_name_tsv_index ON users USING gin(name_tsv);
|
||
|
|
|
||
|
|
ALTER TABLE bands ADD COLUMN name_tsv tsvector;
|
||
|
|
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
|
||
|
|
ON bands FOR EACH ROW EXECUTE PROCEDURE
|
||
|
|
tsvector_update_trigger(name_tsv, 'public.jamenglish', name);
|
||
|
|
CREATE INDEX bands_name_tsv_index ON bands USING gin(name_tsv);
|
||
|
|
|
||
|
|
ALTER TABLE recordings ADD COLUMN description_tsv tsvector;
|
||
|
|
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
|
||
|
|
ON recordings FOR EACH ROW EXECUTE PROCEDURE
|
||
|
|
tsvector_update_trigger(description_tsv, 'public.jamenglish', description);
|
||
|
|
CREATE INDEX recordings_description_tsv_index ON recordings USING gin(description_tsv);
|
||
|
|
|
||
|
|
-- update all existing data to invoke triggers
|
||
|
|
update users set first_name=first_name, last_name=last_name;
|
||
|
|
update bands set name=name;
|
||
|
|
update recordings set description=description;
|