Annotation of embedaddon/sqlite3/test/vacuum3.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to vacuum3.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test