000001 # 2010 September 24
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # This file implements tests to verify that the "testable statements" in
000013 # the lang_vacuum.html document are correct.
000014 #
000015
000016 set testdir [file dirname $argv0]
000017 source $testdir/tester.tcl
000018
000019 sqlite3_test_control_pending_byte 0x1000000
000020
000021 proc create_db {{sql ""}} {
000022 catch { db close }
000023 forcedelete test.db
000024 sqlite3 db test.db
000025
000026 db transaction {
000027 execsql { PRAGMA page_size = 1024; }
000028 execsql $sql
000029 execsql {
000030 CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
000031 INSERT INTO t1 VALUES(1, randomblob(400));
000032 INSERT INTO t1 SELECT a+1, randomblob(400) FROM t1;
000033 INSERT INTO t1 SELECT a+2, randomblob(400) FROM t1;
000034 INSERT INTO t1 SELECT a+4, randomblob(400) FROM t1;
000035 INSERT INTO t1 SELECT a+8, randomblob(400) FROM t1;
000036 INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1;
000037 INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1;
000038 INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1;
000039
000040 CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
000041 INSERT INTO t2 SELECT * FROM t1;
000042 }
000043 }
000044
000045 return [expr {[file size test.db] / 1024}]
000046 }
000047
000048 # This proc returns the number of contiguous blocks of pages that make up
000049 # the table or index named by the only argument. For example, if the table
000050 # occupies database pages 3, 4, 8 and 9, then this command returns 2 (there
000051 # are 2 fragments - one consisting of pages 3 and 4, the other of fragments
000052 # 8 and 9).
000053 #
000054 proc fragment_count {name} {
000055 execsql { CREATE VIRTUAL TABLE temp.stat USING dbstat }
000056 set nFrag 1
000057 db eval {SELECT pageno FROM stat WHERE name = 't1' ORDER BY pageno} {
000058 if {[info exists prevpageno] && $prevpageno != $pageno-1} {
000059 incr nFrag
000060 }
000061 set prevpageno $pageno
000062 }
000063 execsql { DROP TABLE temp.stat }
000064 set nFrag
000065 }
000066
000067
000068 # -- syntax diagram vacuum-stmt
000069 #
000070 do_execsql_test e_vacuum-0.1 { VACUUM } {}
000071
000072 # EVIDENCE-OF: R-51469-36013 Unless SQLite is running in
000073 # "auto_vacuum=FULL" mode, when a large amount of data is deleted from
000074 # the database file it leaves behind empty space, or "free" database
000075 # pages.
000076 #
000077 # EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database
000078 # reclaims this space and reduces the size of the database file.
000079 #
000080 foreach {tn avmode sz} {
000081 1 none 7
000082 2 full 8
000083 3 incremental 8
000084 } {
000085 set nPage [create_db "PRAGMA auto_vacuum = $avmode"]
000086
000087 do_execsql_test e_vacuum-1.1.$tn.1 {
000088 DELETE FROM t1;
000089 DELETE FROM t2;
000090 } {}
000091
000092 if {$avmode == "full"} {
000093 # This branch tests the "unless ... auto_vacuum=FULL" in the requirement
000094 # above. If auto_vacuum is set to FULL, then no empty space is left in
000095 # the database file.
000096 do_execsql_test e_vacuum-1.1.$tn.2 {PRAGMA freelist_count} 0
000097 } else {
000098 set freelist [expr {$nPage - $sz}]
000099 if {$avmode == "incremental"} {
000100 # The page size is 1024 bytes. Therefore, assuming the database contains
000101 # somewhere between 207 and 411 pages (it does), there are 2 pointer-map
000102 # pages.
000103 incr freelist -2
000104 }
000105 do_execsql_test e_vacuum-1.1.$tn.3 {PRAGMA freelist_count} $freelist
000106 do_execsql_test e_vacuum-1.1.$tn.4 {VACUUM} {}
000107 }
000108
000109 do_test e_vacuum-1.1.$tn.5 { expr {[file size test.db] / 1024} } $sz
000110 }
000111
000112 # EVIDENCE-OF: R-50943-18433 Frequent inserts, updates, and deletes can
000113 # cause the database file to become fragmented - where data for a single
000114 # table or index is scattered around the database file.
000115 #
000116 # EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and
000117 # index is largely stored contiguously within the database file.
000118 #
000119 # e_vacuum-1.2.1 - Perform many INSERT, UPDATE and DELETE ops on table t1.
000120 # e_vacuum-1.2.2 - Verify that t1 and its indexes are now quite fragmented.
000121 # e_vacuum-1.2.3 - Run VACUUM.
000122 # e_vacuum-1.2.4 - Verify that t1 and its indexes are now much
000123 # less fragmented.
000124 #
000125 ifcapable vtab&&compound {
000126 create_db
000127 register_dbstat_vtab db
000128 do_execsql_test e_vacuum-1.2.1 {
000129 DELETE FROM t1 WHERE a%2;
000130 INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2;
000131 UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0;
000132 } {}
000133
000134 do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1
000135 do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1
000136 do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1
000137
000138 do_execsql_test e_vacuum-1.2.3 { VACUUM } {}
000139
000140 # In practice, the tables and indexes each end up stored as two fragments -
000141 # one containing the root page and another containing all other pages.
000142 #
000143 do_test e_vacuum-1.2.4.1 { fragment_count t1 } 2
000144 do_test e_vacuum-1.2.4.2 { fragment_count sqlite_autoindex_t1_1 } 2
000145 do_test e_vacuum-1.2.4.3 { fragment_count sqlite_autoindex_t1_2 } 2
000146 }
000147
000148 # EVIDENCE-OF: R-20474-44465 Normally, the database page_size and
000149 # whether or not the database supports auto_vacuum must be configured
000150 # before the database file is actually created.
000151 #
000152 do_test e_vacuum-1.3.1.1 {
000153 create_db "PRAGMA page_size = 1024 ; PRAGMA auto_vacuum = FULL"
000154 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
000155 } {1024 1}
000156 do_test e_vacuum-1.3.1.2 {
000157 execsql { PRAGMA page_size = 2048 }
000158 execsql { PRAGMA auto_vacuum = NONE }
000159 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
000160 } {1024 1}
000161
000162 if {![nonzero_reserved_bytes]} {
000163 # EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode,
000164 # the page_size and/or auto_vacuum properties of an existing database
000165 # may be changed by using the page_size and/or pragma auto_vacuum
000166 # pragmas and then immediately VACUUMing the database.
000167 #
000168 do_test e_vacuum-1.3.2.1 {
000169 execsql { PRAGMA journal_mode = delete }
000170 execsql { PRAGMA page_size = 2048 }
000171 execsql { PRAGMA auto_vacuum = NONE }
000172 execsql VACUUM
000173 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
000174 } {2048 0}
000175
000176 # EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the
000177 # auto_vacuum support property can be changed using VACUUM.
000178 #
000179 if {[wal_is_capable]} {
000180 do_test e_vacuum-1.3.3.1 {
000181 execsql { PRAGMA journal_mode = wal }
000182 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
000183 } {2048 0}
000184 do_test e_vacuum-1.3.3.2 {
000185 execsql { PRAGMA page_size = 1024 }
000186 execsql { PRAGMA auto_vacuum = FULL }
000187 execsql VACUUM
000188 execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
000189 } {2048 1}
000190 }
000191 }
000192
000193 # EVIDENCE-OF: R-40347-36128 By default, VACUUM operates on the main
000194 # database.
000195 forcedelete test.db2
000196 create_db { PRAGMA auto_vacuum = NONE }
000197 do_execsql_test e_vacuum-2.1.1 {
000198 ATTACH 'test.db2' AS aux;
000199 PRAGMA aux.page_size = 1024;
000200 CREATE TABLE aux.t3 AS SELECT * FROM t1;
000201 DELETE FROM t3;
000202 } {}
000203 set original_size [file size test.db2]
000204
000205 # Vacuuming the main database does not affect aux
000206 do_execsql_test e_vacuum-2.1.3 { VACUUM } {}
000207 do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1
000208
000209 # EVIDENCE-OF: R-36598-60500 Attached databases can be vacuumed by
000210 # appending the appropriate schema-name to the VACUUM statement.
000211 do_execsql_test e_vacuum-2.1.7 { VACUUM aux; } {}
000212 do_test e_vacuum-2.1.8 { expr {[file size test.db2]<$::original_size} } 1
000213
000214 # EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of
000215 # entries in any tables that do not have an explicit INTEGER PRIMARY
000216 # KEY.
000217 #
000218 # Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when
000219 # a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding
000220 # an INTEGER PRIMARY KEY column to a table stops this from happening.
000221 #
000222 # Update 2019-01-07: Rowids are now preserved by VACUUM.
000223 #
000224 do_execsql_test e_vacuum-3.1.1 {
000225 CREATE TABLE t4(x);
000226 INSERT INTO t4(x) VALUES('x');
000227 INSERT INTO t4(x) VALUES('y');
000228 INSERT INTO t4(x) VALUES('z');
000229 DELETE FROM t4 WHERE x = 'y';
000230 SELECT rowid, x FROM t4;
000231 } {1 x 3 z}
000232 do_execsql_test e_vacuum-3.1.2 {
000233 VACUUM;
000234 SELECT rowid, x FROM t4;
000235 } {1 x 2 z}
000236
000237 # Rowids are preserved if an INTEGER PRIMARY KEY is used
000238 do_execsql_test e_vacuum-3.1.3 {
000239 CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
000240 INSERT INTO t5(x) VALUES('x');
000241 INSERT INTO t5(x) VALUES('y');
000242 INSERT INTO t5(x) VALUES('z');
000243 DELETE FROM t5 WHERE x = 'y';
000244 SELECT rowid, x FROM t5;
000245 } {1 x 3 z}
000246 do_execsql_test e_vacuum-3.1.4 {
000247 VACUUM;
000248 SELECT rowid, x FROM t5;
000249 } {1 x 3 z}
000250
000251 # Rowid is preserved for VACUUM INTO
000252 do_execsql_test e_vacuum-3.1.5 {
000253 DROP TABLE t5;
000254 CREATE TABLE t5(x);
000255 INSERT INTO t5(x) VALUES('x');
000256 INSERT INTO t5(x) VALUES('y');
000257 INSERT INTO t5(x) VALUES('z');
000258 DELETE FROM t5 WHERE x = 'y';
000259 SELECT rowid, x FROM t5;
000260 } {1 x 3 z}
000261 forcedelete test2.db
000262 do_execsql_test e_vacuum-3.1.6 {
000263 VACUUM INTO 'test2.db';
000264 ATTACH 'test2.db' AS aux1;
000265 SELECT rowid, x FROM aux1.t5;
000266 DETACH aux1;
000267 } {1 x 3 z}
000268
000269 # Rowids are not renumbered if the table being vacuumed
000270 # has indexes.
000271 do_execsql_test e_vacuum-3.1.7 {
000272 DROP TABLE t5;
000273 CREATE TABLE t5(x,y,z);
000274 INSERT INTO t5(x) VALUES('x');
000275 INSERT INTO t5(x) VALUES('y');
000276 INSERT INTO t5(x) VALUES('z');
000277 UPDATE t5 SET y=x, z=random();
000278 DELETE FROM t5 WHERE x = 'y';
000279 CREATE INDEX t5x ON t5(x);
000280 CREATE UNIQUE INDEX t5y ON t5(y);
000281 CREATE INDEX t5zxy ON t5(z,x,y);
000282 SELECT rowid, x FROM t5;
000283 } {1 x 3 z}
000284 do_execsql_test e_vacuum-3.1.8 {
000285 VACUUM;
000286 SELECT rowid, x FROM t5;
000287 } {1 x 3 z}
000288
000289 # EVIDENCE-OF: R-12218-18073 A VACUUM will fail if there is an open
000290 # transaction on the database connection that is attempting to run the
000291 # VACUUM.
000292 #
000293 do_execsql_test e_vacuum-3.2.1.1 { BEGIN } {}
000294 do_catchsql_test e_vacuum-3.2.1.2 {
000295 VACUUM
000296 } {1 {cannot VACUUM from within a transaction}}
000297 do_execsql_test e_vacuum-3.2.1.3 { COMMIT } {}
000298 do_execsql_test e_vacuum-3.2.1.4 { VACUUM } {}
000299 do_execsql_test e_vacuum-3.2.1.5 { SAVEPOINT x } {}
000300 do_catchsql_test e_vacuum-3.2.1.6 {
000301 VACUUM
000302 } {1 {cannot VACUUM from within a transaction}}
000303 do_execsql_test e_vacuum-3.2.1.7 { COMMIT } {}
000304 do_execsql_test e_vacuum-3.2.1.8 { VACUUM } {}
000305
000306 create_db
000307 do_test e_vacuum-3.2.2.1 {
000308 set res ""
000309 db eval { SELECT a FROM t1 } {
000310 if {$a == 10} { set res [catchsql VACUUM] }
000311 }
000312 set res
000313 } {1 {cannot VACUUM - SQL statements in progress}}
000314
000315
000316 # EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command
000317 # to reclaim space after data has been deleted is auto-vacuum mode,
000318 # enabled using the auto_vacuum pragma.
000319 #
000320 do_test e_vacuum-3.3.1 {
000321 create_db { PRAGMA auto_vacuum = FULL }
000322 execsql { PRAGMA auto_vacuum }
000323 } {1}
000324
000325 # EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database
000326 # free pages may be reclaimed after deleting data, causing the file to
000327 # shrink, without rebuilding the entire database using VACUUM.
000328 #
000329 do_test e_vacuum-3.3.2.1 {
000330 create_db { PRAGMA auto_vacuum = FULL }
000331 execsql {
000332 DELETE FROM t1;
000333 DELETE FROM t2;
000334 }
000335 expr {[file size test.db] / 1024}
000336 } {8}
000337 do_test e_vacuum-3.3.2.2 {
000338 create_db { PRAGMA auto_vacuum = INCREMENTAL }
000339 execsql {
000340 DELETE FROM t1;
000341 DELETE FROM t2;
000342 PRAGMA incremental_vacuum;
000343 }
000344 expr {[file size test.db] / 1024}
000345 } {8}
000346
000347 finish_test