/ Check-in [65a85a15]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the cache_spill pragma. Change the fullfsync and checkpoint_fullfsync pragmas to apply to all attached databases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 65a85a156f3c827890e9f1fd22484a2f875c3b6f
User & Date: drh 2013-08-17 16:37:04
Context
2013-08-19
11:15
Fixes for harmless compiler warnings. check-in: a0d9ca4f user: drh tags: trunk
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
2013-08-15
22:40
Make sure that GROUP BY terms select input column names in preference to output column names, in compliance with the SQL standard. Ticket [1c69be2dafc28]. check-in: f2d175f9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/attach.c.

   154    154           "attached databases must use the same text encoding as main database");
   155    155         rc = SQLITE_ERROR;
   156    156       }
   157    157       pPager = sqlite3BtreePager(aNew->pBt);
   158    158       sqlite3PagerLockingMode(pPager, db->dfltLockMode);
   159    159       sqlite3BtreeSecureDelete(aNew->pBt,
   160    160                                sqlite3BtreeSecureDelete(db->aDb[0].pBt,-1) );
          161  +    sqlite3BtreeSetPagerFlags(aNew->pBt, 3 | (db->flags & PAGER_FLAGS_MASK));
   161    162     }
   162    163     aNew->safety_level = 3;
   163    164     aNew->zName = sqlite3DbStrDup(db, zName);
   164    165     if( rc==SQLITE_OK && aNew->zName==0 ){
   165    166       rc = SQLITE_NOMEM;
   166    167     }
   167    168   

