Debian Codegraph @

test/resetdb.test

raw
1 # 2018-04-28
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 # Test cases for SQLITE_DBCONFIG_RESET_DATABASE
12 #
13
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix resetdb
17
18 do_not_use_codec
19
20 ifcapable !vtab||!compound {
21 finish_test
22 return
23 }
24
25 # In the "inmemory_journal" permutation, each new connection executes
26 # "PRAGMA journal_mode = memory". This fails with SQLITE_BUSY if attempted
27 # on a wal mode database with existing connections. For this and a few
28 # other reasons, this test is not run as part of "inmemory_journal".
29 #
30 # Permutation "journaltest" does not support wal mode.
31 #
32 if {[permutation]=="inmemory_journal"
33 || [permutation]=="journaltest"
34 } {
35 finish_test
36 return
37 }
38
39 # Create a sample database
40 do_execsql_test 100 {
41 PRAGMA auto_vacuum = 0;
42 PRAGMA page_size=4096;
43 CREATE TABLE t1(a,b);
44 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
45 INSERT INTO t1(a,b) SELECT x, randomblob(300) FROM c;
46 CREATE INDEX t1a ON t1(a);
47 CREATE INDEX t1b ON t1(b);
48 SELECT sum(a), sum(length(b)) FROM t1;
49 PRAGMA integrity_check;
50 PRAGMA journal_mode;
51 PRAGMA page_count;
52 } {210 6000 ok delete 8}
53
54 # Verify that the same content is seen from a separate database connection
55 sqlite3 db2 test.db
56 do_test 110 {
57 execsql {
58 SELECT sum(a), sum(length(b)) FROM t1;
59 PRAGMA integrity_check;
60 PRAGMA journal_mode;
61 PRAGMA page_count;
62 } db2
63 } {210 6000 ok delete 8}
64
65 do_test 200 {
66 # Thoroughly corrupt the database file by overwriting the first
67 # page with randomness.
68 sqlite3_db_config db DEFENSIVE 0
69 catchsql {
70 UPDATE sqlite_dbpage SET data=randomblob(4096) WHERE pgno=1;
71 PRAGMA quick_check;
72 }
73 } {1 {file is not a database}}
74 do_test 201 {
75 catchsql {
76 PRAGMA quick_check;
77 } db2
78 } {1 {file is not a database}}
79
80 do_test 210 {
81 # Reset the database file using SQLITE_DBCONFIG_RESET_DATABASE
82 sqlite3_db_config db RESET_DB 1
83 db eval VACUUM
84 sqlite3_db_config db RESET_DB 0
85
86 # If using sqlite3_prepare() instead of _v2() or _v3(), the block
87 # below raises an SQLITE_SCHEMA error. The following fixes this.
88 if {[permutation]=="prepare"} { catchsql "SELECT * FROM sqlite_master" db2 }
89
90 # Verify that the reset took, even on the separate database connection
91 catchsql {
92 PRAGMA page_count;
93 PRAGMA page_size;
94 PRAGMA quick_check;
95 PRAGMA journal_mode;
96 } db2
97 } {0 {1 4096 ok delete}}
98
99 # Delete the old connections and database and start over again
100 # with a different page size and in WAL mode.
101 #
102 db close
103 db2 close
104 forcedelete test.db
105 sqlite3 db test.db
106 do_execsql_test 300 {
107 PRAGMA auto_vacuum = 0;
108 PRAGMA page_size=8192;
109 PRAGMA journal_mode=WAL;
110 CREATE TABLE t1(a,b);
111 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
112 INSERT INTO t1(a,b) SELECT x, randomblob(1300) FROM c;
113 CREATE INDEX t1a ON t1(a);
114 CREATE INDEX t1b ON t1(b);
115 SELECT sum(a), sum(length(b)) FROM t1;
116 PRAGMA integrity_check;
117 PRAGMA journal_mode;
118 PRAGMA page_size;
119 PRAGMA page_count;
120 } {wal 210 26000 ok wal 8192 12}
121 sqlite3 db2 test.db
122 do_test 310 {
123 execsql {
124 SELECT sum(a), sum(length(b)) FROM t1;
125 PRAGMA integrity_check;
126 PRAGMA journal_mode;
127 PRAGMA page_size;
128 PRAGMA page_count;
129 } db2
130 } {210 26000 ok wal 8192 12}
131
132 # Corrupt the database again
133 sqlite3_db_config db DEFENSIVE 0
134 do_catchsql_test 320 {
135 UPDATE sqlite_dbpage SET data=randomblob(8192) WHERE pgno=1;
136 PRAGMA quick_check
137 } {1 {file is not a database}}
138
139 do_test 330 {
140 catchsql {
141 PRAGMA quick_check
142 } db2
143 } {1 {file is not a database}}
144
145 db2 cache flush ;# Required by permutation "prepare".
146
147 # Reset the database yet again. Verify that the page size and
148 # journal mode are preserved.
149 #
150 do_test 400 {
151 sqlite3_db_config db RESET_DB 1
152 db eval VACUUM
153 sqlite3_db_config db RESET_DB 0
154 catchsql {
155 PRAGMA page_count;
156 PRAGMA page_size;
157 PRAGMA journal_mode;
158 PRAGMA quick_check;
159 } db2
160 } {0 {1 8192 wal ok}}
161 db2 close
162
163 # Reset the database yet again. This time immediately after it is closed
164 # and reopened. So that the VACUUM is the first statement run.
165 #
166 db close
167 sqlite3 db test.db
168 do_test 500 {
169 sqlite3_finalize [
170 sqlite3_prepare db "SELECT 1 FROM sqlite_master LIMIT 1" -1 tail
171 ]
172 sqlite3_db_config db RESET_DB 1
173 db eval VACUUM
174 sqlite3_db_config db RESET_DB 0
175 sqlite3 db2 test.db
176 catchsql {
177 PRAGMA page_count;
178 PRAGMA page_size;
179 PRAGMA journal_mode;
180 PRAGMA quick_check;
181 } db2
182 } {0 {1 8192 wal ok}}
183 db2 close
184
185 #-------------------------------------------------------------------------
186 reset_db
187 sqlite3 db2 test.db
188 do_execsql_test 600 {
189 PRAGMA journal_mode = wal;
190 CREATE TABLE t1(a);
191 INSERT INTO t1 VALUES(1), (2), (3), (4);
192 } {wal}
193
194 do_execsql_test -db db2 610 {
195 SELECT * FROM t1
196 } {1 2 3 4}
197
198 do_test 620 {
199 set res [list]
200 db2 eval {SELECT a FROM t1} {
201 lappend res $a
202 if {$a==3} {
203 sqlite3_db_config db RESET_DB 1
204 db eval VACUUM
205 sqlite3_db_config db RESET_DB 0
206 }
207 }
208
209 set res
210 } {1 2 3 4}
211
212 do_execsql_test -db db2 630 {
213 SELECT * FROM sqlite_master
214 } {}
215
216 #-------------------------------------------------------------------------
217 db2 close
218 reset_db
219
220 do_execsql_test 700 {
221 PRAGMA page_size=512;
222 PRAGMA auto_vacuum = 0;
223 CREATE TABLE t1(a,b,c);
224 CREATE INDEX t1a ON t1(a);
225 CREATE INDEX t1bc ON t1(b,c);
226 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)
227 INSERT INTO t1(a,b,c) SELECT x, randomblob(100),randomblob(100) FROM c;
228 PRAGMA page_count;
229 PRAGMA integrity_check;
230 } {19 ok}
231
232 if {[nonzero_reserved_bytes]} {
233 finish_test
234 return
235 }
236
237 sqlite3_db_config db DEFENSIVE 0
238 do_execsql_test 710 {
239 UPDATE sqlite_dbpage SET data=
240 X'53514C69746520666F726D61742033000200030100402020000000000000001300000000000000000000000300000004000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000D00000003017C0001D801AC017C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002E03061715110145696E6465787431626374310443524541544520494E4445582074316263204F4E20743128622C63292A0206171311013F696E64657874316174310343524541544520494E44455820743161204F4E20743128612926010617111101397461626C657431743102435245415445205441424C4520743128612C622C6329' WHERE pgno=1;
241 }
242
243 do_execsql_test 720 {
244 PRAGMA integrity_check;
245 } {ok}
246
247 do_test 730 {
248 sqlite3_db_config db RESET_DB 1
249 db eval VACUUM
250 sqlite3_db_config db RESET_DB 0
251 } {0}
252
253 do_execsql_test 740 {
254 PRAGMA page_count;
255 PRAGMA integrity_check;
256 } {1 ok}
257
258 #-------------------------------------------------------------------------
259 ifcapable utf16 {
260 reset_db
261 do_execsql_test 800 {
262 PRAGMA encoding = 'utf8';
263 CREATE TABLE t1(a, b);
264 PRAGMA encoding;
265 } {UTF-8}
266
267 db close
268 sqlite3 db test.db
269
270 sqlite3 db2 test.db
271 do_execsql_test -db db2 810 {
272 CREATE TEMP TABLE t2(x);
273 INSERT INTO t2 VALUES('hello world');
274 SELECT name FROM sqlite_schema;
275 } {t1}
276 do_test 820 {
277 db eval "PRAGMA encoding = 'utf16'"
278 sqlite3_db_config db RESET_DB 1
279 } {1}
280 do_test 830 {
281 db eval VACUUM
282 sqlite3_db_config db RESET_DB 0
283 } {0}
284 do_test 840 {
285 string range [db eval {
286 CREATE TABLE t1(a, b);
287 INSERT INTO t1 VALUES('one', 'two');
288 PRAGMA encoding;
289 }] 0 5
290 } {UTF-16}
291
292 do_test 850 {
293 catchsql { SELECT * FROM t1; } db2
294 } {1 {attached databases must use the same text encoding as main database}}
295 do_test 860 {
296 catchsql { SELECT * FROM t2; } db2
297 } {1 {attached databases must use the same text encoding as main database}}
298 }
299
300 finish_test