|
1 # 2006 September 13 |
|
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 FTS3 module. |
|
13 # |
|
14 # $Id: fts3ab.test,v 1.1 2007/08/20 17:38:42 shess Exp $ |
|
15 # |
|
16 |
|
17 set testdir [file dirname $argv0] |
|
18 source $testdir/tester.tcl |
|
19 |
|
20 # If SQLITE_ENABLE_FTS3 is defined, omit this file. |
|
21 ifcapable !fts3 { |
|
22 finish_test |
|
23 return |
|
24 } |
|
25 |
|
26 # Fill the full-text index "t1" with phrases in english, spanish, |
|
27 # and german. For the i-th row, fill in the names for the bits |
|
28 # that are set in the value of i. The least significant bit is |
|
29 # 1. For example, the value 5 is 101 in binary which will be |
|
30 # converted to "one three" in english. |
|
31 # |
|
32 proc fill_multilanguage_fulltext_t1 {} { |
|
33 set english {one two three four five} |
|
34 set spanish {un dos tres cuatro cinco} |
|
35 set german {eine zwei drei vier funf} |
|
36 |
|
37 for {set i 1} {$i<=31} {incr i} { |
|
38 set cmd "INSERT INTO t1 VALUES" |
|
39 set vset {} |
|
40 foreach lang {english spanish german} { |
|
41 set words {} |
|
42 for {set j 0; set k 1} {$j<5} {incr j; incr k $k} { |
|
43 if {$k&$i} {lappend words [lindex [set $lang] $j]} |
|
44 } |
|
45 lappend vset "'$words'" |
|
46 } |
|
47 set sql "INSERT INTO t1(english,spanish,german) VALUES([join $vset ,])" |
|
48 # puts $sql |
|
49 db eval $sql |
|
50 } |
|
51 } |
|
52 |
|
53 # Construct a full-text search table containing five keywords: |
|
54 # one, two, three, four, and five, in various combinations. The |
|
55 # rowid for each will be a bitmask for the elements it contains. |
|
56 # |
|
57 db eval { |
|
58 CREATE VIRTUAL TABLE t1 USING fts3(english,spanish,german); |
|
59 } |
|
60 fill_multilanguage_fulltext_t1 |
|
61 |
|
62 do_test fts3ab-1.1 { |
|
63 execsql {SELECT rowid FROM t1 WHERE english MATCH 'one'} |
|
64 } {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31} |
|
65 do_test fts3ab-1.2 { |
|
66 execsql {SELECT rowid FROM t1 WHERE spanish MATCH 'one'} |
|
67 } {} |
|
68 do_test fts3ab-1.3 { |
|
69 execsql {SELECT rowid FROM t1 WHERE german MATCH 'one'} |
|
70 } {} |
|
71 do_test fts3ab-1.4 { |
|
72 execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one'} |
|
73 } {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31} |
|
74 do_test fts3ab-1.5 { |
|
75 execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one dos drei'} |
|
76 } {7 15 23 31} |
|
77 do_test fts3ab-1.6 { |
|
78 execsql {SELECT english, spanish, german FROM t1 WHERE rowid=1} |
|
79 } {one un eine} |
|
80 do_test fts3ab-1.7 { |
|
81 execsql {SELECT rowid FROM t1 WHERE t1 MATCH '"one un"'} |
|
82 } {} |
|
83 |
|
84 do_test fts3ab-2.1 { |
|
85 execsql { |
|
86 CREATE VIRTUAL TABLE t2 USING fts3(from,to); |
|
87 INSERT INTO t2([from],[to]) VALUES ('one two three', 'four five six'); |
|
88 SELECT [from], [to] FROM t2 |
|
89 } |
|
90 } {{one two three} {four five six}} |
|
91 |
|
92 |
|
93 # Compute an SQL string that contains the words one, two, three,... to |
|
94 # describe bits set in the value $i. Only the lower 5 bits are examined. |
|
95 # |
|
96 proc wordset {i} { |
|
97 set x {} |
|
98 for {set j 0; set k 1} {$j<5} {incr j; incr k $k} { |
|
99 if {$k&$i} {lappend x [lindex {one two three four five} $j]} |
|
100 } |
|
101 return '$x' |
|
102 } |
|
103 |
|
104 # Create a new FTS table with three columns: |
|
105 # |
|
106 # norm: words for the bits of rowid |
|
107 # plusone: words for the bits of rowid+1 |
|
108 # invert: words for the bits of ~rowid |
|
109 # |
|
110 db eval { |
|
111 CREATE VIRTUAL TABLE t4 USING fts3([norm],'plusone',"invert"); |
|
112 } |
|
113 for {set i 1} {$i<=15} {incr i} { |
|
114 set vset [list [wordset $i] [wordset [expr {$i+1}]] [wordset [expr {~$i}]]] |
|
115 db eval "INSERT INTO t4(norm,plusone,invert) VALUES([join $vset ,]);" |
|
116 } |
|
117 |
|
118 do_test fts3ab-4.1 { |
|
119 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one'} |
|
120 } {1 3 5 7 9 11 13 15} |
|
121 do_test fts3ab-4.2 { |
|
122 execsql {SELECT rowid FROM t4 WHERE norm MATCH 'one'} |
|
123 } {1 3 5 7 9 11 13 15} |
|
124 do_test fts3ab-4.3 { |
|
125 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'one'} |
|
126 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15} |
|
127 do_test fts3ab-4.4 { |
|
128 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:one'} |
|
129 } {2 4 6 8 10 12 14} |
|
130 do_test fts3ab-4.5 { |
|
131 execsql {SELECT rowid FROM t4 WHERE plusone MATCH 'one'} |
|
132 } {2 4 6 8 10 12 14} |
|
133 do_test fts3ab-4.6 { |
|
134 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one plusone:two'} |
|
135 } {1 5 9 13} |
|
136 do_test fts3ab-4.7 { |
|
137 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one two'} |
|
138 } {1 3 5 7 9 11 13 15} |
|
139 do_test fts3ab-4.8 { |
|
140 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:two norm:one'} |
|
141 } {1 5 9 13} |
|
142 do_test fts3ab-4.9 { |
|
143 execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'two norm:one'} |
|
144 } {1 3 5 7 9 11 13 15} |
|
145 |
|
146 |
|
147 finish_test |