gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/resetdb.test (about) 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 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix resetdb 17 18 do_not_use_codec 19 20 ifcapable !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 # 32 if {[permutation]=="inmemory_journal" 33 || [permutation]=="journaltest" 34 } { 35 finish_test 36 return 37 } 38 39 # Create a sample database 40 do_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 55 sqlite3 db2 test.db 56 do_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 65 do_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}} 74 do_test 201 { 75 catchsql { 76 PRAGMA quick_check; 77 } db2 78 } {1 {file is not a database}} 79 80 do_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 # 102 db close 103 db2 close 104 forcedelete test.db 105 sqlite3 db test.db 106 do_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} 121 sqlite3 db2 test.db 122 do_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 133 sqlite3_db_config db DEFENSIVE 0 134 do_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 139 do_test 330 { 140 catchsql { 141 PRAGMA quick_check 142 } db2 143 } {1 {file is not a database}} 144 145 db2 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 # 150 do_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}} 161 db2 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 # 166 db close 167 sqlite3 db test.db 168 do_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}} 183 db2 close 184 185 #------------------------------------------------------------------------- 186 reset_db 187 sqlite3 db2 test.db 188 do_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 194 do_execsql_test -db db2 610 { 195 SELECT * FROM t1 196 } {1 2 3 4} 197 198 do_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 212 do_execsql_test -db db2 630 { 213 SELECT * FROM sqlite_master 214 } {} 215 216 #------------------------------------------------------------------------- 217 db2 close 218 reset_db 219 220 do_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 232 if {[nonzero_reserved_bytes]} { 233 finish_test 234 return 235 } 236 237 sqlite3_db_config db DEFENSIVE 0 238 do_execsql_test 710 { 239 UPDATE sqlite_dbpage SET data= 240 X'53514C69746520666F726D61742033000200030100402020000000000000001300000000000000000000000300000004000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000D00000003017C0001D801AC017C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002E03061715110145696E6465787431626374310443524541544520494E4445582074316263204F4E20743128622C63292A0206171311013F696E64657874316174310343524541544520494E44455820743161204F4E20743128612926010617111101397461626C657431743102435245415445205441424C4520743128612C622C6329' WHERE pgno=1; 241 } 242 243 do_execsql_test 720 { 244 PRAGMA integrity_check; 245 } {ok} 246 247 do_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 253 do_execsql_test 740 { 254 PRAGMA page_count; 255 PRAGMA integrity_check; 256 } {1 ok} 257 258 finish_test