gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/spellfix.test (about) 1 # 2012 July 12 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # 12 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set testprefix spellfix 16 17 ifcapable !vtab { finish_test ; return } 18 19 load_static_extension db spellfix nextchar 20 21 set vocab { 22 rabbi rabbit rabbits rabble rabid rabies raccoon raccoons race raced racer 23 racers races racetrack racial racially racing rack racked racket racketeer 24 racketeering racketeers rackets racking racks radar radars radial radially 25 radian radiance radiant radiantly radiate radiated radiates radiating radiation 26 radiations radiator radiators radical radically radicals radices radii radio 27 radioactive radioastronomy radioed radiography radioing radiology radios radish 28 radishes radium radius radix radon raft rafter rafters rafts rag rage raged 29 rages ragged raggedly raggedness raging rags ragweed raid raided raider raiders 30 raiding raids rail railed railer railers railing railroad railroaded railroader 31 railroaders railroading railroads rails railway railways raiment rain rainbow 32 raincoat raincoats raindrop raindrops rained rainfall rainier rainiest raining 33 rains rainstorm rainy raise raised raiser raisers raises raisin raising rake 34 raked rakes raking rallied rallies rally rallying ram ramble rambler rambles 35 rambling ramblings ramification ramifications ramp rampage rampant rampart 36 ramps ramrod rams ran ranch ranched rancher ranchers ranches ranching rancid 37 random randomization randomize randomized randomizes randomly randomness randy 38 rang range ranged rangeland ranger rangers ranges ranging rangy rank ranked 39 ranker rankers rankest ranking rankings rankle rankly rankness ranks ransack 40 ransacked ransacking ransacks ransom ransomer ransoming ransoms rant ranted 41 ranter ranters ranting rants rap rapacious rape raped raper rapes rapid 42 rapidity rapidly rapids rapier raping rapport rapprochement raps rapt raptly 43 rapture raptures rapturous rare rarely rareness rarer rarest rarity rascal 44 rascally rascals rash rasher rashly rashness rasp raspberry rasped rasping 45 rasps raster rat rate rated rater raters rates rather ratification ratified 46 ratifies ratify ratifying rating ratings ratio ration rational rationale 47 rationales rationalities rationality rationalization rationalizations 48 rationalize rationalized rationalizes rationalizing rationally rationals 49 rationing rations ratios rats rattle rattled rattler rattlers rattles 50 rattlesnake rattlesnakes rattling raucous ravage ravaged ravager ravagers 51 ravages ravaging rave raved raven ravening ravenous ravenously ravens raves 52 ravine ravines raving ravings raw rawer rawest rawly rawness ray rays raze 53 razor razors re reabbreviate reabbreviated reabbreviates reabbreviating reach 54 reachability reachable reachably reached reacher reaches reaching reacquired 55 react reacted reacting reaction reactionaries reactionary reactions reactivate 56 reactivated reactivates reactivating reactivation reactive reactively 57 reactivity reactor reactors reacts read readability readable reader readers 58 readied readier readies readiest readily readiness reading readings readjusted 59 readout readouts reads ready readying real realest realign realigned realigning 60 realigns realism realist realistic realistically realists realities reality 61 } 62 63 do_test 1.1 { 64 execsql { CREATE VIRTUAL TABLE t1 USING spellfix1 } 65 foreach word $vocab { 66 execsql { INSERT INTO t1(word) VALUES($word) } 67 } 68 } {} 69 70 foreach {tn word res} { 71 1 raxpi* {rasping 5 rasped 5 ragweed 5 raspberry 6 rasp 4} 72 2 ril* {rail 4 railed 4 railer 4 railers 4 railing 4} 73 3 rilis* {realism 6 realist 6 realistic 6 realistically 6 realists 6} 74 4 reail* {real 3 realest 3 realign 3 realigned 3 realigning 3} 75 5 ras* {rascal 3 rascally 3 rascals 3 rash 3 rasher 3} 76 6 realistss* {realists 8 realigns 8 realistic 9 realistically 9 realest 7} 77 7 realistss {realists 8 realist 7 realigns 8 realistic 9 realest 7} 78 8 rllation* {realities 9 reality 7 rallied 7 railed 4} 79 9 renstom* {rainstorm 8 ransom 6 ransomer 6 ransoming 6 ransoms 6} 80 } { 81 do_execsql_test 1.2.$tn { 82 SELECT word, matchlen FROM t1 WHERE word MATCH $word 83 ORDER BY score, word LIMIT 5 84 } $res 85 } 86 87 # Tests of the next_char function. 88 # 89 do_test 1.10 { 90 db eval { 91 CREATE TABLE vocab(w TEXT PRIMARY KEY); 92 INSERT INTO vocab SELECT word FROM t1; 93 } 94 } {} 95 do_execsql_test 1.11 { 96 SELECT next_char('re','vocab','w'); 97 } {a} 98 do_execsql_test 1.11sub { 99 SELECT next_char('re','(SELECT w AS x FROM vocab)','x'); 100 } {a} 101 do_execsql_test 1.12 { 102 SELECT next_char('r','vocab','w'); 103 } {ae} 104 do_execsql_test 1.13 { 105 SELECT next_char('','vocab','w'); 106 } {r} 107 do_test 1.14 { 108 catchsql {SELECT next_char('','xyzzy','a')} 109 } {1 {no such table: xyzzy}} 110 111 do_execsql_test 1.20 { 112 CREATE TABLE vocab2(w TEXT); 113 CREATE INDEX vocab2w ON vocab2(w COLLATE nocase); 114 INSERT INTO vocab2 VALUES('abc'), ('ABD'), ('aBe'), ('AbF'); 115 SELECT next_char('ab', 'vocab2', 'w', null, 'nocase'); 116 } {cDeF} 117 do_execsql_test 1.21 { 118 SELECT next_char('ab','vocab2','w',null,null); 119 } {c} 120 do_execsql_test 1.22 { 121 SELECT next_char('AB','vocab2','w',null,'NOCASE'); 122 } {cDeF} 123 do_execsql_test 1.23 { 124 SELECT next_char('ab','vocab2','w',null,'binary'); 125 } {c} 126 127 do_execsql_test 1.30 { 128 SELECT rowid FROM t1 WHERE word='rabbit'; 129 } {2} 130 do_execsql_test 1.31 { 131 UPDATE t1 SET rowid=2000 WHERE word='rabbit'; 132 SELECT rowid FROM t1 WHERE word='rabbit'; 133 } {2000} 134 do_execsql_test 1.32 { 135 INSERT INTO t1(rowid, word) VALUES(3000,'melody'); 136 SELECT rowid, word, matchlen FROM t1 WHERE word MATCH 'melotti' 137 ORDER BY score LIMIT 3; 138 } {3000 melody 6} 139 do_test 1.33 { 140 catchsql {INSERT INTO t1(rowid, word) VALUES(3000,'garden');} 141 } {1 {constraint failed}} 142 143 do_execsql_test 2.1 { 144 CREATE VIRTUAL TABLE t2 USING spellfix1; 145 INSERT INTO t2 (word, soundslike) VALUES('school', 'skuul'); 146 INSERT INTO t2 (word, soundslike) VALUES('psalm', 'sarm'); 147 SELECT word, matchlen FROM t2 WHERE word MATCH 'sar*' LIMIT 5; 148 } {psalm 4} 149 150 do_execsql_test 2.2 { 151 SELECT word, matchlen FROM t2 WHERE word MATCH 'skol*' LIMIT 5; 152 } {school 6} 153 154 set vocab { 155 kangaroo kanji kappa karate keel keeled keeling keels keen keener keenest 156 keenly keenness keep keeper keepers keeping keeps ken kennel kennels kept 157 kerchief kerchiefs kern kernel kernels kerosene ketchup kettle 158 kettles key keyboard keyboards keyed keyhole keying keynote keypad keypads keys 159 keystroke keystrokes keyword keywords kick kicked kicker kickers kicking 160 kickoff kicks kid kidded kiddie kidding kidnap kidnapper kidnappers kidnapping 161 kidnappings kidnaps kidney kidneys kids kill killed killer killers killing 162 killingly killings killjoy kills kilobit kilobits kiloblock kilobyte kilobytes 163 kilogram kilograms kilohertz kilohm kilojoule kilometer kilometers kiloton 164 kilovolt kilowatt kiloword kimono kin kind kinder kindergarten kindest 165 kindhearted kindle kindled kindles kindling kindly kindness kindred kinds 166 kinetic king kingdom kingdoms kingly kingpin kings kink kinky kinship kinsman 167 kiosk kiss kissed kisser kissers kisses kissing kit kitchen kitchenette 168 kitchens kite kited kites kiting kits kitten kittenish kittens kitty klaxon 169 kludge kludges klystron knack knapsack knapsacks knave knaves knead kneads knee 170 kneecap kneed kneeing kneel kneeled kneeling kneels knees knell knells knelt 171 knew knife knifed knifes knifing knight knighted knighthood knighting knightly 172 knights knit knits knives knob knobs knock knockdown knocked knocker knockers 173 knocking knockout knocks knoll knolls knot knots knotted knotting know knowable 174 knower knowhow knowing knowingly knowledge knowledgeable known knows knuckle 175 knuckled knuckles koala kosher kudo 176 } 177 178 do_execsql_test 3.1 { 179 CREATE TABLE costs(iLang, cFrom, cTo, iCost); 180 INSERT INTO costs VALUES(0, 'a', 'e', 1); 181 INSERT INTO costs VALUES(0, 'e', 'i', 1); 182 INSERT INTO costs VALUES(0, 'i', 'o', 1); 183 INSERT INTO costs VALUES(0, 'o', 'u', 1); 184 INSERT INTO costs VALUES(0, 'u', 'a', 1); 185 CREATE VIRTUAL TABLE t3 USING spellfix1(edit_cost_table=costs); 186 } 187 188 do_test 3.2 { 189 foreach w $vocab { 190 execsql { INSERT INTO t3(word) VALUES($w) } 191 } 192 } {} 193 194 foreach {tn word res} { 195 1 kos* {kosher 3 kiosk 4 kudo 2 kiss 3 kissed 3} 196 2 kellj* {killjoy 5 kill 4 killed 4 killer 4 killers 4} 197 3 kellj {kill 4 kills 5 killjoy 7 keel 4 killed 6} 198 } { 199 do_execsql_test 3.2.$tn { 200 SELECT word, matchlen FROM t3 WHERE word MATCH $word 201 ORDER BY score, word LIMIT 5 202 } $res 203 } 204 205 do_execsql_test 4.0 { 206 INSERT INTO t3(command) VALUES('edit_cost_table=NULL'); 207 } 208 foreach {tn word res} { 209 1 kosher {kosher 0 kisser 51 kissers 76 kissed 126 kisses 126} 210 2 kellj {keels 60 killjoy 68 kills 80 keel 120 kill 125} 211 3 kashar {kosher 80 kisser 91 kissers 116 kissed 166 kisses 166} 212 } { 213 do_execsql_test 4.1.$tn { 214 SELECT word, distance FROM t3 WHERE word MATCH $word 215 ORDER BY score, word LIMIT 5 216 } $res 217 } 218 do_execsql_test 5.0 { 219 CREATE TABLE costs2(iLang, cFrom, cTo, iCost); 220 INSERT INTO costs2 VALUES(0, 'a', 'o', 1); 221 INSERT INTO costs2 VALUES(0, 'e', 'o', 4); 222 INSERT INTO costs2 VALUES(0, 'i', 'o', 8); 223 INSERT INTO costs2 VALUES(0, 'u', 'o', 16); 224 INSERT INTO t3(command) VALUES('edit_cost_table="costs2"'); 225 } 226 227 foreach {tn word res} { 228 1 kasher {kosher 1} 229 2 kesher {kosher 4} 230 3 kisher {kosher 8} 231 4 kosher {kosher 0} 232 5 kusher {kosher 16} 233 } { 234 do_execsql_test 5.1.$tn { 235 SELECT word, distance FROM t3 WHERE word MATCH $word 236 ORDER BY score, word LIMIT 1 237 } $res 238 } 239 240 #------------------------------------------------------------------------- 241 # Try some queries by rowid. 242 # 243 do_execsql_test 6.1.1 { 244 SELECT word FROM t3 WHERE rowid = 10; 245 } {keener} 246 do_execsql_test 6.1.2 { 247 SELECT word, distance FROM t3 WHERE rowid = 10; 248 } {keener {}} 249 do_execsql_test 6.1.3 { 250 SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner'; 251 } {keener 300} 252 253 ifcapable trace { 254 proc trace_callback {sql} { 255 if {[string range $sql 0 2] == "-- "} { 256 lappend ::trace [string range $sql 3 end] 257 } 258 } 259 260 proc do_tracesql_test {tn sql {res {}}} { 261 set ::trace [list] 262 uplevel [list do_test $tn [subst -nocommands { 263 set vals [execsql {$sql}] 264 concat [set vals] [set ::trace] 265 }] [list {*}$res]] 266 } 267 268 db trace trace_callback 269 do_tracesql_test 6.2.1 { 270 SELECT word FROM t3 WHERE rowid = 10; 271 } {keener 272 {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?} 273 } 274 do_tracesql_test 6.2.2 { 275 SELECT word, distance FROM t3 WHERE rowid = 10; 276 } {keener {} 277 {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?} 278 } 279 do_tracesql_test 6.2.3 { 280 SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner'; 281 } {keener 300 282 {SELECT id, word, rank, coalesce(k1,word) FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2} 283 } 284 } 285 286 #------------------------------------------------------------------------- 287 # Test that the spellfix1 table supports conflict handling (OR REPLACE 288 # and so on). 289 # 290 do_execsql_test 7.1 { 291 CREATE VIRTUAL TABLE t4 USING spellfix1; 292 PRAGMA table_info = t4; 293 } { 294 0 word {} 0 {} 0 295 1 rank {} 0 {} 0 296 2 distance {} 0 {} 0 297 3 langid {} 0 {} 0 298 4 score {} 0 {} 0 299 5 matchlen {} 0 {} 0 300 } 301 302 do_execsql_test 7.2.1 { 303 INSERT INTO t4(rowid, word) VALUES(1, 'Archilles'); 304 INSERT INTO t4(rowid, word) VALUES(2, 'Pluto'); 305 INSERT INTO t4(rowid, word) VALUES(3, 'Atrides'); 306 INSERT OR REPLACE INTO t4(rowid, word) VALUES(2, 'Apollo'); 307 SELECT rowid, word FROM t4; 308 } { 309 1 Archilles 2 Apollo 3 Atrides 310 } 311 do_catchsql_test 7.2.2 { 312 INSERT OR ABORT INTO t4(rowid, word) VALUES(1, 'Leto'); 313 } {1 {constraint failed}} 314 do_catchsql_test 7.2.3 { 315 INSERT OR ROLLBACK INTO t4(rowid, word) VALUES(3, 'Zeus'); 316 } {1 {constraint failed}} 317 do_catchsql_test 7.2.4 { 318 INSERT OR FAIL INTO t4(rowid, word) VALUES(3, 'Zeus'); 319 } {1 {constraint failed}} 320 do_execsql_test 7.2.5 { 321 INSERT OR IGNORE INTO t4(rowid, word) VALUES(3, 'Zeus'); 322 SELECT rowid, word FROM t4; 323 } { 324 1 Archilles 2 Apollo 3 Atrides 325 } 326 327 do_execsql_test 7.3.1 { 328 UPDATE OR REPLACE t4 SET rowid=3 WHERE rowid=1; 329 SELECT rowid, word FROM t4; 330 } {2 Apollo 3 Archilles} 331 do_catchsql_test 7.3.2 { 332 UPDATE OR ABORT t4 SET rowid=3 WHERE rowid=2; 333 } {1 {constraint failed}} 334 do_catchsql_test 7.3.3 { 335 UPDATE OR ROLLBACK t4 SET rowid=3 WHERE rowid=2; 336 } {1 {constraint failed}} 337 do_catchsql_test 7.3.4 { 338 UPDATE OR FAIL t4 SET rowid=3 WHERE rowid=2; 339 } {1 {constraint failed}} 340 do_execsql_test 7.3.5 { 341 UPDATE OR IGNORE t4 SET rowid=3 WHERE rowid=2; 342 SELECT rowid, word FROM t4; 343 } {2 Apollo 3 Archilles} 344 345 do_execsql_test 7.4.1 { 346 DELETE FROM t4; 347 INSERT INTO t4(rowid, word) VALUES(10, 'Agamemnon'); 348 INSERT INTO t4(rowid, word) VALUES(20, 'Patroclus'); 349 INSERT INTO t4(rowid, word) VALUES(30, 'Chryses'); 350 351 CREATE TABLE t5(i, w); 352 INSERT INTO t5 VALUES(5, 'Poseidon'); 353 INSERT INTO t5 VALUES(20, 'Chronos'); 354 INSERT INTO t5 VALUES(30, 'Hera'); 355 } 356 357 db_save_and_close 358 foreach {tn conflict err bRollback res} { 359 0 "" {1 {constraint failed}} 0 360 {10 Agamemnon 20 Patroclus 30 Chryses} 361 1 "OR REPLACE" {0 {}} 0 362 {5 Poseidon 10 Agamemnon 20 Chronos 30 Hera} 363 2 "OR ABORT" {1 {constraint failed}} 0 364 {10 Agamemnon 20 Patroclus 30 Chryses} 365 3 "OR ROLLBACK" {1 {constraint failed}} 1 366 {10 Agamemnon 20 Patroclus 30 Chryses} 367 5 "OR IGNORE" {0 {}} 0 368 {5 Poseidon 10 Agamemnon 20 Patroclus 30 Chryses} 369 } { 370 db_restore_and_reopen 371 load_static_extension db spellfix nextchar 372 373 execsql BEGIN 374 set sql "INSERT $conflict INTO t4(rowid, word) SELECT i, w FROM t5" 375 do_catchsql_test 7.4.2.$tn.1 $sql $err 376 do_execsql_test 7.4.2.$tn.2 { SELECT rowid, word FROM t4 } $res 377 378 do_test 7.4.2.$tn.3 { sqlite3_get_autocommit db } $bRollback 379 catchsql ROLLBACK 380 } 381 382 foreach {tn conflict err bRollback res} { 383 0 "" {1 {constraint failed}} 0 384 {10 Agamemnon 20 Patroclus 30 Chryses} 385 1 "OR REPLACE" {0 {}} 0 386 {15 Agamemnon 45 Chryses} 387 2 "OR ABORT" {1 {constraint failed}} 0 388 {10 Agamemnon 20 Patroclus 30 Chryses} 389 3 "OR ROLLBACK" {1 {constraint failed}} 1 390 {10 Agamemnon 20 Patroclus 30 Chryses} 391 5 "OR IGNORE" {0 {}} 0 392 {15 Agamemnon 20 Patroclus 45 Chryses} 393 } { 394 db_restore_and_reopen 395 load_static_extension db spellfix nextchar 396 397 execsql BEGIN 398 set sql "UPDATE $conflict t4 SET rowid=rowid + (rowid/2)" 399 do_catchsql_test 7.5.2.$tn.1 $sql $err 400 do_execsql_test 7.5.2.$tn.2 { SELECT rowid, word FROM t4 } $res 401 do_test 7.5.2.$tn.3 { sqlite3_get_autocommit db } $bRollback 402 catchsql ROLLBACK 403 } 404 405 finish_test