Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Invalidate sqlite3_blob* handles whenever an SQL statement is used to delete or modify the rows containing the open blob. Previously, modifying the table containing the open blob in any way invalidated the handle. This was too restrictive. (CVS 5199) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e339c91f8718482ce74fc53781091db9 |
User & Date: | danielk1977 2008-06-10 17:30:26.000 |
Context
2008-06-10
| ||
17:41 | A completely new design for the sqlite3_initialize() and sqlite3_shutdown() interfaces. (CVS 5200) (check-in: 7dfcd73dc8 user: drh tags: trunk) | |
17:30 | Invalidate sqlite3_blob* handles whenever an SQL statement is used to delete or modify the rows containing the open blob. Previously, modifying the table containing the open blob in any way invalidated the handle. This was too restrictive. (CVS 5199) (check-in: e339c91f87 user: danielk1977 tags: trunk) | |
2008-06-09
| ||
21:57 | Initial attempt at defining the sqlite3_initialize() and sqlite3_shutdown() interfaces. (CVS 5198) (check-in: 220bfd1f5c user: drh tags: trunk) | |
Changes
Changes to src/btree.c.
1 2 3 4 5 6 7 8 9 10 11 | /* ** 2004 April 6 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | /* ** 2004 April 6 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** $Id: btree.c,v 1.461 2008/06/10 17:30:26 danielk1977 Exp $ ** ** This file implements a external (disk-based) database using BTrees. ** See the header comment on "btreeInt.h" for additional information. ** Including a description of file format and an overview of operation. */ #include "btreeInt.h" |
︙ | ︙ | |||
64 65 66 67 68 69 70 | } #endif /* ** Forward declaration */ | | | 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | } #endif /* ** Forward declaration */ static int checkReadLocks(Btree*, Pgno, BtCursor*, i64); #ifdef SQLITE_OMIT_SHARED_CACHE /* ** The functions queryTableLock(), lockTable() and unlockAllTables() ** manipulate entries in the BtShared.pLock linked list used to store ** shared-cache table level locks. If the library is compiled with the |
︙ | ︙ | |||
369 370 371 372 373 374 375 | int sqlite3BtreeRestoreOrClearCursorPosition(BtCursor *pCur){ int rc; assert( cursorHoldsMutex(pCur) ); assert( pCur->eState>=CURSOR_REQUIRESEEK ); if( pCur->eState==CURSOR_FAULT ){ return pCur->skip; } | < < < < < | 369 370 371 372 373 374 375 376 377 378 379 380 381 382 | int sqlite3BtreeRestoreOrClearCursorPosition(BtCursor *pCur){ int rc; assert( cursorHoldsMutex(pCur) ); assert( pCur->eState>=CURSOR_REQUIRESEEK ); if( pCur->eState==CURSOR_FAULT ){ return pCur->skip; } pCur->eState = CURSOR_INVALID; rc = sqlite3BtreeMoveto(pCur, pCur->pKey, 0, pCur->nKey, 0, &pCur->skip); if( rc==SQLITE_OK ){ sqlite3_free(pCur->pKey); pCur->pKey = 0; assert( pCur->eState==CURSOR_VALID || pCur->eState==CURSOR_INVALID ); } |
︙ | ︙ | |||
2713 2714 2715 2716 2717 2718 2719 | BtShared *pBt = p->pBt; assert( sqlite3BtreeHoldsMutex(p) ); if( wrFlag ){ if( pBt->readOnly ){ return SQLITE_READONLY; } | | | 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 | BtShared *pBt = p->pBt; assert( sqlite3BtreeHoldsMutex(p) ); if( wrFlag ){ if( pBt->readOnly ){ return SQLITE_READONLY; } if( checkReadLocks(p, iTable, 0, 0) ){ return SQLITE_LOCKED; } } if( pBt->pPage1==0 ){ rc = lockBtreeWithRetry(p); if( rc!=SQLITE_OK ){ |
︙ | ︙ | |||
3249 3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 | ** ** Return SQLITE_OK on success or an error code if anything goes ** wrong. An error is returned if "offset+amt" is larger than ** the available payload. */ int sqlite3BtreeData(BtCursor *pCur, u32 offset, u32 amt, void *pBuf){ int rc; assert( cursorHoldsMutex(pCur) ); rc = restoreOrClearCursorPosition(pCur); if( rc==SQLITE_OK ){ assert( pCur->eState==CURSOR_VALID ); assert( pCur->pPage!=0 ); assert( pCur->idx>=0 && pCur->idx<pCur->pPage->nCell ); | > > > > > > | 3244 3245 3246 3247 3248 3249 3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 | ** ** Return SQLITE_OK on success or an error code if anything goes ** wrong. An error is returned if "offset+amt" is larger than ** the available payload. */ int sqlite3BtreeData(BtCursor *pCur, u32 offset, u32 amt, void *pBuf){ int rc; #ifndef SQLITE_OMIT_INCRBLOB if ( pCur->eState==CURSOR_INVALID ){ return SQLITE_ABORT; } #endif assert( cursorHoldsMutex(pCur) ); rc = restoreOrClearCursorPosition(pCur); if( rc==SQLITE_OK ){ assert( pCur->eState==CURSOR_VALID ); assert( pCur->pPage!=0 ); assert( pCur->idx>=0 && pCur->idx<pCur->pPage->nCell ); |
︙ | ︙ | |||
5590 5591 5592 5593 5594 5595 5596 | ** This routine checks all cursors that point to table pgnoRoot. ** If any of those cursors were opened with wrFlag==0 in a different ** database connection (a database connection that shares the pager ** cache with the current connection) and that other connection ** is not in the ReadUncommmitted state, then this routine returns ** SQLITE_LOCKED. ** | < | > | > | < | > | > > > > > > > > > > > > > > > | > > > > > < > > > > > > > > > | > > > > < < | 5591 5592 5593 5594 5595 5596 5597 5598 5599 5600 5601 5602 5603 5604 5605 5606 5607 5608 5609 5610 5611 5612 5613 5614 5615 5616 5617 5618 5619 5620 5621 5622 5623 5624 5625 5626 5627 5628 5629 5630 5631 5632 5633 5634 5635 5636 5637 5638 5639 5640 5641 5642 5643 5644 5645 5646 5647 5648 5649 5650 5651 5652 5653 5654 5655 5656 5657 5658 5659 5660 | ** This routine checks all cursors that point to table pgnoRoot. ** If any of those cursors were opened with wrFlag==0 in a different ** database connection (a database connection that shares the pager ** cache with the current connection) and that other connection ** is not in the ReadUncommmitted state, then this routine returns ** SQLITE_LOCKED. ** ** As well as cursors with wrFlag==0, cursors with wrFlag==1 and ** isIncrblobHandle==1 are also considered 'read' cursors. Incremental ** blob cursors are used for both reading and writing. ** ** When pgnoRoot is the root page of an intkey table, this function is also ** responsible for invalidating incremental blob cursors when the table row ** on which they are opened is deleted or modified. Cursors are invalidated ** according to the following rules: ** ** 1) When BtreeClearTable() is called to completely delete the contents ** of a B-Tree table, pExclude is set to zero and parameter iRow is ** set to non-zero. In this case all incremental blob cursors open ** on the table rooted at pgnoRoot are invalidated. ** ** 2) When BtreeInsert(), BtreeDelete() or BtreePutData() is called to ** modify a table row via an SQL statement, pExclude is set to the ** write cursor used to do the modification and parameter iRow is set ** to the integer row id of the B-Tree entry being modified. Unless ** pExclude is itself an incremental blob cursor, then all incremental ** blob cursors open on row iRow of the B-Tree are invalidated. ** ** 3) If both pExclude and iRow are set to zero, no incremental blob ** cursors are invalidated. */ static int checkReadLocks( Btree *pBtree, Pgno pgnoRoot, BtCursor *pExclude, i64 iRow ){ BtCursor *p; BtShared *pBt = pBtree->pBt; sqlite3 *db = pBtree->db; assert( sqlite3BtreeHoldsMutex(pBtree) ); for(p=pBt->pCursor; p; p=p->pNext){ if( p==pExclude ) continue; if( p->pgnoRoot!=pgnoRoot ) continue; #ifndef SQLITE_OMIT_INCRBLOB if( p->isIncrblobHandle && ( (!pExclude && iRow) || (pExclude && !pExclude->isIncrblobHandle && p->info.nKey==iRow) )){ p->eState = CURSOR_INVALID; } #endif if( p->eState!=CURSOR_VALID ) continue; if( p->wrFlag==0 #ifndef SQLITE_OMIT_INCRBLOB || p->isIncrblobHandle #endif ){ sqlite3 *dbOther = p->pBtree->db; if( dbOther==0 || (dbOther!=db && (dbOther->flags & SQLITE_ReadUncommitted)==0) ){ return SQLITE_LOCKED; } } } return SQLITE_OK; } /* ** Make sure pBt->pTmpSpace points to an allocation of |
︙ | ︙ | |||
5665 5666 5667 5668 5669 5670 5671 | rc = pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR; return rc; } assert( !pBt->readOnly ); if( !pCur->wrFlag ){ return SQLITE_PERM; /* Cursor not open for writing */ } | | | 5697 5698 5699 5700 5701 5702 5703 5704 5705 5706 5707 5708 5709 5710 5711 | rc = pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR; return rc; } assert( !pBt->readOnly ); if( !pCur->wrFlag ){ return SQLITE_PERM; /* Cursor not open for writing */ } if( checkReadLocks(pCur->pBtree, pCur->pgnoRoot, pCur, nKey) ){ return SQLITE_LOCKED; /* The table pCur points to has a read lock */ } if( pCur->eState==CURSOR_FAULT ){ return pCur->skip; } /* Save the positions of any other cursors open on this table */ |
︙ | ︙ | |||
5759 5760 5761 5762 5763 5764 5765 | } if( pCur->idx >= pPage->nCell ){ return SQLITE_ERROR; /* The cursor is not pointing to anything */ } if( !pCur->wrFlag ){ return SQLITE_PERM; /* Did not open this cursor for writing */ } | | | 5791 5792 5793 5794 5795 5796 5797 5798 5799 5800 5801 5802 5803 5804 5805 | } if( pCur->idx >= pPage->nCell ){ return SQLITE_ERROR; /* The cursor is not pointing to anything */ } if( !pCur->wrFlag ){ return SQLITE_PERM; /* Did not open this cursor for writing */ } if( checkReadLocks(pCur->pBtree, pCur->pgnoRoot, pCur, pCur->info.nKey) ){ return SQLITE_LOCKED; /* The table pCur points to has a read lock */ } /* Restore the current cursor position (a no-op if the cursor is not in ** CURSOR_REQUIRESEEK state) and save the positions of any other cursors ** open on the same table. Then call sqlite3PagerWrite() on the page ** that the entry will be deleted from. |
︙ | ︙ | |||
6058 6059 6060 6061 6062 6063 6064 | int sqlite3BtreeClearTable(Btree *p, int iTable){ int rc; BtShared *pBt = p->pBt; sqlite3BtreeEnter(p); pBt->db = p->db; if( p->inTrans!=TRANS_WRITE ){ rc = pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR; | | | 6090 6091 6092 6093 6094 6095 6096 6097 6098 6099 6100 6101 6102 6103 6104 | int sqlite3BtreeClearTable(Btree *p, int iTable){ int rc; BtShared *pBt = p->pBt; sqlite3BtreeEnter(p); pBt->db = p->db; if( p->inTrans!=TRANS_WRITE ){ rc = pBt->readOnly ? SQLITE_READONLY : SQLITE_ERROR; }else if( (rc = checkReadLocks(p, iTable, 0, 1))!=SQLITE_OK ){ /* nothing to do */ }else if( SQLITE_OK!=(rc = saveAllCursors(pBt, iTable, 0)) ){ /* nothing to do */ }else{ rc = clearDatabasePage(pBt, (Pgno)iTable, 0, 0); } sqlite3BtreeLeave(p); |
︙ | ︙ | |||
7108 7109 7110 7111 7112 7113 7114 | ** Only the data content may only be modified, it is not possible ** to change the length of the data stored. */ int sqlite3BtreePutData(BtCursor *pCsr, u32 offset, u32 amt, void *z){ assert( cursorHoldsMutex(pCsr) ); assert( sqlite3_mutex_held(pCsr->pBtree->db->mutex) ); assert(pCsr->isIncrblobHandle); | > > | | < < | < | | 7140 7141 7142 7143 7144 7145 7146 7147 7148 7149 7150 7151 7152 7153 7154 7155 7156 7157 7158 7159 7160 7161 7162 7163 7164 7165 7166 7167 7168 7169 7170 7171 | ** Only the data content may only be modified, it is not possible ** to change the length of the data stored. */ int sqlite3BtreePutData(BtCursor *pCsr, u32 offset, u32 amt, void *z){ assert( cursorHoldsMutex(pCsr) ); assert( sqlite3_mutex_held(pCsr->pBtree->db->mutex) ); assert(pCsr->isIncrblobHandle); restoreOrClearCursorPosition(pCsr); assert( pCsr->eState!=CURSOR_REQUIRESEEK ); if( pCsr->eState!=CURSOR_VALID ){ return SQLITE_ABORT; } /* Check some preconditions: ** (a) the cursor is open for writing, ** (b) there is no read-lock on the table being modified and ** (c) the cursor points at a valid row of an intKey table. */ if( !pCsr->wrFlag ){ return SQLITE_READONLY; } assert( !pCsr->pBt->readOnly && pCsr->pBt->inTransaction==TRANS_WRITE ); if( checkReadLocks(pCsr->pBtree, pCsr->pgnoRoot, pCsr, 0) ){ return SQLITE_LOCKED; /* The table pCur points to has a read lock */ } if( pCsr->eState==CURSOR_INVALID || !pCsr->pPage->intKey ){ return SQLITE_ERROR; } return accessPayload(pCsr, offset, amt, (unsigned char *)z, 0, 1); |
︙ | ︙ |
Changes to test/incrblob2.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # # Test that it is possible to have two open blob handles on a single # blob object. # | | | | | > | 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 | # May you share freely, never taking more than you give. # #*********************************************************************** # # Test that it is possible to have two open blob handles on a single # blob object. # # $Id: incrblob2.test,v 1.2 2008/06/10 17:30:26 danielk1977 Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable {!autovacuum || !pragma || !incrblob} { finish_test return } do_test incrblob2-1.0 { execsql { CREATE TABLE blobs(id INTEGER PRIMARY KEY, data BLOB); INSERT INTO blobs VALUES(NULL, zeroblob(5000)); INSERT INTO blobs VALUES(NULL, zeroblob(5000)); INSERT INTO blobs VALUES(NULL, zeroblob(5000)); INSERT INTO blobs VALUES(NULL, zeroblob(5000)); } } {} foreach iOffset [list 0 256 4094] { do_test incrblob2-1.$iOffset.1 { set fd [db incrblob blobs data 1] puts $fd "[string repeat x $iOffset]SQLite version 3.6.0" |
︙ | ︙ | |||
85 86 87 88 89 90 91 | #-------------------------------------------------------------------------- foreach iOffset [list 0 256 4094] { do_test incrblob2-2.$iOffset.1 { set fd1 [db incrblob blobs data 1] | | | | 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | #-------------------------------------------------------------------------- foreach iOffset [list 0 256 4094] { do_test incrblob2-2.$iOffset.1 { set fd1 [db incrblob blobs data 1] seek $fd1 [expr $iOffset - 5000] end fconfigure $fd1 -buffering none set fd2 [db incrblob blobs data 1] seek $fd2 [expr $iOffset - 5000] end fconfigure $fd2 -buffering none puts -nonewline $fd1 "123456" } {} do_test incrblob2-2.$iOffset.2 { read $fd2 6 |
︙ | ︙ | |||
111 112 113 114 115 116 117 | do_test incrblob2-3.1 { set fd1 [db incrblob blobs data 1] fconfigure $fd1 -buffering none } {} do_test incrblob2-3.2 { execsql { | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 | do_test incrblob2-3.1 { set fd1 [db incrblob blobs data 1] fconfigure $fd1 -buffering none } {} do_test incrblob2-3.2 { execsql { INSERT INTO blobs VALUES(5, zeroblob(10240)); } } {} do_test incrblob2-3.3 { set rc [catch { read $fd1 6 } msg] list $rc $msg } {0 123456} do_test incrblob2-3.4 { close $fd1 } {} #-------------------------------------------------------------------------- # The following tests - incrblob2-4.* - test that blob handles are # invalidated at the correct times. # do_test incrblob2-4.1 { db eval BEGIN db eval { CREATE TABLE t1(id INTEGER PRIMARY KEY, data BLOB); } for {set ii 1} {$ii < 100} {incr ii} { set data [string repeat "blob$ii" 500] db eval { INSERT INTO t1 VALUES($ii, $data) } } db eval COMMIT } {} proc aborted_handles {} { global handles set aborted {} for {set ii 1} {$ii < 100} {incr ii} { set str "blob$ii" set nByte [string length $str] set iOffset [expr $nByte * $ii * 2] set rc [catch {sqlite3_blob_read $handles($ii) $iOffset $nByte} msg] if {$rc && $msg eq "SQLITE_ABORT"} { lappend aborted $ii } else { if {$rc || $msg ne $str} { error "blob $ii: $msg" } } } set aborted } do_test incrblob2-4.2 { for {set ii 1} {$ii < 100} {incr ii} { set handles($ii) [db incrblob t1 data $ii] } aborted_handles } {} # Update row 3. This should abort handle 3 but leave all others untouched. # do_test incrblob2-4.3 { db eval {UPDATE t1 SET data = data || '' WHERE id = 3} aborted_handles } {3} # Test that a write to handle 3 also returns SQLITE_ABORT. # do_test incrblob2-4.3.1 { set rc [catch {sqlite3_blob_write $::handles(3) 10 HELLO} msg] list $rc $msg } {1 SQLITE_ABORT} # Delete row 14. This should abort handle 6 but leave all others untouched. # do_test incrblob2-4.4 { db eval {DELETE FROM t1 WHERE id = 14} aborted_handles } {3 14} # Change the rowid of row 15 to 102. Should abort handle 15. # do_test incrblob2-4.5 { db eval {UPDATE t1 SET id = 102 WHERE id = 15} aborted_handles } {3 14 15} # Clobber row 92 using INSERT OR REPLACE. # do_test incrblob2-4.6 { db eval {INSERT OR REPLACE INTO t1 VALUES(92, zeroblob(1000))} aborted_handles } {3 14 15 92} # Clobber row 65 using UPDATE OR REPLACE on row 35. This should abort # handles 35 and 65. # do_test incrblob2-4.7 { db eval {UPDATE OR REPLACE t1 SET id = 65 WHERE id = 35} aborted_handles } {3 14 15 35 65 92} # Insert a couple of new rows. This should not invalidate any handles. # do_test incrblob2-4.9 { db eval {INSERT INTO t1 SELECT NULL, data FROM t1} aborted_handles } {3 14 15 35 65 92} # Delete all rows from 1 to 25. This should abort all handles up to 25. # do_test incrblob2-4.9 { db eval {DELETE FROM t1 WHERE id >=1 AND id <= 25} aborted_handles } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 35 65 92} # Delete the whole table (this will use sqlite3BtreeClearTable()). All handles # should now be aborted. # do_test incrblob2-4.10 { db eval {DELETE FROM t1} aborted_handles } {1 2 3 4 5 6 7 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} do_test incrblob2-4.1.X { for {set ii 1} {$ii < 100} {incr ii} { close $handles($ii) } } {} #-------------------------------------------------------------------------- # The following tests - incrblob2-5.* - test that in shared cache an open # blob handle counts as a read-lock on its table. # ifcapable shared_cache { db close set ::enable_shared_cache [sqlite3_enable_shared_cache 1] do_test incrblob2-5.1 { sqlite3 db test.db sqlite3 db2 test.db execsql { INSERT INTO t1 VALUES(1, 'abcde'); } } {} do_test incrblob2-5.2 { catchsql { INSERT INTO t1 VALUES(2, 'fghij') } db2 } {0 {}} do_test incrblob2-5.3 { set blob [db incrblob t1 data 1] catchsql { INSERT INTO t1 VALUES(3, 'klmno') } db2 } {1 {database is locked}} do_test incrblob2-5.4 { close $blob execsql BEGIN db2 catchsql { INSERT INTO t1 VALUES(4, 'pqrst') } db2 } {0 {}} do_test incrblob2-5.5 { set blob [db incrblob -readonly t1 data 1] catchsql { INSERT INTO t1 VALUES(5, 'uvwxy') } db2 } {1 {database table is locked}} do_test incrblob2-5.6 { close $blob catchsql { INSERT INTO t1 VALUES(3, 'klmno') } db2 } {0 {}} db2 close db close sqlite3_enable_shared_cache $::enable_shared_cache } finish_test |