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