AryaWu/sqlite
0
1# 2012 July 122#3# The author disclaims copyright to this source code. In place of4# 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.tcl15set testprefix spellfix16 17ifcapable !vtab { finish_test ; return }18 19load_static_extension db spellfix nextchar20 21set vocab {22rabbi rabbit rabbits rabble rabid rabies raccoon raccoons race raced racer23racers races racetrack racial racially racing rack racked racket racketeer24racketeering racketeers rackets racking racks radar radars radial radially25radian radiance radiant radiantly radiate radiated radiates radiating radiation26radiations radiator radiators radical radically radicals radices radii radio27radioactive radioastronomy radioed radiography radioing radiology radios radish28radishes radium radius radix radon raft rafter rafters rafts rag rage raged29rages ragged raggedly raggedness raging rags ragweed raid raided raider raiders30raiding raids rail railed railer railers railing railroad railroaded railroader31railroaders railroading railroads rails railway railways raiment rain rainbow32raincoat raincoats raindrop raindrops rained rainfall rainier rainiest raining33rains rainstorm rainy raise raised raiser raisers raises raisin raising rake34raked rakes raking rallied rallies rally rallying ram ramble rambler rambles35rambling ramblings ramification ramifications ramp rampage rampant rampart36ramps ramrod rams ran ranch ranched rancher ranchers ranches ranching rancid37random randomization randomize randomized randomizes randomly randomness randy38rang range ranged rangeland ranger rangers ranges ranging rangy rank ranked39ranker rankers rankest ranking rankings rankle rankly rankness ranks ransack40ransacked ransacking ransacks ransom ransomer ransoming ransoms rant ranted41ranter ranters ranting rants rap rapacious rape raped raper rapes rapid42rapidity rapidly rapids rapier raping rapport rapprochement raps rapt raptly43rapture raptures rapturous rare rarely rareness rarer rarest rarity rascal44rascally rascals rash rasher rashly rashness rasp raspberry rasped rasping45rasps raster rat rate rated rater raters rates rather ratification ratified46ratifies ratify ratifying rating ratings ratio ration rational rationale47rationales rationalities rationality rationalization rationalizations48rationalize rationalized rationalizes rationalizing rationally rationals49rationing rations ratios rats rattle rattled rattler rattlers rattles50rattlesnake rattlesnakes rattling raucous ravage ravaged ravager ravagers51ravages ravaging rave raved raven ravening ravenous ravenously ravens raves52ravine ravines raving ravings raw rawer rawest rawly rawness ray rays raze53razor razors re reabbreviate reabbreviated reabbreviates reabbreviating reach54reachability reachable reachably reached reacher reaches reaching reacquired55react reacted reacting reaction reactionaries reactionary reactions reactivate56reactivated reactivates reactivating reactivation reactive reactively57reactivity reactor reactors reacts read readability readable reader readers58readied readier readies readiest readily readiness reading readings readjusted59readout readouts reads ready readying real realest realign realigned realigning60realigns realism realist realistic realistically realists realities reality61}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 584 } $res85}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 keenest156keenly keenness keep keeper keepers keeping keeps ken kennel kennels kept157kerchief kerchiefs kern kernel kernels kerosene ketchup kettle158kettles key keyboard keyboards keyed keyhole keying keynote keypad keypads keys159keystroke keystrokes keyword keywords kick kicked kicker kickers kicking160kickoff kicks kid kidded kiddie kidding kidnap kidnapper kidnappers kidnapping161kidnappings kidnaps kidney kidneys kids kill killed killer killers killing162killingly killings killjoy kills kilobit kilobits kiloblock kilobyte kilobytes163kilogram kilograms kilohertz kilohm kilojoule kilometer kilometers kiloton164kilovolt kilowatt kiloword kimono kin kind kinder kindergarten kindest165kindhearted kindle kindled kindles kindling kindly kindness kindred kinds166kinetic king kingdom kingdoms kingly kingpin kings kink kinky kinship kinsman167kiosk kiss kissed kisser kissers kisses kissing kit kitchen kitchenette168kitchens kite kited kites kiting kits kitten kittenish kittens kitty klaxon169kludge kludges klystron knack knapsack knapsacks knave knaves knead kneads knee170kneecap kneed kneeing kneel kneeled kneeling kneels knees knell knells knelt171knew knife knifed knifes knifing knight knighted knighthood knighting knightly172knights knit knits knives knob knobs knock knockdown knocked knocker knockers173knocking knockout knocks knoll knolls knot knots knotted knotting know knowable174knower knowhow knowing knowingly knowledge knowledgeable known knows knuckle175knuckled knuckles koala kosher kudo176}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 $word201 ORDER BY score, word LIMIT 5202 } $res203}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 $word215 ORDER BY score, word LIMIT 5216 } $res217}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 $word236 ORDER BY score, word LIMIT 1237 } $res238}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_callback269 do_tracesql_test 6.2.1 {270 SELECT word FROM t3 WHERE rowid = 10;271 } {keener272 {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 300282 {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#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 {} 0300}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 Atrides310}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 Atrides325}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_close358foreach {tn conflict err bRollback res} {359 0 "" {1 {constraint failed}} 0360 {10 Agamemnon 20 Patroclus 30 Chryses}361 1 "OR REPLACE" {0 {}} 0362 {5 Poseidon 10 Agamemnon 20 Chronos 30 Hera}363 2 "OR ABORT" {1 {constraint failed}} 0364 {10 Agamemnon 20 Patroclus 30 Chryses}365 3 "OR ROLLBACK" {1 {constraint failed}} 1366 {10 Agamemnon 20 Patroclus 30 Chryses}367 5 "OR IGNORE" {0 {}} 0368 {5 Poseidon 10 Agamemnon 20 Patroclus 30 Chryses}369} {370 db_restore_and_reopen371 load_static_extension db spellfix nextchar372 373 execsql BEGIN374 set sql "INSERT $conflict INTO t4(rowid, word) SELECT i, w FROM t5"375 do_catchsql_test 7.4.2.$tn.1 $sql $err376 do_execsql_test 7.4.2.$tn.2 { SELECT rowid, word FROM t4 } $res377 378 do_test 7.4.2.$tn.3 { sqlite3_get_autocommit db } $bRollback379 catchsql ROLLBACK380}381 382foreach {tn conflict err bRollback res} {383 0 "" {1 {constraint failed}} 0384 {10 Agamemnon 20 Patroclus 30 Chryses}385 1 "OR REPLACE" {0 {}} 0386 {15 Agamemnon 45 Chryses}387 2 "OR ABORT" {1 {constraint failed}} 0388 {10 Agamemnon 20 Patroclus 30 Chryses}389 3 "OR ROLLBACK" {1 {constraint failed}} 1390 {10 Agamemnon 20 Patroclus 30 Chryses}391 5 "OR IGNORE" {0 {}} 0392 {15 Agamemnon 20 Patroclus 45 Chryses}393} {394 db_restore_and_reopen395 load_static_extension db spellfix nextchar396 397 execsql BEGIN398 set sql "UPDATE $conflict t4 SET rowid=rowid + (rowid/2)"399 do_catchsql_test 7.5.2.$tn.1 $sql $err400 do_execsql_test 7.5.2.$tn.2 { SELECT rowid, word FROM t4 } $res401 do_test 7.5.2.$tn.3 { sqlite3_get_autocommit db } $bRollback402 catchsql ROLLBACK403}404 405finish_test406