/ Check-in [43db1f44]
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:Experimental "PRAGMA data_version" command for detecting when another process has changed the database file.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | data_version_pragma
Files: files | file ages | folders
SHA1: 43db1f44bce5a0ee50197b95ab0d844540b69d86
User & Date: drh 2014-12-19 19:28:02
Context
2014-12-19
20:27
Adding test cases for the "PRAGMA data_version" command. check-in: c5fb7d6a user: drh tags: data_version_pragma
19:28
Experimental "PRAGMA data_version" command for detecting when another process has changed the database file. check-in: 43db1f44 user: drh tags: data_version_pragma
18:49
Simplify the implementation of the "header-value" pragmas (schema_version, user_version, freelist_count, and application_id) by making them more table-driven. check-in: da27a09d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  8173   8173   ** is the number of free pages currently in the database.  Meta[1]
  8174   8174   ** through meta[15] are available for use by higher layers.  Meta[0]
  8175   8175   ** is read-only, the others are read/write.
  8176   8176   ** 
  8177   8177   ** The schema layer numbers meta values differently.  At the schema
  8178   8178   ** layer (and the SetCookie and ReadCookie opcodes) the number of
  8179   8179   ** free pages is not visible.  So Cookie[0] is the same as Meta[1].
         8180  +**
         8181  +** This routine treats Meta[BTREE_DATA_VERSION] as a special case.  Instead
         8182  +** of reading the value out of the header, it instead loads the "DataVersion"
         8183  +** from the pager.  The BTREE_DATA_VERSION value is not actually stored in the
         8184  +** database file.  It is a number computed by the pager.  But its access
         8185  +** pattern is the same as header meta values, and so it is convenient to
         8186  +** read it from this routine.
  8180   8187   */
  8181   8188   void sqlite3BtreeGetMeta(Btree *p, int idx, u32 *pMeta){
  8182   8189     BtShared *pBt = p->pBt;
  8183   8190   
  8184   8191     sqlite3BtreeEnter(p);
  8185   8192     assert( p->inTrans>TRANS_NONE );
  8186   8193     assert( SQLITE_OK==querySharedCacheTableLock(p, MASTER_ROOT, READ_LOCK) );
  8187   8194     assert( pBt->pPage1 );
  8188   8195     assert( idx>=0 && idx<=15 );
  8189   8196   
  8190         -  *pMeta = get4byte(&pBt->pPage1->aData[36 + idx*4]);
         8197  +  if( idx==BTREE_DATA_VERSION ){
         8198  +    *pMeta = sqlite3PagerDataVersion(pBt->pPager);
         8199  +  }else{
         8200  +    *pMeta = get4byte(&pBt->pPage1->aData[36 + idx*4]);
         8201  +  }
  8191   8202   
  8192   8203     /* If auto-vacuum is disabled in this build and this is an auto-vacuum
  8193   8204     ** database, mark the database as read-only.  */
  8194   8205   #ifdef SQLITE_OMIT_AUTOVACUUM
  8195   8206     if( idx==BTREE_LARGEST_ROOT_PAGE && *pMeta>0 ){
  8196   8207       pBt->btsFlags |= BTS_READ_ONLY;
  8197   8208     }

Changes to src/btree.h.

    15     15   */
    16     16   #ifndef _BTREE_H_
    17     17   #define _BTREE_H_
    18     18   
    19     19   /* TODO: This definition is just included so other modules compile. It
    20     20   ** needs to be revisited.
    21     21   */
    22         -#define SQLITE_N_BTREE_META 10
           22  +#define SQLITE_N_BTREE_META 16
    23     23   
    24     24   /*
    25     25   ** If defined as non-zero, auto-vacuum is enabled by default. Otherwise
    26     26   ** it must be turned on for each database using "PRAGMA auto_vacuum = 1".
    27     27   */
    28     28   #ifndef SQLITE_DEFAULT_AUTOVACUUM
    29     29     #define SQLITE_DEFAULT_AUTOVACUUM 0
................................................................................
   130    130   ** SQLite database header may be found using the following formula:
   131    131   **
   132    132   **   offset = 36 + (idx * 4)
   133    133   **
   134    134   ** For example, the free-page-count field is located at byte offset 36 of
   135    135   ** the database file header. The incr-vacuum-flag field is located at
   136    136   ** byte offset 64 (== 36+4*7).
          137  +**
          138  +** The BTREE_DATA_VERSION value is not really a value stored in the header.
          139  +** It is a read-only number computed by the pager.  But we merge it with
          140  +** the header value access routines since its access pattern is the same.
          141  +** Call it a "virtual meta value".
   137    142   */
   138    143   #define BTREE_FREE_PAGE_COUNT     0
   139    144   #define BTREE_SCHEMA_VERSION      1
   140    145   #define BTREE_FILE_FORMAT         2
   141    146   #define BTREE_DEFAULT_CACHE_SIZE  3
   142    147   #define BTREE_LARGEST_ROOT_PAGE   4
   143    148   #define BTREE_TEXT_ENCODING       5
   144    149   #define BTREE_USER_VERSION        6
   145    150   #define BTREE_INCR_VACUUM         7
   146    151   #define BTREE_APPLICATION_ID      8
          152  +#define BTREE_DATA_VERSION        15  /* A virtual meta-value */
   147    153   
   148    154   /*
   149    155   ** Values that may be OR'd together to form the second argument of an
   150    156   ** sqlite3BtreeCursorHints() call.
   151    157   */
   152    158   #define BTREE_BULKLOAD 0x00000001
   153    159   

Changes to src/pager.c.

   642    642     */
   643    643     u8 eState;                  /* Pager state (OPEN, READER, WRITER_LOCKED..) */
   644    644     u8 eLock;                   /* Current lock held on database file */
   645    645     u8 changeCountDone;         /* Set after incrementing the change-counter */
   646    646     u8 setMaster;               /* True if a m-j name has been written to jrnl */
   647    647     u8 doNotSpill;              /* Do not spill the cache when non-zero */
   648    648     u8 subjInMemory;            /* True to use in-memory sub-journals */
          649  +  u8 bUseFetch;               /* True to use xFetch() */
   649    650     Pgno dbSize;                /* Number of pages in the database */
   650    651     Pgno dbOrigSize;            /* dbSize before the current transaction */
   651    652     Pgno dbFileSize;            /* Number of pages in the database file */
   652    653     Pgno dbHintSize;            /* Value passed to FCNTL_SIZE_HINT call */
   653    654     int errCode;                /* One of several kinds of errors */
   654    655     int nRec;                   /* Pages journalled since last j-header written */
   655    656     u32 cksumInit;              /* Quasi-random value added to every checksum */
................................................................................
   659    660     sqlite3_file *jfd;          /* File descriptor for main journal */
   660    661     sqlite3_file *sjfd;         /* File descriptor for sub-journal */
   661    662     i64 journalOff;             /* Current write offset in the journal file */
   662    663     i64 journalHdr;             /* Byte offset to previous journal header */
   663    664     sqlite3_backup *pBackup;    /* Pointer to list of ongoing backup processes */
   664    665     PagerSavepoint *aSavepoint; /* Array of active savepoints */
   665    666     int nSavepoint;             /* Number of elements in aSavepoint[] */
          667  +  u32 nReset;                 /* Number of calls to pager_reset() */
   666    668     char dbFileVers[16];        /* Changes whenever database file changes */
   667    669   
   668         -  u8 bUseFetch;               /* True to use xFetch() */
   669    670     int nMmapOut;               /* Number of mmap pages currently outstanding */
   670    671     sqlite3_int64 szMmap;       /* Desired maximum mmap size */
   671    672     PgHdr *pMmapFreelist;       /* List of free mmap page headers (pDirty) */
   672    673     /*
   673    674     ** End of the routinely-changing class members
   674    675     ***************************************************************************/
   675    676   
................................................................................
  1677   1678     return rc;
  1678   1679   }
  1679   1680   
  1680   1681   /*
  1681   1682   ** Discard the entire contents of the in-memory page-cache.
  1682   1683   */
  1683   1684   static void pager_reset(Pager *pPager){
         1685  +  pPager->nReset++;
  1684   1686     sqlite3BackupRestart(pPager->pBackup);
  1685   1687     sqlite3PcacheClear(pPager->pPCache);
  1686   1688   }
         1689  +
         1690  +/*
         1691  +** Return the pPager->nReset value
         1692  +*/
         1693  +u32 sqlite3PagerDataVersion(Pager *pPager){
         1694  +  assert( pPager->eState>PAGER_OPEN );
         1695  +  return pPager->nReset;
         1696  +}
  1687   1697   
  1688   1698   /*
  1689   1699   ** Free all structures in the Pager.aSavepoint[] array and set both
  1690   1700   ** Pager.aSavepoint and Pager.nSavepoint to zero. Close the sub-journal
  1691   1701   ** if it is open and the pager is not in exclusive mode.
  1692   1702   */
  1693   1703   static void releaseAllSavepoints(Pager *pPager){

Changes to src/pager.h.

   168    168   
   169    169   #ifdef SQLITE_ENABLE_ZIPVFS
   170    170     int sqlite3PagerWalFramesize(Pager *pPager);
   171    171   #endif
   172    172   
   173    173   /* Functions used to query pager state and configuration. */
   174    174   u8 sqlite3PagerIsreadonly(Pager*);
          175  +u32 sqlite3PagerDataVersion(Pager*);
   175    176   int sqlite3PagerRefcount(Pager*);
   176    177   int sqlite3PagerMemUsed(Pager*);
   177    178   const char *sqlite3PagerFilename(Pager*, int);
   178    179   const sqlite3_vfs *sqlite3PagerVfs(Pager*);
   179    180   sqlite3_file *sqlite3PagerFile(Pager*);
   180    181   const char *sqlite3PagerJournalname(Pager*);
   181    182   int sqlite3PagerNosync(Pager*);

Changes to src/pragma.c.

   150    150   #endif
   151    151   #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && SQLITE_OS_WIN
   152    152     { /* zName:     */ "data_store_directory",
   153    153       /* ePragTyp:  */ PragTyp_DATA_STORE_DIRECTORY,
   154    154       /* ePragFlag: */ 0,
   155    155       /* iArg:      */ 0 },
   156    156   #endif
          157  +#if !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)
          158  +  { /* zName:     */ "data_version",
          159  +    /* ePragTyp:  */ PragTyp_HEADER_VALUE,
          160  +    /* ePragFlag: */ PragFlag_ReadOnly,
          161  +    /* iArg:      */ BTREE_DATA_VERSION },
          162  +#endif
   157    163   #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
   158    164     { /* zName:     */ "database_list",
   159    165       /* ePragTyp:  */ PragTyp_DATABASE_LIST,
   160    166       /* ePragFlag: */ PragFlag_NeedSchema,
   161    167       /* iArg:      */ 0 },
   162    168   #endif
   163    169   #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && !defined(SQLITE_OMIT_DEPRECATED)
................................................................................
   467    473   #if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
   468    474     { /* zName:     */ "writable_schema",
   469    475       /* ePragTyp:  */ PragTyp_FLAG,
   470    476       /* ePragFlag: */ 0,
   471    477       /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
   472    478   #endif
   473    479   };
   474         -/* Number of pragmas: 57 on by default, 70 total. */
          480  +/* Number of pragmas: 58 on by default, 71 total. */
   475    481   /* End of the automatically generated pragma table.
   476    482   ***************************************************************************/
   477    483   
   478    484   /*
   479    485   ** Interpret the given string as a safety level.  Return 0 for OFF,
   480    486   ** 1 for ON or NORMAL and 2 for FULL.  Return 1 for an empty or 
   481    487   ** unrecognized string argument.  The FULL option is disallowed

Changes to tool/mkpragmatab.tcl.

   250    250     IF:   !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)
   251    251   
   252    252     NAME: user_version
   253    253     TYPE: HEADER_VALUE
   254    254     ARG:  BTREE_USER_VERSION
   255    255     IF:   !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)
   256    256   
          257  +  NAME: data_version
          258  +  TYPE: HEADER_VALUE
          259  +  ARG:  BTREE_DATA_VERSION
          260  +  FLAG: ReadOnly
          261  +  IF:   !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)
          262  +
   257    263     NAME: freelist_count
   258    264     TYPE: HEADER_VALUE
   259    265     ARG:  BTREE_FREE_PAGE_COUNT
   260    266     FLAG: ReadOnly
   261    267     IF:   !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)
   262    268   
   263    269     NAME: application_id