AryaWu/sqlite
0
1# 2014 May 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# This file implements regression tests for SQLite library. The12# focus of this script is testing the FTS4 module.13#14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix fts4growth19 20# If SQLITE_ENABLE_FTS3 is defined, omit this file.21ifcapable !fts3 {22 finish_test23 return24}25 26source $testdir/genesis.tcl27 28sqlite3_db_config db DEFENSIVE 029do_execsql_test 1.1 { CREATE VIRTUAL TABLE x1 USING fts3; }30 31do_test 1.2 {32 foreach L {33 {"See here, young man," said Mulga Bill, "from Walgett to the sea,}34 {From Conroy's Gap to Castlereagh, there's none can ride like me.}35 {I'm good all round at everything as everybody knows,}36 {Although I'm not the one to talk -- I hate a man that blows.}37 } {38 execsql { INSERT INTO x1 VALUES($L) }39 }40 execsql { SELECT end_block, length(root) FROM x1_segdir }41} {{0 114} 114 {0 118} 118 {0 95} 95 {0 115} 115}42 43do_execsql_test 1.3 {44 INSERT INTO x1(x1) VALUES('optimize');45 SELECT level, end_block, length(root) FROM x1_segdir;46} {0 {0 394} 394}47 48do_test 1.4 {49 foreach L {50 {But riding is my special gift, my chiefest, sole delight;}51 {Just ask a wild duck can it swim, a wildcat can it fight.}52 {There's nothing clothed in hair or hide, or built of flesh or steel,}53 {There's nothing walks or jumps, or runs, on axle, hoof, or wheel,}54 {But what I'll sit, while hide will hold and girths and straps are tight:}55 {I'll ride this here two-wheeled concern right straight away at sight."}56 } {57 execsql { INSERT INTO x1 VALUES($L) }58 }59 execsql { 60 INSERT INTO x1(x1) VALUES('merge=4,4');61 SELECT level, end_block, length(root) FROM x1_segdir;62 }63} {1 {224 921} 2}64 65do_execsql_test 1.5 {66 SELECT length(block) FROM x1_segments;67} {921 {}}68 69do_test 1.6 {70 foreach L {71 {'Twas Mulga Bill, from Eaglehawk, that sought his own abode,}72 {That perched above Dead Man's Creek, beside the mountain road.}73 {He turned the cycle down the hill and mounted for the fray,}74 {But 'ere he'd gone a dozen yards it bolted clean away.}75 76 {It left the track, and through the trees, just like a silver steak,}77 {It whistled down the awful slope towards the Dead Man's Creek.}78 {It shaved a stump by half an inch, it dodged a big white-box:}79 {The very wallaroos in fright went scrambling up the rocks,}80 81 {The wombats hiding in their caves dug deeper underground,}82 {As Mulga Bill, as white as chalk, sat tight to every bound.}83 {It struck a stone and gave a spring that cleared a fallen tree,}84 {It raced beside a precipice as close as close could be;}85 86 {And then as Mulga Bill let out one last despairing shriek}87 {It made a leap of twenty feet into the Dead Man's Creek.}88 {It shaved a stump by half an inch, it dodged a big white-box:}89 {The very wallaroos in fright went scrambling up the rocks,}90 {The wombats hiding in their caves dug deeper underground,}91 } {92 execsql { INSERT INTO x1 VALUES($L) }93 }94 execsql { 95 SELECT level, end_block, length(root) FROM x1_segdir;96 }97} {1 {224 921} 2 1 {226 1230} 7 0 {0 98} 98}98 99do_execsql_test 1.7 {100 SELECT sum(length(block)) FROM x1_segments WHERE blockid IN (224,225,226)101} {1230}102 103#-------------------------------------------------------------------------104#105do_execsql_test 2.1 { 106 CREATE TABLE t1(docid, words);107 CREATE VIRTUAL TABLE x2 USING fts4;108}109fts_kjv_genesis 110do_test 2.2 {111 foreach id [db eval {SELECT docid FROM t1}] {112 execsql {113 INSERT INTO x2(docid, content) SELECT $id, words FROM t1 WHERE docid=$id114 }115 }116 foreach id [db eval {SELECT docid FROM t1}] {117 execsql {118 INSERT INTO x2(docid, content) SELECT NULL, words FROM t1 WHERE docid=$id119 }120 if {[db one {SELECT count(*) FROM x2_segdir WHERE level<2}]==2} break121 }122} {}123 124do_execsql_test 2.3 { 125 SELECT count(*) FROM x2_segdir WHERE level=2;126 SELECT count(*) FROM x2_segdir WHERE level=3;127} {6 0}128 129do_execsql_test 2.4 { 130 INSERT INTO x2(x2) VALUES('merge=4,4');131 SELECT count(*) FROM x2_segdir WHERE level=2;132 SELECT count(*) FROM x2_segdir WHERE level=3;133} {6 1}134 135do_execsql_test 2.5 { 136 SELECT end_block FROM x2_segdir WHERE level=3;137 INSERT INTO x2(x2) VALUES('merge=4,4');138 SELECT end_block FROM x2_segdir WHERE level=3;139 INSERT INTO x2(x2) VALUES('merge=4,4');140 SELECT end_block FROM x2_segdir WHERE level=3;141} {{5588 -3950} {5588 -11766} {5588 -15541}}142 143do_execsql_test 2.6 {144 SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE 145 blockid BETWEEN start_block AND leaves_end_block146 AND level=3147} {15541}148 149do_execsql_test 2.7 { 150 INSERT INTO x2(x2) VALUES('merge=1000,4');151 SELECT end_block FROM x2_segdir WHERE level=3;152} {{5588 127563}}153 154do_execsql_test 2.8 {155 SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE 156 blockid BETWEEN start_block AND leaves_end_block157 AND level=3158} {127563}159 160#--------------------------------------------------------------------------161# Test that delete markers are removed from FTS segments when possible.162# It is only possible to remove delete markers when the output of the163# merge operation will become the oldest segment in the index.164#165# 3.1 - when the oldest segment is created by an 'optimize'.166# 3.2 - when the oldest segment is created by an incremental merge.167# 3.3 - by a crisis merge.168#169 170proc insert_doc {args} {171 foreach iDoc $args {172 set L [lindex {173 {In your eagerness to engage the Trojans,}174 {don’t any of you charge ahead of others,}175 {trusting in your strength and horsemanship.}176 {And don’t lag behind. That will hurt our charge.}177 {Any man whose chariot confronts an enemy’s}178 {should thrust with his spear at him from there.}179 {That’s the most effective tactic, the way}180 {men wiped out city strongholds long ago —}181 {their chests full of that style and spirit.}182 } [expr $iDoc%9]]183 execsql { REPLACE INTO x3(docid, content) VALUES($iDoc, $L) }184 }185}186 187proc delete_doc {args} {188 foreach iDoc $args {189 execsql { DELETE FROM x3 WHERE docid = $iDoc }190 }191}192 193proc second {x} { lindex $x 1 }194db func second second195 196do_execsql_test 3.0 { CREATE VIRTUAL TABLE x3 USING fts4 }197 198do_test 3.1.1 {199 db transaction { insert_doc 1 2 3 4 5 6 }200 execsql { SELECT level, idx, second(end_block) FROM x3_segdir }201} {0 0 412}202do_test 3.1.2 {203 delete_doc 1 2 3 4 5 6204 execsql { SELECT count(*) FROM x3_segdir }205} {0}206do_test 3.1.3 {207 db transaction { 208 insert_doc 1 2 3 4 5 6 7 8 9209 delete_doc 9 8 7210 }211 execsql { SELECT level, idx, second(end_block) FROM x3_segdir }212} {0 0 591 0 1 65 0 2 72 0 3 76}213do_test 3.1.4 {214 execsql { INSERT INTO x3(x3) VALUES('optimize') }215 execsql { SELECT level, idx, second(end_block) FROM x3_segdir }216} {0 0 412}217 218do_test 3.2.1 {219 execsql { DELETE FROM x3 }220 insert_doc 8 7 6 5 4 3 2 1221 delete_doc 7 8222 execsql { SELECT count(*) FROM x3_segdir }223} {10}224do_test 3.2.2 {225 execsql { INSERT INTO x3(x3) VALUES('merge=500,10') }226 execsql { SELECT level, idx, second(end_block) FROM x3_segdir }227} {1 0 412}228 229# This assumes the crisis merge happens when there are already 16 230# segments and one more is added.231#232do_test 3.3.1 {233 execsql { DELETE FROM x3 }234 insert_doc 1 2 3 4 5 6 7 8 9 10 11235 delete_doc 11 10 9 8 7236 execsql { SELECT count(*) FROM x3_segdir }237} {16}238 239do_test 3.3.2 {240 insert_doc 12241 execsql { SELECT level, idx, second(end_block) FROM x3_segdir WHERE level=1 }242} {1 0 412}243 244#--------------------------------------------------------------------------245# Check a theory on a bug in fts4 - that segments with idx==0 were not 246# being incrementally merged correctly. Theory turned out to be false.247#248do_execsql_test 4.1 {249 DROP TABLE IF EXISTS x4;250 DROP TABLE IF EXISTS t1;251 CREATE TABLE t1(docid, words);252 CREATE VIRTUAL TABLE x4 USING fts4(words);253}254do_test 4.2 {255 fts_kjv_genesis 256 execsql { INSERT INTO x4 SELECT words FROM t1 }257 execsql { INSERT INTO x4 SELECT words FROM t1 }258} {}259 260do_execsql_test 4.3 {261 SELECT level, idx, second(end_block) FROM x4_segdir 262} {0 0 117483 0 1 118006}263 264do_execsql_test 4.4 {265 INSERT INTO x4(x4) VALUES('merge=10,2');266 SELECT count(*) FROM x4_segdir;267} {3}268 269do_execsql_test 4.5 {270 INSERT INTO x4(x4) VALUES('merge=10,2');271 SELECT count(*) FROM x4_segdir;272} {3}273 274do_execsql_test 4.6 {275 INSERT INTO x4(x4) VALUES('merge=1000,2');276 SELECT count(*) FROM x4_segdir;277} {1}278 279 280 281#--------------------------------------------------------------------------282# Check that segments are not promoted if the "end_block" field does not283# contain a size.284#285do_execsql_test 5.1 {286 DROP TABLE IF EXISTS x2;287 DROP TABLE IF EXISTS t1;288 CREATE TABLE t1(docid, words);289 CREATE VIRTUAL TABLE x2 USING fts4;290}291fts_kjv_genesis 292 293proc first {L} {lindex $L 0}294db func first first295 296do_test 5.2 {297 foreach r [db eval { SELECT rowid FROM t1 }] {298 execsql {299 INSERT INTO x2(docid, content) SELECT docid, words FROM t1 WHERE rowid=$r300 }301 }302 foreach d [db eval { SELECT docid FROM t1 LIMIT -1 OFFSET 20 }] {303 execsql { DELETE FROM x2 WHERE docid = $d }304 }305 306 execsql {307 INSERT INTO x2(x2) VALUES('optimize');308 SELECT level, idx, end_block FROM x2_segdir309 }310} {2 0 {752 1926}}311 312do_execsql_test 5.3 {313 UPDATE x2_segdir SET end_block = CAST( first(end_block) AS INTEGER );314 SELECT end_block, typeof(end_block) FROM x2_segdir;315} {752 integer}316 317do_execsql_test 5.4 {318 INSERT INTO x2 SELECT words FROM t1 LIMIT 50;319 SELECT level, idx, end_block FROM x2_segdir320} {2 0 752 0 0 {758 5174}}321 322do_execsql_test 5.5 {323 UPDATE x2_segdir SET end_block = end_block || ' 1926' WHERE level=2;324 INSERT INTO x2 SELECT words FROM t1 LIMIT 40;325 SELECT level, idx, end_block FROM x2_segdir326} {0 0 {752 1926} 0 1 {758 5174} 0 2 {763 4170}}327 328proc t1_to_x2 {} {329 foreach id [db eval {SELECT docid FROM t1 LIMIT 2}] {330 execsql {331 DELETE FROM x2 WHERE docid=$id;332 INSERT INTO x2(docid, content) SELECT $id, words FROM t1 WHERE docid=$id;333 }334 }335}336 337#--------------------------------------------------------------------------338# Check that segments created by auto-merge are not promoted until they339# are completed.340#341 342do_execsql_test 6.1 {343 CREATE VIRTUAL TABLE x5 USING fts4;344 INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 0;345 INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 25;346 INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 50;347 INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 75;348 SELECT count(*) FROM x5_segdir349} {4}350 351do_execsql_test 6.2 {352 INSERT INTO x5(x5) VALUES('merge=2,4');353 SELECT level, idx, end_block FROM x5_segdir;354} {0 0 {10 9216} 0 1 {21 9330} 0 2 {31 8850} 0 3 {40 8689} 1 0 {1320 -3117}}355 356do_execsql_test 6.3 {357 INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 100;358 SELECT level, idx, end_block FROM x5_segdir;359} {360 0 0 {10 9216} 0 1 {21 9330} 0 2 {31 8850} 361 0 3 {40 8689} 1 0 {1320 -3117} 0 4 {1329 8297}362}363 364do_execsql_test 6.4 {365 INSERT INTO x5(x5) VALUES('merge=200,4');366 SELECT level, idx, end_block FROM x5_segdir;367} {0 0 {1329 8297} 1 0 {1320 28009}}368 369do_execsql_test 6.5 {370 INSERT INTO x5 SELECT words FROM t1;371 SELECT level, idx, end_block FROM x5_segdir;372} {373 0 1 {1329 8297} 0 0 {1320 28009} 0 2 {1449 118006}374}375 376#--------------------------------------------------------------------------377# Ensure that if part of an incremental merge is performed by an old378# version that does not support storing segment sizes in the end_block379# field, no size is stored in the final segment (as it would be incorrect).380#381do_execsql_test 7.1 {382 CREATE VIRTUAL TABLE x6 USING fts4;383 INSERT INTO x6 SELECT words FROM t1;384 INSERT INTO x6 SELECT words FROM t1;385 INSERT INTO x6 SELECT words FROM t1;386 INSERT INTO x6 SELECT words FROM t1;387 INSERT INTO x6 SELECT words FROM t1;388 INSERT INTO x6 SELECT words FROM t1;389 SELECT level, idx, end_block FROM x6_segdir;390} {391 0 0 {118 117483} 0 1 {238 118006} 0 2 {358 118006} 392 0 3 {478 118006} 0 4 {598 118006} 0 5 {718 118006}393}394 395do_execsql_test 7.2 {396 INSERT INTO x6(x6) VALUES('merge=25,4');397 SELECT level, idx, end_block FROM x6_segdir;398} {399 0 0 {118 117483} 0 1 {238 118006} 0 2 {358 118006} 400 0 3 {478 118006} 0 4 {598 118006} 0 5 {718 118006}401 1 0 {23694 -69477}402}403 404do_execsql_test 7.3 {405 UPDATE x6_segdir SET end_block = first(end_block) WHERE level=1;406 SELECT level, idx, end_block FROM x6_segdir;407} {408 0 0 {118 117483} 0 1 {238 118006} 0 2 {358 118006} 409 0 3 {478 118006} 0 4 {598 118006} 0 5 {718 118006}410 1 0 23694411}412 413do_execsql_test 7.4 {414 INSERT INTO x6(x6) VALUES('merge=25,4');415 SELECT level, idx, end_block FROM x6_segdir;416} {417 0 0 {118 117483} 0 1 {238 118006} 0 2 {358 118006} 418 0 3 {478 118006} 0 4 {598 118006} 0 5 {718 118006}419 1 0 23694420}421 422do_execsql_test 7.5 {423 INSERT INTO x6(x6) VALUES('merge=2500,4');424 SELECT level, idx, start_block, leaves_end_block, end_block FROM x6_segdir;425} {426 1 0 719 1171 23694427}428 429do_execsql_test 7.6 {430 INSERT INTO x6(x6) VALUES('merge=2500,2');431 SELECT level, idx, start_block, leaves_end_block, end_block FROM x6_segdir;432} {433 1 0 719 1171 23694434}435 436do_execsql_test 7.7 {437 SELECT sum(length(block)) FROM x6_segments 438} {635247}439 440 441finish_test442