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