Annotation of embedaddon/sqlite3/test/wal2.test, revision 1.1
1.1 ! misho 1: # 2010 May 5
! 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 wal2
! 23:
! 24: ifcapable !wal {finish_test ; return }
! 25:
! 26: set sqlite_sync_count 0
! 27: proc cond_incr_sync_count {adj} {
! 28: global sqlite_sync_count
! 29: if {$::tcl_platform(platform) == "windows"} {
! 30: incr sqlite_sync_count $adj
! 31: } {
! 32: ifcapable !dirsync {
! 33: incr sqlite_sync_count $adj
! 34: }
! 35: }
! 36: }
! 37:
! 38: proc set_tvfs_hdr {file args} {
! 39:
! 40: # Set $nHdr to the number of bytes in the wal-index header:
! 41: set nHdr 48
! 42: set nInt [expr {$nHdr/4}]
! 43:
! 44: if {[llength $args]>2} {
! 45: error {wrong # args: should be "set_tvfs_hdr fileName ?val1? ?val2?"}
! 46: }
! 47:
! 48: set blob [tvfs shm $file]
! 49:
! 50: if {[llength $args]} {
! 51: set ia [lindex $args 0]
! 52: set ib $ia
! 53: if {[llength $args]==2} {
! 54: set ib [lindex $args 1]
! 55: }
! 56: binary scan $blob a[expr $nHdr*2]a* dummy tail
! 57: set blob [binary format i${nInt}i${nInt}a* $ia $ib $tail]
! 58: tvfs shm $file $blob
! 59: }
! 60:
! 61: binary scan $blob i${nInt} ints
! 62: return $ints
! 63: }
! 64:
! 65: proc incr_tvfs_hdr {file idx incrval} {
! 66: set ints [set_tvfs_hdr $file]
! 67: set v [lindex $ints $idx]
! 68: incr v $incrval
! 69: lset ints $idx $v
! 70: set_tvfs_hdr $file $ints
! 71: }
! 72:
! 73:
! 74: #-------------------------------------------------------------------------
! 75: # Test case wal2-1.*:
! 76: #
! 77: # Set up a small database containing a single table. The database is not
! 78: # checkpointed during the test - all content resides in the log file.
! 79: #
! 80: # Two connections are established to the database file - a writer ([db])
! 81: # and a reader ([db2]). For each of the 8 integer fields in the wal-index
! 82: # header (6 fields and 2 checksum values), do the following:
! 83: #
! 84: # 1. Modify the database using the writer.
! 85: #
! 86: # 2. Attempt to read the database using the reader. Before the reader
! 87: # has a chance to snapshot the wal-index header, increment one
! 88: # of the the integer fields (so that the reader ends up with a corrupted
! 89: # header).
! 90: #
! 91: # 3. Check that the reader recovers the wal-index and reads the correct
! 92: # database content.
! 93: #
! 94: do_test wal2-1.0 {
! 95: proc tvfs_cb {method filename args} {
! 96: set ::filename $filename
! 97: return SQLITE_OK
! 98: }
! 99:
! 100: testvfs tvfs
! 101: tvfs script tvfs_cb
! 102: tvfs filter xShmOpen
! 103:
! 104: sqlite3 db test.db -vfs tvfs
! 105: sqlite3 db2 test.db -vfs tvfs
! 106:
! 107: execsql {
! 108: PRAGMA journal_mode = WAL;
! 109: CREATE TABLE t1(a);
! 110: } db2
! 111: execsql {
! 112: INSERT INTO t1 VALUES(1);
! 113: INSERT INTO t1 VALUES(2);
! 114: INSERT INTO t1 VALUES(3);
! 115: INSERT INTO t1 VALUES(4);
! 116: SELECT count(a), sum(a) FROM t1;
! 117: }
! 118: } {4 10}
! 119: do_test wal2-1.1 {
! 120: execsql { SELECT count(a), sum(a) FROM t1 } db2
! 121: } {4 10}
! 122:
! 123: set RECOVER [list \
! 124: {0 1 lock exclusive} {1 7 lock exclusive} \
! 125: {1 7 unlock exclusive} {0 1 unlock exclusive} \
! 126: ]
! 127: set READ [list \
! 128: {4 1 lock exclusive} {4 1 unlock exclusive} \
! 129: {4 1 lock shared} {4 1 unlock shared} \
! 130: ]
! 131:
! 132: foreach {tn iInsert res wal_index_hdr_mod wal_locks} "
! 133: 2 5 {5 15} 0 {$RECOVER $READ}
! 134: 3 6 {6 21} 1 {$RECOVER $READ}
! 135: 4 7 {7 28} 2 {$RECOVER $READ}
! 136: 5 8 {8 36} 3 {$RECOVER $READ}
! 137: 6 9 {9 45} 4 {$RECOVER $READ}
! 138: 7 10 {10 55} 5 {$RECOVER $READ}
! 139: 8 11 {11 66} 6 {$RECOVER $READ}
! 140: 9 12 {12 78} 7 {$RECOVER $READ}
! 141: 10 13 {13 91} 8 {$RECOVER $READ}
! 142: 11 14 {14 105} 9 {$RECOVER $READ}
! 143: 12 15 {15 120} -1 {$READ}
! 144: " {
! 145:
! 146: do_test wal2-1.$tn.1 {
! 147: execsql { INSERT INTO t1 VALUES($iInsert) }
! 148: set ::locks [list]
! 149: proc tvfs_cb {method args} {
! 150: lappend ::locks [lindex $args 2]
! 151: return SQLITE_OK
! 152: }
! 153: tvfs filter xShmLock
! 154: if {$::wal_index_hdr_mod >= 0} {
! 155: incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
! 156: }
! 157: execsql { SELECT count(a), sum(a) FROM t1 } db2
! 158: } $res
! 159:
! 160: do_test wal2-1.$tn.2 {
! 161: set ::locks
! 162: } $wal_locks
! 163: }
! 164: db close
! 165: db2 close
! 166: tvfs delete
! 167: forcedelete test.db test.db-wal test.db-journal
! 168:
! 169: #-------------------------------------------------------------------------
! 170: # This test case is very similar to the previous one, except, after
! 171: # the reader reads the corrupt wal-index header, but before it has
! 172: # a chance to re-read it under the cover of the RECOVER lock, the
! 173: # wal-index header is replaced with a valid, but out-of-date, header.
! 174: #
! 175: # Because the header checksum looks Ok, the reader does not run recovery,
! 176: # it simply drops back to a READ lock and proceeds. But because the
! 177: # header is out-of-date, the reader reads the out-of-date snapshot.
! 178: #
! 179: # After this, the header is corrupted again and the reader is allowed
! 180: # to run recovery. This time, it sees an up-to-date snapshot of the
! 181: # database file.
! 182: #
! 183: set WRITER [list 0 1 lock exclusive]
! 184: set LOCKS [list \
! 185: {0 1 lock exclusive} {0 1 unlock exclusive} \
! 186: {4 1 lock exclusive} {4 1 unlock exclusive} \
! 187: {4 1 lock shared} {4 1 unlock shared} \
! 188: ]
! 189: do_test wal2-2.0 {
! 190:
! 191: testvfs tvfs
! 192: tvfs script tvfs_cb
! 193: tvfs filter xShmOpen
! 194: proc tvfs_cb {method args} {
! 195: set ::filename [lindex $args 0]
! 196: return SQLITE_OK
! 197: }
! 198:
! 199: sqlite3 db test.db -vfs tvfs
! 200: sqlite3 db2 test.db -vfs tvfs
! 201:
! 202: execsql {
! 203: PRAGMA journal_mode = WAL;
! 204: CREATE TABLE t1(a);
! 205: } db2
! 206: execsql {
! 207: INSERT INTO t1 VALUES(1);
! 208: INSERT INTO t1 VALUES(2);
! 209: INSERT INTO t1 VALUES(3);
! 210: INSERT INTO t1 VALUES(4);
! 211: SELECT count(a), sum(a) FROM t1;
! 212: }
! 213: } {4 10}
! 214: do_test wal2-2.1 {
! 215: execsql { SELECT count(a), sum(a) FROM t1 } db2
! 216: } {4 10}
! 217:
! 218: foreach {tn iInsert res0 res1 wal_index_hdr_mod} {
! 219: 2 5 {4 10} {5 15} 0
! 220: 3 6 {5 15} {6 21} 1
! 221: 4 7 {6 21} {7 28} 2
! 222: 5 8 {7 28} {8 36} 3
! 223: 6 9 {8 36} {9 45} 4
! 224: 7 10 {9 45} {10 55} 5
! 225: 8 11 {10 55} {11 66} 6
! 226: 9 12 {11 66} {12 78} 7
! 227: } {
! 228: tvfs filter xShmLock
! 229:
! 230: do_test wal2-2.$tn.1 {
! 231: set oldhdr [set_tvfs_hdr $::filename]
! 232: execsql { INSERT INTO t1 VALUES($iInsert) }
! 233: execsql { SELECT count(a), sum(a) FROM t1 }
! 234: } $res1
! 235:
! 236: do_test wal2-2.$tn.2 {
! 237: set ::locks [list]
! 238: proc tvfs_cb {method args} {
! 239: set lock [lindex $args 2]
! 240: lappend ::locks $lock
! 241: if {$lock == $::WRITER} {
! 242: set_tvfs_hdr $::filename $::oldhdr
! 243: }
! 244: return SQLITE_OK
! 245: }
! 246:
! 247: if {$::wal_index_hdr_mod >= 0} {
! 248: incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
! 249: }
! 250: execsql { SELECT count(a), sum(a) FROM t1 } db2
! 251: } $res0
! 252:
! 253: do_test wal2-2.$tn.3 {
! 254: set ::locks
! 255: } $LOCKS
! 256:
! 257: do_test wal2-2.$tn.4 {
! 258: set ::locks [list]
! 259: proc tvfs_cb {method args} {
! 260: set lock [lindex $args 2]
! 261: lappend ::locks $lock
! 262: return SQLITE_OK
! 263: }
! 264:
! 265: if {$::wal_index_hdr_mod >= 0} {
! 266: incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
! 267: }
! 268: execsql { SELECT count(a), sum(a) FROM t1 } db2
! 269: } $res1
! 270: }
! 271: db close
! 272: db2 close
! 273: tvfs delete
! 274: forcedelete test.db test.db-wal test.db-journal
! 275:
! 276:
! 277: if 0 {
! 278: #-------------------------------------------------------------------------
! 279: # This test case - wal2-3.* - tests the response of the library to an
! 280: # SQLITE_BUSY when attempting to obtain a READ or RECOVER lock.
! 281: #
! 282: # wal2-3.0 - 2: SQLITE_BUSY when obtaining a READ lock
! 283: # wal2-3.3 - 6: SQLITE_BUSY when obtaining a RECOVER lock
! 284: #
! 285: do_test wal2-3.0 {
! 286: proc tvfs_cb {method args} {
! 287: if {$method == "xShmLock"} {
! 288: if {[info exists ::locked]} { return SQLITE_BUSY }
! 289: }
! 290: return SQLITE_OK
! 291: }
! 292:
! 293: proc busyhandler x {
! 294: if {$x>3} { unset -nocomplain ::locked }
! 295: return 0
! 296: }
! 297:
! 298: testvfs tvfs
! 299: tvfs script tvfs_cb
! 300: sqlite3 db test.db -vfs tvfs
! 301: db busy busyhandler
! 302:
! 303: execsql {
! 304: PRAGMA journal_mode = WAL;
! 305: CREATE TABLE t1(a);
! 306: INSERT INTO t1 VALUES(1);
! 307: INSERT INTO t1 VALUES(2);
! 308: INSERT INTO t1 VALUES(3);
! 309: INSERT INTO t1 VALUES(4);
! 310: }
! 311:
! 312: set ::locked 1
! 313: info exists ::locked
! 314: } {1}
! 315: do_test wal2-3.1 {
! 316: execsql { SELECT count(a), sum(a) FROM t1 }
! 317: } {4 10}
! 318: do_test wal2-3.2 {
! 319: info exists ::locked
! 320: } {0}
! 321:
! 322: do_test wal2-3.3 {
! 323: proc tvfs_cb {method args} {
! 324: if {$method == "xShmLock"} {
! 325: if {[info exists ::sabotage]} {
! 326: unset -nocomplain ::sabotage
! 327: incr_tvfs_hdr [lindex $args 0] 1 1
! 328: }
! 329: if {[info exists ::locked] && [lindex $args 2] == "RECOVER"} {
! 330: return SQLITE_BUSY
! 331: }
! 332: }
! 333: return SQLITE_OK
! 334: }
! 335: set ::sabotage 1
! 336: set ::locked 1
! 337: list [info exists ::sabotage] [info exists ::locked]
! 338: } {1 1}
! 339: do_test wal2-3.4 {
! 340: execsql { SELECT count(a), sum(a) FROM t1 }
! 341: } {4 10}
! 342: do_test wal2-3.5 {
! 343: list [info exists ::sabotage] [info exists ::locked]
! 344: } {0 0}
! 345: db close
! 346: tvfs delete
! 347: forcedelete test.db test.db-wal test.db-journal
! 348:
! 349: }
! 350:
! 351: #-------------------------------------------------------------------------
! 352: # Test that a database connection using a VFS that does not support the
! 353: # xShmXXX interfaces cannot open a WAL database.
! 354: #
! 355: do_test wal2-4.1 {
! 356: sqlite3 db test.db
! 357: execsql {
! 358: PRAGMA auto_vacuum = 0;
! 359: PRAGMA journal_mode = WAL;
! 360: CREATE TABLE data(x);
! 361: INSERT INTO data VALUES('need xShmOpen to see this');
! 362: PRAGMA wal_checkpoint;
! 363: }
! 364: # Three pages in the WAL file at this point: One copy of page 1 and two
! 365: # of the root page for table "data".
! 366: } {wal 0 3 3}
! 367: do_test wal2-4.2 {
! 368: db close
! 369: testvfs tvfs -noshm 1
! 370: sqlite3 db test.db -vfs tvfs
! 371: catchsql { SELECT * FROM data }
! 372: } {1 {unable to open database file}}
! 373: do_test wal2-4.3 {
! 374: db close
! 375: testvfs tvfs
! 376: sqlite3 db test.db -vfs tvfs
! 377: catchsql { SELECT * FROM data }
! 378: } {0 {{need xShmOpen to see this}}}
! 379: db close
! 380: tvfs delete
! 381:
! 382: #-------------------------------------------------------------------------
! 383: # Test that if a database connection is forced to run recovery before it
! 384: # can perform a checkpoint, it does not transition into RECOVER state.
! 385: #
! 386: # UPDATE: This has now changed. When running a checkpoint, if recovery is
! 387: # required the client grabs all exclusive locks (just as it would for a
! 388: # recovery performed as a pre-cursor to a normal database transaction).
! 389: #
! 390: set expected_locks [list]
! 391: lappend expected_locks {1 1 lock exclusive} ;# Lock checkpoint
! 392: lappend expected_locks {0 1 lock exclusive} ;# Lock writer
! 393: lappend expected_locks {2 6 lock exclusive} ;# Lock recovery & all aReadMark[]
! 394: lappend expected_locks {2 6 unlock exclusive} ;# Unlock recovery & aReadMark[]
! 395: lappend expected_locks {0 1 unlock exclusive} ;# Unlock writer
! 396: lappend expected_locks {3 1 lock exclusive} ;# Lock aReadMark[0]
! 397: lappend expected_locks {3 1 unlock exclusive} ;# Unlock aReadMark[0]
! 398: lappend expected_locks {1 1 unlock exclusive} ;# Unlock checkpoint
! 399: do_test wal2-5.1 {
! 400: proc tvfs_cb {method args} {
! 401: set ::shm_file [lindex $args 0]
! 402: if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
! 403: return $::tvfs_cb_return
! 404: }
! 405: set tvfs_cb_return SQLITE_OK
! 406:
! 407: testvfs tvfs
! 408: tvfs script tvfs_cb
! 409:
! 410: sqlite3 db test.db -vfs tvfs
! 411: execsql {
! 412: PRAGMA journal_mode = WAL;
! 413: CREATE TABLE x(y);
! 414: INSERT INTO x VALUES(1);
! 415: }
! 416:
! 417: incr_tvfs_hdr $::shm_file 1 1
! 418: set ::locks [list]
! 419: execsql { PRAGMA wal_checkpoint }
! 420: set ::locks
! 421: } $expected_locks
! 422: db close
! 423: tvfs delete
! 424:
! 425: #-------------------------------------------------------------------------
! 426: # This block, test cases wal2-6.*, tests the operation of WAL with
! 427: # "PRAGMA locking_mode=EXCLUSIVE" set.
! 428: #
! 429: # wal2-6.1.*: Changing to WAL mode before setting locking_mode=exclusive.
! 430: #
! 431: # wal2-6.2.*: Changing to WAL mode after setting locking_mode=exclusive.
! 432: #
! 433: # wal2-6.3.*: Changing back to rollback mode from WAL mode after setting
! 434: # locking_mode=exclusive.
! 435: #
! 436: # wal2-6.4.*: Check that xShmLock calls are omitted in exclusive locking
! 437: # mode.
! 438: #
! 439: # wal2-6.5.*:
! 440: #
! 441: # wal2-6.6.*: Check that if the xShmLock() to reaquire a WAL read-lock when
! 442: # exiting exclusive mode fails (i.e. SQLITE_IOERR), then the
! 443: # connection silently remains in exclusive mode.
! 444: #
! 445: do_test wal2-6.1.1 {
! 446: forcedelete test.db test.db-wal test.db-journal
! 447: sqlite3 db test.db
! 448: execsql {
! 449: Pragma Journal_Mode = Wal;
! 450: }
! 451: } {wal}
! 452: do_test wal2-6.1.2 {
! 453: execsql { PRAGMA lock_status }
! 454: } {main unlocked temp closed}
! 455: do_test wal2-6.1.3 {
! 456: execsql {
! 457: SELECT * FROM sqlite_master;
! 458: Pragma Locking_Mode = Exclusive;
! 459: }
! 460: execsql {
! 461: BEGIN;
! 462: CREATE TABLE t1(a, b);
! 463: INSERT INTO t1 VALUES(1, 2);
! 464: COMMIT;
! 465: PRAGMA lock_status;
! 466: }
! 467: } {main exclusive temp closed}
! 468: do_test wal2-6.1.4 {
! 469: execsql {
! 470: PRAGMA locking_mode = normal;
! 471: PRAGMA lock_status;
! 472: }
! 473: } {normal main exclusive temp closed}
! 474: do_test wal2-6.1.5 {
! 475: execsql {
! 476: SELECT * FROM t1;
! 477: PRAGMA lock_status;
! 478: }
! 479: } {1 2 main shared temp closed}
! 480: do_test wal2-6.1.6 {
! 481: execsql {
! 482: INSERT INTO t1 VALUES(3, 4);
! 483: PRAGMA lock_status;
! 484: }
! 485: } {main shared temp closed}
! 486: db close
! 487:
! 488: do_test wal2-6.2.1 {
! 489: forcedelete test.db test.db-wal test.db-journal
! 490: sqlite3 db test.db
! 491: execsql {
! 492: Pragma Locking_Mode = Exclusive;
! 493: Pragma Journal_Mode = Wal;
! 494: Pragma Lock_Status;
! 495: }
! 496: } {exclusive wal main exclusive temp closed}
! 497: do_test wal2-6.2.2 {
! 498: execsql {
! 499: BEGIN;
! 500: CREATE TABLE t1(a, b);
! 501: INSERT INTO t1 VALUES(1, 2);
! 502: COMMIT;
! 503: Pragma loCK_STATus;
! 504: }
! 505: } {main exclusive temp closed}
! 506: do_test wal2-6.2.3 {
! 507: db close
! 508: sqlite3 db test.db
! 509: execsql { SELECT * FROM sqlite_master }
! 510: execsql { PRAGMA LOCKING_MODE = EXCLUSIVE }
! 511: } {exclusive}
! 512: do_test wal2-6.2.4 {
! 513: execsql {
! 514: SELECT * FROM t1;
! 515: pragma lock_status;
! 516: }
! 517: } {1 2 main shared temp closed}
! 518: do_test wal2-6.2.5 {
! 519: execsql {
! 520: INSERT INTO t1 VALUES(3, 4);
! 521: pragma lock_status;
! 522: }
! 523: } {main exclusive temp closed}
! 524: do_test wal2-6.2.6 {
! 525: execsql {
! 526: PRAGMA locking_mode = NORMAL;
! 527: pragma lock_status;
! 528: }
! 529: } {normal main exclusive temp closed}
! 530: do_test wal2-6.2.7 {
! 531: execsql {
! 532: BEGIN IMMEDIATE; COMMIT;
! 533: pragma lock_status;
! 534: }
! 535: } {main shared temp closed}
! 536: do_test wal2-6.2.8 {
! 537: execsql {
! 538: PRAGMA locking_mode = EXCLUSIVE;
! 539: BEGIN IMMEDIATE; COMMIT;
! 540: PRAGMA locking_mode = NORMAL;
! 541: }
! 542: execsql {
! 543: SELECT * FROM t1;
! 544: pragma lock_status;
! 545: }
! 546: } {1 2 3 4 main shared temp closed}
! 547: do_test wal2-6.2.9 {
! 548: execsql {
! 549: INSERT INTO t1 VALUES(5, 6);
! 550: SELECT * FROM t1;
! 551: pragma lock_status;
! 552: }
! 553: } {1 2 3 4 5 6 main shared temp closed}
! 554: db close
! 555:
! 556: do_test wal2-6.3.1 {
! 557: forcedelete test.db test.db-wal test.db-journal
! 558: sqlite3 db test.db
! 559: execsql {
! 560: PRAGMA journal_mode = WAL;
! 561: PRAGMA locking_mode = exclusive;
! 562: BEGIN;
! 563: CREATE TABLE t1(x);
! 564: INSERT INTO t1 VALUES('Chico');
! 565: INSERT INTO t1 VALUES('Harpo');
! 566: COMMIT;
! 567: }
! 568: list [file exists test.db-wal] [file exists test.db-journal]
! 569: } {1 0}
! 570: do_test wal2-6.3.2 {
! 571: execsql { PRAGMA journal_mode = DELETE }
! 572: file exists test.db-wal
! 573: } {0}
! 574: do_test wal2-6.3.3 {
! 575: execsql { PRAGMA lock_status }
! 576: } {main exclusive temp closed}
! 577: do_test wal2-6.3.4 {
! 578: execsql {
! 579: BEGIN;
! 580: INSERT INTO t1 VALUES('Groucho');
! 581: }
! 582: list [file exists test.db-wal] [file exists test.db-journal]
! 583: } {0 1}
! 584: do_test wal2-6.3.5 {
! 585: execsql { PRAGMA lock_status }
! 586: } {main exclusive temp closed}
! 587: do_test wal2-6.3.6 {
! 588: execsql { COMMIT }
! 589: list [file exists test.db-wal] [file exists test.db-journal]
! 590: } {0 1}
! 591: do_test wal2-6.3.7 {
! 592: execsql { PRAGMA lock_status }
! 593: } {main exclusive temp closed}
! 594: db close
! 595:
! 596:
! 597: # This test - wal2-6.4.* - uses a single database connection and the
! 598: # [testvfs] instrumentation to test that xShmLock() is being called
! 599: # as expected when a WAL database is used with locking_mode=exclusive.
! 600: #
! 601: do_test wal2-6.4.1 {
! 602: forcedelete test.db test.db-wal test.db-journal
! 603: proc tvfs_cb {method args} {
! 604: set ::shm_file [lindex $args 0]
! 605: if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
! 606: return "SQLITE_OK"
! 607: }
! 608: testvfs tvfs
! 609: tvfs script tvfs_cb
! 610: sqlite3 db test.db -vfs tvfs
! 611: set {} {}
! 612: } {}
! 613:
! 614: set RECOVERY {
! 615: {0 1 lock exclusive} {1 7 lock exclusive}
! 616: {1 7 unlock exclusive} {0 1 unlock exclusive}
! 617: }
! 618: set READMARK0_READ {
! 619: {3 1 lock shared} {3 1 unlock shared}
! 620: }
! 621: set READMARK0_WRITE {
! 622: {3 1 lock shared}
! 623: {0 1 lock exclusive} {3 1 unlock shared}
! 624: {4 1 lock exclusive} {4 1 unlock exclusive} {4 1 lock shared}
! 625: {0 1 unlock exclusive} {4 1 unlock shared}
! 626: }
! 627: set READMARK1_SET {
! 628: {4 1 lock exclusive} {4 1 unlock exclusive}
! 629: }
! 630: set READMARK1_READ {
! 631: {4 1 lock shared} {4 1 unlock shared}
! 632: }
! 633: set READMARK1_WRITE {
! 634: {4 1 lock shared}
! 635: {0 1 lock exclusive} {0 1 unlock exclusive}
! 636: {4 1 unlock shared}
! 637: }
! 638:
! 639: foreach {tn sql res expected_locks} {
! 640: 2 {
! 641: PRAGMA auto_vacuum = 0;
! 642: PRAGMA journal_mode = WAL;
! 643: BEGIN;
! 644: CREATE TABLE t1(x);
! 645: INSERT INTO t1 VALUES('Leonard');
! 646: INSERT INTO t1 VALUES('Arthur');
! 647: COMMIT;
! 648: } {wal} {
! 649: $RECOVERY
! 650: $READMARK0_WRITE
! 651: }
! 652:
! 653: 3 {
! 654: # This test should do the READMARK1_SET locking to populate the
! 655: # aReadMark[1] slot with the current mxFrame value. Followed by
! 656: # READMARK1_READ to read the database.
! 657: #
! 658: SELECT * FROM t1
! 659: } {Leonard Arthur} {
! 660: $READMARK1_SET
! 661: $READMARK1_READ
! 662: }
! 663:
! 664: 4 {
! 665: # aReadMark[1] is already set to mxFrame. So just READMARK1_READ
! 666: # this time, not READMARK1_SET.
! 667: #
! 668: SELECT * FROM t1 ORDER BY x
! 669: } {Arthur Leonard} {
! 670: $READMARK1_READ
! 671: }
! 672:
! 673: 5 {
! 674: PRAGMA locking_mode = exclusive
! 675: } {exclusive} { }
! 676:
! 677: 6 {
! 678: INSERT INTO t1 VALUES('Julius Henry');
! 679: SELECT * FROM t1;
! 680: } {Leonard Arthur {Julius Henry}} {
! 681: $READMARK1_READ
! 682: }
! 683:
! 684: 7 {
! 685: INSERT INTO t1 VALUES('Karl');
! 686: SELECT * FROM t1;
! 687: } {Leonard Arthur {Julius Henry} Karl} { }
! 688:
! 689: 8 {
! 690: PRAGMA locking_mode = normal
! 691: } {normal} { }
! 692:
! 693: 9 {
! 694: SELECT * FROM t1 ORDER BY x
! 695: } {Arthur {Julius Henry} Karl Leonard} $READMARK1_READ
! 696:
! 697: 10 { DELETE FROM t1 } {} $READMARK1_WRITE
! 698:
! 699: 11 {
! 700: SELECT * FROM t1
! 701: } {} {
! 702: $READMARK1_SET
! 703: $READMARK1_READ
! 704: }
! 705: } {
! 706:
! 707: set L [list]
! 708: foreach el [subst $expected_locks] { lappend L $el }
! 709:
! 710: set S ""
! 711: foreach sq [split $sql "\n"] {
! 712: set sq [string trim $sq]
! 713: if {[string match {#*} $sq]==0} {append S "$sq\n"}
! 714: }
! 715:
! 716: set ::locks [list]
! 717: do_test wal2-6.4.$tn.1 { execsql $S } $res
! 718: do_test wal2-6.4.$tn.2 { set ::locks } $L
! 719: }
! 720:
! 721: db close
! 722: tvfs delete
! 723:
! 724: do_test wal2-6.5.1 {
! 725: sqlite3 db test.db
! 726: execsql {
! 727: PRAGMA auto_vacuum = 0;
! 728: PRAGMA journal_mode = wal;
! 729: PRAGMA locking_mode = exclusive;
! 730: CREATE TABLE t2(a, b);
! 731: PRAGMA wal_checkpoint;
! 732: INSERT INTO t2 VALUES('I', 'II');
! 733: PRAGMA journal_mode;
! 734: }
! 735: } {wal exclusive 0 2 2 wal}
! 736: do_test wal2-6.5.2 {
! 737: execsql {
! 738: PRAGMA locking_mode = normal;
! 739: INSERT INTO t2 VALUES('III', 'IV');
! 740: PRAGMA locking_mode = exclusive;
! 741: SELECT * FROM t2;
! 742: }
! 743: } {normal exclusive I II III IV}
! 744: do_test wal2-6.5.3 {
! 745: execsql { PRAGMA wal_checkpoint }
! 746: } {0 2 2}
! 747: db close
! 748:
! 749: proc lock_control {method filename handle spec} {
! 750: foreach {start n op type} $spec break
! 751: if {$op == "lock"} { return SQLITE_IOERR }
! 752: return SQLITE_OK
! 753: }
! 754: do_test wal2-6.6.1 {
! 755: testvfs T
! 756: T script lock_control
! 757: T filter {}
! 758: sqlite3 db test.db -vfs T
! 759: execsql { SELECT * FROM sqlite_master }
! 760: execsql { PRAGMA locking_mode = exclusive }
! 761: execsql { INSERT INTO t2 VALUES('V', 'VI') }
! 762: } {}
! 763: do_test wal2-6.6.2 {
! 764: execsql { PRAGMA locking_mode = normal }
! 765: T filter xShmLock
! 766: execsql { INSERT INTO t2 VALUES('VII', 'VIII') }
! 767: } {}
! 768: do_test wal2-6.6.3 {
! 769: # At this point the connection should still be in exclusive-mode, even
! 770: # though it tried to exit exclusive-mode when committing the INSERT
! 771: # statement above. To exit exclusive mode, SQLite has to take a read-lock
! 772: # on the WAL file using xShmLock(). Since that call failed, it remains
! 773: # in exclusive mode.
! 774: #
! 775: sqlite3 db2 test.db -vfs T
! 776: catchsql { SELECT * FROM t2 } db2
! 777: } {1 {database is locked}}
! 778: do_test wal2-6.6.2 {
! 779: db2 close
! 780: T filter {}
! 781: execsql { INSERT INTO t2 VALUES('IX', 'X') }
! 782: } {}
! 783: do_test wal2-6.6.4 {
! 784: # This time, we have successfully exited exclusive mode. So the second
! 785: # connection can read the database.
! 786: sqlite3 db2 test.db -vfs T
! 787: catchsql { SELECT * FROM t2 } db2
! 788: } {0 {I II III IV V VI VII VIII IX X}}
! 789:
! 790: db close
! 791: db2 close
! 792: T delete
! 793:
! 794: #-------------------------------------------------------------------------
! 795: # Test a theory about the checksum algorithm. Theory was false and this
! 796: # test did not provoke a bug.
! 797: #
! 798: forcedelete test.db test.db-wal test.db-journal
! 799: do_test wal2-7.1.1 {
! 800: sqlite3 db test.db
! 801: execsql {
! 802: PRAGMA page_size = 4096;
! 803: PRAGMA journal_mode = WAL;
! 804: CREATE TABLE t1(a, b);
! 805: }
! 806: file size test.db
! 807: } {4096}
! 808: do_test wal2-7.1.2 {
! 809: forcecopy test.db test2.db
! 810: forcecopy test.db-wal test2.db-wal
! 811: hexio_write test2.db-wal 48 FF
! 812: } {1}
! 813: do_test wal2-7.1.3 {
! 814: sqlite3 db2 test2.db
! 815: execsql { PRAGMA wal_checkpoint } db2
! 816: execsql { SELECT * FROM sqlite_master } db2
! 817: } {}
! 818: db close
! 819: db2 close
! 820: forcedelete test.db test.db-wal test.db-journal
! 821: do_test wal2-8.1.2 {
! 822: sqlite3 db test.db
! 823: execsql {
! 824: PRAGMA auto_vacuum=OFF;
! 825: PRAGMA page_size = 1024;
! 826: PRAGMA journal_mode = WAL;
! 827: CREATE TABLE t1(x);
! 828: INSERT INTO t1 VALUES(zeroblob(8188*1020));
! 829: CREATE TABLE t2(y);
! 830: PRAGMA wal_checkpoint;
! 831: }
! 832: execsql {
! 833: SELECT rootpage>=8192 FROM sqlite_master WHERE tbl_name = 't2';
! 834: }
! 835: } {1}
! 836: do_test wal2-8.1.3 {
! 837: execsql {
! 838: PRAGMA cache_size = 10;
! 839: CREATE TABLE t3(z);
! 840: BEGIN;
! 841: INSERT INTO t3 VALUES(randomblob(900));
! 842: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 843: INSERT INTO t2 VALUES('hello');
! 844: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 845: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 846: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 847: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 848: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 849: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 850: ROLLBACK;
! 851: }
! 852: execsql {
! 853: INSERT INTO t2 VALUES('goodbye');
! 854: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 855: INSERT INTO t3 SELECT randomblob(900) FROM t3;
! 856: }
! 857: } {}
! 858: do_test wal2-8.1.4 {
! 859: sqlite3 db2 test.db
! 860: execsql { SELECT * FROM t2 }
! 861: } {goodbye}
! 862: db2 close
! 863: db close
! 864:
! 865: #-------------------------------------------------------------------------
! 866: # Test that even if the checksums for both are valid, if the two copies
! 867: # of the wal-index header in the wal-index do not match, the client
! 868: # runs (or at least tries to run) database recovery.
! 869: #
! 870: #
! 871: proc get_name {method args} { set ::filename [lindex $args 0] ; tvfs filter {} }
! 872: testvfs tvfs
! 873: tvfs script get_name
! 874: tvfs filter xShmOpen
! 875:
! 876: forcedelete test.db test.db-wal test.db-journal
! 877: do_test wal2-9.1 {
! 878: sqlite3 db test.db -vfs tvfs
! 879: execsql {
! 880: PRAGMA journal_mode = WAL;
! 881: CREATE TABLE x(y);
! 882: INSERT INTO x VALUES('Barton');
! 883: INSERT INTO x VALUES('Deakin');
! 884: }
! 885:
! 886: # Set $wih(1) to the contents of the wal-index header after
! 887: # the frames associated with the first two rows in table 'x' have
! 888: # been inserted. Then insert one more row and set $wih(2)
! 889: # to the new value of the wal-index header.
! 890: #
! 891: # If the $wih(1) is written into the wal-index before running
! 892: # a read operation, the client will see only the first two rows. If
! 893: # $wih(2) is written into the wal-index, the client will see
! 894: # three rows. If an invalid header is written into the wal-index, then
! 895: # the client will run recovery and see three rows.
! 896: #
! 897: set wih(1) [set_tvfs_hdr $::filename]
! 898: execsql { INSERT INTO x VALUES('Watson') }
! 899: set wih(2) [set_tvfs_hdr $::filename]
! 900:
! 901: sqlite3 db2 test.db -vfs tvfs
! 902: execsql { SELECT * FROM x } db2
! 903: } {Barton Deakin Watson}
! 904:
! 905: foreach {tn hdr1 hdr2 res} [list \
! 906: 3 $wih(1) $wih(1) {Barton Deakin} \
! 907: 4 $wih(1) $wih(2) {Barton Deakin Watson} \
! 908: 5 $wih(2) $wih(1) {Barton Deakin Watson} \
! 909: 6 $wih(2) $wih(2) {Barton Deakin Watson} \
! 910: 7 $wih(1) $wih(1) {Barton Deakin} \
! 911: 8 {0 0 0 0 0 0 0 0 0 0 0 0} {0 0 0 0 0 0 0 0 0 0 0 0} {Barton Deakin Watson}
! 912: ] {
! 913: do_test wal2-9.$tn {
! 914: set_tvfs_hdr $::filename $hdr1 $hdr2
! 915: execsql { SELECT * FROM x } db2
! 916: } $res
! 917: }
! 918:
! 919: db2 close
! 920: db close
! 921:
! 922: #-------------------------------------------------------------------------
! 923: # This block of tests - wal2-10.* - focus on the libraries response to
! 924: # new versions of the wal or wal-index formats.
! 925: #
! 926: # wal2-10.1.*: Test that the library refuses to "recover" a new WAL
! 927: # format.
! 928: #
! 929: # wal2-10.2.*: Test that the library refuses to read or write a database
! 930: # if the wal-index version is newer than it understands.
! 931: #
! 932: # At time of writing, the only versions of the wal and wal-index formats
! 933: # that exist are versions 3007000 (corresponding to SQLite version 3.7.0,
! 934: # the first version of SQLite to feature wal mode).
! 935: #
! 936: do_test wal2-10.1.1 {
! 937: faultsim_delete_and_reopen
! 938: execsql {
! 939: PRAGMA journal_mode = WAL;
! 940: CREATE TABLE t1(a, b);
! 941: PRAGMA wal_checkpoint;
! 942: INSERT INTO t1 VALUES(1, 2);
! 943: INSERT INTO t1 VALUES(3, 4);
! 944: }
! 945: faultsim_save_and_close
! 946: } {}
! 947: do_test wal2-10.1.2 {
! 948: faultsim_restore_and_reopen
! 949: execsql { SELECT * FROM t1 }
! 950: } {1 2 3 4}
! 951: do_test wal2-10.1.3 {
! 952: faultsim_restore_and_reopen
! 953: set hdr [wal_set_walhdr test.db-wal]
! 954: lindex $hdr 1
! 955: } {3007000}
! 956: do_test wal2-10.1.4 {
! 957: lset hdr 1 3007001
! 958: wal_set_walhdr test.db-wal $hdr
! 959: catchsql { SELECT * FROM t1 }
! 960: } {1 {unable to open database file}}
! 961:
! 962: testvfs tvfs -default 1
! 963: do_test wal2-10.2.1 {
! 964: faultsim_restore_and_reopen
! 965: execsql { SELECT * FROM t1 }
! 966: } {1 2 3 4}
! 967: do_test wal2-10.2.2 {
! 968: set hdr [set_tvfs_hdr $::filename]
! 969: lindex $hdr 0
! 970: } {3007000}
! 971: do_test wal2-10.2.3 {
! 972: lset hdr 0 3007001
! 973: wal_fix_walindex_cksum hdr
! 974: set_tvfs_hdr $::filename $hdr
! 975: catchsql { SELECT * FROM t1 }
! 976: } {1 {unable to open database file}}
! 977: db close
! 978: tvfs delete
! 979:
! 980: #-------------------------------------------------------------------------
! 981: # This block of tests - wal2-11.* - tests that it is not possible to put
! 982: # the library into an infinite loop by presenting it with a corrupt
! 983: # hash table (one that appears to contain a single chain of infinite
! 984: # length).
! 985: #
! 986: # wal2-11.1.*: While reading the hash-table.
! 987: #
! 988: # wal2-11.2.*: While writing the hash-table.
! 989: #
! 990: testvfs tvfs -default 1
! 991: do_test wal2-11.0 {
! 992: faultsim_delete_and_reopen
! 993: execsql {
! 994: PRAGMA journal_mode = WAL;
! 995: CREATE TABLE t1(a, b, c);
! 996: INSERT INTO t1 VALUES(1, 2, 3);
! 997: INSERT INTO t1 VALUES(4, 5, 6);
! 998: INSERT INTO t1 VALUES(7, 8, 9);
! 999: SELECT * FROM t1;
! 1000: }
! 1001: } {wal 1 2 3 4 5 6 7 8 9}
! 1002:
! 1003: do_test wal2-11.1.1 {
! 1004: sqlite3 db2 test.db
! 1005: execsql { SELECT name FROM sqlite_master } db2
! 1006: } {t1}
! 1007:
! 1008: if {$::tcl_version>=8.5} {
! 1009: # Set all zeroed slots in the first hash table to invalid values.
! 1010: #
! 1011: set blob [string range [tvfs shm $::filename] 0 16383]
! 1012: set I [string range [tvfs shm $::filename] 16384 end]
! 1013: binary scan $I t* L
! 1014: set I [list]
! 1015: foreach p $L {
! 1016: lappend I [expr $p ? $p : 400]
! 1017: }
! 1018: append blob [binary format t* $I]
! 1019: tvfs shm $::filename $blob
! 1020: do_test wal2-11.2 {
! 1021: catchsql { INSERT INTO t1 VALUES(10, 11, 12) }
! 1022: } {1 {database disk image is malformed}}
! 1023:
! 1024: # Fill up the hash table on the first page of shared memory with 0x55 bytes.
! 1025: #
! 1026: set blob [string range [tvfs shm $::filename] 0 16383]
! 1027: append blob [string repeat [binary format c 55] 16384]
! 1028: tvfs shm $::filename $blob
! 1029: do_test wal2-11.3 {
! 1030: catchsql { SELECT * FROM t1 } db2
! 1031: } {1 {database disk image is malformed}}
! 1032: }
! 1033:
! 1034: db close
! 1035: db2 close
! 1036: tvfs delete
! 1037:
! 1038: #-------------------------------------------------------------------------
! 1039: # If a connection is required to create a WAL or SHM file, it creates
! 1040: # the new files with the same file-system permissions as the database
! 1041: # file itself. Test this.
! 1042: #
! 1043: if {$::tcl_platform(platform) == "unix"} {
! 1044: faultsim_delete_and_reopen
! 1045: set umask [exec /bin/sh -c umask]
! 1046:
! 1047: do_test wal2-12.1 {
! 1048: sqlite3 db test.db
! 1049: execsql {
! 1050: CREATE TABLE tx(y, z);
! 1051: PRAGMA journal_mode = WAL;
! 1052: }
! 1053: db close
! 1054: list [file exists test.db-wal] [file exists test.db-shm]
! 1055: } {0 0}
! 1056:
! 1057: foreach {tn permissions} {
! 1058: 1 00644
! 1059: 2 00666
! 1060: 3 00600
! 1061: 4 00755
! 1062: } {
! 1063: set effective [format %.5o [expr $permissions & ~$umask]]
! 1064: do_test wal2-12.2.$tn.1 {
! 1065: file attributes test.db -permissions $permissions
! 1066: file attributes test.db -permissions
! 1067: } $permissions
! 1068: do_test wal2-12.2.$tn.2 {
! 1069: list [file exists test.db-wal] [file exists test.db-shm]
! 1070: } {0 0}
! 1071: do_test wal2-12.2.$tn.3 {
! 1072: sqlite3 db test.db
! 1073: execsql { INSERT INTO tx DEFAULT VALUES }
! 1074: list [file exists test.db-wal] [file exists test.db-shm]
! 1075: } {1 1}
! 1076: do_test wal2-12.2.$tn.4 {
! 1077: list [file attr test.db-wal -perm] [file attr test.db-shm -perm]
! 1078: } [list $effective $effective]
! 1079: do_test wal2-12.2.$tn.5 {
! 1080: db close
! 1081: list [file exists test.db-wal] [file exists test.db-shm]
! 1082: } {0 0}
! 1083: }
! 1084: }
! 1085:
! 1086: #-------------------------------------------------------------------------
! 1087: # Test the libraries response to discovering that one or more of the
! 1088: # database, wal or shm files cannot be opened, or can only be opened
! 1089: # read-only.
! 1090: #
! 1091: if {$::tcl_platform(platform) == "unix"} {
! 1092: proc perm {} {
! 1093: set L [list]
! 1094: foreach f {test.db test.db-wal test.db-shm} {
! 1095: if {[file exists $f]} {
! 1096: lappend L [file attr $f -perm]
! 1097: } else {
! 1098: lappend L {}
! 1099: }
! 1100: }
! 1101: set L
! 1102: }
! 1103:
! 1104: faultsim_delete_and_reopen
! 1105: execsql {
! 1106: PRAGMA journal_mode = WAL;
! 1107: CREATE TABLE t1(a, b);
! 1108: PRAGMA wal_checkpoint;
! 1109: INSERT INTO t1 VALUES('3.14', '2.72');
! 1110: }
! 1111: do_test wal2-13.1.1 {
! 1112: list [file exists test.db-shm] [file exists test.db-wal]
! 1113: } {1 1}
! 1114: faultsim_save_and_close
! 1115:
! 1116: foreach {tn db_perm wal_perm shm_perm can_open can_read can_write} {
! 1117: 2 00644 00644 00644 1 1 1
! 1118: 3 00644 00400 00644 1 1 0
! 1119: 4 00644 00644 00400 1 0 0
! 1120: 5 00400 00644 00644 1 1 0
! 1121:
! 1122: 7 00644 00000 00644 1 0 0
! 1123: 8 00644 00644 00000 1 0 0
! 1124: 9 00000 00644 00644 0 0 0
! 1125: } {
! 1126: faultsim_restore
! 1127: do_test wal2-13.$tn.1 {
! 1128: file attr test.db -perm $db_perm
! 1129: file attr test.db-wal -perm $wal_perm
! 1130: file attr test.db-shm -perm $shm_perm
! 1131:
! 1132: set L [file attr test.db -perm]
! 1133: lappend L [file attr test.db-wal -perm]
! 1134: lappend L [file attr test.db-shm -perm]
! 1135: } [list $db_perm $wal_perm $shm_perm]
! 1136:
! 1137: # If $can_open is true, then it should be possible to open a database
! 1138: # handle. Otherwise, if $can_open is 0, attempting to open the db
! 1139: # handle throws an "unable to open database file" exception.
! 1140: #
! 1141: set r(1) {0 ok}
! 1142: set r(0) {1 {unable to open database file}}
! 1143: do_test wal2-13.$tn.2 {
! 1144: list [catch {sqlite3 db test.db ; set {} ok} msg] $msg
! 1145: } $r($can_open)
! 1146:
! 1147: if {$can_open} {
! 1148:
! 1149: # If $can_read is true, then the client should be able to read from
! 1150: # the database file. If $can_read is false, attempting to read should
! 1151: # throw the "unable to open database file" exception.
! 1152: #
! 1153: set a(0) {1 {unable to open database file}}
! 1154: set a(1) {0 {3.14 2.72}}
! 1155: do_test wal2-13.$tn.3 {
! 1156: catchsql { SELECT * FROM t1 }
! 1157: } $a($can_read)
! 1158:
! 1159: # Now try to write to the db file. If the client can read but not
! 1160: # write, then it should throw the familiar "unable to open db file"
! 1161: # exception. If it can read but not write, the exception should
! 1162: # be "attempt to write a read only database".
! 1163: #
! 1164: # If the client can read and write, the operation should succeed.
! 1165: #
! 1166: set b(0,0) {1 {unable to open database file}}
! 1167: set b(1,0) {1 {attempt to write a readonly database}}
! 1168: set b(1,1) {0 {}}
! 1169: do_test wal2-13.$tn.4 {
! 1170: catchsql { INSERT INTO t1 DEFAULT VALUES }
! 1171: } $b($can_read,$can_write)
! 1172: }
! 1173: catch { db close }
! 1174: }
! 1175: }
! 1176:
! 1177: #-------------------------------------------------------------------------
! 1178: # Test that "PRAGMA checkpoint_fullsync" appears to be working.
! 1179: #
! 1180: foreach {tn sql reslist} {
! 1181: 1 { } {10 0 4 0 6 0}
! 1182: 2 { PRAGMA checkpoint_fullfsync = 1 } {10 4 4 2 6 2}
! 1183: 3 { PRAGMA checkpoint_fullfsync = 0 } {10 0 4 0 6 0}
! 1184: } {
! 1185: faultsim_delete_and_reopen
! 1186:
! 1187: execsql {PRAGMA auto_vacuum = 0}
! 1188: execsql $sql
! 1189: do_execsql_test wal2-14.$tn.0 { PRAGMA page_size = 4096 } {}
! 1190: do_execsql_test wal2-14.$tn.1 { PRAGMA journal_mode = WAL } {wal}
! 1191:
! 1192: set sqlite_sync_count 0
! 1193: set sqlite_fullsync_count 0
! 1194:
! 1195: do_execsql_test wal2-14.$tn.2 {
! 1196: PRAGMA wal_autocheckpoint = 10;
! 1197: CREATE TABLE t1(a, b); -- 2 wal syncs
! 1198: INSERT INTO t1 VALUES(1, 2); -- 2 wal sync
! 1199: PRAGMA wal_checkpoint; -- 1 wal sync, 1 db sync
! 1200: BEGIN;
! 1201: INSERT INTO t1 VALUES(3, 4);
! 1202: INSERT INTO t1 VALUES(5, 6);
! 1203: COMMIT; -- 2 wal sync
! 1204: PRAGMA wal_checkpoint; -- 1 wal sync, 1 db sync
! 1205: } {10 0 3 3 0 1 1}
! 1206:
! 1207: do_test wal2-14.$tn.3 {
! 1208: cond_incr_sync_count 1
! 1209: list $sqlite_sync_count $sqlite_fullsync_count
! 1210: } [lrange $reslist 0 1]
! 1211:
! 1212: set sqlite_sync_count 0
! 1213: set sqlite_fullsync_count 0
! 1214:
! 1215: do_test wal2-14.$tn.4 {
! 1216: execsql { INSERT INTO t1 VALUES(7, zeroblob(12*4096)) }
! 1217: list $sqlite_sync_count $sqlite_fullsync_count
! 1218: } [lrange $reslist 2 3]
! 1219:
! 1220: set sqlite_sync_count 0
! 1221: set sqlite_fullsync_count 0
! 1222:
! 1223: do_test wal2-14.$tn.5 {
! 1224: execsql { PRAGMA wal_autocheckpoint = 1000 }
! 1225: execsql { INSERT INTO t1 VALUES(9, 10) }
! 1226: execsql { INSERT INTO t1 VALUES(11, 12) }
! 1227: execsql { INSERT INTO t1 VALUES(13, 14) }
! 1228: db close
! 1229: list $sqlite_sync_count $sqlite_fullsync_count
! 1230: } [lrange $reslist 4 5]
! 1231: }
! 1232:
! 1233: catch { db close }
! 1234:
! 1235: # PRAGMA checkpoint_fullsync
! 1236: # PRAGMA fullfsync
! 1237: # PRAGMA synchronous
! 1238: #
! 1239: foreach {tn settings restart_sync commit_sync ckpt_sync} {
! 1240: 1 {0 0 off} {0 0} {0 0} {0 0}
! 1241: 2 {0 0 normal} {1 0} {0 0} {2 0}
! 1242: 3 {0 0 full} {2 0} {1 0} {2 0}
! 1243:
! 1244: 4 {0 1 off} {0 0} {0 0} {0 0}
! 1245: 5 {0 1 normal} {0 1} {0 0} {0 2}
! 1246: 6 {0 1 full} {0 2} {0 1} {0 2}
! 1247:
! 1248: 7 {1 0 off} {0 0} {0 0} {0 0}
! 1249: 8 {1 0 normal} {1 0} {0 0} {0 2}
! 1250: 9 {1 0 full} {2 0} {1 0} {0 2}
! 1251:
! 1252: 10 {1 1 off} {0 0} {0 0} {0 0}
! 1253: 11 {1 1 normal} {0 1} {0 0} {0 2}
! 1254: 12 {1 1 full} {0 2} {0 1} {0 2}
! 1255: } {
! 1256: forcedelete test.db
! 1257:
! 1258: testvfs tvfs -default 1
! 1259: tvfs filter xSync
! 1260: tvfs script xSyncCb
! 1261: proc xSyncCb {method file fileid flags} {
! 1262: incr ::sync($flags)
! 1263: }
! 1264:
! 1265: sqlite3 db test.db
! 1266: do_execsql_test 15.$tn.1 "
! 1267: PRAGMA page_size = 4096;
! 1268: CREATE TABLE t1(x);
! 1269: PRAGMA wal_autocheckpoint = OFF;
! 1270: PRAGMA journal_mode = WAL;
! 1271: PRAGMA checkpoint_fullfsync = [lindex $settings 0];
! 1272: PRAGMA fullfsync = [lindex $settings 1];
! 1273: PRAGMA synchronous = [lindex $settings 2];
! 1274: " {0 wal}
! 1275:
! 1276: if { $tn==2} breakpoint
! 1277: do_test 15.$tn.2 {
! 1278: set sync(normal) 0
! 1279: set sync(full) 0
! 1280: execsql { INSERT INTO t1 VALUES('abc') }
! 1281: list $::sync(normal) $::sync(full)
! 1282: } $restart_sync
! 1283:
! 1284: do_test 15.$tn.3 {
! 1285: set sync(normal) 0
! 1286: set sync(full) 0
! 1287: execsql { INSERT INTO t1 VALUES('abc') }
! 1288: list $::sync(normal) $::sync(full)
! 1289: } $commit_sync
! 1290:
! 1291: do_test 15.$tn.4 {
! 1292: set sync(normal) 0
! 1293: set sync(full) 0
! 1294: execsql { INSERT INTO t1 VALUES('def') }
! 1295: list $::sync(normal) $::sync(full)
! 1296: } $commit_sync
! 1297:
! 1298: do_test 15.$tn.5 {
! 1299: set sync(normal) 0
! 1300: set sync(full) 0
! 1301: execsql { PRAGMA wal_checkpoint }
! 1302: list $::sync(normal) $::sync(full)
! 1303: } $ckpt_sync
! 1304:
! 1305: db close
! 1306: tvfs delete
! 1307: }
! 1308:
! 1309:
! 1310:
! 1311: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to wal2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test