AryaWu/sqlite
0
1# 2008 April 172#3# The author disclaims copyright to this source code. In place of4# 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 focus12# 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 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19ifcapable {!pager_pragmas} {20 finish_test21 return22}23 24if {[info exists TEMP_STORE] && $TEMP_STORE>=2} {25 set temp_persist memory26 set temp_delete memory27 set temp_truncate memory28 set temp_off off29} else {30 set temp_persist persist31 set temp_delete delete32 set temp_truncate truncate33 set temp_off off34}35 36proc 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 newmode44}45 46#----------------------------------------------------------------------47# Test cases jrnlmode-1.X test the PRAGMA logic.48#49do_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]]56do_test jrnlmode-1.1 {57 execsql {58 PRAGMA journal_mode = persist;59 } 60} {persist}61do_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]]68do_test jrnlmode-1.4a {69 # When defensive is on, unable to set journal_mode to OFF70 sqlite3_db_config db DEFENSIVE 171 execsql {72 PRAGMA journal_mode = off;73 } 74} {persist}75do_test jrnlmode-1.4b {76 # When defensive is on, unable to set journal_mode to OFF77 sqlite3_db_config db DEFENSIVE 078 execsql {79 PRAGMA journal_mode = off;80 } 81} {off}82do_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]]89do_test jrnlmode-1.6 {90 execsql {91 PRAGMA journal_mode = delete;92 } 93} {delete}94do_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]]101do_test jrnlmode-1.7.1 {102 execsql {103 PRAGMA journal_mode = truncate;104 } 105} {truncate}106do_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]]113do_test jrnlmode-1.8 {114 execsql {115 PRAGMA journal_mode = off;116 PRAGMA journal_mode = invalid;117 } 118} {off off}119ifcapable 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 211ifcapable attach {212 forcedelete test2.db213 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-journal232 } {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-journal242 } {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 close254 forcedelete test2.db255 forcedelete test.db256 sqlite3 db test.db257 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 280ifcapable autovacuum&&pragma {281 db close282 forcedelete test.db283 sqlite3 db test.db284 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 this302 # 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.5308}309 310#------------------------------------------------------------------------311# The following test caes, jrnlmode-5.*, test the journal_size_limit312# pragma.313ifcapable pragma {314if {[atomic_batch_write test.db]==0} {315 db close316 forcedelete test.db test2.db test3.db317 sqlite3 db test.db318 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 COMMIT406 file size test.db-journal407 } {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 COMMIT418 file size test2.db-journal419 } {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 COMMIT431 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 COMMIT446 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 COMMIT464 list [file exists test.db-journal] [file size test.db-journal]465 } {1 0}466}467}468 469ifcapable pragma {470if {[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-journal486 } {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-journal495 } {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-journal503 } {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-journal512 } {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-journal519 } {0}520 }521}522}523 524ifcapable pragma {525 catch { db close }526 do_test jrnlmode-7.1 {527 foreach f [glob -nocomplain test.db*] { forcedelete $f }528 sqlite3 db test.db529 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 540do_execsql_test jrnlmode-8.1 { PRAGMA locking_mode=EXCLUSIVE } {exclusive}541do_execsql_test jrnlmode-8.2 { CREATE TABLE t1(x) } {}542do_execsql_test jrnlmode-8.3 { INSERT INTO t1 VALUES(123) } {}543do_execsql_test jrnlmode-8.4 { SELECT * FROM t1 } {123}544do_execsql_test jrnlmode-8.5 { PRAGMA journal_mode=PERSIST } {persist}545do_execsql_test jrnlmode-8.6 { PRAGMA journal_mode=DELETE } {delete}546do_execsql_test jrnlmode-8.7 { PRAGMA journal_mode=TRUNCATE } {truncate}547do_execsql_test jrnlmode-8.8 { PRAGMA journal_mode=DELETE } {delete}548do_execsql_test jrnlmode-8.9 { CREATE TABLE t2(y) } {}549do_execsql_test jrnlmode-8.10 { INSERT INTO t2 VALUES(456) } {}550do_execsql_test jrnlmode-8.11 { SELECT * FROM t1, t2 } {123 456}551do_execsql_test jrnlmode-8.12 { PRAGMA locking_mode=NORMAL } {normal}552do_execsql_test jrnlmode-8.13 { PRAGMA journal_mode=PERSIST } {persist}553do_execsql_test jrnlmode-8.14 { PRAGMA journal_mode=TRUNCATE } {truncate}554do_execsql_test jrnlmode-8.15 { PRAGMA journal_mode=PERSIST } {persist}555do_execsql_test jrnlmode-8.16 { PRAGMA journal_mode=DELETE } {delete}556do_execsql_test jrnlmode-8.17 { PRAGMA journal_mode=TRUNCATE } {truncate}557do_execsql_test jrnlmode-8.18 { PRAGMA locking_mode=EXCLUSIVE } {exclusive}558do_execsql_test jrnlmode-8.19 { CREATE TABLE t3(z) } {}559do_execsql_test jrnlmode-8.20 { BEGIN IMMEDIATE } {}560do_execsql_test jrnlmode-8.21 { PRAGMA journal_mode=DELETE } {delete}561do_execsql_test jrnlmode-8.22 { COMMIT } {}562do_execsql_test jrnlmode-8.23 { PRAGMA journal_mode=DELETE } {delete}563do_execsql_test jrnlmode-8.24 { PRAGMA journal_mode=TRUNCATE } {truncate}564do_execsql_test jrnlmode-8.25 { PRAGMA locking_mode=NORMAL } {normal}565do_execsql_test jrnlmode-8.26 { CREATE TABLE t4(w) } {}566do_execsql_test jrnlmode-8.27 { BEGIN IMMEDIATE } {}567do_execsql_test jrnlmode-8.28 { PRAGMA journal_mode=DELETE } {delete}568do_execsql_test jrnlmode-8.29 { COMMIT } {}569do_execsql_test jrnlmode-8.30 { PRAGMA journal_mode=DELETE } {delete}570 571# Assertion fault on 2015-05-01572do_test jrnlmode-9.1 {573 forcedelete test2.db574 sqlite3 db2 test2.db575 db2 eval {CREATE TEMP TABLE t(l); PRAGMA journal_mode=off;}576 db2 close577} {}578do_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 585finish_test586