# 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.42 2006/01/16 16:24:25 danielk1977 Exp $#set testdir [file dirname $argv0]source $testdir/tester.tclfor {set i 2} {$i<=15} {incr i} {  file delete -force test$i.db  file delete -force test$i.db-journal}set btree_trace 0do_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.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 {}}do_test attach-1.19 {  catchsql {    ATTACH 'test.db' as db12;  }} {1 {too many attached databases - max 10}}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_pragmasintegrity_check attach-1.20.3ifcapable tempdb {  execsql {select * from sqlite_temp_master}}do_test attach-1.21 {  catchsql {    ATTACH 'test.db' as db12;  }} {0 {}}do_test attach-1.22 {  catchsql {    ATTACH 'test.db' as db13;  }} {1 {too many attached databases - max 10}}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_pragmasifcapable {trigger} {  # Only do the following tests if triggers are enableddo_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&&tempdbifcapable schema_pragmas&&!tempdb {  do_test attach-2.8 {    db_list db  } {0 main 2 db2}} ;# ifcapable schema_pragmas&&!tempdbdo_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_pragmasdo_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);  }}