| blob | 2a580ca313424ca9fc8bf09ed7b6e30846e027ba |
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 #
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
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
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 }
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 } {
57 #if {$DO_MALLOC_TEST} break
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'"
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
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}
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}
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}
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]
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}
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}
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 }
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} {}
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}
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...}}
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...}}
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...}}
175 write_test 1.2.1.8 pages_content { DELETE FROM pages }
176 read_test 1.2.1.9 { SELECT docid, * FROM pages } {}
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 or missing database}
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 }
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 }
214 ddl_test 1.3.2.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) }
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 }
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 }
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 }
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
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 }
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 } {}
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}
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}}
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 }
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) }
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 }
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}}
421 ddl_test 1.7.2.1 { CREATE VIRTUAL TABLE text USING fts3() }
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 }
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>}}
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...}}
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}}
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'} {}
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}
472 }
473 # End of tests of example code in fts3.html
474 #-------------------------------------------------------------------------
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 #-------------------------------------------------------------------------
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 #-------------------------------------------------------------------------
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 #-------------------------------------------------------------------------
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 #-------------------------------------------------------------------------
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 #-------------------------------------------------------------------------
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}}
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}
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}}
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 #-------------------------------------------------------------------------
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 }
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') }
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}}
655 ddl_test 9.1.6 { ALTER TABLE t10 RENAME TO t11 }
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 #-------------------------------------------------------------------------
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 write_test 10.1.4 ta_content { DELETE FROM ta_content WHERE rowid = 2 }
681 read_test 10.1.5 {
682 SELECT * FROM ta WHERE ta MATCH 'summer'
683 } {{During a summer vacation in 1790}}
684 error_test 10.1.6 {
685 SELECT * FROM ta WHERE ta MATCH 'walking'
686 } {database disk image is malformed}
688 write_test 10.2.1 ta_content { DELETE FROM ta }
689 write_test 10.2.2 ta_content {
690 INSERT INTO ta VALUES('debate demonstrated the rising difficulty') }
691 write_test 10.2.3 ta_content {
692 INSERT INTO ta VALUES('Google released its browser beta') }
694 set blob [db one {SELECT root FROM ta_segdir WHERE rowid = 2}]
695 binary scan $blob "a6 a3 a*" start middle end
696 set middle "\x0E\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\x06\x06"
697 set blob [binary format "a6 a* a*" $start $middle $end]
698 write_test 10.2.4 ta_segdir {
699 UPDATE ta_segdir SET root = $blob WHERE rowid = 2
700 }
701 error_test 10.2.5 {
702 SELECT * FROM ta WHERE ta MATCH 'beta'
703 } {database disk image is malformed}
706 finish_test