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