Debian Codegraph @

ext/fts5/test/fts5integrity.test

raw
1 # 2015 Jan 13
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 focused on the integrity-check procedure.
13 #
14
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5integrity
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23
24 do_execsql_test 1.0 {
25 CREATE VIRTUAL TABLE xx USING fts5(x);
26 INSERT INTO xx VALUES('term');
27 }
28 do_execsql_test 1.1 {
29 INSERT INTO xx(xx) VALUES('integrity-check');
30 }
31
32 do_execsql_test 2.0 {
33 CREATE VIRTUAL TABLE yy USING fts5(x, prefix=1);
34 INSERT INTO yy VALUES('term');
35 }
36 do_execsql_test 2.1 {
37 INSERT INTO yy(yy) VALUES('integrity-check');
38 }
39
40 db close
41 sqlite3 db test.db
42 do_execsql_test 2.1 {
43 INSERT INTO yy(yy) VALUES('integrity-check');
44 }
45
46 #--------------------------------------------------------------------
47 #
48 do_execsql_test 3.0 {
49 CREATE VIRTUAL TABLE zz USING fts5(z);
50 INSERT INTO zz(zz, rank) VALUES('pgsz', 32);
51 INSERT INTO zz VALUES('b b b b b b b b b b b b b b');
52 INSERT INTO zz SELECT z FROM zz;
53 INSERT INTO zz SELECT z FROM zz;
54 INSERT INTO zz SELECT z FROM zz;
55 INSERT INTO zz SELECT z FROM zz;
56 INSERT INTO zz SELECT z FROM zz;
57 INSERT INTO zz SELECT z FROM zz;
58 INSERT INTO zz(zz) VALUES('optimize');
59 }
60
61 do_execsql_test 3.1 { INSERT INTO zz(zz) VALUES('integrity-check'); }
62
63 #--------------------------------------------------------------------
64 # Mess around with a docsize record. And the averages record. Then
65 # check that integrity-check picks it up.
66 #
67 do_execsql_test 4.0 {
68 CREATE VIRTUAL TABLE aa USING fts5(zz);
69 INSERT INTO aa(zz) VALUES('a b c d e');
70 INSERT INTO aa(zz) VALUES('a b c d');
71 INSERT INTO aa(zz) VALUES('a b c');
72 INSERT INTO aa(zz) VALUES('a b');
73 INSERT INTO aa(zz) VALUES('a');
74 SELECT length(sz) FROM aa_docsize;
75 } {1 1 1 1 1}
76 do_execsql_test 4.1 {
77 INSERT INTO aa(aa) VALUES('integrity-check');
78 }
79
80 sqlite3_db_config db DEFENSIVE 0
81 do_catchsql_test 4.2 {
82 BEGIN;
83 UPDATE aa_docsize SET sz = X'44' WHERE rowid = 3;
84 INSERT INTO aa(aa) VALUES('integrity-check');
85 } {1 {database disk image is malformed}}
86 do_execsql_test 4.2.1 {
87 PRAGMA integrity_check(aa);
88 } {{malformed inverted index for FTS5 table main.aa}}
89
90 do_catchsql_test 4.3 {
91 ROLLBACK;
92 BEGIN;
93 UPDATE aa_data SET block = X'44' WHERE rowid = 1;
94 INSERT INTO aa(aa) VALUES('integrity-check');
95 } {1 {database disk image is malformed}}
96
97 do_catchsql_test 4.4 {
98 ROLLBACK;
99 BEGIN;
100 INSERT INTO aa_docsize VALUES(23, X'04');
101 INSERT INTO aa(aa) VALUES('integrity-check');
102 } {1 {database disk image is malformed}}
103
104 do_catchsql_test 4.5 {
105 ROLLBACK;
106 BEGIN;
107 INSERT INTO aa_docsize VALUES(23, X'00');
108 INSERT INTO aa_content VALUES(23, '');
109 INSERT INTO aa(aa) VALUES('integrity-check');
110 } {1 {database disk image is malformed}}
111
112 #db eval {SELECT rowid, fts5_decode(rowid, block) aS r FROM zz_data} {puts $r}
113 #exit
114
115 execsql { ROLLBACK }
116
117
118 #-------------------------------------------------------------------------
119 # Test that integrity-check works on a reasonably large db with many
120 # different terms.
121
122 # Document generator command.
123 proc rnddoc {n} {
124 set doc [list]
125 for {set i 0} {$i<$n} {incr i} {
126 lappend doc [format %.5d [expr int(rand()*10000)]]
127 }
128 return $doc
129 }
130 db func rnddoc rnddoc
131
132 expr srand(0)
133 do_execsql_test 5.0 {
134 CREATE VIRTUAL TABLE gg USING fts5(a, prefix="1,2,3");
135 INSERT INTO gg(gg, rank) VALUES('pgsz', 256);
136 INSERT INTO gg VALUES(rnddoc(20));
137 INSERT INTO gg SELECT rnddoc(20) FROM gg;
138 INSERT INTO gg SELECT rnddoc(20) FROM gg;
139 INSERT INTO gg SELECT rnddoc(20) FROM gg;
140 INSERT INTO gg SELECT rnddoc(20) FROM gg;
141 INSERT INTO gg SELECT rnddoc(20) FROM gg;
142 INSERT INTO gg SELECT rnddoc(20) FROM gg;
143 INSERT INTO gg SELECT rnddoc(20) FROM gg;
144 INSERT INTO gg SELECT rnddoc(20) FROM gg;
145 INSERT INTO gg SELECT rnddoc(20) FROM gg;
146 INSERT INTO gg SELECT rnddoc(20) FROM gg;
147 INSERT INTO gg SELECT rnddoc(20) FROM gg;
148 }
149
150 do_execsql_test 5.1 {
151 INSERT INTO gg(gg) VALUES('integrity-check');
152 }
153
154 do_execsql_test 5.2 {
155 INSERT INTO gg(gg) VALUES('optimize');
156 }
157
158 do_execsql_test 5.3 {
159 INSERT INTO gg(gg) VALUES('integrity-check');
160 }
161
162 unset -nocomplain res
163 do_test 5.4.1 {
164 set ok 0
165 for {set i 0} {$i < 10000} {incr i} {
166 set T [format %.5d $i]
167 set res [db eval { SELECT rowid FROM gg($T) ORDER BY rowid ASC }]
168 set res2 [db eval { SELECT rowid FROM gg($T) ORDER BY rowid DESC }]
169 if {$res == [lsort -integer $res2]} { incr ok }
170 }
171 set ok
172 } {10000}
173
174 do_test 5.4.2 {
175 set ok 0
176 for {set i 0} {$i < 100} {incr i} {
177 set T "[format %.3d $i]*"
178 set res [db eval { SELECT rowid FROM gg($T) ORDER BY rowid ASC }]
179 set res2 [db eval { SELECT rowid FROM gg($T) ORDER BY rowid DESC }]
180 if {$res == [lsort -integer $res2]} { incr ok }
181 }
182 set ok
183 } {100}
184
185 #-------------------------------------------------------------------------
186 # Similar to 5.*.
187 #
188 foreach {tn pgsz} {
189 1 32
190 2 36
191 3 40
192 4 44
193 5 48
194 } {
195 do_execsql_test 6.$tn.1 {
196 DROP TABLE IF EXISTS hh;
197 CREATE VIRTUAL TABLE hh USING fts5(y);
198 INSERT INTO hh(hh, rank) VALUES('pgsz', $pgsz);
199
200 WITH s(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<999)
201 INSERT INTO hh SELECT printf('%.3d%.3d%.3d %.3d%.3d%.3d',i,i,i,i+1,i+1,i+1)
202 FROM s;
203
204 WITH s(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<999)
205 INSERT INTO hh SELECT printf('%.3d%.3d%.3d %.3d%.3d%.3d',i,i,i,i+1,i+1,i+1)
206 FROM s;
207
208 INSERT INTO hh(hh) VALUES('optimize');
209 }
210
211 do_test 6.$tn.2 {
212 set ok 0
213 for {set i 0} {$i < 1000} {incr i} {
214 set T [format %.3d%.3d%.3d $i $i $i]
215 set res [db eval { SELECT rowid FROM hh($T) ORDER BY rowid ASC }]
216 set res2 [db eval { SELECT rowid FROM hh($T) ORDER BY rowid DESC }]
217 if {$res == [lsort -integer $res2]} { incr ok }
218 }
219 set ok
220 } {1000}
221 }
222
223 #-------------------------------------------------------------------------
224 #
225 reset_db
226 do_execsql_test 7.0 {
227 PRAGMA encoding = 'UTF-16';
228 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
229 INSERT INTO vt0 VALUES (x'46f0');
230 SELECT quote(c0) FROM vt0;
231 } {X'46F0'}
232 do_execsql_test 7.1 {
233 INSERT INTO vt0(vt0) VALUES('integrity-check');
234 }
235 do_execsql_test 7.2 {
236 INSERT INTO vt0(vt0) VALUES('rebuild');
237 }
238 do_execsql_test 7.3 {
239 INSERT INTO vt0(vt0) VALUES('integrity-check');
240 }
241 do_execsql_test 7.4 {
242 UPDATE vt0 SET c0='';
243 }
244 do_execsql_test 7.5 {
245 INSERT INTO vt0(vt0) VALUES('integrity-check');
246 }
247
248 #-------------------------------------------------------------------------
249 # Ticket 7a458c2a5f4
250 #
251 reset_db
252 do_execsql_test 8.0 {
253 PRAGMA locking_mode = EXCLUSIVE;
254 PRAGMA journal_mode = PERSIST;
255 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
256 } {exclusive persist}
257 do_execsql_test 8.1 {
258 PRAGMA data_version
259 } {1}
260 do_execsql_test 8.2 {
261 INSERT INTO vt0(vt0) VALUES('integrity-check');
262 PRAGMA data_version;
263 } {1}
264 do_execsql_test 8.1 {
265 INSERT INTO vt0(vt0, rank) VALUES('usermerge', 2);
266 }
267
268 #-------------------------------------------------------------------------
269 # Ticket [771fe617]
270 #
271 reset_db
272 do_execsql_test 9.0 {
273 PRAGMA encoding = 'UTF16';
274 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
275 }
276
277 #explain_i { SELECT quote(SUBSTR(x'37', 0)); }
278 #execsql { PRAGMA vdbe_trace = 1 }
279 do_execsql_test 9.1.1 {
280 SELECT quote(SUBSTR(x'37', 0));
281 } {X'37'}
282 do_execsql_test 9.1.2 {
283 SELECT quote(x'37');
284 } {X'37'}
285
286 do_execsql_test 9.2 {
287 INSERT INTO vt0 VALUES (SUBSTR(x'37', 0));
288 -- INSERT INTO vt0 VALUES (x'37');
289 }
290 do_execsql_test 9.3 {
291 INSERT INTO vt0(vt0) VALUES('integrity-check');
292 }
293
294 #-------------------------------------------------------------------------
295 reset_db
296 do_execsql_test 10.0 {
297 CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b);
298 CREATE VIRTUAL TABLE vt0 USING fts5(a, b, content=t1);
299 INSERT INTO vt0(rowid, a, b) VALUES(1, 'abc', 'def');
300 }
301 do_catchsql_test 10.1 {
302 INSERT INTO vt0(vt0) VALUES('integrity-check');
303 } {0 {}}
304 do_catchsql_test 10.2 {
305 INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 0);
306 } {0 {}}
307 do_catchsql_test 10.3 {
308 INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
309 } {1 {database disk image is malformed}}
310 do_catchsql_test 10.3 {
311 INSERT INTO t1 VALUES(1, 'abc', 'def');
312 INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
313 } {0 {}}
314
315 do_execsql_test 10.4 {
316 CREATE VIRTUAL TABLE vt1 USING fts5(a, b, content=);
317 INSERT INTO vt1(rowid, a, b) VALUES(1, 'abc', 'def');
318 }
319
320 do_catchsql_test 10.5.1 {
321 INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 0);
322 } {0 {}}
323 do_catchsql_test 10.5.2 {
324 INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
325 } {0 {}}
326 do_catchsql_test 10.5.3 {
327 INSERT INTO vt0(vt0) VALUES('integrity-check');
328 } {0 {}}
329
330 reset_db
331 proc slang {in} {return [string map {th d e eh} $in]}
332 db function slang -deterministic -innocuous slang
333 do_execsql_test 11.0 {
334 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT AS (slang(b)));
335 INSERT INTO t1(b) VALUES('the quick fox jumps over the lazy brown dog');
336 SELECT c FROM t1;
337 } {{deh quick fox jumps ovehr deh lazy brown dog}}
338
339 do_execsql_test 11.1 {
340 CREATE VIRTUAL TABLE t2 USING fts5(content="t1", c);
341 INSERT INTO t2(t2) VALUES('rebuild');
342 SELECT rowid FROM t2 WHERE t2 MATCH 'deh';
343 } {1}
344
345 do_execsql_test 11.2 {
346 PRAGMA integrity_check(t2);
347 } {ok}
348 db close
349 sqlite3 db test.db
350
351 # FIX ME?
352 #
353 # FTS5 integrity-check does not care if the content table is unreadable or
354 # does not exist. It only looks for internal inconsistencies in the
355 # inverted index.
356 #
357 do_execsql_test 11.3 {
358 PRAGMA integrity_check(t2);
359 } {ok}
360 do_execsql_test 11.4 {
361 DROP TABLE t1;
362 PRAGMA integrity_check(t2);
363 } {ok}
364
365 #-------------------------------------------------------------------
366 reset_db
367
368 do_execsql_test 12.1 {
369 CREATE VIRTUAL TABLE x1 USING fts5(a, b);
370 INSERT INTO x1 VALUES('one', 'two');
371 INSERT INTO x1 VALUES('three', 'four');
372 INSERT INTO x1 VALUES('five', 'six');
373 }
374
375 do_execsql_test 12.2 {
376 PRAGMA integrity_check
377 } {ok}
378
379 db close
380 sqlite3 db test.db -readonly 1
381
382 do_execsql_test 12.3 {
383 PRAGMA integrity_check
384 } {ok}
385
386
387 #-------------------------------------------------------------------
388 reset_db
389 do_execsql_test 13.1 {
390 CREATE VIRTUAL TABLE t1 USING fts5(a, tokenize=ascii);
391 INSERT INTO t1 VALUES('a b c'), ('d e f');
392 PRAGMA integrity_check;
393 } {ok}
394
395 db close
396 sqlite3 db test.db
397 do_catchsql_test 13.2 {
398 PRAGMA integrity_check;
399 } {0 ok}
400
401 do_execsql_test 13.3 {
402 PRAGMA writable_schema = 1;
403 UPDATE sqlite_schema SET sql = 'CREATE VIRTUAL TABLE t1 USING fts5(a, tokenize=blah)'
404 WHERE name = 't1';
405 }
406
407 db close
408 sqlite3 db test.db
409 breakpoint
410 do_catchsql_test 13.4 {
411 PRAGMA integrity_check;
412 } {1 {SQL logic error}}
413
414
415 finish_test