1# 2010 September 24 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# 12# This file implements tests to verify that the "testable statements" in 13# the lang_vacuum.html document are correct. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19sqlite3_test_control_pending_byte 0x1000000 20 21proc create_db {{sql ""}} { 22 catch { db close } 23 forcedelete test.db 24 sqlite3 db test.db 25 26 db transaction { 27 execsql { PRAGMA page_size = 1024; } 28 execsql $sql 29 execsql { 30 CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); 31 INSERT INTO t1 VALUES(1, randomblob(400)); 32 INSERT INTO t1 SELECT a+1, randomblob(400) FROM t1; 33 INSERT INTO t1 SELECT a+2, randomblob(400) FROM t1; 34 INSERT INTO t1 SELECT a+4, randomblob(400) FROM t1; 35 INSERT INTO t1 SELECT a+8, randomblob(400) FROM t1; 36 INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1; 37 INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1; 38 INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1; 39 40 CREATE TABLE t2(a PRIMARY KEY, b UNIQUE); 41 INSERT INTO t2 SELECT * FROM t1; 42 } 43 } 44 45 return [expr {[file size test.db] / 1024}] 46} 47 48# This proc returns the number of contiguous blocks of pages that make up 49# the table or index named by the only argument. For example, if the table 50# occupies database pages 3, 4, 8 and 9, then this command returns 2 (there 51# are 2 fragments - one consisting of pages 3 and 4, the other of fragments 52# 8 and 9). 53# 54proc fragment_count {name} { 55 execsql { CREATE VIRTUAL TABLE temp.stat USING dbstat } 56 set nFrag 1 57 db eval {SELECT pageno FROM stat WHERE name = 't1' ORDER BY pageno} { 58 if {[info exists prevpageno] && $prevpageno != $pageno-1} { 59 incr nFrag 60 } 61 set prevpageno $pageno 62 } 63 execsql { DROP TABLE temp.stat } 64 set nFrag 65} 66 67 68# EVIDENCE-OF: R-63707-33375 -- syntax diagram vacuum-stmt 69# 70do_execsql_test e_vacuum-0.1 { VACUUM } {} 71 72# EVIDENCE-OF: R-51469-36013 Unless SQLite is running in 73# "auto_vacuum=FULL" mode, when a large amount of data is deleted from 74# the database file it leaves behind empty space, or "free" database 75# pages. 76# 77# EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database 78# reclaims this space and reduces the size of the database file. 79# 80foreach {tn avmode sz} { 81 1 none 7 82 2 full 8 83 3 incremental 8 84} { 85 set nPage [create_db "PRAGMA auto_vacuum = $avmode"] 86 87 do_execsql_test e_vacuum-1.1.$tn.1 { 88 DELETE FROM t1; 89 DELETE FROM t2; 90 } {} 91 92 if {$avmode == "full"} { 93 # This branch tests the "unless ... auto_vacuum=FULL" in the requirement 94 # above. If auto_vacuum is set to FULL, then no empty space is left in 95 # the database file. 96 do_execsql_test e_vacuum-1.1.$tn.2 {PRAGMA freelist_count} 0 97 } else { 98 set freelist [expr {$nPage - $sz}] 99 if {$avmode == "incremental"} { 100 # The page size is 1024 bytes. Therefore, assuming the database contains 101 # somewhere between 207 and 411 pages (it does), there are 2 pointer-map 102 # pages. 103 incr freelist -2 104 } 105 do_execsql_test e_vacuum-1.1.$tn.3 {PRAGMA freelist_count} $freelist 106 do_execsql_test e_vacuum-1.1.$tn.4 {VACUUM} {} 107 } 108 109 do_test e_vacuum-1.1.$tn.5 { expr {[file size test.db] / 1024} } $sz 110} 111 112# EVIDENCE-OF: R-50943-18433 Frequent inserts, updates, and deletes can 113# cause the database file to become fragmented - where data for a single 114# table or index is scattered around the database file. 115# 116# EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and 117# index is largely stored contiguously within the database file. 118# 119# e_vacuum-1.2.1 - Perform many INSERT, UPDATE and DELETE ops on table t1. 120# e_vacuum-1.2.2 - Verify that t1 and its indexes are now quite fragmented. 121# e_vacuum-1.2.3 - Run VACUUM. 122# e_vacuum-1.2.4 - Verify that t1 and its indexes are now much 123# less fragmented. 124# 125ifcapable vtab { 126 create_db 127 register_dbstat_vtab db 128 do_execsql_test e_vacuum-1.2.1 { 129 DELETE FROM t1 WHERE a%2; 130 INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2; 131 UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0; 132 } {} 133 134 do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1 135 do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1 136 do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1 137 138 do_execsql_test e_vacuum-1.2.3 { VACUUM } {} 139 140 # In practice, the tables and indexes each end up stored as two fragments - 141 # one containing the root page and another containing all other pages. 142 # 143 do_test e_vacuum-1.2.4.1 { fragment_count t1 } 2 144 do_test e_vacuum-1.2.4.2 { fragment_count sqlite_autoindex_t1_1 } 2 145 do_test e_vacuum-1.2.4.3 { fragment_count sqlite_autoindex_t1_2 } 2 146} 147 148# EVIDENCE-OF: R-20474-44465 Normally, the database page_size and 149# whether or not the database supports auto_vacuum must be configured 150# before the database file is actually created. 151# 152do_test e_vacuum-1.3.1.1 { 153 create_db "PRAGMA page_size = 1024 ; PRAGMA auto_vacuum = FULL" 154 execsql { PRAGMA page_size ; PRAGMA auto_vacuum } 155} {1024 1} 156do_test e_vacuum-1.3.1.2 { 157 execsql { PRAGMA page_size = 2048 } 158 execsql { PRAGMA auto_vacuum = NONE } 159 execsql { PRAGMA page_size ; PRAGMA auto_vacuum } 160} {1024 1} 161 162# EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode, 163# the page_size and/or auto_vacuum properties of an existing database 164# may be changed by using the page_size and/or pragma auto_vacuum 165# pragmas and then immediately VACUUMing the database. 166# 167do_test e_vacuum-1.3.2.1 { 168 execsql { PRAGMA journal_mode = delete } 169 execsql { PRAGMA page_size = 2048 } 170 execsql { PRAGMA auto_vacuum = NONE } 171 execsql VACUUM 172 execsql { PRAGMA page_size ; PRAGMA auto_vacuum } 173} {2048 0} 174 175# EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the 176# auto_vacuum support property can be changed using VACUUM. 177# 178ifcapable wal { 179do_test e_vacuum-1.3.3.1 { 180 execsql { PRAGMA journal_mode = wal } 181 execsql { PRAGMA page_size ; PRAGMA auto_vacuum } 182} {2048 0} 183do_test e_vacuum-1.3.3.2 { 184 execsql { PRAGMA page_size = 1024 } 185 execsql { PRAGMA auto_vacuum = FULL } 186 execsql VACUUM 187 execsql { PRAGMA page_size ; PRAGMA auto_vacuum } 188} {2048 1} 189} 190 191# EVIDENCE-OF: R-38001-03952 VACUUM only works on the main database. It 192# is not possible to VACUUM an attached database file. 193forcedelete test.db2 194create_db { PRAGMA auto_vacuum = NONE } 195do_execsql_test e_vacuum-2.1.1 { 196 ATTACH 'test.db2' AS aux; 197 PRAGMA aux.page_size = 1024; 198 CREATE TABLE aux.t3 AS SELECT * FROM t1; 199 DELETE FROM t3; 200} {} 201set original_size [file size test.db2] 202 203# Try everything we can think of to get the aux database vacuumed: 204do_execsql_test e_vacuum-2.1.3 { VACUUM } {} 205do_execsql_test e_vacuum-2.1.4 { VACUUM aux } {} 206do_execsql_test e_vacuum-2.1.5 { VACUUM 'test.db2' } {} 207 208# Despite our efforts, space in the aux database has not been reclaimed: 209do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1 210 211# EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of 212# entries in any tables that do not have an explicit INTEGER PRIMARY 213# KEY. 214# 215# Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when 216# a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding 217# an INTEGER PRIMARY KEY column to a table stops this from happening. 218# 219do_execsql_test e_vacuum-3.1.1 { 220 CREATE TABLE t4(x); 221 INSERT INTO t4(x) VALUES('x'); 222 INSERT INTO t4(x) VALUES('y'); 223 INSERT INTO t4(x) VALUES('z'); 224 DELETE FROM t4 WHERE x = 'y'; 225 SELECT rowid, x FROM t4; 226} {1 x 3 z} 227do_execsql_test e_vacuum-3.1.2 { 228 VACUUM; 229 SELECT rowid, x FROM t4; 230} {1 x 2 z} 231 232do_execsql_test e_vacuum-3.1.3 { 233 CREATE TABLE t5(x, y INTEGER PRIMARY KEY); 234 INSERT INTO t5(x) VALUES('x'); 235 INSERT INTO t5(x) VALUES('y'); 236 INSERT INTO t5(x) VALUES('z'); 237 DELETE FROM t5 WHERE x = 'y'; 238 SELECT rowid, x FROM t5; 239} {1 x 3 z} 240do_execsql_test e_vacuum-3.1.4 { 241 VACUUM; 242 SELECT rowid, x FROM t5; 243} {1 x 3 z} 244 245# EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open 246# transaction, or if there are one or more active SQL statements when it 247# is run. 248# 249do_execsql_test e_vacuum-3.2.1.1 { BEGIN } {} 250do_catchsql_test e_vacuum-3.2.1.2 { 251 VACUUM 252} {1 {cannot VACUUM from within a transaction}} 253do_execsql_test e_vacuum-3.2.1.3 { COMMIT } {} 254do_execsql_test e_vacuum-3.2.1.4 { VACUUM } {} 255do_execsql_test e_vacuum-3.2.1.5 { SAVEPOINT x } {} 256do_catchsql_test e_vacuum-3.2.1.6 { 257 VACUUM 258} {1 {cannot VACUUM from within a transaction}} 259do_execsql_test e_vacuum-3.2.1.7 { COMMIT } {} 260do_execsql_test e_vacuum-3.2.1.8 { VACUUM } {} 261 262create_db 263do_test e_vacuum-3.2.2.1 { 264 set res "" 265 db eval { SELECT a FROM t1 } { 266 if {$a == 10} { set res [catchsql VACUUM] } 267 } 268 set res 269} {1 {cannot VACUUM - SQL statements in progress}} 270 271 272# EVIDENCE-OF: R-38735-12540 As of SQLite version 3.1, an alternative to 273# using the VACUUM command to reclaim space after data has been deleted 274# is auto-vacuum mode, enabled using the auto_vacuum pragma. 275# 276do_test e_vacuum-3.3.1 { 277 create_db { PRAGMA auto_vacuum = FULL } 278 execsql { PRAGMA auto_vacuum } 279} {1} 280 281# EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database 282# free pages may be reclaimed after deleting data, causing the file to 283# shrink, without rebuilding the entire database using VACUUM. 284# 285do_test e_vacuum-3.3.2.1 { 286 create_db { PRAGMA auto_vacuum = FULL } 287 execsql { 288 DELETE FROM t1; 289 DELETE FROM t2; 290 } 291 expr {[file size test.db] / 1024} 292} {8} 293do_test e_vacuum-3.3.2.2 { 294 create_db { PRAGMA auto_vacuum = INCREMENTAL } 295 execsql { 296 DELETE FROM t1; 297 DELETE FROM t2; 298 PRAGMA incremental_vacuum; 299 } 300 expr {[file size test.db] / 1024} 301} {8} 302 303finish_test 304