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