Debian Codegraph @

test/fts4growth.test

raw
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 sqlite3_db_config db DEFENSIVE 0
29 do_execsql_test 1.1 { CREATE VIRTUAL TABLE x1 USING fts3; }
30
31 do_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
43 do_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
48 do_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
65 do_execsql_test 1.5 {
66 SELECT length(block) FROM x1_segments;
67 } {921 {}}
68
69 do_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
99 do_execsql_test 1.7 {
100 SELECT sum(length(block)) FROM x1_segments WHERE blockid IN (224,225,226)
101 } {1230}
102
103 #-------------------------------------------------------------------------
104 #
105 do_execsql_test 2.1 {
106 CREATE TABLE t1(docid, words);
107 CREATE VIRTUAL TABLE x2 USING fts4;
108 }
109 fts_kjv_genesis
110 do_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=$id
114 }
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=$id
119 }
120 if {[db one {SELECT count(*) FROM x2_segdir WHERE level<2}]==2} break
121 }
122 } {}
123
124 do_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
129 do_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
135 do_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
143 do_execsql_test 2.6 {
144 SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE
145 blockid BETWEEN start_block AND leaves_end_block
146 AND level=3
147 } {15541}
148
149 do_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
154 do_execsql_test 2.8 {
155 SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE
156 blockid BETWEEN start_block AND leaves_end_block
157 AND level=3
158 } {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 the
163 # 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
170 proc 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
187 proc delete_doc {args} {
188 foreach iDoc $args {
189 execsql { DELETE FROM x3 WHERE docid = $iDoc }
190 }
191 }
192
193 proc second {x} { lindex $x 1 }
194 db func second second
195
196 do_execsql_test 3.0 { CREATE VIRTUAL TABLE x3 USING fts4 }
197
198 do_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}
202 do_test 3.1.2 {
203 delete_doc 1 2 3 4 5 6
204 execsql { SELECT count(*) FROM x3_segdir }
205 } {0}
206 do_test 3.1.3 {
207 db transaction {
208 insert_doc 1 2 3 4 5 6 7 8 9
209 delete_doc 9 8 7
210 }
211 execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
212 } {0 0 591 0 1 65 0 2 72 0 3 76}
213 do_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
218 do_test 3.2.1 {
219 execsql { DELETE FROM x3 }
220 insert_doc 8 7 6 5 4 3 2 1
221 delete_doc 7 8
222 execsql { SELECT count(*) FROM x3_segdir }
223 } {10}
224 do_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 #
232 do_test 3.3.1 {
233 execsql { DELETE FROM x3 }
234 insert_doc 1 2 3 4 5 6 7 8 9 10 11
235 delete_doc 11 10 9 8 7
236 execsql { SELECT count(*) FROM x3_segdir }
237 } {16}
238
239 do_test 3.3.2 {
240 insert_doc 12
241 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 #
248 do_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 }
254 do_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
260 do_execsql_test 4.3 {
261 SELECT level, idx, second(end_block) FROM x4_segdir
262 } {0 0 117483 0 1 118006}
263
264 do_execsql_test 4.4 {
265 INSERT INTO x4(x4) VALUES('merge=10,2');
266 SELECT count(*) FROM x4_segdir;
267 } {3}
268
269 do_execsql_test 4.5 {
270 INSERT INTO x4(x4) VALUES('merge=10,2');
271 SELECT count(*) FROM x4_segdir;
272 } {3}
273
274 do_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 not
283 # contain a size.
284 #
285 do_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 }
291 fts_kjv_genesis
292
293 proc first {L} {lindex $L 0}
294 db func first first
295
296 do_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=$r
300 }
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_segdir
309 }
310 } {2 0 {752 1926}}
311
312 do_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
317 do_execsql_test 5.4 {
318 INSERT INTO x2 SELECT words FROM t1 LIMIT 50;
319 SELECT level, idx, end_block FROM x2_segdir
320 } {2 0 752 0 0 {758 5174}}
321
322 do_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_segdir
326 } {0 0 {752 1926} 0 1 {758 5174} 0 2 {763 4170}}
327
328 proc 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 they
339 # are completed.
340 #
341
342 do_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_segdir
349 } {4}
350
351 do_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
356 do_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
364 do_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
369 do_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 old
378 # version that does not support storing segment sizes in the end_block
379 # field, no size is stored in the final segment (as it would be incorrect).
380 #
381 do_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
395 do_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
404 do_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 23694
411 }
412
413 do_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 23694
420 }
421
422 do_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 23694
427 }
428
429 do_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 23694
434 }
435
436 do_execsql_test 7.7 {
437 SELECT sum(length(block)) FROM x6_segments
438 } {635247}
439
440
441 finish_test