# 2007 March 24

#

# 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.

#

# $Id: exclusive2.test,v 1.9 2008/08/22 00:25:53 aswift Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

ifcapable {!pager_pragmas} {

  finish_test

  return

}

# This module does not work right if the cache spills at unexpected

# moments.  So disable the soft-heap-limit.

#

sqlite3_soft_heap_limit 0

proc pagerChangeCounter {filename new {fd ""}} {

  if {$fd==""} {

    set fd [open $filename RDWR]

    fconfigure $fd -translation binary -encoding binary

    set needClose 1

  } else {

    set needClose 0

  }

  if {$new ne ""} {

    seek $fd 24

    set a [expr {($new&0xFF000000)>>24}]

    set b [expr {($new&0x00FF0000)>>16}]

    set c [expr {($new&0x0000FF00)>>8}]

    set d [expr {($new&0x000000FF)}]

    puts -nonewline $fd [binary format cccc $a $b $c $d]

    flush $fd

  }

  seek $fd 24

  foreach {a b c d} [list 0 0 0 0] {}

  binary scan [read $fd 4] cccc a b c d

  set  ret [expr ($a&0x000000FF)<<24]

  incr ret [expr ($b&0x000000FF)<<16]

  incr ret [expr ($c&0x000000FF)<<8]

  incr ret [expr ($d&0x000000FF)<<0]

  if {$needClose} {close $fd}

  return $ret

}

proc readPagerChangeCounter {filename} {

  set fd [open $filename RDONLY]

  fconfigure $fd -translation binary -encoding binary

  seek $fd 24

  foreach {a b c d} [list 0 0 0 0] {}

  binary scan [read $fd 4] cccc a b c d

  set  ret [expr ($a&0x000000FF)<<24]

  incr ret [expr ($b&0x000000FF)<<16]

  incr ret [expr ($c&0x000000FF)<<8]

  incr ret [expr ($d&0x000000FF)<<0]

  close $fd

  return $ret

}

proc t1sig {{db db}} {

  execsql {SELECT count(*), md5sum(a) FROM t1} $db

}

do_test exclusive2-1.0 {

  readPagerChangeCounter test.db

} {0}

#-----------------------------------------------------------------------

# The following tests - exclusive2-1.X - check that:

#

# 1-3:   Build a database with connection 1, calculate a signature.

# 4-9:   Modify the database using a second connection in a way that

#        does not modify the freelist, then reset the pager change-counter

#        to the value it had before the modifications.

# 8:     Check that using the first connection, the database signature

#        is still the same. This is because it uses the in-memory cache.

#        It can't tell the db has changed because we reset the change-counter.

# 9:     Increment the change-counter.

# 10:    Ensure that the first connection now sees the updated database. It

#        sees the change-counter has been incremented and discards the 

#        invalid in-memory cache.

#

# This will only work if the database cache is large enough to hold 

# the entire database. In the case of 1024 byte pages, this means

# the cache size must be at least 17. Otherwise, some pages will be

# loaded from the database file in step 8.

#

do_test exclusive2-1.1 {

  execsql {

    BEGIN;

    CREATE TABLE t1(a, b);

    INSERT INTO t1(a) VALUES(randstr(10, 400));

    INSERT INTO t1(a) VALUES(randstr(10, 400));

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    COMMIT;

    SELECT count(*) FROM t1;

  }

} {64}

do_test exclusive2-1.2.1 {

  # Make sure the pager cache is large enough to store the 

  # entire database.

  set nPage [expr [file size test.db]/1024]

  if {$::SQLITE_DEFAULT_CACHE_SIZE < $nPage} {

    execsql "PRAGMA cache_size = $nPage"

  }

  expr {[execsql {PRAGMA cache_size}] >= $nPage}

} {1}

do_test exclusive2-1.2 {

  set ::sig [t1sig]

  readPagerChangeCounter test.db

} {1}

do_test exclusive2-1.3 {

  t1sig

} $::sig

do_test exclusive2-1.4 {

  sqlite3 db2 test.db

  t1sig db2

} $::sig

do_test exclusive2-1.5 {

  execsql {

    UPDATE t1 SET b=a, a=NULL;

  } db2

  expr {[t1sig db2] eq $::sig}

} 0

do_test exclusive2-1.6 {

  readPagerChangeCounter test.db

} {2}

do_test exclusive2-1.7 {

  pagerChangeCounter test.db 1

} {1}

do_test exclusive2-1.9 {

  t1sig

  expr {[t1sig] eq $::sig}

} {1}

do_test exclusive2-1.10 {

  pagerChangeCounter test.db 2

} {2}

do_test exclusive2-1.11 {

  expr {[t1sig] eq $::sig}

} {0}

