AryaWu/sqlite
0
1# 2009 December 202#3# The author disclaims copyright to this source code. In place of4# 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 contains tests of fts3 queries that have been useful during13# the development process as well as some that have been useful in tracking14# down bugs. They are not focused on any particular functionality.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# If this build does not include FTS3, skip the tests in this file.21#22ifcapable !fts3 { finish_test ; return }23source $testdir/malloc_common.tcl24source $testdir/fts3_common.tcl25set DO_MALLOC_TEST 026 27set testprefix fts3query28 29do_test fts3query-1.1 {30 execsql {31 CREATE VIRTUAL TABLE t1 USING fts3(x);32 BEGIN;33 INSERT INTO t1 VALUES('The source code for SQLite is in the public');34 }35} {}36 37do_select_test fts3query-1.2 {38 SELECT * FROM t1;39} {{The source code for SQLite is in the public}}40do_select_test fts3query-1.3 {41 SELECT * FROM t1 WHERE t1 MATCH 'sqlite'42} {{The source code for SQLite is in the public}}43 44do_test fts3query-1.4 { execsql {COMMIT} } {}45 46do_select_test fts3query-1.5 {47 SELECT * FROM t1;48} {{The source code for SQLite is in the public}}49do_select_test fts3query-1.6 {50 SELECT * FROM t1 WHERE t1 MATCH 'sqlite'51} {{The source code for SQLite is in the public}}52 53 54set sqlite_fts3_enable_parentheses 155do_test fts3query-2.1 {56 execsql {57 CREATE VIRTUAL TABLE zoink USING fts3;58 INSERT INTO zoink VALUES('The apple falls far from the tree');59 }60} {}61do_test fts3query-2.2 {62 execsql {63 SELECT docid FROM zoink WHERE zoink MATCH '(apple oranges) AND apple'64 }65} {}66do_test fts3query-2.3 {67 execsql {68 SELECT docid FROM zoink WHERE zoink MATCH 'apple AND (oranges apple)'69 }70} {}71set sqlite_fts3_enable_parentheses 072 73do_test fts3query-3.1 {74 execsql {75 CREATE VIRTUAL TABLE foobar using FTS3(description, tokenize porter);76 INSERT INTO foobar (description) values ('77 Filed under: Emerging Technologies, EV/Plug-in, Hybrid, Chevrolet, GM, 78 ZENN 2011 Chevy Volt - Click above for high-res image gallery There are 79 16 days left in the month of December. Besides being time for most 80 Americans to kick their Christmas shopping sessions into high gear and81 start planning their resolutions for 2010, it also means that there''s82 precious little time for EEStor to "deliver functional technology" to83 Zenn Motors as promised. Still, the promises held out by the secretive84 company are too great for us to forget about entirely. We''d love for85 EEStor''s claims to be independently verified and proven accurate, as86 would just about anyone else looking to break free of petroleum in fav87 '); 88 }89} {}90 91do_test fts3query-3.2 {92 execsql { SELECT docid FROM foobar WHERE description MATCH '"high sp d"' }93} {}94 95proc mit {blob} {96 set scan(littleEndian) i*97 set scan(bigEndian) I*98 binary scan $blob $scan($::tcl_platform(byteOrder)) r99 return $r100}101db func mit mit102 103do_test fts3query-3.3 {104 execsql { SELECT mit(matchinfo(foobar)) FROM foobar WHERE foobar MATCH 'the' }105} {{1 1 3 3 1}}106 107# The following tests check that ticket 775b39dd3c has been fixed.108#109do_test fts3query-4.1 {110 execsql {111 DROP TABLE IF EXISTS t1;112 CREATE TABLE t1(number INTEGER PRIMARY KEY, date);113 CREATE INDEX i1 ON t1(date);114 CREATE VIRTUAL TABLE ft USING fts3(title);115 CREATE TABLE bt(title);116 }117} {}118do_eqp_test fts3query-4.2 {119 SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date120} {121 QUERY PLAN122 |--SCAN t1 USING COVERING INDEX i1123 `--SCAN ft VIRTUAL TABLE INDEX 1:124}125do_eqp_test fts3query-4.3 {126 SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date127} {128 QUERY PLAN129 |--SCAN t1 USING COVERING INDEX i1130 `--SCAN ft VIRTUAL TABLE INDEX 1:131}132do_eqp_test fts3query-4.4 {133 SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date134} {135 QUERY PLAN136 |--SCAN t1 USING COVERING INDEX i1137 `--SEARCH bt USING INTEGER PRIMARY KEY (rowid=?)138}139do_eqp_test fts3query-4.5 {140 SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date141} {142 QUERY PLAN143 |--SCAN t1 USING COVERING INDEX i1144 `--SEARCH bt USING INTEGER PRIMARY KEY (rowid=?)145}146 147 148# Test that calling matchinfo() with the wrong number of arguments, or with149# an invalid argument returns an error.150#151do_execsql_test 5.1 {152 CREATE VIRTUAL TABLE t2 USING FTS4;153 INSERT INTO t2 VALUES('it was the first time in history');154}155do_select_tests 5.2 -errorformat {156 wrong number of arguments to function %s()157} {158 1 "SELECT matchinfo() FROM t2 WHERE t2 MATCH 'history'" matchinfo159 3 "SELECT snippet(t2, 1, 2, 3, 4, 5, 6) FROM t2 WHERE t2 MATCH 'history'" 160 snippet161}162do_select_tests 5.3 -errorformat {163 illegal first argument to %s164} {165 1 "SELECT matchinfo(content) FROM t2 WHERE t2 MATCH 'history'" matchinfo166 2 "SELECT offsets(content) FROM t2 WHERE t2 MATCH 'history'" offsets167 3 "SELECT snippet(content) FROM t2 WHERE t2 MATCH 'history'" snippet168 4 "SELECT optimize(content) FROM t2 WHERE t2 MATCH 'history'" optimize169}170sqlite3_db_config db DEFENSIVE 0171do_execsql_test 5.4.0 { UPDATE t2_content SET c0content = X'1234' }172do_select_tests 5.4 -errorformat {173 illegal first argument to %s174} {175 1 "SELECT matchinfo(content) FROM t2 WHERE t2 MATCH 'history'" matchinfo176 2 "SELECT offsets(content) FROM t2 WHERE t2 MATCH 'history'" offsets177 3 "SELECT snippet(content) FROM t2 WHERE t2 MATCH 'history'" snippet178 4 "SELECT optimize(content) FROM t2 WHERE t2 MATCH 'history'" optimize179}180do_catchsql_test 5.5.1 {181 SELECT matchinfo(t2, 'abcd') FROM t2 WHERE t2 MATCH 'history'182} {1 {unrecognized matchinfo request: d}}183 184do_execsql_test 5.5 { DROP TABLE t2 }185 186 187# Test the snippet() function with 1 to 6 arguments.188# 189do_execsql_test 6.1 {190 CREATE VIRTUAL TABLE t3 USING FTS4(a, b);191 INSERT INTO t3 VALUES('no gestures', 'another intriguing discovery by observing the hand gestures (called beats) people make while speaking. Research has shown that such gestures do more than add visual emphasis to our words (many people gesture while they''re on the telephone, for example); it seems they actually help our brains find words');192}193do_select_tests 6.2 {194 1 "SELECT snippet(t3) FROM t3 WHERE t3 MATCH 'gestures'"195 {{<b>...</b>hand <b>gestures</b> (called beats) people make while speaking. Research has shown that such <b>gestures</b> do<b>...</b>}}196 197 2 "SELECT snippet(t3, 'XXX') FROM t3 WHERE t3 MATCH 'gestures'" 198 {{<b>...</b>hand XXXgestures</b> (called beats) people make while speaking. Research has shown that such XXXgestures</b> do<b>...</b>}}199 200 3 "SELECT snippet(t3, 'XXX', 'YYY') FROM t3 WHERE t3 MATCH 'gestures'" 201 {{<b>...</b>hand XXXgesturesYYY (called beats) people make while speaking. Research has shown that such XXXgesturesYYY do<b>...</b>}}202 203 4 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ') FROM t3 WHERE t3 MATCH 'gestures'" 204 {{ZZZhand XXXgesturesYYY (called beats) people make while speaking. Research has shown that such XXXgesturesYYY doZZZ}}205 206 5 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1) FROM t3 WHERE t3 MATCH 'gestures'" 207 {{ZZZhand XXXgesturesYYY (called beats) people make while speaking. Research has shown that such XXXgesturesYYY doZZZ}}208 209 6 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 0) FROM t3 WHERE t3 MATCH 'gestures'" 210 {{no XXXgesturesYYY}}211 212 7 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1, 5) FROM t3 WHERE t3 MATCH 'gestures'" 213 {{ZZZthe hand XXXgesturesYYY (called beatsZZZ}}214}215 216# Test some range queries on the rowid field.217# 218do_execsql_test 7.1 {219 CREATE VIRTUAL TABLE ft4 USING fts4(x);220 CREATE TABLE t4(x);221}222 223set SMALLINT -9223372036854775808224set LARGEINT 9223372036854775807225do_test 7.2 {226 db transaction {227 foreach {iFirst nEntry} [subst {228 0 100229 $SMALLINT 100230 [expr $LARGEINT - 99] 100231 }] {232 for {set i 0} {$i < $nEntry} {incr i} {233 set iRowid [expr $i + $iFirst]234 execsql {235 INSERT INTO ft4(rowid, x) VALUES($iRowid, 'x y z');236 INSERT INTO t4(rowid, x) VALUES($iRowid, 'x y z');237 }238 }239 }240 }241} {}242 243foreach {tn iFirst iLast} [subst {244 1 5 10245 2 $SMALLINT [expr $SMALLINT+5]246 3 $SMALLINT [expr $SMALLINT+50]247 4 [expr $LARGEINT-5] $LARGEINT248 5 $LARGEINT $LARGEINT249 6 $SMALLINT $LARGEINT250 7 $SMALLINT $SMALLINT251 8 $LARGEINT $SMALLINT252}] {253 set res [db eval { 254 SELECT rowid FROM t4 WHERE rowid BETWEEN $iFirst AND $iLast 255 } ]256 257 do_execsql_test 7.2.$tn.1.[llength $res] {258 SELECT rowid FROM ft4 WHERE rowid BETWEEN $iFirst AND $iLast259 } $res260 set res [db eval { 261 SELECT rowid FROM t4 WHERE rowid BETWEEN $iFirst AND $iLast 262 ORDER BY +rowid DESC263 } ]264 do_execsql_test 7.2.$tn.2.[llength $res] {265 SELECT rowid FROM ft4 WHERE rowid BETWEEN $iFirst AND $iLast266 ORDER BY rowid DESC267 } $res268}269 270foreach ii [db eval {SELECT rowid FROM t4}] {271 set res1 [db eval {SELECT rowid FROM t4 WHERE rowid > $ii}]272 set res2 [db eval {SELECT rowid FROM t4 WHERE rowid < $ii}]273 set res1s [db eval {SELECT rowid FROM t4 WHERE rowid > $ii ORDER BY +rowid DESC}]274 set res2s [db eval {SELECT rowid FROM t4 WHERE rowid < $ii ORDER BY +rowid DESC}]275 276 do_execsql_test 7.3.$ii.1 {277 SELECT rowid FROM ft4 WHERE rowid > $ii278 } $res1279 280 do_execsql_test 7.3.$ii.2 {281 SELECT rowid FROM ft4 WHERE rowid < $ii282 } $res2283 284 do_execsql_test 7.3.$ii.3 {285 SELECT rowid FROM ft4 WHERE rowid > $ii ORDER BY rowid DESC286 } $res1s287 288 do_execsql_test 7.3.$ii.4 {289 SELECT rowid FROM ft4 WHERE rowid < $ii ORDER BY rowid DESC290 } $res2s291}292 293finish_test294