Annotation of embedaddon/sqlite3/test/pager1.test, revision 1.1
1.1 ! misho 1: # 2010 June 15
! 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: #
! 12:
! 13: set testdir [file dirname $argv0]
! 14: source $testdir/tester.tcl
! 15: source $testdir/lock_common.tcl
! 16: source $testdir/malloc_common.tcl
! 17: source $testdir/wal_common.tcl
! 18:
! 19: # Do not use a codec for tests in this file, as the database file is
! 20: # manipulated directly using tcl scripts (using the [hexio_write] command).
! 21: #
! 22: do_not_use_codec
! 23:
! 24: #
! 25: # pager1-1.*: Test inter-process locking (clients in multiple processes).
! 26: #
! 27: # pager1-2.*: Test intra-process locking (multiple clients in this process).
! 28: #
! 29: # pager1-3.*: Savepoint related tests.
! 30: #
! 31: # pager1-4.*: Hot-journal related tests.
! 32: #
! 33: # pager1-5.*: Cases related to multi-file commits.
! 34: #
! 35: # pager1-6.*: Cases related to "PRAGMA max_page_count"
! 36: #
! 37: # pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE"
! 38: #
! 39: # pager1-8.*: Cases using temporary and in-memory databases.
! 40: #
! 41: # pager1-9.*: Tests related to the backup API.
! 42: #
! 43: # pager1-10.*: Test that the assumed file-system sector-size is limited to
! 44: # 64KB.
! 45: #
! 46: # pager1-12.*: Tests involving "PRAGMA page_size"
! 47: #
! 48: # pager1-13.*: Cases specific to "PRAGMA journal_mode=PERSIST"
! 49: #
! 50: # pager1-14.*: Cases specific to "PRAGMA journal_mode=OFF"
! 51: #
! 52: # pager1-15.*: Varying sqlite3_vfs.szOsFile
! 53: #
! 54: # pager1-16.*: Varying sqlite3_vfs.mxPathname
! 55: #
! 56: # pager1-17.*: Tests related to "PRAGMA omit_readlock"
! 57: #
! 58: # pager1-18.*: Test that the pager layer responds correctly if the b-tree
! 59: # requests an invalid page number (due to db corruption).
! 60: #
! 61:
! 62: proc recursive_select {id table {script {}}} {
! 63: set cnt 0
! 64: db eval "SELECT rowid, * FROM $table WHERE rowid = ($id-1)" {
! 65: recursive_select $rowid $table $script
! 66: incr cnt
! 67: }
! 68: if {$cnt==0} { eval $script }
! 69: }
! 70:
! 71: set a_string_counter 1
! 72: proc a_string {n} {
! 73: global a_string_counter
! 74: incr a_string_counter
! 75: string range [string repeat "${a_string_counter}." $n] 1 $n
! 76: }
! 77: db func a_string a_string
! 78:
! 79: do_multiclient_test tn {
! 80:
! 81: # Create and populate a database table using connection [db]. Check
! 82: # that connections [db2] and [db3] can see the schema and content.
! 83: #
! 84: do_test pager1-$tn.1 {
! 85: sql1 {
! 86: CREATE TABLE t1(a PRIMARY KEY, b);
! 87: CREATE INDEX i1 ON t1(b);
! 88: INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two');
! 89: }
! 90: } {}
! 91: do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
! 92: do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two}
! 93:
! 94: # Open a transaction and add a row using [db]. This puts [db] in
! 95: # RESERVED state. Check that connections [db2] and [db3] can still
! 96: # read the database content as it was before the transaction was
! 97: # opened. [db] should see the inserted row.
! 98: #
! 99: do_test pager1-$tn.4 {
! 100: sql1 {
! 101: BEGIN;
! 102: INSERT INTO t1 VALUES(3, 'three');
! 103: }
! 104: } {}
! 105: do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
! 106: do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
! 107:
! 108: # [db] still has an open write transaction. Check that this prevents
! 109: # other connections (specifically [db2]) from writing to the database.
! 110: #
! 111: # Even if [db2] opens a transaction first, it may not write to the
! 112: # database. After the attempt to write the db within a transaction,
! 113: # [db2] is left with an open transaction, but not a read-lock on
! 114: # the main database. So it does not prevent [db] from committing.
! 115: #
! 116: do_test pager1-$tn.8 {
! 117: csql2 { UPDATE t1 SET a = a + 10 }
! 118: } {1 {database is locked}}
! 119: do_test pager1-$tn.9 {
! 120: csql2 {
! 121: BEGIN;
! 122: UPDATE t1 SET a = a + 10;
! 123: }
! 124: } {1 {database is locked}}
! 125:
! 126: # Have [db] commit its transactions. Check the other connections can
! 127: # now see the new database content.
! 128: #
! 129: do_test pager1-$tn.10 { sql1 { COMMIT } } {}
! 130: do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
! 131: do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
! 132: do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
! 133:
! 134: # Check that, as noted above, [db2] really did keep an open transaction
! 135: # after the attempt to write the database failed.
! 136: #
! 137: do_test pager1-$tn.14 {
! 138: csql2 { BEGIN }
! 139: } {1 {cannot start a transaction within a transaction}}
! 140: do_test pager1-$tn.15 { sql2 { ROLLBACK } } {}
! 141:
! 142: # Have [db2] open a transaction and take a read-lock on the database.
! 143: # Check that this prevents [db] from writing to the database (outside
! 144: # of any transaction). After this fails, check that [db3] can read
! 145: # the db (showing that [db] did not take a PENDING lock etc.)
! 146: #
! 147: do_test pager1-$tn.15 {
! 148: sql2 { BEGIN; SELECT * FROM t1; }
! 149: } {1 one 2 two 3 three}
! 150: do_test pager1-$tn.16 {
! 151: csql1 { UPDATE t1 SET a = a + 10 }
! 152: } {1 {database is locked}}
! 153: do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
! 154:
! 155: # This time, have [db] open a transaction before writing the database.
! 156: # This works - [db] gets a RESERVED lock which does not conflict with
! 157: # the SHARED lock [db2] is holding.
! 158: #
! 159: do_test pager1-$tn.18 {
! 160: sql1 {
! 161: BEGIN;
! 162: UPDATE t1 SET a = a + 10;
! 163: }
! 164: } {}
! 165: do_test pager1-$tn-19 {
! 166: sql1 { PRAGMA lock_status }
! 167: } {main reserved temp closed}
! 168: do_test pager1-$tn-20 {
! 169: sql2 { PRAGMA lock_status }
! 170: } {main shared temp closed}
! 171:
! 172: # Check that all connections can still read the database. Only [db] sees
! 173: # the updated content (as the transaction has not been committed yet).
! 174: #
! 175: do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
! 176: do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
! 177: do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
! 178:
! 179: # Because [db2] still has the SHARED lock, [db] is unable to commit the
! 180: # transaction. If it tries, an error is returned and the connection
! 181: # upgrades to a PENDING lock.
! 182: #
! 183: # Once this happens, [db] can read the database and see the new content,
! 184: # [db2] (still holding SHARED) can still read the old content, but [db3]
! 185: # (not holding any lock) is prevented by [db]'s PENDING from reading
! 186: # the database.
! 187: #
! 188: do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}}
! 189: do_test pager1-$tn-25 {
! 190: sql1 { PRAGMA lock_status }
! 191: } {main pending temp closed}
! 192: do_test pager1-$tn.26 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
! 193: do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
! 194: do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}
! 195:
! 196: # Have [db2] commit its read transaction, releasing the SHARED lock it
! 197: # is holding. Now, neither [db2] nor [db3] may read the database (as [db]
! 198: # is still holding a PENDING).
! 199: #
! 200: do_test pager1-$tn.29 { sql2 { COMMIT } } {}
! 201: do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}}
! 202: do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}
! 203:
! 204: # [db] is now able to commit the transaction. Once the transaction is
! 205: # committed, all three connections can read the new content.
! 206: #
! 207: do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {}
! 208: do_test pager1-$tn.26 { sql1 { COMMIT } } {}
! 209: do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three}
! 210: do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three}
! 211: do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three}
! 212:
! 213: # Install a busy-handler for connection [db].
! 214: #
! 215: set ::nbusy [list]
! 216: proc busy {n} {
! 217: lappend ::nbusy $n
! 218: if {$n>5} { sql2 COMMIT }
! 219: return 0
! 220: }
! 221: db busy busy
! 222:
! 223: do_test pager1-$tn.29 {
! 224: sql1 { BEGIN ; INSERT INTO t1 VALUES('x', 'y') }
! 225: } {}
! 226: do_test pager1-$tn.30 {
! 227: sql2 { BEGIN ; SELECT * FROM t1 }
! 228: } {21 one 22 two 23 three}
! 229: do_test pager1-$tn.31 { sql1 COMMIT } {}
! 230: do_test pager1-$tn.32 { set ::nbusy } {0 1 2 3 4 5 6}
! 231: }
! 232:
! 233: #-------------------------------------------------------------------------
! 234: # Savepoint related test cases.
! 235: #
! 236: # pager1-3.1.2.*: Force a savepoint rollback to cause the database file
! 237: # to grow.
! 238: #
! 239: # pager1-3.1.3.*: Use a journal created in synchronous=off mode as part
! 240: # of a savepoint rollback.
! 241: #
! 242: do_test pager1-3.1.1 {
! 243: faultsim_delete_and_reopen
! 244: execsql {
! 245: CREATE TABLE t1(a PRIMARY KEY, b);
! 246: CREATE TABLE counter(
! 247: i CHECK (i<5),
! 248: u CHECK (u<10)
! 249: );
! 250: INSERT INTO counter VALUES(0, 0);
! 251: CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
! 252: UPDATE counter SET i = i+1;
! 253: END;
! 254: CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN
! 255: UPDATE counter SET u = u+1;
! 256: END;
! 257: }
! 258: execsql { SELECT * FROM counter }
! 259: } {0 0}
! 260:
! 261: do_execsql_test pager1-3.1.2 {
! 262: PRAGMA cache_size = 10;
! 263: BEGIN;
! 264: INSERT INTO t1 VALUES(1, randomblob(1500));
! 265: INSERT INTO t1 VALUES(2, randomblob(1500));
! 266: INSERT INTO t1 VALUES(3, randomblob(1500));
! 267: SELECT * FROM counter;
! 268: } {3 0}
! 269: do_catchsql_test pager1-3.1.3 {
! 270: INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1
! 271: } {1 {constraint failed}}
! 272: do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}
! 273: do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}
! 274: do_execsql_test pager1-3.6 { COMMIT } {}
! 275:
! 276: foreach {tn sql tcl} {
! 277: 7 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } {
! 278: testvfs tv -default 1
! 279: tv devchar safe_append
! 280: }
! 281: 8 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 2 } {
! 282: testvfs tv -default 1
! 283: tv devchar sequential
! 284: }
! 285: 9 { PRAGMA synchronous = FULL } { }
! 286: 10 { PRAGMA synchronous = NORMAL } { }
! 287: 11 { PRAGMA synchronous = OFF } { }
! 288: 12 { PRAGMA synchronous = FULL ; PRAGMA fullfsync = 1 } { }
! 289: 13 { PRAGMA synchronous = FULL } {
! 290: testvfs tv -default 1
! 291: tv devchar sequential
! 292: }
! 293: 14 { PRAGMA locking_mode = EXCLUSIVE } {
! 294: }
! 295: } {
! 296: do_test pager1-3.$tn.1 {
! 297: eval $tcl
! 298: faultsim_delete_and_reopen
! 299: db func a_string a_string
! 300: execsql $sql
! 301: execsql {
! 302: PRAGMA auto_vacuum = 2;
! 303: PRAGMA cache_size = 10;
! 304: CREATE TABLE z(x INTEGER PRIMARY KEY, y);
! 305: BEGIN;
! 306: INSERT INTO z VALUES(NULL, a_string(800));
! 307: INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 2
! 308: INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 4
! 309: INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 8
! 310: INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 16
! 311: INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 32
! 312: INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 64
! 313: INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 128
! 314: INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 256
! 315: COMMIT;
! 316: }
! 317: execsql { PRAGMA auto_vacuum }
! 318: } {2}
! 319: do_execsql_test pager1-3.$tn.2 {
! 320: BEGIN;
! 321: INSERT INTO z VALUES(NULL, a_string(800));
! 322: INSERT INTO z VALUES(NULL, a_string(800));
! 323: SAVEPOINT one;
! 324: UPDATE z SET y = NULL WHERE x>256;
! 325: PRAGMA incremental_vacuum;
! 326: SELECT count(*) FROM z WHERE x < 100;
! 327: ROLLBACK TO one;
! 328: COMMIT;
! 329: } {99}
! 330:
! 331: do_execsql_test pager1-3.$tn.3 {
! 332: BEGIN;
! 333: SAVEPOINT one;
! 334: UPDATE z SET y = y||x;
! 335: ROLLBACK TO one;
! 336: COMMIT;
! 337: SELECT count(*) FROM z;
! 338: } {258}
! 339:
! 340: do_execsql_test pager1-3.$tn.4 {
! 341: SAVEPOINT one;
! 342: UPDATE z SET y = y||x;
! 343: ROLLBACK TO one;
! 344: } {}
! 345: do_execsql_test pager1-3.$tn.5 {
! 346: SELECT count(*) FROM z;
! 347: RELEASE one;
! 348: PRAGMA integrity_check;
! 349: } {258 ok}
! 350:
! 351: do_execsql_test pager1-3.$tn.6 {
! 352: SAVEPOINT one;
! 353: RELEASE one;
! 354: } {}
! 355:
! 356: db close
! 357: catch { tv delete }
! 358: }
! 359:
! 360: #-------------------------------------------------------------------------
! 361: # Hot journal rollback related test cases.
! 362: #
! 363: # pager1.4.1.*: Test that the pager module deletes very small invalid
! 364: # journal files.
! 365: #
! 366: # pager1.4.2.*: Test that if the master journal pointer at the end of a
! 367: # hot-journal file appears to be corrupt (checksum does not
! 368: # compute) the associated journal is rolled back (and no
! 369: # xAccess() call to check for the presence of any master
! 370: # journal file is made).
! 371: #
! 372: # pager1.4.3.*: Test that the contents of a hot-journal are ignored if the
! 373: # page-size or sector-size in the journal header appear to
! 374: # be invalid (too large, too small or not a power of 2).
! 375: #
! 376: # pager1.4.4.*: Test hot-journal rollback of journal file with a master
! 377: # journal pointer generated in various "PRAGMA synchronous"
! 378: # modes.
! 379: #
! 380: # pager1.4.5.*: Test that hot-journal rollback stops if it encounters a
! 381: # journal-record for which the checksum fails.
! 382: #
! 383: # pager1.4.6.*: Test that when rolling back a hot-journal that contains a
! 384: # master journal pointer, the master journal file is deleted
! 385: # after all the hot-journals that refer to it are deleted.
! 386: #
! 387: # pager1.4.7.*: Test that if a hot-journal file exists but a client can
! 388: # open it for reading only, the database cannot be accessed and
! 389: # SQLITE_CANTOPEN is returned.
! 390: #
! 391: do_test pager1.4.1.1 {
! 392: faultsim_delete_and_reopen
! 393: execsql {
! 394: CREATE TABLE x(y, z);
! 395: INSERT INTO x VALUES(1, 2);
! 396: }
! 397: set fd [open test.db-journal w]
! 398: puts -nonewline $fd "helloworld"
! 399: close $fd
! 400: file exists test.db-journal
! 401: } {1}
! 402: do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2}
! 403: do_test pager1.4.1.3 { file exists test.db-journal } {0}
! 404:
! 405: # Set up a [testvfs] to snapshot the file-system just before SQLite
! 406: # deletes the master-journal to commit a multi-file transaction.
! 407: #
! 408: # In subsequent test cases, invoking [faultsim_restore_and_reopen] sets
! 409: # up the file system to contain two databases, two hot-journal files and
! 410: # a master-journal.
! 411: #
! 412: do_test pager1.4.2.1 {
! 413: testvfs tstvfs -default 1
! 414: tstvfs filter xDelete
! 415: tstvfs script xDeleteCallback
! 416: proc xDeleteCallback {method file args} {
! 417: set file [file tail $file]
! 418: if { [string match *mj* $file] } { faultsim_save }
! 419: }
! 420: faultsim_delete_and_reopen
! 421: db func a_string a_string
! 422: execsql {
! 423: ATTACH 'test.db2' AS aux;
! 424: PRAGMA journal_mode = DELETE;
! 425: PRAGMA main.cache_size = 10;
! 426: PRAGMA aux.cache_size = 10;
! 427: CREATE TABLE t1(a UNIQUE, b UNIQUE);
! 428: CREATE TABLE aux.t2(a UNIQUE, b UNIQUE);
! 429: INSERT INTO t1 VALUES(a_string(200), a_string(300));
! 430: INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
! 431: INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
! 432: INSERT INTO t2 SELECT * FROM t1;
! 433: BEGIN;
! 434: INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1;
! 435: INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1;
! 436: INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1;
! 437: INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1;
! 438: REPLACE INTO t2 SELECT * FROM t1;
! 439: COMMIT;
! 440: }
! 441: db close
! 442: tstvfs delete
! 443: } {}
! 444:
! 445: if {$::tcl_platform(platform)!="windows"} {
! 446: do_test pager1.4.2.2 {
! 447: faultsim_restore_and_reopen
! 448: execsql {
! 449: SELECT count(*) FROM t1;
! 450: PRAGMA integrity_check;
! 451: }
! 452: } {4 ok}
! 453: do_test pager1.4.2.3 {
! 454: faultsim_restore_and_reopen
! 455: foreach f [glob test.db-mj*] { forcedelete $f }
! 456: execsql {
! 457: SELECT count(*) FROM t1;
! 458: PRAGMA integrity_check;
! 459: }
! 460: } {64 ok}
! 461: do_test pager1.4.2.4 {
! 462: faultsim_restore_and_reopen
! 463: hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456
! 464: execsql {
! 465: SELECT count(*) FROM t1;
! 466: PRAGMA integrity_check;
! 467: }
! 468: } {4 ok}
! 469: do_test pager1.4.2.5 {
! 470: faultsim_restore_and_reopen
! 471: hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456
! 472: foreach f [glob test.db-mj*] { forcedelete $f }
! 473: execsql {
! 474: SELECT count(*) FROM t1;
! 475: PRAGMA integrity_check;
! 476: }
! 477: } {4 ok}
! 478: }
! 479:
! 480: do_test pager1.4.3.1 {
! 481: testvfs tstvfs -default 1
! 482: tstvfs filter xSync
! 483: tstvfs script xSyncCallback
! 484: proc xSyncCallback {method file args} {
! 485: set file [file tail $file]
! 486: if { 0==[string match *journal $file] } { faultsim_save }
! 487: }
! 488: faultsim_delete_and_reopen
! 489: execsql {
! 490: PRAGMA journal_mode = DELETE;
! 491: CREATE TABLE t1(a, b);
! 492: INSERT INTO t1 VALUES(1, 2);
! 493: INSERT INTO t1 VALUES(3, 4);
! 494: }
! 495: db close
! 496: tstvfs delete
! 497: } {}
! 498:
! 499: foreach {tn ofst value result} {
! 500: 2 20 31 {1 2 3 4}
! 501: 3 20 32 {1 2 3 4}
! 502: 4 20 33 {1 2 3 4}
! 503: 5 20 65536 {1 2 3 4}
! 504: 6 20 131072 {1 2 3 4}
! 505:
! 506: 7 24 511 {1 2 3 4}
! 507: 8 24 513 {1 2 3 4}
! 508: 9 24 131072 {1 2 3 4}
! 509:
! 510: 10 32 65536 {1 2}
! 511: } {
! 512: do_test pager1.4.3.$tn {
! 513: faultsim_restore_and_reopen
! 514: hexio_write test.db-journal $ofst [format %.8x $value]
! 515: execsql { SELECT * FROM t1 }
! 516: } $result
! 517: }
! 518: db close
! 519:
! 520: # Set up a VFS that snapshots the file-system just before a master journal
! 521: # file is deleted to commit a multi-file transaction. Specifically, the
! 522: # file-system is saved just before the xDelete() call to remove the
! 523: # master journal file from the file-system.
! 524: #
! 525: testvfs tv -default 1
! 526: tv script copy_on_mj_delete
! 527: set ::mj_filename_length 0
! 528: proc copy_on_mj_delete {method filename args} {
! 529: if {[string match *mj* [file tail $filename]]} {
! 530: set ::mj_filename_length [string length $filename]
! 531: faultsim_save
! 532: }
! 533: return SQLITE_OK
! 534: }
! 535:
! 536: set pwd [pwd]
! 537: foreach {tn1 tcl} {
! 538: 1 { set prefix "test.db" }
! 539: 2 {
! 540: # This test depends on the underlying VFS being able to open paths
! 541: # 512 bytes in length. The idea is to create a hot-journal file that
! 542: # contains a master-journal pointer so large that it could contain
! 543: # a valid page record (if the file page-size is 512 bytes). So as to
! 544: # make sure SQLite doesn't get confused by this.
! 545: #
! 546: set nPadding [expr 511 - $::mj_filename_length]
! 547: if {$tcl_platform(platform)=="windows"} {
! 548: # TBD need to figure out how to do this correctly for Windows!!!
! 549: set nPadding [expr 255 - $::mj_filename_length]
! 550: }
! 551:
! 552: # We cannot just create a really long database file name to open, as
! 553: # Linux limits a single component of a path to 255 bytes by default
! 554: # (and presumably other systems have limits too). So create a directory
! 555: # hierarchy to work in.
! 556: #
! 557: set dirname "d123456789012345678901234567890/"
! 558: set nDir [expr $nPadding / 32]
! 559: if { $nDir } {
! 560: set p [string repeat $dirname $nDir]
! 561: file mkdir $p
! 562: cd $p
! 563: }
! 564:
! 565: set padding [string repeat x [expr $nPadding %32]]
! 566: set prefix "test.db${padding}"
! 567: }
! 568: } {
! 569: eval $tcl
! 570: foreach {tn2 sql} {
! 571: o {
! 572: PRAGMA main.synchronous=OFF;
! 573: PRAGMA aux.synchronous=OFF;
! 574: PRAGMA journal_mode = DELETE;
! 575: }
! 576: o512 {
! 577: PRAGMA main.synchronous=OFF;
! 578: PRAGMA aux.synchronous=OFF;
! 579: PRAGMA main.page_size = 512;
! 580: PRAGMA aux.page_size = 512;
! 581: PRAGMA journal_mode = DELETE;
! 582: }
! 583: n {
! 584: PRAGMA main.synchronous=NORMAL;
! 585: PRAGMA aux.synchronous=NORMAL;
! 586: PRAGMA journal_mode = DELETE;
! 587: }
! 588: f {
! 589: PRAGMA main.synchronous=FULL;
! 590: PRAGMA aux.synchronous=FULL;
! 591: PRAGMA journal_mode = DELETE;
! 592: }
! 593: } {
! 594:
! 595: set tn "${tn1}.${tn2}"
! 596:
! 597: # Set up a connection to have two databases, test.db (main) and
! 598: # test.db2 (aux). Then run a multi-file transaction on them. The
! 599: # VFS will snapshot the file-system just before the master-journal
! 600: # file is deleted to commit the transaction.
! 601: #
! 602: tv filter xDelete
! 603: do_test pager1-4.4.$tn.1 {
! 604: faultsim_delete_and_reopen $prefix
! 605: execsql "
! 606: ATTACH '${prefix}2' AS aux;
! 607: $sql
! 608: CREATE TABLE a(x);
! 609: CREATE TABLE aux.b(x);
! 610: INSERT INTO a VALUES('double-you');
! 611: INSERT INTO a VALUES('why');
! 612: INSERT INTO a VALUES('zed');
! 613: INSERT INTO b VALUES('won');
! 614: INSERT INTO b VALUES('too');
! 615: INSERT INTO b VALUES('free');
! 616: "
! 617: execsql {
! 618: BEGIN;
! 619: INSERT INTO a SELECT * FROM b WHERE rowid<=3;
! 620: INSERT INTO b SELECT * FROM a WHERE rowid<=3;
! 621: COMMIT;
! 622: }
! 623: } {}
! 624: tv filter {}
! 625:
! 626: # Check that the transaction was committed successfully.
! 627: #
! 628: do_execsql_test pager1-4.4.$tn.2 {
! 629: SELECT * FROM a
! 630: } {double-you why zed won too free}
! 631: do_execsql_test pager1-4.4.$tn.3 {
! 632: SELECT * FROM b
! 633: } {won too free double-you why zed}
! 634:
! 635: # Restore the file-system and reopen the databases. Check that it now
! 636: # appears that the transaction was not committed (because the file-system
! 637: # was restored to the state where it had not been).
! 638: #
! 639: do_test pager1-4.4.$tn.4 {
! 640: faultsim_restore_and_reopen $prefix
! 641: execsql "ATTACH '${prefix}2' AS aux"
! 642: } {}
! 643: do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}
! 644: do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}
! 645:
! 646: # Restore the file-system again. This time, before reopening the databases,
! 647: # delete the master-journal file from the file-system. It now appears that
! 648: # the transaction was committed (no master-journal file == no rollback).
! 649: #
! 650: do_test pager1-4.4.$tn.7 {
! 651: faultsim_restore_and_reopen $prefix
! 652: foreach f [glob ${prefix}-mj*] { forcedelete $f }
! 653: execsql "ATTACH '${prefix}2' AS aux"
! 654: } {}
! 655: do_execsql_test pager1-4.4.$tn.8 {
! 656: SELECT * FROM a
! 657: } {double-you why zed won too free}
! 658: do_execsql_test pager1-4.4.$tn.9 {
! 659: SELECT * FROM b
! 660: } {won too free double-you why zed}
! 661: }
! 662:
! 663: cd $pwd
! 664: }
! 665: db close
! 666: tv delete
! 667: forcedelete $dirname
! 668:
! 669:
! 670: # Set up a VFS to make a copy of the file-system just before deleting a
! 671: # journal file to commit a transaction. The transaction modifies exactly
! 672: # two database pages (and page 1 - the change counter).
! 673: #
! 674: testvfs tv -default 1
! 675: tv sectorsize 512
! 676: tv script copy_on_journal_delete
! 677: tv filter xDelete
! 678: proc copy_on_journal_delete {method filename args} {
! 679: if {[string match *journal $filename]} faultsim_save
! 680: return SQLITE_OK
! 681: }
! 682: faultsim_delete_and_reopen
! 683: do_execsql_test pager1.4.5.1 {
! 684: PRAGMA journal_mode = DELETE;
! 685: PRAGMA page_size = 1024;
! 686: CREATE TABLE t1(a, b);
! 687: CREATE TABLE t2(a, b);
! 688: INSERT INTO t1 VALUES('I', 'II');
! 689: INSERT INTO t2 VALUES('III', 'IV');
! 690: BEGIN;
! 691: INSERT INTO t1 VALUES(1, 2);
! 692: INSERT INTO t2 VALUES(3, 4);
! 693: COMMIT;
! 694: } {delete}
! 695: tv filter {}
! 696:
! 697: # Check the transaction was committed:
! 698: #
! 699: do_execsql_test pager1.4.5.2 {
! 700: SELECT * FROM t1;
! 701: SELECT * FROM t2;
! 702: } {I II 1 2 III IV 3 4}
! 703:
! 704: # Now try four tests:
! 705: #
! 706: # pager1-4.5.3: Restore the file-system. Check that the whole transaction
! 707: # is rolled back.
! 708: #
! 709: # pager1-4.5.4: Restore the file-system. Corrupt the first record in the
! 710: # journal. Check the transaction is not rolled back.
! 711: #
! 712: # pager1-4.5.5: Restore the file-system. Corrupt the second record in the
! 713: # journal. Check that the first record in the transaction is
! 714: # played back, but not the second.
! 715: #
! 716: # pager1-4.5.6: Restore the file-system. Try to open the database with a
! 717: # readonly connection. This should fail, as a read-only
! 718: # connection cannot roll back the database file.
! 719: #
! 720: faultsim_restore_and_reopen
! 721: do_execsql_test pager1.4.5.3 {
! 722: SELECT * FROM t1;
! 723: SELECT * FROM t2;
! 724: } {I II III IV}
! 725: faultsim_restore_and_reopen
! 726: hexio_write test.db-journal [expr 512+4+1024 - 202] 0123456789ABCDEF
! 727: do_execsql_test pager1.4.5.4 {
! 728: SELECT * FROM t1;
! 729: SELECT * FROM t2;
! 730: } {I II 1 2 III IV 3 4}
! 731: faultsim_restore_and_reopen
! 732: hexio_write test.db-journal [expr 512+4+1024+4+4+1024 - 202] 0123456789ABCDEF
! 733: do_execsql_test pager1.4.5.5 {
! 734: SELECT * FROM t1;
! 735: SELECT * FROM t2;
! 736: } {I II III IV 3 4}
! 737:
! 738: faultsim_restore_and_reopen
! 739: db close
! 740: sqlite3 db test.db -readonly 1
! 741: do_catchsql_test pager1.4.5.6 {
! 742: SELECT * FROM t1;
! 743: SELECT * FROM t2;
! 744: } {1 {disk I/O error}}
! 745: db close
! 746:
! 747: # Snapshot the file-system just before multi-file commit. Save the name
! 748: # of the master journal file in $::mj_filename.
! 749: #
! 750: tv script copy_on_mj_delete
! 751: tv filter xDelete
! 752: proc copy_on_mj_delete {method filename args} {
! 753: if {[string match *mj* [file tail $filename]]} {
! 754: set ::mj_filename $filename
! 755: faultsim_save
! 756: }
! 757: return SQLITE_OK
! 758: }
! 759: do_test pager1.4.6.1 {
! 760: faultsim_delete_and_reopen
! 761: execsql {
! 762: PRAGMA journal_mode = DELETE;
! 763: ATTACH 'test.db2' AS two;
! 764: CREATE TABLE t1(a, b);
! 765: CREATE TABLE two.t2(a, b);
! 766: INSERT INTO t1 VALUES(1, 't1.1');
! 767: INSERT INTO t2 VALUES(1, 't2.1');
! 768: BEGIN;
! 769: UPDATE t1 SET b = 't1.2';
! 770: UPDATE t2 SET b = 't2.2';
! 771: COMMIT;
! 772: }
! 773: tv filter {}
! 774: db close
! 775: } {}
! 776:
! 777: faultsim_restore_and_reopen
! 778: do_execsql_test pager1.4.6.2 { SELECT * FROM t1 } {1 t1.1}
! 779: do_test pager1.4.6.3 { file exists $::mj_filename } {1}
! 780: do_execsql_test pager1.4.6.4 {
! 781: ATTACH 'test.db2' AS two;
! 782: SELECT * FROM t2;
! 783: } {1 t2.1}
! 784: do_test pager1.4.6.5 { file exists $::mj_filename } {0}
! 785:
! 786: faultsim_restore_and_reopen
! 787: db close
! 788: do_test pager1.4.6.8 {
! 789: set ::mj_filename1 $::mj_filename
! 790: tv filter xDelete
! 791: sqlite3 db test.db2
! 792: execsql {
! 793: PRAGMA journal_mode = DELETE;
! 794: ATTACH 'test.db3' AS three;
! 795: CREATE TABLE three.t3(a, b);
! 796: INSERT INTO t3 VALUES(1, 't3.1');
! 797: BEGIN;
! 798: UPDATE t2 SET b = 't2.3';
! 799: UPDATE t3 SET b = 't3.3';
! 800: COMMIT;
! 801: }
! 802: expr {$::mj_filename1 != $::mj_filename}
! 803: } {1}
! 804: faultsim_restore_and_reopen
! 805: tv filter {}
! 806:
! 807: # The file-system now contains:
! 808: #
! 809: # * three databases
! 810: # * three hot-journal files
! 811: # * two master-journal files.
! 812: #
! 813: # The hot-journals associated with test.db2 and test.db3 point to
! 814: # master journal $::mj_filename. The hot-journal file associated with
! 815: # test.db points to master journal $::mj_filename1. So reading from
! 816: # test.db should delete $::mj_filename1.
! 817: #
! 818: do_test pager1.4.6.9 {
! 819: lsort [glob test.db*]
! 820: } [lsort [list \
! 821: test.db test.db2 test.db3 \
! 822: test.db-journal test.db2-journal test.db3-journal \
! 823: [file tail $::mj_filename] [file tail $::mj_filename1]
! 824: ]]
! 825:
! 826: # The master-journal $::mj_filename1 contains pointers to test.db and
! 827: # test.db2. However the hot-journal associated with test.db2 points to
! 828: # a different master-journal. Therefore, reading from test.db only should
! 829: # be enough to cause SQLite to delete $::mj_filename1.
! 830: #
! 831: do_test pager1.4.6.10 { file exists $::mj_filename } {1}
! 832: do_test pager1.4.6.11 { file exists $::mj_filename1 } {1}
! 833: do_execsql_test pager1.4.6.12 { SELECT * FROM t1 } {1 t1.1}
! 834: do_test pager1.4.6.13 { file exists $::mj_filename } {1}
! 835: do_test pager1.4.6.14 { file exists $::mj_filename1 } {0}
! 836:
! 837: do_execsql_test pager1.4.6.12 {
! 838: ATTACH 'test.db2' AS two;
! 839: SELECT * FROM t2;
! 840: } {1 t2.1}
! 841: do_test pager1.4.6.13 { file exists $::mj_filename } {1}
! 842: do_execsql_test pager1.4.6.14 {
! 843: ATTACH 'test.db3' AS three;
! 844: SELECT * FROM t3;
! 845: } {1 t3.1}
! 846: do_test pager1.4.6.15 { file exists $::mj_filename } {0}
! 847:
! 848: db close
! 849: tv delete
! 850:
! 851: testvfs tv -default 1
! 852: tv sectorsize 512
! 853: tv script copy_on_journal_delete
! 854: tv filter xDelete
! 855: proc copy_on_journal_delete {method filename args} {
! 856: if {[string match *journal $filename]} faultsim_save
! 857: return SQLITE_OK
! 858: }
! 859: faultsim_delete_and_reopen
! 860: do_execsql_test pager1.4.7.1 {
! 861: PRAGMA journal_mode = DELETE;
! 862: CREATE TABLE t1(x PRIMARY KEY, y);
! 863: CREATE INDEX i1 ON t1(y);
! 864: INSERT INTO t1 VALUES('I', 'one');
! 865: INSERT INTO t1 VALUES('II', 'four');
! 866: INSERT INTO t1 VALUES('III', 'nine');
! 867: BEGIN;
! 868: INSERT INTO t1 VALUES('IV', 'sixteen');
! 869: INSERT INTO t1 VALUES('V' , 'twentyfive');
! 870: COMMIT;
! 871: } {delete}
! 872: tv filter {}
! 873: db close
! 874: tv delete
! 875: do_test pager1.4.7.2 {
! 876: faultsim_restore_and_reopen
! 877: catch {file attributes test.db-journal -permissions r--------}
! 878: catch {file attributes test.db-journal -readonly 1}
! 879: catchsql { SELECT * FROM t1 }
! 880: } {1 {unable to open database file}}
! 881: do_test pager1.4.7.3 {
! 882: db close
! 883: catch {file attributes test.db-journal -permissions rw-rw-rw-}
! 884: catch {file attributes test.db-journal -readonly 0}
! 885: delete_file test.db-journal
! 886: file exists test.db-journal
! 887: } {0}
! 888:
! 889: #-------------------------------------------------------------------------
! 890: # The following tests deal with multi-file commits.
! 891: #
! 892: # pager1-5.1.*: The case where a multi-file cannot be committed because
! 893: # another connection is holding a SHARED lock on one of the
! 894: # files. After the SHARED lock is removed, the COMMIT succeeds.
! 895: #
! 896: # pager1-5.2.*: Multi-file commits with journal_mode=memory.
! 897: #
! 898: # pager1-5.3.*: Multi-file commits with journal_mode=memory.
! 899: #
! 900: # pager1-5.4.*: Check that with synchronous=normal, the master-journal file
! 901: # name is added to a journal file immediately after the last
! 902: # journal record. But with synchronous=full, extra unused space
! 903: # is allocated between the last journal record and the
! 904: # master-journal file name so that the master-journal file
! 905: # name does not lie on the same sector as the last journal file
! 906: # record.
! 907: #
! 908: # pager1-5.5.*: Check that in journal_mode=PERSIST mode, a journal file is
! 909: # truncated to zero bytes when a multi-file transaction is
! 910: # committed (instead of the first couple of bytes being zeroed).
! 911: #
! 912: #
! 913: do_test pager1-5.1.1 {
! 914: faultsim_delete_and_reopen
! 915: execsql {
! 916: ATTACH 'test.db2' AS aux;
! 917: CREATE TABLE t1(a, b);
! 918: CREATE TABLE aux.t2(a, b);
! 919: INSERT INTO t1 VALUES(17, 'Lenin');
! 920: INSERT INTO t1 VALUES(22, 'Stalin');
! 921: INSERT INTO t1 VALUES(53, 'Khrushchev');
! 922: }
! 923: } {}
! 924: do_test pager1-5.1.2 {
! 925: execsql {
! 926: BEGIN;
! 927: INSERT INTO t1 VALUES(64, 'Brezhnev');
! 928: INSERT INTO t2 SELECT * FROM t1;
! 929: }
! 930: sqlite3 db2 test.db2
! 931: execsql {
! 932: BEGIN;
! 933: SELECT * FROM t2;
! 934: } db2
! 935: } {}
! 936: do_test pager1-5.1.3 {
! 937: catchsql COMMIT
! 938: } {1 {database is locked}}
! 939: do_test pager1-5.1.4 {
! 940: execsql COMMIT db2
! 941: execsql COMMIT
! 942: execsql { SELECT * FROM t2 } db2
! 943: } {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev}
! 944: do_test pager1-5.1.5 {
! 945: db2 close
! 946: } {}
! 947:
! 948: do_test pager1-5.2.1 {
! 949: execsql {
! 950: PRAGMA journal_mode = memory;
! 951: BEGIN;
! 952: INSERT INTO t1 VALUES(84, 'Andropov');
! 953: INSERT INTO t2 VALUES(84, 'Andropov');
! 954: COMMIT;
! 955: }
! 956: } {memory}
! 957: do_test pager1-5.3.1 {
! 958: execsql {
! 959: PRAGMA journal_mode = off;
! 960: BEGIN;
! 961: INSERT INTO t1 VALUES(85, 'Gorbachev');
! 962: INSERT INTO t2 VALUES(85, 'Gorbachev');
! 963: COMMIT;
! 964: }
! 965: } {off}
! 966:
! 967: do_test pager1-5.4.1 {
! 968: db close
! 969: testvfs tv
! 970: sqlite3 db test.db -vfs tv
! 971: execsql { ATTACH 'test.db2' AS aux }
! 972:
! 973: tv filter xDelete
! 974: tv script max_journal_size
! 975: tv sectorsize 512
! 976: set ::max_journal 0
! 977: proc max_journal_size {method args} {
! 978: set sz 0
! 979: catch { set sz [file size test.db-journal] }
! 980: if {$sz > $::max_journal} {
! 981: set ::max_journal $sz
! 982: }
! 983: return SQLITE_OK
! 984: }
! 985: execsql {
! 986: PRAGMA journal_mode = DELETE;
! 987: PRAGMA synchronous = NORMAL;
! 988: BEGIN;
! 989: INSERT INTO t1 VALUES(85, 'Gorbachev');
! 990: INSERT INTO t2 VALUES(85, 'Gorbachev');
! 991: COMMIT;
! 992: }
! 993:
! 994: # The size of the journal file is now:
! 995: #
! 996: # 1) 512 byte header +
! 997: # 2) 2 * (1024+8) byte records +
! 998: # 3) 20+N bytes of master-journal pointer, where N is the size of
! 999: # the master-journal name encoded as utf-8 with no nul term.
! 1000: #
! 1001: set mj_pointer [expr {
! 1002: 20 + [string length [pwd]] + [string length "/test.db-mjXXXXXX9XX"]
! 1003: }]
! 1004: expr {$::max_journal==(512+2*(1024+8)+$mj_pointer)}
! 1005: } 1
! 1006: do_test pager1-5.4.2 {
! 1007: set ::max_journal 0
! 1008: execsql {
! 1009: PRAGMA synchronous = full;
! 1010: BEGIN;
! 1011: DELETE FROM t1 WHERE b = 'Lenin';
! 1012: DELETE FROM t2 WHERE b = 'Lenin';
! 1013: COMMIT;
! 1014: }
! 1015:
! 1016: # In synchronous=full mode, the master-journal pointer is not written
! 1017: # directly after the last record in the journal file. Instead, it is
! 1018: # written starting at the next (in this case 512 byte) sector boundary.
! 1019: #
! 1020: set mj_pointer [expr {
! 1021: 20 + [string length [pwd]] + [string length "/test.db-mjXXXXXX9XX"]
! 1022: }]
! 1023: expr {$::max_journal==(((512+2*(1024+8)+511)/512)*512 + $mj_pointer)}
! 1024: } 1
! 1025: db close
! 1026: tv delete
! 1027:
! 1028: do_test pager1-5.5.1 {
! 1029: sqlite3 db test.db
! 1030: execsql {
! 1031: ATTACH 'test.db2' AS aux;
! 1032: PRAGMA journal_mode = PERSIST;
! 1033: CREATE TABLE t3(a, b);
! 1034: INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 1035: UPDATE t3 SET b = randomblob(1500);
! 1036: }
! 1037: expr [file size test.db-journal] > 15000
! 1038: } {1}
! 1039: do_test pager1-5.5.2 {
! 1040: execsql {
! 1041: PRAGMA synchronous = full;
! 1042: BEGIN;
! 1043: DELETE FROM t1 WHERE b = 'Stalin';
! 1044: DELETE FROM t2 WHERE b = 'Stalin';
! 1045: COMMIT;
! 1046: }
! 1047: file size test.db-journal
! 1048: } {0}
! 1049:
! 1050:
! 1051: #-------------------------------------------------------------------------
! 1052: # The following tests work with "PRAGMA max_page_count"
! 1053: #
! 1054: do_test pager1-6.1 {
! 1055: faultsim_delete_and_reopen
! 1056: execsql {
! 1057: PRAGMA auto_vacuum = none;
! 1058: PRAGMA max_page_count = 10;
! 1059: CREATE TABLE t2(a, b);
! 1060: CREATE TABLE t3(a, b);
! 1061: CREATE TABLE t4(a, b);
! 1062: CREATE TABLE t5(a, b);
! 1063: CREATE TABLE t6(a, b);
! 1064: CREATE TABLE t7(a, b);
! 1065: CREATE TABLE t8(a, b);
! 1066: CREATE TABLE t9(a, b);
! 1067: CREATE TABLE t10(a, b);
! 1068: }
! 1069: } {10}
! 1070: do_catchsql_test pager1-6.2 {
! 1071: CREATE TABLE t11(a, b)
! 1072: } {1 {database or disk is full}}
! 1073: do_execsql_test pager1-6.4 { PRAGMA max_page_count } {10}
! 1074: do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15}
! 1075: do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b) } {}
! 1076: do_execsql_test pager1-6.7 {
! 1077: BEGIN;
! 1078: INSERT INTO t11 VALUES(1, 2);
! 1079: PRAGMA max_page_count = 13;
! 1080: } {13}
! 1081: do_execsql_test pager1-6.8 {
! 1082: INSERT INTO t11 VALUES(3, 4);
! 1083: PRAGMA max_page_count = 10;
! 1084: } {11}
! 1085: do_execsql_test pager1-6.9 { COMMIT } {}
! 1086:
! 1087: do_execsql_test pager1-6.10 { PRAGMA max_page_count = 10 } {11}
! 1088: do_execsql_test pager1-6.11 { SELECT * FROM t11 } {1 2 3 4}
! 1089: do_execsql_test pager1-6.12 { PRAGMA max_page_count } {11}
! 1090:
! 1091:
! 1092: #-------------------------------------------------------------------------
! 1093: # The following tests work with "PRAGMA journal_mode=TRUNCATE" and
! 1094: # "PRAGMA locking_mode=EXCLUSIVE".
! 1095: #
! 1096: # Each test is specified with 5 variables. As follows:
! 1097: #
! 1098: # $tn: Test Number. Used as part of the [do_test] test names.
! 1099: # $sql: SQL to execute.
! 1100: # $res: Expected result of executing $sql.
! 1101: # $js: The expected size of the journal file, in bytes, after executing
! 1102: # the SQL script. Or -1 if the journal is not expected to exist.
! 1103: # $ws: The expected size of the WAL file, in bytes, after executing
! 1104: # the SQL script. Or -1 if the WAL is not expected to exist.
! 1105: #
! 1106: ifcapable wal {
! 1107: faultsim_delete_and_reopen
! 1108: foreach {tn sql res js ws} [subst {
! 1109:
! 1110: 1 {
! 1111: CREATE TABLE t1(a, b);
! 1112: PRAGMA auto_vacuum=OFF;
! 1113: PRAGMA synchronous=NORMAL;
! 1114: PRAGMA page_size=1024;
! 1115: PRAGMA locking_mode=EXCLUSIVE;
! 1116: PRAGMA journal_mode=TRUNCATE;
! 1117: INSERT INTO t1 VALUES(1, 2);
! 1118: } {exclusive truncate} 0 -1
! 1119:
! 1120: 2 {
! 1121: BEGIN IMMEDIATE;
! 1122: SELECT * FROM t1;
! 1123: COMMIT;
! 1124: } {1 2} 0 -1
! 1125:
! 1126: 3 {
! 1127: BEGIN;
! 1128: SELECT * FROM t1;
! 1129: COMMIT;
! 1130: } {1 2} 0 -1
! 1131:
! 1132: 4 { PRAGMA journal_mode = WAL } wal -1 -1
! 1133: 5 { INSERT INTO t1 VALUES(3, 4) } {} -1 [wal_file_size 1 1024]
! 1134: 6 { PRAGMA locking_mode = NORMAL } exclusive -1 [wal_file_size 1 1024]
! 1135: 7 { INSERT INTO t1 VALUES(5, 6); } {} -1 [wal_file_size 2 1024]
! 1136:
! 1137: 8 { PRAGMA journal_mode = TRUNCATE } truncate 0 -1
! 1138: 9 { INSERT INTO t1 VALUES(7, 8) } {} 0 -1
! 1139: 10 { SELECT * FROM t1 } {1 2 3 4 5 6 7 8} 0 -1
! 1140:
! 1141: }] {
! 1142: do_execsql_test pager1-7.1.$tn.1 $sql $res
! 1143: catch { set J -1 ; set J [file size test.db-journal] }
! 1144: catch { set W -1 ; set W [file size test.db-wal] }
! 1145: do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws]
! 1146: }
! 1147: }
! 1148:
! 1149: do_test pager1-7.2.1 {
! 1150: faultsim_delete_and_reopen
! 1151: execsql {
! 1152: PRAGMA locking_mode = EXCLUSIVE;
! 1153: CREATE TABLE t1(a, b);
! 1154: BEGIN;
! 1155: PRAGMA journal_mode = delete;
! 1156: PRAGMA journal_mode = truncate;
! 1157: }
! 1158: } {exclusive delete truncate}
! 1159: do_test pager1-7.2.2 {
! 1160: execsql { INSERT INTO t1 VALUES(1, 2) }
! 1161: execsql { PRAGMA journal_mode = persist }
! 1162: } {truncate}
! 1163: do_test pager1-7.2.3 {
! 1164: execsql { COMMIT }
! 1165: execsql {
! 1166: PRAGMA journal_mode = persist;
! 1167: PRAGMA journal_size_limit;
! 1168: }
! 1169: } {persist -1}
! 1170:
! 1171: #-------------------------------------------------------------------------
! 1172: # The following tests, pager1-8.*, test that the special filenames
! 1173: # ":memory:" and "" open temporary databases.
! 1174: #
! 1175: foreach {tn filename} {
! 1176: 1 :memory:
! 1177: 2 ""
! 1178: } {
! 1179: do_test pager1-8.$tn.1 {
! 1180: faultsim_delete_and_reopen
! 1181: db close
! 1182: sqlite3 db $filename
! 1183: execsql {
! 1184: PRAGMA auto_vacuum = 1;
! 1185: CREATE TABLE x1(x);
! 1186: INSERT INTO x1 VALUES('Charles');
! 1187: INSERT INTO x1 VALUES('James');
! 1188: INSERT INTO x1 VALUES('Mary');
! 1189: SELECT * FROM x1;
! 1190: }
! 1191: } {Charles James Mary}
! 1192:
! 1193: do_test pager1-8.$tn.2 {
! 1194: sqlite3 db2 $filename
! 1195: catchsql { SELECT * FROM x1 } db2
! 1196: } {1 {no such table: x1}}
! 1197:
! 1198: do_execsql_test pager1-8.$tn.3 {
! 1199: BEGIN;
! 1200: INSERT INTO x1 VALUES('William');
! 1201: INSERT INTO x1 VALUES('Anne');
! 1202: ROLLBACK;
! 1203: } {}
! 1204: }
! 1205:
! 1206: #-------------------------------------------------------------------------
! 1207: # The next block of tests - pager1-9.* - deal with interactions between
! 1208: # the pager and the backup API. Test cases:
! 1209: #
! 1210: # pager1-9.1.*: Test that a backup completes successfully even if the
! 1211: # source db is written to during the backup op.
! 1212: #
! 1213: # pager1-9.2.*: Test that a backup completes successfully even if the
! 1214: # source db is written to and then rolled back during a
! 1215: # backup operation.
! 1216: #
! 1217: do_test pager1-9.0.1 {
! 1218: faultsim_delete_and_reopen
! 1219: db func a_string a_string
! 1220: execsql {
! 1221: PRAGMA cache_size = 10;
! 1222: BEGIN;
! 1223: CREATE TABLE ab(a, b, UNIQUE(a, b));
! 1224: INSERT INTO ab VALUES( a_string(200), a_string(300) );
! 1225: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
! 1226: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
! 1227: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
! 1228: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
! 1229: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
! 1230: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
! 1231: INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
! 1232: COMMIT;
! 1233: }
! 1234: } {}
! 1235: do_test pager1-9.0.2 {
! 1236: sqlite3 db2 test.db2
! 1237: db2 eval { PRAGMA cache_size = 10 }
! 1238: sqlite3_backup B db2 main db main
! 1239: list [B step 10000] [B finish]
! 1240: } {SQLITE_DONE SQLITE_OK}
! 1241: do_test pager1-9.0.3 {
! 1242: db one {SELECT md5sum(a, b) FROM ab}
! 1243: } [db2 one {SELECT md5sum(a, b) FROM ab}]
! 1244:
! 1245: do_test pager1-9.1.1 {
! 1246: execsql { UPDATE ab SET a = a_string(201) }
! 1247: sqlite3_backup B db2 main db main
! 1248: B step 30
! 1249: } {SQLITE_OK}
! 1250: do_test pager1-9.1.2 {
! 1251: execsql { UPDATE ab SET b = a_string(301) }
! 1252: list [B step 10000] [B finish]
! 1253: } {SQLITE_DONE SQLITE_OK}
! 1254: do_test pager1-9.1.3 {
! 1255: db one {SELECT md5sum(a, b) FROM ab}
! 1256: } [db2 one {SELECT md5sum(a, b) FROM ab}]
! 1257: do_test pager1-9.1.4 { execsql { SELECT count(*) FROM ab } } {128}
! 1258:
! 1259: do_test pager1-9.2.1 {
! 1260: execsql { UPDATE ab SET a = a_string(202) }
! 1261: sqlite3_backup B db2 main db main
! 1262: B step 30
! 1263: } {SQLITE_OK}
! 1264: do_test pager1-9.2.2 {
! 1265: execsql {
! 1266: BEGIN;
! 1267: UPDATE ab SET b = a_string(301);
! 1268: ROLLBACK;
! 1269: }
! 1270: list [B step 10000] [B finish]
! 1271: } {SQLITE_DONE SQLITE_OK}
! 1272: do_test pager1-9.2.3 {
! 1273: db one {SELECT md5sum(a, b) FROM ab}
! 1274: } [db2 one {SELECT md5sum(a, b) FROM ab}]
! 1275: do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128}
! 1276: db close
! 1277: db2 close
! 1278:
! 1279: do_test pager1-9.3.1 {
! 1280: testvfs tv -default 1
! 1281: tv sectorsize 4096
! 1282: faultsim_delete_and_reopen
! 1283:
! 1284: execsql { PRAGMA page_size = 1024 }
! 1285: for {set ii 0} {$ii < 4} {incr ii} { execsql "CREATE TABLE t${ii}(a, b)" }
! 1286: } {}
! 1287: do_test pager1-9.3.2 {
! 1288: sqlite3 db2 test.db2
! 1289:
! 1290: execsql {
! 1291: PRAGMA page_size = 4096;
! 1292: PRAGMA synchronous = OFF;
! 1293: CREATE TABLE t1(a, b);
! 1294: CREATE TABLE t2(a, b);
! 1295: } db2
! 1296:
! 1297: sqlite3_backup B db2 main db main
! 1298: B step 30
! 1299: list [B step 10000] [B finish]
! 1300: } {SQLITE_DONE SQLITE_OK}
! 1301: do_test pager1-9.3.3 {
! 1302: db2 close
! 1303: db close
! 1304: tv delete
! 1305: file size test.db2
! 1306: } [file size test.db]
! 1307:
! 1308: do_test pager1-9.4.1 {
! 1309: faultsim_delete_and_reopen
! 1310: sqlite3 db2 test.db2
! 1311: execsql {
! 1312: PRAGMA page_size = 4096;
! 1313: CREATE TABLE t1(a, b);
! 1314: CREATE TABLE t2(a, b);
! 1315: } db2
! 1316: sqlite3_backup B db2 main db main
! 1317: list [B step 10000] [B finish]
! 1318: } {SQLITE_DONE SQLITE_OK}
! 1319: do_test pager1-9.4.2 {
! 1320: list [file size test.db2] [file size test.db]
! 1321: } {0 0}
! 1322: db2 close
! 1323:
! 1324: #-------------------------------------------------------------------------
! 1325: # Test that regardless of the value returned by xSectorSize(), the
! 1326: # minimum effective sector-size is 512 and the maximum 65536 bytes.
! 1327: #
! 1328: testvfs tv -default 1
! 1329: foreach sectorsize {
! 1330: 32 64 128 256 512 1024 2048
! 1331: 4096 8192 16384 32768 65536 131072 262144
! 1332: } {
! 1333: tv sectorsize $sectorsize
! 1334: tv devchar {}
! 1335: set eff $sectorsize
! 1336: if {$sectorsize < 512} { set eff 512 }
! 1337: if {$sectorsize > 65536} { set eff 65536 }
! 1338:
! 1339: do_test pager1-10.$sectorsize.1 {
! 1340: faultsim_delete_and_reopen
! 1341: db func a_string a_string
! 1342: execsql {
! 1343: PRAGMA journal_mode = PERSIST;
! 1344: PRAGMA page_size = 1024;
! 1345: BEGIN;
! 1346: CREATE TABLE t1(a, b);
! 1347: CREATE TABLE t2(a, b);
! 1348: CREATE TABLE t3(a, b);
! 1349: COMMIT;
! 1350: }
! 1351: file size test.db-journal
! 1352: } [expr $sectorsize > 65536 ? 65536 : $sectorsize]
! 1353:
! 1354: do_test pager1-10.$sectorsize.2 {
! 1355: execsql {
! 1356: INSERT INTO t3 VALUES(a_string(300), a_string(300));
! 1357: INSERT INTO t3 SELECT * FROM t3; /* 2 */
! 1358: INSERT INTO t3 SELECT * FROM t3; /* 4 */
! 1359: INSERT INTO t3 SELECT * FROM t3; /* 8 */
! 1360: INSERT INTO t3 SELECT * FROM t3; /* 16 */
! 1361: INSERT INTO t3 SELECT * FROM t3; /* 32 */
! 1362: }
! 1363: } {}
! 1364:
! 1365: do_test pager1-10.$sectorsize.3 {
! 1366: db close
! 1367: sqlite3 db test.db
! 1368: execsql {
! 1369: PRAGMA cache_size = 10;
! 1370: BEGIN;
! 1371: }
! 1372: recursive_select 32 t3 {db eval "INSERT INTO t2 VALUES(1, 2)"}
! 1373: execsql {
! 1374: COMMIT;
! 1375: SELECT * FROM t2;
! 1376: }
! 1377: } {1 2}
! 1378:
! 1379: do_test pager1-10.$sectorsize.4 {
! 1380: execsql {
! 1381: CREATE TABLE t6(a, b);
! 1382: CREATE TABLE t7(a, b);
! 1383: CREATE TABLE t5(a, b);
! 1384: DROP TABLE t6;
! 1385: DROP TABLE t7;
! 1386: }
! 1387: execsql {
! 1388: BEGIN;
! 1389: CREATE TABLE t6(a, b);
! 1390: }
! 1391: recursive_select 32 t3 {db eval "INSERT INTO t5 VALUES(1, 2)"}
! 1392: execsql {
! 1393: COMMIT;
! 1394: SELECT * FROM t5;
! 1395: }
! 1396: } {1 2}
! 1397:
! 1398: }
! 1399: db close
! 1400:
! 1401: tv sectorsize 4096
! 1402: do_test pager1.10.x.1 {
! 1403: faultsim_delete_and_reopen
! 1404: execsql {
! 1405: PRAGMA auto_vacuum = none;
! 1406: PRAGMA page_size = 1024;
! 1407: CREATE TABLE t1(x);
! 1408: }
! 1409: for {set i 0} {$i<30} {incr i} {
! 1410: execsql { INSERT INTO t1 VALUES(zeroblob(900)) }
! 1411: }
! 1412: file size test.db
! 1413: } {32768}
! 1414: do_test pager1.10.x.2 {
! 1415: execsql {
! 1416: CREATE TABLE t2(x);
! 1417: DROP TABLE t2;
! 1418: }
! 1419: file size test.db
! 1420: } {33792}
! 1421: do_test pager1.10.x.3 {
! 1422: execsql {
! 1423: BEGIN;
! 1424: CREATE TABLE t2(x);
! 1425: }
! 1426: recursive_select 30 t1
! 1427: execsql {
! 1428: CREATE TABLE t3(x);
! 1429: COMMIT;
! 1430: }
! 1431: } {}
! 1432:
! 1433: db close
! 1434: tv delete
! 1435:
! 1436: testvfs tv -default 1
! 1437: faultsim_delete_and_reopen
! 1438: db func a_string a_string
! 1439: do_execsql_test pager1-11.1 {
! 1440: PRAGMA journal_mode = DELETE;
! 1441: PRAGMA cache_size = 10;
! 1442: BEGIN;
! 1443: CREATE TABLE zz(top PRIMARY KEY);
! 1444: INSERT INTO zz VALUES(a_string(222));
! 1445: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
! 1446: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
! 1447: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
! 1448: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
! 1449: INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
! 1450: COMMIT;
! 1451: BEGIN;
! 1452: UPDATE zz SET top = a_string(345);
! 1453: } {delete}
! 1454:
! 1455: proc lockout {method args} { return SQLITE_IOERR }
! 1456: tv script lockout
! 1457: tv filter {xWrite xTruncate xSync}
! 1458: do_catchsql_test pager1-11.2 { COMMIT } {1 {disk I/O error}}
! 1459:
! 1460: tv script {}
! 1461: do_test pager1-11.3 {
! 1462: sqlite3 db2 test.db
! 1463: execsql {
! 1464: PRAGMA journal_mode = TRUNCATE;
! 1465: PRAGMA integrity_check;
! 1466: } db2
! 1467: } {truncate ok}
! 1468: do_test pager1-11.4 {
! 1469: db2 close
! 1470: file exists test.db-journal
! 1471: } {0}
! 1472: do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32}
! 1473: db close
! 1474: tv delete
! 1475:
! 1476: #-------------------------------------------------------------------------
! 1477: # Test "PRAGMA page_size"
! 1478: #
! 1479: testvfs tv -default 1
! 1480: tv sectorsize 1024
! 1481: foreach pagesize {
! 1482: 512 1024 2048 4096 8192 16384 32768
! 1483: } {
! 1484: faultsim_delete_and_reopen
! 1485:
! 1486: # The sector-size (according to the VFS) is 1024 bytes. So if the
! 1487: # page-size requested using "PRAGMA page_size" is greater than the
! 1488: # compile time value of SQLITE_MAX_PAGE_SIZE, then the effective
! 1489: # page-size remains 1024 bytes.
! 1490: #
! 1491: set eff $pagesize
! 1492: if {$eff > $::SQLITE_MAX_PAGE_SIZE} { set eff 1024 }
! 1493:
! 1494: do_test pager1-12.$pagesize.1 {
! 1495: sqlite3 db2 test.db
! 1496: execsql "
! 1497: PRAGMA page_size = $pagesize;
! 1498: CREATE VIEW v AS SELECT * FROM sqlite_master;
! 1499: " db2
! 1500: file size test.db
! 1501: } $eff
! 1502: do_test pager1-12.$pagesize.2 {
! 1503: sqlite3 db2 test.db
! 1504: execsql {
! 1505: SELECT count(*) FROM v;
! 1506: PRAGMA main.page_size;
! 1507: } db2
! 1508: } [list 1 $eff]
! 1509: do_test pager1-12.$pagesize.3 {
! 1510: execsql {
! 1511: SELECT count(*) FROM v;
! 1512: PRAGMA main.page_size;
! 1513: }
! 1514: } [list 1 $eff]
! 1515: db2 close
! 1516: }
! 1517: db close
! 1518: tv delete
! 1519:
! 1520: #-------------------------------------------------------------------------
! 1521: # Test specal "PRAGMA journal_mode=PERSIST" test cases.
! 1522: #
! 1523: # pager1-13.1.*: This tests a special case encountered in persistent
! 1524: # journal mode: If the journal associated with a transaction
! 1525: # is smaller than the journal file (because a previous
! 1526: # transaction left a very large non-hot journal file in the
! 1527: # file-system), then SQLite has to be careful that there is
! 1528: # not a journal-header left over from a previous transaction
! 1529: # immediately following the journal content just written.
! 1530: # If there is, and the process crashes so that the journal
! 1531: # becomes a hot-journal and must be rolled back by another
! 1532: # process, there is a danger that the other process may roll
! 1533: # back the aborted transaction, then continue copying data
! 1534: # from an older transaction from the remainder of the journal.
! 1535: # See the syncJournal() function for details.
! 1536: #
! 1537: # pager1-13.2.*: Same test as the previous. This time, throw an index into
! 1538: # the mix to make the integrity-check more likely to catch
! 1539: # errors.
! 1540: #
! 1541: testvfs tv -default 1
! 1542: tv script xSyncCb
! 1543: tv filter xSync
! 1544: proc xSyncCb {method filename args} {
! 1545: set t [file tail $filename]
! 1546: if {$t == "test.db"} faultsim_save
! 1547: return SQLITE_OK
! 1548: }
! 1549: faultsim_delete_and_reopen
! 1550: db func a_string a_string
! 1551:
! 1552: # The UPDATE statement at the end of this test case creates a really big
! 1553: # journal. Since the cache-size is only 10 pages, the journal contains
! 1554: # frequent journal headers.
! 1555: #
! 1556: do_execsql_test pager1-13.1.1 {
! 1557: PRAGMA page_size = 1024;
! 1558: PRAGMA journal_mode = PERSIST;
! 1559: PRAGMA cache_size = 10;
! 1560: BEGIN;
! 1561: CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);
! 1562: INSERT INTO t1 VALUES(NULL, a_string(400));
! 1563: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 2 */
! 1564: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 4 */
! 1565: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 8 */
! 1566: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 16 */
! 1567: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 32 */
! 1568: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 64 */
! 1569: INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 128 */
! 1570: COMMIT;
! 1571: UPDATE t1 SET b = a_string(400);
! 1572: } {persist}
! 1573:
! 1574: if {$::tcl_platform(platform)!="windows"} {
! 1575: # Run transactions of increasing sizes. Eventually, one (or more than one)
! 1576: # of these will write just enough content that one of the old headers created
! 1577: # by the transaction in the block above lies immediately after the content
! 1578: # journalled by the current transaction.
! 1579: #
! 1580: for {set nUp 1} {$nUp<64} {incr nUp} {
! 1581: do_execsql_test pager1-13.1.2.$nUp.1 {
! 1582: UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
! 1583: } {}
! 1584: do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok}
! 1585:
! 1586: # Try to access the snapshot of the file-system.
! 1587: #
! 1588: sqlite3 db2 sv_test.db
! 1589: do_test pager1-13.1.2.$nUp.3 {
! 1590: execsql { SELECT sum(length(b)) FROM t1 } db2
! 1591: } [expr {128*400 - ($nUp-1)}]
! 1592: do_test pager1-13.1.2.$nUp.4 {
! 1593: execsql { PRAGMA integrity_check } db2
! 1594: } {ok}
! 1595: db2 close
! 1596: }
! 1597: }
! 1598:
! 1599: if {$::tcl_platform(platform)!="windows"} {
! 1600: # Same test as above. But this time with an index on the table.
! 1601: #
! 1602: do_execsql_test pager1-13.2.1 {
! 1603: CREATE INDEX i1 ON t1(b);
! 1604: UPDATE t1 SET b = a_string(400);
! 1605: } {}
! 1606: for {set nUp 1} {$nUp<64} {incr nUp} {
! 1607: do_execsql_test pager1-13.2.2.$nUp.1 {
! 1608: UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
! 1609: } {}
! 1610: do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok}
! 1611: sqlite3 db2 sv_test.db
! 1612: do_test pager1-13.2.2.$nUp.3 {
! 1613: execsql { SELECT sum(length(b)) FROM t1 } db2
! 1614: } [expr {128*400 - ($nUp-1)}]
! 1615: do_test pager1-13.2.2.$nUp.4 {
! 1616: execsql { PRAGMA integrity_check } db2
! 1617: } {ok}
! 1618: db2 close
! 1619: }
! 1620: }
! 1621:
! 1622: db close
! 1623: tv delete
! 1624:
! 1625: #-------------------------------------------------------------------------
! 1626: # Test specal "PRAGMA journal_mode=OFF" test cases.
! 1627: #
! 1628: faultsim_delete_and_reopen
! 1629: do_execsql_test pager1-14.1.1 {
! 1630: PRAGMA journal_mode = OFF;
! 1631: CREATE TABLE t1(a, b);
! 1632: BEGIN;
! 1633: INSERT INTO t1 VALUES(1, 2);
! 1634: COMMIT;
! 1635: SELECT * FROM t1;
! 1636: } {off 1 2}
! 1637: do_catchsql_test pager1-14.1.2 {
! 1638: BEGIN;
! 1639: INSERT INTO t1 VALUES(3, 4);
! 1640: ROLLBACK;
! 1641: } {0 {}}
! 1642: do_execsql_test pager1-14.1.3 {
! 1643: SELECT * FROM t1;
! 1644: } {1 2}
! 1645: do_catchsql_test pager1-14.1.4 {
! 1646: BEGIN;
! 1647: INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
! 1648: INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
! 1649: } {1 {PRIMARY KEY must be unique}}
! 1650: do_execsql_test pager1-14.1.5 {
! 1651: COMMIT;
! 1652: SELECT * FROM t1;
! 1653: } {1 2 2 2}
! 1654:
! 1655: #-------------------------------------------------------------------------
! 1656: # Test opening and closing the pager sub-system with different values
! 1657: # for the sqlite3_vfs.szOsFile variable.
! 1658: #
! 1659: faultsim_delete_and_reopen
! 1660: do_execsql_test pager1-15.0 {
! 1661: CREATE TABLE tx(y, z);
! 1662: INSERT INTO tx VALUES('Ayutthaya', 'Beijing');
! 1663: INSERT INTO tx VALUES('London', 'Tokyo');
! 1664: } {}
! 1665: db close
! 1666: for {set i 0} {$i<513} {incr i 3} {
! 1667: testvfs tv -default 1 -szosfile $i
! 1668: sqlite3 db test.db
! 1669: do_execsql_test pager1-15.$i.1 {
! 1670: SELECT * FROM tx;
! 1671: } {Ayutthaya Beijing London Tokyo}
! 1672: db close
! 1673: tv delete
! 1674: }
! 1675:
! 1676: #-------------------------------------------------------------------------
! 1677: # Check that it is not possible to open a database file if the full path
! 1678: # to the associated journal file will be longer than sqlite3_vfs.mxPathname.
! 1679: #
! 1680: testvfs tv -default 1
! 1681: tv script xOpenCb
! 1682: tv filter xOpen
! 1683: proc xOpenCb {method filename args} {
! 1684: set ::file_len [string length $filename]
! 1685: }
! 1686: sqlite3 db test.db
! 1687: db close
! 1688: tv delete
! 1689:
! 1690: for {set ii [expr $::file_len-5]} {$ii < [expr $::file_len+20]} {incr ii} {
! 1691: testvfs tv -default 1 -mxpathname $ii
! 1692:
! 1693: # The length of the full path to file "test.db-journal" is ($::file_len+8).
! 1694: # If the configured sqlite3_vfs.mxPathname value greater than or equal to
! 1695: # this, then the file can be opened. Otherwise, it cannot.
! 1696: #
! 1697: if {$ii >= [expr $::file_len+8]} {
! 1698: set res {0 {}}
! 1699: } else {
! 1700: set res {1 {unable to open database file}}
! 1701: }
! 1702:
! 1703: do_test pager1-16.1.$ii {
! 1704: list [catch { sqlite3 db test.db } msg] $msg
! 1705: } $res
! 1706:
! 1707: catch {db close}
! 1708: tv delete
! 1709: }
! 1710:
! 1711: #-------------------------------------------------------------------------
! 1712: # Test "PRAGMA omit_readlock".
! 1713: #
! 1714: # pager1-17.$tn.1.*: Test that if a second connection has an open
! 1715: # read-transaction, it is not usually possible to write
! 1716: # the database.
! 1717: #
! 1718: # pager1-17.$tn.2.*: Test that if the second connection was opened with
! 1719: # the SQLITE_OPEN_READONLY flag, and
! 1720: # "PRAGMA omit_readlock = 1" is executed before attaching
! 1721: # the database and opening a read-transaction on it, it is
! 1722: # possible to write the db.
! 1723: #
! 1724: # pager1-17.$tn.3.*: Test that if the second connection was *not* opened with
! 1725: # the SQLITE_OPEN_READONLY flag, executing
! 1726: # "PRAGMA omit_readlock = 1" has no effect.
! 1727: #
! 1728: do_multiclient_test tn {
! 1729: do_test pager1-17.$tn.1.1 {
! 1730: sql1 {
! 1731: CREATE TABLE t1(a, b);
! 1732: INSERT INTO t1 VALUES(1, 2);
! 1733: }
! 1734: sql2 {
! 1735: BEGIN;
! 1736: SELECT * FROM t1;
! 1737: }
! 1738: } {1 2}
! 1739: do_test pager1-17.$tn.1.2 {
! 1740: csql1 { INSERT INTO t1 VALUES(3, 4) }
! 1741: } {1 {database is locked}}
! 1742: do_test pager1-17.$tn.1.3 {
! 1743: sql2 { COMMIT }
! 1744: sql1 { INSERT INTO t1 VALUES(3, 4) }
! 1745: } {}
! 1746:
! 1747: do_test pager1-17.$tn.2.1 {
! 1748: code2 {
! 1749: db2 close
! 1750: sqlite3 db2 :memory: -readonly 1
! 1751: }
! 1752: sql2 {
! 1753: PRAGMA omit_readlock = 1;
! 1754: ATTACH 'test.db' AS two;
! 1755: BEGIN;
! 1756: SELECT * FROM t1;
! 1757: }
! 1758: } {1 2 3 4}
! 1759: do_test pager1-17.$tn.2.2 { sql1 "INSERT INTO t1 VALUES(5, 6)" } {}
! 1760: do_test pager1-17.$tn.2.3 { sql2 "SELECT * FROM t1" } {1 2 3 4}
! 1761: do_test pager1-17.$tn.2.4 { sql2 "COMMIT ; SELECT * FROM t1" } {1 2 3 4 5 6}
! 1762:
! 1763: do_test pager1-17.$tn.3.1 {
! 1764: code2 {
! 1765: db2 close
! 1766: sqlite3 db2 :memory:
! 1767: }
! 1768: sql2 {
! 1769: PRAGMA omit_readlock = 1;
! 1770: ATTACH 'test.db' AS two;
! 1771: BEGIN;
! 1772: SELECT * FROM t1;
! 1773: }
! 1774: } {1 2 3 4 5 6}
! 1775: do_test pager1-17.$tn.3.2 {
! 1776: csql1 { INSERT INTO t1 VALUES(3, 4) }
! 1777: } {1 {database is locked}}
! 1778: do_test pager1-17.$tn.3.3 { sql2 COMMIT } {}
! 1779: }
! 1780:
! 1781: #-------------------------------------------------------------------------
! 1782: # Test the pagers response to the b-tree layer requesting illegal page
! 1783: # numbers:
! 1784: #
! 1785: # + The locking page,
! 1786: # + Page 0,
! 1787: # + A page with a page number greater than (2^31-1).
! 1788: #
! 1789: # These tests will not work if SQLITE_DIRECT_OVERFLOW_READ is defined. In
! 1790: # that case IO errors are sometimes reported instead of SQLITE_CORRUPT.
! 1791: #
! 1792: ifcapable !direct_read {
! 1793: do_test pager1-18.1 {
! 1794: faultsim_delete_and_reopen
! 1795: db func a_string a_string
! 1796: execsql {
! 1797: PRAGMA page_size = 1024;
! 1798: CREATE TABLE t1(a, b);
! 1799: INSERT INTO t1 VALUES(a_string(500), a_string(200));
! 1800: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
! 1801: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
! 1802: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
! 1803: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
! 1804: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
! 1805: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
! 1806: INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
! 1807: }
! 1808: } {}
! 1809: do_test pager1-18.2 {
! 1810: set root [db one "SELECT rootpage FROM sqlite_master"]
! 1811: set lockingpage [expr (0x10000/1024) + 1]
! 1812: execsql {
! 1813: PRAGMA writable_schema = 1;
! 1814: UPDATE sqlite_master SET rootpage = $lockingpage;
! 1815: }
! 1816: sqlite3 db2 test.db
! 1817: catchsql { SELECT count(*) FROM t1 } db2
! 1818: } {1 {database disk image is malformed}}
! 1819: db2 close
! 1820: do_test pager1-18.3 {
! 1821: execsql {
! 1822: CREATE TABLE t2(x);
! 1823: INSERT INTO t2 VALUES(a_string(5000));
! 1824: }
! 1825: set pgno [expr ([file size test.db] / 1024)-2]
! 1826: hexio_write test.db [expr ($pgno-1)*1024] 00000000
! 1827: sqlite3 db2 test.db
! 1828: catchsql { SELECT length(x) FROM t2 } db2
! 1829: } {1 {database disk image is malformed}}
! 1830: db2 close
! 1831: do_test pager1-18.4 {
! 1832: hexio_write test.db [expr ($pgno-1)*1024] 90000000
! 1833: sqlite3 db2 test.db
! 1834: catchsql { SELECT length(x) FROM t2 } db2
! 1835: } {1 {database disk image is malformed}}
! 1836: db2 close
! 1837: do_test pager1-18.5 {
! 1838: sqlite3 db ""
! 1839: execsql {
! 1840: CREATE TABLE t1(a, b);
! 1841: CREATE TABLE t2(a, b);
! 1842: PRAGMA writable_schema = 1;
! 1843: UPDATE sqlite_master SET rootpage=5 WHERE tbl_name = 't1';
! 1844: PRAGMA writable_schema = 0;
! 1845: ALTER TABLE t1 RENAME TO x1;
! 1846: }
! 1847: catchsql { SELECT * FROM x1 }
! 1848: } {1 {database disk image is malformed}}
! 1849: db close
! 1850:
! 1851: do_test pager1-18.6 {
! 1852: faultsim_delete_and_reopen
! 1853: db func a_string a_string
! 1854: execsql {
! 1855: PRAGMA page_size = 1024;
! 1856: CREATE TABLE t1(x);
! 1857: INSERT INTO t1 VALUES(a_string(800));
! 1858: INSERT INTO t1 VALUES(a_string(800));
! 1859: }
! 1860:
! 1861: set root [db one "SELECT rootpage FROM sqlite_master"]
! 1862: db close
! 1863:
! 1864: hexio_write test.db [expr ($root-1)*1024 + 8] 00000000
! 1865: sqlite3 db test.db
! 1866: catchsql { SELECT length(x) FROM t1 }
! 1867: } {1 {database disk image is malformed}}
! 1868: }
! 1869:
! 1870: do_test pager1-19.1 {
! 1871: sqlite3 db ""
! 1872: db func a_string a_string
! 1873: execsql {
! 1874: PRAGMA page_size = 512;
! 1875: PRAGMA auto_vacuum = 1;
! 1876: CREATE TABLE t1(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an,
! 1877: ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn,
! 1878: ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn,
! 1879: da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn,
! 1880: ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en,
! 1881: fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn,
! 1882: ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn,
! 1883: ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn,
! 1884: ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix,
! 1885: ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn,
! 1886: ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn,
! 1887: la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln,
! 1888: ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn
! 1889: );
! 1890: CREATE TABLE t2(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an,
! 1891: ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn,
! 1892: ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn,
! 1893: da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn,
! 1894: ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en,
! 1895: fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn,
! 1896: ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn,
! 1897: ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn,
! 1898: ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix,
! 1899: ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn,
! 1900: ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn,
! 1901: la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln,
! 1902: ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn
! 1903: );
! 1904: INSERT INTO t1(aa) VALUES( a_string(100000) );
! 1905: INSERT INTO t2(aa) VALUES( a_string(100000) );
! 1906: VACUUM;
! 1907: }
! 1908: } {}
! 1909:
! 1910: #-------------------------------------------------------------------------
! 1911: # Test a couple of special cases that come up while committing
! 1912: # transactions:
! 1913: #
! 1914: # pager1-20.1.*: Committing an in-memory database transaction when the
! 1915: # database has not been modified at all.
! 1916: #
! 1917: # pager1-20.2.*: As above, but with a normal db in exclusive-locking mode.
! 1918: #
! 1919: # pager1-20.3.*: Committing a transaction in WAL mode where the database has
! 1920: # been modified, but all dirty pages have been flushed to
! 1921: # disk before the commit.
! 1922: #
! 1923: do_test pager1-20.1.1 {
! 1924: catch {db close}
! 1925: sqlite3 db :memory:
! 1926: execsql {
! 1927: CREATE TABLE one(two, three);
! 1928: INSERT INTO one VALUES('a', 'b');
! 1929: }
! 1930: } {}
! 1931: do_test pager1-20.1.2 {
! 1932: execsql {
! 1933: BEGIN EXCLUSIVE;
! 1934: COMMIT;
! 1935: }
! 1936: } {}
! 1937:
! 1938: do_test pager1-20.2.1 {
! 1939: faultsim_delete_and_reopen
! 1940: execsql {
! 1941: PRAGMA locking_mode = exclusive;
! 1942: PRAGMA journal_mode = persist;
! 1943: CREATE TABLE one(two, three);
! 1944: INSERT INTO one VALUES('a', 'b');
! 1945: }
! 1946: } {exclusive persist}
! 1947: do_test pager1-20.2.2 {
! 1948: execsql {
! 1949: BEGIN EXCLUSIVE;
! 1950: COMMIT;
! 1951: }
! 1952: } {}
! 1953:
! 1954: ifcapable wal {
! 1955: do_test pager1-20.3.1 {
! 1956: faultsim_delete_and_reopen
! 1957: db func a_string a_string
! 1958: execsql {
! 1959: PRAGMA cache_size = 10;
! 1960: PRAGMA journal_mode = wal;
! 1961: BEGIN;
! 1962: CREATE TABLE t1(x);
! 1963: CREATE TABLE t2(y);
! 1964: INSERT INTO t1 VALUES(a_string(800));
! 1965: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */
! 1966: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */
! 1967: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */
! 1968: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */
! 1969: INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */
! 1970: COMMIT;
! 1971: }
! 1972: } {wal}
! 1973: do_test pager1-20.3.2 {
! 1974: execsql {
! 1975: BEGIN;
! 1976: INSERT INTO t2 VALUES('xxxx');
! 1977: }
! 1978: recursive_select 32 t1
! 1979: execsql COMMIT
! 1980: } {}
! 1981: }
! 1982:
! 1983: #-------------------------------------------------------------------------
! 1984: # Test that a WAL database may not be opened if:
! 1985: #
! 1986: # pager1-21.1.*: The VFS has an iVersion less than 2, or
! 1987: # pager1-21.2.*: The VFS does not provide xShmXXX() methods.
! 1988: #
! 1989: ifcapable wal {
! 1990: do_test pager1-21.0 {
! 1991: faultsim_delete_and_reopen
! 1992: execsql {
! 1993: PRAGMA journal_mode = WAL;
! 1994: CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
! 1995: INSERT INTO ko DEFAULT VALUES;
! 1996: }
! 1997: } {wal}
! 1998: do_test pager1-21.1 {
! 1999: testvfs tv -noshm 1
! 2000: sqlite3 db2 test.db -vfs tv
! 2001: catchsql { SELECT * FROM ko } db2
! 2002: } {1 {unable to open database file}}
! 2003: db2 close
! 2004: tv delete
! 2005: do_test pager1-21.2 {
! 2006: testvfs tv -iversion 1
! 2007: sqlite3 db2 test.db -vfs tv
! 2008: catchsql { SELECT * FROM ko } db2
! 2009: } {1 {unable to open database file}}
! 2010: db2 close
! 2011: tv delete
! 2012: }
! 2013:
! 2014: #-------------------------------------------------------------------------
! 2015: # Test that a "PRAGMA wal_checkpoint":
! 2016: #
! 2017: # pager1-22.1.*: is a no-op on a non-WAL db, and
! 2018: # pager1-22.2.*: does not cause xSync calls with a synchronous=off db.
! 2019: #
! 2020: ifcapable wal {
! 2021: do_test pager1-22.1.1 {
! 2022: faultsim_delete_and_reopen
! 2023: execsql {
! 2024: CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
! 2025: INSERT INTO ko DEFAULT VALUES;
! 2026: }
! 2027: execsql { PRAGMA wal_checkpoint }
! 2028: } {0 -1 -1}
! 2029: do_test pager1-22.2.1 {
! 2030: testvfs tv -default 1
! 2031: tv filter xSync
! 2032: tv script xSyncCb
! 2033: proc xSyncCb {args} {incr ::synccount}
! 2034: set ::synccount 0
! 2035: sqlite3 db test.db
! 2036: execsql {
! 2037: PRAGMA synchronous = off;
! 2038: PRAGMA journal_mode = WAL;
! 2039: INSERT INTO ko DEFAULT VALUES;
! 2040: }
! 2041: execsql { PRAGMA wal_checkpoint }
! 2042: set synccount
! 2043: } {0}
! 2044: db close
! 2045: tv delete
! 2046: }
! 2047:
! 2048: #-------------------------------------------------------------------------
! 2049: # Tests for changing journal mode.
! 2050: #
! 2051: # pager1-23.1.*: Test that when changing from PERSIST to DELETE mode,
! 2052: # the journal file is deleted.
! 2053: #
! 2054: # pager1-23.2.*: Same test as above, but while a shared lock is held
! 2055: # on the database file.
! 2056: #
! 2057: # pager1-23.3.*: Same test as above, but while a reserved lock is held
! 2058: # on the database file.
! 2059: #
! 2060: # pager1-23.4.*: And, for fun, while holding an exclusive lock.
! 2061: #
! 2062: # pager1-23.5.*: Try to set various different journal modes with an
! 2063: # in-memory database (only MEMORY and OFF should work).
! 2064: #
! 2065: # pager1-23.6.*: Try to set locking_mode=normal on an in-memory database
! 2066: # (doesn't work - in-memory databases always use
! 2067: # locking_mode=exclusive).
! 2068: #
! 2069: do_test pager1-23.1.1 {
! 2070: faultsim_delete_and_reopen
! 2071: execsql {
! 2072: PRAGMA journal_mode = PERSIST;
! 2073: CREATE TABLE t1(a, b);
! 2074: }
! 2075: file exists test.db-journal
! 2076: } {1}
! 2077: do_test pager1-23.1.2 {
! 2078: execsql { PRAGMA journal_mode = DELETE }
! 2079: file exists test.db-journal
! 2080: } {0}
! 2081:
! 2082: do_test pager1-23.2.1 {
! 2083: execsql {
! 2084: PRAGMA journal_mode = PERSIST;
! 2085: INSERT INTO t1 VALUES('Canberra', 'ACT');
! 2086: }
! 2087: db eval { SELECT * FROM t1 } {
! 2088: db eval { PRAGMA journal_mode = DELETE }
! 2089: }
! 2090: execsql { PRAGMA journal_mode }
! 2091: } {delete}
! 2092: do_test pager1-23.2.2 {
! 2093: file exists test.db-journal
! 2094: } {0}
! 2095:
! 2096: do_test pager1-23.3.1 {
! 2097: execsql {
! 2098: PRAGMA journal_mode = PERSIST;
! 2099: INSERT INTO t1 VALUES('Darwin', 'NT');
! 2100: BEGIN IMMEDIATE;
! 2101: }
! 2102: db eval { PRAGMA journal_mode = DELETE }
! 2103: execsql { PRAGMA journal_mode }
! 2104: } {delete}
! 2105: do_test pager1-23.3.2 {
! 2106: file exists test.db-journal
! 2107: } {0}
! 2108: do_test pager1-23.3.3 {
! 2109: execsql COMMIT
! 2110: } {}
! 2111:
! 2112: do_test pager1-23.4.1 {
! 2113: execsql {
! 2114: PRAGMA journal_mode = PERSIST;
! 2115: INSERT INTO t1 VALUES('Adelaide', 'SA');
! 2116: BEGIN EXCLUSIVE;
! 2117: }
! 2118: db eval { PRAGMA journal_mode = DELETE }
! 2119: execsql { PRAGMA journal_mode }
! 2120: } {delete}
! 2121: do_test pager1-23.4.2 {
! 2122: file exists test.db-journal
! 2123: } {0}
! 2124: do_test pager1-23.4.3 {
! 2125: execsql COMMIT
! 2126: } {}
! 2127:
! 2128: do_test pager1-23.5.1 {
! 2129: faultsim_delete_and_reopen
! 2130: sqlite3 db :memory:
! 2131: } {}
! 2132: foreach {tn mode possible} {
! 2133: 2 off 1
! 2134: 3 memory 1
! 2135: 4 persist 0
! 2136: 5 delete 0
! 2137: 6 wal 0
! 2138: 7 truncate 0
! 2139: } {
! 2140: do_test pager1-23.5.$tn.1 {
! 2141: execsql "PRAGMA journal_mode = off"
! 2142: execsql "PRAGMA journal_mode = $mode"
! 2143: } [if $possible {list $mode} {list off}]
! 2144: do_test pager1-23.5.$tn.2 {
! 2145: execsql "PRAGMA journal_mode = memory"
! 2146: execsql "PRAGMA journal_mode = $mode"
! 2147: } [if $possible {list $mode} {list memory}]
! 2148: }
! 2149: do_test pager1-23.6.1 {
! 2150: execsql {PRAGMA locking_mode = normal}
! 2151: } {exclusive}
! 2152: do_test pager1-23.6.2 {
! 2153: execsql {PRAGMA locking_mode = exclusive}
! 2154: } {exclusive}
! 2155: do_test pager1-23.6.3 {
! 2156: execsql {PRAGMA locking_mode}
! 2157: } {exclusive}
! 2158: do_test pager1-23.6.4 {
! 2159: execsql {PRAGMA main.locking_mode}
! 2160: } {exclusive}
! 2161:
! 2162: #-------------------------------------------------------------------------
! 2163: #
! 2164: do_test pager1-24.1.1 {
! 2165: faultsim_delete_and_reopen
! 2166: db func a_string a_string
! 2167: execsql {
! 2168: PRAGMA cache_size = 10;
! 2169: PRAGMA auto_vacuum = FULL;
! 2170: CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z));
! 2171: CREATE TABLE x2(x, y, z, PRIMARY KEY(y, z));
! 2172: INSERT INTO x2 VALUES(a_string(400), a_string(500), a_string(600));
! 2173: INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
! 2174: INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
! 2175: INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
! 2176: INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
! 2177: INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
! 2178: INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
! 2179: INSERT INTO x1 SELECT * FROM x2;
! 2180: }
! 2181: } {}
! 2182: do_test pager1-24.1.2 {
! 2183: execsql {
! 2184: BEGIN;
! 2185: DELETE FROM x1 WHERE rowid<32;
! 2186: }
! 2187: recursive_select 64 x2
! 2188: } {}
! 2189: do_test pager1-24.1.3 {
! 2190: execsql {
! 2191: UPDATE x1 SET z = a_string(300) WHERE rowid>40;
! 2192: COMMIT;
! 2193: PRAGMA integrity_check;
! 2194: SELECT count(*) FROM x1;
! 2195: }
! 2196: } {ok 33}
! 2197:
! 2198: do_test pager1-24.1.4 {
! 2199: execsql {
! 2200: DELETE FROM x1;
! 2201: INSERT INTO x1 SELECT * FROM x2;
! 2202: BEGIN;
! 2203: DELETE FROM x1 WHERE rowid<32;
! 2204: UPDATE x1 SET z = a_string(299) WHERE rowid>40;
! 2205: }
! 2206: recursive_select 64 x2 {db eval COMMIT}
! 2207: execsql {
! 2208: PRAGMA integrity_check;
! 2209: SELECT count(*) FROM x1;
! 2210: }
! 2211: } {ok 33}
! 2212:
! 2213: do_test pager1-24.1.5 {
! 2214: execsql {
! 2215: DELETE FROM x1;
! 2216: INSERT INTO x1 SELECT * FROM x2;
! 2217: }
! 2218: recursive_select 64 x2 { db eval {CREATE TABLE x3(x, y, z)} }
! 2219: execsql { SELECT * FROM x3 }
! 2220: } {}
! 2221:
! 2222: #-------------------------------------------------------------------------
! 2223: #
! 2224: do_test pager1-25-1 {
! 2225: faultsim_delete_and_reopen
! 2226: execsql {
! 2227: BEGIN;
! 2228: SAVEPOINT abc;
! 2229: CREATE TABLE t1(a, b);
! 2230: ROLLBACK TO abc;
! 2231: COMMIT;
! 2232: }
! 2233: db close
! 2234: } {}
! 2235: breakpoint
! 2236: do_test pager1-25-2 {
! 2237: faultsim_delete_and_reopen
! 2238: execsql {
! 2239: SAVEPOINT abc;
! 2240: CREATE TABLE t1(a, b);
! 2241: ROLLBACK TO abc;
! 2242: COMMIT;
! 2243: }
! 2244: db close
! 2245: } {}
! 2246:
! 2247: #-------------------------------------------------------------------------
! 2248: # Sector-size tests.
! 2249: #
! 2250: do_test pager1-26.1 {
! 2251: testvfs tv -default 1
! 2252: tv sectorsize 4096
! 2253: faultsim_delete_and_reopen
! 2254: db func a_string a_string
! 2255: execsql {
! 2256: PRAGMA page_size = 512;
! 2257: CREATE TABLE tbl(a PRIMARY KEY, b UNIQUE);
! 2258: BEGIN;
! 2259: INSERT INTO tbl VALUES(a_string(25), a_string(600));
! 2260: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
! 2261: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
! 2262: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
! 2263: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
! 2264: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
! 2265: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
! 2266: INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
! 2267: COMMIT;
! 2268: }
! 2269: } {}
! 2270: do_execsql_test pager1-26.1 {
! 2271: UPDATE tbl SET b = a_string(550);
! 2272: } {}
! 2273: db close
! 2274: tv delete
! 2275:
! 2276: #-------------------------------------------------------------------------
! 2277: #
! 2278: do_test pager1.27.1 {
! 2279: faultsim_delete_and_reopen
! 2280: sqlite3_pager_refcounts db
! 2281: execsql {
! 2282: BEGIN;
! 2283: CREATE TABLE t1(a, b);
! 2284: }
! 2285: sqlite3_pager_refcounts db
! 2286: execsql COMMIT
! 2287: } {}
! 2288:
! 2289: #-------------------------------------------------------------------------
! 2290: # Test that attempting to open a write-transaction with
! 2291: # locking_mode=exclusive in WAL mode fails if there are other clients on
! 2292: # the same database.
! 2293: #
! 2294: catch { db close }
! 2295: ifcapable wal {
! 2296: do_multiclient_test tn {
! 2297: do_test pager1-28.$tn.1 {
! 2298: sql1 {
! 2299: PRAGMA journal_mode = WAL;
! 2300: CREATE TABLE t1(a, b);
! 2301: INSERT INTO t1 VALUES('a', 'b');
! 2302: }
! 2303: } {wal}
! 2304: do_test pager1-28.$tn.2 { sql2 { SELECT * FROM t1 } } {a b}
! 2305:
! 2306: do_test pager1-28.$tn.3 { sql1 { PRAGMA locking_mode=exclusive } } {exclusive}
! 2307: do_test pager1-28.$tn.4 {
! 2308: csql1 { BEGIN; INSERT INTO t1 VALUES('c', 'd'); }
! 2309: } {1 {database is locked}}
! 2310: code2 { db2 close ; sqlite3 db2 test.db }
! 2311: do_test pager1-28.$tn.4 {
! 2312: sql1 { INSERT INTO t1 VALUES('c', 'd'); COMMIT }
! 2313: } {}
! 2314: }
! 2315: }
! 2316:
! 2317: #-------------------------------------------------------------------------
! 2318: # Normally, when changing from journal_mode=PERSIST to DELETE the pager
! 2319: # attempts to delete the journal file. However, if it cannot obtain a
! 2320: # RESERVED lock on the database file, this step is skipped.
! 2321: #
! 2322: do_multiclient_test tn {
! 2323: do_test pager1-28.$tn.1 {
! 2324: sql1 {
! 2325: PRAGMA journal_mode = PERSIST;
! 2326: CREATE TABLE t1(a, b);
! 2327: INSERT INTO t1 VALUES('a', 'b');
! 2328: }
! 2329: } {persist}
! 2330: do_test pager1-28.$tn.2 { file exists test.db-journal } 1
! 2331: do_test pager1-28.$tn.3 { sql1 { PRAGMA journal_mode = DELETE } } delete
! 2332: do_test pager1-28.$tn.4 { file exists test.db-journal } 0
! 2333:
! 2334: do_test pager1-28.$tn.5 {
! 2335: sql1 {
! 2336: PRAGMA journal_mode = PERSIST;
! 2337: INSERT INTO t1 VALUES('c', 'd');
! 2338: }
! 2339: } {persist}
! 2340: do_test pager1-28.$tn.6 { file exists test.db-journal } 1
! 2341: do_test pager1-28.$tn.7 {
! 2342: sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); }
! 2343: } {}
! 2344: do_test pager1-28.$tn.8 { file exists test.db-journal } 1
! 2345: do_test pager1-28.$tn.9 { sql1 { PRAGMA journal_mode = DELETE } } delete
! 2346: do_test pager1-28.$tn.10 { file exists test.db-journal } 1
! 2347:
! 2348: do_test pager1-28.$tn.11 { sql2 COMMIT } {}
! 2349: do_test pager1-28.$tn.12 { file exists test.db-journal } 0
! 2350:
! 2351: do_test pager1-28-$tn.13 {
! 2352: code1 { set channel [db incrblob -readonly t1 a 2] }
! 2353: sql1 {
! 2354: PRAGMA journal_mode = PERSIST;
! 2355: INSERT INTO t1 VALUES('g', 'h');
! 2356: }
! 2357: } {persist}
! 2358: do_test pager1-28.$tn.14 { file exists test.db-journal } 1
! 2359: do_test pager1-28.$tn.15 {
! 2360: sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); }
! 2361: } {}
! 2362: do_test pager1-28.$tn.16 { sql1 { PRAGMA journal_mode = DELETE } } delete
! 2363: do_test pager1-28.$tn.17 { file exists test.db-journal } 1
! 2364:
! 2365: do_test pager1-28.$tn.17 { csql2 { COMMIT } } {1 {database is locked}}
! 2366: do_test pager1-28-$tn.18 { code1 { read $channel } } c
! 2367: do_test pager1-28-$tn.19 { code1 { close $channel } } {}
! 2368: do_test pager1-28.$tn.20 { sql2 { COMMIT } } {}
! 2369: }
! 2370:
! 2371: do_test pager1-29.1 {
! 2372: faultsim_delete_and_reopen
! 2373: execsql {
! 2374: PRAGMA page_size = 1024;
! 2375: PRAGMA auto_vacuum = full;
! 2376: PRAGMA locking_mode=exclusive;
! 2377: CREATE TABLE t1(a, b);
! 2378: INSERT INTO t1 VALUES(1, 2);
! 2379: }
! 2380: file size test.db
! 2381: } [expr 1024*3]
! 2382: do_test pager1-29.2 {
! 2383: execsql {
! 2384: PRAGMA page_size = 4096;
! 2385: VACUUM;
! 2386: }
! 2387: file size test.db
! 2388: } [expr 4096*3]
! 2389:
! 2390: #-------------------------------------------------------------------------
! 2391: # Test that if an empty database file (size 0 bytes) is opened in
! 2392: # exclusive-locking mode, any journal file is deleted from the file-system
! 2393: # without being rolled back. And that the RESERVED lock obtained while
! 2394: # doing this is not released.
! 2395: #
! 2396: do_test pager1-30.1 {
! 2397: db close
! 2398: delete_file test.db
! 2399: delete_file test.db-journal
! 2400: set fd [open test.db-journal w]
! 2401: seek $fd [expr 512+1032*2]
! 2402: puts -nonewline $fd x
! 2403: close $fd
! 2404:
! 2405: sqlite3 db test.db
! 2406: execsql {
! 2407: PRAGMA locking_mode=EXCLUSIVE;
! 2408: SELECT count(*) FROM sqlite_master;
! 2409: PRAGMA lock_status;
! 2410: }
! 2411: } {exclusive 0 main reserved temp closed}
! 2412:
! 2413: #-------------------------------------------------------------------------
! 2414: # Test that if the "page-size" field in a journal-header is 0, the journal
! 2415: # file can still be rolled back. This is required for backward compatibility -
! 2416: # versions of SQLite prior to 3.5.8 always set this field to zero.
! 2417: #
! 2418: if {$tcl_platform(platform)=="unix"} {
! 2419: do_test pager1-31.1 {
! 2420: faultsim_delete_and_reopen
! 2421: execsql {
! 2422: PRAGMA cache_size = 10;
! 2423: PRAGMA page_size = 1024;
! 2424: CREATE TABLE t1(x, y, UNIQUE(x, y));
! 2425: INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500));
! 2426: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2427: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2428: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2429: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2430: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2431: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2432: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2433: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2434: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2435: INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
! 2436: BEGIN;
! 2437: UPDATE t1 SET y = randomblob(1499);
! 2438: }
! 2439: copy_file test.db test.db2
! 2440: copy_file test.db-journal test.db2-journal
! 2441:
! 2442: hexio_write test.db2-journal 24 00000000
! 2443: sqlite3 db2 test.db2
! 2444: execsql { PRAGMA integrity_check } db2
! 2445: } {ok}
! 2446: }
! 2447:
! 2448: #-------------------------------------------------------------------------
! 2449: # Test that a database file can be "pre-hinted" to a certain size and that
! 2450: # subsequent spilling of the pager cache does not result in the database
! 2451: # file being shrunk.
! 2452: #
! 2453: catch {db close}
! 2454: forcedelete test.db
! 2455:
! 2456: do_test pager1-32.1 {
! 2457: sqlite3 db test.db
! 2458: execsql {
! 2459: CREATE TABLE t1(x, y);
! 2460: }
! 2461: db close
! 2462: sqlite3 db test.db
! 2463: execsql {
! 2464: BEGIN;
! 2465: INSERT INTO t1 VALUES(1, randomblob(10000));
! 2466: }
! 2467: file_control_chunksize_test db main 1024
! 2468: file_control_sizehint_test db main 20971520; # 20MB
! 2469: execsql {
! 2470: PRAGMA cache_size = 10;
! 2471: INSERT INTO t1 VALUES(1, randomblob(10000));
! 2472: INSERT INTO t1 VALUES(2, randomblob(10000));
! 2473: INSERT INTO t1 SELECT x+2, randomblob(10000) from t1;
! 2474: INSERT INTO t1 SELECT x+4, randomblob(10000) from t1;
! 2475: INSERT INTO t1 SELECT x+8, randomblob(10000) from t1;
! 2476: INSERT INTO t1 SELECT x+16, randomblob(10000) from t1;
! 2477: SELECT count(*) FROM t1;
! 2478: COMMIT;
! 2479: }
! 2480: db close
! 2481: file size test.db
! 2482: } {20971520}
! 2483:
! 2484: # Cleanup 20MB file left by the previous test.
! 2485: forcedelete test.db
! 2486:
! 2487: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to pager1.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