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