# 2009 November 28 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file implements tests to verify the "testable statements" in the # fts3.in document. # set testdir [file dirname $argv0] source $testdir/tester.tcl # If this build does not include FTS3, skip the tests in this file. # ifcapable !fts3 { finish_test ; return } source $testdir/fts3_common.tcl # Procs used to make the tests in this file easier to read. # proc ddl_test {tn ddl} { uplevel [list do_write_test e_fts3-$tn sqlite_master $ddl] } proc write_test {tn tbl sql} { uplevel [list do_write_test e_fts3-$tn $tbl $sql] } proc read_test {tn sql result} { uplevel [list do_select_test e_fts3-$tn $sql $result] } #------------------------------------------------------------------------- # The body of the following [foreach] block contains test cases to verify # that the example code in fts3.html works as expected. The tests run three # times, with different values for DO_MALLOC_TEST. # # DO_MALLOC_TEST=0: Run tests with no OOM errors. # DO_MALLOC_TEST=1: Run tests with transient OOM errors. # DO_MALLOC_TEST=2: Run tests with persistent OOM errors. # foreach DO_MALLOC_TEST {0 1 2} { # Reset the database and database connection. If this iteration of the # [foreach] loop is testing with OOM errors, disable the lookaside buffer. # db close file delete -force test.db test.db-journal sqlite3 db test.db if {$DO_MALLOC_TEST} { sqlite3_db_config_lookaside db 0 0 0 } ########################################################################## # Test the example CREATE VIRTUAL TABLE statements in section 1.1 # of fts3.in. # ddl_test 1.1.1.1 {CREATE VIRTUAL TABLE data USING fts3()} read_test 1.1.1.2 {PRAGMA table_info(data)} {0 content {} 0 {} 0} ddl_test 1.1.2.1 { CREATE VIRTUAL TABLE pages USING fts3(title, keywords, body) } read_test 1.1.2.2 { PRAGMA table_info(pages) } {0 title {} 0 {} 0 1 keywords {} 0 {} 0 2 body {} 0 {} 0} ddl_test 1.1.3.1 { CREATE VIRTUAL TABLE mail USING fts3( subject VARCHAR(256) NOT NULL, body TEXT CHECK(length(body)<10240) ) } read_test 1.1.3.2 { PRAGMA table_info(mail) } {0 subject {} 0 {} 0 1 body {} 0 {} 0} # A very large string. Used to test if the constraint on column "body" of # table "mail" is enforced (it should not be - FTS3 tables do not support # constraints). set largetext [string repeat "the quick brown fox " 5000] write_test 1.1.3.3 mail_content { INSERT INTO mail VALUES(NULL, $largetext) } read_test 1.1.3.4 { SELECT subject IS NULL, length(body) FROM mail } [list 1 100000] ddl_test 1.1.4.1 { CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter) } read_test 1.1.4.2 { PRAGMA table_info(papers) } {0 author {} 0 {} 0 1 document {} 0 {} 0} ddl_test 1.1.5.1 { CREATE VIRTUAL TABLE simpledata USING fts3(tokenize=simple) } read_test 1.1.5.2 { PRAGMA table_info(simpledata) } {0 content {} 0 {} 0} ifcapable icu { ddl_test 1.1.6.1 { CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU) } read_test 1.1.6.2 { PRAGMA table_info(names) } {0 a {} 0 {} 0 1 b {} 0 {} 0} } ddl_test 1.1.7.1 {DROP TABLE data} ddl_test 1.1.7.2 {DROP TABLE pages} ddl_test 1.1.7.3 {DROP TABLE mail} ddl_test 1.1.7.4 {DROP TABLE papers} ddl_test 1.1.7.5 {DROP TABLE simpledata} read_test 1.1.7.6 {SELECT * FROM sqlite_master} {} # The following is not one of the examples in section 1.1. It tests # specifying an FTS3 table with no module arguments using a slightly # different syntax. ddl_test 1.1.8.1 {CREATE VIRTUAL TABLE data USING fts3;} read_test 1.1.8.2 {PRAGMA table_info(data)} {0 content {} 0 {} 0} ddl_test 1.1.8.3 {DROP TABLE data} ########################################################################## ########################################################################## # Test the examples in section 1.2 (populating fts3 tables) # ddl_test 1.2.1.1 { CREATE VIRTUAL TABLE pages USING fts3(title, body); } write_test 1.2.1.2 pages_content { INSERT INTO pages(docid, title, body) VALUES(53, 'Home Page', 'SQLite is a software...'); } read_test 1.2.1.3 { SELECT docid, * FROM pages } {53 {Home Page} {SQLite is a software...}} write_test 1.2.1.4 pages_content { INSERT INTO pages(title, body) VALUES('Download', 'All SQLite source code...'); } read_test 1.2.1.5 { SELECT docid, * FROM pages } {53 {Home Page} {SQLite is a software...} 54 Download {All SQLite source code...}} write_test 1.2.1.6 pages_content { UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54 } read_test 1.2.1.7 { SELECT docid, * FROM pages } {53 {Home Page} {SQLite is a software...} 54 {Download SQLite} {All SQLite source code...}} write_test 1.2.1.8 pages_content { DELETE FROM pages } read_test 1.2.1.9 { SELECT docid, * FROM pages } {} do_error_test fts3-1.2.1.10 { INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body'); } {SQL logic error or missing database} # Test the optimize() function example: ddl_test 1.2.2.1 { CREATE VIRTUAL TABLE docs USING fts3 } write_test 1.2.2.2 docs_content { INSERT INTO docs VALUES('Others translate the first clause as'); } write_test 1.2.2.3 docs_content { INSERT INTO docs VALUES('"which is for Solomon," meaning that'); } write_test 1.2.2.4 docs_content { INSERT INTO docs VALUES('the book is dedicated to Solomon.'); } read_test 1.2.2.5 { SELECT count(*) FROM docs_segdir } {3} #set DO_MALLOC_TEST 1 write_test 1.2.2.6 docs_segdir { SELECT * FROM (SELECT optimize(docs) FROM docs LIMIT 1) WHERE 0; } read_test 1.2.2.7 { SELECT count(*) FROM docs_segdir } {1} ddl_test 1.2.2.8 { DROP TABLE docs } ########################################################################## # Test the examples in section 1.3 (querying FTS3 tables) # ddl_test 1.3.1.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) } read_test 1.3.1.2 { SELECT * FROM mail WHERE rowid = 15; -- Fast. Rowid lookup. SELECT * FROM mail WHERE body MATCH 'sqlite'; -- Fast. Full-text query. SELECT * FROM mail WHERE mail MATCH 'search'; -- Fast. Full-text query. SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; -- Slow. Linear scan. SELECT * FROM mail WHERE subject = 'database'; -- Slow. Linear scan. SELECT * FROM mail WHERE subject MATCH 'database'; -- Fast. Full-text query. } {} ddl_test 1.3.1.3 { DROP TABLE mail } ddl_test 1.3.2.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) } write_test 1.3.2.2 mail_content { INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow') } write_test 1.3.2.3 mail_content { INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback') } write_test 1.3.2.4 mail_content { INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order', 'was a software problem') } read_test 1.3.2.5 { SELECT * FROM mail WHERE subject MATCH 'software' } {{software feedback} {found it too slow} {software feedback} {no feedback}} read_test 1.3.2.6 { SELECT * FROM mail WHERE body MATCH 'feedback' } {{software feedback} {no feedback}} read_test 1.3.2.7 { SELECT * FROM mail WHERE mail MATCH 'software' } {{software feedback} {found it too slow} {software feedback} {no feedback} {slow lunch order} {was a software problem}} read_test 1.3.2.7 { SELECT * FROM mail WHERE mail MATCH 'slow' } {{software feedback} {found it too slow} {slow lunch order} {was a software problem}} ddl_test 1.3.2.8 { DROP TABLE mail } ddl_test 1.3.3.1 { CREATE VIRTUAL TABLE docs USING fts3(content) } read_test 1.3.3.2 { SELECT * FROM docs WHERE docs MATCH 'sqlite' } {} read_test 1.3.3.3 { SELECT * FROM docs WHERE docs.docs MATCH 'sqlite' } {} read_test 1.3.3.4 { SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite' } {} do_error_test e_fts3-1.3.3.5 { SELECT * FROM docs WHERE main.docs MATCH 'sqlite' } {no such column: main.docs} ddl_test 1.3.2.8 { DROP TABLE docs } ########################################################################## ########################################################################## # Test the examples in section 3 (full-text index queries). # ddl_test 1.4.1.1 { CREATE VIRTUAL TABLE docs USING fts3(title, body) } foreach {tn title body} { 2 "linux driver" "a device" 3 "driver" "linguistic trick" 4 "problems" "linux problems" 5 "linux" "big problems" 6 "linux driver" "a device driver problem" 7 "good times" "applications for linux" 8 "not so good" "linux applications" 9 "alternative" "linoleum appliances" 10 "no L I N" "to be seen" } { write_test 1.4.1.$tn docs_content { INSERT INTO docs VALUES($title,$body) } set R($tn) [list $title $body] } read_test 1.4.1.11 { SELECT * FROM docs WHERE docs MATCH 'linux' } [concat $R(2) $R(4) $R(5) $R(6) $R(7) $R(8)] read_test 1.4.1.12 { SELECT * FROM docs WHERE docs MATCH 'lin*' } [concat $R(2) $R(3) $R(4) $R(5) $R(6) $R(7) $R(8) $R(9)] read_test 1.4.1.13 { SELECT * FROM docs WHERE docs MATCH 'title:linux problems' } [concat $R(5)] read_test 1.4.1.14 { SELECT * FROM docs WHERE body MATCH 'title:linux driver' } [concat $R(6)] read_test 1.4.1.15 { SELECT * FROM docs WHERE docs MATCH '"linux applications"' } [concat $R(8)] read_test 1.4.1.16 { SELECT * FROM docs WHERE docs MATCH '"lin* app*"' } [concat $R(8) $R(9)] ddl_test 1.4.1.17 { DROP TABLE docs } unset R ddl_test 1.4.2.1 { CREATE VIRTUAL TABLE docs USING fts3() } write_test 1.4.2.2 docs_content { INSERT INTO docs VALUES( 'SQLite is an ACID compliant embedded relational database management system') } foreach {tn query hit} { 3 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database'} 1 4 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite'} 1 5 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite'} 0 6 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"'} 1 7 {SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite'} 1 8 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational'} 1 9 {SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational'} 0 } { set res [db eval {SELECT * FROM docs WHERE $hit}] read_test 1.4.2.$tn $query $res } ddl_test 1.4.2.10 { DROP TABLE docs } ########################################################################## # Test the example in section 3.1 (set operators with enhanced syntax). # set sqlite_fts3_enable_parentheses 1 ddl_test 1.5.1.1 { CREATE VIRTUAL TABLE docs USING fts3() } # TODO: Change numbering after here... ########################################################################## # Test the example in section 5 (custom tokenizers). # ddl_test 2.1.1 { CREATE VIRTUAL TABLE simple USING fts3(tokenize=simple) } write_test 2.1.2 simple_content { INSERT INTO simple VALUES('Right now they''re very frustrated') } read_test 2.1.3 {SELECT docid FROM simple WHERE simple MATCH 'Frustrated'} {1} read_test 2.1.4 {SELECT docid FROM simple WHERE simple MATCH 'Frustration'} {} ddl_test 2.2.1 { CREATE VIRTUAL TABLE porter USING fts3(tokenize=porter) } write_test 2.2.2 porter_content { INSERT INTO porter VALUES('Right now they''re very frustrated') } read_test 2.2.3 {SELECT docid FROM porter WHERE porter MATCH 'Frustrated'} {1} read_test 2.2.4 {SELECT docid FROM porter WHERE porter MATCH 'Frustration'} {1} ########################################################################## ########################################################################## # Test the examples in section 4 (auxillary functions). # ddl_test 3.1.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) } write_test 3.1.2 mail_content { INSERT INTO mail VALUES( 'hello world', 'This message is a hello world message.'); } write_test 3.1.3 mail_content { INSERT INTO mail VALUES( 'urgent: serious', 'This mail is seen as a more serious mail'); } read_test 3.1.4 { SELECT offsets(mail) FROM mail WHERE mail MATCH 'world'; } {{0 0 6 5 1 0 24 5}} read_test 3.1.5 { SELECT offsets(mail) FROM mail WHERE mail MATCH 'message' } {{1 0 5 7 1 0 30 7}} read_test 3.1.6 { SELECT offsets(mail) FROM mail WHERE mail MATCH '"serious mail"' } {{1 0 28 7 1 1 36 4}} ddl_test 3.2.1 { CREATE VIRTUAL TABLE text USING fts3() } write_test 3.2.2 text_content { INSERT INTO text VALUES(' 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. '); } read_test 3.2.3 { SELECT snippet(text) FROM text WHERE text MATCH 'cold' } {{... elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops, minimum ...}} read_test 3.2.4 { SELECT snippet(text, '[', ']', '...') FROM text WHERE text MATCH '"min* tem*"' } {{... 2-3oC drops. Cool in the upper portion, [minimum] [temperature] 14-16oC and cool elsewhere, [minimum] ...}} #break } # End of tests of example code in fts3.html #------------------------------------------------------------------------- finish_test