Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the "PRAGMA wal_autocheckpoint" command. Rename "PRAGMA checkpoint" to "PRAGMA wal_checkpoint". |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | wal |
Files: | files | file ages | folders |
SHA1: |
714e5947264571386f966aa8fcdd5607 |
User & Date: | dan 2010-05-03 11:05:09.000 |
Context
2010-05-03
| ||
12:14 | Have sqlite3_wal_checkpoint() populate the database handle error message and error code (as returned by sqlite3_errmsg() and sqlite3_errcode()). (check-in: ff234cf574 user: dan tags: wal) | |
11:05 | Add the "PRAGMA wal_autocheckpoint" command. Rename "PRAGMA checkpoint" to "PRAGMA wal_checkpoint". (check-in: 714e594726 user: dan tags: wal) | |
08:19 | Merge two wal leaves. (check-in: 23c0e6c3f3 user: dan tags: wal) | |
Changes
Changes to src/main.c.
︙ | ︙ | |||
1187 1188 1189 1190 1191 1192 1193 | } #ifndef SQLITE_OMIT_WAL /* ** The sqlite3_wal_hook() callback registered by sqlite3_wal_autocheckpoint(). ** Return non-zero, indicating to the caller that a checkpoint should be run, ** if the number of frames in the log file is greater than | | | < > | 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 | } #ifndef SQLITE_OMIT_WAL /* ** The sqlite3_wal_hook() callback registered by sqlite3_wal_autocheckpoint(). ** Return non-zero, indicating to the caller that a checkpoint should be run, ** if the number of frames in the log file is greater than ** sqlite3.nAutoCheckpoint (the value configured by wal_autocheckpoint()). */ static int defaultWalHook(void *p, sqlite3 *db, const char *z, int nFrame){ UNUSED_PARAMETER(p); UNUSED_PARAMETER(z); return ( nFrame>=db->nAutoCheckpoint ); } /* ** Configure an sqlite3_wal_hook() callback to automatically checkpoint ** a database after committing a transaction if there are nFrame or ** more frames in the log file. Passing zero or a negative value as the ** nFrame parameter disables automatic checkpoints entirely. ** ** The callback registered by this function replaces any existing callback ** registered using sqlite3_wal_hook(). Likewise, registering a callback ** using sqlite3_wal_hook() disables the automatic checkpoint mechanism ** configured by this function. */ int sqlite3_wal_autocheckpoint(sqlite3 *db, int nFrame){ sqlite3_mutex_enter(db->mutex); if( nFrame>0 ){ sqlite3_wal_hook(db, defaultWalHook, 0); db->nAutoCheckpoint = nFrame; }else{ sqlite3_wal_hook(db, 0, 0); } sqlite3_mutex_leave(db->mutex); return SQLITE_OK; } |
︙ | ︙ | |||
1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 | void *pArg /* First argument passed to xCallback() */ ){ void *pRet; sqlite3_mutex_enter(db->mutex); pRet = db->pWalArg; db->xWalCallback = xCallback; db->pWalArg = pArg; sqlite3_mutex_leave(db->mutex); return pRet; } /* ** Checkpoint database zDb. If zDb is NULL, the main database is checkpointed. */ | > | 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 | void *pArg /* First argument passed to xCallback() */ ){ void *pRet; sqlite3_mutex_enter(db->mutex); pRet = db->pWalArg; db->xWalCallback = xCallback; db->pWalArg = pArg; db->nAutoCheckpoint = 0; sqlite3_mutex_leave(db->mutex); return pRet; } /* ** Checkpoint database zDb. If zDb is NULL, the main database is checkpointed. */ |
︙ | ︙ | |||
1866 1867 1868 1869 1870 1871 1872 | SQLITE_DEFAULT_LOCKING_MODE); #endif /* Enable the lookaside-malloc subsystem */ setupLookaside(db, 0, sqlite3GlobalConfig.szLookaside, sqlite3GlobalConfig.nLookaside); | | | 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 | SQLITE_DEFAULT_LOCKING_MODE); #endif /* Enable the lookaside-malloc subsystem */ setupLookaside(db, 0, sqlite3GlobalConfig.szLookaside, sqlite3GlobalConfig.nLookaside); sqlite3_wal_autocheckpoint(db, SQLITE_DEFAULT_WAL_AUTOCHECKPOINT); opendb_out: if( db ){ assert( db->mutex!=0 || isThreadsafe==0 || sqlite3GlobalConfig.bFullMutex==0 ); sqlite3_mutex_leave(db->mutex); } rc = sqlite3_errcode(db); |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1398 1399 1400 1401 1402 1403 1404 | sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1); } }else #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ #ifndef SQLITE_OMIT_WAL /* | | | > > > > > > > > > > > > > > > > > | 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 | sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1); } }else #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ #ifndef SQLITE_OMIT_WAL /* ** PRAGMA [database.]wal_checkpoint ** ** Checkpoint the database. */ if( sqlite3StrICmp(zLeft, "wal_checkpoint")==0 ){ if( sqlite3ReadSchema(pParse) ) goto pragma_out; sqlite3VdbeAddOp3(v, OP_Checkpoint, iDb, 0, 0); }else /* ** PRAGMA wal_autocheckpoint ** PRAGMA wal_autocheckpoint = N ** ** Configure a database connection to automatically checkpoint a database ** after accumulating N frames in the log. Or query for the current value ** of N. */ if( sqlite3StrICmp(zLeft, "wal_autocheckpoint")==0 ){ if( zRight ){ int nAuto = atoi(zRight); sqlite3_wal_autocheckpoint(db, nAuto); } returnSingleInt(pParse, "wal_autocheckpoint", db->nAutoCheckpoint); }else #endif #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) /* ** Report the current state of file logs for all databases */ if( sqlite3StrICmp(zLeft, "lock_status")==0 ){ |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
820 821 822 823 824 825 826 | void *pCommitArg; /* Argument to xCommitCallback() */ int (*xCommitCallback)(void*); /* Invoked at every commit. */ void *pRollbackArg; /* Argument to xRollbackCallback() */ void (*xRollbackCallback)(void*); /* Invoked at every commit. */ void *pUpdateArg; void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64); #ifndef SQLITE_OMIT_WAL | | | 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 | void *pCommitArg; /* Argument to xCommitCallback() */ int (*xCommitCallback)(void*); /* Invoked at every commit. */ void *pRollbackArg; /* Argument to xRollbackCallback() */ void (*xRollbackCallback)(void*); /* Invoked at every commit. */ void *pUpdateArg; void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64); #ifndef SQLITE_OMIT_WAL int nAutoCheckpoint; /* Value configured by wal_autocheckpoint() */ int (*xWalCallback)(void *, sqlite3 *, const char *, int); void *pWalArg; #endif void(*xCollNeeded)(void*,sqlite3*,int eTextRep,const char*); void(*xCollNeeded16)(void*,sqlite3*,int eTextRep,const void*); void *pCollNeededArg; sqlite3_value *pErr; /* Most recent error message */ |
︙ | ︙ |
Changes to src/sqliteLimit.h.
︙ | ︙ | |||
104 105 106 107 108 109 110 111 112 113 114 115 116 117 | #ifndef SQLITE_DEFAULT_CACHE_SIZE # define SQLITE_DEFAULT_CACHE_SIZE 2000 #endif #ifndef SQLITE_DEFAULT_TEMP_CACHE_SIZE # define SQLITE_DEFAULT_TEMP_CACHE_SIZE 500 #endif /* ** The maximum number of attached databases. This must be between 0 ** and 30. The upper bound on 30 is because a 32-bit integer bitmap ** is used internally to track attached databases. */ #ifndef SQLITE_MAX_ATTACHED # define SQLITE_MAX_ATTACHED 10 | > > > > > > > > | 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | #ifndef SQLITE_DEFAULT_CACHE_SIZE # define SQLITE_DEFAULT_CACHE_SIZE 2000 #endif #ifndef SQLITE_DEFAULT_TEMP_CACHE_SIZE # define SQLITE_DEFAULT_TEMP_CACHE_SIZE 500 #endif /* ** The default number of frames to accumulate in the log file before ** checkpointing the database in WAL mode. */ #ifndef SQLITE_DEFAULT_WAL_AUTOCHECKPOINT # define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 1000 #endif /* ** The maximum number of attached databases. This must be between 0 ** and 30. The upper bound on 30 is because a 32-bit integer bitmap ** is used internally to track attached databases. */ #ifndef SQLITE_MAX_ATTACHED # define SQLITE_MAX_ATTACHED 10 |
︙ | ︙ |
Changes to src/vdbeapi.c.
︙ | ︙ | |||
317 318 319 320 321 322 323 | for(i=0; i<db->nDb; i++){ Btree *pBt = db->aDb[i].pBt; if( pBt ){ int nEntry = sqlite3PagerWalCallback(sqlite3BtreePager(pBt)); if( db->xWalCallback && nEntry>0 && rc==SQLITE_OK && db->xWalCallback(db->pWalArg, db, db->aDb[i].zName, nEntry) ){ | | | 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 | for(i=0; i<db->nDb; i++){ Btree *pBt = db->aDb[i].pBt; if( pBt ){ int nEntry = sqlite3PagerWalCallback(sqlite3BtreePager(pBt)); if( db->xWalCallback && nEntry>0 && rc==SQLITE_OK && db->xWalCallback(db->pWalArg, db, db->aDb[i].zName, nEntry) ){ rc = sqlite3Checkpoint(db, i); } } } #endif return rc; } |
︙ | ︙ |
Changes to test/savepoint.test.
︙ | ︙ | |||
788 789 790 791 792 793 794 | CREATE TABLE t3(a, b, UNIQUE(a, b)); ROLLBACK TO one; } } {} integrity_check savepoint-11.7 do_test savepoint-11.8 { execsql { ROLLBACK } | | | 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 | CREATE TABLE t3(a, b, UNIQUE(a, b)); ROLLBACK TO one; } } {} integrity_check savepoint-11.7 do_test savepoint-11.8 { execsql { ROLLBACK } execsql { PRAGMA wal_checkpoint } file size test.db } {8192} do_test savepoint-11.9 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; |
︙ | ︙ |
Changes to test/wal.test.
︙ | ︙ | |||
345 346 347 348 349 350 351 | PRAGMA page_size = 1024; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); } list [file size test.db] [file size test.db-wal] } [list 1024 [log_file_size 3 1024]] do_test wal-7.2 { | | | 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 | PRAGMA page_size = 1024; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); } list [file size test.db] [file size test.db-wal] } [list 1024 [log_file_size 3 1024]] do_test wal-7.2 { execsql { PRAGMA wal_checkpoint } list [file size test.db] [file size test.db-wal] } [list 2048 [log_file_size 3 1024]] # Execute some transactions in auto-vacuum mode to test database file # truncation. # do_test wal-8.1 { |
︙ | ︙ | |||
376 377 378 379 380 381 382 | INSERT INTO t1 VALUES(blob(900)); INSERT INTO t1 VALUES(blob(900)); INSERT INTO t1 SELECT blob(900) FROM t1; /* 4 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 8 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 16 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 32 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 64 */ | | | | 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 | INSERT INTO t1 VALUES(blob(900)); INSERT INTO t1 VALUES(blob(900)); INSERT INTO t1 SELECT blob(900) FROM t1; /* 4 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 8 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 16 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 32 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 64 */ PRAGMA wal_checkpoint; } file size test.db } [expr 68*1024] do_test wal-8.3 { execsql { DELETE FROM t1 WHERE rowid<54; PRAGMA wal_checkpoint; } file size test.db } [expr 14*1024] # Run some "warm-body" tests to ensure that log-summary files with more # than 256 entries (log summaries that contain index blocks) work Ok. # |
︙ | ︙ | |||
422 423 424 425 426 427 428 | file copy test.db-wal test2.db-wal sqlite3_wal db3 test2.db execsql {PRAGMA integrity_check } db3 } {ok} db3 close do_test wal-9.4 { | | | 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 | file copy test.db-wal test2.db-wal sqlite3_wal db3 test2.db execsql {PRAGMA integrity_check } db3 } {ok} db3 close do_test wal-9.4 { execsql { PRAGMA wal_checkpoint } db2 close sqlite3_wal db2 test.db execsql {PRAGMA integrity_check } db2 } {ok} foreach handle {db db2 db3} { catch { $handle close } } unset handle |
︙ | ︙ | |||
545 546 547 548 549 550 551 | # Open a read transaction with [db2]. Check that this prevents [db] from # checkpointing the database. But not from writing to it. # do_test wal-10.$tn.11 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10} do_test wal-10.$tn.12 { | | | | | | 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 | # Open a read transaction with [db2]. Check that this prevents [db] from # checkpointing the database. But not from writing to it. # do_test wal-10.$tn.11 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10} do_test wal-10.$tn.12 { catchsql { PRAGMA wal_checkpoint } } {1 {database is locked}} do_test wal-10.$tn.13 { execsql { INSERT INTO t1 VALUES(11, 12) } sql2 {SELECT * FROM t1} } {1 2 3 4 5 6 7 8 9 10} # Connection [db2] is holding a lock on a snapshot, preventing [db] from # checkpointing the database. Add a busy-handler to [db]. If [db2] completes # its transaction from within the busy-handler, [db] is able to complete # the checkpoint operation. # proc busyhandler x { if {$x==4} { sql2 COMMIT } if {$x<5} { return 0 } return 1 } db busy busyhandler do_test wal-10.$tn.14 { execsql { PRAGMA wal_checkpoint } } {} # Similar to the test above. Except this time, a new read transaction is # started (db3) while the checkpointer is waiting for an old one (db2) to # finish. The checkpointer can finish, but any subsequent write operations # must wait until after db3 has closed the read transaction, as db3 is a # "region D" writer. # db busy {} do_test wal-10.$tn.15 { sql2 { BEGIN; SELECT * FROM t1; } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test wal-10.$tn.16 { catchsql { PRAGMA wal_checkpoint } } {1 {database is locked}} proc busyhandler x { if {$x==3} { sql3 { BEGIN; SELECT * FROM t1 } } if {$x==4} { sql2 COMMIT } if {$x<5} { return 0 } return 1 } db busy busyhandler do_test wal-10.$tn.17 { execsql { PRAGMA wal_checkpoint } } {} do_test wal-10.$tn.18 { sql3 { SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test wal-10.$tn.19 { catchsql { INSERT INTO t1 VALUES(13, 14) } } {1 {database is locked}} |
︙ | ︙ | |||
612 613 614 615 616 617 618 | } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} # Set [db3] up as a "region D" reader again. Then upgrade it to a writer # and back down to a reader. Then, check that a checkpoint is not possible # (as [db3] still has a snapshot locked). # do_test wal-10.$tn.23 { | | | | | | 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 | } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} # Set [db3] up as a "region D" reader again. Then upgrade it to a writer # and back down to a reader. Then, check that a checkpoint is not possible # (as [db3] still has a snapshot locked). # do_test wal-10.$tn.23 { execsql { PRAGMA wal_checkpoint } } {} do_test wal-10.$tn.24 { sql2 { BEGIN; SELECT * FROM t1; } } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} do_test wal-10.$tn.25 { execsql { PRAGMA wal_checkpoint } } {} do_test wal-10.$tn.26 { catchsql { INSERT INTO t1 VALUES(15, 16) } } {1 {database is locked}} do_test wal-10.$tn.27 { sql3 { INSERT INTO t1 VALUES(15, 16) } } {} do_test wal-10.$tn.28 { code3 { set ::STMT [sqlite3_prepare db3 "SELECT * FROM t1" -1 TAIL] sqlite3_step $::STMT } sql3 COMMIT execsql { SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} db busy {} do_test wal-10.$tn.29 { execsql { INSERT INTO t1 VALUES(17, 18) } catchsql { PRAGMA wal_checkpoint } } {1 {database is locked}} do_test wal-10.$tn.30 { code3 { sqlite3_finalize $::STMT } execsql { PRAGMA wal_checkpoint } } {} # At one point, if a reader failed to upgrade to a writer because it # was reading an old snapshot, the write-locks were not being released. # Test that this bug has been fixed. # do_test wal-10.$tn.31 { |
︙ | ︙ | |||
681 682 683 684 685 686 687 | BEGIN; SELECT * FROM t1; } } {a b c d} proc busyhandler x { return 1 } db busy busyhandler do_test wal-10.$tn.36 { | | | | 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 | BEGIN; SELECT * FROM t1; } } {a b c d} proc busyhandler x { return 1 } db busy busyhandler do_test wal-10.$tn.36 { catchsql { PRAGMA wal_checkpoint } } {1 {database is locked}} do_test wal-10.$tn.36 { sql3 { INSERT INTO t1 VALUES('e', 'f') } sql2 { SELECT * FROM t1 } } {a b c d} do_test wal-10.$tn.37 { sql2 COMMIT execsql { PRAGMA wal_checkpoint } } {} catch { db close } catch { code2 { db2 close } } catch { code3 { db3 close } } catch { close $::code2_chan } catch { close $::code3_chan } |
︙ | ︙ | |||
714 715 716 717 718 719 720 | PRAGMA cache_size = 10; PRAGMA page_size = 1024; CREATE TABLE t1(x PRIMARY KEY); } list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044] } {1 3} do_test wal-11.2 { | | | 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 | PRAGMA cache_size = 10; PRAGMA page_size = 1024; CREATE TABLE t1(x PRIMARY KEY); } list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044] } {1 3} do_test wal-11.2 { execsql { PRAGMA wal_checkpoint } list [expr [file size test.db]/1024] [file size test.db-wal] } [list 3 [log_file_size 3 1024]] do_test wal-11.3 { execsql { INSERT INTO t1 VALUES( blob(900) ) } list [expr [file size test.db]/1024] [file size test.db-wal] } [list 3 [log_file_size 4 1024]] |
︙ | ︙ | |||
749 750 751 752 753 754 755 | do_test wal-11.7 { execsql { SELECT count(*) FROM t1; PRAGMA integrity_check; } } {16 ok} do_test wal-11.8 { | | | 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 | do_test wal-11.7 { execsql { SELECT count(*) FROM t1; PRAGMA integrity_check; } } {16 ok} do_test wal-11.8 { execsql { PRAGMA wal_checkpoint } list [expr [file size test.db]/1024] [file size test.db-wal] } [list 37 [log_file_size 41 1024]] do_test wal-11.9 { db close list [expr [file size test.db]/1024] [log_deleted test.db-wal] } {37 1} sqlite3_wal db test.db |
︙ | ︙ | |||
825 826 827 828 829 830 831 | file copy -force test.db-wal test2.db-wal sqlite3_wal db2 test2.db execsql { SELECT * FROM t2 } db2 } {B 1} db2 close do_test wal-12.5 { execsql { | | | | | 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 | file copy -force test.db-wal test2.db-wal sqlite3_wal db2 test2.db execsql { SELECT * FROM t2 } db2 } {B 1} db2 close do_test wal-12.5 { execsql { PRAGMA wal_checkpoint; UPDATE t2 SET y = 2 WHERE x = 'B'; PRAGMA wal_checkpoint; UPDATE t1 SET y = 1 WHERE x = 'A'; PRAGMA wal_checkpoint; UPDATE t1 SET y = 0 WHERE x = 'A'; SELECT * FROM t2; } } {B 2} do_test wal-12.6 { file copy -force test.db test2.db file copy -force test.db-wal test2.db-wal |
︙ | ︙ | |||
951 952 953 954 955 956 957 | db2 eval { INSERT INTO t1 SELECT randomblob(10), randomblob(100); INSERT INTO t1 SELECT randomblob(10), randomblob(100); INSERT INTO t1 SELECT randomblob(10), randomblob(100); INSERT INTO t1 SELECT randomblob(10), randomblob(100); } | | | | 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 | db2 eval { INSERT INTO t1 SELECT randomblob(10), randomblob(100); INSERT INTO t1 SELECT randomblob(10), randomblob(100); INSERT INTO t1 SELECT randomblob(10), randomblob(100); INSERT INTO t1 SELECT randomblob(10), randomblob(100); } # After executing the "PRAGMA wal_checkpoint", connection [db] was being # left with an inconsistent cache. Running the CREATE INDEX statement # in this state led to database corruption. catchsql { PRAGMA wal_checkpoint; CREATE INDEX i1 on t1(b); } db2 eval { PRAGMA integrity_check } } {ok} catch { db close } catch { db2 close } finish_test |
Changes to test/walbak.test.
︙ | ︙ | |||
67 68 69 70 71 72 73 | PRAGMA main.journal_mode; } } {wal} do_test walbak-1.5 { list [file size test.db] [file size test.db-wal] } [list 1024 [log_file_size 6 1024]] do_test walbak-1.6 { | | | | 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | PRAGMA main.journal_mode; } } {wal} do_test walbak-1.5 { list [file size test.db] [file size test.db-wal] } [list 1024 [log_file_size 6 1024]] do_test walbak-1.6 { execsql { PRAGMA wal_checkpoint } list [file size test.db] [file size test.db-wal] } [list [expr 3*1024] [log_file_size 6 1024]] do_test walbak-1.7 { execsql { CREATE TABLE t2(a, b); INSERT INTO t2 SELECT * FROM t1; DROP TABLE t1; } list [file size test.db] [file size test.db-wal] } [list [expr 3*1024] [log_file_size 6 1024]] do_test walbak-1.8 { execsql { VACUUM } list [file size test.db] [file size test.db-wal] } [list [expr 3*1024] [log_file_size 8 1024]] do_test walbak-1.9 { execsql { PRAGMA wal_checkpoint } list [file size test.db] [file size test.db-wal] } [list [expr 2*1024] [log_file_size 8 1024]] #------------------------------------------------------------------------- # Backups when the source db is modified mid-backup. # proc sig {{db db}} { |
︙ | ︙ |
Changes to test/walcrash.test.
︙ | ︙ | |||
188 189 190 191 192 193 194 | INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 12 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 16 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 20 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 24 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 28 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 32 */ | | | 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 | INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 12 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 16 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 20 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 24 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 28 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 32 */ PRAGMA wal_checkpoint; INSERT INTO t1 VALUES(randomblob(900)); INSERT INTO t1 VALUES(randomblob(900)); INSERT INTO t1 VALUES(randomblob(900)); } } {1 {child process exited abnormally}} do_test walcrash-5.$i.2 { |
︙ | ︙ | |||
229 230 231 232 233 234 235 | INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 12 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 16 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 20 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 24 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 28 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 32 */ | | | 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 | INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 12 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 16 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 20 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 24 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 28 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 32 */ PRAGMA wal_checkpoint; INSERT INTO t1 VALUES(randomblob(900)); INSERT INTO t1 VALUES(randomblob(900)); INSERT INTO t1 VALUES(randomblob(900)); } } {1 {child process exited abnormally}} do_test walcrash-6.$i.2 { |
︙ | ︙ | |||
256 257 258 259 260 261 262 | do_test walcrash-7.$i.1 { crashsql -delay 3 -file test.db -seed [incr seed] -blocksize 512 { PRAGMA journal_mode = wal; BEGIN; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); COMMIT; | | | | 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 | do_test walcrash-7.$i.1 { crashsql -delay 3 -file test.db -seed [incr seed] -blocksize 512 { PRAGMA journal_mode = wal; BEGIN; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); COMMIT; PRAGMA wal_checkpoint; CREATE INDEX i1 ON t1(a); PRAGMA wal_checkpoint; } } {1 {child process exited abnormally}} do_test walcrash-7.$i.2 { sqlite3 db test.db execsql { SELECT b FROM t1 WHERE a = 1 } } {2} |
︙ | ︙ |
Changes to test/walhook.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 15 16 17 18 19 20 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the operation of the library in # "PRAGMA journal_mode=WAL" mode. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !wal {finish_test ; return } | > > > > | | < < < < < < < > > > > > | > > < | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the operation of the library in # "PRAGMA journal_mode=WAL" mode. # # More specifically, this file contains regression tests for the # sqlite3_wal_hook() mechanism, including the sqlite3_wal_autocheckpoint() # and "PRAGMA wal_autocheckpoint" convenience interfaces. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !wal {finish_test ; return } proc log_file_size {nFrame pgsz} { expr {12 + ($pgsz+16)*$nFrame} } set ::wal_hook [list] proc wal_hook {zDb nEntry} { lappend ::wal_hook $zDb $nEntry return 0 } db wal_hook wal_hook do_test walhook-1.1 { execsql { PRAGMA page_size = 1024; PRAGMA journal_mode = wal; PRAGMA synchronous = normal; CREATE TABLE t1(i PRIMARY KEY, j); } set ::wal_hook } {main 3} do_test walhook-1.2 { set ::wal_hook [list] execsql { INSERT INTO t1 VALUES(1, 'one') } set ::wal_hook } {main 5} do_test walhook-1.3 { proc wal_hook {args} { return 1 } execsql { INSERT INTO t1 VALUES(2, 'two') } file size test.db } [expr 3*1024] do_test walhook-1.4 { proc wal_hook {zDb nEntry} { execsql { PRAGMA wal_checkpoint } return 0 } execsql { CREATE TABLE t2(a, b) } file size test.db } [expr 4*1024] do_test walhook-1.5 { sqlite3 db2 test.db proc wal_hook {zDb nEntry} { execsql { PRAGMA wal_checkpoint } db2 return 0 } execsql { CREATE TABLE t3(a PRIMARY KEY, b) } file size test.db } [expr 6*1024] db2 close db close sqlite3 db test.db do_test walhook-2.1 { execsql { PRAGMA synchronous = NORMAL } execsql { PRAGMA wal_autocheckpoint } } {1000} do_test walhook-2.2 { execsql { PRAGMA wal_autocheckpoint = 10} } {10} do_test walhook-2.3 { execsql { PRAGMA wal_autocheckpoint } } {10} # # The database connection is configured with "PRAGMA wal_autocheckpoint = 10". # Check that transactions are written to the log file until it contains at # least 10 frames, then the database is checkpointed. Subsequent transactions # are written into the start of the log file. # foreach {tn sql dbpages logpages} { 4 "CREATE TABLE t4(x PRIMARY KEY, y)" 6 3 5 "INSERT INTO t4 VALUES(1, 'one')" 6 5 6 "INSERT INTO t4 VALUES(2, 'two')" 6 7 7 "INSERT INTO t4 VALUES(3, 'three')" 6 9 8 "INSERT INTO t4 VALUES(4, 'four')" 8 11 9 "INSERT INTO t4 VALUES(5, 'five')" 8 11 } { do_test walhook-2.$tn { execsql $sql list [file size test.db] [file size test.db-wal] } [list [expr $dbpages*1024] [log_file_size $logpages 1024]] } catch { db2 close } catch { db close } finish_test |
Changes to test/walslow.test.
︙ | ︙ | |||
44 45 46 47 48 49 50 | set w [expr int(rand()*2000)] set x [expr int(rand()*2000)] execsql { INSERT INTO t1 VALUES(randomblob($w), randomblob($x)) } execsql { PRAGMA integrity_check } } {ok} do_test walslow-1.seed=$seed.$iTest.2 { | | | 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | set w [expr int(rand()*2000)] set x [expr int(rand()*2000)] execsql { INSERT INTO t1 VALUES(randomblob($w), randomblob($x)) } execsql { PRAGMA integrity_check } } {ok} do_test walslow-1.seed=$seed.$iTest.2 { execsql "PRAGMA wal_checkpoint;" execsql { PRAGMA integrity_check } } {ok} do_test walslow-1.seed=$seed.$iTest.3 { file delete -force testX.db testX.db-wal file copy test.db testX.db file copy test.db-wal testX.db-wal |
︙ | ︙ |
Changes to test/walthread.test.
︙ | ︙ | |||
237 238 239 240 241 242 243 | # rows in the table. # # Each of the N threads runs N read transactions followed by a single write # transaction in a loop as fast as possible. # # There is also a single checkpointer thread. It runs the following loop: # | | | 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 | # rows in the table. # # Each of the N threads runs N read transactions followed by a single write # transaction in a loop as fast as possible. # # There is also a single checkpointer thread. It runs the following loop: # # 1) Execute "PRAGMA wal_checkpoint" # 2) Sleep for 500 ms. # do_thread_test2 walthread-1 -seconds $seconds(walthread-1) -init { execsql { PRAGMA journal_mode = WAL; CREATE TABLE t1(x PRIMARY KEY); PRAGMA lock_status; |
︙ | ︙ | |||
291 292 293 294 295 296 297 | incr nRun } set nRun } -thread ckpt 1 { set nRun 0 while {[tt_continue]} { | | | 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 | incr nRun } set nRun } -thread ckpt 1 { set nRun 0 while {[tt_continue]} { db eval "PRAGMA wal_checkpoint" usleep 500 incr nRun } set nRun } #-------------------------------------------------------------------------- |
︙ | ︙ |