Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -8175,10 +8175,17 @@ ** is read-only, the others are read/write. ** ** The schema layer numbers meta values differently. At the schema ** layer (and the SetCookie and ReadCookie opcodes) the number of ** free pages is not visible. So Cookie[0] is the same as Meta[1]. +** +** This routine treats Meta[BTREE_DATA_VERSION] as a special case. Instead +** of reading the value out of the header, it instead loads the "DataVersion" +** from the pager. The BTREE_DATA_VERSION value is not actually stored in the +** database file. It is a number computed by the pager. But its access +** pattern is the same as header meta values, and so it is convenient to +** read it from this routine. */ void sqlite3BtreeGetMeta(Btree *p, int idx, u32 *pMeta){ BtShared *pBt = p->pBt; sqlite3BtreeEnter(p); @@ -8185,11 +8192,15 @@ assert( p->inTrans>TRANS_NONE ); assert( SQLITE_OK==querySharedCacheTableLock(p, MASTER_ROOT, READ_LOCK) ); assert( pBt->pPage1 ); assert( idx>=0 && idx<=15 ); - *pMeta = get4byte(&pBt->pPage1->aData[36 + idx*4]); + if( idx==BTREE_DATA_VERSION ){ + *pMeta = sqlite3PagerDataVersion(pBt->pPager); + }else{ + *pMeta = get4byte(&pBt->pPage1->aData[36 + idx*4]); + } /* If auto-vacuum is disabled in this build and this is an auto-vacuum ** database, mark the database as read-only. */ #ifdef SQLITE_OMIT_AUTOVACUUM if( idx==BTREE_LARGEST_ROOT_PAGE && *pMeta>0 ){ Index: src/btree.h ================================================================== --- src/btree.h +++ src/btree.h @@ -17,11 +17,11 @@ #define _BTREE_H_ /* TODO: This definition is just included so other modules compile. It ** needs to be revisited. */ -#define SQLITE_N_BTREE_META 10 +#define SQLITE_N_BTREE_META 16 /* ** If defined as non-zero, auto-vacuum is enabled by default. Otherwise ** it must be turned on for each database using "PRAGMA auto_vacuum = 1". */ @@ -132,10 +132,15 @@ ** offset = 36 + (idx * 4) ** ** For example, the free-page-count field is located at byte offset 36 of ** the database file header. The incr-vacuum-flag field is located at ** byte offset 64 (== 36+4*7). +** +** The BTREE_DATA_VERSION value is not really a value stored in the header. +** It is a read-only number computed by the pager. But we merge it with +** the header value access routines since its access pattern is the same. +** Call it a "virtual meta value". */ #define BTREE_FREE_PAGE_COUNT 0 #define BTREE_SCHEMA_VERSION 1 #define BTREE_FILE_FORMAT 2 #define BTREE_DEFAULT_CACHE_SIZE 3 @@ -142,10 +147,11 @@ #define BTREE_LARGEST_ROOT_PAGE 4 #define BTREE_TEXT_ENCODING 5 #define BTREE_USER_VERSION 6 #define BTREE_INCR_VACUUM 7 #define BTREE_APPLICATION_ID 8 +#define BTREE_DATA_VERSION 15 /* A virtual meta-value */ /* ** Values that may be OR'd together to form the second argument of an ** sqlite3BtreeCursorHints() call. */ Index: src/pager.c ================================================================== --- src/pager.c +++ src/pager.c @@ -644,10 +644,11 @@ u8 eLock; /* Current lock held on database file */ u8 changeCountDone; /* Set after incrementing the change-counter */ u8 setMaster; /* True if a m-j name has been written to jrnl */ u8 doNotSpill; /* Do not spill the cache when non-zero */ u8 subjInMemory; /* True to use in-memory sub-journals */ + u8 bUseFetch; /* True to use xFetch() */ Pgno dbSize; /* Number of pages in the database */ Pgno dbOrigSize; /* dbSize before the current transaction */ Pgno dbFileSize; /* Number of pages in the database file */ Pgno dbHintSize; /* Value passed to FCNTL_SIZE_HINT call */ int errCode; /* One of several kinds of errors */ @@ -661,13 +662,13 @@ i64 journalOff; /* Current write offset in the journal file */ i64 journalHdr; /* Byte offset to previous journal header */ sqlite3_backup *pBackup; /* Pointer to list of ongoing backup processes */ PagerSavepoint *aSavepoint; /* Array of active savepoints */ int nSavepoint; /* Number of elements in aSavepoint[] */ + u32 iDataVersion; /* Changes whenever database content changes */ char dbFileVers[16]; /* Changes whenever database file changes */ - u8 bUseFetch; /* True to use xFetch() */ int nMmapOut; /* Number of mmap pages currently outstanding */ sqlite3_int64 szMmap; /* Desired maximum mmap size */ PgHdr *pMmapFreelist; /* List of free mmap page headers (pDirty) */ /* ** End of the routinely-changing class members @@ -1679,13 +1680,22 @@ /* ** Discard the entire contents of the in-memory page-cache. */ static void pager_reset(Pager *pPager){ + pPager->iDataVersion++; sqlite3BackupRestart(pPager->pBackup); sqlite3PcacheClear(pPager->pPCache); } + +/* +** Return the pPager->iDataVersion value +*/ +u32 sqlite3PagerDataVersion(Pager *pPager){ + assert( pPager->eState>PAGER_OPEN ); + return pPager->iDataVersion; +} /* ** Free all structures in the Pager.aSavepoint[] array and set both ** Pager.aSavepoint and Pager.nSavepoint to zero. Close the sub-journal ** if it is open and the pager is not in exclusive mode. @@ -6305,10 +6315,11 @@ pPager->eState = PAGER_READER; return SQLITE_OK; } PAGERTRACE(("COMMIT %d\n", PAGERID(pPager))); + pPager->iDataVersion++; rc = pager_end_transaction(pPager, pPager->setMaster, 1); return pager_error(pPager, rc); } /* Index: src/pager.h ================================================================== --- src/pager.h +++ src/pager.h @@ -170,10 +170,11 @@ int sqlite3PagerWalFramesize(Pager *pPager); #endif /* Functions used to query pager state and configuration. */ u8 sqlite3PagerIsreadonly(Pager*); +u32 sqlite3PagerDataVersion(Pager*); int sqlite3PagerRefcount(Pager*); int sqlite3PagerMemUsed(Pager*); const char *sqlite3PagerFilename(Pager*, int); const sqlite3_vfs *sqlite3PagerVfs(Pager*); sqlite3_file *sqlite3PagerFile(Pager*); Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -152,10 +152,16 @@ { /* zName: */ "data_store_directory", /* ePragTyp: */ PragTyp_DATA_STORE_DIRECTORY, /* ePragFlag: */ 0, /* iArg: */ 0 }, #endif +#if !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS) + { /* zName: */ "data_version", + /* ePragTyp: */ PragTyp_HEADER_VALUE, + /* ePragFlag: */ PragFlag_ReadOnly, + /* iArg: */ BTREE_DATA_VERSION }, +#endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) { /* zName: */ "database_list", /* ePragTyp: */ PragTyp_DATABASE_LIST, /* ePragFlag: */ PragFlag_NeedSchema, /* iArg: */ 0 }, @@ -469,11 +475,11 @@ /* ePragTyp: */ PragTyp_FLAG, /* ePragFlag: */ 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; -/* Number of pragmas: 57 on by default, 70 total. */ +/* Number of pragmas: 58 on by default, 71 total. */ /* End of the automatically generated pragma table. ***************************************************************************/ /* ** Interpret the given string as a safety level. Return 0 for OFF, ADDED test/pragma3.test Index: test/pragma3.test ================================================================== --- /dev/null +++ test/pragma3.test @@ -0,0 +1,158 @@ +# 2014-12-19 +# +# 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. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# +# This file implements tests for PRAGMA data_version command. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test pragma3-100 { + PRAGMA data_version; +} {1} +do_execsql_test pragma3-101 { + PRAGMA temp.data_version; +} {1} + +# Writing to the pragma is a no-op +do_execsql_test pragma3-102 { + PRAGMA main.data_version=1234; + PRAGMA main.data_version; +} {1 1} + +# EVIDENCE-OF: R-27726-60934 The "PRAGMA data_version" command provides +# an indication that the database file has been modified. +# +# EVIDENCE-OF: R-30058-27547 The integer values returned by two +# invocations of "PRAGMA data_version" will be different if changes +# where committed to that database in between the two invocations. +# +# EVIDENCE-OF: R-10201-09349 The "PRAGMA data_version" command responses +# to changes committed by the same database connection, by database +# connections sharing a cache in shared cache mode, and by completely +# independent database connections including connections in separate +# threads and processes. +# +# In this test, it response to two separate changes on the same database +# connection. +# +do_execsql_test pragma3-110 { + CREATE TABLE t1(a); + INSERT INTO t1 VALUES(100),(200),(300); + SELECT * FROM t1; + PRAGMA data_version; +} {100 200 300 3} + +sqlite3 db2 test.db +do_test pragma3-120 { + db2 eval { + SELECT * FROM t1; + PRAGMA data_version; + } +} {100 200 300 1} + +do_execsql_test pragma3-130 { + INSERT INTO t1 VALUES(400),(500); + SELECT * FROM t1; + PRAGMA data_version; +} {100 200 300 400 500 4} + +# EVIDENCE-OF: R-10201-09349 The "PRAGMA data_version" command responses +# to changes committed by the same database connection, by database +# connections sharing a cache in shared cache mode, and by completely +# independent database connections including connections in separate +# threads and processes. +# +# In these test, it response to changes in a different database connection +# part of the same process. +# +do_test pragma3-140 { + db2 eval { + SELECT * FROM t1; + PRAGMA data_version; + UPDATE t1 SET a=a+1; + SELECT * FROM t1; + PRAGMA data_version; + } +} {100 200 300 400 500 2 101 201 301 401 501 3} +do_execsql_test pragma3-150 { + SELECT * FROM t1; + PRAGMA data_version; +} {101 201 301 401 501 5} + +# EVIDENCE-OF: R-10201-09349 The "PRAGMA data_version" command responses +# to changes committed by the same database connection, by database +# connections sharing a cache in shared cache mode, and by completely +# independent database connections including connections in separate +# threads and processes. +# +# This test verifies behavior when a separate process changes the database +# file. +# +do_test pragma3-200 { + set fd [open pragma3.txt wb] + puts $fd { + sqlite3 db test.db; + db eval {DELETE FROM t1 WHERE a>300}; + db close; + exit; + } + close $fd + exec [info nameofexec] pragma3.txt + forcedelete pragma3.txt + db eval { + PRAGMA data_version; + SELECT * FROM t1; + } +} {6 101 201} +db2 close +db close + +# EVIDENCE-OF: R-10201-09349 The "PRAGMA data_version" command responses +# to changes committed by the same database connection, by database +# connections sharing a cache in shared cache mode, and by completely +# independent database connections including connections in separate +# threads and processes. +# +# The next series of tests verifies the behavior for shared-cache +# database connections. +# +ifcapable shared_cache { + set ::enable_shared_cache [sqlite3_enable_shared_cache 1] + sqlite3 db test.db + sqlite3 db2 test.db + do_test pragma3-300 { + db eval { + PRAGMA data_version; + CREATE TABLE t3(a,b,c); + PRAGMA data_version; + } + } {1 2} + do_test pragma3-310 { + db2 eval { + PRAGMA data_version; + INSERT INTO t3(a,b,c) VALUES('abc','def','ghi'); + SELECT * FROM t3; + PRAGMA data_version; + } + } {2 abc def ghi 3} + do_test pragma3-320 { + db eval { + PRAGMA data_version; + SELECT * FROM t3; + } + } {3 abc def ghi} + db2 close + sqlite3_enable_shared_cache $::enable_shared_cache +} + +finish_test Index: tool/mkpragmatab.tcl ================================================================== --- tool/mkpragmatab.tcl +++ tool/mkpragmatab.tcl @@ -252,10 +252,16 @@ NAME: user_version TYPE: HEADER_VALUE ARG: BTREE_USER_VERSION IF: !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS) + NAME: data_version + TYPE: HEADER_VALUE + ARG: BTREE_DATA_VERSION + FLAG: ReadOnly + IF: !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS) + NAME: freelist_count TYPE: HEADER_VALUE ARG: BTREE_FREE_PAGE_COUNT FLAG: ReadOnly IF: !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)