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-55119-57913 By default, VACUUM only works only on the
000194 # main 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 do_execsql_test e_vacuum-3.1.1 {
000223 CREATE TABLE t4(x);
000224 INSERT INTO t4(x) VALUES('x');
000225 INSERT INTO t4(x) VALUES('y');
000226 INSERT INTO t4(x) VALUES('z');
000227 DELETE FROM t4 WHERE x = 'y';
000228 SELECT rowid, x FROM t4;
000229 } {1 x 3 z}
000230 do_execsql_test e_vacuum-3.1.2 {
000231 VACUUM;
000232 SELECT rowid, x FROM t4;
000233 } {1 x 2 z}
000234
000235 do_execsql_test e_vacuum-3.1.3 {
000236 CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
000237 INSERT INTO t5(x) VALUES('x');
000238 INSERT INTO t5(x) VALUES('y');
000239 INSERT INTO t5(x) VALUES('z');
000240 DELETE FROM t5 WHERE x = 'y';
000241 SELECT rowid, x FROM t5;
000242 } {1 x 3 z}
000243 do_execsql_test e_vacuum-3.1.4 {
000244 VACUUM;
000245 SELECT rowid, x FROM t5;
000246 } {1 x 3 z}
000247
000248 # EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open
000249 # transaction, or if there are one or more active SQL statements when it
000250 # is run.
000251 #
000252 do_execsql_test e_vacuum-3.2.1.1 { BEGIN } {}
000253 do_catchsql_test e_vacuum-3.2.1.2 {
000254 VACUUM
000255 } {1 {cannot VACUUM from within a transaction}}
000256 do_execsql_test e_vacuum-3.2.1.3 { COMMIT } {}
000257 do_execsql_test e_vacuum-3.2.1.4 { VACUUM } {}
000258 do_execsql_test e_vacuum-3.2.1.5 { SAVEPOINT x } {}
000259 do_catchsql_test e_vacuum-3.2.1.6 {
000260 VACUUM
000261 } {1 {cannot VACUUM from within a transaction}}
000262 do_execsql_test e_vacuum-3.2.1.7 { COMMIT } {}
000263 do_execsql_test e_vacuum-3.2.1.8 { VACUUM } {}
000264
000265 create_db
000266 do_test e_vacuum-3.2.2.1 {
000267 set res ""
000268 db eval { SELECT a FROM t1 } {
000269 if {$a == 10} { set res [catchsql VACUUM] }
000270 }
000271 set res
000272 } {1 {cannot VACUUM - SQL statements in progress}}
000273
000274
000275 # EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command
000276 # to reclaim space after data has been deleted is auto-vacuum mode,
000277 # enabled using the auto_vacuum pragma.
000278 #
000279 do_test e_vacuum-3.3.1 {
000280 create_db { PRAGMA auto_vacuum = FULL }
000281 execsql { PRAGMA auto_vacuum }
000282 } {1}
000283
000284 # EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database
000285 # free pages may be reclaimed after deleting data, causing the file to
000286 # shrink, without rebuilding the entire database using VACUUM.
000287 #
000288 do_test e_vacuum-3.3.2.1 {
000289 create_db { PRAGMA auto_vacuum = FULL }
000290 execsql {
000291 DELETE FROM t1;
000292 DELETE FROM t2;
000293 }
000294 expr {[file size test.db] / 1024}
000295 } {8}
000296 do_test e_vacuum-3.3.2.2 {
000297 create_db { PRAGMA auto_vacuum = INCREMENTAL }
000298 execsql {
000299 DELETE FROM t1;
000300 DELETE FROM t2;
000301 PRAGMA incremental_vacuum;
000302 }
000303 expr {[file size test.db] / 1024}
000304 } {8}
000305
000306 finish_test