Changes to src/btree.c.

  2161   2161   ** how well the database resists damage due to OS crashes and power
  2162   2162   ** failures.  Level 1 is the same as asynchronous (no syncs() occur and
  2163   2163   ** there is a high probability of damage)  Level 2 is the default.  There
  2164   2164   ** is a very low but non-zero probability of damage.  Level 3 reduces the
  2165   2165   ** probability of damage to near zero but with a write performance reduction.
  2166   2166   */
  2167   2167   #ifndef SQLITE_OMIT_PAGER_PRAGMAS
  2168         -int sqlite3BtreeSetSafetyLevel(
         2168  +int sqlite3BtreeSetPagerFlags(
  2169   2169     Btree *p,              /* The btree to set the safety level on */
  2170         -  int level,             /* PRAGMA synchronous.  1=OFF, 2=NORMAL, 3=FULL */
  2171         -  int fullSync,          /* PRAGMA fullfsync. */
  2172         -  int ckptFullSync       /* PRAGMA checkpoint_fullfync */
         2170  +  unsigned pgFlags       /* Various PAGER_* flags */
  2173   2171   ){
  2174   2172     BtShared *pBt = p->pBt;
  2175   2173     assert( sqlite3_mutex_held(p->db->mutex) );
  2176         -  assert( level>=1 && level<=3 );
  2177   2174     sqlite3BtreeEnter(p);
  2178         -  sqlite3PagerSetSafetyLevel(pBt->pPager, level, fullSync, ckptFullSync);
         2175  +  sqlite3PagerSetFlags(pBt->pPager, pgFlags);
  2179   2176     sqlite3BtreeLeave(p);
  2180   2177     return SQLITE_OK;
  2181   2178   }
  2182   2179   #endif
  2183   2180   
  2184   2181   /*
  2185   2182   ** Return TRUE if the given btree is set to safety level 1.  In other

Changes to src/btree.h.

    60     60   #define BTREE_MEMORY        2  /* This is an in-memory DB */
    61     61   #define BTREE_SINGLE        4  /* The file contains at most 1 b-tree */
    62     62   #define BTREE_UNORDERED     8  /* Use of a hash implementation is OK */
    63     63   
    64     64   int sqlite3BtreeClose(Btree*);
    65     65   int sqlite3BtreeSetCacheSize(Btree*,int);
    66     66   int sqlite3BtreeSetMmapLimit(Btree*,sqlite3_int64);
    67         -int sqlite3BtreeSetSafetyLevel(Btree*,int,int,int);
           67  +int sqlite3BtreeSetPagerFlags(Btree*,unsigned);
    68     68   int sqlite3BtreeSyncDisabled(Btree*);
    69     69   int sqlite3BtreeSetPageSize(Btree *p, int nPagesize, int nReserve, int eFix);
    70     70   int sqlite3BtreeGetPageSize(Btree*);
    71     71   int sqlite3BtreeMaxPageCount(Btree*,int);
    72     72   u32 sqlite3BtreeLastPage(Btree*);
    73     73   int sqlite3BtreeSecureDelete(Btree*,int);
    74     74   int sqlite3BtreeGetReserve(Btree*);

Changes to src/main.c.

  2457   2457   
  2458   2458     assert( sizeof(db->aLimit)==sizeof(aHardLimit) );
  2459   2459     memcpy(db->aLimit, aHardLimit, sizeof(db->aLimit));
  2460   2460     db->autoCommit = 1;
  2461   2461     db->nextAutovac = -1;
  2462   2462     db->szMmap = sqlite3GlobalConfig.szMmap;
  2463   2463     db->nextPagesize = 0;
  2464         -  db->flags |= SQLITE_ShortColNames | SQLITE_EnableTrigger
         2464  +  db->flags |= SQLITE_ShortColNames | SQLITE_EnableTrigger | SQLITE_CacheSpill
  2465   2465   #if !defined(SQLITE_DEFAULT_AUTOMATIC_INDEX) || SQLITE_DEFAULT_AUTOMATIC_INDEX
  2466   2466                    | SQLITE_AutoIndex
  2467   2467   #endif
  2468   2468   #if SQLITE_DEFAULT_FILE_FORMAT<4
  2469   2469                    | SQLITE_LegacyFileFmt
  2470   2470   #endif
  2471   2471   #ifdef SQLITE_ENABLE_LOAD_EXTENSION

Changes to src/pager.c.

   449    449     Pgno nOrig;                  /* Original number of pages in file */
   450    450     Pgno iSubRec;                /* Index of first record in sub-journal */
   451    451   #ifndef SQLITE_OMIT_WAL
   452    452     u32 aWalData[WAL_SAVEPOINT_NDATA];        /* WAL savepoint context */
   453    453   #endif
   454    454   };
   455    455   
          456  +/*
          457  +** Bits of the Pager.doNotSpill flag.  See further description below.
          458  +*/
          459  +#define SPILLFLAG_OFF         0x01      /* Never spill cache.  Set via pragma */
          460  +#define SPILLFLAG_ROLLBACK    0x02      /* Current rolling back, so do not spill */
          461  +#define SPILLFLAG_NOSYNC      0x04      /* Spill is ok, but do not sync */
          462  +
   456    463   /*
   457    464   ** A open page cache is an instance of struct Pager. A description of
   458    465   ** some of the more important member variables follows:
   459    466   **
   460    467   ** eState
   461    468   **
   462    469   **   The current 'state' of the pager object. See the comment and state
................................................................................
   515    522   **   subsequently interrupted transaction that reuses the journal file.
   516    523   **
   517    524   **   The flag is cleared as soon as the journal file is finalized (either
   518    525   **   by PagerCommitPhaseTwo or PagerRollback). If an IO error prevents the
   519    526   **   journal file from being successfully finalized, the setMaster flag
   520    527   **   is cleared anyway (and the pager will move to ERROR state).
   521    528   **
   522         -** doNotSpill, doNotSyncSpill
          529  +** doNotSpill
   523    530   **
   524         -**   These two boolean variables control the behavior of cache-spills
   525         -**   (calls made by the pcache module to the pagerStress() routine to
   526         -**   write cached data to the file-system in order to free up memory).
          531  +**   This variables control the behavior of cache-spills  (calls made by
          532  +**   the pcache module to the pagerStress() routine to write cached data
          533  +**   to the file-system in order to free up memory).
   527    534   **
   528         -**   When doNotSpill is non-zero, writing to the database from pagerStress()
   529         -**   is disabled altogether. This is done in a very obscure case that
          535  +**   When bits SPILLFLAG_OFF or SPILLFLAG_ROLLBACK of doNotSpill are set,
          536  +**   writing to the database from pagerStress() is disabled altogether.
          537  +**   The SPILLFLAG_ROLLBACK case is done in a very obscure case that
   530    538   **   comes up during savepoint rollback that requires the pcache module
   531    539   **   to allocate a new page to prevent the journal file from being written
   532         -**   while it is being traversed by code in pager_playback().
          540  +**   while it is being traversed by code in pager_playback().  The SPILLFLAG_OFF
          541  +**   case is a user preference.
   533    542   ** 
   534         -**   If doNotSyncSpill is non-zero, writing to the database from pagerStress()
          543  +**   If the SPILLFLAG_NOSYNC bit is set, writing to the database from pagerStress()
   535    544   **   is permitted, but syncing the journal file is not. This flag is set
   536    545   **   by sqlite3PagerWrite() when the file-system sector-size is larger than
   537    546   **   the database page-size in order to prevent a journal sync from happening 
   538    547   **   in between the journalling of two pages on the same sector. 
   539    548   **
   540    549   ** subjInMemory
   541    550   **
................................................................................
   631    640     ** "configuration" of the pager.
   632    641     */
   633    642     u8 eState;                  /* Pager state (OPEN, READER, WRITER_LOCKED..) */
   634    643     u8 eLock;                   /* Current lock held on database file */
   635    644     u8 changeCountDone;         /* Set after incrementing the change-counter */
   636    645     u8 setMaster;               /* True if a m-j name has been written to jrnl */
   637    646     u8 doNotSpill;              /* Do not spill the cache when non-zero */
   638         -  u8 doNotSyncSpill;          /* Do not do a spill that requires jrnl sync */
   639    647     u8 subjInMemory;            /* True to use in-memory sub-journals */
   640    648     Pgno dbSize;                /* Number of pages in the database */
   641    649     Pgno dbOrigSize;            /* dbSize before the current transaction */
   642    650     Pgno dbFileSize;            /* Number of pages in the database file */
   643    651     Pgno dbHintSize;            /* Value passed to FCNTL_SIZE_HINT call */
   644    652     int errCode;                /* One of several kinds of errors */
   645    653     int nRec;                   /* Pages journalled since last j-header written */
