test/wal2simple.test

# 2017 September 19
#
# 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 the library in
# "PRAGMA journal_mode=WAL2" mode.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/malloc_common.tcl
source $testdir/wal_common.tcl

set testprefix wal2simple
ifcapable !wal {finish_test ; return }

#-------------------------------------------------------------------------
# The following tests verify that a client can switch in and out of wal
# and wal2 mode. But that it is not possible to change directly from wal
# to wal2, or from wal2 to wal mode.
#
do_execsql_test 1.1.0 {
  PRAGMA journal_mode = wal2
} {wal2}
execsql { SELECT * FROM sqlite_master} 
do_execsql_test 1.x {
  PRAGMA journal_mode;
  PRAGMA main.journal_mode;
} {wal2 wal2}
db close
do_test 1.1.1 { file size test.db } {1024}
do_test 1.1.2 { hexio_read test.db 18 2 } 0303

sqlite3 db test.db
do_execsql_test 1.2.0 {
  SELECT * FROM sqlite_master;
  PRAGMA journal_mode = delete;
} {delete}
db close
do_test 1.2.1 { file size test.db } {1024}
do_test 1.2.2 { hexio_read test.db 18 2 } 0101

sqlite3 db test.db
do_execsql_test 1.3.0 {
  SELECT * FROM sqlite_master;
  PRAGMA journal_mode = wal;
} {wal}
db close
do_test 1.3.1 { file size test.db } {1024}
do_test 1.3.2 { hexio_read test.db 18 2 } 0202

sqlite3 db test.db
do_catchsql_test 1.4.0 {
  PRAGMA journal_mode = wal2;
} {1 {cannot change from wal to wal2 mode}}
do_execsql_test 1.4.1 {
  PRAGMA journal_mode = wal;
  PRAGMA journal_mode = delete;
  PRAGMA journal_mode = wal2;
  PRAGMA journal_mode = wal2;
} {wal delete wal2 wal2}
do_catchsql_test 1.4.2 {
  PRAGMA journal_mode = wal;
} {1 {cannot change from wal2 to wal mode}}
db close
do_test 1.4.3 { hexio_read test.db 18 2 } 0303

#-------------------------------------------------------------------------
# Test that recovery in wal2 mode works.
#
forcedelete test.db test.db-wal test.db-wal2
reset_db
do_execsql_test 2.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  PRAGMA journal_mode = wal2;
  PRAGMA journal_size_limit = 5000;
} {wal2 5000}

proc wal_hook {DB nm nFrame} { $DB eval { PRAGMA wal_checkpoint } }
db wal_hook {wal_hook db}

for {set i 1} {$i <= 200} {incr i} {
  execsql { INSERT INTO t1 VALUES(NULL, randomblob(100)) }
  set res [db eval { SELECT sum(a), md5sum(b) FROM t1 }]

  do_test 2.1.$i {
    foreach f [glob -nocomplain test.db2*] { forcedelete $f }
    forcecopy test.db      test.db2
    forcecopy test.db-wal  test.db2-wal
    forcecopy test.db-wal2 test.db2-wal2

    sqlite3 db2 test.db2
    db2 eval { SELECT sum(a), md5sum(b) FROM t1 }
  } $res

  db2 close
}

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

reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(x BLOB, y INTEGER PRIMARY KEY);
  CREATE INDEX i1 ON t1(x);
  PRAGMA cache_size = 5;
  PRAGMA journal_mode = wal2;
} {wal2}

do_test 3.1 {
  execsql BEGIN
  for {set i 1} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES(randomblob(800), $i) }
  }
  execsql COMMIT
} {}

do_execsql_test 3.2 {
  PRAGMA integrity_check;
} {ok}

#-------------------------------------------------------------------------
catch { db close }
foreach f [glob -nocomplain test.db*] { forcedelete $f }
reset_db
do_execsql_test 4.0 {
  CREATE TABLE t1(x, y);
  PRAGMA journal_mode = wal2;
} {wal2}

do_execsql_test 4.1 {
  SELECT * FROM t1;
} {}

do_execsql_test 4.2 {
  INSERT INTO t1 VALUES(1, 2);
} {}

do_execsql_test 4.3 {
  SELECT * FROM t1;
} {1 2}

do_test 4.4 {
  sqlite3 db2 test.db
  execsql { SELECT * FROM t1 } db2
} {1 2}

do_test 4.5 {
  lsort [glob test.db*]
} {test.db test.db-shm test.db-wal test.db-wal2}

do_test 4.6 {
  db close
  db2 close
  sqlite3 db test.db
  execsql { SELECT * FROM t1 }
} {1 2}

