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# 535ifcapable altertable { 536 sqlite3_db_config db DEFENSIVE 0 537 do_execsql_test pragma-3.20 { 538 CREATE TABLE t1(a,b); 539 CREATE INDEX t1a ON t1(a); 540 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(2,4),(NULL,5),(NULL,6); 541 PRAGMA writable_schema=ON; 542 UPDATE sqlite_master SET sql='CREATE UNIQUE INDEX t1a ON t1(a)' 543 WHERE name='t1a'; 544 UPDATE sqlite_master SET sql='CREATE TABLE t1(a NOT NULL,b)' 545 WHERE name='t1'; 546 PRAGMA writable_schema=OFF; 547 ALTER TABLE t1 RENAME TO t1x; 548 PRAGMA integrity_check; 549 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a} {NULL value in t1x.a}} 550 do_execsql_test pragma-3.21 { 551 PRAGMA integrity_check(3); 552 } {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a}} 553 do_execsql_test pragma-3.22 { 554 PRAGMA integrity_check(2); 555 } {{non-unique entry in index t1a} {NULL value in t1x.a}} 556 do_execsql_test pragma-3.23 { 557 PRAGMA integrity_check(1); 558 } {{non-unique entry in index t1a}} 559} 560 561# PRAGMA integrity check (or more specifically the sqlite3BtreeCount() 562# interface) used to leave index cursors in an inconsistent state 563# which could result in an assertion fault in sqlite3BtreeKey() 564# called from saveCursorPosition() if content is removed from the 565# index while the integrity_check is still running. This test verifies 566# that problem has been fixed. 567# 568do_test pragma-3.30 { 569 catch { db close } 570 delete_file test.db 571 sqlite3 db test.db 572 db eval { 573 CREATE TABLE t1(a,b,c); 574 WITH RECURSIVE 575 c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<100) 576 INSERT INTO t1(a,b,c) SELECT i, printf('xyz%08x',i), 2000-i FROM c; 577 CREATE INDEX t1a ON t1(a); 578 CREATE INDEX t1bc ON t1(b,c); 579 } 580 db eval {PRAGMA integrity_check} { 581 db eval {DELETE FROM t1} 582 } 583} {} 584 585# Test modifying the cache_size of an attached database. 586ifcapable pager_pragmas&&attach { 587do_test pragma-4.1 { 588 execsql { 589 ATTACH 'test2.db' AS aux; 590 pragma aux.cache_size; 591 pragma aux.default_cache_size; 592 } 593} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 594do_test pragma-4.2 { 595 execsql { 596 pragma aux.cache_size = 50; 597 pragma aux.cache_size; 598 pragma aux.default_cache_size; 599 } 600} [list 50 $DFLT_CACHE_SZ] 601do_test pragma-4.3 { 602 execsql { 603 pragma aux.default_cache_size = 456; 604 pragma aux.cache_size; 605 pragma aux.default_cache_size; 606 } 607} {456 456} 608do_test pragma-4.4 { 609 execsql { 610 pragma cache_size; 611 pragma default_cache_size; 612 } 613} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 614do_test pragma-4.5 { 615 execsql { 616 DETACH aux; 617 ATTACH 'test3.db' AS aux; 618 pragma aux.cache_size; 619 pragma aux.default_cache_size; 620 } 621} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 622do_test pragma-4.6 { 623 execsql { 624 DETACH aux; 625 ATTACH 'test2.db' AS aux; 626 pragma aux.cache_size; 627 pragma aux.default_cache_size; 628 } 629} {456 456} 630} ;# ifcapable pager_pragmas 631 632# Test that modifying the sync-level in the middle of a transaction is 633# disallowed. 634ifcapable pager_pragmas { 635do_test pragma-5.0 { 636 execsql { 637 pragma synchronous; 638 } 639} {2} 640do_test pragma-5.1 { 641 catchsql { 642 BEGIN; 643 pragma synchronous = OFF; 644 } 645} {1 {Safety level may not be changed inside a transaction}} 646do_test pragma-5.2 { 647 execsql { 648 pragma synchronous; 649 } 650} {2} 651catchsql {COMMIT;} 652} ;# ifcapable pager_pragmas 653 654# Test schema-query pragmas 655# 656ifcapable schema_pragmas { 657ifcapable tempdb&&attach { 658 do_test pragma-6.1 { 659 set res {} 660 execsql {SELECT * FROM sqlite_temp_master} 661 foreach {idx name file} [execsql {pragma database_list}] { 662 lappend res $idx $name 663 } 664 set res 665 } {0 main 1 temp 2 aux} 666} 667do_test pragma-6.2 { 668 execsql { 669 CREATE TABLE t2(a TYPE_X, b [TYPE_Y], c "TYPE_Z"); 670 pragma table_info(t2) 671 } 672} {0 a TYPE_X 0 {} 0 1 b TYPE_Y 0 {} 0 2 c TYPE_Z 0 {} 0} 673do_test pragma-6.2.1 { 674 execsql { 675 pragma table_info; 676 } 677} {} 678db nullvalue <<NULL>> 679do_test pragma-6.2.2 { 680 execsql { 681 CREATE TABLE t5( 682 a TEXT DEFAULT CURRENT_TIMESTAMP, 683 b DEFAULT (5+3), 684 c TEXT, 685 d INTEGER DEFAULT NULL, 686 e TEXT DEFAULT '', 687 UNIQUE(b,c,d), 688 PRIMARY KEY(e,b,c) 689 ); 690 PRAGMA table_info(t5); 691 } 692} {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} 693db nullvalue {} 694do_test pragma-6.2.3 { 695 execsql { 696 CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c); 697 pragma table_info(t2_3) 698 } 699} {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0} 700ifcapable {foreignkey} { 701 do_test pragma-6.3.1 { 702 execsql { 703 CREATE TABLE t3(a int references t2(b), b UNIQUE); 704 pragma foreign_key_list(t3); 705 } 706 } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE} 707 do_test pragma-6.3.2 { 708 execsql { 709 pragma foreign_key_list; 710 } 711 } {} 712 do_test pragma-6.3.3 { 713 execsql { 714 pragma foreign_key_list(t3_bogus); 715 } 716 } {} 717 do_test pragma-6.3.4 { 718 execsql { 719 pragma foreign_key_list(t5); 720 } 721 } {} 722 do_test pragma-6.4 { 723 capture_pragma db out { 724 pragma index_list(t3); 725 } 726 db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq} 727 } {0 sqlite_autoindex_t3_1 1} 728} 729ifcapable {!foreignkey} { 730 execsql {CREATE TABLE t3(a,b UNIQUE)} 731} 732do_test pragma-6.5.1 { 733 execsql { 734 CREATE INDEX t3i1 ON t3(a,b); 735 } 736 capture_pragma db out { 737 pragma index_info(t3i1); 738 } 739 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno} 740} {0 0 a 1 1 b} 741 742# EVIDENCE-OF: R-23114-21695 The auxiliary index-columns are not shown 743# by the index_info pragma, but they are listed by the index_xinfo 744# pragma. 745# 746do_test pragma-6.5.1b { 747 capture_pragma db out {PRAGMA index_xinfo(t3i1)} 748 db eval {SELECT seqno, cid, name FROM out ORDER BY seqno} 749} {0 0 a 1 1 b 2 -1 {}} 750 751 752# EVIDENCE-OF: R-29448-60346 PRAGMA schema.index_info(index-name); This 753# pragma returns one row for each key column in the named index. 754# 755# (The first column of output from PRAGMA index_info is...) 756# EVIDENCE-OF: R-34186-52914 The rank of the column within the index. (0 757# means left-most.) 758# 759# (The second column of output from PRAGMA index_info is...) 760# EVIDENCE-OF: R-65019-08383 The rank of the column within the table 761# being indexed. 762# 763# (The third column of output from PRAGMA index_info is...) 764# EVIDENCE-OF: R-09773-34266 The name of the column being indexed. 765# 766do_execsql_test pragma-6.5.1c { 767 CREATE INDEX t3i2 ON t3(b,a); 768 PRAGMA index_info='t3i2'; 769 DROP INDEX t3i2; 770} {0 1 b 1 0 a} 771 772do_test pragma-6.5.2 { 773 execsql { 774 pragma index_info(t3i1_bogus); 775 } 776} {} 777 778ifcapable tempdb { 779 # Test for ticket #3320. When a temp table of the same name exists, make 780 # sure the schema of the main table can still be queried using 781 # "pragma table_info": 782 do_test pragma-6.6.1 { 783 execsql { 784 CREATE TABLE trial(col_main); 785 CREATE TEMP TABLE trial(col_temp); 786 } 787 } {} 788 do_test pragma-6.6.2 { 789 execsql { 790 PRAGMA table_info(trial); 791 } 792 } {0 col_temp {} 0 {} 0} 793 do_test pragma-6.6.3 { 794 execsql { 795 PRAGMA temp.table_info(trial); 796 } 797 } {0 col_temp {} 0 {} 0} 798 do_test pragma-6.6.4 { 799 execsql { 800 PRAGMA main.table_info(trial); 801 } 802 } {0 col_main {} 0 {} 0} 803} 804 805do_test pragma-6.7 { 806 execsql { 807 CREATE TABLE test_table( 808 one INT NOT NULL DEFAULT -1, 809 two text, 810 three VARCHAR(45, 65) DEFAULT 'abcde', 811 four REAL DEFAULT X'abcdef', 812 five DEFAULT CURRENT_TIME 813 ); 814 } 815 capture_pragma db out {PRAGMA table_info(test_table)} 816 db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out 817 ORDER BY cid} 818} [concat \ 819 {0 one INT 1 -1 0} \ 820 {1 two TEXT 0 {} 0} \ 821 {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \ 822 {3 four REAL 0 X'abcdef' 0} \ 823 {4 five {} 0 CURRENT_TIME 0} \ 824] 825do_test pragma-6.8 { 826 execsql { 827 CREATE TABLE t68(a,b,c,PRIMARY KEY(a,b,a,c)); 828 PRAGMA table_info(t68); 829 } 830} [concat \ 831 {0 a {} 0 {} 1} \ 832 {1 b {} 0 {} 2} \ 833 {2 c {} 0 {} 4} \ 834] 835} ;# ifcapable schema_pragmas 836# Miscellaneous tests 837# 838ifcapable schema_pragmas { 839# EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This 840# pragma returns one row for each index associated with the given table. 841# 842do_test pragma-7.1.1 { 843 # Make sure a pragma knows to read the schema if it needs to 844 db close 845 sqlite3 db test.db 846 capture_pragma db out "PRAGMA index_list(t3)" 847 db eval {SELECT name, "origin" FROM out ORDER BY name DESC} 848} {t3i1 c sqlite_autoindex_t3_1 u} 849do_test pragma-7.1.2 { 850 execsql { 851 pragma index_list(t3_bogus); 852 } 853} {} 854} ;# ifcapable schema_pragmas 855ifcapable {utf16} { 856 if {[permutation] == ""} { 857 do_test pragma-7.2 { 858 db close 859 sqlite3 db test.db 860 catchsql { 861 pragma encoding=bogus; 862 } 863 } {1 {unsupported encoding: bogus}} 864 } 865} 866ifcapable tempdb { 867 do_test pragma-7.3 { 868 db close 869 sqlite3 db test.db 870 execsql { 871 pragma lock_status; 872 } 873 } {main unlocked temp closed} 874} else { 875 do_test pragma-7.3 { 876 db close 877 sqlite3 db test.db 878 execsql { 879 pragma lock_status; 880 } 881 } {main unlocked} 882} 883 884 885#---------------------------------------------------------------------- 886# Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA 887# user_version" statements. 888# 889# pragma-8.1: PRAGMA schema_version 890# pragma-8.2: PRAGMA user_version 891# 892 893ifcapable schema_version { 894 895# First check that we can set the schema version and then retrieve the 896# same value. 897do_test pragma-8.1.1 { 898 execsql { 899 PRAGMA schema_version = 105; 900 } 901} {} 902do_test pragma-8.1.2 { 903 execsql2 { 904 PRAGMA schema_version; 905 } 906} {schema_version 105} 907sqlite3_db_config db DEFENSIVE 1 908do_execsql_test pragma-8.1.3 { 909 PRAGMA schema_version = 106; 910 PRAGMA schema_version; 911} 105 912sqlite3_db_config db DEFENSIVE 0 913do_execsql_test pragma-8.1.4 { 914 PRAGMA schema_version = 106; 915 PRAGMA schema_version; 916} 106 917 918# Check that creating a table modifies the schema-version (this is really 919# to verify that the value being read is in fact the schema version). 920do_test pragma-8.1.5 { 921 execsql { 922 CREATE TABLE t4(a, b, c); 923 INSERT INTO t4 VALUES(1, 2, 3); 924 SELECT * FROM t4; 925 } 926} {1 2 3} 927do_test pragma-8.1.6 { 928 execsql { 929 PRAGMA schema_version; 930 } 931} 107 932 933# Now open a second connection to the database. Ensure that changing the 934# schema-version using the first connection forces the second connection 935# to reload the schema. This has to be done using the C-API test functions, 936# because the TCL API accounts for SCHEMA_ERROR and retries the query. 937do_test pragma-8.1.7 { 938 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] 939 execsql { 940 SELECT * FROM t4; 941 } db2 942} {1 2 3} 943do_test pragma-8.1.8 { 944 execsql { 945 PRAGMA schema_version = 108; 946 } 947} {} 948do_test pragma-8.1.9 { 949 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY] 950 sqlite3_step $::STMT 951} SQLITE_ERROR 952do_test pragma-8.1.10 { 953 sqlite3_finalize $::STMT 954} SQLITE_SCHEMA 955 956# Make sure the schema-version can be manipulated in an attached database. 957forcedelete test2.db 958forcedelete test2.db-journal 959ifcapable attach { 960 do_test pragma-8.1.11 { 961 execsql { 962 ATTACH 'test2.db' AS aux; 963 CREATE TABLE aux.t1(a, b, c); 964 PRAGMA aux.schema_version = 205; 965 } 966 } {} 967 do_test pragma-8.1.12 { 968 execsql { 969 PRAGMA aux.schema_version; 970 } 971 } 205 972} 973do_test pragma-8.1.13 { 974 execsql { 975 PRAGMA schema_version; 976 } 977} 108 978 979# And check that modifying the schema-version in an attached database 980# forces the second connection to reload the schema. 981ifcapable attach { 982 do_test pragma-8.1.14 { 983 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] 984 execsql { 985 ATTACH 'test2.db' AS aux; 986 SELECT * FROM aux.t1; 987 } db2 988 } {} 989 do_test pragma-8.1.15 { 990 execsql { 991 PRAGMA aux.schema_version = 206; 992 } 993 } {} 994 do_test pragma-8.1.16 { 995 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY] 996 sqlite3_step $::STMT 997 } SQLITE_ERROR 998 do_test pragma-8.1.17 { 999 sqlite3_finalize $::STMT 1000 } SQLITE_SCHEMA 1001 do_test pragma-8.1.18 { 1002 db2 close 1003 } {} 1004} 1005 1006# Now test that the user-version can be read and written (and that we aren't 1007# accidentally manipulating the schema-version instead). 1008do_test pragma-8.2.1 { 1009 execsql2 { 1010 PRAGMA user_version; 1011 } 1012} {user_version 0} 1013do_test pragma-8.2.2 { 1014 execsql { 1015 PRAGMA user_version = 2; 1016 } 1017} {} 1018do_test pragma-8.2.3.1 { 1019 execsql2 { 1020 PRAGMA user_version; 1021 } 1022} {user_version 2} 1023do_test pragma-8.2.3.2 { 1024 db close 1025 sqlite3 db test.db 1026 execsql { 1027 PRAGMA user_version; 1028 } 1029} {2} 1030do_test pragma-8.2.4.1 { 1031 execsql { 1032 PRAGMA schema_version; 1033 } 1034} {108} 1035ifcapable vacuum { 1036 do_test pragma-8.2.4.2 { 1037 execsql { 1038 VACUUM; 1039 PRAGMA user_version; 1040 } 1041 } {2} 1042 do_test pragma-8.2.4.3 { 1043 execsql { 1044 PRAGMA schema_version; 1045 } 1046 } {109} 1047} 1048 1049ifcapable attach { 1050 db eval {ATTACH 'test2.db' AS aux} 1051 1052 # Check that the user-version in the auxilary database can be manipulated ( 1053 # and that we aren't accidentally manipulating the same in the main db). 1054 do_test pragma-8.2.5 { 1055 execsql { 1056 PRAGMA aux.user_version; 1057 } 1058 } {0} 1059 do_test pragma-8.2.6 { 1060 execsql { 1061 PRAGMA aux.user_version = 3; 1062 } 1063 } {} 1064 do_test pragma-8.2.7 { 1065 execsql { 1066 PRAGMA aux.user_version; 1067 } 1068 } {3} 1069 do_test pragma-8.2.8 { 1070 execsql { 1071 PRAGMA main.user_version; 1072 } 1073 } {2} 1074 1075 # Now check that a ROLLBACK resets the user-version if it has been modified 1076 # within a transaction. 1077 do_test pragma-8.2.9 { 1078 execsql { 1079 BEGIN; 1080 PRAGMA aux.user_version = 10; 1081 PRAGMA user_version = 11; 1082 } 1083 } {} 1084 do_test pragma-8.2.10 { 1085 execsql { 1086 PRAGMA aux.user_version; 1087 } 1088 } {10} 1089 do_test pragma-8.2.11 { 1090 execsql { 1091 PRAGMA main.user_version; 1092 } 1093 } {11} 1094 do_test pragma-8.2.12 { 1095 execsql { 1096 ROLLBACK; 1097 PRAGMA aux.user_version; 1098 } 1099 } {3} 1100 do_test pragma-8.2.13 { 1101 execsql { 1102 PRAGMA main.user_version; 1103 } 1104 } {2} 1105} 1106 1107# Try a negative value for the user-version 1108do_test pragma-8.2.14 { 1109 execsql { 1110 PRAGMA user_version = -450; 1111 } 1112} {} 1113do_test pragma-8.2.15 { 1114 execsql { 1115 PRAGMA user_version; 1116 } 1117} {-450} 1118} ; # ifcapable schema_version 1119 1120# Check to see if TEMP_STORE is memory or disk. Return strings 1121# "memory" or "disk" as appropriate. 1122# 1123proc check_temp_store {} { 1124 db eval { 1125 PRAGMA temp.cache_size = 1; 1126 CREATE TEMP TABLE IF NOT EXISTS a(b); 1127 DELETE FROM a; 1128 INSERT INTO a VALUES(randomblob(1000)); 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 INSERT INTO a SELECT * FROM a; 1136 INSERT INTO a SELECT * FROM a; 1137 } 1138 db eval {PRAGMA database_list} { 1139 if {$name=="temp"} { 1140 set bt [btree_from_db db 1] 1141 if {[btree_ismemdb $bt]} { 1142 return "memory" 1143 } 1144 return "disk" 1145 } 1146 } 1147 return "unknown" 1148} 1149 1150# Application_ID 1151# 1152do_test pragma-8.3.1 { 1153 execsql { 1154 PRAGMA application_id; 1155 } 1156} {0} 1157do_test pragma-8.3.2 { 1158 execsql {PRAGMA Application_ID(12345); PRAGMA application_id;} 1159} {12345} 1160 1161# Test temp_store and temp_store_directory pragmas 1162# 1163ifcapable pager_pragmas { 1164do_test pragma-9.1 { 1165 db close 1166 sqlite3 db test.db 1167 execsql { 1168 PRAGMA temp_store; 1169 } 1170} {0} 1171if {$TEMP_STORE<=1} { 1172 do_test pragma-9.1.1 { 1173 check_temp_store 1174 } {disk} 1175} else { 1176 do_test pragma-9.1.1 { 1177 check_temp_store 1178 } {memory} 1179} 1180 1181do_test pragma-9.2 { 1182 db close 1183 sqlite3 db test.db 1184 execsql { 1185 PRAGMA temp_store=file; 1186 PRAGMA temp_store; 1187 } 1188} {1} 1189if {$TEMP_STORE==3} { 1190 # When TEMP_STORE is 3, always use memory regardless of pragma settings. 1191 do_test pragma-9.2.1 { 1192 check_temp_store 1193 } {memory} 1194} else { 1195 do_test pragma-9.2.1 { 1196 check_temp_store 1197 } {disk} 1198} 1199 1200do_test pragma-9.3 { 1201 db close 1202 sqlite3 db test.db 1203 execsql { 1204 PRAGMA temp_store=memory; 1205 PRAGMA temp_store; 1206 } 1207} {2} 1208if {$TEMP_STORE==0} { 1209 # When TEMP_STORE is 0, always use the disk regardless of pragma settings. 1210 do_test pragma-9.3.1 { 1211 check_temp_store 1212 } {disk} 1213} else { 1214 do_test pragma-9.3.1 { 1215 check_temp_store 1216 } {memory} 1217} 1218 1219do_test pragma-9.4 { 1220 execsql { 1221 PRAGMA temp_store_directory; 1222 } 1223} {} 1224ifcapable wsd { 1225 do_test pragma-9.5 { 1226 set pwd [string map {' ''} [file nativename [get_pwd]]] 1227 execsql " 1228 PRAGMA temp_store_directory='$pwd'; 1229 " 1230 } {} 1231 do_test pragma-9.6 { 1232 execsql { 1233 PRAGMA temp_store_directory; 1234 } 1235 } [list [file nativename [get_pwd]]] 1236 do_test pragma-9.7 { 1237 catchsql { 1238 PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR'; 1239 } 1240 } {1 {not a writable directory}} 1241 do_test pragma-9.8 { 1242 execsql { 1243 PRAGMA temp_store_directory=''; 1244 } 1245 } {} 1246 if {![info exists TEMP_STORE] || $TEMP_STORE<=1} { 1247 ifcapable tempdb { 1248 do_test pragma-9.9 { 1249 execsql { 1250 PRAGMA temp_store_directory; 1251 PRAGMA temp_store=FILE; 1252 CREATE TEMP TABLE temp_store_directory_test(a integer); 1253 INSERT INTO temp_store_directory_test values (2); 1254 SELECT * FROM temp_store_directory_test; 1255 } 1256 } {2} 1257 do_test pragma-9.10 { 1258 catchsql " 1259 PRAGMA temp_store_directory='$pwd'; 1260 SELECT * FROM temp_store_directory_test; 1261 " 1262 } {1 {no such table: temp_store_directory_test}} 1263 } 1264 } 1265} 1266do_test pragma-9.11 { 1267 execsql { 1268 PRAGMA temp_store = 0; 1269 PRAGMA temp_store; 1270 } 1271} {0} 1272do_test pragma-9.12 { 1273 execsql { 1274 PRAGMA temp_store = 1; 1275 PRAGMA temp_store; 1276 } 1277} {1} 1278do_test pragma-9.13 { 1279 execsql { 1280 PRAGMA temp_store = 2; 1281 PRAGMA temp_store; 1282 } 1283} {2} 1284do_test pragma-9.14 { 1285 execsql { 1286 PRAGMA temp_store = 3; 1287 PRAGMA temp_store; 1288 } 1289} {0} 1290do_test pragma-9.15 { 1291 catchsql { 1292 BEGIN EXCLUSIVE; 1293 CREATE TEMP TABLE temp_table(t); 1294 INSERT INTO temp_table VALUES('valuable data'); 1295 PRAGMA temp_store = 1; 1296 } 1297} {1 {temporary storage cannot be changed from within a transaction}} 1298do_test pragma-9.16 { 1299 execsql { 1300 SELECT * FROM temp_table; 1301 COMMIT; 1302 } 1303} {{valuable data}} 1304 1305do_test pragma-9.17 { 1306 execsql { 1307 INSERT INTO temp_table VALUES('valuable data II'); 1308 SELECT * FROM temp_table; 1309 } 1310} {{valuable data} {valuable data II}} 1311 1312do_test pragma-9.18 { 1313 set rc [catch { 1314 db eval {SELECT t FROM temp_table} { 1315 execsql {pragma temp_store = 1} 1316 } 1317 } msg] 1318 list $rc $msg 1319} {1 {temporary storage cannot be changed from within a transaction}} 1320 1321} ;# ifcapable pager_pragmas 1322 1323ifcapable trigger { 1324 1325do_test pragma-10.0 { 1326 catchsql { 1327 DROP TABLE main.t1; 1328 } 1329 execsql { 1330 PRAGMA count_changes = 1; 1331 1332 CREATE TABLE t1(a PRIMARY KEY); 1333 CREATE TABLE t1_mirror(a); 1334 CREATE TABLE t1_mirror2(a); 1335 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN 1336 INSERT INTO t1_mirror VALUES(new.a); 1337 END; 1338 CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN 1339 INSERT INTO t1_mirror2 VALUES(new.a); 1340 END; 1341 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN 1342 UPDATE t1_mirror SET a = new.a WHERE a = old.a; 1343 END; 1344 CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN 1345 UPDATE t1_mirror2 SET a = new.a WHERE a = old.a; 1346 END; 1347 CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN 1348 DELETE FROM t1_mirror WHERE a = old.a; 1349 END; 1350 CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN 1351 DELETE FROM t1_mirror2 WHERE a = old.a; 1352 END; 1353 } 1354} {} 1355 1356do_test pragma-10.1 { 1357 execsql { 1358 INSERT INTO t1 VALUES(randstr(10,10)); 1359 } 1360} {1} 1361do_test pragma-10.2 { 1362 execsql { 1363 UPDATE t1 SET a = randstr(10,10); 1364 } 1365} {1} 1366do_test pragma-10.3 { 1367 execsql { 1368 DELETE FROM t1; 1369 } 1370} {1} 1371 1372} ;# ifcapable trigger 1373 1374ifcapable schema_pragmas { 1375 do_test pragma-11.1 { 1376 execsql2 { 1377 pragma collation_list; 1378 } 1379 } {seq 0 name RTRIM seq 1 name NOCASE seq 2 name BINARY} 1380 do_test pragma-11.2 { 1381 db collate New_Collation blah... 1382 execsql { 1383 pragma collation_list; 1384 } 1385 } {0 New_Collation 1 RTRIM 2 NOCASE 3 BINARY} 1386} 1387 1388ifcapable schema_pragmas&&tempdb { 1389 do_test pragma-12.1 { 1390 sqlite3 db2 test.db 1391 execsql { 1392 PRAGMA temp.table_info('abc'); 1393 } db2 1394 } {} 1395 db2 close 1396 1397 do_test pragma-12.2 { 1398 sqlite3 db2 test.db 1399 execsql { 1400 PRAGMA temp.default_cache_size = 200; 1401 PRAGMA temp.default_cache_size; 1402 } db2 1403 } {200} 1404 db2 close 1405 1406 do_test pragma-12.3 { 1407 sqlite3 db2 test.db 1408 execsql { 1409 PRAGMA temp.cache_size = 400; 1410 PRAGMA temp.cache_size; 1411 } db2 1412 } {400} 1413 db2 close 1414} 1415 1416ifcapable bloblit { 1417 1418do_test pragma-13.1 { 1419 execsql { 1420 DROP TABLE IF EXISTS t4; 1421 PRAGMA vdbe_trace=on; 1422 PRAGMA vdbe_listing=on; 1423 PRAGMA sql_trace=on; 1424 CREATE TABLE t4(a INTEGER PRIMARY KEY,b); 1425 INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789'); 1426 INSERT INTO t4(b) VALUES(randstr(30,30)); 1427 INSERT INTO t4(b) VALUES(1.23456); 1428 INSERT INTO t4(b) VALUES(NULL); 1429 INSERT INTO t4(b) VALUES(0); 1430 INSERT INTO t4(b) SELECT b||b||b||b FROM t4; 1431 SELECT * FROM t4; 1432 } 1433 execsql { 1434 PRAGMA vdbe_trace=off; 1435 PRAGMA vdbe_listing=off; 1436 PRAGMA sql_trace=off; 1437 } 1438} {} 1439 1440} ;# ifcapable bloblit 1441 1442ifcapable pager_pragmas { 1443 db close 1444 forcedelete test.db 1445 sqlite3 db test.db 1446 1447 # EVIDENCE-OF: R-15672-33611 PRAGMA schema.page_count; Return the total 1448 # number of pages in the database file. 1449 # 1450 do_test pragma-14.1 { 1451 execsql { pragma auto_vacuum = 0 } 1452 execsql { pragma page_count; pragma main.page_count } 1453 } {0 0} 1454 1455 do_test pragma-14.2 { 1456 execsql { 1457 CREATE TABLE abc(a, b, c); 1458 PRAGMA page_count; 1459 PRAGMA main.page_count; 1460 PRAGMA temp.page_count; 1461 } 1462 } {2 2 0} 1463 do_test pragma-14.2uc { 1464 execsql {pragma PAGE_COUNT} 1465 } {2} 1466 1467 do_test pragma-14.3 { 1468 execsql { 1469 BEGIN; 1470 CREATE TABLE def(a, b, c); 1471 PRAGMA page_count; 1472 } 1473 } {3} 1474 do_test pragma-14.3uc { 1475 execsql {pragma PAGE_COUNT} 1476 } {3} 1477 1478 do_test pragma-14.4 { 1479 set page_size [db one {pragma page_size}] 1480 expr [file size test.db] / $page_size 1481 } {2} 1482 1483 do_test pragma-14.5 { 1484 execsql { 1485 ROLLBACK; 1486 PRAGMA page_count; 1487 } 1488 } {2} 1489 1490 do_test pragma-14.6 { 1491 forcedelete test2.db 1492 sqlite3 db2 test2.db 1493 execsql { 1494 PRAGMA auto_vacuum = 0; 1495 CREATE TABLE t1(a, b, c); 1496 CREATE TABLE t2(a, b, c); 1497 CREATE TABLE t3(a, b, c); 1498 CREATE TABLE t4(a, b, c); 1499 } db2 1500 db2 close 1501 execsql { 1502 ATTACH 'test2.db' AS aux; 1503 PRAGMA aux.page_count; 1504 } 1505 } {5} 1506 do_test pragma-14.6uc { 1507 execsql {pragma AUX.PAGE_COUNT} 1508 } {5} 1509} 1510 1511# Test that the value set using the cache_size pragma is not reset when the 1512# schema is reloaded. 1513# 1514ifcapable pager_pragmas { 1515 db close 1516 sqlite3 db test.db 1517 do_test pragma-15.1 { 1518 execsql { 1519 PRAGMA cache_size=59; 1520 PRAGMA cache_size; 1521 } 1522 } {59} 1523 do_test pragma-15.2 { 1524 sqlite3 db2 test.db 1525 execsql { 1526 CREATE TABLE newtable(a, b, c); 1527 } db2 1528 db2 close 1529 } {} 1530 do_test pragma-15.3 { 1531 # Evaluating this statement will cause the schema to be reloaded (because 1532 # the schema was changed by another connection in pragma-15.2). At one 1533 # point there was a bug that reset the cache_size to its default value 1534 # when this happened. 1535 execsql { SELECT * FROM sqlite_master } 1536 execsql { PRAGMA cache_size } 1537 } {59} 1538} 1539 1540# Reset the sqlite3_temp_directory variable for the next run of tests: 1541sqlite3 dbX :memory: 1542dbX eval {PRAGMA temp_store_directory = ""} 1543dbX close 1544 1545ifcapable lock_proxy_pragmas&&prefer_proxy_locking { 1546 set sqlite_hostid_num 1 1547 1548 set using_proxy 0 1549 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] { 1550 set using_proxy $value 1551 } 1552 1553 # Test the lock_proxy_file pragmas. 1554 # 1555 db close 1556 set env(SQLITE_FORCE_PROXY_LOCKING) "0" 1557 1558 sqlite3 db test.db 1559 do_test pragma-16.1 { 1560 execsql { 1561 PRAGMA lock_proxy_file="mylittleproxy"; 1562 select * from sqlite_master; 1563 } 1564 execsql { 1565 PRAGMA lock_proxy_file; 1566 } 1567 } {mylittleproxy} 1568 1569 do_test pragma-16.2 { 1570 sqlite3 db2 test.db 1571 execsql { 1572 PRAGMA lock_proxy_file="mylittleproxy"; 1573 } db2 1574 } {} 1575 1576 db2 close 1577 do_test pragma-16.2.1 { 1578 sqlite3 db2 test.db 1579 execsql { 1580 PRAGMA lock_proxy_file=":auto:"; 1581 select * from sqlite_master; 1582 } db2 1583 execsql { 1584 PRAGMA lock_proxy_file; 1585 } db2 1586 } {mylittleproxy} 1587 1588 db2 close 1589 do_test pragma-16.3 { 1590 sqlite3 db2 test.db 1591 execsql { 1592 PRAGMA lock_proxy_file="myotherproxy"; 1593 } db2 1594 catchsql { 1595 select * from sqlite_master; 1596 } db2 1597 } {1 {database is locked}} 1598 1599 do_test pragma-16.4 { 1600 db2 close 1601 db close 1602 sqlite3 db2 test.db 1603 execsql { 1604 PRAGMA lock_proxy_file="myoriginalproxy"; 1605 PRAGMA lock_proxy_file="myotherproxy"; 1606 PRAGMA lock_proxy_file; 1607 } db2 1608 } {myotherproxy} 1609 1610 db2 close 1611 set env(SQLITE_FORCE_PROXY_LOCKING) "1" 1612 do_test pragma-16.5 { 1613 sqlite3 db2 test.db 1614 execsql { 1615 PRAGMA lock_proxy_file=":auto:"; 1616 PRAGMA lock_proxy_file; 1617 } db2 1618 } {myotherproxy} 1619 1620 do_test pragma-16.6 { 1621 db2 close 1622 sqlite3 db2 test2.db 1623 set lockpath [execsql { 1624 PRAGMA lock_proxy_file=":auto:"; 1625 PRAGMA lock_proxy_file; 1626 } db2] 1627 string match "*test2.db:auto:" $lockpath 1628 } {1} 1629 1630 set sqlite_hostid_num 2 1631 do_test pragma-16.7 { 1632 list [catch { 1633 sqlite3 db test2.db 1634 execsql { 1635 PRAGMA lock_proxy_file=":auto:"; 1636 select * from sqlite_master; 1637 } 1638 } msg] $msg 1639 } {1 {database is locked}} 1640 db close 1641 1642 do_test pragma-16.8 { 1643 list [catch { 1644 sqlite3 db test2.db 1645 execsql { select * from sqlite_master } 1646 } msg] $msg 1647 } {1 {database is locked}} 1648 1649 db2 close 1650 do_test pragma-16.8.1 { 1651 execsql { 1652 PRAGMA lock_proxy_file="yetanotherproxy"; 1653 PRAGMA lock_proxy_file; 1654 } 1655 } {yetanotherproxy} 1656 do_test pragma-16.8.2 { 1657 execsql { 1658 create table mine(x); 1659 } 1660 } {} 1661 1662 db close 1663 do_test pragma-16.9 { 1664 sqlite3 db proxytest.db 1665 set lockpath2 [execsql { 1666 PRAGMA lock_proxy_file=":auto:"; 1667 PRAGMA lock_proxy_file; 1668 } db] 1669 string match "*proxytest.db:auto:" $lockpath2 1670 } {1} 1671 1672 set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy 1673 set sqlite_hostid_num 0 1674} 1675 1676# Parsing of auto_vacuum settings. 1677# 1678foreach {autovac_setting val} { 1679 0 0 1680 1 1 1681 2 2 1682 3 0 1683 -1 0 1684 none 0 1685 NONE 0 1686 NoNe 0 1687 full 1 1688 FULL 1 1689 incremental 2 1690 INCREMENTAL 2 1691 -1234 0 1692 1234 0 1693} { 1694 do_test pragma-17.1.$autovac_setting { 1695 catch {db close} 1696 sqlite3 db :memory: 1697 execsql " 1698 PRAGMA auto_vacuum=$::autovac_setting; 1699 PRAGMA auto_vacuum; 1700 " 1701 } $val 1702} 1703 1704# Parsing of temp_store settings. 1705# 1706foreach {temp_setting val} { 1707 0 0 1708 1 1 1709 2 2 1710 3 0 1711 -1 0 1712 file 1 1713 FILE 1 1714 fIlE 1 1715 memory 2 1716 MEMORY 2 1717 MeMoRy 2 1718} { 1719 do_test pragma-18.1.$temp_setting { 1720 catch {db close} 1721 sqlite3 db :memory: 1722 execsql " 1723 PRAGMA temp_store=$::temp_setting; 1724 PRAGMA temp_store=$::temp_setting; 1725 PRAGMA temp_store; 1726 " 1727 } $val 1728} 1729 1730# The SQLITE_FCNTL_PRAGMA logic, with error handling. 1731# 1732db close 1733testvfs tvfs 1734sqlite3 db test.db -vfs tvfs 1735do_test pragma-19.1 { 1736 catchsql {PRAGMA error} 1737} {1 {SQL logic error}} 1738do_test pragma-19.2 { 1739 catchsql {PRAGMA error='This is the error message'} 1740} {1 {This is the error message}} 1741do_test pragma-19.3 { 1742 catchsql {PRAGMA error='7 This is the error message'} 1743} {1 {This is the error message}} 1744do_test pragma-19.4 { 1745 catchsql {PRAGMA error=7} 1746} {1 {out of memory}} 1747do_test pragma-19.5 { 1748 file tail [lindex [execsql {PRAGMA filename}] 0] 1749} {test.db} 1750 1751if {$tcl_platform(platform)=="windows"} { 1752# Test data_store_directory pragma 1753# 1754db close 1755sqlite3 db test.db 1756file mkdir data_dir 1757do_test pragma-20.1 { 1758 catchsql {PRAGMA data_store_directory} 1759} {0 {}} 1760do_test pragma-20.2 { 1761 set pwd [string map {' ''} [file nativename [get_pwd]]] 1762 catchsql "PRAGMA data_store_directory='$pwd';" 1763} {0 {}} 1764do_test pragma-20.3 { 1765 catchsql {PRAGMA data_store_directory} 1766} [list 0 [list [file nativename [get_pwd]]]] 1767do_test pragma-20.4 { 1768 set pwd [string map {' ''} [file nativename \ 1769 [file join [get_pwd] data_dir]]] 1770 catchsql "PRAGMA data_store_directory='$pwd';" 1771} {0 {}} 1772do_test pragma-20.5 { 1773 sqlite3 db2 test2.db 1774 catchsql "PRAGMA database_list;" db2 1775} [list 0 [list 0 main [file nativename \ 1776 [file join [get_pwd] data_dir test2.db]]]] 1777catch {db2 close} 1778do_test pragma-20.6 { 1779 sqlite3 db2 [file join [get_pwd] test2.db] 1780 catchsql "PRAGMA database_list;" db2 1781} [list 0 [list 0 main [file nativename \ 1782 [file join [get_pwd] test2.db]]]] 1783catch {db2 close} 1784do_test pragma-20.7 { 1785 catchsql "PRAGMA data_store_directory='';" 1786} {0 {}} 1787do_test pragma-20.8 { 1788 catchsql {PRAGMA data_store_directory} 1789} {0 {}} 1790 1791forcedelete data_dir 1792} ;# endif windows 1793 1794database_may_be_corrupt 1795if {![nonzero_reserved_bytes]} { 1796 1797 do_test 21.1 { 1798 # Create a corrupt database in testerr.db. And a non-corrupt at test.db. 1799 # 1800 db close 1801 forcedelete test.db 1802 sqlite3 db test.db 1803 execsql { 1804 PRAGMA page_size = 1024; 1805 PRAGMA auto_vacuum = 0; 1806 CREATE TABLE t1(a PRIMARY KEY, b); 1807 INSERT INTO t1 VALUES(1, 1); 1808 } 1809 for {set i 0} {$i < 10} {incr i} { 1810 execsql { INSERT INTO t1 SELECT a + (1 << $i), b + (1 << $i) FROM t1 } 1811 } 1812 db close 1813 forcecopy test.db testerr.db 1814 hexio_write testerr.db 15000 [string repeat 55 100] 1815 } {100} 1816 1817 set mainerr {*** in database main *** 1818Multiple uses for byte 672 of page 15} 1819 set auxerr {*** in database aux *** 1820Multiple uses for byte 672 of page 15} 1821 1822 set mainerr {/{\*\*\* in database main \*\*\* 1823Multiple uses for byte 672 of page 15}.*/} 1824 set auxerr {/{\*\*\* in database aux \*\*\* 1825Multiple uses for byte 672 of page 15}.*/} 1826 1827 do_test 22.2 { 1828 catch { db close } 1829 sqlite3 db testerr.db 1830 execsql { PRAGMA integrity_check } 1831 } $mainerr 1832 1833 do_test 22.3.1 { 1834 catch { db close } 1835 sqlite3 db test.db 1836 execsql { 1837 ATTACH 'testerr.db' AS 'aux'; 1838 PRAGMA integrity_check; 1839 } 1840 } $auxerr 1841 do_test 22.3.2 { 1842 execsql { PRAGMA main.integrity_check; } 1843 } {ok} 1844 do_test 22.3.3 { 1845 execsql { PRAGMA aux.integrity_check; } 1846 } $auxerr 1847 1848 do_test 22.4.1 { 1849 catch { db close } 1850 sqlite3 db testerr.db 1851 execsql { 1852 ATTACH 'test.db' AS 'aux'; 1853 PRAGMA integrity_check; 1854 } 1855 } $mainerr 1856 do_test 22.4.2 { 1857 execsql { PRAGMA main.integrity_check; } 1858 } $mainerr 1859 do_test 22.4.3 { 1860 execsql { PRAGMA aux.integrity_check; } 1861 } {ok} 1862} 1863 1864db close 1865forcedelete test.db test.db-wal test.db-journal 1866sqlite3 db test.db 1867sqlite3 db2 test.db 1868do_test 23.1 { 1869 db eval { 1870 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); 1871 CREATE INDEX i1 ON t1(b,c); 1872 CREATE INDEX i2 ON t1(c,d); 1873 CREATE INDEX i2x ON t1(d COLLATE nocase, c DESC); 1874 CREATE INDEX i3 ON t1(d,b+c,c); 1875 CREATE TABLE t2(x INTEGER REFERENCES t1); 1876 } 1877 db2 eval {SELECT name FROM sqlite_master} 1878} {t1 i1 i2 i2x i3 t2} 1879do_test 23.2a { 1880 db eval { 1881 DROP INDEX i2; 1882 CREATE INDEX i2 ON t1(c,d,b); 1883 } 1884 capture_pragma db2 out {PRAGMA index_info(i2)} 1885 db2 eval {SELECT cid, name, '|' FROM out ORDER BY seqno} 1886} {2 c | 3 d | 1 b |} 1887 1888# EVIDENCE-OF: R-56143-29319 PRAGMA schema.index_xinfo(index-name); This 1889# pragma returns information about every column in an index. 1890# 1891# EVIDENCE-OF: R-45970-35618 Unlike this index_info pragma, this pragma 1892# returns information about every column in the index, not just the key 1893# columns. 1894# 1895do_test 23.2b { 1896 capture_pragma db2 out {PRAGMA index_xinfo(i2)} 1897 db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno} 1898} {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |} 1899 1900# (The first column of output from PRAGMA index_xinfo is...) 1901# EVIDENCE-OF: R-00197-14279 The rank of the column within the index. (0 1902# means left-most. Key columns come before auxiliary columns.) 1903# 1904# (The second column of output from PRAGMA index_xinfo is...) 1905# EVIDENCE-OF: R-06603-49335 The rank of the column within the table 1906# being indexed, or -1 if the index-column is the rowid of the table 1907# being indexed and -2 if the index is on an expression. 1908# 1909# (The third column of output from PRAGMA index_xinfo is...) 1910# EVIDENCE-OF: R-40641-22898 The name of the column being indexed, or 1911# NULL if the index-column is the rowid of the table being indexed or an 1912# expression. 1913# 1914# (The fourth column of output from PRAGMA index_xinfo is...) 1915# EVIDENCE-OF: R-11847-09179 1 if the index-column is sorted in reverse 1916# (DESC) order by the index and 0 otherwise. 1917# 1918# (The fifth column of output from PRAGMA index_xinfo is...) 1919# EVIDENCE-OF: R-15313-19540 The name for the collating sequence used to 1920# compare values in the index-column. 1921# 1922# (The sixth column of output from PRAGMA index_xinfo is...) 1923# EVIDENCE-OF: R-14310-64553 1 if the index-column is a key column and 0 1924# if the index-column is an auxiliary column. 1925# 1926do_test 23.2c { 1927 db2 eval {PRAGMA index_xinfo(i2)} 1928} {0 2 c 0 BINARY 1 1 3 d 0 BINARY 1 2 1 b 0 BINARY 1 3 -1 {} 0 BINARY 0} 1929do_test 23.2d { 1930 db2 eval {PRAGMA index_xinfo(i2x)} 1931} {0 3 d 0 nocase 1 1 2 c 1 BINARY 1 2 -1 {} 0 BINARY 0} 1932do_test 23.2e { 1933 db2 eval {PRAGMA index_xinfo(i3)} 1934} {0 3 d 0 BINARY 1 1 -2 {} 0 BINARY 1 2 2 c 0 BINARY 1 3 -1 {} 0 BINARY 0} 1935 1936# EVIDENCE-OF: R-64103-17776 PRAGMA schema.index_list(table-name); This 1937# pragma returns one row for each index associated with the given table. 1938# 1939# (The first column of output from PRAGMA index_list is...) 1940# EVIDENCE-OF: R-02753-24748 A sequence number assigned to each index 1941# for internal tracking purposes. 1942# 1943# (The second column of output from PRAGMA index_list is...) 1944# EVIDENCE-OF: R-35496-03635 The name of the index. 1945# 1946# (The third column of output from PRAGMA index_list is...) 1947# EVIDENCE-OF: R-57301-64506 "1" if the index is UNIQUE and "0" if not. 1948# 1949# (The fourth column of output from PRAGMA index_list is...) 1950# EVIDENCE-OF: R-36609-39554 "c" if the index was created by a CREATE 1951# INDEX statement, "u" if the index was created by a UNIQUE constraint, 1952# or "pk" if the index was created by a PRIMARY KEY constraint. 1953# 1954do_test 23.3 { 1955 db eval { 1956 DROP INDEX IF EXISTS i3; 1957 CREATE INDEX i3 ON t1(d,b,c); 1958 } 1959 capture_pragma db2 out {PRAGMA index_list(t1)} 1960 db2 eval {SELECT seq, name, "unique", origin, '|' FROM out ORDER BY seq} 1961} {0 i3 0 c | 1 i2 0 c | 2 i2x 0 c | 3 i1 0 c |} 1962ifcapable altertable { 1963 do_test 23.4 { 1964 db eval { 1965 ALTER TABLE t1 ADD COLUMN e; 1966 } 1967 db2 eval { 1968 PRAGMA table_info(t1); 1969 } 1970 } {/4 e {} 0 {} 0/} 1971} 1972do_test 23.5 { 1973 db eval { 1974 DROP TABLE t2; 1975 CREATE TABLE t2(x, y INTEGER REFERENCES t1); 1976 } 1977 db2 eval { 1978 PRAGMA foreign_key_list(t2); 1979 } 1980} {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE} 1981db2 close 1982 1983ifcapable !has_codec { 1984 reset_db 1985 do_execsql_test 24.0 { 1986 PRAGMA page_size = 1024; 1987 CREATE TABLE t1(a, b, c); 1988 CREATE INDEX i1 ON t1(b); 1989 INSERT INTO t1 VALUES('a', 'b', 'c'); 1990 PRAGMA integrity_check; 1991 } {ok} 1992 1993 set r [db one {SELECT rootpage FROM sqlite_master WHERE name = 't1'}] 1994 db close 1995 hexio_write test.db [expr $r*1024 - 16] 000000000000000701040f0f1f616263 1996 1997 sqlite3 db test.db 1998 do_catchsql_test 24.1 { 1999 SELECT * FROM t1; 2000 } {1 {database disk image is malformed}} 2001 do_catchsql_test 24.2 { 2002 PRAGMA integrity_check; 2003 } {0 {{database disk image is malformed}}} 2004} 2005database_never_corrupt 2006finish_test 2007