| # 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 } |
| |
| |
| # A checkpoint may be requested either using the C API or by executing |
| # an SQL PRAGMA command. To test both methods, all tests in this file are |
| # run twice - once using each method to request checkpoints. |
| # |
| foreach {testprefix do_wal_checkpoint} { |
| |
| wal5-pragma { |
| proc do_wal_checkpoint { dbhandle args } { |
| array set a $args |
| foreach key [array names a] { |
| if {[lsearch {-mode -db} $key]<0} { error "unknown switch: $key" } |
| } |
| |
| set sql "PRAGMA " |
| if {[info exists a(-db)]} { append sql "$a(-db)." } |
| append sql "wal_checkpoint" |
| if {[info exists a(-mode)]} { append sql " = $a(-mode)" } |
| |
| uplevel [list $dbhandle eval $sql] |
| } |
| } |
| |
| wal5-capi { |
| proc do_wal_checkpoint { dbhandle args } { |
| set a(-mode) passive |
| array set a $args |
| foreach key [array names a] { |
| if {[lsearch {-mode -db} $key]<0} { error "unknown switch: $key" } |
| } |
| |
| if {$a(-mode)!="restart" && $a(-mode)!="full"} { set a(-mode) passive } |
| |
| set cmd [list sqlite3_wal_checkpoint_v2 $dbhandle $a(-mode)] |
| if {[info exists a(-db)]} { lappend sql $a(-db) } |
| |
| uplevel $cmd |
| } |
| } |
| } { |
| |
| eval $do_wal_checkpoint |
| |
| 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 { |
| code1 { do_wal_checkpoint db } |
| 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 { |
| code1 { do_wal_checkpoint db -mode 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 { |
| code1 { do_wal_checkpoint db -mode 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 aux.auto_vacuum = 0; |
| PRAGMA main.auto_vacuum = 0; |
| 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. In capi mode, check that this is |
| # true if a NULL pointer is passed to wal_checkpoint_v2() in place of a |
| # database name. |
| 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 { code1 { do_wal_checkpoint db } } {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 { code1 { do_wal_checkpoint db -mode 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 { code1 { do_wal_checkpoint db -mode 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 } |
| code1 { do_wal_checkpoint db -mode [string tolower $checkpoint] } |
| } $ckpt_expected |
| do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected |
| } |
| } |
| |
| |
| do_multiclient_test tn { |
| |
| code1 $do_wal_checkpoint |
| code2 $do_wal_checkpoint |
| code3 $do_wal_checkpoint |
| |
| do_test 3.$tn.1 { |
| sql1 { |
| PRAGMA auto_vacuum = 0; |
| PRAGMA journal_mode = WAL; |
| PRAGMA synchronous = normal; |
| CREATE TABLE t1(x, y); |
| } |
| |
| sql2 { PRAGMA journal_mode } |
| sql3 { PRAGMA journal_mode } |
| } {wal} |
| |
| do_test 3.$tn.2 { code2 { do_wal_checkpoint db2 } } {0 2 2} |
| |
| do_test 3.$tn.3 { code2 { do_wal_checkpoint db2 } } {0 2 2} |
| |
| do_test 3.$tn.4 { code3 { do_wal_checkpoint db3 } } {0 2 2} |
| |
| code1 {db close} |
| code2 {db2 close} |
| code3 {db3 close} |
| |
| code1 {sqlite3 db test.db} |
| code2 {sqlite3 db2 test.db} |
| code3 {sqlite3 db3 test.db} |
| |
| do_test 3.$tn.5 { sql3 { PRAGMA journal_mode } } {wal} |
| |
| do_test 3.$tn.6 { code3 { do_wal_checkpoint db3 } } {0 0 0} |
| } |
| } |
| |
| |
| finish_test |
| |