/ Check-in [de50f25c]
Login

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

Overview
Comment:Add the "PRAGMA data_version" command for checking to see if a database has been modified.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: de50f25ce3226fa4929b8236c72c88b739859d5f
User & Date: drh 2014-12-20 14:50:28
Context
2014-12-20
21:14
Minor fixes and enhancements to the SQLITE_ENABLE_API_ARMOR functionality. check-in: cb3e4219 user: mistachkin tags: trunk
14:58
Merge the PRAGMA data_version command and the enhancements to FK query planning from trunk into the sessions branch. check-in: d4f82af0 user: drh tags: sessions
14:50
Add the "PRAGMA data_version" command for checking to see if a database has been modified. check-in: de50f25c user: drh tags: trunk
14:34
Update the PRAGMA data_version command so that it reponse to changes made by a shared-cache database connection, and also to changes made by the same database connection. Add test cases to verify the new behavior. Closed-Leaf check-in: 44ee5383 user: drh tags: data_version_pragma
2014-12-19
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 iDataVersion;           /* Changes whenever database content changes */
   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->iDataVersion++;
  1684   1686     sqlite3BackupRestart(pPager->pBackup);
  1685   1687     sqlite3PcacheClear(pPager->pPCache);
  1686   1688   }
         1689  +
         1690  +/*
         1691  +** Return the pPager->iDataVersion value
         1692  +*/
         1693  +u32 sqlite3PagerDataVersion(Pager *pPager){
         1694  +  assert( pPager->eState>PAGER_OPEN );
         1695  +  return pPager->iDataVersion;
         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){
................................................................................
  6303   6313     ){
  6304   6314       assert( pPager->journalOff==JOURNAL_HDR_SZ(pPager) || !pPager->journalOff );
  6305   6315       pPager->eState = PAGER_READER;
  6306   6316       return SQLITE_OK;
  6307   6317     }
  6308   6318   
  6309   6319     PAGERTRACE(("COMMIT %d\n", PAGERID(pPager)));
         6320  +  pPager->iDataVersion++;
  6310   6321     rc = pager_end_transaction(pPager, pPager->setMaster, 1);
  6311   6322     return pager_error(pPager, rc);
  6312   6323   }
  6313   6324   
  6314   6325   /*
  6315   6326   ** If a write transaction is open, then all changes made within the 
  6316   6327   ** transaction are reverted and the current write-transaction is closed.

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

Added test/pragma3.test.

            1  +# 2014-12-19
            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  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests for PRAGMA data_version command.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +do_execsql_test pragma3-100 {
           20  +  PRAGMA data_version;
           21  +} {1}
           22  +do_execsql_test pragma3-101 {
           23  +  PRAGMA temp.data_version;
           24  +} {1}
           25  +
           26  +# Writing to the pragma is a no-op 
           27  +do_execsql_test pragma3-102 {
           28  +  PRAGMA main.data_version=1234;
           29  +  PRAGMA main.data_version;
           30  +} {1 1}
           31  +
           32  +# EVIDENCE-OF: R-27726-60934 The "PRAGMA data_version" command provides
           33  +# an indication that the database file has been modified.
           34  +#
           35  +# EVIDENCE-OF: R-30058-27547 The integer values returned by two
           36  +# invocations of "PRAGMA data_version" will be different if changes
           37  +# where committed to that database in between the two invocations.
           38  +#
           39  +# EVIDENCE-OF: R-10201-09349 The "PRAGMA data_version" command responses
           40  +# to changes committed by the same database connection, by database
           41  +# connections sharing a cache in shared cache mode, and by completely
           42  +# independent database connections including connections in separate
           43  +# threads and processes.
           44  +#
           45  +# In this test, it response to two separate changes on the same database
           46  +# connection.
           47  +#
           48  +do_execsql_test pragma3-110 {
           49  +  CREATE TABLE t1(a);
           50  +  INSERT INTO t1 VALUES(100),(200),(300);
           51  +  SELECT * FROM t1;
           52  +  PRAGMA data_version;
           53  +} {100 200 300 3}
           54  +
           55  +sqlite3 db2 test.db
           56  +do_test pragma3-120 {
           57  +  db2 eval {
           58  +    SELECT * FROM t1;
           59  +    PRAGMA data_version;
           60  +  }
           61  +} {100 200 300 1}
           62  +
           63  +do_execsql_test pragma3-130 {
           64  +  INSERT INTO t1 VALUES(400),(500);
           65  +  SELECT * FROM t1;
           66  +  PRAGMA data_version;
           67  +} {100 200 300 400 500 4}
           68  +
           69  +# EVIDENCE-OF: R-10201-09349 The "PRAGMA data_version" command responses
           70  +# to changes committed by the same database connection, by database
           71  +# connections sharing a cache in shared cache mode, and by completely
           72  +# independent database connections including connections in separate
           73  +# threads and processes.
           74  +#
           75  +# In these test, it response to changes in a different database connection
           76  +# part of the same process.
           77  +#
           78  +do_test pragma3-140 {
           79  +  db2 eval {
           80  +    SELECT * FROM t1;
           81  +    PRAGMA data_version;
           82  +    UPDATE t1 SET a=a+1;
           83  +    SELECT * FROM t1;
           84  +    PRAGMA data_version;
           85  +  }
           86  +} {100 200 300 400 500 2 101 201 301 401 501 3}
           87  +do_execsql_test pragma3-150 {
           88  +  SELECT * FROM t1;
           89  +  PRAGMA data_version;
           90  +} {101 201 301 401 501 5}
           91  +
           92  +# EVIDENCE-OF: R-10201-09349 The "PRAGMA data_version" command responses
           93  +# to changes committed by the same database connection, by database
           94  +# connections sharing a cache in shared cache mode, and by completely
           95  +# independent database connections including connections in separate
           96  +# threads and processes.
           97  +#
           98  +# This test verifies behavior when a separate process changes the database
           99  +# file.
          100  +#
          101  +do_test pragma3-200 {
          102  +  set fd [open pragma3.txt wb]
          103  +  puts $fd {
          104  +     sqlite3 db test.db;
          105  +     db eval {DELETE FROM t1 WHERE a>300};
          106  +     db close;
          107  +     exit;
          108  +  }
          109  +  close $fd
          110  +  exec [info nameofexec] pragma3.txt
          111  +  forcedelete pragma3.txt
          112  +  db eval {
          113  +    PRAGMA data_version;
          114  +    SELECT * FROM t1;
          115  +  }
          116  +} {6 101 201}
          117  +db2 close
          118  +db close
          119  +
          120  +# EVIDENCE-OF: R-10201-09349 The "PRAGMA data_version" command responses
          121  +# to changes committed by the same database connection, by database
          122  +# connections sharing a cache in shared cache mode, and by completely
          123  +# independent database connections including connections in separate
          124  +# threads and processes.
          125  +#
          126  +# The next series of tests verifies the behavior for shared-cache
          127  +# database connections.
          128  +#
          129  +ifcapable shared_cache {
          130  +  set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
          131  +  sqlite3 db test.db
          132  +  sqlite3 db2 test.db
          133  +  do_test pragma3-300 {
          134  +    db eval {
          135  +      PRAGMA data_version;
          136  +      CREATE TABLE t3(a,b,c);
          137  +      PRAGMA data_version;
          138  +    }
          139  +  } {1 2}
          140  +  do_test pragma3-310 {
          141  +    db2 eval {
          142  +      PRAGMA data_version;
          143  +      INSERT INTO t3(a,b,c) VALUES('abc','def','ghi');
          144  +      SELECT * FROM t3;
          145  +      PRAGMA data_version;
          146  +    }
          147  +  } {2 abc def ghi 3}
          148  +  do_test pragma3-320 {
          149  +    db eval {
          150  +      PRAGMA data_version;
          151  +      SELECT * FROM t3;
          152  +    }
          153  +  } {3 abc def ghi}
          154  +  db2 close
          155  +  db close
          156  +  sqlite3_enable_shared_cache $::enable_shared_cache
          157  +}
          158  +
          159  +# Make sure this also works in WAL mode
          160  +#
          161  +ifcapable wal {
          162  +  sqlite3 db test.db
          163  +  db eval {PRAGMA journal_mode=WAL}
          164  +  sqlite3 db2 test.db
          165  +  do_test pragma3-400 {
          166  +    db eval {
          167  +      PRAGMA data_version;
          168  +      PRAGMA journal_mode;
          169  +      SELECT * FROM t1;
          170  +    }
          171  +  } {3 wal 101 201}
          172  +  do_test pragma3-410 {
          173  +    db2 eval {
          174  +      PRAGMA data_version;
          175  +      PRAGMA journal_mode;
          176  +      SELECT * FROM t1;
          177  +    }
          178  +  } {2 wal 101 201}
          179  +  do_test pragma3-420 {
          180  +    db eval {UPDATE t1 SET a=111*(a/100); PRAGMA data_version; SELECT * FROM t1}
          181  +  } {4 111 222}
          182  +  do_test pragma3-430 {
          183  +    db2 eval {PRAGMA data_version; SELECT * FROM t1;}
          184  +  } {3 111 222}
          185  +  db2 close
          186  +}
          187  +
          188  +finish_test

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