1: # 2009 April 01
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: shared6.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
13:
14: set testdir [file dirname $argv0]
15: source $testdir/tester.tcl
16: ifcapable !shared_cache { finish_test ; return }
17:
18: do_test shared6-1.1.1 {
19: execsql {
20: CREATE TABLE t1(a, b);
21: CREATE TABLE t2(c, d);
22: CREATE TABLE t3(e, f);
23: }
24: db close
25: } {}
26: do_test shared6-1.1.2 {
27: set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
28: sqlite3_enable_shared_cache
29: } {1}
30:
31: do_test shared6-1.1.3 {
32: sqlite3 db1 test.db
33: sqlite3 db2 test.db
34: } {}
35:
36: # Exclusive shared-cache locks. Test the following:
37: #
38: # 1.2.1: If [db1] has an exclusive lock, [db2] cannot read.
39: # 1.2.2: If [db1] has an exclusive lock, [db1] can read.
40: # 1.2.3: If [db1] has a non-exclusive write-lock, [db2] can read.
41: #
42: do_test shared6-1.2.1 {
43: execsql { SELECT * FROM t1 } db2 ;# Cache a compiled statement
44: execsql { BEGIN EXCLUSIVE } db1
45: catchsql { SELECT * FROM t1 } db2 ;# Execute the cached compiled statement
46: } {1 {database table is locked}}
47: do_test shared6-1.2.2 {
48: execsql { SELECT * FROM t1 } db1
49: } {}
50: do_test shared6-1.2.3 {
51: execsql {
52: COMMIT;
53: BEGIN;
54: INSERT INTO t2 VALUES(3, 4);
55: } db1
56: execsql { SELECT * FROM t1 } db2
57: } {}
58: do_test shared6-1.2.X {
59: execsql { COMMIT } db1
60: } {}
61:
62: # Regular shared-cache locks. Verify the following:
63: #
64: # 1.3.1: If [db1] has a write-lock on t1, [db1] can read from t1.
65: # 1.3.2: If [db1] has a write-lock on t1, [db2] can read from t2.
66: # 1.3.3: If [db1] has a write-lock on t1, [db2] cannot read from t1.
67: # 1.3.4: If [db1] has a write-lock on t1, [db2] cannot write to t1.
68: # 1.3.5: If [db1] has a read-lock on t1, [db2] can read from t1.
69: # 1.3.6: If [db1] has a read-lock on t1, [db2] cannot write to t1.
70: #
71: do_test shared6-1.3.1 {
72: execsql {
73: BEGIN;
74: INSERT INTO t1 VALUES(1, 2);
75: } db1
76: execsql { SELECT * FROM t1 } db1
77: } {1 2}
78: do_test shared6-1.3.2 {
79: execsql { SELECT * FROM t2 } db2
80: } {3 4}
81: do_test shared6-1.3.3 {
82: catchsql { SELECT * FROM t1 } db2
83: } {1 {database table is locked: t1}}
84: do_test shared6-1.3.4 {
85: catchsql { INSERT INTO t2 VALUES(1, 2) } db2
86: } {1 {database table is locked}}
87: do_test shared6-1.3.5 {
88: execsql {
89: COMMIT;
90: BEGIN;
91: SELECT * FROM t1;
92: } db1
93: execsql { SELECT * FROM t1 } db2
94: } {1 2}
95: do_test shared6-1.3.5 {
96: catchsql { INSERT INTO t1 VALUES(5, 6) } db2
97: } {1 {database table is locked: t1}}
98: do_test shared6-1.3.X {
99: execsql { COMMIT } db1
100: } {}
101:
102: # Read-uncommitted mode.
103: #
104: # For these tests, connection [db2] is in read-uncommitted mode.
105: #
106: # 1.4.1: If [db1] has a write-lock on t1, [db2] can still read from t1.
107: # 1.4.2: If [db1] has a write-lock on the db schema (sqlite_master table),
108: # [db2] cannot read from the schema.
109: # 1.4.3: If [db1] has a read-lock on t1, [db2] cannot write to t1.
110: #
111: do_test shared6-1.4.1 {
112: execsql { PRAGMA read_uncommitted = 1 } db2
113: execsql {
114: BEGIN;
115: INSERT INTO t1 VALUES(5, 6);
116: } db1
117: execsql { SELECT * FROM t1 } db2
118: } {1 2 5 6}
119: do_test shared6-1.4.2 {
120: execsql { CREATE TABLE t4(a, b) } db1
121: catchsql { SELECT * FROM t1 } db2
122: } {1 {database table is locked}}
123: do_test shared6-1.4.3 {
124: execsql {
125: COMMIT;
126: BEGIN;
127: SELECT * FROM t1;
128: } db1
129: catchsql { INSERT INTO t1 VALUES(7, 8) } db2
130: } {1 {database table is locked: t1}}
131:
132: do_test shared6-1.X {
133: db1 close
134: db2 close
135: } {}
136:
137: #-------------------------------------------------------------------------
138: # The following tests - shared6-2.* - test that two database connections
139: # that connect to the same file using different VFS implementations do
140: # not share a cache.
141: #
142: if {$::tcl_platform(platform) eq "unix"} {
143: do_test shared6-2.1 {
144: sqlite3 db1 test.db -vfs unix
145: sqlite3 db2 test.db -vfs unix
146: sqlite3 db3 test.db -vfs unix-none
147: sqlite3 db4 test.db -vfs unix-none
148: } {}
149:
150: do_test shared6-2.2 {
151: execsql { BEGIN; INSERT INTO t1 VALUES(9, 10); } db1
152: catchsql { SELECT * FROM t1 } db2
153: } {1 {database table is locked: t1}}
154: do_test shared6-2.3 {
155: execsql { SELECT * FROM t1 } db3
156: } {1 2 5 6}
157:
158: do_test shared6-2.3 {
159: execsql { COMMIT } db1
160: execsql { BEGIN; INSERT INTO t1 VALUES(11, 12); } db3
161: catchsql { SELECT * FROM t1 } db4
162: } {1 {database table is locked: t1}}
163:
164: do_test shared6-2.4 {
165: execsql { SELECT * FROM t1 } db1
166: } {1 2 5 6 9 10}
167:
168: do_test shared6-2.5 {
169: execsql { COMMIT } db3
170: } {}
171:
172: do_test shared6-2.X {
173: db1 close
174: db2 close
175: db3 close
176: db4 close
177: } {}
178: }
179:
180: #-------------------------------------------------------------------------
181: # Test that it is possible to open an exclusive transaction while
182: # already holding a read-lock on the database file. And that it is
183: # not possible if some other connection holds such a lock.
184: #
185: do_test shared6-3.1 {
186: sqlite3 db1 test.db
187: sqlite3 db2 test.db
188: sqlite3 db3 test.db
189: } {}
190: db1 eval {SELECT * FROM t1} {
191: # Within this block [db1] is holding a read-lock on t1. Test that
192: # this means t1 cannot be written by [db2].
193: #
194: do_test shared6-3.2 {
195: catchsql { INSERT INTO t1 VALUES(1, 2) } db2
196: } {1 {database table is locked: t1}}
197:
198: do_test shared6-3.3 {
199: execsql { BEGIN EXCLUSIVE } db1
200: } {}
201: break
202: }
203: do_test shared6-3.4 {
204: catchsql { SELECT * FROM t1 } db2
205: } {1 {database schema is locked: main}}
206: do_test shared6-3.5 {
207: execsql COMMIT db1
208: } {}
209: db2 eval {SELECT * FROM t1} {
210: do_test shared6-3.6 {
211: catchsql { BEGIN EXCLUSIVE } db1
212: } {1 {database table is locked}}
213: break
214: }
215: do_test shared6-3.7 {
216: execsql { BEGIN } db1
217: execsql { BEGIN } db2
218: } {}
219: db2 eval {SELECT * FROM t1} {
220: do_test shared6-3.8 {
221: catchsql { INSERT INTO t1 VALUES(1, 2) } db1
222: } {1 {database table is locked: t1}}
223: break
224: }
225: do_test shared6-3.9 {
226: execsql { BEGIN ; ROLLBACK } db3
227: } {}
228: do_test shared6-3.10 {
229: catchsql { SELECT * FROM t1 } db3
230: } {1 {database table is locked}}
231: do_test shared6-3.X {
232: db1 close
233: db2 close
234: db3 close
235: } {}
236:
237: do_test shared6-4.1 {
238: #forcedelete test.db test.db-journal
239: sqlite3 db1 test.db
240: sqlite3 db2 test.db
241:
242: set ::STMT [sqlite3_prepare_v2 db1 "SELECT * FROM t1" -1 DUMMY]
243: execsql { CREATE TABLE t5(a, b) } db2
244: } {}
245: do_test shared6-4.2 {
246: sqlite3_finalize $::STMT
247: } {SQLITE_OK}
248: do_test shared6-4.X {
249:
250: db1 close
251: db2 close
252: } {}
253:
254: sqlite3_enable_shared_cache $::enable_shared_cache
255: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to shared6.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test