SQLite

Check-in [decaccc37c]
Login

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
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: decaccc37cbdcd2a663233469efdf4982a810513
User & Date: dan 2014-09-16 20:02:41.756
Context
2014-09-17
15:20
Add tests and fixes for "PRAGMA ota_mode". (check-in: 39df35c4ac 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: decaccc37c user: dan tags: ota-update)
2014-09-15
19:34
Remove the experimental sqlite3_transaction_save() and restore() APIs. (check-in: 48d201cd8b user: dan tags: ota-update)
Changes
Unified Diff Ignore Whitespace Patch
Added ext/ota/ota4.test.


















































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
# 2014 August 30
#
# 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.
#
#***********************************************************************
#
# Test some properties of the pager_ota_mode pragma.
#

set testdir [file join [file dirname $argv0] .. .. test]
source $testdir/tester.tcl
set ::testprefix ota4

# 1. Cannot set the pager_ota_mode flag on a WAL mode database.
#
# 2. Or if there is an open read transaction.
#
# 3. Cannot start a transaction with pager_ota_mode set if there
#    is a WAL file in the file-system.
# 
# 4. Or if the wal-mode flag is set in the database file header.
# 
# 5. Cannot open a transaction with pager_ota_mode set if the database
#    file has been modified by a rollback mode client since the *-oal
#    file was started.
#

do_execsql_test 1.1 { 
  PRAGMA journal_mode = wal;
  SELECT * FROM sqlite_master;
} {wal}
do_catchsql_test 1.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode in wal mode}}


do_execsql_test 2.1 { 
  PRAGMA journal_mode = delete;
  BEGIN;
    SELECT * FROM sqlite_master;
} {delete}
do_catchsql_test 2.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode with open transaction}}
do_execsql_test 2.3 { 
  COMMIT;
} {}


do_execsql_test 3.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
} {wal}
do_test 3.2 {
  forcecopy test.db-wal test.db-bak
  execsql { 
    PRAGMA journal_mode = delete;
    PRAGMA pager_ota_mode = 1;
  }
  forcecopy test.db-bak test.db-wal
  catchsql {
    SELECT * FROM sqlite_master
  }
} {1 {unable to open database file}}

do_test 4.1 {
  db close
  forcedelete test.db-wal test.db-oal
  sqlite3 db test.db
  execsql { 
    PRAGMA journal_mode = wal;
    PRAGMA pager_ota_mode = 1;
  }
  catchsql {
    SELECT * FROM sqlite_master;
  }
} {1 {unable to open database file}}

do_test 5.1 {
  forcedelete test.db-oal
  reset_db
  execsql {
    PRAGMA journal_mode = delete;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
  }
  execsql {
    PRAGMA pager_ota_mode = 1;
    INSERT INTO t1 VALUES(3, 4);
  }
  db close
  sqlite3 db test.db
  execsql {
    SELECT * FROM t1;
  }
} {1 2}
do_execsql_test 5.2 {
  PRAGMA pager_ota_mode = 1;
  SELECT * FROM t1;
  INSERT INTO t1 VALUES(5, 6);
} {1 2 3 4}
do_test 5.3 {
  db close
  sqlite3 db test.db
  execsql {
    INSERT INTO t1 VALUES(7, 8);
    SELECT * FROM t1;
  }
} {1 2 7 8}
do_catchsql_test 5.4 {
  PRAGMA pager_ota_mode = 1;
  SELECT * FROM t1;
} {1 {database is locked}}

finish_test
Changes to src/pager.c.
611
612
613
614
615
616
617









618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
**
** errCode
**
**   The Pager.errCode variable is only ever used in PAGER_ERROR state. It
**   is set to zero in all other states. In PAGER_ERROR state, Pager.errCode 
**   is always set to SQLITE_FULL, SQLITE_IOERR or one of the SQLITE_IOERR_XXX 
**   sub-codes.









