Annotation of embedaddon/sqlite3/test/walmode.test, revision 1.1
1.1 ! misho 1: # 2010 April 19
! 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/malloc_common.tcl
! 19:
! 20: # If the library was compiled without WAL support, check that the
! 21: # "PRAGMA journal_mode=WAL" treats "WAL" as an unrecognized mode.
! 22: #
! 23: ifcapable !wal {
! 24:
! 25: do_test walmode-0.1 {
! 26: execsql { PRAGMA journal_mode = wal }
! 27: } {delete}
! 28: do_test walmode-0.2 {
! 29: execsql { PRAGMA main.journal_mode = wal }
! 30: } {delete}
! 31: do_test walmode-0.3 {
! 32: execsql { PRAGMA main.journal_mode }
! 33: } {delete}
! 34:
! 35: finish_test
! 36: return
! 37: }
! 38:
! 39: do_test walmode-1.1 {
! 40: set sqlite_sync_count 0
! 41: execsql { PRAGMA page_size = 1024 }
! 42: execsql { PRAGMA journal_mode = wal }
! 43: } {wal}
! 44: do_test walmode-1.2 {
! 45: file size test.db
! 46: } {1024}
! 47:
! 48: set expected_sync_count 3
! 49: if {$::tcl_platform(platform)!="windows"} {
! 50: ifcapable dirsync {
! 51: incr expected_sync_count
! 52: }
! 53: }
! 54: do_test walmode-1.3 {
! 55: set sqlite_sync_count
! 56: } $expected_sync_count
! 57:
! 58: do_test walmode-1.4 {
! 59: file exists test.db-wal
! 60: } {0}
! 61: do_test walmode-1.5 {
! 62: execsql { CREATE TABLE t1(a, b) }
! 63: file size test.db
! 64: } {1024}
! 65: do_test walmode-1.6 {
! 66: file exists test.db-wal
! 67: } {1}
! 68: do_test walmode-1.7 {
! 69: db close
! 70: file exists test.db-wal
! 71: } {0}
! 72:
! 73: # There is now a database file with the read and write versions set to 2
! 74: # in the file system. This file should default to WAL mode.
! 75: #
! 76: do_test walmode-2.1 {
! 77: sqlite3 db test.db
! 78: file exists test.db-wal
! 79: } {0}
! 80: do_test walmode-2.2 {
! 81: execsql { SELECT * FROM sqlite_master }
! 82: file exists test.db-wal
! 83: } {1}
! 84: do_test walmode-2.3 {
! 85: db close
! 86: file exists test.db-wal
! 87: } {0}
! 88:
! 89: # If the first statement executed is "PRAGMA journal_mode = wal", and
! 90: # the file is already configured for WAL (read and write versions set
! 91: # to 2), then there should be no need to write the database. The
! 92: # statement should cause the client to connect to the log file.
! 93: #
! 94: set sqlite_sync_count 0
! 95: do_test walmode-3.1 {
! 96: sqlite3 db test.db
! 97: execsql { PRAGMA journal_mode = wal }
! 98: } {wal}
! 99: do_test walmode-3.2 {
! 100: list $sqlite_sync_count [file exists test.db-wal] [file size test.db-wal]
! 101: } {0 1 0}
! 102:
! 103: # Test that changing back to journal_mode=persist works.
! 104: #
! 105: do_test walmode-4.1 {
! 106: execsql { INSERT INTO t1 VALUES(1, 2) }
! 107: execsql { PRAGMA journal_mode = persist }
! 108: } {persist}
! 109: do_test walmode-4.2 {
! 110: list [file exists test.db-journal] [file exists test.db-wal]
! 111: } {1 0}
! 112: do_test walmode-4.3 {
! 113: execsql { SELECT * FROM t1 }
! 114: } {1 2}
! 115: do_test walmode-4.4 {
! 116: db close
! 117: sqlite3 db test.db
! 118: execsql { SELECT * FROM t1 }
! 119: } {1 2}
! 120: do_test walmode-4.5 {
! 121: list [file exists test.db-journal] [file exists test.db-wal]
! 122: } {1 0}
! 123:
! 124: # Test that nothing goes wrong if a connection is prevented from changing
! 125: # from WAL to rollback mode because a second connection has the database
! 126: # open. Or from rollback to WAL.
! 127: #
! 128: do_test walmode-4.6 {
! 129: sqlite3 db2 test.db
! 130: execsql { PRAGMA main.journal_mode } db2
! 131: } {delete}
! 132: do_test walmode-4.7 {
! 133: execsql { PRAGMA main.journal_mode = wal } db
! 134: } {wal}
! 135: do_test walmode-4.8 {
! 136: execsql { SELECT * FROM t1 } db2
! 137: } {1 2}
! 138: do_test walmode-4.9 {
! 139: catchsql { PRAGMA journal_mode = delete } db
! 140: } {1 {database is locked}}
! 141: do_test walmode-4.10 {
! 142: execsql { PRAGMA main.journal_mode } db
! 143: } {wal}
! 144:
! 145: do_test walmode-4.11 {
! 146: db2 close
! 147: execsql { PRAGMA journal_mode = delete } db
! 148: } {delete}
! 149: do_test walmode-4.12 {
! 150: execsql { PRAGMA main.journal_mode } db
! 151: } {delete}
! 152: do_test walmode-4.13 {
! 153: list [file exists test.db-journal] [file exists test.db-wal]
! 154: } {0 0}
! 155: do_test walmode-4.14 {
! 156: sqlite3 db2 test.db
! 157: execsql {
! 158: BEGIN;
! 159: SELECT * FROM t1;
! 160: } db2
! 161: } {1 2}
! 162:
! 163: do_test walmode-4.16 { execsql { PRAGMA main.journal_mode } db } {delete}
! 164: do_test walmode-4.17 { execsql { PRAGMA main.journal_mode } db2 } {delete}
! 165:
! 166: do_test walmode-4.17 {
! 167: catchsql { PRAGMA main.journal_mode = wal } db
! 168: } {1 {database is locked}}
! 169: do_test walmode-4.18 {
! 170: execsql { PRAGMA main.journal_mode } db
! 171: } {delete}
! 172: catch { db close }
! 173: catch { db2 close }
! 174:
! 175: # Test that it is not possible to change a temporary or in-memory database
! 176: # to WAL mode. WAL mode is for persistent file-backed databases only.
! 177: #
! 178: # walmode-5.1.*: Try to set journal_mode=WAL on [sqlite3 db :memory:] database.
! 179: # walmode-5.2.*: Try to set journal_mode=WAL on [sqlite3 db ""] database.
! 180: # walmode-5.3.*: Try to set temp.journal_mode=WAL.
! 181: #
! 182: do_test walmode-5.1.1 {
! 183: sqlite3 db :memory:
! 184: execsql { PRAGMA main.journal_mode }
! 185: } {memory}
! 186: do_test walmode-5.1.2 {
! 187: execsql { PRAGMA main.journal_mode = wal }
! 188: } {memory}
! 189: do_test walmode-5.1.3 {
! 190: execsql {
! 191: BEGIN;
! 192: CREATE TABLE t1(a, b);
! 193: INSERT INTO t1 VALUES(1, 2);
! 194: COMMIT;
! 195: SELECT * FROM t1;
! 196: PRAGMA main.journal_mode;
! 197: }
! 198: } {1 2 memory}
! 199: do_test walmode-5.1.4 {
! 200: execsql { PRAGMA main.journal_mode = wal }
! 201: } {memory}
! 202: do_test walmode-5.1.5 {
! 203: execsql {
! 204: INSERT INTO t1 VALUES(3, 4);
! 205: SELECT * FROM t1;
! 206: PRAGMA main.journal_mode;
! 207: }
! 208: } {1 2 3 4 memory}
! 209:
! 210: if {$TEMP_STORE>=2} {
! 211: set tempJrnlMode memory
! 212: } else {
! 213: set tempJrnlMode delete
! 214: }
! 215: do_test walmode-5.2.1 {
! 216: sqlite3 db ""
! 217: execsql { PRAGMA main.journal_mode }
! 218: } $tempJrnlMode
! 219: do_test walmode-5.2.2 {
! 220: execsql { PRAGMA main.journal_mode = wal }
! 221: } $tempJrnlMode
! 222: do_test walmode-5.2.3 {
! 223: execsql {
! 224: BEGIN;
! 225: CREATE TABLE t1(a, b);
! 226: INSERT INTO t1 VALUES(1, 2);
! 227: COMMIT;
! 228: SELECT * FROM t1;
! 229: PRAGMA main.journal_mode;
! 230: }
! 231: } [list 1 2 $tempJrnlMode]
! 232: do_test walmode-5.2.4 {
! 233: execsql { PRAGMA main.journal_mode = wal }
! 234: } $tempJrnlMode
! 235: do_test walmode-5.2.5 {
! 236: execsql {
! 237: INSERT INTO t1 VALUES(3, 4);
! 238: SELECT * FROM t1;
! 239: PRAGMA main.journal_mode;
! 240: }
! 241: } [list 1 2 3 4 $tempJrnlMode]
! 242:
! 243: do_test walmode-5.3.1 {
! 244: sqlite3 db test.db
! 245: execsql { PRAGMA temp.journal_mode }
! 246: } $tempJrnlMode
! 247: do_test walmode-5.3.2 {
! 248: execsql { PRAGMA temp.journal_mode = wal }
! 249: } $tempJrnlMode
! 250: do_test walmode-5.3.3 {
! 251: execsql {
! 252: BEGIN;
! 253: CREATE TEMP TABLE t1(a, b);
! 254: INSERT INTO t1 VALUES(1, 2);
! 255: COMMIT;
! 256: SELECT * FROM t1;
! 257: PRAGMA temp.journal_mode;
! 258: }
! 259: } [list 1 2 $tempJrnlMode]
! 260: do_test walmode-5.3.4 {
! 261: execsql { PRAGMA temp.journal_mode = wal }
! 262: } $tempJrnlMode
! 263: do_test walmode-5.3.5 {
! 264: execsql {
! 265: INSERT INTO t1 VALUES(3, 4);
! 266: SELECT * FROM t1;
! 267: PRAGMA temp.journal_mode;
! 268: }
! 269: } [list 1 2 3 4 $tempJrnlMode]
! 270:
! 271:
! 272: #-------------------------------------------------------------------------
! 273: # Test changing to WAL mode from journal_mode=off or journal_mode=memory
! 274: #
! 275: foreach {tn mode} {
! 276: 1 off
! 277: 2 memory
! 278: 3 persist
! 279: 4 delete
! 280: 5 truncate
! 281: } {
! 282: do_test walmode-6.$tn {
! 283: faultsim_delete_and_reopen
! 284: execsql "
! 285: PRAGMA journal_mode = $mode;
! 286: PRAGMA journal_mode = wal;
! 287: "
! 288: } [list $mode wal]
! 289: }
! 290: db close
! 291:
! 292: #-------------------------------------------------------------------------
! 293: # Test the effect of a "PRAGMA journal_mode" command being the first
! 294: # thing executed by a new connection. This means that the schema is not
! 295: # loaded when sqlite3_prepare_v2() is called to compile the statement.
! 296: #
! 297: do_test walmode-7.0 {
! 298: forcedelete test.db
! 299: sqlite3 db test.db
! 300: execsql {
! 301: PRAGMA journal_mode = WAL;
! 302: CREATE TABLE t1(a, b);
! 303: }
! 304: } {wal}
! 305: foreach {tn sql result} {
! 306: 1 "PRAGMA journal_mode" wal
! 307: 2 "PRAGMA main.journal_mode" wal
! 308: 3 "PRAGMA journal_mode = delete" delete
! 309: 4 "PRAGMA journal_mode" delete
! 310: 5 "PRAGMA main.journal_mode" delete
! 311: 6 "PRAGMA journal_mode = wal" wal
! 312: 7 "PRAGMA journal_mode" wal
! 313: 8 "PRAGMA main.journal_mode" wal
! 314:
! 315: 9 "PRAGMA journal_mode" wal
! 316: 10 "PRAGMA main.journal_mode" wal
! 317: 11 "PRAGMA main.journal_mode = delete" delete
! 318: 12 "PRAGMA journal_mode" delete
! 319: 13 "PRAGMA main.journal_mode" delete
! 320: 14 "PRAGMA main.journal_mode = wal" wal
! 321: 15 "PRAGMA journal_mode" wal
! 322: 16 "PRAGMA main.journal_mode" wal
! 323: } {
! 324: do_test walmode-7.$tn {
! 325: db close
! 326: sqlite3 db test.db
! 327: execsql $sql
! 328: } $result
! 329: }
! 330: db close
! 331:
! 332: #-------------------------------------------------------------------------
! 333: # Test the effect of a "PRAGMA journal_mode" command on an attached
! 334: # database.
! 335: #
! 336: faultsim_delete_and_reopen
! 337: do_execsql_test walmode-8.1 {
! 338: CREATE TABLE t1(a, b);
! 339: PRAGMA journal_mode = WAL;
! 340: ATTACH 'test.db2' AS two;
! 341: CREATE TABLE two.t2(a, b);
! 342: } {wal}
! 343: do_execsql_test walmode-8.2 { PRAGMA main.journal_mode } {wal}
! 344: do_execsql_test walmode-8.3 { PRAGMA two.journal_mode } {delete}
! 345: do_execsql_test walmode-8.4 { PRAGMA two.journal_mode = DELETE } {delete}
! 346:
! 347: db close
! 348: sqlite3 db test.db
! 349: do_execsql_test walmode-8.5 { ATTACH 'test.db2' AS two } {}
! 350: do_execsql_test walmode-8.6 { PRAGMA main.journal_mode } {wal}
! 351: do_execsql_test walmode-8.7 { PRAGMA two.journal_mode } {delete}
! 352: do_execsql_test walmode-8.8 { INSERT INTO two.t2 DEFAULT VALUES } {}
! 353: do_execsql_test walmode-8.9 { PRAGMA two.journal_mode } {delete}
! 354: do_execsql_test walmode-8.10 { INSERT INTO t1 DEFAULT VALUES } {}
! 355: do_execsql_test walmode-8.11 { PRAGMA main.journal_mode } {wal}
! 356: do_execsql_test walmode-8.12 { PRAGMA journal_mode } {wal}
! 357:
! 358: # Change to WAL mode on test2.db and make sure (in the tests that follow)
! 359: # that this mode change persists.
! 360: do_test walmode-8.x1 {
! 361: execsql {
! 362: PRAGMA two.journal_mode=WAL;
! 363: PRAGMA two.journal_mode;
! 364: }
! 365: } {wal wal}
! 366:
! 367: db close
! 368: sqlite3 db test.db
! 369: do_execsql_test walmode-8.13 { PRAGMA journal_mode = WAL } {wal}
! 370: do_execsql_test walmode-8.14 { ATTACH 'test.db2' AS two } {}
! 371: do_execsql_test walmode-8.15 { PRAGMA main.journal_mode } {wal}
! 372: do_execsql_test walmode-8.16 { PRAGMA two.journal_mode } {wal}
! 373: do_execsql_test walmode-8.17 { INSERT INTO two.t2 DEFAULT VALUES } {}
! 374: do_execsql_test walmode-8.18 { PRAGMA two.journal_mode } {wal}
! 375:
! 376: sqlite3 db2 test.db2
! 377: do_test walmode-8.19 { execsql { PRAGMA main.journal_mode } db2 } {wal}
! 378: db2 close
! 379:
! 380: do_execsql_test walmode-8.20 { PRAGMA journal_mode = DELETE } {delete}
! 381: do_execsql_test walmode-8.21 { PRAGMA main.journal_mode } {delete}
! 382: do_execsql_test walmode-8.22 { PRAGMA two.journal_mode } {delete}
! 383: do_execsql_test walmode-8.21 { PRAGMA journal_mode = WAL } {wal}
! 384: do_execsql_test walmode-8.21 { PRAGMA main.journal_mode } {wal}
! 385: do_execsql_test walmode-8.22 { PRAGMA two.journal_mode } {wal}
! 386:
! 387: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to walmode.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