# 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