Debian Codegraph @

ext/fts5/test/fts5secure.test

raw
1 # 2023 Feb 17
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
13 source [file join [file dirname [info script]] fts5_common.tcl]
14 ifcapable !fts5 { finish_test ; return }
15 set ::testprefix fts5secure
16
17 proc dump {tname} {
18 execsql_pp "SELECT * FROM ${tname}_idx"
19 execsql_pp "SELECT id, quote(block), fts5_decode(id,block) FROM ${tname}_data"
20 }
21
22
23 do_execsql_test 0.0 {
24 CREATE VIRTUAL TABLE t1 USING fts5(ab);
25 CREATE VIRTUAL TABLE v1 USING fts5vocab('t1', 'instance');
26 INSERT INTO t1(rowid, ab) VALUES
27 (0,'abc'), (1,'abc'), (2,'abc'), (3,'abc'), (4,'def');
28 }
29
30 do_execsql_test 0.1 {
31 INSERT INTO t1(t1, rank) VALUES('secure-delete', 1);
32 }
33
34 do_execsql_test 0.2 {
35 DELETE FROM t1 WHERE rowid=2;
36 }
37
38 do_execsql_test 0.3 {
39 SELECT count(*) FROM t1_data
40 } 3
41
42 do_execsql_test 0.4 {
43 INSERT INTO t1(t1) VALUES('integrity-check');
44 }
45
46 do_execsql_test 0.5 {
47 DELETE FROM t1 WHERE rowid=3;
48 }
49
50 do_execsql_test 0.6 {
51 INSERT INTO t1(t1) VALUES('integrity-check');
52 }
53
54 do_execsql_test 0.7 {
55 DELETE FROM t1 WHERE rowid=0;
56 }
57
58 do_execsql_test 0.8 {
59 INSERT INTO t1(t1) VALUES('integrity-check');
60 }
61
62 #----------------------------------
63
64 do_execsql_test 1.0 {
65 CREATE VIRTUAL TABLE t2 USING fts5(ab);
66 INSERT INTO t2(rowid, ab) VALUES (5, 'key'), (6, 'value');
67 INSERT INTO t2(t2, rank) VALUES('secure-delete', 1);
68 }
69
70 #execsql_pp { SELECT id, quote(block) FROM t1_data }
71 #execsql_pp { SELECT segid, quote(term), pgno FROM t1_idx }
72
73 do_execsql_test 1.1 {
74 DELETE FROM t2 WHERE rowid = 5;
75 }
76
77 do_execsql_test 1.2 {
78 INSERT INTO t2(t2) VALUES('integrity-check');
79 }
80
81 do_execsql_test 1.3 {
82 DELETE FROM t2 WHERE rowid = 6;
83 }
84
85 do_execsql_test 1.4 {
86 INSERT INTO t2(t2) VALUES('integrity-check');
87 }
88
89 do_execsql_test 1.5 {
90 SELECT * FROM t2('value');
91 SELECT * FROM t2('v*');
92 }
93
94 do_execsql_test 1.6 {
95 SELECT * FROM t2('value') ORDER BY rowid DESC;
96 SELECT * FROM t2('v*') ORDER BY rowid DESC;
97 }
98 execsql_pp {
99 SELECT id, quote(block) FROM t2_data;
100 }
101
102 #----------------------------------
103
104 do_execsql_test 2.0 {
105 CREATE VIRTUAL TABLE ft USING fts5(ab);
106 CREATE VIRTUAL TABLE vocab USING fts5vocab('ft', 'instance');
107 INSERT INTO ft(rowid, ab) VALUES
108 (1, 'one'),
109 (2, 'two'),
110 (3, 'three'),
111 (4, 'four'),
112 (5, 'one one'),
113 (6, 'one two'),
114 (7, 'one three'),
115 (8, 'one four'),
116 (9, 'two one'),
117 (10, 'two two'),
118 (11, 'two three'),
119 (12, 'two four'),
120 (13, 'three one'),
121 (14, 'three two'),
122 (15, 'three three'),
123 (16, 'three four');
124 }
125
126 do_execsql_test 2.1 {
127 SELECT count(*) FROM ft_data;
128 } {3}
129
130 do_execsql_test 2.2 {
131 INSERT INTO ft(ft, rank) VALUES('secure-delete', 1);
132 }
133
134 do_execsql_test 2.3 {
135 DELETE FROM ft WHERE rowid=9;
136 }
137
138 do_execsql_test 2.4 {
139 INSERT INTO ft(ft) VALUES('integrity-check');
140 }
141
142 do_execsql_test 2.5 {
143 DELETE FROM ft WHERE ab LIKE '%two%'
144 }
145
146 do_execsql_test 2.6 {
147 INSERT INTO ft(ft) VALUES('integrity-check');
148 }
149
150 do_execsql_test 2.7 {
151 SELECT count(*) FROM ft_data;
152 } {3}
153
154 #----------------------------------
155 reset_db
156
157 set ::vocab {
158 one two three four five six seven eight nine ten
159 eleven twelve thirteen fourteen fifteen sixteen
160 seventeen eighteen nineteen twenty
161 }
162 proc rnddoc {} {
163 set nVocab [llength $::vocab]
164 set ret [list]
165 for {set ii 0} {$ii < 8} {incr ii} {
166 lappend ret [lindex $::vocab [expr int(abs(rand()) * $nVocab)]]
167 }
168 set ret
169 }
170
171 proc contains {list val} {
172 expr {[lsearch $list $val]>=0}
173 }
174
175 foreach {tn pgsz} {
176 2 64
177 1 1000
178 } {
179 reset_db
180 db function rnddoc rnddoc
181 db function contains contains
182
183 expr srand(1)
184
185 do_execsql_test 3.$tn.0 {
186 CREATE VIRTUAL TABLE t1 USING fts5(x);
187 INSERT INTO t1(t1, rank) VALUES('pgsz', $pgsz);
188 WITH s(i) AS (
189 VALUES(1) UNION SELECT i+1 FROM s WHERE i<20
190 )
191 INSERT INTO t1 SELECT rnddoc() FROM s;
192 }
193
194 do_execsql_test 3.$tn.1 {
195 INSERT INTO t1(t1, rank) VALUES('secure-delete', 1);
196 }
197
198 foreach {rowid} {
199 6 16 3 4 9 14 13 7 20 15 19 10 11 2 5 18 17 1 12 8
200 } {
201
202 do_execsql_test 3.$tn.2.$rowid {
203 DELETE FROM t1 WHERE rowid=$rowid;
204 }
205 do_execsql_test 3.$tn.2.$rowid.ic {
206 INSERT INTO t1(t1) VALUES('integrity-check');
207 }
208
209 foreach v $::vocab {
210 do_execsql_test 3.$tn.2.$rowid.q.$v {
211 SELECT rowid FROM t1($v)
212 } [db eval {SELECT rowid FROM t1 WHERE contains(x, $v)}]
213
214 do_execsql_test 3.$tn.2.$rowid.q.$v.DESC {
215 SELECT rowid FROM t1($v) ORDER BY 1 DESC
216 } [db eval {SELECT rowid FROM t1 WHERE contains(x, $v) ORDER BY 1 DESC}]
217 }
218 }
219 }
220
221 do_execsql_test 3.3 {
222 INSERT INTO t1(x) VALUES('optimize');
223 INSERT INTO t1(t1) VALUES('optimize');
224 SELECT count(*) FROM t1_data;
225 } {3}
226
227 #----------------------------------
228 reset_db
229 do_execsql_test 4.0 {
230 CREATE VIRTUAL TABLE t1 USING fts5(x);
231 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
232 INSERT INTO t1(t1, rank) VALUES('secure-delete', 1);
233 }
234
235 set L1 [string repeat abcdefghij 10]
236 set L2 [string repeat 1234567890 10]
237
238 do_execsql_test 4.1 {
239 INSERT INTO t1 VALUES('aa' || $L1 || ' ' || $L2);
240 }
241 do_execsql_test 4.2 {
242 DELETE FROM t1 WHERE rowid=1
243 }
244 do_execsql_test 4.3 {
245 INSERT INTO t1(t1) VALUES('integrity-check');
246 }
247
248 #----------------------------------
249 reset_db
250 do_execsql_test 5.0 {
251 CREATE VIRTUAL TABLE t1 USING fts5(x);
252 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
253 INSERT INTO t1(t1, rank) VALUES('secure-delete', 1);
254 }
255
256 set doc "aa [string repeat {abc } 60]"
257
258 do_execsql_test 5.1 {
259 BEGIN;
260 INSERT INTO t1 VALUES($doc);
261 INSERT INTO t1 VALUES('aa abc');
262 COMMIT;
263 }
264
265 do_execsql_test 5.2 {
266 DELETE FROM t1 WHERE rowid = 1;
267 }
268
269 do_execsql_test 5.3 {
270 INSERT INTO t1(t1) VALUES('integrity-check');
271 }
272
273 do_execsql_test 5.4 { SELECT rowid FROM t1('abc'); } 2
274 do_execsql_test 5.5 { SELECT rowid FROM t1('aa'); } 2
275
276 #-------------------------------------------------------------------------
277 # Tests for the bug fixed by https://sqlite.org/src/info/4b60a1c3
278 #
279 reset_db
280 do_execsql_test 6.0 {
281 CREATE VIRTUAL TABLE fts USING fts5(content);
282 INSERT INTO fts(fts, rank) VALUES ('secure-delete', 1);
283 INSERT INTO fts(rowid, content) VALUES
284 (3407, 'profile profile profile profile profile profile profile profile pull pulling pulling really');
285 DELETE FROM fts WHERE rowid IS 3407;
286 INSERT INTO fts(fts) VALUES ('integrity-check');
287 }
288
289 foreach {tn detail} {
290 1 full
291 2 column
292 3 none
293 } {
294 do_execsql_test 6.1.$detail "
295 DROP TABLE IF EXISTS t1;
296 CREATE VIRTUAL TABLE t1 USING fts5(x, detail=$detail);
297 "
298
299 do_execsql_test 6.2.$detail {
300 INSERT INTO t1(t1, rank) VALUES('secure-delete', 1);
301 }
302
303 for {set ii 1} {$ii < 100} {incr ii} {
304 do_execsql_test 6.3.$detail.$ii.1 {
305 BEGIN;
306 INSERT INTO t1(rowid, x) VALUES(10, 'word1');
307 WITH s(i) AS (
308 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<CAST($ii AS integer)
309 )
310 INSERT INTO t1(x) SELECT 'word3' FROM s;
311 COMMIT;
312 INSERT INTO t1(t1) VALUES('optimize');
313 }
314
315 do_execsql_test 6.3.$detail.$ii.2 {
316 DELETE FROM t1 WHERE rowid=10;
317 INSERT INTO t1(t1) VALUES ('integrity-check');
318 }
319
320 do_execsql_test 6.3.$detail.$ii.3 {
321 DELETE FROM t1;
322 }
323
324 do_execsql_test 6.3.$detail.$ii.4 {
325 BEGIN;
326 INSERT INTO t1(rowid, x) VALUES(10, 'tokenA');
327 WITH s(i) AS (
328 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<CAST($ii AS integer)
329 )
330 INSERT INTO t1(x) SELECT group_concat('tokenB ') FROM s;
331 COMMIT;
332 INSERT INTO t1(t1) VALUES('optimize');
333 }
334
335 do_execsql_test 6.3.$detail.$ii.5 {
336 DELETE FROM t1 WHERE rowid=10;
337 INSERT INTO t1(t1) VALUES ('integrity-check');
338 }
339
340 do_execsql_test 6.3.$detail.$ii.6 {
341 DELETE FROM t1;
342 }
343 }
344 }
345
346
347 finish_test
348