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