gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/pragma3.test (about) 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 if {[sqlite3 -has-codec]} { 20 finish_test 21 return 22 } 23 24 do_execsql_test pragma3-100 { 25 PRAGMA data_version; 26 } {1} 27 do_execsql_test pragma3-101 { 28 PRAGMA temp.data_version; 29 } {1} 30 31 # Writing to the pragma is a no-op 32 do_execsql_test pragma3-102 { 33 PRAGMA main.data_version=1234; 34 PRAGMA main.data_version; 35 } {1 1} 36 37 # EVIDENCE-OF: R-27726-60934 The "PRAGMA data_version" command provides 38 # an indication that the database file has been modified. 39 # 40 # EVIDENCE-OF: R-47505-58569 The "PRAGMA data_version" value is 41 # unchanged for commits made on the same database connection. 42 # 43 do_execsql_test pragma3-110 { 44 PRAGMA data_version; 45 BEGIN IMMEDIATE; 46 PRAGMA data_version; 47 CREATE TABLE t1(a); 48 INSERT INTO t1 VALUES(100),(200),(300); 49 PRAGMA data_version; 50 COMMIT; 51 SELECT * FROM t1; 52 PRAGMA data_version; 53 } {1 1 1 100 200 300 1} 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 PRAGMA data_version; 65 BEGIN IMMEDIATE; 66 PRAGMA data_version; 67 INSERT INTO t1 VALUES(400),(500); 68 PRAGMA data_version; 69 COMMIT; 70 SELECT * FROM t1; 71 PRAGMA data_version; 72 PRAGMA shrink_memory; 73 } {1 1 1 100 200 300 400 500 1} 74 75 # EVIDENCE-OF: R-63005-41812 The integer values returned by two 76 # invocations of "PRAGMA data_version" from the same connection will be 77 # different if changes were committed to the database by any other 78 # connection in the interim. 79 # 80 # Value went from 1 in pragma3-120 to 2 here. 81 # 82 do_test pragma3-140 { 83 db2 eval { 84 SELECT * FROM t1; 85 PRAGMA data_version; 86 BEGIN IMMEDIATE; 87 PRAGMA data_version; 88 UPDATE t1 SET a=a+1; 89 COMMIT; 90 SELECT * FROM t1; 91 PRAGMA data_version; 92 } 93 } {100 200 300 400 500 2 2 101 201 301 401 501 2} 94 do_execsql_test pragma3-150 { 95 SELECT * FROM t1; 96 PRAGMA data_version; 97 } {101 201 301 401 501 2} 98 99 # 100 do_test pragma3-160 { 101 db eval { 102 BEGIN; 103 PRAGMA data_version; 104 UPDATE t1 SET a=555 WHERE a=501; 105 PRAGMA data_version; 106 SELECT * FROM t1 ORDER BY a; 107 PRAGMA data_version; 108 } 109 } {2 2 101 201 301 401 555 2} 110 do_test pragma3-170 { 111 db2 eval { 112 PRAGMA data_version; 113 } 114 } {2} 115 do_test pragma3-180 { 116 db eval { 117 COMMIT; 118 PRAGMA data_version; 119 } 120 } {2} 121 do_test pragma3-190 { 122 db2 eval { 123 PRAGMA data_version; 124 } 125 } {3} 126 127 # EVIDENCE-OF: R-19326-44825 The "PRAGMA data_version" value is a local 128 # property of each database connection and so values returned by two 129 # concurrent invocations of "PRAGMA data_version" on separate database 130 # connections are often different even though the underlying database is 131 # identical. 132 # 133 do_test pragma3-195 { 134 expr {[db eval {PRAGMA data_version}]!=[db2 eval {PRAGMA data_version}]} 135 } {1} 136 137 # EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is 138 # the same for all database connections, including database connections 139 # in separate processes and shared cache database connections. 140 # 141 # The next block checks the behavior for separate processes. 142 # 143 do_test pragma3-200 { 144 db eval {PRAGMA data_version; SELECT * FROM t1;} 145 } {2 101 201 301 401 555} 146 do_test pragma3-201 { 147 set fd [open pragma3.txt wb] 148 puts $fd { 149 sqlite3 db test.db; 150 db eval {DELETE FROM t1 WHERE a>300}; 151 db close; 152 exit; 153 } 154 close $fd 155 exec [info nameofexec] pragma3.txt 156 forcedelete pragma3.txt 157 db eval { 158 PRAGMA data_version; 159 SELECT * FROM t1; 160 } 161 } {3 101 201} 162 db2 close 163 db close 164 165 # EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is 166 # the same for all database connections, including database connections 167 # in separate processes and shared cache database connections. 168 # 169 # The next block checks that behavior is the same for shared-cache. 170 # 171 ifcapable shared_cache { 172 set ::enable_shared_cache [sqlite3_enable_shared_cache 1] 173 sqlite3 db test.db 174 sqlite3 db2 test.db 175 do_test pragma3-300 { 176 db eval { 177 PRAGMA data_version; 178 BEGIN; 179 CREATE TABLE t3(a,b,c); 180 CREATE TABLE t4(x,y,z); 181 INSERT INTO t4 VALUES(123,456,789); 182 PRAGMA data_version; 183 COMMIT; 184 PRAGMA data_version; 185 } 186 } {1 1 1} 187 do_test pragma3-310 { 188 db2 eval { 189 PRAGMA data_version; 190 BEGIN; 191 INSERT INTO t3(a,b,c) VALUES('abc','def','ghi'); 192 SELECT * FROM t3; 193 PRAGMA data_version; 194 } 195 } {2 abc def ghi 2} 196 # The transaction in db2 has not yet committed, so the data_version in 197 # db is unchanged. 198 do_test pragma3-320 { 199 db eval { 200 PRAGMA data_version; 201 SELECT * FROM t4; 202 } 203 } {1 123 456 789} 204 do_test pragma3-330 { 205 db2 eval { 206 COMMIT; 207 PRAGMA data_version; 208 SELECT * FROM t4; 209 } 210 } {2 123 456 789} 211 do_test pragma3-340 { 212 db eval { 213 PRAGMA data_version; 214 SELECT * FROM t3; 215 SELECT * FROM t4; 216 } 217 } {2 abc def ghi 123 456 789} 218 db2 close 219 db close 220 sqlite3_enable_shared_cache $::enable_shared_cache 221 } 222 223 # Make sure this also works in WAL mode 224 # 225 # This will not work with the in-memory journal permutation, as opening 226 # [db2] switches the journal mode back to "memory" 227 # 228 if {[wal_is_capable]} { 229 if {[permutation]!="inmemory_journal"} { 230 231 sqlite3 db test.db 232 db eval {PRAGMA journal_mode=WAL} 233 sqlite3 db2 test.db 234 do_test pragma3-400 { 235 db eval { 236 PRAGMA data_version; 237 PRAGMA journal_mode; 238 SELECT * FROM t1; 239 } 240 } {2 wal 101 201} 241 do_test pragma3-410 { 242 db2 eval { 243 PRAGMA data_version; 244 PRAGMA journal_mode; 245 SELECT * FROM t1; 246 } 247 } {2 wal 101 201} 248 do_test pragma3-420 { 249 db eval {UPDATE t1 SET a=111*(a/100); PRAGMA data_version; SELECT * FROM t1} 250 } {2 111 222} 251 do_test pragma3-430 { 252 db2 eval {PRAGMA data_version; SELECT * FROM t1;} 253 } {3 111 222} 254 db2 close 255 } 256 } 257 258 #------------------------------------------------------------------------- 259 # Check that empty write transactions do not cause the return of "PRAGMA 260 # data_version" to be decremented with journal_mode=PERSIST and 261 # locking_mode=EXCLUSIVE 262 # 263 foreach {tn sql} { 264 A { 265 } 266 B { 267 PRAGMA journal_mode = PERSIST; 268 PRAGMA locking_mode = EXCLUSIVE; 269 } 270 } { 271 reset_db 272 execsql $sql 273 274 do_execsql_test pragma3-510$tn { 275 CREATE TABLE t1(x, y); 276 INSERT INTO t1 VALUES(1, 2); 277 PRAGMA data_version; 278 } {1} 279 280 do_execsql_test pragma3-520$tn { 281 BEGIN EXCLUSIVE; 282 COMMIT; 283 PRAGMA data_version; 284 } {1} 285 } 286 287 finish_test