#--------------------------------------------------------------------

# These tests - exclusive2-2.X - are similar to exclusive2-1.X, 

# except that they are run with locking_mode=EXCLUSIVE.

#

# 1-3:   Build a database with exclusive-access connection 1, 

#        calculate a signature.

# 4:     Corrupt the database by writing 10000 bytes of garbage

#        starting at the beginning of page 2. Check that connection 1

#        still works. It should be accessing the in-memory cache.

# 5-6:   Modify the dataase change-counter. Connection 1 still works

#        entirely from in-memory cache, because it doesn't check the

#        change-counter.

# 7-8    Set the locking-mode back to normal. After the db is unlocked,

#        SQLite detects the modified change-counter and discards the

#        in-memory cache. Then it finds the corruption caused in step 4....

#

# As above, this test is only applicable if the pager cache is

# large enough to hold the entire database. With 1024 byte pages,

# this means 19 pages.  We also need to disable the soft-heap-limit

# to prevent memory-induced cache spills.

#

do_test exclusive2-2.1 {

  execsql {PRAGMA locking_mode = exclusive;}

  execsql {

    BEGIN;

    DELETE FROM t1;

    INSERT INTO t1(a) VALUES(randstr(10, 400));

    INSERT INTO t1(a) VALUES(randstr(10, 400));

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    INSERT INTO t1(a) SELECT randstr(10, 400) FROM t1;

    COMMIT;

    SELECT count(*) FROM t1;

  }

} {64}

do_test exclusive2-2.2.1 {

  # Make sure the pager cache is large enough to store the 

  # entire database.

  set nPage [expr [file size test.db]/1024]

  if {$::SQLITE_DEFAULT_CACHE_SIZE < $nPage} {

    execsql "PRAGMA cache_size = $nPage"

  }

  expr {[execsql {PRAGMA cache_size}] >= $nPage}

} {1}

do_test exclusive2-2.2 {

  set ::sig [t1sig]

  readPagerChangeCounter test.db

} {3}

do_test exclusive2-2.3 {

  t1sig

} $::sig

do_test exclusive2-2.4 {

  set ::fd [open test.db RDWR]

  fconfigure $::fd -translation binary

  seek $::fd 1024

  puts -nonewline $::fd [string repeat [binary format c 0] 10000]

  flush $::fd

  t1sig

} $::sig

do_test exclusive2-2.5 {

  pagerChangeCounter test.db 5 $::fd

} {5}

do_test exclusive2-2.6 {

  t1sig

} $::sig

do_test exclusive2-2.7 {

  execsql {PRAGMA locking_mode = normal}

  t1sig

} $::sig

do_test exclusive2-2.8 {

  set rc [catch {t1sig} msg]

  list $rc $msg

} {1 {database disk image is malformed}}

#--------------------------------------------------------------------

# These tests - exclusive2-3.X - verify that the pager change-counter

# is only incremented by the first change when in exclusive access

# mode. In normal mode, the change-counter is incremented once

# per write-transaction.

#

db close

db2 close

catch {close $::fd}

file delete -force test.db

file delete -force test.db-journal

do_test exclusive2-3.0 {

  sqlite3 db test.db

  execsql {

    BEGIN;

    CREATE TABLE t1(a UNIQUE);

    INSERT INTO t1 VALUES(randstr(10, 400));

    INSERT INTO t1 VALUES(randstr(10, 400));

    COMMIT;

  }

  readPagerChangeCounter test.db

} {1}

do_test exclusive2-3.1 {

  execsql {

    INSERT INTO t1 VALUES(randstr(10, 400));

  }

  readPagerChangeCounter test.db

} {2}

do_test exclusive2-3.2 {

  execsql {

    INSERT INTO t1 VALUES(randstr(10, 400));

  }

  readPagerChangeCounter test.db

} {3}

do_test exclusive2-3.3 {

  execsql {

    PRAGMA locking_mode = exclusive;

    INSERT INTO t1 VALUES(randstr(10, 400));

  }

  readPagerChangeCounter test.db

} {4}

do_test exclusive2-3.4 {

  execsql {

    INSERT INTO t1 VALUES(randstr(10, 400));

  }

  readPagerChangeCounter test.db

} {4}

do_test exclusive2-3.5 {

  execsql {

    PRAGMA locking_mode = normal;

    INSERT INTO t1 VALUES(randstr(10, 400));

  }

  readPagerChangeCounter test.db

} {4}

do_test exclusive2-3.6 {

  execsql {

    INSERT INTO t1 VALUES(randstr(10, 400));

  }

  readPagerChangeCounter test.db

} {5}

sqlite3_soft_heap_limit $soft_limit

finish_test