/ Artifact Content
Login
Home Timeline Branches Tags Tickets Wiki More...
Check-ins Using Download Hex
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Artifact 1f99651d856c8b9e1376781c981d1b903e93a478:

# 2010 April 13
#
# 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 file is testing the operation of "blocking-checkpoint"
# operations.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/wal_common.tcl
ifcapable !wal {finish_test ; return }

set testprefix wal5

proc db_page_count  {{file test.db}} { expr [file size $file] / 1024 }
proc wal_page_count {{file test.db}} { wal_frame_count ${file}-wal 1024 }


do_multiclient_test tn {


  set ::nBusyHandler 0
  set ::busy_handler_script ""
  proc busyhandler {n} {
    incr ::nBusyHandler 
    eval $::busy_handler_script
    return 0
  }

  proc reopen_all {} {
    code1 {db close}
    code2 {db2 close}
    code3 {db3 close}
    code1 {sqlite3 db test.db}
    code2 {sqlite3 db2 test.db}
    code3 {sqlite3 db3 test.db}
    sql1  { PRAGMA synchronous = NORMAL }
    code1 { db busy busyhandler }
  }

  do_test 1.$tn.1 {
    reopen_all
    sql1 {
      PRAGMA page_size = 1024;
      PRAGMA auto_vacuum = 0;
      CREATE TABLE t1(x, y);
      PRAGMA journal_mode = WAL;
      INSERT INTO t1 VALUES(1, zeroblob(1200));
      INSERT INTO t1 VALUES(2, zeroblob(1200));
      INSERT INTO t1 VALUES(3, zeroblob(1200));
    }
    expr [file size test.db] / 1024
  } {2}

  # Have connection 2 grab a read-lock on the current snapshot.
  do_test 1.$tn.2 { sql2 { BEGIN; SELECT x FROM t1 } } {1 2 3}

  # Attempt a checkpoint.
  do_test 1.$tn.3 {
    sql1 { PRAGMA wal_checkpoint }
    list [db_page_count] [wal_page_count]
  } {5 9}

  # Write to the db again. The log cannot wrap because of the lock still
  # held by connection 2. The busy-handler has not yet been invoked.
  do_test 1.$tn.4 {
    sql1 { INSERT INTO t1 VALUES(4, zeroblob(1200)) }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {5 12 0}

  # Now do a blocking-checkpoint. Set the busy-handler up so that connection
  # 2 releases its lock on the 6th invocation. The checkpointer should then
  # proceed to checkpoint the entire log file. Next write should go to the 
  # start of the log file.
  #
  set ::busy_handler_script { if {$n==5} { sql2 COMMIT } }
  do_test 1.$tn.5 {
    sql1 { PRAGMA wal_checkpoint = RESTART }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {6 12 6}
  do_test 1.$tn.6 {
    set ::nBusyHandler 0
    sql1 { INSERT INTO t1 VALUES(5, zeroblob(1200)) }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {6 12 0}

  do_test 1.$tn.7 {
    reopen_all
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {7 0 0}

  do_test 1.$tn.8  { sql2 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5}
  do_test 1.$tn.9  {
    sql1 { INSERT INTO t1 VALUES(6, zeroblob(1200)) }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {7 5 0}
  do_test 1.$tn.10 { sql3 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5 6}

  set ::busy_handler_script { 
    if {$n==5} { sql2 COMMIT } 
    if {$n==6} { set ::db_file_size [db_page_count] }
    if {$n==7} { sql3 COMMIT }
  }
  do_test 1.$tn.11 {
    sql1 { PRAGMA wal_checkpoint = RESTART }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {10 5 8}
  do_test 1.$tn.12 { set ::db_file_size } 10
}


#-------------------------------------------------------------------------
# This block of tests explores checkpoint operations on more than one 
# database file.
#
proc setup_and_attach_aux {} {
  sql1 { ATTACH 'test.db2' AS aux }
  sql2 { ATTACH 'test.db2' AS aux }
  sql3 { ATTACH 'test.db2' AS aux }
  sql1 {
    PRAGMA main.page_size=1024; PRAGMA main.journal_mode=WAL;
    PRAGMA aux.page_size=1024;  PRAGMA aux.journal_mode=WAL;
  }
}

proc file_page_counts {} {
  list [db_page_count  test.db ] \
       [wal_page_count test.db ] \
       [db_page_count  test.db2] \
       [wal_page_count test.db2]
}

# Test that executing "PRAGMA wal_checkpoint" checkpoints all attached
# databases, not just the main db.
#
do_multiclient_test tn {
  setup_and_attach_aux
  do_test 2.1.$tn.1 {
    sql1 {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
    }
  } {}
  do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5}
  do_test 2.1.$tn.3 { sql1 { PRAGMA wal_checkpoint } } {0 5 5}
  do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5}
}

do_multiclient_test tn {
  setup_and_attach_aux
  do_test 2.2.$tn.1 {
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
      INSERT INTO t2 VALUES(3, 4);
    }
  } {}
  do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7}
  do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
  do_test 2.2.$tn.4 { sql1 { PRAGMA wal_checkpoint = RESTART } } {1 5 5}
  do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7}
}

do_multiclient_test tn {
  setup_and_attach_aux
  do_test 2.3.$tn.1 {
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
    }
  } {}
  do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5}
  do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
  do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {}
  do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {}
  do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7}
  do_test 2.3.$tn.7 { sql1 { PRAGMA wal_checkpoint = FULL } } {1 7 5}
  do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7}
}

# Check that checkpoints block on the correct locks. And respond correctly
# if they cannot obtain those locks. There are three locks that a checkpoint
# may block on (in the following order):
#
#   1. The writer lock: FULL and RESTART checkpoints block until any writer
#      process releases its lock.
#
#   2. Readers using part of the log file. FULL and RESTART checkpoints block
#      until readers using part (but not all) of the log file have finished.
#
#   3. Readers using any of the log file. After copying data into the
#      database file, RESTART checkpoints block until readers using any part
#      of the log file have finished.
#
# This test case involves running a checkpoint while there exist other 
# processes holding all three types of locks.
#
foreach {tn1 checkpoint busy_on ckpt_expected expected} {
  1   PASSIVE   -   {0 5 5}   -
  2   TYPO      -   {0 5 5}   -

  3   FULL      -   {0 7 7}   2
  4   FULL      1   {1 5 5}   1
  5   FULL      2   {1 7 5}   2
  6   FULL      3   {0 7 7}   2

  7   RESTART   -   {0 7 7}   3
  8   RESTART   1   {1 5 5}   1
  9   RESTART   2   {1 7 5}   2
  10  RESTART   3   {1 7 7}   3

} {
  do_multiclient_test tn {
    setup_and_attach_aux
  
    proc busyhandler {x} {
      set ::max_busyhandler $x
      if {$::busy_on!="-" && $x==$::busy_on} { return 1 }
      switch -- $x {
        1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" }
        2 { sql3 "COMMIT" }
        3 { sql2 "COMMIT" }
      }
      return 0
    }
    set ::max_busyhandler -
  
    do_test 2.4.$tn1.$tn.1 {
      sql1 {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
      }
      sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) }
      sql3 { BEGIN; SELECT * FROM t1 }
    } {1 2}
  
    do_test 2.4.$tn1.$tn.2 {
      code1 { db busy busyhandler }
      sql1 "PRAGMA wal_checkpoint = $checkpoint"
    } $ckpt_expected
    do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected
  }
}


finish_test
This page was generated in about 0.005s by Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13