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