................................................................................
  2291   2299       **
  2292   2300       ** The solution is to add an in-memory page to the cache containing
  2293   2301       ** the data just read from the sub-journal. Mark the page as dirty 
  2294   2302       ** and if the pager requires a journal-sync, then mark the page as 
  2295   2303       ** requiring a journal-sync before it is written.
  2296   2304       */
  2297   2305       assert( isSavepnt );
  2298         -    assert( pPager->doNotSpill==0 );
  2299         -    pPager->doNotSpill++;
         2306  +    assert( (pPager->doNotSpill & SPILLFLAG_ROLLBACK)==0 );
         2307  +    pPager->doNotSpill |= SPILLFLAG_ROLLBACK;
  2300   2308       rc = sqlite3PagerAcquire(pPager, pgno, &pPg, 1);
  2301         -    assert( pPager->doNotSpill==1 );
  2302         -    pPager->doNotSpill--;
         2309  +    assert( (pPager->doNotSpill & SPILLFLAG_ROLLBACK)!=0 );
         2310  +    pPager->doNotSpill &= ~SPILLFLAG_ROLLBACK;
  2303   2311       if( rc!=SQLITE_OK ) return rc;
  2304   2312       pPg->flags &= ~PGHDR_NEED_READ;
  2305   2313       sqlite3PcacheMakeDirty(pPg);
  2306   2314     }
  2307   2315     if( pPg ){
  2308   2316       /* No page should ever be explicitly rolled back that is in use, except
  2309   2317       ** for page 1 which is held in use in order to keep the lock on the
................................................................................
  3400   3408   ** Free as much memory as possible from the pager.
  3401   3409   */
  3402   3410   void sqlite3PagerShrink(Pager *pPager){
  3403   3411     sqlite3PcacheShrink(pPager->pPCache);
  3404   3412   }
  3405   3413   
  3406   3414   /*
  3407         -** Adjust the robustness of the database to damage due to OS crashes
  3408         -** or power failures by changing the number of syncs()s when writing
  3409         -** the rollback journal.  There are three levels:
         3415  +** Adjust settings of the pager to those specified in the pgFlags parameter.
         3416  +**
         3417  +** The "level" in pgFlags & PAGER_SYNCHRONOUS_MASK sets the robustness
         3418  +** of the database to damage due to OS crashes or power failures by
         3419  +** changing the number of syncs()s when writing the journals.
         3420  +** There are three levels:
  3410   3421   **
  3411   3422   **    OFF       sqlite3OsSync() is never called.  This is the default
  3412   3423   **              for temporary and transient files.
  3413   3424   **
  3414   3425   **    NORMAL    The journal is synced once before writes begin on the
  3415   3426   **              database.  This is normally adequate protection, but
  3416   3427   **              it is theoretically possible, though very unlikely,
................................................................................
  3443   3454   ** synchronous=FULL versus synchronous=NORMAL setting determines when
  3444   3455   ** the xSync primitive is called and is relevant to all platforms.
  3445   3456   **
  3446   3457   ** Numeric values associated with these states are OFF==1, NORMAL=2,
  3447   3458   ** and FULL=3.
  3448   3459   */
  3449   3460   #ifndef SQLITE_OMIT_PAGER_PRAGMAS
  3450         -void sqlite3PagerSetSafetyLevel(
         3461  +void sqlite3PagerSetFlags(
  3451   3462     Pager *pPager,        /* The pager to set safety level for */
  3452         -  int level,            /* PRAGMA synchronous.  1=OFF, 2=NORMAL, 3=FULL */  
  3453         -  int bFullFsync,       /* PRAGMA fullfsync */
  3454         -  int bCkptFullFsync    /* PRAGMA checkpoint_fullfsync */
         3463  +  unsigned pgFlags      /* Various flags */
  3455   3464   ){
         3465  +  unsigned level = pgFlags & PAGER_SYNCHRONOUS_MASK;
  3456   3466     assert( level>=1 && level<=3 );
  3457   3467     pPager->noSync =  (level==1 || pPager->tempFile) ?1:0;
  3458   3468     pPager->fullSync = (level==3 && !pPager->tempFile) ?1:0;
  3459   3469     if( pPager->noSync ){
  3460   3470       pPager->syncFlags = 0;
  3461   3471       pPager->ckptSyncFlags = 0;
  3462         -  }else if( bFullFsync ){
         3472  +  }else if( pgFlags & PAGER_FULLFSYNC ){
  3463   3473       pPager->syncFlags = SQLITE_SYNC_FULL;
  3464   3474       pPager->ckptSyncFlags = SQLITE_SYNC_FULL;
  3465         -  }else if( bCkptFullFsync ){
         3475  +  }else if( pgFlags & PAGER_CKPT_FULLFSYNC ){
  3466   3476       pPager->syncFlags = SQLITE_SYNC_NORMAL;
  3467   3477       pPager->ckptSyncFlags = SQLITE_SYNC_FULL;
  3468   3478     }else{
  3469   3479       pPager->syncFlags = SQLITE_SYNC_NORMAL;
  3470   3480       pPager->ckptSyncFlags = SQLITE_SYNC_NORMAL;
  3471   3481     }
  3472   3482     pPager->walSyncFlags = pPager->syncFlags;
  3473   3483     if( pPager->fullSync ){
  3474   3484       pPager->walSyncFlags |= WAL_SYNC_TRANSACTIONS;
  3475   3485     }
         3486  +  if( pgFlags & PAGER_CACHESPILL ){
         3487  +    pPager->doNotSpill &= ~SPILLFLAG_OFF;
         3488  +  }else{
         3489  +    pPager->doNotSpill |= SPILLFLAG_OFF;
         3490  +  }
  3476   3491   }
  3477   3492   #endif
  3478   3493   
  3479   3494   /*
  3480   3495   ** The following global variable is incremented whenever the library
  3481   3496   ** attempts to open a temporary file.  This information is used for
  3482   3497   ** testing and analysis only.  
................................................................................
  4369   4384   static int pagerStress(void *p, PgHdr *pPg){
  4370   4385     Pager *pPager = (Pager *)p;
  4371   4386     int rc = SQLITE_OK;
  4372   4387   
  4373   4388     assert( pPg->pPager==pPager );
  4374   4389     assert( pPg->flags&PGHDR_DIRTY );
  4375   4390   
  4376         -  /* The doNotSyncSpill flag is set during times when doing a sync of
         4391  +  /* The doNotSpill NOSYNC bit is set during times when doing a sync of
  4377   4392     ** journal (and adding a new header) is not allowed.  This occurs
  4378   4393     ** during calls to sqlite3PagerWrite() while trying to journal multiple
  4379   4394     ** pages belonging to the same sector.
  4380   4395     **
  4381         -  ** The doNotSpill flag inhibits all cache spilling regardless of whether
  4382         -  ** or not a sync is required.  This is set during a rollback.
         4396  +  ** The doNotSpill ROLLBACK and OFF bits inhibits all cache spilling
         4397  +  ** regardless of whether or not a sync is required.  This is set during
         4398  +  ** a rollback or by user request, respectively.
  4383   4399     **
  4384   4400     ** Spilling is also prohibited when in an error state since that could
  4385   4401     ** lead to database corruption.   In the current implementaton it 
  4386   4402     ** is impossible for sqlite3PcacheFetch() to be called with createFlag==1
  4387   4403     ** while in the error state, hence it is impossible for this routine to
  4388   4404     ** be called in the error state.  Nevertheless, we include a NEVER()
  4389   4405     ** test for the error state as a safeguard against future changes.
  4390   4406     */
  4391   4407     if( NEVER(pPager->errCode) ) return SQLITE_OK;
  4392         -  if( pPager->doNotSpill ) return SQLITE_OK;
  4393         -  if( pPager->doNotSyncSpill && (pPg->flags & PGHDR_NEED_SYNC)!=0 ){
         4408  +  testcase( pPager->doNotSpill & SPILLFLAG_ROLLBACK );
         4409  +  testcase( pPager->doNotSpill & SPILLFLAG_OFF );
         4410  +  testcase( pPager->doNotSpill & SPILLFLAG_NOSYNC );
         4411  +  if( pPager->doNotSpill
         4412  +   && ((pPager->doNotSpill & (SPILLFLAG_ROLLBACK|SPILLFLAG_OFF))!=0
         4413  +      || (pPg->flags & PGHDR_NEED_SYNC)!=0)
         4414  +  ){
  4394   4415       return SQLITE_OK;
  4395   4416     }
  4396   4417   
  4397   4418     pPg->pDirty = 0;
  4398   4419     if( pagerUseWal(pPager) ){
  4399   4420       /* Write a single frame for this page to the log. */
  4400   4421       if( subjRequiresPage(pPg) ){ 
................................................................................
  5740   5761     if( nPagePerSector>1 ){
  5741   5762       Pgno nPageCount;          /* Total number of pages in database file */
  5742   5763       Pgno pg1;                 /* First page of the sector pPg is located on. */
  5743   5764       int nPage = 0;            /* Number of pages starting at pg1 to journal */
  5744   5765       int ii;                   /* Loop counter */
  5745   5766       int needSync = 0;         /* True if any page has PGHDR_NEED_SYNC */
  5746   5767   
  5747         -    /* Set the doNotSyncSpill flag to 1. This is because we cannot allow
         5768  +    /* Set the doNotSpill NOSYNC bit to 1. This is because we cannot allow
  5748   5769       ** a journal header to be written between the pages journaled by
  5749   5770       ** this function.
  5750   5771       */
  5751   5772       assert( !MEMDB );
  5752         -    assert( pPager->doNotSyncSpill==0 );
  5753         -    pPager->doNotSyncSpill++;
         5773  +    assert( (pPager->doNotSpill & SPILLFLAG_NOSYNC)==0 );
         5774  +    pPager->doNotSpill |= SPILLFLAG_NOSYNC;
  5754   5775   
  5755   5776       /* This trick assumes that both the page-size and sector-size are
  5756   5777       ** an integer power of 2. It sets variable pg1 to the identifier
  5757   5778       ** of the first page of the sector pPg is located on.
  5758   5779       */
  5759   5780       pg1 = ((pPg->pgno-1) & ~(nPagePerSector-1)) + 1;
  5760   5781   
................................................................................
  5805   5826           if( pPage ){
  5806   5827             pPage->flags |= PGHDR_NEED_SYNC;
  5807   5828             sqlite3PagerUnref(pPage);
  5808   5829           }
  5809   5830         }
  5810   5831       }
  5811   5832   
  5812         -    assert( pPager->doNotSyncSpill==1 );
  5813         -    pPager->doNotSyncSpill--;
         5833  +    assert( (pPager->doNotSpill & SPILLFLAG_NOSYNC)!=0 );
         5834  +    pPager->doNotSpill &= ~SPILLFLAG_NOSYNC;
  5814   5835     }else{
  5815   5836       rc = pager_write(pDbPage);
  5816   5837     }
  5817   5838     return rc;
  5818   5839   }
  5819   5840   
  5820   5841   /*

Changes to src/pager.h.

    80     80   
    81     81   /*
    82     82   ** Flags that make up the mask passed to sqlite3PagerAcquire().
    83     83   */
    84     84   #define PAGER_ACQUIRE_NOCONTENT     0x01  /* Do not load data from disk */
    85     85   #define PAGER_ACQUIRE_READONLY      0x02  /* Read-only page is acceptable */
    86     86   
           87  +/*
           88  +** Flags for sqlite3PagerSetFlags()
           89  +*/
           90  +#define PAGER_SYNCHRONOUS_OFF       0x01  /* PRAGMA synchronous=OFF */
           91  +#define PAGER_SYNCHRONOUS_NORMAL    0x02  /* PRAGMA synchronous=NORMAL */
           92  +#define PAGER_SYNCHRONOUS_FULL      0x03  /* PRAGMA synchronous=FULL */
           93  +#define PAGER_SYNCHRONOUS_MASK      0x03  /* Mask for three values above */
           94  +#define PAGER_FULLFSYNC             0x04  /* PRAGMA fullfsync=ON */
           95  +#define PAGER_CKPT_FULLFSYNC        0x08  /* PRAGMA checkpoint_fullfsync=ON */
           96  +#define PAGER_CACHESPILL            0x10  /* PRAGMA cache_spill=ON */
           97  +#define PAGER_FLAGS_MASK            0x1c  /* All above except SYNCHRONOUS */
           98  +
    87     99   /*
    88    100   ** The remainder of this file contains the declarations of the functions
    89    101   ** that make up the Pager sub-system API. See source code comments for 
    90    102   ** a detailed description of each routine.
    91    103   */
    92    104   
    93    105   /* Open and close a Pager connection. */ 
................................................................................
   106    118   /* Functions used to configure a Pager object. */
   107    119   void sqlite3PagerSetBusyhandler(Pager*, int(*)(void *), void *);
   108    120   int sqlite3PagerSetPagesize(Pager*, u32*, int);
   109    121   int sqlite3PagerMaxPageCount(Pager*, int);
   110    122   void sqlite3PagerSetCachesize(Pager*, int);
   111    123   void sqlite3PagerSetMmapLimit(Pager *, sqlite3_int64);
   112    124   void sqlite3PagerShrink(Pager*);
   113         -void sqlite3PagerSetSafetyLevel(Pager*,int,int,int);
          125  +void sqlite3PagerSetFlags(Pager*,unsigned);
   114    126   int sqlite3PagerLockingMode(Pager *, int);
   115    127   int sqlite3PagerSetJournalMode(Pager *, int);
   116    128   int sqlite3PagerGetJournalMode(Pager*);
   117    129   int sqlite3PagerOkToChangeJournalMode(Pager*);
   118    130   i64 sqlite3PagerJournalSizeLimit(Pager *, i64);
   119    131   sqlite3_backup **sqlite3PagerBackupPtr(Pager*);
   120    132   

Changes to src/pragma.c.

   153    153       memcpy(pI64, &value, sizeof(value));
   154    154     }
   155    155     sqlite3VdbeAddOp4(v, OP_Int64, 0, mem, 0, (char*)pI64, P4_INT64);
   156    156     sqlite3VdbeSetNumCols(v, 1);
   157    157     sqlite3VdbeSetColName(v, 0, COLNAME_NAME, zLabel, SQLITE_STATIC);
   158    158     sqlite3VdbeAddOp2(v, OP_ResultRow, mem, 1);
   159    159   }
          160  +
          161  +
          162  +/*
          163  +** Set the safety_level and pager flags for pager iDb.  Or if iDb<0
          164  +** set these values for all pagers.
          165  +*/
          166  +#ifndef SQLITE_OMIT_PAGER_PRAGMAS
          167  +static void setAllPagerFlags(sqlite3 *db){
          168  +  if( db->autoCommit ){
          169  +    Db *pDb = db->aDb;
          170  +    int n = db->nDb;
          171  +    assert( SQLITE_FullFSync==PAGER_FULLFSYNC );
          172  +    assert( SQLITE_CkptFullFSync==PAGER_CKPT_FULLFSYNC );
          173  +    assert( SQLITE_CacheSpill==PAGER_CACHESPILL );
          174  +    assert( (PAGER_FULLFSYNC | PAGER_CKPT_FULLFSYNC | PAGER_CACHESPILL)
          175  +             ==  PAGER_FLAGS_MASK );
          176  +    assert( (pDb->safety_level & PAGER_SYNCHRONOUS_MASK)==pDb->safety_level );
          177  +    while( (n--) > 0 ){
          178  +      if( pDb->pBt ){
          179  +        sqlite3BtreeSetPagerFlags(pDb->pBt,
          180  +                 pDb->safety_level | (db->flags & PAGER_FLAGS_MASK) );
          181  +      }
          182  +      pDb++;
          183  +    }
          184  +  }
          185  +}
          186  +#endif
          187  +
   160    188   
   161    189   #ifndef SQLITE_OMIT_FLAG_PRAGMAS
   162    190   /*
   163    191   ** Check to see if zRight and zLeft refer to a pragma that queries
   164    192   ** or changes one of the flags in db->flags.  Return 1 if so and 0 if not.
   165    193   ** Also, implement the pragma.
   166    194   */
