Debian Codegraph @

test/attach2.test

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