1 # 2005 January 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 # 12 # $Id: shared2.test,v 1.8 2009/06/05 17:09:12 drh Exp $ 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 source $testdir/lock_common.tcl 17 source $testdir/malloc_common.tcl 18 db close 19 20 ifcapable !shared_cache { 21 finish_test 22 return 23 } 24 set ::enable_shared_cache [sqlite3_enable_shared_cache 1] 25 26 # Test that if we delete all rows from a table any read-uncommitted 27 # cursors are correctly invalidated. Test on both table and index btrees. 28 do_test shared2-1.1 { 29 sqlite3 db1 test.db 30 sqlite3 db2 test.db 31 32 # Set up some data. Table "numbers" has 64 rows after this block 33 # is executed. 34 execsql { 35 BEGIN; 36 CREATE TABLE numbers(a PRIMARY KEY, b); 37 INSERT INTO numbers(oid) VALUES(NULL); 38 INSERT INTO numbers(oid) SELECT NULL FROM numbers; 39 INSERT INTO numbers(oid) SELECT NULL FROM numbers; 40 INSERT INTO numbers(oid) SELECT NULL FROM numbers; 41 INSERT INTO numbers(oid) SELECT NULL FROM numbers; 42 INSERT INTO numbers(oid) SELECT NULL FROM numbers; 43 INSERT INTO numbers(oid) SELECT NULL FROM numbers; 44 UPDATE numbers set a = oid, b = 'abcdefghijklmnopqrstuvwxyz0123456789'; 45 COMMIT; 46 } db1 47 } {} 48 do_test shared2-1.2 { 49 # Put connection 2 in read-uncommitted mode and start a SELECT on table 50 # 'numbers'. Half way through the SELECT, use connection 1 to delete the 51 # contents of this table. 52 execsql { 53 pragma read_uncommitted = 1; 54 } db2 55 set count [execsql {SELECT count(*) FROM numbers} db2] 56 db2 eval {SELECT a FROM numbers ORDER BY oid} { 57 if {$a==32} { 58 execsql { 59 BEGIN; 60 DELETE FROM numbers; 61 } db1 62 } 63 } 64 list $a $count 65 } {32 64} 66 do_test shared2-1.3 { 67 # Same test as 1.2, except scan using the index this time. 68 execsql { 69 ROLLBACK; 70 } db1 71 set count [execsql {SELECT count(*) FROM numbers} db2] 72 db2 eval {SELECT a, b FROM numbers ORDER BY a} { 73 if {$a==32} { 74 execsql { 75 DELETE FROM numbers; 76 } db1 77 } 78 } 79 list $a $count 80 } {32 64} 81 82 #--------------------------------------------------------------------------- 83 # These tests, shared2.2.*, test the outcome when data is added to or 84 # removed from a table due to a rollback while a read-uncommitted 85 # cursor is scanning it. 86 # 87 do_test shared2-2.1 { 88 execsql { 89 INSERT INTO numbers VALUES(1, 'Medium length text field'); 90 INSERT INTO numbers VALUES(2, 'Medium length text field'); 91 INSERT INTO numbers VALUES(3, 'Medium length text field'); 92 INSERT INTO numbers VALUES(4, 'Medium length text field'); 93 BEGIN; 94 DELETE FROM numbers WHERE (a%2)=0; 95 } db1 96 set res [list] 97 db2 eval { 98 SELECT a FROM numbers ORDER BY a; 99 } { 100 lappend res $a 101 if {$a==3} { 102 execsql {ROLLBACK} db1 103 } 104 } 105 set res 106 } {1 3 4} 107 do_test shared2-2.2 { 108 execsql { 109 BEGIN; 110 INSERT INTO numbers VALUES(5, 'Medium length text field'); 111 INSERT INTO numbers VALUES(6, 'Medium length text field'); 112 } db1 113 set res [list] 114 db2 eval { 115 SELECT a FROM numbers ORDER BY a; 116 } { 117 lappend res $a 118 if {$a==5} { 119 execsql {ROLLBACK} db1 120 } 121 } 122 set res 123 } {1 2 3 4 5} 124 125 db1 close 126 db2 close 127 128 do_test shared2-3.2 { 129 sqlite3_enable_shared_cache 1 130 } {1} 131 132 file delete -force test.db 133 134 sqlite3 db test.db 135 do_test shared2-4.1 { 136 execsql { 137 CREATE TABLE t0(a, b); 138 CREATE TABLE t1(a, b DEFAULT 'hello world'); 139 } 140 } {} 141 db close 142 143 sqlite3 db test.db 144 sqlite3 db2 test.db 145 146 do_test shared2-4.2 { 147 execsql { SELECT a, b FROM t0 } db 148 execsql { INSERT INTO t1(a) VALUES(1) } db2 149 } {} 150 151 do_test shared2-4.3 { 152 db2 close 153 db close 154 } {} 155 156 # At one point, this was causing a crash. 157 # 158 do_test shared2-5.1 { 159 sqlite3 db test.db 160 sqlite3 db2 test.db 161 execsql { CREATE TABLE t2(a, b, c) } 162 163 # The following statement would crash when attempting to sqlite3_free() 164 # a pointer allocated from a lookaside buffer. 165 execsql { CREATE INDEX i1 ON t2(a) } db2 166 } {} 167 168 db close 169 db2 close 170 171 # The following test verifies that shared-cache mode does not automatically 172 # turn on exclusive-locking mode for some reason. 173 do_multiclient_test {tn} { 174 sql1 { CREATE TABLE t1(a, b) } 175 sql2 { CREATE TABLE t2(a, b) } 176 do_test shared2-6.$tn.1 { sql1 { SELECT * FROM t2 } } {} 177 do_test shared2-6.$tn.2 { sql2 { SELECT * FROM t1 } } {} 178 } 179 180 sqlite3_enable_shared_cache $::enable_shared_cache 181 finish_test 182
