wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2003 April 4 |
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: attach.test,v 1.52 2009/05/29 14:39:08 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 | for {set i 2} {$i<=15} {incr i} { |
||
27 | file delete -force test$i.db |
||
28 | file delete -force test$i.db-journal |
||
29 | } |
||
30 | |||
31 | do_test attach-1.1 { |
||
32 | execsql { |
||
33 | CREATE TABLE t1(a,b); |
||
34 | INSERT INTO t1 VALUES(1,2); |
||
35 | INSERT INTO t1 VALUES(3,4); |
||
36 | SELECT * FROM t1; |
||
37 | } |
||
38 | } {1 2 3 4} |
||
39 | do_test attach-1.2 { |
||
40 | sqlite3 db2 test2.db |
||
41 | execsql { |
||
42 | CREATE TABLE t2(x,y); |
||
43 | INSERT INTO t2 VALUES(1,'x'); |
||
44 | INSERT INTO t2 VALUES(2,'y'); |
||
45 | SELECT * FROM t2; |
||
46 | } db2 |
||
47 | } {1 x 2 y} |
||
48 | do_test attach-1.3 { |
||
49 | execsql { |
||
50 | ATTACH DATABASE 'test2.db' AS two; |
||
51 | SELECT * FROM two.t2; |
||
52 | } |
||
53 | } {1 x 2 y} |
||
54 | do_test attach-1.4 { |
||
55 | execsql { |
||
56 | SELECT * FROM t2; |
||
57 | } |
||
58 | } {1 x 2 y} |
||
59 | do_test attach-1.5 { |
||
60 | execsql { |
||
61 | DETACH DATABASE two; |
||
62 | SELECT * FROM t1; |
||
63 | } |
||
64 | } {1 2 3 4} |
||
65 | do_test attach-1.6 { |
||
66 | catchsql { |
||
67 | SELECT * FROM t2; |
||
68 | } |
||
69 | } {1 {no such table: t2}} |
||
70 | do_test attach-1.7 { |
||
71 | catchsql { |
||
72 | SELECT * FROM two.t2; |
||
73 | } |
||
74 | } {1 {no such table: two.t2}} |
||
75 | do_test attach-1.8 { |
||
76 | catchsql { |
||
77 | ATTACH DATABASE 'test3.db' AS three; |
||
78 | } |
||
79 | } {0 {}} |
||
80 | do_test attach-1.9 { |
||
81 | catchsql { |
||
82 | SELECT * FROM three.sqlite_master; |
||
83 | } |
||
84 | } {0 {}} |
||
85 | do_test attach-1.10 { |
||
86 | catchsql { |
||
87 | DETACH DATABASE [three]; |
||
88 | } |
||
89 | } {0 {}} |
||
90 | do_test attach-1.11 { |
||
91 | execsql { |
||
92 | ATTACH 'test.db' AS db2; |
||
93 | ATTACH 'test.db' AS db3; |
||
94 | ATTACH 'test.db' AS db4; |
||
95 | ATTACH 'test.db' AS db5; |
||
96 | ATTACH 'test.db' AS db6; |
||
97 | ATTACH 'test.db' AS db7; |
||
98 | ATTACH 'test.db' AS db8; |
||
99 | ATTACH 'test.db' AS db9; |
||
100 | } |
||
101 | } {} |
||
102 | proc db_list {db} { |
||
103 | set list {} |
||
104 | foreach {idx name file} [execsql {PRAGMA database_list} $db] { |
||
105 | lappend list $idx $name |
||
106 | } |
||
107 | return $list |
||
108 | } |
||
109 | ifcapable schema_pragmas { |
||
110 | do_test attach-1.11b { |
||
111 | db_list db |
||
112 | } {0 main 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9} |
||
113 | } ;# ifcapable schema_pragmas |
||
114 | do_test attach-1.12 { |
||
115 | catchsql { |
||
116 | ATTACH 'test.db' as db2; |
||
117 | } |
||
118 | } {1 {database db2 is already in use}} |
||
119 | do_test attach-1.12.2 { |
||
120 | db errorcode |
||
121 | } {1} |
||
122 | do_test attach-1.13 { |
||
123 | catchsql { |
||
124 | ATTACH 'test.db' as db5; |
||
125 | } |
||
126 | } {1 {database db5 is already in use}} |
||
127 | do_test attach-1.14 { |
||
128 | catchsql { |
||
129 | ATTACH 'test.db' as db9; |
||
130 | } |
||
131 | } {1 {database db9 is already in use}} |
||
132 | do_test attach-1.15 { |
||
133 | catchsql { |
||
134 | ATTACH 'test.db' as main; |
||
135 | } |
||
136 | } {1 {database main is already in use}} |
||
137 | ifcapable tempdb { |
||
138 | do_test attach-1.16 { |
||
139 | catchsql { |
||
140 | ATTACH 'test.db' as temp; |
||
141 | } |
||
142 | } {1 {database temp is already in use}} |
||
143 | } |
||
144 | do_test attach-1.17 { |
||
145 | catchsql { |
||
146 | ATTACH 'test.db' as MAIN; |
||
147 | } |
||
148 | } {1 {database MAIN is already in use}} |
||
149 | do_test attach-1.18 { |
||
150 | catchsql { |
||
151 | ATTACH 'test.db' as db10; |
||
152 | ATTACH 'test.db' as db11; |
||
153 | } |
||
154 | } {0 {}} |
||
155 | if {$SQLITE_MAX_ATTACHED==10} { |
||
156 | do_test attach-1.19 { |
||
157 | catchsql { |
||
158 | ATTACH 'test.db' as db12; |
||
159 | } |
||
160 | } {1 {too many attached databases - max 10}} |
||
161 | do_test attach-1.19.1 { |
||
162 | db errorcode |
||
163 | } {1} |
||
164 | } |
||
165 | do_test attach-1.20.1 { |
||
166 | execsql { |
||
167 | DETACH db5; |
||
168 | } |
||
169 | } {} |
||
170 | ifcapable schema_pragmas { |
||
171 | do_test attach-1.20.2 { |
||
172 | db_list db |
||
173 | } {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11} |
||
174 | } ;# ifcapable schema_pragmas |
||
175 | integrity_check attach-1.20.3 |
||
176 | ifcapable tempdb { |
||
177 | execsql {select * from sqlite_temp_master} |
||
178 | } |
||
179 | do_test attach-1.21 { |
||
180 | catchsql { |
||
181 | ATTACH 'test.db' as db12; |
||
182 | } |
||
183 | } {0 {}} |
||
184 | if {$SQLITE_MAX_ATTACHED==10} { |
||
185 | do_test attach-1.22 { |
||
186 | catchsql { |
||
187 | ATTACH 'test.db' as db13; |
||
188 | } |
||
189 | } {1 {too many attached databases - max 10}} |
||
190 | do_test attach-1.22.1 { |
||
191 | db errorcode |
||
192 | } {1} |
||
193 | } |
||
194 | do_test attach-1.23 { |
||
195 | catchsql { |
||
196 | DETACH "db14"; |
||
197 | } |
||
198 | } {1 {no such database: db14}} |
||
199 | do_test attach-1.24 { |
||
200 | catchsql { |
||
201 | DETACH db12; |
||
202 | } |
||
203 | } {0 {}} |
||
204 | do_test attach-1.25 { |
||
205 | catchsql { |
||
206 | DETACH db12; |
||
207 | } |
||
208 | } {1 {no such database: db12}} |
||
209 | do_test attach-1.26 { |
||
210 | catchsql { |
||
211 | DETACH main; |
||
212 | } |
||
213 | } {1 {cannot detach database main}} |
||
214 | |||
215 | ifcapable tempdb { |
||
216 | do_test attach-1.27 { |
||
217 | catchsql { |
||
218 | DETACH Temp; |
||
219 | } |
||
220 | } {1 {cannot detach database Temp}} |
||
221 | } else { |
||
222 | do_test attach-1.27 { |
||
223 | catchsql { |
||
224 | DETACH Temp; |
||
225 | } |
||
226 | } {1 {no such database: Temp}} |
||
227 | } |
||
228 | |||
229 | do_test attach-1.28 { |
||
230 | catchsql { |
||
231 | DETACH db11; |
||
232 | DETACH db10; |
||
233 | DETACH db9; |
||
234 | DETACH db8; |
||
235 | DETACH db7; |
||
236 | DETACH db6; |
||
237 | DETACH db4; |
||
238 | DETACH db3; |
||
239 | DETACH db2; |
||
240 | } |
||
241 | } {0 {}} |
||
242 | ifcapable schema_pragmas { |
||
243 | ifcapable tempdb { |
||
244 | do_test attach-1.29 { |
||
245 | db_list db |
||
246 | } {0 main 1 temp} |
||
247 | } else { |
||
248 | do_test attach-1.29 { |
||
249 | db_list db |
||
250 | } {0 main} |
||
251 | } |
||
252 | } ;# ifcapable schema_pragmas |
||
253 | |||
254 | ifcapable {trigger} { # Only do the following tests if triggers are enabled |
||
255 | do_test attach-2.1 { |
||
256 | execsql { |
||
257 | CREATE TABLE tx(x1,x2,y1,y2); |
||
258 | CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN |
||
259 | INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y); |
||
260 | END; |
||
261 | SELECT * FROM tx; |
||
262 | } db2; |
||
263 | } {} |
||
264 | do_test attach-2.2 { |
||
265 | execsql { |
||
266 | UPDATE t2 SET x=x+10; |
||
267 | SELECT * FROM tx; |
||
268 | } db2; |
||
269 | } {1 11 x x 2 12 y y} |
||
270 | do_test attach-2.3 { |
||
271 | execsql { |
||
272 | CREATE TABLE tx(x1,x2,y1,y2); |
||
273 | SELECT * FROM tx; |
||
274 | } |
||
275 | } {} |
||
276 | do_test attach-2.4 { |
||
277 | execsql { |
||
278 | ATTACH 'test2.db' AS db2; |
||
279 | } |
||
280 | } {} |
||
281 | do_test attach-2.5 { |
||
282 | execsql { |
||
283 | UPDATE db2.t2 SET x=x+10; |
||
284 | SELECT * FROM db2.tx; |
||
285 | } |
||
286 | } {1 11 x x 2 12 y y 11 21 x x 12 22 y y} |
||
287 | do_test attach-2.6 { |
||
288 | execsql { |
||
289 | SELECT * FROM main.tx; |
||
290 | } |
||
291 | } {} |
||
292 | do_test attach-2.7 { |
||
293 | execsql { |
||
294 | SELECT type, name, tbl_name FROM db2.sqlite_master; |
||
295 | } |
||
296 | } {table t2 t2 table tx tx trigger r1 t2} |
||
297 | |||
298 | ifcapable schema_pragmas&&tempdb { |
||
299 | do_test attach-2.8 { |
||
300 | db_list db |
||
301 | } {0 main 1 temp 2 db2} |
||
302 | } ;# ifcapable schema_pragmas&&tempdb |
||
303 | ifcapable schema_pragmas&&!tempdb { |
||
304 | do_test attach-2.8 { |
||
305 | db_list db |
||
306 | } {0 main 2 db2} |
||
307 | } ;# ifcapable schema_pragmas&&!tempdb |
||
308 | |||
309 | do_test attach-2.9 { |
||
310 | execsql { |
||
311 | CREATE INDEX i2 ON t2(x); |
||
312 | SELECT * FROM t2 WHERE x>5; |
||
313 | } db2 |
||
314 | } {21 x 22 y} |
||
315 | do_test attach-2.10 { |
||
316 | execsql { |
||
317 | SELECT type, name, tbl_name FROM sqlite_master; |
||
318 | } db2 |
||
319 | } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
||
320 | #do_test attach-2.11 { |
||
321 | # catchsql { |
||
322 | # SELECT * FROM t2 WHERE x>5; |
||
323 | # } |
||
324 | #} {1 {database schema has changed}} |
||
325 | ifcapable schema_pragmas { |
||
326 | ifcapable tempdb { |
||
327 | do_test attach-2.12 { |
||
328 | db_list db |
||
329 | } {0 main 1 temp 2 db2} |
||
330 | } else { |
||
331 | do_test attach-2.12 { |
||
332 | db_list db |
||
333 | } {0 main 2 db2} |
||
334 | } |
||
335 | } ;# ifcapable schema_pragmas |
||
336 | do_test attach-2.13 { |
||
337 | catchsql { |
||
338 | SELECT * FROM t2 WHERE x>5; |
||
339 | } |
||
340 | } {0 {21 x 22 y}} |
||
341 | do_test attach-2.14 { |
||
342 | execsql { |
||
343 | SELECT type, name, tbl_name FROM sqlite_master; |
||
344 | } |
||
345 | } {table t1 t1 table tx tx} |
||
346 | do_test attach-2.15 { |
||
347 | execsql { |
||
348 | SELECT type, name, tbl_name FROM db2.sqlite_master; |
||
349 | } |
||
350 | } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
||
351 | do_test attach-2.16 { |
||
352 | db close |
||
353 | sqlite3 db test.db |
||
354 | execsql { |
||
355 | ATTACH 'test2.db' AS db2; |
||
356 | SELECT type, name, tbl_name FROM db2.sqlite_master; |
||
357 | } |
||
358 | } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
||
359 | } ;# End of ifcapable {trigger} |
||
360 | |||
361 | do_test attach-3.1 { |
||
362 | db close |
||
363 | db2 close |
||
364 | sqlite3 db test.db |
||
365 | sqlite3 db2 test2.db |
||
366 | execsql { |
||
367 | SELECT * FROM t1 |
||
368 | } |
||
369 | } {1 2 3 4} |
||
370 | |||
371 | # If we are testing a version of the code that lacks trigger support, |
||
372 | # adjust the database contents so that they are the same if triggers |
||
373 | # had been enabled. |
||
374 | ifcapable {!trigger} { |
||
375 | db2 eval { |
||
376 | DELETE FROM t2; |
||
377 | INSERT INTO t2 VALUES(21, 'x'); |
||
378 | INSERT INTO t2 VALUES(22, 'y'); |
||
379 | CREATE TABLE tx(x1,x2,y1,y2); |
||
380 | INSERT INTO tx VALUES(1, 11, 'x', 'x'); |
||
381 | INSERT INTO tx VALUES(2, 12, 'y', 'y'); |
||
382 | INSERT INTO tx VALUES(11, 21, 'x', 'x'); |
||
383 | INSERT INTO tx VALUES(12, 22, 'y', 'y'); |
||
384 | CREATE INDEX i2 ON t2(x); |
||
385 | } |
||
386 | } |
||
387 | |||
388 | do_test attach-3.2 { |
||
389 | catchsql { |
||
390 | SELECT * FROM t2 |
||
391 | } |
||
392 | } {1 {no such table: t2}} |
||
393 | do_test attach-3.3 { |
||
394 | catchsql { |
||
395 | ATTACH DATABASE 'test2.db' AS db2; |
||
396 | SELECT * FROM t2 |
||
397 | } |
||
398 | } {0 {21 x 22 y}} |
||
399 | |||
400 | # Even though 'db' has started a transaction, it should not yet have |
||
401 | # a lock on test2.db so 'db2' should be readable. |
||
402 | do_test attach-3.4 { |
||
403 | execsql BEGIN |
||
404 | catchsql { |
||
405 | SELECT * FROM t2; |
||
406 | } db2; |
||
407 | } {0 {21 x 22 y}} |
||
408 | |||
409 | # Reading from test2.db from db within a transaction should not |
||
410 | # prevent test2.db from being read by db2. |
||
411 | do_test attach-3.5 { |
||
412 | execsql {SELECT * FROM t2} |
||
413 | catchsql { |
||
414 | SELECT * FROM t2; |
||
415 | } db2; |
||
416 | } {0 {21 x 22 y}} |
||
417 | |||
418 | # Making a change to test2.db through db causes test2.db to get |
||
419 | # a reserved lock. It should still be accessible through db2. |
||
420 | do_test attach-3.6 { |
||
421 | execsql { |
||
422 | UPDATE t2 SET x=x+1 WHERE x=50; |
||
423 | } |
||
424 | catchsql { |
||
425 | SELECT * FROM t2; |
||
426 | } db2; |
||
427 | } {0 {21 x 22 y}} |
||
428 | |||
429 | do_test attach-3.7 { |
||
430 | execsql ROLLBACK |
||
431 | execsql {SELECT * FROM t2} db2 |
||
432 | } {21 x 22 y} |
||
433 | |||
434 | # Start transactions on both db and db2. Once again, just because |
||
435 | # we make a change to test2.db using db2, only a RESERVED lock is |
||
436 | # obtained, so test2.db should still be readable using db. |
||
437 | # |
||
438 | do_test attach-3.8 { |
||
439 | execsql BEGIN |
||
440 | execsql BEGIN db2 |
||
441 | execsql {UPDATE t2 SET x=0 WHERE 0} db2 |
||
442 | catchsql {SELECT * FROM t2} |
||
443 | } {0 {21 x 22 y}} |
||
444 | |||
445 | # It is also still accessible from db2. |
||
446 | do_test attach-3.9 { |
||
447 | catchsql {SELECT * FROM t2} db2 |
||
448 | } {0 {21 x 22 y}} |
||
449 | |||
450 | do_test attach-3.10 { |
||
451 | execsql {SELECT * FROM t1} |
||
452 | } {1 2 3 4} |
||
453 | |||
454 | do_test attach-3.11 { |
||
455 | catchsql {UPDATE t1 SET a=a+1} |
||
456 | } {0 {}} |
||
457 | do_test attach-3.12 { |
||
458 | execsql {SELECT * FROM t1} |
||
459 | } {2 2 4 4} |
||
460 | |||
461 | # db2 has a RESERVED lock on test2.db, so db cannot write to any tables |
||
462 | # in test2.db. |
||
463 | do_test attach-3.13 { |
||
464 | catchsql {UPDATE t2 SET x=x+1 WHERE x=50} |
||
465 | } {1 {database is locked}} |
||
466 | |||
467 | # Change for version 3. Transaction is no longer rolled back |
||
468 | # for a locked database. |
||
469 | execsql {ROLLBACK} |
||
470 | |||
471 | # db is able to reread its schema because db2 still only holds a |
||
472 | # reserved lock. |
||
473 | do_test attach-3.14 { |
||
474 | catchsql {SELECT * FROM t1} |
||
475 | } {0 {1 2 3 4}} |
||
476 | do_test attach-3.15 { |
||
477 | execsql COMMIT db2 |
||
478 | execsql {SELECT * FROM t1} |
||
479 | } {1 2 3 4} |
||
480 | |||
481 | # Ticket #323 |
||
482 | do_test attach-4.1 { |
||
483 | execsql {DETACH db2} |
||
484 | db2 close |
||
485 | sqlite3 db2 test2.db |
||
486 | execsql { |
||
487 | CREATE TABLE t3(x,y); |
||
488 | CREATE UNIQUE INDEX t3i1 ON t3(x); |
||
489 | INSERT INTO t3 VALUES(1,2); |
||
490 | SELECT * FROM t3; |
||
491 | } db2; |
||
492 | } {1 2} |
||
493 | do_test attach-4.2 { |
||
494 | execsql { |
||
495 | CREATE TABLE t3(a,b); |
||
496 | CREATE UNIQUE INDEX t3i1b ON t3(a); |
||
497 | INSERT INTO t3 VALUES(9,10); |
||
498 | SELECT * FROM t3; |
||
499 | } |
||
500 | } {9 10} |
||
501 | do_test attach-4.3 { |
||
502 | execsql { |
||
503 | ATTACH DATABASE 'test2.db' AS db2; |
||
504 | SELECT * FROM db2.t3; |
||
505 | } |
||
506 | } {1 2} |
||
507 | do_test attach-4.4 { |
||
508 | execsql { |
||
509 | SELECT * FROM main.t3; |
||
510 | } |
||
511 | } {9 10} |
||
512 | do_test attach-4.5 { |
||
513 | execsql { |
||
514 | INSERT INTO db2.t3 VALUES(9,10); |
||
515 | SELECT * FROM db2.t3; |
||
516 | } |
||
517 | } {1 2 9 10} |
||
518 | execsql { |
||
519 | DETACH db2; |
||
520 | } |
||
521 | ifcapable {trigger} { |
||
522 | do_test attach-4.6 { |
||
523 | execsql { |
||
524 | CREATE TABLE t4(x); |
||
525 | CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN |
||
526 | INSERT INTO t4 VALUES('db2.' || NEW.x); |
||
527 | END; |
||
528 | INSERT INTO t3 VALUES(6,7); |
||
529 | SELECT * FROM t4; |
||
530 | } db2 |
||
531 | } {db2.6} |
||
532 | do_test attach-4.7 { |
||
533 | execsql { |
||
534 | CREATE TABLE t4(y); |
||
535 | CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN |
||
536 | INSERT INTO t4 VALUES('main.' || NEW.a); |
||
537 | END; |
||
538 | INSERT INTO main.t3 VALUES(11,12); |
||
539 | SELECT * FROM main.t4; |
||
540 | } |
||
541 | } {main.11} |
||
542 | } |
||
543 | ifcapable {!trigger} { |
||
544 | # When we do not have trigger support, set up the table like they |
||
545 | # would have been had triggers been there. The tests that follow need |
||
546 | # this setup. |
||
547 | execsql { |
||
548 | CREATE TABLE t4(x); |
||
549 | INSERT INTO t3 VALUES(6,7); |
||
550 | INSERT INTO t4 VALUES('db2.6'); |
||
551 | INSERT INTO t4 VALUES('db2.13'); |
||
552 | } db2 |
||
553 | execsql { |
||
554 | CREATE TABLE t4(y); |
||
555 | INSERT INTO main.t3 VALUES(11,12); |
||
556 | INSERT INTO t4 VALUES('main.11'); |
||
557 | } |
||
558 | } |
||
559 | |||
560 | |||
561 | # This one is tricky. On the UNION ALL select, we have to make sure |
||
562 | # the schema for both main and db2 is valid before starting to execute |
||
563 | # the first query of the UNION ALL. If we wait to test the validity of |
||
564 | # the schema for main until after the first query has run, that test will |
||
565 | # fail and the query will abort but we will have already output some |
||
566 | # results. When the query is retried, the results will be repeated. |
||
567 | # |
||
568 | ifcapable compound { |
||
569 | do_test attach-4.8 { |
||
570 | execsql { |
||
571 | ATTACH DATABASE 'test2.db' AS db2; |
||
572 | INSERT INTO db2.t3 VALUES(13,14); |
||
573 | SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4; |
||
574 | } |
||
575 | } {db2.6 db2.13 main.11} |
||
576 | |||
577 | do_test attach-4.9 { |
||
578 | ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}} |
||
579 | execsql { |
||
580 | INSERT INTO main.t3 VALUES(15,16); |
||
581 | SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4; |
||
582 | } |
||
583 | } {db2.6 db2.13 main.11 main.15} |
||
584 | } ;# ifcapable compound |
||
585 | |||
586 | ifcapable !compound { |
||
587 | ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}} |
||
588 | execsql { |
||
589 | ATTACH DATABASE 'test2.db' AS db2; |
||
590 | INSERT INTO db2.t3 VALUES(13,14); |
||
591 | INSERT INTO main.t3 VALUES(15,16); |
||
592 | } |
||
593 | } ;# ifcapable !compound |
||
594 | |||
595 | ifcapable view { |
||
596 | do_test attach-4.10 { |
||
597 | execsql { |
||
598 | DETACH DATABASE db2; |
||
599 | } |
||
600 | execsql { |
||
601 | CREATE VIEW v3 AS SELECT x*100+y FROM t3; |
||
602 | SELECT * FROM v3; |
||
603 | } db2 |
||
604 | } {102 910 607 1314} |
||
605 | do_test attach-4.11 { |
||
606 | execsql { |
||
607 | CREATE VIEW v3 AS SELECT a*100+b FROM t3; |
||
608 | SELECT * FROM v3; |
||
609 | } |
||
610 | } {910 1112 1516} |
||
611 | do_test attach-4.12 { |
||
612 | execsql { |
||
613 | ATTACH DATABASE 'test2.db' AS db2; |
||
614 | SELECT * FROM db2.v3; |
||
615 | } |
||
616 | } {102 910 607 1314} |
||
617 | do_test attach-4.13 { |
||
618 | execsql { |
||
619 | SELECT * FROM main.v3; |
||
620 | } |
||
621 | } {910 1112 1516} |
||
622 | } ;# ifcapable view |
||
623 | |||
624 | # Tests for the sqliteFix...() routines in attach.c |
||
625 | # |
||
626 | ifcapable {trigger} { |
||
627 | do_test attach-5.1 { |
||
628 | db close |
||
629 | sqlite3 db test.db |
||
630 | db2 close |
||
631 | file delete -force test2.db |
||
632 | sqlite3 db2 test2.db |
||
633 | catchsql { |
||
634 | ATTACH DATABASE 'test.db' AS orig; |
||
635 | CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN |
||
636 | SELECT 'no-op'; |
||
637 | END; |
||
638 | } db2 |
||
639 | } {1 {trigger r1 cannot reference objects in database orig}} |
||
640 | do_test attach-5.2 { |
||
641 | catchsql { |
||
642 | CREATE TABLE t5(x,y); |
||
643 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
644 | SELECT 'no-op'; |
||
645 | END; |
||
646 | } db2 |
||
647 | } {0 {}} |
||
648 | do_test attach-5.3 { |
||
649 | catchsql { |
||
650 | DROP TRIGGER r5; |
||
651 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
652 | SELECT 'no-op' FROM orig.t1; |
||
653 | END; |
||
654 | } db2 |
||
655 | } {1 {trigger r5 cannot reference objects in database orig}} |
||
656 | ifcapable tempdb { |
||
657 | do_test attach-5.4 { |
||
658 | catchsql { |
||
659 | CREATE TEMP TABLE t6(p,q,r); |
||
660 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
661 | SELECT 'no-op' FROM temp.t6; |
||
662 | END; |
||
663 | } db2 |
||
664 | } {1 {trigger r5 cannot reference objects in database temp}} |
||
665 | } |
||
666 | ifcapable subquery { |
||
667 | do_test attach-5.5 { |
||
668 | catchsql { |
||
669 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
670 | SELECT 'no-op' || (SELECT * FROM temp.t6); |
||
671 | END; |
||
672 | } db2 |
||
673 | } {1 {trigger r5 cannot reference objects in database temp}} |
||
674 | do_test attach-5.6 { |
||
675 | catchsql { |
||
676 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
677 | SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6); |
||
678 | END; |
||
679 | } db2 |
||
680 | } {1 {trigger r5 cannot reference objects in database temp}} |
||
681 | do_test attach-5.7 { |
||
682 | catchsql { |
||
683 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
684 | SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6); |
||
685 | END; |
||
686 | } db2 |
||
687 | } {1 {trigger r5 cannot reference objects in database temp}} |
||
688 | do_test attach-5.7 { |
||
689 | catchsql { |
||
690 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
691 | SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1; |
||
692 | END; |
||
693 | } db2 |
||
694 | } {1 {trigger r5 cannot reference objects in database temp}} |
||
695 | do_test attach-5.8 { |
||
696 | catchsql { |
||
697 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
698 | INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5); |
||
699 | END; |
||
700 | } db2 |
||
701 | } {1 {trigger r5 cannot reference objects in database temp}} |
||
702 | do_test attach-5.9 { |
||
703 | catchsql { |
||
704 | CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
||
705 | DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6); |
||
706 | END; |
||
707 | } db2 |
||
708 | } {1 {trigger r5 cannot reference objects in database temp}} |
||
709 | } ;# endif subquery |
||
710 | } ;# endif trigger |
||
711 | |||
712 | # Check to make sure we get a sensible error if unable to open |
||
713 | # the file that we are trying to attach. |
||
714 | # |
||
715 | do_test attach-6.1 { |
||
716 | catchsql { |
||
717 | ATTACH DATABASE 'no-such-file' AS nosuch; |
||
718 | } |
||
719 | } {0 {}} |
||
720 | if {$tcl_platform(platform)=="unix"} { |
||
721 | do_test attach-6.2 { |
||
722 | sqlite3 dbx cannot-read |
||
723 | dbx eval {CREATE TABLE t1(a,b,c)} |
||
724 | dbx close |
||
725 | file attributes cannot-read -permission 0000 |
||
726 | if {[file writable cannot-read]} { |
||
727 | puts "\n**** Tests do not work when run as root ****" |
||
728 | file delete -force cannot-read |
||
729 | exit 1 |
||
730 | } |
||
731 | catchsql { |
||
732 | ATTACH DATABASE 'cannot-read' AS noread; |
||
733 | } |
||
734 | } {1 {unable to open database: cannot-read}} |
||
735 | do_test attach-6.2.2 { |
||
736 | db errorcode |
||
737 | } {14} |
||
738 | file delete -force cannot-read |
||
739 | } |
||
740 | |||
741 | # Check the error message if we try to access a database that has |
||
742 | # not been attached. |
||
743 | do_test attach-6.3 { |
||
744 | catchsql { |
||
745 | CREATE TABLE no_such_db.t1(a, b, c); |
||
746 | } |
||
747 | } {1 {unknown database no_such_db}} |
||
748 | for {set i 2} {$i<=15} {incr i} { |
||
749 | catch {db$i close} |
||
750 | } |
||
751 | db close |
||
752 | file delete -force test2.db |
||
753 | file delete -force no-such-file |
||
754 | |||
755 | ifcapable subquery { |
||
756 | do_test attach-7.1 { |
||
757 | file delete -force test.db test.db-journal |
||
758 | sqlite3 db test.db |
||
759 | catchsql { |
||
760 | DETACH RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY |
||
761 | REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL ) |
||
762 | } |
||
763 | } {1 {no such table: AAAAAA}} |
||
764 | } |
||
765 | |||
766 | # Create a malformed file (a file that is not a valid database) |
||
767 | # and try to attach it |
||
768 | # |
||
769 | do_test attach-8.1 { |
||
770 | set fd [open test2.db w] |
||
771 | puts $fd "This file is not a valid SQLite database" |
||
772 | close $fd |
||
773 | catchsql { |
||
774 | ATTACH 'test2.db' AS t2; |
||
775 | } |
||
776 | } {1 {file is encrypted or is not a database}} |
||
777 | do_test attach-8.2 { |
||
778 | db errorcode |
||
779 | } {26} |
||
780 | file delete -force test2.db |
||
781 | do_test attach-8.3 { |
||
782 | sqlite3 db2 test2.db |
||
783 | db2 eval {CREATE TABLE t1(x); BEGIN EXCLUSIVE} |
||
784 | catchsql { |
||
785 | ATTACH 'test2.db' AS t2; |
||
786 | } |
||
787 | } {1 {database is locked}} |
||
788 | do_test attach-8.4 { |
||
789 | db errorcode |
||
790 | } {5} |
||
791 | db2 close |
||
792 | file delete -force test2.db |
||
793 | |||
794 | # Test that it is possible to attach the same database more than |
||
795 | # once when not in shared-cache mode. That this is not possible in |
||
796 | # shared-cache mode is tested in shared7.test. |
||
797 | do_test attach-9.1 { |
||
798 | file delete -force test4.db |
||
799 | execsql { |
||
800 | ATTACH 'test4.db' AS aux1; |
||
801 | CREATE TABLE aux1.t1(a, b); |
||
802 | INSERT INTO aux1.t1 VALUES(1, 2); |
||
803 | ATTACH 'test4.db' AS aux2; |
||
804 | SELECT * FROM aux2.t1; |
||
805 | } |
||
806 | } {1 2} |
||
807 | do_test attach-9.2 { |
||
808 | catchsql { |
||
809 | BEGIN; |
||
810 | INSERT INTO aux1.t1 VALUES(3, 4); |
||
811 | INSERT INTO aux2.t1 VALUES(5, 6); |
||
812 | } |
||
813 | } {1 {database is locked}} |
||
814 | do_test attach-9.3 { |
||
815 | execsql { |
||
816 | COMMIT; |
||
817 | SELECT * FROM aux2.t1; |
||
818 | } |
||
819 | } {1 2 3 4} |
||
820 | |||
821 | # Ticket [abe728bbc311d81334dae9762f0db87c07a98f79]. |
||
822 | # Multi-database commit on an attached TEMP database. |
||
823 | # |
||
824 | do_test attach-10.1 { |
||
825 | execsql { |
||
826 | ATTACH '' AS noname; |
||
827 | ATTACH ':memory:' AS inmem; |
||
828 | BEGIN; |
||
829 | CREATE TABLE noname.noname(x); |
||
830 | CREATE TABLE inmem.inmem(y); |
||
831 | CREATE TABLE main.main(z); |
||
832 | COMMIT; |
||
833 | SELECT name FROM noname.sqlite_master; |
||
834 | SELECT name FROM inmem.sqlite_master; |
||
835 | } |
||
836 | } {noname inmem} |
||
837 | do_test attach-10.2 { |
||
838 | lrange [execsql { |
||
839 | PRAGMA database_list; |
||
840 | }] 9 end |
||
841 | } {4 noname {} 5 inmem {}} |
||
842 | |||
843 | finish_test |