# 2010 April 13 # # 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 "blocking-checkpoint" # operations. # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/lock_common.tcl source $testdir/wal_common.tcl ifcapable !wal {finish_test ; return } set testprefix wal5 proc db_page_count {{file test.db}} { expr [file size $file] / 1024 } proc wal_page_count {{file test.db}} { wal_frame_count ${file}-wal 1024 } do_multiclient_test tn { set ::nBusyHandler 0 set ::busy_handler_script "" proc busyhandler {n} { incr ::nBusyHandler eval $::busy_handler_script return 0 } proc reopen_all {} { code1 {db close} code2 {db2 close} code3 {db3 close} code1 {sqlite3 db test.db} code2 {sqlite3 db2 test.db} code3 {sqlite3 db3 test.db} sql1 { PRAGMA synchronous = NORMAL } code1 { db busy busyhandler } } do_test 1.$tn.1 { reopen_all sql1 { PRAGMA page_size = 1024; PRAGMA auto_vacuum = 0; CREATE TABLE t1(x, y); PRAGMA journal_mode = WAL; INSERT INTO t1 VALUES(1, zeroblob(1200)); INSERT INTO t1 VALUES(2, zeroblob(1200)); INSERT INTO t1 VALUES(3, zeroblob(1200)); } expr [file size test.db] / 1024 } {2} # Have connection 2 grab a read-lock on the current snapshot. do_test 1.$tn.2 { sql2 { BEGIN; SELECT x FROM t1 } } {1 2 3} # Attempt a checkpoint. do_test 1.$tn.3 { sql1 { PRAGMA wal_checkpoint } list [db_page_count] [wal_page_count] } {5 9} # Write to the db again. The log cannot wrap because of the lock still # held by connection 2. The busy-handler has not yet been invoked. do_test 1.$tn.4 { sql1 { INSERT INTO t1 VALUES(4, zeroblob(1200)) } list [db_page_count] [wal_page_count] $::nBusyHandler } {5 12 0} # Now do a blocking-checkpoint. Set the busy-handler up so that connection # 2 releases its lock on the 6th invocation. The checkpointer should then # proceed to checkpoint the entire log file. Next write should go to the # start of the log file. # set ::busy_handler_script { if {$n==5} { sql2 COMMIT } } do_test 1.$tn.5 { sql1 { PRAGMA wal_checkpoint = RESTART } list [db_page_count] [wal_page_count] $::nBusyHandler } {6 12 6} do_test 1.$tn.6 { set ::nBusyHandler 0 sql1 { INSERT INTO t1 VALUES(5, zeroblob(1200)) } list [db_page_count] [wal_page_count] $::nBusyHandler } {6 12 0} do_test 1.$tn.7 { reopen_all list [db_page_count] [wal_page_count] $::nBusyHandler } {7 0 0} do_test 1.$tn.8 { sql2 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5} do_test 1.$tn.9 { sql1 { INSERT INTO t1 VALUES(6, zeroblob(1200)) } list [db_page_count] [wal_page_count] $::nBusyHandler } {7 5 0} do_test 1.$tn.10 { sql3 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5 6} set ::busy_handler_script { if {$n==5} { sql2 COMMIT } if {$n==6} { set ::db_file_size [db_page_count] } if {$n==7} { sql3 COMMIT } } do_test 1.$tn.11 { sql1 { PRAGMA wal_checkpoint = RESTART } list [db_page_count] [wal_page_count] $::nBusyHandler } {10 5 8} do_test 1.$tn.12 { set ::db_file_size } 10 } #------------------------------------------------------------------------- # This block of tests explores checkpoint operations on more than one # database file. # proc setup_and_attach_aux {} { sql1 { ATTACH 'test.db2' AS aux } sql2 { ATTACH 'test.db2' AS aux } sql3 { ATTACH 'test.db2' AS aux } sql1 { PRAGMA main.page_size=1024; PRAGMA main.journal_mode=WAL; PRAGMA aux.page_size=1024; PRAGMA aux.journal_mode=WAL; } } proc file_page_counts {} { list [db_page_count test.db ] \ [wal_page_count test.db ] \ [db_page_count test.db2] \ [wal_page_count test.db2] } # Test that executing "PRAGMA wal_checkpoint" checkpoints all attached # databases, not just the main db. # do_multiclient_test tn { setup_and_attach_aux do_test 2.1.$tn.1 { sql1 { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); CREATE TABLE aux.t2(a, b); INSERT INTO t2 VALUES(1, 2); } } {} do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5} do_test 2.1.$tn.3 { sql1 { PRAGMA wal_checkpoint } } {0 5 5} do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5} } do_multiclient_test tn { setup_and_attach_aux do_test 2.2.$tn.1 { execsql { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); CREATE TABLE aux.t2(a, b); INSERT INTO t2 VALUES(1, 2); INSERT INTO t2 VALUES(3, 4); } } {} do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7} do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} do_test 2.2.$tn.4 { sql1 { PRAGMA wal_checkpoint = RESTART } } {1 5 5} do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7} } do_multiclient_test tn { setup_and_attach_aux do_test 2.3.$tn.1 { execsql { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); CREATE TABLE aux.t2(a, b); INSERT INTO t2 VALUES(1, 2); } } {} do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5} do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {} do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {} do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7} do_test 2.3.$tn.7 { sql1 { PRAGMA wal_checkpoint = FULL } } {1 7 5} do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7} } # Check that checkpoints block on the correct locks. And respond correctly # if they cannot obtain those locks. There are three locks that a checkpoint # may block on (in the following order): # # 1. The writer lock: FULL and RESTART checkpoints block until any writer # process releases its lock. # # 2. Readers using part of the log file. FULL and RESTART checkpoints block # until readers using part (but not all) of the log file have finished. # # 3. Readers using any of the log file. After copying data into the # database file, RESTART checkpoints block until readers using any part # of the log file have finished. # # This test case involves running a checkpoint while there exist other # processes holding all three types of locks. # foreach {tn1 checkpoint busy_on ckpt_expected expected} { 1 PASSIVE - {0 5 5} - 2 TYPO - {0 5 5} - 3 FULL - {0 7 7} 2 4 FULL 1 {1 5 5} 1 5 FULL 2 {1 7 5} 2 6 FULL 3 {0 7 7} 2 7 RESTART - {0 7 7} 3 8 RESTART 1 {1 5 5} 1 9 RESTART 2 {1 7 5} 2 10 RESTART 3 {1 7 7} 3 } { do_multiclient_test tn { setup_and_attach_aux proc busyhandler {x} { set ::max_busyhandler $x if {$::busy_on!="-" && $x==$::busy_on} { return 1 } switch -- $x { 1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" } 2 { sql3 "COMMIT" } 3 { sql2 "COMMIT" } } return 0 } set ::max_busyhandler - do_test 2.4.$tn1.$tn.1 { sql1 { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); } sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) } sql3 { BEGIN; SELECT * FROM t1 } } {1 2} do_test 2.4.$tn1.$tn.2 { code1 { db busy busyhandler } sql1 "PRAGMA wal_checkpoint = $checkpoint" } $ckpt_expected do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected } } finish_test