4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
11 #***********************************************************************
12 # This file implements regression tests for SQLite library. The
13 # focus of this script is testing the ATTACH and DETACH commands
14 # and related functionality.
16 # $Id: attach.test,v 1.13 2004/02/14 01:39:50 drh Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 for {set i 2} {$i<=15} {incr i} {
23 file delete -force test$i.db
24 file delete -force test$i.db-journal
30 INSERT INTO t1 VALUES(1,2);
31 INSERT INTO t1 VALUES(3,4);
39 INSERT INTO t2 VALUES(1,'x');
40 INSERT INTO t2 VALUES(2,'y');
46 ATTACH DATABASE 'test2.db' AS two;
65 } {1 {no such table: t2}}
70 } {1 {no such table: two.t2}}
73 ATTACH DATABASE 'test3.db' AS three;
75 } {1 {cannot attach empty database: three}}
78 SELECT * FROM three.sqlite_master;
80 } {1 {no such table: three.sqlite_master}}
83 DETACH DATABASE three;
85 } {1 {no such database: three}}
88 ATTACH 'test.db' AS db2;
89 ATTACH 'test.db' AS db3;
90 ATTACH 'test.db' AS db4;
91 ATTACH 'test.db' AS db5;
92 ATTACH 'test.db' AS db6;
93 ATTACH 'test.db' AS db7;
94 ATTACH 'test.db' AS db8;
95 ATTACH 'test.db' AS db9;
100 foreach {idx name file} [execsql {PRAGMA database_list} $db] {
101 lappend list $idx $name
105 do_test attach-1.11b {
107 } {0 main 1 temp 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9}
108 do_test attach-1.12 {
110 ATTACH 'test.db' as db2;
112 } {1 {database db2 is already in use}}
113 do_test attach-1.13 {
115 ATTACH 'test.db' as db5;
117 } {1 {database db5 is already in use}}
118 do_test attach-1.14 {
120 ATTACH 'test.db' as db9;
122 } {1 {database db9 is already in use}}
123 do_test attach-1.15 {
125 ATTACH 'test.db' as main;
127 } {1 {database main is already in use}}
128 do_test attach-1.16 {
130 ATTACH 'test.db' as temp;
132 } {1 {database temp is already in use}}
133 do_test attach-1.17 {
135 ATTACH 'test.db' as MAIN;
137 } {1 {database MAIN is already in use}}
138 do_test attach-1.18 {
140 ATTACH 'test.db' as db10;
141 ATTACH 'test.db' as db11;
144 do_test attach-1.19 {
146 ATTACH 'test.db' as db12;
148 } {1 {too many attached databases - max 10}}
149 do_test attach-1.20.1 {
154 } {0 main 1 temp 2 db2 3 db3 4 db4 5 db11 6 db6 7 db7 8 db8 9 db9 10 db10}
155 integrity_check attach-1.20.2
156 do_test attach-1.21 {
158 ATTACH 'test.db' as db12;
161 do_test attach-1.22 {
163 ATTACH 'test.db' as db13;
165 } {1 {too many attached databases - max 10}}
166 do_test attach-1.23 {
170 } {1 {no such database: db14}}
171 do_test attach-1.24 {
176 do_test attach-1.25 {
180 } {1 {no such database: db12}}
181 do_test attach-1.26 {
185 } {1 {cannot detach database main}}
186 do_test attach-1.27 {
190 } {1 {cannot detach database Temp}}
191 do_test attach-1.28 {
204 do_test attach-1.29 {
210 CREATE TABLE tx(x1,x2,y1,y2);
211 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
212 INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y);
219 UPDATE t2 SET x=x+10;
222 } {1 11 x x 2 12 y y}
225 CREATE TABLE tx(x1,x2,y1,y2);
231 ATTACH 'test2.db' AS db2;
236 UPDATE db2.t2 SET x=x+10;
237 SELECT * FROM db2.tx;
239 } {1 11 x x 2 12 y y 11 21 x x 12 22 y y}
242 SELECT * FROM main.tx;
247 SELECT type, name, tbl_name FROM db2.sqlite_master;
249 } {table t2 t2 table tx tx trigger r1 t2}
252 } {0 main 1 temp 2 db2}
255 CREATE INDEX i2 ON t2(x);
256 SELECT * FROM t2 WHERE x>5;
259 do_test attach-2.10 {
261 SELECT type, name, tbl_name FROM sqlite_master;
263 } {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
264 #do_test attach-2.11 {
266 # SELECT * FROM t2 WHERE x>5;
268 #} {1 {database schema has changed}}
269 do_test attach-2.12 {
271 } {0 main 1 temp 2 db2}
272 do_test attach-2.13 {
274 SELECT * FROM t2 WHERE x>5;
277 do_test attach-2.14 {
279 SELECT type, name, tbl_name FROM sqlite_master;
281 } {table t1 t1 table tx tx}
282 do_test attach-2.15 {
284 SELECT type, name, tbl_name FROM db2.sqlite_master;
286 } {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
287 do_test attach-2.16 {
291 ATTACH 'test2.db' AS db2;
292 SELECT type, name, tbl_name FROM db2.sqlite_master;
294 } {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
309 } {1 {no such table: t2}}
312 ATTACH DATABASE 'test2.db' AS db2;
317 # Even though main has a transaction, test2.db should not be locked.
325 # Reading from db2 should not lock test2.db
327 execsql {SELECT * FROM t2}
333 # Making a change to db2 causes test2.ddb to become locked.
336 UPDATE t2 SET x=x+1 WHERE x=50;
341 } {1 {database is locked}}
345 execsql {SELECT * FROM t2} db2
350 catchsql {SELECT * FROM t2}
351 } {1 {database is locked}}
353 catchsql {SELECT * FROM t2} db2
355 do_test attach-3.10 {
356 execsql {SELECT * FROM t1}
358 do_test attach-3.11 {
359 catchsql {UPDATE t1 SET a=a+1}
361 do_test attach-3.12 {
362 execsql {SELECT * FROM t1}
364 do_test attach-3.13 {
365 catchsql {UPDATE t2 SET x=x+1 WHERE x=50}
366 } {1 {database is locked}}
367 do_test attach-3.14 {
368 # Unable to reinitialize the schema tables because the aux database
370 catchsql {SELECT * FROM t1}
371 } {1 {database is locked}}
372 do_test attach-3.15 {
374 execsql {SELECT * FROM t1}
383 CREATE TABLE t3(x,y);
384 CREATE UNIQUE INDEX t3i1 ON t3(x);
385 INSERT INTO t3 VALUES(1,2);
391 CREATE TABLE t3(a,b);
392 CREATE UNIQUE INDEX t3i1b ON t3(a);
393 INSERT INTO t3 VALUES(9,10);
399 ATTACH DATABASE 'test2.db' AS db2;
400 SELECT * FROM db2.t3;
405 SELECT * FROM main.t3;
410 INSERT INTO db2.t3 VALUES(9,10);
411 SELECT * FROM db2.t3;
420 CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
421 INSERT INTO t4 VALUES('db2.' || NEW.x);
423 INSERT INTO t3 VALUES(6,7);
430 CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
431 INSERT INTO t4 VALUES('main.' || NEW.a);
433 INSERT INTO main.t3 VALUES(11,12);
434 SELECT * FROM main.t4;
439 ATTACH DATABASE 'test2.db' AS db2;
440 INSERT INTO db2.t3 VALUES(13,14);
441 SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
443 } {db2.6 db2.13 main.11}
446 INSERT INTO main.t3 VALUES(15,16);
447 SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
449 } {db2.6 db2.13 main.11 main.15}
450 do_test attach-4.10 {
455 CREATE VIEW v3 AS SELECT x*100+y FROM t3;
459 do_test attach-4.11 {
461 CREATE VIEW v3 AS SELECT a*100+b FROM t3;
465 do_test attach-4.12 {
467 ATTACH DATABASE 'test2.db' AS db2;
468 SELECT * FROM db2.v3;
471 do_test attach-4.13 {
473 SELECT * FROM main.v3;
477 # Tests for the sqliteFix...() routines in attach.c
483 file delete -force test2.db
486 ATTACH DATABASE 'test.db' AS orig;
487 CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN;
491 } {1 {triggers may not be added to auxiliary database orig}}
494 CREATE TABLE t5(x,y);
495 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
503 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
504 SELECT 'no-op' FROM orig.t1;
507 } {1 {trigger r5 cannot reference objects in database orig}}
510 CREATE TEMP TABLE t6(p,q,r);
511 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
512 SELECT 'no-op' FROM temp.t6;
515 } {1 {trigger r5 cannot reference objects in database temp}}
518 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
519 SELECT 'no-op' || (SELECT * FROM temp.t6);
522 } {1 {trigger r5 cannot reference objects in database temp}}
525 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
526 SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
529 } {1 {trigger r5 cannot reference objects in database temp}}
532 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
533 SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6);
536 } {1 {trigger r5 cannot reference objects in database temp}}
539 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
540 SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1;
543 } {1 {trigger r5 cannot reference objects in database temp}}
546 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
547 INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5);
550 } {1 {trigger r5 cannot reference objects in database temp}}
553 CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
554 DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
557 } {1 {trigger r5 cannot reference objects in database temp}}
559 # Check to make sure we get a sensible error if unable to open
560 # the file that we are trying to attach.
564 ATTACH DATABASE 'no-such-file' AS nosuch;
566 } {1 {cannot attach empty database: nosuch}}
567 file delete -force no-such-file
568 if {$tcl_platform(platform)=="unix"} {
570 sqlite dbx cannot-read
571 dbx eval {CREATE TABLE t1(a,b,c)}
573 file attributes cannot-read -permission 0000
575 ATTACH DATABASE 'cannot-read' AS noread;
577 } {1 {unable to open database: cannot-read}}
578 file delete -force cannot-read
581 for {set i 2} {$i<=15} {incr i} {
584 file delete -force test2.db