1# 2011 January 27 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 17ifcapable !fts3 { finish_test ; return } 18set ::testprefix fts3aux2 19 20do_execsql_test 1.1 { 21 CREATE VIRTUAL TABLE t1 USING fts4(a, b, languageid=l); 22 INSERT INTO t1(a, b, l) VALUES 23 ('zero zero', 'zero zero', 0), 24 ('one two', 'three four', 1), 25 ('five six', 'seven eight', 2) 26 ; 27 CREATE VIRTUAL TABLE terms USING fts4aux(t1); 28} {} 29 30do_execsql_test 1.2.1 { 31 SELECT term, documents, occurrences, languageid FROM terms WHERE col = '*'; 32} {zero 1 4 0} 33 34do_execsql_test 1.2.2 { 35 SELECT * FROM terms; 36} {zero * 1 4 zero 0 1 2 zero 1 1 2} 37 38do_execsql_test 1.2.3 { 39 SELECT * FROM terms WHERE languageid=''; 40} {} 41 42do_execsql_test 1.2.4 { 43 SELECT * FROM terms WHERE languageid=-1; 44} {} 45 46do_execsql_test 1.2.5 { 47 SELECT * FROM terms WHERE languageid=9223372036854775807; 48} {} 49 50do_execsql_test 1.2.6 { 51 SELECT * FROM terms WHERE languageid=-9223372036854775808; 52} {} 53 54do_execsql_test 1.2.7 { 55 SELECT * FROM terms WHERE languageid=NULL; 56} {} 57 58do_execsql_test 1.3.1 { 59 SELECT term, documents, occurrences, languageid 60 FROM terms WHERE col = '*' AND languageid=1; 61} { 62 four 1 1 1 one 1 1 1 three 1 1 1 two 1 1 1 63} 64 65do_execsql_test 1.3.2 { 66 SELECT term, col, documents, occurrences, languageid 67 FROM terms WHERE languageid=1; 68} { 69 four * 1 1 1 four 1 1 1 1 70 one * 1 1 1 one 0 1 1 1 71 three * 1 1 1 three 1 1 1 1 72 two * 1 1 1 two 0 1 1 1 73} 74 75do_execsql_test 1.3.3 { 76 SELECT term, col, documents, occurrences, languageid 77 FROM terms WHERE languageid=1 AND term='zero' 78} { 79} 80 81do_execsql_test 1.3.4 { 82 SELECT term, col, documents, occurrences, languageid 83 FROM terms WHERE languageid='1' AND term='two' 84} { 85 two * 1 1 1 two 0 1 1 1 86} 87 88do_execsql_test 1.3.5 { 89 SELECT term, col, documents, occurrences, languageid 90 FROM terms WHERE languageid='+1' AND term>'four' 91} { 92 one * 1 1 1 one 0 1 1 1 93 three * 1 1 1 three 1 1 1 1 94 two * 1 1 1 two 0 1 1 1 95} 96 97do_execsql_test 1.4.1 { 98 SELECT term, documents, occurrences, languageid 99 FROM terms WHERE col = '*' AND languageid=2; 100} { 101 eight 1 1 2 five 1 1 2 seven 1 1 2 six 1 1 2 102} 103 104do_execsql_test 1.4.2 { 105 SELECT term, col, documents, occurrences, languageid 106 FROM terms WHERE languageid=2; 107} { 108 eight * 1 1 2 eight 1 1 1 2 109 five * 1 1 2 five 0 1 1 2 110 seven * 1 1 2 seven 1 1 1 2 111 six * 1 1 2 six 0 1 1 2 112} 113 114do_execsql_test 1.4.3 { 115 SELECT term, col, documents, occurrences, languageid 116 FROM terms WHERE languageid=2 AND term='five'; 117} { 118 five * 1 1 2 five 0 1 1 2 119} 120 121do_execsql_test 1.4.4 { 122 SELECT term, col, documents, occurrences, languageid 123 FROM terms WHERE term='five' AND languageid=2 124} { 125 five * 1 1 2 five 0 1 1 2 126} 127 128do_execsql_test 1.4.5 { 129 SELECT term, col, documents, occurrences, languageid 130 FROM terms WHERE term>='seven' AND languageid=2 131} { 132 seven * 1 1 2 seven 1 1 1 2 133 six * 1 1 2 six 0 1 1 2 134} 135 136do_execsql_test 1.4.6 { 137 SELECT term, col, documents, occurrences, languageid 138 FROM terms WHERE term>='e' AND term<'seven' AND languageid=2 139} { 140 eight * 1 1 2 eight 1 1 1 2 141 five * 1 1 2 five 0 1 1 2 142} 143 144#------------------------------------------------------------------------- 145do_execsql_test 2.0 { 146 CREATE VIRTUAL TABLE ft USING fts3(); 147 INSERT INTO ft VALUES('a_234567890123456789'); 148 INSERT INTO ft VALUES('b_234567890123456789'); 149 INSERT INTO ft VALUES('c_234567890123456789'); 150 CREATE VIRTUAL TABLE t2 USING fts4aux(ft); 151} 152 153do_execsql_test 2.1 { 154 SELECT term FROM t2 WHERE term=X'625f323334353637383930313233343536373839'; 155} 156 157do_execsql_test 2.2 { 158 SELECT term FROM t2 WHERE term<X'625f003334353637383930313233343536373839'; 159} { 160 234567890123456789 234567890123456789 a a b b 161} 162 163do_execsql_test 2.3 { 164 SELECT term FROM t2 WHERE term=X'625f003334353637383930313233343536373839'; 165} 166 167 168finish_test 169