| # 2007 March 19 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # This file implements regression tests for SQLite library. The |
| # focus of this file is changing the database page size using a |
| # VACUUM statement. |
| # |
| # $Id: vacuum3.test,v 1.9 2008/08/26 21:07:27 drh Exp $ |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| |
| # If the VACUUM statement is disabled in the current build, skip all |
| # the tests in this file. |
| # |
| ifcapable !vacuum { |
| finish_test |
| return |
| } |
| |
| |
| #------------------------------------------------------------------- |
| # Test cases vacuum3-1.* convert a simple 2-page database between a |
| # few different page sizes. |
| # |
| do_test vacuum3-1.1 { |
| execsql { |
| PRAGMA auto_vacuum=OFF; |
| PRAGMA page_size = 1024; |
| CREATE TABLE t1(a, b, c); |
| INSERT INTO t1 VALUES(1, 2, 3); |
| } |
| } {} |
| do_test vacuum3-1.2 { |
| execsql { PRAGMA page_size } |
| } {1024} |
| do_test vacuum3-1.3 { |
| file size test.db |
| } {2048} |
| |
| set I 4 |
| foreach {request actual database} [list \ |
| 2048 2048 4096 \ |
| 1024 1024 2048 \ |
| 1170 1024 2048 \ |
| 256 1024 2048 \ |
| 512 512 1024 \ |
| 4096 4096 8192 \ |
| 1024 1024 2048 \ |
| ] { |
| do_test vacuum3-1.$I.1 { |
| execsql " |
| PRAGMA page_size = $request; |
| VACUUM; |
| " |
| execsql { PRAGMA page_size } |
| } $actual |
| do_test vacuum3-1.$I.2 { |
| file size test.db |
| } $database |
| do_test vacuum3-1.$I.3 { |
| execsql { SELECT * FROM t1 } |
| } {1 2 3} |
| integrity_check vacuum3-1.$I.4 |
| |
| incr I |
| } |
| |
| #------------------------------------------------------------------- |
| # Test cases vacuum3-2.* convert a simple 3-page database between a |
| # few different page sizes. |
| # |
| do_test vacuum3-2.1 { |
| execsql { |
| PRAGMA page_size = 1024; |
| VACUUM; |
| ALTER TABLE t1 ADD COLUMN d; |
| UPDATE t1 SET d = randomblob(1000); |
| } |
| file size test.db |
| } {3072} |
| do_test vacuum3-2.2 { |
| execsql { PRAGMA page_size } |
| } {1024} |
| do_test vacuum3-2.3 { |
| set blob [db one {select d from t1}] |
| string length $blob |
| } {1000} |
| |
| set I 4 |
| foreach {request actual database} [list \ |
| 2048 2048 4096 \ |
| 1024 1024 3072 \ |
| 1170 1024 3072 \ |
| 256 1024 3072 \ |
| 512 512 2048 \ |
| 4096 4096 8192 \ |
| 1024 1024 3072 \ |
| ] { |
| do_test vacuum3-2.$I.1 { |
| execsql " |
| PRAGMA page_size = $request; |
| VACUUM; |
| " |
| execsql { PRAGMA page_size } |
| } $actual |
| do_test vacuum3-2.$I.2 { |
| file size test.db |
| } $database |
| do_test vacuum3-2.$I.3 { |
| execsql { SELECT * FROM t1 } |
| } [list 1 2 3 $blob] |
| integrity_check vacuum3-1.$I.4 |
| |
| incr I |
| } |
| |
| #------------------------------------------------------------------- |
| # Test cases vacuum3-3.* converts a database large enough to include |
| # the locking page (in a test environment) between few different |
| # page sizes. |
| # |
| proc signature {} { |
| return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}] |
| } |
| do_test vacuum3-3.1 { |
| execsql " |
| PRAGMA page_size = 1024; |
| BEGIN; |
| CREATE TABLE abc(a PRIMARY KEY, b, c); |
| INSERT INTO abc VALUES(randomblob(100), randomblob(200), randomblob(1000)); |
| INSERT INTO abc |
| SELECT randomblob(1000), randomblob(200), randomblob(100) |
| FROM abc; |
| INSERT INTO abc |
| SELECT randomblob(100), randomblob(200), randomblob(1000) |
| FROM abc; |
| INSERT INTO abc |
| SELECT randomblob(100), randomblob(200), randomblob(1000) |
| FROM abc; |
| INSERT INTO abc |
| SELECT randomblob(100), randomblob(200), randomblob(1000) |
| FROM abc; |
| INSERT INTO abc |
| SELECT randomblob(100), randomblob(200), randomblob(1000) |
| FROM abc; |
| INSERT INTO abc |
| SELECT randomblob(25), randomblob(45), randomblob(9456) |
| FROM abc; |
| INSERT INTO abc |
| SELECT randomblob(100), randomblob(200), randomblob(1000) |
| FROM abc; |
| INSERT INTO abc |
| SELECT randomblob(25), randomblob(45), randomblob(9456) |
| FROM abc; |
| COMMIT; |
| " |
| } {} |
| do_test vacuum3-3.2 { |
| execsql { PRAGMA page_size } |
| } {1024} |
| |
| set ::sig [signature] |
| |
| set I 3 |
| foreach {request actual} [list \ |
| 2048 2048 \ |
| 1024 1024 \ |
| 1170 1024 \ |
| 256 1024 \ |
| 512 512 \ |
| 4096 4096 \ |
| 1024 1024 \ |
| ] { |
| do_test vacuum3-3.$I.1 { |
| execsql " |
| PRAGMA page_size = $request; |
| VACUUM; |
| " |
| execsql { PRAGMA page_size } |
| } $actual |
| do_test vacuum3-3.$I.2 { |
| signature |
| } $::sig |
| integrity_check vacuum3-3.$I.3 |
| |
| incr I |
| } |
| |
| do_test vacuum3-4.1 { |
| db close |
| file delete test.db |
| sqlite3 db test.db |
| execsql { |
| PRAGMA page_size=1024; |
| CREATE TABLE abc(a, b, c); |
| INSERT INTO abc VALUES(1, 2, 3); |
| INSERT INTO abc VALUES(4, 5, 6); |
| } |
| execsql { SELECT * FROM abc } |
| } {1 2 3 4 5 6} |
| do_test vacuum3-4.2 { |
| sqlite3 db2 test.db |
| execsql { SELECT * FROM abc } db2 |
| } {1 2 3 4 5 6} |
| do_test vacuum3-4.3 { |
| execsql { |
| PRAGMA page_size = 2048; |
| VACUUM; |
| } |
| execsql { SELECT * FROM abc } |
| } {1 2 3 4 5 6} |
| do_test vacuum3-4.4 { |
| execsql { SELECT * FROM abc } db2 |
| } {1 2 3 4 5 6} |
| do_test vacuum3-4.5 { |
| execsql { |
| PRAGMA page_size=16384; |
| VACUUM; |
| } db2 |
| execsql { SELECT * FROM abc } db2 |
| } {1 2 3 4 5 6} |
| do_test vacuum3-4.6 { |
| execsql { |
| PRAGMA page_size=1024; |
| VACUUM; |
| } |
| execsql { SELECT * FROM abc } db2 |
| } {1 2 3 4 5 6} |
| |
| # Unable to change the page-size of an in-memory using vacuum. |
| db2 close |
| sqlite3 db2 :memory: |
| do_test vacuum3-5.1 { |
| db2 eval { |
| CREATE TABLE t1(x); |
| INSERT INTO t1 VALUES(1234); |
| PRAGMA page_size=4096; |
| VACUUM; |
| SELECT * FROM t1; |
| } |
| } {1234} |
| do_test vacuum3-5.2 { |
| db2 eval { |
| PRAGMA page_size |
| } |
| } {1024} |
| |
| set create_database_sql { |
| BEGIN; |
| CREATE TABLE t1(a, b, c); |
| INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); |
| INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600)); |
| CREATE TABLE t2 AS SELECT * FROM t1; |
| CREATE TABLE t3 AS SELECT * FROM t1; |
| COMMIT; |
| DROP TABLE t2; |
| } |
| |
| do_ioerr_test vacuum3-ioerr-1 -cksum true -sqlprep " |
| PRAGMA page_size = 1024; |
| $create_database_sql |
| " -sqlbody { |
| PRAGMA page_size = 4096; |
| VACUUM; |
| } |
| do_ioerr_test vacuum3-ioerr-2 -cksum true -sqlprep " |
| PRAGMA page_size = 2048; |
| $create_database_sql |
| " -sqlbody { |
| PRAGMA page_size = 512; |
| VACUUM; |
| } |
| |
| ifcapable autovacuum { |
| do_ioerr_test vacuum3-ioerr-3 -cksum true -sqlprep " |
| PRAGMA auto_vacuum = 0; |
| $create_database_sql |
| " -sqlbody { |
| PRAGMA auto_vacuum = 1; |
| VACUUM; |
| } |
| do_ioerr_test vacuum3-ioerr-4 -cksum true -sqlprep " |
| PRAGMA auto_vacuum = 1; |
| $create_database_sql |
| " -sqlbody { |
| PRAGMA auto_vacuum = 0; |
| VACUUM; |
| } |
| } |
| |
| source $testdir/malloc_common.tcl |
| if {$MEMDEBUG} { |
| do_malloc_test vacuum3-malloc-1 -sqlprep { |
| PRAGMA page_size = 2048; |
| BEGIN; |
| CREATE TABLE t1(a, b, c); |
| INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); |
| INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1; |
| INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600)); |
| CREATE TABLE t2 AS SELECT * FROM t1; |
| CREATE TABLE t3 AS SELECT * FROM t1; |
| COMMIT; |
| DROP TABLE t2; |
| } -sqlbody { |
| PRAGMA page_size = 512; |
| VACUUM; |
| } |
| do_malloc_test vacuum3-malloc-2 -sqlprep { |
| PRAGMA encoding=UTF16; |
| CREATE TABLE t1(a, b, c); |
| INSERT INTO t1 VALUES(1, 2, 3); |
| CREATE TABLE t2(x,y,z); |
| INSERT INTO t2 SELECT * FROM t1; |
| } -sqlbody { |
| VACUUM; |
| } |
| } |
| |
| finish_test |
