1# 2015 December 7 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. The focus 12# of this file is the sqlite3_snapshot_xxx() APIs. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17ifcapable !snapshot {finish_test; return} 18set testprefix snapshot 19 20# This test does not work with the inmemory_journal permutation. The reason 21# is that each connection opened as part of this permutation executes 22# "PRAGMA journal_mode=memory", which fails if the database is in wal mode 23# and there are one or more existing connections. 24if {[permutation]=="inmemory_journal"} { 25 finish_test 26 return 27} 28 29foreach {tn tcl} { 30 1 { 31 proc snapshot_get {DB DBNAME} { 32 uplevel [list sqlite3_snapshot_get $DB $DBNAME] 33 } 34 proc snapshot_open {DB DBNAME SNAPSHOT} { 35 uplevel [list sqlite3_snapshot_open $DB $DBNAME $SNAPSHOT] 36 } 37 proc snapshot_free {SNAPSHOT} { 38 uplevel [list sqlite3_snapshot_free $SNAPSHOT] 39 } 40 proc snapshot_cmp {SNAPSHOT1 SNAPSHOT2} { 41 uplevel [list sqlite3_snapshot_cmp $SNAPSHOT1 $SNAPSHOT2] 42 } 43 } 44 45 2 { 46 proc snapshot_get {DB DBNAME} { 47 uplevel [list sqlite3_snapshot_get_blob $DB $DBNAME] 48 } 49 proc snapshot_open {DB DBNAME SNAPSHOT} { 50 uplevel [list sqlite3_snapshot_open_blob $DB $DBNAME $SNAPSHOT] 51 } 52 proc snapshot_free {SNAPSHOT} { 53 } 54 proc snapshot_cmp {SNAPSHOT1 SNAPSHOT2} { 55 uplevel [list sqlite3_snapshot_cmp_blob $SNAPSHOT1 $SNAPSHOT2] 56 } 57 } 58} { 59 60 reset_db 61 eval $tcl 62 63 #------------------------------------------------------------------------- 64 # Check some error conditions in snapshot_get(). It is an error if: 65 # 66 # 1) snapshot_get() is called on a non-WAL database, or 67 # 2) there is an open write transaction on the database. 68 # 3) the database handle is in auto-commit mode 69 # 70 do_execsql_test $tn.1.0 { 71 CREATE TABLE t1(a, b); 72 INSERT INTO t1 VALUES(1, 2); 73 INSERT INTO t1 VALUES(3, 4); 74 } 75 76 do_test $tn.1.1.1 { 77 execsql { BEGIN; SELECT * FROM t1; } 78 list [catch { snapshot_get db main } msg] $msg 79 } {1 SQLITE_ERROR} 80 do_execsql_test $tn.1.1.2 COMMIT 81 82 do_test $tn.1.2.1 { 83 execsql { 84 PRAGMA journal_mode = WAL; 85 BEGIN; 86 INSERT INTO t1 VALUES(5, 6); 87 INSERT INTO t1 VALUES(7, 8); 88 } 89 list [catch { snapshot_get db main } msg] $msg 90 } {1 SQLITE_ERROR} 91 do_execsql_test $tn.1.2.2 COMMIT 92 93 do_test $tn.1.3.1 { 94 list [catch { snapshot_get db main } msg] $msg 95 } {1 SQLITE_ERROR} 96 do_test $tn.1.3.2 { 97 db trans { set snap [snapshot_get db main] } 98 snapshot_free $snap 99 } {} 100 101 #------------------------------------------------------------------------- 102 # Check that a simple case works. Reuse the database created by the 103 # block of tests above. 104 # 105 do_execsql_test $tn.2.1.0 { 106 BEGIN; 107 SELECT * FROM t1; 108 } {1 2 3 4 5 6 7 8} 109 110 do_test $tn.2.1.1 { 111 set snapshot [snapshot_get db main] 112 execsql { 113 COMMIT; 114 INSERT INTO t1 VALUES(9, 10); 115 SELECT * FROM t1; 116 } 117 } {1 2 3 4 5 6 7 8 9 10} 118 119 do_test $tn.2.1.2 { 120 execsql BEGIN 121 snapshot_open db main $snapshot 122 execsql { 123 SELECT * FROM t1; 124 } 125 } {1 2 3 4 5 6 7 8} 126 127 do_test $tn.2.1.3 { 128 snapshot_free $snapshot 129 execsql COMMIT 130 } {} 131 132 do_test $tn.2.2.0 { 133 sqlite3 db2 test.db 134 execsql { 135 BEGIN; 136 SELECT * FROM t1; 137 } db2 138 } {1 2 3 4 5 6 7 8 9 10} 139 140 do_test $tn.2.2.1 { 141 set snapshot [snapshot_get db2 main] 142 execsql { 143 INSERT INTO t1 VALUES(11, 12); 144 SELECT * FROM t1; 145 } 146 } {1 2 3 4 5 6 7 8 9 10 11 12} 147 148 do_test $tn.2.2.2 { 149 execsql BEGIN 150 snapshot_open db main $snapshot 151 execsql { 152 SELECT * FROM t1; 153 } 154 } {1 2 3 4 5 6 7 8 9 10} 155 156 do_test $tn.2.2.3 { 157 snapshot_free $snapshot 158 execsql COMMIT 159 execsql COMMIT db2 160 db2 close 161 } {} 162 163 do_test $tn.2.3.1 { 164 execsql { DELETE FROM t1 WHERE a>6 } 165 db trans { set snapshot [snapshot_get db main] } 166 execsql { 167 INSERT INTO t1 VALUES('a', 'b'); 168 INSERT INTO t1 VALUES('c', 'd'); 169 SELECT * FROM t1; 170 } 171 } {1 2 3 4 5 6 a b c d} 172 do_test $tn.2.3.2 { 173 execsql BEGIN 174 snapshot_open db main $snapshot 175 execsql { SELECT * FROM t1 } 176 } {1 2 3 4 5 6} 177 178 do_test $tn.2.3.3 { 179 catchsql { 180 INSERT INTO t1 VALUES('x','y') 181 } 182 } {1 {database is locked}} 183 do_test $tn.2.3.4 { 184 execsql COMMIT 185 snapshot_free $snapshot 186 } {} 187 188 #------------------------------------------------------------------------- 189 # Check some errors in snapshot_open(). It is an error if: 190 # 191 # 1) the db is in auto-commit mode, 192 # 2) the db has an open (read or write) transaction, 193 # 3) the db is not a wal database, 194 # 195 # Reuse the database created by earlier tests. 196 # 197 do_execsql_test $tn.3.0.0 { 198 CREATE TABLE t2(x, y); 199 INSERT INTO t2 VALUES('a', 'b'); 200 INSERT INTO t2 VALUES('c', 'd'); 201 BEGIN; 202 SELECT * FROM t2; 203 } {a b c d} 204 do_test $tn.3.0.1 { 205 set snapshot [snapshot_get db main] 206 execsql { COMMIT } 207 execsql { INSERT INTO t2 VALUES('e', 'f'); } 208 } {} 209 210 do_test $tn.3.1 { 211 list [catch {snapshot_open db main $snapshot } msg] $msg 212 } {1 SQLITE_ERROR} 213 214 do_test $tn.3.2.1 { 215 execsql { 216 BEGIN; 217 SELECT * FROM t2; 218 } 219 } {a b c d e f} 220 do_test $tn.3.2.2 { 221 list [catch {snapshot_open db main $snapshot } msg] $msg 222 } {1 SQLITE_ERROR} 223 224 do_test $tn.3.2.3 { 225 execsql { 226 COMMIT; 227 BEGIN; 228 INSERT INTO t2 VALUES('g', 'h'); 229 } 230 list [catch {snapshot_open db main $snapshot } msg] $msg 231 } {1 SQLITE_ERROR} 232 do_execsql_test $tn.3.2.4 COMMIT 233 234 do_test $tn.3.3.1 { 235 execsql { PRAGMA journal_mode = DELETE } 236 execsql { BEGIN } 237 list [catch {snapshot_open db main $snapshot } msg] $msg 238 } {1 SQLITE_ERROR} 239 240 do_test $tn.$tn.3.3.2 { 241 snapshot_free $snapshot 242 execsql COMMIT 243 } {} 244 245 #------------------------------------------------------------------------- 246 # Check that SQLITE_BUSY_SNAPSHOT is returned if the specified snapshot 247 # no longer exists because the wal file has been checkpointed. 248 # 249 # 1. Reading a snapshot from the middle of a wal file is not possible 250 # after the wal file has been checkpointed. 251 # 252 # 2. That a snapshot from the end of a wal file can not be read once 253 # the wal file has been wrapped. 254 # 255 do_execsql_test $tn.4.1.0 { 256 PRAGMA journal_mode = wal; 257 CREATE TABLE t3(i, j); 258 INSERT INTO t3 VALUES('o', 't'); 259 INSERT INTO t3 VALUES('t', 'f'); 260 BEGIN; 261 SELECT * FROM t3; 262 } {wal o t t f} 263 264 do_test $tn.4.1.1 { 265 set snapshot [snapshot_get db main] 266 execsql COMMIT 267 } {} 268 do_test $tn.4.1.2 { 269 execsql { 270 INSERT INTO t3 VALUES('f', 's'); 271 BEGIN; 272 } 273 snapshot_open db main $snapshot 274 execsql { SELECT * FROM t3 } 275 } {o t t f} 276 277 do_test $tn.4.1.3 { 278 execsql { 279 COMMIT; 280 PRAGMA wal_checkpoint; 281 BEGIN; 282 } 283 list [catch {snapshot_open db main $snapshot} msg] $msg 284 } {1 SQLITE_BUSY_SNAPSHOT} 285 do_test $tn.4.1.4 { 286 snapshot_free $snapshot 287 execsql COMMIT 288 } {} 289 290 do_test $tn.4.2.1 { 291 execsql { 292 INSERT INTO t3 VALUES('s', 'e'); 293 INSERT INTO t3 VALUES('n', 't'); 294 BEGIN; 295 SELECT * FROM t3; 296 } 297 } {o t t f f s s e n t} 298 do_test $tn.4.2.2 { 299 set snapshot [snapshot_get db main] 300 execsql { 301 COMMIT; 302 PRAGMA wal_checkpoint; 303 BEGIN; 304 } 305 snapshot_open db main $snapshot 306 execsql { SELECT * FROM t3 } 307 } {o t t f f s s e n t} 308 do_test $tn.4.2.3 { 309 execsql { 310 COMMIT; 311 INSERT INTO t3 VALUES('e', 't'); 312 BEGIN; 313 } 314 list [catch {snapshot_open db main $snapshot} msg] $msg 315 } {1 SQLITE_BUSY_SNAPSHOT} 316 do_test $tn.4.2.4 { 317 snapshot_free $snapshot 318 } {} 319 320 #------------------------------------------------------------------------- 321 # Check that SQLITE_BUSY is returned if a checkpoint is running when 322 # sqlite3_snapshot_open() is called. 323 # 324 reset_db 325 db close 326 testvfs tvfs 327 sqlite3 db test.db -vfs tvfs 328 329 do_execsql_test $tn.5.1 { 330 PRAGMA journal_mode = wal; 331 CREATE TABLE x1(x, xx, xxx); 332 INSERT INTO x1 VALUES('z', 'zz', 'zzz'); 333 BEGIN; 334 SELECT * FROM x1; 335 } {wal z zz zzz} 336 337 do_test $tn.5.2 { 338 set ::snapshot [snapshot_get db main] 339 sqlite3 db2 test.db -vfs tvfs 340 execsql { 341 INSERT INTO x1 VALUES('a', 'aa', 'aaa'); 342 COMMIT; 343 } 344 } {} 345 346 set t53 0 347 proc write_callback {args} { 348 do_test $tn.5.3.[incr ::t53] { 349 execsql BEGIN 350 list [catch { snapshot_open db main $::snapshot } msg] $msg 351 } {1 SQLITE_BUSY} 352 catchsql COMMIT 353 } 354 355 tvfs filter xWrite 356 tvfs script write_callback 357 db2 eval { PRAGMA wal_checkpoint } 358 db close 359 db2 close 360 tvfs delete 361 snapshot_free $snapshot 362 363 #------------------------------------------------------------------------- 364 # Test that sqlite3_snapshot_get() may be called immediately after 365 # "BEGIN; PRAGMA user_version;". And that sqlite3_snapshot_open() may 366 # be called after opening the db handle and running the script 367 # "PRAGMA user_version; BEGIN". 368 reset_db 369 do_execsql_test $tn.6.1 { 370 PRAGMA journal_mode = wal; 371 CREATE TABLE x1(x, xx, xxx); 372 INSERT INTO x1 VALUES('z', 'zz', 'zzz'); 373 BEGIN; 374 PRAGMA user_version; 375 } {wal 0} 376 do_test $tn.6.2 { 377 set ::snapshot [snapshot_get db main] 378 execsql { 379 INSERT INTO x1 VALUES('a', 'aa', 'aaa'); 380 COMMIT; 381 } 382 } {} 383 do_test $tn.6.3 { 384 sqlite3 db2 test.db 385 db2 eval "PRAGMA user_version ; BEGIN" 386 snapshot_open db2 main $::snapshot 387 db2 eval { SELECT * FROM x1 } 388 } {z zz zzz} 389 do_test $tn.6.4 { 390 db2 close 391 sqlite3 db2 test.db 392 db2 eval "PRAGMA application_id" 393 db2 eval "BEGIN" 394 snapshot_open db2 main $::snapshot 395 db2 eval { SELECT * FROM x1 } 396 } {z zz zzz} 397 398 do_test $tn.6.5 { 399 db2 close 400 sqlite3 db2 test.db 401 db2 eval "BEGIN" 402 list [catch {snapshot_open db2 main $::snapshot} msg] $msg 403 } {1 SQLITE_ERROR} 404 405 snapshot_free $snapshot 406 407 #------------------------------------------------------------------------- 408 # The following tests investigate the sqlite3_snapshot_cmp() API. 409 # 410 411 # Compare snapshots $p1 and $p2, checking that the result is $r. 412 # 413 proc do_snapshot_cmp_test {tn p1 p2 r} { 414 uplevel [list do_test $tn.1 [list snapshot_cmp $p1 $p2] $r] 415 uplevel [list do_test $tn.2 [list snapshot_cmp $p2 $p1] [expr $r*-1]] 416 uplevel [list do_test $tn.3 [list snapshot_cmp $p1 $p1] 0] 417 uplevel [list do_test $tn.4 [list snapshot_cmp $p2 $p2] 0] 418 } 419 420 catch { db2 close } 421 reset_db 422 423 do_execsql_test $tn.7.1 { 424 PRAGMA journal_mode = wal; 425 CREATE TABLE t1(x); 426 } wal 427 428 do_test $tn.7.1.2 { 429 execsql { BEGIN ; PRAGMA application_id } 430 set p1 [snapshot_get db main] 431 execsql { 432 INSERT INTO t1 VALUES(10); 433 COMMIT; 434 } 435 execsql { BEGIN ; PRAGMA application_id } 436 set p2 [snapshot_get db main] 437 execsql COMMIT 438 } {} 439 440 do_snapshot_cmp_test $tn.7.1.3 $p1 $p2 -1 441 snapshot_free $p1 442 snapshot_free $p2 443 444 do_execsql_test $tn.7.2.1 { 445 INSERT INTO t1 VALUES(11); 446 INSERT INTO t1 VALUES(12); 447 INSERT INTO t1 VALUES(13); 448 BEGIN; 449 PRAGMA application_id; 450 } {0} 451 do_test $tn.7.2.2 { 452 set p1 [snapshot_get db main] 453 execsql { 454 COMMIT; 455 INSERT INTO t1 VALUES(14); 456 PRAGMA wal_checkpoint; 457 BEGIN; 458 PRAGMA application_id; 459 } 460 set p2 [snapshot_get db main] 461 execsql COMMIT 462 } {} 463 464 do_snapshot_cmp_test $tn.7.2.3 $p1 $p2 -1 465 snapshot_free $p2 466 467 do_test $tn.7.3.1 { 468 execsql { 469 INSERT INTO t1 VALUES(14); 470 BEGIN; 471 PRAGMA application_id; 472 } 473 set p2 [snapshot_get db main] 474 execsql COMMIT 475 } {} 476 477 do_snapshot_cmp_test $tn.7.3.2 $p1 $p2 -1 478 snapshot_free $p1 479 snapshot_free $p2 480} 481 482finish_test 483