# 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
source $testdir/wal_common.tcl
# Do not use a codec for tests in this file, as the database file is
# manipulated directly using tcl scripts (using the [hexio_write] command).
#
do_not_use_codec
#
# 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.
#
# pager1-6.*: Cases related to "PRAGMA max_page_count"
#
# pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE"
#
# pager1-8.*: Cases using temporary and in-memory databases.
#
# pager1-9.*: Tests related to the backup API.
#
# pager1-10.*: Test that the assumed file-system sector-size is limited to
# 64KB.
#
# pager1-12.*: Tests involving "PRAGMA page_size"
#
# pager1-13.*: Cases specific to "PRAGMA journal_mode=PERSIST"
#
# pager1-14.*: Cases specific to "PRAGMA journal_mode=OFF"
#
# pager1-15.*: Varying sqlite3_vfs.szOsFile
#
# pager1-16.*: Varying sqlite3_vfs.mxPathname
#
# pager1-17.*: Tests related to "PRAGMA omit_readlock"
#
# pager1-18.*: Test that the pager layer responds correctly if the b-tree
# requests an invalid page number (due to db corruption).
#
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}
# Install a busy-handler for connection [db].
#
set ::nbusy [list]
proc busy {n} {
lappend ::nbusy $n
if {$n>5} { sql2 COMMIT }
return 0
}
db busy busy
do_test pager1-$tn.29 {
sql1 { BEGIN ; INSERT INTO t1 VALUES('x', 'y') }
} {}
do_test pager1-$tn.30 {
sql2 { BEGIN ; SELECT * FROM t1 }
} {21 one 22 two 23 three}
do_test pager1-$tn.31 { sql1 COMMIT } {}
do_test pager1-$tn.32 { set ::nbusy } {0 1 2 3 4 5 6}
}
#-------------------------------------------------------------------------
# Savepoint related test cases.
#
# pager1-3.1.2.*: Force a savepoint rollback to cause the database file
# to grow.
#
# pager1-3.1.3.*: Use a journal created in synchronous=off mode as part
# of a savepoint rollback.
#
do_test pager1-3.1.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.1.2 {
PRAGMA cache_size = 10;
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.1.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 } {}
foreach {tn sql tcl} {
7 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } {
testvfs tv -default 1
tv devchar safe_append
}
8 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 2 } {
testvfs tv -default 1
tv devchar sequential
}
9 { PRAGMA synchronous = FULL } { }
10 { PRAGMA synchronous = NORMAL } { }
11 { PRAGMA synchronous = OFF } { }
12 { PRAGMA synchronous = FULL ; PRAGMA fullfsync = 1 } { }
13 { PRAGMA synchronous = FULL } {
testvfs tv -default 1
tv devchar sequential
}
} {
do_test pager1-3.$tn.1 {
eval $tcl
faultsim_delete_and_reopen
db func a_string a_string
execsql $sql
execsql {
PRAGMA auto_vacuum = 2;
PRAGMA cache_size = 10;
CREATE TABLE z(x INTEGER PRIMARY KEY, y);
BEGIN;
INSERT INTO z VALUES(NULL, a_string(800));
INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 2
INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 4
INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 8
INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 16
INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 32
INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 64
INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 128
INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 256
COMMIT;
}
execsql { PRAGMA auto_vacuum }
} {2}
do_execsql_test pager1-3.$tn.2 {
BEGIN;
INSERT INTO z VALUES(NULL, a_string(800));
INSERT INTO z VALUES(NULL, a_string(800));
SAVEPOINT one;
UPDATE z SET y = NULL WHERE x>256;
PRAGMA incremental_vacuum;
SELECT count(*) FROM z WHERE x < 100;
ROLLBACK TO one;
COMMIT;
} {99}
do_execsql_test pager1-3.$tn.3 {
BEGIN;
SAVEPOINT one;
UPDATE z SET y = y||x;
ROLLBACK TO one;
COMMIT;
SELECT count(*) FROM z;
} {258}
do_execsql_test pager1-3.$tn.4 {
SAVEPOINT one;
UPDATE z SET y = y||x;
ROLLBACK TO one;
} {}
do_execsql_test pager1-3.$tn.5 {
SELECT count(*) FROM z;
RELEASE one;
PRAGMA integrity_check;
} {258 ok}
db close
catch { 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).
#
# pager1.4.4.*: Test hot-journal rollback of journal file with a master
# journal pointer generated in various "PRAGMA synchronous"
# modes.
#
# pager1.4.5.*: Test that hot-journal rollback stops if it encounters a
# journal-record for which the checksum fails.
#
# pager1.4.6.*: Test that when rolling back a hot-journal that contains a
# master journal pointer, the master journal file is deleted
# after all the hot-journals that refer to it are deleted.
#
# pager1.4.7.*: Test that if a hot-journal file exists but a client can
# open it for reading only, the database cannot be accessed and
# SQLITE_CANTOPEN is returned.
#
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
# Set up a VFS that snapshots the file-system just before a master journal
# file is deleted to commit a multi-file transaction. Specifically, the
# file-system is saved just before the xDelete() call to remove the
# master journal file from the file-system.
#
testvfs tv -default 1
tv script copy_on_mj_delete
set ::mj_filename_length 0
proc copy_on_mj_delete {method filename args} {
if {[string match *mj* [file tail $filename]]} {
set ::mj_filename_length [string length $filename]
faultsim_save
}
return SQLITE_OK
}
set pwd [pwd]
foreach {tn1 tcl} {
1 { set prefix "test.db" }
2 {
# This test depends on the underlying VFS being able to open paths
# 512 bytes in length. The idea is to create a hot-journal file that
# contains a master-journal pointer so large that it could contain
# a valid page record (if the file page-size is 512 bytes). So as to
# make sure SQLite doesn't get confused by this.
#
set nPadding [expr 511 - $::mj_filename_length]
# We cannot just create a really long database file name to open, as
# Linux limits a single component of a path to 255 bytes by default
# (and presumably other systems have limits too). So create a directory
# hierarchy to work in.
#
set dirname "d123456789012345678901234567890/"
set nDir [expr $nPadding / 32]
if { $nDir } {
set p [string repeat $dirname $nDir]
file mkdir $p
cd $p
}
set padding [string repeat x [expr $nPadding %32]]
set prefix "test.db${padding}"
}
} {
eval $tcl
foreach {tn2 sql} {
o {
PRAGMA main.synchronous=OFF;
PRAGMA aux.synchronous=OFF;
}
o512 {
PRAGMA main.synchronous=OFF;
PRAGMA aux.synchronous=OFF;
PRAGMA main.page_size = 512;
PRAGMA aux.page_size = 512;
}
n {
PRAGMA main.synchronous=NORMAL;
PRAGMA aux.synchronous=NORMAL;
}
f {
PRAGMA main.synchronous=FULL;
PRAGMA aux.synchronous=FULL;
}
} {
set tn "${tn1}.${tn2}"
# Set up a connection to have two databases, test.db (main) and
# test.db2 (aux). Then run a multi-file transaction on them. The
# VFS will snapshot the file-system just before the master-journal
# file is deleted to commit the transaction.
#
tv filter xDelete
do_test pager1-4.4.$tn.1 {
faultsim_delete_and_reopen $prefix
execsql "
ATTACH '${prefix}2' AS aux;
$sql
CREATE TABLE a(x);
CREATE TABLE aux.b(x);
INSERT INTO a VALUES('double-you');
INSERT INTO a VALUES('why');
INSERT INTO a VALUES('zed');
INSERT INTO b VALUES('won');
INSERT INTO b VALUES('too');
INSERT INTO b VALUES('free');
"
execsql {
BEGIN;
INSERT INTO a SELECT * FROM b WHERE rowid<=3;
INSERT INTO b SELECT * FROM a WHERE rowid<=3;
COMMIT;
}
} {}
tv filter {}
# Check that the transaction was committed successfully.
#
do_execsql_test pager1-4.4.$tn.2 {
SELECT * FROM a
} {double-you why zed won too free}
do_execsql_test pager1-4.4.$tn.3 {
SELECT * FROM b
} {won too free double-you why zed}
# Restore the file-system and reopen the databases. Check that it now
# appears that the transaction was not committed (because the file-system
# was restored to the state where it had not been).
#
do_test pager1-4.4.$tn.4 {
faultsim_restore_and_reopen $prefix
execsql "ATTACH '${prefix}2' AS aux"
} {}
do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}
do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}
# Restore the file-system again. This time, before reopening the databases,
# delete the master-journal file from the file-system. It now appears that
# the transaction was committed (no master-journal file == no rollback).
#
do_test pager1-4.4.$tn.7 {
faultsim_restore_and_reopen $prefix
foreach f [glob ${prefix}-mj*] { file delete -force $f }
execsql "ATTACH '${prefix}2' AS aux"
} {}
do_execsql_test pager1-4.4.$tn.8 {
SELECT * FROM a
} {double-you why zed won too free}
do_execsql_test pager1-4.4.$tn.9 {
SELECT * FROM b
} {won too free double-you why zed}
}
cd $pwd
}
db close
tv delete
file delete -force $dirname
# Set up a VFS to make a copy of the file-system just before deleting a
# journal file to commit a transaction. The transaction modifies exactly
# two database pages (and page 1 - the change counter).
#
testvfs tv -default 1
tv sectorsize 512
tv script copy_on_journal_delete
tv filter xDelete
proc copy_on_journal_delete {method filename args} {
if {[string match *journal $filename]} faultsim_save
return SQLITE_OK
}
faultsim_delete_and_reopen
do_execsql_test pager1.4.5.1 {
PRAGMA page_size = 1024;
CREATE TABLE t1(a, b);
CREATE TABLE t2(a, b);
INSERT INTO t1 VALUES('I', 'II');
INSERT INTO t2 VALUES('III', 'IV');
BEGIN;
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t2 VALUES(3, 4);
COMMIT;
} {}
tv filter {}
# Check the transaction was committed:
#
do_execsql_test pager1.4.5.2 {
SELECT * FROM t1;
SELECT * FROM t2;
} {I II 1 2 III IV 3 4}
# Now try four tests:
#
# pager1-4.5.3: Restore the file-system. Check that the whole transaction
# is rolled back.
#
# pager1-4.5.4: Restore the file-system. Corrupt the first record in the
# journal. Check the transaction is not rolled back.
#
# pager1-4.5.5: Restore the file-system. Corrupt the second record in the
# journal. Check that the first record in the transaction is
# played back, but not the second.
#
# pager1-4.5.6: Restore the file-system. Try to open the database with a
# readonly connection. This should fail, as a read-only
# connection cannot roll back the database file.
#
faultsim_restore_and_reopen
do_execsql_test pager1.4.5.3 {
SELECT * FROM t1;
SELECT * FROM t2;
} {I II III IV}
faultsim_restore_and_reopen
hexio_write test.db-journal [expr 512+4+1024 - 202] 0123456789ABCDEF
do_execsql_test pager1.4.5.4 {
SELECT * FROM t1;
SELECT * FROM t2;
} {I II 1 2 III IV 3 4}
faultsim_restore_and_reopen
hexio_write test.db-journal [expr 512+4+1024+4+4+1024 - 202] 0123456789ABCDEF
do_execsql_test pager1.4.5.5 {
SELECT * FROM t1;
SELECT * FROM t2;
} {I II III IV 3 4}
faultsim_restore_and_reopen
db close
sqlite3 db test.db -readonly 1
do_catchsql_test pager1.4.5.6 {
SELECT * FROM t1;
SELECT * FROM t2;
} {1 {disk I/O error}}
db close
# Snapshot the file-system just before multi-file commit. Save the name
# of the master journal file in $::mj_filename.
#
tv script copy_on_mj_delete
tv filter xDelete
proc copy_on_mj_delete {method filename args} {
if {[string match *mj* [file tail $filename]]} {
set ::mj_filename $filename
faultsim_save
}
return SQLITE_OK
}
do_test pager1.4.6.1 {
faultsim_delete_and_reopen
execsql {
ATTACH 'test.db2' AS two;
CREATE TABLE t1(a, b);
CREATE TABLE two.t2(a, b);
INSERT INTO t1 VALUES(1, 't1.1');
INSERT INTO t2 VALUES(1, 't2.1');
BEGIN;
UPDATE t1 SET b = 't1.2';
UPDATE t2 SET b = 't2.2';
COMMIT;
}
tv filter {}
db close
} {}
faultsim_restore_and_reopen
do_execsql_test pager1.4.6.2 { SELECT * FROM t1 } {1 t1.1}
do_test pager1.4.6.3 { file exists $::mj_filename } {1}
do_execsql_test pager1.4.6.4 {
ATTACH 'test.db2' AS two;
SELECT * FROM t2;
} {1 t2.1}
do_test pager1.4.6.5 { file exists $::mj_filename } {0}
faultsim_restore_and_reopen
db close
do_test pager1.4.6.8 {
set ::mj_filename1 $::mj_filename
tv filter xDelete
sqlite3 db test.db2
execsql {
ATTACH 'test.db3' AS three;
CREATE TABLE three.t3(a, b);
INSERT INTO t3 VALUES(1, 't3.1');
BEGIN;
UPDATE t2 SET b = 't2.3';
UPDATE t3 SET b = 't3.3';
COMMIT;
}
expr {$::mj_filename1 != $::mj_filename}
} {1}
faultsim_restore_and_reopen
tv filter {}
# The file-system now contains:
#
# * three databases
# * three hot-journal files
# * two master-journal files.
#
# The hot-journals associated with test.db2 and test.db3 point to
# master journal $::mj_filename. The hot-journal file associated with
# test.db points to master journal $::mj_filename1. So reading from
# test.db should delete $::mj_filename1.
#
do_test pager1.4.6.9 {
lsort [glob test.db*]
} [lsort [list \
test.db test.db2 test.db3 \
test.db-journal test.db2-journal test.db3-journal \
[file tail $::mj_filename] [file tail $::mj_filename1]
]]
# The master-journal $::mj_filename1 contains pointers to test.db and
# test.db2. However the hot-journal associated with test.db2 points to
# a different master-journal. Therefore, reading from test.db only should
# be enough to cause SQLite to delete $::mj_filename1.
#
do_test pager1.4.6.10 { file exists $::mj_filename } {1}
do_test pager1.4.6.11 { file exists $::mj_filename1 } {1}
do_execsql_test pager1.4.6.12 { SELECT * FROM t1 } {1 t1.1}
do_test pager1.4.6.13 { file exists $::mj_filename } {1}
do_test pager1.4.6.14 { file exists $::mj_filename1 } {0}
do_execsql_test pager1.4.6.12 {
ATTACH 'test.db2' AS two;
SELECT * FROM t2;
} {1 t2.1}
do_test pager1.4.6.13 { file exists $::mj_filename } {1}
do_execsql_test pager1.4.6.14 {
ATTACH 'test.db3' AS three;
SELECT * FROM t3;
} {1 t3.1}
do_test pager1.4.6.15 { file exists $::mj_filename } {0}
db close
tv delete
testvfs tv -default 1
tv sectorsize 512
tv script copy_on_journal_delete
tv filter xDelete
proc copy_on_journal_delete {method filename args} {
if {[string match *journal $filename]} faultsim_save
return SQLITE_OK
}
faultsim_delete_and_reopen
do_execsql_test pager1.4.7.1 {
CREATE TABLE t1(x PRIMARY KEY, y);
CREATE INDEX i1 ON t1(y);
INSERT INTO t1 VALUES('I', 'one');
INSERT INTO t1 VALUES('II', 'four');
INSERT INTO t1 VALUES('III', 'nine');
BEGIN;
INSERT INTO t1 VALUES('IV', 'sixteen');
INSERT INTO t1 VALUES('V' , 'twentyfive');
COMMIT;
} {}
tv filter {}
db close
tv delete
do_test pager1.4.7.2 {
faultsim_restore_and_reopen
catch {file attributes test.db-journal -permissions r--------}
catch {file attributes test.db-journal -readonly 1}
catchsql { SELECT * FROM t1 }
} {1 {unable to open database file}}
do_test pager1.4.7.3 {
db close
catch {file attributes test.db-journal -permissions rw-rw-rw-}
catch {file attributes test.db-journal -readonly 0}
file delete test.db-journal
file exists test.db-journal
} {0}
#-------------------------------------------------------------------------
# The following tests deal with multi-file commits.
#
# pager1-5.1.*: The case where a multi-file cannot be committed because
# another connection is holding a SHARED lock on one of the
# files. After the SHARED lock is removed, the COMMIT succeeds.
#
# pager1-5.2.*: Multi-file commits with journal_mode=memory.
#
# pager1-5.3.*: Multi-file commits with journal_mode=memory.
#
# pager1-5.4.*: Check that with synchronous=normal, the master-journal file
# name is added to a journal file immediately after the last
# journal record. But with synchronous=full, extra unused space
# is allocated between the last journal record and the
# master-journal file name so that the master-journal file
# name does not lie on the same sector as the last journal file
# record.
#
# pager1-5.5.*: Check that in journal_mode=PERSIST mode, a journal file is
# truncated to zero bytes when a multi-file transaction is
# committed (instead of the first couple of bytes being zeroed).
#
#
do_test pager1-5.1.1 {
faultsim_delete_and_reopen
execsql {
ATTACH 'test.db2' AS aux;
CREATE TABLE t1(a, b);
CREATE TABLE aux.t2(a, b);
INSERT INTO t1 VALUES(17, 'Lenin');
INSERT INTO t1 VALUES(22, 'Stalin');
INSERT INTO t1 VALUES(53, 'Khrushchev');
}
} {}
do_test pager1-5.1.2 {
execsql {
BEGIN;
INSERT INTO t1 VALUES(64, 'Brezhnev');
INSERT INTO t2 SELECT * FROM t1;
}
sqlite3 db2 test.db2
execsql {
BEGIN;
SELECT * FROM t2;
} db2
} {}
do_test pager1-5.1.3 {
catchsql COMMIT
} {1 {database is locked}}
do_test pager1-5.1.4 {
execsql COMMIT db2
execsql COMMIT
execsql { SELECT * FROM t2 } db2
} {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev}
do_test pager1-5.1.5 {
db2 close
} {}
do_test pager1-5.2.1 {
execsql {
PRAGMA journal_mode = memory;
BEGIN;
INSERT INTO t1 VALUES(84, 'Andropov');
INSERT INTO t2 VALUES(84, 'Andropov');
COMMIT;
}
} {memory}
do_test pager1-5.3.1 {
execsql {
PRAGMA journal_mode = off;
BEGIN;
INSERT INTO t1 VALUES(85, 'Gorbachev');
INSERT INTO t2 VALUES(85, 'Gorbachev');
COMMIT;
}
} {off}
do_test pager1-5.4.1 {
db close
testvfs tv
sqlite3 db test.db -vfs tv
execsql { ATTACH 'test.db2' AS aux }
tv filter xDelete
tv script max_journal_size
tv sectorsize 512
set ::max_journal 0
proc max_journal_size {method args} {
set sz 0
catch { set sz [file size test.db-journal] }
if {$sz > $::max_journal} {
set ::max_journal $sz
}
return SQLITE_OK
}
execsql {
PRAGMA journal_mode = DELETE;
PRAGMA synchronous = NORMAL;
BEGIN;
INSERT INTO t1 VALUES(85, 'Gorbachev');
INSERT INTO t2 VALUES(85, 'Gorbachev');
COMMIT;
}
set ::max_journal
} [expr 2615+[string length [pwd]]]
do_test pager1-5.4.2 {
set ::max_journal 0
execsql {
PRAGMA synchronous = full;
BEGIN;
DELETE FROM t1 WHERE b = 'Lenin';
DELETE FROM t2 WHERE b = 'Lenin';
COMMIT;
}
set ::max_journal
} [expr 3111+[string length [pwd]]]
db close
tv delete
do_test pager1-5.5.1 {
sqlite3 db test.db
execsql {
ATTACH 'test.db2' AS aux;
PRAGMA journal_mode = PERSIST;
CREATE TABLE t3(a, b);
INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1;
UPDATE t3 SET b = randomblob(1500);
}
expr [file size test.db-journal] > 15000
} {1}
do_test pager1-5.5.2 {
execsql {
PRAGMA synchronous = full;
BEGIN;
DELETE FROM t1 WHERE b = 'Stalin';
DELETE FROM t2 WHERE b = 'Stalin';
COMMIT;
}
file size test.db-journal
} {0}
#-------------------------------------------------------------------------
# The following tests work with "PRAGMA max_page_count"
#
do_test pager1-6.1 {
faultsim_delete_and_reopen
execsql {
PRAGMA max_page_count = 10;
CREATE TABLE t2(a, b);
CREATE TABLE t3(a, b);
CREATE TABLE t4(a, b);
CREATE TABLE t5(a, b);
CREATE TABLE t6(a, b);
CREATE TABLE t7(a, b);
CREATE TABLE t8(a, b);
CREATE TABLE t9(a, b);
CREATE TABLE t10(a, b);
}
} {10}
do_catchsql_test pager1-6.2 {
CREATE TABLE t11(a, b)
} {1 {database or disk is full}}
do_execsql_test pager1-6.4 { PRAGMA max_page_count } {10}
do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15}
do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b) } {}
do_execsql_test pager1-6.7 {
BEGIN;
INSERT INTO t11 VALUES(1, 2);
PRAGMA max_page_count = 13;
} {13}
do_execsql_test pager1-6.8 {
INSERT INTO t11 VALUES(3, 4);
PRAGMA max_page_count = 10;
} {11}
do_execsql_test pager1-6.9 { COMMIT } {}
#-------------------------------------------------------------------------
# The following tests work with "PRAGMA journal_mode=TRUNCATE" and
# "PRAGMA locking_mode=EXCLUSIVE".
#
# Each test is specified with 5 variables. As follows:
#
# $tn: Test Number. Used as part of the [do_test] test names.
# $sql: SQL to execute.
# $res: Expected result of executing $sql.
# $js: The expected size of the journal file, in bytes, after executing
# the SQL script. Or -1 if the journal is not expected to exist.
# $ws: The expected size of the WAL file, in bytes, after executing
# the SQL script. Or -1 if the WAL is not expected to exist.
#
ifcapable wal {
faultsim_delete_and_reopen
foreach {tn sql res js ws} [subst {
1 {
CREATE TABLE t1(a, b);
PRAGMA auto_vacuum=OFF;
PRAGMA synchronous=NORMAL;
PRAGMA page_size=1024;
PRAGMA locking_mode=EXCLUSIVE;
PRAGMA journal_mode=TRUNCATE;
INSERT INTO t1 VALUES(1, 2);
} {exclusive truncate} 0 -1
2 {
BEGIN IMMEDIATE;
SELECT * FROM t1;
COMMIT;
} {1 2} 0 -1
3 {
BEGIN;
SELECT * FROM t1;
COMMIT;
} {1 2} 0 -1
4 { PRAGMA journal_mode = WAL } wal -1 -1
5 { INSERT INTO t1 VALUES(3, 4) } {} -1 [wal_file_size 1 1024]
6 { PRAGMA locking_mode = NORMAL } normal -1 [wal_file_size 1 1024]
7 { INSERT INTO t1 VALUES(5, 6); } {} -1 [wal_file_size 2 1024]
8 { PRAGMA journal_mode = TRUNCATE } truncate 0 -1
9 { INSERT INTO t1 VALUES(7, 8) } {} 0 -1
10 { SELECT * FROM t1 } {1 2 3 4 5 6 7 8} 0 -1
}] {
do_execsql_test pager1-7.1.$tn.1 $sql $res
catch { set J -1 ; set J [file size test.db-journal] }
catch { set W -1 ; set W [file size test.db-wal] }
do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws]
}
}
#-------------------------------------------------------------------------
# The following tests, pager1-8.*, test that the special filenames
# ":memory:" and "" open temporary databases.
#
foreach {tn filename} {
1 :memory:
2 ""
} {
do_test pager1-8.$tn.1 {
faultsim_delete_and_reopen
db close
sqlite3 db $filename
execsql {
PRAGMA auto_vacuum = 1;
CREATE TABLE x1(x);
INSERT INTO x1 VALUES('Charles');
INSERT INTO x1 VALUES('James');
INSERT INTO x1 VALUES('Mary');
SELECT * FROM x1;
}
} {Charles James Mary}
do_test pager1-8.$tn.2 {
sqlite3 db2 $filename
catchsql { SELECT * FROM x1 } db2
} {1 {no such table: x1}}
do_execsql_test pager1-8.$tn.3 {
BEGIN;
INSERT INTO x1 VALUES('William');
INSERT INTO x1 VALUES('Anne');
ROLLBACK;
} {}
}
#-------------------------------------------------------------------------
# The next block of tests - pager1-9.* - deal with interactions between
# the pager and the backup API. Test cases:
#
# pager1-9.1.*: Test that a backup completes successfully even if the
# source db is written to during the backup op.
#
# pager1-9.2.*: Test that a backup completes successfully even if the
# source db is written to and then rolled back during a
# backup operation.
#
do_test pager1-9.0.1 {
faultsim_delete_and_reopen
db func a_string a_string
execsql {
PRAGMA cache_size = 10;
BEGIN;
CREATE TABLE ab(a, b, UNIQUE(a, b));
INSERT INTO ab VALUES( a_string(200), a_string(300) );
INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
COMMIT;
}
} {}
do_test pager1-9.0.2 {
sqlite3 db2 test.db2
db2 eval { PRAGMA cache_size = 10 }
sqlite3_backup B db2 main db main
list [B step 10000] [B finish]
} {SQLITE_DONE SQLITE_OK}
do_test pager1-9.0.3 {
db one {SELECT md5sum(a, b) FROM ab}
} [db2 one {SELECT md5sum(a, b) FROM ab}]
do_test pager1-9.1.1 {
execsql { UPDATE ab SET a = a_string(201) }
sqlite3_backup B db2 main db main
B step 30
} {SQLITE_OK}
do_test pager1-9.1.2 {
execsql { UPDATE ab SET b = a_string(301) }
list [B step 10000] [B finish]
} {SQLITE_DONE SQLITE_OK}
do_test pager1-9.1.3 {
db one {SELECT md5sum(a, b) FROM ab}
} [db2 one {SELECT md5sum(a, b) FROM ab}]
do_test pager1-9.1.4 { execsql { SELECT count(*) FROM ab } } {128}
do_test pager1-9.2.1 {
execsql { UPDATE ab SET a = a_string(202) }
sqlite3_backup B db2 main db main
B step 30
} {SQLITE_OK}
do_test pager1-9.2.2 {
execsql {
BEGIN;
UPDATE ab SET b = a_string(301);
ROLLBACK;
}
list [B step 10000] [B finish]
} {SQLITE_DONE SQLITE_OK}
do_test pager1-9.2.3 {
db one {SELECT md5sum(a, b) FROM ab}
} [db2 one {SELECT md5sum(a, b) FROM ab}]
do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128}
db close
db2 close
#-------------------------------------------------------------------------
# Test that regardless of the value returned by xSectorSize(), the
# minimum effective sector-size is 512 and the maximum 65536 bytes.
#
testvfs tv -default 1
foreach sectorsize {
32 64 128 256 512 1024 2048
4096 8192 16384 32768 65536 131072 262144
} {
tv sectorsize $sectorsize
set eff $sectorsize
if {$sectorsize < 512} { set eff 512 }
if {$sectorsize > 65536} { set eff 65536 }
do_test pager1-10.$sectorsize {
faultsim_delete_and_reopen
execsql {
PRAGMA journal_mode = PERSIST;
PRAGMA page_size = 1024;
CREATE TABLE t1(a, b);
}
file size test.db-journal
} [expr $sectorsize > 65536 ? 65536 : $sectorsize]
}
db close
tv delete
testvfs tv -default 1
faultsim_delete_and_reopen
db func a_string a_string
do_execsql_test pager1-11.1 {
PRAGMA cache_size = 10;
BEGIN;
CREATE TABLE zz(top PRIMARY KEY);
INSERT INTO zz VALUES(a_string(222));
INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
COMMIT;
BEGIN;
UPDATE zz SET top = a_string(345);
} {}
proc lockout {method args} { return SQLITE_IOERR }
tv script lockout
tv filter {xWrite xTruncate xSync}
do_catchsql_test pager1-11.2 { COMMIT } {1 {disk I/O error}}
tv script {}
do_test pager1-11.3 {
sqlite3 db2 test.db
execsql {
PRAGMA journal_mode = TRUNCATE;
PRAGMA integrity_check;
} db2
} {truncate ok}
do_test pager1-11.4 {
db2 close
file size test.db-journal
} {0}
do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32}
db close
tv delete
#-------------------------------------------------------------------------
# Test "PRAGMA page_size"
#
foreach pagesize {
512 1024 2048 4096 8192 16384 32768
} {
faultsim_delete_and_reopen
do_test pager1-12.$pagesize.1 {
sqlite3 db2 test.db
execsql "
PRAGMA page_size = $pagesize;
CREATE VIEW v AS SELECT * FROM sqlite_master;
" db2
file size test.db
} $pagesize
do_test pager1-12.$pagesize.2 {
sqlite3 db2 test.db
execsql {
SELECT count(*) FROM v;
PRAGMA main.page_size;
} db2
} [list 1 $pagesize]
do_test pager1-12.$pagesize.3 {
execsql {
SELECT count(*) FROM v;
PRAGMA main.page_size;
}
} [list 1 $pagesize]
db2 close
}
#-------------------------------------------------------------------------
# Test specal "PRAGMA journal_mode=PERSIST" test cases.
#
# pager1-13.1.*: This tests a special case encountered in persistent
# journal mode: If the journal associated with a transaction
# is smaller than the journal file (because a previous
# transaction left a very large non-hot journal file in the
# file-system), then SQLite has to be careful that there is
# not a journal-header left over from a previous transaction
# immediately following the journal content just written.
# If there is, and the process crashes so that the journal
# becomes a hot-journal and must be rolled back by another
# process, there is a danger that the other process may roll
# back the aborted transaction, then continue copying data
# from an older transaction from the remainder of the journal.
# See the syncJournal() function for details.
#
# pager1-13.2.*: Same test as the previous. This time, throw an index into
# the mix to make the integrity-check more likely to catch
# errors.
#
testvfs tv -default 1
tv script xSyncCb
tv filter xSync
proc xSyncCb {method filename args} {
set t [file tail $filename]
if {$t == "test.db"} faultsim_save
return SQLITE_OK
}
faultsim_delete_and_reopen
db func a_string a_string
# The UPDATE statement at the end of this test case creates a really big
# journal. Since the cache-size is only 10 pages, the journal contains
# frequent journal headers.
#
do_execsql_test pager1-13.1.1 {
PRAGMA page_size = 1024;
PRAGMA journal_mode = PERSIST;
PRAGMA cache_size = 10;
BEGIN;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);
INSERT INTO t1 VALUES(NULL, a_string(400));
INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 2 */
INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 4 */
INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 8 */
INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 16 */
INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 32 */
INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 64 */
INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 128 */
COMMIT;
UPDATE t1 SET b = a_string(400);
} {persist}
# Run transactions of increasing sizes. Eventually, one (or more than one)
# of these will write just enough content that one of the old headers created
# by the transaction in the block above lies immediately after the content
# journalled by the current transaction.
#
for {set nUp 1} {$nUp<64} {incr nUp} {
do_execsql_test pager1-13.1.2.$nUp.1 {
UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
} {}
do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok}
# Try to access the snapshot of the file-system.
#
sqlite3 db2 sv_test.db
do_test pager1-13.1.2.$nUp.3 {
execsql { SELECT sum(length(b)) FROM t1 } db2
} [expr {128*400 - ($nUp-1)}]
do_test pager1-13.1.2.$nUp.4 {
execsql { PRAGMA integrity_check } db2
} {ok}
db2 close
}
# Same test as above. But this time with an index on the table.
#
do_execsql_test pager1-13.2.1 {
CREATE INDEX i1 ON t1(b);
UPDATE t1 SET b = a_string(400);
} {}
for {set nUp 1} {$nUp<64} {incr nUp} {
do_execsql_test pager1-13.2.2.$nUp.1 {
UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
} {}
do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok}
sqlite3 db2 sv_test.db
do_test pager1-13.2.2.$nUp.3 {
execsql { SELECT sum(length(b)) FROM t1 } db2
} [expr {128*400 - ($nUp-1)}]
do_test pager1-13.2.2.$nUp.4 {
execsql { PRAGMA integrity_check } db2
} {ok}
db2 close
}
db close
tv delete
#-------------------------------------------------------------------------
# Test specal "PRAGMA journal_mode=OFF" test cases.
#
faultsim_delete_and_reopen
do_execsql_test pager1-14.1.1 {
PRAGMA journal_mode = OFF;
CREATE TABLE t1(a, b);
BEGIN;
INSERT INTO t1 VALUES(1, 2);
COMMIT;
SELECT * FROM t1;
} {off 1 2}
do_catchsql_test pager1-14.1.2 {
BEGIN;
INSERT INTO t1 VALUES(3, 4);
ROLLBACK;
} {0 {}}
do_execsql_test pager1-14.1.3 {
SELECT * FROM t1;
} {1 2 3 4}
do_catchsql_test pager1-14.1.4 {
BEGIN;
INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
} {1 {PRIMARY KEY must be unique}}
do_execsql_test pager1-14.1.5 {
COMMIT;
SELECT * FROM t1;
} {1 2 3 4 2 2 4 4}
#-------------------------------------------------------------------------
# Test opening and closing the pager sub-system with different values
# for the sqlite3_vfs.szOsFile variable.
#
faultsim_delete_and_reopen
do_execsql_test pager1-15.0 {
CREATE TABLE tx(y, z);
INSERT INTO tx VALUES('Ayutthaya', 'Beijing');
INSERT INTO tx VALUES('London', 'Tokyo');
} {}
db close
for {set i 0} {$i<513} {incr i 3} {
testvfs tv -default 1 -szosfile $i
sqlite3 db test.db
do_execsql_test pager1-15.$i.1 {
SELECT * FROM tx;
} {Ayutthaya Beijing London Tokyo}
db close
tv delete
}
#-------------------------------------------------------------------------
# Check that it is not possible to open a database file if the full path
# to the associated journal file will be longer than sqlite3_vfs.mxPathname.
#
testvfs tv -default 1
tv script xOpenCb
tv filter xOpen
proc xOpenCb {method filename} {
set ::file_len [string length $filename]
}
sqlite3 db test.db
db close
tv delete
for {set ii [expr $::file_len-5]} {$ii < [expr $::file_len+20]} {incr ii} {
testvfs tv -default 1 -mxpathname $ii
# The length of the full path to file "test.db-journal" is ($::file_len+8).
# If the configured sqlite3_vfs.mxPathname value greater than or equal to
# this, then the file can be opened. Otherwise, it cannot.
#
if {$ii >= [expr $::file_len+8]} {
set res {0 {}}
} else {
set res {1 {unable to open database file}}
}
do_test pager1-16.1.$ii {
list [catch { sqlite3 db test.db } msg] $msg
} $res
catch {db close}
tv delete
}
#-------------------------------------------------------------------------
# Test "PRAGMA omit_readlock".
#
# pager1-17.$tn.1.*: Test that if a second connection has an open
# read-transaction, it is not usually possible to write
# the database.
#
# pager1-17.$tn.2.*: Test that if the second connection was opened with
# the SQLITE_OPEN_READONLY flag, and
# "PRAGMA omit_readlock = 1" is executed before attaching
# the database and opening a read-transaction on it, it is
# possible to write the db.
#
# pager1-17.$tn.3.*: Test that if the second connection was *not* opened with
# the SQLITE_OPEN_READONLY flag, executing
# "PRAGMA omit_readlock = 1" has no effect.
#
do_multiclient_test tn {
do_test pager1-17.$tn.1.1 {
sql1 {
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 2);
}
sql2 {
BEGIN;
SELECT * FROM t1;
}
} {1 2}
do_test pager1-17.$tn.1.2 {
csql1 { INSERT INTO t1 VALUES(3, 4) }
} {1 {database is locked}}
do_test pager1-17.$tn.1.3 {
sql2 { COMMIT }
sql1 { INSERT INTO t1 VALUES(3, 4) }
} {}
do_test pager1-17.$tn.2.1 {
code2 {
db2 close
sqlite3 db2 :memory: -readonly 1
}
sql2 {
PRAGMA omit_readlock = 1;
ATTACH 'test.db' AS two;
BEGIN;
SELECT * FROM t1;
}
} {1 2 3 4}
do_test pager1-17.$tn.2.2 { sql1 "INSERT INTO t1 VALUES(5, 6)" } {}
do_test pager1-17.$tn.2.3 { sql2 "SELECT * FROM t1" } {1 2 3 4}
do_test pager1-17.$tn.2.4 { sql2 "COMMIT ; SELECT * FROM t1" } {1 2 3 4 5 6}
do_test pager1-17.$tn.3.1 {
code2 {
db2 close
sqlite3 db2 :memory:
}
sql2 {
PRAGMA omit_readlock = 1;
ATTACH 'test.db' AS two;
BEGIN;
SELECT * FROM t1;
}
} {1 2 3 4 5 6}
do_test pager1-17.$tn.3.2 {
csql1 { INSERT INTO t1 VALUES(3, 4) }
} {1 {database is locked}}
do_test pager1-17.$tn.3.3 { sql2 COMMIT } {}
}
#-------------------------------------------------------------------------
# Test the pagers response to the b-tree layer requesting illegal page
# numbers:
#
# + The locking page,
# + Page 0,
# + A page with a page number greater than (2^31-1).
#
do_test pager1-18.1 {
faultsim_delete_and_reopen
db func a_string a_string
execsql {
PRAGMA page_size = 1024;
CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(a_string(500), a_string(200));
INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
}
} {}
do_test pager1-18.2 {
set root [db one "SELECT rootpage FROM sqlite_master"]
set lockingpage [expr (0x10000/1024) + 1]
execsql {
PRAGMA writable_schema = 1;
UPDATE sqlite_master SET rootpage = $lockingpage;
}
sqlite3 db2 test.db
catchsql { SELECT count(*) FROM t1 } db2
} {1 {database disk image is malformed}}
db2 close
do_test pager1-18.3 {
execsql {
CREATE TABLE t2(x);
INSERT INTO t2 VALUES(a_string(5000));
}
set pgno [expr ([file size test.db] / 1024)-2]
hexio_write test.db [expr ($pgno-1)*1024] 00000000
sqlite3 db2 test.db
catchsql { SELECT length(x) FROM t2 } db2
} {1 {database disk image is malformed}}
db2 close
do_test pager1-18.4 {
hexio_write test.db [expr ($pgno-1)*1024] 90000000
sqlite3 db2 test.db
catchsql { SELECT length(x) FROM t2 } db2
} {1 {database disk image is malformed}}
db2 close
do_test pager1-18.5 {
sqlite3 db ""
execsql {
CREATE TABLE t1(a, b);
CREATE TABLE t2(a, b);
PRAGMA writable_schema = 1;
UPDATE sqlite_master SET rootpage=5 WHERE tbl_name = 't1';
PRAGMA writable_schema = 0;
ALTER TABLE t1 RENAME TO x1;
}
catchsql { SELECT * FROM x1 }
} {}
db close
finish_test