1# 2018-04-28 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# Test cases for SQLITE_DBCONFIG_RESET_DATABASE 12# 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix resetdb 17 18do_not_use_codec 19 20ifcapable !vtab||!compound { 21 finish_test 22 return 23} 24 25# In the "inmemory_journal" permutation, each new connection executes 26# "PRAGMA journal_mode = memory". This fails with SQLITE_BUSY if attempted 27# on a wal mode database with existing connections. For this and a few 28# other reasons, this test is not run as part of "inmemory_journal". 29# 30# Permutation "journaltest" does not support wal mode. 31# 32if {[permutation]=="inmemory_journal" 33 || [permutation]=="journaltest" 34} { 35 finish_test 36 return 37} 38 39# Create a sample database 40do_execsql_test 100 { 41 PRAGMA auto_vacuum = 0; 42 PRAGMA page_size=4096; 43 CREATE TABLE t1(a,b); 44 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20) 45 INSERT INTO t1(a,b) SELECT x, randomblob(300) FROM c; 46 CREATE INDEX t1a ON t1(a); 47 CREATE INDEX t1b ON t1(b); 48 SELECT sum(a), sum(length(b)) FROM t1; 49 PRAGMA integrity_check; 50 PRAGMA journal_mode; 51 PRAGMA page_count; 52} {210 6000 ok delete 8} 53 54# Verify that the same content is seen from a separate database connection 55sqlite3 db2 test.db 56do_test 110 { 57 execsql { 58 SELECT sum(a), sum(length(b)) FROM t1; 59 PRAGMA integrity_check; 60 PRAGMA journal_mode; 61 PRAGMA page_count; 62 } db2 63} {210 6000 ok delete 8} 64 65do_test 200 { 66 # Thoroughly corrupt the database file by overwriting the first 67 # page with randomness. 68 catchsql { 69 UPDATE sqlite_dbpage SET data=randomblob(4096) WHERE pgno=1; 70 PRAGMA quick_check; 71 } 72} {1 {unsupported file format}} 73do_test 201 { 74 catchsql { 75 PRAGMA quick_check; 76 } db2 77} {1 {unsupported file format}} 78 79do_test 210 { 80 # Reset the database file using SQLITE_DBCONFIG_RESET_DATABASE 81 sqlite3_db_config db RESET_DB 1 82 db eval VACUUM 83 sqlite3_db_config db RESET_DB 0 84 85 # Verify that the reset took, even on the separate database connection 86 catchsql { 87 PRAGMA page_count; 88 PRAGMA page_size; 89 PRAGMA quick_check; 90 PRAGMA journal_mode; 91 } db2 92} {0 {1 4096 ok delete}} 93 94# Delete the old connections and database and start over again 95# with a different page size and in WAL mode. 96# 97db close 98db2 close 99forcedelete test.db 100sqlite3 db test.db 101do_execsql_test 300 { 102 PRAGMA auto_vacuum = 0; 103 PRAGMA page_size=8192; 104 PRAGMA journal_mode=WAL; 105 CREATE TABLE t1(a,b); 106 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20) 107 INSERT INTO t1(a,b) SELECT x, randomblob(1300) FROM c; 108 CREATE INDEX t1a ON t1(a); 109 CREATE INDEX t1b ON t1(b); 110 SELECT sum(a), sum(length(b)) FROM t1; 111 PRAGMA integrity_check; 112 PRAGMA journal_mode; 113 PRAGMA page_size; 114 PRAGMA page_count; 115} {wal 210 26000 ok wal 8192 12} 116sqlite3 db2 test.db 117do_test 310 { 118 execsql { 119 SELECT sum(a), sum(length(b)) FROM t1; 120 PRAGMA integrity_check; 121 PRAGMA journal_mode; 122 PRAGMA page_size; 123 PRAGMA page_count; 124 } db2 125} {210 26000 ok wal 8192 12} 126 127# Corrupt the database again 128do_catchsql_test 320 { 129 UPDATE sqlite_dbpage SET data=randomblob(8192) WHERE pgno=1; 130 PRAGMA quick_check 131} {1 {file is not a database}} 132 133do_test 330 { 134 catchsql { 135 PRAGMA quick_check 136 } db2 137} {1 {file is not a database}} 138 139db2 cache flush ;# Required by permutation "prepare". 140 141# Reset the database yet again. Verify that the page size and 142# journal mode are preserved. 143# 144do_test 400 { 145 sqlite3_db_config db RESET_DB 1 146 db eval VACUUM 147 sqlite3_db_config db RESET_DB 0 148 catchsql { 149 PRAGMA page_count; 150 PRAGMA page_size; 151 PRAGMA journal_mode; 152 PRAGMA quick_check; 153 } db2 154} {0 {1 8192 wal ok}} 155db2 close 156 157# Reset the database yet again. This time immediately after it is closed 158# and reopened. So that the VACUUM is the first statement run. 159# 160db close 161sqlite3 db test.db 162do_test 500 { 163 sqlite3_finalize [ 164 sqlite3_prepare db "SELECT 1 FROM sqlite_master LIMIT 1" -1 tail 165 ] 166 sqlite3_db_config db RESET_DB 1 167 db eval VACUUM 168 sqlite3_db_config db RESET_DB 0 169 sqlite3 db2 test.db 170 catchsql { 171 PRAGMA page_count; 172 PRAGMA page_size; 173 PRAGMA journal_mode; 174 PRAGMA quick_check; 175 } db2 176} {0 {1 8192 wal ok}} 177db2 close 178 179#------------------------------------------------------------------------- 180reset_db 181sqlite3 db2 test.db 182do_execsql_test 600 { 183 PRAGMA journal_mode = wal; 184 CREATE TABLE t1(a); 185 INSERT INTO t1 VALUES(1), (2), (3), (4); 186} {wal} 187 188do_execsql_test -db db2 610 { 189 SELECT * FROM t1 190} {1 2 3 4} 191 192do_test 620 { 193 set res [list] 194 db2 eval {SELECT a FROM t1} { 195 lappend res $a 196 if {$a==3} { 197 sqlite3_db_config db RESET_DB 1 198 db eval VACUUM 199 sqlite3_db_config db RESET_DB 0 200 } 201 } 202 203 set res 204} {1 2 3 4} 205 206do_execsql_test -db db2 630 { 207 SELECT * FROM sqlite_master 208} {} 209 210finish_test 211 212