000001 # 2002 March 6 000002 # 000003 # The author disclaims copyright to this source code. In place of 000004 # a legal notice, here is a blessing: 000005 # 000006 # May you do good and not evil. 000007 # May you find forgiveness for yourself and forgive others. 000008 # May you share freely, never taking more than you give. 000009 # 000010 #*********************************************************************** 000011 # This file implements regression tests for SQLite library. 000012 # 000013 # This file implements tests for the PRAGMA command. 000014 # 000015 # $Id: pragma.test,v 1.73 2009/01/12 14:01:45 danielk1977 Exp $ 000016 000017 set testdir [file dirname $argv0] 000018 source $testdir/tester.tcl 000019 set testprefix pragma 000020 000021 # Do not use a codec for tests in this file, as the database file is 000022 # manipulated directly using tcl scripts (using the [hexio_write] command). 000023 # 000024 do_not_use_codec 000025 000026 # Test organization: 000027 # 000028 # pragma-1.*: Test cache_size, default_cache_size and synchronous on main db. 000029 # pragma-2.*: Test synchronous on attached db. 000030 # pragma-3.*: Test detection of table/index inconsistency by integrity_check. 000031 # pragma-4.*: Test cache_size and default_cache_size on attached db. 000032 # pragma-5.*: Test that pragma synchronous may not be used inside of a 000033 # transaction. 000034 # pragma-6.*: Test schema-query pragmas. 000035 # pragma-7.*: Miscellaneous tests. 000036 # pragma-8.*: Test user_version and schema_version pragmas. 000037 # pragma-9.*: Test temp_store and temp_store_directory. 000038 # pragma-10.*: Test the count_changes pragma in the presence of triggers. 000039 # pragma-11.*: Test the collation_list pragma. 000040 # pragma-14.*: Test the page_count pragma. 000041 # pragma-15.*: Test that the value set using the cache_size pragma is not 000042 # reset when the schema is reloaded. 000043 # pragma-16.*: Test proxy locking 000044 # pragma-20.*: Test data_store_directory. 000045 # pragma-22.*: Test that "PRAGMA [db].integrity_check" respects the "db" 000046 # directive - if it is present. 000047 # 000048 000049 ifcapable !pragma { 000050 finish_test 000051 return 000052 } 000053 000054 # Capture the output of a pragma in a TEMP table. 000055 # 000056 proc capture_pragma {db tabname sql} { 000057 $db eval "DROP TABLE IF EXISTS temp.$tabname" 000058 set once 1 000059 $db eval $sql x { 000060 if {$once} { 000061 set once 0 000062 set ins "INSERT INTO $tabname VALUES" 000063 set crtab "CREATE TEMP TABLE $tabname " 000064 set sep "(" 000065 foreach col $x(*) { 000066 append ins ${sep}\$x($col) 000067 append crtab ${sep}\"$col\" 000068 set sep , 000069 } 000070 append ins ) 000071 append crtab ) 000072 $db eval $crtab 000073 } 000074 $db eval $ins 000075 } 000076 } 000077 000078 # Delete the preexisting database to avoid the special setup 000079 # that the "all.test" script does. 000080 # 000081 db close 000082 delete_file test.db test.db-journal 000083 delete_file test3.db test3.db-journal 000084 sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 000085 000086 # EVIDENCE-OF: R-13861-56665 PRAGMA schema.cache_size; PRAGMA 000087 # schema.cache_size = pages; PRAGMA schema.cache_size = -kibibytes; 000088 # Query or change the suggested maximum number of database disk pages 000089 # that SQLite will hold in memory at once per open database file. 000090 # 000091 ifcapable pager_pragmas { 000092 set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}] 000093 set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}] 000094 do_test pragma-1.1 { 000095 execsql { 000096 PRAGMA cache_size; 000097 PRAGMA default_cache_size; 000098 PRAGMA synchronous; 000099 } 000100 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 000101 do_test pragma-1.2 { 000102 # EVIDENCE-OF: R-42059-47211 If the argument N is positive then the 000103 # suggested cache size is set to N. 000104 execsql { 000105 PRAGMA synchronous=OFF; 000106 PRAGMA cache_size=1234; 000107 PRAGMA cache_size; 000108 PRAGMA default_cache_size; 000109 PRAGMA synchronous; 000110 } 000111 } [list 1234 $DFLT_CACHE_SZ 0] 000112 do_test pragma-1.3 { 000113 db close 000114 sqlite3 db test.db 000115 execsql { 000116 PRAGMA cache_size; 000117 PRAGMA default_cache_size; 000118 PRAGMA synchronous; 000119 } 000120 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 000121 do_test pragma-1.4 { 000122 execsql { 000123 PRAGMA synchronous=OFF; 000124 PRAGMA cache_size; 000125 PRAGMA default_cache_size; 000126 PRAGMA synchronous; 000127 } 000128 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0] 000129 do_test pragma-1.5 { 000130 execsql { 000131 PRAGMA cache_size=-4321; 000132 PRAGMA cache_size; 000133 PRAGMA default_cache_size; 000134 PRAGMA synchronous; 000135 } 000136 } [list -4321 $DFLT_CACHE_SZ 0] 000137 do_test pragma-1.6 { 000138 execsql { 000139 PRAGMA synchronous=ON; 000140 PRAGMA cache_size; 000141 PRAGMA default_cache_size; 000142 PRAGMA synchronous; 000143 } 000144 } [list -4321 $DFLT_CACHE_SZ 1] 000145 do_test pragma-1.7 { 000146 db close 000147 sqlite3 db test.db 000148 execsql { 000149 PRAGMA cache_size; 000150 PRAGMA default_cache_size; 000151 PRAGMA synchronous; 000152 } 000153 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 000154 do_test pragma-1.8 { 000155 execsql { 000156 PRAGMA default_cache_size=-123; 000157 PRAGMA cache_size; 000158 PRAGMA default_cache_size; 000159 PRAGMA synchronous; 000160 } 000161 } {123 123 2} 000162 do_test pragma-1.9.1 { 000163 db close 000164 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] 000165 execsql { 000166 PRAGMA cache_size; 000167 PRAGMA default_cache_size; 000168 PRAGMA synchronous; 000169 } 000170 } {123 123 2} 000171 ifcapable vacuum { 000172 do_test pragma-1.9.2 { 000173 execsql { 000174 VACUUM; 000175 PRAGMA cache_size; 000176 PRAGMA default_cache_size; 000177 PRAGMA synchronous; 000178 } 000179 } {123 123 2} 000180 } 000181 do_test pragma-1.10 { 000182 execsql { 000183 PRAGMA synchronous=NORMAL; 000184 PRAGMA cache_size; 000185 PRAGMA default_cache_size; 000186 PRAGMA synchronous; 000187 } 000188 } {123 123 1} 000189 do_test pragma-1.11.1 { 000190 execsql { 000191 PRAGMA synchronous=EXTRA; 000192 PRAGMA cache_size; 000193 PRAGMA default_cache_size; 000194 PRAGMA synchronous; 000195 } 000196 } {123 123 3} 000197 do_test pragma-1.11.2 { 000198 execsql { 000199 PRAGMA synchronous=FULL; 000200 PRAGMA cache_size; 000201 PRAGMA default_cache_size; 000202 PRAGMA synchronous; 000203 } 000204 } {123 123 2} 000205 do_test pragma-1.12 { 000206 db close 000207 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] 000208 execsql { 000209 PRAGMA cache_size; 000210 PRAGMA default_cache_size; 000211 PRAGMA synchronous; 000212 } 000213 } {123 123 2} 000214 000215 # Make sure the pragma handler understands numeric values in addition 000216 # to keywords like "off" and "full". 000217 # 000218 do_test pragma-1.13 { 000219 execsql { 000220 PRAGMA synchronous=0; 000221 PRAGMA synchronous; 000222 } 000223 } {0} 000224 do_test pragma-1.14 { 000225 execsql { 000226 PRAGMA synchronous=2; 000227 PRAGMA synchronous; 000228 } 000229 } {2} 000230 do_test pragma-1.14.1 { 000231 execsql { 000232 PRAGMA synchronous=4; 000233 PRAGMA synchronous; 000234 } 000235 } {4} 000236 do_test pragma-1.14.2 { 000237 execsql { 000238 PRAGMA synchronous=3; 000239 PRAGMA synchronous; 000240 } 000241 } {3} 000242 do_test pragma-1.14.3 { 000243 execsql { 000244 PRAGMA synchronous=8; 000245 PRAGMA synchronous; 000246 } 000247 } {0} 000248 do_test pragma-1.14.4 { 000249 execsql { 000250 PRAGMA synchronous=10; 000251 PRAGMA synchronous; 000252 } 000253 } {2} 000254 000255 do_execsql_test 1.15.1 { 000256 PRAGMA default_cache_size = 0; 000257 } 000258 do_execsql_test 1.15.2 { 000259 PRAGMA default_cache_size; 000260 } $DFLT_CACHE_SZ 000261 do_execsql_test 1.15.3 { 000262 PRAGMA default_cache_size = -500; 000263 } 000264 do_execsql_test 1.15.4 { 000265 PRAGMA default_cache_size; 000266 } 500 000267 do_execsql_test 1.15.3 { 000268 PRAGMA default_cache_size = 500; 000269 } 000270 do_execsql_test 1.15.4 { 000271 PRAGMA default_cache_size; 000272 } 500 000273 db close 000274 hexio_write test.db 48 FFFFFF00 000275 sqlite3 db test.db 000276 do_execsql_test 1.15.4 { 000277 PRAGMA default_cache_size; 000278 } 256 000279 } ;# ifcapable pager_pragmas 000280 000281 # Test turning "flag" pragmas on and off. 000282 # 000283 ifcapable debug { 000284 # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG 000285 # 000286 do_test pragma-1.15 { 000287 execsql { 000288 PRAGMA vdbe_listing=YES; 000289 PRAGMA vdbe_listing; 000290 } 000291 } {1} 000292 do_test pragma-1.16 { 000293 execsql { 000294 PRAGMA vdbe_listing=NO; 000295 PRAGMA vdbe_listing; 000296 } 000297 } {0} 000298 } 000299 000300 do_test pragma-1.17 { 000301 execsql { 000302 PRAGMA parser_trace=ON; 000303 PRAGMA parser_trace=OFF; 000304 } 000305 } {} 000306 do_test pragma-1.18 { 000307 execsql { 000308 PRAGMA bogus = -1234; -- Parsing of negative values 000309 } 000310 } {} 000311 000312 # Test modifying the safety_level of an attached database. 000313 ifcapable pager_pragmas&&attach { 000314 do_test pragma-2.1 { 000315 forcedelete test2.db 000316 forcedelete test2.db-journal 000317 execsql { 000318 ATTACH 'test2.db' AS aux; 000319 } 000320 } {} 000321 do_test pragma-2.2 { 000322 execsql { 000323 pragma aux.synchronous; 000324 } 000325 } {2} 000326 do_test pragma-2.3 { 000327 execsql { 000328 pragma aux.synchronous = OFF; 000329 pragma aux.synchronous; 000330 pragma synchronous; 000331 } 000332 } {0 2} 000333 do_test pragma-2.4 { 000334 execsql { 000335 pragma aux.synchronous = ON; 000336 pragma synchronous; 000337 pragma aux.synchronous; 000338 } 000339 } {2 1} 000340 } ;# ifcapable pager_pragmas 000341 000342 # Construct a corrupted index and make sure the integrity_check 000343 # pragma finds it. 000344 # 000345 # These tests won't work if the database is encrypted 000346 # 000347 do_test pragma-3.1 { 000348 db close 000349 forcedelete test.db test.db-journal 000350 sqlite3 db test.db 000351 execsql { 000352 PRAGMA auto_vacuum=OFF; 000353 BEGIN; 000354 CREATE TABLE t2(a,b,c); 000355 CREATE INDEX i2 ON t2(a); 000356 INSERT INTO t2 VALUES(11,2,3); 000357 INSERT INTO t2 VALUES(22,3,4); 000358 COMMIT; 000359 SELECT rowid, * from t2; 000360 } 000361 } {1 11 2 3 2 22 3 4} 000362 ifcapable attach { 000363 if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} { 000364 do_test pragma-3.2 { 000365 db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break 000366 set pgsz [db eval {PRAGMA page_size}] 000367 # overwrite the header on the rootpage of the index in order to 000368 # make the index appear to be empty. 000369 # 000370 set offset [expr {$pgsz*($rootpage-1)}] 000371 hexio_write test.db $offset 0a00000000040000000000 000372 db close 000373 sqlite3 db test.db 000374 execsql {PRAGMA integrity_check} 000375 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 000376 do_test pragma-3.3 { 000377 execsql {PRAGMA integrity_check=1} 000378 } {{row 1 missing from index i2}} 000379 do_test pragma-3.4 { 000380 execsql { 000381 ATTACH DATABASE 'test.db' AS t2; 000382 PRAGMA integrity_check 000383 } 000384 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 000385 do_test pragma-3.5 { 000386 execsql { 000387 PRAGMA integrity_check=4 000388 } 000389 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2}} 000390 do_catchsql_test pragma-3.6 { 000391 PRAGMA integrity_check=xyz 000392 } {1 {no such table: xyz}} 000393 do_catchsql_test pragma-3.6b { 000394 PRAGMA integrity_check=t2 000395 } {0 {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}} 000396 do_catchsql_test pragma-3.6c { 000397 PRAGMA integrity_check=sqlite_schema 000398 } {0 ok} 000399 do_test pragma-3.7 { 000400 execsql { 000401 PRAGMA integrity_check=0 000402 } 000403 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 000404 000405 # Add additional corruption by appending unused pages to the end of 000406 # the database file testerr.db 000407 # 000408 do_test pragma-3.8 { 000409 execsql {DETACH t2} 000410 forcedelete testerr.db testerr.db-journal 000411 set out [open testerr.db w] 000412 fconfigure $out -translation binary 000413 set in [open test.db r] 000414 fconfigure $in -translation binary 000415 puts -nonewline $out [read $in] 000416 seek $in 0 000417 puts -nonewline $out [read $in] 000418 close $in 000419 close $out 000420 hexio_write testerr.db 28 00000000 000421 execsql {REINDEX t2} 000422 execsql {PRAGMA integrity_check} 000423 } {ok} 000424 do_test pragma-3.8.1 { 000425 execsql {PRAGMA quick_check} 000426 } {ok} 000427 do_test pragma-3.8.2 { 000428 execsql {PRAGMA QUICK_CHECK} 000429 } {ok} 000430 do_test pragma-3.9a { 000431 execsql { 000432 ATTACH 'testerr.db' AS t2; 000433 PRAGMA integrity_check 000434 } 000435 } {{*** in database t2 *** 000436 Page 4 is never used 000437 Page 5 is never used 000438 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 000439 do_execsql_test pragma-3.9b { 000440 PRAGMA t2.integrity_check=t2; 000441 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 000442 do_execsql_test pragma-3.9c { 000443 PRAGMA t2.integrity_check=sqlite_schema; 000444 } {ok} 000445 do_test pragma-3.10 { 000446 execsql { 000447 PRAGMA integrity_check=1 000448 } 000449 } {{*** in database t2 *** 000450 Page 4 is never used}} 000451 do_test pragma-3.11 { 000452 execsql { 000453 PRAGMA integrity_check=5 000454 } 000455 } {{*** in database t2 *** 000456 Page 4 is never used 000457 Page 5 is never used 000458 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2}} 000459 do_test pragma-3.12 { 000460 execsql { 000461 PRAGMA integrity_check=4 000462 } 000463 } {{*** in database t2 *** 000464 Page 4 is never used 000465 Page 5 is never used 000466 Page 6 is never used} {row 1 missing from index i2}} 000467 do_test pragma-3.13 { 000468 execsql { 000469 PRAGMA integrity_check=3 000470 } 000471 } {{*** in database t2 *** 000472 Page 4 is never used 000473 Page 5 is never used 000474 Page 6 is never used}} 000475 do_test pragma-3.14 { 000476 execsql { 000477 PRAGMA integrity_check(2) 000478 } 000479 } {{*** in database t2 *** 000480 Page 4 is never used 000481 Page 5 is never used}} 000482 do_test pragma-3.15 { 000483 execsql { 000484 ATTACH 'testerr.db' AS t3; 000485 PRAGMA integrity_check 000486 } 000487 } {{*** in database t2 *** 000488 Page 4 is never used 000489 Page 5 is never used 000490 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** 000491 Page 4 is never used 000492 Page 5 is never used 000493 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 000494 do_test pragma-3.16 { 000495 execsql { 000496 PRAGMA integrity_check(10) 000497 } 000498 } {{*** in database t2 *** 000499 Page 4 is never used 000500 Page 5 is never used 000501 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** 000502 Page 4 is never used 000503 Page 5 is never used 000504 Page 6 is never used} {row 1 missing from index i2}} 000505 do_test pragma-3.17 { 000506 execsql { 000507 PRAGMA integrity_check=8 000508 } 000509 } {{*** in database t2 *** 000510 Page 4 is never used 000511 Page 5 is never used 000512 Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** 000513 Page 4 is never used 000514 Page 5 is never used}} 000515 do_test pragma-3.18 { 000516 execsql { 000517 PRAGMA integrity_check=4 000518 } 000519 } {{*** in database t2 *** 000520 Page 4 is never used 000521 Page 5 is never used 000522 Page 6 is never used} {row 1 missing from index i2}} 000523 } 000524 do_test pragma-3.19 { 000525 catch {db close} 000526 forcedelete test.db test.db-journal 000527 sqlite3 db test.db 000528 db eval {PRAGMA integrity_check} 000529 } {ok} 000530 } 000531 000532 # Verify that PRAGMA integrity_check catches UNIQUE and NOT NULL 000533 # constraint violations. 000534 # 000535 ifcapable altertable { 000536 sqlite3_db_config db DEFENSIVE 0 000537 do_execsql_test pragma-3.20 { 000538 CREATE TABLE t1(a,b); 000539 CREATE INDEX t1a ON t1(a); 000540 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(2,4),(NULL,5),(NULL,6); 000541 PRAGMA writable_schema=ON; 000542 UPDATE sqlite_master SET sql='CREATE UNIQUE INDEX t1a ON t1(a)' 000543 WHERE name='t1a'; 000544 UPDATE sqlite_master SET sql='CREATE TABLE t1(a NOT NULL,b)' 000545 WHERE name='t1'; 000546 PRAGMA writable_schema=OFF; 000547 ALTER TABLE t1 RENAME TO t1x; 000548 PRAGMA integrity_check; 000549 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a} {NULL value in t1x.a}} 000550 do_execsql_test pragma-3.21 { 000551 PRAGMA integrity_check(3); 000552 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a}} 000553 do_execsql_test pragma-3.22 { 000554 PRAGMA integrity_check(2); 000555 } {{non-unique entry in index t1a} {NULL value in t1x.a}} 000556 do_execsql_test pragma-3.23 { 000557 PRAGMA integrity_check(1); 000558 } {{non-unique entry in index t1a}} 000559 } 000560 000561 # PRAGMA integrity check (or more specifically the sqlite3BtreeCount() 000562 # interface) used to leave index cursors in an inconsistent state 000563 # which could result in an assertion fault in sqlite3BtreeKey() 000564 # called from saveCursorPosition() if content is removed from the 000565 # index while the integrity_check is still running. This test verifies 000566 # that problem has been fixed. 000567 # 000568 do_test pragma-3.30 { 000569 catch { db close } 000570 delete_file test.db 000571 sqlite3 db test.db 000572 db eval { 000573 CREATE TABLE t1(a,b,c); 000574 WITH RECURSIVE 000575 c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<100) 000576 INSERT INTO t1(a,b,c) SELECT i, printf('xyz%08x',i), 2000-i FROM c; 000577 CREATE INDEX t1a ON t1(a); 000578 CREATE INDEX t1bc ON t1(b,c); 000579 } 000580 db eval {PRAGMA integrity_check} { 000581 db eval {DELETE FROM t1} 000582 } 000583 } {} 000584 000585 # Test modifying the cache_size of an attached database. 000586 ifcapable pager_pragmas&&attach { 000587 do_test pragma-4.1 { 000588 execsql { 000589 ATTACH 'test2.db' AS aux; 000590 pragma aux.cache_size; 000591 pragma aux.default_cache_size; 000592 } 000593 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 000594 do_test pragma-4.2 { 000595 execsql { 000596 pragma aux.cache_size = 50; 000597 pragma aux.cache_size; 000598 pragma aux.default_cache_size; 000599 } 000600 } [list 50 $DFLT_CACHE_SZ] 000601 do_test pragma-4.3 { 000602 execsql { 000603 pragma aux.default_cache_size = 456; 000604 pragma aux.cache_size; 000605 pragma aux.default_cache_size; 000606 } 000607 } {456 456} 000608 do_test pragma-4.4 { 000609 execsql { 000610 pragma cache_size; 000611 pragma default_cache_size; 000612 } 000613 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 000614 do_test pragma-4.5 { 000615 execsql { 000616 DETACH aux; 000617 ATTACH 'test3.db' AS aux; 000618 pragma aux.cache_size; 000619 pragma aux.default_cache_size; 000620 } 000621 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 000622 do_test pragma-4.6 { 000623 execsql { 000624 DETACH aux; 000625 ATTACH 'test2.db' AS aux; 000626 pragma aux.cache_size; 000627 pragma aux.default_cache_size; 000628 } 000629 } {456 456} 000630 } ;# ifcapable pager_pragmas 000631 000632 # Test that modifying the sync-level in the middle of a transaction is 000633 # disallowed. 000634 ifcapable pager_pragmas { 000635 do_test pragma-5.0 { 000636 execsql { 000637 pragma synchronous; 000638 } 000639 } {2} 000640 do_test pragma-5.1 { 000641 catchsql { 000642 BEGIN; 000643 pragma synchronous = OFF; 000644 } 000645 } {1 {Safety level may not be changed inside a transaction}} 000646 do_test pragma-5.2 { 000647 execsql { 000648 pragma synchronous; 000649 } 000650 } {2} 000651 catchsql {COMMIT;} 000652 } ;# ifcapable pager_pragmas 000653 000654 # Test schema-query pragmas 000655 # 000656 ifcapable schema_pragmas { 000657 ifcapable tempdb&&attach { 000658 do_test pragma-6.1 { 000659 set res {} 000660 execsql {SELECT * FROM sqlite_temp_master} 000661 foreach {idx name file} [execsql {pragma database_list}] { 000662 lappend res $idx $name 000663 } 000664 set res 000665 } {0 main 1 temp 2 aux} 000666 } 000667 do_test pragma-6.2 { 000668 execsql { 000669 CREATE TABLE t2(a TYPE_X, b [TYPE_Y], c "TYPE_Z"); 000670 pragma table_info(t2) 000671 } 000672 } {0 a TYPE_X 0 {} 0 1 b TYPE_Y 0 {} 0 2 c TYPE_Z 0 {} 0} 000673 do_test pragma-6.2.1 { 000674 execsql { 000675 pragma table_info; 000676 } 000677 } {} 000678 db nullvalue <<NULL>> 000679 do_test pragma-6.2.2 { 000680 execsql { 000681 CREATE TABLE t5( 000682 a TEXT DEFAULT CURRENT_TIMESTAMP, 000683 b DEFAULT (5+3), 000684 c TEXT, 000685 d INTEGER DEFAULT NULL, 000686 e TEXT DEFAULT '', 000687 UNIQUE(b,c,d), 000688 PRIMARY KEY(e,b,c) 000689 ); 000690 PRAGMA table_info(t5); 000691 } 000692 } {0 a TEXT 0 CURRENT_TIMESTAMP 0 1 b {} 0 5+3 2 2 c TEXT 0 <<NULL>> 3 3 d INTEGER 0 NULL 0 4 e TEXT 0 '' 1} 000693 db nullvalue {} 000694 do_test pragma-6.2.3 { 000695 execsql { 000696 CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c); 000697 pragma table_info(t2_3) 000698 } 000699 } {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0} 000700 ifcapable {foreignkey} { 000701 do_test pragma-6.3.1 { 000702 execsql { 000703 CREATE TABLE t3(a int references t2(b), b UNIQUE); 000704 pragma foreign_key_list(t3); 000705 } 000706 } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE} 000707 do_test pragma-6.3.2 { 000708 execsql { 000709 pragma foreign_key_list; 000710 } 000711 } {} 000712 do_test pragma-6.3.3 { 000713 execsql { 000714 pragma foreign_key_list(t3_bogus); 000715 } 000716 } {} 000717 do_test pragma-6.3.4 { 000718 execsql { 000719 pragma foreign_key_list(t5); 000720 } 000721 } {} 000722 do_test pragma-6.4 { 000723 capture_pragma db out { 000724 pragma index_list(t3); 000725 } 000726 db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq} 000727 } {0 sqlite_autoindex_t3_1 1} 000728 } 000729 ifcapable {!foreignkey} { 000730 execsql {CREATE TABLE t3(a,b UNIQUE)} 000731 } 000732 do_test pragma-6.5.1 { 000733 execsql { 000734 CREATE INDEX t3i1 ON t3(a,b); 000735 } 000736 capture_pragma db out { 000737 pragma index_info(t3i1); 000738 } 000739 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno} 000740 } {0 0 a 1 1 b} 000741 000742 # EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown 000743 # by the index_info pragma, but they are listed by the index_xinfo 000744 # pragma. 000745 # 000746 do_test pragma-6.5.1b { 000747 capture_pragma db out {PRAGMA index_xinfo(t3i1)} 000748 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno} 000749 } {0 0 a 1 1 b 2 -1 {}} 000750 000751 000752 # EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This 000753 # pragma returns one row for each key column in the named index. 000754 # 000755 # (The first column of output from PRAGMA index_info is...) 000756 # EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0 000757 # means left-most.) 000758 # 000759 # (The second column of output from PRAGMA index_info is...) 000760 # EVIDENCE-OF: R-65019-08383 The rank of the column within the table 000761 # being indexed. 000762 # 000763 # (The third column of output from PRAGMA index_info is...) 000764 # EVIDENCE-OF: R-09773-34266 The name of the column being indexed. 000765 # 000766 do_execsql_test pragma-6.5.1c { 000767 CREATE INDEX t3i2 ON t3(b,a); 000768 PRAGMA index_info='t3i2'; 000769 DROP INDEX t3i2; 000770 } {0 1 b 1 0 a} 000771 000772 do_test pragma-6.5.2 { 000773 execsql { 000774 pragma index_info(t3i1_bogus); 000775 } 000776 } {} 000777 000778 ifcapable tempdb { 000779 # Test for ticket #3320. When a temp table of the same name exists, make 000780 # sure the schema of the main table can still be queried using 000781 # "pragma table_info": 000782 do_test pragma-6.6.1 { 000783 execsql { 000784 CREATE TABLE trial(col_main); 000785 CREATE TEMP TABLE trial(col_temp); 000786 } 000787 } {} 000788 do_test pragma-6.6.2 { 000789 execsql { 000790 PRAGMA table_info(trial); 000791 } 000792 } {0 col_temp {} 0 {} 0} 000793 do_test pragma-6.6.3 { 000794 execsql { 000795 PRAGMA temp.table_info(trial); 000796 } 000797 } {0 col_temp {} 0 {} 0} 000798 do_test pragma-6.6.4 { 000799 execsql { 000800 PRAGMA main.table_info(trial); 000801 } 000802 } {0 col_main {} 0 {} 0} 000803 } 000804 000805 do_test pragma-6.7 { 000806 execsql { 000807 CREATE TABLE test_table( 000808 one INT NOT NULL DEFAULT -1, 000809 two text, 000810 three VARCHAR(45, 65) DEFAULT 'abcde', 000811 four REAL DEFAULT X'abcdef', 000812 five DEFAULT CURRENT_TIME 000813 ); 000814 } 000815 capture_pragma db out {PRAGMA table_info(test_table)} 000816 db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out 000817 ORDER BY cid} 000818 } [concat \ 000819 {0 one INT 1 -1 0} \ 000820 {1 two TEXT 0 {} 0} \ 000821 {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \ 000822 {3 four REAL 0 X'abcdef' 0} \ 000823 {4 five {} 0 CURRENT_TIME 0} \ 000824 ] 000825 do_test pragma-6.8 { 000826 execsql { 000827 CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c)); 000828 PRAGMA table_info(t68); 000829 } 000830 } [concat \ 000831 {0 a {} 0 {} 1} \ 000832 {1 b {} 0 {} 2} \ 000833 {2 c {} 0 {} 4} \ 000834 ] 000835 } ;# ifcapable schema_pragmas 000836 # Miscellaneous tests 000837 # 000838 ifcapable schema_pragmas { 000839 # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This 000840 # pragma returns one row for each index associated with the given table. 000841 # 000842 do_test pragma-7.1.1 { 000843 # Make sure a pragma knows to read the schema if it needs to 000844 db close 000845 sqlite3 db test.db 000846 capture_pragma db out "PRAGMA index_list(t3)" 000847 db eval {SELECT name, "origin" FROM out ORDER BY name DESC} 000848 } {t3i1 c sqlite_autoindex_t3_1 u} 000849 do_test pragma-7.1.2 { 000850 execsql { 000851 pragma index_list(t3_bogus); 000852 } 000853 } {} 000854 } ;# ifcapable schema_pragmas 000855 ifcapable {utf16} { 000856 if {[permutation] == ""} { 000857 do_test pragma-7.2 { 000858 db close 000859 sqlite3 db test.db 000860 catchsql { 000861 pragma encoding=bogus; 000862 } 000863 } {1 {unsupported encoding: bogus}} 000864 } 000865 } 000866 ifcapable tempdb { 000867 do_test pragma-7.3 { 000868 db close 000869 sqlite3 db test.db 000870 execsql { 000871 pragma lock_status; 000872 } 000873 } {main unlocked temp closed} 000874 } else { 000875 do_test pragma-7.3 { 000876 db close 000877 sqlite3 db test.db 000878 execsql { 000879 pragma lock_status; 000880 } 000881 } {main unlocked} 000882 } 000883 000884 000885 #---------------------------------------------------------------------- 000886 # Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA 000887 # user_version" statements. 000888 # 000889 # pragma-8.1: PRAGMA schema_version 000890 # pragma-8.2: PRAGMA user_version 000891 # 000892 000893 ifcapable schema_version { 000894 000895 # First check that we can set the schema version and then retrieve the 000896 # same value. 000897 do_test pragma-8.1.1 { 000898 execsql { 000899 PRAGMA schema_version = 105; 000900 } 000901 } {} 000902 do_test pragma-8.1.2 { 000903 execsql2 { 000904 PRAGMA schema_version; 000905 } 000906 } {schema_version 105} 000907 do_test pragma-8.1.3 { 000908 execsql { 000909 PRAGMA schema_version = 106; 000910 } 000911 } {} 000912 do_test pragma-8.1.4 { 000913 execsql { 000914 PRAGMA schema_version; 000915 } 000916 } 106 000917 000918 # Check that creating a table modifies the schema-version (this is really 000919 # to verify that the value being read is in fact the schema version). 000920 do_test pragma-8.1.5 { 000921 execsql { 000922 CREATE TABLE t4(a, b, c); 000923 INSERT INTO t4 VALUES(1, 2, 3); 000924 SELECT * FROM t4; 000925 } 000926 } {1 2 3} 000927 do_test pragma-8.1.6 { 000928 execsql { 000929 PRAGMA schema_version; 000930 } 000931 } 107 000932 000933 # Now open a second connection to the database. Ensure that changing the 000934 # schema-version using the first connection forces the second connection 000935 # to reload the schema. This has to be done using the C-API test functions, 000936 # because the TCL API accounts for SCHEMA_ERROR and retries the query. 000937 do_test pragma-8.1.7 { 000938 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] 000939 execsql { 000940 SELECT * FROM t4; 000941 } db2 000942 } {1 2 3} 000943 do_test pragma-8.1.8 { 000944 execsql { 000945 PRAGMA schema_version = 108; 000946 } 000947 } {} 000948 do_test pragma-8.1.9 { 000949 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY] 000950 sqlite3_step $::STMT 000951 } SQLITE_ERROR 000952 do_test pragma-8.1.10 { 000953 sqlite3_finalize $::STMT 000954 } SQLITE_SCHEMA 000955 000956 # Make sure the schema-version can be manipulated in an attached database. 000957 forcedelete test2.db 000958 forcedelete test2.db-journal 000959 ifcapable attach { 000960 do_test pragma-8.1.11 { 000961 execsql { 000962 ATTACH 'test2.db' AS aux; 000963 CREATE TABLE aux.t1(a, b, c); 000964 PRAGMA aux.schema_version = 205; 000965 } 000966 } {} 000967 do_test pragma-8.1.12 { 000968 execsql { 000969 PRAGMA aux.schema_version; 000970 } 000971 } 205 000972 } 000973 do_test pragma-8.1.13 { 000974 execsql { 000975 PRAGMA schema_version; 000976 } 000977 } 108 000978 000979 # And check that modifying the schema-version in an attached database 000980 # forces the second connection to reload the schema. 000981 ifcapable attach { 000982 do_test pragma-8.1.14 { 000983 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] 000984 execsql { 000985 ATTACH 'test2.db' AS aux; 000986 SELECT * FROM aux.t1; 000987 } db2 000988 } {} 000989 do_test pragma-8.1.15 { 000990 execsql { 000991 PRAGMA aux.schema_version = 206; 000992 } 000993 } {} 000994 do_test pragma-8.1.16 { 000995 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY] 000996 sqlite3_step $::STMT 000997 } SQLITE_ERROR 000998 do_test pragma-8.1.17 { 000999 sqlite3_finalize $::STMT 001000 } SQLITE_SCHEMA 001001 do_test pragma-8.1.18 { 001002 db2 close 001003 } {} 001004 } 001005 001006 # Now test that the user-version can be read and written (and that we aren't 001007 # accidentally manipulating the schema-version instead). 001008 do_test pragma-8.2.1 { 001009 execsql2 { 001010 PRAGMA user_version; 001011 } 001012 } {user_version 0} 001013 do_test pragma-8.2.2 { 001014 execsql { 001015 PRAGMA user_version = 2; 001016 } 001017 } {} 001018 do_test pragma-8.2.3.1 { 001019 execsql2 { 001020 PRAGMA user_version; 001021 } 001022 } {user_version 2} 001023 do_test pragma-8.2.3.2 { 001024 db close 001025 sqlite3 db test.db 001026 execsql { 001027 PRAGMA user_version; 001028 } 001029 } {2} 001030 do_test pragma-8.2.4.1 { 001031 execsql { 001032 PRAGMA schema_version; 001033 } 001034 } {108} 001035 ifcapable vacuum { 001036 do_test pragma-8.2.4.2 { 001037 execsql { 001038 VACUUM; 001039 PRAGMA user_version; 001040 } 001041 } {2} 001042 do_test pragma-8.2.4.3 { 001043 execsql { 001044 PRAGMA schema_version; 001045 } 001046 } {109} 001047 } 001048 001049 ifcapable attach { 001050 db eval {ATTACH 'test2.db' AS aux} 001051 001052 # Check that the user-version in the auxilary database can be manipulated ( 001053 # and that we aren't accidentally manipulating the same in the main db). 001054 do_test pragma-8.2.5 { 001055 execsql { 001056 PRAGMA aux.user_version; 001057 } 001058 } {0} 001059 do_test pragma-8.2.6 { 001060 execsql { 001061 PRAGMA aux.user_version = 3; 001062 } 001063 } {} 001064 do_test pragma-8.2.7 { 001065 execsql { 001066 PRAGMA aux.user_version; 001067 } 001068 } {3} 001069 do_test pragma-8.2.8 { 001070 execsql { 001071 PRAGMA main.user_version; 001072 } 001073 } {2} 001074 001075 # Now check that a ROLLBACK resets the user-version if it has been modified 001076 # within a transaction. 001077 do_test pragma-8.2.9 { 001078 execsql { 001079 BEGIN; 001080 PRAGMA aux.user_version = 10; 001081 PRAGMA user_version = 11; 001082 } 001083 } {} 001084 do_test pragma-8.2.10 { 001085 execsql { 001086 PRAGMA aux.user_version; 001087 } 001088 } {10} 001089 do_test pragma-8.2.11 { 001090 execsql { 001091 PRAGMA main.user_version; 001092 } 001093 } {11} 001094 do_test pragma-8.2.12 { 001095 execsql { 001096 ROLLBACK; 001097 PRAGMA aux.user_version; 001098 } 001099 } {3} 001100 do_test pragma-8.2.13 { 001101 execsql { 001102 PRAGMA main.user_version; 001103 } 001104 } {2} 001105 } 001106 001107 # Try a negative value for the user-version 001108 do_test pragma-8.2.14 { 001109 execsql { 001110 PRAGMA user_version = -450; 001111 } 001112 } {} 001113 do_test pragma-8.2.15 { 001114 execsql { 001115 PRAGMA user_version; 001116 } 001117 } {-450} 001118 } ; # ifcapable schema_version 001119 001120 # Check to see if TEMP_STORE is memory or disk. Return strings 001121 # "memory" or "disk" as appropriate. 001122 # 001123 proc check_temp_store {} { 001124 db eval { 001125 PRAGMA temp.cache_size = 1; 001126 CREATE TEMP TABLE IF NOT EXISTS a(b); 001127 DELETE FROM a; 001128 INSERT INTO a VALUES(randomblob(1000)); 001129 INSERT INTO a SELECT * FROM a; 001130 INSERT INTO a SELECT * FROM a; 001131 INSERT INTO a SELECT * FROM a; 001132 INSERT INTO a SELECT * FROM a; 001133 INSERT INTO a SELECT * FROM a; 001134 INSERT INTO a SELECT * FROM a; 001135 INSERT INTO a SELECT * FROM a; 001136 INSERT INTO a SELECT * FROM a; 001137 } 001138 db eval {PRAGMA database_list} { 001139 if {$name=="temp"} { 001140 set bt [btree_from_db db 1] 001141 if {[btree_ismemdb $bt]} { 001142 return "memory" 001143 } 001144 return "disk" 001145 } 001146 } 001147 return "unknown" 001148 } 001149 001150 # Application_ID 001151 # 001152 do_test pragma-8.3.1 { 001153 execsql { 001154 PRAGMA application_id; 001155 } 001156 } {0} 001157 do_test pragma-8.3.2 { 001158 execsql {PRAGMA Application_ID(12345); PRAGMA application_id;} 001159 } {12345} 001160 001161 # Test temp_store and temp_store_directory pragmas 001162 # 001163 ifcapable pager_pragmas { 001164 do_test pragma-9.1 { 001165 db close 001166 sqlite3 db test.db 001167 execsql { 001168 PRAGMA temp_store; 001169 } 001170 } {0} 001171 if {$TEMP_STORE<=1} { 001172 do_test pragma-9.1.1 { 001173 check_temp_store 001174 } {disk} 001175 } else { 001176 do_test pragma-9.1.1 { 001177 check_temp_store 001178 } {memory} 001179 } 001180 001181 do_test pragma-9.2 { 001182 db close 001183 sqlite3 db test.db 001184 execsql { 001185 PRAGMA temp_store=file; 001186 PRAGMA temp_store; 001187 } 001188 } {1} 001189 if {$TEMP_STORE==3} { 001190 # When TEMP_STORE is 3, always use memory regardless of pragma settings. 001191 do_test pragma-9.2.1 { 001192 check_temp_store 001193 } {memory} 001194 } else { 001195 do_test pragma-9.2.1 { 001196 check_temp_store 001197 } {disk} 001198 } 001199 001200 do_test pragma-9.3 { 001201 db close 001202 sqlite3 db test.db 001203 execsql { 001204 PRAGMA temp_store=memory; 001205 PRAGMA temp_store; 001206 } 001207 } {2} 001208 if {$TEMP_STORE==0} { 001209 # When TEMP_STORE is 0, always use the disk regardless of pragma settings. 001210 do_test pragma-9.3.1 { 001211 check_temp_store 001212 } {disk} 001213 } else { 001214 do_test pragma-9.3.1 { 001215 check_temp_store 001216 } {memory} 001217 } 001218 001219 do_test pragma-9.4 { 001220 execsql { 001221 PRAGMA temp_store_directory; 001222 } 001223 } {} 001224 ifcapable wsd { 001225 do_test pragma-9.5 { 001226 set pwd [string map {' ''} [file nativename [get_pwd]]] 001227 execsql " 001228 PRAGMA temp_store_directory='$pwd'; 001229 " 001230 } {} 001231 do_test pragma-9.6 { 001232 execsql { 001233 PRAGMA temp_store_directory; 001234 } 001235 } [list [file nativename [get_pwd]]] 001236 do_test pragma-9.7 { 001237 catchsql { 001238 PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR'; 001239 } 001240 } {1 {not a writable directory}} 001241 do_test pragma-9.8 { 001242 execsql { 001243 PRAGMA temp_store_directory=''; 001244 } 001245 } {} 001246 if {![info exists TEMP_STORE] || $TEMP_STORE<=1} { 001247 ifcapable tempdb { 001248 do_test pragma-9.9 { 001249 execsql { 001250 PRAGMA temp_store_directory; 001251 PRAGMA temp_store=FILE; 001252 CREATE TEMP TABLE temp_store_directory_test(a integer); 001253 INSERT INTO temp_store_directory_test values (2); 001254 SELECT * FROM temp_store_directory_test; 001255 } 001256 } {2} 001257 do_test pragma-9.10 { 001258 catchsql " 001259 PRAGMA temp_store_directory='$pwd'; 001260 SELECT * FROM temp_store_directory_test; 001261 " 001262 } {1 {no such table: temp_store_directory_test}} 001263 } 001264 } 001265 } 001266 do_test pragma-9.11 { 001267 execsql { 001268 PRAGMA temp_store = 0; 001269 PRAGMA temp_store; 001270 } 001271 } {0} 001272 do_test pragma-9.12 { 001273 execsql { 001274 PRAGMA temp_store = 1; 001275 PRAGMA temp_store; 001276 } 001277 } {1} 001278 do_test pragma-9.13 { 001279 execsql { 001280 PRAGMA temp_store = 2; 001281 PRAGMA temp_store; 001282 } 001283 } {2} 001284 do_test pragma-9.14 { 001285 execsql { 001286 PRAGMA temp_store = 3; 001287 PRAGMA temp_store; 001288 } 001289 } {0} 001290 do_test pragma-9.15 { 001291 catchsql { 001292 BEGIN EXCLUSIVE; 001293 CREATE TEMP TABLE temp_table(t); 001294 INSERT INTO temp_table VALUES('valuable data'); 001295 PRAGMA temp_store = 1; 001296 } 001297 } {1 {temporary storage cannot be changed from within a transaction}} 001298 do_test pragma-9.16 { 001299 execsql { 001300 SELECT * FROM temp_table; 001301 COMMIT; 001302 } 001303 } {{valuable data}} 001304 001305 do_test pragma-9.17 { 001306 execsql { 001307 INSERT INTO temp_table VALUES('valuable data II'); 001308 SELECT * FROM temp_table; 001309 } 001310 } {{valuable data} {valuable data II}} 001311 001312 do_test pragma-9.18 { 001313 set rc [catch { 001314 db eval {SELECT t FROM temp_table} { 001315 execsql {pragma temp_store = 1} 001316 } 001317 } msg] 001318 list $rc $msg 001319 } {1 {temporary storage cannot be changed from within a transaction}} 001320 001321 } ;# ifcapable pager_pragmas 001322 001323 ifcapable trigger { 001324 001325 do_test pragma-10.0 { 001326 catchsql { 001327 DROP TABLE main.t1; 001328 } 001329 execsql { 001330 PRAGMA count_changes = 1; 001331 001332 CREATE TABLE t1(a PRIMARY KEY); 001333 CREATE TABLE t1_mirror(a); 001334 CREATE TABLE t1_mirror2(a); 001335 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN 001336 INSERT INTO t1_mirror VALUES(new.a); 001337 END; 001338 CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN 001339 INSERT INTO t1_mirror2 VALUES(new.a); 001340 END; 001341 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN 001342 UPDATE t1_mirror SET a = new.a WHERE a = old.a; 001343 END; 001344 CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN 001345 UPDATE t1_mirror2 SET a = new.a WHERE a = old.a; 001346 END; 001347 CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN 001348 DELETE FROM t1_mirror WHERE a = old.a; 001349 END; 001350 CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN 001351 DELETE FROM t1_mirror2 WHERE a = old.a; 001352 END; 001353 } 001354 } {} 001355 001356 do_test pragma-10.1 { 001357 execsql { 001358 INSERT INTO t1 VALUES(randstr(10,10)); 001359 } 001360 } {1} 001361 do_test pragma-10.2 { 001362 execsql { 001363 UPDATE t1 SET a = randstr(10,10); 001364 } 001365 } {1} 001366 do_test pragma-10.3 { 001367 execsql { 001368 DELETE FROM t1; 001369 } 001370 } {1} 001371 001372 } ;# ifcapable trigger 001373 001374 ifcapable schema_pragmas { 001375 do_test pragma-11.1 { 001376 execsql2 { 001377 pragma collation_list; 001378 } 001379 } {seq 0 name RTRIM seq 1 name NOCASE seq 2 name BINARY} 001380 do_test pragma-11.2 { 001381 db collate New_Collation blah... 001382 execsql { 001383 pragma collation_list; 001384 } 001385 } {0 New_Collation 1 RTRIM 2 NOCASE 3 BINARY} 001386 } 001387 001388 ifcapable schema_pragmas&&tempdb { 001389 do_test pragma-12.1 { 001390 sqlite3 db2 test.db 001391 execsql { 001392 PRAGMA temp.table_info('abc'); 001393 } db2 001394 } {} 001395 db2 close 001396 001397 do_test pragma-12.2 { 001398 sqlite3 db2 test.db 001399 execsql { 001400 PRAGMA temp.default_cache_size = 200; 001401 PRAGMA temp.default_cache_size; 001402 } db2 001403 } {200} 001404 db2 close 001405 001406 do_test pragma-12.3 { 001407 sqlite3 db2 test.db 001408 execsql { 001409 PRAGMA temp.cache_size = 400; 001410 PRAGMA temp.cache_size; 001411 } db2 001412 } {400} 001413 db2 close 001414 } 001415 001416 ifcapable bloblit { 001417 001418 do_test pragma-13.1 { 001419 execsql { 001420 DROP TABLE IF EXISTS t4; 001421 PRAGMA vdbe_trace=on; 001422 PRAGMA vdbe_listing=on; 001423 PRAGMA sql_trace=on; 001424 CREATE TABLE t4(a INTEGER PRIMARY KEY,b); 001425 INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789'); 001426 INSERT INTO t4(b) VALUES(randstr(30,30)); 001427 INSERT INTO t4(b) VALUES(1.23456); 001428 INSERT INTO t4(b) VALUES(NULL); 001429 INSERT INTO t4(b) VALUES(0); 001430 INSERT INTO t4(b) SELECT b||b||b||b FROM t4; 001431 SELECT * FROM t4; 001432 } 001433 execsql { 001434 PRAGMA vdbe_trace=off; 001435 PRAGMA vdbe_listing=off; 001436 PRAGMA sql_trace=off; 001437 } 001438 } {} 001439 001440 } ;# ifcapable bloblit 001441 001442 ifcapable pager_pragmas { 001443 db close 001444 forcedelete test.db 001445 sqlite3 db test.db 001446 001447 # EVIDENCE-OF: R-15672-33611 PRAGMA schema.page_count; Return the total 001448 # number of pages in the database file. 001449 # 001450 do_test pragma-14.1 { 001451 execsql { pragma auto_vacuum = 0 } 001452 execsql { pragma page_count; pragma main.page_count } 001453 } {0 0} 001454 001455 do_test pragma-14.2 { 001456 execsql { 001457 CREATE TABLE abc(a, b, c); 001458 PRAGMA page_count; 001459 PRAGMA main.page_count; 001460 PRAGMA temp.page_count; 001461 } 001462 } {2 2 0} 001463 do_test pragma-14.2uc { 001464 execsql {pragma PAGE_COUNT} 001465 } {2} 001466 001467 do_test pragma-14.3 { 001468 execsql { 001469 BEGIN; 001470 CREATE TABLE def(a, b, c); 001471 PRAGMA page_count; 001472 } 001473 } {3} 001474 do_test pragma-14.3uc { 001475 execsql {pragma PAGE_COUNT} 001476 } {3} 001477 001478 do_test pragma-14.4 { 001479 set page_size [db one {pragma page_size}] 001480 expr [file size test.db] / $page_size 001481 } {2} 001482 001483 do_test pragma-14.5 { 001484 execsql { 001485 ROLLBACK; 001486 PRAGMA page_count; 001487 } 001488 } {2} 001489 001490 do_test pragma-14.6 { 001491 forcedelete test2.db 001492 sqlite3 db2 test2.db 001493 execsql { 001494 PRAGMA auto_vacuum = 0; 001495 CREATE TABLE t1(a, b, c); 001496 CREATE TABLE t2(a, b, c); 001497 CREATE TABLE t3(a, b, c); 001498 CREATE TABLE t4(a, b, c); 001499 } db2 001500 db2 close 001501 execsql { 001502 ATTACH 'test2.db' AS aux; 001503 PRAGMA aux.page_count; 001504 } 001505 } {5} 001506 do_test pragma-14.6uc { 001507 execsql {pragma AUX.PAGE_COUNT} 001508 } {5} 001509 } 001510 001511 # Test that the value set using the cache_size pragma is not reset when the 001512 # schema is reloaded. 001513 # 001514 ifcapable pager_pragmas { 001515 db close 001516 sqlite3 db test.db 001517 do_test pragma-15.1 { 001518 execsql { 001519 PRAGMA cache_size=59; 001520 PRAGMA cache_size; 001521 } 001522 } {59} 001523 do_test pragma-15.2 { 001524 sqlite3 db2 test.db 001525 execsql { 001526 CREATE TABLE newtable(a, b, c); 001527 } db2 001528 db2 close 001529 } {} 001530 do_test pragma-15.3 { 001531 # Evaluating this statement will cause the schema to be reloaded (because 001532 # the schema was changed by another connection in pragma-15.2). At one 001533 # point there was a bug that reset the cache_size to its default value 001534 # when this happened. 001535 execsql { SELECT * FROM sqlite_master } 001536 execsql { PRAGMA cache_size } 001537 } {59} 001538 } 001539 001540 # Reset the sqlite3_temp_directory variable for the next run of tests: 001541 sqlite3 dbX :memory: 001542 dbX eval {PRAGMA temp_store_directory = ""} 001543 dbX close 001544 001545 ifcapable lock_proxy_pragmas&&prefer_proxy_locking { 001546 set sqlite_hostid_num 1 001547 001548 set using_proxy 0 001549 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] { 001550 set using_proxy $value 001551 } 001552 001553 # Test the lock_proxy_file pragmas. 001554 # 001555 db close 001556 set env(SQLITE_FORCE_PROXY_LOCKING) "0" 001557 001558 sqlite3 db test.db 001559 do_test pragma-16.1 { 001560 execsql { 001561 PRAGMA lock_proxy_file="mylittleproxy"; 001562 select * from sqlite_master; 001563 } 001564 execsql { 001565 PRAGMA lock_proxy_file; 001566 } 001567 } {mylittleproxy} 001568 001569 do_test pragma-16.2 { 001570 sqlite3 db2 test.db 001571 execsql { 001572 PRAGMA lock_proxy_file="mylittleproxy"; 001573 } db2 001574 } {} 001575 001576 db2 close 001577 do_test pragma-16.2.1 { 001578 sqlite3 db2 test.db 001579 execsql { 001580 PRAGMA lock_proxy_file=":auto:"; 001581 select * from sqlite_master; 001582 } db2 001583 execsql { 001584 PRAGMA lock_proxy_file; 001585 } db2 001586 } {mylittleproxy} 001587 001588 db2 close 001589 do_test pragma-16.3 { 001590 sqlite3 db2 test.db 001591 execsql { 001592 PRAGMA lock_proxy_file="myotherproxy"; 001593 } db2 001594 catchsql { 001595 select * from sqlite_master; 001596 } db2 001597 } {1 {database is locked}} 001598 001599 do_test pragma-16.4 { 001600 db2 close 001601 db close 001602 sqlite3 db2 test.db 001603 execsql { 001604 PRAGMA lock_proxy_file="myoriginalproxy"; 001605 PRAGMA lock_proxy_file="myotherproxy"; 001606 PRAGMA lock_proxy_file; 001607 } db2 001608 } {myotherproxy} 001609 001610 db2 close 001611 set env(SQLITE_FORCE_PROXY_LOCKING) "1" 001612 do_test pragma-16.5 { 001613 sqlite3 db2 test.db 001614 execsql { 001615 PRAGMA lock_proxy_file=":auto:"; 001616 PRAGMA lock_proxy_file; 001617 } db2 001618 } {myotherproxy} 001619 001620 do_test pragma-16.6 { 001621 db2 close 001622 sqlite3 db2 test2.db 001623 set lockpath [execsql { 001624 PRAGMA lock_proxy_file=":auto:"; 001625 PRAGMA lock_proxy_file; 001626 } db2] 001627 string match "*test2.db:auto:" $lockpath 001628 } {1} 001629 001630 set sqlite_hostid_num 2 001631 do_test pragma-16.7 { 001632 list [catch { 001633 sqlite3 db test2.db 001634 execsql { 001635 PRAGMA lock_proxy_file=":auto:"; 001636 select * from sqlite_master; 001637 } 001638 } msg] $msg 001639 } {1 {database is locked}} 001640 db close 001641 001642 do_test pragma-16.8 { 001643 list [catch { 001644 sqlite3 db test2.db 001645 execsql { select * from sqlite_master } 001646 } msg] $msg 001647 } {1 {database is locked}} 001648 001649 db2 close 001650 do_test pragma-16.8.1 { 001651 execsql { 001652 PRAGMA lock_proxy_file="yetanotherproxy"; 001653 PRAGMA lock_proxy_file; 001654 } 001655 } {yetanotherproxy} 001656 do_test pragma-16.8.2 { 001657 execsql { 001658 create table mine(x); 001659 } 001660 } {} 001661 001662 db close 001663 do_test pragma-16.9 { 001664 sqlite3 db proxytest.db 001665 set lockpath2 [execsql { 001666 PRAGMA lock_proxy_file=":auto:"; 001667 PRAGMA lock_proxy_file; 001668 } db] 001669 string match "*proxytest.db:auto:" $lockpath2 001670 } {1} 001671 001672 set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy 001673 set sqlite_hostid_num 0 001674 } 001675 001676 # Parsing of auto_vacuum settings. 001677 # 001678 foreach {autovac_setting val} { 001679 0 0 001680 1 1 001681 2 2 001682 3 0 001683 -1 0 001684 none 0 001685 NONE 0 001686 NoNe 0 001687 full 1 001688 FULL 1 001689 incremental 2 001690 INCREMENTAL 2 001691 -1234 0 001692 1234 0 001693 } { 001694 do_test pragma-17.1.$autovac_setting { 001695 catch {db close} 001696 sqlite3 db :memory: 001697 execsql " 001698 PRAGMA auto_vacuum=$::autovac_setting; 001699 PRAGMA auto_vacuum; 001700 " 001701 } $val 001702 } 001703 001704 # Parsing of temp_store settings. 001705 # 001706 foreach {temp_setting val} { 001707 0 0 001708 1 1 001709 2 2 001710 3 0 001711 -1 0 001712 file 1 001713 FILE 1 001714 fIlE 1 001715 memory 2 001716 MEMORY 2 001717 MeMoRy 2 001718 } { 001719 do_test pragma-18.1.$temp_setting { 001720 catch {db close} 001721 sqlite3 db :memory: 001722 execsql " 001723 PRAGMA temp_store=$::temp_setting; 001724 PRAGMA temp_store=$::temp_setting; 001725 PRAGMA temp_store; 001726 " 001727 } $val 001728 } 001729 001730 # The SQLITE_FCNTL_PRAGMA logic, with error handling. 001731 # 001732 db close 001733 testvfs tvfs 001734 sqlite3 db test.db -vfs tvfs 001735 do_test pragma-19.1 { 001736 catchsql {PRAGMA error} 001737 } {1 {SQL logic error}} 001738 do_test pragma-19.2 { 001739 catchsql {PRAGMA error='This is the error message'} 001740 } {1 {This is the error message}} 001741 do_test pragma-19.3 { 001742 catchsql {PRAGMA error='7 This is the error message'} 001743 } {1 {This is the error message}} 001744 do_test pragma-19.4 { 001745 catchsql {PRAGMA error=7} 001746 } {1 {out of memory}} 001747 do_test pragma-19.5 { 001748 file tail [lindex [execsql {PRAGMA filename}] 0] 001749 } {test.db} 001750 001751 if {$tcl_platform(platform)=="windows"} { 001752 # Test data_store_directory pragma 001753 # 001754 db close 001755 sqlite3 db test.db 001756 file mkdir data_dir 001757 do_test pragma-20.1 { 001758 catchsql {PRAGMA data_store_directory} 001759 } {0 {}} 001760 do_test pragma-20.2 { 001761 set pwd [string map {' ''} [file nativename [get_pwd]]] 001762 catchsql "PRAGMA data_store_directory='$pwd';" 001763 } {0 {}} 001764 do_test pragma-20.3 { 001765 catchsql {PRAGMA data_store_directory} 001766 } [list 0 [list [file nativename [get_pwd]]]] 001767 do_test pragma-20.4 { 001768 set pwd [string map {' ''} [file nativename \ 001769 [file join [get_pwd] data_dir]]] 001770 catchsql "PRAGMA data_store_directory='$pwd';" 001771 } {0 {}} 001772 do_test pragma-20.5 { 001773 sqlite3 db2 test2.db 001774 catchsql "PRAGMA database_list;" db2 001775 } [list 0 [list 0 main [file nativename \ 001776 [file join [get_pwd] data_dir test2.db]]]] 001777 catch {db2 close} 001778 do_test pragma-20.6 { 001779 sqlite3 db2 [file join [get_pwd] test2.db] 001780 catchsql "PRAGMA database_list;" db2 001781 } [list 0 [list 0 main [file nativename \ 001782 [file join [get_pwd] test2.db]]]] 001783 catch {db2 close} 001784 do_test pragma-20.7 { 001785 catchsql "PRAGMA data_store_directory='';" 001786 } {0 {}} 001787 do_test pragma-20.8 { 001788 catchsql {PRAGMA data_store_directory} 001789 } {0 {}} 001790 001791 forcedelete data_dir 001792 } ;# endif windows 001793 001794 database_may_be_corrupt 001795 if {![nonzero_reserved_bytes]} { 001796 001797 do_test 21.1 { 001798 # Create a corrupt database in testerr.db. And a non-corrupt at test.db. 001799 # 001800 db close 001801 forcedelete test.db 001802 sqlite3 db test.db 001803 execsql { 001804 PRAGMA page_size = 1024; 001805 PRAGMA auto_vacuum = 0; 001806 CREATE TABLE t1(a PRIMARY KEY, b); 001807 INSERT INTO t1 VALUES(1, 1); 001808 } 001809 for {set i 0} {$i < 10} {incr i} { 001810 execsql { INSERT INTO t1 SELECT a + (1 << $i), b + (1 << $i) FROM t1 } 001811 } 001812 db close 001813 forcecopy test.db testerr.db 001814 hexio_write testerr.db 15000 [string repeat 55 100] 001815 } {100} 001816 001817 set mainerr {*** in database main *** 001818 Multiple uses for byte 672 of page 15} 001819 set auxerr {*** in database aux *** 001820 Multiple uses for byte 672 of page 15} 001821 001822 set mainerr {/{\*\*\* in database main \*\*\* 001823 Multiple uses for byte 672 of page 15}.*/} 001824 set auxerr {/{\*\*\* in database aux \*\*\* 001825 Multiple uses for byte 672 of page 15}.*/} 001826 001827 do_test 22.2 { 001828 catch { db close } 001829 sqlite3 db testerr.db 001830 execsql { PRAGMA integrity_check } 001831 } $mainerr 001832 001833 do_test 22.3.1 { 001834 catch { db close } 001835 sqlite3 db test.db 001836 execsql { 001837 ATTACH 'testerr.db' AS 'aux'; 001838 PRAGMA integrity_check; 001839 } 001840 } $auxerr 001841 do_test 22.3.2 { 001842 execsql { PRAGMA main.integrity_check; } 001843 } {ok} 001844 do_test 22.3.3 { 001845 execsql { PRAGMA aux.integrity_check; } 001846 } $auxerr 001847 001848 do_test 22.4.1 { 001849 catch { db close } 001850 sqlite3 db testerr.db 001851 execsql { 001852 ATTACH 'test.db' AS 'aux'; 001853 PRAGMA integrity_check; 001854 } 001855 } $mainerr 001856 do_test 22.4.2 { 001857 execsql { PRAGMA main.integrity_check; } 001858 } $mainerr 001859 do_test 22.4.3 { 001860 execsql { PRAGMA aux.integrity_check; } 001861 } {ok} 001862 } 001863 001864 db close 001865 forcedelete test.db test.db-wal test.db-journal 001866 sqlite3 db test.db 001867 sqlite3 db2 test.db 001868 do_test 23.1 { 001869 db eval { 001870 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); 001871 CREATE INDEX i1 ON t1(b,c); 001872 CREATE INDEX i2 ON t1(c,d); 001873 CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC); 001874 CREATE INDEX i3 ON t1(d,b+c,c); 001875 CREATE TABLE t2(x INTEGER REFERENCES t1); 001876 } 001877 db2 eval {SELECT name FROM sqlite_master} 001878 } {t1 i1 i2 i2x i3 t2} 001879 do_test 23.2a { 001880 db eval { 001881 DROP INDEX i2; 001882 CREATE INDEX i2 ON t1(c,d,b); 001883 } 001884 capture_pragma db2 out {PRAGMA index_info(i2)} 001885 db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno} 001886 } {2 c | 3 d | 1 b |} 001887 001888 # EVIDENCE-OF: R-56143-29319 PRAGMA schema.index_xinfo(index-name); This 001889 # pragma returns information about every column in an index. 001890 # 001891 # EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma 001892 # returns information about every column in the index, not just the key 001893 # columns. 001894 # 001895 do_test 23.2b { 001896 capture_pragma db2 out {PRAGMA index_xinfo(i2)} 001897 db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno} 001898 } {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |} 001899 001900 # (The first column of output from PRAGMA index_xinfo is...) 001901 # EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0 001902 # means left-most. Key columns come before auxiliary columns.) 001903 # 001904 # (The second column of output from PRAGMA index_xinfo is...) 001905 # EVIDENCE-OF: R-06603-49335 The rank of the column within the table 001906 # being indexed, or -1 if the index-column is the rowid of the table 001907 # being indexed and -2 if the index is on an expression. 001908 # 001909 # (The third column of output from PRAGMA index_xinfo is...) 001910 # EVIDENCE-OF: R-40641-22898 The name of the column being indexed, or 001911 # NULL if the index-column is the rowid of the table being indexed or an 001912 # expression. 001913 # 001914 # (The fourth column of output from PRAGMA index_xinfo is...) 001915 # EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse 001916 # (DESC) order by the index and 0 otherwise. 001917 # 001918 # (The fifth column of output from PRAGMA index_xinfo is...) 001919 # EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to 001920 # compare values in the index-column. 001921 # 001922 # (The sixth column of output from PRAGMA index_xinfo is...) 001923 # EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0 001924 # if the index-column is an auxiliary column. 001925 # 001926 do_test 23.2c { 001927 db2 eval {PRAGMA index_xinfo(i2)} 001928 } {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0} 001929 do_test 23.2d { 001930 db2 eval {PRAGMA index_xinfo(i2x)} 001931 } {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0} 001932 do_test 23.2e { 001933 db2 eval {PRAGMA index_xinfo(i3)} 001934 } {0 3 d 0 BINARY 1 1 -2 {} 0 BINARY 1 2 2 c 0 BINARY 1 3 -1 {} 0 BINARY 0} 001935 001936 # EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This 001937 # pragma returns one row for each index associated with the given table. 001938 # 001939 # (The first column of output from PRAGMA index_list is...) 001940 # EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index 001941 # for internal tracking purposes. 001942 # 001943 # (The second column of output from PRAGMA index_list is...) 001944 # EVIDENCE-OF: R-35496-03635 The name of the index. 001945 # 001946 # (The third column of output from PRAGMA index_list is...) 001947 # EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not. 001948 # 001949 # (The fourth column of output from PRAGMA index_list is...) 001950 # EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE 001951 # INDEX statement, "u" if the index was created by a UNIQUE constraint, 001952 # or "pk" if the index was created by a PRIMARY KEY constraint. 001953 # 001954 do_test 23.3 { 001955 db eval { 001956 DROP INDEX IF EXISTS i3; 001957 CREATE INDEX i3 ON t1(d,b,c); 001958 } 001959 capture_pragma db2 out {PRAGMA index_list(t1)} 001960 db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq} 001961 } {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |} 001962 ifcapable altertable { 001963 do_test 23.4 { 001964 db eval { 001965 ALTER TABLE t1 ADD COLUMN e; 001966 } 001967 db2 eval { 001968 PRAGMA table_info(t1); 001969 } 001970 } {/4 e {} 0 {} 0/} 001971 } 001972 do_test 23.5 { 001973 db eval { 001974 DROP TABLE t2; 001975 CREATE TABLE t2(x, y INTEGER REFERENCES t1); 001976 } 001977 db2 eval { 001978 PRAGMA foreign_key_list(t2); 001979 } 001980 } {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE} 001981 db2 close 001982 001983 ifcapable !has_codec { 001984 reset_db 001985 do_execsql_test 24.0 { 001986 PRAGMA page_size = 1024; 001987 CREATE TABLE t1(a, b, c); 001988 CREATE INDEX i1 ON t1(b); 001989 INSERT INTO t1 VALUES('a', 'b', 'c'); 001990 PRAGMA integrity_check; 001991 } {ok} 001992 001993 set r [db one {SELECT rootpage FROM sqlite_master WHERE name = 't1'}] 001994 db close 001995 hexio_write test.db [expr $r*1024 - 16] 000000000000000701040f0f1f616263 001996 001997 sqlite3 db test.db 001998 do_catchsql_test 24.1 { 001999 SELECT * FROM t1; 002000 } {1 {database disk image is malformed}} 002001 do_catchsql_test 24.2 { 002002 PRAGMA integrity_check; 002003 } {0 {{database disk image is malformed}}} 002004 } 002005 database_never_corrupt 002006 finish_test