/trunk/test/incrvacuum2.test |
@@ -0,0 +1,211 @@ |
# 2007 May 04 |
# |
# 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. |
# |
# $Id: incrvacuum2.test,v 1.6 2009/07/25 13:42:50 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 |
} |
|
set testprefix incrvacuum2 |
|
# Create a database in incremental vacuum mode that has many |
# pages on the freelist. |
# |
do_test incrvacuum2-1.1 { |
execsql { |
PRAGMA page_size=1024; |
PRAGMA auto_vacuum=incremental; |
CREATE TABLE t1(x); |
INSERT INTO t1 VALUES(zeroblob(30000)); |
DELETE FROM t1; |
} |
file size test.db |
} {32768} |
|
# Vacuum off a single page. |
# |
do_test incrvacuum2-1.2 { |
execsql { |
PRAGMA incremental_vacuum(1); |
} |
file size test.db |
} {31744} |
|
# Vacuum off five pages |
# |
do_test incrvacuum2-1.3 { |
execsql { |
PRAGMA incremental_vacuum(5); |
} |
file size test.db |
} {26624} |
|
# Vacuum off all the rest |
# |
do_test incrvacuum2-1.4 { |
execsql { |
PRAGMA incremental_vacuum(1000); |
} |
file size test.db |
} {3072} |
|
# Make sure incremental vacuum works on attached databases. |
# |
ifcapable attach { |
do_test incrvacuum2-2.1 { |
file delete -force test2.db test2.db-journal |
execsql { |
ATTACH DATABASE 'test2.db' AS aux; |
PRAGMA aux.auto_vacuum=incremental; |
CREATE TABLE aux.t2(x); |
INSERT INTO t2 VALUES(zeroblob(30000)); |
INSERT INTO t1 SELECT * FROM t2; |
DELETE FROM t2; |
DELETE FROM t1; |
} |
list [file size test.db] [file size test2.db] |
} {32768 32768} |
do_test incrvacuum2-2.2 { |
execsql { |
PRAGMA aux.incremental_vacuum(1) |
} |
list [file size test.db] [file size test2.db] |
} {32768 31744} |
do_test incrvacuum2-2.3 { |
execsql { |
PRAGMA aux.incremental_vacuum(5) |
} |
list [file size test.db] [file size test2.db] |
} {32768 26624} |
do_test incrvacuum2-2.4 { |
execsql { |
PRAGMA main.incremental_vacuum(5) |
} |
list [file size test.db] [file size test2.db] |
} {27648 26624} |
do_test incrvacuum2-2.5 { |
execsql { |
PRAGMA aux.incremental_vacuum |
} |
list [file size test.db] [file size test2.db] |
} {27648 3072} |
do_test incrvacuum2-2.6 { |
execsql { |
PRAGMA incremental_vacuum(1) |
} |
list [file size test.db] [file size test2.db] |
} {26624 3072} |
} |
|
do_test incrvacuum2-3.1 { |
execsql { |
PRAGMA auto_vacuum = 'full'; |
BEGIN; |
CREATE TABLE abc(a); |
INSERT INTO abc VALUES(randstr(1500,1500)); |
COMMIT; |
} |
} {} |
do_test incrvacuum2-3.2 { |
execsql { |
BEGIN; |
DELETE FROM abc; |
PRAGMA incremental_vacuum; |
COMMIT; |
} |
} {} |
|
integrity_check incrvacuum2-3.3 |
|
ifcapable wal { |
# At one point, when a specific page was being extracted from the b-tree |
# free-list (e.g. during an incremental-vacuum), all trunk pages that |
# occurred before the specific page in the free-list trunk were being |
# written to the journal or wal file. This is not necessary. Only the |
# extracted page and the page that contains the pointer to it need to |
# be journalled. |
# |
# This problem was fixed by [d03d63d77e] (just before 3.7.6 release). |
# |
# This test case builds a database containing many free pages. Then runs |
# "PRAGMA incremental_vacuum(1)" until the db contains zero free pages. |
# Each "PRAGMA incremental_vacuum(1)" should modify at most 4 pages. The |
# worst case is when a trunk page is removed from the end of the db file. |
# In this case pages written are: |
# |
# 1. The previous trunk page (that contains a pointer to the recycled |
# trunk page), and |
# 2. The leaf page transformed into a trunk page to replace the recycled |
# page, and |
# 3. The trunk page that contained a pointer to the leaf page used |
# in (2), and |
# 4. Page 1. Page 1 is always updated, even in WAL mode, since it contains |
# the "number of free-list pages" field. |
# |
db close |
forcedelete test.db |
sqlite3 db test.db |
|
do_execsql_test 4.1 { |
PRAGMA page_size = 512; |
PRAGMA auto_vacuum = 2; |
CREATE TABLE t1(x); |
INSERT INTO t1 VALUES(randomblob(400)); |
INSERT INTO t1 SELECT * FROM t1; -- 2 |
INSERT INTO t1 SELECT * FROM t1; -- 4 |
INSERT INTO t1 SELECT * FROM t1; -- 8 |
INSERT INTO t1 SELECT * FROM t1; -- 16 |
INSERT INTO t1 SELECT * FROM t1; -- 32 |
INSERT INTO t1 SELECT * FROM t1; -- 128 |
INSERT INTO t1 SELECT * FROM t1; -- 256 |
INSERT INTO t1 SELECT * FROM t1; -- 512 |
INSERT INTO t1 SELECT * FROM t1; -- 1024 |
INSERT INTO t1 SELECT * FROM t1; -- 2048 |
INSERT INTO t1 SELECT * FROM t1; -- 4096 |
INSERT INTO t1 SELECT * FROM t1; -- 8192 |
DELETE FROM t1 WHERE oid>512; |
DELETE FROM t1; |
} |
|
do_test 4.2 { |
execsql { |
PRAGMA journal_mode = WAL; |
PRAGMA incremental_vacuum(1); |
PRAGMA wal_checkpoint; |
} |
file size test.db-wal |
} {1640} |
|
do_test 4.3 { |
db close |
sqlite3 db test.db |
set maxsz 0 |
while {[file size test.db] > [expr 512*3]} { |
execsql { PRAGMA journal_mode = WAL } |
execsql { PRAGMA wal_checkpoint } |
execsql { PRAGMA incremental_vacuum(1) } |
set newsz [file size test.db-wal] |
if {$newsz>$maxsz} {set maxsz $newsz} |
} |
set maxsz |
} {2176} |
} |
|
finish_test |