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 221 # Update - it is no longer an error to have a read-transaction open, 222 # provided there are no active SELECT statements. 223 do_test $tn.3.2.2a { 224 db eval "SELECT * FROM t2" { 225 set res [list [catch {snapshot_open db main $snapshot } msg] $msg] 226 break 227 } 228 set res 229 } {1 SQLITE_ERROR} 230 do_test $tn.3.2.2b { 231 snapshot_open db main $snapshot 232 } {} 233 234 do_test $tn.3.2.3 { 235 execsql { 236 COMMIT; 237 BEGIN; 238 INSERT INTO t2 VALUES('g', 'h'); 239 } 240 list [catch {snapshot_open db main $snapshot } msg] $msg 241 } {1 SQLITE_ERROR} 242 do_execsql_test $tn.3.2.4 COMMIT 243 244 do_test $tn.3.3.1a { 245 execsql { PRAGMA journal_mode = DELETE } 246 execsql { BEGIN } 247 list [catch {snapshot_open db main $snapshot } msg] $msg 248 } {1 SQLITE_ERROR} 249 250 do_test $tn.3.3.1b { 251 execsql { COMMIT ; BEGIN ; SELECT * FROM t2 } 252 list [catch {snapshot_open db main $snapshot } msg] $msg 253 } {1 SQLITE_ERROR} 254 255 do_test $tn.$tn.3.3.2 { 256 snapshot_free $snapshot 257 execsql COMMIT 258 } {} 259 260 #------------------------------------------------------------------------- 261 # Check that SQLITE_ERROR_SNAPSHOT is returned if the specified snapshot 262 # no longer exists because the wal file has been checkpointed. 263 # 264 # 1. Reading a snapshot from the middle of a wal file is not possible 265 # after the wal file has been checkpointed. 266 # 267 # 2. That a snapshot from the end of a wal file can not be read once 268 # the wal file has been wrapped. 269 # 270 do_execsql_test $tn.4.1.0 { 271 PRAGMA journal_mode = wal; 272 CREATE TABLE t3(i, j); 273 INSERT INTO t3 VALUES('o', 't'); 274 INSERT INTO t3 VALUES('t', 'f'); 275 BEGIN; 276 SELECT * FROM t3; 277 } {wal o t t f} 278 279 do_test $tn.4.1.1 { 280 set snapshot [snapshot_get db main] 281 execsql COMMIT 282 } {} 283 do_test $tn.4.1.2 { 284 execsql { 285 INSERT INTO t3 VALUES('f', 's'); 286 BEGIN; 287 } 288 snapshot_open db main $snapshot 289 execsql { SELECT * FROM t3 } 290 } {o t t f} 291 292 do_test $tn.4.1.3 { 293 execsql { 294 COMMIT; 295 PRAGMA wal_checkpoint; 296 BEGIN; 297 } 298 list [catch {snapshot_open db main $snapshot} msg] $msg 299 } {1 SQLITE_ERROR_SNAPSHOT} 300 do_test $tn.4.1.4 { 301 snapshot_free $snapshot 302 execsql COMMIT 303 } {} 304 305 do_test $tn.4.2.1 { 306 execsql { 307 INSERT INTO t3 VALUES('s', 'e'); 308 INSERT INTO t3 VALUES('n', 't'); 309 BEGIN; 310 SELECT * FROM t3; 311 } 312 } {o t t f f s s e n t} 313 do_test $tn.4.2.2 { 314 set snapshot [snapshot_get db main] 315 execsql { 316 COMMIT; 317 PRAGMA wal_checkpoint; 318 BEGIN; 319 } 320 snapshot_open db main $snapshot 321 execsql { SELECT * FROM t3 } 322 } {o t t f f s s e n t} 323 do_test $tn.4.2.3 { 324 execsql { 325 COMMIT; 326 INSERT INTO t3 VALUES('e', 't'); 327 BEGIN; 328 } 329 list [catch {snapshot_open db main $snapshot} msg] $msg 330 } {1 SQLITE_ERROR_SNAPSHOT} 331 do_test $tn.4.2.4 { 332 snapshot_free $snapshot 333 } {} 334 335 #------------------------------------------------------------------------- 336 # Check that SQLITE_BUSY is returned if a checkpoint is running when 337 # sqlite3_snapshot_open() is called. 338 # 339 reset_db 340 db close 341 testvfs tvfs 342 sqlite3 db test.db -vfs tvfs 343 344 do_execsql_test $tn.5.1 { 345 PRAGMA journal_mode = wal; 346 CREATE TABLE x1(x, xx, xxx); 347 INSERT INTO x1 VALUES('z', 'zz', 'zzz'); 348 BEGIN; 349 SELECT * FROM x1; 350 } {wal z zz zzz} 351 352 do_test $tn.5.2 { 353 set ::snapshot [snapshot_get db main] 354 sqlite3 db2 test.db -vfs tvfs 355 execsql { 356 INSERT INTO x1 VALUES('a', 'aa', 'aaa'); 357 COMMIT; 358 } 359 } {} 360 361 set t53 0 362 proc write_callback {args} { 363 do_test $tn.5.3.[incr ::t53] { 364 execsql BEGIN 365 list [catch { snapshot_open db main $::snapshot } msg] $msg 366 } {1 SQLITE_BUSY} 367 catchsql COMMIT 368 } 369 370 tvfs filter xWrite 371 tvfs script write_callback 372 db2 eval { PRAGMA wal_checkpoint } 373 db close 374 db2 close 375 tvfs delete 376 snapshot_free $snapshot 377 378 #------------------------------------------------------------------------- 379 # Test that sqlite3_snapshot_get() may be called immediately after 380 # "BEGIN; PRAGMA user_version;". And that sqlite3_snapshot_open() may 381 # be called after opening the db handle and running the script 382 # "PRAGMA user_version; BEGIN". 383 reset_db 384 do_execsql_test $tn.6.1 { 385 PRAGMA journal_mode = wal; 386 CREATE TABLE x1(x, xx, xxx); 387 INSERT INTO x1 VALUES('z', 'zz', 'zzz'); 388 BEGIN; 389 PRAGMA user_version; 390 } {wal 0} 391 do_test $tn.6.2 { 392 set ::snapshot [snapshot_get db main] 393 execsql { 394 INSERT INTO x1 VALUES('a', 'aa', 'aaa'); 395 COMMIT; 396 } 397 } {} 398 do_test $tn.6.3 { 399 sqlite3 db2 test.db 400 db2 eval "PRAGMA user_version ; BEGIN" 401 snapshot_open db2 main $::snapshot 402 db2 eval { SELECT * FROM x1 } 403 } {z zz zzz} 404 do_test $tn.6.4 { 405 db2 close 406 sqlite3 db2 test.db 407 db2 eval "PRAGMA application_id" 408 db2 eval "BEGIN" 409 snapshot_open db2 main $::snapshot 410 db2 eval { SELECT * FROM x1 } 411 } {z zz zzz} 412 413 do_test $tn.6.5 { 414 db2 close 415 sqlite3 db2 test.db 416 db2 eval "BEGIN" 417 list [catch {snapshot_open db2 main $::snapshot} msg] $msg 418 } {1 SQLITE_ERROR} 419 420 snapshot_free $snapshot 421 422 #------------------------------------------------------------------------- 423 # The following tests investigate the sqlite3_snapshot_cmp() API. 424 # 425 426 # Compare snapshots $p1 and $p2, checking that the result is $r. 427 # 428 proc do_snapshot_cmp_test {tn p1 p2 r} { 429 uplevel [list do_test $tn.1 [list snapshot_cmp $p1 $p2] $r] 430 uplevel [list do_test $tn.2 [list snapshot_cmp $p2 $p1] [expr $r*-1]] 431 uplevel [list do_test $tn.3 [list snapshot_cmp $p1 $p1] 0] 432 uplevel [list do_test $tn.4 [list snapshot_cmp $p2 $p2] 0] 433 } 434 435 catch { db2 close } 436 reset_db 437 438 do_execsql_test $tn.7.1 { 439 PRAGMA journal_mode = wal; 440 CREATE TABLE t1(x); 441 } wal 442 443 do_test $tn.7.1.2 { 444 execsql { BEGIN ; PRAGMA application_id } 445 set p1 [snapshot_get db main] 446 execsql { 447 INSERT INTO t1 VALUES(10); 448 COMMIT; 449 } 450 execsql { BEGIN ; PRAGMA application_id } 451 set p2 [snapshot_get db main] 452 execsql COMMIT 453 } {} 454 455 do_snapshot_cmp_test $tn.7.1.3 $p1 $p2 -1 456 snapshot_free $p1 457 snapshot_free $p2 458 459 do_execsql_test $tn.7.2.1 { 460 INSERT INTO t1 VALUES(11); 461 INSERT INTO t1 VALUES(12); 462 INSERT INTO t1 VALUES(13); 463 BEGIN; 464 PRAGMA application_id; 465 } {0} 466 do_test $tn.7.2.2 { 467 set p1 [snapshot_get db main] 468 execsql { 469 COMMIT; 470 INSERT INTO t1 VALUES(14); 471 PRAGMA wal_checkpoint; 472 BEGIN; 473 PRAGMA application_id; 474 } 475 set p2 [snapshot_get db main] 476 execsql COMMIT 477 } {} 478 479 do_snapshot_cmp_test $tn.7.2.3 $p1 $p2 -1 480 snapshot_free $p2 481 482 do_test $tn.7.3.1 { 483 execsql { 484 INSERT INTO t1 VALUES(14); 485 BEGIN; 486 PRAGMA application_id; 487 } 488 set p2 [snapshot_get db main] 489 execsql COMMIT 490 } {} 491 492 do_snapshot_cmp_test $tn.7.3.2 $p1 $p2 -1 493 snapshot_free $p1 494 snapshot_free $p2 495} 496 497finish_test 498