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