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