wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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.38 2007/12/13 21:54:11 drh 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 file delete -force test2.db
37 file delete -force 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 file delete -force 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*] {file delete -force $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