| blob | 415251dcced746f95079689bd1085b513782f34b |
1 # 2010 January 07
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 #
12 # The tests in this file test the FTS3 auxillary functions offsets(),
13 # snippet() and matchinfo() work. At time of writing, running this file
14 # provides full coverage of fts3_snippet.c.
15 #
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19 set testprefix fts3snippet
21 # If SQLITE_ENABLE_FTS3 is not defined, omit this file.
22 ifcapable !fts3 { finish_test ; return }
23 source $testdir/fts3_common.tcl
25 set sqlite_fts3_enable_parentheses 1
26 set DO_MALLOC_TEST 0
28 # Transform the list $L to its "normal" form. So that it can be compared to
29 # another list with the same set of elements using [string compare].
30 #
31 proc normalize {L} {
32 set ret [list]
33 foreach l $L {lappend ret $l}
34 return $ret
35 }
37 proc do_offsets_test {name expr args} {
38 set result [list]
39 foreach a $args {
40 lappend result [normalize $a]
41 }
42 do_select_test $name {
43 SELECT offsets(ft) FROM ft WHERE ft MATCH $expr
44 } $result
45 }
47 # Document text used by a few tests. Contains the English names of all
48 # integers between 1 and 300.
49 #
50 set numbers [normalize {
51 one two three four five six seven eight nine ten eleven twelve thirteen
52 fourteen fifteen sixteen seventeen eighteen nineteen twenty twentyone
53 twentytwo twentythree twentyfour twentyfive twentysix twentyseven
54 twentyeight twentynine thirty thirtyone thirtytwo thirtythree thirtyfour
55 thirtyfive thirtysix thirtyseven thirtyeight thirtynine forty fortyone
56 fortytwo fortythree fortyfour fortyfive fortysix fortyseven fortyeight
57 fortynine fifty fiftyone fiftytwo fiftythree fiftyfour fiftyfive fiftysix
58 fiftyseven fiftyeight fiftynine sixty sixtyone sixtytwo sixtythree sixtyfour
59 sixtyfive sixtysix sixtyseven sixtyeight sixtynine seventy seventyone
60 seventytwo seventythree seventyfour seventyfive seventysix seventyseven
61 seventyeight seventynine eighty eightyone eightytwo eightythree eightyfour
62 eightyfive eightysix eightyseven eightyeight eightynine ninety ninetyone
63 ninetytwo ninetythree ninetyfour ninetyfive ninetysix ninetyseven
64 ninetyeight ninetynine onehundred onehundredone onehundredtwo
65 onehundredthree onehundredfour onehundredfive onehundredsix onehundredseven
66 onehundredeight onehundrednine onehundredten onehundredeleven
67 onehundredtwelve onehundredthirteen onehundredfourteen onehundredfifteen
68 onehundredsixteen onehundredseventeen onehundredeighteen onehundrednineteen
69 onehundredtwenty onehundredtwentyone onehundredtwentytwo
70 onehundredtwentythree onehundredtwentyfour onehundredtwentyfive
71 onehundredtwentysix onehundredtwentyseven onehundredtwentyeight
72 onehundredtwentynine onehundredthirty onehundredthirtyone
73 onehundredthirtytwo onehundredthirtythree onehundredthirtyfour
74 onehundredthirtyfive onehundredthirtysix onehundredthirtyseven
75 onehundredthirtyeight onehundredthirtynine onehundredforty
76 onehundredfortyone onehundredfortytwo onehundredfortythree
77 onehundredfortyfour onehundredfortyfive onehundredfortysix
78 onehundredfortyseven onehundredfortyeight onehundredfortynine
79 onehundredfifty onehundredfiftyone onehundredfiftytwo onehundredfiftythree
80 onehundredfiftyfour onehundredfiftyfive onehundredfiftysix
81 onehundredfiftyseven onehundredfiftyeight onehundredfiftynine
82 onehundredsixty onehundredsixtyone onehundredsixtytwo onehundredsixtythree
83 onehundredsixtyfour onehundredsixtyfive onehundredsixtysix
84 onehundredsixtyseven onehundredsixtyeight onehundredsixtynine
85 onehundredseventy onehundredseventyone onehundredseventytwo
86 onehundredseventythree onehundredseventyfour onehundredseventyfive
87 onehundredseventysix onehundredseventyseven onehundredseventyeight
88 onehundredseventynine onehundredeighty onehundredeightyone
89 onehundredeightytwo onehundredeightythree onehundredeightyfour
90 onehundredeightyfive onehundredeightysix onehundredeightyseven
91 onehundredeightyeight onehundredeightynine onehundredninety
92 onehundredninetyone onehundredninetytwo onehundredninetythree
93 onehundredninetyfour onehundredninetyfive onehundredninetysix
94 onehundredninetyseven onehundredninetyeight onehundredninetynine twohundred
95 twohundredone twohundredtwo twohundredthree twohundredfour twohundredfive
96 twohundredsix twohundredseven twohundredeight twohundrednine twohundredten
97 twohundredeleven twohundredtwelve twohundredthirteen twohundredfourteen
98 twohundredfifteen twohundredsixteen twohundredseventeen twohundredeighteen
99 twohundrednineteen twohundredtwenty twohundredtwentyone twohundredtwentytwo
100 twohundredtwentythree twohundredtwentyfour twohundredtwentyfive
101 twohundredtwentysix twohundredtwentyseven twohundredtwentyeight
102 twohundredtwentynine twohundredthirty twohundredthirtyone
103 twohundredthirtytwo twohundredthirtythree twohundredthirtyfour
104 twohundredthirtyfive twohundredthirtysix twohundredthirtyseven
105 twohundredthirtyeight twohundredthirtynine twohundredforty
106 twohundredfortyone twohundredfortytwo twohundredfortythree
107 twohundredfortyfour twohundredfortyfive twohundredfortysix
108 twohundredfortyseven twohundredfortyeight twohundredfortynine
109 twohundredfifty twohundredfiftyone twohundredfiftytwo twohundredfiftythree
110 twohundredfiftyfour twohundredfiftyfive twohundredfiftysix
111 twohundredfiftyseven twohundredfiftyeight twohundredfiftynine
112 twohundredsixty twohundredsixtyone twohundredsixtytwo twohundredsixtythree
113 twohundredsixtyfour twohundredsixtyfive twohundredsixtysix
114 twohundredsixtyseven twohundredsixtyeight twohundredsixtynine
115 twohundredseventy twohundredseventyone twohundredseventytwo
116 twohundredseventythree twohundredseventyfour twohundredseventyfive
117 twohundredseventysix twohundredseventyseven twohundredseventyeight
118 twohundredseventynine twohundredeighty twohundredeightyone
119 twohundredeightytwo twohundredeightythree twohundredeightyfour
120 twohundredeightyfive twohundredeightysix twohundredeightyseven
121 twohundredeightyeight twohundredeightynine twohundredninety
122 twohundredninetyone twohundredninetytwo twohundredninetythree
123 twohundredninetyfour twohundredninetyfive twohundredninetysix
124 twohundredninetyseven twohundredninetyeight twohundredninetynine
125 threehundred
126 }]
128 foreach {DO_MALLOC_TEST enc} {
129 0 utf8
130 1 utf8
131 1 utf16
132 } {
134 db close
135 forcedelete test.db
136 sqlite3 db test.db
137 sqlite3_db_config_lookaside db 0 0 0
138 db eval "PRAGMA encoding = \"$enc\""
140 # Set variable $T to the test name prefix for this iteration of the loop.
141 #
142 set T "fts3snippet-1.$enc"
144 ##########################################################################
145 # Test the offset function.
146 #
147 do_test $T.1.1 {
148 execsql {
149 CREATE VIRTUAL TABLE ft USING fts3;
150 INSERT INTO ft VALUES('xxx xxx xxx xxx');
151 }
152 } {}
153 do_offsets_test $T.1.2 {xxx} {0 0 0 3 0 0 4 3 0 0 8 3 0 0 12 3}
154 do_offsets_test $T.1.3 {"xxx xxx"} {
155 0 0 0 3 0 0 4 3 0 1 4 3 0 0 8 3
156 0 1 8 3 0 1 12 3
157 }
158 do_offsets_test $T.1.4 {"xxx xxx" xxx} {
159 0 0 0 3 0 2 0 3 0 0 4 3 0 1 4 3
160 0 2 4 3 0 0 8 3 0 1 8 3 0 2 8 3
161 0 1 12 3 0 2 12 3
162 }
163 do_offsets_test $T.1.5 {xxx "xxx xxx"} {
164 0 0 0 3 0 1 0 3 0 0 4 3 0 1 4 3
165 0 2 4 3 0 0 8 3 0 1 8 3 0 2 8 3
166 0 0 12 3 0 2 12 3
167 }
169 do_test $T.2.1 {
170 set v1 [lrange $numbers 0 99]
171 execsql {
172 DROP TABLE IF EXISTS ft;
173 CREATE VIRTUAL TABLE ft USING fts3(a, b);
174 INSERT INTO ft VALUES($v1, $numbers);
175 INSERT INTO ft VALUES($v1, NULL);
176 }
177 } {}
179 set off [string first "twohundred " $numbers]
180 do_offsets_test $T.2.1 {twohundred} [list 1 0 $off 10]
182 set off [string first "onehundred " $numbers]
183 do_offsets_test $T.2.2 {onehundred} \
184 [list 0 0 $off 10 1 0 $off 10] [list 0 0 $off 10]
186 # Test a corruption case:
187 execsql { UPDATE ft_content SET c1b = 'hello world' WHERE c1b = $numbers }
188 do_error_test $T.2.3 {
189 SELECT offsets(ft) FROM ft WHERE ft MATCH 'onehundred'
190 } {database disk image is malformed}
192 ##########################################################################
193 # Test the snippet function.
194 #
195 proc do_snippet_test {name expr iCol nTok args} {
196 set res [list]
197 foreach a $args { lappend res [string trim $a] }
198 do_select_test $name {
199 SELECT snippet(ft,'{','}','...',$iCol,$nTok) FROM ft WHERE ft MATCH $expr
200 } $res
201 }
202 do_test $T.3.1 {
203 execsql {
204 DROP TABLE IF EXISTS ft;
205 CREATE VIRTUAL TABLE ft USING fts3;
206 INSERT INTO ft VALUES('one two three four five six seven eight nine ten');
207 }
208 } {}
209 do_snippet_test $T.3.2 one 0 5 "{one} two three four five..."
210 do_snippet_test $T.3.3 two 0 5 "one {two} three four five..."
211 do_snippet_test $T.3.4 three 0 5 "one two {three} four five..."
212 do_snippet_test $T.3.5 four 0 5 "...two three {four} five six..."
213 do_snippet_test $T.3.6 five 0 5 "...three four {five} six seven..."
214 do_snippet_test $T.3.7 six 0 5 "...four five {six} seven eight..."
215 do_snippet_test $T.3.8 seven 0 5 "...five six {seven} eight nine..."
216 do_snippet_test $T.3.9 eight 0 5 "...six seven {eight} nine ten"
217 do_snippet_test $T.3.10 nine 0 5 "...six seven eight {nine} ten"
218 do_snippet_test $T.3.11 ten 0 5 "...six seven eight nine {ten}"
220 do_test $T.4.1 {
221 execsql {
222 INSERT INTO ft VALUES(
223 'one two three four five '
224 || 'six seven eight nine ten '
225 || 'eleven twelve thirteen fourteen fifteen '
226 || 'sixteen seventeen eighteen nineteen twenty '
227 || 'one two three four five '
228 || 'six seven eight nine ten '
229 || 'eleven twelve thirteen fourteen fifteen '
230 || 'sixteen seventeen eighteen nineteen twenty'
231 );
232 }
233 } {}
235 do_snippet_test $T.4.2 {one nine} 0 5 {
236 {one} two three...eight {nine} ten
237 } {
238 {one} two three...eight {nine} ten...
239 }
241 do_snippet_test $T.4.3 {one nine} 0 -5 {
242 {one} two three four five...six seven eight {nine} ten
243 } {
244 {one} two three four five...seven eight {nine} ten eleven...
245 }
246 do_snippet_test $T.4.3 {one nineteen} 0 -5 {
247 ...eighteen {nineteen} twenty {one} two...
248 }
249 do_snippet_test $T.4.4 {two nineteen} 0 -5 {
250 ...eighteen {nineteen} twenty one {two}...
251 }
252 do_snippet_test $T.4.5 {three nineteen} 0 -5 {
253 ...{nineteen} twenty one two {three}...
254 }
256 do_snippet_test $T.4.6 {four nineteen} 0 -5 {
257 ...two three {four} five six...seventeen eighteen {nineteen} twenty one...
258 }
259 do_snippet_test $T.4.7 {four NEAR nineteen} 0 -5 {
260 ...seventeen eighteen {nineteen} twenty one...two three {four} five six...
261 }
263 do_snippet_test $T.4.8 {four nineteen} 0 5 {
264 ...three {four} five...eighteen {nineteen} twenty...
265 }
266 do_snippet_test $T.4.9 {four NEAR nineteen} 0 5 {
267 ...eighteen {nineteen} twenty...three {four} five...
268 }
269 do_snippet_test $T.4.10 {four NEAR nineteen} 0 -5 {
270 ...seventeen eighteen {nineteen} twenty one...two three {four} five six...
271 }
272 do_snippet_test $T.4.11 {four NOT (nineteen twentyone)} 0 5 {
273 ...two three {four} five six...
274 } {
275 ...two three {four} five six...
276 }
277 do_snippet_test $T.4.12 {four OR nineteen NEAR twentyone} 0 5 {
278 ...two three {four} five six...
279 } {
280 ...two three {four} five six...
281 }
283 do_test $T.5.1 {
284 execsql {
285 DROP TABLE IF EXISTS ft;
286 CREATE VIRTUAL TABLE ft USING fts3(a, b, c);
287 INSERT INTO ft VALUES(
288 'one two three four five',
289 'four five six seven eight',
290 'seven eight nine ten eleven'
291 );
292 }
293 } {}
295 do_snippet_test $T.5.2 {five} -1 3 {...three four {five}}
296 do_snippet_test $T.5.3 {five} 0 3 {...three four {five}}
297 do_snippet_test $T.5.4 {five} 1 3 {four {five} six...}
298 do_snippet_test $T.5.5 {five} 2 3 {seven eight nine...}
300 do_test $T.5.6 {
301 execsql { UPDATE ft SET b = NULL }
302 } {}
304 do_snippet_test $T.5.7 {five} -1 3 {...three four {five}}
305 do_snippet_test $T.5.8 {five} 0 3 {...three four {five}}
306 do_snippet_test $T.5.9 {five} 1 3 {}
307 do_snippet_test $T.5.10 {five} 2 3 {seven eight nine...}
309 do_snippet_test $T.5.11 {one "seven eight nine"} -1 -3 {
310 {one} two three...{seven} {eight} {nine}...
311 }
313 do_test $T.6.1 {
314 execsql {
315 DROP TABLE IF EXISTS ft;
316 CREATE VIRTUAL TABLE ft USING fts3(x);
317 INSERT INTO ft VALUES($numbers);
318 }
319 } {}
320 do_snippet_test $T.6.2 {
321 one fifty onehundred onehundredfifty twohundredfifty threehundred
322 } -1 4 {
323 {one}...{fifty}...{onehundred}...{onehundredfifty}...
324 }
325 do_snippet_test $T.6.3 {
326 one fifty onehundred onehundredfifty twohundredfifty threehundred
327 } -1 -4 {
328 {one} two three four...fortyeight fortynine {fifty} fiftyone...ninetyeight ninetynine {onehundred} onehundredone...onehundredfortyeight onehundredfortynine {onehundredfifty} onehundredfiftyone...
329 }
331 do_test $T.7.1 {
332 execsql {
333 BEGIN;
334 DROP TABLE IF EXISTS ft;
335 CREATE VIRTUAL TABLE ft USING fts3(x);
336 }
337 set testresults [list]
338 for {set i 1} {$i < 150} {incr i} {
339 set commas [string repeat , $i]
340 execsql {INSERT INTO ft VALUES('one' || $commas || 'two')}
341 lappend testresults "{one}$commas{two}"
342 }
343 execsql COMMIT
344 } {}
345 eval [list do_snippet_test $T.7.2 {one two} -1 3] $testresults
347 ##########################################################################
348 # Test the matchinfo function.
349 #
350 proc mit {blob} {
351 set scan(littleEndian) i*
352 set scan(bigEndian) I*
353 binary scan $blob $scan($::tcl_platform(byteOrder)) r
354 return $r
355 }
356 db func mit mit
357 proc do_matchinfo_test {name expr args} {
358 set res [list]
359 foreach a $args { lappend res [normalize $a] }
360 do_select_test $name {
361 SELECT mit(matchinfo(ft)) FROM ft WHERE ft MATCH $expr
362 } $res
363 }
364 do_test $T.8.1 {
365 set ten {one two three four five six seven eight nine ten}
366 execsql {
367 DROP TABLE IF EXISTS ft;
368 CREATE VIRTUAL TABLE ft USING fts3;
369 INSERT INTO ft VALUES($ten);
370 INSERT INTO ft VALUES($ten || ' ' || $ten);
371 }
372 } {}
374 do_matchinfo_test $T.8.2 "one" {1 1 1 3 2} {1 1 2 3 2}
375 do_matchinfo_test $T.8.3 "one NEAR/3 ten" {2 1 1 1 1 1 1 1}
376 do_matchinfo_test $T.8.4 "five NEAR/4 ten" \
377 {2 1 1 3 2 1 3 2} {2 1 2 3 2 2 3 2}
378 do_matchinfo_test $T.8.5 "six NEAR/3 ten NEAR/3 two" \
379 {3 1 1 1 1 1 1 1 1 1 1}
380 do_matchinfo_test $T.8.6 "five NEAR/4 ten NEAR/3 two" \
381 {3 1 2 2 1 1 1 1 1 1 1}
383 do_test $T.9.1 {
384 execsql {
385 DROP TABLE IF EXISTS ft;
386 CREATE VIRTUAL TABLE ft USING fts3(x, y);
387 }
388 foreach n {1 2 3} {
389 set v1 [lrange $numbers 0 [expr $n*100]]
390 set v2 [string trim [string repeat "$numbers " $n]]
391 set docid [expr $n * 1000000]
392 execsql { INSERT INTO ft(docid, x, y) VALUES($docid, $v1, $v2) }
393 }
394 } {}
395 do_matchinfo_test $T.9.2 {two*} \
396 { 1 2 1 105 3 101 606 3} \
397 { 1 2 3 105 3 202 606 3} \
398 { 1 2 101 105 3 303 606 3}
400 do_matchinfo_test $T.9.4 {"one* two*"} \
401 { 1 2 1 5 3 2 12 3} \
402 { 1 2 2 5 3 4 12 3} \
403 { 1 2 2 5 3 6 12 3}
405 do_matchinfo_test $T.9.5 {twohundredfifty} \
406 { 1 2 0 1 1 1 6 3} \
407 { 1 2 0 1 1 2 6 3} \
408 { 1 2 1 1 1 3 6 3}
410 do_matchinfo_test $T.9.6 {"threehundred one"} \
411 { 1 2 0 0 0 1 3 2} \
412 { 1 2 0 0 0 2 3 2}
414 do_matchinfo_test $T.9.7 {one OR fivehundred} \
415 { 2 2 1 3 3 1 6 3 0 0 0 0 0 0 } \
416 { 2 2 1 3 3 2 6 3 0 0 0 0 0 0 } \
417 { 2 2 1 3 3 3 6 3 0 0 0 0 0 0 }
419 do_matchinfo_test $T.9.8 {two OR "threehundred one"} \
420 { 2 2 1 3 3 1 6 3 0 0 0 0 3 2 } \
421 { 2 2 1 3 3 2 6 3 0 0 0 1 3 2 } \
422 { 2 2 1 3 3 3 6 3 0 0 0 2 3 2 }
424 do_select_test $T.9.9 {
425 SELECT mit(matchinfo(ft)), mit(matchinfo(ft))
426 FROM ft WHERE ft MATCH 'two OR "threehundred one"'
427 } [normalize {
428 {2 2 1 3 3 1 6 3 0 0 0 0 3 2}
429 {2 2 1 3 3 1 6 3 0 0 0 0 3 2}
430 {2 2 1 3 3 2 6 3 0 0 0 1 3 2}
431 {2 2 1 3 3 2 6 3 0 0 0 1 3 2}
432 {2 2 1 3 3 3 6 3 0 0 0 2 3 2}
433 {2 2 1 3 3 3 6 3 0 0 0 2 3 2}
434 }]
436 # EVIDENCE-OF: R-40630-02268 If used within a SELECT that uses the
437 # "query by rowid" or "linear scan" strategies, then the snippet and
438 # offsets both return an empty string, and the matchinfo function
439 # returns a blob value zero bytes in size.
440 #
441 set r 1000000 ;# A rowid that exists in table ft
442 do_select_test $T.10.0 { SELECT rowid FROM ft WHERE rowid = $r } $r
443 do_select_test $T.10.1 {
444 SELECT length(offsets(ft)), typeof(offsets(ft)) FROM ft;
445 } {0 text 0 text 0 text}
446 do_select_test $T.10.2 {
447 SELECT length(offsets(ft)), typeof(offsets(ft)) FROM ft WHERE rowid = $r
448 } {0 text}
449 do_select_test $T.10.3 {
450 SELECT length(snippet(ft)), typeof(snippet(ft)) FROM ft;
451 } {0 text 0 text 0 text}
452 do_select_test $T.10.4 {
453 SELECT length(snippet(ft)), typeof(snippet(ft)) FROM ft WHERE rowid = $r;
454 } {0 text}
455 do_select_test $T.10.5 {
456 SELECT length(matchinfo(ft)), typeof(matchinfo(ft)) FROM ft;
457 } {0 blob 0 blob 0 blob}
458 do_select_test $T.10.6 {
459 SELECT length(matchinfo(ft)), typeof(matchinfo(ft)) FROM ft WHERE rowid = $r
460 } {0 blob}
461 }
463 #-------------------------------------------------------------------------
464 # Test an interaction between the snippet() function and OR clauses.
465 #
466 do_execsql_test 2.1 {
467 CREATE VIRTUAL TABLE t2 USING fts4;
468 INSERT INTO t2 VALUES('one two three four five');
469 INSERT INTO t2 VALUES('two three four five one');
470 INSERT INTO t2 VALUES('three four five one two');
471 INSERT INTO t2 VALUES('four five one two three');
472 INSERT INTO t2 VALUES('five one two three four');
473 }
475 do_execsql_test 2.2 {
476 SELECT snippet(t2, '[', ']') FROM t2 WHERE t2 MATCH 'one OR (four AND six)'
477 } {
478 {[one] two three [four] five}
479 {two three [four] five [one]}
480 {three [four] five [one] two}
481 {[four] five [one] two three}
482 {five [one] two three [four]}
483 }
485 do_execsql_test 2.3 {
486 SELECT snippet(t2, '[', ']') FROM t2
487 WHERE t2 MATCH 'one OR (four AND six)'
488 ORDER BY docid DESC
489 } {
490 {five [one] two three [four]}
491 {[four] five [one] two three}
492 {three [four] five [one] two}
493 {two three [four] five [one]}
494 {[one] two three [four] five}
495 }
497 do_execsql_test 2.4 {
498 INSERT INTO t2 VALUES('six');
499 }
501 do_execsql_test 2.5 {
502 SELECT snippet(t2, '[', ']') FROM t2 WHERE t2 MATCH 'one OR (four AND six)'
503 } {
504 {[one] two three [four] five}
505 {two three [four] five [one]}
506 {three [four] five [one] two}
507 {[four] five [one] two three}
508 {five [one] two three [four]}
509 }
511 do_execsql_test 2.6 {
512 SELECT snippet(t2, '[', ']') FROM t2
513 WHERE t2 MATCH 'one OR (four AND six)'
514 ORDER BY docid DESC
515 } {
516 {five [one] two three [four]}
517 {[four] five [one] two three}
518 {three [four] five [one] two}
519 {two three [four] five [one]}
520 {[one] two three [four] five}
521 }
523 set sqlite_fts3_enable_parentheses 0
524 finish_test