1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/fts2r.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,121 @@
1.4 +# 2008 July 29
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 +# These tests exercise the various types of fts2 cursors.
1.15 +#
1.16 +# $Id: fts2r.test,v 1.1 2008/07/29 20:38:18 shess Exp $
1.17 +#
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# If SQLITE_ENABLE_FTS2 is not defined, omit this file.
1.23 +ifcapable !fts2 {
1.24 + finish_test
1.25 + return
1.26 +}
1.27 +
1.28 +#*************************************************************************
1.29 +# Test table scan (QUERY_GENERIC). This kind of query happens for
1.30 +# queries with no WHERE clause, or for WHERE clauses which cannot be
1.31 +# satisfied by an index.
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, 'This is a test');
1.36 + INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');
1.37 + INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');
1.38 +}
1.39 +
1.40 +do_test fts2e-1.1 {
1.41 + execsql {
1.42 + SELECT rowid FROM t1 ORDER BY rowid;
1.43 + }
1.44 +} {1 2 3}
1.45 +
1.46 +do_test fts2e-1.2 {
1.47 + execsql {
1.48 + SELECT rowid FROM t1 WHERE c LIKE '%test' ORDER BY rowid;
1.49 + }
1.50 +} {1 2 3}
1.51 +
1.52 +do_test fts2e-1.3 {
1.53 + execsql {
1.54 + SELECT rowid FROM t1 WHERE c LIKE 'That%' ORDER BY rowid;
1.55 + }
1.56 +} {2}
1.57 +
1.58 +#*************************************************************************
1.59 +# Test lookup by rowid (QUERY_ROWID). This kind of query happens for
1.60 +# queries which select by the rowid implicit index.
1.61 +db eval {
1.62 + DROP TABLE IF EXISTS t1;
1.63 + DROP TABLE IF EXISTS t2;
1.64 + CREATE VIRTUAL TABLE t1 USING fts2(c);
1.65 + CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
1.66 + INSERT INTO t2 VALUES (null, 10);
1.67 + INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
1.68 + INSERT INTO t2 VALUES (null, 5);
1.69 + INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'That was a test');
1.70 + INSERT INTO t2 VALUES (null, 20);
1.71 + INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
1.72 +}
1.73 +
1.74 +# TODO(shess): This actually is doing QUERY_GENERIC? I'd have
1.75 +# expected QUERY_ROWID in this case, as for a very large table the
1.76 +# full scan is less efficient.
1.77 +do_test fts2e-2.1 {
1.78 + execsql {
1.79 + SELECT rowid FROM t1 WHERE rowid in (1, 2, 10);
1.80 + }
1.81 +} {1 2}
1.82 +
1.83 +do_test fts2e-2.2 {
1.84 + execsql {
1.85 + SELECT t1.rowid, weight FROM t1, t2 WHERE t2.id = t1.rowid ORDER BY weight;
1.86 + }
1.87 +} {2 5 1 10 3 20}
1.88 +
1.89 +do_test fts2e-2.3 {
1.90 + execsql {
1.91 + SELECT t1.rowid, weight FROM t1, t2
1.92 + WHERE t2.weight>5 AND t2.id = t1.rowid ORDER BY weight;
1.93 + }
1.94 +} {1 10 3 20}
1.95 +
1.96 +#*************************************************************************
1.97 +# Test lookup by MATCH (QUERY_FULLTEXT). This is the fulltext index.
1.98 +db eval {
1.99 + DROP TABLE IF EXISTS t1;
1.100 + DROP TABLE IF EXISTS t2;
1.101 + CREATE VIRTUAL TABLE t1 USING fts2(c);
1.102 + CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
1.103 + INSERT INTO t2 VALUES (null, 10);
1.104 + INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
1.105 + INSERT INTO t2 VALUES (null, 5);
1.106 + INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'That was a test');
1.107 + INSERT INTO t2 VALUES (null, 20);
1.108 + INSERT INTO t1 (rowid, c) VALUES (last_insert_rowid(), 'This is a test');
1.109 +}
1.110 +
1.111 +do_test fts2e-3.1 {
1.112 + execsql {
1.113 + SELECT rowid FROM t1 WHERE t1 MATCH 'this' ORDER BY rowid;
1.114 + }
1.115 +} {1 3}
1.116 +
1.117 +do_test fts2e-3.2 {
1.118 + execsql {
1.119 + SELECT t1.rowid, weight FROM t1, t2
1.120 + WHERE t1 MATCH 'this' AND t1.rowid = t2.id ORDER BY weight;
1.121 + }
1.122 +} {1 10 3 20}
1.123 +
1.124 +finish_test