# 2001 September 15 # # 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 script is page cache subsystem. # # $Id: pager.test,v 1.34 2009/01/07 15:18:21 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl if {[info commands pager_open]!=""} { db close # Basic sanity check. Open and close a pager. # do_test pager-1.0 { catch {file delete -force ptf1.db} catch {file delete -force ptf1.db-journal} set v [catch { set ::p1 [pager_open ptf1.db 10] } msg] } {0} do_test pager-1.1 { pager_stats $::p1 } {ref 0 page 0 max 10 size -1 state 0 err 0 hit 0 miss 0 ovfl 0} do_test pager-1.2 { pager_pagecount $::p1 } {0} do_test pager-1.3 { pager_stats $::p1 } {ref 0 page 0 max 10 size -1 state 0 err 0 hit 0 miss 0 ovfl 0} do_test pager-1.4 { pager_close $::p1 } {} # Try to write a few pages. # do_test pager-2.1 { set v [catch { set ::p1 [pager_open ptf1.db 10] } msg] } {0} #do_test pager-2.2 { # set v [catch { # set ::g1 [page_get $::p1 0] # } msg] # lappend v $msg #} {1 SQLITE_ERROR} do_test pager-2.3.1 { set ::gx [page_lookup $::p1 1] } {} do_test pager-2.3.2 { pager_stats $::p1 } {ref 0 page 0 max 10 size -1 state 0 err 0 hit 0 miss 0 ovfl 0} do_test pager-2.3.3 { set v [catch { set ::g1 [page_get $::p1 1] } msg] if {$v} {lappend v $msg} set v } {0} do_test pager-2.3.3 { pager_stats $::p1 } {ref 1 page 1 max 10 size 0 state 1 err 0 hit 0 miss 1 ovfl 0} do_test pager-2.3.4 { set ::gx [page_lookup $::p1 1] expr {$::gx!=""} } {1} do_test pager-2.3.5 { page_unref $::gx pager_stats $::p1 } {ref 1 page 1 max 10 size 0 state 1 err 0 hit 0 miss 1 ovfl 0} do_test pager-2.3.6 { expr {$::g1==$::gx} } {1} do_test pager-2.3.7 { pager_stats $::p1 } {ref 1 page 1 max 10 size 0 state 1 err 0 hit 0 miss 1 ovfl 0} do_test pager-2.4 { pager_stats $::p1 } {ref 1 page 1 max 10 size 0 state 1 err 0 hit 0 miss 1 ovfl 0} do_test pager-2.5 { pager_pagecount $::p1 } {0} do_test pager-2.6 { pager_stats $::p1 } {ref 1 page 1 max 10 size 0 state 1 err 0 hit 0 miss 1 ovfl 0} do_test pager-2.7 { page_number $::g1 } {1} do_test pager-2.8 { page_read $::g1 } {} do_test pager-2.9 { page_unref $::g1 } {} # Update 24/03/2007: Even though the ref-count has dropped to zero, the # pager-cache still contains some pages. Previously, it was always true # that if there were no references to a pager it was empty. do_test pager-2.10 { pager_stats $::p1 } {ref 0 page 1 max 10 size -1 state 0 err 0 hit 0 miss 1 ovfl 0} do_test pager-2.11 { set ::g1 [page_get $::p1 1] expr {$::g1!=0} } {1} do_test pager-2.12 { page_number $::g1 } {1} do_test pager-2.13 { pager_stats $::p1 } {ref 1 page 1 max 10 size 0 state 1 err 0 hit 1 miss 1 ovfl 0} do_test pager-2.14 { set v [catch { page_write $::g1 "Page-One" } msg] lappend v $msg } {0 {}} do_test pager-2.15 { pager_stats $::p1 } {ref 1 page 1 max 10 size 1 state 2 err 0 hit 1 miss 1 ovfl 0} do_test pager-2.16 { page_read $::g1 } {Page-One} do_test pager-2.17 { set v [catch { pager_commit $::p1 } msg] lappend v $msg } {0 {}} do_test pager-2.20 { pager_stats $::p1 } {ref 1 page 1 max 10 size -1 state 1 err 0 hit 2 miss 1 ovfl 0} do_test pager-2.19 { pager_pagecount $::p1 } {1} do_test pager-2.21 { pager_stats $::p1 } {ref 1 page 1 max 10 size 1 state 1 err 0 hit 2 miss 1 ovfl 0} do_test pager-2.22 { page_unref $::g1 } {} do_test pager-2.23 { pager_stats $::p1 } {ref 0 page 1 max 10 size -1 state 0 err 0 hit 2 miss 1 ovfl 0} do_test pager-2.24 { set v [catch { page_get $::p1 1 } ::g1] if {$v} {lappend v $::g1} set v } {0} do_test pager-2.25 { page_read $::g1 } {Page-One} do_test pager-2.26 { set v [catch { page_write $::g1 {page-one} } msg] lappend v $msg } {0 {}} do_test pager-2.27 { page_read $::g1 } {page-one} do_test pager-2.28 { set v [catch { pager_rollback $::p1 } msg] lappend v $msg } {0 {}} do_test pager-2.29 { page_unref $::g1 set ::g1 [page_get $::p1 1] page_read $::g1 } {Page-One} do_test pager-2.99 { page_unref $::g1 pager_close $::p1 } {} do_test pager-3.1 { set v [catch { set ::p1 [pager_open ptf1.db 15] } msg] if {$v} {lappend v $msg} set v } {0} do_test pager-3.2 { pager_pagecount $::p1 } {1} do_test pager-3.3 { set v [catch { set ::g(1) [page_get $::p1 1] } msg] if {$v} {lappend v $msg} set v } {0} do_test pager-3.4 { page_read $::g(1) } {Page-One} do_test pager-3.5 { for {set i 2} {$i<=20} {incr i} { set gx [page_get $::p1 $i] page_write $gx "Page-$i" page_unref $gx } breakpoint pager_commit $::p1 page_unref $::g(1) } {} for {set i 2} {$i<=20} {incr i} { do_test pager-3.6.[expr {$i-1}] [subst { set gx \[page_get $::p1 $i\] set v \[page_read \$gx\] page_unref \$gx set v }] "Page-$i" } for {set i 1} {$i<=20} {incr i} { regsub -all CNT { set ::g1 [page_get $::p1 CNT] set ::g2 [page_get $::p1 CNT] set ::vx [page_read $::g2] expr {$::g1==$::g2} } $i body; do_test pager-3.7.$i.1 $body {1} regsub -all CNT { page_unref $::g2 set vy [page_read $::g1] expr {$vy==$::vx} } $i body; do_test pager-3.7.$i.2 $body {1} regsub -all CNT { page_unref $::g1 set gx [page_get $::p1 CNT] set vy [page_read $gx] page_unref $gx expr {$vy==$::vx} } $i body; do_test pager-3.7.$i.3 $body {1} } do_test pager-3.99 { pager_close $::p1 } {} # tests of the checkpoint mechanism and api # do_test pager-4.0 { set v [catch { file delete -force ptf1.db set ::p1 [pager_open ptf1.db 15] } msg] if {$v} {lappend v $msg} set v } {0} do_test pager-4.1 { set g1 [page_get $::p1 1] page_write $g1 "Page-1 v0" for {set i 2} {$i<=20} {incr i} { set gx [page_get $::p1 $i] page_write $gx "Page-$i v0" page_unref $gx } pager_commit $::p1 } {} for {set i 1} {$i<=20} {incr i} { do_test pager-4.2.$i { set gx [page_get $p1 $i] set v [page_read $gx] page_unref $gx set v } "Page-$i v0" } do_test pager-4.3 { lrange [pager_stats $::p1] 0 1 } {ref 1} do_test pager-4.4 { lrange [pager_stats $::p1] 8 9 } {state 1} for {set i 1} {$i<20} {incr i} { do_test pager-4.5.$i.0 { set res {} for {set j 2} {$j<=20} {incr j} { set gx [page_get $p1 $j] set value [page_read $gx] page_unref $gx set shouldbe "Page-$j v[expr {$i-1}]" if {$value!=$shouldbe} { lappend res $value $shouldbe } } set res } {} do_test pager-4.5.$i.1 { page_write $g1 "Page-1 v$i" lrange [pager_stats $p1] 8 9 } {state 2} do_test pager-4.5.$i.2 { for {set j 2} {$j<=20} {incr j} { set gx [page_get $p1 $j] page_write $gx "Page-$j v$i" page_unref $gx if {$j==$i} { pager_stmt_begin $p1 } } } {} do_test pager-4.5.$i.3 { set res {} for {set j 2} {$j<=20} {incr j} { set gx [page_get $p1 $j] set value [page_read $gx] page_unref $gx set shouldbe "Page-$j v$i" if {$value!=$shouldbe} { lappend res $value $shouldbe } } set res } {} do_test pager-4.5.$i.4 { pager_rollback $p1 set res {} for {set j 2} {$j<=20} {incr j} { set gx [page_get $p1 $j] set value [page_read $gx] page_unref $gx set shouldbe "Page-$j v[expr {$i-1}]" if {$value!=$shouldbe} { lappend res $value $shouldbe } } set res } {} do_test pager-4.5.$i.5 { page_write $g1 "Page-1 v$i" lrange [pager_stats $p1] 8 9 } {state 2} do_test pager-4.5.$i.6 { for {set j 2} {$j<=20} {incr j} { set gx [page_get $p1 $j] page_write $gx "Page-$j v$i" page_unref $gx if {$j==$i} { pager_stmt_begin $p1 } } } {} do_test pager-4.5.$i.7 { pager_stmt_rollback $p1 for {set j 2} {$j<=20} {incr j} { set gx [page_get $p1 $j] set value [page_read $gx] page_unref $gx if {$j<=$i || $i==1} { set shouldbe "Page-$j v$i" } else { set shouldbe "Page-$j v[expr {$i-1}]" } if {$value!=$shouldbe} { lappend res $value $shouldbe } } set res } {} do_test pager-4.5.$i.8 { for {set j 2} {$j<=20} {incr j} { set gx [page_get $p1 $j] page_write $gx "Page-$j v$i" page_unref $gx if {$j==$i} { pager_stmt_begin $p1 } } } {} do_test pager-4.5.$i.9 { pager_stmt_commit $p1 for {set j 2} {$j<=20} {incr j} { set gx [page_get $p1 $j] set value [page_read $gx] page_unref $gx set shouldbe "Page-$j v$i" if {$value!=$shouldbe} { lappend res $value $shouldbe } } set res } {} do_test pager-4.5.$i.10 { pager_commit $p1 lrange [pager_stats $p1] 8 9 } {state 1} } # Test that nothing bad happens when sqlite3pager_set_cachesize() is # called with a negative argument. do_test pager-4.6.1 { pager_close [pager_open ptf2.db -15] } {} # Test truncate on an in-memory database is Ok. ifcapable memorydb { do_test pager-4.6.2 { set ::p2 [pager_open :memory: 10] pager_truncate $::p2 0 } {} do_test pager-4.6.3 { set page1 [page_get $::p2 1] for {set i 1} {$i<5} {incr i} { set p [page_get $::p2 $i] page_write $p "Page $i" pager_commit $::p2 page_unref $p } page_unref $page1 pager_truncate $::p2 3 } {} do_test pager-4.6.4 { pager_close $::p2 } {} } do_test pager-4.99 { page_unref $::g1 pager_close $::p1 } {} file delete -force ptf1.db } ;# end if( not mem: and has pager_open command ); if 0 { # Ticket #615: an assertion fault inside the pager. It is a benign # fault, but we might as well test for it. # do_test pager-5.1 { sqlite3 db test.db execsql { BEGIN; CREATE TABLE t1(x); PRAGMA synchronous=off; COMMIT; } } {} } # The following tests cover rolling back hot journal files. # They can't be run on windows because the windows version of # SQLite holds a mandatory exclusive lock on journal files it has open. # # They cannot be run during the journaltest permutation because # "PRAGMA synchronous = 0" is used. # if {$tcl_platform(platform)!="windows" && ( 0 == [info exists ::permutations_test_prefix] || $::permutations_test_prefix ne "journaltest" )} { do_test pager-6.1 { file delete -force test2.db file delete -force test2.db-journal sqlite3 db2 test2.db execsql { PRAGMA synchronous = 0; CREATE TABLE abc(a, b, c); INSERT INTO abc VALUES(1, 2, randstr(200,200)); INSERT INTO abc VALUES(1, 2, randstr(200,200)); INSERT INTO abc VALUES(1, 2, randstr(200,200)); INSERT INTO abc VALUES(1, 2, randstr(200,200)); INSERT INTO abc VALUES(1, 2, randstr(200,200)); INSERT INTO abc VALUES(1, 2, randstr(200,200)); INSERT INTO abc VALUES(1, 2, randstr(200,200)); INSERT INTO abc VALUES(1, 2, randstr(200,200)); INSERT INTO abc VALUES(1, 2, randstr(200,200)); BEGIN; UPDATE abc SET c = randstr(200,200); } db2 copy_file test2.db test.db copy_file test2.db-journal test.db-journal set f [open test.db-journal a] fconfigure $f -encoding binary seek $f [expr [file size test.db-journal] - 1032] start puts -nonewline $f "\00\00\00\00" close $f sqlite3 db test.db execsql { SELECT sql FROM sqlite_master } } {{CREATE TABLE abc(a, b, c)}} do_test pager-6.2 { copy_file test2.db test.db copy_file test2.db-journal test.db-journal set f [open test.db-journal a] fconfigure $f -encoding binary seek $f [expr [file size test.db-journal] - 1032] start puts -nonewline $f "\00\00\00\FF" close $f sqlite3 db test.db execsql { SELECT sql FROM sqlite_master } } {{CREATE TABLE abc(a, b, c)}} do_test pager-6.3 { copy_file test2.db test.db copy_file test2.db-journal test.db-journal set f [open test.db-journal a] fconfigure $f -encoding binary seek $f [expr [file size test.db-journal] - 4] start puts -nonewline $f "\00\00\00\00" close $f sqlite3 db test.db execsql { SELECT sql FROM sqlite_master } } {{CREATE TABLE abc(a, b, c)}} do_test pager-6.4.1 { execsql { BEGIN; SELECT sql FROM sqlite_master; } copy_file test2.db-journal test.db-journal; sqlite3 db3 test.db catchsql { BEGIN; SELECT sql FROM sqlite_master; } db3; } {1 {database is locked}} do_test pager-6.4.2 { file delete -force test.db-journal catchsql { SELECT sql FROM sqlite_master; } db3; } {0 {{CREATE TABLE abc(a, b, c)}}} do_test pager-6.4.3 { db3 close execsql { COMMIT; } } {} do_test pager-6.5 { copy_file test2.db test.db copy_file test2.db-journal test.db-journal set f [open test.db-journal a] fconfigure $f -encoding binary puts -nonewline $f "hello" puts -nonewline $f "\x00\x00\x00\x05\x01\x02\x03\x04" puts -nonewline $f "\xd9\xd5\x05\xf9\x20\xa1\x63\xd7" close $f sqlite3 db test.db execsql { SELECT sql FROM sqlite_master } } {{CREATE TABLE abc(a, b, c)}} do_test pager-6.5 { db2 close } {} } finish_test