# 2001 September 15

#

# Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.

#

# 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.31 2008/08/20 14:49:25 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

  }

  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 5

  } {}

  do_test pager-4.6.3 {

    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

    }

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

#

if {$tcl_platform(platform)!="windows" && $tcl_platform(platform)!="symbian"} {

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)}}