1--Test text search dictionaries and configurations 2 3-- Test ISpell dictionary with ispell affix file 4CREATE TEXT SEARCH DICTIONARY ispell ( 5 Template=ispell, 6 DictFile=ispell_sample, 7 AffFile=ispell_sample 8); 9 10SELECT ts_lexize('ispell', 'skies'); 11SELECT ts_lexize('ispell', 'bookings'); 12SELECT ts_lexize('ispell', 'booking'); 13SELECT ts_lexize('ispell', 'foot'); 14SELECT ts_lexize('ispell', 'foots'); 15SELECT ts_lexize('ispell', 'rebookings'); 16SELECT ts_lexize('ispell', 'rebooking'); 17SELECT ts_lexize('ispell', 'rebook'); 18SELECT ts_lexize('ispell', 'unbookings'); 19SELECT ts_lexize('ispell', 'unbooking'); 20SELECT ts_lexize('ispell', 'unbook'); 21 22SELECT ts_lexize('ispell', 'footklubber'); 23SELECT ts_lexize('ispell', 'footballklubber'); 24SELECT ts_lexize('ispell', 'ballyklubber'); 25SELECT ts_lexize('ispell', 'footballyklubber'); 26 27-- Test ISpell dictionary with hunspell affix file 28CREATE TEXT SEARCH DICTIONARY hunspell ( 29 Template=ispell, 30 DictFile=ispell_sample, 31 AffFile=hunspell_sample 32); 33 34SELECT ts_lexize('hunspell', 'skies'); 35SELECT ts_lexize('hunspell', 'bookings'); 36SELECT ts_lexize('hunspell', 'booking'); 37SELECT ts_lexize('hunspell', 'foot'); 38SELECT ts_lexize('hunspell', 'foots'); 39SELECT ts_lexize('hunspell', 'rebookings'); 40SELECT ts_lexize('hunspell', 'rebooking'); 41SELECT ts_lexize('hunspell', 'rebook'); 42SELECT ts_lexize('hunspell', 'unbookings'); 43SELECT ts_lexize('hunspell', 'unbooking'); 44SELECT ts_lexize('hunspell', 'unbook'); 45 46SELECT ts_lexize('hunspell', 'footklubber'); 47SELECT ts_lexize('hunspell', 'footballklubber'); 48SELECT ts_lexize('hunspell', 'ballyklubber'); 49SELECT ts_lexize('hunspell', 'footballyklubber'); 50 51-- Test ISpell dictionary with hunspell affix file with FLAG long parameter 52CREATE TEXT SEARCH DICTIONARY hunspell_long ( 53 Template=ispell, 54 DictFile=hunspell_sample_long, 55 AffFile=hunspell_sample_long 56); 57 58SELECT ts_lexize('hunspell_long', 'skies'); 59SELECT ts_lexize('hunspell_long', 'bookings'); 60SELECT ts_lexize('hunspell_long', 'booking'); 61SELECT ts_lexize('hunspell_long', 'foot'); 62SELECT ts_lexize('hunspell_long', 'foots'); 63SELECT ts_lexize('hunspell_long', 'rebookings'); 64SELECT ts_lexize('hunspell_long', 'rebooking'); 65SELECT ts_lexize('hunspell_long', 'rebook'); 66SELECT ts_lexize('hunspell_long', 'unbookings'); 67SELECT ts_lexize('hunspell_long', 'unbooking'); 68SELECT ts_lexize('hunspell_long', 'unbook'); 69SELECT ts_lexize('hunspell_long', 'booked'); 70 71SELECT ts_lexize('hunspell_long', 'footklubber'); 72SELECT ts_lexize('hunspell_long', 'footballklubber'); 73SELECT ts_lexize('hunspell_long', 'ballyklubber'); 74SELECT ts_lexize('hunspell_long', 'ballsklubber'); 75SELECT ts_lexize('hunspell_long', 'footballyklubber'); 76SELECT ts_lexize('hunspell_long', 'ex-machina'); 77 78-- Test ISpell dictionary with hunspell affix file with FLAG num parameter 79CREATE TEXT SEARCH DICTIONARY hunspell_num ( 80 Template=ispell, 81 DictFile=hunspell_sample_num, 82 AffFile=hunspell_sample_num 83); 84 85SELECT ts_lexize('hunspell_num', 'skies'); 86SELECT ts_lexize('hunspell_num', 'sk'); 87SELECT ts_lexize('hunspell_num', 'bookings'); 88SELECT ts_lexize('hunspell_num', 'booking'); 89SELECT ts_lexize('hunspell_num', 'foot'); 90SELECT ts_lexize('hunspell_num', 'foots'); 91SELECT ts_lexize('hunspell_num', 'rebookings'); 92SELECT ts_lexize('hunspell_num', 'rebooking'); 93SELECT ts_lexize('hunspell_num', 'rebook'); 94SELECT ts_lexize('hunspell_num', 'unbookings'); 95SELECT ts_lexize('hunspell_num', 'unbooking'); 96SELECT ts_lexize('hunspell_num', 'unbook'); 97SELECT ts_lexize('hunspell_num', 'booked'); 98 99SELECT ts_lexize('hunspell_num', 'footklubber'); 100SELECT ts_lexize('hunspell_num', 'footballklubber'); 101SELECT ts_lexize('hunspell_num', 'ballyklubber'); 102SELECT ts_lexize('hunspell_num', 'footballyklubber'); 103 104-- Test suitability of affix and dict files 105CREATE TEXT SEARCH DICTIONARY hunspell_err ( 106 Template=ispell, 107 DictFile=ispell_sample, 108 AffFile=hunspell_sample_long 109); 110 111CREATE TEXT SEARCH DICTIONARY hunspell_err ( 112 Template=ispell, 113 DictFile=ispell_sample, 114 AffFile=hunspell_sample_num 115); 116 117CREATE TEXT SEARCH DICTIONARY hunspell_invalid_1 ( 118 Template=ispell, 119 DictFile=hunspell_sample_long, 120 AffFile=ispell_sample 121); 122 123CREATE TEXT SEARCH DICTIONARY hunspell_invalid_2 ( 124 Template=ispell, 125 DictFile=hunspell_sample_long, 126 AffFile=hunspell_sample_num 127); 128 129CREATE TEXT SEARCH DICTIONARY hunspell_invalid_3 ( 130 Template=ispell, 131 DictFile=hunspell_sample_num, 132 AffFile=ispell_sample 133); 134 135CREATE TEXT SEARCH DICTIONARY hunspell_err ( 136 Template=ispell, 137 DictFile=hunspell_sample_num, 138 AffFile=hunspell_sample_long 139); 140 141-- Synonym dictionary 142CREATE TEXT SEARCH DICTIONARY synonym ( 143 Template=synonym, 144 Synonyms=synonym_sample 145); 146 147SELECT ts_lexize('synonym', 'PoStGrEs'); 148SELECT ts_lexize('synonym', 'Gogle'); 149SELECT ts_lexize('synonym', 'indices'); 150 151-- test altering boolean parameters 152SELECT dictinitoption FROM pg_ts_dict WHERE dictname = 'synonym'; 153 154ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = 1); 155SELECT ts_lexize('synonym', 'PoStGrEs'); 156SELECT dictinitoption FROM pg_ts_dict WHERE dictname = 'synonym'; 157 158ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = 2); -- fail 159 160ALTER TEXT SEARCH DICTIONARY synonym (CaseSensitive = off); 161SELECT ts_lexize('synonym', 'PoStGrEs'); 162SELECT dictinitoption FROM pg_ts_dict WHERE dictname = 'synonym'; 163 164-- Create and simple test thesaurus dictionary 165-- More tests in configuration checks because ts_lexize() 166-- cannot pass more than one word to thesaurus. 167CREATE TEXT SEARCH DICTIONARY thesaurus ( 168 Template=thesaurus, 169 DictFile=thesaurus_sample, 170 Dictionary=english_stem 171); 172 173SELECT ts_lexize('thesaurus', 'one'); 174 175-- Test ispell dictionary in configuration 176CREATE TEXT SEARCH CONFIGURATION ispell_tst ( 177 COPY=english 178); 179 180ALTER TEXT SEARCH CONFIGURATION ispell_tst ALTER MAPPING FOR 181 word, numword, asciiword, hword, numhword, asciihword, hword_part, hword_numpart, hword_asciipart 182 WITH ispell, english_stem; 183 184SELECT to_tsvector('ispell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 185SELECT to_tsquery('ispell_tst', 'footballklubber'); 186SELECT to_tsquery('ispell_tst', 'footballyklubber:b & rebookings:A & sky'); 187 188-- Test ispell dictionary with hunspell affix in configuration 189CREATE TEXT SEARCH CONFIGURATION hunspell_tst ( 190 COPY=ispell_tst 191); 192 193ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 194 REPLACE ispell WITH hunspell; 195 196SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 197SELECT to_tsquery('hunspell_tst', 'footballklubber'); 198SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 199 200SELECT to_tsquery('hunspell_tst', 'footballyklubber:b <-> sky'); 201SELECT phraseto_tsquery('hunspell_tst', 'footballyklubber sky'); 202 203-- Test ispell dictionary with hunspell affix with FLAG long in configuration 204ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 205 REPLACE hunspell WITH hunspell_long; 206 207SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 208SELECT to_tsquery('hunspell_tst', 'footballklubber'); 209SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 210 211-- Test ispell dictionary with hunspell affix with FLAG num in configuration 212ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 213 REPLACE hunspell_long WITH hunspell_num; 214 215SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 216SELECT to_tsquery('hunspell_tst', 'footballklubber'); 217SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 218 219-- Test synonym dictionary in configuration 220CREATE TEXT SEARCH CONFIGURATION synonym_tst ( 221 COPY=english 222); 223 224ALTER TEXT SEARCH CONFIGURATION synonym_tst ALTER MAPPING FOR 225 asciiword, hword_asciipart, asciihword 226 WITH synonym, english_stem; 227 228SELECT to_tsvector('synonym_tst', 'Postgresql is often called as postgres or pgsql and pronounced as postgre'); 229SELECT to_tsvector('synonym_tst', 'Most common mistake is to write Gogle instead of Google'); 230SELECT to_tsvector('synonym_tst', 'Indexes or indices - Which is right plural form of index?'); 231SELECT to_tsquery('synonym_tst', 'Index & indices'); 232 233-- test thesaurus in configuration 234-- see thesaurus_sample.ths to understand 'odd' resulting tsvector 235CREATE TEXT SEARCH CONFIGURATION thesaurus_tst ( 236 COPY=synonym_tst 237); 238 239ALTER TEXT SEARCH CONFIGURATION thesaurus_tst ALTER MAPPING FOR 240 asciiword, hword_asciipart, asciihword 241 WITH synonym, thesaurus, english_stem; 242 243SELECT to_tsvector('thesaurus_tst', 'one postgres one two one two three one'); 244SELECT to_tsvector('thesaurus_tst', 'Supernovae star is very new star and usually called supernovae (abbreviation SN)'); 245SELECT to_tsvector('thesaurus_tst', 'Booking tickets is looking like a booking a tickets'); 246 247-- invalid: non-lowercase quoted identifiers 248CREATE TEXT SEARCH DICTIONARY tsdict_case 249( 250 Template = ispell, 251 "DictFile" = ispell_sample, 252 "AffFile" = ispell_sample 253); 254