1 files changed, 699 insertions, 0 deletions

diff --git a/libraries/sqlite/unix/sqlite-3.5.1/test/incrvacuum.test b/libraries/sqlite/unix/sqlite-3.5.1/test/incrvacuum.test
new file mode 100644
index 0000000..e9c5db9
--- /dev/null
+++ b/libraries/sqlite/unix/sqlite-3.5.1/test/incrvacuum.test
@@ -0,0 +1,699 @@
+# 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.14 2007/09/01 10:01:13 danielk1977 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 {
+  expr {[file size test.db] > 0}
+} {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');
+}
+]
+
+# 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]
+
+file delete -force 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
+  file delete -force 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 {
+breakpoint
+  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}
+
+#----------------------------------------------------------------
+# 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
+file delete -force test.db test.db-journal
+sqlite3 db test.db
+
+ifcapable default_autovacuum {
+  do_test incrvacuum-11.1-av-dflt-on {
+    execsql {
+      PRAGMA auto_vacuum;
+    }
+  } {1}
+} 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
+file delete -force 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 }
+} {1}
+
+do_test incrvacuum-12.3 {
+  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
+file delete -force test.db test.db-journal
+sqlite3 db test.db  ;  set ::DB [sqlite3_connection_pointer db]
+sqlite3 db2 test.db
+
+do_test incrvacuum-13.1 {
+  expr {[file size test.db]>0}
+} {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}
+
+db2 close
+finish_test