000001  # 2014 December 04
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  
000013  set testdir [file dirname $argv0]
000014  source $testdir/tester.tcl
000015  source $testdir/wal_common.tcl
000016  set testprefix e_walauto
000017  
000018  # Do not run this test on OpenBSD, as it depends on read() and mmap both
000019  # accessing the same coherent view of the "test.db-shm" file. This doesn't
000020  # work on OpenBSD.
000021  #
000022  if {$tcl_platform(os) == "OpenBSD"} {
000023    finish_test
000024    return
000025  }
000026  
000027  # This module uses hard-coded offsets which do not work if the reserved_bytes
000028  # value is nonzero.
000029  if {[nonzero_reserved_bytes]} {finish_test; return;}
000030  
000031  
000032  proc read_nbackfill {} {
000033    seek $::shmfd 96
000034    binary scan [read $::shmfd 4] n nBackfill
000035    set nBackfill
000036  }
000037  proc read_mxframe {} {
000038    seek $::shmfd 16
000039    binary scan [read $::shmfd 4] n mxFrame
000040    set mxFrame
000041  }
000042  
000043  # Assuming that the main db for database handle
000044  #
000045  proc do_autocommit_threshold_test {tn value} {
000046  
000047    set nBackfillSaved [read_nbackfill]
000048    while {1} {
000049      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000050      if {[read_mxframe] >= $value} break
000051    }
000052    
000053    set nBackfillNew [read_nbackfill]
000054    uplevel [list do_test $tn "expr $nBackfillNew > $nBackfillSaved" 1]
000055  }
000056  
000057  # EVIDENCE-OF: R-30135-06439 The wal_autocheckpoint pragma can be used
000058  # to invoke this interface from SQL.
000059  #
000060  #   All tests in this file are run twice - once using the
000061  #   sqlite3_wal_autocheckpoint() API, and once using "PRAGMA
000062  #   wal_autocheckpoint".
000063  #
000064  foreach {tn code} {
000065    1 {
000066      proc autocheckpoint {db value} {
000067        uplevel [list $db eval "PRAGMA wal_autocheckpoint = $value"]
000068      }
000069    }
000070  
000071    2 {
000072      proc autocheckpoint {db value} {
000073        uplevel [list sqlite3_wal_autocheckpoint $db $value]
000074        return $value
000075      }
000076    }
000077  } {
000078  
000079    eval $code
000080  
000081    reset_db
000082    execsql { PRAGMA auto_vacuum = 0 }
000083    do_execsql_test 1.$tn.0 { PRAGMA journal_mode = WAL } {wal}
000084    do_execsql_test 1.$tn.1 { CREATE TABLE t1(a, b) }
000085    set shmfd [open "test.db-shm" rb]
000086  
000087    # EVIDENCE-OF: R-41531-51083 Every new database connection defaults to
000088    # having the auto-checkpoint enabled with a threshold of 1000 or
000089    # SQLITE_DEFAULT_WAL_AUTOCHECKPOINT pages.
000090    #
000091    do_autocommit_threshold_test 1.$tn.2 1000
000092    db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000093    do_autocommit_threshold_test 1.$tn.3 1000
000094  
000095    # EVIDENCE-OF: R-38128-34102 The sqlite3_wal_autocheckpoint(D,N) is a
000096    # wrapper around sqlite3_wal_hook() that causes any database on database
000097    # connection D to automatically checkpoint after committing a
000098    # transaction if there are N or more frames in the write-ahead log file.
000099    #
000100    do_test 1.$tn.4 {
000101      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000102      autocheckpoint db 100
000103    } {100}
000104    do_autocommit_threshold_test 1.$tn.5 100
000105  
000106    do_test 1.$tn.6 {
000107      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000108      autocheckpoint db 500
000109    } {500}
000110    do_autocommit_threshold_test 1.$tn.7 500
000111  
000112    # EVIDENCE-OF: R-26993-43540 Passing zero or a negative value as the
000113    # nFrame parameter disables automatic checkpoints entirely.
000114    #
000115    do_test 1.$tn.7 {
000116      autocheckpoint db 0    ;# Set to zero
000117      for {set i 0} {$i < 10000} {incr i} {
000118        db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000119      }
000120      expr {[file size test.db-wal] > (5 * 1024 * 1024)}
000121    } 1
000122    do_test 1.$tn.8 {
000123      sqlite3_wal_checkpoint_v2 db truncate
000124      file size test.db-wal
000125    } 0
000126    do_test 1.$tn.9 {
000127      autocheckpoint db -4    ;# Set to a negative value
000128      for {set i 0} {$i < 10000} {incr i} {
000129        db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000130      }
000131      expr {[file size test.db-wal] > (5 * 1024 * 1024)}
000132    } 1
000133  
000134    # EVIDENCE-OF: R-10203-42688 The callback registered by this function
000135    # replaces any existing callback registered using sqlite3_wal_hook().
000136    #
000137    set ::wal_hook_callback 0
000138    proc wal_hook_callback {args} { incr ::wal_hook_callback ; return 0 }
000139    do_test 1.$tn.10.1 {
000140      db wal_hook wal_hook_callback
000141      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000142      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000143      set ::wal_hook_callback
000144    } 2
000145    do_test 1.$tn.10.2 {
000146      autocheckpoint db 100
000147      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000148      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000149      set ::wal_hook_callback
000150    } 2
000151  
000152    # EVIDENCE-OF: R-17497-43474 Likewise, registering a callback using
000153    # sqlite3_wal_hook() disables the automatic checkpoint mechanism
000154    # configured by this function.
000155    do_test 1.$tn.11.1 {
000156      sqlite3_wal_checkpoint_v2 db truncate
000157      file size test.db-wal
000158    } 0
000159    do_test 1.$tn.11.2 {
000160      autocheckpoint db 100 
000161      for {set i 0} {$i < 1000} {incr i} {
000162        db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000163      }
000164      expr {[file size test.db-wal] < (1 * 1024 * 1024)}
000165    } 1
000166    do_test 1.$tn.11.3 {
000167      db wal_hook wal_hook_callback
000168      for {set i 0} {$i < 1000} {incr i} {
000169        db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000170      }
000171      expr {[file size test.db-wal] < (1 * 1024 * 1024)}
000172    } 0
000173  
000174    # EVIDENCE-OF: R-33080-59193 Checkpoints initiated by this mechanism 
000175    # are PASSIVE.
000176    #
000177    set ::busy_callback_count 0
000178    proc busy_callback {args} {
000179      incr ::busy_callback_count
000180      return 0
000181    }
000182    do_test 1.$tn.12.1 {
000183      sqlite3_wal_checkpoint_v2 db truncate
000184      autocheckpoint db 100 
000185      db busy busy_callback
000186      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000187      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000188    } {}
000189    do_test 1.$tn.12.2 {
000190      sqlite3 db2 test.db
000191      db2 eval { BEGIN; SELECT * FROM t1 LIMIT 10; }
000192      read_nbackfill
000193    } {0}
000194    do_test 1.$tn.12.3 {
000195      for {set i 0} {$i < 1000} {incr i} {
000196        db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000197      }
000198      read_nbackfill
000199    } {2}
000200    do_test 1.$tn.12.4 {
000201      set ::busy_callback_count
000202    } {0}
000203    db2 close
000204  
000205    do_test 1.$tn.12.5 {
000206      db eval { INSERT INTO t1 VALUES(randomblob(100), randomblob(100)) }
000207      read_nbackfill
000208    } {1559}
000209  
000210    db close
000211    close $shmfd
000212  }
000213  
000214  finish_test