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