Annotation of embedaddon/sqlite3/test/fts4content.test, revision 1.1.1.1
1.1 misho 1: # 2011 October 03
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 content=xxx FTS4 option.
13: #
14:
15: set testdir [file dirname $argv0]
16: source $testdir/tester.tcl
17: set ::testprefix fts4content
18:
19: # If SQLITE_ENABLE_FTS3 is defined, omit this file.
20: ifcapable !fts3 {
21: finish_test
22: return
23: }
24:
25: #-------------------------------------------------------------------------
26: # Test organization:
27: #
28: # 1.* - Warm-body tests.
29: #
30: # 2.* - Querying a content=xxx FTS table.
31: #
32: # 3.* - Writing to a content=xxx FTS table.
33: #
34: # 4.* - The "INSERT INTO fts(fts) VALUES('rebuild')" command.
35: #
36: # 5.* - Check that CREATE TABLE, DROP TABLE and ALTER TABLE correctly
37: # ignore any %_content table when used with the content=xxx option.
38: #
39: # 6.* - Test the effects of messing with the schema of table xxx after
40: # creating a content=xxx FTS index.
41: #
42: # 7.* - Test that if content=xxx is specified and table xxx does not
43: # exist, the FTS table can still be used for INSERT and some
44: # SELECT statements.
45: #
46:
47: do_execsql_test 1.1.1 {
48: CREATE TABLE t1(a, b, c);
49: INSERT INTO t1 VALUES('w x', 'x y', 'y z');
50: CREATE VIRTUAL TABLE ft1 USING fts4(content=t1);
51: }
52:
53: do_execsql_test 1.1.2 {
54: PRAGMA table_info(ft1);
55: } {
56: 0 a {} 0 {} 0
57: 1 b {} 0 {} 0
58: 2 c {} 0 {} 0
59: }
60:
61: do_execsql_test 1.1.3 { SELECT *, rowid FROM ft1 } {{w x} {x y} {y z} 1}
62: do_execsql_test 1.1.4 { SELECT a, c FROM ft1 WHERE rowid=1 } {{w x} {y z}}
63:
64: do_execsql_test 1.1.5 { INSERT INTO ft1(ft1) VALUES('rebuild') } {}
65: do_execsql_test 1.1.6 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'x' } {1}
66: do_execsql_test 1.1.7 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'a' } {}
67:
68: do_execsql_test 1.2.1 {
69: DROP TABLE ft1;
70: CREATE VIRTUAL TABLE ft1 USING fts4(content=t1, b);
71: PRAGMA table_info(ft1);
72: } {
73: 0 b {} 0 {} 0
74: }
75: do_execsql_test 1.2.2 {
76: SELECT *, rowid FROM ft1
77: } {{x y} 1}
78:
79: #-------------------------------------------------------------------------
80: # The following block of tests - 2.* - test that a content=xxx FTS table
81: # can be queried. Also tested are cases where rows identified in the FTS
82: # are missing from the content table, and cases where the index is
83: # inconsistent with the content table.
84: #
85: do_execsql_test 2.0 {
86: CREATE TABLE t2(x);
87: INSERT INTO t2 VALUES('O S W W F U C R Q I C N P Z Y Y E Y Y E'); -- 1
88: INSERT INTO t2 VALUES('Y X U V L B E H Y J C Y A I A P V F V K'); -- 2
89: INSERT INTO t2 VALUES('P W I N J H I I N I F B K D U Q B Z S F'); -- 3
90: INSERT INTO t2 VALUES('N R O R H J R H G M D I U U B O M P A U'); -- 4
91: INSERT INTO t2 VALUES('Y O V O G T P N G T N F I V B U M J M G'); -- 5
92: INSERT INTO t2 VALUES('J O B N K N E C H Z R K J O U G M K L S'); -- 6
93: INSERT INTO t2 VALUES('S Z S R I Q U A P W R X H K C Z U L S P'); -- 7
94: INSERT INTO t2 VALUES('J C H N R C K R V N M O F Z M Z A I H W'); -- 8
95: INSERT INTO t2 VALUES('O Y G I S J U U W O D Z F J K N R P R L'); -- 9
96: INSERT INTO t2 VALUES('B G L K U R U P V X Z I H V R W C Q A S'); -- 10
97: INSERT INTO t2 VALUES('T F T J F F Y V F W N X K Q A Y L X W G'); -- 11
98: INSERT INTO t2 VALUES('C J U H B Q X L C M M Y E G V F W V Z C'); -- 12
99: INSERT INTO t2 VALUES('B W L T F S G X D P H N G M R I O A X I'); -- 13
100: INSERT INTO t2 VALUES('N G Y O K Q K Z N M H U J E D H U W R K'); -- 14
101: INSERT INTO t2 VALUES('U D T R U Y F J D S J X E H Q G V A S Z'); -- 15
102: INSERT INTO t2 VALUES('M I W P J S H R J D Q I C G P C T P H R'); -- 16
103: INSERT INTO t2 VALUES('J M N I S L X Q C A B F C B Y D H V R J'); -- 17
104: INSERT INTO t2 VALUES('F V Z W J Q L P X Y E W B U Q N H X K T'); -- 18
105: INSERT INTO t2 VALUES('R F S R Y O F Q E I E G H C B H R X Y N'); -- 19
106: INSERT INTO t2 VALUES('U Q Q Q T E P D M F X P J G H X C Q D L'); -- 20
107: }
108:
109: do_execsql_test 2.1 {
110: CREATE VIRTUAL TABLE ft2 USING fts4(content=t2);
111: INSERT INTO ft2(ft2) VALUES('rebuild');
112:
113: -- Modify the backing table a bit: Row 17 is missing and the contents
114: -- of row 20 do not match the FTS index contents.
115: DELETE FROM t2 WHERE rowid = 17;
116: UPDATE t2 SET x = 'a b c d e f g h i j' WHERE rowid = 20;
117: }
118:
119: foreach {tn match rowidlist} {
120: 1 {S} {1 3 6 7 9 10 13 15 16 17 19}
121: 2 {"S R"} {7 19}
122: 3 {"N K N"} {6}
123: 4 {"Q Q"} {20}
124: 5 {"B Y D"} {17}
125: } {
126: do_execsql_test 2.2.1.$tn {
127: SELECT rowid FROM ft2 WHERE ft2 MATCH $match
128: } $rowidlist
129:
130: do_execsql_test 2.2.2.$tn {
131: SELECT docid FROM ft2 WHERE ft2 MATCH $match
132: } $rowidlist
133: }
134:
135: foreach {tn match result} {
136: 1 {"N K N"} {{J O B N K N E C H Z R K J O U G M K L S}}
137: 2 {"Q Q"} {{a b c d e f g h i j}}
138: 3 {"B Y D"} {{}}
139: } {
140: do_execsql_test 2.3.$tn {
141: SELECT * FROM ft2 WHERE ft2 MATCH $match
142: } $result
143: }
144:
145: foreach {tn match result} {
146: 1 {"N K N"} {{..O B [N] [K] [N] E..}}
147: 2 {"B Y D"} {{}}
148: 3 {"Q Q"} {{a [b] [c] [d] e f..}}
149: } {
150: do_execsql_test 2.4.$tn {
151: SELECT snippet(ft2, '[', ']', '..', -1, 6) FROM ft2 WHERE ft2 MATCH $match
152: } $result
153: }
154:
155: foreach {tn match result} {
156: 1 {"N K N"} {{0 0 6 1 0 1 8 1 0 2 10 1}}
157: 2 {"B Y D"} {{}}
158: 3 {"Q Q"} {{0 0 2 1 0 0 4 1 0 1 4 1 0 1 6 1}}
159: 4 {"Q D L"} {{}}
160: } {
161: do_execsql_test 2.5.$tn {
162: SELECT offsets(ft2) FROM ft2 WHERE ft2 MATCH $match
163: } $result
164: }
165:
166: #-------------------------------------------------------------------------
167: # The following block of tests - 3.* - test that the FTS index can be
168: # modified by writing to the table. But that this has no effect on the
169: # content table.
170: #
171:
172: do_execsql_test 3.1 {
173: CREATE TABLE t3(x, y);
174: CREATE VIRTUAL TABLE ft3 USING fts4(content=t3);
175: }
176:
177: do_catchsql_test 3.1.1 {
178: INSERT INTO ft3 VALUES('a b c', 'd e f');
179: } {1 {constraint failed}}
180: do_execsql_test 3.1.2 {
181: INSERT INTO ft3(docid, x, y) VALUES(21, 'a b c', 'd e f');
182: SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
183: } {21}
184: do_execsql_test 3.1.3 { SELECT * FROM t3 } {}
185:
186: # This DELETE does not work, since there is no row in [t3] to base the
187: # DELETE on. So the SELECT on [ft3] still returns rowid 21.
188: do_execsql_test 3.1.4 {
189: DELETE FROM ft3;
190: SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
191: } {21}
192:
193: # If the row is added to [t3] before the DELETE on [ft3], it works.
194: do_execsql_test 3.1.5 {
195: INSERT INTO t3(rowid, x, y) VALUES(21, 'a b c', 'd e f');
196: DELETE FROM ft3;
197: SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
198: } {}
199: do_execsql_test 3.1.6 { SELECT rowid FROM t3 } {21}
200:
201: do_execsql_test 3.2.1 {
202: INSERT INTO ft3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');
203: INSERT INTO ft3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');
204: INSERT INTO ft3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');
205: INSERT INTO ft3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');
206: INSERT INTO ft3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');
207: INSERT INTO ft3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');
208: INSERT INTO ft3(rowid, x, y) VALUES(6, 'I Q I S P', 'D R O Q B');
209: INSERT INTO ft3(rowid, x, y) VALUES(7, 'T K T Z J', 'B W D G O');
210: INSERT INTO ft3(rowid, x, y) VALUES(8, 'Y K F X T', 'D F G V G');
211: INSERT INTO ft3(rowid, x, y) VALUES(9, 'E L E T L', 'P W N F Z');
212: INSERT INTO ft3(rowid, x, y) VALUES(10, 'O G J G X', 'G J F E P');
213: INSERT INTO ft3(rowid, x, y) VALUES(11, 'O L N N Z', 'K E Z F D');
214: INSERT INTO ft3(rowid, x, y) VALUES(12, 'R Z M R J', 'X G I M Z');
215: INSERT INTO ft3(rowid, x, y) VALUES(13, 'L X N N X', 'R R N S T');
216: INSERT INTO ft3(rowid, x, y) VALUES(14, 'F L B J H', 'K W F L C');
217: INSERT INTO ft3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');
218: INSERT INTO ft3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');
219: INSERT INTO ft3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');
220: INSERT INTO ft3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');
221: INSERT INTO ft3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');
222: }
223:
224: foreach {tn match rowidlist} {
225: 1 "N A" {5 19}
226: 2 "x:O" {1 2 10 11 17}
227: 3 "y:O" {0 2 6 7 18 19}
228: } {
229: set res [list]
230: foreach rowid $rowidlist { lappend res $rowid {} {} }
231:
232: do_execsql_test 3.2.2.$tn {
233: SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
234: } $res
235: do_execsql_test 3.2.3.$tn {
236: SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
237: } $res
238: }
239:
240: do_execsql_test 3.3.1 {
241: INSERT INTO t3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');
242: INSERT INTO t3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');
243: INSERT INTO t3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');
244: INSERT INTO t3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');
245: INSERT INTO t3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');
246: INSERT INTO t3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');
247: UPDATE ft3 SET x = y, y = x;
248: DELETE FROM t3;
249: }
250:
251: foreach {tn match rowidlist} {
252: 1 "N A" {5 19}
253: 2 "x:O" {0 2 10 11 17}
254: 3 "y:O" {1 2 6 7 18 19}
255: } {
256: set res [list]
257: foreach rowid $rowidlist { lappend res $rowid {} {} }
258:
259: do_execsql_test 3.3.2.$tn {
260: SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
261: } $res
262: do_execsql_test 3.3.3.$tn {
263: SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
264: } $res
265: }
266:
267: do_execsql_test 3.3.1 {
268: INSERT INTO t3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');
269: INSERT INTO t3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');
270: INSERT INTO t3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');
271: INSERT INTO t3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');
272: INSERT INTO t3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');
273: DELETE FROM ft3;
274: }
275:
276: foreach {tn match rowidlist} {
277: 1 "N A" {5}
278: 2 "x:O" {0 2 10 11}
279: 3 "y:O" {1 2 6 7}
280: } {
281: set res [list]
282: foreach rowid $rowidlist { lappend res $rowid {} {} }
283:
284: do_execsql_test 3.3.2.$tn {
285: SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
286: } $res
287: do_execsql_test 3.3.3.$tn {
288: SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
289: } $res
290: }
291:
292:
293: #-------------------------------------------------------------------------
294: # Test cases 4.* test the 'rebuild' command. On content=xxx and regular
295: # FTS tables.
296: #
297: do_execsql_test 4.0 {
298: CREATE TABLE t4(x);
299: CREATE VIRTUAL TABLE ft4 USING fts4(content=t4);
300: CREATE VIRTUAL TABLE ft4x USING fts4(x);
301: }
302:
303: do_execsql_test 4.1.1 {
304: INSERT INTO ft4x(ft4x) VALUES('rebuild');
305: INSERT INTO ft4(ft4) VALUES('rebuild');
306: } {}
307: do_execsql_test 4.1.2 {
308: SELECT id, quote(value) FROM ft4_stat
309: } {0 X'000000'}
310: do_execsql_test 4.1.3 {
311: SELECT id, quote(value) FROM ft4x_stat
312: } {0 X'000000'}
313:
314: do_execsql_test 4.2.1 {
315: INSERT INTO ft4x VALUES('M G M F T');
316: INSERT INTO ft4x VALUES('Z Q C A U');
317: INSERT INTO ft4x VALUES('N L L V');
318: INSERT INTO ft4x VALUES('T F D X D');
319: INSERT INTO ft4x VALUES('Z H I S D');
320:
321: SELECT id, quote(value) FROM ft4x_stat
322: } {0 X'05182B'}
323:
324: do_execsql_test 4.2.2 {
325: INSERT INTO ft4(rowid, x) SELECT rowid, * FROM ft4x;
326: SELECT id, quote(value) FROM ft4_stat
327: } {0 X'05182B'}
328:
329: do_execsql_test 4.2.3 {
330: SELECT docid, quote(size) FROM ft4_docsize
331: } {1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}
332:
333: do_execsql_test 4.2.4 {
334: INSERT INTO ft4x(ft4x) VALUES('rebuild');
335: SELECT id, quote(value) FROM ft4x_stat;
336: SELECT docid, quote(size) FROM ft4x_docsize
337: } {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}
338:
339: do_execsql_test 4.2.5 {
340: INSERT INTO ft4(ft4) VALUES('rebuild');
341: SELECT id, quote(value) FROM ft4_stat;
342: SELECT docid, quote(size) FROM ft4_docsize
343: } {0 X'000000'}
344:
345: do_execsql_test 4.2.6 {
346: INSERT INTO t4(rowid, x) SELECT rowid, x FROM ft4x;
347: INSERT INTO ft4(ft4) VALUES('rebuild');
348: SELECT id, quote(value) FROM ft4_stat;
349: SELECT docid, quote(size) FROM ft4_docsize
350: } {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}
351:
352:
353: #-------------------------------------------------------------------------
354: # Test cases 5.* test that the following commands do not create/move or
355: # delete a %_content table when used with a content=xxx FTS table.
356: #
357: do_execsql_test 5.1.1 {
358: CREATE TABLE t5(a, b, c, d);
359: CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);
360: SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
361: } {
362: t5 ft5 ft5_segments ft5_segdir
363: sqlite_autoindex_ft5_segdir_1 ft5_docsize ft5_stat
364: }
365: do_execsql_test 5.1.2 {
366: ALTER TABLE ft5 RENAME TO ft6;
367: SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
368: } {
369: t5
370: }
371: do_execsql_test 5.1.3 {
372: SELECT name FROM sqlite_master WHERE name LIKE '%t6%';
373: } {
374: ft6 ft6_segments ft6_segdir
375: sqlite_autoindex_ft6_segdir_1 ft6_docsize ft6_stat
376: }
377: do_execsql_test 5.1.4 {
378: INSERT INTO t5 VALUES('a', 'b', 'c', 'd');
379: INSERT INTO ft6(ft6) VALUES('rebuild');
380: SELECT rowid FROM ft6 WHERE ft6 MATCH 'b';
381: } {1}
382: do_execsql_test 5.1.5 {
383: DROP TABLE ft6;
384: SELECT * FROM t5;
385: } {a b c d}
386: do_execsql_test 5.1.6 {
387: SELECT name FROM sqlite_master WHERE name LIKE '%t6%';
388: } {
389: }
390: do_execsql_test 5.1.7 {
391: CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);
392: CREATE TABLE t5_content(a, b);
393: DROP TABLE ft5;
394: SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
395: } {
396: t5 t5_content
397: }
398:
399: #-------------------------------------------------------------------------
400: # Test cases 6.* test
401: #
402: do_catchsql_test 6.1.1 {
403: CREATE VIRTUAL TABLE ft7 USING fts4(content=t7);
404: } {1 {vtable constructor failed: ft7}}
405:
406: do_execsql_test 6.2.1 {
407: CREATE TABLE t7(one, two);
408: CREATE VIRTUAL TABLE ft7 USING fts4(content=t7);
409: INSERT INTO t7 VALUES('A B', 'B A');
410: INSERT INTO t7 VALUES('C D', 'A A');
411: SELECT * FROM ft7;
412: } {
413: {A B} {B A} {C D} {A A}
414: }
415:
416: do_catchsql_test 6.2.2 {
417: DROP TABLE t7;
418: SELECT * FROM ft7;
419: } {1 {SQL logic error or missing database}}
420:
421: db close
422: sqlite3 db test.db
423: do_execsql_test 6.2.3 {
424: SELECT name FROM sqlite_master WHERE name LIKE '%t7%'
425: } {
426: ft7 ft7_segments ft7_segdir sqlite_autoindex_ft7_segdir_1
427: ft7_docsize ft7_stat
428: }
429: do_catchsql_test 6.2.4 {
430: SELECT * FROM ft7;
431: } {1 {vtable constructor failed: ft7}}
432: do_execsql_test 6.2.5 {
433: CREATE TABLE t7(x, y);
434: INSERT INTO t7 VALUES('A B', 'B A');
435: INSERT INTO t7 VALUES('C D', 'A A');
436: SELECT * FROM ft7;
437: } {
438: {A B} {B A} {C D} {A A}
439: }
440:
441: do_execsql_test 6.2.6 {
442: INSERT INTO ft7(ft7) VALUES('rebuild');
443: SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
444: } {2}
445:
446: do_execsql_test 6.2.7 {
447: DROP TABLE t7;
448: CREATE TABLE t7(x);
449: }
450: do_catchsql_test 6.2.8 {
451: SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';
452: } {1 {SQL logic error or missing database}}
453: do_catchsql_test 6.2.9 {
454: SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';
455: } {1 {SQL logic error or missing database}}
456:
457: db close
458: sqlite3 db test.db
459: do_catchsql_test 6.2.10 {
460: SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
461: } {0 2}
462: do_catchsql_test 6.2.11 {
463: SELECT rowid, * FROM ft7 WHERE ft7 MATCH '"A A"';
464: } {0 {2 {}}}
465:
466: #-------------------------------------------------------------------------
467: # Test cases 7.*
468: #
469: do_execsql_test 7.1.1 {
470: CREATE VIRTUAL TABLE ft8 USING fts4(content=nosuchtable, x);
471: INSERT INTO ft8(docid, x) VALUES(13, 'U O N X G');
472: INSERT INTO ft8(docid, x) VALUES(14, 'C J J U B');
473: INSERT INTO ft8(docid, x) VALUES(15, 'N J Y G X');
474: INSERT INTO ft8(docid, x) VALUES(16, 'R Y D O R');
475: INSERT INTO ft8(docid, x) VALUES(17, 'I Y T Q O');
476: }
477:
478: do_execsql_test 7.1.2 {
479: SELECT docid FROM ft8 WHERE ft8 MATCH 'N';
480: } {13 15}
481:
482: do_execsql_test 7.2.1 {
483: CREATE VIRTUAL TABLE ft9 USING fts4(content=, x);
484: INSERT INTO ft9(docid, x) VALUES(13, 'U O N X G');
485: INSERT INTO ft9(docid, x) VALUES(14, 'C J J U B');
486: INSERT INTO ft9(docid, x) VALUES(15, 'N J Y G X');
487: INSERT INTO ft9(docid, x) VALUES(16, 'R Y D O R');
488: INSERT INTO ft9(docid, x) VALUES(17, 'I Y T Q O');
489: }
490: do_execsql_test 7.2.2 {
491: SELECT docid FROM ft9 WHERE ft9 MATCH 'N';
492: } {13 15}
493: do_execsql_test 7.2.3 {
494: SELECT name FROM sqlite_master WHERE name LIKE 'ft9_%';
495: } {ft9_segments ft9_segdir ft9_docsize ft9_stat}
496:
497: do_catchsql_test 7.2.4 {
498: SELECT * FROM ft9 WHERE ft9 MATCH 'N';
499: } {1 {SQL logic error or missing database}}
500:
501: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>