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