................................................................................
   172    200       { "full_column_names",        SQLITE_FullColNames  },
   173    201       { "short_column_names",       SQLITE_ShortColNames },
   174    202       { "count_changes",            SQLITE_CountRows     },
   175    203       { "empty_result_callbacks",   SQLITE_NullCallback  },
   176    204       { "legacy_file_format",       SQLITE_LegacyFileFmt },
   177    205       { "fullfsync",                SQLITE_FullFSync     },
   178    206       { "checkpoint_fullfsync",     SQLITE_CkptFullFSync },
          207  +    { "cache_spill",              SQLITE_CacheSpill    },
   179    208       { "reverse_unordered_selects", SQLITE_ReverseOrder  },
   180    209       { "query_only",               SQLITE_QueryOnly     },
   181    210   #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
   182    211       { "automatic_index",          SQLITE_AutoIndex     },
   183    212   #endif
   184    213   #ifdef SQLITE_DEBUG
   185    214       { "sql_trace",                SQLITE_SqlTrace      },
................................................................................
   962    991         returnSingleInt(pParse, "synchronous", pDb->safety_level-1);
   963    992       }else{
   964    993         if( !db->autoCommit ){
   965    994           sqlite3ErrorMsg(pParse, 
   966    995               "Safety level may not be changed inside a transaction");
   967    996         }else{
   968    997           pDb->safety_level = getSafetyLevel(zRight,0,1)+1;
          998  +        setAllPagerFlags(db);
   969    999         }
   970   1000       }
   971   1001     }else
   972   1002   #endif /* SQLITE_OMIT_PAGER_PRAGMAS */
   973   1003   
   974   1004   #ifndef SQLITE_OMIT_FLAG_PRAGMAS
   975   1005     if( flagPragma(pParse, zLeft, zRight) ){
   976         -    /* The flagPragma() subroutine also generates any necessary code
   977         -    ** there is nothing more to do here */
         1006  +    setAllPagerFlags(db);
   978   1007     }else
   979   1008   #endif /* SQLITE_OMIT_FLAG_PRAGMAS */
   980   1009   
   981   1010   #ifndef SQLITE_OMIT_SCHEMA_PRAGMAS
   982   1011     /*
   983   1012     **   PRAGMA table_info(<table>)
   984   1013     **
................................................................................
  1801   1830   #endif
  1802   1831     }else
  1803   1832   #endif
  1804   1833   
  1805   1834    
  1806   1835     {/* Empty ELSE clause */}
  1807   1836   
  1808         -  /*
  1809         -  ** Reset the safety level, in case the fullfsync flag or synchronous
  1810         -  ** setting changed.
  1811         -  */
  1812         -#ifndef SQLITE_OMIT_PAGER_PRAGMAS
  1813         -  if( db->autoCommit ){
  1814         -    sqlite3BtreeSetSafetyLevel(pDb->pBt, pDb->safety_level,
  1815         -               (db->flags&SQLITE_FullFSync)!=0,
  1816         -               (db->flags&SQLITE_CkptFullFSync)!=0);
  1817         -  }
  1818         -#endif
  1819   1837   pragma_out:
  1820   1838     sqlite3DbFree(db, zLeft);
  1821   1839     sqlite3DbFree(db, zRight);
  1822   1840   }
  1823   1841   
  1824   1842   #endif /* SQLITE_OMIT_PRAGMA */

