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.1.1.1 2012/02/21 17:04:16 misho 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: delete_file test.db test.db-journal
55: delete_file 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: forcedelete test2.db
225: forcedelete 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: forcedelete 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: forcedelete 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.8.2 {
333: execsql {PRAGMA QUICK_CHECK}
334: } {ok}
335: do_test pragma-3.9 {
336: execsql {
337: ATTACH 'testerr.db' AS t2;
338: PRAGMA integrity_check
339: }
340: } {{*** in database t2 ***
341: Page 4 is never used
342: Page 5 is never used
343: Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
344: do_test pragma-3.10 {
345: execsql {
346: PRAGMA integrity_check=1
347: }
348: } {{*** in database t2 ***
349: Page 4 is never used}}
350: do_test pragma-3.11 {
351: execsql {
352: PRAGMA integrity_check=5
353: }
354: } {{*** in database t2 ***
355: Page 4 is never used
356: Page 5 is never used
357: Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2}}
358: do_test pragma-3.12 {
359: execsql {
360: PRAGMA integrity_check=4
361: }
362: } {{*** in database t2 ***
363: Page 4 is never used
364: Page 5 is never used
365: Page 6 is never used} {rowid 1 missing from index i2}}
366: do_test pragma-3.13 {
367: execsql {
368: PRAGMA integrity_check=3
369: }
370: } {{*** in database t2 ***
371: Page 4 is never used
372: Page 5 is never used
373: Page 6 is never used}}
374: do_test pragma-3.14 {
375: execsql {
376: PRAGMA integrity_check(2)
377: }
378: } {{*** in database t2 ***
379: Page 4 is never used
380: Page 5 is never used}}
381: do_test pragma-3.15 {
382: execsql {
383: ATTACH 'testerr.db' AS t3;
384: PRAGMA integrity_check
385: }
386: } {{*** in database t2 ***
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} {*** in database t3 ***
390: Page 4 is never used
391: Page 5 is never used
392: Page 6 is never used} {rowid 1 missing from index i2} {rowid 2 missing from index i2} {wrong # of entries in index i2}}
393: do_test pragma-3.16 {
394: execsql {
395: PRAGMA integrity_check(10)
396: }
397: } {{*** in database t2 ***
398: Page 4 is never used
399: Page 5 is never used
400: 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 ***
401: Page 4 is never used
402: Page 5 is never used
403: Page 6 is never used} {rowid 1 missing from index i2}}
404: do_test pragma-3.17 {
405: execsql {
406: PRAGMA integrity_check=8
407: }
408: } {{*** in database t2 ***
409: Page 4 is never used
410: Page 5 is never used
411: 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 ***
412: Page 4 is never used
413: Page 5 is never used}}
414: do_test pragma-3.18 {
415: execsql {
416: PRAGMA integrity_check=4
417: }
418: } {{*** in database t2 ***
419: Page 4 is never used
420: Page 5 is never used
421: Page 6 is never used} {rowid 1 missing from index i2}}
422: }
423: do_test pragma-3.19 {
424: catch {db close}
425: forcedelete test.db test.db-journal
426: sqlite3 db test.db
427: db eval {PRAGMA integrity_check}
428: } {ok}
429: }
430: #exit
431:
432: # Test modifying the cache_size of an attached database.
433: ifcapable pager_pragmas&&attach {
434: do_test pragma-4.1 {
435: execsql {
436: ATTACH 'test2.db' AS aux;
437: pragma aux.cache_size;
438: pragma aux.default_cache_size;
439: }
440: } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
441: do_test pragma-4.2 {
442: execsql {
443: pragma aux.cache_size = 50;
444: pragma aux.cache_size;
445: pragma aux.default_cache_size;
446: }
447: } [list 50 $DFLT_CACHE_SZ]
448: do_test pragma-4.3 {
449: execsql {
450: pragma aux.default_cache_size = 456;
451: pragma aux.cache_size;
452: pragma aux.default_cache_size;
453: }
454: } {456 456}
455: do_test pragma-4.4 {
456: execsql {
457: pragma cache_size;
458: pragma default_cache_size;
459: }
460: } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
461: do_test pragma-4.5 {
462: execsql {
463: DETACH aux;
464: ATTACH 'test3.db' AS aux;
465: pragma aux.cache_size;
466: pragma aux.default_cache_size;
467: }
468: } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ]
469: do_test pragma-4.6 {
470: execsql {
471: DETACH aux;
472: ATTACH 'test2.db' AS aux;
473: pragma aux.cache_size;
474: pragma aux.default_cache_size;
475: }
476: } {456 456}
477: } ;# ifcapable pager_pragmas
478:
479: # Test that modifying the sync-level in the middle of a transaction is
480: # disallowed.
481: ifcapable pager_pragmas {
482: do_test pragma-5.0 {
483: execsql {
484: pragma synchronous;
485: }
486: } {2}
487: do_test pragma-5.1 {
488: catchsql {
489: BEGIN;
490: pragma synchronous = OFF;
491: }
492: } {1 {Safety level may not be changed inside a transaction}}
493: do_test pragma-5.2 {
494: execsql {
495: pragma synchronous;
496: }
497: } {2}
498: catchsql {COMMIT;}
499: } ;# ifcapable pager_pragmas
500:
501: # Test schema-query pragmas
502: #
503: ifcapable schema_pragmas {
504: ifcapable tempdb&&attach {
505: do_test pragma-6.1 {
506: set res {}
507: execsql {SELECT * FROM sqlite_temp_master}
508: foreach {idx name file} [execsql {pragma database_list}] {
509: lappend res $idx $name
510: }
511: set res
512: } {0 main 1 temp 2 aux}
513: }
514: do_test pragma-6.2 {
515: execsql {
516: CREATE TABLE t2(a,b,c);
517: pragma table_info(t2)
518: }
519: } {0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0}
520: do_test pragma-6.2.1 {
521: execsql {
522: pragma table_info;
523: }
524: } {}
525: db nullvalue <<NULL>>
526: do_test pragma-6.2.2 {
527: execsql {
528: CREATE TABLE t5(
529: a TEXT DEFAULT CURRENT_TIMESTAMP,
530: b DEFAULT (5+3),
531: c TEXT,
532: d INTEGER DEFAULT NULL,
533: e TEXT DEFAULT ''
534: );
535: PRAGMA table_info(t5);
536: }
537: } {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}
538: db nullvalue {}
539: ifcapable {foreignkey} {
540: do_test pragma-6.3.1 {
541: execsql {
542: CREATE TABLE t3(a int references t2(b), b UNIQUE);
543: pragma foreign_key_list(t3);
544: }
545: } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE}
546: do_test pragma-6.3.2 {
547: execsql {
548: pragma foreign_key_list;
549: }
550: } {}
551: do_test pragma-6.3.3 {
552: execsql {
553: pragma foreign_key_list(t3_bogus);
554: }
555: } {}
556: do_test pragma-6.3.4 {
557: execsql {
558: pragma foreign_key_list(t5);
559: }
560: } {}
561: do_test pragma-6.4 {
562: execsql {
563: pragma index_list(t3);
564: }
565: } {0 sqlite_autoindex_t3_1 1}
566: }
567: ifcapable {!foreignkey} {
568: execsql {CREATE TABLE t3(a,b UNIQUE)}
569: }
570: do_test pragma-6.5.1 {
571: execsql {
572: CREATE INDEX t3i1 ON t3(a,b);
573: pragma index_info(t3i1);
574: }
575: } {0 0 a 1 1 b}
576: do_test pragma-6.5.2 {
577: execsql {
578: pragma index_info(t3i1_bogus);
579: }
580: } {}
581:
582: ifcapable tempdb {
583: # Test for ticket #3320. When a temp table of the same name exists, make
584: # sure the schema of the main table can still be queried using
585: # "pragma table_info":
586: do_test pragma-6.6.1 {
587: execsql {
588: CREATE TABLE trial(col_main);
589: CREATE TEMP TABLE trial(col_temp);
590: }
591: } {}
592: do_test pragma-6.6.2 {
593: execsql {
594: PRAGMA table_info(trial);
595: }
596: } {0 col_temp {} 0 {} 0}
597: do_test pragma-6.6.3 {
598: execsql {
599: PRAGMA temp.table_info(trial);
600: }
601: } {0 col_temp {} 0 {} 0}
602: do_test pragma-6.6.4 {
603: execsql {
604: PRAGMA main.table_info(trial);
605: }
606: } {0 col_main {} 0 {} 0}
607: }
608:
609: do_test pragma-6.7 {
610: execsql {
611: CREATE TABLE test_table(
612: one INT NOT NULL DEFAULT -1,
613: two text,
614: three VARCHAR(45, 65) DEFAULT 'abcde',
615: four REAL DEFAULT X'abcdef',
616: five DEFAULT CURRENT_TIME
617: );
618: PRAGMA table_info(test_table);
619: }
620: } [concat \
621: {0 one INT 1 -1 0} \
622: {1 two text 0 {} 0} \
623: {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \
624: {3 four REAL 0 X'abcdef' 0} \
625: {4 five {} 0 CURRENT_TIME 0} \
626: ]
627: } ;# ifcapable schema_pragmas
628: # Miscellaneous tests
629: #
630: ifcapable schema_pragmas {
631: do_test pragma-7.1.1 {
632: # Make sure a pragma knows to read the schema if it needs to
633: db close
634: sqlite3 db test.db
635: execsql {
636: pragma index_list(t3);
637: }
638: } {0 t3i1 0 1 sqlite_autoindex_t3_1 1}
639: do_test pragma-7.1.2 {
640: execsql {
641: pragma index_list(t3_bogus);
642: }
643: } {}
644: } ;# ifcapable schema_pragmas
645: ifcapable {utf16} {
646: if {[permutation] == ""} {
647: do_test pragma-7.2 {
648: db close
649: sqlite3 db test.db
650: catchsql {
651: pragma encoding=bogus;
652: }
653: } {1 {unsupported encoding: bogus}}
654: }
655: }
656: ifcapable tempdb {
657: do_test pragma-7.3 {
658: db close
659: sqlite3 db test.db
660: execsql {
661: pragma lock_status;
662: }
663: } {main unlocked temp closed}
664: } else {
665: do_test pragma-7.3 {
666: db close
667: sqlite3 db test.db
668: execsql {
669: pragma lock_status;
670: }
671: } {main unlocked}
672: }
673:
674:
675: #----------------------------------------------------------------------
676: # Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
677: # user_version" statements.
678: #
679: # pragma-8.1: PRAGMA schema_version
680: # pragma-8.2: PRAGMA user_version
681: #
682:
683: ifcapable schema_version {
684:
685: # First check that we can set the schema version and then retrieve the
686: # same value.
687: do_test pragma-8.1.1 {
688: execsql {
689: PRAGMA schema_version = 105;
690: }
691: } {}
692: do_test pragma-8.1.2 {
693: execsql2 {
694: PRAGMA schema_version;
695: }
696: } {schema_version 105}
697: do_test pragma-8.1.3 {
698: execsql {
699: PRAGMA schema_version = 106;
700: }
701: } {}
702: do_test pragma-8.1.4 {
703: execsql {
704: PRAGMA schema_version;
705: }
706: } 106
707:
708: # Check that creating a table modifies the schema-version (this is really
709: # to verify that the value being read is in fact the schema version).
710: do_test pragma-8.1.5 {
711: execsql {
712: CREATE TABLE t4(a, b, c);
713: INSERT INTO t4 VALUES(1, 2, 3);
714: SELECT * FROM t4;
715: }
716: } {1 2 3}
717: do_test pragma-8.1.6 {
718: execsql {
719: PRAGMA schema_version;
720: }
721: } 107
722:
723: # Now open a second connection to the database. Ensure that changing the
724: # schema-version using the first connection forces the second connection
725: # to reload the schema. This has to be done using the C-API test functions,
726: # because the TCL API accounts for SCHEMA_ERROR and retries the query.
727: do_test pragma-8.1.7 {
728: sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
729: execsql {
730: SELECT * FROM t4;
731: } db2
732: } {1 2 3}
733: do_test pragma-8.1.8 {
734: execsql {
735: PRAGMA schema_version = 108;
736: }
737: } {}
738: do_test pragma-8.1.9 {
739: set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY]
740: sqlite3_step $::STMT
741: } SQLITE_ERROR
742: do_test pragma-8.1.10 {
743: sqlite3_finalize $::STMT
744: } SQLITE_SCHEMA
745:
746: # Make sure the schema-version can be manipulated in an attached database.
747: forcedelete test2.db
748: forcedelete test2.db-journal
749: ifcapable attach {
750: do_test pragma-8.1.11 {
751: execsql {
752: ATTACH 'test2.db' AS aux;
753: CREATE TABLE aux.t1(a, b, c);
754: PRAGMA aux.schema_version = 205;
755: }
756: } {}
757: do_test pragma-8.1.12 {
758: execsql {
759: PRAGMA aux.schema_version;
760: }
761: } 205
762: }
763: do_test pragma-8.1.13 {
764: execsql {
765: PRAGMA schema_version;
766: }
767: } 108
768:
769: # And check that modifying the schema-version in an attached database
770: # forces the second connection to reload the schema.
771: ifcapable attach {
772: do_test pragma-8.1.14 {
773: sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2]
774: execsql {
775: ATTACH 'test2.db' AS aux;
776: SELECT * FROM aux.t1;
777: } db2
778: } {}
779: do_test pragma-8.1.15 {
780: execsql {
781: PRAGMA aux.schema_version = 206;
782: }
783: } {}
784: do_test pragma-8.1.16 {
785: set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY]
786: sqlite3_step $::STMT
787: } SQLITE_ERROR
788: do_test pragma-8.1.17 {
789: sqlite3_finalize $::STMT
790: } SQLITE_SCHEMA
791: do_test pragma-8.1.18 {
792: db2 close
793: } {}
794: }
795:
796: # Now test that the user-version can be read and written (and that we aren't
797: # accidentally manipulating the schema-version instead).
798: do_test pragma-8.2.1 {
799: execsql2 {
800: PRAGMA user_version;
801: }
802: } {user_version 0}
803: do_test pragma-8.2.2 {
804: execsql {
805: PRAGMA user_version = 2;
806: }
807: } {}
808: do_test pragma-8.2.3.1 {
809: execsql2 {
810: PRAGMA user_version;
811: }
812: } {user_version 2}
813: do_test pragma-8.2.3.2 {
814: db close
815: sqlite3 db test.db
816: execsql {
817: PRAGMA user_version;
818: }
819: } {2}
820: do_test pragma-8.2.4.1 {
821: execsql {
822: PRAGMA schema_version;
823: }
824: } {108}
825: ifcapable vacuum {
826: do_test pragma-8.2.4.2 {
827: execsql {
828: VACUUM;
829: PRAGMA user_version;
830: }
831: } {2}
832: do_test pragma-8.2.4.3 {
833: execsql {
834: PRAGMA schema_version;
835: }
836: } {109}
837: }
838:
839: ifcapable attach {
840: db eval {ATTACH 'test2.db' AS aux}
841:
842: # Check that the user-version in the auxilary database can be manipulated (
843: # and that we aren't accidentally manipulating the same in the main db).
844: do_test pragma-8.2.5 {
845: execsql {
846: PRAGMA aux.user_version;
847: }
848: } {0}
849: do_test pragma-8.2.6 {
850: execsql {
851: PRAGMA aux.user_version = 3;
852: }
853: } {}
854: do_test pragma-8.2.7 {
855: execsql {
856: PRAGMA aux.user_version;
857: }
858: } {3}
859: do_test pragma-8.2.8 {
860: execsql {
861: PRAGMA main.user_version;
862: }
863: } {2}
864:
865: # Now check that a ROLLBACK resets the user-version if it has been modified
866: # within a transaction.
867: do_test pragma-8.2.9 {
868: execsql {
869: BEGIN;
870: PRAGMA aux.user_version = 10;
871: PRAGMA user_version = 11;
872: }
873: } {}
874: do_test pragma-8.2.10 {
875: execsql {
876: PRAGMA aux.user_version;
877: }
878: } {10}
879: do_test pragma-8.2.11 {
880: execsql {
881: PRAGMA main.user_version;
882: }
883: } {11}
884: do_test pragma-8.2.12 {
885: execsql {
886: ROLLBACK;
887: PRAGMA aux.user_version;
888: }
889: } {3}
890: do_test pragma-8.2.13 {
891: execsql {
892: PRAGMA main.user_version;
893: }
894: } {2}
895: }
896:
897: # Try a negative value for the user-version
898: do_test pragma-8.2.14 {
899: execsql {
900: PRAGMA user_version = -450;
901: }
902: } {}
903: do_test pragma-8.2.15 {
904: execsql {
905: PRAGMA user_version;
906: }
907: } {-450}
908: } ; # ifcapable schema_version
909:
910: # Check to see if TEMP_STORE is memory or disk. Return strings
911: # "memory" or "disk" as appropriate.
912: #
913: proc check_temp_store {} {
914: db eval {CREATE TEMP TABLE IF NOT EXISTS a(b)}
915: db eval {PRAGMA database_list} {
916: if {$name=="temp"} {
917: set bt [btree_from_db db 1]
918: if {[btree_ismemdb $bt]} {
919: return "memory"
920: }
921: return "disk"
922: }
923: }
924: return "unknown"
925: }
926:
927:
928: # Test temp_store and temp_store_directory pragmas
929: #
930: ifcapable pager_pragmas {
931: do_test pragma-9.1 {
932: db close
933: sqlite3 db test.db
934: execsql {
935: PRAGMA temp_store;
936: }
937: } {0}
938: if {$TEMP_STORE<=1} {
939: do_test pragma-9.1.1 {
940: check_temp_store
941: } {disk}
942: } else {
943: do_test pragma-9.1.1 {
944: check_temp_store
945: } {memory}
946: }
947:
948: do_test pragma-9.2 {
949: db close
950: sqlite3 db test.db
951: execsql {
952: PRAGMA temp_store=file;
953: PRAGMA temp_store;
954: }
955: } {1}
956: if {$TEMP_STORE==3} {
957: # When TEMP_STORE is 3, always use memory regardless of pragma settings.
958: do_test pragma-9.2.1 {
959: check_temp_store
960: } {memory}
961: } else {
962: do_test pragma-9.2.1 {
963: check_temp_store
964: } {disk}
965: }
966:
967: do_test pragma-9.3 {
968: db close
969: sqlite3 db test.db
970: execsql {
971: PRAGMA temp_store=memory;
972: PRAGMA temp_store;
973: }
974: } {2}
975: if {$TEMP_STORE==0} {
976: # When TEMP_STORE is 0, always use the disk regardless of pragma settings.
977: do_test pragma-9.3.1 {
978: check_temp_store
979: } {disk}
980: } else {
981: do_test pragma-9.3.1 {
982: check_temp_store
983: } {memory}
984: }
985:
986: do_test pragma-9.4 {
987: execsql {
988: PRAGMA temp_store_directory;
989: }
990: } {}
991: ifcapable wsd {
992: do_test pragma-9.5 {
993: set pwd [string map {' ''} [file nativename [pwd]]]
994: execsql "
995: PRAGMA temp_store_directory='$pwd';
996: "
997: } {}
998: do_test pragma-9.6 {
999: execsql {
1000: PRAGMA temp_store_directory;
1001: }
1002: } [list [file nativename [pwd]]]
1003: do_test pragma-9.7 {
1004: catchsql {
1005: PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR';
1006: }
1007: } {1 {not a writable directory}}
1008: do_test pragma-9.8 {
1009: execsql {
1010: PRAGMA temp_store_directory='';
1011: }
1012: } {}
1013: if {![info exists TEMP_STORE] || $TEMP_STORE<=1} {
1014: ifcapable tempdb {
1015: do_test pragma-9.9 {
1016: execsql {
1017: PRAGMA temp_store_directory;
1018: PRAGMA temp_store=FILE;
1019: CREATE TEMP TABLE temp_store_directory_test(a integer);
1020: INSERT INTO temp_store_directory_test values (2);
1021: SELECT * FROM temp_store_directory_test;
1022: }
1023: } {2}
1024: do_test pragma-9.10 {
1025: catchsql "
1026: PRAGMA temp_store_directory='$pwd';
1027: SELECT * FROM temp_store_directory_test;
1028: "
1029: } {1 {no such table: temp_store_directory_test}}
1030: }
1031: }
1032: }
1033: do_test pragma-9.11 {
1034: execsql {
1035: PRAGMA temp_store = 0;
1036: PRAGMA temp_store;
1037: }
1038: } {0}
1039: do_test pragma-9.12 {
1040: execsql {
1041: PRAGMA temp_store = 1;
1042: PRAGMA temp_store;
1043: }
1044: } {1}
1045: do_test pragma-9.13 {
1046: execsql {
1047: PRAGMA temp_store = 2;
1048: PRAGMA temp_store;
1049: }
1050: } {2}
1051: do_test pragma-9.14 {
1052: execsql {
1053: PRAGMA temp_store = 3;
1054: PRAGMA temp_store;
1055: }
1056: } {0}
1057: do_test pragma-9.15 {
1058: catchsql {
1059: BEGIN EXCLUSIVE;
1060: CREATE TEMP TABLE temp_table(t);
1061: INSERT INTO temp_table VALUES('valuable data');
1062: PRAGMA temp_store = 1;
1063: }
1064: } {1 {temporary storage cannot be changed from within a transaction}}
1065: do_test pragma-9.16 {
1066: execsql {
1067: SELECT * FROM temp_table;
1068: COMMIT;
1069: }
1070: } {{valuable data}}
1071:
1072: do_test pragma-9.17 {
1073: execsql {
1074: INSERT INTO temp_table VALUES('valuable data II');
1075: SELECT * FROM temp_table;
1076: }
1077: } {{valuable data} {valuable data II}}
1078:
1079: do_test pragma-9.18 {
1080: set rc [catch {
1081: db eval {SELECT t FROM temp_table} {
1082: execsql {pragma temp_store = 1}
1083: }
1084: } msg]
1085: list $rc $msg
1086: } {1 {temporary storage cannot be changed from within a transaction}}
1087:
1088: } ;# ifcapable pager_pragmas
1089:
1090: ifcapable trigger {
1091:
1092: do_test pragma-10.0 {
1093: catchsql {
1094: DROP TABLE main.t1;
1095: }
1096: execsql {
1097: PRAGMA count_changes = 1;
1098:
1099: CREATE TABLE t1(a PRIMARY KEY);
1100: CREATE TABLE t1_mirror(a);
1101: CREATE TABLE t1_mirror2(a);
1102: CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN
1103: INSERT INTO t1_mirror VALUES(new.a);
1104: END;
1105: CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
1106: INSERT INTO t1_mirror2 VALUES(new.a);
1107: END;
1108: CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN
1109: UPDATE t1_mirror SET a = new.a WHERE a = old.a;
1110: END;
1111: CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN
1112: UPDATE t1_mirror2 SET a = new.a WHERE a = old.a;
1113: END;
1114: CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN
1115: DELETE FROM t1_mirror WHERE a = old.a;
1116: END;
1117: CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
1118: DELETE FROM t1_mirror2 WHERE a = old.a;
1119: END;
1120: }
1121: } {}
1122:
1123: do_test pragma-10.1 {
1124: execsql {
1125: INSERT INTO t1 VALUES(randstr(10,10));
1126: }
1127: } {1}
1128: do_test pragma-10.2 {
1129: execsql {
1130: UPDATE t1 SET a = randstr(10,10);
1131: }
1132: } {1}
1133: do_test pragma-10.3 {
1134: execsql {
1135: DELETE FROM t1;
1136: }
1137: } {1}
1138:
1139: } ;# ifcapable trigger
1140:
1141: ifcapable schema_pragmas {
1142: do_test pragma-11.1 {
1143: execsql2 {
1144: pragma collation_list;
1145: }
1146: } {seq 0 name NOCASE seq 1 name RTRIM seq 2 name BINARY}
1147: do_test pragma-11.2 {
1148: db collate New_Collation blah...
1149: execsql {
1150: pragma collation_list;
1151: }
1152: } {0 New_Collation 1 NOCASE 2 RTRIM 3 BINARY}
1153: }
1154:
1155: ifcapable schema_pragmas&&tempdb {
1156: do_test pragma-12.1 {
1157: sqlite3 db2 test.db
1158: execsql {
1159: PRAGMA temp.table_info('abc');
1160: } db2
1161: } {}
1162: db2 close
1163:
1164: do_test pragma-12.2 {
1165: sqlite3 db2 test.db
1166: execsql {
1167: PRAGMA temp.default_cache_size = 200;
1168: PRAGMA temp.default_cache_size;
1169: } db2
1170: } {200}
1171: db2 close
1172:
1173: do_test pragma-12.3 {
1174: sqlite3 db2 test.db
1175: execsql {
1176: PRAGMA temp.cache_size = 400;
1177: PRAGMA temp.cache_size;
1178: } db2
1179: } {400}
1180: db2 close
1181: }
1182:
1183: ifcapable bloblit {
1184:
1185: do_test pragma-13.1 {
1186: execsql {
1187: DROP TABLE IF EXISTS t4;
1188: PRAGMA vdbe_trace=on;
1189: PRAGMA vdbe_listing=on;
1190: PRAGMA sql_trace=on;
1191: CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
1192: INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789');
1193: INSERT INTO t4(b) VALUES(randstr(30,30));
1194: INSERT INTO t4(b) VALUES(1.23456);
1195: INSERT INTO t4(b) VALUES(NULL);
1196: INSERT INTO t4(b) VALUES(0);
1197: INSERT INTO t4(b) SELECT b||b||b||b FROM t4;
1198: SELECT * FROM t4;
1199: }
1200: execsql {
1201: PRAGMA vdbe_trace=off;
1202: PRAGMA vdbe_listing=off;
1203: PRAGMA sql_trace=off;
1204: }
1205: } {}
1206:
1207: } ;# ifcapable bloblit
1208:
1209: ifcapable pager_pragmas {
1210: db close
1211: forcedelete test.db
1212: sqlite3 db test.db
1213:
1214: do_test pragma-14.1 {
1215: execsql { pragma auto_vacuum = 0 }
1216: execsql { pragma page_count }
1217: } {0}
1218:
1219: do_test pragma-14.2 {
1220: execsql {
1221: CREATE TABLE abc(a, b, c);
1222: PRAGMA page_count;
1223: }
1224: } {2}
1225: do_test pragma-14.2uc {
1226: execsql {pragma PAGE_COUNT}
1227: } {2}
1228:
1229: do_test pragma-14.3 {
1230: execsql {
1231: BEGIN;
1232: CREATE TABLE def(a, b, c);
1233: PRAGMA page_count;
1234: }
1235: } {3}
1236: do_test pragma-14.3uc {
1237: execsql {pragma PAGE_COUNT}
1238: } {3}
1239:
1240: do_test pragma-14.4 {
1241: set page_size [db one {pragma page_size}]
1242: expr [file size test.db] / $page_size
1243: } {2}
1244:
1245: do_test pragma-14.5 {
1246: execsql {
1247: ROLLBACK;
1248: PRAGMA page_count;
1249: }
1250: } {2}
1251:
1252: do_test pragma-14.6 {
1253: forcedelete test2.db
1254: sqlite3 db2 test2.db
1255: execsql {
1256: PRAGMA auto_vacuum = 0;
1257: CREATE TABLE t1(a, b, c);
1258: CREATE TABLE t2(a, b, c);
1259: CREATE TABLE t3(a, b, c);
1260: CREATE TABLE t4(a, b, c);
1261: } db2
1262: db2 close
1263: execsql {
1264: ATTACH 'test2.db' AS aux;
1265: PRAGMA aux.page_count;
1266: }
1267: } {5}
1268: do_test pragma-14.6uc {
1269: execsql {pragma AUX.PAGE_COUNT}
1270: } {5}
1271: }
1272:
1273: # Test that the value set using the cache_size pragma is not reset when the
1274: # schema is reloaded.
1275: #
1276: ifcapable pager_pragmas {
1277: db close
1278: sqlite3 db test.db
1279: do_test pragma-15.1 {
1280: execsql {
1281: PRAGMA cache_size=59;
1282: PRAGMA cache_size;
1283: }
1284: } {59}
1285: do_test pragma-15.2 {
1286: sqlite3 db2 test.db
1287: execsql {
1288: CREATE TABLE newtable(a, b, c);
1289: } db2
1290: db2 close
1291: } {}
1292: do_test pragma-15.3 {
1293: # Evaluating this statement will cause the schema to be reloaded (because
1294: # the schema was changed by another connection in pragma-15.2). At one
1295: # point there was a bug that reset the cache_size to its default value
1296: # when this happened.
1297: execsql { SELECT * FROM sqlite_master }
1298: execsql { PRAGMA cache_size }
1299: } {59}
1300: }
1301:
1302: # Reset the sqlite3_temp_directory variable for the next run of tests:
1303: sqlite3 dbX :memory:
1304: dbX eval {PRAGMA temp_store_directory = ""}
1305: dbX close
1306:
1307: ifcapable lock_proxy_pragmas&&prefer_proxy_locking {
1308: set sqlite_hostid_num 1
1309:
1310: set using_proxy 0
1311: foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
1312: set using_proxy $value
1313: }
1314:
1315: # Test the lock_proxy_file pragmas.
1316: #
1317: db close
1318: set env(SQLITE_FORCE_PROXY_LOCKING) "0"
1319:
1320: sqlite3 db test.db
1321: do_test pragma-16.1 {
1322: execsql {
1323: PRAGMA lock_proxy_file="mylittleproxy";
1324: select * from sqlite_master;
1325: }
1326: execsql {
1327: PRAGMA lock_proxy_file;
1328: }
1329: } {mylittleproxy}
1330:
1331: do_test pragma-16.2 {
1332: sqlite3 db2 test.db
1333: execsql {
1334: PRAGMA lock_proxy_file="mylittleproxy";
1335: } db2
1336: } {}
1337:
1338: db2 close
1339: do_test pragma-16.2.1 {
1340: sqlite3 db2 test.db
1341: execsql {
1342: PRAGMA lock_proxy_file=":auto:";
1343: select * from sqlite_master;
1344: } db2
1345: execsql {
1346: PRAGMA lock_proxy_file;
1347: } db2
1348: } {mylittleproxy}
1349:
1350: db2 close
1351: do_test pragma-16.3 {
1352: sqlite3 db2 test.db
1353: execsql {
1354: PRAGMA lock_proxy_file="myotherproxy";
1355: } db2
1356: catchsql {
1357: select * from sqlite_master;
1358: } db2
1359: } {1 {database is locked}}
1360:
1361: do_test pragma-16.4 {
1362: db2 close
1363: db close
1364: sqlite3 db2 test.db
1365: execsql {
1366: PRAGMA lock_proxy_file="myoriginalproxy";
1367: PRAGMA lock_proxy_file="myotherproxy";
1368: PRAGMA lock_proxy_file;
1369: } db2
1370: } {myotherproxy}
1371:
1372: db2 close
1373: set env(SQLITE_FORCE_PROXY_LOCKING) "1"
1374: do_test pragma-16.5 {
1375: sqlite3 db2 test.db
1376: execsql {
1377: PRAGMA lock_proxy_file=":auto:";
1378: PRAGMA lock_proxy_file;
1379: } db2
1380: } {myotherproxy}
1381:
1382: do_test pragma-16.6 {
1383: db2 close
1384: sqlite3 db2 test2.db
1385: set lockpath [execsql {
1386: PRAGMA lock_proxy_file=":auto:";
1387: PRAGMA lock_proxy_file;
1388: } db2]
1389: string match "*test2.db:auto:" $lockpath
1390: } {1}
1391:
1392: set sqlite_hostid_num 2
1393: do_test pragma-16.7 {
1394: list [catch {
1395: sqlite3 db test2.db
1396: execsql {
1397: PRAGMA lock_proxy_file=":auto:";
1398: select * from sqlite_master;
1399: }
1400: } msg] $msg
1401: } {1 {database is locked}}
1402: db close
1403:
1404: do_test pragma-16.8 {
1405: list [catch {
1406: sqlite3 db test2.db
1407: execsql { select * from sqlite_master }
1408: } msg] $msg
1409: } {1 {database is locked}}
1410:
1411: db2 close
1412: do_test pragma-16.8.1 {
1413: execsql {
1414: PRAGMA lock_proxy_file="yetanotherproxy";
1415: PRAGMA lock_proxy_file;
1416: }
1417: } {yetanotherproxy}
1418: do_test pragma-16.8.2 {
1419: execsql {
1420: create table mine(x);
1421: }
1422: } {}
1423:
1424: db close
1425: do_test pragma-16.9 {
1426: sqlite3 db proxytest.db
1427: set lockpath2 [execsql {
1428: PRAGMA lock_proxy_file=":auto:";
1429: PRAGMA lock_proxy_file;
1430: } db]
1431: string match "*proxytest.db:auto:" $lockpath2
1432: } {1}
1433:
1434: set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy
1435: set sqlite_hostid_num 0
1436: }
1437:
1438: # Parsing of auto_vacuum settings.
1439: #
1440: foreach {autovac_setting val} {
1441: 0 0
1442: 1 1
1443: 2 2
1444: 3 0
1445: -1 0
1446: none 0
1447: NONE 0
1448: NoNe 0
1449: full 1
1450: FULL 1
1451: incremental 2
1452: INCREMENTAL 2
1453: -1234 0
1454: 1234 0
1455: } {
1456: do_test pragma-17.1.$autovac_setting {
1457: catch {db close}
1458: sqlite3 db :memory:
1459: execsql "
1460: PRAGMA auto_vacuum=$::autovac_setting;
1461: PRAGMA auto_vacuum;
1462: "
1463: } $val
1464: }
1465:
1466: # Parsing of temp_store settings.
1467: #
1468: foreach {temp_setting val} {
1469: 0 0
1470: 1 1
1471: 2 2
1472: 3 0
1473: -1 0
1474: file 1
1475: FILE 1
1476: fIlE 1
1477: memory 2
1478: MEMORY 2
1479: MeMoRy 2
1480: } {
1481: do_test pragma-18.1.$temp_setting {
1482: catch {db close}
1483: sqlite3 db :memory:
1484: execsql "
1485: PRAGMA temp_store=$::temp_setting;
1486: PRAGMA temp_store=$::temp_setting;
1487: PRAGMA temp_store;
1488: "
1489: } $val
1490: }
1491:
1492: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to pragma.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test