wasCSharpSQLite – Blame information for rev 7
?pathlinks?
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 |