diff options
Diffstat (limited to 'test/incrvacuum2.test')
| -rw-r--r-- | test/incrvacuum2.test | 213 |
1 files changed, 213 insertions, 0 deletions
diff --git a/test/incrvacuum2.test b/test/incrvacuum2.test new file mode 100644 index 0000000..6ec66c9 --- /dev/null +++ b/test/incrvacuum2.test @@ -0,0 +1,213 @@ +# 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 { + forcedelete 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 + +if {[wal_is_capable]} { + # 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); + } + } {wal} + do_test 4.2.1 { + execsql { PRAGMA wal_checkpoint } + file size test.db-wal + } [expr {32+2*(512+24)}] + + 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 + } [expr {32+3*(512+24)}] +} + +finish_test |