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