# 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} finish_test