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