1 # 2005 February 15 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing the VACUUM statement. 13 # 14 # $Id: vacuum2.test,v 1.10 2009/02/18 20:31:18 drh Exp $ 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # Do not use a codec for tests in this file, as the database file is 20 # manipulated directly using tcl scripts (using the [hexio_write] command). 21 # 22 do_not_use_codec 23 24 # If the VACUUM statement is disabled in the current build, skip all 25 # the tests in this file. 26 # 27 ifcapable {!vacuum||!autoinc} { 28 finish_test 29 return 30 } 31 if $AUTOVACUUM { 32 finish_test 33 return 34 } 35 36 # Ticket #1121 - make sure vacuum works if all autoincrement tables 37 # have been deleted. 38 # 39 do_test vacuum2-1.1 { 40 execsql { 41 CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); 42 DROP TABLE t1; 43 VACUUM; 44 } 45 } {} 46 47 # Ticket #2518. Make sure vacuum increments the change counter 48 # in the database header. 49 # 50 do_test vacuum2-2.1 { 51 execsql { 52 CREATE TABLE t1(x); 53 CREATE TABLE t2(y); 54 INSERT INTO t1 VALUES(1); 55 } 56 hexio_get_int [hexio_read test.db 24 4] 57 } [expr {[hexio_get_int [hexio_read test.db 24 4]]+3}] 58 do_test vacuum2-2.1 { 59 execsql { 60 VACUUM 61 } 62 hexio_get_int [hexio_read test.db 24 4] 63 } [expr {[hexio_get_int [hexio_read test.db 24 4]]+1}] 64 65 ############################################################################ 66 # Verify that we can use the auto_vacuum pragma to request a new 67 # autovacuum setting, do a VACUUM, and the new setting takes effect. 68 # Make sure this happens correctly even if there are multiple open 69 # connections to the same database file. 70 # 71 sqlite3 db2 test.db 72 set pageSize [db eval {pragma page_size}] 73 74 # We are currently not autovacuuming so the database should be 3 pages 75 # in size. 1 page for each of sqlite_master, t1, and t2. 76 # 77 do_test vacuum2-3.1 { 78 execsql { 79 INSERT INTO t1 VALUES('hello'); 80 INSERT INTO t2 VALUES('out there'); 81 } 82 expr {[file size test.db]/$pageSize} 83 } {3} 84 set cksum [cksum] 85 do_test vacuum2-3.2 { 86 cksum db2 87 } $cksum 88 89 # Convert the database to an autovacuumed database. 90 ifcapable autovacuum { 91 do_test vacuum2-3.3 { 92 execsql { 93 PRAGMA auto_vacuum=FULL; 94 VACUUM; 95 } 96 expr {[file size test.db]/$pageSize} 97 } {4} 98 } 99 do_test vacuum2-3.4 { 100 cksum db2 101 } $cksum 102 do_test vacuum2-3.5 { 103 cksum 104 } $cksum 105 do_test vacuum2-3.6 { 106 execsql {PRAGMA integrity_check} db2 107 } {ok} 108 do_test vacuum2-3.7 { 109 execsql {PRAGMA integrity_check} db 110 } {ok} 111 112 # Convert the database back to a non-autovacuumed database. 113 do_test vacuum2-3.13 { 114 execsql { 115 PRAGMA auto_vacuum=NONE; 116 VACUUM; 117 } 118 expr {[file size test.db]/$pageSize} 119 } {3} 120 do_test vacuum2-3.14 { 121 cksum db2 122 } $cksum 123 do_test vacuum2-3.15 { 124 cksum 125 } $cksum 126 do_test vacuum2-3.16 { 127 execsql {PRAGMA integrity_check} db2 128 } {ok} 129 do_test vacuum2-3.17 { 130 execsql {PRAGMA integrity_check} db 131 } {ok} 132 133 db2 close 134 135 ifcapable autovacuum { 136 do_test vacuum2-4.1 { 137 db close 138 file delete -force test.db 139 sqlite3 db test.db 140 execsql { 141 pragma auto_vacuum=1; 142 create table t(a, b); 143 insert into t values(1, 2); 144 insert into t values(1, 2); 145 pragma auto_vacuum=0; 146 vacuum; 147 pragma auto_vacuum; 148 } 149 } {0} 150 do_test vacuum2-4.2 { 151 execsql { 152 pragma auto_vacuum=1; 153 vacuum; 154 pragma auto_vacuum; 155 } 156 } {1} 157 do_test vacuum2-4.3 { 158 execsql { 159 pragma integrity_check 160 } 161 } {ok} 162 do_test vacuum2-4.4 { 163 db close 164 sqlite3 db test.db 165 execsql { 166 pragma auto_vacuum; 167 } 168 } {1} 169 do_test vacuum2-4.5 { # Ticket #3663 170 execsql { 171 pragma auto_vacuum=2; 172 vacuum; 173 pragma auto_vacuum; 174 } 175 } {2} 176 do_test vacuum2-4.6 { 177 execsql { 178 pragma integrity_check 179 } 180 } {ok} 181 do_test vacuum2-4.7 { 182 db close 183 sqlite3 db test.db 184 execsql { 185 pragma auto_vacuum; 186 } 187 } {2} 188 } 189 190 191 #------------------------------------------------------------------------- 192 # The following block of tests verify the behaviour of the library when 193 # a database is VACUUMed when there are one or more unfinalized SQL 194 # statements reading the same database using the same db handle. 195 # 196 db close 197 forcedelete test.db 198 sqlite3 db test.db 199 do_execsql_test vacuum2-5.1 { 200 CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); 201 INSERT INTO t1 VALUES(1, randomblob(500)); 202 INSERT INTO t1 SELECT a+1, randomblob(500) FROM t1; -- 2 203 INSERT INTO t1 SELECT a+2, randomblob(500) FROM t1; -- 4 204 INSERT INTO t1 SELECT a+4, randomblob(500) FROM t1; -- 8 205 INSERT INTO t1 SELECT a+8, randomblob(500) FROM t1; -- 16 206 } {} 207 208 do_test vacuum2-5.2 { 209 list [catch { 210 db eval {SELECT a, b FROM t1} { if {$a == 8} { execsql VACUUM } } 211 } msg] $msg 212 } {1 {cannot VACUUM - SQL statements in progress}} 213 214 do_test vacuum2-5.3 { 215 list [catch { 216 db eval {SELECT 1, 2, 3} { execsql VACUUM } 217 } msg] $msg 218 } {1 {cannot VACUUM - SQL statements in progress}} 219 220 do_test vacuum2-5.4 { 221 set res "" 222 set res2 "" 223 db eval {SELECT a, b FROM t1 WHERE a<=10} { 224 if {$a==6} { set res [catchsql VACUUM] } 225 lappend res2 $a 226 } 227 lappend res2 $res 228 } {1 2 3 4 5 6 7 8 9 10 {1 {cannot VACUUM - SQL statements in progress}}} 229 230 231 finish_test 232
