summaryrefslogtreecommitdiffstats
path: root/ext/repair/test
diff options
context:
space:
mode:
Diffstat (limited to 'ext/repair/test')
-rw-r--r--ext/repair/test/README.md13
-rw-r--r--ext/repair/test/checkfreelist01.test92
-rw-r--r--ext/repair/test/checkindex01.test349
-rw-r--r--ext/repair/test/test.tcl67
4 files changed, 521 insertions, 0 deletions
diff --git a/ext/repair/test/README.md b/ext/repair/test/README.md
new file mode 100644
index 0000000..8cc954a
--- /dev/null
+++ b/ext/repair/test/README.md
@@ -0,0 +1,13 @@
+To run these tests, first build sqlite3_checker:
+
+
+> make sqlite3_checker
+
+
+Then run the "test.tcl" script using:
+
+
+> ./sqlite3_checker --test $path/test.tcl
+
+
+Optionally add the full pathnames of individual *.test modules
diff --git a/ext/repair/test/checkfreelist01.test b/ext/repair/test/checkfreelist01.test
new file mode 100644
index 0000000..7e2dd51
--- /dev/null
+++ b/ext/repair/test/checkfreelist01.test
@@ -0,0 +1,92 @@
+# 2017-10-11
+
+set testprefix checkfreelist
+
+do_execsql_test 1.0 {
+ PRAGMA page_size=1024;
+ CREATE TABLE t1(a, b);
+}
+
+do_execsql_test 1.2 { SELECT checkfreelist('main') } {ok}
+do_execsql_test 1.3 {
+ WITH s(i) AS (
+ SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000
+ )
+ INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s;
+ DELETE FROM t1 WHERE rowid%3;
+ PRAGMA freelist_count;
+} {6726}
+
+do_execsql_test 1.4 { SELECT checkfreelist('main') } {ok}
+do_execsql_test 1.5 {
+ WITH freelist_trunk(i, d, n) AS (
+ SELECT 1, NULL, sqlite_readint32(data, 32) FROM sqlite_dbpage WHERE pgno=1
+ UNION ALL
+ SELECT n, data, sqlite_readint32(data)
+ FROM freelist_trunk, sqlite_dbpage WHERE pgno=n
+ )
+ SELECT i FROM freelist_trunk WHERE i!=1;
+} {
+ 10009 9715 9343 8969 8595 8222 7847 7474 7102 6727 6354 5982 5608 5234
+ 4860 4487 4112 3740 3367 2992 2619 2247 1872 1499 1125 752 377 5
+}
+
+do_execsql_test 1.6 { SELECT checkfreelist('main') } {ok}
+
+proc set_int {blob idx newval} {
+ binary scan $blob I* ints
+ lset ints $idx $newval
+ binary format I* $ints
+}
+db func set_int set_int
+
+proc get_int {blob idx} {
+ binary scan $blob I* ints
+ lindex $ints $idx
+}
+db func get_int get_int
+
+do_execsql_test 1.7 {
+ BEGIN;
+ UPDATE sqlite_dbpage
+ SET data = set_int(data, 1, get_int(data, 1)-1)
+ WHERE pgno=4860;
+ SELECT checkfreelist('main');
+ ROLLBACK;
+} {{free-list count mismatch: actual=6725 header=6726}}
+
+do_execsql_test 1.8 {
+ BEGIN;
+ UPDATE sqlite_dbpage
+ SET data = set_int(data, 5, (SELECT * FROM pragma_page_count)+1)
+ WHERE pgno=4860;
+ SELECT checkfreelist('main');
+ ROLLBACK;
+} {{leaf page 10092 is out of range (child 3 of trunk page 4860)}}
+
+do_execsql_test 1.9 {
+ BEGIN;
+ UPDATE sqlite_dbpage
+ SET data = set_int(data, 5, 0)
+ WHERE pgno=4860;
+ SELECT checkfreelist('main');
+ ROLLBACK;
+} {{leaf page 0 is out of range (child 3 of trunk page 4860)}}
+
+do_execsql_test 1.10 {
+ BEGIN;
+ UPDATE sqlite_dbpage
+ SET data = set_int(data, get_int(data, 1)+1, 0)
+ WHERE pgno=5;
+ SELECT checkfreelist('main');
+ ROLLBACK;
+} {{leaf page 0 is out of range (child 247 of trunk page 5)}}
+
+do_execsql_test 1.11 {
+ BEGIN;
+ UPDATE sqlite_dbpage
+ SET data = set_int(data, 1, 249)
+ WHERE pgno=5;
+ SELECT checkfreelist('main');
+ ROLLBACK;
+} {{leaf count out of range (249) on trunk page 5}}
diff --git a/ext/repair/test/checkindex01.test b/ext/repair/test/checkindex01.test
new file mode 100644
index 0000000..97973ae
--- /dev/null
+++ b/ext/repair/test/checkindex01.test
@@ -0,0 +1,349 @@
+# 2017-10-11
+#
+set testprefix checkindex
+
+do_execsql_test 1.0 {
+ CREATE TABLE t1(a, b);
+ CREATE INDEX i1 ON t1(a);
+ INSERT INTO t1 VALUES('one', 2);
+ INSERT INTO t1 VALUES('two', 4);
+ INSERT INTO t1 VALUES('three', 6);
+ INSERT INTO t1 VALUES('four', 8);
+ INSERT INTO t1 VALUES('five', 10);
+
+ CREATE INDEX i2 ON t1(a DESC);
+} {}
+
+proc incr_index_check {idx nStep} {
+ set Q {
+ SELECT errmsg, current_key FROM incremental_index_check($idx, $after)
+ LIMIT $nStep
+ }
+
+ set res [list]
+ while {1} {
+ unset -nocomplain current_key
+ set res1 [db eval $Q]
+ if {[llength $res1]==0} break
+ set res [concat $res $res1]
+ set after [lindex $res end]
+ }
+
+ return $res
+}
+
+proc do_index_check_test {tn idx res} {
+ uplevel [list do_execsql_test $tn.1 "
+ SELECT errmsg, current_key FROM incremental_index_check('$idx');
+ " $res]
+
+ uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]]
+ uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]]
+ uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]]
+}
+
+
+do_execsql_test 1.2.1 {
+ SELECT rowid, errmsg IS NULL, current_key FROM incremental_index_check('i1');
+} {
+ 1 1 'five',5
+ 2 1 'four',4
+ 3 1 'one',1
+ 4 1 'three',3
+ 5 1 'two',2
+}
+do_execsql_test 1.2.2 {
+ SELECT errmsg IS NULL, current_key, index_name, after_key, scanner_sql
+ FROM incremental_index_check('i1') LIMIT 1;
+} {
+ 1
+ 'five',5
+ i1
+ {}
+ {SELECT (SELECT a IS i.i0 FROM 't1' AS t WHERE "rowid" COLLATE BINARY IS i.i1), quote(i0)||','||quote(i1) FROM (SELECT (a) AS i0, ("rowid" COLLATE BINARY) AS i1 FROM 't1' INDEXED BY 'i1' ORDER BY 1,2) AS i}
+}
+
+do_index_check_test 1.3 i1 {
+ {} 'five',5
+ {} 'four',4
+ {} 'one',1
+ {} 'three',3
+ {} 'two',2
+}
+
+do_index_check_test 1.4 i2 {
+ {} 'two',2
+ {} 'three',3
+ {} 'one',1
+ {} 'four',4
+ {} 'five',5
+}
+
+do_test 1.5 {
+ set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t1' }]
+ sqlite3_imposter db main $tblroot {CREATE TABLE xt1(a,b)}
+ db eval {
+ UPDATE xt1 SET a='six' WHERE rowid=3;
+ DELETE FROM xt1 WHERE rowid = 5;
+ }
+ sqlite3_imposter db main
+} {}
+
+do_index_check_test 1.6 i1 {
+ {row missing} 'five',5
+ {} 'four',4
+ {} 'one',1
+ {row data mismatch} 'three',3
+ {} 'two',2
+}
+
+do_index_check_test 1.7 i2 {
+ {} 'two',2
+ {row data mismatch} 'three',3
+ {} 'one',1
+ {} 'four',4
+ {row missing} 'five',5
+}
+
+#--------------------------------------------------------------------------
+do_execsql_test 2.0 {
+
+ CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c, d);
+
+ INSERT INTO t2 VALUES(1, NULL, 1, 1);
+ INSERT INTO t2 VALUES(2, 1, NULL, 1);
+ INSERT INTO t2 VALUES(3, 1, 1, NULL);
+
+ INSERT INTO t2 VALUES(4, 2, 2, 1);
+ INSERT INTO t2 VALUES(5, 2, 2, 2);
+ INSERT INTO t2 VALUES(6, 2, 2, 3);
+
+ INSERT INTO t2 VALUES(7, 2, 2, 1);
+ INSERT INTO t2 VALUES(8, 2, 2, 2);
+ INSERT INTO t2 VALUES(9, 2, 2, 3);
+
+ CREATE INDEX i3 ON t2(b, c, d);
+ CREATE INDEX i4 ON t2(b DESC, c DESC, d DESC);
+ CREATE INDEX i5 ON t2(d, c DESC, b);
+} {}
+
+do_index_check_test 2.1 i3 {
+ {} NULL,1,1,1
+ {} 1,NULL,1,2
+ {} 1,1,NULL,3
+ {} 2,2,1,4
+ {} 2,2,1,7
+ {} 2,2,2,5
+ {} 2,2,2,8
+ {} 2,2,3,6
+ {} 2,2,3,9
+}
+
+do_index_check_test 2.2 i4 {
+ {} 2,2,3,6
+ {} 2,2,3,9
+ {} 2,2,2,5
+ {} 2,2,2,8
+ {} 2,2,1,4
+ {} 2,2,1,7
+ {} 1,1,NULL,3
+ {} 1,NULL,1,2
+ {} NULL,1,1,1
+}
+
+do_index_check_test 2.3 i5 {
+ {} NULL,1,1,3
+ {} 1,2,2,4
+ {} 1,2,2,7
+ {} 1,1,NULL,1
+ {} 1,NULL,1,2
+ {} 2,2,2,5
+ {} 2,2,2,8
+ {} 3,2,2,6
+ {} 3,2,2,9
+}
+
+#--------------------------------------------------------------------------
+do_execsql_test 3.0 {
+
+ CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID;
+ CREATE INDEX t3wxy ON t3(w, x, y);
+ CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC);
+
+ INSERT INTO t3 VALUES(NULL, NULL, NULL, 1);
+ INSERT INTO t3 VALUES(NULL, NULL, NULL, 2);
+ INSERT INTO t3 VALUES(NULL, NULL, NULL, 3);
+
+ INSERT INTO t3 VALUES('a', NULL, NULL, 4);
+ INSERT INTO t3 VALUES('a', NULL, NULL, 5);
+ INSERT INTO t3 VALUES('a', NULL, NULL, 6);
+
+ INSERT INTO t3 VALUES('a', 'b', NULL, 7);
+ INSERT INTO t3 VALUES('a', 'b', NULL, 8);
+ INSERT INTO t3 VALUES('a', 'b', NULL, 9);
+
+} {}
+
+do_index_check_test 3.1 t3wxy {
+ {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3
+ {} 'a',NULL,NULL,4 {} 'a',NULL,NULL,5 {} 'a',NULL,NULL,6
+ {} 'a','b',NULL,7 {} 'a','b',NULL,8 {} 'a','b',NULL,9
+}
+do_index_check_test 3.2 t3wxy2 {
+ {} 'a','b',NULL,7 {} 'a','b',NULL,8 {} 'a','b',NULL,9
+ {} 'a',NULL,NULL,4 {} 'a',NULL,NULL,5 {} 'a',NULL,NULL,6
+ {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3
+}
+
+#--------------------------------------------------------------------------
+# Test with an index that uses non-default collation sequences.
+#
+do_execsql_test 4.0 {
+ CREATE TABLE t4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT);
+ INSERT INTO t4 VALUES(1, 'aaa', 'bbb');
+ INSERT INTO t4 VALUES(2, 'AAA', 'CCC');
+ INSERT INTO t4 VALUES(3, 'aab', 'ddd');
+ INSERT INTO t4 VALUES(4, 'AAB', 'EEE');
+
+ CREATE INDEX t4cc ON t4(c1 COLLATE nocase, c2 COLLATE nocase);
+}
+
+do_index_check_test 4.1 t4cc {
+ {} 'aaa','bbb',1
+ {} 'AAA','CCC',2
+ {} 'aab','ddd',3
+ {} 'AAB','EEE',4
+}
+
+do_test 4.2 {
+ set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t4' }]
+ sqlite3_imposter db main $tblroot \
+ {CREATE TABLE xt4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT)}
+
+ db eval {
+ UPDATE xt4 SET c1='hello' WHERE rowid=2;
+ DELETE FROM xt4 WHERE rowid = 3;
+ }
+ sqlite3_imposter db main
+} {}
+
+do_index_check_test 4.3 t4cc {
+ {} 'aaa','bbb',1
+ {row data mismatch} 'AAA','CCC',2
+ {row missing} 'aab','ddd',3
+ {} 'AAB','EEE',4
+}
+
+#--------------------------------------------------------------------------
+# Test an index on an expression.
+#
+do_execsql_test 5.0 {
+ CREATE TABLE t5(x INTEGER PRIMARY KEY, y TEXT, UNIQUE(y));
+ INSERT INTO t5 VALUES(1, '{"x":1, "y":1}');
+ INSERT INTO t5 VALUES(2, '{"x":2, "y":2}');
+ INSERT INTO t5 VALUES(3, '{"x":3, "y":3}');
+ INSERT INTO t5 VALUES(4, '{"w":4, "z":4}');
+ INSERT INTO t5 VALUES(5, '{"x":5, "y":5}');
+
+ CREATE INDEX t5x ON t5( json_extract(y, '$.x') );
+ CREATE INDEX t5y ON t5( json_extract(y, '$.y') DESC );
+}
+
+do_index_check_test 5.1.1 t5x {
+ {} NULL,4 {} 1,1 {} 2,2 {} 3,3 {} 5,5
+}
+
+do_index_check_test 5.1.2 t5y {
+ {} 5,5 {} 3,3 {} 2,2 {} 1,1 {} NULL,4
+}
+
+do_index_check_test 5.1.3 sqlite_autoindex_t5_1 {
+ {} {'{"w":4, "z":4}',4}
+ {} {'{"x":1, "y":1}',1}
+ {} {'{"x":2, "y":2}',2}
+ {} {'{"x":3, "y":3}',3}
+ {} {'{"x":5, "y":5}',5}
+}
+
+do_test 5.2 {
+ set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t5' }]
+ sqlite3_imposter db main $tblroot \
+ {CREATE TABLE xt5(a INTEGER PRIMARY KEY, c1 TEXT);}
+ db eval {
+ UPDATE xt5 SET c1='{"x":22, "y":11}' WHERE rowid=1;
+ DELETE FROM xt5 WHERE rowid = 4;
+ }
+ sqlite3_imposter db main
+} {}
+
+do_index_check_test 5.3.1 t5x {
+ {row missing} NULL,4
+ {row data mismatch} 1,1
+ {} 2,2
+ {} 3,3
+ {} 5,5
+}
+
+do_index_check_test 5.3.2 sqlite_autoindex_t5_1 {
+ {row missing} {'{"w":4, "z":4}',4}
+ {row data mismatch} {'{"x":1, "y":1}',1}
+ {} {'{"x":2, "y":2}',2}
+ {} {'{"x":3, "y":3}',3}
+ {} {'{"x":5, "y":5}',5}
+}
+
+#-------------------------------------------------------------------------
+#
+do_execsql_test 6.0 {
+ CREATE TABLE t6(x INTEGER PRIMARY KEY, y, z);
+ CREATE INDEX t6x1 ON t6(y, /* one,two,three */ z);
+ CREATE INDEX t6x2 ON t6(z, -- hello,world,
+ y);
+
+ CREATE INDEX t6x3 ON t6(z -- hello,world
+ , y);
+
+ INSERT INTO t6 VALUES(1, 2, 3);
+ INSERT INTO t6 VALUES(4, 5, 6);
+}
+
+do_index_check_test 6.1 t6x1 {
+ {} 2,3,1
+ {} 5,6,4
+}
+do_index_check_test 6.2 t6x2 {
+ {} 3,2,1
+ {} 6,5,4
+}
+do_index_check_test 6.2 t6x3 {
+ {} 3,2,1
+ {} 6,5,4
+}
+
+#-------------------------------------------------------------------------
+#
+do_execsql_test 7.0 {
+ CREATE TABLE t7(x INTEGER PRIMARY KEY, y, z);
+ INSERT INTO t7 VALUES(1, 1, 1);
+ INSERT INTO t7 VALUES(2, 2, 0);
+ INSERT INTO t7 VALUES(3, 3, 1);
+ INSERT INTO t7 VALUES(4, 4, 0);
+
+ CREATE INDEX t7i1 ON t7(y) WHERE z=1;
+ CREATE INDEX t7i2 ON t7(y) /* hello,world */ WHERE z=1;
+ CREATE INDEX t7i3 ON t7(y) WHERE -- yep
+ z=1;
+ CREATE INDEX t7i4 ON t7(y) WHERE z=1 -- yep;
+}
+do_index_check_test 7.1 t7i1 {
+ {} 1,1 {} 3,3
+}
+do_index_check_test 7.2 t7i2 {
+ {} 1,1 {} 3,3
+}
+do_index_check_test 7.3 t7i3 {
+ {} 1,1 {} 3,3
+}
+do_index_check_test 7.4 t7i4 {
+ {} 1,1 {} 3,3
+}
diff --git a/ext/repair/test/test.tcl b/ext/repair/test/test.tcl
new file mode 100644
index 0000000..c073bb7
--- /dev/null
+++ b/ext/repair/test/test.tcl
@@ -0,0 +1,67 @@
+# Run this script using
+#
+# sqlite3_checker --test $thisscript $testscripts
+#
+# The $testscripts argument is optional. If omitted, all *.test files
+# in the same directory as $thisscript are run.
+#
+set NTEST 0
+set NERR 0
+
+
+# Invoke the do_test procedure to run a single test
+#
+# The $expected parameter is the expected result. The result is the return
+# value from the last TCL command in $cmd.
+#
+# Normally, $expected must match exactly. But if $expected is of the form
+# "/regexp/" then regular expression matching is used. If $expected is
+# "~/regexp/" then the regular expression must NOT match. If $expected is
+# of the form "#/value-list/" then each term in value-list must be numeric
+# and must approximately match the corresponding numeric term in $result.
+# Values must match within 10%. Or if the $expected term is A..B then the
+# $result term must be in between A and B.
+#
+proc do_test {name cmd expected} {
+ if {[info exists ::testprefix]} {
+ set name "$::testprefix$name"
+ }
+
+ incr ::NTEST
+ puts -nonewline $name...
+ flush stdout
+
+ if {[catch {uplevel #0 "$cmd;\n"} result]} {
+ puts -nonewline $name...
+ puts "\nError: $result"
+ incr ::NERR
+ } else {
+ set ok [expr {[string compare $result $expected]==0}]
+ if {!$ok} {
+ puts "\n! $name expected: \[$expected\]\n! $name got: \[$result\]"
+ incr ::NERR
+ } else {
+ puts " Ok"
+ }
+ }
+ flush stdout
+}
+
+#
+# do_execsql_test TESTNAME SQL RES
+#
+proc do_execsql_test {testname sql {result {}}} {
+ uplevel [list do_test $testname [list db eval $sql] [list {*}$result]]
+}
+
+if {[llength $argv]==0} {
+ set dir [file dirname $argv0]
+ set argv [glob -nocomplain $dir/*.test]
+}
+foreach testfile $argv {
+ file delete -force test.db
+ sqlite3 db test.db
+ source $testfile
+ catch {db close}
+}
+puts "$NERR errors out of $NTEST tests"
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-26 12:24:49 +0000