xref: /sqlite-3.40.0/test/vacuum3.test (revision 11e29677)

# 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;
  }
  ifcapable altertable {
    execsql { ALTER TABLE t1 ADD COLUMN d; }
  } else {
    execsql {
      DROP TABLE t1;
      CREATE TABLE t1(a, b, c, d);
      INSERT INTO t1 VALUES(1, 2, 3, NULL);
    }
  }
  execsql {
    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
  delete_file 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