1# 2011 May 04 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# This file implements regression tests for SQLite library. The 12# focus of this script is testing the FTS3 module. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18# If SQLITE_ENABLE_FTS3 is defined, omit this file. 19ifcapable !fts3 { 20 finish_test 21 return 22} 23 24 25proc build_database {nRow param} { 26 db close 27 forcedelete test.db 28 sqlite3 db test.db 29 30 set vocab [list aa ab ac ba bb bc ca cb cc da] 31 expr srand(0) 32 33 execsql "CREATE VIRTUAL TABLE t1 USING fts4($param)" 34 for {set i 0} {$i < $nRow} {incr i} { 35 set v [expr int(rand()*1000000)] 36 set doc [list] 37 for {set div 1} {$div < 1000000} {set div [expr $div*10]} { 38 lappend doc [lindex $vocab [expr ($v/$div) % 10]] 39 } 40 execsql { INSERT INTO t1 VALUES($doc) } 41 } 42} 43 44set testprefix fts3sort 45 46unset -nocomplain CONTROL 47foreach {t param} { 48 1 "" 49 2 "order=asc" 50 3 "order=desc" 51} { 52 53 set testprefix fts3sort-1.$t 54 55 set nRow 1000 56 do_test 1.0 { 57 build_database $nRow $param 58 execsql { SELECT count(*) FROM t1 } 59 } $nRow 60 61 foreach {tn query} { 62 1 "SELECT docid, * FROM t1" 63 2 "SELECT docid, * FROM t1 WHERE t1 MATCH 'aa'" 64 3 "SELECT docid, * FROM t1 WHERE t1 MATCH 'a*'" 65 4 "SELECT docid, quote(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'a*'" 66 5 "SELECT docid, quote(matchinfo(t1,'pcnxals')) FROM t1 WHERE t1 MATCH 'b*'" 67 6 "SELECT docid, * FROM t1 WHERE t1 MATCH 'a* b* c*'" 68 7 "SELECT docid, * FROM t1 WHERE t1 MATCH 'aa OR da'" 69 8 "SELECT docid, * FROM t1 WHERE t1 MATCH 'nosuchtoken'" 70 9 "SELECT docid, snippet(t1) FROM t1 WHERE t1 MATCH 'aa OR da'" 71 10 "SELECT docid, snippet(t1) FROM t1 WHERE t1 MATCH 'aa OR nosuchtoken'" 72 11 "SELECT docid, snippet(t1) FROM t1 WHERE t1 MATCH 'aa NEAR bb'" 73 12 "SELECT docid, snippet(t1) FROM t1 WHERE t1 MATCH '\"aa bb\"'" 74 13 "SELECT docid, content FROM t1 WHERE t1 MATCH 'aa NEAR/2 bb NEAR/3 cc'" 75 14 "SELECT docid, content FROM t1 WHERE t1 MATCH '\"aa bb cc\"'" 76 } { 77 78 unset -nocomplain A B C D 79 set A_list [list] 80 set B_list [list] 81 set C_list [list] 82 set D_list [list] 83 84 unset -nocomplain X 85 db eval "$query ORDER BY rowid ASC" X { 86 set A($X(docid)) [array get X] 87 lappend A_list $X(docid) 88 } 89 unset -nocomplain X 90 db eval "$query ORDER BY rowid DESC" X { 91 set B($X(docid)) [array get X] 92 lappend B_list $X(docid) 93 } 94 unset -nocomplain X 95 db eval "$query ORDER BY docid ASC" X { 96 set C($X(docid)) [array get X] 97 lappend C_list $X(docid) 98 } 99 unset -nocomplain X 100 db eval "$query ORDER BY docid DESC" X { 101 set D($X(docid)) [array get X] 102 lappend D_list $X(docid) 103 } 104 105 do_test $tn.1 { set A_list } [lsort -integer -increasing $A_list] 106 do_test $tn.2 { set B_list } [lsort -integer -decreasing $B_list] 107 do_test $tn.3 { set C_list } [lsort -integer -increasing $C_list] 108 do_test $tn.4 { set D_list } [lsort -integer -decreasing $D_list] 109 110 unset -nocomplain DATA 111 unset -nocomplain X 112 db eval "$query" X { 113 set DATA($X(docid)) [array get X] 114 } 115 116 do_test $tn.5 { lsort [array get A] } [lsort [array get DATA]] 117 do_test $tn.6 { lsort [array get B] } [lsort [array get DATA]] 118 do_test $tn.7 { lsort [array get C] } [lsort [array get DATA]] 119 do_test $tn.8 { lsort [array get D] } [lsort [array get DATA]] 120 121 if {[info exists CONTROL($tn)]} { 122 do_test $tn.9 { set CONTROL($tn) } [lsort [array get DATA]] 123 } else { 124 set CONTROL($tn) [lsort [array get DATA]] 125 } 126 } 127} 128unset -nocomplain CONTROL 129 130set testprefix fts3sort 131 132#------------------------------------------------------------------------- 133# Tests for parsing the "order=asc" and "order=desc" directives. 134# 135foreach {tn param res} { 136 1 "order=asc" {0 {}} 137 2 "order=desc" {0 {}} 138 3 "order=dec" {1 {unrecognized order: dec}} 139 4 "order=xxx, order=asc" {1 {unrecognized order: xxx}} 140 5 "order=desc, order=asc" {0 {}} 141 6 "order=xxxx, order=asc" {1 {unrecognized order: xxxx}} 142 7 "order=desk" {1 {unrecognized order: desk}} 143} { 144 execsql { DROP TABLE IF EXISTS t1 } 145 do_catchsql_test 2.1.$tn " 146 CREATE VIRTUAL TABLE t1 USING fts4(a, b, $param) 147 " $res 148} 149 150do_execsql_test 2.2 { 151 BEGIN; 152 CREATE VIRTUAL TABLE t2 USING fts4(order=desc); 153 INSERT INTO t2 VALUES('aa bb'); 154 INSERT INTO t2 VALUES('bb cc'); 155 INSERT INTO t2 VALUES('cc aa'); 156 SELECT docid FROM t2 WHERE t2 MATCH 'aa'; 157 END; 158} {3 1} 159do_execsql_test 2.3 { 160 SELECT docid FROM t2 WHERE t2 MATCH 'aa'; 161} {3 1} 162do_execsql_test 2.4 { 163 SELECT docid FROM t2 WHERE t2 MATCH 'aa' ORDER BY content; 164} {1 3} 165 166#------------------------------------------------------------------------- 167# Test that ticket [56be976859] has been fixed. 168# 169do_execsql_test 3.1 { 170 CREATE VIRTUAL TABLE t3 USING fts4(x, order=DESC); 171 INSERT INTO t3(docid, x) VALUES(113382409004785664, 'aa'); 172 INSERT INTO t3(docid, x) VALUES(1, 'ab'); 173 SELECT rowid FROM t3 WHERE x MATCH 'a*' ORDER BY docid DESC; 174} {113382409004785664 1} 175do_execsql_test 3.2 { 176 CREATE VIRTUAL TABLE t4 USING fts4(x); 177 INSERT INTO t4(docid, x) VALUES(-113382409004785664, 'aa'); 178 INSERT INTO t4(docid, x) VALUES(1, 'ab'); 179 SELECT rowid FROM t4 WHERE x MATCH 'a*'; 180} {-113382409004785664 1} 181 182 183 184finish_test 185