| 1 |
# 2011 May 04 |
| 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 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
set testprefix fts3prefix |
| 18 |
|
| 19 |
ifcapable !fts3 { |
| 20 |
finish_test |
| 21 |
return |
| 22 |
} |
| 23 |
|
| 24 |
# This proc tests that the prefixes index appears to represent the same content |
| 25 |
# as the terms index. |
| 26 |
# |
| 27 |
proc fts3_terms_and_prefixes {db tbl prefixlengths} { |
| 28 |
|
| 29 |
set iIndex 0 |
| 30 |
foreach len $prefixlengths { |
| 31 |
incr iIndex |
| 32 |
$db eval { |
| 33 |
DROP TABLE IF EXISTS fts3check1; |
| 34 |
DROP TABLE IF EXISTS fts3check2; |
| 35 |
} |
| 36 |
$db eval "CREATE VIRTUAL TABLE fts3check1 USING fts4term($tbl, 0);" |
| 37 |
$db eval "CREATE VIRTUAL TABLE fts3check2 USING fts4term($tbl, $iIndex);" |
| 38 |
|
| 39 |
$db eval { |
| 40 |
DROP TABLE IF EXISTS temp.terms; |
| 41 |
DROP TABLE IF EXISTS temp.prefixes; |
| 42 |
CREATE TEMP TABLE terms AS SELECT * FROM fts3check1; |
| 43 |
CREATE TEMP TABLE prefixes AS SELECT * FROM fts3check2; |
| 44 |
CREATE INDEX temp.idx ON prefixes(term); |
| 45 |
DROP TABLE fts3check1; |
| 46 |
DROP TABLE fts3check2; |
| 47 |
} |
| 48 |
|
| 49 |
set nExpect 0 |
| 50 |
$db eval { SELECT term, docid, col, pos FROM temp.terms } a { |
| 51 |
if {[string length $a(term)]<$len} continue |
| 52 |
incr nExpect |
| 53 |
set prefix [string range $a(term) 0 [expr $len-1]] |
| 54 |
set r [$db one { |
| 55 |
SELECT count(*) FROM temp.prefixes WHERE |
| 56 |
term = $prefix AND docid = $a(docid) AND col = $a(col) AND pos = $a(pos) |
| 57 |
}] |
| 58 |
if {$r != 1} { |
| 59 |
error "$t, $a(docid), $a(col), $a(pos)" |
| 60 |
} |
| 61 |
} |
| 62 |
|
| 63 |
set nCount [$db one {SELECT count(*) FROM temp.prefixes}] |
| 64 |
if {$nCount != $nExpect} { |
| 65 |
error "prefixes.count(*) is $nCount expected $nExpect" |
| 66 |
} |
| 67 |
|
| 68 |
execsql { DROP TABLE temp.prefixes } |
| 69 |
execsql { DROP TABLE temp.terms } |
| 70 |
|
| 71 |
set list [list] |
| 72 |
$db eval " |
| 73 |
SELECT sum( 1 << (16*(level%1024)) ) AS total, (level/1024) AS tree |
| 74 |
FROM ${tbl}_segdir GROUP BY tree |
| 75 |
" { |
| 76 |
lappend list [list $total $tree] |
| 77 |
} |
| 78 |
|
| 79 |
if { [lsort -integer -index 0 $list] != [lsort -integer -index 1 $list] } { |
| 80 |
error "inconsistent tree structures: $list" |
| 81 |
} |
| 82 |
} |
| 83 |
|
| 84 |
return "" |
| 85 |
} |
| 86 |
proc fts3_tap_test {tn db tbl lens} { |
| 87 |
uplevel [list do_test $tn [list fts3_terms_and_prefixes $db $tbl $lens] ""] |
| 88 |
} |
| 89 |
|
| 90 |
#------------------------------------------------------------------------- |
| 91 |
# Test cases 1.* are a sanity check. They test that the prefixes index is |
| 92 |
# being constructed correctly for the simplest possible case. |
| 93 |
# |
| 94 |
do_execsql_test 1.1 { |
| 95 |
CREATE VIRTUAL TABLE t1 USING fts4(prefix='1,3,6'); |
| 96 |
|
| 97 |
CREATE VIRTUAL TABLE p1 USING fts4term(t1, 1); |
| 98 |
CREATE VIRTUAL TABLE p2 USING fts4term(t1, 2); |
| 99 |
CREATE VIRTUAL TABLE p3 USING fts4term(t1, 3); |
| 100 |
CREATE VIRTUAL TABLE terms USING fts4term(t1); |
| 101 |
} |
| 102 |
do_execsql_test 1.2 { |
| 103 |
INSERT INTO t1 VALUES('sqlite mysql firebird'); |
| 104 |
} |
| 105 |
do_execsql_test 1.3.1 { SELECT term FROM p1 } {f m s} |
| 106 |
do_execsql_test 1.3.2 { SELECT term FROM p2 } {fir mys sql} |
| 107 |
do_execsql_test 1.3.3 { SELECT term FROM p3 } {firebi sqlite} |
| 108 |
do_execsql_test 1.4 { |
| 109 |
SELECT term FROM terms; |
| 110 |
} {firebird mysql sqlite} |
| 111 |
|
| 112 |
fts3_tap_test 1.5 db t1 {1 3 6} |
| 113 |
|
| 114 |
#------------------------------------------------------------------------- |
| 115 |
# A slightly more complicated dataset. This test also verifies that DELETE |
| 116 |
# operations do not corrupt the prefixes index. |
| 117 |
# |
| 118 |
do_execsql_test 2.1 { |
| 119 |
INSERT INTO t1 VALUES('FTS3 and FTS4 are an SQLite virtual table modules'); |
| 120 |
INSERT INTO t1 VALUES('that allows users to perform full-text searches on'); |
| 121 |
INSERT INTO t1 VALUES('a set of documents. The most common (and'); |
| 122 |
INSERT INTO t1 VALUES('effective) way to describe full-text searches is'); |
| 123 |
INSERT INTO t1 VALUES('"what Google, Yahoo and Altavista do with'); |
| 124 |
INSERT INTO t1 VALUES('documents placed on the World Wide Web". Users'); |
| 125 |
INSERT INTO t1 VALUES('input a term, or series of terms, perhaps'); |
| 126 |
INSERT INTO t1 VALUES('connected by a binary operator or grouped together'); |
| 127 |
INSERT INTO t1 VALUES('into a phrase, and the full-text query system'); |
| 128 |
INSERT INTO t1 VALUES('finds the set of documents that best matches those'); |
| 129 |
INSERT INTO t1 VALUES('terms considering the operators and groupings the'); |
| 130 |
INSERT INTO t1 VALUES('user has specified. This article describes the'); |
| 131 |
INSERT INTO t1 VALUES('deployment and usage of FTS3 and FTS4.'); |
| 132 |
INSERT INTO t1 VALUES('FTS1 and FTS2 are obsolete full-text search'); |
| 133 |
INSERT INTO t1 VALUES('modules for SQLite. There are known issues with'); |
| 134 |
INSERT INTO t1 VALUES('these older modules and their use should be'); |
| 135 |
INSERT INTO t1 VALUES('avoided. Portions of the original FTS3 code were'); |
| 136 |
INSERT INTO t1 VALUES('contributed to the SQLite project by Scott Hess of'); |
| 137 |
INSERT INTO t1 VALUES('Google. It is now developed and maintained as part'); |
| 138 |
INSERT INTO t1 VALUES('of SQLite. '); |
| 139 |
} |
| 140 |
fts3_tap_test 2.2 db t1 {1 3 6} |
| 141 |
do_execsql_test 2.3 { DELETE FROM t1 WHERE docid%2; } |
| 142 |
fts3_tap_test 2.4 db t1 {1 3 6} |
| 143 |
|
| 144 |
do_execsql_test 2.5 { INSERT INTO t1(t1) VALUES('optimize') } |
| 145 |
fts3_tap_test 2.6 db t1 {1 3 6} |
| 146 |
|
| 147 |
do_execsql_test 3.1 { |
| 148 |
CREATE VIRTUAL TABLE t2 USING fts4(prefix='1,2,3'); |
| 149 |
INSERT INTO t2 VALUES('On 12 September the wind direction turned and'); |
| 150 |
INSERT INTO t2 VALUES('William''s fleet sailed. A storm blew up and the'); |
| 151 |
INSERT INTO t2 VALUES('fleet was forced to take shelter at'); |
| 152 |
INSERT INTO t2 VALUES('Saint-Valery-sur-Somme and again wait for the wind'); |
| 153 |
INSERT INTO t2 VALUES('to change. On 27 September the Norman fleet'); |
| 154 |
INSERT INTO t2 VALUES('finally set sail, landing in England at Pevensey'); |
| 155 |
INSERT INTO t2 VALUES('Bay (Sussex) on 28 September. William then moved'); |
| 156 |
INSERT INTO t2 VALUES('to Hastings, a few miles to the east, where he'); |
| 157 |
INSERT INTO t2 VALUES('built a prefabricated wooden castle for a base of'); |
| 158 |
INSERT INTO t2 VALUES('operations. From there, he ravaged the hinterland'); |
| 159 |
INSERT INTO t2 VALUES('and waited for Harold''s return from the north.'); |
| 160 |
INSERT INTO t2 VALUES('On 12 September the wind direction turned and'); |
| 161 |
INSERT INTO t2 VALUES('William''s fleet sailed. A storm blew up and the'); |
| 162 |
INSERT INTO t2 VALUES('fleet was forced to take shelter at'); |
| 163 |
INSERT INTO t2 VALUES('Saint-Valery-sur-Somme and again wait for the wind'); |
| 164 |
INSERT INTO t2 VALUES('to change. On 27 September the Norman fleet'); |
| 165 |
INSERT INTO t2 VALUES('finally set sail, landing in England at Pevensey'); |
| 166 |
INSERT INTO t2 VALUES('Bay (Sussex) on 28 September. William then moved'); |
| 167 |
INSERT INTO t2 VALUES('to Hastings, a few miles to the east, where he'); |
| 168 |
INSERT INTO t2 VALUES('built a prefabricated wooden castle for a base of'); |
| 169 |
INSERT INTO t2 VALUES('operations. From there, he ravaged the hinterland'); |
| 170 |
INSERT INTO t2 VALUES('and waited for Harold''s return from the north.'); |
| 171 |
} |
| 172 |
|
| 173 |
fts3_tap_test 3.2 db t2 {1 2 3} |
| 174 |
do_execsql_test 3.3 { SELECT optimize(t2) FROM t2 LIMIT 1 } {{Index optimized}} |
| 175 |
fts3_tap_test 3.4 db t2 {1 2 3} |
| 176 |
|
| 177 |
|
| 178 |
#------------------------------------------------------------------------- |
| 179 |
# Simple tests for reading the prefix-index. |
| 180 |
# |
| 181 |
do_execsql_test 4.1 { |
| 182 |
CREATE VIRTUAL TABLE t3 USING fts4(prefix="1,4"); |
| 183 |
INSERT INTO t3 VALUES('one two three'); |
| 184 |
INSERT INTO t3 VALUES('four five six'); |
| 185 |
INSERT INTO t3 VALUES('seven eight nine'); |
| 186 |
} |
| 187 |
do_execsql_test 4.2 { |
| 188 |
SELECT * FROM t3 WHERE t3 MATCH 'f*' |
| 189 |
} {{four five six}} |
| 190 |
do_execsql_test 4.3 { |
| 191 |
SELECT * FROM t3 WHERE t3 MATCH 'four*' |
| 192 |
} {{four five six}} |
| 193 |
do_execsql_test 4.4 { |
| 194 |
SELECT * FROM t3 WHERE t3 MATCH 's*' |
| 195 |
} {{four five six} {seven eight nine}} |
| 196 |
do_execsql_test 4.5 { |
| 197 |
SELECT * FROM t3 WHERE t3 MATCH 'sev*' |
| 198 |
} {{seven eight nine}} |
| 199 |
do_execsql_test 4.6 { |
| 200 |
SELECT * FROM t3 WHERE t3 MATCH 'one*' |
| 201 |
} {{one two three}} |
| 202 |
|
| 203 |
#------------------------------------------------------------------------- |
| 204 |
# Syntax tests. |
| 205 |
# |
| 206 |
do_catchsql_test 5.1 { |
| 207 |
CREATE VIRTUAL TABLE t4 USING fts4(prefix="abc"); |
| 208 |
} {1 {error parsing prefix parameter: abc}} |
| 209 |
do_catchsql_test 5.2 { |
| 210 |
CREATE VIRTUAL TABLE t4 USING fts4(prefix=""); |
| 211 |
} {0 {}} |
| 212 |
do_catchsql_test 5.3 { |
| 213 |
CREATE VIRTUAL TABLE t5 USING fts4(prefix="-1"); |
| 214 |
} {1 {error parsing prefix parameter: -1}} |
| 215 |
|
| 216 |
#------------------------------------------------------------------------- |
| 217 |
# Prefix indexes of size 0 are ignored. Demonstrate this by showing that |
| 218 |
# adding prefix=0 does not change the contents of the %_segdir table. |
| 219 |
# |
| 220 |
reset_db |
| 221 |
do_execsql_test 6.1.1 { |
| 222 |
CREATE VIRTUAL TABLE t1 USING fts4(prefix=0); |
| 223 |
CREATE VIRTUAL TABLE t2 USING fts4; |
| 224 |
INSERT INTO t1 VALUES('Twas Mulga Bill, from Eaglehawk, '); |
| 225 |
INSERT INTO t2 VALUES('Twas Mulga Bill, from Eaglehawk, '); |
| 226 |
} {} |
| 227 |
do_execsql_test 6.1.2 { |
| 228 |
SELECT md5sum(quote(root)) FROM t1_segdir; |
| 229 |
} [db eval {SELECT md5sum(quote(root)) FROM t2_segdir}] |
| 230 |
|
| 231 |
reset_db |
| 232 |
do_execsql_test 6.2.1 { |
| 233 |
CREATE VIRTUAL TABLE t1 USING fts4(prefix="1,0,2"); |
| 234 |
CREATE VIRTUAL TABLE t2 USING fts4(prefix="1,2"); |
| 235 |
INSERT INTO t1 VALUES('that caught the cycling craze;'); |
| 236 |
INSERT INTO t2 VALUES('that caught the cycling craze;'); |
| 237 |
} {} |
| 238 |
do_execsql_test 6.2.2 { |
| 239 |
SELECT md5sum(quote(root)) FROM t1_segdir; |
| 240 |
} [db eval {SELECT md5sum(quote(root)) FROM t2_segdir}] |
| 241 |
|
| 242 |
reset_db |
| 243 |
do_execsql_test 6.3.1 { |
| 244 |
CREATE VIRTUAL TABLE t1 USING fts4(prefix="1,3,2"); |
| 245 |
CREATE VIRTUAL TABLE t2 USING fts4(prefix="1,2"); |
| 246 |
INSERT INTO t1 VALUES('He turned away the good old horse'); |
| 247 |
INSERT INTO t2 VALUES('He turned away the good old horse'); |
| 248 |
} {} |
| 249 |
do_test 6.3.2 { |
| 250 |
set one [db eval {SELECT md5sum(quote(root)) FROM t1_segdir}] |
| 251 |
set two [db eval {SELECT md5sum(quote(root)) FROM t2_segdir}] |
| 252 |
expr {$one == $two} |
| 253 |
} 0 |
| 254 |
|
| 255 |
reset_db |
| 256 |
do_execsql_test 6.4.1 { |
| 257 |
CREATE VIRTUAL TABLE t1 USING fts4(prefix="1,600,2"); |
| 258 |
CREATE VIRTUAL TABLE t2 USING fts4(prefix="1,2"); |
| 259 |
INSERT INTO t1 VALUES('that served him many days;'); |
| 260 |
INSERT INTO t2 VALUES('that served him many days;'); |
| 261 |
} {} |
| 262 |
do_execsql_test 6.4.2 { |
| 263 |
SELECT md5sum(quote(root)) FROM t1_segdir; |
| 264 |
} [db eval {SELECT md5sum(quote(root)) FROM t2_segdir}] |
| 265 |
|
| 266 |
reset_db |
| 267 |
do_execsql_test 6.5.1 { |
| 268 |
CREATE VIRTUAL TABLE t1 USING fts4(prefix="2147483647,2147483648,2147483649"); |
| 269 |
CREATE VIRTUAL TABLE t2 USING fts4(prefix=); |
| 270 |
INSERT INTO t1 VALUES('He dressed himself in cycling clothes'); |
| 271 |
INSERT INTO t2 VALUES('He dressed himself in cycling clothes'); |
| 272 |
} {} |
| 273 |
do_execsql_test 6.5.2 { |
| 274 |
SELECT md5sum(quote(root)) FROM t1_segdir; |
| 275 |
} [db eval {SELECT md5sum(quote(root)) FROM t2_segdir}] |
| 276 |
|
| 277 |
|
| 278 |
do_execsql_test 7.0 { |
| 279 |
CREATE VIRTUAL TABLE t6 USING fts4(x,order=DESC); |
| 280 |
INSERT INTO t6(docid, x) VALUES(-1,'a b'); |
| 281 |
INSERT INTO t6(docid, x) VALUES(1, 'b'); |
| 282 |
} |
| 283 |
do_execsql_test 7.1 { |
| 284 |
SELECT docid FROM t6 WHERE t6 MATCH '"a* b"'; |
| 285 |
} {-1} |
| 286 |
do_execsql_test 7.2 { |
| 287 |
SELECT docid FROM t6 WHERE t6 MATCH 'a*'; |
| 288 |
} {-1} |
| 289 |
do_execsql_test 7.3 { |
| 290 |
SELECT docid FROM t6 WHERE t6 MATCH 'a* b'; |
| 291 |
} {-1} |
| 292 |
|
| 293 |
|
| 294 |
|
| 295 |
finish_test |