# 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.20 2008/09/10 10:57:28 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 {

  # 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]

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;