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