| 1 |
# 2009 November 28 |
| 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 |
# |
| 12 |
# This file implements tests to verify the "testable statements" in the |
| 13 |
# fts3.in document. |
| 14 |
# |
| 15 |
|
| 16 |
set testdir [file dirname $argv0] |
| 17 |
source $testdir/tester.tcl |
| 18 |
|
| 19 |
# If this build does not include FTS3, skip the tests in this file. |
| 20 |
# |
| 21 |
ifcapable !fts3 { finish_test ; return } |
| 22 |
source $testdir/fts3_common.tcl |
| 23 |
source $testdir/malloc_common.tcl |
| 24 |
|
| 25 |
# Procs used to make the tests in this file easier to read. |
| 26 |
# |
| 27 |
proc ddl_test {tn ddl} { |
| 28 |
uplevel [list do_write_test e_fts3-$tn sqlite_master $ddl] |
| 29 |
} |
| 30 |
proc write_test {tn tbl sql} { |
| 31 |
uplevel [list do_write_test e_fts3-$tn $tbl $sql] |
| 32 |
} |
| 33 |
proc read_test {tn sql result} { |
| 34 |
uplevel [list do_select_test e_fts3-$tn $sql $result] |
| 35 |
} |
| 36 |
proc error_test {tn sql result} { |
| 37 |
uplevel [list do_error_test e_fts3-$tn $sql $result] |
| 38 |
} |
| 39 |
|
| 40 |
|
| 41 |
#------------------------------------------------------------------------- |
| 42 |
# The body of the following [foreach] block contains test cases to verify |
| 43 |
# that the example code in fts3.html works as expected. The tests run three |
| 44 |
# times, with different values for DO_MALLOC_TEST. |
| 45 |
# |
| 46 |
# DO_MALLOC_TEST=0: Run tests with no OOM errors. |
| 47 |
# DO_MALLOC_TEST=1: Run tests with transient OOM errors. |
| 48 |
# DO_MALLOC_TEST=2: Run tests with persistent OOM errors. |
| 49 |
# |
| 50 |
foreach {DO_MALLOC_TEST enc} { |
| 51 |
0 utf-8 |
| 52 |
1 utf-8 |
| 53 |
2 utf-8 |
| 54 |
1 utf-16 |
| 55 |
} { |
| 56 |
|
| 57 |
#if {$DO_MALLOC_TEST} break |
| 58 |
|
| 59 |
# Reset the database and database connection. If this iteration of the |
| 60 |
# [foreach] loop is testing with OOM errors, disable the lookaside buffer. |
| 61 |
# |
| 62 |
db close |
| 63 |
forcedelete test.db test.db-journal |
| 64 |
sqlite3 db test.db |
| 65 |
if {$DO_MALLOC_TEST} { sqlite3_db_config_lookaside db 0 0 0 } |
| 66 |
db eval "PRAGMA encoding = '$enc'" |
| 67 |
|
| 68 |
proc mit {blob} { |
| 69 |
set scan(littleEndian) i* |
| 70 |
set scan(bigEndian) I* |
| 71 |
binary scan $blob $scan($::tcl_platform(byteOrder)) r |
| 72 |
return $r |
| 73 |
} |
| 74 |
db func mit mit |
| 75 |
|
| 76 |
########################################################################## |
| 77 |
# Test the example CREATE VIRTUAL TABLE statements in section 1.1 |
| 78 |
# of fts3.in. |
| 79 |
# |
| 80 |
ddl_test 1.1.1.1 {CREATE VIRTUAL TABLE data USING fts3()} |
| 81 |
read_test 1.1.1.2 {PRAGMA table_info(data)} {0 content {} 0 {} 0} |
| 82 |
|
| 83 |
ddl_test 1.1.2.1 { |
| 84 |
CREATE VIRTUAL TABLE pages USING fts3(title, keywords, body) |
| 85 |
} |
| 86 |
read_test 1.1.2.2 { |
| 87 |
PRAGMA table_info(pages) |
| 88 |
} {0 title {} 0 {} 0 1 keywords {} 0 {} 0 2 body {} 0 {} 0} |
| 89 |
|
| 90 |
ddl_test 1.1.3.1 { |
| 91 |
CREATE VIRTUAL TABLE mail USING fts3( |
| 92 |
subject VARCHAR(256) NOT NULL, |
| 93 |
body TEXT CHECK(length(body)<10240) |
| 94 |
) |
| 95 |
} |
| 96 |
read_test 1.1.3.2 { |
| 97 |
PRAGMA table_info(mail) |
| 98 |
} {0 subject {} 0 {} 0 1 body {} 0 {} 0} |
| 99 |
|
| 100 |
# A very large string. Used to test if the constraint on column "body" of |
| 101 |
# table "mail" is enforced (it should not be - FTS3 tables do not support |
| 102 |
# constraints). |
| 103 |
set largetext [string repeat "the quick brown fox " 5000] |
| 104 |
write_test 1.1.3.3 mail_content { INSERT INTO mail VALUES(NULL, $largetext) } |
| 105 |
read_test 1.1.3.4 { |
| 106 |
SELECT subject IS NULL, length(body) FROM mail |
| 107 |
} [list 1 100000] |
| 108 |
|
| 109 |
ddl_test 1.1.4.1 { |
| 110 |
CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter) |
| 111 |
} |
| 112 |
read_test 1.1.4.2 { |
| 113 |
PRAGMA table_info(papers) |
| 114 |
} {0 author {} 0 {} 0 1 document {} 0 {} 0} |
| 115 |
|
| 116 |
ddl_test 1.1.5.1 { |
| 117 |
CREATE VIRTUAL TABLE simpledata USING fts3(tokenize=simple) |
| 118 |
} |
| 119 |
read_test 1.1.5.2 { |
| 120 |
PRAGMA table_info(simpledata) |
| 121 |
} {0 content {} 0 {} 0} |
| 122 |
|
| 123 |
ifcapable icu { |
| 124 |
ddl_test 1.1.6.1 { |
| 125 |
CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU) |
| 126 |
} |
| 127 |
read_test 1.1.6.2 { |
| 128 |
PRAGMA table_info(names) |
| 129 |
} {0 a {} 0 {} 0 1 b {} 0 {} 0} |
| 130 |
} |
| 131 |
|
| 132 |
ddl_test 1.1.7.1 {DROP TABLE data} |
| 133 |
ddl_test 1.1.7.2 {DROP TABLE pages} |
| 134 |
ddl_test 1.1.7.3 {DROP TABLE mail} |
| 135 |
ddl_test 1.1.7.4 {DROP TABLE papers} |
| 136 |
ddl_test 1.1.7.5 {DROP TABLE simpledata} |
| 137 |
read_test 1.1.7.6 {SELECT * FROM sqlite_master} {} |
| 138 |
|
| 139 |
# The following is not one of the examples in section 1.1. It tests |
| 140 |
# specifying an FTS3 table with no module arguments using a slightly |
| 141 |
# different syntax. |
| 142 |
ddl_test 1.1.8.1 {CREATE VIRTUAL TABLE data USING fts3;} |
| 143 |
read_test 1.1.8.2 {PRAGMA table_info(data)} {0 content {} 0 {} 0} |
| 144 |
ddl_test 1.1.8.3 {DROP TABLE data} |
| 145 |
|
| 146 |
########################################################################## |
| 147 |
# Test the examples in section 1.2 (populating fts3 tables) |
| 148 |
# |
| 149 |
ddl_test 1.2.1.1 { |
| 150 |
CREATE VIRTUAL TABLE pages USING fts3(title, body); |
| 151 |
} |
| 152 |
write_test 1.2.1.2 pages_content { |
| 153 |
INSERT INTO pages(docid, title, body) |
| 154 |
VALUES(53, 'Home Page', 'SQLite is a software...'); |
| 155 |
} |
| 156 |
read_test 1.2.1.3 { |
| 157 |
SELECT docid, * FROM pages |
| 158 |
} {53 {Home Page} {SQLite is a software...}} |
| 159 |
|
| 160 |
write_test 1.2.1.4 pages_content { |
| 161 |
INSERT INTO pages(title, body) |
| 162 |
VALUES('Download', 'All SQLite source code...'); |
| 163 |
} |
| 164 |
read_test 1.2.1.5 { |
| 165 |
SELECT docid, * FROM pages |
| 166 |
} {53 {Home Page} {SQLite is a software...} 54 Download {All SQLite source code...}} |
| 167 |
|
| 168 |
write_test 1.2.1.6 pages_content { |
| 169 |
UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54 |
| 170 |
} |
| 171 |
read_test 1.2.1.7 { |
| 172 |
SELECT docid, * FROM pages |
| 173 |
} {53 {Home Page} {SQLite is a software...} 54 {Download SQLite} {All SQLite source code...}} |
| 174 |
|
| 175 |
write_test 1.2.1.8 pages_content { DELETE FROM pages } |
| 176 |
read_test 1.2.1.9 { SELECT docid, * FROM pages } {} |
| 177 |
|
| 178 |
do_error_test fts3-1.2.1.10 { |
| 179 |
INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body'); |
| 180 |
} {SQL logic error} |
| 181 |
|
| 182 |
# Test the optimize() function example: |
| 183 |
ddl_test 1.2.2.1 { CREATE VIRTUAL TABLE docs USING fts3 } |
| 184 |
write_test 1.2.2.2 docs_content { |
| 185 |
INSERT INTO docs VALUES('Others translate the first clause as'); |
| 186 |
} |
| 187 |
write_test 1.2.2.3 docs_content { |
| 188 |
INSERT INTO docs VALUES('"which is for Solomon," meaning that'); |
| 189 |
} |
| 190 |
write_test 1.2.2.4 docs_content { |
| 191 |
INSERT INTO docs VALUES('the book is dedicated to Solomon.'); |
| 192 |
} |
| 193 |
read_test 1.2.2.5 { SELECT count(*) FROM docs_segdir } {3} |
| 194 |
write_test 1.2.2.6 docs_segdir { |
| 195 |
INSERT INTO docs(docs) VALUES('optimize'); |
| 196 |
} |
| 197 |
read_test 1.2.2.7 { SELECT count(*) FROM docs_segdir } {1} |
| 198 |
ddl_test 1.2.2.8 { DROP TABLE docs } |
| 199 |
|
| 200 |
########################################################################## |
| 201 |
# Test the examples in section 1.3 (querying FTS3 tables) |
| 202 |
# |
| 203 |
ddl_test 1.3.1.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) } |
| 204 |
read_test 1.3.1.2 { |
| 205 |
SELECT * FROM mail WHERE rowid = 15; -- Fast. Rowid lookup. |
| 206 |
SELECT * FROM mail WHERE body MATCH 'sqlite'; -- Fast. Full-text query. |
| 207 |
SELECT * FROM mail WHERE mail MATCH 'search'; -- Fast. Full-text query. |
| 208 |
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; -- Slow. Linear scan. |
| 209 |
SELECT * FROM mail WHERE subject = 'database'; -- Slow. Linear scan. |
| 210 |
SELECT * FROM mail WHERE subject MATCH 'database'; -- Fast. Full-text query. |
| 211 |
} {} |
| 212 |
ddl_test 1.3.1.3 { DROP TABLE mail } |
| 213 |
|
| 214 |
ddl_test 1.3.2.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) } |
| 215 |
|
| 216 |
write_test 1.3.2.2 mail_content { |
| 217 |
INSERT INTO mail(docid, subject, body) |
| 218 |
VALUES(1, 'software feedback', 'found it too slow') |
| 219 |
} |
| 220 |
write_test 1.3.2.3 mail_content { |
| 221 |
INSERT INTO mail(docid, subject, body) |
| 222 |
VALUES(2, 'software feedback', 'no feedback') |
| 223 |
} |
| 224 |
write_test 1.3.2.4 mail_content { |
| 225 |
INSERT INTO mail(docid, subject, body) |
| 226 |
VALUES(3, 'slow lunch order', 'was a software problem') |
| 227 |
} |
| 228 |
read_test 1.3.2.5 { |
| 229 |
SELECT * FROM mail WHERE subject MATCH 'software' |
| 230 |
} {{software feedback} {found it too slow} {software feedback} {no feedback}} |
| 231 |
read_test 1.3.2.6 { |
| 232 |
SELECT * FROM mail WHERE body MATCH 'feedback' |
| 233 |
} {{software feedback} {no feedback}} |
| 234 |
read_test 1.3.2.7 { |
| 235 |
SELECT * FROM mail WHERE mail MATCH 'software' |
| 236 |
} {{software feedback} {found it too slow} {software feedback} {no feedback} {slow lunch order} {was a software problem}} |
| 237 |
read_test 1.3.2.7 { |
| 238 |
SELECT * FROM mail WHERE mail MATCH 'slow' |
| 239 |
} {{software feedback} {found it too slow} {slow lunch order} {was a software problem}} |
| 240 |
ddl_test 1.3.2.8 { DROP TABLE mail } |
| 241 |
|
| 242 |
ddl_test 1.3.3.1 { CREATE VIRTUAL TABLE docs USING fts3(content) } |
| 243 |
read_test 1.3.3.2 { SELECT * FROM docs WHERE docs MATCH 'sqlite' } {} |
| 244 |
read_test 1.3.3.3 { SELECT * FROM docs WHERE docs.docs MATCH 'sqlite' } {} |
| 245 |
read_test 1.3.3.4 { SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite' } {} |
| 246 |
do_error_test e_fts3-1.3.3.5 { |
| 247 |
SELECT * FROM docs WHERE main.docs MATCH 'sqlite' |
| 248 |
} {no such column: main.docs} |
| 249 |
ddl_test 1.3.2.8 { DROP TABLE docs } |
| 250 |
|
| 251 |
########################################################################## |
| 252 |
# Test the examples in section 3 (full-text index queries). |
| 253 |
# |
| 254 |
ddl_test 1.4.1.1 { CREATE VIRTUAL TABLE docs USING fts3(title, body) } |
| 255 |
unset -nocomplain R |
| 256 |
foreach {tn title body} { |
| 257 |
2 "linux driver" "a device" |
| 258 |
3 "driver" "linguistic trick" |
| 259 |
4 "problems" "linux problems" |
| 260 |
5 "linux" "big problems" |
| 261 |
6 "linux driver" "a device driver problem" |
| 262 |
7 "good times" "applications for linux" |
| 263 |
8 "not so good" "linux applications" |
| 264 |
9 "alternative" "linoleum appliances" |
| 265 |
10 "no L I N" "to be seen" |
| 266 |
} { |
| 267 |
write_test 1.4.1.$tn docs_content { INSERT INTO docs VALUES($title,$body) } |
| 268 |
set R($tn) [list $title $body] |
| 269 |
} |
| 270 |
|
| 271 |
read_test 1.4.1.11 { |
| 272 |
SELECT * FROM docs WHERE docs MATCH 'linux' |
| 273 |
} [concat $R(2) $R(4) $R(5) $R(6) $R(7) $R(8)] |
| 274 |
read_test 1.4.1.12 { |
| 275 |
SELECT * FROM docs WHERE docs MATCH 'lin*' |
| 276 |
} [concat $R(2) $R(3) $R(4) $R(5) $R(6) $R(7) $R(8) $R(9)] |
| 277 |
read_test 1.4.1.13 { |
| 278 |
SELECT * FROM docs WHERE docs MATCH 'title:linux problems' |
| 279 |
} [concat $R(5)] |
| 280 |
read_test 1.4.1.14 { |
| 281 |
SELECT * FROM docs WHERE body MATCH 'title:linux driver' |
| 282 |
} [concat $R(6)] |
| 283 |
read_test 1.4.1.15 { |
| 284 |
SELECT * FROM docs WHERE docs MATCH '"linux applications"' |
| 285 |
} [concat $R(8)] |
| 286 |
read_test 1.4.1.16 { |
| 287 |
SELECT * FROM docs WHERE docs MATCH '"lin* app*"' |
| 288 |
} [concat $R(8) $R(9)] |
| 289 |
ddl_test 1.4.1.17 { DROP TABLE docs } |
| 290 |
unset R |
| 291 |
|
| 292 |
ddl_test 1.4.2.1 { CREATE VIRTUAL TABLE docs USING fts3() } |
| 293 |
write_test 1.4.2.2 docs_content { |
| 294 |
INSERT INTO docs VALUES( |
| 295 |
'SQLite is an ACID compliant embedded relational database management system') |
| 296 |
} |
| 297 |
foreach {tn query hit} { |
| 298 |
3 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database'} 1 |
| 299 |
4 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite'} 1 |
| 300 |
5 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite'} 0 |
| 301 |
6 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"'} 1 |
| 302 |
7 {SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite'} 1 |
| 303 |
8 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational'} 1 |
| 304 |
9 {SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational'} 0 |
| 305 |
} { |
| 306 |
set res [db eval {SELECT * FROM docs WHERE $hit}] |
| 307 |
read_test 1.4.2.$tn $query $res |
| 308 |
} |
| 309 |
ddl_test 1.4.2.10 { DROP TABLE docs } |
| 310 |
|
| 311 |
########################################################################## |
| 312 |
# Test the example in section 3.1 (set operators with enhanced syntax). |
| 313 |
# |
| 314 |
set sqlite_fts3_enable_parentheses 1 |
| 315 |
ddl_test 1.5.1.1 { CREATE VIRTUAL TABLE docs USING fts3() } |
| 316 |
foreach {tn docid content} { |
| 317 |
2 1 "a database is a software system" |
| 318 |
3 2 "sqlite is a software system" |
| 319 |
4 3 "sqlite is a database" |
| 320 |
} { |
| 321 |
set R($docid) $content |
| 322 |
write_test 1.5.1.$tn docs_content { |
| 323 |
INSERT INTO docs(docid, content) VALUES($docid, $content) |
| 324 |
} |
| 325 |
} |
| 326 |
read_test 1.5.1.4 { |
| 327 |
SELECT * FROM docs WHERE docs MATCH 'sqlite AND database' |
| 328 |
} [list $R(3)] |
| 329 |
read_test 1.5.1.5 { |
| 330 |
SELECT * FROM docs WHERE docs MATCH 'database sqlite' |
| 331 |
} [list $R(3)] |
| 332 |
read_test 1.5.1.6 { |
| 333 |
SELECT * FROM docs WHERE docs MATCH 'sqlite OR database' |
| 334 |
} [list $R(1) $R(2) $R(3)] |
| 335 |
read_test 1.5.1.7 { |
| 336 |
SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite' |
| 337 |
} [list $R(1)] |
| 338 |
read_test 1.5.1.8 { |
| 339 |
SELECT * FROM docs WHERE docs MATCH 'database and sqlite' |
| 340 |
} {} |
| 341 |
|
| 342 |
write_test 1.5.2.1 docs_content { |
| 343 |
INSERT INTO docs |
| 344 |
SELECT 'sqlite is also a library' UNION ALL |
| 345 |
SELECT 'library software' |
| 346 |
} |
| 347 |
read_test 1.5.2.2 { |
| 348 |
SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database OR library' |
| 349 |
} {3 4 5} |
| 350 |
read_test 1.5.2.3 { |
| 351 |
SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database' |
| 352 |
UNION |
| 353 |
SELECT docid FROM docs WHERE docs MATCH 'library' |
| 354 |
} {3 4 5} |
| 355 |
write_test 1.5.2.4 docs_content { |
| 356 |
INSERT INTO docs |
| 357 |
SELECT 'the sqlite library runs on linux' UNION ALL |
| 358 |
SELECT 'as does the sqlite database (on linux)' UNION ALL |
| 359 |
SELECT 'the sqlite database is accessed by the sqlite library' |
| 360 |
} |
| 361 |
read_test 1.5.2.2 { |
| 362 |
SELECT docid FROM docs |
| 363 |
WHERE docs MATCH '("sqlite database" OR "sqlite library") AND linux'; |
| 364 |
} {6 7} |
| 365 |
read_test 1.5.2.3 { |
| 366 |
SELECT docid FROM docs WHERE docs MATCH 'linux' |
| 367 |
INTERSECT |
| 368 |
SELECT docid FROM ( |
| 369 |
SELECT docid FROM docs WHERE docs MATCH '"sqlite library"' |
| 370 |
UNION |
| 371 |
SELECT docid FROM docs WHERE docs MATCH '"sqlite database"' |
| 372 |
); |
| 373 |
} {6 7} |
| 374 |
|
| 375 |
########################################################################## |
| 376 |
# Test the examples in section 3.2 (set operators with standard syntax). |
| 377 |
# These tests reuse the table populated by the block above. |
| 378 |
# |
| 379 |
set sqlite_fts3_enable_parentheses 0 |
| 380 |
read_test 1.6.1.1 { |
| 381 |
SELECT * FROM docs WHERE docs MATCH 'sqlite -database' |
| 382 |
} {{sqlite is a software system} {sqlite is also a library} {the sqlite library runs on linux}} |
| 383 |
read_test 1.6.1.2 { |
| 384 |
SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library' |
| 385 |
} {{sqlite is also a library} {the sqlite library runs on linux} {the sqlite database is accessed by the sqlite library}} |
| 386 |
|
| 387 |
set sqlite_fts3_enable_parentheses 1 |
| 388 |
read_test 1.6.1.3 { |
| 389 |
SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library' |
| 390 |
} {{sqlite is a software system} {sqlite is a database} {sqlite is also a library} {the sqlite library runs on linux} {as does the sqlite database (on linux)} {the sqlite database is accessed by the sqlite library}} |
| 391 |
read_test 1.6.1.4 { |
| 392 |
SELECT * FROM docs WHERE docs MATCH '(sqlite OR database) library' |
| 393 |
} {{sqlite is also a library} {the sqlite library runs on linux} {the sqlite database is accessed by the sqlite library}} |
| 394 |
set sqlite_fts3_enable_parentheses 0 |
| 395 |
ddl_test 1.6.1.5 { DROP TABLE docs } |
| 396 |
|
| 397 |
########################################################################## |
| 398 |
# Test the examples in section 4 (auxillary functions). |
| 399 |
# |
| 400 |
ddl_test 1.7.1.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) } |
| 401 |
|
| 402 |
write_test 1.7.1.2 mail_content { |
| 403 |
INSERT INTO mail VALUES( |
| 404 |
'hello world', 'This message is a hello world message.'); |
| 405 |
} |
| 406 |
write_test 1.7.1.3 mail_content { |
| 407 |
INSERT INTO mail VALUES( |
| 408 |
'urgent: serious', 'This mail is seen as a more serious mail'); |
| 409 |
} |
| 410 |
|
| 411 |
read_test 1.7.1.4 { |
| 412 |
SELECT offsets(mail) FROM mail WHERE mail MATCH 'world'; |
| 413 |
} {{0 0 6 5 1 0 24 5}} |
| 414 |
read_test 1.7.1.5 { |
| 415 |
SELECT offsets(mail) FROM mail WHERE mail MATCH 'message' |
| 416 |
} {{1 0 5 7 1 0 30 7}} |
| 417 |
read_test 1.7.1.6 { |
| 418 |
SELECT offsets(mail) FROM mail WHERE mail MATCH '"serious mail"' |
| 419 |
} {{1 0 28 7 1 1 36 4}} |
| 420 |
|
| 421 |
ddl_test 1.7.2.1 { CREATE VIRTUAL TABLE text USING fts3() } |
| 422 |
|
| 423 |
write_test 1.7.2.2 text_content { |
| 424 |
INSERT INTO text VALUES(' |
| 425 |
During 30 Nov-1 Dec, 2-3oC drops. Cool in the upper portion, minimum temperature 14-16oC and cool elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops, minimum temperature 6-12oC. Northeasterly winds 15-30 km/hr. After that, temperature increases. Northeasterly winds 15-30 km/hr. |
| 426 |
'); |
| 427 |
} |
| 428 |
|
| 429 |
read_test 1.7.2.3 { |
| 430 |
SELECT snippet(text) FROM text WHERE text MATCH 'cold' |
| 431 |
} {{<b>...</b>cool elsewhere, minimum temperature 17-20oC. <b>Cold</b> to very <b>cold</b> on mountaintops, minimum temperature 6<b>...</b>}} |
| 432 |
|
| 433 |
read_test 1.7.2.4 { |
| 434 |
SELECT snippet(text, '[', ']', '...') FROM text WHERE text MATCH '"min* tem*"' |
| 435 |
} {{...the upper portion, [minimum] [temperature] 14-16oC and cool elsewhere, [minimum] [temperature] 17-20oC. Cold...}} |
| 436 |
|
| 437 |
ddl_test 1.7.3.1 { DROP TABLE IF EXISTS t1 } |
| 438 |
ddl_test 1.7.3.2 { CREATE VIRTUAL TABLE t1 USING fts3(a, b) } |
| 439 |
write_test 1.7.3.3 t1_content { |
| 440 |
INSERT INTO t1 VALUES( |
| 441 |
'transaction default models default', 'Non transaction reads'); |
| 442 |
} |
| 443 |
write_test 1.7.3.4 t1_content { |
| 444 |
INSERT INTO t1 VALUES('the default transaction', 'these semantics present'); |
| 445 |
} |
| 446 |
write_test 1.7.3.5 t1_content { |
| 447 |
INSERT INTO t1 VALUES('single request', 'default data'); |
| 448 |
} |
| 449 |
read_test 1.7.3.6 { |
| 450 |
SELECT mit(matchinfo(t1)) FROM t1 |
| 451 |
WHERE t1 MATCH 'default transaction "these semantics"'; |
| 452 |
} {{3 2 1 3 2 0 1 1 1 2 2 0 1 1 0 0 0 1 1 1}} |
| 453 |
|
| 454 |
########################################################################## |
| 455 |
# Test the example in section 5 (custom tokenizers). |
| 456 |
# |
| 457 |
ddl_test 1.8.1.1 { CREATE VIRTUAL TABLE simple USING fts3(tokenize=simple) } |
| 458 |
write_test 1.8.1.2 simple_content { |
| 459 |
INSERT INTO simple VALUES('Right now they''re very frustrated') |
| 460 |
} |
| 461 |
read_test 1.8.1.3 {SELECT docid FROM simple WHERE simple MATCH 'Frustrated'} {1} |
| 462 |
read_test 1.8.1.4 {SELECT docid FROM simple WHERE simple MATCH 'Frustration'} {} |
| 463 |
|
| 464 |
ddl_test 1.8.2.1 { CREATE VIRTUAL TABLE porter USING fts3(tokenize=porter) } |
| 465 |
write_test 1.8.2.2 porter_content { |
| 466 |
INSERT INTO porter VALUES('Right now they''re very frustrated') |
| 467 |
} |
| 468 |
read_test 1.8.2.4 { |
| 469 |
SELECT docid FROM porter WHERE porter MATCH 'Frustration' |
| 470 |
} {1} |
| 471 |
|
| 472 |
} |
| 473 |
# End of tests of example code in fts3.html |
| 474 |
#------------------------------------------------------------------------- |
| 475 |
|
| 476 |
#------------------------------------------------------------------------- |
| 477 |
# Test that errors in the arguments passed to the snippet and offsets |
| 478 |
# functions are handled correctly. |
| 479 |
# |
| 480 |
set DO_MALLOC_TEST 0 |
| 481 |
ddl_test 2.1.0 { DROP TABLE IF EXISTS t1 } |
| 482 |
ddl_test 2.1.1 { CREATE VIRTUAL TABLE t1 USING fts3(a, b) } |
| 483 |
write_test 2.1.2 t1_content { |
| 484 |
INSERT INTO t1 VALUES('one two three', x'A1B2C3D4E5F6'); |
| 485 |
} |
| 486 |
error_test 2.1.3 { |
| 487 |
SELECT offsets(a) FROM t1 WHERE a MATCH 'one' |
| 488 |
} {illegal first argument to offsets} |
| 489 |
error_test 2.1.4 { |
| 490 |
SELECT offsets(b) FROM t1 WHERE a MATCH 'one' |
| 491 |
} {illegal first argument to offsets} |
| 492 |
error_test 2.1.5 { |
| 493 |
SELECT optimize(a) FROM t1 LIMIT 1 |
| 494 |
} {illegal first argument to optimize} |
| 495 |
error_test 2.1.6 { |
| 496 |
SELECT snippet(a) FROM t1 WHERE a MATCH 'one' |
| 497 |
} {illegal first argument to snippet} |
| 498 |
error_test 2.1.7 { |
| 499 |
SELECT snippet() FROM t1 WHERE a MATCH 'one' |
| 500 |
} {unable to use function snippet in the requested context} |
| 501 |
error_test 2.1.8 { |
| 502 |
SELECT snippet(a, b, 'A', 'B', 'C', 'D', 'E') FROM t1 WHERE a MATCH 'one' |
| 503 |
} {wrong number of arguments to function snippet()} |
| 504 |
#------------------------------------------------------------------------- |
| 505 |
|
| 506 |
#------------------------------------------------------------------------- |
| 507 |
# Test the effect of an OOM error while installing the FTS3 module (i.e. |
| 508 |
# opening a database handle). This case was not tested by the OOM testing |
| 509 |
# of the document examples above. |
| 510 |
# |
| 511 |
do_malloc_test e_fts3-3 -tclbody { |
| 512 |
if {[catch {sqlite3 db test.db}]} { error "out of memory" } |
| 513 |
} |
| 514 |
#------------------------------------------------------------------------- |
| 515 |
|
| 516 |
#------------------------------------------------------------------------- |
| 517 |
# Verify the return values of the optimize() function. If no error occurs, |
| 518 |
# the returned value should be "Index optimized" if the data structure |
| 519 |
# was modified, or "Index already optimal" if it were not. |
| 520 |
# |
| 521 |
set DO_MALLOC_TEST 0 |
| 522 |
ddl_test 4.1 { CREATE VIRTUAL TABLE t4 USING fts3(a, b) } |
| 523 |
write_test 4.2 t4_content { |
| 524 |
INSERT INTO t4 VALUES('In Xanadu', 'did Kubla Khan'); |
| 525 |
} |
| 526 |
write_test 4.3 t4_content { |
| 527 |
INSERT INTO t4 VALUES('a stately pleasure', 'dome decree'); |
| 528 |
} |
| 529 |
do_test e_fts3-4.4 { |
| 530 |
execsql { SELECT optimize(t4) FROM t4 LIMIT 1 } |
| 531 |
} {{Index optimized}} |
| 532 |
do_test e_fts3-4.5 { |
| 533 |
execsql { SELECT optimize(t4) FROM t4 LIMIT 1 } |
| 534 |
} {{Index already optimal}} |
| 535 |
#------------------------------------------------------------------------- |
| 536 |
|
| 537 |
#------------------------------------------------------------------------- |
| 538 |
# Test that the snippet function appears to work correctly with 1, 2, 3 |
| 539 |
# or 4 arguments passed to it. |
| 540 |
# |
| 541 |
set DO_MALLOC_TEST 0 |
| 542 |
ddl_test 5.1 { CREATE VIRTUAL TABLE t5 USING fts3(x) } |
| 543 |
write_test 5.2 t5_content { |
| 544 |
INSERT INTO t5 VALUES('In Xanadu did Kubla Khan A stately pleasure-dome decree Where Alph, the sacred river, ran Through caverns measureless to man Down to a sunless sea. So twice five miles of fertile ground With walls and towers were girdled round : And there were gardens bright with sinuous rills, Where blossomed many an incense-bearing tree ; And here were forests ancient as the hills, Enfolding sunny spots of greenery.'); |
| 545 |
} |
| 546 |
read_test 5.3 { |
| 547 |
SELECT snippet(t5) FROM t5 WHERE t5 MATCH 'miles' |
| 548 |
} {{<b>...</b>to a sunless sea. So twice five <b>miles</b> of fertile ground With walls and towers<b>...</b>}} |
| 549 |
read_test 5.4 { |
| 550 |
SELECT snippet(t5, '<i>') FROM t5 WHERE t5 MATCH 'miles' |
| 551 |
} {{<b>...</b>to a sunless sea. So twice five <i>miles</b> of fertile ground With walls and towers<b>...</b>}} |
| 552 |
read_test 5.5 { |
| 553 |
SELECT snippet(t5, '<i>', '</i>') FROM t5 WHERE t5 MATCH 'miles' |
| 554 |
} {{<b>...</b>to a sunless sea. So twice five <i>miles</i> of fertile ground With walls and towers<b>...</b>}} |
| 555 |
read_test 5.6 { |
| 556 |
SELECT snippet(t5, '<i>', '</i>', 'XXX') FROM t5 WHERE t5 MATCH 'miles' |
| 557 |
} {{XXXto a sunless sea. So twice five <i>miles</i> of fertile ground With walls and towersXXX}} |
| 558 |
#------------------------------------------------------------------------- |
| 559 |
|
| 560 |
#------------------------------------------------------------------------- |
| 561 |
# Test that an empty MATCH expression returns an empty result set. As |
| 562 |
# does passing a NULL value as a MATCH expression. |
| 563 |
# |
| 564 |
set DO_MALLOC_TEST 0 |
| 565 |
ddl_test 6.1 { CREATE VIRTUAL TABLE t6 USING fts3(x) } |
| 566 |
write_test 6.2 t5_content { INSERT INTO t6 VALUES('a'); } |
| 567 |
write_test 6.3 t5_content { INSERT INTO t6 VALUES('b'); } |
| 568 |
write_test 6.4 t5_content { INSERT INTO t6 VALUES('c'); } |
| 569 |
read_test 6.5 { SELECT * FROM t6 WHERE t6 MATCH '' } {} |
| 570 |
read_test 6.6 { SELECT * FROM t6 WHERE x MATCH '' } {} |
| 571 |
read_test 6.7 { SELECT * FROM t6 WHERE t6 MATCH NULL } {} |
| 572 |
read_test 6.8 { SELECT * FROM t6 WHERE x MATCH NULL } {} |
| 573 |
#------------------------------------------------------------------------- |
| 574 |
|
| 575 |
#------------------------------------------------------------------------- |
| 576 |
# Test a few facets of the FTS3 xFilter() callback implementation: |
| 577 |
# |
| 578 |
# 1. That the sqlite3_index_constraint.usable flag is respected. |
| 579 |
# |
| 580 |
# 2. That it is an error to use the "docid" or "rowid" column of |
| 581 |
# an FTS3 table as the LHS of a MATCH operator. |
| 582 |
# |
| 583 |
# 3. That it is an error to AND together two MATCH expressions in |
| 584 |
# that refer to a single FTS3 table in a WHERE clause. |
| 585 |
# |
| 586 |
# |
| 587 |
set DO_MALLOC_TEST 0 |
| 588 |
ddl_test 7.1.1 { CREATE VIRTUAL TABLE t7 USING fts3(a) } |
| 589 |
ddl_test 7.1.2 { CREATE VIRTUAL TABLE t8 USING fts3(b) } |
| 590 |
write_test 7.1.3 t7_content { INSERT INTO t7(docid, a) VALUES(4,'number four') } |
| 591 |
write_test 7.1.4 t7_content { INSERT INTO t7(docid, a) VALUES(5,'number five') } |
| 592 |
write_test 7.1.5 t8_content { INSERT INTO t8(docid, b) VALUES(4,'letter D') } |
| 593 |
write_test 7.1.6 t8_content { INSERT INTO t8(docid, b) VALUES(5,'letter E') } |
| 594 |
read_test 7.1.7 { |
| 595 |
SELECT a || ':' || b FROM t7 JOIN t8 USING(docid) |
| 596 |
} {{number four:letter D} {number five:letter E}} |
| 597 |
|
| 598 |
error_test 7.2.1 { |
| 599 |
SELECT * FROM t7 WHERE docid MATCH 'number' |
| 600 |
} {unable to use function MATCH in the requested context} |
| 601 |
error_test 7.2.2 { |
| 602 |
SELECT * FROM t7 WHERE rowid MATCH 'number' |
| 603 |
} {unable to use function MATCH in the requested context} |
| 604 |
|
| 605 |
error_test 7.3.1 { |
| 606 |
SELECT * FROM t7 WHERE a MATCH 'number' AND a MATCH 'four' |
| 607 |
} {unable to use function MATCH in the requested context} |
| 608 |
error_test 7.3.2 { |
| 609 |
SELECT * FROM t7, t8 WHERE a MATCH 'number' AND a MATCH 'four' |
| 610 |
} {unable to use function MATCH in the requested context} |
| 611 |
error_test 7.3.3 { |
| 612 |
SELECT * FROM t7, t8 WHERE b MATCH 'letter' AND b MATCH 'd' |
| 613 |
} {unable to use function MATCH in the requested context} |
| 614 |
read_test 7.3.4 { |
| 615 |
SELECT * FROM t7, t8 WHERE a MATCH 'number' AND b MATCH 'letter' |
| 616 |
} {{number four} {letter D} {number four} {letter E} {number five} {letter D} {number five} {letter E}} |
| 617 |
read_test 7.3.5 { |
| 618 |
SELECT * FROM t7 WHERE a MATCH 'number' AND docid = 4 |
| 619 |
} {{number four}} |
| 620 |
|
| 621 |
#------------------------------------------------------------------------- |
| 622 |
# Test the quoting of FTS3 table column names. Names may be quoted using |
| 623 |
# any of "", '', ``` or []. |
| 624 |
# |
| 625 |
set DO_MALLOC_TEST 0 |
| 626 |
ddl_test 8.1.1 { CREATE VIRTUAL TABLE t9a USING fts3("c1", [c2]) } |
| 627 |
ddl_test 8.1.2 { CREATE VIRTUAL TABLE t9b USING fts3('c1', `c2`) } |
| 628 |
read_test 8.1.3 { PRAGMA table_info(t9a) } {0 c1 {} 0 {} 0 1 c2 {} 0 {} 0} |
| 629 |
read_test 8.1.4 { PRAGMA table_info(t9b) } {0 c1 {} 0 {} 0 1 c2 {} 0 {} 0} |
| 630 |
ddl_test 8.2.1 { CREATE VIRTUAL TABLE t9c USING fts3("c""1", 'c''2') } |
| 631 |
read_test 8.2.2 { PRAGMA table_info(t9c) } {0 c\"1 {} 0 {} 0 1 c'2 {} 0 {} 0} |
| 632 |
#------------------------------------------------------------------------- |
| 633 |
|
| 634 |
#------------------------------------------------------------------------- |
| 635 |
# Test that FTS3 tables can be renamed using the ALTER RENAME command. |
| 636 |
# OOM errors are tested during ALTER RENAME commands also. |
| 637 |
# |
| 638 |
foreach DO_MALLOC_TEST {0 1 2} { |
| 639 |
db close |
| 640 |
forcedelete test.db test.db-journal |
| 641 |
sqlite3 db test.db |
| 642 |
if {$DO_MALLOC_TEST} { sqlite3_db_config_lookaside db 0 0 0 } |
| 643 |
|
| 644 |
ddl_test 9.1.1 { CREATE VIRTUAL TABLE t10 USING fts3(x) } |
| 645 |
write_test 9.1.2 t10_content { INSERT INTO t10 VALUES('fts3 tables') } |
| 646 |
write_test 9.1.3 t10_content { INSERT INTO t10 VALUES('are renameable') } |
| 647 |
|
| 648 |
read_test 9.1.4 { |
| 649 |
SELECT * FROM t10 WHERE t10 MATCH 'table*' |
| 650 |
} {{fts3 tables}} |
| 651 |
read_test 9.1.5 { |
| 652 |
SELECT * FROM t10 WHERE x MATCH 'rename*' |
| 653 |
} {{are renameable}} |
| 654 |
|
| 655 |
ddl_test 9.1.6 { ALTER TABLE t10 RENAME TO t11 } |
| 656 |
|
| 657 |
read_test 9.1.7 { |
| 658 |
SELECT * FROM t11 WHERE t11 MATCH 'table*' |
| 659 |
} {{fts3 tables}} |
| 660 |
read_test 9.1.8 { |
| 661 |
SELECT * FROM t11 WHERE x MATCH 'rename*' |
| 662 |
} {{are renameable}} |
| 663 |
} |
| 664 |
#------------------------------------------------------------------------- |
| 665 |
|
| 666 |
#------------------------------------------------------------------------- |
| 667 |
# Test a couple of cases involving corrupt data structures: |
| 668 |
# |
| 669 |
# 1) A case where a document referenced by the full-text index is |
| 670 |
# not present in the %_content table. |
| 671 |
# |
| 672 |
# 2) A badly formatted b-tree segment node. |
| 673 |
# |
| 674 |
set DO_MALLOC_TEST 0 |
| 675 |
ddl_test 10.1.1 { CREATE VIRTUAL TABLE ta USING fts3 } |
| 676 |
write_test 10.1.2 ta_content { |
| 677 |
INSERT INTO ta VALUES('During a summer vacation in 1790') } |
| 678 |
write_test 10.1.3 ta_content { |
| 679 |
INSERT INTO ta VALUES('Wordsworth went on a walking tour') } |
| 680 |
sqlite3_db_config db DEFENSIVE 0 |
| 681 |
write_test 10.1.4 ta_content { DELETE FROM ta_content WHERE rowid = 2 } |
| 682 |
read_test 10.1.5 { |
| 683 |
SELECT * FROM ta WHERE ta MATCH 'summer' |
| 684 |
} {{During a summer vacation in 1790}} |
| 685 |
error_test 10.1.6 { |
| 686 |
SELECT * FROM ta WHERE ta MATCH 'walking' |
| 687 |
} {database disk image is malformed} |
| 688 |
|
| 689 |
write_test 10.2.1 ta_content { DELETE FROM ta } |
| 690 |
write_test 10.2.2 ta_content { |
| 691 |
INSERT INTO ta VALUES('debate demonstrated the rising difficulty') } |
| 692 |
write_test 10.2.3 ta_content { |
| 693 |
INSERT INTO ta VALUES('Google released its browser beta') } |
| 694 |
|
| 695 |
set blob [db one {SELECT root FROM ta_segdir WHERE rowid = 2}] |
| 696 |
binary scan $blob "a6 a3 a*" start middle end |
| 697 |
set middle "\x0E\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\x06\x06" |
| 698 |
set blob [binary format "a6 a* a*" $start $middle $end] |
| 699 |
write_test 10.2.4 ta_segdir { |
| 700 |
UPDATE ta_segdir SET root = $blob WHERE rowid = 2 |
| 701 |
} |
| 702 |
error_test 10.2.5 { |
| 703 |
SELECT * FROM ta WHERE ta MATCH 'beta' |
| 704 |
} {database disk image is malformed} |
| 705 |
|
| 706 |
|
| 707 |
finish_test |