Return to incrvacuum2.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    1: # 2007 May 04
    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 testing the incremental vacuum feature.
   13: #
   14: # $Id: incrvacuum2.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
   15: 
   16: set testdir [file dirname $argv0]
   17: source $testdir/tester.tcl
   18: 
   19: # If this build of the library does not support auto-vacuum, omit this
   20: # whole file.
   21: ifcapable {!autovacuum || !pragma} {
   22:   finish_test
   23:   return
   24: }
   25: 
   26: set testprefix incrvacuum2
   27: 
   28: # Create a database in incremental vacuum mode that has many
   29: # pages on the freelist.
   30: #
   31: do_test incrvacuum2-1.1 {
   32:   execsql {
   33:     PRAGMA page_size=1024;
   34:     PRAGMA auto_vacuum=incremental;
   35:     CREATE TABLE t1(x);
   36:     INSERT INTO t1 VALUES(zeroblob(30000));
   37:     DELETE FROM t1;
   38:   }
   39:   file size test.db
   40: } {32768}
   41: 
   42: # Vacuum off a single page.
   43: #
   44: do_test incrvacuum2-1.2 {
   45:   execsql {
   46:     PRAGMA incremental_vacuum(1);
   47:   }
   48:   file size test.db
   49: } {31744}
   50: 
   51: # Vacuum off five pages
   52: #
   53: do_test incrvacuum2-1.3 {
   54:   execsql {
   55:     PRAGMA incremental_vacuum(5);
   56:   }
   57:   file size test.db
   58: } {26624}
   59: 
   60: # Vacuum off all the rest
   61: #
   62: do_test incrvacuum2-1.4 {
   63:   execsql {
   64:     PRAGMA incremental_vacuum(1000);
   65:   }
   66:   file size test.db
   67: } {3072}
   68: 
   69: # Make sure incremental vacuum works on attached databases.
   70: #
   71: ifcapable attach {
   72:   do_test incrvacuum2-2.1 {
   73:     forcedelete test2.db test2.db-journal
   74:     execsql {
   75:       ATTACH DATABASE 'test2.db' AS aux;
   76:       PRAGMA aux.auto_vacuum=incremental;
   77:       CREATE TABLE aux.t2(x);
   78:       INSERT INTO t2 VALUES(zeroblob(30000));
   79:       INSERT INTO t1 SELECT * FROM t2;
   80:       DELETE FROM t2;
   81:       DELETE FROM t1;
   82:     }
   83:     list [file size test.db] [file size test2.db]
   84:   } {32768 32768}
   85:   do_test incrvacuum2-2.2 {
   86:     execsql {
   87:       PRAGMA aux.incremental_vacuum(1)
   88:     }
   89:     list [file size test.db] [file size test2.db]
   90:   } {32768 31744}
   91:   do_test incrvacuum2-2.3 {
   92:     execsql {
   93:       PRAGMA aux.incremental_vacuum(5)
   94:     }
   95:     list [file size test.db] [file size test2.db]
   96:   } {32768 26624}
   97:   do_test incrvacuum2-2.4 {
   98:     execsql {
   99:       PRAGMA main.incremental_vacuum(5)
  100:     }
  101:     list [file size test.db] [file size test2.db]
  102:   } {27648 26624}
  103:   do_test incrvacuum2-2.5 {
  104:     execsql {
  105:       PRAGMA aux.incremental_vacuum
  106:     }
  107:     list [file size test.db] [file size test2.db]
  108:   } {27648 3072}
  109:   do_test incrvacuum2-2.6 {
  110:     execsql {
  111:       PRAGMA incremental_vacuum(1)
  112:     }
  113:     list [file size test.db] [file size test2.db]
  114:   } {26624 3072}
  115: }
  116: 
  117: do_test incrvacuum2-3.1 {
  118:   execsql {
  119:     PRAGMA auto_vacuum = 'full';
  120:     BEGIN;
  121:     CREATE TABLE abc(a);
  122:     INSERT INTO abc VALUES(randstr(1500,1500));
  123:     COMMIT;
  124:   }
  125: } {}
  126: do_test incrvacuum2-3.2 {
  127:   execsql {
  128:     BEGIN;
  129:     DELETE FROM abc;
  130:     PRAGMA incremental_vacuum;
  131:     COMMIT;
  132:   }
  133: } {}
  134: 
  135: integrity_check incrvacuum2-3.3
  136: 
  137: ifcapable wal {
  138:   # At one point, when a specific page was being extracted from the b-tree
  139:   # free-list (e.g. during an incremental-vacuum), all trunk pages that
  140:   # occurred before the specific page in the free-list trunk were being
  141:   # written to the journal or wal file. This is not necessary. Only the 
  142:   # extracted page and the page that contains the pointer to it need to
  143:   # be journalled.
  144:   #
  145:   # This problem was fixed by [d03d63d77e] (just before 3.7.6 release).
  146:   #
  147:   # This test case builds a database containing many free pages. Then runs
  148:   # "PRAGMA incremental_vacuum(1)" until the db contains zero free pages.
  149:   # Each "PRAGMA incremental_vacuum(1)" should modify at most 4 pages. The
  150:   # worst case is when a trunk page is removed from the end of the db file.
  151:   # In this case pages written are:
  152:   #
  153:   #   1. The previous trunk page (that contains a pointer to the recycled
  154:   #      trunk page), and
  155:   #   2. The leaf page transformed into a trunk page to replace the recycled
  156:   #      page, and
  157:   #   3. The trunk page that contained a pointer to the leaf page used 
  158:   #      in (2), and
  159:   #   4. Page 1. Page 1 is always updated, even in WAL mode, since it contains
  160:   #      the "number of free-list pages" field.
  161:   #
  162:   db close
  163:   forcedelete test.db
  164:   sqlite3 db test.db
  165: 
  166:   do_execsql_test 4.1 {
  167:     PRAGMA page_size = 512;
  168:     PRAGMA auto_vacuum = 2;
  169:     CREATE TABLE t1(x);
  170:     INSERT INTO t1 VALUES(randomblob(400));
  171:     INSERT INTO t1 SELECT * FROM t1;            --    2
  172:     INSERT INTO t1 SELECT * FROM t1;            --    4
  173:     INSERT INTO t1 SELECT * FROM t1;            --    8
  174:     INSERT INTO t1 SELECT * FROM t1;            --   16
  175:     INSERT INTO t1 SELECT * FROM t1;            --   32
  176:     INSERT INTO t1 SELECT * FROM t1;            --  128
  177:     INSERT INTO t1 SELECT * FROM t1;            --  256
  178:     INSERT INTO t1 SELECT * FROM t1;            --  512
  179:     INSERT INTO t1 SELECT * FROM t1;            -- 1024
  180:     INSERT INTO t1 SELECT * FROM t1;            -- 2048
  181:     INSERT INTO t1 SELECT * FROM t1;            -- 4096
  182:     INSERT INTO t1 SELECT * FROM t1;            -- 8192
  183:     DELETE FROM t1 WHERE oid>512;
  184:     DELETE FROM t1;
  185:   }
  186: 
  187:   do_test 4.2 {
  188:     execsql { 
  189:       PRAGMA journal_mode = WAL;
  190:       PRAGMA incremental_vacuum(1);
  191:       PRAGMA wal_checkpoint;
  192:     }
  193:     file size test.db-wal
  194:   } [expr {32+2*(512+24)}]
  195: 
  196:   do_test 4.3 {
  197:     db close
  198:     sqlite3 db test.db
  199:     set maxsz 0
  200:     while {[file size test.db] > [expr 512*3]} {
  201:       execsql { PRAGMA journal_mode = WAL }
  202:       execsql { PRAGMA wal_checkpoint }
  203:       execsql { PRAGMA incremental_vacuum(1) }
  204:       set newsz [file size test.db-wal]
  205:       if {$newsz>$maxsz} {set maxsz $newsz}
  206:     }
  207:     set maxsz 
  208:   } [expr {32+3*(512+24)}]
  209: }
  210: 
  211: finish_test