Changes to src/sqliteInt.h.

   968    968   #define ENC(db) ((db)->aDb[0].pSchema->enc)
   969    969   
   970    970   /*
   971    971   ** Possible values for the sqlite3.flags.
   972    972   */
   973    973   #define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */
   974    974   #define SQLITE_InternChanges  0x00000002  /* Uncommitted Hash table changes */
   975         -#define SQLITE_FullColNames   0x00000004  /* Show full column names on SELECT */
   976         -#define SQLITE_ShortColNames  0x00000008  /* Show short columns names */
   977         -#define SQLITE_CountRows      0x00000010  /* Count rows changed by INSERT, */
          975  +#define SQLITE_FullFSync      0x00000004  /* Use full fsync on the backend */
          976  +#define SQLITE_CkptFullFSync  0x00000008  /* Use full fsync for checkpoint */
          977  +#define SQLITE_CacheSpill     0x00000010  /* OK to spill pager cache */
          978  +#define SQLITE_FullColNames   0x00000020  /* Show full column names on SELECT */
          979  +#define SQLITE_ShortColNames  0x00000040  /* Show short columns names */
          980  +#define SQLITE_CountRows      0x00000080  /* Count rows changed by INSERT, */
   978    981                                             /*   DELETE, or UPDATE and return */
   979    982                                             /*   the count using a callback. */
   980         -#define SQLITE_NullCallback   0x00000020  /* Invoke the callback once if the */
          983  +#define SQLITE_NullCallback   0x00000100  /* Invoke the callback once if the */
   981    984                                             /*   result set is empty */
   982         -#define SQLITE_SqlTrace       0x00000040  /* Debug print SQL as it executes */
   983         -#define SQLITE_VdbeListing    0x00000080  /* Debug listings of VDBE programs */
   984         -#define SQLITE_WriteSchema    0x00000100  /* OK to update SQLITE_MASTER */
   985         -#define SQLITE_VdbeAddopTrace 0x00000200  /* Trace sqlite3VdbeAddOp() calls */
   986         -#define SQLITE_IgnoreChecks   0x00000400  /* Do not enforce check constraints */
   987         -#define SQLITE_ReadUncommitted 0x0000800  /* For shared-cache mode */
   988         -#define SQLITE_LegacyFileFmt  0x00001000  /* Create new databases in format 1 */
   989         -#define SQLITE_FullFSync      0x00002000  /* Use full fsync on the backend */
   990         -#define SQLITE_CkptFullFSync  0x00004000  /* Use full fsync for checkpoint */
   991         -#define SQLITE_RecoveryMode   0x00008000  /* Ignore schema errors */
   992         -#define SQLITE_ReverseOrder   0x00010000  /* Reverse unordered SELECTs */
   993         -#define SQLITE_RecTriggers    0x00020000  /* Enable recursive triggers */
   994         -#define SQLITE_ForeignKeys    0x00040000  /* Enforce foreign key constraints  */
   995         -#define SQLITE_AutoIndex      0x00080000  /* Enable automatic indexes */
   996         -#define SQLITE_PreferBuiltin  0x00100000  /* Preference to built-in funcs */
   997         -#define SQLITE_LoadExtension  0x00200000  /* Enable load_extension */
   998         -#define SQLITE_EnableTrigger  0x00400000  /* True to enable triggers */
   999         -#define SQLITE_DeferFKs       0x00800000  /* Defer all FK constraints */
  1000         -#define SQLITE_QueryOnly      0x01000000  /* Disable database changes */
          985  +#define SQLITE_SqlTrace       0x00000200  /* Debug print SQL as it executes */
          986  +#define SQLITE_VdbeListing    0x00000400  /* Debug listings of VDBE programs */
          987  +#define SQLITE_WriteSchema    0x00000800  /* OK to update SQLITE_MASTER */
          988  +#define SQLITE_VdbeAddopTrace 0x00001000  /* Trace sqlite3VdbeAddOp() calls */
          989  +#define SQLITE_IgnoreChecks   0x00002000  /* Do not enforce check constraints */
          990  +#define SQLITE_ReadUncommitted 0x0004000  /* For shared-cache mode */
          991  +#define SQLITE_LegacyFileFmt  0x00008000  /* Create new databases in format 1 */
          992  +#define SQLITE_RecoveryMode   0x00010000  /* Ignore schema errors */
          993  +#define SQLITE_ReverseOrder   0x00020000  /* Reverse unordered SELECTs */
          994  +#define SQLITE_RecTriggers    0x00040000  /* Enable recursive triggers */
          995  +#define SQLITE_ForeignKeys    0x00080000  /* Enforce foreign key constraints  */
          996  +#define SQLITE_AutoIndex      0x00100000  /* Enable automatic indexes */
          997  +#define SQLITE_PreferBuiltin  0x00200000  /* Preference to built-in funcs */
          998  +#define SQLITE_LoadExtension  0x00400000  /* Enable load_extension */
          999  +#define SQLITE_EnableTrigger  0x00800000  /* True to enable triggers */
         1000  +#define SQLITE_DeferFKs       0x01000000  /* Defer all FK constraints */
         1001  +#define SQLITE_QueryOnly      0x02000000  /* Disable database changes */
  1001   1002   
  1002   1003   
  1003   1004   /*
  1004   1005   ** Bits of the sqlite3.dbOptFlags field that are used by the
  1005   1006   ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
  1006   1007   ** selectively disable various optimizations.
  1007   1008   */

