AryaWu/sqlite

1# 2008 July 292#3# The author disclaims copyright to this source code.  In place of4# 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# These tests exercise the various types of fts3 cursors.12#13# $Id: fts3e.test,v 1.1 2008/07/29 20:24:46 shess Exp $14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# If SQLITE_ENABLE_FTS3 is not defined, omit this file.20ifcapable !fts3 {21  finish_test22  return23}24 25#*************************************************************************26# Test table scan (QUERY_GENERIC).  This kind of query happens for27# queries with no WHERE clause, or for WHERE clauses which cannot be28# satisfied by an index.29db eval {30  DROP TABLE IF EXISTS t1;31  CREATE VIRTUAL TABLE t1 USING fts3(c);32  INSERT INTO t1 (docid, c) VALUES (1, 'This is a test');33  INSERT INTO t1 (docid, c) VALUES (2, 'That was a test');34  INSERT INTO t1 (docid, c) VALUES (3, 'This is a test');35}36 37do_test fts3e-1.1 {38  execsql {39    SELECT docid FROM t1 ORDER BY docid;40  }41} {1 2 3}42 43do_test fts3e-1.2 {44  execsql {45    SELECT docid FROM t1 WHERE c LIKE '%test' ORDER BY docid;46  }47} {1 2 3}48 49do_test fts3e-1.3 {50  execsql {51    SELECT docid FROM t1 WHERE c LIKE 'That%' ORDER BY docid;52  }53} {2}54 55#*************************************************************************56# Test lookup by docid (QUERY_DOCID).  This kind of query happens for57# queries which select by the docid/rowid implicit index.58db eval {59  DROP TABLE IF EXISTS t1;60  DROP TABLE IF EXISTS t2;61  CREATE VIRTUAL TABLE t1 USING fts3(c);62  CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);63  INSERT INTO t2 VALUES (null, 10);64  INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');65  INSERT INTO t2 VALUES (null, 5);66  INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'That was a test');67  INSERT INTO t2 VALUES (null, 20);68  INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');69}70 71# TODO(shess): This actually is doing QUERY_GENERIC?  I'd have72# expected QUERY_DOCID in this case, as for a very large table the73# full scan is less efficient.74do_test fts3e-2.1 {75  execsql {76    SELECT docid FROM t1 WHERE docid in (1, 2, 10);77    SELECT rowid FROM t1 WHERE rowid in (1, 2, 10);78  }79} {1 2 1 2}80 81do_test fts3e-2.2 {82  execsql {83    SELECT docid, weight FROM t1, t2 WHERE t2.id = t1.docid ORDER BY weight;84    SELECT t1.rowid, weight FROM t1, t2 WHERE t2.id = t1.rowid ORDER BY weight;85  }86} {2 5 1 10 3 20 2 5 1 10 3 20}87 88do_test fts3e-2.3 {89  execsql {90    SELECT docid, weight FROM t1, t291           WHERE t2.weight>5 AND t2.id = t1.docid ORDER BY weight;92    SELECT t1.rowid, weight FROM t1, t293           WHERE t2.weight>5 AND t2.id = t1.rowid ORDER BY weight;94  }95} {1 10 3 20 1 10 3 20}96 97#*************************************************************************98# Test lookup by MATCH (QUERY_FULLTEXT).  This is the fulltext index.99db eval {100  DROP TABLE IF EXISTS t1;101  DROP TABLE IF EXISTS t2;102  CREATE VIRTUAL TABLE t1 USING fts3(c);103  CREATE TABLE t2(id INTEGER PRIMARY KEY AUTOINCREMENT, weight INTEGER UNIQUE);104  INSERT INTO t2 VALUES (null, 10);105  INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');106  INSERT INTO t2 VALUES (null, 5);107  INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'That was a test');108  INSERT INTO t2 VALUES (null, 20);109  INSERT INTO t1 (docid, c) VALUES (last_insert_rowid(), 'This is a test');110}111 112do_test fts3e-3.1 {113  execsql {114    SELECT docid FROM t1 WHERE t1 MATCH 'this' ORDER BY docid;115  }116} {1 3}117 118do_test fts3e-3.2 {119  execsql {120    SELECT docid, weight FROM t1, t2121     WHERE t1 MATCH 'this' AND t1.docid = t2.id ORDER BY weight;122  }123} {1 10 3 20}124 125finish_test126