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