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.1 2008/04/03 14:36:26 danielk1977 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 page_size = 1024; 36 CREATE TABLE t1(a, b, c); 37 INSERT INTO t1 VALUES(1, 2, 3); 38 } 39} {} 40do_test vacuum3-1.2 { 41 execsql { PRAGMA page_size } 42} {1024} 43do_test vacuum3-1.3 { 44 file size test.db 45} {2048} 46 47set I 4 48foreach {request actual database} [list \ 49 2048 2048 4096 \ 50 1024 1024 2048 \ 51 1170 1024 2048 \ 52 256 1024 2048 \ 53 512 512 1024 \ 54 4096 4096 8192 \ 55 1024 1024 2048 \ 56] { 57if {$I==10} breakpoint 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 ALTER TABLE t1 ADD COLUMN d; 85 UPDATE t1 SET d = randomblob(1000); 86 } 87 file size test.db 88} {3072} 89do_test vacuum3-2.2 { 90 execsql { PRAGMA page_size } 91} {1024} 92do_test vacuum3-2.3 { 93 set blob [db one {select d from t1}] 94 string length $blob 95} {1000} 96 97set I 4 98foreach {request actual database} [list \ 99 2048 2048 4096 \ 100 1024 1024 3072 \ 101 1170 1024 3072 \ 102 256 1024 3072 \ 103 512 512 2048 \ 104 4096 4096 8192 \ 105 1024 1024 3072 \ 106] { 107if {$I==10} breakpoint 108 do_test vacuum3-2.$I.1 { 109 execsql " 110 PRAGMA page_size = $request; 111 VACUUM; 112 " 113 execsql { PRAGMA page_size } 114 } $actual 115 do_test vacuum3-2.$I.2 { 116 file size test.db 117 } $database 118 do_test vacuum3-2.$I.3 { 119 execsql { SELECT * FROM t1 } 120 } [list 1 2 3 $blob] 121 integrity_check vacuum3-1.$I.4 122 123 incr I 124} 125 126#------------------------------------------------------------------- 127# Test cases vacuum3-3.* converts a database large enough to include 128# the locking page (in a test environment) between few different 129# page sizes. 130# 131proc signature {} { 132 return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}] 133} 134do_test vacuum3-3.1 { 135 execsql " 136 PRAGMA page_size = 1024; 137 BEGIN; 138 CREATE TABLE abc(a PRIMARY KEY, b, c); 139 INSERT INTO abc VALUES(randomblob(100), randomblob(200), randomblob(1000)); 140 INSERT INTO abc 141 SELECT randomblob(1000), randomblob(200), randomblob(100) 142 FROM abc; 143 INSERT INTO abc 144 SELECT randomblob(100), randomblob(200), randomblob(1000) 145 FROM abc; 146 INSERT INTO abc 147 SELECT randomblob(100), randomblob(200), randomblob(1000) 148 FROM abc; 149 INSERT INTO abc 150 SELECT randomblob(100), randomblob(200), randomblob(1000) 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(25), randomblob(45), randomblob(9456) 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(25), randomblob(45), randomblob(9456) 163 FROM abc; 164 COMMIT; 165 " 166} {} 167do_test vacuum3-3.2 { 168 execsql { PRAGMA page_size } 169} {1024} 170 171set ::sig [signature] 172 173set I 3 174foreach {request actual} [list \ 175 2048 2048 \ 176 1024 1024 \ 177 1170 1024 \ 178 256 1024 \ 179 512 512 \ 180 4096 4096 \ 181 1024 1024 \ 182] { 183if {$I==10} breakpoint 184 do_test vacuum3-3.$I.1 { 185 execsql " 186 PRAGMA page_size = $request; 187 VACUUM; 188 " 189 execsql { PRAGMA page_size } 190 } $actual 191 do_test vacuum3-3.$I.2 { 192 signature 193 } $::sig 194 integrity_check vacuum3-3.$I.3 195 196 incr I 197} 198 199do_ioerr_test vacuum3-ioerr-1 -cksum true -sqlprep { 200 PRAGMA page_size = 1024; 201 BEGIN; 202 CREATE TABLE t1(a, b, c); 203 INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); 204 INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; 205 INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1; 206 INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1; 207 INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1; 208 INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1; 209 INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1; 210 INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1; 211 INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600)); 212 CREATE TABLE t2 AS SELECT * FROM t1; 213 CREATE TABLE t3 AS SELECT * FROM t1; 214 COMMIT; 215 DROP TABLE t2; 216} -sqlbody { 217 PRAGMA page_size = 4096; 218 VACUUM; 219} 220do_ioerr_test vacuum3-ioerr-2 -cksum true -sqlprep { 221 PRAGMA page_size = 2048; 222 BEGIN; 223 CREATE TABLE t1(a, b, c); 224 INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); 225 INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; 226 INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1; 227 INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1; 228 INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1; 229 INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1; 230 INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1; 231 INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1; 232 INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600)); 233 CREATE TABLE t2 AS SELECT * FROM t1; 234 CREATE TABLE t3 AS SELECT * FROM t1; 235 COMMIT; 236 DROP TABLE t2; 237} -sqlbody { 238 PRAGMA page_size = 512; 239 VACUUM; 240} 241 242source $testdir/malloc_common.tcl 243if {$MEMDEBUG} { 244 do_malloc_test vacuum3-malloc-1 -sqlprep { 245 PRAGMA page_size = 2048; 246 BEGIN; 247 CREATE TABLE t1(a, b, c); 248 INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); 249 INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; 250 INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1; 251 INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1; 252 INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1; 253 INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1; 254 INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1; 255 INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1; 256 INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600)); 257 CREATE TABLE t2 AS SELECT * FROM t1; 258 CREATE TABLE t3 AS SELECT * FROM t1; 259 COMMIT; 260 DROP TABLE t2; 261 } -sqlbody { 262 PRAGMA page_size = 512; 263 VACUUM; 264 } 265} 266 267finish_test 268 269