1# 2007 March 19 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 changing the database page size using a 13# VACUUM statement. 14# 15# $Id: vacuum3.test,v 1.9 2008/08/26 21:07:27 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# If the VACUUM statement is disabled in the current build, skip all 21# the tests in this file. 22# 23ifcapable !vacuum { 24 finish_test 25 return 26} 27 28 29#------------------------------------------------------------------- 30# Test cases vacuum3-1.* convert a simple 2-page database between a 31# few different page sizes. 32# 33do_test vacuum3-1.1 { 34 execsql { 35 PRAGMA auto_vacuum=OFF; 36 PRAGMA page_size = 1024; 37 CREATE TABLE t1(a, b, c); 38 INSERT INTO t1 VALUES(1, 2, 3); 39 } 40} {} 41do_test vacuum3-1.2 { 42 execsql { PRAGMA page_size } 43} {1024} 44do_test vacuum3-1.3 { 45 file size test.db 46} {2048} 47 48set I 4 49foreach {request actual database} [list \ 50 2048 2048 4096 \ 51 1024 1024 2048 \ 52 1170 1024 2048 \ 53 256 1024 2048 \ 54 512 512 1024 \ 55 4096 4096 8192 \ 56 1024 1024 2048 \ 57] { 58 do_test vacuum3-1.$I.1 { 59 execsql " 60 PRAGMA page_size = $request; 61 VACUUM; 62 " 63 execsql { PRAGMA page_size } 64 } $actual 65 do_test vacuum3-1.$I.2 { 66 file size test.db 67 } $database 68 do_test vacuum3-1.$I.3 { 69 execsql { SELECT * FROM t1 } 70 } {1 2 3} 71 integrity_check vacuum3-1.$I.4 72 73 incr I 74} 75 76#------------------------------------------------------------------- 77# Test cases vacuum3-2.* convert a simple 3-page database between a 78# few different page sizes. 79# 80do_test vacuum3-2.1 { 81 execsql { 82 PRAGMA page_size = 1024; 83 VACUUM; 84 } 85 ifcapable altertable { 86 execsql { ALTER TABLE t1 ADD COLUMN d; } 87 } else { 88 execsql { 89 DROP TABLE t1; 90 CREATE TABLE t1(a, b, c, d); 91 INSERT INTO t1 VALUES(1, 2, 3, NULL); 92 } 93 } 94 execsql { 95 UPDATE t1 SET d = randomblob(1000); 96 } 97 file size test.db 98} {3072} 99do_test vacuum3-2.2 { 100 execsql { PRAGMA page_size } 101} {1024} 102do_test vacuum3-2.3 { 103 set blob [db one {select d from t1}] 104 string length $blob 105} {1000} 106 107set I 4 108foreach {request actual database} [list \ 109 2048 2048 4096 \ 110 1024 1024 3072 \ 111 1170 1024 3072 \ 112 256 1024 3072 \ 113 512 512 2048 \ 114 4096 4096 8192 \ 115 1024 1024 3072 \ 116] { 117 do_test vacuum3-2.$I.1 { 118 execsql " 119 PRAGMA page_size = $request; 120 VACUUM; 121 " 122 execsql { PRAGMA page_size } 123 } $actual 124 do_test vacuum3-2.$I.2 { 125 file size test.db 126 } $database 127 do_test vacuum3-2.$I.3 { 128 execsql { SELECT * FROM t1 } 129 } [list 1 2 3 $blob] 130 integrity_check vacuum3-1.$I.4 131 132 incr I 133} 134 135#------------------------------------------------------------------- 136# Test cases vacuum3-3.* converts a database large enough to include 137# the locking page (in a test environment) between few different 138# page sizes. 139# 140proc signature {} { 141 return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}] 142} 143do_test vacuum3-3.1 { 144 execsql " 145 PRAGMA page_size = 1024; 146 BEGIN; 147 CREATE TABLE abc(a PRIMARY KEY, b, c); 148 INSERT INTO abc VALUES(randomblob(100), randomblob(200), randomblob(1000)); 149 INSERT INTO abc 150 SELECT randomblob(1000), randomblob(200), randomblob(100) 151 FROM abc; 152 INSERT INTO abc 153 SELECT randomblob(100), randomblob(200), randomblob(1000) 154 FROM abc; 155 INSERT INTO abc 156 SELECT randomblob(100), randomblob(200), randomblob(1000) 157 FROM abc; 158 INSERT INTO abc 159 SELECT randomblob(100), randomblob(200), randomblob(1000) 160 FROM abc; 161 INSERT INTO abc 162 SELECT randomblob(100), randomblob(200), randomblob(1000) 163 FROM abc; 164 INSERT INTO abc 165 SELECT randomblob(25), randomblob(45), randomblob(9456) 166 FROM abc; 167 INSERT INTO abc 168 SELECT randomblob(100), randomblob(200), randomblob(1000) 169 FROM abc; 170 INSERT INTO abc 171 SELECT randomblob(25), randomblob(45), randomblob(9456) 172 FROM abc; 173 COMMIT; 174 " 175} {} 176do_test vacuum3-3.2 { 177 execsql { PRAGMA page_size } 178} {1024} 179 180set ::sig [signature] 181 182set I 3 183foreach {request actual} [list \ 184 2048 2048 \ 185 1024 1024 \ 186 1170 1024 \ 187 256 1024 \ 188 512 512 \ 189 4096 4096 \ 190 1024 1024 \ 191] { 192 do_test vacuum3-3.$I.1 { 193 execsql " 194 PRAGMA page_size = $request; 195 VACUUM; 196 " 197 execsql { PRAGMA page_size } 198 } $actual 199 do_test vacuum3-3.$I.2 { 200 signature 201 } $::sig 202 integrity_check vacuum3-3.$I.3 203 204 incr I 205} 206 207do_test vacuum3-4.1 { 208 db close 209 delete_file test.db 210 sqlite3 db test.db 211 execsql { 212 PRAGMA page_size=1024; 213 CREATE TABLE abc(a, b, c); 214 INSERT INTO abc VALUES(1, 2, 3); 215 INSERT INTO abc VALUES(4, 5, 6); 216 } 217 execsql { SELECT * FROM abc } 218} {1 2 3 4 5 6} 219do_test vacuum3-4.2 { 220 sqlite3 db2 test.db 221 execsql { SELECT * FROM abc } db2 222} {1 2 3 4 5 6} 223do_test vacuum3-4.3 { 224 execsql { 225 PRAGMA page_size = 2048; 226 VACUUM; 227 } 228 execsql { SELECT * FROM abc } 229} {1 2 3 4 5 6} 230do_test vacuum3-4.4 { 231 execsql { SELECT * FROM abc } db2 232} {1 2 3 4 5 6} 233do_test vacuum3-4.5 { 234 execsql { 235 PRAGMA page_size=16384; 236 VACUUM; 237 } db2 238 execsql { SELECT * FROM abc } db2 239} {1 2 3 4 5 6} 240do_test vacuum3-4.6 { 241 execsql { 242 PRAGMA page_size=1024; 243 VACUUM; 244 } 245 execsql { SELECT * FROM abc } db2 246} {1 2 3 4 5 6} 247 248# Unable to change the page-size of an in-memory using vacuum. 249db2 close 250sqlite3 db2 :memory: 251do_test vacuum3-5.1 { 252 db2 eval { 253 CREATE TABLE t1(x); 254 INSERT INTO t1 VALUES(1234); 255 PRAGMA page_size=4096; 256 VACUUM; 257 SELECT * FROM t1; 258 } 259} {1234} 260do_test vacuum3-5.2 { 261 db2 eval { 262 PRAGMA page_size 263 } 264} {1024} 265 266set create_database_sql { 267 BEGIN; 268 CREATE TABLE t1(a, b, c); 269 INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); 270 INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; 271 INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1; 272 INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1; 273 INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1; 274 INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1; 275 INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1; 276 INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1; 277 INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600)); 278 CREATE TABLE t2 AS SELECT * FROM t1; 279 CREATE TABLE t3 AS SELECT * FROM t1; 280 COMMIT; 281 DROP TABLE t2; 282} 283 284do_ioerr_test vacuum3-ioerr-1 -cksum true -sqlprep " 285 PRAGMA page_size = 1024; 286 $create_database_sql 287" -sqlbody { 288 PRAGMA page_size = 4096; 289 VACUUM; 290} 291do_ioerr_test vacuum3-ioerr-2 -cksum true -sqlprep " 292 PRAGMA page_size = 2048; 293 $create_database_sql 294" -sqlbody { 295 PRAGMA page_size = 512; 296 VACUUM; 297} 298 299ifcapable autovacuum { 300 do_ioerr_test vacuum3-ioerr-3 -cksum true -sqlprep " 301 PRAGMA auto_vacuum = 0; 302 $create_database_sql 303 " -sqlbody { 304 PRAGMA auto_vacuum = 1; 305 VACUUM; 306 } 307 do_ioerr_test vacuum3-ioerr-4 -cksum true -sqlprep " 308 PRAGMA auto_vacuum = 1; 309 $create_database_sql 310 " -sqlbody { 311 PRAGMA auto_vacuum = 0; 312 VACUUM; 313 } 314} 315 316source $testdir/malloc_common.tcl 317if {$MEMDEBUG} { 318 do_malloc_test vacuum3-malloc-1 -sqlprep { 319 PRAGMA page_size = 2048; 320 BEGIN; 321 CREATE TABLE t1(a, b, c); 322 INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); 323 INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; 324 INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1; 325 INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1; 326 INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1; 327 INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1; 328 INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1; 329 INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1; 330 INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600)); 331 CREATE TABLE t2 AS SELECT * FROM t1; 332 CREATE TABLE t3 AS SELECT * FROM t1; 333 COMMIT; 334 DROP TABLE t2; 335 } -sqlbody { 336 PRAGMA page_size = 512; 337 VACUUM; 338 } 339 do_malloc_test vacuum3-malloc-2 -sqlprep { 340 PRAGMA encoding=UTF16; 341 CREATE TABLE t1(a, b, c); 342 INSERT INTO t1 VALUES(1, 2, 3); 343 CREATE TABLE t2(x,y,z); 344 INSERT INTO t2 SELECT * FROM t1; 345 } -sqlbody { 346 VACUUM; 347 } 348} 349 350finish_test 351