123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413 |
- source [file join [file dirname [info script]] fts5_common.tcl]
- set testprefix fts5integrity
- ifcapable !fts5 {
- finish_test
- return
- }
- do_execsql_test 1.0 {
- CREATE VIRTUAL TABLE xx USING fts5(x);
- INSERT INTO xx VALUES('term');
- }
- do_execsql_test 1.1 {
- INSERT INTO xx(xx) VALUES('integrity-check');
- }
- do_execsql_test 2.0 {
- CREATE VIRTUAL TABLE yy USING fts5(x, prefix=1);
- INSERT INTO yy VALUES('term');
- }
- do_execsql_test 2.1 {
- INSERT INTO yy(yy) VALUES('integrity-check');
- }
- do_execsql_test 3.0 {
- CREATE VIRTUAL TABLE zz USING fts5(z);
- INSERT INTO zz(zz, rank) VALUES('pgsz', 32);
- INSERT INTO zz VALUES('b b b b b b b b b b b b b b');
- INSERT INTO zz SELECT z FROM zz;
- INSERT INTO zz SELECT z FROM zz;
- INSERT INTO zz SELECT z FROM zz;
- INSERT INTO zz SELECT z FROM zz;
- INSERT INTO zz SELECT z FROM zz;
- INSERT INTO zz SELECT z FROM zz;
- INSERT INTO zz(zz) VALUES('optimize');
- }
- do_execsql_test 3.1 { INSERT INTO zz(zz) VALUES('integrity-check'); }
- do_execsql_test 4.0 {
- CREATE VIRTUAL TABLE aa USING fts5(zz);
- INSERT INTO aa(zz) VALUES('a b c d e');
- INSERT INTO aa(zz) VALUES('a b c d');
- INSERT INTO aa(zz) VALUES('a b c');
- INSERT INTO aa(zz) VALUES('a b');
- INSERT INTO aa(zz) VALUES('a');
- SELECT length(sz) FROM aa_docsize;
- } {1 1 1 1 1}
- do_execsql_test 4.1 {
- INSERT INTO aa(aa) VALUES('integrity-check');
- }
- sqlite3_db_config db DEFENSIVE 0
- do_catchsql_test 4.2 {
- BEGIN;
- UPDATE aa_docsize SET sz = X'44' WHERE rowid = 3;
- INSERT INTO aa(aa) VALUES('integrity-check');
- } {1 {database disk image is malformed}}
- do_execsql_test 4.2.1 {
- PRAGMA integrity_check(aa);
- } {{malformed inverted index for FTS5 table main.aa}}
- do_catchsql_test 4.3 {
- ROLLBACK;
- BEGIN;
- UPDATE aa_data SET block = X'44' WHERE rowid = 1;
- INSERT INTO aa(aa) VALUES('integrity-check');
- } {1 {database disk image is malformed}}
- do_catchsql_test 4.4 {
- ROLLBACK;
- BEGIN;
- INSERT INTO aa_docsize VALUES(23, X'04');
- INSERT INTO aa(aa) VALUES('integrity-check');
- } {1 {database disk image is malformed}}
- do_catchsql_test 4.5 {
- ROLLBACK;
- BEGIN;
- INSERT INTO aa_docsize VALUES(23, X'00');
- INSERT INTO aa_content VALUES(23, '');
- INSERT INTO aa(aa) VALUES('integrity-check');
- } {1 {database disk image is malformed}}
- execsql { ROLLBACK }
- proc rnddoc {n} {
- set doc [list]
- for {set i 0} {$i<$n} {incr i} {
- lappend doc [format %.5d [expr int(rand()*10000)]]
- }
- return $doc
- }
- db func rnddoc rnddoc
- expr srand(0)
- do_execsql_test 5.0 {
- CREATE VIRTUAL TABLE gg USING fts5(a, prefix="1,2,3");
- INSERT INTO gg(gg, rank) VALUES('pgsz', 256);
- INSERT INTO gg VALUES(rnddoc(20));
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- INSERT INTO gg SELECT rnddoc(20) FROM gg;
- }
- do_execsql_test 5.1 {
- INSERT INTO gg(gg) VALUES('integrity-check');
- }
- do_execsql_test 5.2 {
- INSERT INTO gg(gg) VALUES('optimize');
- }
- do_execsql_test 5.3 {
- INSERT INTO gg(gg) VALUES('integrity-check');
- }
- unset -nocomplain res
- do_test 5.4.1 {
- set ok 0
- for {set i 0} {$i < 10000} {incr i} {
- set T [format %.5d $i]
- set res [db eval { SELECT rowid FROM gg($T) ORDER BY rowid ASC }]
- set res2 [db eval { SELECT rowid FROM gg($T) ORDER BY rowid DESC }]
- if {$res == [lsort -integer $res2]} { incr ok }
- }
- set ok
- } {10000}
- do_test 5.4.2 {
- set ok 0
- for {set i 0} {$i < 100} {incr i} {
- set T "[format %.3d $i]*"
- set res [db eval { SELECT rowid FROM gg($T) ORDER BY rowid ASC }]
- set res2 [db eval { SELECT rowid FROM gg($T) ORDER BY rowid DESC }]
- if {$res == [lsort -integer $res2]} { incr ok }
- }
- set ok
- } {100}
- foreach {tn pgsz} {
- 1 32
- 2 36
- 3 40
- 4 44
- 5 48
- } {
- do_execsql_test 6.$tn.1 {
- DROP TABLE IF EXISTS hh;
- CREATE VIRTUAL TABLE hh USING fts5(y);
- INSERT INTO hh(hh, rank) VALUES('pgsz', $pgsz);
- WITH s(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<999)
- INSERT INTO hh SELECT printf('%.3d%.3d%.3d %.3d%.3d%.3d',i,i,i,i+1,i+1,i+1)
- FROM s;
- WITH s(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<999)
- INSERT INTO hh SELECT printf('%.3d%.3d%.3d %.3d%.3d%.3d',i,i,i,i+1,i+1,i+1)
- FROM s;
- INSERT INTO hh(hh) VALUES('optimize');
- }
- do_test 6.$tn.2 {
- set ok 0
- for {set i 0} {$i < 1000} {incr i} {
- set T [format %.3d%.3d%.3d $i $i $i]
- set res [db eval { SELECT rowid FROM hh($T) ORDER BY rowid ASC }]
- set res2 [db eval { SELECT rowid FROM hh($T) ORDER BY rowid DESC }]
- if {$res == [lsort -integer $res2]} { incr ok }
- }
- set ok
- } {1000}
- }
- reset_db
- do_execsql_test 7.0 {
- PRAGMA encoding = 'UTF-16';
- CREATE VIRTUAL TABLE vt0 USING fts5(c0);
- INSERT INTO vt0 VALUES (x'46f0');
- SELECT quote(c0) FROM vt0;
- } {X'46F0'}
- do_execsql_test 7.1 {
- INSERT INTO vt0(vt0) VALUES('integrity-check');
- }
- do_execsql_test 7.2 {
- INSERT INTO vt0(vt0) VALUES('rebuild');
- }
- do_execsql_test 7.3 {
- INSERT INTO vt0(vt0) VALUES('integrity-check');
- }
- do_execsql_test 7.4 {
- UPDATE vt0 SET c0='';
- }
- do_execsql_test 7.5 {
- INSERT INTO vt0(vt0) VALUES('integrity-check');
- }
- reset_db
- do_execsql_test 8.0 {
- PRAGMA locking_mode = EXCLUSIVE;
- PRAGMA journal_mode = PERSIST;
- CREATE VIRTUAL TABLE vt0 USING fts5(c0);
- } {exclusive persist}
- do_execsql_test 8.1 {
- PRAGMA data_version
- } {1}
- do_execsql_test 8.2 {
- INSERT INTO vt0(vt0) VALUES('integrity-check');
- PRAGMA data_version;
- } {1}
- do_execsql_test 8.1 {
- INSERT INTO vt0(vt0, rank) VALUES('usermerge', 2);
- }
- reset_db
- do_execsql_test 9.0 {
- PRAGMA encoding = 'UTF16';
- CREATE VIRTUAL TABLE vt0 USING fts5(c0);
- }
- do_execsql_test 9.1.1 {
- SELECT quote(SUBSTR(x'37', 0));
- } {X'37'}
- do_execsql_test 9.1.2 {
- SELECT quote(x'37');
- } {X'37'}
- do_execsql_test 9.2 {
- INSERT INTO vt0 VALUES (SUBSTR(x'37', 0));
- -- INSERT INTO vt0 VALUES (x'37');
- }
- do_execsql_test 9.3 {
- INSERT INTO vt0(vt0) VALUES('integrity-check');
- }
- reset_db
- do_execsql_test 10.0 {
- CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b);
- CREATE VIRTUAL TABLE vt0 USING fts5(a, b, content=t1);
- INSERT INTO vt0(rowid, a, b) VALUES(1, 'abc', 'def');
- }
- do_catchsql_test 10.1 {
- INSERT INTO vt0(vt0) VALUES('integrity-check');
- } {0 {}}
- do_catchsql_test 10.2 {
- INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 0);
- } {0 {}}
- do_catchsql_test 10.3 {
- INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
- } {1 {database disk image is malformed}}
- do_catchsql_test 10.3 {
- INSERT INTO t1 VALUES(1, 'abc', 'def');
- INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
- } {0 {}}
- do_execsql_test 10.4 {
- CREATE VIRTUAL TABLE vt1 USING fts5(a, b, content=);
- INSERT INTO vt1(rowid, a, b) VALUES(1, 'abc', 'def');
- }
- do_catchsql_test 10.5.1 {
- INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 0);
- } {0 {}}
- do_catchsql_test 10.5.2 {
- INSERT INTO vt0(vt0, rank) VALUES('integrity-check', 1);
- } {0 {}}
- do_catchsql_test 10.5.3 {
- INSERT INTO vt0(vt0) VALUES('integrity-check');
- } {0 {}}
- reset_db
- proc slang {in} {return [string map {th d e eh} $in]}
- db function slang -deterministic -innocuous slang
- do_execsql_test 11.0 {
- CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT AS (slang(b)));
- INSERT INTO t1(b) VALUES('the quick fox jumps over the lazy brown dog');
- SELECT c FROM t1;
- } {{deh quick fox jumps ovehr deh lazy brown dog}}
- do_execsql_test 11.1 {
- CREATE VIRTUAL TABLE t2 USING fts5(content="t1", c);
- INSERT INTO t2(t2) VALUES('rebuild');
- SELECT rowid FROM t2 WHERE t2 MATCH 'deh';
- } {1}
- do_execsql_test 11.2 {
- PRAGMA integrity_check(t2);
- } {ok}
- db close
- sqlite3 db test.db
- do_execsql_test 11.3 {
- PRAGMA integrity_check(t2);
- } {ok}
- do_execsql_test 11.4 {
- DROP TABLE t1;
- PRAGMA integrity_check(t2);
- } {ok}
- reset_db
- do_execsql_test 12.1 {
- CREATE VIRTUAL TABLE x1 USING fts5(a, b);
- INSERT INTO x1 VALUES('one', 'two');
- INSERT INTO x1 VALUES('three', 'four');
- INSERT INTO x1 VALUES('five', 'six');
- }
- do_execsql_test 12.2 {
- PRAGMA integrity_check
- } {ok}
- db close
- sqlite3 db test.db -readonly 1
- explain_i {
- PRAGMA integrity_check
- }
- do_execsql_test 12.3 {
- PRAGMA integrity_check
- } {ok}
- reset_db
- do_execsql_test 13.1 {
- CREATE VIRTUAL TABLE t1 USING fts5(a, tokenize=ascii);
- INSERT INTO t1 VALUES('a b c'), ('d e f');
- PRAGMA integrity_check;
- } {ok}
- db close
- sqlite3 db test.db
- do_catchsql_test 13.2 {
- PRAGMA integrity_check;
- } {0 ok}
- do_execsql_test 13.3 {
- PRAGMA writable_schema = 1;
- UPDATE sqlite_schema SET sql = 'CREATE VIRTUAL TABLE t1 USING fts5(a, tokenize=blah)'
- WHERE name = 't1';
- }
- db close
- sqlite3 db test.db
- breakpoint
- do_catchsql_test 13.4 {
- PRAGMA integrity_check;
- } {1 {SQL logic error}}
- finish_test
|