1# 2012 March 26 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# 12 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15source $testdir/fts3_common.tcl 16set ::testprefix fts4docid 17 18# If SQLITE_ENABLE_FTS3 is defined, omit this file. 19ifcapable !fts3 { 20 finish_test 21 return 22} 23 24# Initialize a table with pseudo-randomly generated data. 25# 26do_execsql_test 1.0 { CREATE VIRTUAL TABLE t1 USING fts4; } 27do_test 1.1 { 28 foreach {docid content} { 29 0 {F N K B T I K V B A} 1 {D M J E S P H E L O} 30 2 {W U T Q T Q T L H G} 3 {D W H M B R S Z B K} 31 4 {F Q I N P Q J L Z D} 5 {J O Q E Y A O E L B} 32 6 {O V R A C R K C Y H} 7 {Z J H T Q Q O R A G} 33 8 {L K J W G D Y W B M} 9 {K E Y I A Q R Q T S} 34 10 {N P H Y Z M R T I C} 11 {E X H O I S E S Z F} 35 12 {B Y Q T J X C L L J} 13 {Q D C U U A Q E Z U} 36 14 {S I T C J R X S J M} 15 {M X M K E X L H Q Y} 37 16 {O W E I C H U Y S Y} 17 {P V V E M T H C C S} 38 18 {L Y A M I E N M X O} 19 {S Y R U L S Q Y F P} 39 20 {U J S T T J J S V X} 21 {T E I W P O V A A P} 40 22 {W D K H D H F G O J} 23 {T X Y P G M J U I L} 41 24 {F V X E B C N B K W} 25 {E B A Y N N T Z I C} 42 26 {G E E B C P U D H G} 27 {J D J K N S B Q T M} 43 28 {Q T G M D O D Y V G} 29 {P X W I W V P W Z G} 44 } { 45 execsql { INSERT INTO t1(docid, content) VALUES($docid, $content) } 46 } 47} {} 48 49# Quick test regarding affinites and the docid/rowid column. 50do_execsql_test 2.1.1 { SELECT docid FROM t1 WHERE docid = 5 } {5} 51do_execsql_test 2.1.2 { SELECT docid FROM t1 WHERE docid = '5' } {5} 52do_execsql_test 2.1.3 { SELECT docid FROM t1 WHERE docid = +5 } {5} 53do_execsql_test 2.1.4 { SELECT docid FROM t1 WHERE docid = +'5' } {5} 54do_execsql_test 2.1.5 { SELECT docid FROM t1 WHERE docid < 5 } {0 1 2 3 4} 55do_execsql_test 2.1.6 { SELECT docid FROM t1 WHERE docid < '5' } {0 1 2 3 4} 56 57do_execsql_test 2.2.1 { SELECT rowid FROM t1 WHERE rowid = 5 } {5} 58do_execsql_test 2.2.2 { SELECT rowid FROM t1 WHERE rowid = '5' } {5} 59do_execsql_test 2.2.3 { SELECT rowid FROM t1 WHERE rowid = +5 } {5} 60do_execsql_test 2.2.4 { SELECT rowid FROM t1 WHERE rowid = +'5' } {5} 61do_execsql_test 2.2.5 { SELECT rowid FROM t1 WHERE rowid < 5 } {0 1 2 3 4} 62do_execsql_test 2.2.6 { SELECT rowid FROM t1 WHERE rowid < '5' } {0 1 2 3 4} 63 64#------------------------------------------------------------------------- 65# Now test a bunch of full-text queries featuring range constraints on 66# the docid field. Each query is run so that the range constraint: 67# 68# * is on the docid field, 69# * is on the docid field with a unary +, 70# * is on the rowid field, 71# * is on the rowid field with a unary +. 72# 73# Queries are run with both "ORDER BY docid DESC" and "ORDER BY docid ASC" 74# clauses. 75# 76foreach {tn where result} { 77 1 {WHERE t1 MATCH 'O' AND xxx < 17} {1 5 6 7 11 16} 78 2 {WHERE t1 MATCH 'O' AND xxx < 4123456789123456} {1 5 6 7 11 16 18 21 22 28} 79 3 {WHERE t1 MATCH 'O' AND xxx < 1} {} 80 4 {WHERE t1 MATCH 'O' AND xxx < -4123456789123456} {} 81 82 5 {WHERE t1 MATCH 'O' AND xxx > 17} {18 21 22 28} 83 6 {WHERE t1 MATCH 'O' AND xxx > 4123456789123456} {} 84 7 {WHERE t1 MATCH 'O' AND xxx > 1} {5 6 7 11 16 18 21 22 28} 85 8 {WHERE t1 MATCH 'O' AND xxx > -4123456789123456} {1 5 6 7 11 16 18 21 22 28} 86 87 9 {WHERE t1 MATCH '"Q T"' AND xxx < 27} {2 9 12} 88 10 {WHERE t1 MATCH '"Q T"' AND xxx <= 27} {2 9 12 27} 89 11 {WHERE t1 MATCH '"Q T"' AND xxx > 27} {28} 90 12 {WHERE t1 MATCH '"Q T"' AND xxx >= 27} {27 28} 91} { 92 foreach {tn2 ref order} { 93 1 docid "ORDER BY docid ASC" 94 2 +docid "ORDER BY docid ASC" 95 3 rowid "ORDER BY docid ASC" 96 4 +rowid "ORDER BY docid ASC" 97 98 5 docid "ORDER BY docid DESC" 99 6 +docid "ORDER BY docid DESC" 100 7 rowid "ORDER BY docid DESC" 101 8 +rowid "ORDER BY docid DESC" 102 } { 103 set w [string map "xxx $ref" $where] 104 set q "SELECT docid FROM t1 $w $order" 105 106 if {$tn2<5} { 107 set r [lsort -integer -increasing $result] 108 } else { 109 set r [lsort -integer -decreasing $result] 110 } 111 112 do_execsql_test 3.$tn.$tn2 $q $r 113 } 114} 115 116finish_test 117