# 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