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 sqlite3_db_config db DEFENSIVE 0 69 catchsql { 70 UPDATE sqlite_dbpage SET data=randomblob(4096) WHERE pgno=1; 71 PRAGMA quick_check; 72 } 73} {1 {unsupported file format}} 74do_test 201 { 75 catchsql { 76 PRAGMA quick_check; 77 } db2 78} {1 {unsupported file format}} 79 80do_test 210 { 81 # Reset the database file using SQLITE_DBCONFIG_RESET_DATABASE 82 sqlite3_db_config db RESET_DB 1 83 db eval VACUUM 84 sqlite3_db_config db RESET_DB 0 85 86 # Verify that the reset took, even on the separate database connection 87 catchsql { 88 PRAGMA page_count; 89 PRAGMA page_size; 90 PRAGMA quick_check; 91 PRAGMA journal_mode; 92 } db2 93} {0 {1 4096 ok delete}} 94 95# Delete the old connections and database and start over again 96# with a different page size and in WAL mode. 97# 98db close 99db2 close 100forcedelete test.db 101sqlite3 db test.db 102do_execsql_test 300 { 103 PRAGMA auto_vacuum = 0; 104 PRAGMA page_size=8192; 105 PRAGMA journal_mode=WAL; 106 CREATE TABLE t1(a,b); 107 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20) 108 INSERT INTO t1(a,b) SELECT x, randomblob(1300) FROM c; 109 CREATE INDEX t1a ON t1(a); 110 CREATE INDEX t1b ON t1(b); 111 SELECT sum(a), sum(length(b)) FROM t1; 112 PRAGMA integrity_check; 113 PRAGMA journal_mode; 114 PRAGMA page_size; 115 PRAGMA page_count; 116} {wal 210 26000 ok wal 8192 12} 117sqlite3 db2 test.db 118do_test 310 { 119 execsql { 120 SELECT sum(a), sum(length(b)) FROM t1; 121 PRAGMA integrity_check; 122 PRAGMA journal_mode; 123 PRAGMA page_size; 124 PRAGMA page_count; 125 } db2 126} {210 26000 ok wal 8192 12} 127 128# Corrupt the database again 129sqlite3_db_config db DEFENSIVE 0 130do_catchsql_test 320 { 131 UPDATE sqlite_dbpage SET data=randomblob(8192) WHERE pgno=1; 132 PRAGMA quick_check 133} {1 {file is not a database}} 134 135do_test 330 { 136 catchsql { 137 PRAGMA quick_check 138 } db2 139} {1 {file is not a database}} 140 141db2 cache flush ;# Required by permutation "prepare". 142 143# Reset the database yet again. Verify that the page size and 144# journal mode are preserved. 145# 146do_test 400 { 147 sqlite3_db_config db RESET_DB 1 148 db eval VACUUM 149 sqlite3_db_config db RESET_DB 0 150 catchsql { 151 PRAGMA page_count; 152 PRAGMA page_size; 153 PRAGMA journal_mode; 154 PRAGMA quick_check; 155 } db2 156} {0 {1 8192 wal ok}} 157db2 close 158 159# Reset the database yet again. This time immediately after it is closed 160# and reopened. So that the VACUUM is the first statement run. 161# 162db close 163sqlite3 db test.db 164do_test 500 { 165 sqlite3_finalize [ 166 sqlite3_prepare db "SELECT 1 FROM sqlite_master LIMIT 1" -1 tail 167 ] 168 sqlite3_db_config db RESET_DB 1 169 db eval VACUUM 170 sqlite3_db_config db RESET_DB 0 171 sqlite3 db2 test.db 172 catchsql { 173 PRAGMA page_count; 174 PRAGMA page_size; 175 PRAGMA journal_mode; 176 PRAGMA quick_check; 177 } db2 178} {0 {1 8192 wal ok}} 179db2 close 180 181#------------------------------------------------------------------------- 182reset_db 183sqlite3 db2 test.db 184do_execsql_test 600 { 185 PRAGMA journal_mode = wal; 186 CREATE TABLE t1(a); 187 INSERT INTO t1 VALUES(1), (2), (3), (4); 188} {wal} 189 190do_execsql_test -db db2 610 { 191 SELECT * FROM t1 192} {1 2 3 4} 193 194do_test 620 { 195 set res [list] 196 db2 eval {SELECT a FROM t1} { 197 lappend res $a 198 if {$a==3} { 199 sqlite3_db_config db RESET_DB 1 200 db eval VACUUM 201 sqlite3_db_config db RESET_DB 0 202 } 203 } 204 205 set res 206} {1 2 3 4} 207 208do_execsql_test -db db2 630 { 209 SELECT * FROM sqlite_master 210} {} 211 212#------------------------------------------------------------------------- 213db2 close 214reset_db 215 216do_execsql_test 700 { 217 PRAGMA page_size=512; 218 PRAGMA auto_vacuum = 0; 219 CREATE TABLE t1(a,b,c); 220 CREATE INDEX t1a ON t1(a); 221 CREATE INDEX t1bc ON t1(b,c); 222 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10) 223 INSERT INTO t1(a,b,c) SELECT x, randomblob(100),randomblob(100) FROM c; 224 PRAGMA page_count; 225 PRAGMA integrity_check; 226} {19 ok} 227 228if {[nonzero_reserved_bytes]} { 229 finish_test 230 return 231} 232 233sqlite3_db_config db DEFENSIVE 0 234do_execsql_test 710 { 235 UPDATE sqlite_dbpage SET data= 236 X'53514C69746520666F726D61742033000200030100402020000000000000001300000000000000000000000300000004000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000D00000003017C0001D801AC017C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002E03061715110145696E6465787431626374310443524541544520494E4445582074316263204F4E20743128622C63292A0206171311013F696E64657874316174310343524541544520494E44455820743161204F4E20743128612926010617111101397461626C657431743102435245415445205441424C4520743128612C622C6329' WHERE pgno=1; 237} 238 239do_execsql_test 720 { 240 PRAGMA integrity_check; 241} {ok} 242 243do_test 730 { 244 sqlite3_db_config db RESET_DB 1 245 db eval VACUUM 246 sqlite3_db_config db RESET_DB 0 247} {0} 248 249do_execsql_test 740 { 250 PRAGMA page_count; 251 PRAGMA integrity_check; 252} {1 ok} 253 254finish_test 255