Annotation of embedaddon/sqlite3/test/e_fts3.test, revision 1.1.1.1
1.1 misho 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 or missing database}
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: 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}
687:
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') }
693:
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}
704:
705:
706: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_fts3.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test