sl@0: # 2007 March 24
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.
sl@0: #
sl@0: # $Id: exclusive2.test,v 1.9 2008/08/22 00:25:53 aswift Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: ifcapable {!pager_pragmas} {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: # This module does not work right if the cache spills at unexpected
sl@0: # moments.  So disable the soft-heap-limit.
sl@0: #
sl@0: sqlite3_soft_heap_limit 0
sl@0: 
sl@0: proc pagerChangeCounter {filename new {fd ""}} {
sl@0:   if {$fd==""} {
sl@0:     set fd [open $filename RDWR]
sl@0:     fconfigure $fd -translation binary -encoding binary
sl@0:     set needClose 1
sl@0:   } else {
sl@0:     set needClose 0
sl@0:   }
sl@0:   if {$new ne ""} {
sl@0:     seek $fd 24
sl@0:     set a [expr {($new&0xFF000000)>>24}]
sl@0:     set b [expr {($new&0x00FF0000)>>16}]
sl@0:     set c [expr {($new&0x0000FF00)>>8}]
sl@0:     set d [expr {($new&0x000000FF)}]
sl@0:     puts -nonewline $fd [binary format cccc $a $b $c $d]
sl@0:     flush $fd
sl@0:   }
sl@0: 
sl@0:   seek $fd 24
sl@0:   foreach {a b c d} [list 0 0 0 0] {}
sl@0:   binary scan [read $fd 4] cccc a b c d
sl@0:   set  ret [expr ($a&0x000000FF)<<24]
sl@0:   incr ret [expr ($b&0x000000FF)<<16]
sl@0:   incr ret [expr ($c&0x000000FF)<<8]
sl@0:   incr ret [expr ($d&0x000000FF)<<0]
sl@0: 
sl@0:   if {$needClose} {close $fd}
sl@0:   return $ret
sl@0: }
sl@0: 
sl@0: proc readPagerChangeCounter {filename} {
sl@0:   set fd [open $filename RDONLY]
sl@0:   fconfigure $fd -translation binary -encoding binary
sl@0: 
sl@0:   seek $fd 24
sl@0:   foreach {a b c d} [list 0 0 0 0] {}
sl@0:   binary scan [read $fd 4] cccc a b c d
sl@0:   set  ret [expr ($a&0x000000FF)<<24]
sl@0:   incr ret [expr ($b&0x000000FF)<<16]
sl@0:   incr ret [expr ($c&0x000000FF)<<8]
sl@0:   incr ret [expr ($d&0x000000FF)<<0]
sl@0: 
sl@0:   close $fd
sl@0:   return $ret
sl@0: }
sl@0: 
sl@0: 
sl@0: proc t1sig {{db db}} {
sl@0:   execsql {SELECT count(*), md5sum(a) FROM t1} $db
sl@0: }
sl@0: do_test exclusive2-1.0 {
sl@0:   readPagerChangeCounter test.db
sl@0: } {0}
sl@0: 
sl@0: #-----------------------------------------------------------------------
sl@0: # The following tests - exclusive2-1.X - check that:
sl@0: #
sl@0: # 1-3:   Build a database with connection 1, calculate a signature.
sl@0: # 4-9:   Modify the database using a second connection in a way that
sl@0: #        does not modify the freelist, then reset the pager change-counter
sl@0: #        to the value it had before the modifications.
sl@0: # 8:     Check that using the first connection, the database signature
sl@0: #        is still the same. This is because it uses the in-memory cache.
sl@0: #        It can't tell the db has changed because we reset the change-counter.
sl@0: # 9:     Increment the change-counter.
sl@0: # 10:    Ensure that the first connection now sees the updated database. It
sl@0: #        sees the change-counter has been incremented and discards the 
sl@0: #        invalid in-memory cache.
sl@0: #
sl@0: # This will only work if the database cache is large enough to hold 
sl@0: # the entire database. In the case of 1024 byte pages, this means
sl@0: # the cache size must be at least 17. Otherwise, some pages will be
sl@0: # loaded from the database file in step 8.
sl@0: #
sl@0: do_test exclusive2-1.1 {
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     CREATE TABLE t1(a, b);
sl@0:     INSERT INTO t1(a) VALUES(randstr(10, 400));
sl@0:     INSERT INTO t1(a) VALUES(randstr(10, 400));
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     COMMIT;
sl@0:     SELECT count(*) FROM t1;
sl@0:   }
sl@0: } {64}
sl@0: do_test exclusive2-1.2.1 {
sl@0:   # Make sure the pager cache is large enough to store the 
sl@0:   # entire database.
sl@0:   set nPage [expr [file size test.db]/1024]
sl@0:   if {$::SQLITE_DEFAULT_CACHE_SIZE < $nPage} {
sl@0:     execsql "PRAGMA cache_size = $nPage"
sl@0:   }
sl@0:   expr {[execsql {PRAGMA cache_size}] >= $nPage}
sl@0: } {1}
sl@0: do_test exclusive2-1.2 {
sl@0:   set ::sig [t1sig]
sl@0:   readPagerChangeCounter test.db
sl@0: } {1}
sl@0: do_test exclusive2-1.3 {
sl@0:   t1sig
sl@0: } $::sig
sl@0: do_test exclusive2-1.4 {
sl@0:   sqlite3 db2 test.db
sl@0:   t1sig db2
sl@0: } $::sig
sl@0: do_test exclusive2-1.5 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET b=a, a=NULL;
sl@0:   } db2
sl@0:   expr {[t1sig db2] eq $::sig}
sl@0: } 0
sl@0: do_test exclusive2-1.6 {
sl@0:   readPagerChangeCounter test.db
sl@0: } {2}
sl@0: do_test exclusive2-1.7 {
sl@0:   pagerChangeCounter test.db 1
sl@0: } {1}
sl@0: do_test exclusive2-1.9 {
sl@0:   t1sig
sl@0:   expr {[t1sig] eq $::sig}
sl@0: } {1}
sl@0: do_test exclusive2-1.10 {
sl@0:   pagerChangeCounter test.db 2
sl@0: } {2}
sl@0: do_test exclusive2-1.11 {
sl@0:   expr {[t1sig] eq $::sig}
sl@0: } {0}
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # These tests - exclusive2-2.X - are similar to exclusive2-1.X, 
sl@0: # except that they are run with locking_mode=EXCLUSIVE.
sl@0: #
sl@0: # 1-3:   Build a database with exclusive-access connection 1, 
sl@0: #        calculate a signature.
sl@0: # 4:     Corrupt the database by writing 10000 bytes of garbage
sl@0: #        starting at the beginning of page 2. Check that connection 1
sl@0: #        still works. It should be accessing the in-memory cache.
sl@0: # 5-6:   Modify the dataase change-counter. Connection 1 still works
sl@0: #        entirely from in-memory cache, because it doesn't check the
sl@0: #        change-counter.
sl@0: # 7-8    Set the locking-mode back to normal. After the db is unlocked,
sl@0: #        SQLite detects the modified change-counter and discards the
sl@0: #        in-memory cache. Then it finds the corruption caused in step 4....
sl@0: #
sl@0: # As above, this test is only applicable if the pager cache is
sl@0: # large enough to hold the entire database. With 1024 byte pages,
sl@0: # this means 19 pages.  We also need to disable the soft-heap-limit
sl@0: # to prevent memory-induced cache spills.
sl@0: #
sl@0: do_test exclusive2-2.1 {
sl@0:   execsql {PRAGMA locking_mode = exclusive;}
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1(a) VALUES(randstr(10, 400));
sl@0:     INSERT INTO t1(a) VALUES(randstr(10, 400));
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;
sl@0:     COMMIT;
sl@0:     SELECT count(*) FROM t1;
sl@0:   }
sl@0: } {64}
sl@0: do_test exclusive2-2.2.1 {
sl@0:   # Make sure the pager cache is large enough to store the 
sl@0:   # entire database.
sl@0:   set nPage [expr [file size test.db]/1024]
sl@0:   if {$::SQLITE_DEFAULT_CACHE_SIZE < $nPage} {
sl@0:     execsql "PRAGMA cache_size = $nPage"
sl@0:   }
sl@0:   expr {[execsql {PRAGMA cache_size}] >= $nPage}
sl@0: } {1}
sl@0: do_test exclusive2-2.2 {
sl@0:   set ::sig [t1sig]
sl@0:   readPagerChangeCounter test.db
sl@0: } {3}
sl@0: do_test exclusive2-2.3 {
sl@0:   t1sig
sl@0: } $::sig
sl@0: 
sl@0: do_test exclusive2-2.4 {
sl@0:   set ::fd [open test.db RDWR]
sl@0:   fconfigure $::fd -translation binary
sl@0:   seek $::fd 1024
sl@0:   puts -nonewline $::fd [string repeat [binary format c 0] 10000]
sl@0:   flush $::fd
sl@0:   t1sig
sl@0: } $::sig
sl@0: 
sl@0: do_test exclusive2-2.5 {
sl@0:   pagerChangeCounter test.db 5 $::fd
sl@0: } {5}
sl@0: do_test exclusive2-2.6 {
sl@0:   t1sig
sl@0: } $::sig
sl@0: do_test exclusive2-2.7 {
sl@0:   execsql {PRAGMA locking_mode = normal}
sl@0:   t1sig
sl@0: } $::sig
sl@0: 
sl@0: do_test exclusive2-2.8 {
sl@0:   set rc [catch {t1sig} msg]
sl@0:   list $rc $msg
sl@0: } {1 {database disk image is malformed}}
sl@0: 
sl@0: #--------------------------------------------------------------------
sl@0: # These tests - exclusive2-3.X - verify that the pager change-counter
sl@0: # is only incremented by the first change when in exclusive access
sl@0: # mode. In normal mode, the change-counter is incremented once
sl@0: # per write-transaction.
sl@0: #
sl@0: 
sl@0: db close
sl@0: db2 close
sl@0: catch {close $::fd}
sl@0: file delete -force test.db
sl@0: file delete -force test.db-journal
sl@0: 
sl@0: do_test exclusive2-3.0 {
sl@0:   sqlite3 db test.db
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     CREATE TABLE t1(a UNIQUE);
sl@0:     INSERT INTO t1 VALUES(randstr(10, 400));
sl@0:     INSERT INTO t1 VALUES(randstr(10, 400));
sl@0:     COMMIT;
sl@0:   }
sl@0:   readPagerChangeCounter test.db
sl@0: } {1}
sl@0: do_test exclusive2-3.1 {
sl@0:   execsql {
sl@0:     INSERT INTO t1 VALUES(randstr(10, 400));
sl@0:   }
sl@0:   readPagerChangeCounter test.db
sl@0: } {2}
sl@0: do_test exclusive2-3.2 {
sl@0:   execsql {
sl@0:     INSERT INTO t1 VALUES(randstr(10, 400));
sl@0:   }
sl@0:   readPagerChangeCounter test.db
sl@0: } {3}
sl@0: do_test exclusive2-3.3 {
sl@0:   execsql {
sl@0:     PRAGMA locking_mode = exclusive;
sl@0:     INSERT INTO t1 VALUES(randstr(10, 400));
sl@0:   }
sl@0:   readPagerChangeCounter test.db
sl@0: } {4}
sl@0: do_test exclusive2-3.4 {
sl@0:   execsql {
sl@0:     INSERT INTO t1 VALUES(randstr(10, 400));
sl@0:   }
sl@0:   readPagerChangeCounter test.db
sl@0: } {4}
sl@0: do_test exclusive2-3.5 {
sl@0:   execsql {
sl@0:     PRAGMA locking_mode = normal;
sl@0:     INSERT INTO t1 VALUES(randstr(10, 400));
sl@0:   }
sl@0:   readPagerChangeCounter test.db
sl@0: } {4}
sl@0: do_test exclusive2-3.6 {
sl@0:   execsql {
sl@0:     INSERT INTO t1 VALUES(randstr(10, 400));
sl@0:   }
sl@0:   readPagerChangeCounter test.db
sl@0: } {5}
sl@0: sqlite3_soft_heap_limit $soft_limit
sl@0: 
sl@0: finish_test