gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fts3prefix.test (about) 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