/ Check-in [decaccc3]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Clarify the effects of the pager_ota_mode pragma. Add tests and fixes for the same.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: decaccc37cbdcd2a663233469efdf4982a810513
User & Date: dan 2014-09-16 20:02:41
Context
2014-09-17
15:20
Add tests and fixes for "PRAGMA ota_mode". check-in: 39df35c4 user: dan tags: ota-update
2014-09-16
20:02
Clarify the effects of the pager_ota_mode pragma. Add tests and fixes for the same. check-in: decaccc3 user: dan tags: ota-update
2014-09-15
19:34
Remove the experimental sqlite3_transaction_save() and restore() APIs. check-in: 48d201cd user: dan tags: ota-update
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Added ext/ota/ota4.test.

            1  +# 2014 August 30
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Test some properties of the pager_ota_mode pragma.
           13  +#
           14  +
           15  +set testdir [file join [file dirname $argv0] .. .. test]
           16  +source $testdir/tester.tcl
           17  +set ::testprefix ota4
           18  +
           19  +# 1. Cannot set the pager_ota_mode flag on a WAL mode database.
           20  +#
           21  +# 2. Or if there is an open read transaction.
           22  +#
           23  +# 3. Cannot start a transaction with pager_ota_mode set if there
           24  +#    is a WAL file in the file-system.
           25  +# 
           26  +# 4. Or if the wal-mode flag is set in the database file header.
           27  +# 
           28  +# 5. Cannot open a transaction with pager_ota_mode set if the database
           29  +#    file has been modified by a rollback mode client since the *-oal
           30  +#    file was started.
           31  +#
           32  +
           33  +do_execsql_test 1.1 { 
           34  +  PRAGMA journal_mode = wal;
           35  +  SELECT * FROM sqlite_master;
           36  +} {wal}
           37  +do_catchsql_test 1.2 { 
           38  +  PRAGMA pager_ota_mode = 1 
           39  +} {1 {cannot set pager_ota_mode in wal mode}}
           40  +
           41  +
           42  +do_execsql_test 2.1 { 
           43  +  PRAGMA journal_mode = delete;
           44  +  BEGIN;
           45  +    SELECT * FROM sqlite_master;
           46  +} {delete}
           47  +do_catchsql_test 2.2 { 
           48  +  PRAGMA pager_ota_mode = 1 
           49  +} {1 {cannot set pager_ota_mode with open transaction}}
           50  +do_execsql_test 2.3 { 
           51  +  COMMIT;
           52  +} {}
           53  +
           54  +
           55  +do_execsql_test 3.1 {
           56  +  PRAGMA journal_mode = wal;
           57  +  CREATE TABLE t1(a, b);
           58  +  INSERT INTO t1 VALUES(1, 2);
           59  +} {wal}
           60  +do_test 3.2 {
           61  +  forcecopy test.db-wal test.db-bak
           62  +  execsql { 
           63  +    PRAGMA journal_mode = delete;
           64  +    PRAGMA pager_ota_mode = 1;
           65  +  }
           66  +  forcecopy test.db-bak test.db-wal
           67  +  catchsql {
           68  +    SELECT * FROM sqlite_master
           69  +  }
           70  +} {1 {unable to open database file}}
           71  +
           72  +do_test 4.1 {
           73  +  db close
           74  +  forcedelete test.db-wal test.db-oal
           75  +  sqlite3 db test.db
           76  +  execsql { 
           77  +    PRAGMA journal_mode = wal;
           78  +    PRAGMA pager_ota_mode = 1;
           79  +  }
           80  +  catchsql {
           81  +    SELECT * FROM sqlite_master;
           82  +  }
           83  +} {1 {unable to open database file}}
           84  +
           85  +do_test 5.1 {
           86  +  forcedelete test.db-oal
           87  +  reset_db
           88  +  execsql {
           89  +    PRAGMA journal_mode = delete;
           90  +    CREATE TABLE t1(a, b);
           91  +    INSERT INTO t1 VALUES(1, 2);
           92  +  }
           93  +  execsql {
           94  +    PRAGMA pager_ota_mode = 1;
           95  +    INSERT INTO t1 VALUES(3, 4);
           96  +  }
           97  +  db close
           98  +  sqlite3 db test.db
           99  +  execsql {
          100  +    SELECT * FROM t1;
          101  +  }
          102  +} {1 2}
          103  +do_execsql_test 5.2 {
          104  +  PRAGMA pager_ota_mode = 1;
          105  +  SELECT * FROM t1;
          106  +  INSERT INTO t1 VALUES(5, 6);
          107  +} {1 2 3 4}
          108  +do_test 5.3 {
          109  +  db close
          110  +  sqlite3 db test.db
          111  +  execsql {
          112  +    INSERT INTO t1 VALUES(7, 8);
          113  +    SELECT * FROM t1;
          114  +  }
          115  +} {1 2 7 8}
          116  +do_catchsql_test 5.4 {
          117  +  PRAGMA pager_ota_mode = 1;
          118  +  SELECT * FROM t1;
          119  +} {1 {database is locked}}
          120  +
          121  +finish_test

