/ Check-in [44ee5383]
Login

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

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | data_version_pragma
Files: files | file ages | folders
SHA1:44ee538374940c50198949f2cbb9213ba2375b6a
User & Date: drh 2014-12-20 14:34:02
Context
2014-12-20
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
20:27
Adding test cases for the "PRAGMA data_version" command. check-in: c5fb7d6a user: drh tags: data_version_pragma
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pager.c.

   660    660     sqlite3_file *jfd;          /* File descriptor for main journal */
   661    661     sqlite3_file *sjfd;         /* File descriptor for sub-journal */
   662    662     i64 journalOff;             /* Current write offset in the journal file */
   663    663     i64 journalHdr;             /* Byte offset to previous journal header */
   664    664     sqlite3_backup *pBackup;    /* Pointer to list of ongoing backup processes */
   665    665     PagerSavepoint *aSavepoint; /* Array of active savepoints */
   666    666     int nSavepoint;             /* Number of elements in aSavepoint[] */
   667         -  u32 nReset;                 /* Number of calls to pager_reset() */
          667  +  u32 iDataVersion;           /* Changes whenever database content changes */
   668    668     char dbFileVers[16];        /* Changes whenever database file changes */
   669    669   
   670    670     int nMmapOut;               /* Number of mmap pages currently outstanding */
   671    671     sqlite3_int64 szMmap;       /* Desired maximum mmap size */
   672    672     PgHdr *pMmapFreelist;       /* List of free mmap page headers (pDirty) */
   673    673     /*
   674    674     ** End of the routinely-changing class members
................................................................................
  1678   1678     return rc;
  1679   1679   }
  1680   1680   
  1681   1681   /*
  1682   1682   ** Discard the entire contents of the in-memory page-cache.
  1683   1683   */
  1684   1684   static void pager_reset(Pager *pPager){
  1685         -  pPager->nReset++;
         1685  +  pPager->iDataVersion++;
  1686   1686     sqlite3BackupRestart(pPager->pBackup);
  1687   1687     sqlite3PcacheClear(pPager->pPCache);
  1688   1688   }
  1689   1689   
  1690   1690   /*
  1691         -** Return the pPager->nReset value
         1691  +** Return the pPager->iDataVersion value
  1692   1692   */
  1693   1693   u32 sqlite3PagerDataVersion(Pager *pPager){
  1694   1694     assert( pPager->eState>PAGER_OPEN );
  1695         -  return pPager->nReset;
         1695  +  return pPager->iDataVersion;
  1696   1696   }
  1697   1697   
  1698   1698   /*
  1699   1699   ** Free all structures in the Pager.aSavepoint[] array and set both
  1700   1700   ** Pager.aSavepoint and Pager.nSavepoint to zero. Close the sub-journal
  1701   1701   ** if it is open and the pager is not in exclusive mode.
  1702   1702   */
................................................................................
  6313   6313     ){
  6314   6314       assert( pPager->journalOff==JOURNAL_HDR_SZ(pPager) || !pPager->journalOff );
  6315   6315       pPager->eState = PAGER_READER;
  6316   6316       return SQLITE_OK;
  6317   6317     }
  6318   6318   
  6319   6319     PAGERTRACE(("COMMIT %d\n", PAGERID(pPager)));
         6320  +  pPager->iDataVersion++;
  6320   6321     rc = pager_end_transaction(pPager, pPager->setMaster, 1);
  6321   6322     return pager_error(pPager, rc);
  6322   6323   }
  6323   6324   
  6324   6325   /*
  6325   6326   ** If a write transaction is open, then all changes made within the 
  6326   6327   ** transaction are reverted and the current write-transaction is closed.

Changes to test/pragma3.test.

    19     19   do_execsql_test pragma3-100 {
    20     20     PRAGMA data_version;
    21     21   } {1}
    22     22   do_execsql_test pragma3-101 {
    23     23     PRAGMA temp.data_version;
    24     24   } {1}
    25     25   
    26         -# Writing is a no-op 
           26  +# Writing to the pragma is a no-op 
    27     27   do_execsql_test pragma3-102 {
    28     28     PRAGMA main.data_version=1234;
    29     29     PRAGMA main.data_version;
    30     30   } {1 1}
    31     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  +#
    32     48   do_execsql_test pragma3-110 {
    33     49     CREATE TABLE t1(a);
    34     50     INSERT INTO t1 VALUES(100),(200),(300);
    35     51     SELECT * FROM t1;
    36     52     PRAGMA data_version;
    37         -} {100 200 300 1}
           53  +} {100 200 300 3}
    38     54   
    39     55   sqlite3 db2 test.db
    40     56   do_test pragma3-120 {
    41     57     db2 eval {
    42     58       SELECT * FROM t1;
    43     59       PRAGMA data_version;
    44     60     }
    45     61   } {100 200 300 1}
    46     62   
    47     63   do_execsql_test pragma3-130 {
    48     64     INSERT INTO t1 VALUES(400),(500);
    49     65     SELECT * FROM t1;
    50     66     PRAGMA data_version;
    51         -} {100 200 300 400 500 1}
           67  +} {100 200 300 400 500 4}
    52     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  +#
    53     78   do_test pragma3-140 {
    54     79     db2 eval {
    55     80       SELECT * FROM t1;
    56     81       PRAGMA data_version;
    57     82       UPDATE t1 SET a=a+1;
    58     83       SELECT * FROM t1;
    59     84       PRAGMA data_version;
    60     85     }
    61         -} {100 200 300 400 500 2 101 201 301 401 501 2}
    62         -
           86  +} {100 200 300 400 500 2 101 201 301 401 501 3}
    63     87   do_execsql_test pragma3-150 {
    64     88     SELECT * FROM t1;
    65     89     PRAGMA data_version;
    66         -} {101 201 301 401 501 2}
           90  +} {101 201 301 401 501 5}
    67     91   
    68         -
           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}
    69    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  +  sqlite3_enable_shared_cache $::enable_shared_cache
          156  +}
          157  +
    70    158   finish_test