do_execsql_test 4.7 {
  PRAGMA journal_size_limit = 4000;
  INSERT INTO t1 VALUES(3, 4);
  INSERT INTO t1 VALUES(5, 6);
  INSERT INTO t1 VALUES(7, 8);
  INSERT INTO t1 VALUES(9, 10);
  INSERT INTO t1 VALUES(11, 12);
  INSERT INTO t1 VALUES(13, 14);
  INSERT INTO t1 VALUES(15, 16);
  INSERT INTO t1 VALUES(17, 18);
  SELECT * FROM t1;
} {4000 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18}

do_test 4.8 {
  sqlite3 db2 test.db
  execsql { SELECT * FROM t1 } db2
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18}

do_test 4.9 {
  db close
  db2 close
  lsort [glob test.db*]
} {test.db}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX i1 ON t1(b, c);
  PRAGMA journal_mode = wal2;
  PRAGMA journal_size_limit = 4000;
} {wal2 4000}

proc wal_hook {DB nm nFrame} {
  $DB eval { PRAGMA wal_checkpoint }
}
db wal_hook [list wal_hook db]


foreach js {4000 8000 12000} {
  foreach NROW [list 100 200 300 400 500 600 1000] {
    do_test 5.$js.$NROW.1 {
      db eval "DELETE FROM t1"
      db eval "PRAGMA journal_size_limit = $js"
      set nTotal 0
      for {set i 0} {$i < $NROW} {incr i} {
        db eval { INSERT INTO t1 VALUES($i, $i, randomblob(abs(random()%50))) }
        incr nTotal $i
      }
      set {} {}
    } {}

    do_test 5.$js.$NROW.2 {
      sqlite3 db2 test.db
      db2 eval { 
        PRAGMA integrity_check;
        SELECT count(*), sum(b) FROM t1;
      }
    } [list ok $NROW $nTotal]

    db2 close
  }
}


#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE tx(x);
  PRAGMA journal_mode = wal2;
  PRAGMA journal_size_limit = 3500;
} {wal2 3500}

do_test 6.1 {
  for {set i 0} {$i < 10} {incr i} {
    execsql "CREATE TABLE t$i (x);"
  }
} {}

do_test 6.2.1 {
  foreach f [glob -nocomplain test.db2*] { forcedelete $f }
  forcecopy test.db-wal2 test.db2-wal2
  sqlite3 db2 test.db2
  db2 eval { SELECT * FROM sqlite_master }
} {}
do_test 6.2.2 {
  db2 eval {
    PRAGMA journal_mode = wal2;
    SELECT * FROM sqlite_master;
  }
} {wal2}

do_test 6.3.1 {
  db2 close
  foreach f [glob -nocomplain test.db2*] { forcedelete $f }
  forcecopy test.db-wal2 test.db2-wal2
  forcecopy test.db test.db2
  sqlite3 db2 test.db2
  db2 eval { SELECT * FROM sqlite_master }
} {table tx tx 2 {CREATE TABLE tx(x)}}
do_test 6.3.2 {
  db2 eval {
    PRAGMA journal_mode = wal2;
    SELECT * FROM sqlite_master;
  }
} {wal2 table tx tx 2 {CREATE TABLE tx(x)}}

do_test 6.4.1 {
  db2 close
  foreach f [glob -nocomplain test.db2*] { forcedelete $f }
  forcecopy test.db-wal2 test.db2-wal2
  forcecopy test.db-wal test.db2-wal
  sqlite3 db2 test.db2
  db2 eval { SELECT * FROM sqlite_master }
} {}
do_test 6.4.2 {
  db2 eval {
    PRAGMA journal_mode = wal2;
    SELECT * FROM sqlite_master;
  }
} {wal2}
db2 close

#-------------------------------------------------------------------------
reset_db
sqlite3 db2 test.db
do_execsql_test 7.0 {
  PRAGMA journal_size_limit = 10000;
  PRAGMA journal_mode = wal2;
  PRAGMA wal_autocheckpoint = 0;
  BEGIN;
    CREATE TABLE t1(a);
    INSERT INTO t1 VALUES( randomblob(8000) );
  COMMIT;
} {10000 wal2 0}

do_test 7.1 {
  list [file size test.db-wal] [file size test.db-wal2]
} {9464 0}

