| blob | 20640d29ac7c4d9c2e2786f1aaeac980f134a256 |
1 # 2011 June 10
2 #
3 # May you do good and not evil.
4 # May you find forgiveness for yourself and forgive others.
5 # May you share freely, never taking more than you give.
6 #
7 #***********************************************************************
8 #
10 set testdir [file dirname $argv0]
11 source $testdir/tester.tcl
13 # If this build does not include FTS3, skip the tests in this file.
14 #
15 ifcapable !fts3 { finish_test ; return }
16 source $testdir/fts3_common.tcl
17 source $testdir/malloc_common.tcl
19 set testprefix fts3auto
20 set sfep $sqlite_fts3_enable_parentheses
21 set sqlite_fts3_enable_parentheses 1
23 #--------------------------------------------------------------------------
24 # Start of Tcl infrastructure used by tests. The entry points are:
25 #
26 # do_fts3query_test
27 # fts3_make_deferrable
28 # fts3_zero_long_segments
29 #
31 #
32 # do_fts3query_test TESTNAME ?OPTIONS? TABLE MATCHEXPR
33 #
34 # This proc runs several test cases on FTS3/4 table $TABLE using match
35 # expression $MATCHEXPR. All documents in $TABLE must be formatted so that
36 # they can be "tokenized" using the Tcl list commands (llength, lindex etc.).
37 # The name and column names used by $TABLE must not require any quoting or
38 # escaping when used in SQL statements.
39 #
40 # $MATCHINFO may be any expression accepted by the FTS4 MATCH operator,
41 # except that the "<column-name>:token" syntax is not supported. Tcl list
42 # commands are used to tokenize the expression. Any parenthesis must appear
43 # either as separate list elements, or as the first (for opening) or last
44 # (for closing) character of a list element. i.e. the expression "(a OR b)c"
45 # will not be parsed correctly, but "( a OR b) c" will.
46 #
47 # Available OPTIONS are:
48 #
49 # -deferred TOKENLIST
50 #
51 # If the "deferred" option is supplied, it is passed a list of tokens that
52 # are deferred by FTS and result in the relevant matchinfo() stats being an
53 # approximation.
54 #
55 set sqlite_fts3_enable_parentheses 1
56 proc do_fts3query_test {tn args} {
58 set nArg [llength $args]
59 if {$nArg < 2 || ($nArg % 2)} {
60 set cmd do_fts3query_test
61 error "wrong # args: should be \"$cmd ?-deferred LIST? TABLE MATCHEXPR\""
62 }
63 set tbl [lindex $args [expr $nArg-2]]
64 set match [lindex $args [expr $nArg-1]]
65 set deferred [list]
67 foreach {k v} [lrange $args 0 [expr $nArg-3]] {
68 switch -- $k {
69 -deferred {
70 ifcapable fts4_deferred { set deferred $v }
71 }
72 default {
73 error "bad option \"$k\": must be -deferred"
74 }
75 }
76 }
78 get_near_results $tbl $match $deferred aHit
79 get_near_results $tbl [string map {AND OR} $match] $deferred aMatchinfo
81 set matchinfo_asc [list]
82 foreach docid [lsort -integer -incr [array names aHit]] {
83 lappend matchinfo_asc $docid $aMatchinfo($docid)
84 }
85 set matchinfo_desc [list]
86 foreach docid [lsort -integer -decr [array names aHit]] {
87 lappend matchinfo_desc $docid $aMatchinfo($docid)
88 }
90 set title "(\"$match\" -> [llength [array names aHit]] rows)"
92 do_execsql_test $tn$title.1 "
93 SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid ASC
94 " [lsort -integer -incr [array names aHit]]
96 do_execsql_test $tn$title.2 "
97 SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid DESC
98 " [lsort -integer -decr [array names aHit]]
100 do_execsql_test $tn$title.3 "
101 SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl
102 WHERE $tbl MATCH '$match' ORDER BY docid DESC
103 " $matchinfo_desc
105 do_execsql_test $tn$title.4 "
106 SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl
107 WHERE $tbl MATCH '$match' ORDER BY docid ASC
108 " $matchinfo_asc
109 }
111 # fts3_make_deferrable TABLE TOKEN ?NROW?
112 #
113 proc fts3_make_deferrable {tbl token {nRow 0}} {
115 set stmt [sqlite3_prepare db "SELECT * FROM $tbl" -1 dummy]
116 set name [sqlite3_column_name $stmt 0]
117 sqlite3_finalize $stmt
119 if {$nRow==0} {
120 set nRow [db one "SELECT count(*) FROM $tbl"]
121 }
122 set pgsz [db one "PRAGMA page_size"]
123 execsql BEGIN
124 for {set i 0} {$i < ($nRow * $pgsz * 1.2)/100} {incr i} {
125 set doc [string repeat "$token " 100]
126 execsql "INSERT INTO $tbl ($name) VALUES(\$doc)"
127 }
128 execsql "INSERT INTO $tbl ($name) VALUES('aaaaaaa ${token}aaaaa')"
129 execsql COMMIT
131 return [expr $nRow*$pgsz]
132 }
134 # fts3_zero_long_segments TABLE ?LIMIT?
135 #
136 proc fts3_zero_long_segments {tbl limit} {
137 execsql "
138 UPDATE ${tbl}_segments
139 SET block = zeroblob(length(block))
140 WHERE length(block)>$limit
141 "
142 return [db changes]
143 }
146 proc mit {blob} {
147 set scan(littleEndian) i*
148 set scan(bigEndian) I*
149 binary scan $blob $scan($::tcl_platform(byteOrder)) r
150 return $r
151 }
152 db func mit mit
154 proc fix_phrase_expr {cols expr colfiltervar} {
155 upvar $colfiltervar iColFilter
157 set out [list]
158 foreach t $expr {
159 if {[string match *:* $t]} {
160 set col [lindex [split $t :] 0]
161 set t [lindex [split $t :] 1]
162 set iCol [lsearch $cols $col]
163 if {$iCol<0} { error "unknown column: $col" }
164 if {$iColFilter < 0} {
165 set iColFilter $iCol
166 } elseif {$iColFilter != $iCol} {
167 set iColFilter [llength $cols]
168 }
169 }
170 lappend out $t
171 }
173 return $out
174 }
176 proc fix_near_expr {cols expr colfiltervar} {
177 upvar $colfiltervar iColFilter
179 set iColFilter -1
181 set out [list]
182 lappend out [fix_phrase_expr $cols [lindex $expr 0] iColFilter]
183 foreach {a b} [lrange $expr 1 end] {
184 if {[string match -nocase near $a]} { set a 10 }
185 if {[string match -nocase near/* $a]} { set a [string range $a 5 end] }
186 lappend out $a
187 lappend out [fix_phrase_expr $cols $b iColFilter]
188 }
189 return $out
190 }
192 proc get_single_near_results {tbl expr deferred arrayvar nullvar} {
193 upvar $arrayvar aMatchinfo
194 upvar $nullvar nullentry
195 catch {array unset aMatchinfo}
197 set cols [list]
198 set miss [list]
199 db eval "PRAGMA table_info($tbl)" A { lappend cols $A(name) ; lappend miss 0 }
200 set expr [fix_near_expr $cols $expr iColFilter]
202 # Calculate the expected results using [fts3_near_match]. The following
203 # loop populates the "hits" and "counts" arrays as follows:
204 #
205 # 1. For each document in the table that matches the NEAR expression,
206 # hits($docid) is set to 1. The set of docids that match the expression
207 # can therefore be found using [array names hits].
208 #
209 # 2. For each column of each document in the table, counts($docid,$iCol)
210 # is set to the -phrasecountvar output.
211 #
212 set res [list]
213 catch { array unset hits }
214 db eval "SELECT docid, * FROM $tbl" d {
215 set iCol 0
216 foreach col [lrange $d(*) 1 end] {
217 set docid $d(docid)
218 if {$iColFilter<0 || $iCol==$iColFilter} {
219 set hit [fts3_near_match $d($col) $expr -p counts($docid,$iCol)]
220 if {$hit} { set hits($docid) 1 }
221 } else {
222 set counts($docid,$iCol) $miss
223 }
224 incr iCol
225 }
226 }
227 set nPhrase [expr ([llength $expr]+1)/2]
228 set nCol $iCol
230 # This block populates the nHit and nDoc arrays. For each phrase/column
231 # in the query/table, array elements are set as follows:
232 #
233 # nHit($iPhrase,$iCol) - Total number of hits for phrase $iPhrase in
234 # column $iCol.
235 #
236 # nDoc($iPhrase,$iCol) - Number of documents with at least one hit for
237 # phrase $iPhrase in column $iCol.
238 #
239 for {set iPhrase 0} {$iPhrase < $nPhrase} {incr iPhrase} {
240 for {set iCol 0} {$iCol < $nCol} {incr iCol} {
241 set nHit($iPhrase,$iCol) 0
242 set nDoc($iPhrase,$iCol) 0
243 }
244 }
245 foreach key [array names counts] {
246 set iCol [lindex [split $key ,] 1]
247 set iPhrase 0
248 foreach c $counts($key) {
249 if {$c>0} { incr nDoc($iPhrase,$iCol) 1 }
250 incr nHit($iPhrase,$iCol) $c
251 incr iPhrase
252 }
253 }
255 if {[llength $deferred] && [llength $expr]==1} {
256 set phrase [lindex $expr 0]
257 set rewritten [list]
258 set partial 0
259 foreach tok $phrase {
260 if {[lsearch $deferred $tok]>=0} {
261 lappend rewritten *
262 } else {
263 lappend rewritten $tok
264 set partial 1
265 }
266 }
267 if {$partial==0} {
268 set tblsize [db one "SELECT count(*) FROM $tbl"]
269 for {set iCol 0} {$iCol < $nCol} {incr iCol} {
270 set nHit(0,$iCol) $tblsize
271 set nDoc(0,$iCol) $tblsize
272 }
273 } elseif {$rewritten != $phrase} {
274 while {[lindex $rewritten end] == "*"} {
275 set rewritten [lrange $rewritten 0 end-1]
276 }
277 while {[lindex $rewritten 0] == "*"} {
278 set rewritten [lrange $rewritten 1 end]
279 }
280 get_single_near_results $tbl [list $rewritten] {} aRewrite nullentry
281 foreach docid [array names hits] {
282 set aMatchinfo($docid) $aRewrite($docid)
283 }
284 return
285 }
286 }
288 # Set up the aMatchinfo array. For each document, set aMatchinfo($docid) to
289 # contain the output of matchinfo('x') for the document.
290 #
291 foreach docid [array names hits] {
292 set mi [list]
293 for {set iPhrase 0} {$iPhrase<$nPhrase} {incr iPhrase} {
294 for {set iCol 0} {$iCol<$nCol} {incr iCol} {
295 lappend mi [lindex $counts($docid,$iCol) $iPhrase]
296 lappend mi $nHit($iPhrase,$iCol)
297 lappend mi $nDoc($iPhrase,$iCol)
298 }
299 }
300 set aMatchinfo($docid) $mi
301 }
303 # Set up the nullentry output.
304 #
305 set nullentry [list]
306 for {set iPhrase 0} {$iPhrase<$nPhrase} {incr iPhrase} {
307 for {set iCol 0} {$iCol<$nCol} {incr iCol} {
308 lappend nullentry 0 $nHit($iPhrase,$iCol) $nDoc($iPhrase,$iCol)
309 }
310 }
311 }
314 proc matching_brackets {expr} {
315 if {[string range $expr 0 0]!="(" || [string range $expr end end] !=")"} {
316 return 0
317 }
319 set iBracket 1
320 set nExpr [string length $expr]
321 for {set i 1} {$iBracket && $i < $nExpr} {incr i} {
322 set c [string range $expr $i $i]
323 if {$c == "("} {incr iBracket}
324 if {$c == ")"} {incr iBracket -1}
325 }
327 return [expr ($iBracket==0 && $i==$nExpr)]
328 }
330 proc get_near_results {tbl expr deferred arrayvar {nullvar ""}} {
331 upvar $arrayvar aMatchinfo
332 if {$nullvar != ""} { upvar $nullvar nullentry }
334 set expr [string trim $expr]
335 while { [matching_brackets $expr] } {
336 set expr [string trim [string range $expr 1 end-1]]
337 }
339 set prec(NOT) 1
340 set prec(AND) 2
341 set prec(OR) 3
343 set currentprec 0
344 set iBracket 0
345 set expr_length [llength $expr]
346 for {set i 0} {$i < $expr_length} {incr i} {
347 set op [lindex $expr $i]
348 if {$iBracket==0 && [info exists prec($op)] && $prec($op)>=$currentprec } {
349 set opidx $i
350 set currentprec $prec($op)
351 } else {
352 for {set j 0} {$j < [string length $op]} {incr j} {
353 set c [string range $op $j $j]
354 if {$c == "("} { incr iBracket +1 }
355 if {$c == ")"} { incr iBracket -1 }
356 }
357 }
358 }
359 if {$iBracket!=0} { error "mismatched brackets in: $expr" }
361 if {[info exists opidx]==0} {
362 get_single_near_results $tbl $expr $deferred aMatchinfo nullentry
363 } else {
364 set eLeft [lrange $expr 0 [expr $opidx-1]]
365 set eRight [lrange $expr [expr $opidx+1] end]
367 get_near_results $tbl $eLeft $deferred aLeft nullleft
368 get_near_results $tbl $eRight $deferred aRight nullright
370 switch -- [lindex $expr $opidx] {
371 "NOT" {
372 foreach hit [array names aLeft] {
373 if {0==[info exists aRight($hit)]} {
374 set aMatchinfo($hit) $aLeft($hit)
375 }
376 }
377 set nullentry $nullleft
378 }
380 "AND" {
381 foreach hit [array names aLeft] {
382 if {[info exists aRight($hit)]} {
383 set aMatchinfo($hit) [concat $aLeft($hit) $aRight($hit)]
384 }
385 }
386 set nullentry [concat $nullleft $nullright]
387 }
389 "OR" {
390 foreach hit [array names aLeft] {
391 if {[info exists aRight($hit)]} {
392 set aMatchinfo($hit) [concat $aLeft($hit) $aRight($hit)]
393 unset aRight($hit)
394 } else {
395 set aMatchinfo($hit) [concat $aLeft($hit) $nullright]
396 }
397 }
398 foreach hit [array names aRight] {
399 set aMatchinfo($hit) [concat $nullleft $aRight($hit)]
400 }
402 set nullentry [concat $nullleft $nullright]
403 }
404 }
405 }
406 }
409 # End of test procs. Actual tests are below this line.
410 #--------------------------------------------------------------------------
412 #--------------------------------------------------------------------------
413 # The following test cases - fts3auto-1.* - focus on testing the Tcl
414 # command [fts3_near_match], which is used by other tests in this file.
415 #
416 proc test_fts3_near_match {tn doc expr res} {
417 fts3_near_match $doc $expr -phrasecountvar p
418 uplevel do_test [list $tn] [list [list set {} $p]] [list $res]
419 }
421 test_fts3_near_match 1.1.1 {a b c a b} a {2}
422 test_fts3_near_match 1.1.2 {a b c a b} {a 5 b 6 c} {2 2 1}
423 test_fts3_near_match 1.1.3 {a b c a b} {"a b"} {2}
424 test_fts3_near_match 1.1.4 {a b c a b} {"b c"} {1}
425 test_fts3_near_match 1.1.5 {a b c a b} {"c c"} {0}
427 test_fts3_near_match 1.2.1 "a b c d e f g" {b 2 f} {0 0}
428 test_fts3_near_match 1.2.2 "a b c d e f g" {b 3 f} {1 1}
429 test_fts3_near_match 1.2.3 "a b c d e f g" {f 2 b} {0 0}
430 test_fts3_near_match 1.2.4 "a b c d e f g" {f 3 b} {1 1}
431 test_fts3_near_match 1.2.5 "a b c d e f g" {"a b" 2 "f g"} {0 0}
432 test_fts3_near_match 1.2.6 "a b c d e f g" {"a b" 3 "f g"} {1 1}
434 set A "a b c d e f g h i j k l m n o p q r s t u v w x y z"
435 test_fts3_near_match 1.3.1 $A {"c d" 5 "i j" 1 "e f"} {0 0 0}
436 test_fts3_near_match 1.3.2 $A {"c d" 5 "i j" 2 "e f"} {1 1 1}
438 #--------------------------------------------------------------------------
439 # Test cases fts3auto-2.* run some simple tests using the
440 # [do_fts3query_test] proc.
441 #
442 foreach {tn create} {
443 1 "fts4(a, b)"
444 2 "fts4(a, b, order=DESC)"
445 3 "fts4(a, b, order=ASC)"
446 4 "fts4(a, b, prefix=1)"
447 5 "fts4(a, b, order=DESC, prefix=1)"
448 6 "fts4(a, b, order=ASC, prefix=1)"
449 } {
450 do_test 2.$tn.1 {
451 catchsql { DROP TABLE t1 }
452 execsql "CREATE VIRTUAL TABLE t1 USING $create"
453 for {set i 0} {$i<32} {incr i} {
454 set doc [list]
455 if {$i&0x01} {lappend doc one}
456 if {$i&0x02} {lappend doc two}
457 if {$i&0x04} {lappend doc three}
458 if {$i&0x08} {lappend doc four}
459 if {$i&0x10} {lappend doc five}
460 execsql { INSERT INTO t1 VALUES($doc, null) }
461 }
462 } {}
464 foreach {tn2 expr} {
465 1 {one}
466 2 {one NEAR/1 five}
467 3 {t*}
468 4 {t* NEAR/0 five}
469 5 {o* NEAR/1 f*}
470 6 {one NEAR five NEAR two NEAR four NEAR three}
471 7 {one NEAR xyz}
472 8 {one OR two}
473 9 {one AND two}
474 10 {one NOT two}
475 11 {one AND two OR three}
476 12 {three OR one AND two}
477 13 {(three OR one) AND two}
478 14 {(three OR one) AND two NOT (five NOT four)}
479 15 {"one two"}
480 16 {"one two" NOT "three four"}
481 } {
482 do_fts3query_test 2.$tn.2.$tn2 t1 $expr
483 }
484 }
486 #--------------------------------------------------------------------------
487 # Some test cases involving deferred tokens.
488 #
490 foreach {tn create} {
491 1 "fts4(x)"
492 2 "fts4(x, order=DESC)"
493 } {
494 catchsql { DROP TABLE t1 }
495 execsql "CREATE VIRTUAL TABLE t1 USING $create"
496 do_execsql_test 3.$tn.1 {
497 INSERT INTO t1(docid, x) VALUES(-2, 'a b c d e f g h i j k');
498 INSERT INTO t1(docid, x) VALUES(-1, 'b c d e f g h i j k a');
499 INSERT INTO t1(docid, x) VALUES(0, 'c d e f g h i j k a b');
500 INSERT INTO t1(docid, x) VALUES(1, 'd e f g h i j k a b c');
501 INSERT INTO t1(docid, x) VALUES(2, 'e f g h i j k a b c d');
502 INSERT INTO t1(docid, x) VALUES(3, 'f g h i j k a b c d e');
503 INSERT INTO t1(docid, x) VALUES(4, 'a c e g i k');
504 INSERT INTO t1(docid, x) VALUES(5, 'a d g j');
505 INSERT INTO t1(docid, x) VALUES(6, 'c a b');
506 }
508 set limit [fts3_make_deferrable t1 c]
510 do_fts3query_test 3.$tn.2.1 t1 {a OR c}
512 ifcapable fts4_deferred {
513 do_test 3.$tn.3 { fts3_zero_long_segments t1 $limit } {1}
514 }
516 foreach {tn2 expr def} {
517 1 {a NEAR c} {}
518 2 {a AND c} c
519 3 {"a c"} c
520 4 {"c a"} c
521 5 {"a c" NEAR/1 g} {}
522 6 {"a c" NEAR/0 g} {}
523 } {
524 do_fts3query_test 3.$tn.4.$tn2 -deferred $def t1 $expr
525 }
526 }
528 #--------------------------------------------------------------------------
529 #
530 foreach {tn create} {
531 1 "fts4(x, y)"
532 2 "fts4(x, y, order=DESC)"
533 3 "fts4(x, y, order=DESC, prefix=2)"
534 } {
536 execsql [subst {
537 DROP TABLE t1;
538 CREATE VIRTUAL TABLE t1 USING $create;
539 INSERT INTO t1 VALUES('one two five four five', '');
540 INSERT INTO t1 VALUES('', 'one two five four five');
541 INSERT INTO t1 VALUES('one two', 'five four five');
542 }]
544 do_fts3query_test 4.$tn.1.1 t1 {one AND five}
545 do_fts3query_test 4.$tn.1.2 t1 {one NEAR five}
546 do_fts3query_test 4.$tn.1.3 t1 {one NEAR/1 five}
547 do_fts3query_test 4.$tn.1.4 t1 {one NEAR/2 five}
548 do_fts3query_test 4.$tn.1.5 t1 {one NEAR/3 five}
550 do_test 4.$tn.2 {
551 set limit [fts3_make_deferrable t1 five]
552 execsql { INSERT INTO t1(t1) VALUES('optimize') }
553 ifcapable fts4_deferred {
554 expr {[fts3_zero_long_segments t1 $limit]>0}
555 } else {
556 expr 1
557 }
558 } {1}
560 do_fts3query_test 4.$tn.3.1 -deferred five t1 {one AND five}
561 do_fts3query_test 4.$tn.3.2 -deferred five t1 {one NEAR five}
562 do_fts3query_test 4.$tn.3.3 -deferred five t1 {one NEAR/1 five}
563 do_fts3query_test 4.$tn.3.4 -deferred five t1 {one NEAR/2 five}
565 do_fts3query_test 4.$tn.3.5 -deferred five t1 {one NEAR/3 five}
567 do_fts3query_test 4.$tn.4.1 -deferred fi* t1 {on* AND fi*}
568 do_fts3query_test 4.$tn.4.2 -deferred fi* t1 {on* NEAR fi*}
569 do_fts3query_test 4.$tn.4.3 -deferred fi* t1 {on* NEAR/1 fi*}
570 do_fts3query_test 4.$tn.4.4 -deferred fi* t1 {on* NEAR/2 fi*}
571 do_fts3query_test 4.$tn.4.5 -deferred fi* t1 {on* NEAR/3 fi*}
572 }
574 #--------------------------------------------------------------------------
575 # The following test cases - fts3auto-5.* - focus on using prefix indexes.
576 #
577 set chunkconfig [fts3_configure_incr_load 1 1]
578 foreach {tn create pending} {
579 1 "fts4(a, b)" 1
580 2 "fts4(a, b, order=ASC, prefix=1)" 1
581 3 "fts4(a, b, order=ASC, prefix=\"1,3\")" 0
582 4 "fts4(a, b, order=DESC, prefix=\"2,4\")" 0
583 5 "fts4(a, b, order=DESC, prefix=\"1\")" 0
584 6 "fts4(a, b, order=ASC, prefix=\"1,3\")" 0
585 } {
587 execsql [subst {
588 DROP TABLE IF EXISTS t1;
589 CREATE VIRTUAL TABLE t1 USING $create;
590 }]
592 if {$pending} {execsql BEGIN}
594 foreach {a b} {
595 "the song of songs which is solomons"
596 "let him kiss me with the kisses of his mouth for thy love is better than wine"
597 "because of the savour of thy good ointments thy name is as ointment poured forth therefore do the virgins love thee"
598 "draw me we will run after thee the king hath brought me into his chambers we will be glad and rejoice in thee we will remember thy love more than wine the upright love thee"
599 "i am black but comely o ye daughters of jerusalem as the tents of kedar as the curtains of solomon"
600 "look not upon me because i am black because the sun hath looked upon me my mothers children were angry with me they made me the keeper of the vineyards but mine own vineyard have i not kept"
601 "tell me o thou whom my soul loveth where thou feedest where thou makest thy flock to rest at noon for why should i be as one that turneth aside by the flocks of thy companions?"
602 "if thou know not o thou fairest among women go thy way forth by the footsteps of the flock and feed thy kids beside the shepherds tents"
603 "i have compared thee o my love to a company of horses in pharaohs chariots"
604 "thy cheeks are comely with rows of jewels thy neck with chains of gold"
605 "we will make thee borders of gold with studs of silver"
606 "while the king sitteth at his table my spikenard sendeth forth the smell thereof"
607 "a bundle of myrrh is my wellbeloved unto me he shall lie all night betwixt my breasts"
608 "my beloved is unto me as a cluster of camphire in the vineyards of en gedi"
609 "behold thou art fair my love behold thou art fair thou hast doves eyes"
610 "behold thou art fair my beloved yea pleasant also our bed is green"
611 "the beams of our house are cedar and our rafters of fir"
612 } {
613 execsql {INSERT INTO t1(a, b) VALUES($a, $b)}
614 }
617 do_fts3query_test 5.$tn.1.1 t1 {s*}
618 do_fts3query_test 5.$tn.1.2 t1 {so*}
619 do_fts3query_test 5.$tn.1.3 t1 {"s* o*"}
620 do_fts3query_test 5.$tn.1.4 t1 {b* NEAR/3 a*}
621 do_fts3query_test 5.$tn.1.5 t1 {a*}
622 do_fts3query_test 5.$tn.1.6 t1 {th* NEAR/5 a* NEAR/5 w*}
623 do_fts3query_test 5.$tn.1.7 t1 {"b* th* art* fair*"}
625 if {$pending} {execsql COMMIT}
626 }
627 eval fts3_configure_incr_load $chunkconfig
629 foreach {tn pending create} {
630 1 0 "fts4(a, b, c, d)"
631 2 1 "fts4(a, b, c, d)"
632 3 0 "fts4(a, b, c, d, order=DESC)"
633 4 1 "fts4(a, b, c, d, order=DESC)"
634 } {
635 execsql [subst {
636 DROP TABLE IF EXISTS t1;
637 CREATE VIRTUAL TABLE t1 USING $create;
638 }]
641 if {$pending} { execsql BEGIN }
643 foreach {a b c d} {
644 "A B C" "D E F" "G H I" "J K L"
645 "B C D" "E F G" "H I J" "K L A"
646 "C D E" "F G H" "I J K" "L A B"
647 "D E F" "G H I" "J K L" "A B C"
648 "E F G" "H I J" "K L A" "B C D"
649 "F G H" "I J K" "L A B" "C D E"
650 } {
651 execsql { INSERT INTO t1 VALUES($a, $b, $c, $d) }
652 }
654 do_fts3query_test 6.$tn.1 t1 {b:G}
655 do_fts3query_test 6.$tn.2 t1 {b:G AND c:I}
656 do_fts3query_test 6.$tn.3 t1 {b:G NEAR c:I}
657 do_fts3query_test 6.$tn.4 t1 {a:C OR b:G OR c:K OR d:C}
659 do_fts3query_test 6.$tn.5 t1 {a:G OR b:G}
661 catchsql { COMMIT }
662 }
664 foreach {tn create} {
665 1 "fts4(x)"
666 2 "fts4(x, order=DESC)"
667 } {
668 execsql [subst {
669 DROP TABLE IF EXISTS t1;
670 CREATE VIRTUAL TABLE t1 USING $create;
671 }]
673 foreach {x} {
674 "F E N O T K X V A X I E X A P G Q V H U"
675 "R V A E T C V Q N I E L O N U G J K L U"
676 "U Y I G W M V F J L X I D C H F P J Q B"
677 "S G D Z X R P G S S Y B K A S G A I L L"
678 "L S I C H T Z S R Q P R N K J X L F M J"
679 "C C C D P X B Z C M A D A C X S B T X V"
680 "W Y J M D R G V R K B X S A W R I T N C"
681 "P K L W T M S P O Y Y V V O E H Q A I R"
682 "C D Y I C Z F H J C O Y A Q F L S B D K"
683 "P G S C Y C Y V I M B D S Z D D Y W I E"
684 "Z K Z U E E S F Y X T U A L W O U J C Q"
685 "P A T Z S W L P L Q V Y Y I P W U X S S"
686 "I U I H U O F Z F R H R F T N D X A G M"
687 "N A B M S H K X S O Y D T X S B R Y H Z"
688 "L U D A S K I L S V Z J P U B E B Y H M"
689 } {
690 execsql { INSERT INTO t1 VALUES($x) }
691 }
693 # Add extra documents to the database such that token "B" will be considered
694 # deferrable if considering the other tokens means that 2 or fewer documents
695 # will be loaded into memory.
696 #
697 fts3_make_deferrable t1 B 2
699 # B is not deferred in either of the first two tests below, since filtering
700 # on "M" or "D" returns 10 documents or so. But filtering on "M * D" only
701 # returns 2, so B is deferred in this case.
702 #
703 do_fts3query_test 7.$tn.1 t1 {"M B"}
704 do_fts3query_test 7.$tn.2 t1 {"B D"}
705 do_fts3query_test 7.$tn.3 -deferred B t1 {"M B D"}
706 }
708 set sqlite_fts3_enable_parentheses $sfep
709 finish_test