sl@0: # 2005 January 19
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: #
sl@0: # $Id: shared2.test,v 1.5 2007/08/23 02:47:54 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: db close
sl@0: 
sl@0: ifcapable !shared_cache {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
sl@0: 
sl@0: # Test that if we delete all rows from a table any read-uncommitted 
sl@0: # cursors are correctly invalidated. Test on both table and index btrees.
sl@0: do_test shared2-1.1 {
sl@0:   sqlite3 db1 test.db
sl@0:   sqlite3 db2 test.db
sl@0: 
sl@0:   # Set up some data. Table "numbers" has 64 rows after this block 
sl@0:   # is executed.
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     CREATE TABLE numbers(a PRIMARY KEY, b);
sl@0:     INSERT INTO numbers(oid) VALUES(NULL);
sl@0:     INSERT INTO numbers(oid) SELECT NULL FROM numbers;
sl@0:     INSERT INTO numbers(oid) SELECT NULL FROM numbers;
sl@0:     INSERT INTO numbers(oid) SELECT NULL FROM numbers;
sl@0:     INSERT INTO numbers(oid) SELECT NULL FROM numbers;
sl@0:     INSERT INTO numbers(oid) SELECT NULL FROM numbers;
sl@0:     INSERT INTO numbers(oid) SELECT NULL FROM numbers;
sl@0:     UPDATE numbers set a = oid, b = 'abcdefghijklmnopqrstuvwxyz0123456789';
sl@0:     COMMIT;
sl@0:   } db1
sl@0: } {}
sl@0: do_test shared2-1.2 {
sl@0:   # Put connection 2 in read-uncommitted mode and start a SELECT on table 
sl@0:   # 'numbers'. Half way through the SELECT, use connection 1 to delete the
sl@0:   # contents of this table.
sl@0:   execsql {
sl@0:     pragma read_uncommitted = 1;
sl@0:   } db2
sl@0:   set count [execsql {SELECT count(*) FROM numbers} db2]
sl@0:   db2 eval {SELECT a FROM numbers ORDER BY oid} {
sl@0:     if {$a==32} {
sl@0:       execsql {
sl@0:         BEGIN;
sl@0:         DELETE FROM numbers;
sl@0:       } db1
sl@0:     }
sl@0:   }
sl@0:   list $a $count
sl@0: } {32 64}
sl@0: do_test shared2-1.3 {
sl@0:   # Same test as 1.2, except scan using the index this time.
sl@0:   execsql {
sl@0:     ROLLBACK;
sl@0:   } db1
sl@0:   set count [execsql {SELECT count(*) FROM numbers} db2]
sl@0:   db2 eval {SELECT a, b FROM numbers ORDER BY a} {
sl@0:     if {$a==32} {
sl@0:       execsql {
sl@0:         DELETE FROM numbers;
sl@0:       } db1
sl@0:     }
sl@0:   }
sl@0:   list $a $count
sl@0: } {32 64}
sl@0: 
sl@0: #---------------------------------------------------------------------------
sl@0: # These tests, shared2.2.*, test the outcome when data is added to or 
sl@0: # removed from a table due to a rollback while a read-uncommitted 
sl@0: # cursor is scanning it.
sl@0: #
sl@0: do_test shared2-2.1 {
sl@0:   execsql {
sl@0:     INSERT INTO numbers VALUES(1, 'Medium length text field');
sl@0:     INSERT INTO numbers VALUES(2, 'Medium length text field');
sl@0:     INSERT INTO numbers VALUES(3, 'Medium length text field');
sl@0:     INSERT INTO numbers VALUES(4, 'Medium length text field');
sl@0:     BEGIN;
sl@0:     DELETE FROM numbers WHERE (a%2)=0;
sl@0:   } db1
sl@0:   set res [list]
sl@0:   db2 eval {
sl@0:     SELECT a FROM numbers ORDER BY a;
sl@0:   } {
sl@0:     lappend res $a
sl@0:     if {$a==3} {
sl@0:       execsql {ROLLBACK} db1
sl@0:     }
sl@0:   }
sl@0:   set res
sl@0: } {1 3 4}
sl@0: do_test shared2-2.2 {
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     INSERT INTO numbers VALUES(5, 'Medium length text field');
sl@0:     INSERT INTO numbers VALUES(6, 'Medium length text field');
sl@0:   } db1
sl@0:   set res [list]
sl@0:   db2 eval {
sl@0:     SELECT a FROM numbers ORDER BY a;
sl@0:   } {
sl@0:     lappend res $a
sl@0:     if {$a==5} {
sl@0:       execsql {ROLLBACK} db1
sl@0:     }
sl@0:   }
sl@0:   set res
sl@0: } {1 2 3 4 5}
sl@0: 
sl@0: db1 close
sl@0: db2 close
sl@0: 
sl@0: do_test shared2-3.2 {
sl@0:   sqlite3_enable_shared_cache 1
sl@0: } {1}
sl@0: 
sl@0: sqlite3_enable_shared_cache $::enable_shared_cache
sl@0: finish_test