| 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 |
|
| 13 |
set testdir [file dirname $argv0] |
| 14 |
source $testdir/tester.tcl |
| 15 |
source $testdir/fts3_common.tcl |
| 16 |
set ::testprefix fts4docid |
| 17 |
|
| 18 |
# If SQLITE_ENABLE_FTS3 is defined, omit this file. |
| 19 |
ifcapable !fts3 { |
| 20 |
finish_test |
| 21 |
return |
| 22 |
} |
| 23 |
|
| 24 |
# Initialize a table with pseudo-randomly generated data. |
| 25 |
# |
| 26 |
do_execsql_test 1.0 { CREATE VIRTUAL TABLE t1 USING fts4; } |
| 27 |
do_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. |
| 50 |
do_execsql_test 2.1.1 { SELECT docid FROM t1 WHERE docid = 5 } {5} |
| 51 |
do_execsql_test 2.1.2 { SELECT docid FROM t1 WHERE docid = '5' } {5} |
| 52 |
do_execsql_test 2.1.3 { SELECT docid FROM t1 WHERE docid = +5 } {5} |
| 53 |
do_execsql_test 2.1.4 { SELECT docid FROM t1 WHERE docid = +'5' } {5} |
| 54 |
do_execsql_test 2.1.5 { SELECT docid FROM t1 WHERE docid < 5 } {0 1 2 3 4} |
| 55 |
do_execsql_test 2.1.6 { SELECT docid FROM t1 WHERE docid < '5' } {0 1 2 3 4} |
| 56 |
|
| 57 |
do_execsql_test 2.2.1 { SELECT rowid FROM t1 WHERE rowid = 5 } {5} |
| 58 |
do_execsql_test 2.2.2 { SELECT rowid FROM t1 WHERE rowid = '5' } {5} |
| 59 |
do_execsql_test 2.2.3 { SELECT rowid FROM t1 WHERE rowid = +5 } {5} |
| 60 |
do_execsql_test 2.2.4 { SELECT rowid FROM t1 WHERE rowid = +'5' } {5} |
| 61 |
do_execsql_test 2.2.5 { SELECT rowid FROM t1 WHERE rowid < 5 } {0 1 2 3 4} |
| 62 |
do_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 |
# |
| 76 |
foreach {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 |
|
| 116 |
finish_test |