Annotation of embedaddon/sqlite3/test/fts3auto.test, revision 1.1.1.1
1.1 misho 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: #
9:
10: set testdir [file dirname $argv0]
11: source $testdir/tester.tcl
12:
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
18:
19: set testprefix fts3auto
20: set sfep $sqlite_fts3_enable_parentheses
21: set sqlite_fts3_enable_parentheses 1
22:
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: #
30:
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} {
57:
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]
66:
67: foreach {k v} [lrange $args 0 [expr $nArg-3]] {
68: switch -- $k {
69: -deferred {
70: set deferred $v
71: }
72: default {
73: error "bad option \"$k\": must be -deferred"
74: }
75: }
76: }
77:
78: get_near_results $tbl $match $deferred aMatchinfo
79:
80: set matchinfo_asc [list]
81: foreach docid [lsort -integer -incr [array names aMatchinfo]] {
82: lappend matchinfo_asc $docid $aMatchinfo($docid)
83: }
84: set matchinfo_desc [list]
85: foreach docid [lsort -integer -decr [array names aMatchinfo]] {
86: lappend matchinfo_desc $docid $aMatchinfo($docid)
87: }
88:
89: set title "(\"$match\" -> [llength [array names aMatchinfo]] rows)"
90:
91: do_execsql_test $tn$title.1 "
92: SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid ASC
93: " [lsort -integer -incr [array names aMatchinfo]]
94:
95: do_execsql_test $tn$title.2 "
96: SELECT docid FROM $tbl WHERE $tbl MATCH '$match' ORDER BY docid DESC
97: " [lsort -integer -decr [array names aMatchinfo]]
98:
99: do_execsql_test $tn$title.3 "
100: SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl
101: WHERE $tbl MATCH '$match' ORDER BY docid DESC
102: " $matchinfo_desc
103:
104: do_execsql_test $tn$title.4 "
105: SELECT docid, mit(matchinfo($tbl, 'x')) FROM $tbl
106: WHERE $tbl MATCH '$match' ORDER BY docid ASC
107: " $matchinfo_asc
108: }
109:
110: # fts3_make_deferrable TABLE TOKEN ?NROW?
111: #
112: proc fts3_make_deferrable {tbl token {nRow 0}} {
113:
114: set stmt [sqlite3_prepare db "SELECT * FROM $tbl" -1 dummy]
115: set name [sqlite3_column_name $stmt 0]
116: sqlite3_finalize $stmt
117:
118: if {$nRow==0} {
119: set nRow [db one "SELECT count(*) FROM $tbl"]
120: }
121: set pgsz [db one "PRAGMA page_size"]
122: execsql BEGIN
123: for {set i 0} {$i < ($nRow * $pgsz * 1.2)/100} {incr i} {
124: set doc [string repeat "$token " 100]
125: execsql "INSERT INTO $tbl ($name) VALUES(\$doc)"
126: }
127: execsql "INSERT INTO $tbl ($name) VALUES('aaaaaaa ${token}aaaaa')"
128: execsql COMMIT
129:
130: return [expr $nRow*$pgsz]
131: }
132:
133: # fts3_zero_long_segments TABLE ?LIMIT?
134: #
135: proc fts3_zero_long_segments {tbl limit} {
136: execsql "
137: UPDATE ${tbl}_segments
138: SET block = zeroblob(length(block))
139: WHERE length(block)>$limit
140: "
141: return [db changes]
142: }
143:
144:
145: proc mit {blob} {
146: set scan(littleEndian) i*
147: set scan(bigEndian) I*
148: binary scan $blob $scan($::tcl_platform(byteOrder)) r
149: return $r
150: }
151: db func mit mit
152:
153: proc fix_phrase_expr {cols expr colfiltervar} {
154: upvar $colfiltervar iColFilter
155:
156: set out [list]
157: foreach t $expr {
158: if {[string match *:* $t]} {
159: set col [lindex [split $t :] 0]
160: set t [lindex [split $t :] 1]
161: set iCol [lsearch $cols $col]
162: if {$iCol<0} { error "unknown column: $col" }
163: if {$iColFilter < 0} {
164: set iColFilter $iCol
165: } elseif {$iColFilter != $iCol} {
166: set iColFilter [llength $cols]
167: }
168: }
169: lappend out $t
170: }
171:
172: return $out
173: }
174:
175: proc fix_near_expr {cols expr colfiltervar} {
176: upvar $colfiltervar iColFilter
177:
178: set iColFilter -1
179:
180: set out [list]
181: lappend out [fix_phrase_expr $cols [lindex $expr 0] iColFilter]
182: foreach {a b} [lrange $expr 1 end] {
183: if {[string match -nocase near $a]} { set a 10 }
184: if {[string match -nocase near/* $a]} { set a [string range $a 5 end] }
185: lappend out $a
186: lappend out [fix_phrase_expr $cols $b iColFilter]
187: }
188: return $out
189: }
190:
191: proc get_single_near_results {tbl expr deferred arrayvar nullvar} {
192: upvar $arrayvar aMatchinfo
193: upvar $nullvar nullentry
194: catch {array unset aMatchinfo}
195:
196: set cols [list]
197: set miss [list]
198: db eval "PRAGMA table_info($tbl)" A { lappend cols $A(name) ; lappend miss 0 }
199: set expr [fix_near_expr $cols $expr iColFilter]
200:
201: # Calculate the expected results using [fts3_near_match]. The following
202: # loop populates the "hits" and "counts" arrays as follows:
203: #
204: # 1. For each document in the table that matches the NEAR expression,
205: # hits($docid) is set to 1. The set of docids that match the expression
206: # can therefore be found using [array names hits].
207: #
208: # 2. For each column of each document in the table, counts($docid,$iCol)
209: # is set to the -phrasecountvar output.
210: #
211: set res [list]
212: catch { array unset hits }
213: db eval "SELECT docid, * FROM $tbl" d {
214: set iCol 0
215: foreach col [lrange $d(*) 1 end] {
216: set docid $d(docid)
217: if {$iColFilter<0 || $iCol==$iColFilter} {
218: set hit [fts3_near_match $d($col) $expr -p counts($docid,$iCol)]
219: if {$hit} { set hits($docid) 1 }
220: } else {
221: set counts($docid,$iCol) $miss
222: }
223: incr iCol
224: }
225: }
226: set nPhrase [expr ([llength $expr]+1)/2]
227: set nCol $iCol
228:
229: # This block populates the nHit and nDoc arrays. For each phrase/column
230: # in the query/table, array elements are set as follows:
231: #
232: # nHit($iPhrase,$iCol) - Total number of hits for phrase $iPhrase in
233: # column $iCol.
234: #
235: # nDoc($iPhrase,$iCol) - Number of documents with at least one hit for
236: # phrase $iPhrase in column $iCol.
237: #
238: for {set iPhrase 0} {$iPhrase < $nPhrase} {incr iPhrase} {
239: for {set iCol 0} {$iCol < $nCol} {incr iCol} {
240: set nHit($iPhrase,$iCol) 0
241: set nDoc($iPhrase,$iCol) 0
242: }
243: }
244: foreach key [array names counts] {
245: set iCol [lindex [split $key ,] 1]
246: set iPhrase 0
247: foreach c $counts($key) {
248: if {$c>0} { incr nDoc($iPhrase,$iCol) 1 }
249: incr nHit($iPhrase,$iCol) $c
250: incr iPhrase
251: }
252: }
253:
254: if {[llength $deferred] && [llength $expr]==1} {
255: set phrase [lindex $expr 0]
256: set rewritten [list]
257: set partial 0
258: foreach tok $phrase {
259: if {[lsearch $deferred $tok]>=0} {
260: lappend rewritten *
261: } else {
262: lappend rewritten $tok
263: set partial 1
264: }
265: }
266: if {$partial==0} {
267: set tblsize [db one "SELECT count(*) FROM $tbl"]
268: for {set iCol 0} {$iCol < $nCol} {incr iCol} {
269: set nHit(0,$iCol) $tblsize
270: set nDoc(0,$iCol) $tblsize
271: }
272: } elseif {$rewritten != $phrase} {
273: while {[lindex $rewritten end] == "*"} {
274: set rewritten [lrange $rewritten 0 end-1]
275: }
276: while {[lindex $rewritten 0] == "*"} {
277: set rewritten [lrange $rewritten 1 end]
278: }
279: get_single_near_results $tbl [list $rewritten] {} aRewrite nullentry
280: foreach docid [array names hits] {
281: set aMatchinfo($docid) $aRewrite($docid)
282: }
283: return
284: }
285: }
286:
287: # Set up the aMatchinfo array. For each document, set aMatchinfo($docid) to
288: # contain the output of matchinfo('x') for the document.
289: #
290: foreach docid [array names hits] {
291: set mi [list]
292: for {set iPhrase 0} {$iPhrase<$nPhrase} {incr iPhrase} {
293: for {set iCol 0} {$iCol<$nCol} {incr iCol} {
294: lappend mi [lindex $counts($docid,$iCol) $iPhrase]
295: lappend mi $nHit($iPhrase,$iCol)
296: lappend mi $nDoc($iPhrase,$iCol)
297: }
298: }
299: set aMatchinfo($docid) $mi
300: }
301:
302: # Set up the nullentry output.
303: #
304: set nullentry [list]
305: for {set iPhrase 0} {$iPhrase<$nPhrase} {incr iPhrase} {
306: for {set iCol 0} {$iCol<$nCol} {incr iCol} {
307: lappend nullentry 0 $nHit($iPhrase,$iCol) $nDoc($iPhrase,$iCol)
308: }
309: }
310: }
311:
312:
313: proc matching_brackets {expr} {
314: if {[string range $expr 0 0]!="(" || [string range $expr end end] !=")"} {
315: return 0
316: }
317:
318: set iBracket 1
319: set nExpr [string length $expr]
320: for {set i 1} {$iBracket && $i < $nExpr} {incr i} {
321: set c [string range $expr $i $i]
322: if {$c == "("} {incr iBracket}
323: if {$c == ")"} {incr iBracket -1}
324: }
325:
326: return [expr ($iBracket==0 && $i==$nExpr)]
327: }
328:
329: proc get_near_results {tbl expr deferred arrayvar {nullvar ""}} {
330: upvar $arrayvar aMatchinfo
331: if {$nullvar != ""} { upvar $nullvar nullentry }
332:
333: set expr [string trim $expr]
334: while { [matching_brackets $expr] } {
335: set expr [string trim [string range $expr 1 end-1]]
336: }
337:
338: set prec(NOT) 1
339: set prec(AND) 2
340: set prec(OR) 3
341:
342: set currentprec 0
343: set iBracket 0
344: set expr_length [llength $expr]
345: for {set i 0} {$i < $expr_length} {incr i} {
346: set op [lindex $expr $i]
347: if {$iBracket==0 && [info exists prec($op)] && $prec($op)>=$currentprec } {
348: set opidx $i
349: set currentprec $prec($op)
350: } else {
351: for {set j 0} {$j < [string length $op]} {incr j} {
352: set c [string range $op $j $j]
353: if {$c == "("} { incr iBracket +1 }
354: if {$c == ")"} { incr iBracket -1 }
355: }
356: }
357: }
358: if {$iBracket!=0} { error "mismatched brackets in: $expr" }
359:
360: if {[info exists opidx]==0} {
361: get_single_near_results $tbl $expr $deferred aMatchinfo nullentry
362: } else {
363: set eLeft [lrange $expr 0 [expr $opidx-1]]
364: set eRight [lrange $expr [expr $opidx+1] end]
365:
366: get_near_results $tbl $eLeft $deferred aLeft nullleft
367: get_near_results $tbl $eRight $deferred aRight nullright
368:
369: switch -- [lindex $expr $opidx] {
370: "NOT" {
371: foreach hit [array names aLeft] {
372: if {0==[info exists aRight($hit)]} {
373: set aMatchinfo($hit) $aLeft($hit)
374: }
375: }
376: set nullentry $nullleft
377: }
378:
379: "AND" {
380: foreach hit [array names aLeft] {
381: if {[info exists aRight($hit)]} {
382: set aMatchinfo($hit) [concat $aLeft($hit) $aRight($hit)]
383: }
384: }
385: set nullentry [concat $nullleft $nullright]
386: }
387:
388: "OR" {
389: foreach hit [array names aLeft] {
390: if {[info exists aRight($hit)]} {
391: set aMatchinfo($hit) [concat $aLeft($hit) $aRight($hit)]
392: unset aRight($hit)
393: } else {
394: set aMatchinfo($hit) [concat $aLeft($hit) $nullright]
395: }
396: }
397: foreach hit [array names aRight] {
398: set aMatchinfo($hit) [concat $nullleft $aRight($hit)]
399: }
400:
401: set nullentry [concat $nullleft $nullright]
402: }
403: }
404: }
405: }
406:
407:
408: # End of test procs. Actual tests are below this line.
409: #--------------------------------------------------------------------------
410:
411: #--------------------------------------------------------------------------
412: # The following test cases - fts3auto-1.* - focus on testing the Tcl
413: # command [fts3_near_match], which is used by other tests in this file.
414: #
415: proc test_fts3_near_match {tn doc expr res} {
416: fts3_near_match $doc $expr -phrasecountvar p
417: uplevel do_test [list $tn] [list [list set {} $p]] [list $res]
418: }
419:
420: test_fts3_near_match 1.1.1 {a b c a b} a {2}
421: test_fts3_near_match 1.1.2 {a b c a b} {a 5 b 6 c} {2 2 1}
422: test_fts3_near_match 1.1.3 {a b c a b} {"a b"} {2}
423: test_fts3_near_match 1.1.4 {a b c a b} {"b c"} {1}
424: test_fts3_near_match 1.1.5 {a b c a b} {"c c"} {0}
425:
426: test_fts3_near_match 1.2.1 "a b c d e f g" {b 2 f} {0 0}
427: test_fts3_near_match 1.2.2 "a b c d e f g" {b 3 f} {1 1}
428: test_fts3_near_match 1.2.3 "a b c d e f g" {f 2 b} {0 0}
429: test_fts3_near_match 1.2.4 "a b c d e f g" {f 3 b} {1 1}
430: test_fts3_near_match 1.2.5 "a b c d e f g" {"a b" 2 "f g"} {0 0}
431: test_fts3_near_match 1.2.6 "a b c d e f g" {"a b" 3 "f g"} {1 1}
432:
433: 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"
434: test_fts3_near_match 1.3.1 $A {"c d" 5 "i j" 1 "e f"} {0 0 0}
435: test_fts3_near_match 1.3.2 $A {"c d" 5 "i j" 2 "e f"} {1 1 1}
436:
437: #--------------------------------------------------------------------------
438: # Test cases fts3auto-2.* run some simple tests using the
439: # [do_fts3query_test] proc.
440: #
441: foreach {tn create} {
442: 1 "fts4(a, b)"
443: 2 "fts4(a, b, order=DESC)"
444: 3 "fts4(a, b, order=ASC)"
445: 4 "fts4(a, b, prefix=1)"
446: 5 "fts4(a, b, order=DESC, prefix=1)"
447: 6 "fts4(a, b, order=ASC, prefix=1)"
448: } {
449: do_test 2.$tn.1 {
450: catchsql { DROP TABLE t1 }
451: execsql "CREATE VIRTUAL TABLE t1 USING $create"
452: for {set i 0} {$i<32} {incr i} {
453: set doc [list]
454: if {$i&0x01} {lappend doc one}
455: if {$i&0x02} {lappend doc two}
456: if {$i&0x04} {lappend doc three}
457: if {$i&0x08} {lappend doc four}
458: if {$i&0x10} {lappend doc five}
459: execsql { INSERT INTO t1 VALUES($doc, null) }
460: }
461: } {}
462:
463: foreach {tn2 expr} {
464: 1 {one}
465: 2 {one NEAR/1 five}
466: 3 {t*}
467: 4 {t* NEAR/0 five}
468: 5 {o* NEAR/1 f*}
469: 6 {one NEAR five NEAR two NEAR four NEAR three}
470: 7 {one NEAR xyz}
471: 8 {one OR two}
472: 9 {one AND two}
473: 10 {one NOT two}
474: 11 {one AND two OR three}
475: 12 {three OR one AND two}
476: 13 {(three OR one) AND two}
477: 14 {(three OR one) AND two NOT (five NOT four)}
478: 15 {"one two"}
479: 16 {"one two" NOT "three four"}
480: } {
481: do_fts3query_test 2.$tn.2.$tn2 t1 $expr
482: }
483: }
484:
485: #--------------------------------------------------------------------------
486: # Some test cases involving deferred tokens.
487: #
488:
489: foreach {tn create} {
490: 1 "fts4(x)"
491: 2 "fts4(x, order=DESC)"
492: } {
493: catchsql { DROP TABLE t1 }
494: execsql "CREATE VIRTUAL TABLE t1 USING $create"
495: do_execsql_test 3.$tn.1 {
496: INSERT INTO t1(docid, x) VALUES(-2, 'a b c d e f g h i j k');
497: INSERT INTO t1(docid, x) VALUES(-1, 'b c d e f g h i j k a');
498: INSERT INTO t1(docid, x) VALUES(0, 'c d e f g h i j k a b');
499: INSERT INTO t1(docid, x) VALUES(1, 'd e f g h i j k a b c');
500: INSERT INTO t1(docid, x) VALUES(2, 'e f g h i j k a b c d');
501: INSERT INTO t1(docid, x) VALUES(3, 'f g h i j k a b c d e');
502: INSERT INTO t1(docid, x) VALUES(4, 'a c e g i k');
503: INSERT INTO t1(docid, x) VALUES(5, 'a d g j');
504: INSERT INTO t1(docid, x) VALUES(6, 'c a b');
505: }
506:
507: set limit [fts3_make_deferrable t1 c]
508:
509: do_fts3query_test 3.$tn.2.1 t1 {a OR c}
510:
511: do_test 3.$tn.3 {
512: fts3_zero_long_segments t1 $limit
513: } {1}
514:
515: foreach {tn2 expr def} {
516: 1 {a NEAR c} {}
517: 2 {a AND c} c
518: 3 {"a c"} c
519: 4 {"c a"} c
520: 5 {"a c" NEAR/1 g} {}
521: 6 {"a c" NEAR/0 g} {}
522: } {
523: do_fts3query_test 3.$tn.4.$tn2 -deferred $def t1 $expr
524: }
525: }
526:
527: #--------------------------------------------------------------------------
528: #
529: foreach {tn create} {
530: 1 "fts4(x, y)"
531: 2 "fts4(x, y, order=DESC)"
532: 3 "fts4(x, y, order=DESC, prefix=2)"
533: } {
534:
535: execsql [subst {
536: DROP TABLE t1;
537: CREATE VIRTUAL TABLE t1 USING $create;
538: INSERT INTO t1 VALUES('one two five four five', '');
539: INSERT INTO t1 VALUES('', 'one two five four five');
540: INSERT INTO t1 VALUES('one two', 'five four five');
541: }]
542:
543: do_fts3query_test 4.$tn.1.1 t1 {one AND five}
544: do_fts3query_test 4.$tn.1.2 t1 {one NEAR five}
545: do_fts3query_test 4.$tn.1.3 t1 {one NEAR/1 five}
546: do_fts3query_test 4.$tn.1.4 t1 {one NEAR/2 five}
547: do_fts3query_test 4.$tn.1.5 t1 {one NEAR/3 five}
548:
549: do_test 4.$tn.2 {
550: set limit [fts3_make_deferrable t1 five]
551: execsql { INSERT INTO t1(t1) VALUES('optimize') }
552: expr {[fts3_zero_long_segments t1 $limit]>0}
553: } {1}
554:
555: do_fts3query_test 4.$tn.3.1 -deferred five t1 {one AND five}
556: do_fts3query_test 4.$tn.3.2 -deferred five t1 {one NEAR five}
557: do_fts3query_test 4.$tn.3.3 -deferred five t1 {one NEAR/1 five}
558: do_fts3query_test 4.$tn.3.4 -deferred five t1 {one NEAR/2 five}
559:
560: do_fts3query_test 4.$tn.3.5 -deferred five t1 {one NEAR/3 five}
561:
562: do_fts3query_test 4.$tn.4.1 -deferred fi* t1 {on* AND fi*}
563: do_fts3query_test 4.$tn.4.2 -deferred fi* t1 {on* NEAR fi*}
564: do_fts3query_test 4.$tn.4.3 -deferred fi* t1 {on* NEAR/1 fi*}
565: do_fts3query_test 4.$tn.4.4 -deferred fi* t1 {on* NEAR/2 fi*}
566: do_fts3query_test 4.$tn.4.5 -deferred fi* t1 {on* NEAR/3 fi*}
567: }
568:
569: #--------------------------------------------------------------------------
570: # The following test cases - fts3auto-5.* - focus on using prefix indexes.
571: #
572: set chunkconfig [fts3_configure_incr_load 1 1]
573: foreach {tn create pending} {
574: 1 "fts4(a, b)" 1
575: 2 "fts4(a, b, order=ASC, prefix=1)" 1
576: 3 "fts4(a, b, order=ASC, prefix=1,3)" 0
577: 4 "fts4(a, b, order=DESC, prefix=2,4)" 0
578: 5 "fts4(a, b, order=DESC, prefix=1)" 0
579: 6 "fts4(a, b, order=ASC, prefix=1,3)" 0
580: } {
581:
582: execsql [subst {
583: DROP TABLE IF EXISTS t1;
584: CREATE VIRTUAL TABLE t1 USING $create;
585: }]
586:
587: if {$pending} {execsql BEGIN}
588:
589: foreach {a b} {
590: "the song of songs which is solomons"
591: "let him kiss me with the kisses of his mouth for thy love is better than wine"
592: "because of the savour of thy good ointments thy name is as ointment poured forth therefore do the virgins love thee"
593: "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"
594: "i am black but comely o ye daughters of jerusalem as the tents of kedar as the curtains of solomon"
595: "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"
596: "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?"
597: "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"
598: "i have compared thee o my love to a company of horses in pharaohs chariots"
599: "thy cheeks are comely with rows of jewels thy neck with chains of gold"
600: "we will make thee borders of gold with studs of silver"
601: "while the king sitteth at his table my spikenard sendeth forth the smell thereof"
602: "a bundle of myrrh is my wellbeloved unto me he shall lie all night betwixt my breasts"
603: "my beloved is unto me as a cluster of camphire in the vineyards of en gedi"
604: "behold thou art fair my love behold thou art fair thou hast doves eyes"
605: "behold thou art fair my beloved yea pleasant also our bed is green"
606: "the beams of our house are cedar and our rafters of fir"
607: } {
608: execsql {INSERT INTO t1(a, b) VALUES($a, $b)}
609: }
610:
611:
612: do_fts3query_test 5.$tn.1.1 t1 {s*}
613: do_fts3query_test 5.$tn.1.2 t1 {so*}
614: do_fts3query_test 5.$tn.1.3 t1 {"s* o*"}
615: do_fts3query_test 5.$tn.1.4 t1 {b* NEAR/3 a*}
616: do_fts3query_test 5.$tn.1.5 t1 {a*}
617: do_fts3query_test 5.$tn.1.6 t1 {th* NEAR/5 a* NEAR/5 w*}
618: do_fts3query_test 5.$tn.1.7 t1 {"b* th* art* fair*"}
619:
620: if {$pending} {execsql COMMIT}
621: }
622: eval fts3_configure_incr_load $chunkconfig
623:
624: foreach {tn pending create} {
625: 1 0 "fts4(a, b, c, d)"
626: 2 1 "fts4(a, b, c, d)"
627: 3 0 "fts4(a, b, c, d, order=DESC)"
628: 4 1 "fts4(a, b, c, d, order=DESC)"
629: } {
630: execsql [subst {
631: DROP TABLE IF EXISTS t1;
632: CREATE VIRTUAL TABLE t1 USING $create;
633: }]
634:
635:
636: if {$pending} { execsql BEGIN }
637:
638: foreach {a b c d} {
639: "A B C" "D E F" "G H I" "J K L"
640: "B C D" "E F G" "H I J" "K L A"
641: "C D E" "F G H" "I J K" "L A B"
642: "D E F" "G H I" "J K L" "A B C"
643: "E F G" "H I J" "K L A" "B C D"
644: "F G H" "I J K" "L A B" "C D E"
645: } {
646: execsql { INSERT INTO t1 VALUES($a, $b, $c, $d) }
647: }
648:
649: do_fts3query_test 6.$tn.1 t1 {b:G}
650: do_fts3query_test 6.$tn.2 t1 {b:G AND c:I}
651: do_fts3query_test 6.$tn.3 t1 {b:G NEAR c:I}
652: do_fts3query_test 6.$tn.4 t1 {a:C OR b:G OR c:K OR d:C}
653: do_fts3query_test 6.$tn.5 t1 {a:G OR b:G}
654:
655: catchsql { COMMIT }
656: }
657:
658: foreach {tn create} {
659: 1 "fts4(x)"
660: 2 "fts4(x, order=DESC)"
661: } {
662: execsql [subst {
663: DROP TABLE IF EXISTS t1;
664: CREATE VIRTUAL TABLE t1 USING $create;
665: }]
666:
667: foreach {x} {
668: "F E N O T K X V A X I E X A P G Q V H U"
669: "R V A E T C V Q N I E L O N U G J K L U"
670: "U Y I G W M V F J L X I D C H F P J Q B"
671: "S G D Z X R P G S S Y B K A S G A I L L"
672: "L S I C H T Z S R Q P R N K J X L F M J"
673: "C C C D P X B Z C M A D A C X S B T X V"
674: "W Y J M D R G V R K B X S A W R I T N C"
675: "P K L W T M S P O Y Y V V O E H Q A I R"
676: "C D Y I C Z F H J C O Y A Q F L S B D K"
677: "P G S C Y C Y V I M B D S Z D D Y W I E"
678: "Z K Z U E E S F Y X T U A L W O U J C Q"
679: "P A T Z S W L P L Q V Y Y I P W U X S S"
680: "I U I H U O F Z F R H R F T N D X A G M"
681: "N A B M S H K X S O Y D T X S B R Y H Z"
682: "L U D A S K I L S V Z J P U B E B Y H M"
683: } {
684: execsql { INSERT INTO t1 VALUES($x) }
685: }
686:
687: # Add extra documents to the database such that token "B" will be considered
688: # deferrable if considering the other tokens means that 2 or fewer documents
689: # will be loaded into memory.
690: #
691: fts3_make_deferrable t1 B 2
692:
693: # B is not deferred in either of the first two tests below, since filtering
694: # on "M" or "D" returns 10 documents or so. But filtering on "M * D" only
695: # returns 2, so B is deferred in this case.
696: #
697: do_fts3query_test 7.$tn.1 t1 {"M B"}
698: do_fts3query_test 7.$tn.2 t1 {"B D"}
699: do_fts3query_test 7.$tn.3 -deferred B t1 {"M B D"}
700: }
701:
702: set sqlite_fts3_enable_parentheses $sfep
703: finish_test
704:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to fts3auto.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test