incrvacuum.test

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

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 {
  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 {
  execsql {
    PRAGMA incremental_vacuum(0);
  }
  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

do_test incrvacuum-11.1 {
  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