| OLD | NEW |
|---|
| (Empty) |
| 1 # 2010 April 13 | |
| 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 # This file implements regression tests for SQLite library. The | |
| 12 # focus of this file is testing the operation of the library in | |
| 13 # "PRAGMA journal_mode=WAL" mode. | |
| 14 # | |
| 15 | |
| 16 set testdir [file dirname $argv0] | |
| 17 source $testdir/tester.tcl | |
| 18 source $testdir/lock_common.tcl | |
| 19 source $testdir/malloc_common.tcl | |
| 20 source $testdir/wal_common.tcl | |
| 21 | |
| 22 set testprefix wal | |
| 23 | |
| 24 ifcapable !wal {finish_test ; return } | |
| 25 | |
| 26 proc reopen_db {} { | |
| 27 catch { db close } | |
| 28 forcedelete test.db test.db-wal test.db-wal-summary | |
| 29 sqlite3_wal db test.db | |
| 30 } | |
| 31 | |
| 32 set ::blobcnt 0 | |
| 33 proc blob {nByte} { | |
| 34 incr ::blobcnt | |
| 35 return [string range [string repeat "${::blobcnt}x" $nByte] 1 $nByte] | |
| 36 } | |
| 37 | |
| 38 proc sqlite3_wal {args} { | |
| 39 eval sqlite3 $args | |
| 40 [lindex $args 0] eval { PRAGMA auto_vacuum = 0 } | |
| 41 [lindex $args 0] eval { PRAGMA page_size = 1024 } | |
| 42 [lindex $args 0] eval { PRAGMA journal_mode = wal } | |
| 43 [lindex $args 0] eval { PRAGMA synchronous = normal } | |
| 44 [lindex $args 0] function blob blob | |
| 45 } | |
| 46 | |
| 47 proc log_deleted {logfile} { | |
| 48 return [expr [file exists $logfile]==0] | |
| 49 } | |
| 50 | |
| 51 # | |
| 52 # These are 'warm-body' tests used while developing the WAL code. They | |
| 53 # serve to prove that a few really simple cases work: | |
| 54 # | |
| 55 # wal-1.*: Read and write the database. | |
| 56 # wal-2.*: Test MVCC with one reader, one writer. | |
| 57 # wal-3.*: Test transaction rollback. | |
| 58 # wal-4.*: Test savepoint/statement rollback. | |
| 59 # wal-5.*: Test the temp database. | |
| 60 # wal-6.*: Test creating databases with different page sizes. | |
| 61 # | |
| 62 # | |
| 63 # | |
| 64 do_test wal-0.1 { | |
| 65 execsql { PRAGMA auto_vacuum = 0 } | |
| 66 execsql { PRAGMA synchronous = normal } | |
| 67 execsql { PRAGMA journal_mode = wal } | |
| 68 } {wal} | |
| 69 do_test wal-0.2 { | |
| 70 file size test.db | |
| 71 } {1024} | |
| 72 | |
| 73 do_test wal-1.0 { | |
| 74 execsql { | |
| 75 BEGIN; | |
| 76 CREATE TABLE t1(a, b); | |
| 77 } | |
| 78 list [file exists test.db-journal] \ | |
| 79 [file exists test.db-wal] \ | |
| 80 [file size test.db] | |
| 81 } {0 1 1024} | |
| 82 do_test wal-1.1 { | |
| 83 execsql COMMIT | |
| 84 list [file exists test.db-journal] [file exists test.db-wal] | |
| 85 } {0 1} | |
| 86 do_test wal-1.2 { | |
| 87 # There are now two pages in the log. | |
| 88 file size test.db-wal | |
| 89 } [wal_file_size 2 1024] | |
| 90 | |
| 91 do_test wal-1.3 { | |
| 92 execsql { SELECT * FROM sqlite_master } | |
| 93 } {table t1 t1 2 {CREATE TABLE t1(a, b)}} | |
| 94 | |
| 95 do_test wal-1.4 { | |
| 96 execsql { INSERT INTO t1 VALUES(1, 2) } | |
| 97 execsql { INSERT INTO t1 VALUES(3, 4) } | |
| 98 execsql { INSERT INTO t1 VALUES(5, 6) } | |
| 99 execsql { INSERT INTO t1 VALUES(7, 8) } | |
| 100 execsql { INSERT INTO t1 VALUES(9, 10) } | |
| 101 } {} | |
| 102 | |
| 103 do_test wal-1.5 { | |
| 104 execsql { SELECT * FROM t1 } | |
| 105 } {1 2 3 4 5 6 7 8 9 10} | |
| 106 | |
| 107 do_test wal-2.1 { | |
| 108 sqlite3_wal db2 ./test.db | |
| 109 execsql { BEGIN; SELECT * FROM t1 } db2 | |
| 110 } {1 2 3 4 5 6 7 8 9 10} | |
| 111 | |
| 112 do_test wal-2.2 { | |
| 113 execsql { INSERT INTO t1 VALUES(11, 12) } | |
| 114 execsql { SELECT * FROM t1 } | |
| 115 } {1 2 3 4 5 6 7 8 9 10 11 12} | |
| 116 | |
| 117 do_test wal-2.3 { | |
| 118 execsql { SELECT * FROM t1 } db2 | |
| 119 } {1 2 3 4 5 6 7 8 9 10} | |
| 120 | |
| 121 do_test wal-2.4 { | |
| 122 execsql { INSERT INTO t1 VALUES(13, 14) } | |
| 123 execsql { SELECT * FROM t1 } | |
| 124 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} | |
| 125 | |
| 126 do_test wal-2.5 { | |
| 127 execsql { SELECT * FROM t1 } db2 | |
| 128 } {1 2 3 4 5 6 7 8 9 10} | |
| 129 | |
| 130 do_test wal-2.6 { | |
| 131 execsql { COMMIT; SELECT * FROM t1 } db2 | |
| 132 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} | |
| 133 | |
| 134 do_test wal-3.1 { | |
| 135 execsql { BEGIN; DELETE FROM t1 } | |
| 136 execsql { SELECT * FROM t1 } | |
| 137 } {} | |
| 138 do_test wal-3.2 { | |
| 139 execsql { SELECT * FROM t1 } db2 | |
| 140 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} | |
| 141 do_test wal-3.3 { | |
| 142 execsql { ROLLBACK } | |
| 143 execsql { SELECT * FROM t1 } | |
| 144 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} | |
| 145 db2 close | |
| 146 | |
| 147 #------------------------------------------------------------------------- | |
| 148 # The following tests, wal-4.*, test that savepoints work with WAL | |
| 149 # databases. | |
| 150 # | |
| 151 do_test wal-4.1 { | |
| 152 execsql { | |
| 153 DELETE FROM t1; | |
| 154 BEGIN; | |
| 155 INSERT INTO t1 VALUES('a', 'b'); | |
| 156 SAVEPOINT sp; | |
| 157 INSERT INTO t1 VALUES('c', 'd'); | |
| 158 SELECT * FROM t1; | |
| 159 } | |
| 160 } {a b c d} | |
| 161 do_test wal-4.2 { | |
| 162 execsql { | |
| 163 ROLLBACK TO sp; | |
| 164 SELECT * FROM t1; | |
| 165 } | |
| 166 } {a b} | |
| 167 do_test wal-4.3 { | |
| 168 execsql { | |
| 169 COMMIT; | |
| 170 SELECT * FROM t1; | |
| 171 } | |
| 172 } {a b} | |
| 173 | |
| 174 do_test wal-4.4.1 { | |
| 175 db close | |
| 176 sqlite3 db test.db | |
| 177 db func blob blob | |
| 178 list [execsql { SELECT * FROM t1 }] [file size test.db-wal] | |
| 179 } {{a b} 0} | |
| 180 do_test wal-4.4.2 { | |
| 181 execsql { PRAGMA cache_size = 10 } | |
| 182 execsql { | |
| 183 CREATE TABLE t2(a, b); | |
| 184 INSERT INTO t2 VALUES(blob(400), blob(400)); | |
| 185 SAVEPOINT tr; | |
| 186 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 2 */ | |
| 187 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 4 */ | |
| 188 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 8 */ | |
| 189 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 16 */ | |
| 190 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 32 */ | |
| 191 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 2 */ | |
| 192 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 4 */ | |
| 193 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 8 */ | |
| 194 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 16 */ | |
| 195 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 32 */ | |
| 196 SELECT count(*) FROM t2; | |
| 197 } | |
| 198 } {32} | |
| 199 do_test wal-4.4.3 { | |
| 200 execsql { ROLLBACK TO tr } | |
| 201 } {} | |
| 202 do_test wal-4.4.4 { | |
| 203 set logsize [file size test.db-wal] | |
| 204 execsql { | |
| 205 INSERT INTO t1 VALUES('x', 'y'); | |
| 206 RELEASE tr; | |
| 207 } | |
| 208 expr { $logsize == [file size test.db-wal] } | |
| 209 } {1} | |
| 210 do_test wal-4.4.5 { | |
| 211 execsql { SELECT count(*) FROM t2 } | |
| 212 } {1} | |
| 213 do_test wal-4.4.6 { | |
| 214 forcecopy test.db test2.db | |
| 215 forcecopy test.db-wal test2.db-wal | |
| 216 sqlite3 db2 test2.db | |
| 217 execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 } db2 | |
| 218 } {1 2} | |
| 219 do_test wal-4.4.7 { | |
| 220 execsql { PRAGMA integrity_check } db2 | |
| 221 } {ok} | |
| 222 db2 close | |
| 223 | |
| 224 do_test wal-4.5.1 { | |
| 225 reopen_db | |
| 226 db func blob blob | |
| 227 execsql { | |
| 228 PRAGMA journal_mode = WAL; | |
| 229 CREATE TABLE t1(a, b); | |
| 230 INSERT INTO t1 VALUES('a', 'b'); | |
| 231 } | |
| 232 sqlite3 db test.db | |
| 233 db func blob blob | |
| 234 list [execsql { SELECT * FROM t1 }] [file size test.db-wal] | |
| 235 } {{a b} 0} | |
| 236 do_test wal-4.5.2 { | |
| 237 execsql { PRAGMA cache_size = 10 } | |
| 238 execsql { | |
| 239 CREATE TABLE t2(a, b); | |
| 240 BEGIN; | |
| 241 INSERT INTO t2 VALUES(blob(400), blob(400)); | |
| 242 SAVEPOINT tr; | |
| 243 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 2 */ | |
| 244 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 4 */ | |
| 245 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 8 */ | |
| 246 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 16 */ | |
| 247 INSERT INTO t2 SELECT blob(400), blob(400) FROM t2; /* 32 */ | |
| 248 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 2 */ | |
| 249 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 4 */ | |
| 250 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 8 */ | |
| 251 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 16 */ | |
| 252 INSERT INTO t1 SELECT blob(400), blob(400) FROM t1; /* 32 */ | |
| 253 SELECT count(*) FROM t2; | |
| 254 } | |
| 255 } {32} | |
| 256 do_test wal-4.5.3 { | |
| 257 execsql { ROLLBACK TO tr } | |
| 258 } {} | |
| 259 do_test wal-4.5.4 { | |
| 260 set logsize [file size test.db-wal] | |
| 261 execsql { | |
| 262 INSERT INTO t1 VALUES('x', 'y'); | |
| 263 RELEASE tr; | |
| 264 COMMIT; | |
| 265 } | |
| 266 expr { $logsize == [file size test.db-wal] } | |
| 267 } {1} | |
| 268 do_test wal-4.5.5 { | |
| 269 execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 } | |
| 270 } {1 2} | |
| 271 do_test wal-4.5.6 { | |
| 272 forcecopy test.db test2.db | |
| 273 forcecopy test.db-wal test2.db-wal | |
| 274 sqlite3 db2 test2.db | |
| 275 execsql { SELECT count(*) FROM t2 ; SELECT count(*) FROM t1 } db2 | |
| 276 } {1 2} | |
| 277 do_test wal-4.5.7 { | |
| 278 execsql { PRAGMA integrity_check } db2 | |
| 279 } {ok} | |
| 280 db2 close | |
| 281 | |
| 282 do_test wal-4.6.1 { | |
| 283 execsql { | |
| 284 DELETE FROM t2; | |
| 285 PRAGMA wal_checkpoint; | |
| 286 BEGIN; | |
| 287 INSERT INTO t2 VALUES('w', 'x'); | |
| 288 SAVEPOINT save; | |
| 289 INSERT INTO t2 VALUES('y', 'z'); | |
| 290 ROLLBACK TO save; | |
| 291 COMMIT; | |
| 292 } | |
| 293 execsql { SELECT * FROM t2 } | |
| 294 } {w x} | |
| 295 | |
| 296 | |
| 297 reopen_db | |
| 298 do_test wal-5.1 { | |
| 299 execsql { | |
| 300 CREATE TEMP TABLE t2(a, b); | |
| 301 INSERT INTO t2 VALUES(1, 2); | |
| 302 } | |
| 303 } {} | |
| 304 do_test wal-5.2 { | |
| 305 execsql { | |
| 306 BEGIN; | |
| 307 INSERT INTO t2 VALUES(3, 4); | |
| 308 SELECT * FROM t2; | |
| 309 } | |
| 310 } {1 2 3 4} | |
| 311 do_test wal-5.3 { | |
| 312 execsql { | |
| 313 ROLLBACK; | |
| 314 SELECT * FROM t2; | |
| 315 } | |
| 316 } {1 2} | |
| 317 do_test wal-5.4 { | |
| 318 execsql { | |
| 319 CREATE TEMP TABLE t3(x UNIQUE); | |
| 320 BEGIN; | |
| 321 INSERT INTO t2 VALUES(3, 4); | |
| 322 INSERT INTO t3 VALUES('abc'); | |
| 323 } | |
| 324 catchsql { INSERT INTO t3 VALUES('abc') } | |
| 325 } {1 {UNIQUE constraint failed: t3.x}} | |
| 326 do_test wal-5.5 { | |
| 327 execsql { | |
| 328 COMMIT; | |
| 329 SELECT * FROM t2; | |
| 330 } | |
| 331 } {1 2 3 4} | |
| 332 db close | |
| 333 | |
| 334 foreach sector {512 4096} { | |
| 335 sqlite3_simulate_device -sectorsize $sector | |
| 336 foreach pgsz {512 1024 2048 4096} { | |
| 337 forcedelete test.db test.db-wal | |
| 338 do_test wal-6.$sector.$pgsz.1 { | |
| 339 sqlite3 db test.db -vfs devsym | |
| 340 execsql " | |
| 341 PRAGMA page_size = $pgsz; | |
| 342 PRAGMA auto_vacuum = 0; | |
| 343 PRAGMA journal_mode = wal; | |
| 344 " | |
| 345 execsql " | |
| 346 CREATE TABLE t1(a, b); | |
| 347 INSERT INTO t1 VALUES(1, 2); | |
| 348 " | |
| 349 db close | |
| 350 file size test.db | |
| 351 } [expr $pgsz*2] | |
| 352 | |
| 353 do_test wal-6.$sector.$pgsz.2 { | |
| 354 log_deleted test.db-wal | |
| 355 } {1} | |
| 356 } | |
| 357 } | |
| 358 | |
| 359 do_test wal-7.1 { | |
| 360 forcedelete test.db test.db-wal | |
| 361 sqlite3_wal db test.db | |
| 362 execsql { | |
| 363 PRAGMA page_size = 1024; | |
| 364 CREATE TABLE t1(a, b); | |
| 365 INSERT INTO t1 VALUES(1, 2); | |
| 366 } | |
| 367 list [file size test.db] [file size test.db-wal] | |
| 368 } [list 1024 [wal_file_size 3 1024]] | |
| 369 do_test wal-7.2 { | |
| 370 execsql { PRAGMA wal_checkpoint } | |
| 371 list [file size test.db] [file size test.db-wal] | |
| 372 } [list 2048 [wal_file_size 3 1024]] | |
| 373 | |
| 374 # Execute some transactions in auto-vacuum mode to test database file | |
| 375 # truncation. | |
| 376 # | |
| 377 do_test wal-8.1 { | |
| 378 reopen_db | |
| 379 catch { db close } | |
| 380 forcedelete test.db test.db-wal | |
| 381 | |
| 382 sqlite3 db test.db | |
| 383 db function blob blob | |
| 384 execsql { | |
| 385 PRAGMA auto_vacuum = 1; | |
| 386 PRAGMA journal_mode = wal; | |
| 387 PRAGMA auto_vacuum; | |
| 388 } | |
| 389 } {wal 1} | |
| 390 do_test wal-8.2 { | |
| 391 execsql { | |
| 392 PRAGMA page_size = 1024; | |
| 393 CREATE TABLE t1(x); | |
| 394 INSERT INTO t1 VALUES(blob(900)); | |
| 395 INSERT INTO t1 VALUES(blob(900)); | |
| 396 INSERT INTO t1 SELECT blob(900) FROM t1; /* 4 */ | |
| 397 INSERT INTO t1 SELECT blob(900) FROM t1; /* 8 */ | |
| 398 INSERT INTO t1 SELECT blob(900) FROM t1; /* 16 */ | |
| 399 INSERT INTO t1 SELECT blob(900) FROM t1; /* 32 */ | |
| 400 INSERT INTO t1 SELECT blob(900) FROM t1; /* 64 */ | |
| 401 PRAGMA wal_checkpoint; | |
| 402 } | |
| 403 file size test.db | |
| 404 } [expr 68*1024] | |
| 405 do_test wal-8.3 { | |
| 406 execsql { | |
| 407 DELETE FROM t1 WHERE rowid<54; | |
| 408 PRAGMA wal_checkpoint; | |
| 409 } | |
| 410 file size test.db | |
| 411 } [expr 14*1024] | |
| 412 | |
| 413 # Run some "warm-body" tests to ensure that log-summary files with more | |
| 414 # than 256 entries (log summaries that contain index blocks) work Ok. | |
| 415 # | |
| 416 do_test wal-9.1 { | |
| 417 reopen_db | |
| 418 execsql { | |
| 419 PRAGMA cache_size=2000; | |
| 420 CREATE TABLE t1(x PRIMARY KEY); | |
| 421 INSERT INTO t1 VALUES(blob(900)); | |
| 422 INSERT INTO t1 VALUES(blob(900)); | |
| 423 INSERT INTO t1 SELECT blob(900) FROM t1; /* 4 */ | |
| 424 INSERT INTO t1 SELECT blob(900) FROM t1; /* 8 */ | |
| 425 INSERT INTO t1 SELECT blob(900) FROM t1; /* 16 */ | |
| 426 INSERT INTO t1 SELECT blob(900) FROM t1; /* 32 */ | |
| 427 INSERT INTO t1 SELECT blob(900) FROM t1; /* 64 */ | |
| 428 INSERT INTO t1 SELECT blob(900) FROM t1; /* 128 */ | |
| 429 INSERT INTO t1 SELECT blob(900) FROM t1; /* 256 */ | |
| 430 } | |
| 431 file size test.db | |
| 432 } 1024 | |
| 433 do_test wal-9.2 { | |
| 434 sqlite3_wal db2 test.db | |
| 435 execsql {PRAGMA integrity_check } db2 | |
| 436 } {ok} | |
| 437 | |
| 438 do_test wal-9.3 { | |
| 439 forcedelete test2.db test2.db-wal | |
| 440 copy_file test.db test2.db | |
| 441 copy_file test.db-wal test2.db-wal | |
| 442 sqlite3_wal db3 test2.db | |
| 443 execsql {PRAGMA integrity_check } db3 | |
| 444 } {ok} | |
| 445 db3 close | |
| 446 | |
| 447 do_test wal-9.4 { | |
| 448 execsql { PRAGMA wal_checkpoint } | |
| 449 db2 close | |
| 450 sqlite3_wal db2 test.db | |
| 451 execsql {PRAGMA integrity_check } db2 | |
| 452 } {ok} | |
| 453 | |
| 454 foreach handle {db db2 db3} { catch { $handle close } } | |
| 455 unset handle | |
| 456 | |
| 457 #------------------------------------------------------------------------- | |
| 458 # The following block of tests - wal-10.* - test that the WAL locking | |
| 459 # scheme works in simple cases. This block of tests is run twice. Once | |
| 460 # using multiple connections in the address space of the current process, | |
| 461 # and once with all connections except one running in external processes. | |
| 462 # | |
| 463 do_multiclient_test tn { | |
| 464 | |
| 465 # Initialize the database schema and contents. | |
| 466 # | |
| 467 do_test wal-10.$tn.1 { | |
| 468 execsql { | |
| 469 PRAGMA auto_vacuum = 0; | |
| 470 PRAGMA journal_mode = wal; | |
| 471 CREATE TABLE t1(a, b); | |
| 472 INSERT INTO t1 VALUES(1, 2); | |
| 473 SELECT * FROM t1; | |
| 474 } | |
| 475 } {wal 1 2} | |
| 476 | |
| 477 # Open a transaction and write to the database using [db]. Check that [db2] | |
| 478 # is still able to read the snapshot before the transaction was opened. | |
| 479 # | |
| 480 do_test wal-10.$tn.2 { | |
| 481 execsql { BEGIN; INSERT INTO t1 VALUES(3, 4); } | |
| 482 sql2 {SELECT * FROM t1} | |
| 483 } {1 2} | |
| 484 | |
| 485 # Have [db] commit the transaction. Check that [db2] is now seeing the | |
| 486 # new, updated snapshot. | |
| 487 # | |
| 488 do_test wal-10.$tn.3 { | |
| 489 execsql { COMMIT } | |
| 490 sql2 {SELECT * FROM t1} | |
| 491 } {1 2 3 4} | |
| 492 | |
| 493 # Have [db2] open a read transaction. Then write to the db via [db]. Check | |
| 494 # that [db2] is still seeing the original snapshot. Then read with [db3]. | |
| 495 # [db3] should see the newly committed data. | |
| 496 # | |
| 497 do_test wal-10.$tn.4 { | |
| 498 sql2 { BEGIN ; SELECT * FROM t1} | |
| 499 } {1 2 3 4} | |
| 500 do_test wal-10.$tn.5 { | |
| 501 execsql { INSERT INTO t1 VALUES(5, 6); } | |
| 502 sql2 {SELECT * FROM t1} | |
| 503 } {1 2 3 4} | |
| 504 do_test wal-10.$tn.6 { | |
| 505 sql3 {SELECT * FROM t1} | |
| 506 } {1 2 3 4 5 6} | |
| 507 do_test wal-10.$tn.7 { | |
| 508 sql2 COMMIT | |
| 509 } {} | |
| 510 | |
| 511 # Have [db2] open a write transaction. Then attempt to write to the | |
| 512 # database via [db]. This should fail (writer lock cannot be obtained). | |
| 513 # | |
| 514 # Then open a read-transaction with [db]. Commit the [db2] transaction | |
| 515 # to disk. Verify that [db] still cannot write to the database (because | |
| 516 # it is reading an old snapshot). | |
| 517 # | |
| 518 # Close the current [db] transaction. Open a new one. [db] can now write | |
| 519 # to the database (as it is not locked and [db] is reading the latest | |
| 520 # snapshot). | |
| 521 # | |
| 522 do_test wal-10.$tn.7 { | |
| 523 sql2 { BEGIN; INSERT INTO t1 VALUES(7, 8) ; } | |
| 524 catchsql { INSERT INTO t1 VALUES(9, 10) } | |
| 525 } {1 {database is locked}} | |
| 526 do_test wal-10.$tn.8 { | |
| 527 execsql { BEGIN ; SELECT * FROM t1 } | |
| 528 } {1 2 3 4 5 6} | |
| 529 do_test wal-10.$tn.9 { | |
| 530 sql2 COMMIT | |
| 531 catchsql { INSERT INTO t1 VALUES(9, 10) } | |
| 532 } {1 {database is locked}} | |
| 533 do_test wal-10.$tn.10 { | |
| 534 execsql { COMMIT } | |
| 535 execsql { BEGIN } | |
| 536 execsql { INSERT INTO t1 VALUES(9, 10) } | |
| 537 execsql { COMMIT } | |
| 538 execsql { SELECT * FROM t1 } | |
| 539 } {1 2 3 4 5 6 7 8 9 10} | |
| 540 | |
| 541 # Open a read transaction with [db2]. Check that this prevents [db] from | |
| 542 # checkpointing the database. But not from writing to it. | |
| 543 # | |
| 544 do_test wal-10.$tn.11 { | |
| 545 sql2 { BEGIN; SELECT * FROM t1 } | |
| 546 } {1 2 3 4 5 6 7 8 9 10} | |
| 547 do_test wal-10.$tn.12 { | |
| 548 catchsql { PRAGMA wal_checkpoint } | |
| 549 } {0 {0 7 7}} ;# Reader no longer block checkpoints | |
| 550 do_test wal-10.$tn.13 { | |
| 551 execsql { INSERT INTO t1 VALUES(11, 12) } | |
| 552 sql2 {SELECT * FROM t1} | |
| 553 } {1 2 3 4 5 6 7 8 9 10} | |
| 554 | |
| 555 # Writers do not block checkpoints any more either. | |
| 556 # | |
| 557 do_test wal-10.$tn.14 { | |
| 558 catchsql { PRAGMA wal_checkpoint } | |
| 559 } {0 {0 8 7}} | |
| 560 | |
| 561 # The following series of test cases used to verify another blocking | |
| 562 # case in WAL - a case which no longer blocks. | |
| 563 # | |
| 564 do_test wal-10.$tn.15 { | |
| 565 sql2 { COMMIT; BEGIN; SELECT * FROM t1; } | |
| 566 } {1 2 3 4 5 6 7 8 9 10 11 12} | |
| 567 do_test wal-10.$tn.16 { | |
| 568 catchsql { PRAGMA wal_checkpoint } | |
| 569 } {0 {0 8 8}} | |
| 570 do_test wal-10.$tn.17 { | |
| 571 execsql { PRAGMA wal_checkpoint } | |
| 572 } {0 8 8} | |
| 573 do_test wal-10.$tn.18 { | |
| 574 sql3 { BEGIN; SELECT * FROM t1 } | |
| 575 } {1 2 3 4 5 6 7 8 9 10 11 12} | |
| 576 do_test wal-10.$tn.19 { | |
| 577 catchsql { INSERT INTO t1 VALUES(13, 14) } | |
| 578 } {0 {}} | |
| 579 do_test wal-10.$tn.20 { | |
| 580 execsql { SELECT * FROM t1 } | |
| 581 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} | |
| 582 do_test wal-10.$tn.21 { | |
| 583 sql3 COMMIT | |
| 584 sql2 COMMIT | |
| 585 } {} | |
| 586 do_test wal-10.$tn.22 { | |
| 587 execsql { SELECT * FROM t1 } | |
| 588 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} | |
| 589 | |
| 590 # Another series of tests that used to demonstrate blocking behavior | |
| 591 # but which now work. | |
| 592 # | |
| 593 do_test wal-10.$tn.23 { | |
| 594 execsql { PRAGMA wal_checkpoint } | |
| 595 } {0 9 9} | |
| 596 do_test wal-10.$tn.24 { | |
| 597 sql2 { BEGIN; SELECT * FROM t1; } | |
| 598 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} | |
| 599 do_test wal-10.$tn.25 { | |
| 600 execsql { PRAGMA wal_checkpoint } | |
| 601 } {0 9 9} | |
| 602 do_test wal-10.$tn.26 { | |
| 603 catchsql { INSERT INTO t1 VALUES(15, 16) } | |
| 604 } {0 {}} | |
| 605 do_test wal-10.$tn.27 { | |
| 606 sql3 { INSERT INTO t1 VALUES(17, 18) } | |
| 607 } {} | |
| 608 do_test wal-10.$tn.28 { | |
| 609 code3 { | |
| 610 set ::STMT [sqlite3_prepare db3 "SELECT * FROM t1" -1 TAIL] | |
| 611 sqlite3_step $::STMT | |
| 612 } | |
| 613 execsql { SELECT * FROM t1 } | |
| 614 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18} | |
| 615 do_test wal-10.$tn.29 { | |
| 616 execsql { INSERT INTO t1 VALUES(19, 20) } | |
| 617 catchsql { PRAGMA wal_checkpoint } | |
| 618 } {0 {0 3 0}} | |
| 619 do_test wal-10.$tn.30 { | |
| 620 code3 { sqlite3_finalize $::STMT } | |
| 621 execsql { PRAGMA wal_checkpoint } | |
| 622 } {0 3 0} | |
| 623 | |
| 624 # At one point, if a reader failed to upgrade to a writer because it | |
| 625 # was reading an old snapshot, the write-locks were not being released. | |
| 626 # Test that this bug has been fixed. | |
| 627 # | |
| 628 do_test wal-10.$tn.31 { | |
| 629 sql2 COMMIT | |
| 630 execsql { BEGIN ; SELECT * FROM t1 } | |
| 631 sql2 { INSERT INTO t1 VALUES(21, 22) } | |
| 632 catchsql { INSERT INTO t1 VALUES(23, 24) } | |
| 633 } {1 {database is locked}} | |
| 634 do_test wal-10.$tn.32 { | |
| 635 # This statement would fail when the bug was present. | |
| 636 sql2 { INSERT INTO t1 VALUES(23, 24) } | |
| 637 } {} | |
| 638 do_test wal-10.$tn.33 { | |
| 639 execsql { SELECT * FROM t1 ; COMMIT } | |
| 640 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20} | |
| 641 do_test wal-10.$tn.34 { | |
| 642 execsql { SELECT * FROM t1 } | |
| 643 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24} | |
| 644 | |
| 645 # Test that if a checkpointer cannot obtain the required locks, it | |
| 646 # releases all locks before returning a busy error. | |
| 647 # | |
| 648 do_test wal-10.$tn.35 { | |
| 649 execsql { | |
| 650 DELETE FROM t1; | |
| 651 INSERT INTO t1 VALUES('a', 'b'); | |
| 652 INSERT INTO t1 VALUES('c', 'd'); | |
| 653 } | |
| 654 sql2 { | |
| 655 BEGIN; | |
| 656 SELECT * FROM t1; | |
| 657 } | |
| 658 } {a b c d} | |
| 659 do_test wal-10.$tn.36 { | |
| 660 catchsql { PRAGMA wal_checkpoint } | |
| 661 } {0 {0 8 8}} | |
| 662 do_test wal-10.$tn.36 { | |
| 663 sql3 { INSERT INTO t1 VALUES('e', 'f') } | |
| 664 sql2 { SELECT * FROM t1 } | |
| 665 } {a b c d} | |
| 666 do_test wal-10.$tn.37 { | |
| 667 sql2 COMMIT | |
| 668 execsql { PRAGMA wal_checkpoint } | |
| 669 } {0 9 9} | |
| 670 } | |
| 671 | |
| 672 #------------------------------------------------------------------------- | |
| 673 # This block of tests, wal-11.*, test that nothing goes terribly wrong | |
| 674 # if frames must be written to the log file before a transaction is | |
| 675 # committed (in order to free up memory). | |
| 676 # | |
| 677 do_test wal-11.1 { | |
| 678 reopen_db | |
| 679 execsql { | |
| 680 PRAGMA cache_size = 10; | |
| 681 PRAGMA page_size = 1024; | |
| 682 CREATE TABLE t1(x PRIMARY KEY); | |
| 683 } | |
| 684 list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044] | |
| 685 } {1 3} | |
| 686 do_test wal-11.2 { | |
| 687 execsql { PRAGMA wal_checkpoint } | |
| 688 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 689 } [list 3 [wal_file_size 3 1024]] | |
| 690 do_test wal-11.3 { | |
| 691 execsql { INSERT INTO t1 VALUES( blob(900) ) } | |
| 692 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 693 } [list 3 [wal_file_size 4 1024]] | |
| 694 | |
| 695 do_test wal-11.4 { | |
| 696 execsql { | |
| 697 BEGIN; | |
| 698 INSERT INTO t1 SELECT blob(900) FROM t1; -- 2 | |
| 699 INSERT INTO t1 SELECT blob(900) FROM t1; -- 4 | |
| 700 INSERT INTO t1 SELECT blob(900) FROM t1; -- 8 | |
| 701 INSERT INTO t1 SELECT blob(900) FROM t1; -- 16 | |
| 702 } | |
| 703 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 704 } [list 3 [wal_file_size 32 1024]] | |
| 705 do_test wal-11.5 { | |
| 706 execsql { | |
| 707 SELECT count(*) FROM t1; | |
| 708 PRAGMA integrity_check; | |
| 709 } | |
| 710 } {16 ok} | |
| 711 do_test wal-11.6 { | |
| 712 execsql COMMIT | |
| 713 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 714 } [list 3 [wal_file_size 41 1024]] | |
| 715 do_test wal-11.7 { | |
| 716 execsql { | |
| 717 SELECT count(*) FROM t1; | |
| 718 PRAGMA integrity_check; | |
| 719 } | |
| 720 } {16 ok} | |
| 721 do_test wal-11.8 { | |
| 722 execsql { PRAGMA wal_checkpoint } | |
| 723 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 724 } [list 37 [wal_file_size 41 1024]] | |
| 725 do_test wal-11.9 { | |
| 726 db close | |
| 727 list [expr [file size test.db]/1024] [log_deleted test.db-wal] | |
| 728 } {37 1} | |
| 729 sqlite3_wal db test.db | |
| 730 set nWal 39 | |
| 731 if {[permutation]!="mmap"} {set nWal 37} | |
| 732 ifcapable !mmap {set nWal 37} | |
| 733 do_test wal-11.10 { | |
| 734 execsql { | |
| 735 PRAGMA cache_size = 10; | |
| 736 BEGIN; | |
| 737 INSERT INTO t1 SELECT blob(900) FROM t1; -- 32 | |
| 738 SELECT count(*) FROM t1; | |
| 739 } | |
| 740 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 741 } [list 37 [wal_file_size $nWal 1024]] | |
| 742 do_test wal-11.11 { | |
| 743 execsql { | |
| 744 SELECT count(*) FROM t1; | |
| 745 ROLLBACK; | |
| 746 SELECT count(*) FROM t1; | |
| 747 } | |
| 748 } {32 16} | |
| 749 do_test wal-11.12 { | |
| 750 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 751 } [list 37 [wal_file_size $nWal 1024]] | |
| 752 do_test wal-11.13 { | |
| 753 execsql { | |
| 754 INSERT INTO t1 VALUES( blob(900) ); | |
| 755 SELECT count(*) FROM t1; | |
| 756 PRAGMA integrity_check; | |
| 757 } | |
| 758 } {17 ok} | |
| 759 do_test wal-11.14 { | |
| 760 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 761 } [list 37 [wal_file_size $nWal 1024]] | |
| 762 | |
| 763 | |
| 764 #------------------------------------------------------------------------- | |
| 765 # This block of tests, wal-12.*, tests the fix for a problem that | |
| 766 # could occur if a log that is a prefix of an older log is written | |
| 767 # into a reused log file. | |
| 768 # | |
| 769 reopen_db | |
| 770 do_test wal-12.1 { | |
| 771 execsql { | |
| 772 PRAGMA page_size = 1024; | |
| 773 CREATE TABLE t1(x, y); | |
| 774 CREATE TABLE t2(x, y); | |
| 775 INSERT INTO t1 VALUES('A', 1); | |
| 776 } | |
| 777 list [expr [file size test.db]/1024] [file size test.db-wal] | |
| 778 } [list 1 [wal_file_size 5 1024]] | |
| 779 do_test wal-12.2 { | |
| 780 db close | |
| 781 sqlite3 db test.db | |
| 782 execsql { | |
| 783 PRAGMA synchronous = normal; | |
| 784 UPDATE t1 SET y = 0 WHERE x = 'A'; | |
| 785 } | |
| 786 list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044] | |
| 787 } {3 1} | |
| 788 do_test wal-12.3 { | |
| 789 execsql { INSERT INTO t2 VALUES('B', 1) } | |
| 790 list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044] | |
| 791 } {3 2} | |
| 792 do_test wal-12.4 { | |
| 793 forcecopy test.db test2.db | |
| 794 forcecopy test.db-wal test2.db-wal | |
| 795 sqlite3_wal db2 test2.db | |
| 796 execsql { SELECT * FROM t2 } db2 | |
| 797 } {B 1} | |
| 798 db2 close | |
| 799 do_test wal-12.5 { | |
| 800 execsql { | |
| 801 PRAGMA wal_checkpoint; | |
| 802 UPDATE t2 SET y = 2 WHERE x = 'B'; | |
| 803 PRAGMA wal_checkpoint; | |
| 804 UPDATE t1 SET y = 1 WHERE x = 'A'; | |
| 805 PRAGMA wal_checkpoint; | |
| 806 UPDATE t1 SET y = 0 WHERE x = 'A'; | |
| 807 } | |
| 808 execsql { SELECT * FROM t2 } | |
| 809 } {B 2} | |
| 810 do_test wal-12.6 { | |
| 811 forcecopy test.db test2.db | |
| 812 forcecopy test.db-wal test2.db-wal | |
| 813 sqlite3_wal db2 test2.db | |
| 814 execsql { SELECT * FROM t2 } db2 | |
| 815 } {B 2} | |
| 816 db2 close | |
| 817 db close | |
| 818 | |
| 819 #------------------------------------------------------------------------- | |
| 820 # Test large log summaries. | |
| 821 # | |
| 822 # In this case "large" usually means a log file that requires a wal-index | |
| 823 # mapping larger than 64KB (the default initial allocation). A 64KB wal-index | |
| 824 # is large enough for a log file that contains approximately 13100 frames. | |
| 825 # So the following tests create logs containing at least this many frames. | |
| 826 # | |
| 827 # wal-13.1.*: This test case creates a very large log file within the | |
| 828 # file-system (around 200MB). The log file does not contain | |
| 829 # any valid frames. Test that the database file can still be | |
| 830 # opened and queried, and that the invalid log file causes no | |
| 831 # problems. | |
| 832 # | |
| 833 # wal-13.2.*: Test that a process may create a large log file and query | |
| 834 # the database (including the log file that it itself created). | |
| 835 # | |
| 836 # wal-13.3.*: Test that if a very large log file is created, and then a | |
| 837 # second connection is opened on the database file, it is possible | |
| 838 # to query the database (and the very large log) using the | |
| 839 # second connection. | |
| 840 # | |
| 841 # wal-13.4.*: Same test as wal-13.3.*. Except in this case the second | |
| 842 # connection is opened by an external process. | |
| 843 # | |
| 844 do_test wal-13.1.1 { | |
| 845 list [file exists test.db] [file exists test.db-wal] | |
| 846 } {1 0} | |
| 847 do_test wal-13.1.2 { | |
| 848 set fd [open test.db-wal w] | |
| 849 seek $fd [expr 200*1024*1024] | |
| 850 puts $fd "" | |
| 851 close $fd | |
| 852 sqlite3 db test.db | |
| 853 execsql { SELECT * FROM t2 } | |
| 854 } {B 2} | |
| 855 do_test wal-13.1.3 { | |
| 856 db close | |
| 857 file exists test.db-wal | |
| 858 } {0} | |
| 859 | |
| 860 do_test wal-13.2.1 { | |
| 861 sqlite3 db test.db | |
| 862 execsql { SELECT count(*) FROM t2 } | |
| 863 } {1} | |
| 864 do_test wal-13.2.2 { | |
| 865 db function blob blob | |
| 866 for {set i 0} {$i < 16} {incr i} { | |
| 867 execsql { INSERT INTO t2 SELECT blob(400), blob(400) FROM t2 } | |
| 868 } | |
| 869 execsql { SELECT count(*) FROM t2 } | |
| 870 } [expr int(pow(2, 16))] | |
| 871 do_test wal-13.2.3 { | |
| 872 expr [file size test.db-wal] > [wal_file_size 33000 1024] | |
| 873 } 1 | |
| 874 | |
| 875 do_multiclient_test tn { | |
| 876 incr tn 2 | |
| 877 | |
| 878 do_test wal-13.$tn.0 { | |
| 879 sql1 { | |
| 880 PRAGMA journal_mode = WAL; | |
| 881 CREATE TABLE t1(x); | |
| 882 INSERT INTO t1 SELECT randomblob(800); | |
| 883 } | |
| 884 sql1 { SELECT count(*) FROM t1 } | |
| 885 } {1} | |
| 886 | |
| 887 for {set ii 1} {$ii<16} {incr ii} { | |
| 888 do_test wal-13.$tn.$ii.a { | |
| 889 sql2 { INSERT INTO t1 SELECT randomblob(800) FROM t1 } | |
| 890 sql2 { SELECT count(*) FROM t1 } | |
| 891 } [expr (1<<$ii)] | |
| 892 do_test wal-13.$tn.$ii.b { | |
| 893 sql1 { SELECT count(*) FROM t1 } | |
| 894 } [expr (1<<$ii)] | |
| 895 do_test wal-13.$tn.$ii.c { | |
| 896 sql1 { SELECT count(*) FROM t1 } | |
| 897 } [expr (1<<$ii)] | |
| 898 do_test wal-13.$tn.$ii.d { | |
| 899 sql1 { PRAGMA integrity_check } | |
| 900 } {ok} | |
| 901 } | |
| 902 } | |
| 903 | |
| 904 #------------------------------------------------------------------------- | |
| 905 # Check a fun corruption case has been fixed. | |
| 906 # | |
| 907 # The problem was that after performing a checkpoint using a connection | |
| 908 # that had an out-of-date pager-cache, the next time the connection was | |
| 909 # used it did not realize the cache was out-of-date and proceeded to | |
| 910 # operate with an inconsistent cache. Leading to corruption. | |
| 911 # | |
| 912 catch { db close } | |
| 913 catch { db2 close } | |
| 914 catch { db3 close } | |
| 915 forcedelete test.db test.db-wal | |
| 916 sqlite3 db test.db | |
| 917 sqlite3 db2 test.db | |
| 918 do_test wal-14 { | |
| 919 execsql { | |
| 920 PRAGMA journal_mode = WAL; | |
| 921 CREATE TABLE t1(a PRIMARY KEY, b); | |
| 922 INSERT INTO t1 VALUES(randomblob(10), randomblob(100)); | |
| 923 INSERT INTO t1 SELECT randomblob(10), randomblob(100) FROM t1; | |
| 924 INSERT INTO t1 SELECT randomblob(10), randomblob(100) FROM t1; | |
| 925 INSERT INTO t1 SELECT randomblob(10), randomblob(100) FROM t1; | |
| 926 } | |
| 927 | |
| 928 db2 eval { | |
| 929 INSERT INTO t1 SELECT randomblob(10), randomblob(100); | |
| 930 INSERT INTO t1 SELECT randomblob(10), randomblob(100); | |
| 931 INSERT INTO t1 SELECT randomblob(10), randomblob(100); | |
| 932 INSERT INTO t1 SELECT randomblob(10), randomblob(100); | |
| 933 } | |
| 934 | |
| 935 # After executing the "PRAGMA wal_checkpoint", connection [db] was being | |
| 936 # left with an inconsistent cache. Running the CREATE INDEX statement | |
| 937 # in this state led to database corruption. | |
| 938 catchsql { | |
| 939 PRAGMA wal_checkpoint; | |
| 940 CREATE INDEX i1 on t1(b); | |
| 941 } | |
| 942 | |
| 943 db2 eval { PRAGMA integrity_check } | |
| 944 } {ok} | |
| 945 | |
| 946 catch { db close } | |
| 947 catch { db2 close } | |
| 948 | |
| 949 #------------------------------------------------------------------------- | |
| 950 # The following block of tests - wal-15.* - focus on testing the | |
| 951 # implementation of the sqlite3_wal_checkpoint() interface. | |
| 952 # | |
| 953 forcedelete test.db test.db-wal | |
| 954 sqlite3 db test.db | |
| 955 do_test wal-15.1 { | |
| 956 execsql { | |
| 957 PRAGMA auto_vacuum = 0; | |
| 958 PRAGMA page_size = 1024; | |
| 959 PRAGMA journal_mode = WAL; | |
| 960 } | |
| 961 execsql { | |
| 962 CREATE TABLE t1(a, b); | |
| 963 INSERT INTO t1 VALUES(1, 2); | |
| 964 } | |
| 965 } {} | |
| 966 | |
| 967 # Test that an error is returned if the database name is not recognized | |
| 968 # | |
| 969 do_test wal-15.2.1 { | |
| 970 sqlite3_wal_checkpoint db aux | |
| 971 } {SQLITE_ERROR} | |
| 972 do_test wal-15.2.2 { | |
| 973 sqlite3_errcode db | |
| 974 } {SQLITE_ERROR} | |
| 975 do_test wal-15.2.3 { | |
| 976 sqlite3_errmsg db | |
| 977 } {unknown database: aux} | |
| 978 | |
| 979 # Test that an error is returned if an attempt is made to checkpoint | |
| 980 # if a transaction is open on the database. | |
| 981 # | |
| 982 do_test wal-15.3.1 { | |
| 983 execsql { | |
| 984 BEGIN; | |
| 985 INSERT INTO t1 VALUES(3, 4); | |
| 986 } | |
| 987 sqlite3_wal_checkpoint db main | |
| 988 } {SQLITE_LOCKED} | |
| 989 do_test wal-15.3.2 { | |
| 990 sqlite3_errcode db | |
| 991 } {SQLITE_LOCKED} | |
| 992 do_test wal-15.3.3 { | |
| 993 sqlite3_errmsg db | |
| 994 } {database table is locked} | |
| 995 | |
| 996 # Earlier versions returned an error is returned if the db cannot be | |
| 997 # checkpointed because of locks held by another connection. Check that | |
| 998 # this is no longer the case. | |
| 999 # | |
| 1000 sqlite3 db2 test.db | |
| 1001 do_test wal-15.4.1 { | |
| 1002 execsql { | |
| 1003 BEGIN; | |
| 1004 SELECT * FROM t1; | |
| 1005 } db2 | |
| 1006 } {1 2} | |
| 1007 do_test wal-15.4.2 { | |
| 1008 execsql { COMMIT } | |
| 1009 sqlite3_wal_checkpoint db | |
| 1010 } {SQLITE_OK} | |
| 1011 do_test wal-15.4.3 { | |
| 1012 sqlite3_errmsg db | |
| 1013 } {not an error} | |
| 1014 | |
| 1015 # After [db2] drops its lock, [db] may checkpoint the db. | |
| 1016 # | |
| 1017 do_test wal-15.4.4 { | |
| 1018 execsql { COMMIT } db2 | |
| 1019 sqlite3_wal_checkpoint db | |
| 1020 } {SQLITE_OK} | |
| 1021 do_test wal-15.4.5 { | |
| 1022 sqlite3_errmsg db | |
| 1023 } {not an error} | |
| 1024 do_test wal-15.4.6 { | |
| 1025 file size test.db | |
| 1026 } [expr 1024*2] | |
| 1027 | |
| 1028 catch { db2 close } | |
| 1029 catch { db close } | |
| 1030 | |
| 1031 #------------------------------------------------------------------------- | |
| 1032 # The following block of tests - wal-16.* - test that if a NULL pointer or | |
| 1033 # an empty string is passed as the second argument of the wal_checkpoint() | |
| 1034 # API, an attempt is made to checkpoint all attached databases. | |
| 1035 # | |
| 1036 foreach {tn ckpt_cmd ckpt_res ckpt_main ckpt_aux} { | |
| 1037 1 {sqlite3_wal_checkpoint db} SQLITE_OK 1 1 | |
| 1038 2 {sqlite3_wal_checkpoint db ""} SQLITE_OK 1 1 | |
| 1039 3 {db eval "PRAGMA wal_checkpoint"} {0 10 10} 1 1 | |
| 1040 | |
| 1041 4 {sqlite3_wal_checkpoint db main} SQLITE_OK 1 0 | |
| 1042 5 {sqlite3_wal_checkpoint db aux} SQLITE_OK 0 1 | |
| 1043 6 {sqlite3_wal_checkpoint db temp} SQLITE_OK 0 0 | |
| 1044 7 {db eval "PRAGMA main.wal_checkpoint"} {0 10 10} 1 0 | |
| 1045 8 {db eval "PRAGMA aux.wal_checkpoint"} {0 13 13} 0 1 | |
| 1046 9 {db eval "PRAGMA temp.wal_checkpoint"} {0 -1 -1} 0 0 | |
| 1047 } { | |
| 1048 do_test wal-16.$tn.1 { | |
| 1049 forcedelete test2.db test2.db-wal test2.db-journal | |
| 1050 forcedelete test.db test.db-wal test.db-journal | |
| 1051 | |
| 1052 sqlite3 db test.db | |
| 1053 execsql { | |
| 1054 ATTACH 'test2.db' AS aux; | |
| 1055 PRAGMA main.auto_vacuum = 0; | |
| 1056 PRAGMA aux.auto_vacuum = 0; | |
| 1057 PRAGMA main.journal_mode = WAL; | |
| 1058 PRAGMA aux.journal_mode = WAL; | |
| 1059 PRAGMA main.synchronous = NORMAL; | |
| 1060 PRAGMA aux.synchronous = NORMAL; | |
| 1061 } | |
| 1062 } {wal wal} | |
| 1063 | |
| 1064 do_test wal-16.$tn.2 { | |
| 1065 execsql { | |
| 1066 CREATE TABLE main.t1(a, b, PRIMARY KEY(a, b)); | |
| 1067 CREATE TABLE aux.t2(a, b, PRIMARY KEY(a, b)); | |
| 1068 | |
| 1069 INSERT INTO t2 VALUES(1, randomblob(1000)); | |
| 1070 INSERT INTO t2 VALUES(2, randomblob(1000)); | |
| 1071 INSERT INTO t1 SELECT * FROM t2; | |
| 1072 } | |
| 1073 | |
| 1074 list [file size test.db] [file size test.db-wal] | |
| 1075 } [list [expr 1*1024] [wal_file_size 10 1024]] | |
| 1076 do_test wal-16.$tn.3 { | |
| 1077 list [file size test2.db] [file size test2.db-wal] | |
| 1078 } [list [expr 1*1024] [wal_file_size 13 1024]] | |
| 1079 | |
| 1080 do_test wal-16.$tn.4 [list eval $ckpt_cmd] $ckpt_res | |
| 1081 | |
| 1082 do_test wal-16.$tn.5 { | |
| 1083 list [file size test.db] [file size test.db-wal] | |
| 1084 } [list [expr ($ckpt_main ? 7 : 1)*1024] [wal_file_size 10 1024]] | |
| 1085 | |
| 1086 do_test wal-16.$tn.6 { | |
| 1087 list [file size test2.db] [file size test2.db-wal] | |
| 1088 } [list [expr ($ckpt_aux ? 7 : 1)*1024] [wal_file_size 13 1024]] | |
| 1089 | |
| 1090 catch { db close } | |
| 1091 } | |
| 1092 | |
| 1093 #------------------------------------------------------------------------- | |
| 1094 # The following tests - wal-17.* - attempt to verify that the correct | |
| 1095 # number of "padding" frames are appended to the log file when a transaction | |
| 1096 # is committed in synchronous=FULL mode. | |
| 1097 # | |
| 1098 # Do this by creating a database that uses 512 byte pages. Then writing | |
| 1099 # a transaction that modifies 171 pages. In synchronous=NORMAL mode, this | |
| 1100 # produces a log file of: | |
| 1101 # | |
| 1102 # 32 + (24+512)*171 = 90312 bytes. | |
| 1103 # | |
| 1104 # Slightly larger than 11*8192 = 90112 bytes. | |
| 1105 # | |
| 1106 # Run the test using various different sector-sizes. In each case, the | |
| 1107 # WAL code should write the 90300 bytes of log file containing the | |
| 1108 # transaction, then append as may frames as are required to extend the | |
| 1109 # log file so that no part of the next transaction will be written into | |
| 1110 # a disk-sector used by transaction just committed. | |
| 1111 # | |
| 1112 set old_pending_byte [sqlite3_test_control_pending_byte 0x10000000] | |
| 1113 catch { db close } | |
| 1114 foreach {tn sectorsize logsize} " | |
| 1115 1 128 [wal_file_size 172 512] | |
| 1116 2 256 [wal_file_size 172 512] | |
| 1117 3 512 [wal_file_size 172 512] | |
| 1118 4 1024 [wal_file_size 172 512] | |
| 1119 5 2048 [wal_file_size 172 512] | |
| 1120 6 4096 [wal_file_size 176 512] | |
| 1121 7 8192 [wal_file_size 184 512] | |
| 1122 " { | |
| 1123 forcedelete test.db test.db-wal test.db-journal | |
| 1124 sqlite3_simulate_device -sectorsize $sectorsize | |
| 1125 sqlite3 db test.db -vfs devsym | |
| 1126 | |
| 1127 do_test wal-17.$tn.1 { | |
| 1128 execsql { | |
| 1129 PRAGMA auto_vacuum = 0; | |
| 1130 PRAGMA page_size = 512; | |
| 1131 PRAGMA cache_size = -2000; | |
| 1132 PRAGMA journal_mode = WAL; | |
| 1133 PRAGMA synchronous = FULL; | |
| 1134 } | |
| 1135 execsql { | |
| 1136 BEGIN; | |
| 1137 CREATE TABLE t(x); | |
| 1138 } | |
| 1139 for {set i 0} {$i<166} {incr i} { | |
| 1140 execsql { INSERT INTO t VALUES(randomblob(400)) } | |
| 1141 } | |
| 1142 execsql COMMIT | |
| 1143 | |
| 1144 file size test.db-wal | |
| 1145 } $logsize | |
| 1146 | |
| 1147 do_test wal-17.$tn.2 { | |
| 1148 file size test.db | |
| 1149 } 512 | |
| 1150 | |
| 1151 do_test wal-17.$tn.3 { | |
| 1152 db close | |
| 1153 file size test.db | |
| 1154 } [expr 512*171] | |
| 1155 } | |
| 1156 sqlite3_test_control_pending_byte $old_pending_byte | |
| 1157 | |
| 1158 #------------------------------------------------------------------------- | |
| 1159 # This test - wal-18.* - verifies a couple of specific conditions that | |
| 1160 # may be encountered while recovering a log file are handled correctly: | |
| 1161 # | |
| 1162 # wal-18.1.* When the first 32-bits of a frame checksum is correct but | |
| 1163 # the second 32-bits are false, and | |
| 1164 # | |
| 1165 # wal-18.2.* When the page-size field that occurs at the start of a log | |
| 1166 # file is a power of 2 greater than 16384 or smaller than 512. | |
| 1167 # | |
| 1168 forcedelete test.db test.db-wal test.db-journal | |
| 1169 do_test wal-18.0 { | |
| 1170 sqlite3 db test.db | |
| 1171 execsql { | |
| 1172 PRAGMA page_size = 1024; | |
| 1173 PRAGMA auto_vacuum = 0; | |
| 1174 PRAGMA journal_mode = WAL; | |
| 1175 PRAGMA synchronous = OFF; | |
| 1176 | |
| 1177 CREATE TABLE t1(a, b, UNIQUE(a, b)); | |
| 1178 INSERT INTO t1 VALUES(0, 0); | |
| 1179 PRAGMA wal_checkpoint; | |
| 1180 | |
| 1181 INSERT INTO t1 VALUES(1, 2); -- frames 1 and 2 | |
| 1182 INSERT INTO t1 VALUES(3, 4); -- frames 3 and 4 | |
| 1183 INSERT INTO t1 VALUES(5, 6); -- frames 5 and 6 | |
| 1184 } | |
| 1185 | |
| 1186 forcecopy test.db testX.db | |
| 1187 forcecopy test.db-wal testX.db-wal | |
| 1188 db close | |
| 1189 list [file size testX.db] [file size testX.db-wal] | |
| 1190 } [list [expr 3*1024] [wal_file_size 6 1024]] | |
| 1191 | |
| 1192 unset -nocomplain nFrame result | |
| 1193 foreach {nFrame result} { | |
| 1194 0 {0 0} | |
| 1195 1 {0 0} | |
| 1196 2 {0 0 1 2} | |
| 1197 3 {0 0 1 2} | |
| 1198 4 {0 0 1 2 3 4} | |
| 1199 5 {0 0 1 2 3 4} | |
| 1200 6 {0 0 1 2 3 4 5 6} | |
| 1201 } { | |
| 1202 do_test wal-18.1.$nFrame { | |
| 1203 forcecopy testX.db test.db | |
| 1204 forcecopy testX.db-wal test.db-wal | |
| 1205 | |
| 1206 hexio_write test.db-wal [expr 24 + $nFrame*(24+1024) + 20] 00000000 | |
| 1207 | |
| 1208 sqlite3 db test.db | |
| 1209 execsql { | |
| 1210 SELECT * FROM t1; | |
| 1211 PRAGMA integrity_check; | |
| 1212 } | |
| 1213 } [concat $result ok] | |
| 1214 db close | |
| 1215 } | |
| 1216 | |
| 1217 proc randomblob {pgsz} { | |
| 1218 sqlite3 rbdb :memory: | |
| 1219 set blob [rbdb one {SELECT randomblob($pgsz)}] | |
| 1220 rbdb close | |
| 1221 set blob | |
| 1222 } | |
| 1223 | |
| 1224 proc logcksum {ckv1 ckv2 blob} { | |
| 1225 upvar $ckv1 c1 | |
| 1226 upvar $ckv2 c2 | |
| 1227 | |
| 1228 # Since the magic number at the start of the -wal file header is | |
| 1229 # 931071618 that indicates that the content should always be read as | |
| 1230 # little-endian. | |
| 1231 # | |
| 1232 set scanpattern i* | |
| 1233 | |
| 1234 binary scan $blob $scanpattern values | |
| 1235 foreach {v1 v2} $values { | |
| 1236 set c1 [expr {($c1 + $v1 + $c2)&0xFFFFFFFF}] | |
| 1237 set c2 [expr {($c2 + $v2 + $c1)&0xFFFFFFFF}] | |
| 1238 } | |
| 1239 } | |
| 1240 | |
| 1241 forcecopy test.db testX.db | |
| 1242 foreach {tn pgsz works} { | |
| 1243 1 128 0 | |
| 1244 2 256 0 | |
| 1245 3 512 1 | |
| 1246 4 1024 1 | |
| 1247 5 2048 1 | |
| 1248 6 4096 1 | |
| 1249 7 8192 1 | |
| 1250 8 16384 1 | |
| 1251 9 32768 1 | |
| 1252 10 65536 1 | |
| 1253 11 131072 0 | |
| 1254 11 1016 0 | |
| 1255 } { | |
| 1256 | |
| 1257 if {$::SQLITE_MAX_PAGE_SIZE < $pgsz} { | |
| 1258 set works 0 | |
| 1259 } | |
| 1260 | |
| 1261 for {set pg 1} {$pg <= 3} {incr pg} { | |
| 1262 forcecopy testX.db test.db | |
| 1263 forcedelete test.db-wal | |
| 1264 | |
| 1265 # Check that the database now exists and consists of three pages. And | |
| 1266 # that there is no associated wal file. | |
| 1267 # | |
| 1268 do_test wal-18.2.$tn.$pg.1 { file exists test.db-wal } 0 | |
| 1269 do_test wal-18.2.$tn.$pg.2 { file exists test.db } 1 | |
| 1270 do_test wal-18.2.$tn.$pg.3 { file size test.db } [expr 1024*3] | |
| 1271 | |
| 1272 do_test wal-18.2.$tn.$pg.4 { | |
| 1273 | |
| 1274 # Create a wal file that contains a single frame (database page | |
| 1275 # number $pg) with the commit flag set. The frame checksum is | |
| 1276 # correct, but the contents of the database page are corrupt. | |
| 1277 # | |
| 1278 # The page-size in the log file header is set to $pgsz. If the | |
| 1279 # WAL code considers $pgsz to be a valid SQLite database file page-size, | |
| 1280 # the database will be corrupt (because the garbage frame contents | |
| 1281 # will be treated as valid content). If $pgsz is invalid (too small | |
| 1282 # or too large), the db will not be corrupt as the log file will | |
| 1283 # be ignored. | |
| 1284 # | |
| 1285 set walhdr [binary format IIIIII 931071618 3007000 $pgsz 1234 22 23] | |
| 1286 set framebody [randomblob $pgsz] | |
| 1287 set framehdr [binary format IIII $pg 5 22 23] | |
| 1288 set c1 0 | |
| 1289 set c2 0 | |
| 1290 logcksum c1 c2 $walhdr | |
| 1291 | |
| 1292 append walhdr [binary format II $c1 $c2] | |
| 1293 logcksum c1 c2 [string range $framehdr 0 7] | |
| 1294 logcksum c1 c2 $framebody | |
| 1295 set framehdr [binary format IIIIII $pg 5 22 23 $c1 $c2] | |
| 1296 | |
| 1297 set fd [open test.db-wal w] | |
| 1298 fconfigure $fd -encoding binary -translation binary | |
| 1299 puts -nonewline $fd $walhdr | |
| 1300 puts -nonewline $fd $framehdr | |
| 1301 puts -nonewline $fd $framebody | |
| 1302 close $fd | |
| 1303 | |
| 1304 file size test.db-wal | |
| 1305 } [wal_file_size 1 $pgsz] | |
| 1306 | |
| 1307 do_test wal-18.2.$tn.$pg.5 { | |
| 1308 sqlite3 db test.db | |
| 1309 set rc [catch { db one {PRAGMA integrity_check} } msg] | |
| 1310 expr { $rc!=0 || $msg!="ok" } | |
| 1311 } $works | |
| 1312 | |
| 1313 db close | |
| 1314 } | |
| 1315 } | |
| 1316 | |
| 1317 #------------------------------------------------------------------------- | |
| 1318 # The following test - wal-19.* - fixes a bug that was present during | |
| 1319 # development. | |
| 1320 # | |
| 1321 # When a database connection in WAL mode is closed, it attempts an | |
| 1322 # EXCLUSIVE lock on the database file. If the lock is obtained, the | |
| 1323 # connection knows that it is the last connection to disconnect from | |
| 1324 # the database, so it runs a checkpoint operation. The bug was that | |
| 1325 # the connection was not updating its private copy of the wal-index | |
| 1326 # header before doing so, meaning that it could checkpoint an old | |
| 1327 # snapshot. | |
| 1328 # | |
| 1329 do_test wal-19.1 { | |
| 1330 forcedelete test.db test.db-wal test.db-journal | |
| 1331 sqlite3 db test.db | |
| 1332 sqlite3 db2 test.db | |
| 1333 execsql { | |
| 1334 PRAGMA journal_mode = WAL; | |
| 1335 CREATE TABLE t1(a, b); | |
| 1336 INSERT INTO t1 VALUES(1, 2); | |
| 1337 INSERT INTO t1 VALUES(3, 4); | |
| 1338 } | |
| 1339 execsql { SELECT * FROM t1 } db2 | |
| 1340 } {1 2 3 4} | |
| 1341 do_test wal-19.2 { | |
| 1342 execsql { | |
| 1343 INSERT INTO t1 VALUES(5, 6); | |
| 1344 SELECT * FROM t1; | |
| 1345 } | |
| 1346 } {1 2 3 4 5 6} | |
| 1347 do_test wal-19.3 { | |
| 1348 db close | |
| 1349 db2 close | |
| 1350 file exists test.db-wal | |
| 1351 } {0} | |
| 1352 do_test wal-19.4 { | |
| 1353 # When the bug was present, the following was returning {1 2 3 4} only, | |
| 1354 # as [db2] had an out-of-date copy of the wal-index header when it was | |
| 1355 # closed. | |
| 1356 # | |
| 1357 sqlite3 db test.db | |
| 1358 execsql { SELECT * FROM t1 } | |
| 1359 } {1 2 3 4 5 6} | |
| 1360 | |
| 1361 #------------------------------------------------------------------------- | |
| 1362 # This test - wal-20.* - uses two connections. One in this process and | |
| 1363 # the other in an external process. The procedure is: | |
| 1364 # | |
| 1365 # 1. Using connection 1, create the database schema. | |
| 1366 # | |
| 1367 # 2. Using connection 2 (in an external process), add so much | |
| 1368 # data to the database without checkpointing that a wal-index | |
| 1369 # larger than 64KB is required. | |
| 1370 # | |
| 1371 # 3. Using connection 1, checkpoint the database. Make sure all | |
| 1372 # the data is present and the database is not corrupt. | |
| 1373 # | |
| 1374 # At one point, SQLite was failing to grow the mapping of the wal-index | |
| 1375 # file in step 3 and the checkpoint was corrupting the database file. | |
| 1376 # | |
| 1377 do_test wal-20.1 { | |
| 1378 catch {db close} | |
| 1379 forcedelete test.db test.db-wal test.db-journal | |
| 1380 sqlite3 db test.db | |
| 1381 execsql { | |
| 1382 PRAGMA journal_mode = WAL; | |
| 1383 CREATE TABLE t1(x); | |
| 1384 INSERT INTO t1 VALUES(randomblob(900)); | |
| 1385 SELECT count(*) FROM t1; | |
| 1386 } | |
| 1387 } {wal 1} | |
| 1388 do_test wal-20.2 { | |
| 1389 set ::buddy [launch_testfixture] | |
| 1390 testfixture $::buddy { | |
| 1391 sqlite3 db test.db | |
| 1392 db transaction { db eval { | |
| 1393 PRAGMA wal_autocheckpoint = 0; | |
| 1394 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 2 */ | |
| 1395 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 4 */ | |
| 1396 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 8 */ | |
| 1397 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 16 */ | |
| 1398 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 32 */ | |
| 1399 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 64 */ | |
| 1400 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 128 */ | |
| 1401 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 256 */ | |
| 1402 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 512 */ | |
| 1403 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 1024 */ | |
| 1404 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 2048 */ | |
| 1405 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 4096 */ | |
| 1406 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 8192 */ | |
| 1407 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 16384 */ | |
| 1408 } } | |
| 1409 } | |
| 1410 } {0} | |
| 1411 do_test wal-20.3 { | |
| 1412 close $::buddy | |
| 1413 execsql { PRAGMA wal_checkpoint } | |
| 1414 execsql { SELECT count(*) FROM t1 } | |
| 1415 } {16384} | |
| 1416 do_test wal-20.4 { | |
| 1417 db close | |
| 1418 sqlite3 db test.db | |
| 1419 execsql { SELECT count(*) FROM t1 } | |
| 1420 } {16384} | |
| 1421 integrity_check wal-20.5 | |
| 1422 | |
| 1423 catch { db2 close } | |
| 1424 catch { db close } | |
| 1425 | |
| 1426 do_test wal-21.1 { | |
| 1427 faultsim_delete_and_reopen | |
| 1428 execsql { | |
| 1429 PRAGMA journal_mode = WAL; | |
| 1430 CREATE TABLE t1(a, b); | |
| 1431 INSERT INTO t1 VALUES(1, 2); | |
| 1432 INSERT INTO t1 VALUES(3, 4); | |
| 1433 INSERT INTO t1 VALUES(5, 6); | |
| 1434 INSERT INTO t1 VALUES(7, 8); | |
| 1435 INSERT INTO t1 VALUES(9, 10); | |
| 1436 INSERT INTO t1 VALUES(11, 12); | |
| 1437 } | |
| 1438 } {wal} | |
| 1439 do_test wal-21.2 { | |
| 1440 execsql { | |
| 1441 PRAGMA cache_size = 10; | |
| 1442 PRAGMA wal_checkpoint; | |
| 1443 BEGIN; | |
| 1444 SAVEPOINT s; | |
| 1445 INSERT INTO t1 SELECT randomblob(900), randomblob(900) FROM t1; | |
| 1446 ROLLBACK TO s; | |
| 1447 COMMIT; | |
| 1448 } | |
| 1449 execsql { SELECT * FROM t1 } | |
| 1450 } {1 2 3 4 5 6 7 8 9 10 11 12} | |
| 1451 do_test wal-21.3 { | |
| 1452 execsql { PRAGMA integrity_check } | |
| 1453 } {ok} | |
| 1454 | |
| 1455 #------------------------------------------------------------------------- | |
| 1456 # Test reading and writing of databases with different page-sizes. | |
| 1457 # | |
| 1458 foreach pgsz {512 1024 2048 4096 8192 16384 32768 65536} { | |
| 1459 do_multiclient_test tn [string map [list %PGSZ% $pgsz] { | |
| 1460 do_test wal-22.%PGSZ%.$tn.1 { | |
| 1461 sql1 { | |
| 1462 PRAGMA main.page_size = %PGSZ%; | |
| 1463 PRAGMA auto_vacuum = 0; | |
| 1464 PRAGMA journal_mode = WAL; | |
| 1465 CREATE TABLE t1(x UNIQUE); | |
| 1466 INSERT INTO t1 SELECT randomblob(800); | |
| 1467 INSERT INTO t1 SELECT randomblob(800); | |
| 1468 INSERT INTO t1 SELECT randomblob(800); | |
| 1469 } | |
| 1470 } {wal} | |
| 1471 do_test wal-22.%PGSZ%.$tn.2 { sql2 { PRAGMA integrity_check } } {ok} | |
| 1472 do_test wal-22.%PGSZ%.$tn.3 { | |
| 1473 sql1 {PRAGMA wal_checkpoint} | |
| 1474 expr {[file size test.db] % %PGSZ%} | |
| 1475 } {0} | |
| 1476 }] | |
| 1477 } | |
| 1478 | |
| 1479 #------------------------------------------------------------------------- | |
| 1480 # Test that when 1 or more pages are recovered from a WAL file, | |
| 1481 # sqlite3_log() is invoked to report this to the user. | |
| 1482 # | |
| 1483 ifcapable curdir { | |
| 1484 set walfile [file nativename [file join [get_pwd] test.db-wal]] | |
| 1485 } else { | |
| 1486 set walfile test.db-wal | |
| 1487 } | |
| 1488 catch {db close} | |
| 1489 forcedelete test.db | |
| 1490 do_test wal-23.1 { | |
| 1491 faultsim_delete_and_reopen | |
| 1492 execsql { | |
| 1493 CREATE TABLE t1(a, b); | |
| 1494 PRAGMA journal_mode = WAL; | |
| 1495 INSERT INTO t1 VALUES(1, 2); | |
| 1496 INSERT INTO t1 VALUES(3, 4); | |
| 1497 } | |
| 1498 faultsim_save_and_close | |
| 1499 | |
| 1500 sqlite3_shutdown | |
| 1501 test_sqlite3_log [list lappend ::log] | |
| 1502 set ::log [list] | |
| 1503 sqlite3 db test.db | |
| 1504 execsql { SELECT * FROM t1 } | |
| 1505 } {1 2 3 4} | |
| 1506 do_test wal-23.2 { set ::log } {} | |
| 1507 | |
| 1508 do_test wal-23.3 { | |
| 1509 db close | |
| 1510 set ::log [list] | |
| 1511 faultsim_restore_and_reopen | |
| 1512 execsql { SELECT * FROM t1 } | |
| 1513 } {1 2 3 4} | |
| 1514 do_test wal-23.4 { | |
| 1515 set ::log | |
| 1516 } [list SQLITE_NOTICE_RECOVER_WAL \ | |
| 1517 "recovered 2 frames from WAL file $walfile"] | |
| 1518 | |
| 1519 | |
| 1520 ifcapable autovacuum { | |
| 1521 # This block tests that if the size of a database is reduced by a | |
| 1522 # transaction (because of an incremental or auto-vacuum), that no | |
| 1523 # data is written to the WAL file for the truncated pages as part | |
| 1524 # of the commit. e.g. if a transaction reduces the size of a database | |
| 1525 # to N pages, data for page N+1 should not be written to the WAL file | |
| 1526 # when committing the transaction. At one point such data was being | |
| 1527 # written. | |
| 1528 # | |
| 1529 catch {db close} | |
| 1530 forcedelete test.db | |
| 1531 sqlite3 db test.db | |
| 1532 do_execsql_test 24.1 { | |
| 1533 PRAGMA auto_vacuum = 2; | |
| 1534 PRAGMA journal_mode = WAL; | |
| 1535 PRAGMA page_size = 1024; | |
| 1536 CREATE TABLE t1(x); | |
| 1537 INSERT INTO t1 VALUES(randomblob(5000)); | |
| 1538 INSERT INTO t1 SELECT * FROM t1; | |
| 1539 INSERT INTO t1 SELECT * FROM t1; | |
| 1540 INSERT INTO t1 SELECT * FROM t1; | |
| 1541 INSERT INTO t1 SELECT * FROM t1; | |
| 1542 } {wal} | |
| 1543 do_test 24.2 { | |
| 1544 execsql { | |
| 1545 DELETE FROM t1; | |
| 1546 PRAGMA wal_checkpoint; | |
| 1547 } | |
| 1548 db close | |
| 1549 sqlite3 db test.db | |
| 1550 file exists test.db-wal | |
| 1551 } 0 | |
| 1552 do_test 24.3 { | |
| 1553 file size test.db | |
| 1554 } [expr 84 * 1024] | |
| 1555 do_test 24.4 { | |
| 1556 execsql { | |
| 1557 PRAGMA cache_size = 200; | |
| 1558 PRAGMA incremental_vacuum; | |
| 1559 PRAGMA wal_checkpoint; | |
| 1560 } | |
| 1561 file size test.db | |
| 1562 } [expr 3 * 1024] | |
| 1563 | |
| 1564 # WAL file now contains a single frame - the new root page for table t1. | |
| 1565 # It would be two frames (the new root page and a padding frame) if the | |
| 1566 # ZERO_DAMAGE flag were not set. | |
| 1567 do_test 24.5 { | |
| 1568 file size test.db-wal | |
| 1569 } [wal_file_size 1 1024] | |
| 1570 } | |
| 1571 | |
| 1572 db close | |
| 1573 sqlite3_shutdown | |
| 1574 test_sqlite3_log | |
| 1575 sqlite3_initialize | |
| 1576 | |
| 1577 # Make sure PRAGMA journal_mode=WAL works with ATTACHED databases in | |
| 1578 # all journal modes. | |
| 1579 # | |
| 1580 foreach mode {OFF MEMORY PERSIST DELETE TRUNCATE WAL} { | |
| 1581 delete_file test.db test2.db | |
| 1582 sqlite3 db test.db | |
| 1583 do_test wal-25.$mode { | |
| 1584 db eval "PRAGMA journal_mode=$mode" | |
| 1585 db eval {ATTACH 'test2.db' AS t2; PRAGMA journal_mode=WAL;} | |
| 1586 } {wal} | |
| 1587 db close | |
| 1588 } | |
| 1589 | |
| 1590 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
