# 2007 April 26
#
# 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 incremental vacuum feature.
#
# Note: There are also some tests for incremental vacuum and IO
# errors in incrvacuum_ioerr.test.
#
# $Id: incrvacuum.test,v 1.23 2009/02/18 20:31:18 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum || !pragma} {
finish_test
return
}
#---------------------------------------------------------------------
# Test the pragma on an empty database.
#
do_test incrvacuum-1.1 {
execsql {
pragma auto_vacuum;
}
} $sqlite_options(default_autovacuum)
do_test incrvacuum-1.2.0 {
# File size is sometimes 1 instead of 0 due to the hack we put in
# to work around ticket #3260. Search for comments on #3260 in
# os_unix.c.
expr {[file size test.db] > 1}
} {0}
do_test incrvacuum-1.2 {
# This command will create the database.
execsql {
pragma auto_vacuum = 'full';
pragma auto_vacuum;
}
} {1}
do_test incrvacuum-1.2.1 {
expr {[file size test.db] > 0}
} {1}
do_test incrvacuum-1.3 {
execsql {
pragma auto_vacuum = 'incremental';
pragma auto_vacuum;
}
} {2}
do_test incrvacuum-1.4 {
# In this case the invalid value is ignored and the auto_vacuum
# setting remains unchanged.
execsql {
pragma auto_vacuum = 'invalid';
pragma auto_vacuum;
}
} {2}
do_test incrvacuum-1.5 {
execsql {
pragma auto_vacuum = 1;
pragma auto_vacuum;
}
} {1}
do_test incrvacuum-1.6 {
execsql {
pragma auto_vacuum = '2';
pragma auto_vacuum;
}
} {2}
do_test incrvacuum-1.7 {
# Invalid value. auto_vacuum setting remains unchanged.
execsql {
pragma auto_vacuum = 5;
pragma auto_vacuum;
}
} {2}
#---------------------------------------------------------------------
# Test the pragma on a non-empty database. It is possible to toggle
# the connection between "full" and "incremental" mode, but not to
# change from either of these to "none", or from "none" to "full" or
# "incremental".
#
do_test incrvacuum-2.1 {
execsql {
pragma auto_vacuum = 1;
CREATE TABLE abc(a, b, c);
}
} {}
do_test incrvacuum-2.2 {
execsql {
pragma auto_vacuum = 'none';
pragma auto_vacuum;
}
} {1}
do_test incrvacuum-2.2.1 {
db close
sqlite3 db test.db
execsql {
pragma auto_vacuum;
}
} {1}
do_test incrvacuum-2.3 {
execsql {
pragma auto_vacuum = 'incremental';
pragma auto_vacuum;
}
} {2}
do_test incrvacuum-2.4 {
execsql {
pragma auto_vacuum = 'full';
pragma auto_vacuum;
}
} {1}
#---------------------------------------------------------------------
# Test that when the auto_vacuum mode is "incremental", the database
# does not shrink when pages are removed from it. But it does if
# the mode is set to "full".
#
do_test incrvacuum-3.1 {
execsql {
pragma auto_vacuum;
}
} {1}
do_test incrvacuum-3.2 {
set ::str [string repeat 1234567890 110]
execsql {
PRAGMA auto_vacuum = 2;
BEGIN;
CREATE TABLE tbl2(str);
INSERT INTO tbl2 VALUES($::str);
COMMIT;
}
# 5 pages:
#
# 1 -> database header
# 2 -> first back-pointer page
# 3 -> table abc
# 4 -> table tbl2
# 5 -> table tbl2 overflow page.
#
expr {[file size test.db] / 1024}
} {5}
do_test incrvacuum-3.3 {
execsql {
DROP TABLE abc;
DELETE FROM tbl2;
}
expr {[file size test.db] / 1024}
} {5}
do_test incrvacuum-3.4 {
execsql {
PRAGMA auto_vacuum = 1;
INSERT INTO tbl2 VALUES('hello world');
}
expr {[file size test.db] / 1024}
} {3}
#---------------------------------------------------------------------
# Try to run a very simple incremental vacuum. Also verify that
# PRAGMA incremental_vacuum is a harmless no-op against a database that
# does not support auto-vacuum.
#
do_test incrvacuum-4.1 {
set ::str [string repeat 1234567890 110]
execsql {
PRAGMA auto_vacuum = 2;
INSERT INTO tbl2 VALUES($::str);
CREATE TABLE tbl1(a, b, c);
}
expr {[file size test.db] / 1024}
} {5}
do_test incrvacuum-4.2 {
execsql {
DELETE FROM tbl2;
DROP TABLE tbl1;
}
expr {[file size test.db] / 1024}
} {5}
do_test incrvacuum-4.3 {
set ::nStep 0
db eval {pragma incremental_vacuum(10)} {
incr ::nStep
}
list [expr {[file size test.db] / 1024}] $::nStep
} {3 2}
#---------------------------------------------------------------------
# The following tests - incrvacuum-5.* - test incremental vacuum
# from within a transaction.
#
do_test incrvacuum-5.1.1 {
expr {[file size test.db] / 1024}
} {3}
do_test incrvacuum-5.1.2 {
execsql {
BEGIN;
DROP TABLE tbl2;
PRAGMA incremental_vacuum;
COMMIT;
}
expr {[file size test.db] / 1024}
} {1}
do_test incrvacuum-5.2.1 {
set ::str [string repeat abcdefghij 110]
execsql {
BEGIN;
CREATE TABLE tbl1(a);
INSERT INTO tbl1 VALUES($::str);
PRAGMA incremental_vacuum; -- this is a no-op.
COMMIT;
}
expr {[file size test.db] / 1024}
} {4}
do_test incrvacuum-5.2.2 {
set ::str [string repeat abcdefghij 110]
execsql {
BEGIN;
INSERT INTO tbl1 VALUES($::str);
INSERT INTO tbl1 SELECT * FROM tbl1;
DELETE FROM tbl1 WHERE oid%2; -- Put 2 overflow pages on free-list.
COMMIT;
}
expr {[file size test.db] / 1024}
} {7}
do_test incrvacuum-5.2.3 {
execsql {
BEGIN;
PRAGMA incremental_vacuum; -- Vacuum up the two pages.
CREATE TABLE tbl2(b); -- Use one free page as a table root.
INSERT INTO tbl2 VALUES('a nice string');
COMMIT;
}
expr {[file size test.db] / 1024}
} {6}
do_test incrvacuum-5.2.4 {
execsql {
SELECT * FROM tbl2;
}
} {{a nice string}}
do_test incrvacuum-5.2.5 {
execsql {
DROP TABLE tbl1;
DROP TABLE tbl2;
PRAGMA incremental_vacuum;
}
expr {[file size test.db] / 1024}
} {1}
# Test cases incrvacuum-5.3.* use the following list as input data.
# Two new databases are opened, one with incremental vacuum enabled,
# the other with no auto-vacuum completely disabled. After executing
# each element of the following list on both databases, test that
# the integrity-check passes and the contents of each are identical.
#
set TestScriptList [list {
BEGIN;
CREATE TABLE t1(a, b);
CREATE TABLE t2(a, b);
CREATE INDEX t1_i ON t1(a);
CREATE INDEX t2_i ON t2(a);
} {
INSERT INTO t1 VALUES($::str1, $::str2);
INSERT INTO t1 VALUES($::str1||$::str2, $::str2||$::str1);
INSERT INTO t2 SELECT b, a FROM t1;
INSERT INTO t2 SELECT a, b FROM t1;
INSERT INTO t1 SELECT b, a FROM t2;
UPDATE t2 SET b = '';
PRAGMA incremental_vacuum;
} {
UPDATE t2 SET b = (SELECT b FROM t1 WHERE t1.oid = t2.oid);
PRAGMA incremental_vacuum;
} {
CREATE TABLE t3(a, b);
INSERT INTO t3 SELECT * FROM t2;
DROP TABLE t2;
PRAGMA incremental_vacuum;
} {
CREATE INDEX t3_i ON t3(a);
COMMIT;
} {
BEGIN;
DROP INDEX t3_i;
PRAGMA incremental_vacuum;
INSERT INTO t3 VALUES('hello', 'world');
ROLLBACK;
} {
INSERT INTO t3 VALUES('hello', 'world');
}
]
# If this build omits subqueries, step 2 in the above list will not
# work. Replace it with "" in this case.
#
ifcapable !subquery { lset TestScriptList 2 "" }
# Compare the contents of databases $A and $B.
#
proc compare_dbs {A B tname} {
set tbl_list [execsql {
SELECT tbl_name FROM sqlite_master WHERE type = 'table'
} $A]
do_test ${tname}.1 [subst {
execsql {
SELECT tbl_name FROM sqlite_master WHERE type = 'table'
} $B
}] $tbl_list
set tn 1
foreach tbl $tbl_list {
set control [execsql "SELECT * FROM $tbl" $A]
do_test ${tname}.[incr tn] [subst {
execsql "SELECT * FROM $tbl" $B
}] $control
}
}
set ::str1 [string repeat abcdefghij 130]
set ::str2 [string repeat 1234567890 105]
forcedelete test1.db test1.db-journal test2.db test2.db-journal
sqlite3 db1 test1.db
sqlite3 db2 test2.db
execsql { PRAGMA auto_vacuum = 'none' } db1
execsql { PRAGMA auto_vacuum = 'incremental' } db2
set tn 1
foreach sql $::TestScriptList {
execsql $sql db1
execsql $sql db2
compare_dbs db1 db2 incrvacuum-5.3.${tn}
do_test incrvacuum-5.3.${tn}.integrity1 {
execsql { PRAGMA integrity_check; } db1
} {ok}
do_test incrvacuum-5.3.${tn}.integrity2 {
execsql { PRAGMA integrity_check; } db2
} {ok}
incr tn
}
db1 close
db2 close
#
# End of test cases 5.3.*
#---------------------------------------------------------------------
# The following tests - incrvacuum-6.* - test running incremental
# vacuum while another statement (a read) is being executed.
#
for {set jj 0} {$jj < 10} {incr jj} {
# Build some test data. Two tables are created in an empty
# database. tbl1 data is a contiguous block starting at page 5 (pages
# 3 and 4 are the table roots). tbl2 is a contiguous block starting
# right after tbl1.
#
# Then drop tbl1 so that when an incr vacuum is run the pages
# of tbl2 have to be moved to fill the gap.
#
do_test incrvacuum-6.${jj}.1 {
execsql {
DROP TABLE IF EXISTS tbl1;
DROP TABLE IF EXISTS tbl2;
PRAGMA incremental_vacuum;
CREATE TABLE tbl1(a, b);
CREATE TABLE tbl2(a, b);
BEGIN;
}
for {set ii 0} {$ii < 1000} {incr ii} {
db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}
}
execsql {
INSERT INTO tbl2 SELECT * FROM tbl1;
COMMIT;
DROP TABLE tbl1;
}
expr {[file size test.db] / 1024}
} {36}
# Run a linear scan query on tbl2. After reading ($jj*100) rows,
# run the incremental vacuum to shrink the database.
#
do_test incrvacuum-6.${jj}.2 {
set ::nRow 0
db eval {SELECT a FROM tbl2} {} {
if {$a == [expr $jj*100]} {
db eval {PRAGMA incremental_vacuum}
}
incr ::nRow
}
list [expr {[file size test.db] / 1024}] $nRow
} {19 1000}
}
#---------------------------------------------------------------------
# This test - incrvacuum-7.* - is to check that the database can be
# written in the middle of an incremental vacuum.
#
set ::iWrite 1
while 1 {
do_test incrvacuum-7.${::iWrite}.1 {
execsql {
DROP TABLE IF EXISTS tbl1;
DROP TABLE IF EXISTS tbl2;
PRAGMA incremental_vacuum;
CREATE TABLE tbl1(a, b);
CREATE TABLE tbl2(a, b);
BEGIN;
}
for {set ii 0} {$ii < 1000} {incr ii} {
db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}
}
execsql {
INSERT INTO tbl2 SELECT * FROM tbl1;
COMMIT;
DROP TABLE tbl1;
}
expr {[file size test.db] / 1024}
} {36}
do_test incrvacuum-7.${::iWrite}.2 {
set ::nRow 0
db eval {PRAGMA incremental_vacuum} {
incr ::nRow
if {$::nRow == $::iWrite} {
db eval {
CREATE TABLE tbl1(a, b);
INSERT INTO tbl1 VALUES('hello', 'world');
}
}
}
list [expr {[file size test.db] / 1024}]
} {20}
do_test incrvacuum-7.${::iWrite}.3 {
execsql {
SELECT * FROM tbl1;
}
} {hello world}
if {$::nRow == $::iWrite} break
incr ::iWrite
}
#---------------------------------------------------------------------
# This test - incrvacuum-8.* - is to check that nothing goes wrong
# with an incremental-vacuum if it is the first statement executed
# after an existing database is opened.
#
# At one point, this would always return SQLITE_SCHEMA (which
# causes an infinite loop in tclsqlite.c if using the Tcl interface).
#
do_test incrvacuum-8.1 {
db close
sqlite3 db test.db
execsql {
PRAGMA incremental_vacuum(50);
}
} {}
#---------------------------------------------------------------------
# At one point this test case was causing an assert() to fail.
#
do_test incrvacuum-9.1 {
db close
forcedelete test.db test.db-journal
sqlite3 db test.db
execsql {
PRAGMA auto_vacuum = 'incremental';
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(a, b, c);
INSERT INTO t2 VALUES(randstr(500,500),randstr(500,500),randstr(500,500));
INSERT INTO t1 VALUES(1, 2, 3);
INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
}
} {}
do_test incrvacuum-9.2 {
execsql {
PRAGMA synchronous = 'OFF';
BEGIN;
UPDATE t1 SET a = a, b = b, c = c;
DROP TABLE t2;
PRAGMA incremental_vacuum(10);
ROLLBACK;
}
} {}
do_test incrvacuum-9.3 {
execsql {
PRAGMA cache_size = 10;
BEGIN;
UPDATE t1 SET a = a, b = b, c = c;
DROP TABLE t2;
PRAGMA incremental_vacuum(10);
ROLLBACK;
}
} {}
#---------------------------------------------------------------------
# Test that the parameter to the incremental_vacuum pragma works. That
# is, if the user executes "PRAGMA incremental_vacuum(N)", at most
# N pages are vacuumed.
#
do_test incrvacuum-10.1 {
execsql {
DROP TABLE t1;
DROP TABLE t2;
}
expr [file size test.db] / 1024
} {29}
do_test incrvacuum-10.2 {
execsql {
PRAGMA incremental_vacuum(1);
}
expr [file size test.db] / 1024
} {28}
do_test incrvacuum-10.3 {
execsql {
PRAGMA incremental_vacuum(5);
}
expr [file size test.db] / 1024
} {23}
do_test incrvacuum-10.4 {
execsql {
PRAGMA incremental_vacuum('1');
}
expr [file size test.db] / 1024
} {22}
do_test incrvacuum-10.5 {
execsql {
PRAGMA incremental_vacuum("+3");
}
expr [file size test.db] / 1024
} {19}
do_test incrvacuum-10.6 {
execsql {
PRAGMA incremental_vacuum = 1;
}
expr [file size test.db] / 1024
} {18}
do_test incrvacuum-10.7 {
# Use a really big number as an argument to incremetal_vacuum. Should
# be interpreted as "free all possible space".
execsql {
PRAGMA incremental_vacuum(2147483649);
}
expr [file size test.db] / 1024
} {1}
do_test incrvacuum-10.8 {
execsql {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(hex(randomblob(1000)));
DROP TABLE t1;
}
# A negative number means free all possible space.
execsql {
PRAGMA incremental_vacuum=-1;
}
expr [file size test.db] / 1024
} {1}
#----------------------------------------------------------------
# Test that if we set the auto_vacuum mode to 'incremental', then
# create a database, thereafter that database defaults to incremental
# vacuum mode.
#
db close
forcedelete test.db test.db-journal
sqlite3 db test.db
ifcapable default_autovacuum {
do_test incrvacuum-11.1-av-dflt-on {
execsql {
PRAGMA auto_vacuum;
}
} $AUTOVACUUM
} else {
do_test incrvacuum-11.1-av-dflt-off {
execsql {
PRAGMA auto_vacuum;
}
} {0}
}
do_test incrvacuum-11.2 {
execsql {
PRAGMA auto_vacuum = incremental;
}
} {}
do_test incrvacuum-11.3 {
execsql {
PRAGMA auto_vacuum;
}
} {2}
do_test incrvacuum-11.4 {
# The database has now been created.
expr {[file size test.db]>0}
} {1}
do_test incrvacuum-11.5 {
# Close and reopen the connection.
db close
sqlite3 db test.db
# Test we are still in incremental vacuum mode.
execsql { PRAGMA auto_vacuum; }
} {2}
do_test incrvacuum-11.6 {
execsql {
PRAGMA auto_vacuum = 'full';
PRAGMA auto_vacuum;
}
} {1}
do_test incrvacuum-11.7 {
# Close and reopen the connection.
db close
sqlite3 db test.db
# Test we are still in "full" auto-vacuum mode.
execsql { PRAGMA auto_vacuum; }
} {1}
#----------------------------------------------------------------------
# Special case: What happens if the database is locked when a "PRAGMA
# auto_vacuum = XXX" statement is executed.
#
db close
forcedelete test.db test.db-journal
sqlite3 db test.db
do_test incrvacuum-12.1 {
execsql {
PRAGMA auto_vacuum = 1;
}
expr {[file size test.db]>0}
} {1}
# Try to change the auto-vacuum from "full" to "incremental" while the
# database is locked. Nothing should change.
#
do_test incrvacuum-12.2 {
sqlite3 db2 test.db
execsql { BEGIN EXCLUSIVE; } db2
catchsql { PRAGMA auto_vacuum = 2; }
} {1 {database is locked}}
do_test incrvacuum-12.3 {
execsql { ROLLBACK; } db2
execsql { PRAGMA auto_vacuum }
} {2} ;# Still 2 because PRAGMA auto_vacuum setting held in case of vacuum
do_test incrvacuum-12.4 {
db close
sqlite3 db test.db
execsql { PRAGMA auto_vacuum }
} {1} ;# Revert to 1 because the database file did not change
do_test incrvacuum-12.5 {
execsql { SELECT * FROM sqlite_master }
execsql { PRAGMA auto_vacuum }
} {1}
#----------------------------------------------------------------------
# Special case #2: What if one process prepares a "PRAGMA auto_vacuum = XXX"
# statement when the database is empty, but doesn't execute it until
# after some other process has created the database.
#
db2 close
db close
forcedelete test.db test.db-journal
sqlite3 db test.db ; set ::DB [sqlite3_connection_pointer db]
sqlite3 db2 test.db
do_test incrvacuum-13.1 {
# File size is sometimes 1 instead of 0 due to the hack we put in
# to work around ticket #3260. Search for comments on #3260 in
# os_unix.c.
expr {[file size test.db]>1}
} {0}
do_test incrvacuum-13.2 {
set ::STMT [sqlite3_prepare $::DB {PRAGMA auto_vacuum = 2} -1 DUMMY]
execsql {
PRAGMA auto_vacuum = none;
PRAGMA default_cache_size = 1024;
PRAGMA auto_vacuum;
} db2
} {0}
do_test incrvacuum-13.3 {
expr {[file size test.db]>0}
} {1}
do_test incrvacuum-13.4 {
set rc [sqlite3_step $::STMT]
list $rc [sqlite3_finalize $::STMT]
} {SQLITE_DONE SQLITE_OK}
do_test incrvacuum-13.5 {
execsql {
PRAGMA auto_vacuum;
}
} {0}
# Verify that the incremental_vacuum pragma fails gracefully if it
# is used against an invalid database file.
#
if {[permutation] == ""} {
do_test incrvacuum-14.1 {
set out [open invalid.db w]
puts $out "This is not an SQLite database file"
close $out
sqlite3 db3 invalid.db
catchsql {
PRAGMA incremental_vacuum(10);
} db3
} {1 {file is not a database}}
db3 close
}
do_test incrvacuum-15.1 {
db close
db2 close
forcedelete test.db
sqlite3 db test.db
set str [string repeat "abcdefghij" 500]
execsql {
PRAGMA cache_size = 10;
PRAGMA auto_vacuum = incremental;
CREATE TABLE t1(x, y);
INSERT INTO t1 VALUES('a', $str);
INSERT INTO t1 VALUES('b', $str);
INSERT INTO t1 VALUES('c', $str);
INSERT INTO t1 VALUES('d', $str);
INSERT INTO t1 VALUES('e', $str);
INSERT INTO t1 VALUES('f', $str);
INSERT INTO t1 VALUES('g', $str);
INSERT INTO t1 VALUES('h', $str);
INSERT INTO t1 VALUES('i', $str);
INSERT INTO t1 VALUES('j', $str);
INSERT INTO t1 VALUES('j', $str);
CREATE TABLE t2(x PRIMARY KEY, y);
INSERT INTO t2 VALUES('a', $str);
INSERT INTO t2 VALUES('b', $str);
INSERT INTO t2 VALUES('c', $str);
INSERT INTO t2 VALUES('d', $str);
BEGIN;
DELETE FROM t2;
PRAGMA incremental_vacuum;
}
catchsql {INSERT INTO t2 SELECT * FROM t1}
execsql {
COMMIT;
PRAGMA integrity_check;
}
} {ok}
#-------------------------------------------------------------------------
# At one point it was unsafe to truncate a db file on windows while there
# were outstanding xFetch() references. This test case attempts to hit
# that case.
#
ifcapable mmap {
reset_db
do_execsql_test incrvacuum-16.0 {
PRAGMA auto_vacuum = 2;
CREATE TABLE t3(a);
INSERT INTO t3 VALUES(1), (2), (3), (4);
CREATE TABLE t2(x);
INSERT INTO t2 VALUES( randomblob(1000) );
INSERT INTO t2 VALUES( randomblob(1000) );
INSERT INTO t2 VALUES( randomblob(1000) );
INSERT INTO t2 VALUES( randomblob(1000) );
INSERT INTO t2 VALUES( randomblob(1000) );
INSERT INTO t2 VALUES( randomblob(1000) );
} {}
# Reopen db to ensure the page-cache is empty.
#
db close
sqlite3 db test.db
# Open db in mmap-mode. Open a transaction, delete some data, then run
# incremental-vacuum. Do not commit the transaction.
#
do_execsql_test incrvacuum-16.1 {
PRAGMA mmap_size = 1000000;
BEGIN;
DELETE FROM t2;
PRAGMA incremental_vacuum = 1000;
} {1000000}
# Scan through table t3 (which is all clean pages - so mmap is used). Then,
# midway through, commit the transaction. This causes the db to be truncated
# while there are outstanding xFetch pages.
#
do_test incrvacuum-16.2 {
set res [list]
db eval { SELECT a FROM t3 } {
if {$a==3} { db eval COMMIT }
lappend res $a
}
set res
} {1 2 3 4}
}
finish_test