1# 2018 August 6 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# 12# Tests for calling sqlite3_snapshot_open() when there is already 13# a read transaction open on the database. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18ifcapable !snapshot {finish_test; return} 19set testprefix snapshot_up 20 21# This test does not work with the inmemory_journal permutation. The reason 22# is that each connection opened as part of this permutation executes 23# "PRAGMA journal_mode=memory", which fails if the database is in wal mode 24# and there are one or more existing connections. 25if {[permutation]=="inmemory_journal"} { 26 finish_test 27 return 28} 29 30do_execsql_test 1.0 { 31 CREATE TABLE t1(a, b, c); 32 PRAGMA journal_mode = wal; 33 INSERT INTO t1 VALUES(1, 2, 3); 34 INSERT INTO t1 VALUES(4, 5, 6); 35 INSERT INTO t1 VALUES(7, 8, 9); 36} {wal} 37 38do_test 1.1 { 39 execsql BEGIN 40 set ::snap1 [sqlite3_snapshot_get db main] 41 execsql COMMIT 42 execsql { INSERT INTO t1 VALUES(10, 11, 12); } 43 execsql BEGIN 44 set ::snap2 [sqlite3_snapshot_get db main] 45 execsql COMMIT 46 execsql { INSERT INTO t1 VALUES(13, 14, 15); } 47 execsql BEGIN 48 set ::snap3 [sqlite3_snapshot_get db main] 49 execsql COMMIT 50} {} 51 52do_execsql_test 1.2 { 53 BEGIN; 54 SELECT * FROM t1 55} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15} 56 57do_test 1.3 { 58 sqlite3_snapshot_open db main $::snap1 59 execsql { SELECT * FROM t1 } 60} {1 2 3 4 5 6 7 8 9} 61 62do_test 1.4 { 63 sqlite3_snapshot_open db main $::snap2 64 execsql { SELECT * FROM t1 } 65} {1 2 3 4 5 6 7 8 9 10 11 12} 66 67do_test 1.5 { 68 sqlite3 db2 test.db 69 execsql { PRAGMA wal_checkpoint } db2 70} {0 5 4} 71 72do_execsql_test 1.6 { 73 SELECT * FROM t1 74} {1 2 3 4 5 6 7 8 9 10 11 12} 75 76do_test 1.7 { 77 list [catch { sqlite3_snapshot_open db main $::snap1 } msg] $msg 78} {1 SQLITE_ERROR_SNAPSHOT} 79 80do_execsql_test 1.8 { 81 SELECT * FROM t1 82} {1 2 3 4 5 6 7 8 9 10 11 12} 83 84do_test 1.9 { 85 execsql { COMMIT ; BEGIN } 86 list [catch { sqlite3_snapshot_open db main $::snap1 } msg] $msg 87} {1 SQLITE_ERROR_SNAPSHOT} 88 89do_test 1.10 { 90 execsql { COMMIT } 91 execsql { 92 PRAGMA wal_checkpoint; 93 DELETE FROM t1 WHERE a = 1; 94 } db2 95 execsql BEGIN 96 set ::snap4 [sqlite3_snapshot_get db main] 97 execsql COMMIT 98 execsql { 99 DELETE FROM t1 WHERE a = 4; 100 } db2 101} {} 102 103do_test 1.11 { 104 execsql { 105 BEGIN; 106 SELECT * FROM t1 107 } 108} {7 8 9 10 11 12 13 14 15} 109do_test 1.12 { 110 sqlite3_snapshot_open db main $::snap4 111 execsql { SELECT * FROM t1 } 112} {4 5 6 7 8 9 10 11 12 13 14 15} 113 114do_test 1.13 { 115 list [catch { sqlite3_snapshot_open db main $::snap3 } msg] $msg 116} {1 SQLITE_ERROR_SNAPSHOT} 117do_test 1.14 { 118 execsql { SELECT * FROM t1 } 119} {4 5 6 7 8 9 10 11 12 13 14 15} 120 121db close 122db2 close 123sqlite3 db test.db 124do_execsql_test 1.15 { 125 BEGIN; 126 SELECT * FROM t1 127} {7 8 9 10 11 12 13 14 15} 128do_test 1.16 { 129 list [catch { sqlite3_snapshot_open db main $::snap4 } msg] $msg 130} {1 SQLITE_ERROR_SNAPSHOT} 131do_execsql_test 1.17 { COMMIT } 132 133sqlite3_snapshot_free $::snap1 134sqlite3_snapshot_free $::snap2 135sqlite3_snapshot_free $::snap3 136sqlite3_snapshot_free $::snap4 137 138#------------------------------------------------------------------------- 139catch { db close } 140sqlite3 db test.db 141sqlite3 db2 test.db 142sqlite3 db3 test.db 143 144proc xBusy {args} { return 1 } 145db3 busy xBusy 146 147do_test 2.1 { 148 execsql { INSERT INTO t1 VALUES(16, 17, 18) } db2 149 execsql BEGIN 150 set ::snap1 [sqlite3_snapshot_get db main] 151 execsql COMMIT 152 execsql { INSERT INTO t1 VALUES(19, 20, 21) } db2 153 execsql BEGIN 154 set ::snap2 [sqlite3_snapshot_get db main] 155 execsql COMMIT 156 set {} {} 157} {} 158 159do_execsql_test -db db2 2.2 { 160 BEGIN; 161 INSERT INTO t1 VALUES(19, 20, 21); 162} 163 164do_test 2.3 { 165 execsql BEGIN 166 sqlite3_snapshot_open db main $::snap1 167 execsql { SELECT * FROM t1 } 168} {7 8 9 10 11 12 13 14 15 16 17 18} 169 170proc xBusy {args} { 171 set ::res [list [catch { sqlite3_snapshot_open db main $::snap2 } msg] $msg] 172 return 1 173} 174db3 busy xBusy 175do_test 2.4 { 176 execsql {PRAGMA wal_checkpoint = restart} db3 177 set ::res 178} {1 SQLITE_BUSY} 179 180sqlite3_snapshot_free $::snap1 181sqlite3_snapshot_free $::snap2 182 183finish_test 184