*/
struct Pager {
  sqlite3_vfs *pVfs;          /* OS functions to use for IO */
  u8 exclusiveMode;           /* Boolean. True if locking_mode==EXCLUSIVE */
  u8 journalMode;             /* One of the PAGER_JOURNALMODE_* values */
  u8 useJournal;              /* Use a rollback journal on this file */
  u8 noSync;                  /* Do not sync the journal if true */
  u8 fullSync;                /* Do extra syncs of the journal for robustness */
  u8 ckptSyncFlags;           /* SYNC_NORMAL or SYNC_FULL for checkpoint */
  u8 walSyncFlags;            /* SYNC_NORMAL or SYNC_FULL for wal writes */
  u8 syncFlags;               /* SYNC_NORMAL or SYNC_FULL otherwise */
  u8 tempFile;                /* zFilename is a temporary or immutable file */
  u8 noLock;                  /* Do not lock (except in WAL mode) */
  u8 readOnly;                /* True for a read-only database */
  u8 memDb;                   /* True to inhibit all file I/O */
  u8 otaMode;                 /* True if in ota_mode */

  /**************************************************************************
  ** The following block contains those class members that change during
  ** routine operation.  Class members not in this block are either fixed
  ** when the pager is first created or else only change when there is a
  ** significant mode change (such as changing the page_size, locking_mode,
  ** or the journal_mode).  From another view, these class members describe







>
>
>
>
>
>
>
>
>















|







611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
**
** errCode
**
**   The Pager.errCode variable is only ever used in PAGER_ERROR state. It
**   is set to zero in all other states. In PAGER_ERROR state, Pager.errCode 
**   is always set to SQLITE_FULL, SQLITE_IOERR or one of the SQLITE_IOERR_XXX 
**   sub-codes.
**
** otaMode
**   This variable is normally 0. It is set to 1 by the PagerSetOtaMode()
**   function - as a result of a "PRAGMA pager_ota_mode=1" command. Once 
**   the *-oal file has been opened and it has been determined that the 
**   database file has not been modified since it was created, this variable 
**   is set to 2.
**
**
*/
struct Pager {
  sqlite3_vfs *pVfs;          /* OS functions to use for IO */
  u8 exclusiveMode;           /* Boolean. True if locking_mode==EXCLUSIVE */
  u8 journalMode;             /* One of the PAGER_JOURNALMODE_* values */
  u8 useJournal;              /* Use a rollback journal on this file */
  u8 noSync;                  /* Do not sync the journal if true */
  u8 fullSync;                /* Do extra syncs of the journal for robustness */
  u8 ckptSyncFlags;           /* SYNC_NORMAL or SYNC_FULL for checkpoint */
  u8 walSyncFlags;            /* SYNC_NORMAL or SYNC_FULL for wal writes */
  u8 syncFlags;               /* SYNC_NORMAL or SYNC_FULL otherwise */
  u8 tempFile;                /* zFilename is a temporary or immutable file */
  u8 noLock;                  /* Do not lock (except in WAL mode) */
  u8 readOnly;                /* True for a read-only database */
  u8 memDb;                   /* True to inhibit all file I/O */
  u8 otaMode;                 /* Non-zero if in ota_mode */

