1 # 2007 May 04 2 # 3 # The author disclaims copyright to this source code. In place of 4 # 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. The 12 # 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # If this build of the library does not support auto-vacuum, omit this 20 # whole file. 21 ifcapable {!autovacuum || !pragma} { 22 finish_test 23 return 24 } 25 26 set testprefix incrvacuum2 27 28 # Create a database in incremental vacuum mode that has many 29 # pages on the freelist. 30 # 31 do_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.db 40 } {32768} 41 42 # Vacuum off a single page. 43 # 44 do_test incrvacuum2-1.2 { 45 execsql { 46 PRAGMA incremental_vacuum(1); 47 } 48 file size test.db 49 } {31744} 50 51 # Vacuum off five pages 52 # 53 do_test incrvacuum2-1.3 { 54 execsql { 55 PRAGMA incremental_vacuum(5); 56 } 57 file size test.db 58 } {26624} 59 60 # Vacuum off all the rest 61 # 62 do_test incrvacuum2-1.4 { 63 execsql { 64 PRAGMA incremental_vacuum(1000); 65 } 66 file size test.db 67 } {3072} 68 69 # Make sure incremental vacuum works on attached databases. 70 # 71 ifcapable attach { 72 do_test incrvacuum2-2.1 { 73 file delete -force test2.db test2.db-journal 74 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_vacuum 106 } 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 117 do_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 } {} 126 do_test incrvacuum2-3.2 { 127 execsql { 128 BEGIN; 129 DELETE FROM abc; 130 PRAGMA incremental_vacuum; 131 COMMIT; 132 } 133 } {} 134 135 integrity_check incrvacuum2-3.3 136 137 ifcapable wal { 138 # At one point, when a specific page was being extracted from the b-tree 139 # free-list (e.g. during an incremental-vacuum), all trunk pages that 140 # occurred before the specific page in the free-list trunk were being 141 # 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 to 143 # 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 runs 148 # "PRAGMA incremental_vacuum(1)" until the db contains zero free pages. 149 # Each "PRAGMA incremental_vacuum(1)" should modify at most 4 pages. The 150 # 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 recycled 154 # trunk page), and 155 # 2. The leaf page transformed into a trunk page to replace the recycled 156 # page, and 157 # 3. The trunk page that contained a pointer to the leaf page used 158 # in (2), and 159 # 4. Page 1. Page 1 is always updated, even in WAL mode, since it contains 160 # the "number of free-list pages" field. 161 # 162 db close 163 forcedelete test.db 164 sqlite3 db test.db 165 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; -- 2 172 INSERT INTO t1 SELECT * FROM t1; -- 4 173 INSERT INTO t1 SELECT * FROM t1; -- 8 174 INSERT INTO t1 SELECT * FROM t1; -- 16 175 INSERT INTO t1 SELECT * FROM t1; -- 32 176 INSERT INTO t1 SELECT * FROM t1; -- 128 177 INSERT INTO t1 SELECT * FROM t1; -- 256 178 INSERT INTO t1 SELECT * FROM t1; -- 512 179 INSERT INTO t1 SELECT * FROM t1; -- 1024 180 INSERT INTO t1 SELECT * FROM t1; -- 2048 181 INSERT INTO t1 SELECT * FROM t1; -- 4096 182 INSERT INTO t1 SELECT * FROM t1; -- 8192 183 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 PRAGMA wal_checkpoint; 192 } 193 file size test.db-wal 194 } {1640} 195 196 do_test 4.3 { 197 db close 198 sqlite3 db test.db 199 set maxsz 0 200 while {[file size test.db] > [expr 512*3]} { 201 execsql { PRAGMA journal_mode = WAL } 202 execsql { PRAGMA wal_checkpoint } 203 execsql { PRAGMA incremental_vacuum(1) } 204 set newsz [file size test.db-wal] 205 if {$newsz>$maxsz} {set maxsz $newsz} 206 } 207 set maxsz 208 } {2176} 209 } 210 211 finish_test 212
