gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/pragma.test (about) 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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 set 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 # 24 do_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 49 ifcapable !pragma { 50 finish_test 51 return 52 } 53 54 # Capture the output of a pragma in a TEMP table. 55 # 56 proc 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 # 81 db close 82 delete_file test.db test.db-journal 83 delete_file test3.db test3.db-journal 84 sqlite3 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 # 91 ifcapable pager_pragmas { 92 set DFLT_CACHE_SZ [db one {PRAGMA default_cache_size}] 93 set TEMP_CACHE_SZ [db one {PRAGMA temp.default_cache_size}] 94 do_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] 101 do_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] 112 do_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] 121 do_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] 129 do_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] 137 do_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] 145 do_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] 154 do_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} 162 do_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} 171 ifcapable 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 } 181 do_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} 189 do_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} 197 do_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} 205 do_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 # 218 do_test pragma-1.13 { 219 execsql { 220 PRAGMA synchronous=0; 221 PRAGMA synchronous; 222 } 223 } {0} 224 do_test pragma-1.14 { 225 execsql { 226 PRAGMA synchronous=2; 227 PRAGMA synchronous; 228 } 229 } {2} 230 do_test pragma-1.14.1 { 231 execsql { 232 PRAGMA synchronous=4; 233 PRAGMA synchronous; 234 } 235 } {4} 236 do_test pragma-1.14.2 { 237 execsql { 238 PRAGMA synchronous=3; 239 PRAGMA synchronous; 240 } 241 } {3} 242 do_test pragma-1.14.3 { 243 execsql { 244 PRAGMA synchronous=8; 245 PRAGMA synchronous; 246 } 247 } {0} 248 do_test pragma-1.14.4 { 249 execsql { 250 PRAGMA synchronous=10; 251 PRAGMA synchronous; 252 } 253 } {2} 254 255 do_execsql_test 1.15.1 { 256 PRAGMA default_cache_size = 0; 257 } 258 do_execsql_test 1.15.2 { 259 PRAGMA default_cache_size; 260 } $DFLT_CACHE_SZ 261 do_execsql_test 1.15.3 { 262 PRAGMA default_cache_size = -500; 263 } 264 do_execsql_test 1.15.4 { 265 PRAGMA default_cache_size; 266 } 500 267 do_execsql_test 1.15.3 { 268 PRAGMA default_cache_size = 500; 269 } 270 do_execsql_test 1.15.4 { 271 PRAGMA default_cache_size; 272 } 500 273 db close 274 hexio_write test.db 48 FFFFFF00 275 sqlite3 db test.db 276 do_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 # 283 ifcapable 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 300 do_test pragma-1.17 { 301 execsql { 302 PRAGMA parser_trace=ON; 303 PRAGMA parser_trace=OFF; 304 } 305 } {} 306 do_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. 313 ifcapable 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 # 347 do_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} 362 ifcapable 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 *** 436 Page 4 is never used 437 Page 5 is never used 438 Page 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 *** 450 Page 4 is never used}} 451 do_test pragma-3.11 { 452 execsql { 453 PRAGMA integrity_check=5 454 } 455 } {{*** in database t2 *** 456 Page 4 is never used 457 Page 5 is never used 458 Page 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 *** 464 Page 4 is never used 465 Page 5 is never used 466 Page 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 *** 472 Page 4 is never used 473 Page 5 is never used 474 Page 6 is never used}} 475 do_test pragma-3.14 { 476 execsql { 477 PRAGMA integrity_check(2) 478 } 479 } {{*** in database t2 *** 480 Page 4 is never used 481 Page 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 *** 488 Page 4 is never used 489 Page 5 is never used 490 Page 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 *** 491 Page 4 is never used 492 Page 5 is never used 493 Page 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 *** 499 Page 4 is never used 500 Page 5 is never used 501 Page 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 *** 502 Page 4 is never used 503 Page 5 is never used 504 Page 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 *** 510 Page 4 is never used 511 Page 5 is never used 512 Page 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 *** 513 Page 4 is never used 514 Page 5 is never used}} 515 do_test pragma-3.18 { 516 execsql { 517 PRAGMA integrity_check=4 518 } 519 } {{*** in database t2 *** 520 Page 4 is never used 521 Page 5 is never used 522 Page 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 # 535 ifcapable 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 # 568 do_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. 586 ifcapable pager_pragmas&&attach { 587 do_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] 594 do_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] 601 do_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} 608 do_test pragma-4.4 { 609 execsql { 610 pragma cache_size; 611 pragma default_cache_size; 612 } 613 } [list $DFLT_CACHE_SZ $DFLT_CACHE_SZ] 614 do_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] 622 do_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. 634 ifcapable pager_pragmas { 635 do_test pragma-5.0 { 636 execsql { 637 pragma synchronous; 638 } 639 } {2} 640 do_test pragma-5.1 { 641 catchsql { 642 BEGIN; 643 pragma synchronous = OFF; 644 } 645 } {1 {Safety level may not be changed inside a transaction}} 646 do_test pragma-5.2 { 647 execsql { 648 pragma synchronous; 649 } 650 } {2} 651 catchsql {COMMIT;} 652 } ;# ifcapable pager_pragmas 653 654 # Test schema-query pragmas 655 # 656 ifcapable schema_pragmas { 657 ifcapable 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 } 667 do_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} 673 do_test pragma-6.2.1 { 674 execsql { 675 pragma table_info; 676 } 677 } {} 678 db nullvalue <<NULL>> 679 do_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} 693 db nullvalue {} 694 do_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} 700 ifcapable {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 } 729 ifcapable {!foreignkey} { 730 execsql {CREATE TABLE t3(a,b UNIQUE)} 731 } 732 do_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 # 746 do_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 # 766 do_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 772 do_test pragma-6.5.2 { 773 execsql { 774 pragma index_info(t3i1_bogus); 775 } 776 } {} 777 778 ifcapable 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 805 do_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 ] 825 do_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 # 838 ifcapable 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 # 842 do_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} 849 do_test pragma-7.1.2 { 850 execsql { 851 pragma index_list(t3_bogus); 852 } 853 } {} 854 } ;# ifcapable schema_pragmas 855 ifcapable {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 } 866 ifcapable 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 893 ifcapable schema_version { 894 895 # First check that we can set the schema version and then retrieve the 896 # same value. 897 do_test pragma-8.1.1 { 898 execsql { 899 PRAGMA schema_version = 105; 900 } 901 } {} 902 do_test pragma-8.1.2 { 903 execsql2 { 904 PRAGMA schema_version; 905 } 906 } {schema_version 105} 907 do_test pragma-8.1.3 { 908 execsql { 909 PRAGMA schema_version = 106; 910 } 911 } {} 912 do_test pragma-8.1.4 { 913 execsql { 914 PRAGMA schema_version; 915 } 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). 920 do_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} 927 do_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. 937 do_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} 943 do_test pragma-8.1.8 { 944 execsql { 945 PRAGMA schema_version = 108; 946 } 947 } {} 948 do_test pragma-8.1.9 { 949 set ::STMT [sqlite3_prepare $::DB2 "SELECT * FROM t4" -1 DUMMY] 950 sqlite3_step $::STMT 951 } SQLITE_ERROR 952 do_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. 957 forcedelete test2.db 958 forcedelete test2.db-journal 959 ifcapable 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 } 973 do_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. 981 ifcapable 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). 1008 do_test pragma-8.2.1 { 1009 execsql2 { 1010 PRAGMA user_version; 1011 } 1012 } {user_version 0} 1013 do_test pragma-8.2.2 { 1014 execsql { 1015 PRAGMA user_version = 2; 1016 } 1017 } {} 1018 do_test pragma-8.2.3.1 { 1019 execsql2 { 1020 PRAGMA user_version; 1021 } 1022 } {user_version 2} 1023 do_test pragma-8.2.3.2 { 1024 db close 1025 sqlite3 db test.db 1026 execsql { 1027 PRAGMA user_version; 1028 } 1029 } {2} 1030 do_test pragma-8.2.4.1 { 1031 execsql { 1032 PRAGMA schema_version; 1033 } 1034 } {108} 1035 ifcapable 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 1049 ifcapable 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 1108 do_test pragma-8.2.14 { 1109 execsql { 1110 PRAGMA user_version = -450; 1111 } 1112 } {} 1113 do_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 # 1123 proc 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 # 1152 do_test pragma-8.3.1 { 1153 execsql { 1154 PRAGMA application_id; 1155 } 1156 } {0} 1157 do_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 # 1163 ifcapable pager_pragmas { 1164 do_test pragma-9.1 { 1165 db close 1166 sqlite3 db test.db 1167 execsql { 1168 PRAGMA temp_store; 1169 } 1170 } {0} 1171 if {$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 1181 do_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} 1189 if {$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 1200 do_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} 1208 if {$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 1219 do_test pragma-9.4 { 1220 execsql { 1221 PRAGMA temp_store_directory; 1222 } 1223 } {} 1224 ifcapable 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 } 1266 do_test pragma-9.11 { 1267 execsql { 1268 PRAGMA temp_store = 0; 1269 PRAGMA temp_store; 1270 } 1271 } {0} 1272 do_test pragma-9.12 { 1273 execsql { 1274 PRAGMA temp_store = 1; 1275 PRAGMA temp_store; 1276 } 1277 } {1} 1278 do_test pragma-9.13 { 1279 execsql { 1280 PRAGMA temp_store = 2; 1281 PRAGMA temp_store; 1282 } 1283 } {2} 1284 do_test pragma-9.14 { 1285 execsql { 1286 PRAGMA temp_store = 3; 1287 PRAGMA temp_store; 1288 } 1289 } {0} 1290 do_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}} 1298 do_test pragma-9.16 { 1299 execsql { 1300 SELECT * FROM temp_table; 1301 COMMIT; 1302 } 1303 } {{valuable data}} 1304 1305 do_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 1312 do_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 1323 ifcapable trigger { 1324 1325 do_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 1356 do_test pragma-10.1 { 1357 execsql { 1358 INSERT INTO t1 VALUES(randstr(10,10)); 1359 } 1360 } {1} 1361 do_test pragma-10.2 { 1362 execsql { 1363 UPDATE t1 SET a = randstr(10,10); 1364 } 1365 } {1} 1366 do_test pragma-10.3 { 1367 execsql { 1368 DELETE FROM t1; 1369 } 1370 } {1} 1371 1372 } ;# ifcapable trigger 1373 1374 ifcapable 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 1388 ifcapable 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 1416 ifcapable bloblit { 1417 1418 do_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 1442 ifcapable 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 # 1514 ifcapable 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: 1541 sqlite3 dbX :memory: 1542 dbX eval {PRAGMA temp_store_directory = ""} 1543 dbX close 1544 1545 ifcapable 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 # 1678 foreach {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 # 1706 foreach {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 # 1732 db close 1733 testvfs tvfs 1734 sqlite3 db test.db -vfs tvfs 1735 do_test pragma-19.1 { 1736 catchsql {PRAGMA error} 1737 } {1 {SQL logic error}} 1738 do_test pragma-19.2 { 1739 catchsql {PRAGMA error='This is the error message'} 1740 } {1 {This is the error message}} 1741 do_test pragma-19.3 { 1742 catchsql {PRAGMA error='7 This is the error message'} 1743 } {1 {This is the error message}} 1744 do_test pragma-19.4 { 1745 catchsql {PRAGMA error=7} 1746 } {1 {out of memory}} 1747 do_test pragma-19.5 { 1748 file tail [lindex [execsql {PRAGMA filename}] 0] 1749 } {test.db} 1750 1751 if {$tcl_platform(platform)=="windows"} { 1752 # Test data_store_directory pragma 1753 # 1754 db close 1755 sqlite3 db test.db 1756 file mkdir data_dir 1757 do_test pragma-20.1 { 1758 catchsql {PRAGMA data_store_directory} 1759 } {0 {}} 1760 do_test pragma-20.2 { 1761 set pwd [string map {' ''} [file nativename [get_pwd]]] 1762 catchsql "PRAGMA data_store_directory='$pwd';" 1763 } {0 {}} 1764 do_test pragma-20.3 { 1765 catchsql {PRAGMA data_store_directory} 1766 } [list 0 [list [file nativename [get_pwd]]]] 1767 do_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 {}} 1772 do_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]]]] 1777 catch {db2 close} 1778 do_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]]]] 1783 catch {db2 close} 1784 do_test pragma-20.7 { 1785 catchsql "PRAGMA data_store_directory='';" 1786 } {0 {}} 1787 do_test pragma-20.8 { 1788 catchsql {PRAGMA data_store_directory} 1789 } {0 {}} 1790 1791 forcedelete data_dir 1792 } ;# endif windows 1793 1794 database_may_be_corrupt 1795 if {![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 *** 1818 Multiple uses for byte 672 of page 15} 1819 set auxerr {*** in database aux *** 1820 Multiple uses for byte 672 of page 15} 1821 1822 set mainerr {/{\*\*\* in database main \*\*\* 1823 Multiple uses for byte 672 of page 15}.*/} 1824 set auxerr {/{\*\*\* in database aux \*\*\* 1825 Multiple 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 1864 db close 1865 forcedelete test.db test.db-wal test.db-journal 1866 sqlite3 db test.db 1867 sqlite3 db2 test.db 1868 do_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} 1879 do_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 # 1895 do_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 # 1926 do_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} 1929 do_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} 1932 do_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 # 1954 do_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 |} 1962 ifcapable 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 } 1972 do_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} 1981 db2 close 1982 1983 ifcapable !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 } 2005 database_never_corrupt 2006 finish_test