  /**************************************************************************
  ** The following block contains those class members that change during
  ** routine operation.  Class members not in this block are either fixed
  ** when the pager is first created or else only change when there is a
  ** significant mode change (such as changing the page_size, locking_mode,
  ** or the journal_mode).  From another view, these class members describe
5179
5180
5181
5182
5183
5184
5185









5186
5187
5188
5189
5190
5191
5192
    assert( pPager->pWal==0 || rc==SQLITE_OK );
#endif
  }

  if( pagerUseWal(pPager) ){
    assert( rc==SQLITE_OK );
    rc = pagerBeginReadTransaction(pPager);









  }

  if( pPager->eState==PAGER_OPEN && rc==SQLITE_OK ){
    rc = pagerPagecount(pPager, &pPager->dbSize);
  }

 failed:







>
>
>
>
>
>
>
>
>







5188
5189
5190
5191
5192
5193
5194
5195
5196
5197
5198
5199
5200
5201
5202
5203
5204
5205
5206
5207
5208
5209
5210
    assert( pPager->pWal==0 || rc==SQLITE_OK );
#endif
  }

  if( pagerUseWal(pPager) ){
    assert( rc==SQLITE_OK );
    rc = pagerBeginReadTransaction(pPager);
    if( rc==SQLITE_OK && pPager->otaMode==1 ){
      rc = sqlite3WalCheckSalt(pPager->pWal, pPager->fd);
      if( rc!=SQLITE_OK ){
        sqlite3WalClose(pPager->pWal, 0, 0, 0);
        pPager->pWal = 0;
      }else{
        pPager->otaMode = 2;
      }
    }
  }

  if( pPager->eState==PAGER_OPEN && rc==SQLITE_OK ){
    rc = pagerPagecount(pPager, &pPager->dbSize);
  }

 failed:
7232
7233
7234
7235
7236
7237
7238














7239
7240
7241
7242
7243
7244
7245
                           pPager->pageSize, (u8*)pPager->pTmpSpace);
      pPager->pWal = 0;
      pagerFixMaplimit(pPager);
    }
  }
  return rc;
}















#endif /* !SQLITE_OMIT_WAL */

#ifdef SQLITE_ENABLE_ZIPVFS
/*
** A read-lock must be held on the pager when this function is called. If
** the pager is in WAL mode and the WAL file currently contains one or more







>
>
>
>
>
>
>
>
>
>
>
>
>
>







7250
7251
7252
7253
7254
7255
7256
7257
7258
7259
7260
7261
7262
7263
7264
7265
7266
7267
7268
7269
7270
7271
7272
7273
7274
7275
7276
7277
                           pPager->pageSize, (u8*)pPager->pTmpSpace);
      pPager->pWal = 0;
      pagerFixMaplimit(pPager);
    }
  }
  return rc;
}

/*
** This function is called by the wal.c module to obtain the 8 bytes of 
** "salt" written into the wal file header. In OTA mode, this is a copy
** of bytes 24-31 of the database file. In non-OTA mode, it is 8 bytes
** of pseudo-random data.
*/
void sqlite3PagerWalSalt(Pager *pPager, u32 *aSalt){
  if( pPager->otaMode ){
    memcpy(aSalt, pPager->dbFileVers, 8);
  }else{
    sqlite3_randomness(8, aSalt);
  }
}

#endif /* !SQLITE_OMIT_WAL */

#ifdef SQLITE_ENABLE_ZIPVFS
/*
** A read-lock must be held on the pager when this function is called. If
** the pager is in WAL mode and the WAL file currently contains one or more
7256
7257
7258
7259
7260
7261
7262
7263
7264
7265
7266
7267
/*
** Set or clear the "OTA mode" flag.
*/
int sqlite3PagerSetOtaMode(Pager *pPager, int bOta){
  if( pPager->pWal || pPager->eState!=PAGER_OPEN ){
    return SQLITE_ERROR;
  }
  pPager->otaMode = (u8)bOta;
  return SQLITE_OK;
}

#endif /* SQLITE_OMIT_DISKIO */







|




7288
7289
7290
7291
7292
7293
7294
7295
7296
7297
7298
7299
/*
** Set or clear the "OTA mode" flag.
*/
int sqlite3PagerSetOtaMode(Pager *pPager, int bOta){
  if( pPager->pWal || pPager->eState!=PAGER_OPEN ){
    return SQLITE_ERROR;
  }
  pPager->otaMode = 1;
  return SQLITE_OK;
}

#endif /* SQLITE_OMIT_DISKIO */
Changes to src/pager.h.
204
205
206
207
208
209
210

211
212
  void enable_simulated_io_errors(void);
#else
# define disable_simulated_io_errors()
# define enable_simulated_io_errors()
#endif

int sqlite3PagerSetOtaMode(Pager *pPager, int bOta);


#endif /* _PAGER_H_ */







>


204
205
206
207
208
209
210
211
212
213
  void enable_simulated_io_errors(void);
#else
# define disable_simulated_io_errors()
# define enable_simulated_io_errors()
#endif

int sqlite3PagerSetOtaMode(Pager *pPager, int bOta);
void sqlite3PagerWalSalt(Pager *pPager, u32 *aSalt);

#endif /* _PAGER_H_ */
Changes to src/pragma.c.
873
874
875
876
877
878
879


















880
881
882
883
884
885




886


887
888
889
890
891
892
893
    }
    break;
  }
