| # 2003 April 4 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # This file implements regression tests for SQLite library. The |
| # focus of this script is testing the ATTACH and DETACH commands |
| # and related functionality. |
| # |
| # $Id: attach.test,v 1.52 2009/05/29 14:39:08 drh Exp $ |
| # |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| |
| ifcapable !attach { |
| finish_test |
| return |
| } |
| |
| for {set i 2} {$i<=15} {incr i} { |
| file delete -force test$i.db |
| file delete -force test$i.db-journal |
| } |
| |
| do_test attach-1.1 { |
| execsql { |
| CREATE TABLE t1(a,b); |
| INSERT INTO t1 VALUES(1,2); |
| INSERT INTO t1 VALUES(3,4); |
| SELECT * FROM t1; |
| } |
| } {1 2 3 4} |
| do_test attach-1.2 { |
| sqlite3 db2 test2.db |
| execsql { |
| CREATE TABLE t2(x,y); |
| INSERT INTO t2 VALUES(1,'x'); |
| INSERT INTO t2 VALUES(2,'y'); |
| SELECT * FROM t2; |
| } db2 |
| } {1 x 2 y} |
| do_test attach-1.3 { |
| execsql { |
| ATTACH DATABASE 'test2.db' AS two; |
| SELECT * FROM two.t2; |
| } |
| } {1 x 2 y} |
| do_test attach-1.4 { |
| execsql { |
| SELECT * FROM t2; |
| } |
| } {1 x 2 y} |
| do_test attach-1.5 { |
| execsql { |
| DETACH DATABASE two; |
| SELECT * FROM t1; |
| } |
| } {1 2 3 4} |
| do_test attach-1.6 { |
| catchsql { |
| SELECT * FROM t2; |
| } |
| } {1 {no such table: t2}} |
| do_test attach-1.7 { |
| catchsql { |
| SELECT * FROM two.t2; |
| } |
| } {1 {no such table: two.t2}} |
| do_test attach-1.8 { |
| catchsql { |
| ATTACH DATABASE 'test3.db' AS three; |
| } |
| } {0 {}} |
| do_test attach-1.9 { |
| catchsql { |
| SELECT * FROM three.sqlite_master; |
| } |
| } {0 {}} |
| do_test attach-1.10 { |
| catchsql { |
| DETACH DATABASE [three]; |
| } |
| } {0 {}} |
| do_test attach-1.11 { |
| execsql { |
| ATTACH 'test.db' AS db2; |
| ATTACH 'test.db' AS db3; |
| ATTACH 'test.db' AS db4; |
| ATTACH 'test.db' AS db5; |
| ATTACH 'test.db' AS db6; |
| ATTACH 'test.db' AS db7; |
| ATTACH 'test.db' AS db8; |
| ATTACH 'test.db' AS db9; |
| } |
| } {} |
| proc db_list {db} { |
| set list {} |
| foreach {idx name file} [execsql {PRAGMA database_list} $db] { |
| lappend list $idx $name |
| } |
| return $list |
| } |
| ifcapable schema_pragmas { |
| do_test attach-1.11b { |
| db_list db |
| } {0 main 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9} |
| } ;# ifcapable schema_pragmas |
| do_test attach-1.12 { |
| catchsql { |
| ATTACH 'test.db' as db2; |
| } |
| } {1 {database db2 is already in use}} |
| do_test attach-1.12.2 { |
| db errorcode |
| } {1} |
| do_test attach-1.13 { |
| catchsql { |
| ATTACH 'test.db' as db5; |
| } |
| } {1 {database db5 is already in use}} |
| do_test attach-1.14 { |
| catchsql { |
| ATTACH 'test.db' as db9; |
| } |
| } {1 {database db9 is already in use}} |
| do_test attach-1.15 { |
| catchsql { |
| ATTACH 'test.db' as main; |
| } |
| } {1 {database main is already in use}} |
| ifcapable tempdb { |
| do_test attach-1.16 { |
| catchsql { |
| ATTACH 'test.db' as temp; |
| } |
| } {1 {database temp is already in use}} |
| } |
| do_test attach-1.17 { |
| catchsql { |
| ATTACH 'test.db' as MAIN; |
| } |
| } {1 {database MAIN is already in use}} |
| do_test attach-1.18 { |
| catchsql { |
| ATTACH 'test.db' as db10; |
| ATTACH 'test.db' as db11; |
| } |
| } {0 {}} |
| if {$SQLITE_MAX_ATTACHED==10} { |
| do_test attach-1.19 { |
| catchsql { |
| ATTACH 'test.db' as db12; |
| } |
| } {1 {too many attached databases - max 10}} |
| do_test attach-1.19.1 { |
| db errorcode |
| } {1} |
| } |
| do_test attach-1.20.1 { |
| execsql { |
| DETACH db5; |
| } |
| } {} |
| ifcapable schema_pragmas { |
| do_test attach-1.20.2 { |
| db_list db |
| } {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11} |
| } ;# ifcapable schema_pragmas |
| integrity_check attach-1.20.3 |
| ifcapable tempdb { |
| execsql {select * from sqlite_temp_master} |
| } |
| do_test attach-1.21 { |
| catchsql { |
| ATTACH 'test.db' as db12; |
| } |
| } {0 {}} |
| if {$SQLITE_MAX_ATTACHED==10} { |
| do_test attach-1.22 { |
| catchsql { |
| ATTACH 'test.db' as db13; |
| } |
| } {1 {too many attached databases - max 10}} |
| do_test attach-1.22.1 { |
| db errorcode |
| } {1} |
| } |
| do_test attach-1.23 { |
| catchsql { |
| DETACH "db14"; |
| } |
| } {1 {no such database: db14}} |
| do_test attach-1.24 { |
| catchsql { |
| DETACH db12; |
| } |
| } {0 {}} |
| do_test attach-1.25 { |
| catchsql { |
| DETACH db12; |
| } |
| } {1 {no such database: db12}} |
| do_test attach-1.26 { |
| catchsql { |
| DETACH main; |
| } |
| } {1 {cannot detach database main}} |
| |
| ifcapable tempdb { |
| do_test attach-1.27 { |
| catchsql { |
| DETACH Temp; |
| } |
| } {1 {cannot detach database Temp}} |
| } else { |
| do_test attach-1.27 { |
| catchsql { |
| DETACH Temp; |
| } |
| } {1 {no such database: Temp}} |
| } |
| |
| do_test attach-1.28 { |
| catchsql { |
| DETACH db11; |
| DETACH db10; |
| DETACH db9; |
| DETACH db8; |
| DETACH db7; |
| DETACH db6; |
| DETACH db4; |
| DETACH db3; |
| DETACH db2; |
| } |
| } {0 {}} |
| ifcapable schema_pragmas { |
| ifcapable tempdb { |
| do_test attach-1.29 { |
| db_list db |
| } {0 main 1 temp} |
| } else { |
| do_test attach-1.29 { |
| db_list db |
| } {0 main} |
| } |
| } ;# ifcapable schema_pragmas |
| |
| ifcapable {trigger} { # Only do the following tests if triggers are enabled |
| do_test attach-2.1 { |
| execsql { |
| CREATE TABLE tx(x1,x2,y1,y2); |
| CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN |
| INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y); |
| END; |
| SELECT * FROM tx; |
| } db2; |
| } {} |
| do_test attach-2.2 { |
| execsql { |
| UPDATE t2 SET x=x+10; |
| SELECT * FROM tx; |
| } db2; |
| } {1 11 x x 2 12 y y} |
| do_test attach-2.3 { |
| execsql { |
| CREATE TABLE tx(x1,x2,y1,y2); |
| SELECT * FROM tx; |
| } |
| } {} |
| do_test attach-2.4 { |
| execsql { |
| ATTACH 'test2.db' AS db2; |
| } |
| } {} |
| do_test attach-2.5 { |
| execsql { |
| UPDATE db2.t2 SET x=x+10; |
| SELECT * FROM db2.tx; |
| } |
| } {1 11 x x 2 12 y y 11 21 x x 12 22 y y} |
| do_test attach-2.6 { |
| execsql { |
| SELECT * FROM main.tx; |
| } |
| } {} |
| do_test attach-2.7 { |
| execsql { |
| SELECT type, name, tbl_name FROM db2.sqlite_master; |
| } |
| } {table t2 t2 table tx tx trigger r1 t2} |
| |
| ifcapable schema_pragmas&&tempdb { |
| do_test attach-2.8 { |
| db_list db |
| } {0 main 1 temp 2 db2} |
| } ;# ifcapable schema_pragmas&&tempdb |
| ifcapable schema_pragmas&&!tempdb { |
| do_test attach-2.8 { |
| db_list db |
| } {0 main 2 db2} |
| } ;# ifcapable schema_pragmas&&!tempdb |
| |
| do_test attach-2.9 { |
| execsql { |
| CREATE INDEX i2 ON t2(x); |
| SELECT * FROM t2 WHERE x>5; |
| } db2 |
| } {21 x 22 y} |
| do_test attach-2.10 { |
| execsql { |
| SELECT type, name, tbl_name FROM sqlite_master; |
| } db2 |
| } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
| #do_test attach-2.11 { |
| # catchsql { |
| # SELECT * FROM t2 WHERE x>5; |
| # } |
| #} {1 {database schema has changed}} |
| ifcapable schema_pragmas { |
| ifcapable tempdb { |
| do_test attach-2.12 { |
| db_list db |
| } {0 main 1 temp 2 db2} |
| } else { |
| do_test attach-2.12 { |
| db_list db |
| } {0 main 2 db2} |
| } |
| } ;# ifcapable schema_pragmas |
| do_test attach-2.13 { |
| catchsql { |
| SELECT * FROM t2 WHERE x>5; |
| } |
| } {0 {21 x 22 y}} |
| do_test attach-2.14 { |
| execsql { |
| SELECT type, name, tbl_name FROM sqlite_master; |
| } |
| } {table t1 t1 table tx tx} |
| do_test attach-2.15 { |
| execsql { |
| SELECT type, name, tbl_name FROM db2.sqlite_master; |
| } |
| } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
| do_test attach-2.16 { |
| db close |
| sqlite3 db test.db |
| execsql { |
| ATTACH 'test2.db' AS db2; |
| SELECT type, name, tbl_name FROM db2.sqlite_master; |
| } |
| } {table t2 t2 table tx tx trigger r1 t2 index i2 t2} |
| } ;# End of ifcapable {trigger} |
| |
| do_test attach-3.1 { |
| db close |
| db2 close |
| sqlite3 db test.db |
| sqlite3 db2 test2.db |
| execsql { |
| SELECT * FROM t1 |
| } |
| } {1 2 3 4} |
| |
| # If we are testing a version of the code that lacks trigger support, |
| # adjust the database contents so that they are the same if triggers |
| # had been enabled. |
| ifcapable {!trigger} { |
| db2 eval { |
| DELETE FROM t2; |
| INSERT INTO t2 VALUES(21, 'x'); |
| INSERT INTO t2 VALUES(22, 'y'); |
| CREATE TABLE tx(x1,x2,y1,y2); |
| INSERT INTO tx VALUES(1, 11, 'x', 'x'); |
| INSERT INTO tx VALUES(2, 12, 'y', 'y'); |
| INSERT INTO tx VALUES(11, 21, 'x', 'x'); |
| INSERT INTO tx VALUES(12, 22, 'y', 'y'); |
| CREATE INDEX i2 ON t2(x); |
| } |
| } |
| |
| do_test attach-3.2 { |
| catchsql { |
| SELECT * FROM t2 |
| } |
| } {1 {no such table: t2}} |
| do_test attach-3.3 { |
| catchsql { |
| ATTACH DATABASE 'test2.db' AS db2; |
| SELECT * FROM t2 |
| } |
| } {0 {21 x 22 y}} |
| |
| # Even though 'db' has started a transaction, it should not yet have |
| # a lock on test2.db so 'db2' should be readable. |
| do_test attach-3.4 { |
| execsql BEGIN |
| catchsql { |
| SELECT * FROM t2; |
| } db2; |
| } {0 {21 x 22 y}} |
| |
| # Reading from test2.db from db within a transaction should not |
| # prevent test2.db from being read by db2. |
| do_test attach-3.5 { |
| execsql {SELECT * FROM t2} |
| catchsql { |
| SELECT * FROM t2; |
| } db2; |
| } {0 {21 x 22 y}} |
| |
| # Making a change to test2.db through db causes test2.db to get |
| # a reserved lock. It should still be accessible through db2. |
| do_test attach-3.6 { |
| execsql { |
| UPDATE t2 SET x=x+1 WHERE x=50; |
| } |
| catchsql { |
| SELECT * FROM t2; |
| } db2; |
| } {0 {21 x 22 y}} |
| |
| do_test attach-3.7 { |
| execsql ROLLBACK |
| execsql {SELECT * FROM t2} db2 |
| } {21 x 22 y} |
| |
| # Start transactions on both db and db2. Once again, just because |
| # we make a change to test2.db using db2, only a RESERVED lock is |
| # obtained, so test2.db should still be readable using db. |
| # |
| do_test attach-3.8 { |
| execsql BEGIN |
| execsql BEGIN db2 |
| execsql {UPDATE t2 SET x=0 WHERE 0} db2 |
| catchsql {SELECT * FROM t2} |
| } {0 {21 x 22 y}} |
| |
| # It is also still accessible from db2. |
| do_test attach-3.9 { |
| catchsql {SELECT * FROM t2} db2 |
| } {0 {21 x 22 y}} |
| |
| do_test attach-3.10 { |
| execsql {SELECT * FROM t1} |
| } {1 2 3 4} |
| |
| do_test attach-3.11 { |
| catchsql {UPDATE t1 SET a=a+1} |
| } {0 {}} |
| do_test attach-3.12 { |
| execsql {SELECT * FROM t1} |
| } {2 2 4 4} |
| |
| # db2 has a RESERVED lock on test2.db, so db cannot write to any tables |
| # in test2.db. |
| do_test attach-3.13 { |
| catchsql {UPDATE t2 SET x=x+1 WHERE x=50} |
| } {1 {database is locked}} |
| |
| # Change for version 3. Transaction is no longer rolled back |
| # for a locked database. |
| execsql {ROLLBACK} |
| |
| # db is able to reread its schema because db2 still only holds a |
| # reserved lock. |
| do_test attach-3.14 { |
| catchsql {SELECT * FROM t1} |
| } {0 {1 2 3 4}} |
| do_test attach-3.15 { |
| execsql COMMIT db2 |
| execsql {SELECT * FROM t1} |
| } {1 2 3 4} |
| |
| # Ticket #323 |
| do_test attach-4.1 { |
| execsql {DETACH db2} |
| db2 close |
| sqlite3 db2 test2.db |
| execsql { |
| CREATE TABLE t3(x,y); |
| CREATE UNIQUE INDEX t3i1 ON t3(x); |
| INSERT INTO t3 VALUES(1,2); |
| SELECT * FROM t3; |
| } db2; |
| } {1 2} |
| do_test attach-4.2 { |
| execsql { |
| CREATE TABLE t3(a,b); |
| CREATE UNIQUE INDEX t3i1b ON t3(a); |
| INSERT INTO t3 VALUES(9,10); |
| SELECT * FROM t3; |
| } |
| } {9 10} |
| do_test attach-4.3 { |
| execsql { |
| ATTACH DATABASE 'test2.db' AS db2; |
| SELECT * FROM db2.t3; |
| } |
| } {1 2} |
| do_test attach-4.4 { |
| execsql { |
| SELECT * FROM main.t3; |
| } |
| } {9 10} |
| do_test attach-4.5 { |
| execsql { |
| INSERT INTO db2.t3 VALUES(9,10); |
| SELECT * FROM db2.t3; |
| } |
| } {1 2 9 10} |
| execsql { |
| DETACH db2; |
| } |
| ifcapable {trigger} { |
| do_test attach-4.6 { |
| execsql { |
| CREATE TABLE t4(x); |
| CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN |
| INSERT INTO t4 VALUES('db2.' || NEW.x); |
| END; |
| INSERT INTO t3 VALUES(6,7); |
| SELECT * FROM t4; |
| } db2 |
| } {db2.6} |
| do_test attach-4.7 { |
| execsql { |
| CREATE TABLE t4(y); |
| CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN |
| INSERT INTO t4 VALUES('main.' || NEW.a); |
| END; |
| INSERT INTO main.t3 VALUES(11,12); |
| SELECT * FROM main.t4; |
| } |
| } {main.11} |
| } |
| ifcapable {!trigger} { |
| # When we do not have trigger support, set up the table like they |
| # would have been had triggers been there. The tests that follow need |
| # this setup. |
| execsql { |
| CREATE TABLE t4(x); |
| INSERT INTO t3 VALUES(6,7); |
| INSERT INTO t4 VALUES('db2.6'); |
| INSERT INTO t4 VALUES('db2.13'); |
| } db2 |
| execsql { |
| CREATE TABLE t4(y); |
| INSERT INTO main.t3 VALUES(11,12); |
| INSERT INTO t4 VALUES('main.11'); |
| } |
| } |
| |
| |
| # This one is tricky. On the UNION ALL select, we have to make sure |
| # the schema for both main and db2 is valid before starting to execute |
| # the first query of the UNION ALL. If we wait to test the validity of |
| # the schema for main until after the first query has run, that test will |
| # fail and the query will abort but we will have already output some |
| # results. When the query is retried, the results will be repeated. |
| # |
| ifcapable compound { |
| do_test attach-4.8 { |
| execsql { |
| ATTACH DATABASE 'test2.db' AS db2; |
| INSERT INTO db2.t3 VALUES(13,14); |
| SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4; |
| } |
| } {db2.6 db2.13 main.11} |
| |
| do_test attach-4.9 { |
| ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}} |
| execsql { |
| INSERT INTO main.t3 VALUES(15,16); |
| SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4; |
| } |
| } {db2.6 db2.13 main.11 main.15} |
| } ;# ifcapable compound |
| |
| ifcapable !compound { |
| ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}} |
| execsql { |
| ATTACH DATABASE 'test2.db' AS db2; |
| INSERT INTO db2.t3 VALUES(13,14); |
| INSERT INTO main.t3 VALUES(15,16); |
| } |
| } ;# ifcapable !compound |
| |
| ifcapable view { |
| do_test attach-4.10 { |
| execsql { |
| DETACH DATABASE db2; |
| } |
| execsql { |
| CREATE VIEW v3 AS SELECT x*100+y FROM t3; |
| SELECT * FROM v3; |
| } db2 |
| } {102 910 607 1314} |
| do_test attach-4.11 { |
| execsql { |
| CREATE VIEW v3 AS SELECT a*100+b FROM t3; |
| SELECT * FROM v3; |
| } |
| } {910 1112 1516} |
| do_test attach-4.12 { |
| execsql { |
| ATTACH DATABASE 'test2.db' AS db2; |
| SELECT * FROM db2.v3; |
| } |
| } {102 910 607 1314} |
| do_test attach-4.13 { |
| execsql { |
| SELECT * FROM main.v3; |
| } |
| } {910 1112 1516} |
| } ;# ifcapable view |
| |
| # Tests for the sqliteFix...() routines in attach.c |
| # |
| ifcapable {trigger} { |
| do_test attach-5.1 { |
| db close |
| sqlite3 db test.db |
| db2 close |
| file delete -force test2.db |
| sqlite3 db2 test2.db |
| catchsql { |
| ATTACH DATABASE 'test.db' AS orig; |
| CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN |
| SELECT 'no-op'; |
| END; |
| } db2 |
| } {1 {trigger r1 cannot reference objects in database orig}} |
| do_test attach-5.2 { |
| catchsql { |
| CREATE TABLE t5(x,y); |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| SELECT 'no-op'; |
| END; |
| } db2 |
| } {0 {}} |
| do_test attach-5.3 { |
| catchsql { |
| DROP TRIGGER r5; |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| SELECT 'no-op' FROM orig.t1; |
| END; |
| } db2 |
| } {1 {trigger r5 cannot reference objects in database orig}} |
| ifcapable tempdb { |
| do_test attach-5.4 { |
| catchsql { |
| CREATE TEMP TABLE t6(p,q,r); |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| SELECT 'no-op' FROM temp.t6; |
| END; |
| } db2 |
| } {1 {trigger r5 cannot reference objects in database temp}} |
| } |
| ifcapable subquery { |
| do_test attach-5.5 { |
| catchsql { |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| SELECT 'no-op' || (SELECT * FROM temp.t6); |
| END; |
| } db2 |
| } {1 {trigger r5 cannot reference objects in database temp}} |
| do_test attach-5.6 { |
| catchsql { |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6); |
| END; |
| } db2 |
| } {1 {trigger r5 cannot reference objects in database temp}} |
| do_test attach-5.7 { |
| catchsql { |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6); |
| END; |
| } db2 |
| } {1 {trigger r5 cannot reference objects in database temp}} |
| do_test attach-5.7 { |
| catchsql { |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1; |
| END; |
| } db2 |
| } {1 {trigger r5 cannot reference objects in database temp}} |
| do_test attach-5.8 { |
| catchsql { |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5); |
| END; |
| } db2 |
| } {1 {trigger r5 cannot reference objects in database temp}} |
| do_test attach-5.9 { |
| catchsql { |
| CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN |
| DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6); |
| END; |
| } db2 |
| } {1 {trigger r5 cannot reference objects in database temp}} |
| } ;# endif subquery |
| } ;# endif trigger |
| |
| # Check to make sure we get a sensible error if unable to open |
| # the file that we are trying to attach. |
| # |
| do_test attach-6.1 { |
| catchsql { |
| ATTACH DATABASE 'no-such-file' AS nosuch; |
| } |
| } {0 {}} |
| if {$tcl_platform(platform)=="unix"} { |
| do_test attach-6.2 { |
| sqlite3 dbx cannot-read |
| dbx eval {CREATE TABLE t1(a,b,c)} |
| dbx close |
| file attributes cannot-read -permission 0000 |
| if {[file writable cannot-read]} { |
| puts "\n**** Tests do not work when run as root ****" |
| file delete -force cannot-read |
| exit 1 |
| } |
| catchsql { |
| ATTACH DATABASE 'cannot-read' AS noread; |
| } |
| } {1 {unable to open database: cannot-read}} |
| do_test attach-6.2.2 { |
| db errorcode |
| } {14} |
| file delete -force cannot-read |
| } |
| |
| # Check the error message if we try to access a database that has |
| # not been attached. |
| do_test attach-6.3 { |
| catchsql { |
| CREATE TABLE no_such_db.t1(a, b, c); |
| } |
| } {1 {unknown database no_such_db}} |
| for {set i 2} {$i<=15} {incr i} { |
| catch {db$i close} |
| } |
| db close |
| file delete -force test2.db |
| file delete -force no-such-file |
| |
| ifcapable subquery { |
| do_test attach-7.1 { |
| file delete -force test.db test.db-journal |
| sqlite3 db test.db |
| catchsql { |
| DETACH RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY |
| REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL ) |
| } |
| } {1 {no such table: AAAAAA}} |
| } |
| |
| # Create a malformed file (a file that is not a valid database) |
| # and try to attach it |
| # |
| do_test attach-8.1 { |
| set fd [open test2.db w] |
| puts $fd "This file is not a valid SQLite database" |
| close $fd |
| catchsql { |
| ATTACH 'test2.db' AS t2; |
| } |
| } {1 {file is encrypted or is not a database}} |
| do_test attach-8.2 { |
| db errorcode |
| } {26} |
| file delete -force test2.db |
| do_test attach-8.3 { |
| sqlite3 db2 test2.db |
| db2 eval {CREATE TABLE t1(x); BEGIN EXCLUSIVE} |
| catchsql { |
| ATTACH 'test2.db' AS t2; |
| } |
| } {1 {database is locked}} |
| do_test attach-8.4 { |
| db errorcode |
| } {5} |
| db2 close |
| file delete -force test2.db |
| |
| # Test that it is possible to attach the same database more than |
| # once when not in shared-cache mode. That this is not possible in |
| # shared-cache mode is tested in shared7.test. |
| do_test attach-9.1 { |
| file delete -force test4.db |
| execsql { |
| ATTACH 'test4.db' AS aux1; |
| CREATE TABLE aux1.t1(a, b); |
| INSERT INTO aux1.t1 VALUES(1, 2); |
| ATTACH 'test4.db' AS aux2; |
| SELECT * FROM aux2.t1; |
| } |
| } {1 2} |
| do_test attach-9.2 { |
| catchsql { |
| BEGIN; |
| INSERT INTO aux1.t1 VALUES(3, 4); |
| INSERT INTO aux2.t1 VALUES(5, 6); |
| } |
| } {1 {database is locked}} |
| do_test attach-9.3 { |
| execsql { |
| COMMIT; |
| SELECT * FROM aux2.t1; |
| } |
| } {1 2 3 4} |
| |
| # Ticket [abe728bbc311d81334dae9762f0db87c07a98f79]. |
| # Multi-database commit on an attached TEMP database. |
| # |
| do_test attach-10.1 { |
| execsql { |
| ATTACH '' AS noname; |
| ATTACH ':memory:' AS inmem; |
| BEGIN; |
| CREATE TABLE noname.noname(x); |
| CREATE TABLE inmem.inmem(y); |
| CREATE TABLE main.main(z); |
| COMMIT; |
| SELECT name FROM noname.sqlite_master; |
| SELECT name FROM inmem.sqlite_master; |
| } |
| } {noname inmem} |
| do_test attach-10.2 { |
| lrange [execsql { |
| PRAGMA database_list; |
| }] 9 end |
| } {4 noname {} 5 inmem {}} |
| |
| finish_test |