Annotation of embedaddon/sqlite3/test/incrvacuum.test, revision 1.1
1.1 ! misho 1: # 2007 April 26
! 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. The
! 12: # focus of this file is testing the incremental vacuum feature.
! 13: #
! 14: # Note: There are also some tests for incremental vacuum and IO
! 15: # errors in incrvacuum_ioerr.test.
! 16: #
! 17: # $Id: incrvacuum.test,v 1.23 2009/02/18 20:31:18 drh Exp $
! 18:
! 19: set testdir [file dirname $argv0]
! 20: source $testdir/tester.tcl
! 21:
! 22: # If this build of the library does not support auto-vacuum, omit this
! 23: # whole file.
! 24: ifcapable {!autovacuum || !pragma} {
! 25: finish_test
! 26: return
! 27: }
! 28:
! 29: #---------------------------------------------------------------------
! 30: # Test the pragma on an empty database.
! 31: #
! 32: do_test incrvacuum-1.1 {
! 33: execsql {
! 34: pragma auto_vacuum;
! 35: }
! 36: } $sqlite_options(default_autovacuum)
! 37: do_test incrvacuum-1.2.0 {
! 38: # File size is sometimes 1 instead of 0 due to the hack we put in
! 39: # to work around ticket #3260. Search for comments on #3260 in
! 40: # os_unix.c.
! 41: expr {[file size test.db] > 1}
! 42: } {0}
! 43: do_test incrvacuum-1.2 {
! 44: # This command will create the database.
! 45: execsql {
! 46: pragma auto_vacuum = 'full';
! 47: pragma auto_vacuum;
! 48: }
! 49: } {1}
! 50: do_test incrvacuum-1.2.1 {
! 51: expr {[file size test.db] > 0}
! 52: } {1}
! 53: do_test incrvacuum-1.3 {
! 54: execsql {
! 55: pragma auto_vacuum = 'incremental';
! 56: pragma auto_vacuum;
! 57: }
! 58: } {2}
! 59: do_test incrvacuum-1.4 {
! 60: # In this case the invalid value is ignored and the auto_vacuum
! 61: # setting remains unchanged.
! 62: execsql {
! 63: pragma auto_vacuum = 'invalid';
! 64: pragma auto_vacuum;
! 65: }
! 66: } {2}
! 67: do_test incrvacuum-1.5 {
! 68: execsql {
! 69: pragma auto_vacuum = 1;
! 70: pragma auto_vacuum;
! 71: }
! 72: } {1}
! 73: do_test incrvacuum-1.6 {
! 74: execsql {
! 75: pragma auto_vacuum = '2';
! 76: pragma auto_vacuum;
! 77: }
! 78: } {2}
! 79: do_test incrvacuum-1.7 {
! 80: # Invalid value. auto_vacuum setting remains unchanged.
! 81: execsql {
! 82: pragma auto_vacuum = 5;
! 83: pragma auto_vacuum;
! 84: }
! 85: } {2}
! 86:
! 87: #---------------------------------------------------------------------
! 88: # Test the pragma on a non-empty database. It is possible to toggle
! 89: # the connection between "full" and "incremental" mode, but not to
! 90: # change from either of these to "none", or from "none" to "full" or
! 91: # "incremental".
! 92: #
! 93: do_test incrvacuum-2.1 {
! 94: execsql {
! 95: pragma auto_vacuum = 1;
! 96: CREATE TABLE abc(a, b, c);
! 97: }
! 98: } {}
! 99: do_test incrvacuum-2.2 {
! 100: execsql {
! 101: pragma auto_vacuum = 'none';
! 102: pragma auto_vacuum;
! 103: }
! 104: } {1}
! 105: do_test incrvacuum-2.2.1 {
! 106: db close
! 107: sqlite3 db test.db
! 108: execsql {
! 109: pragma auto_vacuum;
! 110: }
! 111: } {1}
! 112: do_test incrvacuum-2.3 {
! 113: execsql {
! 114: pragma auto_vacuum = 'incremental';
! 115: pragma auto_vacuum;
! 116: }
! 117: } {2}
! 118: do_test incrvacuum-2.4 {
! 119: execsql {
! 120: pragma auto_vacuum = 'full';
! 121: pragma auto_vacuum;
! 122: }
! 123: } {1}
! 124:
! 125: #---------------------------------------------------------------------
! 126: # Test that when the auto_vacuum mode is "incremental", the database
! 127: # does not shrink when pages are removed from it. But it does if
! 128: # the mode is set to "full".
! 129: #
! 130: do_test incrvacuum-3.1 {
! 131: execsql {
! 132: pragma auto_vacuum;
! 133: }
! 134: } {1}
! 135: do_test incrvacuum-3.2 {
! 136: set ::str [string repeat 1234567890 110]
! 137: execsql {
! 138: PRAGMA auto_vacuum = 2;
! 139: BEGIN;
! 140: CREATE TABLE tbl2(str);
! 141: INSERT INTO tbl2 VALUES($::str);
! 142: COMMIT;
! 143: }
! 144: # 5 pages:
! 145: #
! 146: # 1 -> database header
! 147: # 2 -> first back-pointer page
! 148: # 3 -> table abc
! 149: # 4 -> table tbl2
! 150: # 5 -> table tbl2 overflow page.
! 151: #
! 152: expr {[file size test.db] / 1024}
! 153: } {5}
! 154: do_test incrvacuum-3.3 {
! 155: execsql {
! 156: DROP TABLE abc;
! 157: DELETE FROM tbl2;
! 158: }
! 159: expr {[file size test.db] / 1024}
! 160: } {5}
! 161: do_test incrvacuum-3.4 {
! 162: execsql {
! 163: PRAGMA auto_vacuum = 1;
! 164: INSERT INTO tbl2 VALUES('hello world');
! 165: }
! 166: expr {[file size test.db] / 1024}
! 167: } {3}
! 168:
! 169: #---------------------------------------------------------------------
! 170: # Try to run a very simple incremental vacuum. Also verify that
! 171: # PRAGMA incremental_vacuum is a harmless no-op against a database that
! 172: # does not support auto-vacuum.
! 173: #
! 174: do_test incrvacuum-4.1 {
! 175: set ::str [string repeat 1234567890 110]
! 176: execsql {
! 177: PRAGMA auto_vacuum = 2;
! 178: INSERT INTO tbl2 VALUES($::str);
! 179: CREATE TABLE tbl1(a, b, c);
! 180: }
! 181: expr {[file size test.db] / 1024}
! 182: } {5}
! 183: do_test incrvacuum-4.2 {
! 184: execsql {
! 185: DELETE FROM tbl2;
! 186: DROP TABLE tbl1;
! 187: }
! 188: expr {[file size test.db] / 1024}
! 189: } {5}
! 190: do_test incrvacuum-4.3 {
! 191: set ::nStep 0
! 192: db eval {pragma incremental_vacuum(10)} {
! 193: incr ::nStep
! 194: }
! 195: list [expr {[file size test.db] / 1024}] $::nStep
! 196: } {3 2}
! 197:
! 198: #---------------------------------------------------------------------
! 199: # The following tests - incrvacuum-5.* - test incremental vacuum
! 200: # from within a transaction.
! 201: #
! 202: do_test incrvacuum-5.1.1 {
! 203: expr {[file size test.db] / 1024}
! 204: } {3}
! 205: do_test incrvacuum-5.1.2 {
! 206: execsql {
! 207: BEGIN;
! 208: DROP TABLE tbl2;
! 209: PRAGMA incremental_vacuum;
! 210: COMMIT;
! 211: }
! 212: expr {[file size test.db] / 1024}
! 213: } {1}
! 214:
! 215: do_test incrvacuum-5.2.1 {
! 216: set ::str [string repeat abcdefghij 110]
! 217: execsql {
! 218: BEGIN;
! 219: CREATE TABLE tbl1(a);
! 220: INSERT INTO tbl1 VALUES($::str);
! 221: PRAGMA incremental_vacuum; -- this is a no-op.
! 222: COMMIT;
! 223: }
! 224: expr {[file size test.db] / 1024}
! 225: } {4}
! 226: do_test incrvacuum-5.2.2 {
! 227: set ::str [string repeat abcdefghij 110]
! 228: execsql {
! 229: BEGIN;
! 230: INSERT INTO tbl1 VALUES($::str);
! 231: INSERT INTO tbl1 SELECT * FROM tbl1;
! 232: DELETE FROM tbl1 WHERE oid%2; -- Put 2 overflow pages on free-list.
! 233: COMMIT;
! 234: }
! 235: expr {[file size test.db] / 1024}
! 236: } {7}
! 237: do_test incrvacuum-5.2.3 {
! 238: execsql {
! 239: BEGIN;
! 240: PRAGMA incremental_vacuum; -- Vacuum up the two pages.
! 241: CREATE TABLE tbl2(b); -- Use one free page as a table root.
! 242: INSERT INTO tbl2 VALUES('a nice string');
! 243: COMMIT;
! 244: }
! 245: expr {[file size test.db] / 1024}
! 246: } {6}
! 247: do_test incrvacuum-5.2.4 {
! 248: execsql {
! 249: SELECT * FROM tbl2;
! 250: }
! 251: } {{a nice string}}
! 252: do_test incrvacuum-5.2.5 {
! 253: execsql {
! 254: DROP TABLE tbl1;
! 255: DROP TABLE tbl2;
! 256: PRAGMA incremental_vacuum;
! 257: }
! 258: expr {[file size test.db] / 1024}
! 259: } {1}
! 260:
! 261:
! 262: # Test cases incrvacuum-5.3.* use the following list as input data.
! 263: # Two new databases are opened, one with incremental vacuum enabled,
! 264: # the other with no auto-vacuum completely disabled. After executing
! 265: # each element of the following list on both databases, test that
! 266: # the integrity-check passes and the contents of each are identical.
! 267: #
! 268: set TestScriptList [list {
! 269: BEGIN;
! 270: CREATE TABLE t1(a, b);
! 271: CREATE TABLE t2(a, b);
! 272: CREATE INDEX t1_i ON t1(a);
! 273: CREATE INDEX t2_i ON t2(a);
! 274: } {
! 275: INSERT INTO t1 VALUES($::str1, $::str2);
! 276: INSERT INTO t1 VALUES($::str1||$::str2, $::str2||$::str1);
! 277: INSERT INTO t2 SELECT b, a FROM t1;
! 278: INSERT INTO t2 SELECT a, b FROM t1;
! 279: INSERT INTO t1 SELECT b, a FROM t2;
! 280: UPDATE t2 SET b = '';
! 281: PRAGMA incremental_vacuum;
! 282: } {
! 283: UPDATE t2 SET b = (SELECT b FROM t1 WHERE t1.oid = t2.oid);
! 284: PRAGMA incremental_vacuum;
! 285: } {
! 286: CREATE TABLE t3(a, b);
! 287: INSERT INTO t3 SELECT * FROM t2;
! 288: DROP TABLE t2;
! 289: PRAGMA incremental_vacuum;
! 290: } {
! 291: CREATE INDEX t3_i ON t3(a);
! 292: COMMIT;
! 293: } {
! 294: BEGIN;
! 295: DROP INDEX t3_i;
! 296: PRAGMA incremental_vacuum;
! 297: INSERT INTO t3 VALUES('hello', 'world');
! 298: ROLLBACK;
! 299: } {
! 300: INSERT INTO t3 VALUES('hello', 'world');
! 301: }
! 302: ]
! 303:
! 304: # If this build omits subqueries, step 2 in the above list will not
! 305: # work. Replace it with "" in this case.
! 306: #
! 307: ifcapable !subquery { lset TestScriptList 2 "" }
! 308:
! 309: # Compare the contents of databases $A and $B.
! 310: #
! 311: proc compare_dbs {A B tname} {
! 312: set tbl_list [execsql {
! 313: SELECT tbl_name FROM sqlite_master WHERE type = 'table'
! 314: } $A]
! 315:
! 316: do_test ${tname}.1 [subst {
! 317: execsql {
! 318: SELECT tbl_name FROM sqlite_master WHERE type = 'table'
! 319: } $B
! 320: }] $tbl_list
! 321:
! 322: set tn 1
! 323: foreach tbl $tbl_list {
! 324: set control [execsql "SELECT * FROM $tbl" $A]
! 325: do_test ${tname}.[incr tn] [subst {
! 326: execsql "SELECT * FROM $tbl" $B
! 327: }] $control
! 328: }
! 329: }
! 330:
! 331: set ::str1 [string repeat abcdefghij 130]
! 332: set ::str2 [string repeat 1234567890 105]
! 333:
! 334: forcedelete test1.db test1.db-journal test2.db test2.db-journal
! 335: sqlite3 db1 test1.db
! 336: sqlite3 db2 test2.db
! 337: execsql { PRAGMA auto_vacuum = 'none' } db1
! 338: execsql { PRAGMA auto_vacuum = 'incremental' } db2
! 339:
! 340: set tn 1
! 341: foreach sql $::TestScriptList {
! 342: execsql $sql db1
! 343: execsql $sql db2
! 344:
! 345: compare_dbs db1 db2 incrvacuum-5.3.${tn}
! 346: do_test incrvacuum-5.3.${tn}.integrity1 {
! 347: execsql { PRAGMA integrity_check; } db1
! 348: } {ok}
! 349: do_test incrvacuum-5.3.${tn}.integrity2 {
! 350: execsql { PRAGMA integrity_check; } db2
! 351: } {ok}
! 352: incr tn
! 353: }
! 354: db1 close
! 355: db2 close
! 356: #
! 357: # End of test cases 5.3.*
! 358:
! 359: #---------------------------------------------------------------------
! 360: # The following tests - incrvacuum-6.* - test running incremental
! 361: # vacuum while another statement (a read) is being executed.
! 362: #
! 363: for {set jj 0} {$jj < 10} {incr jj} {
! 364: # Build some test data. Two tables are created in an empty
! 365: # database. tbl1 data is a contiguous block starting at page 5 (pages
! 366: # 3 and 4 are the table roots). tbl2 is a contiguous block starting
! 367: # right after tbl1.
! 368: #
! 369: # Then drop tbl1 so that when an incr vacuum is run the pages
! 370: # of tbl2 have to be moved to fill the gap.
! 371: #
! 372: do_test incrvacuum-6.${jj}.1 {
! 373: execsql {
! 374: DROP TABLE IF EXISTS tbl1;
! 375: DROP TABLE IF EXISTS tbl2;
! 376: PRAGMA incremental_vacuum;
! 377: CREATE TABLE tbl1(a, b);
! 378: CREATE TABLE tbl2(a, b);
! 379: BEGIN;
! 380: }
! 381: for {set ii 0} {$ii < 1000} {incr ii} {
! 382: db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}
! 383: }
! 384: execsql {
! 385: INSERT INTO tbl2 SELECT * FROM tbl1;
! 386: COMMIT;
! 387: DROP TABLE tbl1;
! 388: }
! 389: expr {[file size test.db] / 1024}
! 390: } {36}
! 391:
! 392: # Run a linear scan query on tbl2. After reading ($jj*100) rows,
! 393: # run the incremental vacuum to shrink the database.
! 394: #
! 395: do_test incrvacuum-6.${jj}.2 {
! 396: set ::nRow 0
! 397: db eval {SELECT a FROM tbl2} {} {
! 398: if {$a == [expr $jj*100]} {
! 399: db eval {PRAGMA incremental_vacuum}
! 400: }
! 401: incr ::nRow
! 402: }
! 403: list [expr {[file size test.db] / 1024}] $nRow
! 404: } {19 1000}
! 405: }
! 406:
! 407: #---------------------------------------------------------------------
! 408: # This test - incrvacuum-7.* - is to check that the database can be
! 409: # written in the middle of an incremental vacuum.
! 410: #
! 411: set ::iWrite 1
! 412: while 1 {
! 413: do_test incrvacuum-7.${::iWrite}.1 {
! 414: execsql {
! 415: DROP TABLE IF EXISTS tbl1;
! 416: DROP TABLE IF EXISTS tbl2;
! 417: PRAGMA incremental_vacuum;
! 418: CREATE TABLE tbl1(a, b);
! 419: CREATE TABLE tbl2(a, b);
! 420: BEGIN;
! 421: }
! 422: for {set ii 0} {$ii < 1000} {incr ii} {
! 423: db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}
! 424: }
! 425: execsql {
! 426: INSERT INTO tbl2 SELECT * FROM tbl1;
! 427: COMMIT;
! 428: DROP TABLE tbl1;
! 429: }
! 430: expr {[file size test.db] / 1024}
! 431: } {36}
! 432:
! 433: do_test incrvacuum-7.${::iWrite}.2 {
! 434: set ::nRow 0
! 435: db eval {PRAGMA incremental_vacuum} {
! 436: incr ::nRow
! 437: if {$::nRow == $::iWrite} {
! 438: db eval {
! 439: CREATE TABLE tbl1(a, b);
! 440: INSERT INTO tbl1 VALUES('hello', 'world');
! 441: }
! 442: }
! 443: }
! 444: list [expr {[file size test.db] / 1024}]
! 445: } {20}
! 446:
! 447: do_test incrvacuum-7.${::iWrite}.3 {
! 448: execsql {
! 449: SELECT * FROM tbl1;
! 450: }
! 451: } {hello world}
! 452:
! 453: if {$::nRow == $::iWrite} break
! 454: incr ::iWrite
! 455: }
! 456:
! 457: #---------------------------------------------------------------------
! 458: # This test - incrvacuum-8.* - is to check that nothing goes wrong
! 459: # with an incremental-vacuum if it is the first statement executed
! 460: # after an existing database is opened.
! 461: #
! 462: # At one point, this would always return SQLITE_SCHEMA (which
! 463: # causes an infinite loop in tclsqlite.c if using the Tcl interface).
! 464: #
! 465: do_test incrvacuum-8.1 {
! 466: db close
! 467: sqlite3 db test.db
! 468: execsql {
! 469: PRAGMA incremental_vacuum(50);
! 470: }
! 471: } {}
! 472:
! 473: #---------------------------------------------------------------------
! 474: # At one point this test case was causing an assert() to fail.
! 475: #
! 476: do_test incrvacuum-9.1 {
! 477: db close
! 478: forcedelete test.db test.db-journal
! 479: sqlite3 db test.db
! 480:
! 481: execsql {
! 482: PRAGMA auto_vacuum = 'incremental';
! 483: CREATE TABLE t1(a, b, c);
! 484: CREATE TABLE t2(a, b, c);
! 485: INSERT INTO t2 VALUES(randstr(500,500),randstr(500,500),randstr(500,500));
! 486: INSERT INTO t1 VALUES(1, 2, 3);
! 487: INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
! 488: INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
! 489: INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
! 490: INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
! 491: INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
! 492: INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
! 493: INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
! 494: INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
! 495: }
! 496: } {}
! 497:
! 498: do_test incrvacuum-9.2 {
! 499: execsql {
! 500: PRAGMA synchronous = 'OFF';
! 501: BEGIN;
! 502: UPDATE t1 SET a = a, b = b, c = c;
! 503: DROP TABLE t2;
! 504: PRAGMA incremental_vacuum(10);
! 505: ROLLBACK;
! 506: }
! 507: } {}
! 508:
! 509: do_test incrvacuum-9.3 {
! 510: execsql {
! 511: PRAGMA cache_size = 10;
! 512: BEGIN;
! 513: UPDATE t1 SET a = a, b = b, c = c;
! 514: DROP TABLE t2;
! 515: PRAGMA incremental_vacuum(10);
! 516: ROLLBACK;
! 517: }
! 518: } {}
! 519:
! 520: #---------------------------------------------------------------------
! 521: # Test that the parameter to the incremental_vacuum pragma works. That
! 522: # is, if the user executes "PRAGMA incremental_vacuum(N)", at most
! 523: # N pages are vacuumed.
! 524: #
! 525: do_test incrvacuum-10.1 {
! 526: execsql {
! 527: DROP TABLE t1;
! 528: DROP TABLE t2;
! 529: }
! 530: expr [file size test.db] / 1024
! 531: } {29}
! 532:
! 533: do_test incrvacuum-10.2 {
! 534: execsql {
! 535: PRAGMA incremental_vacuum(1);
! 536: }
! 537: expr [file size test.db] / 1024
! 538: } {28}
! 539:
! 540: do_test incrvacuum-10.3 {
! 541: execsql {
! 542: PRAGMA incremental_vacuum(5);
! 543: }
! 544: expr [file size test.db] / 1024
! 545: } {23}
! 546:
! 547: do_test incrvacuum-10.4 {
! 548: execsql {
! 549: PRAGMA incremental_vacuum('1');
! 550: }
! 551: expr [file size test.db] / 1024
! 552: } {22}
! 553:
! 554: do_test incrvacuum-10.5 {
! 555: execsql {
! 556: PRAGMA incremental_vacuum("+3");
! 557: }
! 558: expr [file size test.db] / 1024
! 559: } {19}
! 560:
! 561: do_test incrvacuum-10.6 {
! 562: execsql {
! 563: PRAGMA incremental_vacuum = 1;
! 564: }
! 565: expr [file size test.db] / 1024
! 566: } {18}
! 567:
! 568: do_test incrvacuum-10.7 {
! 569: # Use a really big number as an argument to incremetal_vacuum. Should
! 570: # be interpreted as "free all possible space".
! 571: execsql {
! 572: PRAGMA incremental_vacuum(2147483649);
! 573: }
! 574: expr [file size test.db] / 1024
! 575: } {1}
! 576:
! 577: do_test incrvacuum-10.8 {
! 578: execsql {
! 579: CREATE TABLE t1(x);
! 580: INSERT INTO t1 VALUES(hex(randomblob(1000)));
! 581: DROP TABLE t1;
! 582: }
! 583: # A negative number means free all possible space.
! 584: execsql {
! 585: PRAGMA incremental_vacuum=-1;
! 586: }
! 587: expr [file size test.db] / 1024
! 588: } {1}
! 589:
! 590: #----------------------------------------------------------------
! 591: # Test that if we set the auto_vacuum mode to 'incremental', then
! 592: # create a database, thereafter that database defaults to incremental
! 593: # vacuum mode.
! 594: #
! 595: db close
! 596: forcedelete test.db test.db-journal
! 597: sqlite3 db test.db
! 598:
! 599: ifcapable default_autovacuum {
! 600: do_test incrvacuum-11.1-av-dflt-on {
! 601: execsql {
! 602: PRAGMA auto_vacuum;
! 603: }
! 604: } $AUTOVACUUM
! 605: } else {
! 606: do_test incrvacuum-11.1-av-dflt-off {
! 607: execsql {
! 608: PRAGMA auto_vacuum;
! 609: }
! 610: } {0}
! 611: }
! 612: do_test incrvacuum-11.2 {
! 613: execsql {
! 614: PRAGMA auto_vacuum = incremental;
! 615: }
! 616: } {}
! 617: do_test incrvacuum-11.3 {
! 618: execsql {
! 619: PRAGMA auto_vacuum;
! 620: }
! 621: } {2}
! 622: do_test incrvacuum-11.4 {
! 623: # The database has now been created.
! 624: expr {[file size test.db]>0}
! 625: } {1}
! 626: do_test incrvacuum-11.5 {
! 627: # Close and reopen the connection.
! 628: db close
! 629: sqlite3 db test.db
! 630:
! 631: # Test we are still in incremental vacuum mode.
! 632: execsql { PRAGMA auto_vacuum; }
! 633: } {2}
! 634: do_test incrvacuum-11.6 {
! 635: execsql {
! 636: PRAGMA auto_vacuum = 'full';
! 637: PRAGMA auto_vacuum;
! 638: }
! 639: } {1}
! 640: do_test incrvacuum-11.7 {
! 641: # Close and reopen the connection.
! 642: db close
! 643: sqlite3 db test.db
! 644:
! 645: # Test we are still in "full" auto-vacuum mode.
! 646: execsql { PRAGMA auto_vacuum; }
! 647: } {1}
! 648:
! 649: #----------------------------------------------------------------------
! 650: # Special case: What happens if the database is locked when a "PRAGMA
! 651: # auto_vacuum = XXX" statement is executed.
! 652: #
! 653: db close
! 654: forcedelete test.db test.db-journal
! 655: sqlite3 db test.db
! 656:
! 657: do_test incrvacuum-12.1 {
! 658: execsql {
! 659: PRAGMA auto_vacuum = 1;
! 660: }
! 661: expr {[file size test.db]>0}
! 662: } {1}
! 663:
! 664: # Try to change the auto-vacuum from "full" to "incremental" while the
! 665: # database is locked. Nothing should change.
! 666: #
! 667: do_test incrvacuum-12.2 {
! 668: sqlite3 db2 test.db
! 669: execsql { BEGIN EXCLUSIVE; } db2
! 670: catchsql { PRAGMA auto_vacuum = 2; }
! 671: } {1 {database is locked}}
! 672:
! 673: do_test incrvacuum-12.3 {
! 674: execsql { ROLLBACK; } db2
! 675: execsql { PRAGMA auto_vacuum }
! 676: } {2} ;# Still 2 because PRAGMA auto_vacuum setting held in case of vacuum
! 677: do_test incrvacuum-12.4 {
! 678: db close
! 679: sqlite3 db test.db
! 680: execsql { PRAGMA auto_vacuum }
! 681: } {1} ;# Revert to 1 because the database file did not change
! 682:
! 683: do_test incrvacuum-12.5 {
! 684: execsql { SELECT * FROM sqlite_master }
! 685: execsql { PRAGMA auto_vacuum }
! 686: } {1}
! 687:
! 688: #----------------------------------------------------------------------
! 689: # Special case #2: What if one process prepares a "PRAGMA auto_vacuum = XXX"
! 690: # statement when the database is empty, but doesn't execute it until
! 691: # after some other process has created the database.
! 692: #
! 693: db2 close
! 694: db close
! 695: forcedelete test.db test.db-journal
! 696: sqlite3 db test.db ; set ::DB [sqlite3_connection_pointer db]
! 697: sqlite3 db2 test.db
! 698:
! 699: do_test incrvacuum-13.1 {
! 700: # File size is sometimes 1 instead of 0 due to the hack we put in
! 701: # to work around ticket #3260. Search for comments on #3260 in
! 702: # os_unix.c.
! 703: expr {[file size test.db]>1}
! 704: } {0}
! 705: do_test incrvacuum-13.2 {
! 706: set ::STMT [sqlite3_prepare $::DB {PRAGMA auto_vacuum = 2} -1 DUMMY]
! 707: execsql {
! 708: PRAGMA auto_vacuum = none;
! 709: PRAGMA default_cache_size = 1024;
! 710: PRAGMA auto_vacuum;
! 711: } db2
! 712: } {0}
! 713: do_test incrvacuum-13.3 {
! 714: expr {[file size test.db]>0}
! 715: } {1}
! 716: do_test incrvacuum-13.4 {
! 717: set rc [sqlite3_step $::STMT]
! 718: list $rc [sqlite3_finalize $::STMT]
! 719: } {SQLITE_DONE SQLITE_OK}
! 720: do_test incrvacuum-13.5 {
! 721: execsql {
! 722: PRAGMA auto_vacuum;
! 723: }
! 724: } {0}
! 725:
! 726:
! 727: # Verify that the incremental_vacuum pragma fails gracefully if it
! 728: # is used against an invalid database file.
! 729: #
! 730: if {[permutation] == ""} {
! 731: do_test incrvacuum-14.1 {
! 732: set out [open invalid.db w]
! 733: puts $out "This is not an SQLite database file"
! 734: close $out
! 735: sqlite3 db3 invalid.db
! 736: catchsql {
! 737: PRAGMA incremental_vacuum(10);
! 738: } db3
! 739: } {1 {file is encrypted or is not a database}}
! 740: db3 close
! 741: }
! 742:
! 743: do_test incrvacuum-15.1 {
! 744: db close
! 745: db2 close
! 746: forcedelete test.db
! 747: sqlite3 db test.db
! 748:
! 749: set str [string repeat "abcdefghij" 500]
! 750:
! 751: execsql {
! 752: PRAGMA cache_size = 10;
! 753: PRAGMA auto_vacuum = incremental;
! 754: CREATE TABLE t1(x, y);
! 755: INSERT INTO t1 VALUES('a', $str);
! 756: INSERT INTO t1 VALUES('b', $str);
! 757: INSERT INTO t1 VALUES('c', $str);
! 758: INSERT INTO t1 VALUES('d', $str);
! 759: INSERT INTO t1 VALUES('e', $str);
! 760: INSERT INTO t1 VALUES('f', $str);
! 761: INSERT INTO t1 VALUES('g', $str);
! 762: INSERT INTO t1 VALUES('h', $str);
! 763: INSERT INTO t1 VALUES('i', $str);
! 764: INSERT INTO t1 VALUES('j', $str);
! 765: INSERT INTO t1 VALUES('j', $str);
! 766:
! 767: CREATE TABLE t2(x PRIMARY KEY, y);
! 768: INSERT INTO t2 VALUES('a', $str);
! 769: INSERT INTO t2 VALUES('b', $str);
! 770: INSERT INTO t2 VALUES('c', $str);
! 771: INSERT INTO t2 VALUES('d', $str);
! 772:
! 773: BEGIN;
! 774: DELETE FROM t2;
! 775: PRAGMA incremental_vacuum;
! 776: }
! 777:
! 778: catchsql {INSERT INTO t2 SELECT * FROM t1}
! 779:
! 780: execsql {
! 781: COMMIT;
! 782: PRAGMA integrity_check;
! 783: }
! 784: } {ok}
! 785:
! 786: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to incrvacuum.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