# 2010 June 15
#
# 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.
#
#***********************************************************************
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/malloc_common.tcl
#
# pager1-1.*: Test inter-process locking (clients in multiple processes).
#
# pager1-2.*: Test intra-process locking (multiple clients in this process).
#
# pager1-3.*: Savepoint related tests.
#
# pager1-4.*: Hot-journal related tests.
#
# pager1-5.*: Cases related to multi-file commits.
#
proc do_execsql_test {testname sql result} {
uplevel do_test $testname [list "execsql {$sql}"] [list $result]
}
proc do_catchsql_test {testname sql result} {
uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}
set a_string_counter 1
proc a_string {n} {
global a_string_counter
incr a_string_counter
string range [string repeat "${a_string_counter}." $n] 1 $n
}
db func a_string a_string
do_multiclient_test tn {
# Create and populate a database table using connection [db]. Check
# that connections [db2] and [db3] can see the schema and content.
#
do_test pager1-$tn.1 {
sql1 {
CREATE TABLE t1(a PRIMARY KEY, b);
CREATE INDEX i1 ON t1(b);
INSERT INTO t1 VALUES(1, 'one');
INSERT INTO t1 VALUES(2, 'two');
}
} {}
do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two}
# Open a transaction and add a row using [db]. This puts [db] in
# RESERVED state. Check that connections [db2] and [db3] can still
# read the database content as it was before the transaction was
# opened. [db] should see the inserted row.
#
do_test pager1-$tn.4 {
sql1 {
BEGIN;
INSERT INTO t1 VALUES(3, 'three');
}
} {}
do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
# [db] still has an open write transaction. Check that this prevents
# other connections (specifically [db2]) from writing to the database.
#
# Even if [db2] opens a transaction first, it may not write to the
# database. After the attempt to write the db within a transaction,
# [db2] is left with an open transaction, but not a read-lock on
# the main database. So it does not prevent [db] from committing.
#
do_test pager1-$tn.8 {
csql2 { UPDATE t1 SET a = a + 10 }
} {1 {database is locked}}
do_test pager1-$tn.9 {
csql2 {
BEGIN;
UPDATE t1 SET a = a + 10;
}
} {1 {database is locked}}
# Have [db] commit its transactions. Check the other connections can
# now see the new database content.
#
do_test pager1-$tn.10 { sql1 { COMMIT } } {}
do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
# Check that, as noted above, [db2] really did keep an open transaction
# after the attempt to write the database failed.
#
do_test pager1-$tn.14 {
csql2 { BEGIN }
} {1 {cannot start a transaction within a transaction}}
do_test pager1-$tn.15 { sql2 { ROLLBACK } } {}
# Have [db2] open a transaction and take a read-lock on the database.
# Check that this prevents [db] from writing to the database (outside
# of any transaction). After this fails, check that [db3] can read
# the db (showing that [db] did not take a PENDING lock etc.)
#
do_test pager1-$tn.15 {
sql2 { BEGIN; SELECT * FROM t1; }
} {1 one 2 two 3 three}
do_test pager1-$tn.16 {
csql1 { UPDATE t1 SET a = a + 10 }
} {1 {database is locked}}
do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
# This time, have [db] open a transaction before writing the database.
# This works - [db] gets a RESERVED lock which does not conflict with
# the SHARED lock [db2] is holding.
#
do_test pager1-$tn.18 {
sql1 {
BEGIN;
UPDATE t1 SET a = a + 10;
}
} {}
do_test pager1-$tn-19 {
sql1 { PRAGMA lock_status }
} {main reserved temp closed}
do_test pager1-$tn-20 {
sql2 { PRAGMA lock_status }
} {main shared temp closed}
# Check that all connections can still read the database. Only [db] sees
# the updated content (as the transaction has not been committed yet).
#
do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
# Because [db2] still has the SHARED lock, [db] is unable to commit the
# transaction. If it tries, an error is returned and the connection
# upgrades to a PENDING lock.
#
# Once this happens, [db] can read the database and see the new content,
# [db2] (still holding SHARED) can still read the old content, but [db3]
# (not holding any lock) is prevented by [db]'s PENDING from reading
# the database.
#
do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}}
do_test pager1-$tn-25 {
sql1 { PRAGMA lock_status }
} {main pending temp closed}
do_test pager1-$tn.26 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}
# Have [db2] commit its read transaction, releasing the SHARED lock it
# is holding. Now, neither [db2] nor [db3] may read the database (as [db]
# is still holding a PENDING).
#
do_test pager1-$tn.29 { sql2 { COMMIT } } {}
do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}}
do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}
# [db] is now able to commit the transaction. Once the transaction is
# committed, all three connections can read the new content.
#
do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {}
do_test pager1-$tn.26 { sql1 { COMMIT } } {}
do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three}
do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three}
do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three}
}
#-------------------------------------------------------------------------
# Savepoint related test cases.
#
do_test pager1-3.1 {
faultsim_delete_and_reopen
execsql {
CREATE TABLE t1(a PRIMARY KEY, b);
CREATE TABLE counter(
i CHECK (i<5),
u CHECK (u<10)
);
INSERT INTO counter VALUES(0, 0);
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
UPDATE counter SET i = i+1;
END;
CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN
UPDATE counter SET u = u+1;
END;
}
execsql { SELECT * FROM counter }
} {0 0}
do_execsql_test pager1-3.2 {
BEGIN;
INSERT INTO t1 VALUES(1, randomblob(1500));
INSERT INTO t1 VALUES(2, randomblob(1500));
INSERT INTO t1 VALUES(3, randomblob(1500));
SELECT * FROM counter;
} {3 0}
do_catchsql_test pager1-3.3 {
INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1
} {1 {constraint failed}}
do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}
do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}
do_execsql_test pager1-3.6 { COMMIT } {}
set otn 0
testvfs tv -default 1
foreach code [list {
set s 512
} {
set s 1024
set sql { PRAGMA journal_mode = memory }
} {
set s 2048
tv devchar safe_append
} {
set s 4096
} {
set s 4096
set sql { PRAGMA journal_mode = WAL }
} {
set s 8192
set sql { PRAGMA synchronous = off }
}] {
incr otn
set sql ""
tv devchar {}
eval $code
tv sectorsize $s
do_test pager1-3.7.$otn.0 {
faultsim_delete_and_reopen
execsql $sql
execsql {
PRAGMA cache_size = 10;
CREATE TABLE t1(i INTEGER PRIMARY KEY, j blob);
}
} {}
set tn 0
set lowpoint 0
foreach x {
100 x 0 100
x
70 22 96 59 96 50 22 56 21 16 37 64 43 40 0 38 22 38 55 0 6
43 62 32 93 54 18 13 29 45 66 29 25 61 31 53 82 75 25 96 86 10 69
2 29 6 60 80 95 42 82 85 50 68 96 90 39 78 69 87 97 48 74 65 43
x
86 34 26 50 41 85 58 44 89 22 6 51 45 46 58 32 97 6 1 12 32 2
69 39 48 71 33 31 5 58 90 43 24 54 12 9 18 57 4 38 91 42 27 45
50 38 56 29 10 0 26 37 83 1 78 15 47 30 75 62 46 29 68 5 30 4
27 96 33 95 79 75 56 10 29 70 32 75 52 88 5 36 50 57 46 63 88 65
x
44 95 64 20 24 35 69 61 61 2 35 92 42 46 23 98 78 1 38 72 79 35
94 37 13 59 5 93 27 58 80 75 58 7 67 13 10 76 84 4 8 70 81 45
8 41 98 5 60 26 92 29 91 90 2 62 40 4 5 22 80 15 83 76 52 88
29 5 68 73 72 7 54 17 89 32 81 94 51 28 53 71 8 42 54 59 70 79
x
} {
incr tn
set now [db one {SELECT count(i) FROM t1}]
if {$x == "x"} {
execsql { COMMIT ; BEGIN }
set lowpoint $now
do_test pager1.3.7.$otn.$tn {
sqlite3 db2 test.db
execsql {
SELECT COALESCE(max(i), 0) FROM t1;
PRAGMA integrity_check;
}
} [list $lowpoint ok]
db2 close
} else {
if {$now > $x } {
if { $x>=$lowpoint } {
execsql "ROLLBACK TO sp_$x"
} else {
execsql "DELETE FROM t1 WHERE i>$x"
set lowpoint $x
}
} elseif {$now < $x} {
for {set k $now} {$k < $x} {incr k} {
execsql "SAVEPOINT sp_$k"
execsql { INSERT INTO t1(j) VALUES(randomblob(1500)) }
}
}
do_execsql_test pager1.3.7.$otn.$tn {
SELECT COALESCE(max(i), 0) FROM t1;
PRAGMA integrity_check;
} [list $x ok]
}
}
}
db close
tv delete
#-------------------------------------------------------------------------
# Hot journal rollback related test cases.
#
# pager1.4.1.*: Test that the pager module deletes very small invalid
# journal files.
#
# pager1.4.2.*: Test that if the master journal pointer at the end of a
# hot-journal file appears to be corrupt (checksum does not
# compute) the associated journal is rolled back (and no
# xAccess() call to check for the presence of any master
# journal file is made).
#
# pager1.4.3.*: Test that the contents of a hot-journal are ignored if the
# page-size or sector-size in the journal header appear to
# be invalid (too large, too small or not a power of 2).
#
do_test pager1.4.1.1 {
faultsim_delete_and_reopen
execsql {
CREATE TABLE x(y, z);
INSERT INTO x VALUES(1, 2);
}
set fd [open test.db-journal w]
puts -nonewline $fd "helloworld"
close $fd
file exists test.db-journal
} {1}
do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2}
do_test pager1.4.1.3 { file exists test.db-journal } {0}
# Set up a [testvfs] to snapshot the file-system just before SQLite
# deletes the master-journal to commit a multi-file transaction.
#
# In subsequent test cases, invoking [faultsim_restore_and_reopen] sets
# up the file system to contain two databases, two hot-journal files and
# a master-journal.
#
do_test pager1.4.2.1 {
testvfs tstvfs -default 1
tstvfs filter xDelete
tstvfs script xDeleteCallback
proc xDeleteCallback {method file args} {
set file [file tail $file]
if { [string match *mj* $file] } { faultsim_save }
}
faultsim_delete_and_reopen
db func a_string a_string
execsql {
ATTACH 'test.db2' AS aux;
PRAGMA journal_mode = DELETE;
PRAGMA main.cache_size = 10;
PRAGMA aux.cache_size = 10;
CREATE TABLE t1(a UNIQUE, b UNIQUE);
CREATE TABLE aux.t2(a UNIQUE, b UNIQUE);
INSERT INTO t1 VALUES(a_string(200), a_string(300));
INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
INSERT INTO t2 SELECT * FROM t1;
BEGIN;
INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1;
INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1;
INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1;
INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1;
REPLACE INTO t2 SELECT * FROM t1;
COMMIT;
}
db close
tstvfs delete
} {}
do_test pager1.4.2.2 {
faultsim_restore_and_reopen
execsql {
SELECT count(*) FROM t1;
PRAGMA integrity_check;
}
} {4 ok}
do_test pager1.4.2.3 {
faultsim_restore_and_reopen
foreach f [glob test.db-mj*] { file delete -force $f }
execsql {
SELECT count(*) FROM t1;
PRAGMA integrity_check;
}
} {64 ok}
do_test pager1.4.2.4 {
faultsim_restore_and_reopen
hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456
execsql {
SELECT count(*) FROM t1;
PRAGMA integrity_check;
}
} {4 ok}
do_test pager1.4.2.5 {
faultsim_restore_and_reopen
hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456
foreach f [glob test.db-mj*] { file delete -force $f }
execsql {
SELECT count(*) FROM t1;
PRAGMA integrity_check;
}
} {4 ok}
do_test pager1.4.3.1 {
testvfs tstvfs -default 1
tstvfs filter xSync
tstvfs script xSyncCallback
proc xSyncCallback {method file args} {
set file [file tail $file]
if { 0==[string match *journal $file] } { faultsim_save }
}
faultsim_delete_and_reopen
execsql {
PRAGMA journal_mode = DELETE;
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(3, 4);
}
db close
tstvfs delete
} {}
foreach {tn ofst value result} {
2 20 31 {1 2 3 4}
3 20 32 {1 2 3 4}
4 20 33 {1 2 3 4}
5 20 65536 {1 2 3 4}
6 20 131072 {1 2 3 4}
7 24 511 {1 2 3 4}
8 24 513 {1 2 3 4}
9 24 65536 {1 2 3 4}
10 32 65536 {1 2}
} {
do_test pager1.4.3.$tn {
faultsim_restore_and_reopen
hexio_write test.db-journal $ofst [format %.8x $value]
execsql { SELECT * FROM t1 }
} $result
}
db close
#-------------------------------------------------------------------------
#
finish_test