xref: /sqlite-3.40.0/test/vacuum5.test (revision 69aedc8d) 
1# 2016-08-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#
12# This file implements a test for VACUUM on attached databases.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set testprefix vacuum5
18
19# If the VACUUM statement is disabled in the current build, skip all
20# the tests in this file.
21#
22ifcapable !vacuum {
23  finish_test
24  return
25}
26
27forcedelete test2.db test3.db
28do_execsql_test vacuum5-1.1 {
29  PRAGMA auto_vacuum = 0;
30  CREATE TABLE main.t1(a,b);
31  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
32    INSERT INTO t1(a,b) SELECT x, randomblob(1000) FROM c;
33  CREATE TEMP TABLE ttemp(x,y);
34  INSERT INTO ttemp SELECT * FROM t1;
35  ATTACH 'test2.db' AS x2;
36  ATTACH 'test3.db' AS x3;
37  CREATE TABLE x2.t2(c,d);
38  INSERT INTO t2 SELECT * FROM t1;
39  CREATE TABLE x3.t3(e,f);
40  INSERT INTO t3 SELECT * FROM t1;
41  DELETE FROM t1 WHERE (rowid%3)!=0;
42  DELETE FROM t2 WHERE (rowid%4)!=0;
43  DELETE FROM t3 WHERE (rowid%5)!=0;
44  PRAGMA main.integrity_check;
45  PRAGMA x2.integrity_check;
46  PRAGMA x3.integrity_check;
47} {ok ok ok}
48set size1 [file size test.db]
49set size2 [file size test2.db]
50set size3 [file size test3.db]
51
52do_execsql_test vacuum5-1.2.1 {
53  VACUUM main;
54} {}
55do_test vacuum5-1.2.2 {
56  expr {[file size test.db]<$size1}
57} {1}
58do_test vacuum5-1.2.3 {
59  file size test2.db
60} $size2
61do_test vacuum5-1.2.4 {
62  file size test3.db
63} $size3
64set size1 [file size test.db]
65do_execsql_test vacuum-1.2.5 {
66  DELETE FROM t1;
67  PRAGMA main.integrity_check;
68} {ok}
69
70do_execsql_test vacuum5-1.3.1 {
71  VACUUM x2;
72} {}
73do_test vacuum5-1.3.2 {
74  file size test.db
75} $size1
76do_test vacuum5-1.3.3 {
77  expr {[file size test2.db]<$size2}
78} 1
79do_test vacuum5-1.3.4 {
80  file size test3.db
81} $size3
82set size2 [file size test2.db]
83do_execsql_test vacuum-1.3.5 {
84  DELETE FROM t2;
85  PRAGMA x2.integrity_check;
86} {ok}
87
88do_execsql_test vacuum5-1.4.1 {
89  VACUUM x3;
90} {}
91do_test vacuum5-1.3.2 {
92  file size test.db
93} $size1
94do_test vacuum5-1.3.3 {
95  file size test2.db
96} $size2
97do_test vacuum5-1.3.4 {
98  expr {[file size test3.db]<$size3}
99} 1
100
101# VACUUM is a no-op on the TEMP table
102#
103set sizeTemp [db one {PRAGMA temp.page_count}]
104do_execsql_test vacuum5-1.4.1 {
105  VACUUM temp;
106} {}
107do_execsql_test vacuum5-1.4.2 {
108  PRAGMA temp.page_count;
109} $sizeTemp
110
111do_catchsql_test vacuum5-2.0 {
112  VACUUM olaf;
113} {1 {unknown database olaf}}
114
115#-------------------------------------------------------------------------
116# Test that a temp file is opened as part of VACUUM.
117#
118if {$::TEMP_STORE<3 && [permutation]!="inmemory_journal"} {
119  db close
120  testvfs tvfs
121  tvfs filter xOpen
122  tvfs script open_cb
123  forcedelete test.db
124
125  set ::openfiles [list]
126  proc open_cb {method args} {
127    lappend ::openfiles [file tail [lindex $args 0]]
128  }
129  sqlite3 db test.db -vfs tvfs
130
131  do_execsql_test 3.0 {
132    PRAGMA temp_store = file;
133    PRAGMA page_size = 1024;
134    PRAGMA cache_size = 50;
135    CREATE TABLE t1(i INTEGER PRIMARY KEY, j UNIQUE);
136    WITH s(i) AS (
137      VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<1000
138    )
139    INSERT INTO t1 SELECT NULL, randomblob(100) FROM s;
140  }
141
142  do_execsql_test 3.1 { VACUUM }
143
144  db close
145  tvfs delete
146  if {[atomic_batch_write test.db]==0} {
147    do_test 3.2 {
148      lrange $::openfiles 0 4
149    } {test.db test.db-journal test.db-journal {} test.db-journal}
150  }
151}
152
153
154
155finish_test
156