wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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