diff options
Diffstat (limited to 'test/incrvacuum.test')
| -rw-r--r-- | test/incrvacuum.test | 786 |
1 files changed, 786 insertions, 0 deletions
diff --git a/test/incrvacuum.test b/test/incrvacuum.test new file mode 100644 index 0000000..91f5c8e --- /dev/null +++ b/test/incrvacuum.test @@ -0,0 +1,786 @@ +# 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 encrypted or 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} + +finish_test |