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