Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | The fullfsync, checkpoint_fullfsync, and cache_spill pragmas apply to all files of a database connection, including those opened by future ATTACH statements. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | cache_spill |
Files: | files | file ages | folders |
SHA1: |
d07c4331a28d44deca1ece8a34118f5b |
User & Date: | drh 2013-08-17 15:42:29.969 |
Context
2013-08-17
| ||
16:37 | Add the cache_spill pragma. Change the fullfsync and checkpoint_fullfsync pragmas to apply to all attached databases. (check-in: 65a85a156f user: drh tags: trunk) | |
15:42 | The fullfsync, checkpoint_fullfsync, and cache_spill pragmas apply to all files of a database connection, including those opened by future ATTACH statements. (Closed-Leaf check-in: d07c4331a2 user: drh tags: cache_spill) | |
00:25 | Test cases for the cache_spill pragma. (check-in: b85c9ec5e0 user: drh tags: cache_spill) | |
Changes
Changes to src/attach.c.
︙ | ︙ | |||
154 155 156 157 158 159 160 161 162 163 164 165 166 167 | "attached databases must use the same text encoding as main database"); rc = SQLITE_ERROR; } pPager = sqlite3BtreePager(aNew->pBt); sqlite3PagerLockingMode(pPager, db->dfltLockMode); sqlite3BtreeSecureDelete(aNew->pBt, sqlite3BtreeSecureDelete(db->aDb[0].pBt,-1) ); } aNew->safety_level = 3; aNew->zName = sqlite3DbStrDup(db, zName); if( rc==SQLITE_OK && aNew->zName==0 ){ rc = SQLITE_NOMEM; } | > | 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | "attached databases must use the same text encoding as main database"); rc = SQLITE_ERROR; } pPager = sqlite3BtreePager(aNew->pBt); sqlite3PagerLockingMode(pPager, db->dfltLockMode); sqlite3BtreeSecureDelete(aNew->pBt, sqlite3BtreeSecureDelete(db->aDb[0].pBt,-1) ); sqlite3BtreeSetPagerFlags(aNew->pBt, 3 | (db->flags & PAGER_FLAGS_MASK)); } aNew->safety_level = 3; aNew->zName = sqlite3DbStrDup(db, zName); if( rc==SQLITE_OK && aNew->zName==0 ){ rc = SQLITE_NOMEM; } |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
153 154 155 156 157 158 159 160 161 162 163 164 165 166 | memcpy(pI64, &value, sizeof(value)); } sqlite3VdbeAddOp4(v, OP_Int64, 0, mem, 0, (char*)pI64, P4_INT64); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLabel, SQLITE_STATIC); sqlite3VdbeAddOp2(v, OP_ResultRow, mem, 1); } #ifndef SQLITE_OMIT_FLAG_PRAGMAS /* ** Check to see if zRight and zLeft refer to a pragma that queries ** or changes one of the flags in db->flags. Return 1 if so and 0 if not. ** Also, implement the pragma. */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | memcpy(pI64, &value, sizeof(value)); } sqlite3VdbeAddOp4(v, OP_Int64, 0, mem, 0, (char*)pI64, P4_INT64); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLabel, SQLITE_STATIC); sqlite3VdbeAddOp2(v, OP_ResultRow, mem, 1); } /* ** Set the safety_level and pager flags for pager iDb. Or if iDb<0 ** set these values for all pagers. */ #ifndef SQLITE_OMIT_PAGER_PRAGMAS static void setAllPagerFlags(sqlite3 *db){ if( db->autoCommit ){ Db *pDb = db->aDb; int n = db->nDb; assert( SQLITE_FullFSync==PAGER_FULLFSYNC ); assert( SQLITE_CkptFullFSync==PAGER_CKPT_FULLFSYNC ); assert( SQLITE_CacheSpill==PAGER_CACHESPILL ); assert( (PAGER_FULLFSYNC | PAGER_CKPT_FULLFSYNC | PAGER_CACHESPILL) == PAGER_FLAGS_MASK ); assert( (pDb->safety_level & PAGER_SYNCHRONOUS_MASK)==pDb->safety_level ); while( (n--) > 0 ){ if( pDb->pBt ){ sqlite3BtreeSetPagerFlags(pDb->pBt, pDb->safety_level | (db->flags & PAGER_FLAGS_MASK) ); } pDb++; } } } #endif #ifndef SQLITE_OMIT_FLAG_PRAGMAS /* ** Check to see if zRight and zLeft refer to a pragma that queries ** or changes one of the flags in db->flags. Return 1 if so and 0 if not. ** Also, implement the pragma. */ |
︙ | ︙ | |||
963 964 965 966 967 968 969 970 971 972 973 974 975 976 | returnSingleInt(pParse, "synchronous", pDb->safety_level-1); }else{ if( !db->autoCommit ){ sqlite3ErrorMsg(pParse, "Safety level may not be changed inside a transaction"); }else{ pDb->safety_level = getSafetyLevel(zRight,0,1)+1; } } }else #endif /* SQLITE_OMIT_PAGER_PRAGMAS */ #ifndef SQLITE_OMIT_FLAG_PRAGMAS if( flagPragma(pParse, zLeft, zRight) ){ | > | < | 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 | returnSingleInt(pParse, "synchronous", pDb->safety_level-1); }else{ if( !db->autoCommit ){ sqlite3ErrorMsg(pParse, "Safety level may not be changed inside a transaction"); }else{ pDb->safety_level = getSafetyLevel(zRight,0,1)+1; setAllPagerFlags(db); } } }else #endif /* SQLITE_OMIT_PAGER_PRAGMAS */ #ifndef SQLITE_OMIT_FLAG_PRAGMAS if( flagPragma(pParse, zLeft, zRight) ){ setAllPagerFlags(db); }else #endif /* SQLITE_OMIT_FLAG_PRAGMAS */ #ifndef SQLITE_OMIT_SCHEMA_PRAGMAS /* ** PRAGMA table_info(<table>) ** |
︙ | ︙ | |||
1802 1803 1804 1805 1806 1807 1808 | #endif }else #endif {/* Empty ELSE clause */} | < < < < < < < < < < < < < < < < | 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 | #endif }else #endif {/* Empty ELSE clause */} pragma_out: sqlite3DbFree(db, zLeft); sqlite3DbFree(db, zRight); } #endif /* SQLITE_OMIT_PRAGMA */ |
Changes to test/pragma2.test.
︙ | ︙ | |||
117 118 119 120 121 122 123 124 125 126 127 128 129 130 | } {9 9} } # Default setting of PRAGMA cache_spill is always ON # db close delete_file test.db test.db-journal sqlite3 db test.db do_execsql_test pragma2-4.1 { PRAGMA cache_spill; PRAGMA main.cache_spill; PRAGMA temp.cache_spill; } {1 1 1} do_execsql_test pragma2-4.2 { | > | 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 | } {9 9} } # Default setting of PRAGMA cache_spill is always ON # db close delete_file test.db test.db-journal delete_file test2.db test2.db-journal sqlite3 db test.db do_execsql_test pragma2-4.1 { PRAGMA cache_spill; PRAGMA main.cache_spill; PRAGMA temp.cache_spill; } {1 1 1} do_execsql_test pragma2-4.2 { |
︙ | ︙ | |||
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 | INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1; COMMIT; PRAGMA cache_spill=ON; } {} do_test pragma2-4.4 { db eval { BEGIN; UPDATE t1 SET c=c+1; PRAGMA lock_status; } } {main exclusive temp unknown} ;# EXCLUSIVE lock due to cache spill do_test pragma2-4.5 { db eval { COMMIT; PRAGMA cache_spill=OFF; BEGIN; UPDATE t1 SET c=c-1; PRAGMA lock_status; } } {main reserved temp unknown} ;# No cache spill, so no exclusive lock finish_test | > > > > > > > > > > > > > > > > > > > > > > > > | 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 | INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1; INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1; COMMIT; ATTACH 'test2.db' AS aux1; CREATE TABLE aux1.t2(a INTEGER PRIMARY KEY, b, c, d); INSERT INTO t2 SELECT * FROM t1; DETACH aux1; PRAGMA cache_spill=ON; } {} do_test pragma2-4.4 { db eval { BEGIN; UPDATE t1 SET c=c+1; PRAGMA lock_status; } } {main exclusive temp unknown} ;# EXCLUSIVE lock due to cache spill do_test pragma2-4.5 { db eval { COMMIT; PRAGMA cache_spill=OFF; BEGIN; UPDATE t1 SET c=c-1; PRAGMA lock_status; } } {main reserved temp unknown} ;# No cache spill, so no exclusive lock # Verify that newly attached databases inherit the cache_spill=OFF # setting. # do_execsql_test pragma2-4.6 { COMMIT; ATTACH 'test2.db' AS aux1; PRAGMA aux1.cache_size=50; BEGIN; UPDATE t2 SET c=c+1; PRAGMA lock_status; } {main unlocked temp unknown aux1 reserved} do_execsql_test pragma2-4.7 { COMMIT; PRAGMA cache_spill=ON; -- Applies to all databases BEGIN; UPDATE t2 SET c=c-1; PRAGMA lock_status; } {main unlocked temp unknown aux1 exclusive} finish_test |