Debian Codegraph @

test/vacuum-into.test

raw
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
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 # If the VACUUM statement is disabled in the current build, skip all
19 # the tests in this file.
20 #
21 ifcapable {!vacuum} {
22 omit_test vacuum.test {Compiled with SQLITE_OMIT_VACUUM}
23 finish_test
24 return
25 }
26
27 forcedelete out.db
28 do_execsql_test vacuum-into-100 {
29 CREATE TABLE t1(
30 a INTEGER PRIMARY KEY,
31 b ANY,
32 c INT AS (b+1), --- See "2024-04-09" block
33 CHECK( typeof(b)!='integer' OR b>a-5 ) --- comment below
34 );
35 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
36 INSERT INTO t1(a,b) SELECT x, randomblob(600) FROM c;
37 CREATE INDEX t1b ON t1(b);
38 DELETE FROM t1 WHERE a%2;
39 SELECT count(*), sum(a), sum(length(b)) FROM t1;
40 } {50 2550 30000}
41
42 # Update 2024-04-09 for forum post eec177d68fe7fa2c.
43 #
44 # VACUUM INTO is sensitive to tables holding both generated columns
45 # and CHECK constraints.
46 #
47 # CHECK constraints are ignored for read-only databases in order to save
48 # memory (see check-in 34ddf02d3d21151b on 2014-05-21). But the xfer
49 # optimization normally only works if CHECK constraints match between the
50 # source and destination tables. So the xfer optimization was not
51 # working for VACUUM INTO when the source was a read-only database and the
52 # table held CHECK constraints. But if the table has generated columns,
53 # then the xfer optimization is required or else VACUUM will raise an
54 # error.
55 #
56 # Fix this by ignoring CHECK constraints when determining whether or not
57 # the xfer optimization can run while doing VACUUM.
58
59 do_execsql_test vacuum-into-110 {
60 VACUUM main INTO 'out.db';
61 } {}
62 sqlite3 db2 out.db
63 do_test vacuum-into-120 {
64 db2 eval {SELECT count(*), sum(a), sum(length(b)) FROM t1}
65 } {50 2550 30000}
66 do_catchsql_test vacuum-into-130 {
67 VACUUM INTO 'out.db';
68 } {1 {output file already exists}}
69 forcedelete out2.db
70 do_catchsql_test vacuum-into-140 {
71 VACUUM INTO 'out2.db';
72 } {0 {}}
73 do_catchsql_test vacuum-into-150 {
74 VACUUM INTO 'out2.db';
75 } {1 {output file already exists}}
76
77 do_catchsql_test vacuum-into-200 {
78 VACUUM main INTO ':memory:';
79 } {0 {}}
80
81 # The INTO argument can be an arbitrary expression.
82 #
83 do_execsql_test vacuum-into-300 {
84 CREATE TABLE t2(name TEXT);
85 INSERT INTO t2 VALUES(':memory:');
86 VACUUM main INTO (SELECT name FROM t2);
87 } {}
88 do_catchsql_test vacuum-into-310 {
89 VACUUM INTO null;
90 } {1 {non-text filename}}
91 do_catchsql_test vacuum-into-320 {
92 VACUUM INTO x;
93 } {1 {no such column: x}}
94 do_catchsql_test vacuum-into-330 {
95 VACUUM INTO t1.nosuchcol;
96 } {1 {no such column: t1.nosuchcol}}
97 do_catchsql_test vacuum-into-340 {
98 VACUUM INTO main.t1.nosuchcol;
99 } {1 {no such column: main.t1.nosuchcol}}
100
101 forcedelete test.db2
102 db func target target
103 proc target {} { return "test.db2" }
104 do_test vacuum-into-410 {
105 execsql { VACUUM INTO target() }
106 file exists test.db2
107 } 1
108 do_catchsql_test vacuum-into-420 {
109 VACUUM INTO target2()
110 } {1 {no such function: target2}}
111
112 # The ability to VACUUM INTO a read-only database
113 db close
114 if {$tcl_platform(platform) eq "windows"} {
115 file attributes test.db -readonly 1
116 } else {
117 file attributes test.db -permissions 292 ;# 292 == 0444
118 }
119 sqlite3 db test.db -readonly 1
120 forcedelete test.db2
121 do_execsql_test vacuum-into-500 {
122 VACUUM INTO 'test.db2';
123 }
124 if {$tcl_platform(platform) eq "windows"} {
125 file attributes test.db -readonly 0
126 } else {
127 file attributes test.db -permissions 420 ;# 420 = 0644
128 }
129 sqlite3 db2 test.db2
130 do_test vacuum-into-510 {
131 db2 eval {SELECT name FROM sqlite_master ORDER BY 1}
132 } {t1 t1b t2}
133 db2 close
134 db close
135
136 # Change the page-size on a VACUUM INTO even if the original
137 # database is in WAL mode.
138 #
139 if {[wal_is_capable]} {
140 forcedelete test.db
141 forcedelete test.db2
142 do_test vacuum-into-600 {
143 sqlite3 db test.db
144 db eval {
145 PRAGMA page_size=4096;
146 PRAGMA journal_mode=WAL;
147 CREATE TABLE t1(a);
148 INSERT INTO t1 VALUES(19);
149 CREATE INDEX t1a ON t1(a);
150 PRAGMA integrity_check;
151 }
152 } {wal ok}
153 do_execsql_test vacuum-into-610 {
154 PRAGMA page_size;
155 } {4096}
156 do_execsql_test vacuum-into-620 {
157 PRAGMA page_size=1024;
158 VACUUM INTO 'test.db2';
159 } {}
160 do_test vacuum-into-630 {
161 sqlite3 db test.db2
162 db eval {
163 PRAGMA page_size;
164 PRAGMA integrity_check;
165 }
166 } {1024 ok}
167 }
168
169 #-------------------------------------------------------------------------
170
171 testvfs tvfs -default 1
172 tvfs filter xSync
173 tvfs script xSyncCb
174 proc xSyncCb {method file fileid flags} {
175 incr ::sync($flags)
176 }
177
178 reset_db
179
180 do_execsql_test vacuum-into-700 {
181 CREATE TABLE t1(a, b);
182 INSERT INTO t1 VALUES(1, 2);
183 }
184
185 foreach {tn pragma res} {
186 710 {
187 PRAGMA synchronous = normal
188 } {normal 2}
189 720 {
190 PRAGMA synchronous = full
191 } {normal 3}
192 730 {
193 PRAGMA synchronous = off
194 } {}
195 740 {
196 PRAGMA synchronous = extra;
197 } {normal 3}
198 750 {
199 PRAGMA fullfsync = 1;
200 PRAGMA synchronous = full;
201 } {full|dataonly 1 full 2}
202 } {
203
204 forcedelete test.db2
205 array unset ::sync
206 do_execsql_test vacuum-into-$tn.1 "
207 $pragma ;
208 VACUUM INTO 'test.db2'
209 "
210
211 do_test vacuum-into-$tn.2 {
212 array get ::sync
213 } $res
214 }
215
216 db close
217 tvfs delete
218
219
220 finish_test