AryaWu/sqlite
0
1# 2007 May 042#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library. The12# focus of this file is testing the incremental vacuum feature.13#14# $Id: incrvacuum2.test,v 1.6 2009/07/25 13:42:50 danielk1977 Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# If this build of the library does not support auto-vacuum, omit this20# whole file.21ifcapable {!autovacuum || !pragma} {22 finish_test23 return24}25 26set testprefix incrvacuum227 28# Create a database in incremental vacuum mode that has many29# pages on the freelist.30#31do_test incrvacuum2-1.1 {32 execsql {33 PRAGMA page_size=1024;34 PRAGMA auto_vacuum=incremental;35 CREATE TABLE t1(x);36 INSERT INTO t1 VALUES(zeroblob(30000));37 DELETE FROM t1;38 }39 file size test.db40} {32768}41 42# Vacuum off a single page.43#44do_test incrvacuum2-1.2 {45 execsql {46 PRAGMA incremental_vacuum(1);47 }48 file size test.db49} {31744}50 51# Vacuum off five pages52#53do_test incrvacuum2-1.3 {54 execsql {55 PRAGMA incremental_vacuum(5);56 }57 file size test.db58} {26624}59 60# Vacuum off all the rest61#62do_test incrvacuum2-1.4 {63 execsql {64 PRAGMA incremental_vacuum(1000);65 }66 file size test.db67} {3072}68 69# Make sure incremental vacuum works on attached databases.70#71ifcapable attach {72 do_test incrvacuum2-2.1 {73 forcedelete test2.db test2.db-journal74 execsql {75 ATTACH DATABASE 'test2.db' AS aux;76 PRAGMA aux.auto_vacuum=incremental;77 CREATE TABLE aux.t2(x);78 INSERT INTO t2 VALUES(zeroblob(30000));79 INSERT INTO t1 SELECT * FROM t2;80 DELETE FROM t2;81 DELETE FROM t1;82 }83 list [file size test.db] [file size test2.db]84 } {32768 32768}85 do_test incrvacuum2-2.2 {86 execsql {87 PRAGMA aux.incremental_vacuum(1)88 }89 list [file size test.db] [file size test2.db]90 } {32768 31744}91 do_test incrvacuum2-2.3 {92 execsql {93 PRAGMA aux.incremental_vacuum(5)94 }95 list [file size test.db] [file size test2.db]96 } {32768 26624}97 do_test incrvacuum2-2.4 {98 execsql {99 PRAGMA main.incremental_vacuum(5)100 }101 list [file size test.db] [file size test2.db]102 } {27648 26624}103 do_test incrvacuum2-2.5 {104 execsql {105 PRAGMA aux.incremental_vacuum106 }107 list [file size test.db] [file size test2.db]108 } {27648 3072}109 do_test incrvacuum2-2.6 {110 execsql {111 PRAGMA incremental_vacuum(1)112 }113 list [file size test.db] [file size test2.db]114 } {26624 3072}115}116 117do_test incrvacuum2-3.1 {118 execsql {119 PRAGMA auto_vacuum = 'full';120 BEGIN;121 CREATE TABLE abc(a);122 INSERT INTO abc VALUES(randstr(1500,1500));123 COMMIT;124 }125} {}126do_test incrvacuum2-3.2 {127 execsql {128 BEGIN;129 DELETE FROM abc;130 PRAGMA incremental_vacuum;131 COMMIT;132 }133} {}134 135integrity_check incrvacuum2-3.3136 137if {[wal_is_capable]} {138 # At one point, when a specific page was being extracted from the b-tree139 # free-list (e.g. during an incremental-vacuum), all trunk pages that140 # occurred before the specific page in the free-list trunk were being141 # written to the journal or wal file. This is not necessary. Only the 142 # extracted page and the page that contains the pointer to it need to143 # be journalled.144 #145 # This problem was fixed by [d03d63d77e] (just before 3.7.6 release).146 #147 # This test case builds a database containing many free pages. Then runs148 # "PRAGMA incremental_vacuum(1)" until the db contains zero free pages.149 # Each "PRAGMA incremental_vacuum(1)" should modify at most 4 pages. The150 # worst case is when a trunk page is removed from the end of the db file.151 # In this case pages written are:152 #153 # 1. The previous trunk page (that contains a pointer to the recycled154 # trunk page), and155 # 2. The leaf page transformed into a trunk page to replace the recycled156 # page, and157 # 3. The trunk page that contained a pointer to the leaf page used 158 # in (2), and159 # 4. Page 1. Page 1 is always updated, even in WAL mode, since it contains160 # the "number of free-list pages" field.161 #162 db close163 forcedelete test.db164 sqlite3 db test.db165 166 do_execsql_test 4.1 {167 PRAGMA page_size = 512;168 PRAGMA auto_vacuum = 2;169 CREATE TABLE t1(x);170 INSERT INTO t1 VALUES(randomblob(400));171 INSERT INTO t1 SELECT * FROM t1; -- 2172 INSERT INTO t1 SELECT * FROM t1; -- 4173 INSERT INTO t1 SELECT * FROM t1; -- 8174 INSERT INTO t1 SELECT * FROM t1; -- 16175 INSERT INTO t1 SELECT * FROM t1; -- 32176 INSERT INTO t1 SELECT * FROM t1; -- 128177 INSERT INTO t1 SELECT * FROM t1; -- 256178 INSERT INTO t1 SELECT * FROM t1; -- 512179 INSERT INTO t1 SELECT * FROM t1; -- 1024180 INSERT INTO t1 SELECT * FROM t1; -- 2048181 INSERT INTO t1 SELECT * FROM t1; -- 4096182 INSERT INTO t1 SELECT * FROM t1; -- 8192183 DELETE FROM t1 WHERE oid>512;184 DELETE FROM t1;185 }186 187 do_test 4.2 {188 execsql { 189 PRAGMA journal_mode = WAL;190 PRAGMA incremental_vacuum(1);191 }192 } {wal}193 do_test 4.2.1 {194 execsql { PRAGMA wal_checkpoint }195 file size test.db-wal196 } [expr {32+2*(512+24)}]197 198 do_test 4.3 {199 db close200 sqlite3 db test.db201 set maxsz 0202 while {[file size test.db] > [expr 512*3]} {203 execsql { PRAGMA journal_mode = WAL }204 execsql { PRAGMA wal_checkpoint }205 execsql { PRAGMA incremental_vacuum(1) }206 set newsz [file size test.db-wal]207 if {$newsz>$maxsz} {set maxsz $newsz}208 }209 set maxsz 210 } [expr {32+3*(512+24)}]211}212 213finish_test214