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