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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | cache_spill |
| Files: | files | file ages | folders |
| SHA1: | d07c4331a28d44deca1ece8a34118f5b121b3ee2 |
| User & Date: | drh 2013-08-17 15:42:29 |
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: 65a85a15 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: d07c4331 user: drh tags: cache_spill | |
| 00:25 | Test cases for the cache_spill pragma. check-in: b85c9ec5 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 ... 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 .... 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 |
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.
*/
................................................................................
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) ){
/* The flagPragma() subroutine also generates any necessary code
** there is nothing more to do here */
}else
#endif /* SQLITE_OMIT_FLAG_PRAGMAS */
#ifndef SQLITE_OMIT_SCHEMA_PRAGMAS
/*
** PRAGMA table_info(<table>)
**
................................................................................
#endif
}else
#endif
{/* Empty ELSE clause */}
/*
** Reset the safety level, in case the fullfsync flag or synchronous
** setting changed.
*/
#ifndef SQLITE_OMIT_PAGER_PRAGMAS
if( db->autoCommit ){
assert( (pDb->safety_level & PAGER_SYNCHRONOUS_MASK)==pDb->safety_level );
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 );
sqlite3BtreeSetPagerFlags(pDb->pBt,
pDb->safety_level | (db->flags & PAGER_FLAGS_MASK) );
}
#endif
pragma_out:
sqlite3DbFree(db, zLeft);
sqlite3DbFree(db, zRight);
}
#endif /* SQLITE_OMIT_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 ... 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 .... 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 |
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.
*/
................................................................................
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>)
**
................................................................................
#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 ... 143 144 145 146 147 148 149 150 151 152 153 154 155 156 ... 161 162 163 164 165 166 167 168 169 |
} {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 {
................................................................................
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;
................................................................................
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
|
> > > > > > > > > > > > > > > > > > > > > > > > > |
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 ... 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 ... 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 |
} {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 {
................................................................................
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;
................................................................................
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
|