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
15source [file join [file dirname [info script]] fts5_common.tcl]
16set testprefix fts5integrity
17
18# If SQLITE_ENABLE_FTS5 is defined, omit this file.
19ifcapable !fts5 {
20  finish_test
21  return
22}
23
24do_execsql_test 1.0 {
25  CREATE VIRTUAL TABLE xx USING fts5(x);
26  INSERT INTO xx VALUES('term');
27}
28do_execsql_test 1.1 {
29  INSERT INTO xx(xx) VALUES('integrity-check');
30}
31
32do_execsql_test 2.0 {
33  CREATE VIRTUAL TABLE yy USING fts5(x, prefix=1);
34  INSERT INTO yy VALUES('term');
35}
36do_execsql_test 2.1 {
37  INSERT INTO yy(yy) VALUES('integrity-check');
38}
39
40#--------------------------------------------------------------------
41#
42do_execsql_test 3.0 {
43  CREATE VIRTUAL TABLE zz USING fts5(z);
44  INSERT INTO zz(zz, rank) VALUES('pgsz', 32);
45  INSERT INTO zz VALUES('b b b b b b b b b b b b b b');
46  INSERT INTO zz SELECT z FROM zz;
47  INSERT INTO zz SELECT z FROM zz;
48  INSERT INTO zz SELECT z FROM zz;
49  INSERT INTO zz SELECT z FROM zz;
50  INSERT INTO zz SELECT z FROM zz;
51  INSERT INTO zz SELECT z FROM zz;
52  INSERT INTO zz(zz) VALUES('optimize');
53}
54
55do_execsql_test 3.1 { INSERT INTO zz(zz) VALUES('integrity-check'); }
56
57#--------------------------------------------------------------------
58# Mess around with a docsize record. And the averages record. Then
59# check that integrity-check picks it up.
60#
61do_execsql_test 4.0 {
62  CREATE VIRTUAL TABLE aa USING fts5(zz);
63  INSERT INTO aa(zz) VALUES('a b c d e');
64  INSERT INTO aa(zz) VALUES('a b c d');
65  INSERT INTO aa(zz) VALUES('a b c');
66  INSERT INTO aa(zz) VALUES('a b');
67  INSERT INTO aa(zz) VALUES('a');
68  SELECT length(sz) FROM aa_docsize;
69} {1 1 1 1 1}
70do_execsql_test 4.1 {
71  INSERT INTO aa(aa) VALUES('integrity-check');
72}
73
74sqlite3_db_config db DEFENSIVE 0
75do_catchsql_test 4.2 {
76  BEGIN;
77    UPDATE aa_docsize SET sz = X'44' WHERE rowid = 3;
78    INSERT INTO aa(aa) VALUES('integrity-check');
79} {1 {database disk image is malformed}}
80
81do_catchsql_test 4.3 {
82  ROLLBACK;
83  BEGIN;
84    UPDATE aa_data SET block = X'44' WHERE rowid = 1;
85    INSERT INTO aa(aa) VALUES('integrity-check');
86} {1 {database disk image is malformed}}
87
88do_catchsql_test 4.4 {
89  ROLLBACK;
90  BEGIN;
91    INSERT INTO aa_docsize VALUES(23, X'04');
92    INSERT INTO aa(aa) VALUES('integrity-check');
93} {1 {database disk image is malformed}}
94
95do_catchsql_test 4.5 {
96  ROLLBACK;
97  BEGIN;
98    INSERT INTO aa_docsize VALUES(23, X'00');
99    INSERT INTO aa_content VALUES(23, '');
100    INSERT INTO aa(aa) VALUES('integrity-check');
101} {1 {database disk image is malformed}}
102
103#db eval {SELECT rowid, fts5_decode(rowid, block) aS r FROM zz_data} {puts $r}
104#exit
105
106execsql { ROLLBACK }
107
108
109#-------------------------------------------------------------------------
110# Test that integrity-check works on a reasonably large db with many
111# different terms.
112
113# Document generator command.
114proc rnddoc {n} {
115  set doc [list]
116  for {set i 0} {$i<$n} {incr i} {
117    lappend doc [format %.5d [expr int(rand()*10000)]]
118  }
119  return $doc
120}
121db func rnddoc rnddoc
122
123expr srand(0)
124do_execsql_test 5.0 {
125  CREATE VIRTUAL TABLE gg USING fts5(a, prefix="1,2,3");
126  INSERT INTO gg(gg, rank) VALUES('pgsz', 256);
127  INSERT INTO gg VALUES(rnddoc(20));
128  INSERT INTO gg SELECT rnddoc(20) FROM gg;
129  INSERT INTO gg SELECT rnddoc(20) FROM gg;
130  INSERT INTO gg SELECT rnddoc(20) FROM gg;
131  INSERT INTO gg SELECT rnddoc(20) FROM gg;
132  INSERT INTO gg SELECT rnddoc(20) FROM gg;
133  INSERT INTO gg SELECT rnddoc(20) FROM gg;
134  INSERT INTO gg SELECT rnddoc(20) FROM gg;
135  INSERT INTO gg SELECT rnddoc(20) FROM gg;
136  INSERT INTO gg SELECT rnddoc(20) FROM gg;
137  INSERT INTO gg SELECT rnddoc(20) FROM gg;
138  INSERT INTO gg SELECT rnddoc(20) FROM gg;
139}
140
141do_execsql_test 5.1 {
142  INSERT INTO gg(gg) VALUES('integrity-check');
143}
144
145do_execsql_test 5.2 {
146  INSERT INTO gg(gg) VALUES('optimize');
147}
148
149do_execsql_test 5.3 {
150  INSERT INTO gg(gg) VALUES('integrity-check');
151}
152
153do_test 5.4.1 {
154  set ok 0
155  for {set i 0} {$i < 10000} {incr i} {
156    set T [format %.5d $i]
157    set res  [db eval { SELECT rowid FROM gg($T) ORDER BY rowid ASC  }]
158    set res2 [db eval { SELECT rowid FROM gg($T) ORDER BY rowid DESC }]
159    if {$res == [lsort -integer $res2]} { incr ok }
160  }
161  set ok
162} {10000}
163
164do_test 5.4.2 {
165  set ok 0
166  for {set i 0} {$i < 100} {incr i} {
167    set T "[format %.3d $i]*"
168    set res  [db eval { SELECT rowid FROM gg($T) ORDER BY rowid ASC  }]
169    set res2 [db eval { SELECT rowid FROM gg($T) ORDER BY rowid DESC }]
170    if {$res == [lsort -integer $res2]} { incr ok }
171  }
172  set ok
173} {100}
174
175#-------------------------------------------------------------------------
176# Similar to 5.*.
177#
178foreach {tn pgsz} {
179  1  32
180  2  36
181  3  40
182  4  44
183  5  48
184} {
185  do_execsql_test 6.$tn.1 {
186    DROP TABLE IF EXISTS hh;
187    CREATE VIRTUAL TABLE hh USING fts5(y);
188    INSERT INTO hh(hh, rank) VALUES('pgsz', $pgsz);
189
190    WITH s(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<999)
191     INSERT INTO hh SELECT printf('%.3d%.3d%.3d %.3d%.3d%.3d',i,i,i,i+1,i+1,i+1)
192     FROM s;
193
194    WITH s(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<999)
195     INSERT INTO hh SELECT printf('%.3d%.3d%.3d %.3d%.3d%.3d',i,i,i,i+1,i+1,i+1)
196     FROM s;
197
198    INSERT INTO hh(hh) VALUES('optimize');
199  }
200
201  do_test 6.$tn.2 {
202    set ok 0
203    for {set i 0} {$i < 1000} {incr i} {
204      set T [format %.3d%.3d%.3d $i $i $i]
205      set res  [db eval { SELECT rowid FROM hh($T) ORDER BY rowid ASC  }]
206      set res2 [db eval { SELECT rowid FROM hh($T) ORDER BY rowid DESC }]
207      if {$res == [lsort -integer $res2]} { incr ok }
208    }
209    set ok
210  } {1000}
211}
212
213#-------------------------------------------------------------------------
214#
215reset_db
216do_execsql_test 7.0 {
217  PRAGMA encoding = 'UTF-16';
218  CREATE VIRTUAL TABLE vt0 USING fts5(c0);
219  INSERT INTO vt0 VALUES (x'46f0');
220  SELECT quote(c0) FROM vt0;
221} {X'46F0'}
222do_execsql_test 7.1 {
223  INSERT INTO vt0(vt0) VALUES('integrity-check');
224}
225do_execsql_test 7.2 {
226  INSERT INTO vt0(vt0) VALUES('rebuild');
227}
228do_execsql_test 7.3 {
229  INSERT INTO vt0(vt0) VALUES('integrity-check');
230}
231do_execsql_test 7.4 {
232  UPDATE vt0 SET c0='';
233}
234do_execsql_test 7.5 {
235  INSERT INTO vt0(vt0) VALUES('integrity-check');
236}
237
238#-------------------------------------------------------------------------
239# Ticket 7a458c2a5f4
240#
241reset_db
242do_execsql_test 8.0 {
243  PRAGMA locking_mode = EXCLUSIVE;
244  PRAGMA journal_mode = PERSIST;
245  CREATE VIRTUAL TABLE vt0 USING fts5(c0);
246} {exclusive persist}
247do_execsql_test 8.1 {
248  PRAGMA data_version
249} {1}
250do_execsql_test 8.2 {
251  INSERT INTO vt0(vt0) VALUES('integrity-check');
252  PRAGMA data_version;
253} {1}
254do_execsql_test 8.1 {
255  INSERT INTO vt0(vt0, rank) VALUES('usermerge', 2);
256}
257
258#-------------------------------------------------------------------------
259# Ticket [771fe617]
260#
261reset_db
262do_execsql_test 9.0 {
263  PRAGMA encoding = 'UTF16';
264  CREATE VIRTUAL TABLE vt0 USING fts5(c0);
265}
266
267#explain_i { SELECT quote(SUBSTR(x'37', 0)); }
268#execsql { PRAGMA vdbe_trace = 1 }
269do_execsql_test 9.1.1 {
270  SELECT quote(SUBSTR(x'37', 0));
271} {X'37'}
272do_execsql_test 9.1.2 {
273  SELECT quote(x'37');
274} {X'37'}
275
276do_execsql_test 9.2 {
277  INSERT INTO vt0 VALUES (SUBSTR(x'37', 0));
278--  INSERT INTO vt0 VALUES (x'37');
279}
280do_execsql_test 9.3 {
281  INSERT INTO vt0(vt0) VALUES('integrity-check');
282}
283
284#-------------------------------------------------------------------------
285reset_db
286do_execsql_test 10.0 {
287  CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b);
288  CREATE VIRTUAL TABLE vt0 USING fts5(a, b, content=t1);
289  INSERT INTO vt0(rowid, a, b) VALUES(1, 'abc', 'def');
290}
291do_catchsql_test 10.1 {
292  INSERT INTO vt0(vt0) VALUES('integrity-check');
293} {0 {}}
294do_catchsql_test 10.2 {
295  INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 0);
296} {0 {}}
297do_catchsql_test 10.3 {
298  INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
299} {1 {database disk image is malformed}}
300do_catchsql_test 10.3 {
301  INSERT INTO t1 VALUES(1, 'abc', 'def');
302  INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
303} {0 {}}
304
305do_execsql_test 10.4 {
306  CREATE VIRTUAL TABLE vt1 USING fts5(a, b, content=);
307  INSERT INTO vt1(rowid, a, b) VALUES(1, 'abc', 'def');
308}
309
310do_catchsql_test 10.5.1 {
311  INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 0);
312} {0 {}}
313do_catchsql_test 10.5.2 {
314  INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
315} {0 {}}
316do_catchsql_test 10.5.3 {
317  INSERT INTO vt0(vt0) VALUES('integrity-check');
318} {0 {}}
319
320finish_test
321