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