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