#endif /* !SQLITE_OMIT_PAGER_PRAGMAS && !SQLITE_OMIT_DEPRECATED */

  /*
  **  PRAGMA [database.]pager_ota_mode=[01]


















  */
  case PragTyp_PAGER_OTA_MODE: {
    Btree *pBt = pDb->pBt;
    assert( pBt!=0 );
    if( zRight ){
      int iArg = !!sqlite3Atoi(zRight);




      rc = sqlite3PagerSetOtaMode(sqlite3BtreePager(pBt), iArg);


    }
    break;
  }

#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
  /*
  **  PRAGMA [database.]page_size







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>






>
>
>
>
|
>
>







873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
    }
    break;
  }
#endif /* !SQLITE_OMIT_PAGER_PRAGMAS && !SQLITE_OMIT_DEPRECATED */

  /*
  **  PRAGMA [database.]pager_ota_mode=[01]
  **
  ** This pragma sets a flag on the pager associated with the main database
  ** only. The flag can only be set when there is no open transaction and 
  ** the pager does not already have an open WAL file.
  **
  ** Once the flag has been set, it is not possible to open a regular WAL
  ** file. If, when the next read-transaction is opened, a *-wal file is 
  ** found or the database header flags indicate that it is a wal-mode 
  ** database, SQLITE_CANTOPEN is returned.
  **
  ** Otherwise, if no WAL file or flags are found, the pager opens the *-oal
  ** file and uses it as a write-ahead-log with the *-shm data stored in
  ** heap-memory. If the *-oal file already exists but the database file has
  ** been modified since it was created, an SQLITE_BUSY_SNAPSHOT error is
  ** returned and the read-transaction cannot be opened.
  **
  ** Other clients see a rollback-mode database on which the pager_ota_mode
  ** client is holding a SHARED lock.
  */
  case PragTyp_PAGER_OTA_MODE: {
    Btree *pBt = pDb->pBt;
    assert( pBt!=0 );
    if( zRight ){
      int iArg = !!sqlite3Atoi(zRight);
      if( sqlite3BtreeIsInReadTrans(pBt) ){
        sqlite3ErrorMsg(pParse, 
            "cannot set pager_ota_mode with open transaction"
        );
      }else if( sqlite3PagerSetOtaMode(sqlite3BtreePager(pBt), iArg) ){
        sqlite3ErrorMsg(pParse, "cannot set pager_ota_mode in wal mode");
      }
    }
    break;
  }

#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
  /*
  **  PRAGMA [database.]page_size
Changes to src/wal.c.
2765
2766
2767
2768
2769
2770
2771
2772


2773
2774
2775
2776
2777
2778
2779
    u8 aWalHdr[WAL_HDRSIZE];      /* Buffer to assemble wal-header in */
    u32 aCksum[2];                /* Checksum for wal-header */

    sqlite3Put4byte(&aWalHdr[0], (WAL_MAGIC | SQLITE_BIGENDIAN));
    sqlite3Put4byte(&aWalHdr[4], WAL_MAX_VERSION);
    sqlite3Put4byte(&aWalHdr[8], szPage);
    sqlite3Put4byte(&aWalHdr[12], pWal->nCkpt);
    if( pWal->nCkpt==0 ) sqlite3_randomness(8, pWal->hdr.aSalt);


    memcpy(&aWalHdr[16], pWal->hdr.aSalt, 8);
    walChecksumBytes(1, aWalHdr, WAL_HDRSIZE-2*4, 0, aCksum);
    sqlite3Put4byte(&aWalHdr[24], aCksum[0]);
    sqlite3Put4byte(&aWalHdr[28], aCksum[1]);
    
    pWal->szPage = szPage;
    pWal->hdr.bigEndCksum = SQLITE_BIGENDIAN;







|
>
>







