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.6 2009/07/25 13:42:50 danielk1977 Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# If this build of the library does not support auto-vacuum, omit this
20# whole file.
21ifcapable {!autovacuum || !pragma} {
22  finish_test
23  return
24}
25
26set testprefix incrvacuum2
27
28# Create a database in incremental vacuum mode that has many
29# pages on the freelist.
30#
31do_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#
44do_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#
53do_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#
62do_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#
71ifcapable attach {
72  do_test incrvacuum2-2.1 {
73    file delete -force 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
117do_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} {}
126do_test incrvacuum2-3.2 {
127  execsql {
128    BEGIN;
129    DELETE FROM abc;
130    PRAGMA incremental_vacuum;
131    COMMIT;
132  }
133} {}
134
135integrity_check incrvacuum2-3.3
136
137ifcapable 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  } {1640}
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  } {2176}
209}
210
211finish_test
212