/ Check-in [44ee5383]
Login
Home Timeline Branches Tags Tickets Wiki More...

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 Unified Diffs Ignore Whitespace Patch

Changes to src/pager.c. 

660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
....
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
....
6313
6314
6315
6316
6317
6318
6319

6320
6321
6322
6323
6324
6325
6326

  sqlite3_file *jfd;          /* File descriptor for main journal */
  sqlite3_file *sjfd;         /* File descriptor for sub-journal */
  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 nReset;                 /* Number of calls to pager_reset() */
  char dbFileVers[16];        /* Changes whenever database file changes */

  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
................................................................................
  return rc;
}

/*
** Discard the entire contents of the in-memory page-cache.
*/
static void pager_reset(Pager *pPager){
  pPager->nReset++;
  sqlite3BackupRestart(pPager->pBackup);
  sqlite3PcacheClear(pPager->pPCache);
}

/*
** Return the pPager->nReset value
*/
u32 sqlite3PagerDataVersion(Pager *pPager){
  assert( pPager->eState>PAGER_OPEN );
  return pPager->nReset;
}

/*
** 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.
*/
................................................................................
  ){
    assert( pPager->journalOff==JOURNAL_HDR_SZ(pPager) || !pPager->journalOff );
    pPager->eState = PAGER_READER;
    return SQLITE_OK;
  }

  PAGERTRACE(("COMMIT %d\n", PAGERID(pPager)));

  rc = pager_end_transaction(pPager, pPager->setMaster, 1);
  return pager_error(pPager, rc);
}

/*
** If a write transaction is open, then all changes made within the 
** transaction are reverted and the current write-transaction is closed.








|







 







|





|



|







 







>








660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
....
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
....
6313
6314
6315
6316
6317
6318
6319
6320
6321
6322
6323
6324
6325
6326
6327

  sqlite3_file *jfd;          /* File descriptor for main journal */
  sqlite3_file *sjfd;         /* File descriptor for sub-journal */
  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 */

  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
................................................................................
  return rc;
}

/*
** 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.
*/
................................................................................
  ){
    assert( pPager->journalOff==JOURNAL_HDR_SZ(pPager) || !pPager->journalOff );
    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);
}

/*
** If a write transaction is open, then all changes made within the 
** transaction are reverted and the current write-transaction is closed.

Changes to test/pragma3.test. 

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

do_execsql_test pragma3-100 {
  PRAGMA data_version;
} {1}
do_execsql_test pragma3-101 {
  PRAGMA temp.data_version;
} {1}

# Writing is a no-op 
do_execsql_test pragma3-102 {
  PRAGMA main.data_version=1234;
  PRAGMA main.data_version;
} {1 1}

















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 1}

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 1}










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 2}

do_execsql_test pragma3-150 {
  SELECT * FROM t1;
  PRAGMA data_version;
} {101 201 301 401 501 2}


























db2 close








































finish_test








|





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





|













|

>
>
>
>
>
>
>
>
>








|
<



|

>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

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


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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158

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

This page was generated in about 0.015s by Fossil 2.11 [ce0bce90cf] 2020-02-25 15:59:11