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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set testprefix spellfix 16 17ifcapable !vtab { finish_test ; return } 18 19load_static_extension db spellfix nextchar 20 21set vocab { 22rabbi rabbit rabbits rabble rabid rabies raccoon raccoons race raced racer 23racers races racetrack racial racially racing rack racked racket racketeer 24racketeering racketeers rackets racking racks radar radars radial radially 25radian radiance radiant radiantly radiate radiated radiates radiating radiation 26radiations radiator radiators radical radically radicals radices radii radio 27radioactive radioastronomy radioed radiography radioing radiology radios radish 28radishes radium radius radix radon raft rafter rafters rafts rag rage raged 29rages ragged raggedly raggedness raging rags ragweed raid raided raider raiders 30raiding raids rail railed railer railers railing railroad railroaded railroader 31railroaders railroading railroads rails railway railways raiment rain rainbow 32raincoat raincoats raindrop raindrops rained rainfall rainier rainiest raining 33rains rainstorm rainy raise raised raiser raisers raises raisin raising rake 34raked rakes raking rallied rallies rally rallying ram ramble rambler rambles 35rambling ramblings ramification ramifications ramp rampage rampant rampart 36ramps ramrod rams ran ranch ranched rancher ranchers ranches ranching rancid 37random randomization randomize randomized randomizes randomly randomness randy 38rang range ranged rangeland ranger rangers ranges ranging rangy rank ranked 39ranker rankers rankest ranking rankings rankle rankly rankness ranks ransack 40ransacked ransacking ransacks ransom ransomer ransoming ransoms rant ranted 41ranter ranters ranting rants rap rapacious rape raped raper rapes rapid 42rapidity rapidly rapids rapier raping rapport rapprochement raps rapt raptly 43rapture raptures rapturous rare rarely rareness rarer rarest rarity rascal 44rascally rascals rash rasher rashly rashness rasp raspberry rasped rasping 45rasps raster rat rate rated rater raters rates rather ratification ratified 46ratifies ratify ratifying rating ratings ratio ration rational rationale 47rationales rationalities rationality rationalization rationalizations 48rationalize rationalized rationalizes rationalizing rationally rationals 49rationing rations ratios rats rattle rattled rattler rattlers rattles 50rattlesnake rattlesnakes rattling raucous ravage ravaged ravager ravagers 51ravages ravaging rave raved raven ravening ravenous ravenously ravens raves 52ravine ravines raving ravings raw rawer rawest rawly rawness ray rays raze 53razor razors re reabbreviate reabbreviated reabbreviates reabbreviating reach 54reachability reachable reachably reached reacher reaches reaching reacquired 55react reacted reacting reaction reactionaries reactionary reactions reactivate 56reactivated reactivates reactivating reactivation reactive reactively 57reactivity reactor reactors reacts read readability readable reader readers 58readied readier readies readiest readily readiness reading readings readjusted 59readout readouts reads ready readying real realest realign realigned realigning 60realigns realism realist realistic realistically realists realities reality 61} 62 63do_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 70foreach {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# 89do_test 1.10 { 90 db eval { 91 CREATE TABLE vocab(w TEXT PRIMARY KEY); 92 INSERT INTO vocab SELECT word FROM t1; 93 } 94} {} 95do_execsql_test 1.11 { 96 SELECT next_char('re','vocab','w'); 97} {a} 98do_execsql_test 1.11sub { 99 SELECT next_char('re','(SELECT w AS x FROM vocab)','x'); 100} {a} 101do_execsql_test 1.12 { 102 SELECT next_char('r','vocab','w'); 103} {ae} 104do_execsql_test 1.13 { 105 SELECT next_char('','vocab','w'); 106} {r} 107do_test 1.14 { 108 catchsql {SELECT next_char('','xyzzy','a')} 109} {1 {no such table: xyzzy}} 110 111do_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} 117do_execsql_test 1.21 { 118 SELECT next_char('ab','vocab2','w',null,null); 119} {c} 120do_execsql_test 1.22 { 121 SELECT next_char('AB','vocab2','w',null,'NOCASE'); 122} {cDeF} 123do_execsql_test 1.23 { 124 SELECT next_char('ab','vocab2','w',null,'binary'); 125} {c} 126 127do_execsql_test 1.30 { 128 SELECT rowid FROM t1 WHERE word='rabbit'; 129} {2} 130do_execsql_test 1.31 { 131 UPDATE t1 SET rowid=2000 WHERE word='rabbit'; 132 SELECT rowid FROM t1 WHERE word='rabbit'; 133} {2000} 134do_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} 139do_test 1.33 { 140 catchsql {INSERT INTO t1(rowid, word) VALUES(3000,'garden');} 141} {1 {constraint failed}} 142 143do_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 150do_execsql_test 2.2 { 151 SELECT word, matchlen FROM t2 WHERE word MATCH 'skol*' LIMIT 5; 152} {school 6} 153 154set vocab { 155kangaroo kanji kappa karate keel keeled keeling keels keen keener keenest 156keenly keenness keep keeper keepers keeping keeps ken kennel kennels kept 157kerchief kerchiefs kern kernel kernels kerosene ketchup kettle 158kettles key keyboard keyboards keyed keyhole keying keynote keypad keypads keys 159keystroke keystrokes keyword keywords kick kicked kicker kickers kicking 160kickoff kicks kid kidded kiddie kidding kidnap kidnapper kidnappers kidnapping 161kidnappings kidnaps kidney kidneys kids kill killed killer killers killing 162killingly killings killjoy kills kilobit kilobits kiloblock kilobyte kilobytes 163kilogram kilograms kilohertz kilohm kilojoule kilometer kilometers kiloton 164kilovolt kilowatt kiloword kimono kin kind kinder kindergarten kindest 165kindhearted kindle kindled kindles kindling kindly kindness kindred kinds 166kinetic king kingdom kingdoms kingly kingpin kings kink kinky kinship kinsman 167kiosk kiss kissed kisser kissers kisses kissing kit kitchen kitchenette 168kitchens kite kited kites kiting kits kitten kittenish kittens kitty klaxon 169kludge kludges klystron knack knapsack knapsacks knave knaves knead kneads knee 170kneecap kneed kneeing kneel kneeled kneeling kneels knees knell knells knelt 171knew knife knifed knifes knifing knight knighted knighthood knighting knightly 172knights knit knits knives knob knobs knock knockdown knocked knocker knockers 173knocking knockout knocks knoll knolls knot knots knotted knotting know knowable 174knower knowhow knowing knowingly knowledge knowledgeable known knows knuckle 175knuckled knuckles koala kosher kudo 176} 177 178do_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 188do_test 3.2 { 189 foreach w $vocab { 190 execsql { INSERT INTO t3(word) VALUES($w) } 191 } 192} {} 193 194foreach {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 205do_execsql_test 4.0 { 206 INSERT INTO t3(command) VALUES('edit_cost_table=NULL'); 207} 208foreach {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} 218do_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 227foreach {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# 243do_execsql_test 6.1.1 { 244 SELECT word FROM t3 WHERE rowid = 10; 245} {keener} 246do_execsql_test 6.1.2 { 247 SELECT word, distance FROM t3 WHERE rowid = 10; 248} {keener {}} 249do_execsql_test 6.1.3 { 250 SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner'; 251} {keener 300} 252 253ifcapable 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, k1 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# 290do_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 302do_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} 311do_catchsql_test 7.2.2 { 312 INSERT OR ABORT INTO t4(rowid, word) VALUES(1, 'Leto'); 313} {1 {constraint failed}} 314do_catchsql_test 7.2.3 { 315 INSERT OR ROLLBACK INTO t4(rowid, word) VALUES(3, 'Zeus'); 316} {1 {constraint failed}} 317do_catchsql_test 7.2.4 { 318 INSERT OR FAIL INTO t4(rowid, word) VALUES(3, 'Zeus'); 319} {1 {constraint failed}} 320do_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 327do_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} 331do_catchsql_test 7.3.2 { 332 UPDATE OR ABORT t4 SET rowid=3 WHERE rowid=2; 333} {1 {constraint failed}} 334do_catchsql_test 7.3.3 { 335 UPDATE OR ROLLBACK t4 SET rowid=3 WHERE rowid=2; 336} {1 {constraint failed}} 337do_catchsql_test 7.3.4 { 338 UPDATE OR FAIL t4 SET rowid=3 WHERE rowid=2; 339} {1 {constraint failed}} 340do_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 345do_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 357db_save_and_close 358foreach {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 382foreach {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 405finish_test 406