# 2010 April 22 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the operation of the library in # "PRAGMA journal_mode=WAL" mode. # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/wal_common.tcl source $testdir/malloc_common.tcl do_not_use_codec ifcapable !wal {finish_test ; return } # Test organization: # # walback-1.*: Simple tests. # # walback-2.*: Test backups when the source db is modified mid-backup. # # walback-3.*: Backup of WAL sources into rollback destinations, and # vice-versa. # # Make sure a simple backup from a WAL database works. # do_test walbak-1.0 { execsql { PRAGMA synchronous = NORMAL; PRAGMA page_size = 1024; PRAGMA auto_vacuum = 0; PRAGMA journal_mode = wal; BEGIN; CREATE TABLE t1(a PRIMARY KEY, b); INSERT INTO t1 VALUES('I', 'one'); COMMIT; } } {wal} do_test walbak-1.1 { file delete -force bak.db bak.db-journal bak.db-wal db backup bak.db file size bak.db } [expr 3*1024] do_test walbak-1.2 { sqlite3 db2 bak.db execsql { SELECT * FROM t1; PRAGMA main.journal_mode; } db2 } {I one wal} do_test walbak-1.3 { execsql { PRAGMA integrity_check } db2 } {ok} db2 close # Try a VACUUM on a WAL database. # do_test walbak-1.4 { execsql { VACUUM; PRAGMA main.journal_mode; } } {wal} do_test walbak-1.5 { list [file size test.db] [file size test.db-wal] } [list 1024 [wal_file_size 6 1024]] do_test walbak-1.6 { execsql { PRAGMA wal_checkpoint } list [file size test.db] [file size test.db-wal] } [list [expr 3*1024] [wal_file_size 6 1024]] do_test walbak-1.7 { execsql { CREATE TABLE t2(a, b); INSERT INTO t2 SELECT * FROM t1; DROP TABLE t1; } list [file size test.db] [file size test.db-wal] } [list [expr 3*1024] [wal_file_size 6 1024]] do_test walbak-1.8 { execsql { VACUUM } list [file size test.db] [file size test.db-wal] } [list [expr 3*1024] [wal_file_size 8 1024]] do_test walbak-1.9 { execsql { PRAGMA wal_checkpoint } list [file size test.db] [file size test.db-wal] } [list [expr 2*1024] [wal_file_size 8 1024]] #------------------------------------------------------------------------- # Backups when the source db is modified mid-backup. # proc sig {{db db}} { $db eval { PRAGMA integrity_check; SELECT md5sum(a, b) FROM t1; } } db close file delete test.db sqlite3 db test.db do_test walbak-2.1 { execsql { PRAGMA journal_mode = WAL } execsql { CREATE TABLE t1(a PRIMARY KEY, b); BEGIN; INSERT INTO t1 VALUES(randomblob(500), randomblob(500)); INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 2 */ INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 4 */ INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 8 */ INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 16 */ INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 32 */ INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 64 */ COMMIT; } } {} do_test walbak-2.2 { db backup abc.db sqlite3 db2 abc.db string compare [sig db] [sig db2] } {0} do_test walbak-2.3 { sqlite3_backup B db2 main db main B step 50 execsql { UPDATE t1 SET b = randomblob(500) } list [B step 1000] [B finish] } {SQLITE_DONE SQLITE_OK} do_test walbak-2.4 { string compare [sig db] [sig db2] } {0} do_test walbak-2.5 { db close sqlite3 db test.db execsql { PRAGMA cache_size = 10 } sqlite3_backup B db2 main db main B step 50 execsql { BEGIN; UPDATE t1 SET b = randomblob(500); } expr [file size test.db-wal] > 10*1024 } {1} do_test walbak-2.6 { B step 1000 } {SQLITE_BUSY} do_test walbak-2.7 { execsql COMMIT list [B step 1000] [B finish] } {SQLITE_DONE SQLITE_OK} do_test walbak-2.8 { string compare [sig db] [sig db2] } {0} do_test walbak-2.9 { db close sqlite3 db test.db execsql { PRAGMA cache_size = 10 } sqlite3_backup B db2 main db main B step 50 execsql { BEGIN; UPDATE t1 SET b = randomblob(500); } expr [file size test.db-wal] > 10*1024 } {1} do_test walbak-2.10 { B step 1000 } {SQLITE_BUSY} do_test walbak-2.11 { execsql ROLLBACK set sigB [sig db] list [B step 1000] [B finish] } {SQLITE_DONE SQLITE_OK} do_test walbak-2.12 { string compare [sig db] [sig db2] } {0} db2 close db close #------------------------------------------------------------------------- # Run some backup operations to copy back and forth between WAL and: # # walbak-3.1.*: an in-memory database # # walbak-3.2.*: a temporary database # # walbak-3.3.*: a database in rollback mode. # # walbak-3.4.*: a database in rollback mode that (initially) uses a # different page-size. # # Check that this does not confuse any connected clients. # foreach {tn setup} { 1 { sqlite3 db test.db sqlite3 db2 :memory: db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL } db2 eval { PRAGMA page_size = 1024 } } 2 { sqlite3 db test.db sqlite3 db2 "" db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL } db2 eval { PRAGMA page_size = 1024 } } 3 { sqlite3 db test.db sqlite3 db2 test.db2 db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL } db2 eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = PERSIST } } 4 { sqlite3 db test.db sqlite3 db2 test.db2 db eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL } db2 eval { PRAGMA page_size = 2048; PRAGMA journal_mode = PERSIST; CREATE TABLE xx(x); } } } { foreach f [glob -nocomplain test.db*] { file delete -force $f } eval $setup do_test walbak-3.$tn.1 { execsql { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); SELECT * FROM t1; } } {1 2 3 4} do_test walbak-3.$tn.2 { sqlite3_backup B db2 main db main B step 10000 B finish execsql { SELECT * FROM t1 } db2 } {1 2 3 4} do_test walbak-3.$tn.3 { execsql { INSERT INTO t1 VALUES(5, 6); INSERT INTO t1 VALUES(7, 8); SELECT * FROM t1; } db2 } {1 2 3 4 5 6 7 8} do_test walbak-3.$tn.4 { sqlite3_backup B db main db2 main B step 10000 B finish execsql { SELECT * FROM t1 } } {1 2 3 4 5 6 7 8} db close db2 close } finish_test