Annotation of embedaddon/sqlite3/test/jrnlmode.test, revision 1.1
1.1 ! misho 1: # 2008 April 17
! 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 focus
! 12: # of these tests is the journal mode pragma.
! 13: #
! 14: # $Id: jrnlmode.test,v 1.16 2009/06/05 17:09:12 drh Exp $
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: ifcapable {!pager_pragmas} {
! 20: finish_test
! 21: return
! 22: }
! 23:
! 24: if {[info exists TEMP_STORE] && $TEMP_STORE>=2} {
! 25: set temp_persist memory
! 26: set temp_delete memory
! 27: set temp_truncate memory
! 28: set temp_off off
! 29: } else {
! 30: set temp_persist persist
! 31: set temp_delete delete
! 32: set temp_truncate truncate
! 33: set temp_off off
! 34: }
! 35:
! 36: proc temp_journal_mode {newmode} {
! 37: if {[info exists ::TEMP_STORE] && $::TEMP_STORE>=2} {
! 38: if {$newmode ne "off" && $newmode ne "memory"} {
! 39: execsql {PRAGMA temp.journal_mode}
! 40: set newmode [db one {PRAGMA temp.journal_mode}]
! 41: }
! 42: }
! 43: set newmode
! 44: }
! 45:
! 46: #----------------------------------------------------------------------
! 47: # Test cases jrnlmode-1.X test the PRAGMA logic.
! 48: #
! 49: do_test jrnlmode-1.0 {
! 50: execsql {
! 51: PRAGMA journal_mode;
! 52: PRAGMA main.journal_mode;
! 53: PRAGMA temp.journal_mode;
! 54: }
! 55: } [list delete delete [temp_journal_mode delete]]
! 56: do_test jrnlmode-1.1 {
! 57: execsql {
! 58: PRAGMA journal_mode = persist;
! 59: }
! 60: } {persist}
! 61: do_test jrnlmode-1.2 {
! 62: execsql {
! 63: PRAGMA journal_mode;
! 64: PRAGMA main.journal_mode;
! 65: PRAGMA temp.journal_mode;
! 66: }
! 67: } [list persist persist [temp_journal_mode persist]]
! 68: do_test jrnlmode-1.4 {
! 69: execsql {
! 70: PRAGMA journal_mode = off;
! 71: }
! 72: } {off}
! 73: do_test jrnlmode-1.5 {
! 74: execsql {
! 75: PRAGMA journal_mode;
! 76: PRAGMA main.journal_mode;
! 77: PRAGMA temp.journal_mode;
! 78: }
! 79: } [list off off [temp_journal_mode off]]
! 80: do_test jrnlmode-1.6 {
! 81: execsql {
! 82: PRAGMA journal_mode = delete;
! 83: }
! 84: } {delete}
! 85: do_test jrnlmode-1.7 {
! 86: execsql {
! 87: PRAGMA journal_mode;
! 88: PRAGMA main.journal_mode;
! 89: PRAGMA Temp.journal_mode;
! 90: }
! 91: } [list delete delete [temp_journal_mode delete]]
! 92: do_test jrnlmode-1.7.1 {
! 93: execsql {
! 94: PRAGMA journal_mode = truncate;
! 95: }
! 96: } {truncate}
! 97: do_test jrnlmode-1.7.2 {
! 98: execsql {
! 99: PRAGMA journal_mode;
! 100: PRAGMA main.journal_mode;
! 101: PRAGMA temp.journal_mode;
! 102: }
! 103: } [list truncate truncate [temp_journal_mode truncate]]
! 104: do_test jrnlmode-1.8 {
! 105: execsql {
! 106: PRAGMA journal_mode = off;
! 107: PRAGMA journal_mode = invalid;
! 108: }
! 109: } {off off}
! 110: ifcapable attach {
! 111: do_test jrnlmode-1.9 {
! 112: execsql {
! 113: PRAGMA journal_mode = PERSIST;
! 114: ATTACH ':memory:' as aux1;
! 115: }
! 116: execsql {
! 117: PRAGMA main.journal_mode;
! 118: PRAGMA aux1.journal_mode;
! 119: }
! 120: } {persist memory}
! 121: do_test jrnlmode-1.10 {
! 122: execsql {
! 123: PRAGMA main.journal_mode = OFF;
! 124: }
! 125: execsql {
! 126: PRAGMA main.journal_mode;
! 127: PRAGMA temp.journal_mode;
! 128: PRAGMA aux1.journal_mode;
! 129: }
! 130: } [list off [temp_journal_mode persist] memory]
! 131: do_test jrnlmode-1.11 {
! 132: execsql {
! 133: PRAGMA journal_mode;
! 134: }
! 135: } {off}
! 136: do_test jrnlmode-1.12 {
! 137: execsql {
! 138: ATTACH ':memory:' as aux2;
! 139: }
! 140: execsql {
! 141: PRAGMA main.journal_mode;
! 142: PRAGMA aux1.journal_mode;
! 143: PRAGMA aux2.journal_mode;
! 144: }
! 145: } {off memory memory}
! 146: do_test jrnlmode-1.13 {
! 147: # The journal-mode used by in-memory databases cannot be changed.
! 148: execsql {
! 149: PRAGMA aux1.journal_mode = DELETE;
! 150: }
! 151: execsql {
! 152: PRAGMA main.journal_mode;
! 153: PRAGMA aux1.journal_mode;
! 154: PRAGMA aux2.journal_mode;
! 155: }
! 156: } {off memory memory}
! 157: do_test jrnlmode-1.14 {
! 158: execsql {
! 159: PRAGMA journal_mode = delete;
! 160: }
! 161: execsql {
! 162: PRAGMA main.journal_mode;
! 163: PRAGMA temp.journal_mode;
! 164: PRAGMA aux1.journal_mode;
! 165: PRAGMA aux2.journal_mode;
! 166: }
! 167: } [list delete [temp_journal_mode delete] memory memory]
! 168: do_test jrnlmode-1.15 {
! 169: execsql {
! 170: ATTACH ':memory:' as aux3;
! 171: }
! 172: execsql {
! 173: PRAGMA main.journal_mode;
! 174: PRAGMA temp.journal_mode;
! 175: PRAGMA aux1.journal_mode;
! 176: PRAGMA aux2.journal_mode;
! 177: PRAGMA aux3.journal_mode;
! 178: }
! 179: } [list delete [temp_journal_mode delete] memory memory memory]
! 180: do_test jrnlmode-1.16 {
! 181: execsql {
! 182: PRAGMA journal_mode = TRUNCATE;
! 183: }
! 184: execsql {
! 185: PRAGMA main.journal_mode;
! 186: PRAGMA temp.journal_mode;
! 187: PRAGMA aux1.journal_mode;
! 188: PRAGMA aux2.journal_mode;
! 189: PRAGMA aux3.journal_mode;
! 190: }
! 191: } [list truncate [temp_journal_mode truncate] memory memory memory]
! 192:
! 193: do_test jrnlmode-1.99 {
! 194: execsql {
! 195: DETACH aux1;
! 196: DETACH aux2;
! 197: DETACH aux3;
! 198: }
! 199: } {}
! 200: }
! 201:
! 202: ifcapable attach {
! 203: forcedelete test2.db
! 204: do_test jrnlmode-2.1 {
! 205: execsql {
! 206: ATTACH 'test2.db' AS aux;
! 207: PRAGMA main.journal_mode = persist;
! 208: PRAGMA aux.journal_mode = persist;
! 209: CREATE TABLE abc(a, b, c);
! 210: CREATE TABLE aux.def(d, e, f);
! 211: }
! 212: execsql {
! 213: BEGIN;
! 214: INSERT INTO abc VALUES(1, 2, 3);
! 215: INSERT INTO def VALUES(4, 5, 6);
! 216: COMMIT;
! 217: }
! 218: list [file exists test.db-journal] [file exists test2.db-journal]
! 219: } {1 1}
! 220:
! 221: do_test jrnlmode-2.2 {
! 222: file size test.db-journal
! 223: } {0}
! 224:
! 225: do_test jrnlmode-2.3 {
! 226: execsql {
! 227: SELECT * FROM abc;
! 228: }
! 229: } {1 2 3}
! 230:
! 231: do_test jrnlmode-2.4 {
! 232: file size test.db-journal
! 233: } {0}
! 234:
! 235: do_test jrnlmode-2.5 {
! 236: execsql {
! 237: SELECT * FROM def;
! 238: }
! 239: } {4 5 6}
! 240:
! 241: #----------------------------------------------------------------------
! 242: # Test caes jrnlmode-3.X verify that ticket #3127 has been fixed.
! 243: #
! 244: db close
! 245: forcedelete test2.db
! 246: forcedelete test.db
! 247: sqlite3 db test.db
! 248:
! 249: do_test jrnlmode-3.1 {
! 250: execsql {
! 251: CREATE TABLE x(n INTEGER);
! 252: ATTACH 'test2.db' AS a;
! 253: create table a.x ( n integer );
! 254: insert into a.x values(1);
! 255: insert into a.x values (2);
! 256: insert into a.x values (3);
! 257: insert into a.x values (4);
! 258: }
! 259: } {}
! 260:
! 261: do_test jrnlmode-3.2 {
! 262: execsql { PRAGMA journal_mode=off; }
! 263: execsql {
! 264: BEGIN IMMEDIATE;
! 265: INSERT OR IGNORE INTO main.x SELECT * FROM a.x;
! 266: COMMIT;
! 267: }
! 268: } {}
! 269: }
! 270:
! 271: ifcapable autovacuum&&pragma {
! 272: db close
! 273: forcedelete test.db
! 274: sqlite3 db test.db
! 275: do_test jrnlmode-4.1 {
! 276: execsql {
! 277: PRAGMA cache_size = 1;
! 278: PRAGMA auto_vacuum = 1;
! 279: CREATE TABLE abc(a, b, c);
! 280: }
! 281: execsql { PRAGMA page_count }
! 282: } {3}
! 283:
! 284: do_test jrnlmode-4.2 {
! 285: execsql { PRAGMA journal_mode = off }
! 286: } {off}
! 287:
! 288: do_test jrnlmode-4.3 {
! 289: execsql { INSERT INTO abc VALUES(1, 2, randomblob(2000)) }
! 290: } {}
! 291:
! 292: # This will attempt to truncate the database file. Check that this
! 293: # is not a problem when journal_mode=off.
! 294: do_test jrnlmode-4.4 {
! 295: execsql { DELETE FROM abc }
! 296: } {}
! 297:
! 298: integrity_check jrnlmode-4.5
! 299: }
! 300:
! 301: #------------------------------------------------------------------------
! 302: # The following test caes, jrnlmode-5.*, test the journal_size_limit
! 303: # pragma.
! 304: ifcapable pragma {
! 305: db close
! 306: forcedelete test.db test2.db test3.db
! 307: sqlite3 db test.db
! 308:
! 309: do_test jrnlmode-5.1 {
! 310: execsql {pragma page_size=1024}
! 311: execsql {pragma journal_mode=persist}
! 312: } {persist}
! 313:
! 314: do_test jrnlmode-5.2 {
! 315: execsql { PRAGMA journal_size_limit }
! 316: } {-1}
! 317: do_test jrnlmode-5.3 {
! 318: execsql {
! 319: ATTACH 'test2.db' AS aux;
! 320: PRAGMA aux.journal_mode=persist;
! 321: PRAGMA aux.journal_size_limit;
! 322: }
! 323: } {persist -1}
! 324: do_test jrnlmode-5.4.1 {
! 325: execsql { PRAGMA aux.journal_size_limit = 999999999999 }
! 326: } {999999999999}
! 327: do_test jrnlmode-5.4.2 {
! 328: execsql { PRAGMA aux.journal_size_limit = 10240 }
! 329: } {10240}
! 330: do_test jrnlmode-5.5 {
! 331: execsql { PRAGMA main.journal_size_limit = 20480 }
! 332: } {20480}
! 333: do_test jrnlmode-5.6 {
! 334: execsql { PRAGMA journal_size_limit }
! 335: } {20480}
! 336: do_test jrnlmode-5.7 {
! 337: execsql { PRAGMA aux.journal_size_limit }
! 338: } {10240}
! 339:
! 340: do_test jrnlmode-5.8 {
! 341: execsql {
! 342: ATTACH 'test3.db' AS aux2;
! 343: PRAGMA aux2.journal_mode=persist;
! 344: }
! 345: } {persist}
! 346:
! 347: do_test jrnlmode-5.9 {
! 348: execsql {
! 349: CREATE TABLE main.t1(a, b, c);
! 350: CREATE TABLE aux.t2(a, b, c);
! 351: CREATE TABLE aux2.t3(a, b, c);
! 352: }
! 353: } {}
! 354: do_test jrnlmode-5.10 {
! 355: list \
! 356: [file exists test.db-journal] \
! 357: [file exists test2.db-journal] \
! 358: [file exists test3.db-journal]
! 359: } {1 1 1}
! 360: do_test jrnlmode-5.11 {
! 361: execsql {
! 362: BEGIN;
! 363: INSERT INTO t3 VALUES(randomblob(1000),randomblob(1000),randomblob(1000));
! 364: INSERT INTO t3
! 365: SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
! 366: INSERT INTO t3
! 367: SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
! 368: INSERT INTO t3
! 369: SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
! 370: INSERT INTO t3
! 371: SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
! 372: INSERT INTO t3
! 373: SELECT randomblob(1000),randomblob(1000),randomblob(1000) FROM t3;
! 374: INSERT INTO t2 SELECT * FROM t3;
! 375: INSERT INTO t1 SELECT * FROM t2;
! 376: COMMIT;
! 377: }
! 378: list \
! 379: [file exists test.db-journal] \
! 380: [file exists test2.db-journal] \
! 381: [file exists test3.db-journal] \
! 382: [file size test.db-journal] \
! 383: [file size test2.db-journal] \
! 384: [file size test3.db-journal]
! 385: } {1 1 1 0 0 0}
! 386:
! 387: do_test jrnlmode-5.12 {
! 388: execsql {
! 389: BEGIN;
! 390: UPDATE t1 SET a = randomblob(1000);
! 391: }
! 392: expr {[file size test.db-journal]>30000}
! 393: } {1}
! 394: do_test jrnlmode-5.13 {
! 395: execsql COMMIT
! 396: file size test.db-journal
! 397: } {20480}
! 398:
! 399: do_test jrnlmode-5.14 {
! 400: execsql {
! 401: BEGIN;
! 402: UPDATE t2 SET a = randomblob(1000);
! 403: }
! 404: expr {[file size test2.db-journal]>30000}
! 405: } {1}
! 406: do_test jrnlmode-5.15 {
! 407: execsql COMMIT
! 408: file size test2.db-journal
! 409: } {10240}
! 410:
! 411: do_test jrnlmode-5.16 {
! 412: execsql {
! 413: BEGIN;
! 414: UPDATE t3 SET a = randomblob(1000);
! 415: }
! 416: set journalsize [file size test3.db-journal]
! 417: expr {$journalsize>30000}
! 418: } {1}
! 419: do_test jrnlmode-5.17 {
! 420: execsql COMMIT
! 421: set sz [file size test3.db-journal]
! 422: expr {$sz>=$journalsize}
! 423: } {1}
! 424:
! 425: do_test jrnlmode-5.18 {
! 426: execsql {
! 427: PRAGMA journal_size_limit = -4;
! 428: BEGIN;
! 429: UPDATE t1 SET a = randomblob(1000);
! 430: }
! 431: set journalsize [file size test.db-journal]
! 432: expr {$journalsize>30000}
! 433: } {1}
! 434: do_test jrnlmode-5.19 {
! 435: execsql COMMIT
! 436: set sz [file size test.db-journal]
! 437: expr {$sz>=$journalsize}
! 438: } {1}
! 439:
! 440: # Test a size-limit of 0.
! 441: #
! 442: do_test jrnlmode-5.20 {
! 443: execsql {
! 444: PRAGMA journal_size_limit = 0;
! 445: BEGIN;
! 446: UPDATE t1 SET a = randomblob(1000);
! 447: }
! 448: } {0}
! 449: do_test jrnlmode-5.21 {
! 450: expr {[file size test.db-journal] > 1024}
! 451: } {1}
! 452: do_test jrnlmode-5.22 {
! 453: execsql COMMIT
! 454: list [file exists test.db-journal] [file size test.db-journal]
! 455: } {1 0}
! 456: }
! 457:
! 458: ifcapable pragma {
! 459: # These tests are not run as part of the "journaltest" permutation,
! 460: # as the test_journal.c layer is incompatible with in-memory journaling.
! 461: if {[permutation] ne "journaltest"} {
! 462:
! 463: do_test jrnlmode-6.1 {
! 464: execsql {
! 465: PRAGMA journal_mode = truncate;
! 466: CREATE TABLE t4(a, b);
! 467: BEGIN;
! 468: INSERT INTO t4 VALUES(1, 2);
! 469: PRAGMA journal_mode = memory;
! 470: }
! 471: } {truncate truncate}
! 472: do_test jrnlmode-6.2 {
! 473: file exists test.db-journal
! 474: } {1}
! 475: do_test jrnlmode-6.3 {
! 476: execsql {
! 477: COMMIT;
! 478: SELECT * FROM t4;
! 479: }
! 480: } {1 2}
! 481: do_test jrnlmode-6.4 {
! 482: file exists test.db-journal
! 483: } {1}
! 484: do_test jrnlmode-6.5 {
! 485: execsql {
! 486: PRAGMA journal_mode = MEMORY;
! 487: BEGIN;
! 488: INSERT INTO t4 VALUES(3, 4);
! 489: }
! 490: file exists test.db-journal
! 491: } {0}
! 492: do_test jrnlmode-6.7 {
! 493: execsql {
! 494: COMMIT;
! 495: SELECT * FROM t4;
! 496: }
! 497: } {1 2 3 4}
! 498: do_test jrnlmode-6.8 {
! 499: file exists test.db-journal
! 500: } {0}
! 501: do_test jrnlmode-6.9 {
! 502: execsql {
! 503: PRAGMA journal_mode = DELETE;
! 504: BEGIN IMMEDIATE; INSERT INTO t4 VALUES(1,2); COMMIT;
! 505: }
! 506: file exists test.db-journal
! 507: } {0}
! 508: }
! 509: }
! 510:
! 511: ifcapable pragma {
! 512: catch { db close }
! 513: do_test jrnlmode-7.1 {
! 514: foreach f [glob -nocomplain test.db*] { forcedelete $f }
! 515: sqlite3 db test.db
! 516: execsql {
! 517: PRAGMA journal_mode = memory;
! 518: PRAGMA auto_vacuum = 0;
! 519: PRAGMA page_size = 1024;
! 520: PRAGMA user_version = 5;
! 521: PRAGMA user_version;
! 522: }
! 523: } {memory 5}
! 524: do_test jrnlmode-7.2 { file size test.db } {1024}
! 525: }
! 526:
! 527: do_execsql_test jrnlmode-8.1 { PRAGMA locking_mode=EXCLUSIVE } {exclusive}
! 528: do_execsql_test jrnlmode-8.2 { CREATE TABLE t1(x) } {}
! 529: do_execsql_test jrnlmode-8.3 { INSERT INTO t1 VALUES(123) } {}
! 530: do_execsql_test jrnlmode-8.4 { SELECT * FROM t1 } {123}
! 531: do_execsql_test jrnlmode-8.5 { PRAGMA journal_mode=PERSIST } {persist}
! 532: do_execsql_test jrnlmode-8.6 { PRAGMA journal_mode=DELETE } {delete}
! 533: do_execsql_test jrnlmode-8.7 { PRAGMA journal_mode=TRUNCATE } {truncate}
! 534: do_execsql_test jrnlmode-8.8 { PRAGMA journal_mode=DELETE } {delete}
! 535: do_execsql_test jrnlmode-8.9 { CREATE TABLE t2(y) } {}
! 536: do_execsql_test jrnlmode-8.10 { INSERT INTO t2 VALUES(456) } {}
! 537: do_execsql_test jrnlmode-8.11 { SELECT * FROM t1, t2 } {123 456}
! 538: do_execsql_test jrnlmode-8.12 { PRAGMA locking_mode=NORMAL } {normal}
! 539: do_execsql_test jrnlmode-8.13 { PRAGMA journal_mode=PERSIST } {persist}
! 540: do_execsql_test jrnlmode-8.14 { PRAGMA journal_mode=TRUNCATE } {truncate}
! 541: do_execsql_test jrnlmode-8.15 { PRAGMA journal_mode=PERSIST } {persist}
! 542: do_execsql_test jrnlmode-8.16 { PRAGMA journal_mode=DELETE } {delete}
! 543: do_execsql_test jrnlmode-8.17 { PRAGMA journal_mode=TRUNCATE } {truncate}
! 544: do_execsql_test jrnlmode-8.18 { PRAGMA locking_mode=EXCLUSIVE } {exclusive}
! 545: do_execsql_test jrnlmode-8.19 { CREATE TABLE t3(z) } {}
! 546: do_execsql_test jrnlmode-8.20 { BEGIN IMMEDIATE } {}
! 547: do_execsql_test jrnlmode-8.21 { PRAGMA journal_mode=DELETE } {delete}
! 548: do_execsql_test jrnlmode-8.22 { COMMIT } {}
! 549: do_execsql_test jrnlmode-8.23 { PRAGMA journal_mode=DELETE } {delete}
! 550: do_execsql_test jrnlmode-8.24 { PRAGMA journal_mode=TRUNCATE } {truncate}
! 551: do_execsql_test jrnlmode-8.25 { PRAGMA locking_mode=NORMAL } {normal}
! 552: do_execsql_test jrnlmode-8.26 { CREATE TABLE t4(w) } {}
! 553: do_execsql_test jrnlmode-8.27 { BEGIN IMMEDIATE } {}
! 554: do_execsql_test jrnlmode-8.28 { PRAGMA journal_mode=DELETE } {delete}
! 555: do_execsql_test jrnlmode-8.29 { COMMIT } {}
! 556: do_execsql_test jrnlmode-8.30 { PRAGMA journal_mode=DELETE } {delete}
! 557:
! 558: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to jrnlmode.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