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# -- 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&&compound { 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 162if {![nonzero_reserved_bytes]} { 163 # EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode, 164 # the page_size and/or auto_vacuum properties of an existing database 165 # may be changed by using the page_size and/or pragma auto_vacuum 166 # pragmas and then immediately VACUUMing the database. 167 # 168 do_test e_vacuum-1.3.2.1 { 169 execsql { PRAGMA journal_mode = delete } 170 execsql { PRAGMA page_size = 2048 } 171 execsql { PRAGMA auto_vacuum = NONE } 172 execsql VACUUM 173 execsql { PRAGMA page_size ; PRAGMA auto_vacuum } 174 } {2048 0} 175 176 # EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the 177 # auto_vacuum support property can be changed using VACUUM. 178 # 179 if {[wal_is_capable]} { 180 do_test e_vacuum-1.3.3.1 { 181 execsql { PRAGMA journal_mode = wal } 182 execsql { PRAGMA page_size ; PRAGMA auto_vacuum } 183 } {2048 0} 184 do_test e_vacuum-1.3.3.2 { 185 execsql { PRAGMA page_size = 1024 } 186 execsql { PRAGMA auto_vacuum = FULL } 187 execsql VACUUM 188 execsql { PRAGMA page_size ; PRAGMA auto_vacuum } 189 } {2048 1} 190 } 191} 192 193# EVIDENCE-OF: R-55119-57913 By default, VACUUM only works only on the 194# main database. 195forcedelete test.db2 196create_db { PRAGMA auto_vacuum = NONE } 197do_execsql_test e_vacuum-2.1.1 { 198 ATTACH 'test.db2' AS aux; 199 PRAGMA aux.page_size = 1024; 200 CREATE TABLE aux.t3 AS SELECT * FROM t1; 201 DELETE FROM t3; 202} {} 203set original_size [file size test.db2] 204 205# Vacuuming the main database does not affect aux 206do_execsql_test e_vacuum-2.1.3 { VACUUM } {} 207do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1 208 209# EVIDENCE-OF: R-36598-60500 Attached databases can be vacuumed by 210# appending the appropriate schema-name to the VACUUM statement. 211do_execsql_test e_vacuum-2.1.7 { VACUUM aux; } {} 212do_test e_vacuum-2.1.8 { expr {[file size test.db2]<$::original_size} } 1 213 214# EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of 215# entries in any tables that do not have an explicit INTEGER PRIMARY 216# KEY. 217# 218# Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when 219# a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding 220# an INTEGER PRIMARY KEY column to a table stops this from happening. 221# 222# Update 2019-01-07: Rowids are now preserved by VACUUM. 223# 224do_execsql_test e_vacuum-3.1.1 { 225 CREATE TABLE t4(x); 226 INSERT INTO t4(x) VALUES('x'); 227 INSERT INTO t4(x) VALUES('y'); 228 INSERT INTO t4(x) VALUES('z'); 229 DELETE FROM t4 WHERE x = 'y'; 230 SELECT rowid, x FROM t4; 231} {1 x 3 z} 232do_execsql_test e_vacuum-3.1.2 { 233 VACUUM; 234 SELECT rowid, x FROM t4; 235} {1 x 2 z} 236 237# Rowids are preserved if an INTEGER PRIMARY KEY is used 238do_execsql_test e_vacuum-3.1.3 { 239 CREATE TABLE t5(x, y INTEGER PRIMARY KEY); 240 INSERT INTO t5(x) VALUES('x'); 241 INSERT INTO t5(x) VALUES('y'); 242 INSERT INTO t5(x) VALUES('z'); 243 DELETE FROM t5 WHERE x = 'y'; 244 SELECT rowid, x FROM t5; 245} {1 x 3 z} 246do_execsql_test e_vacuum-3.1.4 { 247 VACUUM; 248 SELECT rowid, x FROM t5; 249} {1 x 3 z} 250 251# Rowid is preserved for VACUUM INTO 252do_execsql_test e_vacuum-3.1.5 { 253 DROP TABLE t5; 254 CREATE TABLE t5(x); 255 INSERT INTO t5(x) VALUES('x'); 256 INSERT INTO t5(x) VALUES('y'); 257 INSERT INTO t5(x) VALUES('z'); 258 DELETE FROM t5 WHERE x = 'y'; 259 SELECT rowid, x FROM t5; 260} {1 x 3 z} 261forcedelete test2.db 262do_execsql_test e_vacuum-3.1.6 { 263 VACUUM INTO 'test2.db'; 264 ATTACH 'test2.db' AS aux1; 265 SELECT rowid, x FROM aux1.t5; 266 DETACH aux1; 267} {1 x 3 z} 268 269# Rowids are not renumbered if the table being vacuumed 270# has indexes. 271do_execsql_test e_vacuum-3.1.7 { 272 DROP TABLE t5; 273 CREATE TABLE t5(x,y,z); 274 INSERT INTO t5(x) VALUES('x'); 275 INSERT INTO t5(x) VALUES('y'); 276 INSERT INTO t5(x) VALUES('z'); 277 UPDATE t5 SET y=x, z=random(); 278 DELETE FROM t5 WHERE x = 'y'; 279 CREATE INDEX t5x ON t5(x); 280 CREATE UNIQUE INDEX t5y ON t5(y); 281 CREATE INDEX t5zxy ON t5(z,x,y); 282 SELECT rowid, x FROM t5; 283} {1 x 3 z} 284do_execsql_test e_vacuum-3.1.8 { 285 VACUUM; 286 SELECT rowid, x FROM t5; 287} {1 x 3 z} 288 289# EVIDENCE-OF: R-12218-18073 A VACUUM will fail if there is an open 290# transaction on the database connection that is attempting to run the 291# VACUUM. 292# 293do_execsql_test e_vacuum-3.2.1.1 { BEGIN } {} 294do_catchsql_test e_vacuum-3.2.1.2 { 295 VACUUM 296} {1 {cannot VACUUM from within a transaction}} 297do_execsql_test e_vacuum-3.2.1.3 { COMMIT } {} 298do_execsql_test e_vacuum-3.2.1.4 { VACUUM } {} 299do_execsql_test e_vacuum-3.2.1.5 { SAVEPOINT x } {} 300do_catchsql_test e_vacuum-3.2.1.6 { 301 VACUUM 302} {1 {cannot VACUUM from within a transaction}} 303do_execsql_test e_vacuum-3.2.1.7 { COMMIT } {} 304do_execsql_test e_vacuum-3.2.1.8 { VACUUM } {} 305 306create_db 307do_test e_vacuum-3.2.2.1 { 308 set res "" 309 db eval { SELECT a FROM t1 } { 310 if {$a == 10} { set res [catchsql VACUUM] } 311 } 312 set res 313} {1 {cannot VACUUM - SQL statements in progress}} 314 315 316# EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command 317# to reclaim space after data has been deleted is auto-vacuum mode, 318# enabled using the auto_vacuum pragma. 319# 320do_test e_vacuum-3.3.1 { 321 create_db { PRAGMA auto_vacuum = FULL } 322 execsql { PRAGMA auto_vacuum } 323} {1} 324 325# EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database 326# free pages may be reclaimed after deleting data, causing the file to 327# shrink, without rebuilding the entire database using VACUUM. 328# 329do_test e_vacuum-3.3.2.1 { 330 create_db { PRAGMA auto_vacuum = FULL } 331 execsql { 332 DELETE FROM t1; 333 DELETE FROM t2; 334 } 335 expr {[file size test.db] / 1024} 336} {8} 337do_test e_vacuum-3.3.2.2 { 338 create_db { PRAGMA auto_vacuum = INCREMENTAL } 339 execsql { 340 DELETE FROM t1; 341 DELETE FROM t2; 342 PRAGMA incremental_vacuum; 343 } 344 expr {[file size test.db] / 1024} 345} {8} 346 347finish_test 348