1# 2010 November 19 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# 12 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15source $testdir/lock_common.tcl 16 17set testprefix superlock 18 19# Test organization: 20# 21# 1.*: Test superlock on a rollback database. Test that once the db is 22# superlocked, it is not possible for a second client to read from 23# it. 24# 25# 2.*: Test superlock on a WAL database with zero frames in the WAL file. 26# Test that once the db is superlocked, it is not possible to read, 27# write or checkpoint the db. 28# 29# 3.*: As 2.*, for WAL databases with one or more frames in the WAL. 30# 31# 4.*: As 2.*, for WAL databases with one or more checkpointed frames 32# in the WAL. 33# 34# 5.*: Test that a call to sqlite3demo_superlock() uses the busy handler 35# correctly to wait for existing clients to clear on a WAL database. 36# And returns SQLITE_BUSY if no busy handler is defined or the busy 37# handler returns 0 before said clients relinquish their locks. 38# 39# 6.*: Test that if a superlocked WAL database is overwritten, existing 40# clients run the recovery to build the new wal-index after the 41# superlock is released. 42# 43# 44 45do_execsql_test 1.1 { 46 CREATE TABLE t1(a, b); 47 INSERT INTO t1 VALUES(1, 2); 48 PRAGMA journal_mode = DELETE; 49} {delete} 50 51ifcapable !wal { 52 finish_test 53 return 54} 55 56do_test 1.2 { sqlite3demo_superlock unlock test.db } {unlock} 57do_catchsql_test 1.3 { SELECT * FROM t1 } {1 {database is locked}} 58do_test 1.4 { unlock } {} 59 60do_execsql_test 2.1 { 61 INSERT INTO t1 VALUES(3, 4); 62 PRAGMA journal_mode = WAL; 63} {wal} 64 65do_test 2.2 { sqlite3demo_superlock unlock test.db } {unlock} 66do_catchsql_test 2.3 { SELECT * FROM t1 } {1 {database is locked}} 67do_catchsql_test 2.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}} 68do_catchsql_test 2.5 { PRAGMA wal_checkpoint } {0 {1 -1 -1}} 69do_test 2.6 { unlock } {} 70 71do_execsql_test 3.1 { INSERT INTO t1 VALUES(3, 4) } 72 73do_test 3.2 { sqlite3demo_superlock unlock test.db } {unlock} 74do_catchsql_test 3.3 { SELECT * FROM t1 } {1 {database is locked}} 75do_catchsql_test 3.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}} 76do_catchsql_test 3.5 { PRAGMA wal_checkpoint } {0 {1 -1 -1}} 77do_test 3.6 { unlock } {} 78 79# At this point the WAL file consists of a single frame only - written 80# by test case 3.1. If the ZERO_DAMAGE flag were not set, it would consist 81# of two frames - the frame written by 3.1 and a padding frame. 82do_execsql_test 4.1 { PRAGMA wal_checkpoint } {0 1 1} 83 84do_test 4.2 { sqlite3demo_superlock unlock test.db } {unlock} 85do_catchsql_test 4.3 { SELECT * FROM t1 } {1 {database is locked}} 86do_catchsql_test 4.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}} 87do_catchsql_test 4.5 { PRAGMA wal_checkpoint } {0 {1 -1 -1}} 88do_test 4.6 { unlock } {} 89 90do_multiclient_test tn { 91 92 proc busyhandler {x} { 93 switch -- $x { 94 1 { sql1 "COMMIT" } 95 2 { sql2 "COMMIT" } 96 3 { sql3 "COMMIT" } 97 } 98 lappend ::busylist $x 99 return 1 100 } 101 set ::busylist [list] 102 103 do_test 5.$tn.1 { 104 sql1 { 105 CREATE TABLE t1(a, b); 106 PRAGMA journal_mode = WAL; 107 INSERT INTO t1 VALUES(1, 2); 108 } 109 } {wal} 110 111 do_test 5.$tn.2 { 112 sql1 { BEGIN ; SELECT * FROM t1 } 113 sql2 { BEGIN ; INSERT INTO t1 VALUES(3, 4) } 114 sql3 { BEGIN ; SELECT * FROM t1 } 115 } {1 2} 116 117 do_test 5.$tn.3 { 118 set ::busylist [list] 119 sqlite3demo_superlock unlock test.db "" busyhandler 120 set ::busylist 121 } {0 1 2 3} 122 123 do_test 5.$tn.4 { csql2 { SELECT * FROM t1 } } {1 {database is locked}} 124 do_test 5.$tn.5 { 125 csql3 { INSERT INTO t1 VALUES(5, 6) } 126 } {1 {database is locked}} 127 do_test 5.$tn.6 { csql1 "PRAGMA wal_checkpoint" } {0 {1 -1 -1}} 128 129 do_test 5.$tn.7 { unlock } {} 130 131 132 do_test 5.$tn.8 { 133 sql1 { BEGIN ; SELECT * FROM t1 } 134 sql2 { BEGIN ; INSERT INTO t1 VALUES(5, 6) } 135 sql3 { BEGIN ; SELECT * FROM t1 } 136 } {1 2 3 4} 137 138 do_test 5.$tn.9 { 139 list [catch {sqlite3demo_superlock unlock test.db} msg] $msg 140 } {1 {database is locked}} 141 do_test 5.$tn.10 { 142 sql1 COMMIT 143 list [catch {sqlite3demo_superlock unlock test.db} msg] $msg 144 } {1 {database is locked}} 145 do_test 5.$tn.11 { 146 sql2 COMMIT 147 list [catch {sqlite3demo_superlock unlock test.db} msg] $msg 148 } {1 {database is locked}} 149 do_test 5.$tn.12 { 150 sql3 COMMIT 151 list [catch {sqlite3demo_superlock unlock test.db} msg] $msg 152 } {0 unlock} 153 unlock 154 155 156 do_test 5.$tn.13 { sql1 { SELECT * FROM t1 } } {1 2 3 4 5 6} 157 do_test 5.$tn.14 { sql2 { SELECT * FROM t1 } } {1 2 3 4 5 6} 158 do_test 5.$tn.15 { sqlite3demo_superlock unlock test.db } {unlock} 159 do_test 5.$tn.16 { unlock } {} 160 do_test 5.$tn.17 { sql2 { SELECT * FROM t1 } } {1 2 3 4 5 6} 161 do_test 5.$tn.18 { sql1 { SELECT * FROM t1 } } {1 2 3 4 5 6} 162 do_test 5.$tn.19 { sql2 { SELECT * FROM t1 } } {1 2 3 4 5 6} 163} 164 165proc read_content {file} { 166 if {[file exists $file]==0} {return ""} 167 set fd [open $file] 168 fconfigure $fd -encoding binary -translation binary 169 set content [read $fd] 170 close $fd 171 return $content 172} 173 174proc write_content {file content} { 175 set fd [open $file w+] 176 fconfigure $fd -encoding binary -translation binary 177 puts -nonewline $fd $content 178 close $fd 179} 180 181# Both $file1 and $file2 are database files. This function takes a 182# superlock on each, then exchanges the content of the two files (i.e. 183# overwrites $file1 with the initial contents of $file2, and overwrites 184# $file2 with the initial contents of $file1). The contents of any WAL 185# file is also exchanged. 186# 187proc db_swap {file1 file2} { 188 sqlite3demo_superlock unlock1 $file1 189 sqlite3demo_superlock unlock2 $file2 190 191 set db1 [read_content $file1] 192 set db2 [read_content $file2] 193 write_content $file1 $db2 194 write_content $file2 $db1 195 196 set wal1 [read_content ${file1}-wal] 197 set wal2 [read_content ${file2}-wal] 198 write_content ${file1}-wal $wal2 199 write_content ${file2}-wal $wal1 200 201 unlock1 202 unlock2 203} 204 205forcedelete test.db 206sqlite3 db test.db 207do_execsql_test 6.1 { 208 ATTACH 'test.db2' AS aux; 209 PRAGMA aux.journal_mode = wal; 210 CREATE TABLE aux.t2(x, y); 211 INSERT INTO aux.t2 VALUES('a', 'b'); 212 PRAGMA schema_version = 450; 213 DETACH aux; 214 215 PRAGMA main.journal_mode = wal; 216 CREATE TABLE t1(a, b); 217 INSERT INTO t1 VALUES(1, 2); 218 INSERT INTO t1 VALUES(3, 4); 219 SELECT * FROM t1; 220} {wal wal 1 2 3 4} 221 222 223db_swap test.db2 test.db 224do_catchsql_test 6.2 { SELECT * FROM t1 } {1 {no such table: t1}} 225do_catchsql_test 6.3 { SELECT * FROM t2 } {0 {a b}} 226 227db_swap test.db2 test.db 228do_catchsql_test 6.4 { SELECT * FROM t1 } {0 {1 2 3 4}} 229do_catchsql_test 6.5 { SELECT * FROM t2 } {1 {no such table: t2}} 230 231do_execsql_test 6.6 { PRAGMA wal_checkpoint } {0 0 0} 232 233db_swap test.db2 test.db 234do_catchsql_test 6.7 { SELECT * FROM t1 } {1 {no such table: t1}} 235do_catchsql_test 6.8 { SELECT * FROM t2 } {0 {a b}} 236 237db_swap test.db2 test.db 238do_catchsql_test 6.9 { SELECT * FROM t1 } {0 {1 2 3 4}} 239do_catchsql_test 6.10 { SELECT * FROM t2 } {1 {no such table: t2}} 240 241do_execsql_test 6.11 { 242 PRAGMA journal_mode = delete; 243 PRAGMA page_size = 512; 244 VACUUM; 245 PRAGMA journal_mode = wal; 246 INSERT INTO t1 VALUES(5, 6); 247} {delete wal} 248 249db_swap test.db2 test.db 250do_catchsql_test 6.12 { SELECT * FROM t1 } {1 {no such table: t1}} 251do_catchsql_test 6.13 { SELECT * FROM t2 } {0 {a b}} 252 253db_swap test.db2 test.db 254do_catchsql_test 6.14 { SELECT * FROM t1 } {0 {1 2 3 4 5 6}} 255do_catchsql_test 6.15 { SELECT * FROM t2 } {1 {no such table: t2}} 256 257finish_test 258