2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
    u8 aWalHdr[WAL_HDRSIZE];      /* Buffer to assemble wal-header in */
    u32 aCksum[2];                /* Checksum for wal-header */

    sqlite3Put4byte(&aWalHdr[0], (WAL_MAGIC | SQLITE_BIGENDIAN));
    sqlite3Put4byte(&aWalHdr[4], WAL_MAX_VERSION);
    sqlite3Put4byte(&aWalHdr[8], szPage);
    sqlite3Put4byte(&aWalHdr[12], pWal->nCkpt);
    if( pWal->nCkpt==0 ){
      sqlite3PagerWalSalt(pList->pPager, pWal->hdr.aSalt);
    }
    memcpy(&aWalHdr[16], pWal->hdr.aSalt, 8);
    walChecksumBytes(1, aWalHdr, WAL_HDRSIZE-2*4, 0, aCksum);
    sqlite3Put4byte(&aWalHdr[24], aCksum[0]);
    sqlite3Put4byte(&aWalHdr[28], aCksum[1]);
    
    pWal->szPage = szPage;
    pWal->hdr.bigEndCksum = SQLITE_BIGENDIAN;
3079
3080
3081
3082
3083
3084
3085


















3086
3087
3088
3089
3090
3091
3092
** Return true if the argument is non-NULL and the WAL module is using
** heap-memory for the wal-index. Otherwise, if the argument is NULL or the
** WAL module is using shared-memory, return false. 
*/
int sqlite3WalHeapMemory(Wal *pWal){
  return (pWal && pWal->exclusiveMode==WAL_HEAPMEMORY_MODE );
}



















#ifdef SQLITE_ENABLE_ZIPVFS
/*
** If the argument is not NULL, it points to a Wal object that holds a
** read-lock. This function returns the database page-size if it is known,
** or zero if it is not (or if pWal is NULL).
*/







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
** Return true if the argument is non-NULL and the WAL module is using
** heap-memory for the wal-index. Otherwise, if the argument is NULL or the
** WAL module is using shared-memory, return false. 
*/
int sqlite3WalHeapMemory(Wal *pWal){
  return (pWal && pWal->exclusiveMode==WAL_HEAPMEMORY_MODE );
}

/*
** Unless the wal file is empty, check that the 8 bytes of salt stored in
** the wal header are identical to those in the buffer indicated by the
** second argument. If they are not, return SQLITE_BUSY_SNAPSHOT. Otherwise,
** if the buffers match or the WAL file is empty, return SQLITE_OK.
*/
int sqlite3WalCheckSalt(Wal *pWal, sqlite3_file *pFd){
  int rc = SQLITE_OK;
  if( pWal->hdr.mxFrame>0 ){
    u8 aData[16];
    rc = sqlite3OsRead(pFd, aData, sizeof(aData), 24);
    if( rc==SQLITE_OK && memcmp(pWal->hdr.aSalt, aData, 8) ){
      rc = SQLITE_BUSY_SNAPSHOT;
    }
  }
  return rc;
}

#ifdef SQLITE_ENABLE_ZIPVFS
/*
** If the argument is not NULL, it points to a Wal object that holds a
** read-lock. This function returns the database page-size if it is known,
** or zero if it is not (or if pWal is NULL).
*/
Changes to src/wal.h.
121
122
123
124
125
126
127


128
129
130
131
132
133
134
int sqlite3WalExclusiveMode(Wal *pWal, int op);

/* Return true if the argument is non-NULL and the WAL module is using
** heap-memory for the wal-index. Otherwise, if the argument is NULL or the
** WAL module is using shared-memory, return false. 
*/
int sqlite3WalHeapMemory(Wal *pWal);



#ifdef SQLITE_ENABLE_ZIPVFS
/* If the WAL file is not empty, return the number of bytes of content
** stored in each frame (i.e. the db page-size when the WAL was created).
*/
int sqlite3WalFramesize(Wal *pWal);
#endif







>
>







121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
int sqlite3WalExclusiveMode(Wal *pWal, int op);

/* Return true if the argument is non-NULL and the WAL module is using
** heap-memory for the wal-index. Otherwise, if the argument is NULL or the
** WAL module is using shared-memory, return false. 
*/
int sqlite3WalHeapMemory(Wal *pWal);

int sqlite3WalCheckSalt(Wal *pWal, sqlite3_file*);

#ifdef SQLITE_ENABLE_ZIPVFS
/* If the WAL file is not empty, return the number of bytes of content
** stored in each frame (i.e. the db page-size when the WAL was created).
*/
int sqlite3WalFramesize(Wal *pWal);
#endif