# 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