1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/fts2q.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,346 @@
1.4 +# 2008 June 26
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#*************************************************************************
1.14 +# This file implements regression tests for SQLite library. The focus
1.15 +# of this script is testing the FTS2 module's optimize() function.
1.16 +#
1.17 +# $Id: fts2q.test,v 1.2 2008/07/22 23:49:44 shess Exp $
1.18 +#
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# If SQLITE_ENABLE_FTS2 is not defined, omit this file.
1.24 +ifcapable !fts2 {
1.25 + finish_test
1.26 + return
1.27 +}
1.28 +
1.29 +#*************************************************************************
1.30 +# Probe to see if support for the FTS2 dump_* functions is compiled in.
1.31 +# TODO(shess): Change main.mk to do the right thing and remove this test.
1.32 +db eval {
1.33 + DROP TABLE IF EXISTS t1;
1.34 + CREATE VIRTUAL TABLE t1 USING fts2(c);
1.35 + INSERT INTO t1 (rowid, c) VALUES (1, 'x');
1.36 +}
1.37 +
1.38 +set s {SELECT dump_terms(t1, 1) FROM t1 LIMIT 1}
1.39 +set r {1 {unable to use function dump_terms in the requested context}}
1.40 +if {[catchsql $s]==$r} {
1.41 + finish_test
1.42 + return
1.43 +}
1.44 +
1.45 +#*************************************************************************
1.46 +# Utility function to check for the expected terms in the segment
1.47 +# level/index. _all version does same but for entire index.
1.48 +proc check_terms {test level index terms} {
1.49 + # TODO(shess): Figure out why uplevel in do_test can't catch
1.50 + # $level and $index directly.
1.51 + set ::level $level
1.52 + set ::index $index
1.53 + do_test $test.terms {
1.54 + execsql {
1.55 + SELECT dump_terms(t1, $::level, $::index) FROM t1 LIMIT 1;
1.56 + }
1.57 + } [list $terms]
1.58 +}
1.59 +proc check_terms_all {test terms} {
1.60 + do_test $test.terms {
1.61 + execsql {
1.62 + SELECT dump_terms(t1) FROM t1 LIMIT 1;
1.63 + }
1.64 + } [list $terms]
1.65 +}
1.66 +
1.67 +# Utility function to check for the expected doclist for the term in
1.68 +# segment level/index. _all version does same for entire index.
1.69 +proc check_doclist {test level index term doclist} {
1.70 + # TODO(shess): Again, why can't the non-:: versions work?
1.71 + set ::term $term
1.72 + set ::level $level
1.73 + set ::index $index
1.74 + do_test $test {
1.75 + execsql {
1.76 + SELECT dump_doclist(t1, $::term, $::level, $::index) FROM t1 LIMIT 1;
1.77 + }
1.78 + } [list $doclist]
1.79 +}
1.80 +proc check_doclist_all {test term doclist} {
1.81 + set ::term $term
1.82 + do_test $test {
1.83 + execsql {
1.84 + SELECT dump_doclist(t1, $::term) FROM t1 LIMIT 1;
1.85 + }
1.86 + } [list $doclist]
1.87 +}
1.88 +
1.89 +#*************************************************************************
1.90 +# Test results when all rows are deleted and one is added back.
1.91 +# Previously older segments would continue to exist, but now the index
1.92 +# should be dropped when the table is empty. The results should look
1.93 +# exactly like we never added the earlier rows in the first place.
1.94 +db eval {
1.95 + DROP TABLE IF EXISTS t1;
1.96 + CREATE VIRTUAL TABLE t1 USING fts2(c);
1.97 + INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');
1.98 + INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');
1.99 + INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');
1.100 + DELETE FROM t1 WHERE 1=1; -- Delete each row rather than dropping table.
1.101 + INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');
1.102 +}
1.103 +
1.104 +# Should be a single initial segment.
1.105 +do_test fts2q-1.segments {
1.106 + execsql {
1.107 + SELECT level, idx FROM t1_segdir ORDER BY level, idx;
1.108 + }
1.109 +} {0 0}
1.110 +do_test fts2q-1.matches {
1.111 + execsql {
1.112 + SELECT OFFSETS(t1) FROM t1
1.113 + WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;
1.114 + }
1.115 +} {{0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}}
1.116 +
1.117 +check_terms_all fts2q-1.1 {a is test this}
1.118 +check_doclist_all fts2q-1.1.1 a {[1 0[2]]}
1.119 +check_doclist_all fts2q-1.1.2 is {[1 0[1]]}
1.120 +check_doclist_all fts2q-1.1.3 test {[1 0[3]]}
1.121 +check_doclist_all fts2q-1.1.4 this {[1 0[0]]}
1.122 +
1.123 +check_terms fts2q-1.2 0 0 {a is test this}
1.124 +check_doclist fts2q-1.2.1 0 0 a {[1 0[2]]}
1.125 +check_doclist fts2q-1.2.2 0 0 is {[1 0[1]]}
1.126 +check_doclist fts2q-1.2.3 0 0 test {[1 0[3]]}
1.127 +check_doclist fts2q-1.2.4 0 0 this {[1 0[0]]}
1.128 +
1.129 +#*************************************************************************
1.130 +# Test results when everything is optimized manually.
1.131 +# NOTE(shess): This is a copy of fts2c-1.3. I've pulled a copy here
1.132 +# because fts2q-2 and fts2q-3 should have identical results.
1.133 +db eval {
1.134 + DROP TABLE IF EXISTS t1;
1.135 + CREATE VIRTUAL TABLE t1 USING fts2(c);
1.136 + INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');
1.137 + INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');
1.138 + INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');
1.139 + DELETE FROM t1 WHERE rowid IN (1,3);
1.140 + DROP TABLE IF EXISTS t1old;
1.141 + ALTER TABLE t1 RENAME TO t1old;
1.142 + CREATE VIRTUAL TABLE t1 USING fts2(c);
1.143 + INSERT INTO t1 (rowid, c) SELECT rowid, c FROM t1old;
1.144 + DROP TABLE t1old;
1.145 +}
1.146 +
1.147 +# Should be a single optimal segment with the same logical results.
1.148 +do_test fts2q-2.segments {
1.149 + execsql {
1.150 + SELECT level, idx FROM t1_segdir ORDER BY level, idx;
1.151 + }
1.152 +} {0 0}
1.153 +do_test fts2q-2.matches {
1.154 + execsql {
1.155 + SELECT OFFSETS(t1) FROM t1
1.156 + WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;
1.157 + }
1.158 +} {{0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4}}
1.159 +
1.160 +check_terms_all fts2q-2.1 {a test that was}
1.161 +check_doclist_all fts2q-2.1.1 a {[2 0[2]]}
1.162 +check_doclist_all fts2q-2.1.2 test {[2 0[3]]}
1.163 +check_doclist_all fts2q-2.1.3 that {[2 0[0]]}
1.164 +check_doclist_all fts2q-2.1.4 was {[2 0[1]]}
1.165 +
1.166 +check_terms fts2q-2.2 0 0 {a test that was}
1.167 +check_doclist fts2q-2.2.1 0 0 a {[2 0[2]]}
1.168 +check_doclist fts2q-2.2.2 0 0 test {[2 0[3]]}
1.169 +check_doclist fts2q-2.2.3 0 0 that {[2 0[0]]}
1.170 +check_doclist fts2q-2.2.4 0 0 was {[2 0[1]]}
1.171 +
1.172 +#*************************************************************************
1.173 +# Test results when everything is optimized via optimize().
1.174 +db eval {
1.175 + DROP TABLE IF EXISTS t1;
1.176 + CREATE VIRTUAL TABLE t1 USING fts2(c);
1.177 + INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');
1.178 + INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');
1.179 + INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');
1.180 + DELETE FROM t1 WHERE rowid IN (1,3);
1.181 + SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;
1.182 +}
1.183 +
1.184 +# Should be a single optimal segment with the same logical results.
1.185 +do_test fts2q-3.segments {
1.186 + execsql {
1.187 + SELECT level, idx FROM t1_segdir ORDER BY level, idx;
1.188 + }
1.189 +} {0 0}
1.190 +do_test fts2q-3.matches {
1.191 + execsql {
1.192 + SELECT OFFSETS(t1) FROM t1
1.193 + WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;
1.194 + }
1.195 +} {{0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4}}
1.196 +
1.197 +check_terms_all fts2q-3.1 {a test that was}
1.198 +check_doclist_all fts2q-3.1.1 a {[2 0[2]]}
1.199 +check_doclist_all fts2q-3.1.2 test {[2 0[3]]}
1.200 +check_doclist_all fts2q-3.1.3 that {[2 0[0]]}
1.201 +check_doclist_all fts2q-3.1.4 was {[2 0[1]]}
1.202 +
1.203 +check_terms fts2q-3.2 0 0 {a test that was}
1.204 +check_doclist fts2q-3.2.1 0 0 a {[2 0[2]]}
1.205 +check_doclist fts2q-3.2.2 0 0 test {[2 0[3]]}
1.206 +check_doclist fts2q-3.2.3 0 0 that {[2 0[0]]}
1.207 +check_doclist fts2q-3.2.4 0 0 was {[2 0[1]]}
1.208 +
1.209 +#*************************************************************************
1.210 +# Test optimize() against a table involving segment merges.
1.211 +# NOTE(shess): Since there's no transaction, each of the INSERT/UPDATE
1.212 +# statements generates a segment.
1.213 +db eval {
1.214 + DROP TABLE IF EXISTS t1;
1.215 + CREATE VIRTUAL TABLE t1 USING fts2(c);
1.216 +
1.217 + INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');
1.218 + INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');
1.219 + INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');
1.220 +
1.221 + UPDATE t1 SET c = 'This is a test one' WHERE rowid = 1;
1.222 + UPDATE t1 SET c = 'That was a test one' WHERE rowid = 2;
1.223 + UPDATE t1 SET c = 'This is a test one' WHERE rowid = 3;
1.224 +
1.225 + UPDATE t1 SET c = 'This is a test two' WHERE rowid = 1;
1.226 + UPDATE t1 SET c = 'That was a test two' WHERE rowid = 2;
1.227 + UPDATE t1 SET c = 'This is a test two' WHERE rowid = 3;
1.228 +
1.229 + UPDATE t1 SET c = 'This is a test three' WHERE rowid = 1;
1.230 + UPDATE t1 SET c = 'That was a test three' WHERE rowid = 2;
1.231 + UPDATE t1 SET c = 'This is a test three' WHERE rowid = 3;
1.232 +
1.233 + UPDATE t1 SET c = 'This is a test four' WHERE rowid = 1;
1.234 + UPDATE t1 SET c = 'That was a test four' WHERE rowid = 2;
1.235 + UPDATE t1 SET c = 'This is a test four' WHERE rowid = 3;
1.236 +
1.237 + UPDATE t1 SET c = 'This is a test' WHERE rowid = 1;
1.238 + UPDATE t1 SET c = 'That was a test' WHERE rowid = 2;
1.239 + UPDATE t1 SET c = 'This is a test' WHERE rowid = 3;
1.240 +}
1.241 +
1.242 +# 2 segments in level 0, 1 in level 1 (18 segments created, 16
1.243 +# merged).
1.244 +do_test fts2q-4.segments {
1.245 + execsql {
1.246 + SELECT level, idx FROM t1_segdir ORDER BY level, idx;
1.247 + }
1.248 +} {0 0 0 1 1 0}
1.249 +
1.250 +do_test fts2q-4.matches {
1.251 + execsql {
1.252 + SELECT OFFSETS(t1) FROM t1
1.253 + WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;
1.254 + }
1.255 +} [list {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4} \
1.256 + {0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4} \
1.257 + {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}]
1.258 +
1.259 +check_terms_all fts2q-4.1 {a four is one test that this three two was}
1.260 +check_doclist_all fts2q-4.1.1 a {[1 0[2]] [2 0[2]] [3 0[2]]}
1.261 +check_doclist_all fts2q-4.1.2 four {}
1.262 +check_doclist_all fts2q-4.1.3 is {[1 0[1]] [3 0[1]]}
1.263 +check_doclist_all fts2q-4.1.4 one {}
1.264 +check_doclist_all fts2q-4.1.5 test {[1 0[3]] [2 0[3]] [3 0[3]]}
1.265 +check_doclist_all fts2q-4.1.6 that {[2 0[0]]}
1.266 +check_doclist_all fts2q-4.1.7 this {[1 0[0]] [3 0[0]]}
1.267 +check_doclist_all fts2q-4.1.8 three {}
1.268 +check_doclist_all fts2q-4.1.9 two {}
1.269 +check_doclist_all fts2q-4.1.10 was {[2 0[1]]}
1.270 +
1.271 +check_terms fts2q-4.2 0 0 {a four test that was}
1.272 +check_doclist fts2q-4.2.1 0 0 a {[2 0[2]]}
1.273 +check_doclist fts2q-4.2.2 0 0 four {[2]}
1.274 +check_doclist fts2q-4.2.3 0 0 test {[2 0[3]]}
1.275 +check_doclist fts2q-4.2.4 0 0 that {[2 0[0]]}
1.276 +check_doclist fts2q-4.2.5 0 0 was {[2 0[1]]}
1.277 +
1.278 +check_terms fts2q-4.3 0 1 {a four is test this}
1.279 +check_doclist fts2q-4.3.1 0 1 a {[3 0[2]]}
1.280 +check_doclist fts2q-4.3.2 0 1 four {[3]}
1.281 +check_doclist fts2q-4.3.3 0 1 is {[3 0[1]]}
1.282 +check_doclist fts2q-4.3.4 0 1 test {[3 0[3]]}
1.283 +check_doclist fts2q-4.3.5 0 1 this {[3 0[0]]}
1.284 +
1.285 +check_terms fts2q-4.4 1 0 {a four is one test that this three two was}
1.286 +check_doclist fts2q-4.4.1 1 0 a {[1 0[2]] [2 0[2]] [3 0[2]]}
1.287 +check_doclist fts2q-4.4.2 1 0 four {[1] [2 0[4]] [3 0[4]]}
1.288 +check_doclist fts2q-4.4.3 1 0 is {[1 0[1]] [3 0[1]]}
1.289 +check_doclist fts2q-4.4.4 1 0 one {[1] [2] [3]}
1.290 +check_doclist fts2q-4.4.5 1 0 test {[1 0[3]] [2 0[3]] [3 0[3]]}
1.291 +check_doclist fts2q-4.4.6 1 0 that {[2 0[0]]}
1.292 +check_doclist fts2q-4.4.7 1 0 this {[1 0[0]] [3 0[0]]}
1.293 +check_doclist fts2q-4.4.8 1 0 three {[1] [2] [3]}
1.294 +check_doclist fts2q-4.4.9 1 0 two {[1] [2] [3]}
1.295 +check_doclist fts2q-4.4.10 1 0 was {[2 0[1]]}
1.296 +
1.297 +# Optimize should leave the result in the level of the highest-level
1.298 +# prior segment.
1.299 +do_test fts2q-4.5 {
1.300 + execsql {
1.301 + SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;
1.302 + SELECT level, idx FROM t1_segdir ORDER BY level, idx;
1.303 + }
1.304 +} {{Index optimized} 1 0}
1.305 +
1.306 +# Identical to fts2q-4.matches.
1.307 +do_test fts2q-4.5.matches {
1.308 + execsql {
1.309 + SELECT OFFSETS(t1) FROM t1
1.310 + WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;
1.311 + }
1.312 +} [list {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4} \
1.313 + {0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4} \
1.314 + {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}]
1.315 +
1.316 +check_terms_all fts2q-4.5.1 {a is test that this was}
1.317 +check_doclist_all fts2q-4.5.1.1 a {[1 0[2]] [2 0[2]] [3 0[2]]}
1.318 +check_doclist_all fts2q-4.5.1.2 is {[1 0[1]] [3 0[1]]}
1.319 +check_doclist_all fts2q-4.5.1.3 test {[1 0[3]] [2 0[3]] [3 0[3]]}
1.320 +check_doclist_all fts2q-4.5.1.4 that {[2 0[0]]}
1.321 +check_doclist_all fts2q-4.5.1.5 this {[1 0[0]] [3 0[0]]}
1.322 +check_doclist_all fts2q-4.5.1.6 was {[2 0[1]]}
1.323 +
1.324 +check_terms fts2q-4.5.2 1 0 {a is test that this was}
1.325 +check_doclist fts2q-4.5.2.1 1 0 a {[1 0[2]] [2 0[2]] [3 0[2]]}
1.326 +check_doclist fts2q-4.5.2.2 1 0 is {[1 0[1]] [3 0[1]]}
1.327 +check_doclist fts2q-4.5.2.3 1 0 test {[1 0[3]] [2 0[3]] [3 0[3]]}
1.328 +check_doclist fts2q-4.5.2.4 1 0 that {[2 0[0]]}
1.329 +check_doclist fts2q-4.5.2.5 1 0 this {[1 0[0]] [3 0[0]]}
1.330 +check_doclist fts2q-4.5.2.6 1 0 was {[2 0[1]]}
1.331 +
1.332 +# Re-optimizing does nothing.
1.333 +do_test fts2q-5.0 {
1.334 + execsql {
1.335 + SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;
1.336 + SELECT level, idx FROM t1_segdir ORDER BY level, idx;
1.337 + }
1.338 +} {{Index already optimal} 1 0}
1.339 +
1.340 +# Even if we move things around, still does nothing.
1.341 +do_test fts2q-5.1 {
1.342 + execsql {
1.343 + UPDATE t1_segdir SET level = 2 WHERE level = 1 AND idx = 0;
1.344 + SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;
1.345 + SELECT level, idx FROM t1_segdir ORDER BY level, idx;
1.346 + }
1.347 +} {{Index already optimal} 2 0}
1.348 +
1.349 +finish_test