1# 2006 September 9
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# This file implements regression tests for SQLite library.  The
12# focus of this script is testing the FTS1 module.
13#
14# $Id: fts1a.test,v 1.4 2006/09/28 19:43:32 drh Exp $
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# If SQLITE_ENABLE_FTS1 is defined, omit this file.
21ifcapable !fts1 {
22  finish_test
23  return
24}
25
26# Construct a full-text search table containing five keywords:
27# one, two, three, four, and five, in various combinations.  The
28# rowid for each will be a bitmask for the elements it contains.
29#
30db eval {
31  CREATE VIRTUAL TABLE t1 USING fts1(content);
32  INSERT INTO t1(content) VALUES('one');
33  INSERT INTO t1(content) VALUES('two');
34  INSERT INTO t1(content) VALUES('one two');
35  INSERT INTO t1(content) VALUES('three');
36  INSERT INTO t1(content) VALUES('one three');
37  INSERT INTO t1(content) VALUES('two three');
38  INSERT INTO t1(content) VALUES('one two three');
39  INSERT INTO t1(content) VALUES('four');
40  INSERT INTO t1(content) VALUES('one four');
41  INSERT INTO t1(content) VALUES('two four');
42  INSERT INTO t1(content) VALUES('one two four');
43  INSERT INTO t1(content) VALUES('three four');
44  INSERT INTO t1(content) VALUES('one three four');
45  INSERT INTO t1(content) VALUES('two three four');
46  INSERT INTO t1(content) VALUES('one two three four');
47  INSERT INTO t1(content) VALUES('five');
48  INSERT INTO t1(content) VALUES('one five');
49  INSERT INTO t1(content) VALUES('two five');
50  INSERT INTO t1(content) VALUES('one two five');
51  INSERT INTO t1(content) VALUES('three five');
52  INSERT INTO t1(content) VALUES('one three five');
53  INSERT INTO t1(content) VALUES('two three five');
54  INSERT INTO t1(content) VALUES('one two three five');
55  INSERT INTO t1(content) VALUES('four five');
56  INSERT INTO t1(content) VALUES('one four five');
57  INSERT INTO t1(content) VALUES('two four five');
58  INSERT INTO t1(content) VALUES('one two four five');
59  INSERT INTO t1(content) VALUES('three four five');
60  INSERT INTO t1(content) VALUES('one three four five');
61  INSERT INTO t1(content) VALUES('two three four five');
62  INSERT INTO t1(content) VALUES('one two three four five');
63}
64
65do_test fts1a-1.1 {
66  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one'}
67} {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}
68do_test fts1a-1.2 {
69  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one two'}
70} {3 7 11 15 19 23 27 31}
71do_test fts1a-1.3 {
72  execsql {SELECT rowid FROM t1 WHERE content MATCH 'two one'}
73} {3 7 11 15 19 23 27 31}
74do_test fts1a-1.4 {
75  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one two three'}
76} {7 15 23 31}
77do_test fts1a-1.5 {
78  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one three two'}
79} {7 15 23 31}
80do_test fts1a-1.6 {
81  execsql {SELECT rowid FROM t1 WHERE content MATCH 'two three one'}
82} {7 15 23 31}
83do_test fts1a-1.7 {
84  execsql {SELECT rowid FROM t1 WHERE content MATCH 'two one three'}
85} {7 15 23 31}
86do_test fts1a-1.8 {
87  execsql {SELECT rowid FROM t1 WHERE content MATCH 'three one two'}
88} {7 15 23 31}
89do_test fts1a-1.9 {
90  execsql {SELECT rowid FROM t1 WHERE content MATCH 'three two one'}
91} {7 15 23 31}
92do_test fts1a-1.10 {
93  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one two THREE'}
94} {7 15 23 31}
95do_test fts1a-1.11 {
96  execsql {SELECT rowid FROM t1 WHERE content MATCH '  ONE    Two   three  '}
97} {7 15 23 31}
98
99do_test fts1a-2.1 {
100  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one"'}
101} {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}
102do_test fts1a-2.2 {
103  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one two"'}
104} {3 7 11 15 19 23 27 31}
105do_test fts1a-2.3 {
106  execsql {SELECT rowid FROM t1 WHERE content MATCH '"two one"'}
107} {}
108do_test fts1a-2.4 {
109  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one two three"'}
110} {7 15 23 31}
111do_test fts1a-2.5 {
112  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one three two"'}
113} {}
114do_test fts1a-2.6 {
115  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one two three four"'}
116} {15 31}
117do_test fts1a-2.7 {
118  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one three two four"'}
119} {}
120do_test fts1a-2.8 {
121  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one three five"'}
122} {21}
123do_test fts1a-2.9 {
124  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one three" five'}
125} {21 29}
126do_test fts1a-2.10 {
127  execsql {SELECT rowid FROM t1 WHERE content MATCH 'five "one three"'}
128} {21 29}
129do_test fts1a-2.11 {
130  execsql {SELECT rowid FROM t1 WHERE content MATCH 'five "one three" four'}
131} {29}
132do_test fts1a-2.12 {
133  execsql {SELECT rowid FROM t1 WHERE content MATCH 'five four "one three"'}
134} {29}
135do_test fts1a-2.13 {
136  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one three" four five'}
137} {29}
138
139do_test fts1a-3.1 {
140  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one'}
141} {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}
142do_test fts1a-3.2 {
143  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one -two'}
144} {1 5 9 13 17 21 25 29}
145do_test fts1a-3.3 {
146  execsql {SELECT rowid FROM t1 WHERE content MATCH '-two one'}
147} {1 5 9 13 17 21 25 29}
148
149do_test fts1a-4.1 {
150  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one OR two'}
151} {1 2 3 5 6 7 9 10 11 13 14 15 17 18 19 21 22 23 25 26 27 29 30 31}
152do_test fts1a-4.2 {
153  execsql {SELECT rowid FROM t1 WHERE content MATCH '"one two" OR three'}
154} {3 4 5 6 7 11 12 13 14 15 19 20 21 22 23 27 28 29 30 31}
155do_test fts1a-4.3 {
156  execsql {SELECT rowid FROM t1 WHERE content MATCH 'three OR "one two"'}
157} {3 4 5 6 7 11 12 13 14 15 19 20 21 22 23 27 28 29 30 31}
158do_test fts1a-4.4 {
159  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one two OR three'}
160} {3 5 7 11 13 15 19 21 23 27 29 31}
161do_test fts1a-4.5 {
162  execsql {SELECT rowid FROM t1 WHERE content MATCH 'three OR two one'}
163} {3 5 7 11 13 15 19 21 23 27 29 31}
164do_test fts1a-4.6 {
165  execsql {SELECT rowid FROM t1 WHERE content MATCH 'one two OR three OR four'}
166} {3 5 7 9 11 13 15 19 21 23 25 27 29 31}
167do_test fts1a-4.7 {
168  execsql {SELECT rowid FROM t1 WHERE content MATCH 'two OR three OR four one'}
169} {3 5 7 9 11 13 15 19 21 23 25 27 29 31}
170
171# Test the ability to handle NULL content
172#
173do_test fts1a-5.1 {
174  execsql {INSERT INTO t1(content) VALUES(NULL)}
175} {}
176do_test fts1a-5.2 {
177  set rowid [db last_insert_rowid]
178  execsql {SELECT content FROM t1 WHERE rowid=$rowid}
179} {{}}
180do_test fts1a-5.3 {
181  execsql {SELECT rowid FROM t1 WHERE content MATCH NULL}
182} {}
183
184
185
186finish_test
187