|
1 # 2008 June 26 |
|
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 focus |
|
12 # of this script is testing the FTS3 module's optimize() function. |
|
13 # |
|
14 # $Id: fts3d.test,v 1.2 2008/07/15 21:32:07 shess Exp $ |
|
15 # |
|
16 |
|
17 set testdir [file dirname $argv0] |
|
18 source $testdir/tester.tcl |
|
19 |
|
20 # If SQLITE_ENABLE_FTS3 is not defined, omit this file. |
|
21 ifcapable !fts3 { |
|
22 finish_test |
|
23 return |
|
24 } |
|
25 |
|
26 #************************************************************************* |
|
27 # Probe to see if support for the FTS3 dump_* functions is compiled in. |
|
28 # TODO(shess): Change main.mk to do the right thing and remove this test. |
|
29 db eval { |
|
30 DROP TABLE IF EXISTS t1; |
|
31 CREATE VIRTUAL TABLE t1 USING fts3(c); |
|
32 INSERT INTO t1 (docid, c) VALUES (1, 'x'); |
|
33 } |
|
34 |
|
35 set s {SELECT dump_terms(t1, 1) FROM t1 LIMIT 1} |
|
36 set r {1 {unable to use function dump_terms in the requested context}} |
|
37 if {[catchsql $s]==$r} { |
|
38 finish_test |
|
39 return |
|
40 } |
|
41 |
|
42 #************************************************************************* |
|
43 # Utility function to check for the expected terms in the segment |
|
44 # level/index. _all version does same but for entire index. |
|
45 proc check_terms {test level index terms} { |
|
46 # TODO(shess): Figure out why uplevel in do_test can't catch |
|
47 # $level and $index directly. |
|
48 set ::level $level |
|
49 set ::index $index |
|
50 do_test $test.terms { |
|
51 execsql { |
|
52 SELECT dump_terms(t1, $::level, $::index) FROM t1 LIMIT 1; |
|
53 } |
|
54 } [list $terms] |
|
55 } |
|
56 proc check_terms_all {test terms} { |
|
57 do_test $test.terms { |
|
58 execsql { |
|
59 SELECT dump_terms(t1) FROM t1 LIMIT 1; |
|
60 } |
|
61 } [list $terms] |
|
62 } |
|
63 |
|
64 # Utility function to check for the expected doclist for the term in |
|
65 # segment level/index. _all version does same for entire index. |
|
66 proc check_doclist {test level index term doclist} { |
|
67 # TODO(shess): Again, why can't the non-:: versions work? |
|
68 set ::term $term |
|
69 set ::level $level |
|
70 set ::index $index |
|
71 do_test $test { |
|
72 execsql { |
|
73 SELECT dump_doclist(t1, $::term, $::level, $::index) FROM t1 LIMIT 1; |
|
74 } |
|
75 } [list $doclist] |
|
76 } |
|
77 proc check_doclist_all {test term doclist} { |
|
78 set ::term $term |
|
79 do_test $test { |
|
80 execsql { |
|
81 SELECT dump_doclist(t1, $::term) FROM t1 LIMIT 1; |
|
82 } |
|
83 } [list $doclist] |
|
84 } |
|
85 |
|
86 #************************************************************************* |
|
87 # Test results when all rows are deleted and one is added back. |
|
88 # Previously older segments would continue to exist, but now the index |
|
89 # should be dropped when the table is empty. The results should look |
|
90 # exactly like we never added the earlier rows in the first place. |
|
91 db eval { |
|
92 DROP TABLE IF EXISTS t1; |
|
93 CREATE VIRTUAL TABLE t1 USING fts3(c); |
|
94 INSERT INTO t1 (docid, c) VALUES (1, 'This is a test'); |
|
95 INSERT INTO t1 (docid, c) VALUES (2, 'That was a test'); |
|
96 INSERT INTO t1 (docid, c) VALUES (3, 'This is a test'); |
|
97 DELETE FROM t1 WHERE 1=1; -- Delete each row rather than dropping table. |
|
98 INSERT INTO t1 (docid, c) VALUES (1, 'This is a test'); |
|
99 } |
|
100 |
|
101 # Should be a single initial segment. |
|
102 do_test fts3d-1.segments { |
|
103 execsql { |
|
104 SELECT level, idx FROM t1_segdir ORDER BY level, idx; |
|
105 } |
|
106 } {0 0} |
|
107 do_test fts3d-1.matches { |
|
108 execsql { |
|
109 SELECT OFFSETS(t1) FROM t1 |
|
110 WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid; |
|
111 } |
|
112 } {{0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}} |
|
113 |
|
114 check_terms_all fts3d-1.1 {a is test this} |
|
115 check_doclist_all fts3d-1.1.1 a {[1 0[2]]} |
|
116 check_doclist_all fts3d-1.1.2 is {[1 0[1]]} |
|
117 check_doclist_all fts3d-1.1.3 test {[1 0[3]]} |
|
118 check_doclist_all fts3d-1.1.4 this {[1 0[0]]} |
|
119 |
|
120 check_terms fts3d-1.2 0 0 {a is test this} |
|
121 check_doclist fts3d-1.2.1 0 0 a {[1 0[2]]} |
|
122 check_doclist fts3d-1.2.2 0 0 is {[1 0[1]]} |
|
123 check_doclist fts3d-1.2.3 0 0 test {[1 0[3]]} |
|
124 check_doclist fts3d-1.2.4 0 0 this {[1 0[0]]} |
|
125 |
|
126 #************************************************************************* |
|
127 # Test results when everything is optimized manually. |
|
128 # NOTE(shess): This is a copy of fts3c-1.3. I've pulled a copy here |
|
129 # because fts3d-2 and fts3d-3 should have identical results. |
|
130 db eval { |
|
131 DROP TABLE IF EXISTS t1; |
|
132 CREATE VIRTUAL TABLE t1 USING fts3(c); |
|
133 INSERT INTO t1 (docid, c) VALUES (1, 'This is a test'); |
|
134 INSERT INTO t1 (docid, c) VALUES (2, 'That was a test'); |
|
135 INSERT INTO t1 (docid, c) VALUES (3, 'This is a test'); |
|
136 DELETE FROM t1 WHERE docid IN (1,3); |
|
137 DROP TABLE IF EXISTS t1old; |
|
138 ALTER TABLE t1 RENAME TO t1old; |
|
139 CREATE VIRTUAL TABLE t1 USING fts3(c); |
|
140 INSERT INTO t1 (docid, c) SELECT docid, c FROM t1old; |
|
141 DROP TABLE t1old; |
|
142 } |
|
143 |
|
144 # Should be a single optimal segment with the same logical results. |
|
145 do_test fts3d-2.segments { |
|
146 execsql { |
|
147 SELECT level, idx FROM t1_segdir ORDER BY level, idx; |
|
148 } |
|
149 } {0 0} |
|
150 do_test fts3d-2.matches { |
|
151 execsql { |
|
152 SELECT OFFSETS(t1) FROM t1 |
|
153 WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid; |
|
154 } |
|
155 } {{0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4}} |
|
156 |
|
157 check_terms_all fts3d-2.1 {a test that was} |
|
158 check_doclist_all fts3d-2.1.1 a {[2 0[2]]} |
|
159 check_doclist_all fts3d-2.1.2 test {[2 0[3]]} |
|
160 check_doclist_all fts3d-2.1.3 that {[2 0[0]]} |
|
161 check_doclist_all fts3d-2.1.4 was {[2 0[1]]} |
|
162 |
|
163 check_terms fts3d-2.2 0 0 {a test that was} |
|
164 check_doclist fts3d-2.2.1 0 0 a {[2 0[2]]} |
|
165 check_doclist fts3d-2.2.2 0 0 test {[2 0[3]]} |
|
166 check_doclist fts3d-2.2.3 0 0 that {[2 0[0]]} |
|
167 check_doclist fts3d-2.2.4 0 0 was {[2 0[1]]} |
|
168 |
|
169 #************************************************************************* |
|
170 # Test results when everything is optimized via optimize(). |
|
171 db eval { |
|
172 DROP TABLE IF EXISTS t1; |
|
173 CREATE VIRTUAL TABLE t1 USING fts3(c); |
|
174 INSERT INTO t1 (docid, c) VALUES (1, 'This is a test'); |
|
175 INSERT INTO t1 (docid, c) VALUES (2, 'That was a test'); |
|
176 INSERT INTO t1 (docid, c) VALUES (3, 'This is a test'); |
|
177 DELETE FROM t1 WHERE docid IN (1,3); |
|
178 SELECT OPTIMIZE(t1) FROM t1 LIMIT 1; |
|
179 } |
|
180 |
|
181 # Should be a single optimal segment with the same logical results. |
|
182 do_test fts3d-3.segments { |
|
183 execsql { |
|
184 SELECT level, idx FROM t1_segdir ORDER BY level, idx; |
|
185 } |
|
186 } {0 0} |
|
187 do_test fts3d-3.matches { |
|
188 execsql { |
|
189 SELECT OFFSETS(t1) FROM t1 |
|
190 WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid; |
|
191 } |
|
192 } {{0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4}} |
|
193 |
|
194 check_terms_all fts3d-3.1 {a test that was} |
|
195 check_doclist_all fts3d-3.1.1 a {[2 0[2]]} |
|
196 check_doclist_all fts3d-3.1.2 test {[2 0[3]]} |
|
197 check_doclist_all fts3d-3.1.3 that {[2 0[0]]} |
|
198 check_doclist_all fts3d-3.1.4 was {[2 0[1]]} |
|
199 |
|
200 check_terms fts3d-3.2 0 0 {a test that was} |
|
201 check_doclist fts3d-3.2.1 0 0 a {[2 0[2]]} |
|
202 check_doclist fts3d-3.2.2 0 0 test {[2 0[3]]} |
|
203 check_doclist fts3d-3.2.3 0 0 that {[2 0[0]]} |
|
204 check_doclist fts3d-3.2.4 0 0 was {[2 0[1]]} |
|
205 |
|
206 #************************************************************************* |
|
207 # Test optimize() against a table involving segment merges. |
|
208 # NOTE(shess): Since there's no transaction, each of the INSERT/UPDATE |
|
209 # statements generates a segment. |
|
210 db eval { |
|
211 DROP TABLE IF EXISTS t1; |
|
212 CREATE VIRTUAL TABLE t1 USING fts3(c); |
|
213 |
|
214 INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test'); |
|
215 INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test'); |
|
216 INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test'); |
|
217 |
|
218 UPDATE t1 SET c = 'This is a test one' WHERE rowid = 1; |
|
219 UPDATE t1 SET c = 'That was a test one' WHERE rowid = 2; |
|
220 UPDATE t1 SET c = 'This is a test one' WHERE rowid = 3; |
|
221 |
|
222 UPDATE t1 SET c = 'This is a test two' WHERE rowid = 1; |
|
223 UPDATE t1 SET c = 'That was a test two' WHERE rowid = 2; |
|
224 UPDATE t1 SET c = 'This is a test two' WHERE rowid = 3; |
|
225 |
|
226 UPDATE t1 SET c = 'This is a test three' WHERE rowid = 1; |
|
227 UPDATE t1 SET c = 'That was a test three' WHERE rowid = 2; |
|
228 UPDATE t1 SET c = 'This is a test three' WHERE rowid = 3; |
|
229 |
|
230 UPDATE t1 SET c = 'This is a test four' WHERE rowid = 1; |
|
231 UPDATE t1 SET c = 'That was a test four' WHERE rowid = 2; |
|
232 UPDATE t1 SET c = 'This is a test four' WHERE rowid = 3; |
|
233 |
|
234 UPDATE t1 SET c = 'This is a test' WHERE rowid = 1; |
|
235 UPDATE t1 SET c = 'That was a test' WHERE rowid = 2; |
|
236 UPDATE t1 SET c = 'This is a test' WHERE rowid = 3; |
|
237 } |
|
238 |
|
239 # 2 segments in level 0, 1 in level 1 (18 segments created, 16 |
|
240 # merged). |
|
241 do_test fts3d-4.segments { |
|
242 execsql { |
|
243 SELECT level, idx FROM t1_segdir ORDER BY level, idx; |
|
244 } |
|
245 } {0 0 0 1 1 0} |
|
246 |
|
247 do_test fts3d-4.matches { |
|
248 execsql { |
|
249 SELECT OFFSETS(t1) FROM t1 |
|
250 WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid; |
|
251 } |
|
252 } [list {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4} \ |
|
253 {0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4} \ |
|
254 {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}] |
|
255 |
|
256 check_terms_all fts3d-4.1 {a four is one test that this three two was} |
|
257 check_doclist_all fts3d-4.1.1 a {[1 0[2]] [2 0[2]] [3 0[2]]} |
|
258 check_doclist_all fts3d-4.1.2 four {} |
|
259 check_doclist_all fts3d-4.1.3 is {[1 0[1]] [3 0[1]]} |
|
260 check_doclist_all fts3d-4.1.4 one {} |
|
261 check_doclist_all fts3d-4.1.5 test {[1 0[3]] [2 0[3]] [3 0[3]]} |
|
262 check_doclist_all fts3d-4.1.6 that {[2 0[0]]} |
|
263 check_doclist_all fts3d-4.1.7 this {[1 0[0]] [3 0[0]]} |
|
264 check_doclist_all fts3d-4.1.8 three {} |
|
265 check_doclist_all fts3d-4.1.9 two {} |
|
266 check_doclist_all fts3d-4.1.10 was {[2 0[1]]} |
|
267 |
|
268 check_terms fts3d-4.2 0 0 {a four test that was} |
|
269 check_doclist fts3d-4.2.1 0 0 a {[2 0[2]]} |
|
270 check_doclist fts3d-4.2.2 0 0 four {[2]} |
|
271 check_doclist fts3d-4.2.3 0 0 test {[2 0[3]]} |
|
272 check_doclist fts3d-4.2.4 0 0 that {[2 0[0]]} |
|
273 check_doclist fts3d-4.2.5 0 0 was {[2 0[1]]} |
|
274 |
|
275 check_terms fts3d-4.3 0 1 {a four is test this} |
|
276 check_doclist fts3d-4.3.1 0 1 a {[3 0[2]]} |
|
277 check_doclist fts3d-4.3.2 0 1 four {[3]} |
|
278 check_doclist fts3d-4.3.3 0 1 is {[3 0[1]]} |
|
279 check_doclist fts3d-4.3.4 0 1 test {[3 0[3]]} |
|
280 check_doclist fts3d-4.3.5 0 1 this {[3 0[0]]} |
|
281 |
|
282 check_terms fts3d-4.4 1 0 {a four is one test that this three two was} |
|
283 check_doclist fts3d-4.4.1 1 0 a {[1 0[2]] [2 0[2]] [3 0[2]]} |
|
284 check_doclist fts3d-4.4.2 1 0 four {[1] [2 0[4]] [3 0[4]]} |
|
285 check_doclist fts3d-4.4.3 1 0 is {[1 0[1]] [3 0[1]]} |
|
286 check_doclist fts3d-4.4.4 1 0 one {[1] [2] [3]} |
|
287 check_doclist fts3d-4.4.5 1 0 test {[1 0[3]] [2 0[3]] [3 0[3]]} |
|
288 check_doclist fts3d-4.4.6 1 0 that {[2 0[0]]} |
|
289 check_doclist fts3d-4.4.7 1 0 this {[1 0[0]] [3 0[0]]} |
|
290 check_doclist fts3d-4.4.8 1 0 three {[1] [2] [3]} |
|
291 check_doclist fts3d-4.4.9 1 0 two {[1] [2] [3]} |
|
292 check_doclist fts3d-4.4.10 1 0 was {[2 0[1]]} |
|
293 |
|
294 # Optimize should leave the result in the level of the highest-level |
|
295 # prior segment. |
|
296 do_test fts3d-4.5 { |
|
297 execsql { |
|
298 SELECT OPTIMIZE(t1) FROM t1 LIMIT 1; |
|
299 SELECT level, idx FROM t1_segdir ORDER BY level, idx; |
|
300 } |
|
301 } {{Index optimized} 1 0} |
|
302 |
|
303 # Identical to fts3d-4.matches. |
|
304 do_test fts3d-4.5.matches { |
|
305 execsql { |
|
306 SELECT OFFSETS(t1) FROM t1 |
|
307 WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid; |
|
308 } |
|
309 } [list {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4} \ |
|
310 {0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4} \ |
|
311 {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}] |
|
312 |
|
313 check_terms_all fts3d-4.5.1 {a is test that this was} |
|
314 check_doclist_all fts3d-4.5.1.1 a {[1 0[2]] [2 0[2]] [3 0[2]]} |
|
315 check_doclist_all fts3d-4.5.1.2 is {[1 0[1]] [3 0[1]]} |
|
316 check_doclist_all fts3d-4.5.1.3 test {[1 0[3]] [2 0[3]] [3 0[3]]} |
|
317 check_doclist_all fts3d-4.5.1.4 that {[2 0[0]]} |
|
318 check_doclist_all fts3d-4.5.1.5 this {[1 0[0]] [3 0[0]]} |
|
319 check_doclist_all fts3d-4.5.1.6 was {[2 0[1]]} |
|
320 |
|
321 check_terms fts3d-4.5.2 1 0 {a is test that this was} |
|
322 check_doclist fts3d-4.5.2.1 1 0 a {[1 0[2]] [2 0[2]] [3 0[2]]} |
|
323 check_doclist fts3d-4.5.2.2 1 0 is {[1 0[1]] [3 0[1]]} |
|
324 check_doclist fts3d-4.5.2.3 1 0 test {[1 0[3]] [2 0[3]] [3 0[3]]} |
|
325 check_doclist fts3d-4.5.2.4 1 0 that {[2 0[0]]} |
|
326 check_doclist fts3d-4.5.2.5 1 0 this {[1 0[0]] [3 0[0]]} |
|
327 check_doclist fts3d-4.5.2.6 1 0 was {[2 0[1]]} |
|
328 |
|
329 # Re-optimizing does nothing. |
|
330 do_test fts3d-5.0 { |
|
331 execsql { |
|
332 SELECT OPTIMIZE(t1) FROM t1 LIMIT 1; |
|
333 SELECT level, idx FROM t1_segdir ORDER BY level, idx; |
|
334 } |
|
335 } {{Index already optimal} 1 0} |
|
336 |
|
337 # Even if we move things around, still does nothing. |
|
338 do_test fts3d-5.1 { |
|
339 execsql { |
|
340 UPDATE t1_segdir SET level = 2 WHERE level = 1 AND idx = 0; |
|
341 SELECT OPTIMIZE(t1) FROM t1 LIMIT 1; |
|
342 SELECT level, idx FROM t1_segdir ORDER BY level, idx; |
|
343 } |
|
344 } {{Index already optimal} 2 0} |
|
345 |
|
346 finish_test |