xref: /sqlite-3.40.0/test/vacuum2.test (revision 8a29dfde) 
1# 2005 February 15
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 VACUUM statement.
13#
14# $Id: vacuum2.test,v 1.7 2008/01/17 02:36:28 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# If the VACUUM statement is disabled in the current build, skip all
20# the tests in this file.
21#
22ifcapable {!vacuum||!autoinc} {
23  finish_test
24  return
25}
26if $AUTOVACUUM {
27  finish_test
28  return
29}
30
31# Ticket #1121 - make sure vacuum works if all autoincrement tables
32# have been deleted.
33#
34do_test vacuum2-1.1 {
35  execsql {
36    CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
37    DROP TABLE t1;
38    VACUUM;
39  }
40} {}
41
42# Ticket #2518.  Make sure vacuum increments the change counter
43# in the database header.
44#
45do_test vacuum2-2.1 {
46  execsql {
47    CREATE TABLE t1(x);
48    CREATE TABLE t2(y);
49    INSERT INTO t1 VALUES(1);
50  }
51  hexio_get_int [hexio_read test.db 24 4]
52} [expr {[hexio_get_int [hexio_read test.db 24 4]]+3}]
53do_test vacuum2-2.1 {
54  execsql {
55    VACUUM
56  }
57  hexio_get_int [hexio_read test.db 24 4]
58} [expr {[hexio_get_int [hexio_read test.db 24 4]]+1}]
59
60############################################################################
61# Verify that we can use the auto_vacuum pragma to request a new
62# autovacuum setting, do a VACUUM, and the new setting takes effect.
63# Make sure this happens correctly even if there are multiple open
64# connections to the same database file.
65#
66sqlite3 db2 test.db
67set pageSize [db eval {pragma page_size}]
68
69# We are currently not autovacuuming so the database should be 3 pages
70# in size.  1 page for each of sqlite_master, t1, and t2.
71#
72do_test vacuum2-3.1 {
73  execsql {
74    INSERT INTO t1 VALUES('hello');
75    INSERT INTO t2 VALUES('out there');
76  }
77  expr {[file size test.db]/$pageSize}
78} {3}
79set cksum [cksum]
80do_test vacuum2-3.2 {
81  cksum db2
82} $cksum
83
84# Convert the database to an autovacuumed database.
85do_test vacuum2-3.3 {
86  execsql {
87    PRAGMA auto_vacuum=FULL;
88    VACUUM;
89  }
90  expr {[file size test.db]/$pageSize}
91} {4}
92do_test vacuum2-3.4 {
93  cksum db2
94} $cksum
95do_test vacuum2-3.5 {
96  cksum
97} $cksum
98do_test vacuum2-3.6 {
99  execsql {PRAGMA integrity_check} db2
100} {ok}
101do_test vacuum2-3.7 {
102  execsql {PRAGMA integrity_check} db
103} {ok}
104
105# Convert the database back to a non-autovacuumed database.
106do_test vacuum2-3.13 {
107  execsql {
108    PRAGMA auto_vacuum=NONE;
109    VACUUM;
110  }
111  expr {[file size test.db]/$pageSize}
112} {3}
113do_test vacuum2-3.14 {
114  cksum db2
115} $cksum
116do_test vacuum2-3.15 {
117  cksum
118} $cksum
119do_test vacuum2-3.16 {
120  execsql {PRAGMA integrity_check} db2
121} {ok}
122do_test vacuum2-3.17 {
123  execsql {PRAGMA integrity_check} db
124} {ok}
125
126db2 close
127
128finish_test
129