xref: /sqlite-3.40.0/test/shared2.test (revision 2b8c13e7) 
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.3 2006/01/24 14:21:24 danielk1977 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
25# Test that if we delete all rows from a table any read-uncommitted
26# cursors are correctly invalidated. Test on both table and index btrees.
27do_test shared2-1.1 {
28  sqlite3 db1 test.db
29  sqlite3 db2 test.db
30
31  # Set up some data. Table "numbers" has 64 rows after this block
32  # is executed.
33  execsql {
34    BEGIN;
35    CREATE TABLE numbers(a PRIMARY KEY, b);
36    INSERT INTO numbers(oid) VALUES(NULL);
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    INSERT INTO numbers(oid) SELECT NULL FROM numbers;
43    UPDATE numbers set a = oid, b = 'abcdefghijklmnopqrstuvwxyz0123456789';
44    COMMIT;
45  } db1
46} {}
47do_test shared2-1.2 {
48  # Put connection 2 in read-uncommitted mode and start a SELECT on table
49  # 'numbers'. Half way through the SELECT, use connection 1 to delete the
50  # contents of this table.
51  execsql {
52    pragma read_uncommitted = 1;
53  } db2
54  set count [execsql {SELECT count(*) FROM numbers} db2]
55  db2 eval {SELECT a FROM numbers ORDER BY oid} {
56    if {$a==32} {
57      execsql {
58        BEGIN;
59        DELETE FROM numbers;
60      } db1
61    }
62  }
63  list $a $count
64} {32 64}
65do_test shared2-1.3 {
66  # Same test as 1.2, except scan using the index this time.
67  execsql {
68    ROLLBACK;
69  } db1
70  set count [execsql {SELECT count(*) FROM numbers} db2]
71  db2 eval {SELECT a, b FROM numbers ORDER BY a} {
72    if {$a==32} {
73      execsql {
74        DELETE FROM numbers;
75      } db1
76    }
77  }
78  list $a $count
79} {32 64}
80
81# Rollback data into or out of a table while a read-uncommitted
82# cursor is scanning it.
83#
84do_test shared2-2.1 {
85  execsql {
86    INSERT INTO numbers VALUES(1, 'Medium length text field');
87    INSERT INTO numbers VALUES(2, 'Medium length text field');
88    INSERT INTO numbers VALUES(3, 'Medium length text field');
89    INSERT INTO numbers VALUES(4, 'Medium length text field');
90    BEGIN;
91    DELETE FROM numbers WHERE (a%2)=0;
92  } db1
93  set res [list]
94  db2 eval {
95    SELECT a FROM numbers ORDER BY a;
96  } {
97    lappend res $a
98    if {$a==3} {
99      execsql {ROLLBACK} db1
100    }
101  }
102  set res
103} {1 3 4}
104do_test shared2-2.2 {
105  execsql {
106    BEGIN;
107    INSERT INTO numbers VALUES(5, 'Medium length text field');
108    INSERT INTO numbers VALUES(6, 'Medium length text field');
109  } db1
110  set res [list]
111  db2 eval {
112    SELECT a FROM numbers ORDER BY a;
113  } {
114    lappend res $a
115    if {$a==5} {
116      execsql {ROLLBACK} db1
117    }
118  }
119  set res
120} {1 2 3 4 5}
121
122db1 close
123db2 close
124
125do_test shared2-3.2 {
126  sqlite3_thread_cleanup
127  sqlite3_enable_shared_cache 1
128} {0}
129
130sqlite3_enable_shared_cache $::enable_shared_cache
131finish_test
132