1# 2010 May 5 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 12# focus of this file is testing the operation of the library in 13# "PRAGMA journal_mode=WAL" mode. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18source $testdir/wal_common.tcl 19set testprefix waloverwrite 20 21ifcapable !wal {finish_test ; return } 22 23# Simple test: 24# 25# Test cases *.1 - *.6: 26# 27# + Create a database of blobs roughly 50 pages in size. 28# 29# + Set the db cache size to something much smaller than this (5 pages) 30# 31# + Within a transaction, loop through the set of blobs 5 times. Update 32# each blob as it is visited. 33# 34# + Test that the wal file is roughly 50 pages in size - even though many 35# database pages have been written to it multiple times. 36# 37# + Take a copy of the database and wal file. Test that recovery can 38# be run on it. 39# 40# Test cases *.7 - *.9: 41# 42# + Same thing, but before committing the statement transaction open 43# a SAVEPOINT, update the blobs another 5 times, then roll it back. 44# 45# + Check that if recovery is run on the resulting wal file, the rolled 46# back changes from within the SAVEPOINT are not present in the db. 47# 48# The above is run twice - once where the wal file is empty at the start of 49# step 3 (tn==1) and once where it already contains a transaction (tn==2). 50# 51foreach {tn xtra} { 52 1 {} 53 2 { UPDATE t1 SET y = randomblob(799) WHERE x=4 } 54} { 55 reset_db 56 do_execsql_test 1.$tn.0 { 57 CREATE TABLE t1(x, y); 58 CREATE TABLE t2(x, y); 59 CREATE INDEX i1y ON t1(y); 60 61 WITH cnt(i) AS ( 62 SELECT 1 UNION ALL SELECT i+1 FROM cnt WHERE i<20 63 ) 64 INSERT INTO t1 SELECT i, randomblob(800) FROM cnt; 65 } {} 66 67 do_test 1.$tn.1 { 68 set nPg [db one { PRAGMA page_count } ] 69 expr $nPg>40 && $nPg<50 70 } {1} 71 72 do_test 1.$tn.2 { 73 db close 74 sqlite3 db test.db 75 76 execsql {PRAGMA journal_mode = wal} 77 execsql {PRAGMA cache_size = 5} 78 execsql $xtra 79 80 db transaction { 81 for {set i 0} {$i < 5} {incr i} { 82 foreach x [db eval {SELECT x FROM t1}] { 83 execsql { UPDATE t1 SET y = randomblob(799) WHERE x=$x } 84 } 85 } 86 } 87 88 set nPg [wal_frame_count test.db-wal 1024] 89 expr $nPg>40 && $nPg<60 90 } {1} 91 92 do_execsql_test 1.$tn.3 { PRAGMA integrity_check } ok 93 94 do_test 1.$tn.4 { 95 forcedelete test.db2 test.db2-wal 96 forcecopy test.db test.db2 97 sqlite3 db2 test.db2 98 execsql { SELECT sum(length(y)) FROM t1 } db2 99 } [expr 20*800] 100 101 do_test 1.$tn.5 { 102 db2 close 103 forcecopy test.db test.db2 104 forcecopy test.db-wal test.db2-wal 105 sqlite3 db2 test.db2 106 execsql { SELECT sum(length(y)) FROM t1 } db2 107 } [expr 20*799] 108 109 do_test 1.$tn.6 { 110 execsql { PRAGMA integrity_check } db2 111 } ok 112 db2 close 113 114 do_test 1.$tn.7 { 115 execsql { PRAGMA wal_checkpoint } 116 db transaction { 117 for {set i 0} {$i < 1} {incr i} { 118 foreach x [db eval {SELECT x FROM t1}] { 119 execsql { UPDATE t1 SET y = randomblob(798) WHERE x=$x } 120 } 121 } 122 123 execsql { 124 WITH cnt(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM cnt WHERE i<20) 125 INSERT INTO t2 SELECT i, randomblob(800) FROM cnt; 126 } 127 128 execsql {SAVEPOINT abc} 129 for {set i 0} {$i < 5} {incr i} { 130 foreach x [db eval {SELECT x FROM t1}] { 131 execsql { UPDATE t1 SET y = randomblob(797) WHERE x=$x } 132 } 133 } 134 execsql {ROLLBACK TO abc} 135 136 } 137 138 set nPg [wal_frame_count test.db-wal 1024] 139 expr $nPg>55 && $nPg<75 140 } {1} 141 142 do_test 1.$tn.8 { 143 forcedelete test.db2 test.db2-wal 144 forcecopy test.db test.db2 145 sqlite3 db2 test.db2 146 execsql { SELECT sum(length(y)) FROM t1 } db2 147 } [expr 20*799] 148 149 do_test 1.$tn.9 { 150 db2 close 151 forcecopy test.db-wal test.db2-wal 152 sqlite3 db2 test.db2 153 execsql { SELECT sum(length(y)) FROM t1 } db2 154 } [expr 20*798] 155 156 do_test 1.$tn.10 { 157 execsql { PRAGMA integrity_check } db2 158 } ok 159 db2 close 160} 161 162finish_test 163