blob: d57f5bfb52766365fb808db51b8f22ed9d707cb5 [file] [log] [blame]
drh2d458342003-04-05 03:42:26 +00001# 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#
drhfd773cf2009-05-29 14:39:07 +000015# $Id: attach.test,v 1.52 2009/05/29 14:39:08 drh Exp $
drh2d458342003-04-05 03:42:26 +000016#
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
danielk19775a8f9372007-10-09 08:29:32 +000021ifcapable !attach {
22 finish_test
23 return
24}
25
drha73af532003-04-05 16:56:28 +000026for {set i 2} {$i<=15} {incr i} {
mistachkinfda06be2011-08-02 00:57:34 +000027 forcedelete test$i.db
28 forcedelete test$i.db-journal
drha73af532003-04-05 16:56:28 +000029}
drh2d458342003-04-05 03:42:26 +000030
31do_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}
39do_test attach-1.2 {
drhef4ac8f2004-06-19 00:16:31 +000040 sqlite3 db2 test2.db
drh2d458342003-04-05 03:42:26 +000041 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;
drha73af532003-04-05 16:56:28 +000046 } db2
drh2d458342003-04-05 03:42:26 +000047} {1 x 2 y}
48do_test attach-1.3 {
49 execsql {
50 ATTACH DATABASE 'test2.db' AS two;
51 SELECT * FROM two.t2;
52 }
53} {1 x 2 y}
54do_test attach-1.4 {
55 execsql {
56 SELECT * FROM t2;
57 }
58} {1 x 2 y}
59do_test attach-1.5 {
60 execsql {
61 DETACH DATABASE two;
62 SELECT * FROM t1;
63 }
64} {1 2 3 4}
65do_test attach-1.6 {
66 catchsql {
67 SELECT * FROM t2;
68 }
69} {1 {no such table: t2}}
70do_test attach-1.7 {
71 catchsql {
72 SELECT * FROM two.t2;
73 }
74} {1 {no such table: two.t2}}
drha73af532003-04-05 16:56:28 +000075do_test attach-1.8 {
76 catchsql {
77 ATTACH DATABASE 'test3.db' AS three;
78 }
danielk19773df6b252004-05-29 10:23:19 +000079} {0 {}}
drha73af532003-04-05 16:56:28 +000080do_test attach-1.9 {
81 catchsql {
82 SELECT * FROM three.sqlite_master;
83 }
danielk19773df6b252004-05-29 10:23:19 +000084} {0 {}}
drha73af532003-04-05 16:56:28 +000085do_test attach-1.10 {
86 catchsql {
drhae8b3612005-03-15 02:04:12 +000087 DETACH DATABASE [three];
drha73af532003-04-05 16:56:28 +000088 }
danielk19773df6b252004-05-29 10:23:19 +000089} {0 {}}
drha73af532003-04-05 16:56:28 +000090do_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} {}
drhb8ec2092003-06-04 15:53:02 +0000102proc db_list {db} {
103 set list {}
104 foreach {idx name file} [execsql {PRAGMA database_list} $db] {
105 lappend list $idx $name
drha73af532003-04-05 16:56:28 +0000106 }
drhb8ec2092003-06-04 15:53:02 +0000107 return $list
108}
danielk197727188fb2004-11-23 10:13:03 +0000109ifcapable schema_pragmas {
drhb8ec2092003-06-04 15:53:02 +0000110do_test attach-1.11b {
111 db_list db
drh34f47322004-08-18 15:58:22 +0000112} {0 main 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9}
danielk197727188fb2004-11-23 10:13:03 +0000113} ;# ifcapable schema_pragmas
drha73af532003-04-05 16:56:28 +0000114do_test attach-1.12 {
115 catchsql {
116 ATTACH 'test.db' as db2;
117 }
118} {1 {database db2 is already in use}}
drh69544ec2008-02-06 14:11:34 +0000119do_test attach-1.12.2 {
120 db errorcode
121} {1}
drha73af532003-04-05 16:56:28 +0000122do_test attach-1.13 {
123 catchsql {
124 ATTACH 'test.db' as db5;
125 }
126} {1 {database db5 is already in use}}
127do_test attach-1.14 {
128 catchsql {
129 ATTACH 'test.db' as db9;
130 }
131} {1 {database db9 is already in use}}
132do_test attach-1.15 {
133 catchsql {
134 ATTACH 'test.db' as main;
135 }
136} {1 {database main is already in use}}
danielk197753c0f742005-03-29 03:10:59 +0000137ifcapable 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}
drha73af532003-04-05 16:56:28 +0000144do_test attach-1.17 {
145 catchsql {
146 ATTACH 'test.db' as MAIN;
147 }
148} {1 {database MAIN is already in use}}
149do_test attach-1.18 {
150 catchsql {
151 ATTACH 'test.db' as db10;
152 ATTACH 'test.db' as db11;
153 }
154} {0 {}}
drh01c7dc82011-03-23 18:22:34 +0000155if {$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}
drhed717fe2003-06-15 23:42:24 +0000165do_test attach-1.20.1 {
drha73af532003-04-05 16:56:28 +0000166 execsql {
167 DETACH db5;
drha73af532003-04-05 16:56:28 +0000168 }
danielk197727188fb2004-11-23 10:13:03 +0000169} {}
170ifcapable schema_pragmas {
171do_test attach-1.20.2 {
drhb8ec2092003-06-04 15:53:02 +0000172 db_list db
drh34f47322004-08-18 15:58:22 +0000173} {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11}
danielk197727188fb2004-11-23 10:13:03 +0000174} ;# ifcapable schema_pragmas
danielk1977a21c6b62005-01-24 10:25:59 +0000175integrity_check attach-1.20.3
danielk197753c0f742005-03-29 03:10:59 +0000176ifcapable tempdb {
177 execsql {select * from sqlite_temp_master}
178}
drha73af532003-04-05 16:56:28 +0000179do_test attach-1.21 {
180 catchsql {
181 ATTACH 'test.db' as db12;
182 }
183} {0 {}}
drh01c7dc82011-03-23 18:22:34 +0000184if {$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}
drha73af532003-04-05 16:56:28 +0000194do_test attach-1.23 {
195 catchsql {
drhae8b3612005-03-15 02:04:12 +0000196 DETACH "db14";
drha73af532003-04-05 16:56:28 +0000197 }
198} {1 {no such database: db14}}
199do_test attach-1.24 {
200 catchsql {
201 DETACH db12;
202 }
203} {0 {}}
204do_test attach-1.25 {
205 catchsql {
206 DETACH db12;
207 }
208} {1 {no such database: db12}}
209do_test attach-1.26 {
210 catchsql {
211 DETACH main;
212 }
213} {1 {cannot detach database main}}
danielk197753c0f742005-03-29 03:10:59 +0000214
215ifcapable 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
drha73af532003-04-05 16:56:28 +0000229do_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 {}}
danielk197727188fb2004-11-23 10:13:03 +0000242ifcapable schema_pragmas {
danielk197753c0f742005-03-29 03:10:59 +0000243 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 }
danielk197727188fb2004-11-23 10:13:03 +0000252} ;# ifcapable schema_pragmas
drh2d458342003-04-05 03:42:26 +0000253
drhb7f91642004-10-31 02:22:47 +0000254ifcapable {trigger} { # Only do the following tests if triggers are enabled
drha69d9162003-04-17 22:57:53 +0000255do_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} {}
264do_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}
270do_test attach-2.3 {
271 execsql {
272 CREATE TABLE tx(x1,x2,y1,y2);
273 SELECT * FROM tx;
274 }
275} {}
276do_test attach-2.4 {
277 execsql {
278 ATTACH 'test2.db' AS db2;
279 }
280} {}
281do_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}
287do_test attach-2.6 {
288 execsql {
289 SELECT * FROM main.tx;
290 }
291} {}
drh8bf8dc92003-05-17 17:35:10 +0000292do_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}
danielk197753c0f742005-03-29 03:10:59 +0000297
298ifcapable 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
303ifcapable schema_pragmas&&!tempdb {
304 do_test attach-2.8 {
305 db_list db
306 } {0 main 2 db2}
307} ;# ifcapable schema_pragmas&&!tempdb
308
drh8bf8dc92003-05-17 17:35:10 +0000309do_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}
315do_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}
drhb5f70c22004-02-14 01:39:50 +0000320#do_test attach-2.11 {
321# catchsql {
322# SELECT * FROM t2 WHERE x>5;
323# }
324#} {1 {database schema has changed}}
danielk197727188fb2004-11-23 10:13:03 +0000325ifcapable schema_pragmas {
danielk197753c0f742005-03-29 03:10:59 +0000326 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 }
danielk197727188fb2004-11-23 10:13:03 +0000335} ;# ifcapable schema_pragmas
drh8bf8dc92003-05-17 17:35:10 +0000336do_test attach-2.13 {
337 catchsql {
338 SELECT * FROM t2 WHERE x>5;
339 }
340} {0 {21 x 22 y}}
341do_test attach-2.14 {
342 execsql {
343 SELECT type, name, tbl_name FROM sqlite_master;
344 }
345} {table t1 t1 table tx tx}
346do_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}
351do_test attach-2.16 {
352 db close
drhef4ac8f2004-06-19 00:16:31 +0000353 sqlite3 db test.db
drh8bf8dc92003-05-17 17:35:10 +0000354 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}
drhb7f91642004-10-31 02:22:47 +0000359} ;# End of ifcapable {trigger}
drha73af532003-04-05 16:56:28 +0000360
drh6b861112003-05-17 19:23:51 +0000361do_test attach-3.1 {
362 db close
363 db2 close
drhef4ac8f2004-06-19 00:16:31 +0000364 sqlite3 db test.db
365 sqlite3 db2 test2.db
drh6b861112003-05-17 19:23:51 +0000366 execsql {
367 SELECT * FROM t1
368 }
369} {1 2 3 4}
drhb7f91642004-10-31 02:22:47 +0000370
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.
374ifcapable {!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
drh6b861112003-05-17 19:23:51 +0000388do_test attach-3.2 {
389 catchsql {
390 SELECT * FROM t2
391 }
392} {1 {no such table: t2}}
393do_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
drh80242052004-06-09 00:48:12 +0000400# Even though 'db' has started a transaction, it should not yet have
401# a lock on test2.db so 'db2' should be readable.
drh6b861112003-05-17 19:23:51 +0000402do_test attach-3.4 {
403 execsql BEGIN
404 catchsql {
405 SELECT * FROM t2;
406 } db2;
407} {0 {21 x 22 y}}
408
drh80242052004-06-09 00:48:12 +0000409# Reading from test2.db from db within a transaction should not
410# prevent test2.db from being read by db2.
drh6b861112003-05-17 19:23:51 +0000411do_test attach-3.5 {
412 execsql {SELECT * FROM t2}
413 catchsql {
414 SELECT * FROM t2;
415 } db2;
416} {0 {21 x 22 y}}
417
drh80242052004-06-09 00:48:12 +0000418# Making a change to test2.db through db causes test2.db to get
419# a reserved lock. It should still be accessible through db2.
drh6b861112003-05-17 19:23:51 +0000420do_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;
drh80242052004-06-09 00:48:12 +0000427} {0 {21 x 22 y}}
drh6b861112003-05-17 19:23:51 +0000428
429do_test attach-3.7 {
430 execsql ROLLBACK
431 execsql {SELECT * FROM t2} db2
432} {21 x 22 y}
drh80242052004-06-09 00:48:12 +0000433
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#
drh6b861112003-05-17 19:23:51 +0000438do_test attach-3.8 {
439 execsql BEGIN
440 execsql BEGIN db2
danielk19771d850a72004-05-31 08:26:49 +0000441 execsql {UPDATE t2 SET x=0 WHERE 0} db2
drh6b861112003-05-17 19:23:51 +0000442 catchsql {SELECT * FROM t2}
drh80242052004-06-09 00:48:12 +0000443} {0 {21 x 22 y}}
444
445# It is also still accessible from db2.
drh6b861112003-05-17 19:23:51 +0000446do_test attach-3.9 {
447 catchsql {SELECT * FROM t2} db2
448} {0 {21 x 22 y}}
drh80242052004-06-09 00:48:12 +0000449
drh6b861112003-05-17 19:23:51 +0000450do_test attach-3.10 {
451 execsql {SELECT * FROM t1}
452} {1 2 3 4}
drh80242052004-06-09 00:48:12 +0000453
drh6b861112003-05-17 19:23:51 +0000454do_test attach-3.11 {
455 catchsql {UPDATE t1 SET a=a+1}
456} {0 {}}
457do_test attach-3.12 {
458 execsql {SELECT * FROM t1}
459} {2 2 4 4}
drh80242052004-06-09 00:48:12 +0000460
461# db2 has a RESERVED lock on test2.db, so db cannot write to any tables
462# in test2.db.
drh6b861112003-05-17 19:23:51 +0000463do_test attach-3.13 {
464 catchsql {UPDATE t2 SET x=x+1 WHERE x=50}
465} {1 {database is locked}}
danielk19771d850a72004-05-31 08:26:49 +0000466
467# Change for version 3. Transaction is no longer rolled back
468# for a locked database.
469execsql {ROLLBACK}
470
drh80242052004-06-09 00:48:12 +0000471# db is able to reread its schema because db2 still only holds a
472# reserved lock.
drh6b861112003-05-17 19:23:51 +0000473do_test attach-3.14 {
drh8ef83ff2004-02-12 15:31:21 +0000474 catchsql {SELECT * FROM t1}
drh80242052004-06-09 00:48:12 +0000475} {0 {1 2 3 4}}
drh8ef83ff2004-02-12 15:31:21 +0000476do_test attach-3.15 {
477 execsql COMMIT db2
drh6b861112003-05-17 19:23:51 +0000478 execsql {SELECT * FROM t1}
479} {1 2 3 4}
480
drhf26e09c2003-05-31 16:21:12 +0000481# Ticket #323
482do_test attach-4.1 {
483 execsql {DETACH db2}
484 db2 close
drhef4ac8f2004-06-19 00:16:31 +0000485 sqlite3 db2 test2.db
drhf26e09c2003-05-31 16:21:12 +0000486 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}
493do_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}
501do_test attach-4.3 {
502 execsql {
503 ATTACH DATABASE 'test2.db' AS db2;
504 SELECT * FROM db2.t3;
505 }
506} {1 2}
507do_test attach-4.4 {
508 execsql {
509 SELECT * FROM main.t3;
510 }
511} {9 10}
512do_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}
drh798da522004-11-04 04:42:28 +0000518execsql {
519 DETACH db2;
520}
drhb7f91642004-10-31 02:22:47 +0000521ifcapable {trigger} {
522 do_test attach-4.6 {
523 execsql {
drhb7f91642004-10-31 02:22:47 +0000524 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}
drh798da522004-11-04 04:42:28 +0000543ifcapable {!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
drh80242052004-06-09 00:48:12 +0000560
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#
danielk197727c77432004-11-22 13:35:41 +0000568ifcapable compound {
drhf26e09c2003-05-31 16:21:12 +0000569do_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}
drh80242052004-06-09 00:48:12 +0000576
drhf26e09c2003-05-31 16:21:12 +0000577do_test attach-4.9 {
drh798da522004-11-04 04:42:28 +0000578 ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}
drhf26e09c2003-05-31 16:21:12 +0000579 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}
danielk197727c77432004-11-22 13:35:41 +0000584} ;# ifcapable compound
585
586ifcapable !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
danielk19770fa8ddb2004-11-22 08:43:32 +0000594
595ifcapable view {
drhf26e09c2003-05-31 16:21:12 +0000596do_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}
605do_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}
611do_test attach-4.12 {
612 execsql {
613 ATTACH DATABASE 'test2.db' AS db2;
614 SELECT * FROM db2.v3;
615 }
616} {102 910 607 1314}
617do_test attach-4.13 {
618 execsql {
619 SELECT * FROM main.v3;
620 }
621} {910 1112 1516}
danielk19770fa8ddb2004-11-22 08:43:32 +0000622} ;# ifcapable view
drhf26e09c2003-05-31 16:21:12 +0000623
drh4312db52003-06-03 01:47:11 +0000624# Tests for the sqliteFix...() routines in attach.c
625#
drh798da522004-11-04 04:42:28 +0000626ifcapable {trigger} {
drh4312db52003-06-03 01:47:11 +0000627do_test attach-5.1 {
628 db close
drhef4ac8f2004-06-19 00:16:31 +0000629 sqlite3 db test.db
drh9cb733c2003-07-18 01:25:34 +0000630 db2 close
mistachkinfda06be2011-08-02 00:57:34 +0000631 forcedelete test2.db
drhef4ac8f2004-06-19 00:16:31 +0000632 sqlite3 db2 test2.db
drh4312db52003-06-03 01:47:11 +0000633 catchsql {
634 ATTACH DATABASE 'test.db' AS orig;
drh86dac2b2006-05-25 12:17:31 +0000635 CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN
drh4312db52003-06-03 01:47:11 +0000636 SELECT 'no-op';
637 END;
638 } db2
danielk1977ef2cb632004-05-29 02:37:19 +0000639} {1 {trigger r1 cannot reference objects in database orig}}
drh4312db52003-06-03 01:47:11 +0000640do_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 {}}
648do_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}}
danielk197753c0f742005-03-29 03:10:59 +0000656ifcapable 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}
danielk1977e61b9f42005-01-21 04:25:47 +0000666ifcapable 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
drh798da522004-11-04 04:42:28 +0000710} ;# endif trigger
drh4312db52003-06-03 01:47:11 +0000711
712# Check to make sure we get a sensible error if unable to open
713# the file that we are trying to attach.
714#
715do_test attach-6.1 {
716 catchsql {
717 ATTACH DATABASE 'no-such-file' AS nosuch;
718 }
danielk19773df6b252004-05-29 10:23:19 +0000719} {0 {}}
drhbc2bca02003-06-04 12:31:53 +0000720if {$tcl_platform(platform)=="unix"} {
721 do_test attach-6.2 {
drhef4ac8f2004-06-19 00:16:31 +0000722 sqlite3 dbx cannot-read
drhbc2bca02003-06-04 12:31:53 +0000723 dbx eval {CREATE TABLE t1(a,b,c)}
724 dbx close
725 file attributes cannot-read -permission 0000
drh97ba4c92005-03-02 05:18:57 +0000726 if {[file writable cannot-read]} {
727 puts "\n**** Tests do not work when run as root ****"
mistachkinfda06be2011-08-02 00:57:34 +0000728 forcedelete cannot-read
drh97ba4c92005-03-02 05:18:57 +0000729 exit 1
730 }
drhbc2bca02003-06-04 12:31:53 +0000731 catchsql {
732 ATTACH DATABASE 'cannot-read' AS noread;
733 }
734 } {1 {unable to open database: cannot-read}}
drh69544ec2008-02-06 14:11:34 +0000735 do_test attach-6.2.2 {
736 db errorcode
737 } {14}
mistachkinfda06be2011-08-02 00:57:34 +0000738 forcedelete cannot-read
drhbc2bca02003-06-04 12:31:53 +0000739}
drhf26e09c2003-05-31 16:21:12 +0000740
danielk1977576ec6b2005-01-21 11:55:25 +0000741# Check the error message if we try to access a database that has
742# not been attached.
743do_test attach-6.3 {
744 catchsql {
745 CREATE TABLE no_such_db.t1(a, b, c);
746 }
747} {1 {unknown database no_such_db}}
drha73af532003-04-05 16:56:28 +0000748for {set i 2} {$i<=15} {incr i} {
749 catch {db$i close}
750}
drh02f9f6b2004-05-31 18:21:54 +0000751db close
mistachkinfda06be2011-08-02 00:57:34 +0000752forcedelete test2.db
753forcedelete no-such-file
drh2d458342003-04-05 03:42:26 +0000754
danielk19774152e672007-09-12 17:01:45 +0000755ifcapable subquery {
756 do_test attach-7.1 {
mistachkinfda06be2011-08-02 00:57:34 +0000757 forcedelete test.db test.db-journal
danielk19774152e672007-09-12 17:01:45 +0000758 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 }
drhfd773cf2009-05-29 14:39:07 +0000763 } {1 {no such table: AAAAAA}}
danielk19774152e672007-09-12 17:01:45 +0000764}
drh69544ec2008-02-06 14:11:34 +0000765
766# Create a malformed file (a file that is not a valid database)
767# and try to attach it
768#
769do_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}}
777do_test attach-8.2 {
778 db errorcode
779} {26}
mistachkinfda06be2011-08-02 00:57:34 +0000780forcedelete test2.db
drh69544ec2008-02-06 14:11:34 +0000781do_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}}
788do_test attach-8.4 {
789 db errorcode
790} {5}
791db2 close
mistachkinfda06be2011-08-02 00:57:34 +0000792forcedelete test2.db
drh69544ec2008-02-06 14:11:34 +0000793
danac887602009-11-20 10:18:06 +0000794# 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.
797do_test attach-9.1 {
mistachkinfda06be2011-08-02 00:57:34 +0000798 forcedelete test4.db
danac887602009-11-20 10:18:06 +0000799 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}
807do_test attach-9.2 {
danac887602009-11-20 10:18:06 +0000808 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}}
814do_test attach-9.3 {
danac887602009-11-20 10:18:06 +0000815 execsql {
816 COMMIT;
817 SELECT * FROM aux2.t1;
818 }
819} {1 2 3 4}
820
drh4413c432009-12-08 13:44:21 +0000821# Ticket [abe728bbc311d81334dae9762f0db87c07a98f79].
822# Multi-database commit on an attached TEMP database.
823#
824do_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}
837do_test attach-10.2 {
838 lrange [execsql {
839 PRAGMA database_list;
840 }] 9 end
841} {4 noname {} 5 inmem {}}
danbdd9af02010-11-18 16:14:24 +0000842
drh2d458342003-04-05 03:42:26 +0000843finish_test