gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/e_vacuum.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 sqlite3_test_control_pending_byte 0x1000000 20 21 proc 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 # 54 proc 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 # 70 do_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 # 80 foreach {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 # 125 ifcapable 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 # 152 do_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} 156 do_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 if {![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-40347-36128 By default, VACUUM operates on the main 194 # database. 195 forcedelete test.db2 196 create_db { PRAGMA auto_vacuum = NONE } 197 do_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 } {} 203 set original_size [file size test.db2] 204 205 # Vacuuming the main database does not affect aux 206 do_execsql_test e_vacuum-2.1.3 { VACUUM } {} 207 do_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. 211 do_execsql_test e_vacuum-2.1.7 { VACUUM aux; } {} 212 do_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 # 224 do_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} 232 do_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 238 do_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} 246 do_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 252 do_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} 261 forcedelete test2.db 262 do_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. 271 do_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} 284 do_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 # 293 do_execsql_test e_vacuum-3.2.1.1 { BEGIN } {} 294 do_catchsql_test e_vacuum-3.2.1.2 { 295 VACUUM 296 } {1 {cannot VACUUM from within a transaction}} 297 do_execsql_test e_vacuum-3.2.1.3 { COMMIT } {} 298 do_execsql_test e_vacuum-3.2.1.4 { VACUUM } {} 299 do_execsql_test e_vacuum-3.2.1.5 { SAVEPOINT x } {} 300 do_catchsql_test e_vacuum-3.2.1.6 { 301 VACUUM 302 } {1 {cannot VACUUM from within a transaction}} 303 do_execsql_test e_vacuum-3.2.1.7 { COMMIT } {} 304 do_execsql_test e_vacuum-3.2.1.8 { VACUUM } {} 305 306 create_db 307 do_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 # 320 do_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 # 329 do_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} 337 do_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 347 finish_test