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# Delete the preexisting database to avoid the special setup 55# that the "all.test" script does. 56# 57db close 58delete_file test.db test.db-journal 59delete_file test3.db test3.db-journal 60sqlite3 db test.db; set DB [sqlite3_connection_pointer db] 61 62 63ifcapable pager_pragmas { 64set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}] 65set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}] 66do_test pragma-1.1 { 67 execsql { 68 PRAGMA cache_size; 69 PRAGMA default_cache_size; 70 PRAGMA synchronous; 71 } 72} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 73do_test pragma-1.2 { 74 execsql { 75 PRAGMA synchronous=OFF; 76 PRAGMA cache_size=1234; 77 PRAGMA cache_size; 78 PRAGMA default_cache_size; 79 PRAGMA synchronous; 80 } 81} [list 1234 $DFLT_CACHE_SZ 0] 82do_test pragma-1.3 { 83 db close 84 sqlite3 db test.db 85 execsql { 86 PRAGMA cache_size; 87 PRAGMA default_cache_size; 88 PRAGMA synchronous; 89 } 90} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 91do_test pragma-1.4 { 92 execsql { 93 PRAGMA synchronous=OFF; 94 PRAGMA cache_size; 95 PRAGMA default_cache_size; 96 PRAGMA synchronous; 97 } 98} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 0] 99do_test pragma-1.5 { 100 execsql { 101 PRAGMA cache_size=-4321; 102 PRAGMA cache_size; 103 PRAGMA default_cache_size; 104 PRAGMA synchronous; 105 } 106} [list -4321 $DFLT_CACHE_SZ 0] 107do_test pragma-1.6 { 108 execsql { 109 PRAGMA synchronous=ON; 110 PRAGMA cache_size; 111 PRAGMA default_cache_size; 112 PRAGMA synchronous; 113 } 114} [list -4321 $DFLT_CACHE_SZ 1] 115do_test pragma-1.7 { 116 db close 117 sqlite3 db test.db 118 execsql { 119 PRAGMA cache_size; 120 PRAGMA default_cache_size; 121 PRAGMA synchronous; 122 } 123} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ 2] 124do_test pragma-1.8 { 125 execsql { 126 PRAGMA default_cache_size=-123; 127 PRAGMA cache_size; 128 PRAGMA default_cache_size; 129 PRAGMA synchronous; 130 } 131} {123 123 2} 132do_test pragma-1.9.1 { 133 db close 134 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] 135 execsql { 136 PRAGMA cache_size; 137 PRAGMA default_cache_size; 138 PRAGMA synchronous; 139 } 140} {123 123 2} 141ifcapable vacuum { 142 do_test pragma-1.9.2 { 143 execsql { 144 VACUUM; 145 PRAGMA cache_size; 146 PRAGMA default_cache_size; 147 PRAGMA synchronous; 148 } 149 } {123 123 2} 150} 151do_test pragma-1.10 { 152 execsql { 153 PRAGMA synchronous=NORMAL; 154 PRAGMA cache_size; 155 PRAGMA default_cache_size; 156 PRAGMA synchronous; 157 } 158} {123 123 1} 159do_test pragma-1.11 { 160 execsql { 161 PRAGMA synchronous=FULL; 162 PRAGMA cache_size; 163 PRAGMA default_cache_size; 164 PRAGMA synchronous; 165 } 166} {123 123 2} 167do_test pragma-1.12 { 168 db close 169 sqlite3 db test.db; set ::DB [sqlite3_connection_pointer db] 170 execsql { 171 PRAGMA cache_size; 172 PRAGMA default_cache_size; 173 PRAGMA synchronous; 174 } 175} {123 123 2} 176 177# Make sure the pragma handler understands numeric values in addition 178# to keywords like "off" and "full". 179# 180do_test pragma-1.13 { 181 execsql { 182 PRAGMA synchronous=0; 183 PRAGMA synchronous; 184 } 185} {0} 186do_test pragma-1.14 { 187 execsql { 188 PRAGMA synchronous=2; 189 PRAGMA synchronous; 190 } 191} {2} 192} ;# ifcapable pager_pragmas 193 194# Test turning "flag" pragmas on and off. 195# 196ifcapable debug { 197 # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG 198 # 199 do_test pragma-1.15 { 200 execsql { 201 PRAGMA vdbe_listing=YES; 202 PRAGMA vdbe_listing; 203 } 204 } {1} 205 do_test pragma-1.16 { 206 execsql { 207 PRAGMA vdbe_listing=NO; 208 PRAGMA vdbe_listing; 209 } 210 } {0} 211} 212 213do_test pragma-1.17 { 214 execsql { 215 PRAGMA parser_trace=ON; 216 PRAGMA parser_trace=OFF; 217 } 218} {} 219do_test pragma-1.18 { 220 execsql { 221 PRAGMA bogus = -1234; -- Parsing of negative values 222 } 223} {} 224 225# Test modifying the safety_level of an attached database. 226ifcapable pager_pragmas&&attach { 227 do_test pragma-2.1 { 228 forcedelete test2.db 229 forcedelete test2.db-journal 230 execsql { 231 ATTACH 'test2.db' AS aux; 232 } 233 } {} 234 do_test pragma-2.2 { 235 execsql { 236 pragma aux.synchronous; 237 } 238 } {2} 239 do_test pragma-2.3 { 240 execsql { 241 pragma aux.synchronous = OFF; 242 pragma aux.synchronous; 243 pragma synchronous; 244 } 245 } {0 2} 246 do_test pragma-2.4 { 247 execsql { 248 pragma aux.synchronous = ON; 249 pragma synchronous; 250 pragma aux.synchronous; 251 } 252 } {2 1} 253} ;# ifcapable pager_pragmas 254 255# Construct a corrupted index and make sure the integrity_check 256# pragma finds it. 257# 258# These tests won't work if the database is encrypted 259# 260do_test pragma-3.1 { 261 db close 262 forcedelete test.db test.db-journal 263 sqlite3 db test.db 264 execsql { 265 PRAGMA auto_vacuum=OFF; 266 BEGIN; 267 CREATE TABLE t2(a,b,c); 268 CREATE INDEX i2 ON t2(a); 269 INSERT INTO t2 VALUES(11,2,3); 270 INSERT INTO t2 VALUES(22,3,4); 271 COMMIT; 272 SELECT rowid, * from t2; 273 } 274} {1 11 2 3 2 22 3 4} 275ifcapable attach { 276 if {![sqlite3 -has-codec] && $sqlite_options(integrityck)} { 277 do_test pragma-3.2 { 278 db eval {SELECT rootpage FROM sqlite_master WHERE name='i2'} break 279 set pgsz [db eval {PRAGMA page_size}] 280 # overwrite the header on the rootpage of the index in order to 281 # make the index appear to be empty. 282 # 283 set offset [expr {$pgsz*($rootpage-1)}] 284 hexio_write test.db $offset 0a00000000040000000000 285 db close 286 sqlite3 db test.db 287 execsql {PRAGMA integrity_check} 288 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 289 do_test pragma-3.3 { 290 execsql {PRAGMA integrity_check=1} 291 } {{row 1 missing from index i2}} 292 do_test pragma-3.4 { 293 execsql { 294 ATTACH DATABASE 'test.db' AS t2; 295 PRAGMA integrity_check 296 } 297 } {{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}} 298 do_test pragma-3.5 { 299 execsql { 300 PRAGMA integrity_check=4 301 } 302 } {{row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2} {row 1 missing from index i2}} 303 do_test pragma-3.6 { 304 execsql { 305 PRAGMA integrity_check=xyz 306 } 307 } {{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}} 308 do_test pragma-3.7 { 309 execsql { 310 PRAGMA integrity_check=0 311 } 312 } {{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}} 313 314 # Add additional corruption by appending unused pages to the end of 315 # the database file testerr.db 316 # 317 do_test pragma-3.8 { 318 execsql {DETACH t2} 319 forcedelete testerr.db testerr.db-journal 320 set out [open testerr.db w] 321 fconfigure $out -translation binary 322 set in [open test.db r] 323 fconfigure $in -translation binary 324 puts -nonewline $out [read $in] 325 seek $in 0 326 puts -nonewline $out [read $in] 327 close $in 328 close $out 329 hexio_write testerr.db 28 00000000 330 execsql {REINDEX t2} 331 execsql {PRAGMA integrity_check} 332 } {ok} 333 do_test pragma-3.8.1 { 334 execsql {PRAGMA quick_check} 335 } {ok} 336 do_test pragma-3.8.2 { 337 execsql {PRAGMA QUICK_CHECK} 338 } {ok} 339 do_test pragma-3.9 { 340 execsql { 341 ATTACH 'testerr.db' AS t2; 342 PRAGMA integrity_check 343 } 344 } {{*** in database t2 *** 345Page 4 is never used 346Page 5 is never used 347Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 348 do_test pragma-3.10 { 349 execsql { 350 PRAGMA integrity_check=1 351 } 352 } {{*** in database t2 *** 353Page 4 is never used}} 354 do_test pragma-3.11 { 355 execsql { 356 PRAGMA integrity_check=5 357 } 358 } {{*** in database t2 *** 359Page 4 is never used 360Page 5 is never used 361Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2}} 362 do_test pragma-3.12 { 363 execsql { 364 PRAGMA integrity_check=4 365 } 366 } {{*** in database t2 *** 367Page 4 is never used 368Page 5 is never used 369Page 6 is never used} {row 1 missing from index i2}} 370 do_test pragma-3.13 { 371 execsql { 372 PRAGMA integrity_check=3 373 } 374 } {{*** in database t2 *** 375Page 4 is never used 376Page 5 is never used 377Page 6 is never used}} 378 do_test pragma-3.14 { 379 execsql { 380 PRAGMA integrity_check(2) 381 } 382 } {{*** in database t2 *** 383Page 4 is never used 384Page 5 is never used}} 385 do_test pragma-3.15 { 386 execsql { 387 ATTACH 'testerr.db' AS t3; 388 PRAGMA integrity_check 389 } 390 } {{*** in database t2 *** 391Page 4 is never used 392Page 5 is never used 393Page 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 *** 394Page 4 is never used 395Page 5 is never used 396Page 6 is never used} {row 1 missing from index i2} {row 2 missing from index i2} {wrong # of entries in index i2}} 397 do_test pragma-3.16 { 398 execsql { 399 PRAGMA integrity_check(10) 400 } 401 } {{*** in database t2 *** 402Page 4 is never used 403Page 5 is never used 404Page 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 *** 405Page 4 is never used 406Page 5 is never used 407Page 6 is never used} {row 1 missing from index i2}} 408 do_test pragma-3.17 { 409 execsql { 410 PRAGMA integrity_check=8 411 } 412 } {{*** in database t2 *** 413Page 4 is never used 414Page 5 is never used 415Page 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 *** 416Page 4 is never used 417Page 5 is never used}} 418 do_test pragma-3.18 { 419 execsql { 420 PRAGMA integrity_check=4 421 } 422 } {{*** in database t2 *** 423Page 4 is never used 424Page 5 is never used 425Page 6 is never used} {row 1 missing from index i2}} 426 } 427 do_test pragma-3.19 { 428 catch {db close} 429 forcedelete test.db test.db-journal 430 sqlite3 db test.db 431 db eval {PRAGMA integrity_check} 432 } {ok} 433} 434#exit 435 436# Test modifying the cache_size of an attached database. 437ifcapable pager_pragmas&&attach { 438do_test pragma-4.1 { 439 execsql { 440 ATTACH 'test2.db' AS aux; 441 pragma aux.cache_size; 442 pragma aux.default_cache_size; 443 } 444} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 445do_test pragma-4.2 { 446 execsql { 447 pragma aux.cache_size = 50; 448 pragma aux.cache_size; 449 pragma aux.default_cache_size; 450 } 451} [list 50 $DFLT_CACHE_SZ] 452do_test pragma-4.3 { 453 execsql { 454 pragma aux.default_cache_size = 456; 455 pragma aux.cache_size; 456 pragma aux.default_cache_size; 457 } 458} {456 456} 459do_test pragma-4.4 { 460 execsql { 461 pragma cache_size; 462 pragma default_cache_size; 463 } 464} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 465do_test pragma-4.5 { 466 execsql { 467 DETACH aux; 468 ATTACH 'test3.db' AS aux; 469 pragma aux.cache_size; 470 pragma aux.default_cache_size; 471 } 472} [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 473do_test pragma-4.6 { 474 execsql { 475 DETACH aux; 476 ATTACH 'test2.db' AS aux; 477 pragma aux.cache_size; 478 pragma aux.default_cache_size; 479 } 480} {456 456} 481} ;# ifcapable pager_pragmas 482 483# Test that modifying the sync-level in the middle of a transaction is 484# disallowed. 485ifcapable pager_pragmas { 486do_test pragma-5.0 { 487 execsql { 488 pragma synchronous; 489 } 490} {2} 491do_test pragma-5.1 { 492 catchsql { 493 BEGIN; 494 pragma synchronous = OFF; 495 } 496} {1 {Safety level may not be changed inside a transaction}} 497do_test pragma-5.2 { 498 execsql { 499 pragma synchronous; 500 } 501} {2} 502catchsql {COMMIT;} 503} ;# ifcapable pager_pragmas 504 505# Test schema-query pragmas 506# 507ifcapable schema_pragmas { 508ifcapable tempdb&&attach { 509 do_test pragma-6.1 { 510 set res {} 511 execsql {SELECT * FROM sqlite_temp_master} 512 foreach {idx name file} [execsql {pragma database_list}] { 513 lappend res $idx $name 514 } 515 set res 516 } {0 main 1 temp 2 aux} 517} 518do_test pragma-6.2 { 519 execsql { 520 CREATE TABLE t2(a,b,c); 521 pragma table_info(t2) 522 } 523} {0 a {} 0 {} 0 1 b {} 0 {} 0 2 c {} 0 {} 0} 524do_test pragma-6.2.1 { 525 execsql { 526 pragma table_info; 527 } 528} {} 529db nullvalue <<NULL>> 530do_test pragma-6.2.2 { 531 execsql { 532 CREATE TABLE t5( 533 a TEXT DEFAULT CURRENT_TIMESTAMP, 534 b DEFAULT (5+3), 535 c TEXT, 536 d INTEGER DEFAULT NULL, 537 e TEXT DEFAULT '', 538 UNIQUE(b,c,d), 539 PRIMARY KEY(e,b,c) 540 ); 541 PRAGMA table_info(t5); 542 } 543} {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} 544db nullvalue {} 545do_test pragma-6.2.3 { 546 execsql { 547 CREATE TABLE t2_3(a,b INTEGER PRIMARY KEY,c); 548 pragma table_info(t2_3) 549 } 550} {0 a {} 0 {} 0 1 b INTEGER 0 {} 1 2 c {} 0 {} 0} 551ifcapable {foreignkey} { 552 do_test pragma-6.3.1 { 553 execsql { 554 CREATE TABLE t3(a int references t2(b), b UNIQUE); 555 pragma foreign_key_list(t3); 556 } 557 } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE} 558 do_test pragma-6.3.2 { 559 execsql { 560 pragma foreign_key_list; 561 } 562 } {} 563 do_test pragma-6.3.3 { 564 execsql { 565 pragma foreign_key_list(t3_bogus); 566 } 567 } {} 568 do_test pragma-6.3.4 { 569 execsql { 570 pragma foreign_key_list(t5); 571 } 572 } {} 573 do_test pragma-6.4 { 574 execsql { 575 pragma index_list(t3); 576 } 577 } {0 sqlite_autoindex_t3_1 1} 578} 579ifcapable {!foreignkey} { 580 execsql {CREATE TABLE t3(a,b UNIQUE)} 581} 582do_test pragma-6.5.1 { 583 execsql { 584 CREATE INDEX t3i1 ON t3(a,b); 585 pragma index_info(t3i1); 586 } 587} {0 0 a 1 1 b} 588do_test pragma-6.5.2 { 589 execsql { 590 pragma index_info(t3i1_bogus); 591 } 592} {} 593 594ifcapable tempdb { 595 # Test for ticket #3320. When a temp table of the same name exists, make 596 # sure the schema of the main table can still be queried using 597 # "pragma table_info": 598 do_test pragma-6.6.1 { 599 execsql { 600 CREATE TABLE trial(col_main); 601 CREATE TEMP TABLE trial(col_temp); 602 } 603 } {} 604 do_test pragma-6.6.2 { 605 execsql { 606 PRAGMA table_info(trial); 607 } 608 } {0 col_temp {} 0 {} 0} 609 do_test pragma-6.6.3 { 610 execsql { 611 PRAGMA temp.table_info(trial); 612 } 613 } {0 col_temp {} 0 {} 0} 614 do_test pragma-6.6.4 { 615 execsql { 616 PRAGMA main.table_info(trial); 617 } 618 } {0 col_main {} 0 {} 0} 619} 620 621do_test pragma-6.7 { 622 execsql { 623 CREATE TABLE test_table( 624 one INT NOT NULL DEFAULT -1, 625 two text, 626 three VARCHAR(45, 65) DEFAULT 'abcde', 627 four REAL DEFAULT X'abcdef', 628 five DEFAULT CURRENT_TIME 629 ); 630 PRAGMA table_info(test_table); 631 } 632} [concat \ 633 {0 one INT 1 -1 0} \ 634 {1 two text 0 {} 0} \ 635 {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \ 636 {3 four REAL 0 X'abcdef' 0} \ 637 {4 five {} 0 CURRENT_TIME 0} \ 638] 639} ;# ifcapable schema_pragmas 640# Miscellaneous tests 641# 642ifcapable schema_pragmas { 643do_test pragma-7.1.1 { 644 # Make sure a pragma knows to read the schema if it needs to 645 db close 646 sqlite3 db test.db 647 execsql { 648 pragma index_list(t3); 649 } 650} {0 t3i1 0 1 sqlite_autoindex_t3_1 1} 651do_test pragma-7.1.2 { 652 execsql { 653 pragma index_list(t3_bogus); 654 } 655} {} 656} ;# ifcapable schema_pragmas 657ifcapable {utf16} { 658 if {[permutation] == ""} { 659 do_test pragma-7.2 { 660 db close 661 sqlite3 db test.db 662 catchsql { 663 pragma encoding=bogus; 664 } 665 } {1 {unsupported encoding: bogus}} 666 } 667} 668ifcapable tempdb { 669 do_test pragma-7.3 { 670 db close 671 sqlite3 db test.db 672 execsql { 673 pragma lock_status; 674 } 675 } {main unlocked temp closed} 676} else { 677 do_test pragma-7.3 { 678 db close 679 sqlite3 db test.db 680 execsql { 681 pragma lock_status; 682 } 683 } {main unlocked} 684} 685 686 687#---------------------------------------------------------------------- 688# Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA 689# user_version" statements. 690# 691# pragma-8.1: PRAGMA schema_version 692# pragma-8.2: PRAGMA user_version 693# 694 695ifcapable schema_version { 696 697# First check that we can set the schema version and then retrieve the 698# same value. 699do_test pragma-8.1.1 { 700 execsql { 701 PRAGMA schema_version = 105; 702 } 703} {} 704do_test pragma-8.1.2 { 705 execsql2 { 706 PRAGMA schema_version; 707 } 708} {schema_version 105} 709do_test pragma-8.1.3 { 710 execsql { 711 PRAGMA schema_version = 106; 712 } 713} {} 714do_test pragma-8.1.4 { 715 execsql { 716 PRAGMA schema_version; 717 } 718} 106 719 720# Check that creating a table modifies the schema-version (this is really 721# to verify that the value being read is in fact the schema version). 722do_test pragma-8.1.5 { 723 execsql { 724 CREATE TABLE t4(a, b, c); 725 INSERT INTO t4 VALUES(1, 2, 3); 726 SELECT * FROM t4; 727 } 728} {1 2 3} 729do_test pragma-8.1.6 { 730 execsql { 731 PRAGMA schema_version; 732 } 733} 107 734 735# Now open a second connection to the database. Ensure that changing the 736# schema-version using the first connection forces the second connection 737# to reload the schema. This has to be done using the C-API test functions, 738# because the TCL API accounts for SCHEMA_ERROR and retries the query. 739do_test pragma-8.1.7 { 740 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] 741 execsql { 742 SELECT * FROM t4; 743 } db2 744} {1 2 3} 745do_test pragma-8.1.8 { 746 execsql { 747 PRAGMA schema_version = 108; 748 } 749} {} 750do_test pragma-8.1.9 { 751 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY] 752 sqlite3_step $::STMT 753} SQLITE_ERROR 754do_test pragma-8.1.10 { 755 sqlite3_finalize $::STMT 756} SQLITE_SCHEMA 757 758# Make sure the schema-version can be manipulated in an attached database. 759forcedelete test2.db 760forcedelete test2.db-journal 761ifcapable attach { 762 do_test pragma-8.1.11 { 763 execsql { 764 ATTACH 'test2.db' AS aux; 765 CREATE TABLE aux.t1(a, b, c); 766 PRAGMA aux.schema_version = 205; 767 } 768 } {} 769 do_test pragma-8.1.12 { 770 execsql { 771 PRAGMA aux.schema_version; 772 } 773 } 205 774} 775do_test pragma-8.1.13 { 776 execsql { 777 PRAGMA schema_version; 778 } 779} 108 780 781# And check that modifying the schema-version in an attached database 782# forces the second connection to reload the schema. 783ifcapable attach { 784 do_test pragma-8.1.14 { 785 sqlite3 db2 test.db; set ::DB2 [sqlite3_connection_pointer db2] 786 execsql { 787 ATTACH 'test2.db' AS aux; 788 SELECT * FROM aux.t1; 789 } db2 790 } {} 791 do_test pragma-8.1.15 { 792 execsql { 793 PRAGMA aux.schema_version = 206; 794 } 795 } {} 796 do_test pragma-8.1.16 { 797 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM aux.t1" -1 DUMMY] 798 sqlite3_step $::STMT 799 } SQLITE_ERROR 800 do_test pragma-8.1.17 { 801 sqlite3_finalize $::STMT 802 } SQLITE_SCHEMA 803 do_test pragma-8.1.18 { 804 db2 close 805 } {} 806} 807 808# Now test that the user-version can be read and written (and that we aren't 809# accidentally manipulating the schema-version instead). 810do_test pragma-8.2.1 { 811 execsql2 { 812 PRAGMA user_version; 813 } 814} {user_version 0} 815do_test pragma-8.2.2 { 816 execsql { 817 PRAGMA user_version = 2; 818 } 819} {} 820do_test pragma-8.2.3.1 { 821 execsql2 { 822 PRAGMA user_version; 823 } 824} {user_version 2} 825do_test pragma-8.2.3.2 { 826 db close 827 sqlite3 db test.db 828 execsql { 829 PRAGMA user_version; 830 } 831} {2} 832do_test pragma-8.2.4.1 { 833 execsql { 834 PRAGMA schema_version; 835 } 836} {108} 837ifcapable vacuum { 838 do_test pragma-8.2.4.2 { 839 execsql { 840 VACUUM; 841 PRAGMA user_version; 842 } 843 } {2} 844 do_test pragma-8.2.4.3 { 845 execsql { 846 PRAGMA schema_version; 847 } 848 } {109} 849} 850 851ifcapable attach { 852 db eval {ATTACH 'test2.db' AS aux} 853 854 # Check that the user-version in the auxilary database can be manipulated ( 855 # and that we aren't accidentally manipulating the same in the main db). 856 do_test pragma-8.2.5 { 857 execsql { 858 PRAGMA aux.user_version; 859 } 860 } {0} 861 do_test pragma-8.2.6 { 862 execsql { 863 PRAGMA aux.user_version = 3; 864 } 865 } {} 866 do_test pragma-8.2.7 { 867 execsql { 868 PRAGMA aux.user_version; 869 } 870 } {3} 871 do_test pragma-8.2.8 { 872 execsql { 873 PRAGMA main.user_version; 874 } 875 } {2} 876 877 # Now check that a ROLLBACK resets the user-version if it has been modified 878 # within a transaction. 879 do_test pragma-8.2.9 { 880 execsql { 881 BEGIN; 882 PRAGMA aux.user_version = 10; 883 PRAGMA user_version = 11; 884 } 885 } {} 886 do_test pragma-8.2.10 { 887 execsql { 888 PRAGMA aux.user_version; 889 } 890 } {10} 891 do_test pragma-8.2.11 { 892 execsql { 893 PRAGMA main.user_version; 894 } 895 } {11} 896 do_test pragma-8.2.12 { 897 execsql { 898 ROLLBACK; 899 PRAGMA aux.user_version; 900 } 901 } {3} 902 do_test pragma-8.2.13 { 903 execsql { 904 PRAGMA main.user_version; 905 } 906 } {2} 907} 908 909# Try a negative value for the user-version 910do_test pragma-8.2.14 { 911 execsql { 912 PRAGMA user_version = -450; 913 } 914} {} 915do_test pragma-8.2.15 { 916 execsql { 917 PRAGMA user_version; 918 } 919} {-450} 920} ; # ifcapable schema_version 921 922# Check to see if TEMP_STORE is memory or disk. Return strings 923# "memory" or "disk" as appropriate. 924# 925proc check_temp_store {} { 926 db eval {CREATE TEMP TABLE IF NOT EXISTS a(b)} 927 db eval {PRAGMA database_list} { 928 if {$name=="temp"} { 929 set bt [btree_from_db db 1] 930 if {[btree_ismemdb $bt]} { 931 return "memory" 932 } 933 return "disk" 934 } 935 } 936 return "unknown" 937} 938 939# Application_ID 940# 941do_test pragma-8.3.1 { 942 execsql { 943 PRAGMA application_id; 944 } 945} {0} 946do_test pragma-8.3.2 { 947 execsql {PRAGMA Application_ID(12345); PRAGMA application_id;} 948} {12345} 949 950# Test temp_store and temp_store_directory pragmas 951# 952ifcapable pager_pragmas { 953do_test pragma-9.1 { 954 db close 955 sqlite3 db test.db 956 execsql { 957 PRAGMA temp_store; 958 } 959} {0} 960if {$TEMP_STORE<=1} { 961 do_test pragma-9.1.1 { 962 check_temp_store 963 } {disk} 964} else { 965 do_test pragma-9.1.1 { 966 check_temp_store 967 } {memory} 968} 969 970do_test pragma-9.2 { 971 db close 972 sqlite3 db test.db 973 execsql { 974 PRAGMA temp_store=file; 975 PRAGMA temp_store; 976 } 977} {1} 978if {$TEMP_STORE==3} { 979 # When TEMP_STORE is 3, always use memory regardless of pragma settings. 980 do_test pragma-9.2.1 { 981 check_temp_store 982 } {memory} 983} else { 984 do_test pragma-9.2.1 { 985 check_temp_store 986 } {disk} 987} 988 989do_test pragma-9.3 { 990 db close 991 sqlite3 db test.db 992 execsql { 993 PRAGMA temp_store=memory; 994 PRAGMA temp_store; 995 } 996} {2} 997if {$TEMP_STORE==0} { 998 # When TEMP_STORE is 0, always use the disk regardless of pragma settings. 999 do_test pragma-9.3.1 { 1000 check_temp_store 1001 } {disk} 1002} else { 1003 do_test pragma-9.3.1 { 1004 check_temp_store 1005 } {memory} 1006} 1007 1008do_test pragma-9.4 { 1009 execsql { 1010 PRAGMA temp_store_directory; 1011 } 1012} {} 1013ifcapable wsd { 1014 do_test pragma-9.5 { 1015 set pwd [string map {' ''} [file nativename [get_pwd]]] 1016 execsql " 1017 PRAGMA temp_store_directory='$pwd'; 1018 " 1019 } {} 1020 do_test pragma-9.6 { 1021 execsql { 1022 PRAGMA temp_store_directory; 1023 } 1024 } [list [file nativename [get_pwd]]] 1025 do_test pragma-9.7 { 1026 catchsql { 1027 PRAGMA temp_store_directory='/NON/EXISTENT/PATH/FOOBAR'; 1028 } 1029 } {1 {not a writable directory}} 1030 do_test pragma-9.8 { 1031 execsql { 1032 PRAGMA temp_store_directory=''; 1033 } 1034 } {} 1035 if {![info exists TEMP_STORE] || $TEMP_STORE<=1} { 1036 ifcapable tempdb { 1037 do_test pragma-9.9 { 1038 execsql { 1039 PRAGMA temp_store_directory; 1040 PRAGMA temp_store=FILE; 1041 CREATE TEMP TABLE temp_store_directory_test(a integer); 1042 INSERT INTO temp_store_directory_test values (2); 1043 SELECT * FROM temp_store_directory_test; 1044 } 1045 } {2} 1046 do_test pragma-9.10 { 1047 catchsql " 1048 PRAGMA temp_store_directory='$pwd'; 1049 SELECT * FROM temp_store_directory_test; 1050 " 1051 } {1 {no such table: temp_store_directory_test}} 1052 } 1053 } 1054} 1055do_test pragma-9.11 { 1056 execsql { 1057 PRAGMA temp_store = 0; 1058 PRAGMA temp_store; 1059 } 1060} {0} 1061do_test pragma-9.12 { 1062 execsql { 1063 PRAGMA temp_store = 1; 1064 PRAGMA temp_store; 1065 } 1066} {1} 1067do_test pragma-9.13 { 1068 execsql { 1069 PRAGMA temp_store = 2; 1070 PRAGMA temp_store; 1071 } 1072} {2} 1073do_test pragma-9.14 { 1074 execsql { 1075 PRAGMA temp_store = 3; 1076 PRAGMA temp_store; 1077 } 1078} {0} 1079do_test pragma-9.15 { 1080 catchsql { 1081 BEGIN EXCLUSIVE; 1082 CREATE TEMP TABLE temp_table(t); 1083 INSERT INTO temp_table VALUES('valuable data'); 1084 PRAGMA temp_store = 1; 1085 } 1086} {1 {temporary storage cannot be changed from within a transaction}} 1087do_test pragma-9.16 { 1088 execsql { 1089 SELECT * FROM temp_table; 1090 COMMIT; 1091 } 1092} {{valuable data}} 1093 1094do_test pragma-9.17 { 1095 execsql { 1096 INSERT INTO temp_table VALUES('valuable data II'); 1097 SELECT * FROM temp_table; 1098 } 1099} {{valuable data} {valuable data II}} 1100 1101do_test pragma-9.18 { 1102 set rc [catch { 1103 db eval {SELECT t FROM temp_table} { 1104 execsql {pragma temp_store = 1} 1105 } 1106 } msg] 1107 list $rc $msg 1108} {1 {temporary storage cannot be changed from within a transaction}} 1109 1110} ;# ifcapable pager_pragmas 1111 1112ifcapable trigger { 1113 1114do_test pragma-10.0 { 1115 catchsql { 1116 DROP TABLE main.t1; 1117 } 1118 execsql { 1119 PRAGMA count_changes = 1; 1120 1121 CREATE TABLE t1(a PRIMARY KEY); 1122 CREATE TABLE t1_mirror(a); 1123 CREATE TABLE t1_mirror2(a); 1124 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 BEGIN 1125 INSERT INTO t1_mirror VALUES(new.a); 1126 END; 1127 CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN 1128 INSERT INTO t1_mirror2 VALUES(new.a); 1129 END; 1130 CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN 1131 UPDATE t1_mirror SET a = new.a WHERE a = old.a; 1132 END; 1133 CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN 1134 UPDATE t1_mirror2 SET a = new.a WHERE a = old.a; 1135 END; 1136 CREATE TRIGGER t1_bd BEFORE DELETE ON t1 BEGIN 1137 DELETE FROM t1_mirror WHERE a = old.a; 1138 END; 1139 CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN 1140 DELETE FROM t1_mirror2 WHERE a = old.a; 1141 END; 1142 } 1143} {} 1144 1145do_test pragma-10.1 { 1146 execsql { 1147 INSERT INTO t1 VALUES(randstr(10,10)); 1148 } 1149} {1} 1150do_test pragma-10.2 { 1151 execsql { 1152 UPDATE t1 SET a = randstr(10,10); 1153 } 1154} {1} 1155do_test pragma-10.3 { 1156 execsql { 1157 DELETE FROM t1; 1158 } 1159} {1} 1160 1161} ;# ifcapable trigger 1162 1163ifcapable schema_pragmas { 1164 do_test pragma-11.1 { 1165 execsql2 { 1166 pragma collation_list; 1167 } 1168 } {seq 0 name NOCASE seq 1 name RTRIM seq 2 name BINARY} 1169 do_test pragma-11.2 { 1170 db collate New_Collation blah... 1171 execsql { 1172 pragma collation_list; 1173 } 1174 } {0 New_Collation 1 NOCASE 2 RTRIM 3 BINARY} 1175} 1176 1177ifcapable schema_pragmas&&tempdb { 1178 do_test pragma-12.1 { 1179 sqlite3 db2 test.db 1180 execsql { 1181 PRAGMA temp.table_info('abc'); 1182 } db2 1183 } {} 1184 db2 close 1185 1186 do_test pragma-12.2 { 1187 sqlite3 db2 test.db 1188 execsql { 1189 PRAGMA temp.default_cache_size = 200; 1190 PRAGMA temp.default_cache_size; 1191 } db2 1192 } {200} 1193 db2 close 1194 1195 do_test pragma-12.3 { 1196 sqlite3 db2 test.db 1197 execsql { 1198 PRAGMA temp.cache_size = 400; 1199 PRAGMA temp.cache_size; 1200 } db2 1201 } {400} 1202 db2 close 1203} 1204 1205ifcapable bloblit { 1206 1207do_test pragma-13.1 { 1208 execsql { 1209 DROP TABLE IF EXISTS t4; 1210 PRAGMA vdbe_trace=on; 1211 PRAGMA vdbe_listing=on; 1212 PRAGMA sql_trace=on; 1213 CREATE TABLE t4(a INTEGER PRIMARY KEY,b); 1214 INSERT INTO t4(b) VALUES(x'0123456789abcdef0123456789abcdef0123456789'); 1215 INSERT INTO t4(b) VALUES(randstr(30,30)); 1216 INSERT INTO t4(b) VALUES(1.23456); 1217 INSERT INTO t4(b) VALUES(NULL); 1218 INSERT INTO t4(b) VALUES(0); 1219 INSERT INTO t4(b) SELECT b||b||b||b FROM t4; 1220 SELECT * FROM t4; 1221 } 1222 execsql { 1223 PRAGMA vdbe_trace=off; 1224 PRAGMA vdbe_listing=off; 1225 PRAGMA sql_trace=off; 1226 } 1227} {} 1228 1229} ;# ifcapable bloblit 1230 1231ifcapable pager_pragmas { 1232 db close 1233 forcedelete test.db 1234 sqlite3 db test.db 1235 1236 do_test pragma-14.1 { 1237 execsql { pragma auto_vacuum = 0 } 1238 execsql { pragma page_count } 1239 } {0} 1240 1241 do_test pragma-14.2 { 1242 execsql { 1243 CREATE TABLE abc(a, b, c); 1244 PRAGMA page_count; 1245 } 1246 } {2} 1247 do_test pragma-14.2uc { 1248 execsql {pragma PAGE_COUNT} 1249 } {2} 1250 1251 do_test pragma-14.3 { 1252 execsql { 1253 BEGIN; 1254 CREATE TABLE def(a, b, c); 1255 PRAGMA page_count; 1256 } 1257 } {3} 1258 do_test pragma-14.3uc { 1259 execsql {pragma PAGE_COUNT} 1260 } {3} 1261 1262 do_test pragma-14.4 { 1263 set page_size [db one {pragma page_size}] 1264 expr [file size test.db] / $page_size 1265 } {2} 1266 1267 do_test pragma-14.5 { 1268 execsql { 1269 ROLLBACK; 1270 PRAGMA page_count; 1271 } 1272 } {2} 1273 1274 do_test pragma-14.6 { 1275 forcedelete test2.db 1276 sqlite3 db2 test2.db 1277 execsql { 1278 PRAGMA auto_vacuum = 0; 1279 CREATE TABLE t1(a, b, c); 1280 CREATE TABLE t2(a, b, c); 1281 CREATE TABLE t3(a, b, c); 1282 CREATE TABLE t4(a, b, c); 1283 } db2 1284 db2 close 1285 execsql { 1286 ATTACH 'test2.db' AS aux; 1287 PRAGMA aux.page_count; 1288 } 1289 } {5} 1290 do_test pragma-14.6uc { 1291 execsql {pragma AUX.PAGE_COUNT} 1292 } {5} 1293} 1294 1295# Test that the value set using the cache_size pragma is not reset when the 1296# schema is reloaded. 1297# 1298ifcapable pager_pragmas { 1299 db close 1300 sqlite3 db test.db 1301 do_test pragma-15.1 { 1302 execsql { 1303 PRAGMA cache_size=59; 1304 PRAGMA cache_size; 1305 } 1306 } {59} 1307 do_test pragma-15.2 { 1308 sqlite3 db2 test.db 1309 execsql { 1310 CREATE TABLE newtable(a, b, c); 1311 } db2 1312 db2 close 1313 } {} 1314 do_test pragma-15.3 { 1315 # Evaluating this statement will cause the schema to be reloaded (because 1316 # the schema was changed by another connection in pragma-15.2). At one 1317 # point there was a bug that reset the cache_size to its default value 1318 # when this happened. 1319 execsql { SELECT * FROM sqlite_master } 1320 execsql { PRAGMA cache_size } 1321 } {59} 1322} 1323 1324# Reset the sqlite3_temp_directory variable for the next run of tests: 1325sqlite3 dbX :memory: 1326dbX eval {PRAGMA temp_store_directory = ""} 1327dbX close 1328 1329ifcapable lock_proxy_pragmas&&prefer_proxy_locking { 1330 set sqlite_hostid_num 1 1331 1332 set using_proxy 0 1333 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] { 1334 set using_proxy $value 1335 } 1336 1337 # Test the lock_proxy_file pragmas. 1338 # 1339 db close 1340 set env(SQLITE_FORCE_PROXY_LOCKING) "0" 1341 1342 sqlite3 db test.db 1343 do_test pragma-16.1 { 1344 execsql { 1345 PRAGMA lock_proxy_file="mylittleproxy"; 1346 select * from sqlite_master; 1347 } 1348 execsql { 1349 PRAGMA lock_proxy_file; 1350 } 1351 } {mylittleproxy} 1352 1353 do_test pragma-16.2 { 1354 sqlite3 db2 test.db 1355 execsql { 1356 PRAGMA lock_proxy_file="mylittleproxy"; 1357 } db2 1358 } {} 1359 1360 db2 close 1361 do_test pragma-16.2.1 { 1362 sqlite3 db2 test.db 1363 execsql { 1364 PRAGMA lock_proxy_file=":auto:"; 1365 select * from sqlite_master; 1366 } db2 1367 execsql { 1368 PRAGMA lock_proxy_file; 1369 } db2 1370 } {mylittleproxy} 1371 1372 db2 close 1373 do_test pragma-16.3 { 1374 sqlite3 db2 test.db 1375 execsql { 1376 PRAGMA lock_proxy_file="myotherproxy"; 1377 } db2 1378 catchsql { 1379 select * from sqlite_master; 1380 } db2 1381 } {1 {database is locked}} 1382 1383 do_test pragma-16.4 { 1384 db2 close 1385 db close 1386 sqlite3 db2 test.db 1387 execsql { 1388 PRAGMA lock_proxy_file="myoriginalproxy"; 1389 PRAGMA lock_proxy_file="myotherproxy"; 1390 PRAGMA lock_proxy_file; 1391 } db2 1392 } {myotherproxy} 1393 1394 db2 close 1395 set env(SQLITE_FORCE_PROXY_LOCKING) "1" 1396 do_test pragma-16.5 { 1397 sqlite3 db2 test.db 1398 execsql { 1399 PRAGMA lock_proxy_file=":auto:"; 1400 PRAGMA lock_proxy_file; 1401 } db2 1402 } {myotherproxy} 1403 1404 do_test pragma-16.6 { 1405 db2 close 1406 sqlite3 db2 test2.db 1407 set lockpath [execsql { 1408 PRAGMA lock_proxy_file=":auto:"; 1409 PRAGMA lock_proxy_file; 1410 } db2] 1411 string match "*test2.db:auto:" $lockpath 1412 } {1} 1413 1414 set sqlite_hostid_num 2 1415 do_test pragma-16.7 { 1416 list [catch { 1417 sqlite3 db test2.db 1418 execsql { 1419 PRAGMA lock_proxy_file=":auto:"; 1420 select * from sqlite_master; 1421 } 1422 } msg] $msg 1423 } {1 {database is locked}} 1424 db close 1425 1426 do_test pragma-16.8 { 1427 list [catch { 1428 sqlite3 db test2.db 1429 execsql { select * from sqlite_master } 1430 } msg] $msg 1431 } {1 {database is locked}} 1432 1433 db2 close 1434 do_test pragma-16.8.1 { 1435 execsql { 1436 PRAGMA lock_proxy_file="yetanotherproxy"; 1437 PRAGMA lock_proxy_file; 1438 } 1439 } {yetanotherproxy} 1440 do_test pragma-16.8.2 { 1441 execsql { 1442 create table mine(x); 1443 } 1444 } {} 1445 1446 db close 1447 do_test pragma-16.9 { 1448 sqlite3 db proxytest.db 1449 set lockpath2 [execsql { 1450 PRAGMA lock_proxy_file=":auto:"; 1451 PRAGMA lock_proxy_file; 1452 } db] 1453 string match "*proxytest.db:auto:" $lockpath2 1454 } {1} 1455 1456 set env(SQLITE_FORCE_PROXY_LOCKING) $using_proxy 1457 set sqlite_hostid_num 0 1458} 1459 1460# Parsing of auto_vacuum settings. 1461# 1462foreach {autovac_setting val} { 1463 0 0 1464 1 1 1465 2 2 1466 3 0 1467 -1 0 1468 none 0 1469 NONE 0 1470 NoNe 0 1471 full 1 1472 FULL 1 1473 incremental 2 1474 INCREMENTAL 2 1475 -1234 0 1476 1234 0 1477} { 1478 do_test pragma-17.1.$autovac_setting { 1479 catch {db close} 1480 sqlite3 db :memory: 1481 execsql " 1482 PRAGMA auto_vacuum=$::autovac_setting; 1483 PRAGMA auto_vacuum; 1484 " 1485 } $val 1486} 1487 1488# Parsing of temp_store settings. 1489# 1490foreach {temp_setting val} { 1491 0 0 1492 1 1 1493 2 2 1494 3 0 1495 -1 0 1496 file 1 1497 FILE 1 1498 fIlE 1 1499 memory 2 1500 MEMORY 2 1501 MeMoRy 2 1502} { 1503 do_test pragma-18.1.$temp_setting { 1504 catch {db close} 1505 sqlite3 db :memory: 1506 execsql " 1507 PRAGMA temp_store=$::temp_setting; 1508 PRAGMA temp_store=$::temp_setting; 1509 PRAGMA temp_store; 1510 " 1511 } $val 1512} 1513 1514# The SQLITE_FCNTL_PRAGMA logic, with error handling. 1515# 1516db close 1517testvfs tvfs 1518sqlite3 db test.db -vfs tvfs 1519do_test pragma-19.1 { 1520 catchsql {PRAGMA error} 1521} {1 {SQL logic error or missing database}} 1522do_test pragma-19.2 { 1523 catchsql {PRAGMA error='This is the error message'} 1524} {1 {This is the error message}} 1525do_test pragma-19.3 { 1526 catchsql {PRAGMA error='7 This is the error message'} 1527} {1 {This is the error message}} 1528do_test pragma-19.4 { 1529 catchsql {PRAGMA error=7} 1530} {1 {out of memory}} 1531do_test pragma-19.5 { 1532 file tail [lindex [execsql {PRAGMA filename}] 0] 1533} {test.db} 1534 1535if {$tcl_platform(platform)=="windows"} { 1536# Test data_store_directory pragma 1537# 1538db close 1539sqlite3 db test.db 1540file mkdir data_dir 1541do_test pragma-20.1 { 1542 catchsql {PRAGMA data_store_directory} 1543} {0 {}} 1544do_test pragma-20.2 { 1545 set pwd [string map {' ''} [file nativename [get_pwd]]] 1546 catchsql "PRAGMA data_store_directory='$pwd';" 1547} {0 {}} 1548do_test pragma-20.3 { 1549 catchsql {PRAGMA data_store_directory} 1550} [list 0 [list [file nativename [get_pwd]]]] 1551do_test pragma-20.4 { 1552 set pwd [string map {' ''} [file nativename \ 1553 [file join [get_pwd] data_dir]]] 1554 catchsql "PRAGMA data_store_directory='$pwd';" 1555} {0 {}} 1556do_test pragma-20.5 { 1557 sqlite3 db2 test2.db 1558 catchsql "PRAGMA database_list;" db2 1559} [list 0 [list 0 main [file nativename \ 1560 [file join [get_pwd] data_dir test2.db]]]] 1561catch {db2 close} 1562do_test pragma-20.6 { 1563 sqlite3 db2 [file join [get_pwd] test2.db] 1564 catchsql "PRAGMA database_list;" db2 1565} [list 0 [list 0 main [file nativename \ 1566 [file join [get_pwd] test2.db]]]] 1567catch {db2 close} 1568do_test pragma-20.7 { 1569 catchsql "PRAGMA data_store_directory='';" 1570} {0 {}} 1571do_test pragma-20.8 { 1572 catchsql {PRAGMA data_store_directory} 1573} {0 {}} 1574 1575forcedelete data_dir 1576} ;# endif windows 1577 1578do_test 21.1 { 1579 # Create a corrupt database in testerr.db. And a non-corrupt at test.db. 1580 # 1581 db close 1582 forcedelete test.db 1583 sqlite3 db test.db 1584 execsql { 1585 PRAGMA page_size = 1024; 1586 PRAGMA auto_vacuum = 0; 1587 CREATE TABLE t1(a PRIMARY KEY, b); 1588 INSERT INTO t1 VALUES(1, 1); 1589 } 1590 for {set i 0} {$i < 10} {incr i} { 1591 execsql { INSERT INTO t1 SELECT a + (1 << $i), b + (1 << $i) FROM t1 } 1592 } 1593 db close 1594 forcecopy test.db testerr.db 1595 hexio_write testerr.db 15000 [string repeat 55 100] 1596} {100} 1597 1598set mainerr {*** in database main *** 1599Multiple uses for byte 672 of page 15} 1600set auxerr {*** in database aux *** 1601Multiple uses for byte 672 of page 15} 1602 1603do_test 22.2 { 1604 catch { db close } 1605 sqlite3 db testerr.db 1606 execsql { PRAGMA integrity_check } 1607} [list $mainerr] 1608 1609do_test 22.3.1 { 1610 catch { db close } 1611 sqlite3 db test.db 1612 execsql { 1613 ATTACH 'testerr.db' AS 'aux'; 1614 PRAGMA integrity_check; 1615 } 1616} [list $auxerr] 1617do_test 22.3.2 { 1618 execsql { PRAGMA main.integrity_check; } 1619} {ok} 1620do_test 22.3.3 { 1621 execsql { PRAGMA aux.integrity_check; } 1622} [list $auxerr] 1623 1624do_test 22.4.1 { 1625 catch { db close } 1626 sqlite3 db testerr.db 1627 execsql { 1628 ATTACH 'test.db' AS 'aux'; 1629 PRAGMA integrity_check; 1630 } 1631} [list $mainerr] 1632do_test 22.4.2 { 1633 execsql { PRAGMA main.integrity_check; } 1634} [list $mainerr] 1635do_test 22.4.3 { 1636 execsql { PRAGMA aux.integrity_check; } 1637} {ok} 1638 1639db close 1640forcedelete test.db test.db-wal test.db-journal 1641sqlite3 db test.db 1642sqlite3 db2 test.db 1643do_test 23.1 { 1644 db eval { 1645 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); 1646 CREATE INDEX i1 ON t1(b,c); 1647 CREATE INDEX i2 ON t1(c,d); 1648 CREATE TABLE t2(x INTEGER REFERENCES t1); 1649 } 1650 db2 eval {SELECT name FROM sqlite_master} 1651} {t1 i1 i2 t2} 1652do_test 23.2 { 1653 db eval { 1654 DROP INDEX i2; 1655 CREATE INDEX i2 ON t1(c,d,b); 1656 } 1657 db2 eval {PRAGMA index_info(i2)} 1658} {0 2 c 1 3 d 2 1 b} 1659do_test 23.3 { 1660 db eval { 1661 CREATE INDEX i3 ON t1(d,b,c); 1662 } 1663 db2 eval {PRAGMA index_list(t1)} 1664} {0 i3 0 1 i2 0 2 i1 0} 1665do_test 23.4 { 1666 db eval { 1667 ALTER TABLE t1 ADD COLUMN e; 1668 } 1669 db2 eval { 1670 PRAGMA table_info(t1); 1671 } 1672} {/4 e {} 0 {} 0/} 1673do_test 23.5 { 1674 db eval { 1675 DROP TABLE t2; 1676 CREATE TABLE t2(x, y INTEGER REFERENCES t1); 1677 } 1678 db2 eval { 1679 PRAGMA foreign_key_list(t2); 1680 } 1681} {0 0 t1 y {} {NO ACTION} {NO ACTION} NONE} 1682 1683finish_test 1684