1: # 2003 July 1
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: # This file implements regression tests for SQLite library. The
12: # focus of this script is testing the ATTACH and DETACH commands
13: # and related functionality.
14: #
15: # $Id: attach2.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16: #
17:
18: set testdir [file dirname $argv0]
19: source $testdir/tester.tcl
20:
21: ifcapable !attach {
22: finish_test
23: return
24: }
25:
26: # Ticket #354
27: #
28: # Databases test.db and test2.db contain identical schemas. Make
29: # sure we can attach test2.db from test.db.
30: #
31: do_test attach2-1.1 {
32: db eval {
33: CREATE TABLE t1(a,b);
34: CREATE INDEX x1 ON t1(a);
35: }
36: forcedelete test2.db
37: forcedelete test2.db-journal
38: sqlite3 db2 test2.db
39: db2 eval {
40: CREATE TABLE t1(a,b);
41: CREATE INDEX x1 ON t1(a);
42: }
43: catchsql {
44: ATTACH 'test2.db' AS t2;
45: }
46: } {0 {}}
47:
48: # Ticket #514
49: #
50: proc db_list {db} {
51: set list {}
52: foreach {idx name file} [execsql {PRAGMA database_list} $db] {
53: lappend list $idx $name
54: }
55: return $list
56: }
57: db eval {DETACH t2}
58: do_test attach2-2.1 {
59: # lock test2.db then try to attach it. This is no longer an error because
60: # db2 just RESERVES the database. It does not obtain a write-lock until
61: # we COMMIT.
62: db2 eval {BEGIN}
63: db2 eval {UPDATE t1 SET a = 0 WHERE 0}
64: catchsql {
65: ATTACH 'test2.db' AS t2;
66: }
67: } {0 {}}
68: ifcapable schema_pragmas {
69: do_test attach2-2.2 {
70: # make sure test2.db did get attached.
71: db_list db
72: } {0 main 2 t2}
73: } ;# ifcapable schema_pragmas
74: db2 eval {COMMIT}
75:
76: do_test attach2-2.5 {
77: # Make sure we can read test2.db from db
78: catchsql {
79: SELECT name FROM t2.sqlite_master;
80: }
81: } {0 {t1 x1}}
82: do_test attach2-2.6 {
83: # lock test2.db and try to read from it. This should still work because
84: # the lock is only a RESERVED lock which does not prevent reading.
85: #
86: db2 eval BEGIN
87: db2 eval {UPDATE t1 SET a = 0 WHERE 0}
88: catchsql {
89: SELECT name FROM t2.sqlite_master;
90: }
91: } {0 {t1 x1}}
92: do_test attach2-2.7 {
93: # but we can still read from test1.db even though test2.db is locked.
94: catchsql {
95: SELECT name FROM main.sqlite_master;
96: }
97: } {0 {t1 x1}}
98: do_test attach2-2.8 {
99: # start a transaction on test.db even though test2.db is locked.
100: catchsql {
101: BEGIN;
102: INSERT INTO t1 VALUES(8,9);
103: }
104: } {0 {}}
105: do_test attach2-2.9 {
106: execsql {
107: SELECT * FROM t1
108: }
109: } {8 9}
110: do_test attach2-2.10 {
111: # now try to write to test2.db. the write should fail
112: catchsql {
113: INSERT INTO t2.t1 VALUES(1,2);
114: }
115: } {1 {database is locked}}
116: do_test attach2-2.11 {
117: # when the write failed in the previous test, the transaction should
118: # have rolled back.
119: #
120: # Update for version 3: A transaction is no longer rolled back if a
121: # database is found to be busy.
122: execsql {rollback}
123: db2 eval ROLLBACK
124: execsql {
125: SELECT * FROM t1
126: }
127: } {}
128: do_test attach2-2.12 {
129: catchsql {
130: COMMIT
131: }
132: } {1 {cannot commit - no transaction is active}}
133:
134: # Ticket #574: Make sure it works using the non-callback API
135: #
136: do_test attach2-3.1 {
137: set DB [sqlite3_connection_pointer db]
138: set rc [catch {sqlite3_prepare $DB "ATTACH 'test2.db' AS t2" -1 TAIL} VM]
139: if {$rc} {lappend rc $VM}
140: sqlite3_step $VM
141: sqlite3_finalize $VM
142: set rc
143: } {0}
144: do_test attach2-3.2 {
145: set rc [catch {sqlite3_prepare $DB "DETACH t2" -1 TAIL} VM]
146: if {$rc} {lappend rc $VM}
147: sqlite3_step $VM
148: sqlite3_finalize $VM
149: set rc
150: } {0}
151:
152: db close
153: for {set i 2} {$i<=15} {incr i} {
154: catch {db$i close}
155: }
156:
157: # A procedure to verify the status of locks on a database.
158: #
159: proc lock_status {testnum db expected_result} {
160: # If the database was compiled with OMIT_TEMPDB set, then
161: # the lock_status list will not contain an entry for the temp
162: # db. But the test code doesn't know this, so its easiest
163: # to filter it out of the $expected_result list here.
164: ifcapable !tempdb {
165: set expected_result [concat \
166: [lrange $expected_result 0 1] \
167: [lrange $expected_result 4 end] \
168: ]
169: }
170: do_test attach2-$testnum [subst {
171: $db cache flush ;# The lock_status pragma should not be cached
172: execsql {PRAGMA lock_status} $db
173: }] $expected_result
174: }
175: set sqlite_os_trace 0
176:
177: # Tests attach2-4.* test that read-locks work correctly with attached
178: # databases.
179: do_test attach2-4.1 {
180: sqlite3 db test.db
181: sqlite3 db2 test.db
182: execsql {ATTACH 'test2.db' as file2}
183: execsql {ATTACH 'test2.db' as file2} db2
184: } {}
185:
186: lock_status 4.1.1 db {main unlocked temp closed file2 unlocked}
187: lock_status 4.1.2 db2 {main unlocked temp closed file2 unlocked}
188:
189: do_test attach2-4.2 {
190: # Handle 'db' read-locks test.db
191: execsql {BEGIN}
192: execsql {SELECT * FROM t1}
193: # Lock status:
194: # db - shared(main)
195: # db2 -
196: } {}
197:
198: lock_status 4.2.1 db {main shared temp closed file2 unlocked}
199: lock_status 4.2.2 db2 {main unlocked temp closed file2 unlocked}
200:
201: do_test attach2-4.3 {
202: # The read lock held by db does not prevent db2 from reading test.db
203: execsql {SELECT * FROM t1} db2
204: } {}
205:
206: lock_status 4.3.1 db {main shared temp closed file2 unlocked}
207: lock_status 4.3.2 db2 {main unlocked temp closed file2 unlocked}
208:
209: do_test attach2-4.4 {
210: # db is holding a read lock on test.db, so we should not be able
211: # to commit a write to test.db from db2
212: catchsql {
213: INSERT INTO t1 VALUES(1, 2)
214: } db2
215: } {1 {database is locked}}
216:
217: lock_status 4.4.1 db {main shared temp closed file2 unlocked}
218: lock_status 4.4.2 db2 {main unlocked temp closed file2 unlocked}
219:
220: # We have to make sure that the cache_size and the soft_heap_limit
221: # are large enough to hold the entire change in memory. If either
222: # is set too small, then changes will spill to the database, forcing
223: # a reserved lock to promote to exclusive. That will mess up our
224: # test results.
225:
226: set soft_limit [sqlite3_soft_heap_limit 0]
227:
228:
229: do_test attach2-4.5 {
230: # Handle 'db2' reserves file2.
231: execsql {BEGIN} db2
232: execsql {INSERT INTO file2.t1 VALUES(1, 2)} db2
233: # Lock status:
234: # db - shared(main)
235: # db2 - reserved(file2)
236: } {}
237:
238: lock_status 4.5.1 db {main shared temp closed file2 unlocked}
239: lock_status 4.5.2 db2 {main unlocked temp closed file2 reserved}
240:
241: do_test attach2-4.6.1 {
242: # Reads are allowed against a reserved database.
243: catchsql {
244: SELECT * FROM file2.t1;
245: }
246: # Lock status:
247: # db - shared(main), shared(file2)
248: # db2 - reserved(file2)
249: } {0 {}}
250:
251: lock_status 4.6.1.1 db {main shared temp closed file2 shared}
252: lock_status 4.6.1.2 db2 {main unlocked temp closed file2 reserved}
253:
254: do_test attach2-4.6.2 {
255: # Writes against a reserved database are not allowed.
256: catchsql {
257: UPDATE file2.t1 SET a=0;
258: }
259: } {1 {database is locked}}
260:
261: lock_status 4.6.2.1 db {main shared temp closed file2 shared}
262: lock_status 4.6.2.2 db2 {main unlocked temp closed file2 reserved}
263:
264: do_test attach2-4.7 {
265: # Ensure handle 'db' retains the lock on the main file after
266: # failing to obtain a write-lock on file2.
267: catchsql {
268: INSERT INTO t1 VALUES(1, 2)
269: } db2
270: } {0 {}}
271:
272: lock_status 4.7.1 db {main shared temp closed file2 shared}
273: lock_status 4.7.2 db2 {main reserved temp closed file2 reserved}
274:
275: do_test attach2-4.8 {
276: # We should still be able to read test.db from db2
277: execsql {SELECT * FROM t1} db2
278: } {1 2}
279:
280: lock_status 4.8.1 db {main shared temp closed file2 shared}
281: lock_status 4.8.2 db2 {main reserved temp closed file2 reserved}
282:
283: do_test attach2-4.9 {
284: # Try to upgrade the handle 'db' lock.
285: catchsql {
286: INSERT INTO t1 VALUES(1, 2)
287: }
288: } {1 {database is locked}}
289:
290: lock_status 4.9.1 db {main shared temp closed file2 shared}
291: lock_status 4.9.2 db2 {main reserved temp closed file2 reserved}
292:
293: do_test attach2-4.10 {
294: # We cannot commit db2 while db is holding a read-lock
295: catchsql {COMMIT} db2
296: } {1 {database is locked}}
297:
298: lock_status 4.10.1 db {main shared temp closed file2 shared}
299: lock_status 4.10.2 db2 {main pending temp closed file2 reserved}
300:
301: set sqlite_os_trace 0
302: do_test attach2-4.11 {
303: # db is able to commit.
304: catchsql {COMMIT}
305: } {0 {}}
306:
307: lock_status 4.11.1 db {main unlocked temp closed file2 unlocked}
308: lock_status 4.11.2 db2 {main pending temp closed file2 reserved}
309:
310: do_test attach2-4.12 {
311: # Now we can commit db2
312: catchsql {COMMIT} db2
313: } {0 {}}
314:
315: lock_status 4.12.1 db {main unlocked temp closed file2 unlocked}
316: lock_status 4.12.2 db2 {main unlocked temp closed file2 unlocked}
317:
318: do_test attach2-4.13 {
319: execsql {SELECT * FROM file2.t1}
320: } {1 2}
321: do_test attach2-4.14 {
322: execsql {INSERT INTO t1 VALUES(1, 2)}
323: } {}
324: do_test attach2-4.15 {
325: execsql {SELECT * FROM t1} db2
326: } {1 2 1 2}
327:
328: db close
329: db2 close
330: forcedelete test2.db
331: sqlite3_soft_heap_limit $soft_limit
332:
333: # These tests - attach2-5.* - check that the master journal file is deleted
334: # correctly when a multi-file transaction is committed or rolled back.
335: #
336: # Update: It's not actually created if a rollback occurs, so that test
337: # doesn't really prove too much.
338: foreach f [glob test.db*] {forcedelete $f}
339: do_test attach2-5.1 {
340: sqlite3 db test.db
341: execsql {
342: ATTACH 'test.db2' AS aux;
343: }
344: } {}
345: do_test attach2-5.2 {
346: execsql {
347: BEGIN;
348: CREATE TABLE tbl(a, b, c);
349: CREATE TABLE aux.tbl(a, b, c);
350: COMMIT;
351: }
352: } {}
353: do_test attach2-5.3 {
354: lsort [glob test.db*]
355: } {test.db test.db2}
356: do_test attach2-5.4 {
357: execsql {
358: BEGIN;
359: DROP TABLE aux.tbl;
360: DROP TABLE tbl;
361: ROLLBACK;
362: }
363: } {}
364: do_test attach2-5.5 {
365: lsort [glob test.db*]
366: } {test.db test.db2}
367:
368: # Check that a database cannot be ATTACHed or DETACHed during a transaction.
369: do_test attach2-6.1 {
370: execsql {
371: BEGIN;
372: }
373: } {}
374: do_test attach2-6.2 {
375: catchsql {
376: ATTACH 'test3.db' as aux2;
377: }
378: } {1 {cannot ATTACH database within transaction}}
379:
380: do_test attach2-6.3 {
381: catchsql {
382: DETACH aux;
383: }
384: } {1 {cannot DETACH database within transaction}}
385: do_test attach2-6.4 {
386: execsql {
387: COMMIT;
388: DETACH aux;
389: }
390: } {}
391:
392: db close
393:
394: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to attach2.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