1# 2002 March 6 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. 12# 13# This file implements tests for the PRAGMA command. 14# 15# $Id: pragma.test,v 1.73 2009/01/12 14:01:45 danielk1977 Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set testprefix pragma 20 21# Do not use a codec for tests in this file, as the database file is 22# manipulated directly using tcl scripts (using the [hexio_write] command). 23# 24do_not_use_codec 25 26# Test organization: 27# 28# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db. 29# pragma-2.*: Test synchronous on attached db. 30# pragma-3.*: Test detection of table/index inconsistency by integrity_check. 31# pragma-4.*: Test cache_size and default_cache_size on attached db. 32# pragma-5.*: Test that pragma synchronous may not be used inside of a 33# transaction. 34# pragma-6.*: Test schema-query pragmas. 35# pragma-7.*: Miscellaneous tests. 36# pragma-8.*: Test user_version and schema_version pragmas. 37# pragma-9.*: Test temp_store and temp_store_directory. 38# pragma-10.*: Test the count_changes pragma in the presence of triggers. 39# pragma-11.*: Test the collation_list pragma. 40# pragma-14.*: Test the page_count pragma. 41# pragma-15.*: Test that the value set using the cache_size pragma is not 42# reset when the schema is reloaded. 43# pragma-16.*: Test proxy locking 44# pragma-20.*: Test data_store_directory. 45# pragma-22.*: Test that "PRAGMA [db].integrity_check" respects the "db" 46# directive - if it is present. 47# 48 49ifcapable !pragma { 50 finish_test 51 return 52} 53 54# Capture the output of a pragma in a TEMP table. 55# 56proc capture_pragma {db tabname sql} { 57 $db eval "DROP TABLE IF EXISTS temp.$tabname" 58 set once 1 59 $db eval $sql x { 60 if {$once} { 61 set once 0 62 set ins "INSERT INTO $tabname VALUES" 63 set crtab "CREATE TEMP TABLE $tabname " 64 set sep "(" 65 foreach col $x(*) { 66 append ins ${sep}\$x($col) 67 append crtab ${sep}\"$col\" 68 set sep , 69 } 70 append ins ) 71 append crtab ) 72 $db eval $crtab 73 } 74 $db eval $ins 75 } 76} 77 78# Delete the preexisting database to avoid the special setup 79# that the "all.test" script does. 80# 81db close 82delete_file test.db test.db-journal 83delete_file test3.db test3.db-journal 84sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 85 86# EVIDENCE-OF: R-13861-56665 PRAGMA schema.cache_size; PRAGMA 87# schema.cache_size = pages; PRAGMA schema.cache_size = -kibibytes; 88# Query or change the suggested maximum number of database disk pages 89# that SQLite will hold in memory at once per open database file. 90# 91ifcapable pager_pragmas { 92set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}] 93set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}] 94do_test pragma-1.1 { 95 execsql { 96 PRAGMA cache_size; 97 PRAGMA default_cache_size; 98 PRAGMA synchronous; 99 } 100} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 101do_test pragma-1.2 { 102 # EVIDENCE-OF: R-42059-47211 If the argument N is positive then the 103 # suggested cache size is set to N. 104 execsql { 105 PRAGMA synchronous=OFF; 106 PRAGMA cache_size=1234; 107 PRAGMA cache_size; 108 PRAGMA default_cache_size; 109 PRAGMA synchronous; 110 } 111} [list 1234 $DFLT_CACHE_SZ 0] 112do_test pragma-1.3 { 113 db close 114 sqlite3 db test.db 115 execsql { 116 PRAGMA cache_size; 117 PRAGMA default_cache_size; 118 PRAGMA synchronous; 119 } 120} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 121do_test pragma-1.4 { 122 execsql { 123 PRAGMA synchronous=OFF; 124 PRAGMA cache_size; 125 PRAGMA default_cache_size; 126 PRAGMA synchronous; 127 } 128} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0] 129do_test pragma-1.5 { 130 execsql { 131 PRAGMA cache_size=-4321; 132 PRAGMA cache_size; 133 PRAGMA default_cache_size; 134 PRAGMA synchronous; 135 } 136} [list -4321 $DFLT_CACHE_SZ 0] 137do_test pragma-1.6 { 138 execsql { 139 PRAGMA synchronous=ON; 140 PRAGMA cache_size; 141 PRAGMA default_cache_size; 142 PRAGMA synchronous; 143 } 144} [list -4321 $DFLT_CACHE_SZ 1] 145do_test pragma-1.7 { 146 db close 147 sqlite3 db test.db 148 execsql { 149 PRAGMA cache_size; 150 PRAGMA default_cache_size; 151 PRAGMA synchronous; 152 } 153} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 154do_test pragma-1.8 { 155 execsql { 156 PRAGMA default_cache_size=-123; 157 PRAGMA cache_size; 158 PRAGMA default_cache_size; 159 PRAGMA synchronous; 160 } 161} {123 123 2} 162do_test pragma-1.9.1 { 163 db close 164 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] 165 execsql { 166 PRAGMA cache_size; 167 PRAGMA default_cache_size; 168 PRAGMA synchronous; 169 } 170} {123 123 2} 171ifcapable vacuum { 172 do_test pragma-1.9.2 { 173 execsql { 174 VACUUM; 175 PRAGMA cache_size; 176 PRAGMA default_cache_size; 177 PRAGMA synchronous; 178 } 179 } {123 123 2} 180} 181do_test pragma-1.10 { 182 execsql { 183 PRAGMA synchronous=NORMAL; 184 PRAGMA cache_size; 185 PRAGMA default_cache_size; 186 PRAGMA synchronous; 187 } 188} {123 123 1} 189do_test pragma-1.11.1 { 190 execsql { 191 PRAGMA synchronous=EXTRA; 192 PRAGMA cache_size; 193 PRAGMA default_cache_size; 194 PRAGMA synchronous; 195 } 196} {123 123 3} 197do_test pragma-1.11.2 { 198 execsql { 199 PRAGMA synchronous=FULL; 200 PRAGMA cache_size; 201 PRAGMA default_cache_size; 202 PRAGMA synchronous; 203 } 204} {123 123 2} 205do_test pragma-1.12 { 206 db close 207 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] 208 execsql { 209 PRAGMA cache_size; 210 PRAGMA default_cache_size; 211 PRAGMA synchronous; 212 } 213} {123 123 2} 214 215# Make sure the pragma handler understands numeric values in addition 216# to keywords like "off" and "full". 217# 218do_test pragma-1.13 { 219 execsql { 220 PRAGMA synchronous=0; 221 PRAGMA synchronous; 222 } 223} {0} 224do_test pragma-1.14 { 225 execsql { 226 PRAGMA synchronous=2; 227 PRAGMA synchronous; 228 } 229} {2} 230do_test pragma-1.14.1 { 231 execsql { 232 PRAGMA synchronous=4; 233 PRAGMA synchronous; 234 } 235} {4} 236do_test pragma-1.14.2 { 237 execsql { 238 PRAGMA synchronous=3; 239 PRAGMA synchronous; 240 } 241} {3} 242do_test pragma-1.14.3 { 243 execsql { 244 PRAGMA synchronous=8; 245 PRAGMA synchronous; 246 } 247} {0} 248do_test pragma-1.14.4 { 249 execsql { 250 PRAGMA synchronous=10; 251 PRAGMA synchronous; 252 } 253} {2} 254 255do_execsql_test 1.15.1 { 256 PRAGMA default_cache_size = 0; 257} 258do_execsql_test 1.15.2 { 259 PRAGMA default_cache_size; 260} $DFLT_CACHE_SZ 261do_execsql_test 1.15.3 { 262 PRAGMA default_cache_size = -500; 263} 264do_execsql_test 1.15.4 { 265 PRAGMA default_cache_size; 266} 500 267do_execsql_test 1.15.3 { 268 PRAGMA default_cache_size = 500; 269} 270do_execsql_test 1.15.4 { 271 PRAGMA default_cache_size; 272} 500 273db close 274hexio_write test.db 48 FFFFFF00 275sqlite3 db test.db 276do_execsql_test 1.15.4 { 277 PRAGMA default_cache_size; 278} 256 279} ;# ifcapable pager_pragmas 280 281# Test turning "flag" pragmas on and off. 282# 283ifcapable debug { 284 # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG 285 # 286 do_test pragma-1.15 { 287 execsql { 288 PRAGMA vdbe_listing=YES; 289 PRAGMA vdbe_listing; 290 } 291 } {1} 292 do_test pragma-1.16 { 293 execsql { 294 PRAGMA vdbe_listing=NO; 295 PRAGMA vdbe_listing; 296 } 297 } {0} 298} 299 300do_test pragma-1.17 { 301 execsql { 302 PRAGMA parser_trace=ON; 303 PRAGMA parser_trace=OFF; 304 } 305} {} 306do_test pragma-1.18 { 307 execsql { 308 PRAGMA bogus = -1234; -- Parsing of negative values 309 } 310} {} 311 312# Test modifying the safety_level of an attached database. 313ifcapable pager_pragmas&&attach { 314 do_test pragma-2.1 { 315 forcedelete test2.db 316 forcedelete test2.db-journal 317 execsql { 318 ATTACH 'test2.db' AS aux; 319 } 320 } {} 321 do_test pragma-2.2 { 322 execsql { 323 pragma aux.synchronous; 324 } 325 } {2} 326 do_test pragma-2.3 { 327 execsql { 328 pragma aux.synchronous = OFF; 329 pragma aux.synchronous; 330 pragma synchronous; 331 } 332 } {0 2} 333 do_test pragma-2.4 { 334 execsql { 335 pragma aux.synchronous = ON; 336 pragma synchronous; 337 pragma aux.synchronous; 338 } 339 } {2 1} 340} ;# ifcapable pager_pragmas 341 342# Construct a corrupted index and make sure the integrity_check 343# pragma finds it. 344# 345# These tests won't work if the database is encrypted 346# 347do_test pragma-3.1 { 348 db close 349 forcedelete test.db test.db-journal 350 sqlite3 db test.db 351 execsql { 352 PRAGMA auto_vacuum=OFF; 353 BEGIN; 354 CREATE TABLE t2(a,b,c); 355 CREATE INDEX i2 ON t2(a); 356 INSERT INTO t2 VALUES(11,2,3); 357 INSERT INTO t2 VALUES(22,3,4); 358 COMMIT; 359 SELECT rowid, * from t2; 360 } 361} {1 11 2 3 2 22 3 4} 362ifcapable attach { 363 if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} { 364 do_test pragma-3.2 { 365 db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break 366 set pgsz [db eval {PRAGMA page_size}] 367 # overwrite the header on the rootpage of the index in order to 368 # make the index appear to be empty. 369 # 370 set offset [expr {$pgsz*($rootpage-1)}] 371 hexio_write test.db $offset 0a00000000040000000000 372 db close 373 sqlite3 db test.db 374 execsql {PRAGMA integrity_check} 375 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 376 do_test pragma-3.3 { 377 execsql {PRAGMA integrity_check=1} 378 } {{row 1 missing from index i2}} 379 do_test pragma-3.4 { 380 execsql { 381 ATTACH DATABASE 'test.db' AS t2; 382 PRAGMA integrity_check 383 } 384 } {{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}} 385 do_test pragma-3.5 { 386 execsql { 387 PRAGMA integrity_check=4 388 } 389 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2}} 390 do_catchsql_test pragma-3.6 { 391 PRAGMA integrity_check=xyz 392 } {1 {no such table: xyz}} 393 do_catchsql_test pragma-3.6b { 394 PRAGMA integrity_check=t2 395 } {0 {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}}} 396 do_catchsql_test pragma-3.6c { 397 PRAGMA integrity_check=sqlite_schema 398 } {0 ok} 399 do_test pragma-3.7 { 400 execsql { 401 PRAGMA integrity_check=0 402 } 403 } {{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}} 404 405 # Add additional corruption by appending unused pages to the end of 406 # the database file testerr.db 407 # 408 do_test pragma-3.8 { 409 execsql {DETACH t2} 410 forcedelete testerr.db testerr.db-journal 411 set out [open testerr.db w] 412 fconfigure $out -translation binary 413 set in [open test.db r] 414 fconfigure $in -translation binary 415 puts -nonewline $out [read $in] 416 seek $in 0 417 puts -nonewline $out [read $in] 418 close $in 419 close $out 420 hexio_write testerr.db 28 00000000 421 execsql {REINDEX t2} 422 execsql {PRAGMA integrity_check} 423 } {ok} 424 do_test pragma-3.8.1 { 425 execsql {PRAGMA quick_check} 426 } {ok} 427 do_test pragma-3.8.2 { 428 execsql {PRAGMA QUICK_CHECK} 429 } {ok} 430 do_test pragma-3.9a { 431 execsql { 432 ATTACH 'testerr.db' AS t2; 433 PRAGMA integrity_check 434 } 435 } {{*** in database t2 *** 436Page 4 is never used 437Page 5 is never used 438Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 439 do_execsql_test pragma-3.9b { 440 PRAGMA t2.integrity_check=t2; 441 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 442 do_execsql_test pragma-3.9c { 443 PRAGMA t2.integrity_check=sqlite_schema; 444 } {ok} 445 do_test pragma-3.10 { 446 execsql { 447 PRAGMA integrity_check=1 448 } 449 } {{*** in database t2 *** 450Page 4 is never used}} 451 do_test pragma-3.11 { 452 execsql { 453 PRAGMA integrity_check=5 454 } 455 } {{*** in database t2 *** 456Page 4 is never used 457Page 5 is never used 458Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2}} 459 do_test pragma-3.12 { 460 execsql { 461 PRAGMA integrity_check=4 462 } 463 } {{*** in database t2 *** 464Page 4 is never used 465Page 5 is never used 466Page 6 is never used} {row 1 missing from index i2}} 467 do_test pragma-3.13 { 468 execsql { 469 PRAGMA integrity_check=3 470 } 471 } {{*** in database t2 *** 472Page 4 is never used 473Page 5 is never used 474Page 6 is never used}} 475 do_test pragma-3.14 { 476 execsql { 477 PRAGMA integrity_check(2) 478 } 479 } {{*** in database t2 *** 480Page 4 is never used 481Page 5 is never used}} 482 do_test pragma-3.15 { 483 execsql { 484 ATTACH 'testerr.db' AS t3; 485 PRAGMA integrity_check 486 } 487 } {{*** in database t2 *** 488Page 4 is never used 489Page 5 is never used 490Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** 491Page 4 is never used 492Page 5 is never used 493Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 494 do_test pragma-3.16 { 495 execsql { 496 PRAGMA integrity_check(10) 497 } 498 } {{*** in database t2 *** 499Page 4 is never used 500Page 5 is never used 501Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** 502Page 4 is never used 503Page 5 is never used 504Page 6 is never used} {row 1 missing from index i2}} 505 do_test pragma-3.17 { 506 execsql { 507 PRAGMA integrity_check=8 508 } 509 } {{*** in database t2 *** 510Page 4 is never used 511Page 5 is never used 512Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {*** in database t3 *** 513Page 4 is never used 514Page 5 is never used}} 515 do_test pragma-3.18 { 516 execsql { 517 PRAGMA integrity_check=4 518 } 519 } {{*** in database t2 *** 520Page 4 is never used 521Page 5 is never used 522Page 6 is never used} {row 1 missing from index i2}} 523 } 524 do_test pragma-3.19 { 525 catch {db close} 526 forcedelete test.db test.db-journal 527 sqlite3 db test.db 528 db eval {PRAGMA integrity_check} 529 } {ok} 530} 531 532# Verify that PRAGMA integrity_check catches UNIQUE and NOT NULL 533# constraint violations. 534# 535sqlite3_db_config db DEFENSIVE 0 536do_execsql_test pragma-3.20 { 537 CREATE TABLE t1(a,b); 538 CREATE INDEX t1a ON t1(a); 539 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(2,4),(NULL,5),(NULL,6); 540 PRAGMA writable_schema=ON; 541 UPDATE sqlite_master SET sql='CREATE UNIQUE INDEX t1a ON t1(a)' 542 WHERE name='t1a'; 543 UPDATE sqlite_master SET sql='CREATE TABLE t1(a NOT NULL,b)' 544 WHERE name='t1'; 545 PRAGMA writable_schema=OFF; 546 ALTER TABLE t1 RENAME TO t1x; 547 PRAGMA integrity_check; 548} {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a} {NULL value in t1x.a}} 549do_execsql_test pragma-3.21 { 550 PRAGMA integrity_check(3); 551} {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a}} 552do_execsql_test pragma-3.22 { 553 PRAGMA integrity_check(2); 554} {{non-unique entry in index t1a} {NULL value in t1x.a}} 555do_execsql_test pragma-3.23 { 556 PRAGMA integrity_check(1); 557} {{non-unique entry in index t1a}} 558 559# PRAGMA integrity check (or more specifically the sqlite3BtreeCount() 560# interface) used to leave index cursors in an inconsistent state 561# which could result in an assertion fault in sqlite3BtreeKey() 562# called from saveCursorPosition() if content is removed from the 563# index while the integrity_check is still running. This test verifies 564# that problem has been fixed. 565# 566do_test pragma-3.30 { 567 db close 568 delete_file test.db 569 sqlite3 db test.db 570 db eval { 571 CREATE TABLE t1(a,b,c); 572 WITH RECURSIVE 573 c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<100) 574 INSERT INTO t1(a,b,c) SELECT i, printf('xyz%08x',i), 2000-i FROM c; 575 CREATE INDEX t1a ON t1(a); 576 CREATE INDEX t1bc ON t1(b,c); 577 } 578 db eval {PRAGMA integrity_check} { 579 db eval {DELETE FROM t1} 580 } 581} {} 582 583# Test modifying the cache_size of an attached database. 584ifcapable pager_pragmas&&attach { 585do_test pragma-4.1 { 586 execsql { 587 ATTACH 'test2.db' AS aux; 588 pragma aux.cache_size; 589 pragma aux.default_cache_size; 590 } 591} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 592do_test pragma-4.2 { 593 execsql { 594 pragma aux.cache_size = 50; 595 pragma aux.cache_size; 596 pragma aux.default_cache_size; 597 } 598} [list 50 $DFLT_CACHE_SZ] 599do_test pragma-4.3 { 600 execsql { 601 pragma aux.default_cache_size = 456; 602 pragma aux.cache_size; 603 pragma aux.default_cache_size; 604 } 605} {456 456} 606do_test pragma-4.4 { 607 execsql { 608 pragma cache_size; 609 pragma default_cache_size; 610 } 611} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 612do_test pragma-4.5 { 613 execsql { 614 DETACH aux; 615 ATTACH 'test3.db' AS aux; 616 pragma aux.cache_size; 617 pragma aux.default_cache_size; 618 } 619} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 620do_test pragma-4.6 { 621 execsql { 622 DETACH aux; 623 ATTACH 'test2.db' AS aux; 624 pragma aux.cache_size; 625 pragma aux.default_cache_size; 626 } 627} {456 456} 628} ;# ifcapable pager_pragmas 629 630# Test that modifying the sync-level in the middle of a transaction is 631# disallowed. 632ifcapable pager_pragmas { 633do_test pragma-5.0 { 634 execsql { 635 pragma synchronous; 636 } 637} {2} 638do_test pragma-5.1 { 639 catchsql { 640 BEGIN; 641 pragma synchronous = OFF; 642 } 643} {1 {Safety level may not be changed inside a transaction}} 644do_test pragma-5.2 { 645 execsql { 646 pragma synchronous; 647 } 648} {2} 649catchsql {COMMIT;} 650} ;# ifcapable pager_pragmas 651 652# Test schema-query pragmas 653# 654ifcapable schema_pragmas { 655ifcapable tempdb&&attach { 656 do_test pragma-6.1 { 657 set res {} 658 execsql {SELECT * FROM sqlite_temp_master} 659 foreach {idx name file} [execsql {pragma database_list}] { 660 lappend res $idx $name 661 } 662 set res 663 } {0 main 1 temp 2 aux} 664} 665do_test pragma-6.2 { 666 execsql { 667 CREATE TABLE t2(a TYPE_X, b [TYPE_Y], c "TYPE_Z"); 668 pragma table_info(t2) 669 } 670} {0 a TYPE_X 0 {} 0 1 b TYPE_Y 0 {} 0 2 c TYPE_Z 0 {} 0} 671do_test pragma-6.2.1 { 672 execsql { 673 pragma table_info; 674 } 675} {} 676db nullvalue <<NULL>> 677do_test pragma-6.2.2 { 678 execsql { 679 CREATE TABLE t5( 680 a TEXT DEFAULT CURRENT_TIMESTAMP, 681 b DEFAULT (5+3), 682 c TEXT, 683 d INTEGER DEFAULT NULL, 684 e TEXT DEFAULT '', 685 UNIQUE(b,c,d), 686 PRIMARY KEY(e,b,c) 687 ); 688 PRAGMA table_info(t5); 689 } 690} {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} 691db nullvalue {} 692do_test pragma-6.2.3 { 693 execsql { 694 CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c); 695 pragma table_info(t2_3) 696 } 697} {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0} 698ifcapable {foreignkey} { 699 do_test pragma-6.3.1 { 700 execsql { 701 CREATE TABLE t3(a int references t2(b), b UNIQUE); 702 pragma foreign_key_list(t3); 703 } 704 } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE} 705 do_test pragma-6.3.2 { 706 execsql { 707 pragma foreign_key_list; 708 } 709 } {} 710 do_test pragma-6.3.3 { 711 execsql { 712 pragma foreign_key_list(t3_bogus); 713 } 714 } {} 715 do_test pragma-6.3.4 { 716 execsql { 717 pragma foreign_key_list(t5); 718 } 719 } {} 720 do_test pragma-6.4 { 721 capture_pragma db out { 722 pragma index_list(t3); 723 } 724 db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq} 725 } {0 sqlite_autoindex_t3_1 1} 726} 727ifcapable {!foreignkey} { 728 execsql {CREATE TABLE t3(a,b UNIQUE)} 729} 730do_test pragma-6.5.1 { 731 execsql { 732 CREATE INDEX t3i1 ON t3(a,b); 733 } 734 capture_pragma db out { 735 pragma index_info(t3i1); 736 } 737 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno} 738} {0 0 a 1 1 b} 739 740# EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown 741# by the index_info pragma, but they are listed by the index_xinfo 742# pragma. 743# 744do_test pragma-6.5.1b { 745 capture_pragma db out {PRAGMA index_xinfo(t3i1)} 746 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno} 747} {0 0 a 1 1 b 2 -1 {}} 748 749 750# EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This 751# pragma returns one row for each key column in the named index. 752# 753# (The first column of output from PRAGMA index_info is...) 754# EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0 755# means left-most.) 756# 757# (The second column of output from PRAGMA index_info is...) 758# EVIDENCE-OF: R-65019-08383 The rank of the column within the table 759# being indexed. 760# 761# (The third column of output from PRAGMA index_info is...) 762# EVIDENCE-OF: R-09773-34266 The name of the column being indexed. 763# 764do_execsql_test pragma-6.5.1c { 765 CREATE INDEX t3i2 ON t3(b,a); 766 PRAGMA index_info='t3i2'; 767 DROP INDEX t3i2; 768} {0 1 b 1 0 a} 769 770do_test pragma-6.5.2 { 771 execsql { 772 pragma index_info(t3i1_bogus); 773 } 774} {} 775 776ifcapable tempdb { 777 # Test for ticket #3320. When a temp table of the same name exists, make 778 # sure the schema of the main table can still be queried using 779 # "pragma table_info": 780 do_test pragma-6.6.1 { 781 execsql { 782 CREATE TABLE trial(col_main); 783 CREATE TEMP TABLE trial(col_temp); 784 } 785 } {} 786 do_test pragma-6.6.2 { 787 execsql { 788 PRAGMA table_info(trial); 789 } 790 } {0 col_temp {} 0 {} 0} 791 do_test pragma-6.6.3 { 792 execsql { 793 PRAGMA temp.table_info(trial); 794 } 795 } {0 col_temp {} 0 {} 0} 796 do_test pragma-6.6.4 { 797 execsql { 798 PRAGMA main.table_info(trial); 799 } 800 } {0 col_main {} 0 {} 0} 801} 802 803do_test pragma-6.7 { 804 execsql { 805 CREATE TABLE test_table( 806 one INT NOT NULL DEFAULT -1, 807 two text, 808 three VARCHAR(45, 65) DEFAULT 'abcde', 809 four REAL DEFAULT X'abcdef', 810 five DEFAULT CURRENT_TIME 811 ); 812 } 813 capture_pragma db out {PRAGMA table_info(test_table)} 814 db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out 815 ORDER BY cid} 816} [concat \ 817 {0 one INT 1 -1 0} \ 818 {1 two text 0 {} 0} \ 819 {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \ 820 {3 four REAL 0 X'abcdef' 0} \ 821 {4 five {} 0 CURRENT_TIME 0} \ 822] 823do_test pragma-6.8 { 824 execsql { 825 CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c)); 826 PRAGMA table_info(t68); 827 } 828} [concat \ 829 {0 a {} 0 {} 1} \ 830 {1 b {} 0 {} 2} \ 831 {2 c {} 0 {} 4} \ 832] 833} ;# ifcapable schema_pragmas 834# Miscellaneous tests 835# 836ifcapable schema_pragmas { 837# EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This 838# pragma returns one row for each index associated with the given table. 839# 840do_test pragma-7.1.1 { 841 # Make sure a pragma knows to read the schema if it needs to 842 db close 843 sqlite3 db test.db 844 capture_pragma db out "PRAGMA index_list(t3)" 845 db eval {SELECT name, "origin" FROM out ORDER BY name DESC} 846} {t3i1 c sqlite_autoindex_t3_1 u} 847do_test pragma-7.1.2 { 848 execsql { 849 pragma index_list(t3_bogus); 850 } 851} {} 852} ;# ifcapable schema_pragmas 853ifcapable {utf16} { 854 if {[permutation] == ""} { 855 do_test pragma-7.2 { 856 db close 857 sqlite3 db test.db 858 catchsql { 859 pragma encoding=bogus; 860 } 861 } {1 {unsupported encoding: bogus}} 862 } 863} 864ifcapable tempdb { 865 do_test pragma-7.3 { 866 db close 867 sqlite3 db test.db 868 execsql { 869 pragma lock_status; 870 } 871 } {main unlocked temp closed} 872} else { 873 do_test pragma-7.3 { 874 db close 875 sqlite3 db test.db 876 execsql { 877 pragma lock_status; 878 } 879 } {main unlocked} 880} 881 882 883#---------------------------------------------------------------------- 884# Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA 885# user_version" statements. 886# 887# pragma-8.1: PRAGMA schema_version 888# pragma-8.2: PRAGMA user_version 889# 890 891ifcapable schema_version { 892 893# First check that we can set the schema version and then retrieve the 894# same value. 895do_test pragma-8.1.1 { 896 execsql { 897 PRAGMA schema_version = 105; 898 } 899} {} 900do_test pragma-8.1.2 { 901 execsql2 { 902 PRAGMA schema_version; 903 } 904} {schema_version 105} 905do_test pragma-8.1.3 { 906 execsql { 907 PRAGMA schema_version = 106; 908 } 909} {} 910do_test pragma-8.1.4 { 911 execsql { 912 PRAGMA schema_version; 913 } 914} 106 915 916# Check that creating a table modifies the schema-version (this is really 917# to verify that the value being read is in fact the schema version). 918do_test pragma-8.1.5 { 919 execsql { 920 CREATE TABLE t4(a, b, c); 921 INSERT INTO t4 VALUES(1, 2, 3); 922 SELECT * FROM t4; 923 } 924} {1 2 3} 925do_test pragma-8.1.6 { 926 execsql { 927 PRAGMA schema_version; 928 } 929} 107 930 931# Now open a second connection to the database. Ensure that changing the 932# schema-version using the first connection forces the second connection 933# to reload the schema. This has to be done using the C-API test functions, 934# because the TCL API accounts for SCHEMA_ERROR and retries the query. 935do_test pragma-8.1.7 { 936 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] 937 execsql { 938 SELECT * FROM t4; 939 } db2 940} {1 2 3} 941do_test pragma-8.1.8 { 942 execsql { 943 PRAGMA schema_version = 108; 944 } 945} {} 946do_test pragma-8.1.9 { 947 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY] 948 sqlite3_step $::STMT 949} SQLITE_ERROR 950do_test pragma-8.1.10 { 951 sqlite3_finalize $::STMT 952} SQLITE_SCHEMA 953 954# Make sure the schema-version can be manipulated in an attached database. 955forcedelete test2.db 956forcedelete test2.db-journal 957ifcapable attach { 958 do_test pragma-8.1.11 { 959 execsql { 960 ATTACH 'test2.db' AS aux; 961 CREATE TABLE aux.t1(a, b, c); 962 PRAGMA aux.schema_version = 205; 963 } 964 } {} 965 do_test pragma-8.1.12 { 966 execsql { 967 PRAGMA aux.schema_version; 968 } 969 } 205 970} 971do_test pragma-8.1.13 { 972 execsql { 973 PRAGMA schema_version; 974 } 975} 108 976 977# And check that modifying the schema-version in an attached database 978# forces the second connection to reload the schema. 979ifcapable attach { 980 do_test pragma-8.1.14 { 981 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] 982 execsql { 983 ATTACH 'test2.db' AS aux; 984 SELECT * FROM aux.t1; 985 } db2 986 } {} 987 do_test pragma-8.1.15 { 988 execsql { 989 PRAGMA aux.schema_version = 206; 990 } 991 } {} 992 do_test pragma-8.1.16 { 993 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY] 994 sqlite3_step $::STMT 995 } SQLITE_ERROR 996 do_test pragma-8.1.17 { 997 sqlite3_finalize $::STMT 998 } SQLITE_SCHEMA 999 do_test pragma-8.1.18 { 1000 db2 close 1001 } {} 1002} 1003 1004# Now test that the user-version can be read and written (and that we aren't 1005# accidentally manipulating the schema-version instead). 1006do_test pragma-8.2.1 { 1007 execsql2 { 1008 PRAGMA user_version; 1009 } 1010} {user_version 0} 1011do_test pragma-8.2.2 { 1012 execsql { 1013 PRAGMA user_version = 2; 1014 } 1015} {} 1016do_test pragma-8.2.3.1 { 1017 execsql2 { 1018 PRAGMA user_version; 1019 } 1020} {user_version 2} 1021do_test pragma-8.2.3.2 { 1022 db close 1023 sqlite3 db test.db 1024 execsql { 1025 PRAGMA user_version; 1026 } 1027} {2} 1028do_test pragma-8.2.4.1 { 1029 execsql { 1030 PRAGMA schema_version; 1031 } 1032} {108} 1033ifcapable vacuum { 1034 do_test pragma-8.2.4.2 { 1035 execsql { 1036 VACUUM; 1037 PRAGMA user_version; 1038 } 1039 } {2} 1040 do_test pragma-8.2.4.3 { 1041 execsql { 1042 PRAGMA schema_version; 1043 } 1044 } {109} 1045} 1046 1047ifcapable attach { 1048 db eval {ATTACH 'test2.db' AS aux} 1049 1050 # Check that the user-version in the auxilary database can be manipulated ( 1051 # and that we aren't accidentally manipulating the same in the main db). 1052 do_test pragma-8.2.5 { 1053 execsql { 1054 PRAGMA aux.user_version; 1055 } 1056 } {0} 1057 do_test pragma-8.2.6 { 1058 execsql { 1059 PRAGMA aux.user_version = 3; 1060 } 1061 } {} 1062 do_test pragma-8.2.7 { 1063 execsql { 1064 PRAGMA aux.user_version; 1065 } 1066 } {3} 1067 do_test pragma-8.2.8 { 1068 execsql { 1069 PRAGMA main.user_version; 1070 } 1071 } {2} 1072 1073 # Now check that a ROLLBACK resets the user-version if it has been modified 1074 # within a transaction. 1075 do_test pragma-8.2.9 { 1076 execsql { 1077 BEGIN; 1078 PRAGMA aux.user_version = 10; 1079 PRAGMA user_version = 11; 1080 } 1081 } {} 1082 do_test pragma-8.2.10 { 1083 execsql { 1084 PRAGMA aux.user_version; 1085 } 1086 } {10} 1087 do_test pragma-8.2.11 { 1088 execsql { 1089 PRAGMA main.user_version; 1090 } 1091 } {11} 1092 do_test pragma-8.2.12 { 1093 execsql { 1094 ROLLBACK; 1095 PRAGMA aux.user_version; 1096 } 1097 } {3} 1098 do_test pragma-8.2.13 { 1099 execsql { 1100 PRAGMA main.user_version; 1101 } 1102 } {2} 1103} 1104 1105# Try a negative value for the user-version 1106do_test pragma-8.2.14 { 1107 execsql { 1108 PRAGMA user_version = -450; 1109 } 1110} {} 1111do_test pragma-8.2.15 { 1112 execsql { 1113 PRAGMA user_version; 1114 } 1115} {-450} 1116} ; # ifcapable schema_version 1117 1118# Check to see if TEMP_STORE is memory or disk. Return strings 1119# "memory" or "disk" as appropriate. 1120# 1121proc check_temp_store {} { 1122 db eval { 1123 PRAGMA temp.cache_size = 1; 1124 CREATE TEMP TABLE IF NOT EXISTS a(b); 1125 DELETE FROM a; 1126 INSERT INTO a VALUES(randomblob(1000)); 1127 INSERT INTO a SELECT * FROM a; 1128 INSERT INTO a SELECT * FROM a; 1129 INSERT INTO a SELECT * FROM a; 1130 INSERT INTO a SELECT * FROM a; 1131 INSERT INTO a SELECT * FROM a; 1132 INSERT INTO a SELECT * FROM a; 1133 INSERT INTO a SELECT * FROM a; 1134 INSERT INTO a SELECT * FROM a; 1135 } 1136 db eval {PRAGMA database_list} { 1137 if {$name=="temp"} { 1138 set bt [btree_from_db db 1] 1139 if {[btree_ismemdb $bt]} { 1140 return "memory" 1141 } 1142 return "disk" 1143 } 1144 } 1145 return "unknown" 1146} 1147 1148# Application_ID 1149# 1150do_test pragma-8.3.1 { 1151 execsql { 1152 PRAGMA application_id; 1153 } 1154} {0} 1155do_test pragma-8.3.2 { 1156 execsql {PRAGMA Application_ID(12345); PRAGMA application_id;} 1157} {12345} 1158 1159# Test temp_store and temp_store_directory pragmas 1160# 1161ifcapable pager_pragmas { 1162do_test pragma-9.1 { 1163 db close 1164 sqlite3 db test.db 1165 execsql { 1166 PRAGMA temp_store; 1167 } 1168} {0} 1169if {$TEMP_STORE<=1} { 1170 do_test pragma-9.1.1 { 1171 check_temp_store 1172 } {disk} 1173} else { 1174 do_test pragma-9.1.1 { 1175 check_temp_store 1176 } {memory} 1177} 1178 1179do_test pragma-9.2 { 1180 db close 1181 sqlite3 db test.db 1182 execsql { 1183 PRAGMA temp_store=file; 1184 PRAGMA temp_store; 1185 } 1186} {1} 1187if {$TEMP_STORE==3} { 1188 # When TEMP_STORE is 3, always use memory regardless of pragma settings. 1189 do_test pragma-9.2.1 { 1190 check_temp_store 1191 } {memory} 1192} else { 1193 do_test pragma-9.2.1 { 1194 check_temp_store 1195 } {disk} 1196} 1197 1198do_test pragma-9.3 { 1199 db close 1200 sqlite3 db test.db 1201 execsql { 1202 PRAGMA temp_store=memory; 1203 PRAGMA temp_store; 1204 } 1205} {2} 1206if {$TEMP_STORE==0} { 1207 # When TEMP_STORE is 0, always use the disk regardless of pragma settings. 1208 do_test pragma-9.3.1 { 1209 check_temp_store 1210 } {disk} 1211} else { 1212 do_test pragma-9.3.1 { 1213 check_temp_store 1214 } {memory} 1215} 1216 1217do_test pragma-9.4 { 1218 execsql { 1219 PRAGMA temp_store_directory; 1220 } 1221} {} 1222ifcapable wsd { 1223 do_test pragma-9.5 { 1224 set pwd [string map {' ''} [file nativename [get_pwd]]] 1225 execsql " 1226 PRAGMA temp_store_directory='$pwd'; 1227 " 1228 } {} 1229 do_test pragma-9.6 { 1230 execsql { 1231 PRAGMA temp_store_directory; 1232 } 1233 } [list [file nativename [get_pwd]]] 1234 do_test pragma-9.7 { 1235 catchsql { 1236 PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR'; 1237 } 1238 } {1 {not a writable directory}} 1239 do_test pragma-9.8 { 1240 execsql { 1241 PRAGMA temp_store_directory=''; 1242 } 1243 } {} 1244 if {![info exists TEMP_STORE] || $TEMP_STORE<=1} { 1245 ifcapable tempdb { 1246 do_test pragma-9.9 { 1247 execsql { 1248 PRAGMA temp_store_directory; 1249 PRAGMA temp_store=FILE; 1250 CREATE TEMP TABLE temp_store_directory_test(a integer); 1251 INSERT INTO temp_store_directory_test values (2); 1252 SELECT * FROM temp_store_directory_test; 1253 } 1254 } {2} 1255 do_test pragma-9.10 { 1256 catchsql " 1257 PRAGMA temp_store_directory='$pwd'; 1258 SELECT * FROM temp_store_directory_test; 1259 " 1260 } {1 {no such table: temp_store_directory_test}} 1261 } 1262 } 1263} 1264do_test pragma-9.11 { 1265 execsql { 1266 PRAGMA temp_store = 0; 1267 PRAGMA temp_store; 1268 } 1269} {0} 1270do_test pragma-9.12 { 1271 execsql { 1272 PRAGMA temp_store = 1; 1273 PRAGMA temp_store; 1274 } 1275} {1} 1276do_test pragma-9.13 { 1277 execsql { 1278 PRAGMA temp_store = 2; 1279 PRAGMA temp_store; 1280 } 1281} {2} 1282do_test pragma-9.14 { 1283 execsql { 1284 PRAGMA temp_store = 3; 1285 PRAGMA temp_store; 1286 } 1287} {0} 1288do_test pragma-9.15 { 1289 catchsql { 1290 BEGIN EXCLUSIVE; 1291 CREATE TEMP TABLE temp_table(t); 1292 INSERT INTO temp_table VALUES('valuable data'); 1293 PRAGMA temp_store = 1; 1294 } 1295} {1 {temporary storage cannot be changed from within a transaction}} 1296do_test pragma-9.16 { 1297 execsql { 1298 SELECT * FROM temp_table; 1299 COMMIT; 1300 } 1301} {{valuable data}} 1302 1303do_test pragma-9.17 { 1304 execsql { 1305 INSERT INTO temp_table VALUES('valuable data II'); 1306 SELECT * FROM temp_table; 1307 } 1308} {{valuable data} {valuable data II}} 1309 1310do_test pragma-9.18 { 1311 set rc [catch { 1312 db eval {SELECT t FROM temp_table} { 1313 execsql {pragma temp_store = 1} 1314 } 1315 } msg] 1316 list $rc $msg 1317} {1 {temporary storage cannot be changed from within a transaction}} 1318 1319} ;# ifcapable pager_pragmas 1320 1321ifcapable trigger { 1322 1323do_test pragma-10.0 { 1324 catchsql { 1325 DROP TABLE main.t1; 1326 } 1327 execsql { 1328 PRAGMA count_changes = 1; 1329 1330 CREATE TABLE t1(a PRIMARY KEY); 1331 CREATE TABLE t1_mirror(a); 1332 CREATE TABLE t1_mirror2(a); 1333 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN 1334 INSERT INTO t1_mirror VALUES(new.a); 1335 END; 1336 CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN 1337 INSERT INTO t1_mirror2 VALUES(new.a); 1338 END; 1339 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN 1340 UPDATE t1_mirror SET a = new.a WHERE a = old.a; 1341 END; 1342 CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN 1343 UPDATE t1_mirror2 SET a = new.a WHERE a = old.a; 1344 END; 1345 CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN 1346 DELETE FROM t1_mirror WHERE a = old.a; 1347 END; 1348 CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN 1349 DELETE FROM t1_mirror2 WHERE a = old.a; 1350 END; 1351 } 1352} {} 1353 1354do_test pragma-10.1 { 1355 execsql { 1356 INSERT INTO t1 VALUES(randstr(10,10)); 1357 } 1358} {1} 1359do_test pragma-10.2 { 1360 execsql { 1361 UPDATE t1 SET a = randstr(10,10); 1362 } 1363} {1} 1364do_test pragma-10.3 { 1365 execsql { 1366 DELETE FROM t1; 1367 } 1368} {1} 1369 1370} ;# ifcapable trigger 1371 1372ifcapable schema_pragmas { 1373 do_test pragma-11.1 { 1374 execsql2 { 1375 pragma collation_list; 1376 } 1377 } {seq 0 name RTRIM seq 1 name NOCASE seq 2 name BINARY} 1378 do_test pragma-11.2 { 1379 db collate New_Collation blah... 1380 execsql { 1381 pragma collation_list; 1382 } 1383 } {0 New_Collation 1 RTRIM 2 NOCASE 3 BINARY} 1384} 1385 1386ifcapable schema_pragmas&&tempdb { 1387 do_test pragma-12.1 { 1388 sqlite3 db2 test.db 1389 execsql { 1390 PRAGMA temp.table_info('abc'); 1391 } db2 1392 } {} 1393 db2 close 1394 1395 do_test pragma-12.2 { 1396 sqlite3 db2 test.db 1397 execsql { 1398 PRAGMA temp.default_cache_size = 200; 1399 PRAGMA temp.default_cache_size; 1400 } db2 1401 } {200} 1402 db2 close 1403 1404 do_test pragma-12.3 { 1405 sqlite3 db2 test.db 1406 execsql { 1407 PRAGMA temp.cache_size = 400; 1408 PRAGMA temp.cache_size; 1409 } db2 1410 } {400} 1411 db2 close 1412} 1413 1414ifcapable bloblit { 1415 1416do_test pragma-13.1 { 1417 execsql { 1418 DROP TABLE IF EXISTS t4; 1419 PRAGMA vdbe_trace=on; 1420 PRAGMA vdbe_listing=on; 1421 PRAGMA sql_trace=on; 1422 CREATE TABLE t4(a INTEGER PRIMARY KEY,b); 1423 INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789'); 1424 INSERT INTO t4(b) VALUES(randstr(30,30)); 1425 INSERT INTO t4(b) VALUES(1.23456); 1426 INSERT INTO t4(b) VALUES(NULL); 1427 INSERT INTO t4(b) VALUES(0); 1428 INSERT INTO t4(b) SELECT b||b||b||b FROM t4; 1429 SELECT * FROM t4; 1430 } 1431 execsql { 1432 PRAGMA vdbe_trace=off; 1433 PRAGMA vdbe_listing=off; 1434 PRAGMA sql_trace=off; 1435 } 1436} {} 1437 1438} ;# ifcapable bloblit 1439 1440ifcapable pager_pragmas { 1441 db close 1442 forcedelete test.db 1443 sqlite3 db test.db 1444 1445 # EVIDENCE-OF: R-15672-33611 PRAGMA schema.page_count; Return the total 1446 # number of pages in the database file. 1447 # 1448 do_test pragma-14.1 { 1449 execsql { pragma auto_vacuum = 0 } 1450 execsql { pragma page_count; pragma main.page_count } 1451 } {0 0} 1452 1453 do_test pragma-14.2 { 1454 execsql { 1455 CREATE TABLE abc(a, b, c); 1456 PRAGMA page_count; 1457 PRAGMA main.page_count; 1458 PRAGMA temp.page_count; 1459 } 1460 } {2 2 0} 1461 do_test pragma-14.2uc { 1462 execsql {pragma PAGE_COUNT} 1463 } {2} 1464 1465 do_test pragma-14.3 { 1466 execsql { 1467 BEGIN; 1468 CREATE TABLE def(a, b, c); 1469 PRAGMA page_count; 1470 } 1471 } {3} 1472 do_test pragma-14.3uc { 1473 execsql {pragma PAGE_COUNT} 1474 } {3} 1475 1476 do_test pragma-14.4 { 1477 set page_size [db one {pragma page_size}] 1478 expr [file size test.db] / $page_size 1479 } {2} 1480 1481 do_test pragma-14.5 { 1482 execsql { 1483 ROLLBACK; 1484 PRAGMA page_count; 1485 } 1486 } {2} 1487 1488 do_test pragma-14.6 { 1489 forcedelete test2.db 1490 sqlite3 db2 test2.db 1491 execsql { 1492 PRAGMA auto_vacuum = 0; 1493 CREATE TABLE t1(a, b, c); 1494 CREATE TABLE t2(a, b, c); 1495 CREATE TABLE t3(a, b, c); 1496 CREATE TABLE t4(a, b, c); 1497 } db2 1498 db2 close 1499 execsql { 1500 ATTACH 'test2.db' AS aux; 1501 PRAGMA aux.page_count; 1502 } 1503 } {5} 1504 do_test pragma-14.6uc { 1505 execsql {pragma AUX.PAGE_COUNT} 1506 } {5} 1507} 1508 1509# Test that the value set using the cache_size pragma is not reset when the 1510# schema is reloaded. 1511# 1512ifcapable pager_pragmas { 1513 db close 1514 sqlite3 db test.db 1515 do_test pragma-15.1 { 1516 execsql { 1517 PRAGMA cache_size=59; 1518 PRAGMA cache_size; 1519 } 1520 } {59} 1521 do_test pragma-15.2 { 1522 sqlite3 db2 test.db 1523 execsql { 1524 CREATE TABLE newtable(a, b, c); 1525 } db2 1526 db2 close 1527 } {} 1528 do_test pragma-15.3 { 1529 # Evaluating this statement will cause the schema to be reloaded (because 1530 # the schema was changed by another connection in pragma-15.2). At one 1531 # point there was a bug that reset the cache_size to its default value 1532 # when this happened. 1533 execsql { SELECT * FROM sqlite_master } 1534 execsql { PRAGMA cache_size } 1535 } {59} 1536} 1537 1538# Reset the sqlite3_temp_directory variable for the next run of tests: 1539sqlite3 dbX :memory: 1540dbX eval {PRAGMA temp_store_directory = ""} 1541dbX close 1542 1543ifcapable lock_proxy_pragmas&&prefer_proxy_locking { 1544 set sqlite_hostid_num 1 1545 1546 set using_proxy 0 1547 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] { 1548 set using_proxy $value 1549 } 1550 1551 # Test the lock_proxy_file pragmas. 1552 # 1553 db close 1554 set env(SQLITE_FORCE_PROXY_LOCKING) "0" 1555 1556 sqlite3 db test.db 1557 do_test pragma-16.1 { 1558 execsql { 1559 PRAGMA lock_proxy_file="mylittleproxy"; 1560 select * from sqlite_master; 1561 } 1562 execsql { 1563 PRAGMA lock_proxy_file; 1564 } 1565 } {mylittleproxy} 1566 1567 do_test pragma-16.2 { 1568 sqlite3 db2 test.db 1569 execsql { 1570 PRAGMA lock_proxy_file="mylittleproxy"; 1571 } db2 1572 } {} 1573 1574 db2 close 1575 do_test pragma-16.2.1 { 1576 sqlite3 db2 test.db 1577 execsql { 1578 PRAGMA lock_proxy_file=":auto:"; 1579 select * from sqlite_master; 1580 } db2 1581 execsql { 1582 PRAGMA lock_proxy_file; 1583 } db2 1584 } {mylittleproxy} 1585 1586 db2 close 1587 do_test pragma-16.3 { 1588 sqlite3 db2 test.db 1589 execsql { 1590 PRAGMA lock_proxy_file="myotherproxy"; 1591 } db2 1592 catchsql { 1593 select * from sqlite_master; 1594 } db2 1595 } {1 {database is locked}} 1596 1597 do_test pragma-16.4 { 1598 db2 close 1599 db close 1600 sqlite3 db2 test.db 1601 execsql { 1602 PRAGMA lock_proxy_file="myoriginalproxy"; 1603 PRAGMA lock_proxy_file="myotherproxy"; 1604 PRAGMA lock_proxy_file; 1605 } db2 1606 } {myotherproxy} 1607 1608 db2 close 1609 set env(SQLITE_FORCE_PROXY_LOCKING) "1" 1610 do_test pragma-16.5 { 1611 sqlite3 db2 test.db 1612 execsql { 1613 PRAGMA lock_proxy_file=":auto:"; 1614 PRAGMA lock_proxy_file; 1615 } db2 1616 } {myotherproxy} 1617 1618 do_test pragma-16.6 { 1619 db2 close 1620 sqlite3 db2 test2.db 1621 set lockpath [execsql { 1622 PRAGMA lock_proxy_file=":auto:"; 1623 PRAGMA lock_proxy_file; 1624 } db2] 1625 string match "*test2.db:auto:" $lockpath 1626 } {1} 1627 1628 set sqlite_hostid_num 2 1629 do_test pragma-16.7 { 1630 list [catch { 1631 sqlite3 db test2.db 1632 execsql { 1633 PRAGMA lock_proxy_file=":auto:"; 1634 select * from sqlite_master; 1635 } 1636 } msg] $msg 1637 } {1 {database is locked}} 1638 db close 1639 1640 do_test pragma-16.8 { 1641 list [catch { 1642 sqlite3 db test2.db 1643 execsql { select * from sqlite_master } 1644 } msg] $msg 1645 } {1 {database is locked}} 1646 1647 db2 close 1648 do_test pragma-16.8.1 { 1649 execsql { 1650 PRAGMA lock_proxy_file="yetanotherproxy"; 1651 PRAGMA lock_proxy_file; 1652 } 1653 } {yetanotherproxy} 1654 do_test pragma-16.8.2 { 1655 execsql { 1656 create table mine(x); 1657 } 1658 } {} 1659 1660 db close 1661 do_test pragma-16.9 { 1662 sqlite3 db proxytest.db 1663 set lockpath2 [execsql { 1664 PRAGMA lock_proxy_file=":auto:"; 1665 PRAGMA lock_proxy_file; 1666 } db] 1667 string match "*proxytest.db:auto:" $lockpath2 1668 } {1} 1669 1670 set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy 1671 set sqlite_hostid_num 0 1672} 1673 1674# Parsing of auto_vacuum settings. 1675# 1676foreach {autovac_setting val} { 1677 0 0 1678 1 1 1679 2 2 1680 3 0 1681 -1 0 1682 none 0 1683 NONE 0 1684 NoNe 0 1685 full 1 1686 FULL 1 1687 incremental 2 1688 INCREMENTAL 2 1689 -1234 0 1690 1234 0 1691} { 1692 do_test pragma-17.1.$autovac_setting { 1693 catch {db close} 1694 sqlite3 db :memory: 1695 execsql " 1696 PRAGMA auto_vacuum=$::autovac_setting; 1697 PRAGMA auto_vacuum; 1698 " 1699 } $val 1700} 1701 1702# Parsing of temp_store settings. 1703# 1704foreach {temp_setting val} { 1705 0 0 1706 1 1 1707 2 2 1708 3 0 1709 -1 0 1710 file 1 1711 FILE 1 1712 fIlE 1 1713 memory 2 1714 MEMORY 2 1715 MeMoRy 2 1716} { 1717 do_test pragma-18.1.$temp_setting { 1718 catch {db close} 1719 sqlite3 db :memory: 1720 execsql " 1721 PRAGMA temp_store=$::temp_setting; 1722 PRAGMA temp_store=$::temp_setting; 1723 PRAGMA temp_store; 1724 " 1725 } $val 1726} 1727 1728# The SQLITE_FCNTL_PRAGMA logic, with error handling. 1729# 1730db close 1731testvfs tvfs 1732sqlite3 db test.db -vfs tvfs 1733do_test pragma-19.1 { 1734 catchsql {PRAGMA error} 1735} {1 {SQL logic error}} 1736do_test pragma-19.2 { 1737 catchsql {PRAGMA error='This is the error message'} 1738} {1 {This is the error message}} 1739do_test pragma-19.3 { 1740 catchsql {PRAGMA error='7 This is the error message'} 1741} {1 {This is the error message}} 1742do_test pragma-19.4 { 1743 catchsql {PRAGMA error=7} 1744} {1 {out of memory}} 1745do_test pragma-19.5 { 1746 file tail [lindex [execsql {PRAGMA filename}] 0] 1747} {test.db} 1748 1749if {$tcl_platform(platform)=="windows"} { 1750# Test data_store_directory pragma 1751# 1752db close 1753sqlite3 db test.db 1754file mkdir data_dir 1755do_test pragma-20.1 { 1756 catchsql {PRAGMA data_store_directory} 1757} {0 {}} 1758do_test pragma-20.2 { 1759 set pwd [string map {' ''} [file nativename [get_pwd]]] 1760 catchsql "PRAGMA data_store_directory='$pwd';" 1761} {0 {}} 1762do_test pragma-20.3 { 1763 catchsql {PRAGMA data_store_directory} 1764} [list 0 [list [file nativename [get_pwd]]]] 1765do_test pragma-20.4 { 1766 set pwd [string map {' ''} [file nativename \ 1767 [file join [get_pwd] data_dir]]] 1768 catchsql "PRAGMA data_store_directory='$pwd';" 1769} {0 {}} 1770do_test pragma-20.5 { 1771 sqlite3 db2 test2.db 1772 catchsql "PRAGMA database_list;" db2 1773} [list 0 [list 0 main [file nativename \ 1774 [file join [get_pwd] data_dir test2.db]]]] 1775catch {db2 close} 1776do_test pragma-20.6 { 1777 sqlite3 db2 [file join [get_pwd] test2.db] 1778 catchsql "PRAGMA database_list;" db2 1779} [list 0 [list 0 main [file nativename \ 1780 [file join [get_pwd] test2.db]]]] 1781catch {db2 close} 1782do_test pragma-20.7 { 1783 catchsql "PRAGMA data_store_directory='';" 1784} {0 {}} 1785do_test pragma-20.8 { 1786 catchsql {PRAGMA data_store_directory} 1787} {0 {}} 1788 1789forcedelete data_dir 1790} ;# endif windows 1791 1792database_may_be_corrupt 1793if {![nonzero_reserved_bytes]} { 1794 1795 do_test 21.1 { 1796 # Create a corrupt database in testerr.db. And a non-corrupt at test.db. 1797 # 1798 db close 1799 forcedelete test.db 1800 sqlite3 db test.db 1801 execsql { 1802 PRAGMA page_size = 1024; 1803 PRAGMA auto_vacuum = 0; 1804 CREATE TABLE t1(a PRIMARY KEY, b); 1805 INSERT INTO t1 VALUES(1, 1); 1806 } 1807 for {set i 0} {$i < 10} {incr i} { 1808 execsql { INSERT INTO t1 SELECT a + (1 << $i), b + (1 << $i) FROM t1 } 1809 } 1810 db close 1811 forcecopy test.db testerr.db 1812 hexio_write testerr.db 15000 [string repeat 55 100] 1813 } {100} 1814 1815 set mainerr {*** in database main *** 1816Multiple uses for byte 672 of page 15} 1817 set auxerr {*** in database aux *** 1818Multiple uses for byte 672 of page 15} 1819 1820 set mainerr {/{\*\*\* in database main \*\*\* 1821Multiple uses for byte 672 of page 15}.*/} 1822 set auxerr {/{\*\*\* in database aux \*\*\* 1823Multiple uses for byte 672 of page 15}.*/} 1824 1825 do_test 22.2 { 1826 catch { db close } 1827 sqlite3 db testerr.db 1828 execsql { PRAGMA integrity_check } 1829 } $mainerr 1830 1831 do_test 22.3.1 { 1832 catch { db close } 1833 sqlite3 db test.db 1834 execsql { 1835 ATTACH 'testerr.db' AS 'aux'; 1836 PRAGMA integrity_check; 1837 } 1838 } $auxerr 1839 do_test 22.3.2 { 1840 execsql { PRAGMA main.integrity_check; } 1841 } {ok} 1842 do_test 22.3.3 { 1843 execsql { PRAGMA aux.integrity_check; } 1844 } $auxerr 1845 1846 do_test 22.4.1 { 1847 catch { db close } 1848 sqlite3 db testerr.db 1849 execsql { 1850 ATTACH 'test.db' AS 'aux'; 1851 PRAGMA integrity_check; 1852 } 1853 } $mainerr 1854 do_test 22.4.2 { 1855 execsql { PRAGMA main.integrity_check; } 1856 } $mainerr 1857 do_test 22.4.3 { 1858 execsql { PRAGMA aux.integrity_check; } 1859 } {ok} 1860} 1861 1862db close 1863forcedelete test.db test.db-wal test.db-journal 1864sqlite3 db test.db 1865sqlite3 db2 test.db 1866do_test 23.1 { 1867 db eval { 1868 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); 1869 CREATE INDEX i1 ON t1(b,c); 1870 CREATE INDEX i2 ON t1(c,d); 1871 CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC); 1872 CREATE INDEX i3 ON t1(d,b+c,c); 1873 CREATE TABLE t2(x INTEGER REFERENCES t1); 1874 } 1875 db2 eval {SELECT name FROM sqlite_master} 1876} {t1 i1 i2 i2x i3 t2} 1877do_test 23.2a { 1878 db eval { 1879 DROP INDEX i2; 1880 CREATE INDEX i2 ON t1(c,d,b); 1881 } 1882 capture_pragma db2 out {PRAGMA index_info(i2)} 1883 db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno} 1884} {2 c | 3 d | 1 b |} 1885 1886# EVIDENCE-OF: R-56143-29319 PRAGMA schema.index_xinfo(index-name); This 1887# pragma returns information about every column in an index. 1888# 1889# EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma 1890# returns information about every column in the index, not just the key 1891# columns. 1892# 1893do_test 23.2b { 1894 capture_pragma db2 out {PRAGMA index_xinfo(i2)} 1895 db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno} 1896} {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |} 1897 1898# (The first column of output from PRAGMA index_xinfo is...) 1899# EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0 1900# means left-most. Key columns come before auxiliary columns.) 1901# 1902# (The second column of output from PRAGMA index_xinfo is...) 1903# EVIDENCE-OF: R-06603-49335 The rank of the column within the table 1904# being indexed, or -1 if the index-column is the rowid of the table 1905# being indexed and -2 if the index is on an expression. 1906# 1907# (The third column of output from PRAGMA index_xinfo is...) 1908# EVIDENCE-OF: R-40641-22898 The name of the column being indexed, or 1909# NULL if the index-column is the rowid of the table being indexed or an 1910# expression. 1911# 1912# (The fourth column of output from PRAGMA index_xinfo is...) 1913# EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse 1914# (DESC) order by the index and 0 otherwise. 1915# 1916# (The fifth column of output from PRAGMA index_xinfo is...) 1917# EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to 1918# compare values in the index-column. 1919# 1920# (The sixth column of output from PRAGMA index_xinfo is...) 1921# EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0 1922# if the index-column is an auxiliary column. 1923# 1924do_test 23.2c { 1925 db2 eval {PRAGMA index_xinfo(i2)} 1926} {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0} 1927do_test 23.2d { 1928 db2 eval {PRAGMA index_xinfo(i2x)} 1929} {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0} 1930do_test 23.2e { 1931 db2 eval {PRAGMA index_xinfo(i3)} 1932} {0 3 d 0 BINARY 1 1 -2 {} 0 BINARY 1 2 2 c 0 BINARY 1 3 -1 {} 0 BINARY 0} 1933 1934# EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This 1935# pragma returns one row for each index associated with the given table. 1936# 1937# (The first column of output from PRAGMA index_list is...) 1938# EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index 1939# for internal tracking purposes. 1940# 1941# (The second column of output from PRAGMA index_list is...) 1942# EVIDENCE-OF: R-35496-03635 The name of the index. 1943# 1944# (The third column of output from PRAGMA index_list is...) 1945# EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not. 1946# 1947# (The fourth column of output from PRAGMA index_list is...) 1948# EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE 1949# INDEX statement, "u" if the index was created by a UNIQUE constraint, 1950# or "pk" if the index was created by a PRIMARY KEY constraint. 1951# 1952do_test 23.3 { 1953 db eval { 1954 DROP INDEX IF EXISTS i3; 1955 CREATE INDEX i3 ON t1(d,b,c); 1956 } 1957 capture_pragma db2 out {PRAGMA index_list(t1)} 1958 db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq} 1959} {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |} 1960do_test 23.4 { 1961 db eval { 1962 ALTER TABLE t1 ADD COLUMN e; 1963 } 1964 db2 eval { 1965 PRAGMA table_info(t1); 1966 } 1967} {/4 e {} 0 {} 0/} 1968do_test 23.5 { 1969 db eval { 1970 DROP TABLE t2; 1971 CREATE TABLE t2(x, y INTEGER REFERENCES t1); 1972 } 1973 db2 eval { 1974 PRAGMA foreign_key_list(t2); 1975 } 1976} {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE} 1977db2 close 1978 1979ifcapable !has_codec { 1980 reset_db 1981 do_execsql_test 24.0 { 1982 PRAGMA page_size = 1024; 1983 CREATE TABLE t1(a, b, c); 1984 CREATE INDEX i1 ON t1(b); 1985 INSERT INTO t1 VALUES('a', 'b', 'c'); 1986 PRAGMA integrity_check; 1987 } {ok} 1988 1989 set r [db one {SELECT rootpage FROM sqlite_master WHERE name = 't1'}] 1990 db close 1991 hexio_write test.db [expr $r*1024 - 16] 000000000000000701040f0f1f616263 1992 1993 sqlite3 db test.db 1994 do_catchsql_test 24.1 { 1995 SELECT * FROM t1; 1996 } {1 {database disk image is malformed}} 1997 do_catchsql_test 24.2 { 1998 PRAGMA integrity_check; 1999 } {0 {{database disk image is malformed}}} 2000} 2001database_never_corrupt 2002finish_test 2003