AryaWu/sqlite
0
1# 2010 September 242#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#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.tcl18 19sqlite3_test_control_pending_byte 0x100000020 21proc create_db {{sql ""}} {22 catch { db close }23 forcedelete test.db24 sqlite3 db test.db25 26 db transaction {27 execsql { PRAGMA page_size = 1024; }28 execsql $sql29 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 up49# the table or index named by the only argument. For example, if the table50# occupies database pages 3, 4, 8 and 9, then this command returns 2 (there51# are 2 fragments - one consisting of pages 3 and 4, the other of fragments52# 8 and 9).53#54proc fragment_count {name} {55 execsql { CREATE VIRTUAL TABLE temp.stat USING dbstat }56 set nFrag 157 db eval {SELECT pageno FROM stat WHERE name = 't1' ORDER BY pageno} {58 if {[info exists prevpageno] && $prevpageno != $pageno-1} {59 incr nFrag60 }61 set prevpageno $pageno62 }63 execsql { DROP TABLE temp.stat }64 set nFrag65}66 67 68# -- syntax diagram vacuum-stmt69#70do_execsql_test e_vacuum-0.1 { VACUUM } {}71 72# EVIDENCE-OF: R-51469-36013 Unless SQLite is running in73# "auto_vacuum=FULL" mode, when a large amount of data is deleted from74# the database file it leaves behind empty space, or "free" database75# pages.76#77# EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database78# 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 884} {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 requirement94 # above. If auto_vacuum is set to FULL, then no empty space is left in95 # the database file.96 do_execsql_test e_vacuum-1.1.$tn.2 {PRAGMA freelist_count} 097 } else {98 set freelist [expr {$nPage - $sz}]99 if {$avmode == "incremental"} { 100 # The page size is 1024 bytes. Therefore, assuming the database contains101 # somewhere between 207 and 411 pages (it does), there are 2 pointer-map102 # pages.103 incr freelist -2104 }105 do_execsql_test e_vacuum-1.1.$tn.3 {PRAGMA freelist_count} $freelist106 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} } $sz110}111 112# EVIDENCE-OF: R-50943-18433 Frequent inserts, updates, and deletes can113# cause the database file to become fragmented - where data for a single114# table or index is scattered around the database file.115#116# EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and117# 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 db128 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 } 1135 do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1136 do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1137 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 } 2144 do_test e_vacuum-1.2.4.2 { fragment_count sqlite_autoindex_t1_1 } 2145 do_test e_vacuum-1.2.4.3 { fragment_count sqlite_autoindex_t1_2 } 2146}147 148# EVIDENCE-OF: R-20474-44465 Normally, the database page_size and149# whether or not the database supports auto_vacuum must be configured150# 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 database165 # may be changed by using the page_size and/or pragma auto_vacuum166 # 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 VACUUM173 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }174 } {2048 0}175 176 # EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the177 # 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 VACUUM188 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 main194# database.195forcedelete test.db2196create_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 aux206do_execsql_test e_vacuum-2.1.3 { VACUUM } {}207do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1208 209# EVIDENCE-OF: R-36598-60500 Attached databases can be vacuumed by210# 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} } 1213 214# EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of215# entries in any tables that do not have an explicit INTEGER PRIMARY216# KEY.217#218# Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when219# a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding220# 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 used238do_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 INTO252do_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.db262do_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 vacuumed270# 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 open290# transaction on the database connection that is attempting to run the291# 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_db307do_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 res313} {1 {cannot VACUUM - SQL statements in progress}}314 315 316# EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command317# 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 database326# free pages may be reclaimed after deleting data, causing the file to327# 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_test348