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