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   file delete 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