Changes to src/pager.c.

   611    611   **
   612    612   ** errCode
   613    613   **
   614    614   **   The Pager.errCode variable is only ever used in PAGER_ERROR state. It
   615    615   **   is set to zero in all other states. In PAGER_ERROR state, Pager.errCode 
   616    616   **   is always set to SQLITE_FULL, SQLITE_IOERR or one of the SQLITE_IOERR_XXX 
   617    617   **   sub-codes.
          618  +**
          619  +** otaMode
          620  +**   This variable is normally 0. It is set to 1 by the PagerSetOtaMode()
          621  +**   function - as a result of a "PRAGMA pager_ota_mode=1" command. Once 
          622  +**   the *-oal file has been opened and it has been determined that the 
          623  +**   database file has not been modified since it was created, this variable 
          624  +**   is set to 2.
          625  +**
          626  +**
   618    627   */
   619    628   struct Pager {
   620    629     sqlite3_vfs *pVfs;          /* OS functions to use for IO */
   621    630     u8 exclusiveMode;           /* Boolean. True if locking_mode==EXCLUSIVE */
   622    631     u8 journalMode;             /* One of the PAGER_JOURNALMODE_* values */
   623    632     u8 useJournal;              /* Use a rollback journal on this file */
   624    633     u8 noSync;                  /* Do not sync the journal if true */
................................................................................
   626    635     u8 ckptSyncFlags;           /* SYNC_NORMAL or SYNC_FULL for checkpoint */
   627    636     u8 walSyncFlags;            /* SYNC_NORMAL or SYNC_FULL for wal writes */
   628    637     u8 syncFlags;               /* SYNC_NORMAL or SYNC_FULL otherwise */
   629    638     u8 tempFile;                /* zFilename is a temporary or immutable file */
   630    639     u8 noLock;                  /* Do not lock (except in WAL mode) */
   631    640     u8 readOnly;                /* True for a read-only database */
   632    641     u8 memDb;                   /* True to inhibit all file I/O */
   633         -  u8 otaMode;                 /* True if in ota_mode */
          642  +  u8 otaMode;                 /* Non-zero if in ota_mode */
   634    643   
   635    644     /**************************************************************************
   636    645     ** The following block contains those class members that change during
   637    646     ** routine operation.  Class members not in this block are either fixed
   638    647     ** when the pager is first created or else only change when there is a
   639    648     ** significant mode change (such as changing the page_size, locking_mode,
   640    649     ** or the journal_mode).  From another view, these class members describe
................................................................................
  5179   5188       assert( pPager->pWal==0 || rc==SQLITE_OK );
  5180   5189   #endif
  5181   5190     }
  5182   5191   
  5183   5192     if( pagerUseWal(pPager) ){
  5184   5193       assert( rc==SQLITE_OK );
  5185   5194       rc = pagerBeginReadTransaction(pPager);
         5195  +    if( rc==SQLITE_OK && pPager->otaMode==1 ){
         5196  +      rc = sqlite3WalCheckSalt(pPager->pWal, pPager->fd);
         5197  +      if( rc!=SQLITE_OK ){
         5198  +        sqlite3WalClose(pPager->pWal, 0, 0, 0);
         5199  +        pPager->pWal = 0;
         5200  +      }else{
         5201  +        pPager->otaMode = 2;
         5202  +      }
         5203  +    }
  5186   5204     }
  5187   5205   
  5188   5206     if( pPager->eState==PAGER_OPEN && rc==SQLITE_OK ){
  5189   5207       rc = pagerPagecount(pPager, &pPager->dbSize);
  5190   5208     }
  5191   5209   
  5192   5210    failed:
................................................................................
  7232   7250                              pPager->pageSize, (u8*)pPager->pTmpSpace);
  7233   7251         pPager->pWal = 0;
  7234   7252         pagerFixMaplimit(pPager);
  7235   7253       }
  7236   7254     }
  7237   7255     return rc;
  7238   7256   }
         7257  +
         7258  +/*
         7259  +** This function is called by the wal.c module to obtain the 8 bytes of 
         7260  +** "salt" written into the wal file header. In OTA mode, this is a copy
         7261  +** of bytes 24-31 of the database file. In non-OTA mode, it is 8 bytes
         7262  +** of pseudo-random data.
         7263  +*/
         7264  +void sqlite3PagerWalSalt(Pager *pPager, u32 *aSalt){
         7265  +  if( pPager->otaMode ){
         7266  +    memcpy(aSalt, pPager->dbFileVers, 8);
         7267  +  }else{
         7268  +    sqlite3_randomness(8, aSalt);
         7269  +  }
         7270  +}
  7239   7271   
  7240   7272   #endif /* !SQLITE_OMIT_WAL */
  7241   7273   
  7242   7274   #ifdef SQLITE_ENABLE_ZIPVFS
  7243   7275   /*
  7244   7276   ** A read-lock must be held on the pager when this function is called. If
  7245   7277   ** the pager is in WAL mode and the WAL file currently contains one or more
................................................................................
  7256   7288   /*
  7257   7289   ** Set or clear the "OTA mode" flag.
  7258   7290   */
  7259   7291   int sqlite3PagerSetOtaMode(Pager *pPager, int bOta){
  7260   7292     if( pPager->pWal || pPager->eState!=PAGER_OPEN ){
  7261   7293       return SQLITE_ERROR;
  7262   7294     }
  7263         -  pPager->otaMode = (u8)bOta;
         7295  +  pPager->otaMode = 1;
  7264   7296     return SQLITE_OK;
  7265   7297   }
  7266   7298   
  7267   7299   #endif /* SQLITE_OMIT_DISKIO */

Changes to src/pager.h.

   204    204     void enable_simulated_io_errors(void);
   205    205   #else
   206    206   # define disable_simulated_io_errors()
   207    207   # define enable_simulated_io_errors()
   208    208   #endif
   209    209   
   210    210   int sqlite3PagerSetOtaMode(Pager *pPager, int bOta);
          211  +void sqlite3PagerWalSalt(Pager *pPager, u32 *aSalt);
   211    212   
   212    213   #endif /* _PAGER_H_ */

Changes to src/pragma.c.

   873    873       }
   874    874       break;
   875    875     }
   876    876   #endif /* !SQLITE_OMIT_PAGER_PRAGMAS && !SQLITE_OMIT_DEPRECATED */
   877    877   
   878    878     /*
   879    879     **  PRAGMA [database.]pager_ota_mode=[01]
          880  +  **
          881  +  ** This pragma sets a flag on the pager associated with the main database
          882  +  ** only. The flag can only be set when there is no open transaction and 
          883  +  ** the pager does not already have an open WAL file.
          884  +  **
          885  +  ** Once the flag has been set, it is not possible to open a regular WAL
          886  +  ** file. If, when the next read-transaction is opened, a *-wal file is 
          887  +  ** found or the database header flags indicate that it is a wal-mode 
          888  +  ** database, SQLITE_CANTOPEN is returned.
          889  +  **
          890  +  ** Otherwise, if no WAL file or flags are found, the pager opens the *-oal
          891  +  ** file and uses it as a write-ahead-log with the *-shm data stored in
          892  +  ** heap-memory. If the *-oal file already exists but the database file has
          893  +  ** been modified since it was created, an SQLITE_BUSY_SNAPSHOT error is
          894  +  ** returned and the read-transaction cannot be opened.
          895  +  **
          896  +  ** Other clients see a rollback-mode database on which the pager_ota_mode
          897  +  ** client is holding a SHARED lock.
   880    898     */
   881    899     case PragTyp_PAGER_OTA_MODE: {
   882    900       Btree *pBt = pDb->pBt;
   883    901       assert( pBt!=0 );
   884    902       if( zRight ){
   885    903         int iArg = !!sqlite3Atoi(zRight);
   886         -      rc = sqlite3PagerSetOtaMode(sqlite3BtreePager(pBt), iArg);
          904  +      if( sqlite3BtreeIsInReadTrans(pBt) ){
          905  +        sqlite3ErrorMsg(pParse, 
          906  +            "cannot set pager_ota_mode with open transaction"
          907  +        );
          908  +      }else if( sqlite3PagerSetOtaMode(sqlite3BtreePager(pBt), iArg) ){
          909  +        sqlite3ErrorMsg(pParse, "cannot set pager_ota_mode in wal mode");
          910  +      }
   887    911       }
   888    912       break;
   889    913     }
   890    914   
   891    915   #if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
   892    916     /*
   893    917     **  PRAGMA [database.]page_size

Changes to src/wal.c.

  2765   2765       u8 aWalHdr[WAL_HDRSIZE];      /* Buffer to assemble wal-header in */
  2766   2766       u32 aCksum[2];                /* Checksum for wal-header */
  2767   2767   
  2768   2768       sqlite3Put4byte(&aWalHdr[0], (WAL_MAGIC | SQLITE_BIGENDIAN));
  2769   2769       sqlite3Put4byte(&aWalHdr[4], WAL_MAX_VERSION);
  2770   2770       sqlite3Put4byte(&aWalHdr[8], szPage);
  2771   2771       sqlite3Put4byte(&aWalHdr[12], pWal->nCkpt);
  2772         -    if( pWal->nCkpt==0 ) sqlite3_randomness(8, pWal->hdr.aSalt);
         2772  +    if( pWal->nCkpt==0 ){
         2773  +      sqlite3PagerWalSalt(pList->pPager, pWal->hdr.aSalt);
         2774  +    }
  2773   2775       memcpy(&aWalHdr[16], pWal->hdr.aSalt, 8);
  2774   2776       walChecksumBytes(1, aWalHdr, WAL_HDRSIZE-2*4, 0, aCksum);
  2775   2777       sqlite3Put4byte(&aWalHdr[24], aCksum[0]);
  2776   2778       sqlite3Put4byte(&aWalHdr[28], aCksum[1]);
  2777   2779       
  2778   2780       pWal->szPage = szPage;
  2779   2781       pWal->hdr.bigEndCksum = SQLITE_BIGENDIAN;
