1: # 2011 January 27
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 FTS3 module.
13: #
14:
15: set testdir [file dirname $argv0]
16: source $testdir/tester.tcl
17: ifcapable !fts3 { finish_test ; return }
18: set ::testprefix fts3aux1
19:
20: do_execsql_test 1.1 {
21: CREATE VIRTUAL TABLE t1 USING fts4;
22: INSERT INTO t1 VALUES('one two three four');
23: INSERT INTO t1 VALUES('three four five six');
24: INSERT INTO t1 VALUES('one three five seven');
25:
26: CREATE VIRTUAL TABLE terms USING fts4aux(t1);
27: SELECT term, documents, occurrences FROM terms WHERE col = '*';
28: } {
29: five 2 2 four 2 2 one 2 2 seven 1 1
30: six 1 1 three 3 3 two 1 1
31: }
32:
33: do_execsql_test 1.2 {
34: INSERT INTO t1 VALUES('one one one three three three');
35: SELECT term, documents, occurrences FROM terms WHERE col = '*';
36: } {
37: five 2 2 four 2 2 one 3 5 seven 1 1
38: six 1 1 three 4 6 two 1 1
39: }
40:
41: do_execsql_test 1.3.1 { DELETE FROM t1; }
42: do_execsql_test 1.3.2 {
43: SELECT term, documents, occurrences FROM terms WHERE col = '*';
44: }
45:
46: do_execsql_test 1.4 {
47: INSERT INTO t1 VALUES('a b a b a b a');
48: INSERT INTO t1 SELECT * FROM t1;
49: INSERT INTO t1 SELECT * FROM t1;
50: INSERT INTO t1 SELECT * FROM t1;
51: INSERT INTO t1 SELECT * FROM t1;
52: INSERT INTO t1 SELECT * FROM t1;
53: INSERT INTO t1 SELECT * FROM t1;
54: INSERT INTO t1 SELECT * FROM t1;
55: INSERT INTO t1 SELECT * FROM t1;
56: SELECT term, documents, occurrences FROM terms WHERE col = '*';
57: } {a 256 1024 b 256 768}
58:
59: #-------------------------------------------------------------------------
60: # The following tests verify that the fts4aux module uses the full-text
61: # index to reduce the number of rows scanned in the following circumstances:
62: #
63: # * when there is equality comparison against the term column using the
64: # BINARY collating sequence.
65: #
66: # * when there is a range constraint on the term column using the BINARY
67: # collating sequence.
68: #
69: # And also uses the full-text index to optimize ORDER BY clauses of the
70: # form "ORDER BY term ASC" or equivalent.
71: #
72: # Test organization is:
73: #
74: # fts3aux1-2.1.*: equality constraints.
75: # fts3aux1-2.2.*: range constraints.
76: # fts3aux1-2.3.*: ORDER BY optimization.
77: #
78:
79: do_execsql_test 2.0 {
80: DROP TABLE t1;
81: DROP TABLE terms;
82:
83: CREATE VIRTUAL TABLE x1 USING fts4(x);
84: INSERT INTO x1(x1) VALUES('nodesize=24');
85: CREATE VIRTUAL TABLE terms USING fts4aux(x1);
86:
87: CREATE VIEW terms_v AS
88: SELECT term, documents, occurrences FROM terms WHERE col = '*';
89:
90: INSERT INTO x1 VALUES('braes brag bragged bragger bragging');
91: INSERT INTO x1 VALUES('brags braid braided braiding braids');
92: INSERT INTO x1 VALUES('brain brainchild brained braining brains');
93: INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms');
94: }
95:
96: proc rec {varname x} {
97: global $varname
98: incr $varname
99: return 1
100: }
101: db func rec rec
102:
103: # Use EQP to show that the WHERE expression "term='braid'" uses a different
104: # index number (1) than "+term='braid'" (0).
105: #
106: do_execsql_test 2.1.1.1 {
107: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
108: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} }
109: do_execsql_test 2.1.1.2 {
110: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
111: } {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}}
112:
113: # Now show that using "term='braid'" means the virtual table returns
114: # only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
115: #
116: do_test 2.1.2.1 {
117: set cnt 0
118: execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }
119: set cnt
120: } {2}
121: do_test 2.1.2.2 {
122: set cnt 0
123: execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='braid' }
124: set cnt
125: } {38}
126:
127: # Similar to the test immediately above, but using a term ("breakfast") that
128: # is not featured in the dataset.
129: #
130: do_test 2.1.3.1 {
131: set cnt 0
132: execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='breakfast' }
133: set cnt
134: } {0}
135: do_test 2.1.3.2 {
136: set cnt 0
137: execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='breakfast' }
138: set cnt
139: } {38}
140:
141: do_execsql_test 2.1.4.1 { SELECT * FROM terms_v WHERE term='braid' } {braid 1 1}
142: do_execsql_test 2.1.4.2 { SELECT * FROM terms_v WHERE +term='braid'} {braid 1 1}
143: do_execsql_test 2.1.4.3 { SELECT * FROM terms_v WHERE term='breakfast' } {}
144: do_execsql_test 2.1.4.4 { SELECT * FROM terms_v WHERE +term='breakfast' } {}
145:
146: do_execsql_test 2.1.4.5 { SELECT * FROM terms_v WHERE term='cba' } {}
147: do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {}
148: do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc' } {}
149: do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {}
150:
151: # Special case: term=NULL
152: #
153: do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}
154:
155: do_execsql_test 2.2.1.1 {
156: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
157: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~0 rows)} }
158: do_execsql_test 2.2.1.2 {
159: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
160: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
161:
162: do_execsql_test 2.2.1.3 {
163: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
164: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~0 rows)} }
165: do_execsql_test 2.2.1.4 {
166: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
167: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
168:
169: do_execsql_test 2.2.1.5 {
170: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
171: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~0 rows)} }
172: do_execsql_test 2.2.1.6 {
173: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
174: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
175:
176: do_test 2.2.2.1 {
177: set cnt 0
178: execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
179: set cnt
180: } {18}
181: do_test 2.2.2.2 {
182: set cnt 0
183: execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' }
184: set cnt
185: } {38}
186: do_execsql_test 2.2.2.3 {
187: SELECT term, documents, occurrences FROM terms_v WHERE term>'brain'
188: } {
189: brainchild 1 1 brained 1 1 braining 1 1 brains 1 1
190: brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
191: }
192: do_execsql_test 2.2.2.4 {
193: SELECT term, documents, occurrences FROM terms_v WHERE +term>'brain'
194: } {
195: brainchild 1 1 brained 1 1 braining 1 1 brains 1 1
196: brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
197: }
198: do_execsql_test 2.2.2.5 {
199: SELECT term, documents, occurrences FROM terms_v WHERE term>='brain'
200: } {
201: brain 1 1
202: brainchild 1 1 brained 1 1 braining 1 1 brains 1 1
203: brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
204: }
205: do_execsql_test 2.2.2.6 {
206: SELECT term, documents, occurrences FROM terms_v WHERE +term>='brain'
207: } {
208: brain 1 1
209: brainchild 1 1 brained 1 1 braining 1 1 brains 1 1
210: brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
211: }
212:
213: do_execsql_test 2.2.2.7 {
214: SELECT term, documents, occurrences FROM terms_v WHERE term>='abc'
215: } {
216: braes 1 1 brag 1 1 bragged 1 1 bragger 1 1
217: bragging 1 1 brags 1 1 braid 1 1 braided 1 1
218: braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1
219: brained 1 1 braining 1 1 brains 1 1 brainstem 1 1
220: brainstems 1 1 brainstorm 1 1 brainstorms 1 1
221: }
222: do_execsql_test 2.2.2.8 {
223: SELECT term, documents, occurrences FROM terms_v WHERE +term>='abc'
224: } {
225: braes 1 1 brag 1 1 bragged 1 1 bragger 1 1
226: bragging 1 1 brags 1 1 braid 1 1 braided 1 1
227: braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1
228: brained 1 1 braining 1 1 brains 1 1 brainstem 1 1
229: brainstems 1 1 brainstorm 1 1 brainstorms 1 1
230: }
231:
232: do_execsql_test 2.2.2.9 {
233: SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'
234: } {brainstorms 1 1}
235: do_execsql_test 2.2.2.10 {
236: SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'
237: } {brainstorms 1 1}
238: do_execsql_test 2.2.2.11 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}
239: do_execsql_test 2.2.2.12 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}
240:
241: do_execsql_test 2.2.2.13 { SELECT * FROM terms_v WHERE term>'cba' } {}
242: do_execsql_test 2.2.2.14 { SELECT * FROM terms_v WHERE term>'cba' } {}
243:
244: do_test 2.2.3.1 {
245: set cnt 0
246: execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' }
247: set cnt
248: } {22}
249: do_test 2.2.3.2 {
250: set cnt 0
251: execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' }
252: set cnt
253: } {38}
254: do_execsql_test 2.2.3.3 {
255: SELECT term, documents, occurrences FROM terms_v WHERE term<'brain'
256: } {
257: braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1
258: brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
259: }
260: do_execsql_test 2.2.3.4 {
261: SELECT term, documents, occurrences FROM terms_v WHERE +term<'brain'
262: } {
263: braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1
264: brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
265: }
266: do_execsql_test 2.2.3.5 {
267: SELECT term, documents, occurrences FROM terms_v WHERE term<='brain'
268: } {
269: braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1
270: brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
271: brain 1 1
272: }
273: do_execsql_test 2.2.3.6 {
274: SELECT term, documents, occurrences FROM terms_v WHERE +term<='brain'
275: } {
276: braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1
277: brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
278: brain 1 1
279: }
280:
281: do_test 2.2.4.1 {
282: set cnt 0
283: execsql {
284: SELECT term, documents, occurrences FROM terms
285: WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain'
286: }
287: set cnt
288: } {12}
289: do_test 2.2.4.2 {
290: set cnt 0
291: execsql {
292: SELECT term, documents, occurrences FROM terms
293: WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain'
294: }
295: set cnt
296: } {38}
297: do_execsql_test 2.2.4.3 {
298: SELECT term, documents, occurrences FROM terms_v
299: WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain'
300: } {
301: brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1
302: }
303: do_execsql_test 2.2.4.4 {
304: SELECT term, documents, occurrences FROM terms_v
305: WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain'
306: } {
307: brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1
308: }
309: do_execsql_test 2.2.4.5 {
310: SELECT term, documents, occurrences FROM terms_v
311: WHERE rec('cnt', term) AND term > 'brags' AND term < 'brain'
312: } {
313: braid 1 1 braided 1 1 braiding 1 1 braids 1 1
314: }
315: do_execsql_test 2.2.4.6 {
316: SELECT term, documents, occurrences FROM terms_v
317: WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain'
318: } {
319: braid 1 1 braided 1 1 braiding 1 1 braids 1 1
320: }
321:
322: # Check that "ORDER BY term ASC" and equivalents are sorted by the
323: # virtual table implementation. Any other ORDER BY clause requires
324: # SQLite to sort results using a temporary b-tree.
325: #
326: foreach {tn sort orderby} {
327: 1 0 "ORDER BY term ASC"
328: 2 0 "ORDER BY term"
329: 3 1 "ORDER BY term DESC"
330: 4 1 "ORDER BY documents ASC"
331: 5 1 "ORDER BY documents"
332: 6 1 "ORDER BY documents DESC"
333: 7 1 "ORDER BY occurrences ASC"
334: 8 1 "ORDER BY occurrences"
335: 9 1 "ORDER BY occurrences DESC"
336: } {
337:
338: set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}]
339: if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} }
340:
341: set sql "SELECT * FROM terms $orderby"
342: do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
343: }
344:
345: #-------------------------------------------------------------------------
346: # The next set of tests, fts3aux1-3.*, test error conditions in the
347: # fts4aux module. Except, fault injection testing (OOM, IO error etc.) is
348: # done in fts3fault2.test
349: #
350:
351: do_execsql_test 3.1.1 {
352: CREATE VIRTUAL TABLE t2 USING fts4;
353: }
354:
355: do_catchsql_test 3.1.2 {
356: CREATE VIRTUAL TABLE terms2 USING fts4aux;
357: } {1 {wrong number of arguments to fts4aux constructor}}
358: do_catchsql_test 3.1.3 {
359: CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2);
360: } {1 {wrong number of arguments to fts4aux constructor}}
361:
362: do_execsql_test 3.2.1 {
363: CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist)
364: }
365: do_catchsql_test 3.2.2 {
366: SELECT * FROM terms3
367: } {1 {SQL logic error or missing database}}
368: do_catchsql_test 3.2.3 {
369: SELECT * FROM terms3 WHERE term = 'abc'
370: } {1 {SQL logic error or missing database}}
371:
372: do_catchsql_test 3.3.1 {
373: INSERT INTO terms VALUES(1,2,3);
374: } {1 {table terms may not be modified}}
375: do_catchsql_test 3.3.2 {
376: DELETE FROM terms
377: } {1 {table terms may not be modified}}
378: do_catchsql_test 3.3.3 {
379: UPDATE terms set documents = documents+1;
380: } {1 {table terms may not be modified}}
381:
382:
383: #-------------------------------------------------------------------------
384: # The following tests - fts4aux-4.* - test that joins work with fts4aux
385: # tables. And that fts4aux provides reasonably sane cost information via
386: # xBestIndex to the query planner.
387: #
388: db close
389: forcedelete test.db
390: sqlite3 db test.db
391: do_execsql_test 4.1 {
392: CREATE VIRTUAL TABLE x1 USING fts4(x);
393: CREATE VIRTUAL TABLE terms USING fts4aux(x1);
394: CREATE TABLE x2(y);
395: CREATE TABLE x3(y);
396: CREATE INDEX i1 ON x3(y);
397:
398: INSERT INTO x1 VALUES('a b c d e');
399: INSERT INTO x1 VALUES('f g h i j');
400: INSERT INTO x1 VALUES('k k l l a');
401:
402: INSERT INTO x2 SELECT term FROM terms WHERE col = '*';
403: INSERT INTO x3 SELECT term FROM terms WHERE col = '*';
404: }
405:
406: proc do_plansql_test {tn sql r} {
407: uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
408: }
409:
410: do_plansql_test 4.2 {
411: SELECT y FROM x2, terms WHERE y = term AND col = '*'
412: } {
413: 0 0 0 {SCAN TABLE x2 (~1000000 rows)}
414: 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)}
415: a b c d e f g h i j k l
416: }
417:
418: do_plansql_test 4.3 {
419: SELECT y FROM terms, x2 WHERE y = term AND col = '*'
420: } {
421: 0 0 1 {SCAN TABLE x2 (~1000000 rows)}
422: 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)}
423: a b c d e f g h i j k l
424: }
425:
426: do_plansql_test 4.4 {
427: SELECT y FROM x3, terms WHERE y = term AND col = '*'
428: } {
429: 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}
430: 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
431: a b c d e f g h i j k l
432: }
433:
434: do_plansql_test 4.5 {
435: SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
436: } {
437: 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}
438: 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
439: a k l
440: }
441:
442: #-------------------------------------------------------------------------
443: # The following tests check that fts4aux can handle an fts table with an
444: # odd name (one that requires quoting for use in SQL statements). And that
445: # the argument to the fts4aux constructor is properly dequoted before use.
446: #
447: #
448: do_execsql_test 5.1 {
449: CREATE VIRTUAL TABLE "abc '!' def" USING fts4(x, y);
450: INSERT INTO "abc '!' def" VALUES('XX', 'YY');
451:
452: CREATE VIRTUAL TABLE terms3 USING fts4aux("abc '!' def");
453: SELECT * FROM terms3;
454: } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
455:
456: do_execsql_test 5.2 {
457: CREATE VIRTUAL TABLE "%%^^%%" USING fts4aux('abc ''!'' def');
458: SELECT * FROM "%%^^%%";
459: } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
460:
461:
462: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to fts3aux1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test