incrvacuum.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

# 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.12 2007/06/25 08:16:58 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
}