................................................................................
  3079   3081   ** Return true if the argument is non-NULL and the WAL module is using
  3080   3082   ** heap-memory for the wal-index. Otherwise, if the argument is NULL or the
  3081   3083   ** WAL module is using shared-memory, return false. 
  3082   3084   */
  3083   3085   int sqlite3WalHeapMemory(Wal *pWal){
  3084   3086     return (pWal && pWal->exclusiveMode==WAL_HEAPMEMORY_MODE );
  3085   3087   }
         3088  +
         3089  +/*
         3090  +** Unless the wal file is empty, check that the 8 bytes of salt stored in
         3091  +** the wal header are identical to those in the buffer indicated by the
         3092  +** second argument. If they are not, return SQLITE_BUSY_SNAPSHOT. Otherwise,
         3093  +** if the buffers match or the WAL file is empty, return SQLITE_OK.
         3094  +*/
         3095  +int sqlite3WalCheckSalt(Wal *pWal, sqlite3_file *pFd){
         3096  +  int rc = SQLITE_OK;
         3097  +  if( pWal->hdr.mxFrame>0 ){
         3098  +    u8 aData[16];
         3099  +    rc = sqlite3OsRead(pFd, aData, sizeof(aData), 24);
         3100  +    if( rc==SQLITE_OK && memcmp(pWal->hdr.aSalt, aData, 8) ){
         3101  +      rc = SQLITE_BUSY_SNAPSHOT;
         3102  +    }
         3103  +  }
         3104  +  return rc;
         3105  +}
  3086   3106   
  3087   3107   #ifdef SQLITE_ENABLE_ZIPVFS
  3088   3108   /*
  3089   3109   ** If the argument is not NULL, it points to a Wal object that holds a
  3090   3110   ** read-lock. This function returns the database page-size if it is known,
  3091   3111   ** or zero if it is not (or if pWal is NULL).
  3092   3112   */

Changes to src/wal.h.

   121    121   int sqlite3WalExclusiveMode(Wal *pWal, int op);
   122    122   
   123    123   /* Return true if the argument is non-NULL and the WAL module is using
   124    124   ** heap-memory for the wal-index. Otherwise, if the argument is NULL or the
   125    125   ** WAL module is using shared-memory, return false. 
   126    126   */
   127    127   int sqlite3WalHeapMemory(Wal *pWal);
          128  +
          129  +int sqlite3WalCheckSalt(Wal *pWal, sqlite3_file*);
   128    130   
   129    131   #ifdef SQLITE_ENABLE_ZIPVFS
   130    132   /* If the WAL file is not empty, return the number of bytes of content
   131    133   ** stored in each frame (i.e. the db page-size when the WAL was created).
   132    134   */
   133    135   int sqlite3WalFramesize(Wal *pWal);
   134    136   #endif
   135    137   
   136    138   #endif /* ifndef SQLITE_OMIT_WAL */
   137    139   #endif /* _WAL_H_ */