xref: /sqlite-3.40.0/test/vacuum-into.test (revision 80b30f99) 
1# 2018-12-07
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 INTO statement.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18# If the VACUUM statement is disabled in the current build, skip all
19# the tests in this file.
20#
21ifcapable {!vacuum} {
22  omit_test vacuum.test {Compiled with SQLITE_OMIT_VACUUM}
23  finish_test
24  return
25}
26
27forcedelete out.db
28do_execsql_test vacuum-into-100 {
29  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
30  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
31  INSERT INTO t1(a,b) SELECT x, randomblob(600) FROM c;
32  CREATE INDEX t1b ON t1(b);
33  DELETE FROM t1 WHERE a%2;
34  SELECT count(*), sum(a), sum(length(b)) FROM t1;
35} {50 2550 30000}
36do_execsql_test vacuum-into-110 {
37  VACUUM main INTO 'out.db';
38} {}
39sqlite3 db2 out.db
40do_test vacuum-into-120 {
41  db2 eval {SELECT count(*), sum(a), sum(length(b)) FROM t1}
42} {50 2550 30000}
43do_catchsql_test vacuum-into-130 {
44  VACUUM INTO 'out.db';
45} {1 {output file already exists}}
46forcedelete out2.db
47do_catchsql_test vacuum-into-140 {
48  VACUUM INTO 'out2.db';
49} {0 {}}
50do_catchsql_test vacuum-into-150 {
51  VACUUM INTO 'out2.db';
52} {1 {output file already exists}}
53
54do_catchsql_test vacuum-into-200 {
55  VACUUM main INTO ':memory:';
56} {0 {}}
57
58# The INTO argument can be an arbitrary expression.
59#
60do_execsql_test vacuum-into-300 {
61  CREATE TABLE t2(name TEXT);
62  INSERT INTO t2 VALUES(':memory:');
63  VACUUM main INTO (SELECT name FROM t2);
64} {}
65do_catchsql_test vacuum-into-310 {
66  VACUUM INTO null;
67} {1 {non-text filename}}
68do_catchsql_test vacuum-into-320 {
69  VACUUM INTO x;
70} {1 {no such column: x}}
71do_catchsql_test vacuum-into-330 {
72  VACUUM INTO t1.nosuchcol;
73} {1 {no such column: t1.nosuchcol}}
74do_catchsql_test vacuum-into-340 {
75  VACUUM INTO main.t1.nosuchcol;
76} {1 {no such column: main.t1.nosuchcol}}
77
78forcedelete test.db2
79db func target target
80proc target {} { return "test.db2" }
81do_test vacuum-into-410 {
82  execsql { VACUUM INTO target() }
83  file exists test.db2
84} 1
85do_catchsql_test vacuum-into-420 {
86  VACUUM INTO target2()
87} {1 {no such function: target2}}
88
89# The ability to VACUUM INTO a read-only database
90db close
91sqlite3 db test.db -readonly 1
92forcedelete test.db2
93do_execsql_test vacuum-into-500 {
94  VACUUM INTO 'test.db2';
95}
96sqlite3 db2 test.db2
97do_test vacuum-into-510 {
98  db2 eval {SELECT name FROM sqlite_master ORDER BY 1}
99} {t1 t1b t2}
100db2 close
101db close
102
103# Change the page-size on a VACUUM INTO even if the original
104# database is in WAL mode.
105#
106if {[wal_is_capable]} {
107  forcedelete test.db
108  forcedelete test.db2
109  do_test vacuum-into-600 {
110    sqlite3 db test.db
111    db eval {
112      PRAGMA page_size=4096;
113      PRAGMA journal_mode=WAL;
114      CREATE TABLE t1(a);
115      INSERT INTO t1 VALUES(19);
116      CREATE INDEX t1a ON t1(a);
117      PRAGMA integrity_check;
118    }
119  } {wal ok}
120  do_execsql_test vacuum-into-610 {
121    PRAGMA page_size;
122  } {4096}
123  do_execsql_test vacuum-into-620 {
124    PRAGMA page_size=1024;
125    VACUUM INTO 'test.db2';
126  } {}
127  do_test vacuum-into-630 {
128    sqlite3 db test.db2
129    db eval {
130      PRAGMA page_size;
131      PRAGMA integrity_check;
132    }
133  } {1024 ok}
134}
135
136#-------------------------------------------------------------------------
137
138testvfs tvfs -default 1
139tvfs filter xSync
140tvfs script xSyncCb
141proc xSyncCb {method file fileid flags} {
142  incr ::sync($flags)
143}
144
145reset_db
146
147do_execsql_test vacuum-into-700 {
148  CREATE TABLE t1(a, b);
149  INSERT INTO t1 VALUES(1, 2);
150}
151
152foreach {tn pragma res} {
153  710 {
154    PRAGMA synchronous = normal
155  } {normal 2}
156  720 {
157    PRAGMA synchronous = full
158  } {normal 3}
159  730 {
160    PRAGMA synchronous = off
161  } {}
162  740 {
163    PRAGMA synchronous = extra;
164  } {normal 3}
165  750 {
166    PRAGMA fullfsync = 1;
167    PRAGMA synchronous = full;
168  } {full|dataonly 1 full 2}
169} {
170
171  forcedelete test.db2
172  array unset ::sync
173  do_execsql_test vacuum-into-$tn.1 "
174    $pragma ;
175    VACUUM INTO 'test.db2'
176  "
177
178  do_test vacuum-into-$tn.2 {
179    array get ::sync
180  } $res
181}
182
183db close
184tvfs delete
185
186
187finish_test
188
189
190
191