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.5 2007/08/23 02:47:54 drh Exp $

    13 

    14 set testdir [file dirname $argv0]

    15 source $testdir/tester.tcl

    16 db close

    17 

    18 ifcapable !shared_cache {

    19   finish_test

    20   return

    21 }

    22 set ::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.

    26 do_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 } {}

    46 do_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}

    64 do_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 #

    85 do_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}

   105 do_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 

   123 db1 close

   124 db2 close

   125 

   126 do_test shared2-3.2 {

   127   sqlite3_enable_shared_cache 1

   128 } {1}

   129 

   130 sqlite3_enable_shared_cache $::enable_shared_cache

   131 finish_test