wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2002 March 6 |
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. |
||
12 | # |
||
13 | # This file implements tests for the PRAGMA command. |
||
14 | # |
||
15 | # $Id: pragma.test,v 1.73 2009/01/12 14:01:45 danielk1977 Exp $ |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | # Do not use a codec for tests in this file, as the database file is |
||
21 | # manipulated directly using tcl scripts (using the [hexio_write] command). |
||
22 | # |
||
23 | do_not_use_codec |
||
24 | |||
25 | # Test organization: |
||
26 | # |
||
27 | # pragma-1.*: Test cache_size, default_cache_size and synchronous on main db. |
||
28 | # pragma-2.*: Test synchronous on attached db. |
||
29 | # pragma-3.*: Test detection of table/index inconsistency by integrity_check. |
||
30 | # pragma-4.*: Test cache_size and default_cache_size on attached db. |
||
31 | # pragma-5.*: Test that pragma synchronous may not be used inside of a |
||
32 | # transaction. |
||
33 | # pragma-6.*: Test schema-query pragmas. |
||
34 | # pragma-7.*: Miscellaneous tests. |
||
35 | # pragma-8.*: Test user_version and schema_version pragmas. |
||
36 | # pragma-9.*: Test temp_store and temp_store_directory. |
||
37 | # pragma-10.*: Test the count_changes pragma in the presence of triggers. |
||
38 | # pragma-11.*: Test the collation_list pragma. |
||
39 | # pragma-14.*: Test the page_count pragma. |
||
40 | # pragma-15.*: Test that the value set using the cache_size pragma is not |
||
41 | # reset when the schema is reloaded. |
||
42 | # pragma-16.*: Test proxy locking |
||
43 | # |
||
44 | |||
45 | ifcapable !pragma { |
||
46 | finish_test |
||
47 | return |
||
48 | } |
||
49 | |||
50 | # Delete the preexisting database to avoid the special setup |
||
51 | # that the "all.test" script does. |
||
52 | # |
||
53 | db close |
||
54 | file delete test.db test.db-journal |
||
55 | file delete test3.db test3.db-journal |
||
56 | sqlite3 db test.db; set DB [sqlite3_connection_pointer db] |
||
57 | |||
58 | |||
59 | ifcapable pager_pragmas { |
||
60 | set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}] |
||
61 | set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}] |
||
62 | do_test pragma-1.1 { |
||
63 | execsql { |
||
64 | PRAGMA cache_size; |
||
65 | PRAGMA default_cache_size; |
||
66 | PRAGMA synchronous; |
||
67 | } |
||
68 | } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] |
||
69 | do_test pragma-1.2 { |
||
70 | execsql { |
||
71 | PRAGMA synchronous=OFF; |
||
72 | PRAGMA cache_size=1234; |
||
73 | PRAGMA cache_size; |
||
74 | PRAGMA default_cache_size; |
||
75 | PRAGMA synchronous; |
||
76 | } |
||
77 | } [list 1234 $DFLT_CACHE_SZ 0] |
||
78 | do_test pragma-1.3 { |
||
79 | db close |
||
80 | sqlite3 db test.db |
||
81 | execsql { |
||
82 | PRAGMA cache_size; |
||
83 | PRAGMA default_cache_size; |
||
84 | PRAGMA synchronous; |
||
85 | } |
||
86 | } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] |
||
87 | do_test pragma-1.4 { |
||
88 | execsql { |
||
89 | PRAGMA synchronous=OFF; |
||
90 | PRAGMA cache_size; |
||
91 | PRAGMA default_cache_size; |
||
92 | PRAGMA synchronous; |
||
93 | } |
||
94 | } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0] |
||
95 | do_test pragma-1.5 { |
||
96 | execsql { |
||
97 | PRAGMA cache_size=-4321; |
||
98 | PRAGMA cache_size; |
||
99 | PRAGMA default_cache_size; |
||
100 | PRAGMA synchronous; |
||
101 | } |
||
102 | } [list 4321 $DFLT_CACHE_SZ 0] |
||
103 | do_test pragma-1.6 { |
||
104 | execsql { |
||
105 | PRAGMA synchronous=ON; |
||
106 | PRAGMA cache_size; |
||
107 | PRAGMA default_cache_size; |
||
108 | PRAGMA synchronous; |
||
109 | } |
||
110 | } [list 4321 $DFLT_CACHE_SZ 1] |
||
111 | do_test pragma-1.7 { |
||
112 | db close |
||
113 | sqlite3 db test.db |
||
114 | execsql { |
||
115 | PRAGMA cache_size; |
||
116 | PRAGMA default_cache_size; |
||
117 | PRAGMA synchronous; |
||
118 | } |
||
119 | } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] |
||
120 | do_test pragma-1.8 { |
||
121 | execsql { |
||
122 | PRAGMA default_cache_size=-123; |
||
123 | PRAGMA cache_size; |
||
124 | PRAGMA default_cache_size; |
||
125 | PRAGMA synchronous; |
||
126 | } |
||
127 | } {123 123 2} |
||
128 | do_test pragma-1.9.1 { |
||
129 | db close |
||
130 | sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] |
||
131 | execsql { |
||
132 | PRAGMA cache_size; |
||
133 | PRAGMA default_cache_size; |
||
134 | PRAGMA synchronous; |
||
135 | } |
||
136 | } {123 123 2} |
||
137 | ifcapable vacuum { |
||
138 | do_test pragma-1.9.2 { |
||
139 | execsql { |
||
140 | VACUUM; |
||
141 | PRAGMA cache_size; |
||
142 | PRAGMA default_cache_size; |
||
143 | PRAGMA synchronous; |
||
144 | } |
||
145 | } {123 123 2} |
||
146 | } |
||
147 | do_test pragma-1.10 { |
||
148 | execsql { |
||
149 | PRAGMA synchronous=NORMAL; |
||
150 | PRAGMA cache_size; |
||
151 | PRAGMA default_cache_size; |
||
152 | PRAGMA synchronous; |
||
153 | } |
||
154 | } {123 123 1} |
||
155 | do_test pragma-1.11 { |
||
156 | execsql { |
||
157 | PRAGMA synchronous=FULL; |
||
158 | PRAGMA cache_size; |
||
159 | PRAGMA default_cache_size; |
||
160 | PRAGMA synchronous; |
||
161 | } |
||
162 | } {123 123 2} |
||
163 | do_test pragma-1.12 { |
||
164 | db close |
||
165 | sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] |
||
166 | execsql { |
||
167 | PRAGMA cache_size; |
||
168 | PRAGMA default_cache_size; |
||
169 | PRAGMA synchronous; |
||
170 | } |
||
171 | } {123 123 2} |
||
172 | |||
173 | # Make sure the pragma handler understands numeric values in addition |
||
174 | # to keywords like "off" and "full". |
||
175 | # |
||
176 | do_test pragma-1.13 { |
||
177 | execsql { |
||
178 | PRAGMA synchronous=0; |
||
179 | PRAGMA synchronous; |
||
180 | } |
||
181 | } {0} |
||
182 | do_test pragma-1.14 { |
||
183 | execsql { |
||
184 | PRAGMA synchronous=2; |
||
185 | PRAGMA synchronous; |
||
186 | } |
||
187 | } {2} |
||
188 | } ;# ifcapable pager_pragmas |
||
189 | |||
190 | # Test turning "flag" pragmas on and off. |
||
191 | # |
||
192 | ifcapable debug { |
||
193 | # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG |
||
194 | # |
||
195 | do_test pragma-1.15 { |
||
196 | execsql { |
||
197 | PRAGMA vdbe_listing=YES; |
||
198 | PRAGMA vdbe_listing; |
||
199 | } |
||
200 | } {1} |
||
201 | do_test pragma-1.16 { |
||
202 | execsql { |
||
203 | PRAGMA vdbe_listing=NO; |
||
204 | PRAGMA vdbe_listing; |
||
205 | } |
||
206 | } {0} |
||
207 | } |
||
208 | |||
209 | do_test pragma-1.17 { |
||
210 | execsql { |
||
211 | PRAGMA parser_trace=ON; |
||
212 | PRAGMA parser_trace=OFF; |
||
213 | } |
||
214 | } {} |
||
215 | do_test pragma-1.18 { |
||
216 | execsql { |
||
217 | PRAGMA bogus = -1234; -- Parsing of negative values |
||
218 | } |
||
219 | } {} |
||
220 | |||
221 | # Test modifying the safety_level of an attached database. |
||
222 | ifcapable pager_pragmas&&attach { |
||
223 | do_test pragma-2.1 { |
||
224 | file delete -force test2.db |
||
225 | file delete -force test2.db-journal |
||
226 | execsql { |
||
227 | ATTACH 'test2.db' AS aux; |
||
228 | } |
||
229 | } {} |
||
230 | do_test pragma-2.2 { |
||
231 | execsql { |
||
232 | pragma aux.synchronous; |
||
233 | } |
||
234 | } {2} |
||
235 | do_test pragma-2.3 { |
||
236 | execsql { |
||
237 | pragma aux.synchronous = OFF; |
||
238 | pragma aux.synchronous; |
||
239 | pragma synchronous; |
||
240 | } |
||
241 | } {0 2} |
||
242 | do_test pragma-2.4 { |
||
243 | execsql { |
||
244 | pragma aux.synchronous = ON; |
||
245 | pragma synchronous; |
||
246 | pragma aux.synchronous; |
||
247 | } |
||
248 | } {2 1} |
||
249 | } ;# ifcapable pager_pragmas |
||
250 | |||
251 | # Construct a corrupted index and make sure the integrity_check |
||
252 | # pragma finds it. |
||
253 | # |
||
254 | # These tests won't work if the database is encrypted |
||
255 | # |
||
256 | do_test pragma-3.1 { |
||
257 | db close |
||
258 | file delete -force test.db test.db-journal |
||
259 | sqlite3 db test.db |
||
260 | execsql { |
||
261 | PRAGMA auto_vacuum=OFF; |
||
262 | BEGIN; |
||
263 | CREATE TABLE t2(a,b,c); |
||
264 | CREATE INDEX i2 ON t2(a); |
||
265 | INSERT INTO t2 VALUES(11,2,3); |
||
266 | INSERT INTO t2 VALUES(22,3,4); |
||
267 | COMMIT; |
||
268 | SELECT rowid, * from t2; |
||
269 | } |
||
270 | } {1 11 2 3 2 22 3 4} |
||
271 | ifcapable attach { |
||
272 | if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} { |
||
273 | do_test pragma-3.2 { |
||
274 | db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break |
||
275 | set pgsz [db eval {PRAGMA page_size}] |
||
276 | # overwrite the header on the rootpage of the index in order to |
||
277 | # make the index appear to be empty. |
||
278 | # |
||
279 | set offset [expr {$pgsz*($rootpage-1)}] |
||
280 | hexio_write test.db $offset 0a00000000040000000000 |
||
281 | db close |
||
282 | sqlite3 db test.db |
||
283 | execsql {PRAGMA integrity_check} |
||
284 | } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}} |
||
285 | do_test pragma-3.3 { |
||
286 | execsql {PRAGMA integrity_check=1} |
||
287 | } {{rowid 1 missing from index i2}} |
||
288 | do_test pragma-3.4 { |
||
289 | execsql { |
||
290 | ATTACH DATABASE 'test.db' AS t2; |
||
291 | PRAGMA integrity_check |
||
292 | } |
||
293 | } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}} |
||
294 | do_test pragma-3.5 { |
||
295 | execsql { |
||
296 | PRAGMA integrity_check=4 |
||
297 | } |
||
298 | } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2}} |
||
299 | do_test pragma-3.6 { |
||
300 | execsql { |
||
301 | PRAGMA integrity_check=xyz |
||
302 | } |
||
303 | } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}} |
||
304 | do_test pragma-3.7 { |
||
305 | execsql { |
||
306 | PRAGMA integrity_check=0 |
||
307 | } |
||
308 | } {{rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}} |
||
309 | |||
310 | # Add additional corruption by appending unused pages to the end of |
||
311 | # the database file testerr.db |
||
312 | # |
||
313 | do_test pragma-3.8 { |
||
314 | execsql {DETACH t2} |
||
315 | file delete -force testerr.db testerr.db-journal |
||
316 | set out [open testerr.db w] |
||
317 | fconfigure $out -translation binary |
||
318 | set in [open test.db r] |
||
319 | fconfigure $in -translation binary |
||
320 | puts -nonewline $out [read $in] |
||
321 | seek $in 0 |
||
322 | puts -nonewline $out [read $in] |
||
323 | close $in |
||
324 | close $out |
||
325 | hexio_write testerr.db 28 00000000 |
||
326 | execsql {REINDEX t2} |
||
327 | execsql {PRAGMA integrity_check} |
||
328 | } {ok} |
||
329 | do_test pragma-3.8.1 { |
||
330 | execsql {PRAGMA quick_check} |
||
331 | } {ok} |
||
332 | do_test pragma-3.9 { |
||
333 | execsql { |
||
334 | ATTACH 'testerr.db' AS t2; |
||
335 | PRAGMA integrity_check |
||
336 | } |
||
337 | } {{*** in database t2 *** |
||
338 | Page 4 is never used |
||
339 | Page 5 is never used |
||
340 | Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}} |
||
341 | do_test pragma-3.10 { |
||
342 | execsql { |
||
343 | PRAGMA integrity_check=1 |
||
344 | } |
||
345 | } {{*** in database t2 *** |
||
346 | Page 4 is never used}} |
||
347 | do_test pragma-3.11 { |
||
348 | execsql { |
||
349 | PRAGMA integrity_check=5 |
||
350 | } |
||
351 | } {{*** in database t2 *** |
||
352 | Page 4 is never used |
||
353 | Page 5 is never used |
||
354 | Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2}} |
||
355 | do_test pragma-3.12 { |
||
356 | execsql { |
||
357 | PRAGMA integrity_check=4 |
||
358 | } |
||
359 | } {{*** in database t2 *** |
||
360 | Page 4 is never used |
||
361 | Page 5 is never used |
||
362 | Page 6 is never used} {rowid 1 missing from index i2}} |
||
363 | do_test pragma-3.13 { |
||
364 | execsql { |
||
365 | PRAGMA integrity_check=3 |
||
366 | } |
||
367 | } {{*** in database t2 *** |
||
368 | Page 4 is never used |
||
369 | Page 5 is never used |
||
370 | Page 6 is never used}} |
||
371 | do_test pragma-3.14 { |
||
372 | execsql { |
||
373 | PRAGMA integrity_check(2) |
||
374 | } |
||
375 | } {{*** in database t2 *** |
||
376 | Page 4 is never used |
||
377 | Page 5 is never used}} |
||
378 | do_test pragma-3.15 { |
||
379 | execsql { |
||
380 | ATTACH 'testerr.db' AS t3; |
||
381 | PRAGMA integrity_check |
||
382 | } |
||
383 | } {{*** in database t2 *** |
||
384 | Page 4 is never used |
||
385 | Page 5 is never used |
||
386 | Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** |
||
387 | Page 4 is never used |
||
388 | Page 5 is never used |
||
389 | Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}} |
||
390 | do_test pragma-3.16 { |
||
391 | execsql { |
||
392 | PRAGMA integrity_check(10) |
||
393 | } |
||
394 | } {{*** in database t2 *** |
||
395 | Page 4 is never used |
||
396 | Page 5 is never used |
||
397 | Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** |
||
398 | Page 4 is never used |
||
399 | Page 5 is never used |
||
400 | Page 6 is never used} {rowid 1 missing from index i2}} |
||
401 | do_test pragma-3.17 { |
||
402 | execsql { |
||
403 | PRAGMA integrity_check=8 |
||
404 | } |
||
405 | } {{*** in database t2 *** |
||
406 | Page 4 is never used |
||
407 | Page 5 is never used |
||
408 | Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** |
||
409 | Page 4 is never used |
||
410 | Page 5 is never used}} |
||
411 | do_test pragma-3.18 { |
||
412 | execsql { |
||
413 | PRAGMA integrity_check=4 |
||
414 | } |
||
415 | } {{*** in database t2 *** |
||
416 | Page 4 is never used |
||
417 | Page 5 is never used |
||
418 | Page 6 is never used} {rowid 1 missing from index i2}} |
||
419 | } |
||
420 | do_test pragma-3.19 { |
||
421 | catch {db close} |
||
422 | file delete -force test.db test.db-journal |
||
423 | sqlite3 db test.db |
||
424 | db eval {PRAGMA integrity_check} |
||
425 | } {ok} |
||
426 | } |
||
427 | #exit |
||
428 | |||
429 | # Test modifying the cache_size of an attached database. |
||
430 | ifcapable pager_pragmas&&attach { |
||
431 | do_test pragma-4.1 { |
||
432 | execsql { |
||
433 | ATTACH 'test2.db' AS aux; |
||
434 | pragma aux.cache_size; |
||
435 | pragma aux.default_cache_size; |
||
436 | } |
||
437 | } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] |
||
438 | do_test pragma-4.2 { |
||
439 | execsql { |
||
440 | pragma aux.cache_size = 50; |
||
441 | pragma aux.cache_size; |
||
442 | pragma aux.default_cache_size; |
||
443 | } |
||
444 | } [list 50 $DFLT_CACHE_SZ] |
||
445 | do_test pragma-4.3 { |
||
446 | execsql { |
||
447 | pragma aux.default_cache_size = 456; |
||
448 | pragma aux.cache_size; |
||
449 | pragma aux.default_cache_size; |
||
450 | } |
||
451 | } {456 456} |
||
452 | do_test pragma-4.4 { |
||
453 | execsql { |
||
454 | pragma cache_size; |
||
455 | pragma default_cache_size; |
||
456 | } |
||
457 | } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] |
||
458 | do_test pragma-4.5 { |
||
459 | execsql { |
||
460 | DETACH aux; |
||
461 | ATTACH 'test3.db' AS aux; |
||
462 | pragma aux.cache_size; |
||
463 | pragma aux.default_cache_size; |
||
464 | } |
||
465 | } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] |
||
466 | do_test pragma-4.6 { |
||
467 | execsql { |
||
468 | DETACH aux; |
||
469 | ATTACH 'test2.db' AS aux; |
||
470 | pragma aux.cache_size; |
||
471 | pragma aux.default_cache_size; |
||
472 | } |
||
473 | } {456 456} |
||
474 | } ;# ifcapable pager_pragmas |
||
475 | |||
476 | # Test that modifying the sync-level in the middle of a transaction is |
||
477 | # disallowed. |
||
478 | ifcapable pager_pragmas { |
||
479 | do_test pragma-5.0 { |
||
480 | execsql { |
||
481 | pragma synchronous; |
||
482 | } |
||
483 | } {2} |
||
484 | do_test pragma-5.1 { |
||
485 | catchsql { |
||
486 | BEGIN; |
||
487 | pragma synchronous = OFF; |
||
488 | } |
||
489 | } {1 {Safety level may not be changed inside a transaction}} |
||
490 | do_test pragma-5.2 { |
||
491 | execsql { |
||
492 | pragma synchronous; |
||
493 | } |
||
494 | } {2} |
||
495 | catchsql {COMMIT;} |
||
496 | } ;# ifcapable pager_pragmas |
||
497 | |||
498 | # Test schema-query pragmas |
||
499 | # |
||
500 | ifcapable schema_pragmas { |
||
501 | ifcapable tempdb&&attach { |
||
502 | do_test pragma-6.1 { |
||
503 | set res {} |
||
504 | execsql {SELECT * FROM sqlite_temp_master} |
||
505 | foreach {idx name file} [execsql {pragma database_list}] { |
||
506 | lappend res $idx $name |
||
507 | } |
||
508 | set res |
||
509 | } {0 main 1 temp 2 aux} |
||
510 | } |
||
511 | do_test pragma-6.2 { |
||
512 | execsql { |
||
513 | CREATE TABLE t2(a,b,c); |
||
514 | pragma table_info(t2) |
||
515 | } |
||
516 | } {0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0} |
||
517 | do_test pragma-6.2.1 { |
||
518 | execsql { |
||
519 | pragma table_info; |
||
520 | } |
||
521 | } {} |
||
522 | db nullvalue <<NULL>> |
||
523 | do_test pragma-6.2.2 { |
||
524 | execsql { |
||
525 | CREATE TABLE t5( |
||
526 | a TEXT DEFAULT CURRENT_TIMESTAMP, |
||
527 | b DEFAULT (5+3), |
||
528 | c TEXT, |
||
529 | d INTEGER DEFAULT NULL, |
||
530 | e TEXT DEFAULT '' |
||
531 | ); |
||
532 | PRAGMA table_info(t5); |
||
533 | } |
||
534 | } {0 a TEXT 0 CURRENT_TIMESTAMP 0 1 b {} 0 5+3 0 2 c TEXT 0 <<NULL>> 0 3 d INTEGER 0 NULL 0 4 e TEXT 0 '' 0} |
||
535 | db nullvalue {} |
||
536 | ifcapable {foreignkey} { |
||
537 | do_test pragma-6.3.1 { |
||
538 | execsql { |
||
539 | CREATE TABLE t3(a int references t2(b), b UNIQUE); |
||
540 | pragma foreign_key_list(t3); |
||
541 | } |
||
542 | } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE} |
||
543 | do_test pragma-6.3.2 { |
||
544 | execsql { |
||
545 | pragma foreign_key_list; |
||
546 | } |
||
547 | } {} |
||
548 | do_test pragma-6.3.3 { |
||
549 | execsql { |
||
550 | pragma foreign_key_list(t3_bogus); |
||
551 | } |
||
552 | } {} |
||
553 | do_test pragma-6.3.4 { |
||
554 | execsql { |
||
555 | pragma foreign_key_list(t5); |
||
556 | } |
||
557 | } {} |
||
558 | do_test pragma-6.4 { |
||
559 | execsql { |
||
560 | pragma index_list(t3); |
||
561 | } |
||
562 | } {0 sqlite_autoindex_t3_1 1} |
||
563 | } |
||
564 | ifcapable {!foreignkey} { |
||
565 | execsql {CREATE TABLE t3(a,b UNIQUE)} |
||
566 | } |
||
567 | do_test pragma-6.5.1 { |
||
568 | execsql { |
||
569 | CREATE INDEX t3i1 ON t3(a,b); |
||
570 | pragma index_info(t3i1); |
||
571 | } |
||
572 | } {0 0 a 1 1 b} |
||
573 | do_test pragma-6.5.2 { |
||
574 | execsql { |
||
575 | pragma index_info(t3i1_bogus); |
||
576 | } |
||
577 | } {} |
||
578 | |||
579 | ifcapable tempdb { |
||
580 | # Test for ticket #3320. When a temp table of the same name exists, make |
||
581 | # sure the schema of the main table can still be queried using |
||
582 | # "pragma table_info": |
||
583 | do_test pragma-6.6.1 { |
||
584 | execsql { |
||
585 | CREATE TABLE trial(col_main); |
||
586 | CREATE TEMP TABLE trial(col_temp); |
||
587 | } |
||
588 | } {} |
||
589 | do_test pragma-6.6.2 { |
||
590 | execsql { |
||
591 | PRAGMA table_info(trial); |
||
592 | } |
||
593 | } {0 col_temp {} 0 {} 0} |
||
594 | do_test pragma-6.6.3 { |
||
595 | execsql { |
||
596 | PRAGMA temp.table_info(trial); |
||
597 | } |
||
598 | } {0 col_temp {} 0 {} 0} |
||
599 | do_test pragma-6.6.4 { |
||
600 | execsql { |
||
601 | PRAGMA main.table_info(trial); |
||
602 | } |
||
603 | } {0 col_main {} 0 {} 0} |
||
604 | } |
||
605 | |||
606 | do_test pragma-6.7 { |
||
607 | execsql { |
||
608 | CREATE TABLE test_table( |
||
609 | one INT NOT NULL DEFAULT -1, |
||
610 | two text, |
||
611 | three VARCHAR(45, 65) DEFAULT 'abcde', |
||
612 | four REAL DEFAULT X'abcdef', |
||
613 | five DEFAULT CURRENT_TIME |
||
614 | ); |
||
615 | PRAGMA table_info(test_table); |
||
616 | } |
||
617 | } [concat \ |
||
618 | {0 one INT 1 -1 0} \ |
||
619 | {1 two text 0 {} 0} \ |
||
620 | {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \ |
||
621 | {3 four REAL 0 X'abcdef' 0} \ |
||
622 | {4 five {} 0 CURRENT_TIME 0} \ |
||
623 | ] |
||
624 | } ;# ifcapable schema_pragmas |
||
625 | # Miscellaneous tests |
||
626 | # |
||
627 | ifcapable schema_pragmas { |
||
628 | do_test pragma-7.1.1 { |
||
629 | # Make sure a pragma knows to read the schema if it needs to |
||
630 | db close |
||
631 | sqlite3 db test.db |
||
632 | execsql { |
||
633 | pragma index_list(t3); |
||
634 | } |
||
635 | } {0 t3i1 0 1 sqlite_autoindex_t3_1 1} |
||
636 | do_test pragma-7.1.2 { |
||
637 | execsql { |
||
638 | pragma index_list(t3_bogus); |
||
639 | } |
||
640 | } {} |
||
641 | } ;# ifcapable schema_pragmas |
||
642 | ifcapable {utf16} { |
||
643 | if {[permutation] == ""} { |
||
644 | do_test pragma-7.2 { |
||
645 | db close |
||
646 | sqlite3 db test.db |
||
647 | catchsql { |
||
648 | pragma encoding=bogus; |
||
649 | } |
||
650 | } {1 {unsupported encoding: bogus}} |
||
651 | } |
||
652 | } |
||
653 | ifcapable tempdb { |
||
654 | do_test pragma-7.3 { |
||
655 | db close |
||
656 | sqlite3 db test.db |
||
657 | execsql { |
||
658 | pragma lock_status; |
||
659 | } |
||
660 | } {main unlocked temp closed} |
||
661 | } else { |
||
662 | do_test pragma-7.3 { |
||
663 | db close |
||
664 | sqlite3 db test.db |
||
665 | execsql { |
||
666 | pragma lock_status; |
||
667 | } |
||
668 | } {main unlocked} |
||
669 | } |
||
670 | |||
671 | |||
672 | #---------------------------------------------------------------------- |
||
673 | # Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA |
||
674 | # user_version" statements. |
||
675 | # |
||
676 | # pragma-8.1: PRAGMA schema_version |
||
677 | # pragma-8.2: PRAGMA user_version |
||
678 | # |
||
679 | |||
680 | ifcapable schema_version { |
||
681 | |||
682 | # First check that we can set the schema version and then retrieve the |
||
683 | # same value. |
||
684 | do_test pragma-8.1.1 { |
||
685 | execsql { |
||
686 | PRAGMA schema_version = 105; |
||
687 | } |
||
688 | } {} |
||
689 | do_test pragma-8.1.2 { |
||
690 | execsql2 { |
||
691 | PRAGMA schema_version; |
||
692 | } |
||
693 | } {schema_version 105} |
||
694 | do_test pragma-8.1.3 { |
||
695 | execsql { |
||
696 | PRAGMA schema_version = 106; |
||
697 | } |
||
698 | } {} |
||
699 | do_test pragma-8.1.4 { |
||
700 | execsql { |
||
701 | PRAGMA schema_version; |
||
702 | } |
||
703 | } 106 |
||
704 | |||
705 | # Check that creating a table modifies the schema-version (this is really |
||
706 | # to verify that the value being read is in fact the schema version). |
||
707 | do_test pragma-8.1.5 { |
||
708 | execsql { |
||
709 | CREATE TABLE t4(a, b, c); |
||
710 | INSERT INTO t4 VALUES(1, 2, 3); |
||
711 | SELECT * FROM t4; |
||
712 | } |
||
713 | } {1 2 3} |
||
714 | do_test pragma-8.1.6 { |
||
715 | execsql { |
||
716 | PRAGMA schema_version; |
||
717 | } |
||
718 | } 107 |
||
719 | |||
720 | # Now open a second connection to the database. Ensure that changing the |
||
721 | # schema-version using the first connection forces the second connection |
||
722 | # to reload the schema. This has to be done using the C-API test functions, |
||
723 | # because the TCL API accounts for SCHEMA_ERROR and retries the query. |
||
724 | do_test pragma-8.1.7 { |
||
725 | sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] |
||
726 | execsql { |
||
727 | SELECT * FROM t4; |
||
728 | } db2 |
||
729 | } {1 2 3} |
||
730 | do_test pragma-8.1.8 { |
||
731 | execsql { |
||
732 | PRAGMA schema_version = 108; |
||
733 | } |
||
734 | } {} |
||
735 | do_test pragma-8.1.9 { |
||
736 | set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY] |
||
737 | sqlite3_step $::STMT |
||
738 | } SQLITE_ERROR |
||
739 | do_test pragma-8.1.10 { |
||
740 | sqlite3_finalize $::STMT |
||
741 | } SQLITE_SCHEMA |
||
742 | |||
743 | # Make sure the schema-version can be manipulated in an attached database. |
||
744 | file delete -force test2.db |
||
745 | file delete -force test2.db-journal |
||
746 | ifcapable attach { |
||
747 | do_test pragma-8.1.11 { |
||
748 | execsql { |
||
749 | ATTACH 'test2.db' AS aux; |
||
750 | CREATE TABLE aux.t1(a, b, c); |
||
751 | PRAGMA aux.schema_version = 205; |
||
752 | } |
||
753 | } {} |
||
754 | do_test pragma-8.1.12 { |
||
755 | execsql { |
||
756 | PRAGMA aux.schema_version; |
||
757 | } |
||
758 | } 205 |
||
759 | } |
||
760 | do_test pragma-8.1.13 { |
||
761 | execsql { |
||
762 | PRAGMA schema_version; |
||
763 | } |
||
764 | } 108 |
||
765 | |||
766 | # And check that modifying the schema-version in an attached database |
||
767 | # forces the second connection to reload the schema. |
||
768 | ifcapable attach { |
||
769 | do_test pragma-8.1.14 { |
||
770 | sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] |
||
771 | execsql { |
||
772 | ATTACH 'test2.db' AS aux; |
||
773 | SELECT * FROM aux.t1; |
||
774 | } db2 |
||
775 | } {} |
||
776 | do_test pragma-8.1.15 { |
||
777 | execsql { |
||
778 | PRAGMA aux.schema_version = 206; |
||
779 | } |
||
780 | } {} |
||
781 | do_test pragma-8.1.16 { |
||
782 | set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY] |
||
783 | sqlite3_step $::STMT |
||
784 | } SQLITE_ERROR |
||
785 | do_test pragma-8.1.17 { |
||
786 | sqlite3_finalize $::STMT |
||
787 | } SQLITE_SCHEMA |
||
788 | do_test pragma-8.1.18 { |
||
789 | db2 close |
||
790 | } {} |
||
791 | } |
||
792 | |||
793 | # Now test that the user-version can be read and written (and that we aren't |
||
794 | # accidentally manipulating the schema-version instead). |
||
795 | do_test pragma-8.2.1 { |
||
796 | execsql2 { |
||
797 | PRAGMA user_version; |
||
798 | } |
||
799 | } {user_version 0} |
||
800 | do_test pragma-8.2.2 { |
||
801 | execsql { |
||
802 | PRAGMA user_version = 2; |
||
803 | } |
||
804 | } {} |
||
805 | do_test pragma-8.2.3.1 { |
||
806 | execsql2 { |
||
807 | PRAGMA user_version; |
||
808 | } |
||
809 | } {user_version 2} |
||
810 | do_test pragma-8.2.3.2 { |
||
811 | db close |
||
812 | sqlite3 db test.db |
||
813 | execsql { |
||
814 | PRAGMA user_version; |
||
815 | } |
||
816 | } {2} |
||
817 | do_test pragma-8.2.4.1 { |
||
818 | execsql { |
||
819 | PRAGMA schema_version; |
||
820 | } |
||
821 | } {108} |
||
822 | ifcapable vacuum { |
||
823 | do_test pragma-8.2.4.2 { |
||
824 | execsql { |
||
825 | VACUUM; |
||
826 | PRAGMA user_version; |
||
827 | } |
||
828 | } {2} |
||
829 | do_test pragma-8.2.4.3 { |
||
830 | execsql { |
||
831 | PRAGMA schema_version; |
||
832 | } |
||
833 | } {109} |
||
834 | } |
||
835 | |||
836 | ifcapable attach { |
||
837 | db eval {ATTACH 'test2.db' AS aux} |
||
838 | |||
839 | # Check that the user-version in the auxilary database can be manipulated ( |
||
840 | # and that we aren't accidentally manipulating the same in the main db). |
||
841 | do_test pragma-8.2.5 { |
||
842 | execsql { |
||
843 | PRAGMA aux.user_version; |
||
844 | } |
||
845 | } {0} |
||
846 | do_test pragma-8.2.6 { |
||
847 | execsql { |
||
848 | PRAGMA aux.user_version = 3; |
||
849 | } |
||
850 | } {} |
||
851 | do_test pragma-8.2.7 { |
||
852 | execsql { |
||
853 | PRAGMA aux.user_version; |
||
854 | } |
||
855 | } {3} |
||
856 | do_test pragma-8.2.8 { |
||
857 | execsql { |
||
858 | PRAGMA main.user_version; |
||
859 | } |
||
860 | } {2} |
||
861 | |||
862 | # Now check that a ROLLBACK resets the user-version if it has been modified |
||
863 | # within a transaction. |
||
864 | do_test pragma-8.2.9 { |
||
865 | execsql { |
||
866 | BEGIN; |
||
867 | PRAGMA aux.user_version = 10; |
||
868 | PRAGMA user_version = 11; |
||
869 | } |
||
870 | } {} |
||
871 | do_test pragma-8.2.10 { |
||
872 | execsql { |
||
873 | PRAGMA aux.user_version; |
||
874 | } |
||
875 | } {10} |
||
876 | do_test pragma-8.2.11 { |
||
877 | execsql { |
||
878 | PRAGMA main.user_version; |
||
879 | } |
||
880 | } {11} |
||
881 | do_test pragma-8.2.12 { |
||
882 | execsql { |
||
883 | ROLLBACK; |
||
884 | PRAGMA aux.user_version; |
||
885 | } |
||
886 | } {3} |
||
887 | do_test pragma-8.2.13 { |
||
888 | execsql { |
||
889 | PRAGMA main.user_version; |
||
890 | } |
||
891 | } {2} |
||
892 | } |
||
893 | |||
894 | # Try a negative value for the user-version |
||
895 | do_test pragma-8.2.14 { |
||
896 | execsql { |
||
897 | PRAGMA user_version = -450; |
||
898 | } |
||
899 | } {} |
||
900 | do_test pragma-8.2.15 { |
||
901 | execsql { |
||
902 | PRAGMA user_version; |
||
903 | } |
||
904 | } {-450} |
||
905 | } ; # ifcapable schema_version |
||
906 | |||
907 | # Check to see if TEMP_STORE is memory or disk. Return strings |
||
908 | # "memory" or "disk" as appropriate. |
||
909 | # |
||
910 | proc check_temp_store {} { |
||
911 | db eval {CREATE TEMP TABLE IF NOT EXISTS a(b)} |
||
912 | db eval {PRAGMA database_list} { |
||
913 | if {$name=="temp"} { |
||
914 | set bt [btree_from_db db 1] |
||
915 | if {[btree_ismemdb $bt]} { |
||
916 | return "memory" |
||
917 | } |
||
918 | return "disk" |
||
919 | } |
||
920 | } |
||
921 | return "unknown" |
||
922 | } |
||
923 | |||
924 | |||
925 | # Test temp_store and temp_store_directory pragmas |
||
926 | # |
||
927 | ifcapable pager_pragmas { |
||
928 | do_test pragma-9.1 { |
||
929 | db close |
||
930 | sqlite3 db test.db |
||
931 | execsql { |
||
932 | PRAGMA temp_store; |
||
933 | } |
||
934 | } {0} |
||
935 | if {$TEMP_STORE<=1} { |
||
936 | do_test pragma-9.1.1 { |
||
937 | check_temp_store |
||
938 | } {disk} |
||
939 | } else { |
||
940 | do_test pragma-9.1.1 { |
||
941 | check_temp_store |
||
942 | } {memory} |
||
943 | } |
||
944 | |||
945 | do_test pragma-9.2 { |
||
946 | db close |
||
947 | sqlite3 db test.db |
||
948 | execsql { |
||
949 | PRAGMA temp_store=file; |
||
950 | PRAGMA temp_store; |
||
951 | } |
||
952 | } {1} |
||
953 | if {$TEMP_STORE==3} { |
||
954 | # When TEMP_STORE is 3, always use memory regardless of pragma settings. |
||
955 | do_test pragma-9.2.1 { |
||
956 | check_temp_store |
||
957 | } {memory} |
||
958 | } else { |
||
959 | do_test pragma-9.2.1 { |
||
960 | check_temp_store |
||
961 | } {disk} |
||
962 | } |
||
963 | |||
964 | do_test pragma-9.3 { |
||
965 | db close |
||
966 | sqlite3 db test.db |
||
967 | execsql { |
||
968 | PRAGMA temp_store=memory; |
||
969 | PRAGMA temp_store; |
||
970 | } |
||
971 | } {2} |
||
972 | if {$TEMP_STORE==0} { |
||
973 | # When TEMP_STORE is 0, always use the disk regardless of pragma settings. |
||
974 | do_test pragma-9.3.1 { |
||
975 | check_temp_store |
||
976 | } {disk} |
||
977 | } else { |
||
978 | do_test pragma-9.3.1 { |
||
979 | check_temp_store |
||
980 | } {memory} |
||
981 | } |
||
982 | |||
983 | do_test pragma-9.4 { |
||
984 | execsql { |
||
985 | PRAGMA temp_store_directory; |
||
986 | } |
||
987 | } {} |
||
988 | ifcapable wsd { |
||
989 | do_test pragma-9.5 { |
||
990 | set pwd [string map {' ''} [file nativename [pwd]]] |
||
991 | execsql " |
||
992 | PRAGMA temp_store_directory='$pwd'; |
||
993 | " |
||
994 | } {} |
||
995 | do_test pragma-9.6 { |
||
996 | execsql { |
||
997 | PRAGMA temp_store_directory; |
||
998 | } |
||
999 | } [list [file nativename [pwd]]] |
||
1000 | do_test pragma-9.7 { |
||
1001 | catchsql { |
||
1002 | PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR'; |
||
1003 | } |
||
1004 | } {1 {not a writable directory}} |
||
1005 | do_test pragma-9.8 { |
||
1006 | execsql { |
||
1007 | PRAGMA temp_store_directory=''; |
||
1008 | } |
||
1009 | } {} |
||
1010 | if {![info exists TEMP_STORE] || $TEMP_STORE<=1} { |
||
1011 | ifcapable tempdb { |
||
1012 | do_test pragma-9.9 { |
||
1013 | execsql { |
||
1014 | PRAGMA temp_store_directory; |
||
1015 | PRAGMA temp_store=FILE; |
||
1016 | CREATE TEMP TABLE temp_store_directory_test(a integer); |
||
1017 | INSERT INTO temp_store_directory_test values (2); |
||
1018 | SELECT * FROM temp_store_directory_test; |
||
1019 | } |
||
1020 | } {2} |
||
1021 | do_test pragma-9.10 { |
||
1022 | catchsql " |
||
1023 | PRAGMA temp_store_directory='$pwd'; |
||
1024 | SELECT * FROM temp_store_directory_test; |
||
1025 | " |
||
1026 | } {1 {no such table: temp_store_directory_test}} |
||
1027 | } |
||
1028 | } |
||
1029 | } |
||
1030 | do_test pragma-9.11 { |
||
1031 | execsql { |
||
1032 | PRAGMA temp_store = 0; |
||
1033 | PRAGMA temp_store; |
||
1034 | } |
||
1035 | } {0} |
||
1036 | do_test pragma-9.12 { |
||
1037 | execsql { |
||
1038 | PRAGMA temp_store = 1; |
||
1039 | PRAGMA temp_store; |
||
1040 | } |
||
1041 | } {1} |
||
1042 | do_test pragma-9.13 { |
||
1043 | execsql { |
||
1044 | PRAGMA temp_store = 2; |
||
1045 | PRAGMA temp_store; |
||
1046 | } |
||
1047 | } {2} |
||
1048 | do_test pragma-9.14 { |
||
1049 | execsql { |
||
1050 | PRAGMA temp_store = 3; |
||
1051 | PRAGMA temp_store; |
||
1052 | } |
||
1053 | } {0} |
||
1054 | do_test pragma-9.15 { |
||
1055 | catchsql { |
||
1056 | BEGIN EXCLUSIVE; |
||
1057 | CREATE TEMP TABLE temp_table(t); |
||
1058 | INSERT INTO temp_table VALUES('valuable data'); |
||
1059 | PRAGMA temp_store = 1; |
||
1060 | } |
||
1061 | } {1 {temporary storage cannot be changed from within a transaction}} |
||
1062 | do_test pragma-9.16 { |
||
1063 | execsql { |
||
1064 | SELECT * FROM temp_table; |
||
1065 | COMMIT; |
||
1066 | } |
||
1067 | } {{valuable data}} |
||
1068 | |||
1069 | do_test pragma-9.17 { |
||
1070 | execsql { |
||
1071 | INSERT INTO temp_table VALUES('valuable data II'); |
||
1072 | SELECT * FROM temp_table; |
||
1073 | } |
||
1074 | } {{valuable data} {valuable data II}} |
||
1075 | |||
1076 | do_test pragma-9.18 { |
||
1077 | set rc [catch { |
||
1078 | db eval {SELECT t FROM temp_table} { |
||
1079 | execsql {pragma temp_store = 1} |
||
1080 | } |
||
1081 | } msg] |
||
1082 | list $rc $msg |
||
1083 | } {1 {temporary storage cannot be changed from within a transaction}} |
||
1084 | |||
1085 | } ;# ifcapable pager_pragmas |
||
1086 | |||
1087 | ifcapable trigger { |
||
1088 | |||
1089 | do_test pragma-10.0 { |
||
1090 | catchsql { |
||
1091 | DROP TABLE main.t1; |
||
1092 | } |
||
1093 | execsql { |
||
1094 | PRAGMA count_changes = 1; |
||
1095 | |||
1096 | CREATE TABLE t1(a PRIMARY KEY); |
||
1097 | CREATE TABLE t1_mirror(a); |
||
1098 | CREATE TABLE t1_mirror2(a); |
||
1099 | CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN |
||
1100 | INSERT INTO t1_mirror VALUES(new.a); |
||
1101 | END; |
||
1102 | CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN |
||
1103 | INSERT INTO t1_mirror2 VALUES(new.a); |
||
1104 | END; |
||
1105 | CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN |
||
1106 | UPDATE t1_mirror SET a = new.a WHERE a = old.a; |
||
1107 | END; |
||
1108 | CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN |
||
1109 | UPDATE t1_mirror2 SET a = new.a WHERE a = old.a; |
||
1110 | END; |
||
1111 | CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN |
||
1112 | DELETE FROM t1_mirror WHERE a = old.a; |
||
1113 | END; |
||
1114 | CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN |
||
1115 | DELETE FROM t1_mirror2 WHERE a = old.a; |
||
1116 | END; |
||
1117 | } |
||
1118 | } {} |
||
1119 | |||
1120 | do_test pragma-10.1 { |
||
1121 | execsql { |
||
1122 | INSERT INTO t1 VALUES(randstr(10,10)); |
||
1123 | } |
||
1124 | } {1} |
||
1125 | do_test pragma-10.2 { |
||
1126 | execsql { |
||
1127 | UPDATE t1 SET a = randstr(10,10); |
||
1128 | } |
||
1129 | } {1} |
||
1130 | do_test pragma-10.3 { |
||
1131 | execsql { |
||
1132 | DELETE FROM t1; |
||
1133 | } |
||
1134 | } {1} |
||
1135 | |||
1136 | } ;# ifcapable trigger |
||
1137 | |||
1138 | ifcapable schema_pragmas { |
||
1139 | do_test pragma-11.1 { |
||
1140 | execsql2 { |
||
1141 | pragma collation_list; |
||
1142 | } |
||
1143 | } {seq 0 name NOCASE seq 1 name RTRIM seq 2 name BINARY} |
||
1144 | do_test pragma-11.2 { |
||
1145 | db collate New_Collation blah... |
||
1146 | execsql { |
||
1147 | pragma collation_list; |
||
1148 | } |
||
1149 | } {0 New_Collation 1 NOCASE 2 RTRIM 3 BINARY} |
||
1150 | } |
||
1151 | |||
1152 | ifcapable schema_pragmas&&tempdb { |
||
1153 | do_test pragma-12.1 { |
||
1154 | sqlite3 db2 test.db |
||
1155 | execsql { |
||
1156 | PRAGMA temp.table_info('abc'); |
||
1157 | } db2 |
||
1158 | } {} |
||
1159 | db2 close |
||
1160 | |||
1161 | do_test pragma-12.2 { |
||
1162 | sqlite3 db2 test.db |
||
1163 | execsql { |
||
1164 | PRAGMA temp.default_cache_size = 200; |
||
1165 | PRAGMA temp.default_cache_size; |
||
1166 | } db2 |
||
1167 | } {200} |
||
1168 | db2 close |
||
1169 | |||
1170 | do_test pragma-12.3 { |
||
1171 | sqlite3 db2 test.db |
||
1172 | execsql { |
||
1173 | PRAGMA temp.cache_size = 400; |
||
1174 | PRAGMA temp.cache_size; |
||
1175 | } db2 |
||
1176 | } {400} |
||
1177 | db2 close |
||
1178 | } |
||
1179 | |||
1180 | ifcapable bloblit { |
||
1181 | |||
1182 | do_test pragma-13.1 { |
||
1183 | execsql { |
||
1184 | DROP TABLE IF EXISTS t4; |
||
1185 | PRAGMA vdbe_trace=on; |
||
1186 | PRAGMA vdbe_listing=on; |
||
1187 | PRAGMA sql_trace=on; |
||
1188 | CREATE TABLE t4(a INTEGER PRIMARY KEY,b); |
||
1189 | INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789'); |
||
1190 | INSERT INTO t4(b) VALUES(randstr(30,30)); |
||
1191 | INSERT INTO t4(b) VALUES(1.23456); |
||
1192 | INSERT INTO t4(b) VALUES(NULL); |
||
1193 | INSERT INTO t4(b) VALUES(0); |
||
1194 | INSERT INTO t4(b) SELECT b||b||b||b FROM t4; |
||
1195 | SELECT * FROM t4; |
||
1196 | } |
||
1197 | execsql { |
||
1198 | PRAGMA vdbe_trace=off; |
||
1199 | PRAGMA vdbe_listing=off; |
||
1200 | PRAGMA sql_trace=off; |
||
1201 | } |
||
1202 | } {} |
||
1203 | |||
1204 | } ;# ifcapable bloblit |
||
1205 | |||
1206 | ifcapable pager_pragmas { |
||
1207 | db close |
||
1208 | file delete -force test.db |
||
1209 | sqlite3 db test.db |
||
1210 | |||
1211 | do_test pragma-14.1 { |
||
1212 | execsql { pragma auto_vacuum = 0 } |
||
1213 | execsql { pragma page_count } |
||
1214 | } {0} |
||
1215 | |||
1216 | do_test pragma-14.2 { |
||
1217 | execsql { |
||
1218 | CREATE TABLE abc(a, b, c); |
||
1219 | PRAGMA page_count; |
||
1220 | } |
||
1221 | } {2} |
||
1222 | |||
1223 | do_test pragma-14.3 { |
||
1224 | execsql { |
||
1225 | BEGIN; |
||
1226 | CREATE TABLE def(a, b, c); |
||
1227 | PRAGMA page_count; |
||
1228 | } |
||
1229 | } {3} |
||
1230 | |||
1231 | do_test pragma-14.4 { |
||
1232 | set page_size [db one {pragma page_size}] |
||
1233 | expr [file size test.db] / $page_size |
||
1234 | } {2} |
||
1235 | |||
1236 | do_test pragma-14.5 { |
||
1237 | execsql { |
||
1238 | ROLLBACK; |
||
1239 | PRAGMA page_count; |
||
1240 | } |
||
1241 | } {2} |
||
1242 | |||
1243 | do_test pragma-14.6 { |
||
1244 | file delete -force test2.db |
||
1245 | sqlite3 db2 test2.db |
||
1246 | execsql { |
||
1247 | PRAGMA auto_vacuum = 0; |
||
1248 | CREATE TABLE t1(a, b, c); |
||
1249 | CREATE TABLE t2(a, b, c); |
||
1250 | CREATE TABLE t3(a, b, c); |
||
1251 | CREATE TABLE t4(a, b, c); |
||
1252 | } db2 |
||
1253 | db2 close |
||
1254 | execsql { |
||
1255 | ATTACH 'test2.db' AS aux; |
||
1256 | PRAGMA aux.page_count; |
||
1257 | } |
||
1258 | } {5} |
||
1259 | } |
||
1260 | |||
1261 | # Test that the value set using the cache_size pragma is not reset when the |
||
1262 | # schema is reloaded. |
||
1263 | # |
||
1264 | ifcapable pager_pragmas { |
||
1265 | db close |
||
1266 | sqlite3 db test.db |
||
1267 | do_test pragma-15.1 { |
||
1268 | execsql { |
||
1269 | PRAGMA cache_size=59; |
||
1270 | PRAGMA cache_size; |
||
1271 | } |
||
1272 | } {59} |
||
1273 | do_test pragma-15.2 { |
||
1274 | sqlite3 db2 test.db |
||
1275 | execsql { |
||
1276 | CREATE TABLE newtable(a, b, c); |
||
1277 | } db2 |
||
1278 | db2 close |
||
1279 | } {} |
||
1280 | do_test pragma-15.3 { |
||
1281 | # Evaluating this statement will cause the schema to be reloaded (because |
||
1282 | # the schema was changed by another connection in pragma-15.2). At one |
||
1283 | # point there was a bug that reset the cache_size to its default value |
||
1284 | # when this happened. |
||
1285 | execsql { SELECT * FROM sqlite_master } |
||
1286 | execsql { PRAGMA cache_size } |
||
1287 | } {59} |
||
1288 | } |
||
1289 | |||
1290 | # Reset the sqlite3_temp_directory variable for the next run of tests: |
||
1291 | sqlite3 dbX :memory: |
||
1292 | dbX eval {PRAGMA temp_store_directory = ""} |
||
1293 | dbX close |
||
1294 | |||
1295 | ifcapable lock_proxy_pragmas&&prefer_proxy_locking { |
||
1296 | set sqlite_hostid_num 1 |
||
1297 | |||
1298 | set using_proxy 0 |
||
1299 | foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] { |
||
1300 | set using_proxy $value |
||
1301 | } |
||
1302 | |||
1303 | # Test the lock_proxy_file pragmas. |
||
1304 | # |
||
1305 | db close |
||
1306 | set env(SQLITE_FORCE_PROXY_LOCKING) "0" |
||
1307 | |||
1308 | sqlite3 db test.db |
||
1309 | do_test pragma-16.1 { |
||
1310 | execsql { |
||
1311 | PRAGMA lock_proxy_file="mylittleproxy"; |
||
1312 | select * from sqlite_master; |
||
1313 | } |
||
1314 | execsql { |
||
1315 | PRAGMA lock_proxy_file; |
||
1316 | } |
||
1317 | } {mylittleproxy} |
||
1318 | |||
1319 | do_test pragma-16.2 { |
||
1320 | sqlite3 db2 test.db |
||
1321 | execsql { |
||
1322 | PRAGMA lock_proxy_file="mylittleproxy"; |
||
1323 | } db2 |
||
1324 | } {} |
||
1325 | |||
1326 | db2 close |
||
1327 | do_test pragma-16.2.1 { |
||
1328 | sqlite3 db2 test.db |
||
1329 | execsql { |
||
1330 | PRAGMA lock_proxy_file=":auto:"; |
||
1331 | select * from sqlite_master; |
||
1332 | } db2 |
||
1333 | execsql { |
||
1334 | PRAGMA lock_proxy_file; |
||
1335 | } db2 |
||
1336 | } {mylittleproxy} |
||
1337 | |||
1338 | db2 close |
||
1339 | do_test pragma-16.3 { |
||
1340 | sqlite3 db2 test.db |
||
1341 | execsql { |
||
1342 | PRAGMA lock_proxy_file="myotherproxy"; |
||
1343 | } db2 |
||
1344 | catchsql { |
||
1345 | select * from sqlite_master; |
||
1346 | } db2 |
||
1347 | } {1 {database is locked}} |
||
1348 | |||
1349 | do_test pragma-16.4 { |
||
1350 | db2 close |
||
1351 | db close |
||
1352 | sqlite3 db2 test.db |
||
1353 | execsql { |
||
1354 | PRAGMA lock_proxy_file="myoriginalproxy"; |
||
1355 | PRAGMA lock_proxy_file="myotherproxy"; |
||
1356 | PRAGMA lock_proxy_file; |
||
1357 | } db2 |
||
1358 | } {myotherproxy} |
||
1359 | |||
1360 | db2 close |
||
1361 | set env(SQLITE_FORCE_PROXY_LOCKING) "1" |
||
1362 | do_test pragma-16.5 { |
||
1363 | sqlite3 db2 test.db |
||
1364 | execsql { |
||
1365 | PRAGMA lock_proxy_file=":auto:"; |
||
1366 | PRAGMA lock_proxy_file; |
||
1367 | } db2 |
||
1368 | } {myotherproxy} |
||
1369 | |||
1370 | do_test pragma-16.6 { |
||
1371 | db2 close |
||
1372 | sqlite3 db2 test2.db |
||
1373 | set lockpath [execsql { |
||
1374 | PRAGMA lock_proxy_file=":auto:"; |
||
1375 | PRAGMA lock_proxy_file; |
||
1376 | } db2] |
||
1377 | string match "*test2.db:auto:" $lockpath |
||
1378 | } {1} |
||
1379 | |||
1380 | set sqlite_hostid_num 2 |
||
1381 | do_test pragma-16.7 { |
||
1382 | list [catch { |
||
1383 | sqlite3 db test2.db |
||
1384 | execsql { |
||
1385 | PRAGMA lock_proxy_file=":auto:"; |
||
1386 | select * from sqlite_master; |
||
1387 | } |
||
1388 | } msg] $msg |
||
1389 | } {1 {database is locked}} |
||
1390 | db close |
||
1391 | |||
1392 | do_test pragma-16.8 { |
||
1393 | list [catch { |
||
1394 | sqlite3 db test2.db |
||
1395 | execsql { select * from sqlite_master } |
||
1396 | } msg] $msg |
||
1397 | } {1 {database is locked}} |
||
1398 | |||
1399 | db2 close |
||
1400 | do_test pragma-16.8.1 { |
||
1401 | execsql { |
||
1402 | PRAGMA lock_proxy_file="yetanotherproxy"; |
||
1403 | PRAGMA lock_proxy_file; |
||
1404 | } |
||
1405 | } {yetanotherproxy} |
||
1406 | do_test pragma-16.8.2 { |
||
1407 | execsql { |
||
1408 | create table mine(x); |
||
1409 | } |
||
1410 | } {} |
||
1411 | |||
1412 | db close |
||
1413 | do_test pragma-16.9 { |
||
1414 | sqlite3 db proxytest.db |
||
1415 | set lockpath2 [execsql { |
||
1416 | PRAGMA lock_proxy_file=":auto:"; |
||
1417 | PRAGMA lock_proxy_file; |
||
1418 | } db] |
||
1419 | string match "*proxytest.db:auto:" $lockpath2 |
||
1420 | } {1} |
||
1421 | |||
1422 | set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy |
||
1423 | set sqlite_hostid_num 0 |
||
1424 | } |
||
1425 | |||
1426 | # Parsing of auto_vacuum settings. |
||
1427 | # |
||
1428 | foreach {autovac_setting val} { |
||
1429 | |||
1430 | 1 1 |
||
1431 | 2 2 |
||
1432 | 3 0 |
||
1433 | -1 0 |
||
1434 | none 0 |
||
1435 | NONE 0 |
||
1436 | NoNe 0 |
||
1437 | full 1 |
||
1438 | FULL 1 |
||
1439 | incremental 2 |
||
1440 | INCREMENTAL 2 |
||
1441 | -1234 0 |
||
1442 | 1234 0 |
||
1443 | } { |
||
1444 | do_test pragma-17.1.$autovac_setting { |
||
1445 | catch {db close} |
||
1446 | sqlite3 db :memory: |
||
1447 | execsql " |
||
1448 | PRAGMA auto_vacuum=$::autovac_setting; |
||
1449 | PRAGMA auto_vacuum; |
||
1450 | " |
||
1451 | } $val |
||
1452 | } |
||
1453 | |||
1454 | # Parsing of temp_store settings. |
||
1455 | # |
||
1456 | foreach {temp_setting val} { |
||
1457 | |||
1458 | 1 1 |
||
1459 | 2 2 |
||
1460 | 3 0 |
||
1461 | -1 0 |
||
1462 | file 1 |
||
1463 | FILE 1 |
||
1464 | fIlE 1 |
||
1465 | memory 2 |
||
1466 | MEMORY 2 |
||
1467 | MeMoRy 2 |
||
1468 | } { |
||
1469 | do_test pragma-18.1.$temp_setting { |
||
1470 | catch {db close} |
||
1471 | sqlite3 db :memory: |
||
1472 | execsql " |
||
1473 | PRAGMA temp_store=$::temp_setting; |
||
1474 | PRAGMA temp_store=$::temp_setting; |
||
1475 | PRAGMA temp_store; |
||
1476 | " |
||
1477 | } $val |
||
1478 | } |
||
1479 | |||
1480 | finish_test |