1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/fts3e.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,125 @@
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 fts3 cursors.
1.15 +#
1.16 +# $Id: fts3e.test,v 1.1 2008/07/29 20:24:46 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_FTS3 is not defined, omit this file.
1.23 +ifcapable !fts3 {
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 fts3(c);
1.35 + INSERT INTO t1 (docid, c) VALUES (1, 'This is a test');
1.36 + INSERT INTO t1 (docid, c) VALUES (2, 'That was a test');
1.37 + INSERT INTO t1 (docid, c) VALUES (3, 'This is a test');
1.38 +}
1.39 +
1.40 +do_test fts3e-1.1 {
1.41 + execsql {
1.42 + SELECT docid FROM t1 ORDER BY docid;
1.43 + }
1.44 +} {1 2 3}
1.45 +
1.46 +do_test fts3e-1.2 {
1.47 + execsql {
1.48 + SELECT docid FROM t1 WHERE c LIKE '%test' ORDER BY docid;
1.49 + }
1.50 +} {1 2 3}
1.51 +
1.52 +do_test fts3e-1.3 {
1.53 + execsql {
1.54 + SELECT docid FROM t1 WHERE c LIKE 'That%' ORDER BY docid;
1.55 + }
1.56 +} {2}
1.57 +
1.58 +#*************************************************************************
1.59 +# Test lookup by docid (QUERY_DOCID). This kind of query happens for
1.60 +# queries which select by the docid/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 fts3(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 (docid, c) VALUES (last_insert_rowid(), 'This is a test');
1.68 + INSERT INTO t2 VALUES (null, 5);
1.69 + INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'That was a test');
1.70 + INSERT INTO t2 VALUES (null, 20);
1.71 + INSERT INTO t1 (docid, 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_DOCID in this case, as for a very large table the
1.76 +# full scan is less efficient.
1.77 +do_test fts3e-2.1 {
1.78 + execsql {
1.79 + SELECT docid FROM t1 WHERE docid in (1, 2, 10);
1.80 + SELECT rowid FROM t1 WHERE rowid in (1, 2, 10);
1.81 + }
1.82 +} {1 2 1 2}
1.83 +
1.84 +do_test fts3e-2.2 {
1.85 + execsql {
1.86 + SELECT docid, weight FROM t1, t2 WHERE t2.id = t1.docid ORDER BY weight;
1.87 + SELECT t1.rowid, weight FROM t1, t2 WHERE t2.id = t1.rowid ORDER BY weight;
1.88 + }
1.89 +} {2 5 1 10 3 20 2 5 1 10 3 20}
1.90 +
1.91 +do_test fts3e-2.3 {
1.92 + execsql {
1.93 + SELECT docid, weight FROM t1, t2
1.94 + WHERE t2.weight>5 AND t2.id = t1.docid ORDER BY weight;
1.95 + SELECT t1.rowid, weight FROM t1, t2
1.96 + WHERE t2.weight>5 AND t2.id = t1.rowid ORDER BY weight;
1.97 + }
1.98 +} {1 10 3 20 1 10 3 20}
1.99 +
1.100 +#*************************************************************************
1.101 +# Test lookup by MATCH (QUERY_FULLTEXT). This is the fulltext index.
1.102 +db eval {
1.103 + DROP TABLE IF EXISTS t1;
1.104 + DROP TABLE IF EXISTS t2;
1.105 + CREATE VIRTUAL TABLE t1 USING fts3(c);
1.106 + CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);
1.107 + INSERT INTO t2 VALUES (null, 10);
1.108 + INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');
1.109 + INSERT INTO t2 VALUES (null, 5);
1.110 + INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'That was a test');
1.111 + INSERT INTO t2 VALUES (null, 20);
1.112 + INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');
1.113 +}
1.114 +
1.115 +do_test fts3e-3.1 {
1.116 + execsql {
1.117 + SELECT docid FROM t1 WHERE t1 MATCH 'this' ORDER BY docid;
1.118 + }
1.119 +} {1 3}
1.120 +
1.121 +do_test fts3e-3.2 {
1.122 + execsql {
1.123 + SELECT docid, weight FROM t1, t2
1.124 + WHERE t1 MATCH 'this' AND t1.docid = t2.id ORDER BY weight;
1.125 + }
1.126 +} {1 10 3 20}
1.127 +
1.128 +finish_test