185a83755Sdrh# 2011 May 16 285a83755Sdrh# 385a83755Sdrh# The author disclaims copyright to this source code. In place of 485a83755Sdrh# a legal notice, here is a blessing: 585a83755Sdrh# 685a83755Sdrh# May you do good and not evil. 785a83755Sdrh# May you find forgiveness for yourself and forgive others. 885a83755Sdrh# May you share freely, never taking more than you give. 985a83755Sdrh# 1085a83755Sdrh#*********************************************************************** 1185a83755Sdrh# This file implements regression tests for SQLite library. The 1285a83755Sdrh# focus of this file is testing the PRAGMA journal_size_limit when 1385a83755Sdrh# in WAL mode. 1485a83755Sdrh# 1585a83755Sdrh 1685a83755Sdrhset testdir [file dirname $argv0] 1785a83755Sdrhsource $testdir/tester.tcl 1885a83755Sdrhifcapable !wal {finish_test ; return } 1985a83755Sdrh 2085a83755Sdrh# Case 1: No size limit. Journal can get large. 2185a83755Sdrh# 2285a83755Sdrhdo_test wal7-1.0 { 2385a83755Sdrh db close 2485a83755Sdrh forcedelete test.db 2585a83755Sdrh sqlite3 db test.db 2685a83755Sdrh db eval { 2785a83755Sdrh PRAGMA page_size=1024; 2885a83755Sdrh PRAGMA journal_mode=WAL; 2985a83755Sdrh PRAGMA wal_autocheckpoint=50; -- 50 pages 3085a83755Sdrh CREATE TABLE t1(x, y UNIQUE); 3185a83755Sdrh INSERT INTO t1 VALUES(1,2); 3285a83755Sdrh INSERT INTO t1 VALUES(zeroblob(200000),4); 3385a83755Sdrh CREATE TABLE t2(z); 3485a83755Sdrh DELETE FROM t1; 3585a83755Sdrh INSERT INTO t2 SELECT x FROM t1; 3685a83755Sdrh } 3785a83755Sdrh expr {[file size test.db-wal]>50*1100} 3885a83755Sdrh} 1 3985a83755Sdrhdo_test wal7-1.1 { 4085a83755Sdrh db eval {PRAGMA wal_checkpoint} 4185a83755Sdrh expr {[file size test.db-wal]>50*1100} 4285a83755Sdrh} 1 4385a83755Sdrhdo_test wal7-1.2 { 4485a83755Sdrh db eval {INSERT INTO t2 VALUES('hi');} 4585a83755Sdrh expr {[file size test.db-wal]>50*1100} 4685a83755Sdrh} 1 4785a83755Sdrh 4885a83755Sdrh# Case 2: Size limit at half the autocheckpoint size. 4985a83755Sdrh# 5085a83755Sdrhdo_test wal7-2.0 { 5185a83755Sdrh db close 5285a83755Sdrh forcedelete test.db 5385a83755Sdrh sqlite3 db test.db 5485a83755Sdrh db eval { 5585a83755Sdrh PRAGMA page_size=1024; 5685a83755Sdrh PRAGMA journal_mode=WAL; 5785a83755Sdrh PRAGMA wal_autocheckpoint=50; -- 50 pages 5885a83755Sdrh PRAGMA journal_size_limit=25000; 5985a83755Sdrh CREATE TABLE t1(x, y UNIQUE); 6085a83755Sdrh INSERT INTO t1 VALUES(1,2); 6185a83755Sdrh INSERT INTO t1 VALUES(zeroblob(200000),4); 6285a83755Sdrh CREATE TABLE t2(z); 6385a83755Sdrh DELETE FROM t1; 647c65a882Sdrh INSERT INTO t2 VALUES(1); 6585a83755Sdrh } 6685a83755Sdrh file size test.db-wal 6785a83755Sdrh} 25000 6885a83755Sdrh 6985a83755Sdrh 7085a83755Sdrh# Case 3: Size limit of zero. 7185a83755Sdrh# 7285a83755Sdrhdo_test wal7-3.0 { 7385a83755Sdrh db close 7485a83755Sdrh forcedelete test.db 7585a83755Sdrh sqlite3 db test.db 7685a83755Sdrh db eval { 7785a83755Sdrh PRAGMA page_size=1024; 7885a83755Sdrh PRAGMA journal_mode=WAL; 7985a83755Sdrh PRAGMA wal_autocheckpoint=50; -- 50 pages 8085a83755Sdrh PRAGMA journal_size_limit=0; 8185a83755Sdrh CREATE TABLE t1(x, y UNIQUE); 8285a83755Sdrh INSERT INTO t1 VALUES(1,2); 8385a83755Sdrh INSERT INTO t1 VALUES(zeroblob(200000),4); 8485a83755Sdrh CREATE TABLE t2(z); 8585a83755Sdrh DELETE FROM t1; 867c65a882Sdrh INSERT INTO t2 VALUES(1); 8785a83755Sdrh } 8885a83755Sdrh set sz [file size test.db-wal] 89*518d6565Sdrh expr {$sz>0 && $sz<13700} 9085a83755Sdrh} 1 9185a83755Sdrh 9285a83755Sdrh 9385a83755Sdrh# Case 4: Size limit set before going WAL 9485a83755Sdrh# 9585a83755Sdrhdo_test wal7-4.0 { 9685a83755Sdrh db close 9785a83755Sdrh forcedelete test.db 9885a83755Sdrh sqlite3 db test.db 9985a83755Sdrh db eval { 10085a83755Sdrh PRAGMA page_size=1024; 10185a83755Sdrh PRAGMA journal_size_limit=25000; 10285a83755Sdrh PRAGMA journal_mode=WAL; 10385a83755Sdrh PRAGMA wal_autocheckpoint=50; -- 50 pages 10485a83755Sdrh CREATE TABLE t1(x, y UNIQUE); 10585a83755Sdrh INSERT INTO t1 VALUES(1,2); 10685a83755Sdrh INSERT INTO t1 VALUES(zeroblob(200000),4); 10785a83755Sdrh CREATE TABLE t2(z); 10885a83755Sdrh DELETE FROM t1; 1097c65a882Sdrh INSERT INTO t2 VALUES(1); 11085a83755Sdrh } 11185a83755Sdrh set sz [file size test.db-wal] 11285a83755Sdrh} 25000 11385a83755Sdrh 11485a83755Sdrh 11585a83755Sdrh 11685a83755Sdrh 11785a83755Sdrh 11885a83755Sdrhfinish_test 119