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'); 69 70SELECT ts_lexize('hunspell_long', 'footklubber'); 71SELECT ts_lexize('hunspell_long', 'footballklubber'); 72SELECT ts_lexize('hunspell_long', 'ballyklubber'); 73SELECT ts_lexize('hunspell_long', 'footballyklubber'); 74 75-- Test ISpell dictionary with hunspell affix file with FLAG num parameter 76CREATE TEXT SEARCH DICTIONARY hunspell_num ( 77 Template=ispell, 78 DictFile=hunspell_sample_num, 79 AffFile=hunspell_sample_num 80); 81 82SELECT ts_lexize('hunspell_num', 'skies'); 83SELECT ts_lexize('hunspell_num', 'bookings'); 84SELECT ts_lexize('hunspell_num', 'booking'); 85SELECT ts_lexize('hunspell_num', 'foot'); 86SELECT ts_lexize('hunspell_num', 'foots'); 87SELECT ts_lexize('hunspell_num', 'rebookings'); 88SELECT ts_lexize('hunspell_num', 'rebooking'); 89SELECT ts_lexize('hunspell_num', 'rebook'); 90SELECT ts_lexize('hunspell_num', 'unbookings'); 91SELECT ts_lexize('hunspell_num', 'unbooking'); 92SELECT ts_lexize('hunspell_num', 'unbook'); 93 94SELECT ts_lexize('hunspell_num', 'footklubber'); 95SELECT ts_lexize('hunspell_num', 'footballklubber'); 96SELECT ts_lexize('hunspell_num', 'ballyklubber'); 97SELECT ts_lexize('hunspell_num', 'footballyklubber'); 98 99-- Test suitability of affix and dict files 100CREATE TEXT SEARCH DICTIONARY hunspell_err ( 101 Template=ispell, 102 DictFile=ispell_sample, 103 AffFile=hunspell_sample_long 104); 105 106CREATE TEXT SEARCH DICTIONARY hunspell_err ( 107 Template=ispell, 108 DictFile=ispell_sample, 109 AffFile=hunspell_sample_num 110); 111 112CREATE TEXT SEARCH DICTIONARY hunspell_invalid_1 ( 113 Template=ispell, 114 DictFile=hunspell_sample_long, 115 AffFile=ispell_sample 116); 117 118CREATE TEXT SEARCH DICTIONARY hunspell_invalid_2 ( 119 Template=ispell, 120 DictFile=hunspell_sample_long, 121 AffFile=hunspell_sample_num 122); 123 124CREATE TEXT SEARCH DICTIONARY hunspell_invalid_3 ( 125 Template=ispell, 126 DictFile=hunspell_sample_num, 127 AffFile=ispell_sample 128); 129 130CREATE TEXT SEARCH DICTIONARY hunspell_err ( 131 Template=ispell, 132 DictFile=hunspell_sample_num, 133 AffFile=hunspell_sample_long 134); 135 136-- Synonym dictionary 137CREATE TEXT SEARCH DICTIONARY synonym ( 138 Template=synonym, 139 Synonyms=synonym_sample 140); 141 142SELECT ts_lexize('synonym', 'PoStGrEs'); 143SELECT ts_lexize('synonym', 'Gogle'); 144SELECT ts_lexize('synonym', 'indices'); 145 146-- Create and simple test thesaurus dictionary 147-- More tests in configuration checks because ts_lexize() 148-- cannot pass more than one word to thesaurus. 149CREATE TEXT SEARCH DICTIONARY thesaurus ( 150 Template=thesaurus, 151 DictFile=thesaurus_sample, 152 Dictionary=english_stem 153); 154 155SELECT ts_lexize('thesaurus', 'one'); 156 157-- Test ispell dictionary in configuration 158CREATE TEXT SEARCH CONFIGURATION ispell_tst ( 159 COPY=english 160); 161 162ALTER TEXT SEARCH CONFIGURATION ispell_tst ALTER MAPPING FOR 163 word, numword, asciiword, hword, numhword, asciihword, hword_part, hword_numpart, hword_asciipart 164 WITH ispell, english_stem; 165 166SELECT to_tsvector('ispell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 167SELECT to_tsquery('ispell_tst', 'footballklubber'); 168SELECT to_tsquery('ispell_tst', 'footballyklubber:b & rebookings:A & sky'); 169 170-- Test ispell dictionary with hunspell affix in configuration 171CREATE TEXT SEARCH CONFIGURATION hunspell_tst ( 172 COPY=ispell_tst 173); 174 175ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 176 REPLACE ispell WITH hunspell; 177 178SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 179SELECT to_tsquery('hunspell_tst', 'footballklubber'); 180SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 181 182SELECT to_tsquery('hunspell_tst', 'footballyklubber:b <-> sky'); 183SELECT phraseto_tsquery('hunspell_tst', 'footballyklubber sky'); 184 185-- Test ispell dictionary with hunspell affix with FLAG long in configuration 186ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 187 REPLACE hunspell WITH hunspell_long; 188 189SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 190SELECT to_tsquery('hunspell_tst', 'footballklubber'); 191SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 192 193-- Test ispell dictionary with hunspell affix with FLAG num in configuration 194ALTER TEXT SEARCH CONFIGURATION hunspell_tst ALTER MAPPING 195 REPLACE hunspell_long WITH hunspell_num; 196 197SELECT to_tsvector('hunspell_tst', 'Booking the skies after rebookings for footballklubber from a foot'); 198SELECT to_tsquery('hunspell_tst', 'footballklubber'); 199SELECT to_tsquery('hunspell_tst', 'footballyklubber:b & rebookings:A & sky'); 200 201-- Test synonym dictionary in configuration 202CREATE TEXT SEARCH CONFIGURATION synonym_tst ( 203 COPY=english 204); 205 206ALTER TEXT SEARCH CONFIGURATION synonym_tst ALTER MAPPING FOR 207 asciiword, hword_asciipart, asciihword 208 WITH synonym, english_stem; 209 210SELECT to_tsvector('synonym_tst', 'Postgresql is often called as postgres or pgsql and pronounced as postgre'); 211SELECT to_tsvector('synonym_tst', 'Most common mistake is to write Gogle instead of Google'); 212SELECT to_tsvector('synonym_tst', 'Indexes or indices - Which is right plural form of index?'); 213SELECT to_tsquery('synonym_tst', 'Index & indices'); 214 215-- test thesaurus in configuration 216-- see thesaurus_sample.ths to understand 'odd' resulting tsvector 217CREATE TEXT SEARCH CONFIGURATION thesaurus_tst ( 218 COPY=synonym_tst 219); 220 221ALTER TEXT SEARCH CONFIGURATION thesaurus_tst ALTER MAPPING FOR 222 asciiword, hword_asciipart, asciihword 223 WITH synonym, thesaurus, english_stem; 224 225SELECT to_tsvector('thesaurus_tst', 'one postgres one two one two three one'); 226SELECT to_tsvector('thesaurus_tst', 'Supernovae star is very new star and usually called supernovae (abbreviation SN)'); 227SELECT to_tsvector('thesaurus_tst', 'Booking tickets is looking like a booking a tickets'); 228