xref: /sqlite-3.40.0/test/shared9.test (revision 6b9bb59f) 
1# 2012 October 5
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# The tests in this file are intended to show if two connections attach
13# to the same shared cache using different database names, views and
14# virtual tables may still be accessed.
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19source $testdir/lock_common.tcl
20set testprefix shared9
21
22ifcapable !view||!trigger {
23  finish_test
24  return
25}
26
27db close
28set enable_shared_cache [sqlite3_enable_shared_cache 1]
29
30sqlite3 db1 test.db
31sqlite3 db2 test.db
32forcedelete test.db2
33
34do_test 1.1 {
35  db1 eval {
36    ATTACH 'test.db2' AS 'fred';
37    CREATE TABLE fred.t1(a, b, c);
38    CREATE VIEW fred.v1 AS SELECT * FROM t1;
39
40    CREATE TABLE fred.t2(a, b);
41    CREATE TABLE fred.t3(a, b);
42    CREATE TRIGGER fred.trig AFTER INSERT ON t2 BEGIN
43      DELETE FROM t3;
44      INSERT INTO t3 SELECT * FROM t2;
45    END;
46    INSERT INTO t2 VALUES(1, 2);
47    SELECT * FROM t3;
48  }
49} {1 2}
50
51do_test 1.2 { db2 eval "ATTACH 'test.db2' AS 'jones'" } {}
52do_test 1.3 { db2 eval "SELECT * FROM v1"             } {}
53do_test 1.4 { db2 eval "INSERT INTO t2 VALUES(3, 4)"  } {}
54
55ifcapable fts3 {
56  do_test 1.5 {
57    db1 eval {
58      CREATE VIRTUAL TABLE fred.t4 USING fts4;
59      INSERT INTO t4 VALUES('hello world');
60    }
61  } {}
62
63  do_test 1.6 {
64    db2 eval {
65      INSERT INTO t4 VALUES('shared cache');
66      SELECT * FROM t4 WHERE t4 MATCH 'hello';
67    }
68  } {{hello world}}
69
70  do_test 1.7 {
71    db1 eval {
72      SELECT * FROM t4 WHERE t4 MATCH 'c*';
73    }
74  } {{shared cache}}
75}
76
77db1 close
78db2 close
79
80#-------------------------------------------------------------------------
81# The following tests attempt to find a similar problem with collation
82# sequence names - pointers to database handle specific allocations leaking
83# into schema objects and being used after the original handle has been
84# closed.
85#
86forcedelete test.db test.db2
87sqlite3 db1 test.db
88sqlite3 db2 test.db
89foreach x {collate1 collate2 collate3} {
90  proc $x {a b} { string compare $a $b }
91  db1 collate $x $x
92  db2 collate $x $x
93}
94do_test 2.1 {
95  db1 eval {
96    CREATE TABLE t1(a, b, c COLLATE collate1);
97    CREATE INDEX i1 ON t1(a COLLATE collate2, c, b);
98  }
99} {}
100do_test 2.2 {
101  db1 close
102  db2 eval "INSERT INTO t1 VALUES('abc', 'def', 'ghi')"
103} {}
104db2 close
105
106#-------------------------------------------------------------------------
107# At one point, the following would cause a collation sequence belonging
108# to connection [db1] to be invoked by a call to [db2 eval]. Which is a
109# problem if [db1] has already been closed.
110#
111forcedelete test.db test.db2
112sqlite3 db1 test.db
113sqlite3 db2 test.db
114
115proc mycollate_db1 {a b} {set ::invoked_mycollate_db1 1 ; string compare $a $b}
116proc mycollate_db2 {a b} {string compare $a $b}
117
118db1 collate mycollate mycollate_db1
119db2 collate mycollate mycollate_db2
120
121do_test 2.3 {
122  set ::invoked_mycollate_db1 0
123  db1 eval {
124    CREATE TABLE t1(a COLLATE mycollate, CHECK (a IN ('one', 'two', 'three')));
125    INSERT INTO t1 VALUES('one');
126  }
127  db1 close
128  set ::invoked_mycollate_db1
129} {1}
130do_test 2.4 {
131  set ::invoked_mycollate_db1 0
132  db2 eval {
133    INSERT INTO t1 VALUES('two');
134  }
135  db2 close
136  set ::invoked_mycollate_db1
137} {0}
138
139#-------------------------------------------------------------------------
140# This test verifies that a bug causing a busy-handler belonging to one
141# shared-cache connection to be executed as a result of an sqlite3_step()
142# on another has been fixed.
143#
144forcedelete test.db test.db2
145sqlite3 db1 test.db
146sqlite3 db2 test.db
147
148proc busyhandler {handle args} {
149  set ::busyhandler_invoked_for $handle
150  return 1
151}
152db1 busy [list busyhandler db1]
153db2 busy [list busyhandler db2]
154
155do_test 3.1 {
156  db1 eval {
157    BEGIN;
158      CREATE TABLE t1(a, b);
159      CREATE TABLE t2(a, b);
160      INSERT INTO t1 VALUES(1, 2);
161      INSERT INTO t2 VALUES(1, 2);
162  }
163  # Keep this next COMMIT as a separate statement. This ensures that COMMIT
164  # has already been compiled and loaded into the tcl interface statement
165  # cache when it is attempted below.
166  db1 eval COMMIT
167  db1 eval {
168    BEGIN;
169      INSERT INTO t1 VALUES(3, 4);
170  }
171} {}
172
173do_test 3.3 {
174  set ::tf [launch_testfixture]
175  testfixture $::tf {
176    sqlite3 db test.db
177    db eval {
178      BEGIN;
179        SELECT * FROM t1;
180    }
181  }
182} {1 2}
183
184do_test 3.2 {
185  db2 eval { SELECT * FROM t2 }
186} {1 2}
187
188do_test 3.3 {
189  list [catch { db1 eval COMMIT } msg] $msg
190} {1 {database is locked}}
191
192# At one point the following would fail, showing that the busy-handler
193# belonging to [db2] was invoked instead.
194do_test 3.4 {
195  set ::busyhandler_invoked_for
196} {db1}
197do_test 3.5 {
198  close $::tf
199  db1 eval COMMIT
200} {}
201
202db1 close
203db2 close
204
205sqlite3_enable_shared_cache $::enable_shared_cache
206finish_test
207
208