# Connection db2 is holding a PART1 lock. 
#
#   7.2.2: Test that the PART1 does not prevent db from switching to the
#          other wal file.
#
#   7.2.3: Test that the PART1 does prevent a checkpoint of test.db-wal.
#
#   7.2.4: Test that after the PART1 is released the checkpoint is possible.
#
do_test 7.2.1 {
  execsql {
    BEGIN;
      SELECT count(*) FROM t1;
  } db2
} {1}
do_test 7.2.2 {
  execsql {
    INSERT INTO t1 VALUES( randomblob(800) );
    INSERT INTO t1 VALUES( randomblob(800) );
  }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {13656 3176 1024}
do_test 7.2.3 {
  execsql { PRAGMA wal_checkpoint }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {13656 3176 1024}
do_test 7.2.4 {
  execsql { END } db2
  execsql { PRAGMA wal_checkpoint }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {13656 3176 11264}

# Connection db2 is holding a PART2_FULL1 lock. 
#
#   7.3.2: Test that the lock does not prevent checkpointing.
#
#   7.3.3: Test that the lock does prevent the writer from overwriting 
#          test.db-wal.
#
#   7.3.4: Test that after the PART2_FULL1 is released the writer can
#          switch wal files and overwrite test.db-wal
#
db close
db2 close
sqlite3 db test.db
sqlite3 db2 test.db
do_test 7.3.1 {
  execsql {
    PRAGMA wal_autocheckpoint = 0;
    PRAGMA journal_size_limit = 10000;
    INSERT INTO t1 VALUES(randomblob(10000));
    INSERT INTO t1 VALUES(randomblob(500));
  }
  execsql {
    BEGIN;
      SELECT count(*) FROM t1;
  } db2
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 3176 11264}
do_test 7.3.2 {
  execsql { PRAGMA wal_checkpoint }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 3176 21504}
do_test 7.3.3 {
  execsql { 
    INSERT INTO t1 VALUES(randomblob(10000));
    INSERT INTO t1 VALUES(randomblob(500));
  }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 18896 21504}
do_test 7.3.4 {
  execsql END db2
  execsql { INSERT INTO t1 VALUES(randomblob(5000)); }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 18896 21504}

# Connection db2 is holding a PART2 lock. 
#
#   7.4.2: Test that the lock does not prevent writer switching to test.db-wal.
#
#   7.3.3: Test that the lock does prevent checkpointing of test.db-wal2.
#
#   7.3.4: Test that after the PART2 is released test.db-wal2 can be
#          checkpointed.
#
db close
db2 close
sqlite3 db test.db
sqlite3 db2 test.db
do_test 7.4.1 {
  execsql {
    PRAGMA wal_autocheckpoint = 0;
    PRAGMA journal_size_limit = 10000;
    INSERT INTO t1 VALUES(randomblob(10000));
    INSERT INTO t1 VALUES(randomblob(10000));
    PRAGMA wal_checkpoint;
  }
  execsql {
    BEGIN;
      SELECT count(*) FROM t1;
  } db2
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 12608 44032}
do_test 7.4.2 {
  execsql { 
    INSERT INTO t1 VALUES(randomblob(5000));
  }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 12608 44032}
do_test 7.4.3 {
  execsql { PRAGMA wal_checkpoint }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 12608 44032}
do_test 7.4.4 {
  execsql END db2
  execsql { PRAGMA wal_checkpoint }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 12608 54272}

# Connection db2 is holding a PART1_FULL2 lock. 
#
#   7.5.2: Test that the lock does not prevent a checkpoint of test.db-wal2.
#
#   7.5.3: Test that the lock does prevent the writer from overwriting
#          test.db-wal2.
#
#   7.5.4: Test that after the PART1_FULL2 lock is released, the writer
#          can switch to test.db-wal2.
#
db close
db2 close
sqlite3 db test.db
sqlite3 db2 test.db
do_test 7.5.1 {
  execsql {
    PRAGMA wal_autocheckpoint = 0;
    PRAGMA journal_size_limit = 10000;
    INSERT INTO t1 VALUES(randomblob(10000));
    INSERT INTO t1 VALUES(randomblob(10000));
    PRAGMA wal_checkpoint;
    INSERT INTO t1 VALUES(randomblob(5000));
  }
  execsql {
    BEGIN;
      SELECT count(*) FROM t1;
  } db2
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 12608 64512}
do_test 7.5.2 {
  execsql { PRAGMA wal_checkpoint }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {12608 12608 75776}
do_test 7.5.3.1 {
  execsql { INSERT INTO t1 VALUES(randomblob(5000)) }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {14704 12608 75776}
do_test 7.5.3.2 {
  execsql { INSERT INTO t1 VALUES(randomblob(5000)) }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {22040 12608 75776}
do_test 7.5.4 {
  execsql END db2
  execsql { INSERT INTO t1 VALUES(randomblob(5000)) }
  list [file size test.db-wal] [file size test.db-wal2] [file size test.db]
} {22040 12608 75776}


finish_test