AryaWu/sqlite

1# 2019 April 232#3# The author disclaims copyright to this source code.  In place of4# 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# TESTRUNNER: shell12#13# Test the shell tool ".ar" command.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix recover19 20ifcapable !vtab {21  finish_test; return22}23set CLI [test_find_cli]24 25proc compare_result {db1 db2 sql} {26  set r1 [$db1 eval $sql]27  set r2 [$db2 eval $sql]28  if {$r1 != $r2} {29  puts "r1: $r1"30  puts "r2: $r2"31    error "mismatch for $sql"32  }33  return ""34}35 36proc compare_dbs {db1 db2} {37  compare_result $db1 $db2 "SELECT sql FROM sqlite_master ORDER BY 1"38  foreach tbl [$db1 eval {SELECT name FROM sqlite_master WHERE type='table'}] {39    compare_result $db1 $db2 "SELECT * FROM $tbl"40  }41}42 43proc recover_with_opts {opts} {44  set cmd ".recover $opts"45  set fd [open [list |$::CLI -noinit test.db $cmd]]46  fconfigure $fd -translation binary47  set sql [read $fd]48  close $fd49 50  # Remove the ".dbconfig defensive off" line51  set sql [string map {".dbconfig defensive off" ""} $sql]52 53  forcedelete test.db254  sqlite3 db2 test.db255  execsql $sql db256  db2 close57}58 59proc do_recover_test {tn {tsql {}} {res {}}} {60  recover_with_opts ""61 62  sqlite3 db2 test.db263  if {$tsql==""} {64    uplevel [list do_test $tn [list compare_dbs db db2] {}]65  } else {66    uplevel [list do_execsql_test -db db2 $tn $tsql $res]67  }68  db2 close69}70 71set doc {72  hello73  world74}75do_execsql_test 1.1.1 {76  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);77  INSERT INTO t1 VALUES(1, 4, X'1234567800');78  INSERT INTO t1 VALUES(2, 'test', 8.1);79  INSERT INTO t1 VALUES(3, $doc, 8.4);80}81do_recover_test 1.1.282 83do_execsql_test 1.2.1 "84  DELETE FROM t1;85  INSERT INTO t1 VALUES(13, 'hello\r\nworld', 13);86"87do_recover_test 1.2.288 89do_execsql_test 1.3.1 "90  CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, b, c);91  INSERT INTO t2 VALUES(NULL, 1, 2);92  INSERT INTO t2 VALUES(NULL, 3, 4);93  INSERT INTO t2 VALUES(NULL, 5, 6);94  CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, b, c);95  INSERT INTO t3 VALUES(NULL, 1, 2);96  INSERT INTO t3 VALUES(NULL, 3, 4);97  INSERT INTO t3 VALUES(NULL, 5, 6);98  DELETE FROM t2;99"100do_recover_test 1.3.2101 102#-------------------------------------------------------------------------103reset_db104do_execsql_test 2.1.0 {105  PRAGMA auto_vacuum = 0;106  CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;107  INSERT INTO t1 VALUES(1, 2, 3);108  INSERT INTO t1 VALUES(4, 5, 6);109  INSERT INTO t1 VALUES(7, 8, 9);110}111 112do_recover_test 2.1.1113 114do_execsql_test 2.2.0 {115  PRAGMA writable_schema = 1;116  DELETE FROM sqlite_master WHERE name='t1';117}118do_recover_test 2.2.1 {119  SELECT name FROM sqlite_master120} {lost_and_found}121 122do_execsql_test 2.3.0 {123  CREATE TABLE lost_and_found(a, b, c);124}125do_recover_test 2.3.1 {126  SELECT name FROM sqlite_master127} {lost_and_found lost_and_found_0}128 129do_execsql_test 2.4.0 {130  CREATE TABLE lost_and_found_0(a, b, c);131}132do_recover_test 2.4.1 {133  SELECT name FROM sqlite_master;134  SELECT * FROM lost_and_found_1;135} {lost_and_found lost_and_found_0 lost_and_found_1136  2 2 3 {} 2 3 1137  2 2 3 {} 5 6 4138  2 2 3 {} 8 9 7139}140 141#-------------------------------------------------------------------------142reset_db143do_recover_test 3.0144 145#-------------------------------------------------------------------------146reset_db 147execsql { PRAGMA secure_delete = 0 }148execsql { PRAGMA auto_vacuum = 0 }149do_execsql_test 4.0 {150  CREATE TABLE t1(a, b, c);151  CREATE TABLE t2(d, e, f);152  CREATE TABLE t3(g, h, i);153 154  INSERT INTO t2 VALUES(1, 2, 3);155  INSERT INTO t2 VALUES('a', 'b', 'c');156 157  INSERT INTO t3 VALUES('one', 'two', 'three');158  DROP TABLE t1;159  DROP TABLE t2;160}161 162recover_with_opts ""163sqlite3 db2 test.db2164do_execsql_test -db db2 4.1.1 {165  SELECT name FROM sqlite_schema166} {t3 lost_and_found}167do_execsql_test -db db2 4.1.2 {168  SELECT id, c0, c1, c2 FROM lost_and_found169} {1 1 2 3    2 a b c}170db2 close171 172recover_with_opts -ignore-freelist173sqlite3 db2 test.db2174do_execsql_test -db db2 4.2.1 {175  SELECT name FROM sqlite_schema176} {t3}177do_execsql_test -db db2 4.2.2 {178  SELECT * FROM t3179} {one two three}180db2 close181 182finish_test183