Debian Codegraph @

ext/fts5/test/fts5contentless.test

raw
1 # 2014 Dec 20
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 #
12 # This file contains tests for the content= and content_rowid= options.
13 #
14
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5contentless
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23
24 # Check that it is not possible to specify "contentless_delete=1" for
25 # anything other than a contentless table.
26 #
27 set res(0) {0 {}}
28 set res(1) {1 {contentless_delete=1 requires a contentless table}}
29 foreach {tn sql bError} {
30 1 "(a, b, contentless_delete=1)" 1
31 2 "(a, b, contentless_delete=1, content=abc)" 1
32 3 "(a, b, contentless_delete=1, content=)" 0
33 4 "(content=, contentless_delete=1, a)" 0
34 5 "(content='', contentless_delete=1, hello)" 0
35 } {
36 execsql { BEGIN }
37 do_catchsql_test 1.$tn "CREATE VIRTUAL TABLE t1 USING fts5 $sql" $res($bError)
38 execsql { ROLLBACK }
39 }
40
41 # Check that it is not possible to specify "contentless_delete=1"
42 # along with columnsize=1.
43 #
44 set res(0) {0 {}}
45 set res(1) {1 {contentless_delete=1 is incompatible with columnsize=0}}
46 foreach {tn sql bError} {
47 2 "(a, b, content='', contentless_delete=1, columnsize=0)" 1
48 } {
49 execsql { BEGIN }
50 do_catchsql_test 1.$tn "CREATE VIRTUAL TABLE t1 USING fts5 $sql" $res($bError)
51 execsql { ROLLBACK }
52 }
53
54 # Check that if contentless_delete=1 is specified, then the "origin"
55 # column is added to the %_docsize table.
56 reset_db
57 do_execsql_test 3.0 {
58 CREATE VIRTUAL TABLE x1 USING fts5(c, content='');
59 CREATE VIRTUAL TABLE x2 USING fts5(c, content='', contentless_delete=1);
60 }
61 do_execsql_test 3.1 {
62 SELECT sql FROM sqlite_schema WHERE name IN ('x1_docsize', 'x2_docsize');
63 } {
64 {CREATE TABLE 'x1_docsize'(id INTEGER PRIMARY KEY, sz BLOB)}
65 {CREATE TABLE 'x2_docsize'(id INTEGER PRIMARY KEY, sz BLOB, origin INTEGER)}
66 }
67
68 do_execsql_test 3.2.1 {
69 SELECT hex(block) FROM x1_data WHERE id=10
70 } {00000000000000}
71 do_execsql_test 3.2.2 {
72 SELECT hex(block) FROM x2_data WHERE id=10
73 } {00000000FF000001000000}
74
75 do_execsql_test 3.3 {
76 INSERT INTO x2 VALUES('first text');
77 INSERT INTO x2 VALUES('second text');
78 }
79 do_execsql_test 3.4 {
80 SELECT id, origin FROM x2_docsize
81 } {1 1 2 2}
82 do_execsql_test 3.5 {
83 SELECT level, segment, loc1, loc2 FROM fts5_structure(
84 (SELECT block FROM x2_data WHERE id=10)
85 )
86 } {
87 0 0 1 1
88 0 1 2 2
89 }
90 do_execsql_test 3.6 {
91 INSERT INTO x2(x2) VALUES('optimize');
92 }
93 do_execsql_test 3.7 {
94 SELECT level, segment, loc1, loc2 FROM fts5_structure(
95 (SELECT block FROM x2_data WHERE id=10)
96 )
97 } {
98 1 0 1 2
99 }
100
101 do_execsql_test 3.8 {
102 DELETE FROM x2 WHERE rowid=2;
103 }
104
105 do_execsql_test 3.9 {
106 SELECT rowid FROM x2('text')
107 } {1}
108
109 #--------------------------------------------------------------------------
110 reset_db
111 proc document {n} {
112 set vocab [list A B C D E F G H I J K L M N O P Q R S T U V W X Y Z]
113 set ret [list]
114 for {set ii 0} {$ii < $n} {incr ii} {
115 lappend ret [lindex $vocab [expr int(rand()*[llength $vocab])]]
116 }
117 set ret
118 }
119
120 set nRow 1000
121
122 do_execsql_test 4.0 {
123 CREATE TABLE t1(x);
124 CREATE VIRTUAL TABLE ft USING fts5(x, content='', contentless_delete=1);
125 INSERT INTO ft(ft, rank) VALUES('pgsz', 100);
126 }
127 do_test 4.1 {
128 for {set ii 0} {$ii < $nRow} {incr ii} {
129 set doc [document 6]
130 execsql {
131 INSERT INTO t1 VALUES($doc);
132 INSERT INTO ft VALUES($doc);
133 }
134 }
135 } {}
136
137 foreach v {A B C D E F G H I J K L M N O P Q R S T U V W X Y Z} {
138 set L1 [execsql {SELECT rowid FROM t1 WHERE x LIKE '%'||$v||'%'}]
139 set L2 [execsql {SELECT rowid FROM ft($v)}]
140 do_test 4.2.$v { set L1 } $L2
141 }
142
143 do_test 4.3 {
144 for {set ii 1} {$ii < $nRow} {incr ii 2} {
145 execsql {
146 DELETE FROM ft WHERE rowid=$ii;
147 DELETE FROM t1 WHERE rowid=$ii;
148 }
149 }
150 } {}
151
152 foreach v {A B C D E F G H I J K L M N O P Q R S T U V W X Y Z} {
153 set L1 [execsql {SELECT rowid FROM t1 WHERE x LIKE '%'||$v||'%'}]
154 set L2 [execsql {SELECT rowid FROM ft($v)}]
155 do_test 4.4.$v { set L1 } $L2
156 }
157
158 do_execsql_test 4.5 {
159 INSERT INTO ft(ft) VALUES('optimize');
160 } {}
161
162 foreach v {A B C D E F G H I J K L M N O P Q R S T U V W X Y Z} {
163 set L1 [execsql {SELECT rowid FROM t1 WHERE x LIKE '%'||$v||'%'}]
164 set L2 [execsql {SELECT rowid FROM ft($v)}]
165 do_test 4.6.$v { set L1 } $L2
166 }
167
168 #execsql_pp { SELECT fts5_decode(id, block) FROM ft_data }
169
170 #-------------------------------------------------------------------------
171 reset_db
172 do_execsql_test 5.0 {
173 CREATE VIRTUAL TABLE ft USING fts5(x, content='', contentless_delete=1);
174 INSERT INTO ft(rowid, x) VALUES(1, 'one two three');
175 INSERT INTO ft(rowid, x) VALUES(2, 'one two four');
176 INSERT INTO ft(rowid, x) VALUES(3, 'one two five');
177 INSERT INTO ft(rowid, x) VALUES(4, 'one two seven');
178 INSERT INTO ft(rowid, x) VALUES(5, 'one two eight');
179 }
180
181 do_execsql_test 5.1 {
182 DELETE FROM ft WHERE rowid=2
183 }
184
185 do_execsql_test 5.2 {
186 SELECT rowid FROM ft
187 } {1 3 4 5}
188
189 do_catchsql_test 5.3 {
190 UPDATE ft SET x='four six' WHERE rowid=3
191 } {0 {}}
192
193 do_execsql_test 5.4 {
194 SELECT rowid FROM ft('one');
195 } {1 4 5}
196
197 do_execsql_test 5.5 {
198 REPLACE INTO ft(rowid, x) VALUES(3, 'four six');
199 SELECT rowid FROM ft('one');
200 } {1 4 5}
201
202 do_execsql_test 5.6 {
203 REPLACE INTO ft(rowid, x) VALUES(6, 'one two eleven');
204 SELECT rowid FROM ft('one');
205 } {1 4 5 6}
206
207 #-------------------------------------------------------------------------
208 reset_db
209 do_execsql_test 6.0 {
210 CREATE VIRTUAL TABLE ft USING fts5(x, content='', contentless_delete=1);
211 INSERT INTO ft(rowid, x) VALUES(1, 'one two three');
212 INSERT INTO ft(rowid, x) VALUES(2, 'one two four');
213 }
214
215 do_test 6.1 {
216 db eval { SELECT rowid FROM ft('one two') } {
217 if {$rowid==1} {
218 db eval { INSERT INTO ft(rowid, x) VALUES(3, 'one two four') }
219 }
220 }
221 } {}
222
223 #-------------------------------------------------------------------------
224 reset_db
225 do_execsql_test 7.0 {
226 CREATE VIRTUAL TABLE ft USING fts5(x, content='', contentless_delete=1);
227 }
228
229 set lRowid [list -450 0 1 2 42]
230
231 do_test 7.1 {
232 execsql BEGIN
233 foreach r $lRowid {
234 execsql { INSERT INTO ft(rowid, x) VALUES($r, 'one one one'); }
235 }
236 execsql COMMIT
237 } {}
238
239 do_test 7.2 {
240 execsql BEGIN
241 foreach r $lRowid {
242 execsql { REPLACE INTO ft(rowid, x) VALUES($r, 'two two two'); }
243 }
244 execsql COMMIT
245 } {}
246
247 do_execsql_test 7.3 { SELECT rowid FROM ft('one'); } {}
248 do_execsql_test 7.4 { SELECT rowid FROM ft('two'); } $lRowid
249
250 #-------------------------------------------------------------------------
251 reset_db
252 do_execsql_test 8.0 {
253 CREATE VIRTUAL TABLE ft USING fts5(x, content='', contentless_delete=1);
254 INSERT INTO ft VALUES('hello world');
255 INSERT INTO ft VALUES('one two three');
256 }
257
258 do_catchsql_test 8.1 {
259 INSERT INTO ft(ft, rowid, x) VALUES('delete', 1, 'hello world');
260 } {1 {'delete' may not be used with a contentless_delete=1 table}}
261
262 do_execsql_test 8.2 {
263 BEGIN;
264 INSERT INTO ft(rowid, x) VALUES(3, 'four four four');
265 DELETE FROM ft WHERE rowid=3;
266 COMMIT;
267 SELECT rowid FROM ft('four');
268 } {}
269
270 #-------------------------------------------------------------------------
271 reset_db
272 do_execsql_test 9.0 {
273 CREATE VIRTUAL TABLE ft USING fts5(x, content='', contentless_delete=0);
274 INSERT INTO ft VALUES('hello world');
275 INSERT INTO ft VALUES('one two three');
276 }
277
278 do_catchsql_test 9.1 {
279 INSERT INTO ft(ft, rowid, x) VALUES('delete', 1, 'hello world');
280 } {0 {}}
281
282 do_catchsql_test 9.2 {
283 CREATE VIRTUAL TABLE ft2 USING fts5(x, content='', contentless_delete=2);
284 } {1 {malformed contentless_delete=... directive}}
285
286 do_catchsql_test 9.3 {
287 CREATE VIRTUAL TABLE ft2 USING fts5(x, content='', contentless_delete=11);
288 } {1 {malformed contentless_delete=... directive}}
289
290 finish_test