# 2005 January 19

 #

 # The author disclaims copyright to this source code.  In place of

 # a legal notice, here is a blessing:

 #

 #    May you do good and not evil.

 #    May you find forgiveness for yourself and forgive others.

 #    May you share freely, never taking more than you give.

 #

 #***********************************************************************

 #

 # $Id: shared2.test,v 1.5 2007/08/23 02:47:54 drh Exp $

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 db close

 ifcapable !shared_cache {

   finish_test

   return

 }

 set ::enable_shared_cache [sqlite3_enable_shared_cache 1]

 # Test that if we delete all rows from a table any read-uncommitted 

 # cursors are correctly invalidated. Test on both table and index btrees.

 do_test shared2-1.1 {

   sqlite3 db1 test.db

   sqlite3 db2 test.db

   # Set up some data. Table "numbers" has 64 rows after this block 

   # is executed.

   execsql {

     BEGIN;

     CREATE TABLE numbers(a PRIMARY KEY, b);

     INSERT INTO numbers(oid) VALUES(NULL);

     INSERT INTO numbers(oid) SELECT NULL FROM numbers;

     INSERT INTO numbers(oid) SELECT NULL FROM numbers;

     INSERT INTO numbers(oid) SELECT NULL FROM numbers;

     INSERT INTO numbers(oid) SELECT NULL FROM numbers;

     INSERT INTO numbers(oid) SELECT NULL FROM numbers;

     INSERT INTO numbers(oid) SELECT NULL FROM numbers;

     UPDATE numbers set a = oid, b = 'abcdefghijklmnopqrstuvwxyz0123456789';

     COMMIT;

   } db1

 } {}

 do_test shared2-1.2 {

   # Put connection 2 in read-uncommitted mode and start a SELECT on table 

   # 'numbers'. Half way through the SELECT, use connection 1 to delete the

   # contents of this table.

   execsql {

     pragma read_uncommitted = 1;

   } db2

   set count [execsql {SELECT count(*) FROM numbers} db2]

   db2 eval {SELECT a FROM numbers ORDER BY oid} {

     if {$a==32} {

       execsql {

         BEGIN;

         DELETE FROM numbers;

       } db1

     }

   }

   list $a $count

 } {32 64}

 do_test shared2-1.3 {

   # Same test as 1.2, except scan using the index this time.

   execsql {

     ROLLBACK;

   } db1

   set count [execsql {SELECT count(*) FROM numbers} db2]

   db2 eval {SELECT a, b FROM numbers ORDER BY a} {

     if {$a==32} {

       execsql {

         DELETE FROM numbers;

       } db1

     }

   }

   list $a $count

 } {32 64}

 #---------------------------------------------------------------------------

 # These tests, shared2.2.*, test the outcome when data is added to or 

 # removed from a table due to a rollback while a read-uncommitted 

 # cursor is scanning it.

 #

 do_test shared2-2.1 {

   execsql {

     INSERT INTO numbers VALUES(1, 'Medium length text field');

     INSERT INTO numbers VALUES(2, 'Medium length text field');

     INSERT INTO numbers VALUES(3, 'Medium length text field');

     INSERT INTO numbers VALUES(4, 'Medium length text field');

     BEGIN;

     DELETE FROM numbers WHERE (a%2)=0;

   } db1

   set res [list]

   db2 eval {

     SELECT a FROM numbers ORDER BY a;

   } {

     lappend res $a

     if {$a==3} {

       execsql {ROLLBACK} db1

     }

   }

   set res

 } {1 3 4}

 do_test shared2-2.2 {

   execsql {

     BEGIN;

     INSERT INTO numbers VALUES(5, 'Medium length text field');

     INSERT INTO numbers VALUES(6, 'Medium length text field');

   } db1

   set res [list]

   db2 eval {

     SELECT a FROM numbers ORDER BY a;

   } {

     lappend res $a

     if {$a==5} {

       execsql {ROLLBACK} db1

     }

   }

   set res

 } {1 2 3 4 5}

 db1 close

 db2 close

 do_test shared2-3.2 {

   sqlite3_enable_shared_cache 1

 } {1}

 sqlite3_enable_shared_cache $::enable_shared_cache

 finish_test