Changes to test/pragma2.test.

    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Test organization:
    21     21   #
    22     22   # pragma2-1.*: Test freelist_count pragma on the main database.
    23     23   # pragma2-2.*: Test freelist_count pragma on an attached database.
    24     24   # pragma2-3.*: Test trying to write to the freelist_count is a no-op.
           25  +# pragma2-4.*: Tests for PRAGMA cache_spill
    25     26   #
    26     27   
    27     28   ifcapable !pragma||!schema_pragmas {
    28     29     finish_test
    29     30     return
    30     31   }
    31     32   
................................................................................
   111    112     do_test pragma2-3.3 {
   112    113       execsql {
   113    114         PRAGMA aux.freelist_count = 500;
   114    115         PRAGMA aux.freelist_count;
   115    116       }
   116    117     } {9 9}
   117    118   }
          119  +
          120  +# Default setting of PRAGMA cache_spill is always ON
          121  +#
          122  +db close
          123  +delete_file test.db test.db-journal
          124  +delete_file test2.db test2.db-journal
          125  +sqlite3 db test.db
          126  +do_execsql_test pragma2-4.1 {
          127  +  PRAGMA cache_spill;
          128  +  PRAGMA main.cache_spill;
          129  +  PRAGMA temp.cache_spill;
          130  +} {1 1 1}
          131  +do_execsql_test pragma2-4.2 {
          132  +  PRAGMA cache_spill=OFF;
          133  +  PRAGMA cache_spill;
          134  +  PRAGMA main.cache_spill;
          135  +  PRAGMA temp.cache_spill;
          136  +} {0 0 0}
          137  +do_execsql_test pragma2-4.3 {
          138  +  PRAGMA page_size=1024;
          139  +  PRAGMA cache_size=50;
          140  +  BEGIN;
          141  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
          142  +  INSERT INTO t1 VALUES(1, randomblob(400), 1, randomblob(400));
          143  +  INSERT INTO t1 SELECT a+1, randomblob(400), a+1, randomblob(400) FROM t1;
          144  +  INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1;
          145  +  INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1;
          146  +  INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1;
          147  +  INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1;
          148  +  INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1;
          149  +  INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1;
          150  +  COMMIT;
          151  +  ATTACH 'test2.db' AS aux1;
          152  +  CREATE TABLE aux1.t2(a INTEGER PRIMARY KEY, b, c, d);
          153  +  INSERT INTO t2 SELECT * FROM t1;
          154  +  DETACH aux1;
          155  +  PRAGMA cache_spill=ON;
          156  +} {}
          157  +do_test pragma2-4.4 {
          158  +  db eval {
          159  +    BEGIN;
          160  +    UPDATE t1 SET c=c+1;
          161  +    PRAGMA lock_status;
          162  +  }
          163  +} {main exclusive temp unknown}  ;# EXCLUSIVE lock due to cache spill
          164  +do_test pragma2-4.5 {
          165  +  db eval {
          166  +    COMMIT;
          167  +    PRAGMA cache_spill=OFF;
          168  +    BEGIN;
          169  +    UPDATE t1 SET c=c-1;
          170  +    PRAGMA lock_status;
          171  +  }
          172  +} {main reserved temp unknown}   ;# No cache spill, so no exclusive lock
          173  +
          174  +# Verify that newly attached databases inherit the cache_spill=OFF
          175  +# setting.
          176  +#
          177  +do_execsql_test pragma2-4.6 {
          178  +  COMMIT;
          179  +  ATTACH 'test2.db' AS aux1;
          180  +  PRAGMA aux1.cache_size=50;
          181  +  BEGIN;
          182  +  UPDATE t2 SET c=c+1;
          183  +  PRAGMA lock_status;
          184  +} {main unlocked temp unknown aux1 reserved}
          185  +do_execsql_test pragma2-4.7 {
          186  +  COMMIT;
          187  +  PRAGMA cache_spill=ON; -- Applies to all databases
          188  +  BEGIN;
          189  +  UPDATE t2 SET c=c-1;
          190  +  PRAGMA lock_status;
          191  +} {main unlocked temp unknown aux1 exclusive}
          192  +   
   118    193   
   119    194   finish_test