1*1da57d55SToomas Soome#
27c478bd9Sstevel@tonic-gate# 2003 April 4
37c478bd9Sstevel@tonic-gate#
47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code.  In place of
57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing:
67c478bd9Sstevel@tonic-gate#
77c478bd9Sstevel@tonic-gate#    May you do good and not evil.
87c478bd9Sstevel@tonic-gate#    May you find forgiveness for yourself and forgive others.
97c478bd9Sstevel@tonic-gate#    May you share freely, never taking more than you give.
107c478bd9Sstevel@tonic-gate#
117c478bd9Sstevel@tonic-gate#***********************************************************************
127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library.  The
137c478bd9Sstevel@tonic-gate# focus of this script is testing the ATTACH and DETACH commands
147c478bd9Sstevel@tonic-gate# and related functionality.
157c478bd9Sstevel@tonic-gate#
167c478bd9Sstevel@tonic-gate# $Id: attach.test,v 1.13 2004/02/14 01:39:50 drh Exp $
177c478bd9Sstevel@tonic-gate#
187c478bd9Sstevel@tonic-gate
197c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0]
207c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl
217c478bd9Sstevel@tonic-gate
227c478bd9Sstevel@tonic-gatefor {set i 2} {$i<=15} {incr i} {
237c478bd9Sstevel@tonic-gate  file delete -force test$i.db
247c478bd9Sstevel@tonic-gate  file delete -force test$i.db-journal
257c478bd9Sstevel@tonic-gate}
267c478bd9Sstevel@tonic-gate
277c478bd9Sstevel@tonic-gatedo_test attach-1.1 {
287c478bd9Sstevel@tonic-gate  execsql {
297c478bd9Sstevel@tonic-gate    CREATE TABLE t1(a,b);
307c478bd9Sstevel@tonic-gate    INSERT INTO t1 VALUES(1,2);
317c478bd9Sstevel@tonic-gate    INSERT INTO t1 VALUES(3,4);
327c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
337c478bd9Sstevel@tonic-gate  }
347c478bd9Sstevel@tonic-gate} {1 2 3 4}
357c478bd9Sstevel@tonic-gatedo_test attach-1.2 {
367c478bd9Sstevel@tonic-gate  sqlite db2 test2.db
377c478bd9Sstevel@tonic-gate  execsql {
387c478bd9Sstevel@tonic-gate    CREATE TABLE t2(x,y);
397c478bd9Sstevel@tonic-gate    INSERT INTO t2 VALUES(1,'x');
407c478bd9Sstevel@tonic-gate    INSERT INTO t2 VALUES(2,'y');
417c478bd9Sstevel@tonic-gate    SELECT * FROM t2;
427c478bd9Sstevel@tonic-gate  } db2
437c478bd9Sstevel@tonic-gate} {1 x 2 y}
447c478bd9Sstevel@tonic-gatedo_test attach-1.3 {
457c478bd9Sstevel@tonic-gate  execsql {
467c478bd9Sstevel@tonic-gate    ATTACH DATABASE 'test2.db' AS two;
477c478bd9Sstevel@tonic-gate    SELECT * FROM two.t2;
487c478bd9Sstevel@tonic-gate  }
497c478bd9Sstevel@tonic-gate} {1 x 2 y}
507c478bd9Sstevel@tonic-gatedo_test attach-1.4 {
517c478bd9Sstevel@tonic-gate  execsql {
527c478bd9Sstevel@tonic-gate    SELECT * FROM t2;
537c478bd9Sstevel@tonic-gate  }
547c478bd9Sstevel@tonic-gate} {1 x 2 y}
557c478bd9Sstevel@tonic-gatedo_test attach-1.5 {
567c478bd9Sstevel@tonic-gate  execsql {
577c478bd9Sstevel@tonic-gate    DETACH DATABASE two;
587c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
597c478bd9Sstevel@tonic-gate  }
607c478bd9Sstevel@tonic-gate} {1 2 3 4}
617c478bd9Sstevel@tonic-gatedo_test attach-1.6 {
627c478bd9Sstevel@tonic-gate  catchsql {
637c478bd9Sstevel@tonic-gate    SELECT * FROM t2;
647c478bd9Sstevel@tonic-gate  }
657c478bd9Sstevel@tonic-gate} {1 {no such table: t2}}
667c478bd9Sstevel@tonic-gatedo_test attach-1.7 {
677c478bd9Sstevel@tonic-gate  catchsql {
687c478bd9Sstevel@tonic-gate    SELECT * FROM two.t2;
697c478bd9Sstevel@tonic-gate  }
707c478bd9Sstevel@tonic-gate} {1 {no such table: two.t2}}
717c478bd9Sstevel@tonic-gatedo_test attach-1.8 {
727c478bd9Sstevel@tonic-gate  catchsql {
737c478bd9Sstevel@tonic-gate    ATTACH DATABASE 'test3.db' AS three;
747c478bd9Sstevel@tonic-gate  }
757c478bd9Sstevel@tonic-gate} {1 {cannot attach empty database: three}}
767c478bd9Sstevel@tonic-gatedo_test attach-1.9 {
777c478bd9Sstevel@tonic-gate  catchsql {
787c478bd9Sstevel@tonic-gate    SELECT * FROM three.sqlite_master;
797c478bd9Sstevel@tonic-gate  }
807c478bd9Sstevel@tonic-gate} {1 {no such table: three.sqlite_master}}
817c478bd9Sstevel@tonic-gatedo_test attach-1.10 {
827c478bd9Sstevel@tonic-gate  catchsql {
837c478bd9Sstevel@tonic-gate    DETACH DATABASE three;
847c478bd9Sstevel@tonic-gate  }
857c478bd9Sstevel@tonic-gate} {1 {no such database: three}}
867c478bd9Sstevel@tonic-gatedo_test attach-1.11 {
877c478bd9Sstevel@tonic-gate  execsql {
887c478bd9Sstevel@tonic-gate    ATTACH 'test.db' AS db2;
897c478bd9Sstevel@tonic-gate    ATTACH 'test.db' AS db3;
907c478bd9Sstevel@tonic-gate    ATTACH 'test.db' AS db4;
917c478bd9Sstevel@tonic-gate    ATTACH 'test.db' AS db5;
927c478bd9Sstevel@tonic-gate    ATTACH 'test.db' AS db6;
937c478bd9Sstevel@tonic-gate    ATTACH 'test.db' AS db7;
947c478bd9Sstevel@tonic-gate    ATTACH 'test.db' AS db8;
957c478bd9Sstevel@tonic-gate    ATTACH 'test.db' AS db9;
967c478bd9Sstevel@tonic-gate  }
977c478bd9Sstevel@tonic-gate} {}
987c478bd9Sstevel@tonic-gateproc db_list {db} {
997c478bd9Sstevel@tonic-gate  set list {}
1007c478bd9Sstevel@tonic-gate  foreach {idx name file} [execsql {PRAGMA database_list} $db] {
1017c478bd9Sstevel@tonic-gate    lappend list $idx $name
1027c478bd9Sstevel@tonic-gate  }
1037c478bd9Sstevel@tonic-gate  return $list
1047c478bd9Sstevel@tonic-gate}
1057c478bd9Sstevel@tonic-gatedo_test attach-1.11b {
1067c478bd9Sstevel@tonic-gate  db_list db
1077c478bd9Sstevel@tonic-gate} {0 main 1 temp 2 db2 3 db3 4 db4 5 db5 6 db6 7 db7 8 db8 9 db9}
1087c478bd9Sstevel@tonic-gatedo_test attach-1.12 {
1097c478bd9Sstevel@tonic-gate  catchsql {
1107c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as db2;
1117c478bd9Sstevel@tonic-gate  }
1127c478bd9Sstevel@tonic-gate} {1 {database db2 is already in use}}
1137c478bd9Sstevel@tonic-gatedo_test attach-1.13 {
1147c478bd9Sstevel@tonic-gate  catchsql {
1157c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as db5;
1167c478bd9Sstevel@tonic-gate  }
1177c478bd9Sstevel@tonic-gate} {1 {database db5 is already in use}}
1187c478bd9Sstevel@tonic-gatedo_test attach-1.14 {
1197c478bd9Sstevel@tonic-gate  catchsql {
1207c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as db9;
1217c478bd9Sstevel@tonic-gate  }
1227c478bd9Sstevel@tonic-gate} {1 {database db9 is already in use}}
1237c478bd9Sstevel@tonic-gatedo_test attach-1.15 {
1247c478bd9Sstevel@tonic-gate  catchsql {
1257c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as main;
1267c478bd9Sstevel@tonic-gate  }
1277c478bd9Sstevel@tonic-gate} {1 {database main is already in use}}
1287c478bd9Sstevel@tonic-gatedo_test attach-1.16 {
1297c478bd9Sstevel@tonic-gate  catchsql {
1307c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as temp;
1317c478bd9Sstevel@tonic-gate  }
1327c478bd9Sstevel@tonic-gate} {1 {database temp is already in use}}
1337c478bd9Sstevel@tonic-gatedo_test attach-1.17 {
1347c478bd9Sstevel@tonic-gate  catchsql {
1357c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as MAIN;
1367c478bd9Sstevel@tonic-gate  }
1377c478bd9Sstevel@tonic-gate} {1 {database MAIN is already in use}}
1387c478bd9Sstevel@tonic-gatedo_test attach-1.18 {
1397c478bd9Sstevel@tonic-gate  catchsql {
1407c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as db10;
1417c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as db11;
1427c478bd9Sstevel@tonic-gate  }
1437c478bd9Sstevel@tonic-gate} {0 {}}
1447c478bd9Sstevel@tonic-gatedo_test attach-1.19 {
1457c478bd9Sstevel@tonic-gate  catchsql {
1467c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as db12;
1477c478bd9Sstevel@tonic-gate  }
1487c478bd9Sstevel@tonic-gate} {1 {too many attached databases - max 10}}
1497c478bd9Sstevel@tonic-gatedo_test attach-1.20.1 {
1507c478bd9Sstevel@tonic-gate  execsql {
1517c478bd9Sstevel@tonic-gate    DETACH db5;
1527c478bd9Sstevel@tonic-gate  }
1537c478bd9Sstevel@tonic-gate  db_list db
1547c478bd9Sstevel@tonic-gate} {0 main 1 temp 2 db2 3 db3 4 db4 5 db11 6 db6 7 db7 8 db8 9 db9 10 db10}
1557c478bd9Sstevel@tonic-gateintegrity_check attach-1.20.2
1567c478bd9Sstevel@tonic-gatedo_test attach-1.21 {
1577c478bd9Sstevel@tonic-gate  catchsql {
1587c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as db12;
1597c478bd9Sstevel@tonic-gate  }
1607c478bd9Sstevel@tonic-gate} {0 {}}
1617c478bd9Sstevel@tonic-gatedo_test attach-1.22 {
1627c478bd9Sstevel@tonic-gate  catchsql {
1637c478bd9Sstevel@tonic-gate    ATTACH 'test.db' as db13;
1647c478bd9Sstevel@tonic-gate  }
1657c478bd9Sstevel@tonic-gate} {1 {too many attached databases - max 10}}
1667c478bd9Sstevel@tonic-gatedo_test attach-1.23 {
1677c478bd9Sstevel@tonic-gate  catchsql {
1687c478bd9Sstevel@tonic-gate    DETACH db14;
1697c478bd9Sstevel@tonic-gate  }
1707c478bd9Sstevel@tonic-gate} {1 {no such database: db14}}
1717c478bd9Sstevel@tonic-gatedo_test attach-1.24 {
1727c478bd9Sstevel@tonic-gate  catchsql {
1737c478bd9Sstevel@tonic-gate    DETACH db12;
1747c478bd9Sstevel@tonic-gate  }
1757c478bd9Sstevel@tonic-gate} {0 {}}
1767c478bd9Sstevel@tonic-gatedo_test attach-1.25 {
1777c478bd9Sstevel@tonic-gate  catchsql {
1787c478bd9Sstevel@tonic-gate    DETACH db12;
1797c478bd9Sstevel@tonic-gate  }
1807c478bd9Sstevel@tonic-gate} {1 {no such database: db12}}
1817c478bd9Sstevel@tonic-gatedo_test attach-1.26 {
1827c478bd9Sstevel@tonic-gate  catchsql {
1837c478bd9Sstevel@tonic-gate    DETACH main;
1847c478bd9Sstevel@tonic-gate  }
1857c478bd9Sstevel@tonic-gate} {1 {cannot detach database main}}
1867c478bd9Sstevel@tonic-gatedo_test attach-1.27 {
1877c478bd9Sstevel@tonic-gate  catchsql {
1887c478bd9Sstevel@tonic-gate    DETACH Temp;
1897c478bd9Sstevel@tonic-gate  }
1907c478bd9Sstevel@tonic-gate} {1 {cannot detach database Temp}}
1917c478bd9Sstevel@tonic-gatedo_test attach-1.28 {
1927c478bd9Sstevel@tonic-gate  catchsql {
1937c478bd9Sstevel@tonic-gate    DETACH db11;
1947c478bd9Sstevel@tonic-gate    DETACH db10;
1957c478bd9Sstevel@tonic-gate    DETACH db9;
1967c478bd9Sstevel@tonic-gate    DETACH db8;
1977c478bd9Sstevel@tonic-gate    DETACH db7;
1987c478bd9Sstevel@tonic-gate    DETACH db6;
1997c478bd9Sstevel@tonic-gate    DETACH db4;
2007c478bd9Sstevel@tonic-gate    DETACH db3;
2017c478bd9Sstevel@tonic-gate    DETACH db2;
2027c478bd9Sstevel@tonic-gate  }
2037c478bd9Sstevel@tonic-gate} {0 {}}
2047c478bd9Sstevel@tonic-gatedo_test attach-1.29 {
2057c478bd9Sstevel@tonic-gate  db_list db
2067c478bd9Sstevel@tonic-gate} {0 main 1 temp}
2077c478bd9Sstevel@tonic-gate
2087c478bd9Sstevel@tonic-gatedo_test attach-2.1 {
2097c478bd9Sstevel@tonic-gate  execsql {
2107c478bd9Sstevel@tonic-gate    CREATE TABLE tx(x1,x2,y1,y2);
2117c478bd9Sstevel@tonic-gate    CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
2127c478bd9Sstevel@tonic-gate      INSERT INTO tx(x1,x2,y1,y2) VALUES(OLD.x,NEW.x,OLD.y,NEW.y);
2137c478bd9Sstevel@tonic-gate    END;
2147c478bd9Sstevel@tonic-gate    SELECT * FROM tx;
2157c478bd9Sstevel@tonic-gate  } db2;
2167c478bd9Sstevel@tonic-gate} {}
2177c478bd9Sstevel@tonic-gatedo_test attach-2.2 {
2187c478bd9Sstevel@tonic-gate  execsql {
2197c478bd9Sstevel@tonic-gate    UPDATE t2 SET x=x+10;
2207c478bd9Sstevel@tonic-gate    SELECT * FROM tx;
2217c478bd9Sstevel@tonic-gate  } db2;
2227c478bd9Sstevel@tonic-gate} {1 11 x x 2 12 y y}
2237c478bd9Sstevel@tonic-gatedo_test attach-2.3 {
2247c478bd9Sstevel@tonic-gate  execsql {
2257c478bd9Sstevel@tonic-gate    CREATE TABLE tx(x1,x2,y1,y2);
2267c478bd9Sstevel@tonic-gate    SELECT * FROM tx;
2277c478bd9Sstevel@tonic-gate  }
2287c478bd9Sstevel@tonic-gate} {}
2297c478bd9Sstevel@tonic-gatedo_test attach-2.4 {
2307c478bd9Sstevel@tonic-gate  execsql {
2317c478bd9Sstevel@tonic-gate    ATTACH 'test2.db' AS db2;
2327c478bd9Sstevel@tonic-gate  }
2337c478bd9Sstevel@tonic-gate} {}
2347c478bd9Sstevel@tonic-gatedo_test attach-2.5 {
2357c478bd9Sstevel@tonic-gate  execsql {
2367c478bd9Sstevel@tonic-gate    UPDATE db2.t2 SET x=x+10;
2377c478bd9Sstevel@tonic-gate    SELECT * FROM db2.tx;
2387c478bd9Sstevel@tonic-gate  }
2397c478bd9Sstevel@tonic-gate} {1 11 x x 2 12 y y 11 21 x x 12 22 y y}
2407c478bd9Sstevel@tonic-gatedo_test attach-2.6 {
2417c478bd9Sstevel@tonic-gate  execsql {
2427c478bd9Sstevel@tonic-gate    SELECT * FROM main.tx;
2437c478bd9Sstevel@tonic-gate  }
2447c478bd9Sstevel@tonic-gate} {}
2457c478bd9Sstevel@tonic-gatedo_test attach-2.7 {
2467c478bd9Sstevel@tonic-gate  execsql {
2477c478bd9Sstevel@tonic-gate    SELECT type, name, tbl_name FROM db2.sqlite_master;
2487c478bd9Sstevel@tonic-gate  }
2497c478bd9Sstevel@tonic-gate} {table t2 t2 table tx tx trigger r1 t2}
2507c478bd9Sstevel@tonic-gatedo_test attach-2.8 {
2517c478bd9Sstevel@tonic-gate  db_list db
2527c478bd9Sstevel@tonic-gate} {0 main 1 temp 2 db2}
2537c478bd9Sstevel@tonic-gatedo_test attach-2.9 {
2547c478bd9Sstevel@tonic-gate  execsql {
2557c478bd9Sstevel@tonic-gate    CREATE INDEX i2 ON t2(x);
2567c478bd9Sstevel@tonic-gate    SELECT * FROM t2 WHERE x>5;
2577c478bd9Sstevel@tonic-gate  } db2
2587c478bd9Sstevel@tonic-gate} {21 x 22 y}
2597c478bd9Sstevel@tonic-gatedo_test attach-2.10 {
2607c478bd9Sstevel@tonic-gate  execsql {
2617c478bd9Sstevel@tonic-gate    SELECT type, name, tbl_name FROM sqlite_master;
2627c478bd9Sstevel@tonic-gate  } db2
2637c478bd9Sstevel@tonic-gate} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
2647c478bd9Sstevel@tonic-gate#do_test attach-2.11 {
265*1da57d55SToomas Soome#  catchsql {
2667c478bd9Sstevel@tonic-gate#    SELECT * FROM t2 WHERE x>5;
2677c478bd9Sstevel@tonic-gate#  }
2687c478bd9Sstevel@tonic-gate#} {1 {database schema has changed}}
2697c478bd9Sstevel@tonic-gatedo_test attach-2.12 {
2707c478bd9Sstevel@tonic-gate  db_list db
2717c478bd9Sstevel@tonic-gate} {0 main 1 temp 2 db2}
2727c478bd9Sstevel@tonic-gatedo_test attach-2.13 {
2737c478bd9Sstevel@tonic-gate  catchsql {
2747c478bd9Sstevel@tonic-gate    SELECT * FROM t2 WHERE x>5;
2757c478bd9Sstevel@tonic-gate  }
2767c478bd9Sstevel@tonic-gate} {0 {21 x 22 y}}
2777c478bd9Sstevel@tonic-gatedo_test attach-2.14 {
2787c478bd9Sstevel@tonic-gate  execsql {
2797c478bd9Sstevel@tonic-gate    SELECT type, name, tbl_name FROM sqlite_master;
2807c478bd9Sstevel@tonic-gate  }
2817c478bd9Sstevel@tonic-gate} {table t1 t1 table tx tx}
2827c478bd9Sstevel@tonic-gatedo_test attach-2.15 {
2837c478bd9Sstevel@tonic-gate  execsql {
2847c478bd9Sstevel@tonic-gate    SELECT type, name, tbl_name FROM db2.sqlite_master;
2857c478bd9Sstevel@tonic-gate  }
2867c478bd9Sstevel@tonic-gate} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
2877c478bd9Sstevel@tonic-gatedo_test attach-2.16 {
2887c478bd9Sstevel@tonic-gate  db close
2897c478bd9Sstevel@tonic-gate  sqlite db test.db
2907c478bd9Sstevel@tonic-gate  execsql {
2917c478bd9Sstevel@tonic-gate    ATTACH 'test2.db' AS db2;
2927c478bd9Sstevel@tonic-gate    SELECT type, name, tbl_name FROM db2.sqlite_master;
2937c478bd9Sstevel@tonic-gate  }
2947c478bd9Sstevel@tonic-gate} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
2957c478bd9Sstevel@tonic-gate
2967c478bd9Sstevel@tonic-gatedo_test attach-3.1 {
2977c478bd9Sstevel@tonic-gate  db close
2987c478bd9Sstevel@tonic-gate  db2 close
2997c478bd9Sstevel@tonic-gate  sqlite db test.db
3007c478bd9Sstevel@tonic-gate  sqlite db2 test2.db
3017c478bd9Sstevel@tonic-gate  execsql {
3027c478bd9Sstevel@tonic-gate    SELECT * FROM t1
3037c478bd9Sstevel@tonic-gate  }
3047c478bd9Sstevel@tonic-gate} {1 2 3 4}
3057c478bd9Sstevel@tonic-gatedo_test attach-3.2 {
3067c478bd9Sstevel@tonic-gate  catchsql {
3077c478bd9Sstevel@tonic-gate    SELECT * FROM t2
3087c478bd9Sstevel@tonic-gate  }
3097c478bd9Sstevel@tonic-gate} {1 {no such table: t2}}
3107c478bd9Sstevel@tonic-gatedo_test attach-3.3 {
3117c478bd9Sstevel@tonic-gate  catchsql {
3127c478bd9Sstevel@tonic-gate    ATTACH DATABASE 'test2.db' AS db2;
3137c478bd9Sstevel@tonic-gate    SELECT * FROM t2
3147c478bd9Sstevel@tonic-gate  }
3157c478bd9Sstevel@tonic-gate} {0 {21 x 22 y}}
3167c478bd9Sstevel@tonic-gate
3177c478bd9Sstevel@tonic-gate# Even though main has a transaction, test2.db should not be locked.
3187c478bd9Sstevel@tonic-gatedo_test attach-3.4 {
3197c478bd9Sstevel@tonic-gate  execsql BEGIN
3207c478bd9Sstevel@tonic-gate  catchsql {
3217c478bd9Sstevel@tonic-gate    SELECT * FROM t2;
3227c478bd9Sstevel@tonic-gate  } db2;
3237c478bd9Sstevel@tonic-gate} {0 {21 x 22 y}}
3247c478bd9Sstevel@tonic-gate
3257c478bd9Sstevel@tonic-gate# Reading from db2 should not lock test2.db
3267c478bd9Sstevel@tonic-gatedo_test attach-3.5 {
3277c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM t2}
3287c478bd9Sstevel@tonic-gate  catchsql {
3297c478bd9Sstevel@tonic-gate    SELECT * FROM t2;
3307c478bd9Sstevel@tonic-gate  } db2;
3317c478bd9Sstevel@tonic-gate} {0 {21 x 22 y}}
3327c478bd9Sstevel@tonic-gate
3337c478bd9Sstevel@tonic-gate# Making a change to db2 causes test2.ddb to become locked.
3347c478bd9Sstevel@tonic-gatedo_test attach-3.6 {
3357c478bd9Sstevel@tonic-gate  execsql {
3367c478bd9Sstevel@tonic-gate    UPDATE t2 SET x=x+1 WHERE x=50;
3377c478bd9Sstevel@tonic-gate  }
3387c478bd9Sstevel@tonic-gate  catchsql {
3397c478bd9Sstevel@tonic-gate    SELECT * FROM t2;
3407c478bd9Sstevel@tonic-gate  } db2;
3417c478bd9Sstevel@tonic-gate} {1 {database is locked}}
3427c478bd9Sstevel@tonic-gate
3437c478bd9Sstevel@tonic-gatedo_test attach-3.7 {
3447c478bd9Sstevel@tonic-gate  execsql ROLLBACK
3457c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM t2} db2
3467c478bd9Sstevel@tonic-gate} {21 x 22 y}
3477c478bd9Sstevel@tonic-gatedo_test attach-3.8 {
3487c478bd9Sstevel@tonic-gate  execsql BEGIN
3497c478bd9Sstevel@tonic-gate  execsql BEGIN db2
3507c478bd9Sstevel@tonic-gate  catchsql {SELECT * FROM t2}
3517c478bd9Sstevel@tonic-gate} {1 {database is locked}}
3527c478bd9Sstevel@tonic-gatedo_test attach-3.9 {
3537c478bd9Sstevel@tonic-gate  catchsql {SELECT * FROM t2} db2
3547c478bd9Sstevel@tonic-gate} {0 {21 x 22 y}}
3557c478bd9Sstevel@tonic-gatedo_test attach-3.10 {
3567c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM t1}
3577c478bd9Sstevel@tonic-gate} {1 2 3 4}
3587c478bd9Sstevel@tonic-gatedo_test attach-3.11 {
3597c478bd9Sstevel@tonic-gate  catchsql {UPDATE t1 SET a=a+1}
3607c478bd9Sstevel@tonic-gate} {0 {}}
3617c478bd9Sstevel@tonic-gatedo_test attach-3.12 {
3627c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM t1}
3637c478bd9Sstevel@tonic-gate} {2 2 4 4}
3647c478bd9Sstevel@tonic-gatedo_test attach-3.13 {
3657c478bd9Sstevel@tonic-gate  catchsql {UPDATE t2 SET x=x+1 WHERE x=50}
3667c478bd9Sstevel@tonic-gate} {1 {database is locked}}
3677c478bd9Sstevel@tonic-gatedo_test attach-3.14 {
3687c478bd9Sstevel@tonic-gate  # Unable to reinitialize the schema tables because the aux database
3697c478bd9Sstevel@tonic-gate  # is still locked.
3707c478bd9Sstevel@tonic-gate  catchsql {SELECT * FROM t1}
3717c478bd9Sstevel@tonic-gate} {1 {database is locked}}
3727c478bd9Sstevel@tonic-gatedo_test attach-3.15 {
3737c478bd9Sstevel@tonic-gate  execsql COMMIT db2
3747c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM t1}
3757c478bd9Sstevel@tonic-gate} {1 2 3 4}
3767c478bd9Sstevel@tonic-gate
3777c478bd9Sstevel@tonic-gate# Ticket #323
3787c478bd9Sstevel@tonic-gatedo_test attach-4.1 {
3797c478bd9Sstevel@tonic-gate  execsql {DETACH db2}
3807c478bd9Sstevel@tonic-gate  db2 close
3817c478bd9Sstevel@tonic-gate  sqlite db2 test2.db
3827c478bd9Sstevel@tonic-gate  execsql {
3837c478bd9Sstevel@tonic-gate    CREATE TABLE t3(x,y);
3847c478bd9Sstevel@tonic-gate    CREATE UNIQUE INDEX t3i1 ON t3(x);
3857c478bd9Sstevel@tonic-gate    INSERT INTO t3 VALUES(1,2);
3867c478bd9Sstevel@tonic-gate    SELECT * FROM t3;
3877c478bd9Sstevel@tonic-gate  } db2;
3887c478bd9Sstevel@tonic-gate} {1 2}
3897c478bd9Sstevel@tonic-gatedo_test attach-4.2 {
3907c478bd9Sstevel@tonic-gate  execsql {
3917c478bd9Sstevel@tonic-gate    CREATE TABLE t3(a,b);
3927c478bd9Sstevel@tonic-gate    CREATE UNIQUE INDEX t3i1b ON t3(a);
3937c478bd9Sstevel@tonic-gate    INSERT INTO t3 VALUES(9,10);
3947c478bd9Sstevel@tonic-gate    SELECT * FROM t3;
3957c478bd9Sstevel@tonic-gate  }
3967c478bd9Sstevel@tonic-gate} {9 10}
3977c478bd9Sstevel@tonic-gatedo_test attach-4.3 {
3987c478bd9Sstevel@tonic-gate  execsql {
3997c478bd9Sstevel@tonic-gate    ATTACH DATABASE 'test2.db' AS db2;
4007c478bd9Sstevel@tonic-gate    SELECT * FROM db2.t3;
4017c478bd9Sstevel@tonic-gate  }
4027c478bd9Sstevel@tonic-gate} {1 2}
4037c478bd9Sstevel@tonic-gatedo_test attach-4.4 {
4047c478bd9Sstevel@tonic-gate  execsql {
4057c478bd9Sstevel@tonic-gate    SELECT * FROM main.t3;
4067c478bd9Sstevel@tonic-gate  }
4077c478bd9Sstevel@tonic-gate} {9 10}
4087c478bd9Sstevel@tonic-gatedo_test attach-4.5 {
4097c478bd9Sstevel@tonic-gate  execsql {
4107c478bd9Sstevel@tonic-gate    INSERT INTO db2.t3 VALUES(9,10);
4117c478bd9Sstevel@tonic-gate    SELECT * FROM db2.t3;
4127c478bd9Sstevel@tonic-gate  }
4137c478bd9Sstevel@tonic-gate} {1 2 9 10}
4147c478bd9Sstevel@tonic-gatedo_test attach-4.6 {
4157c478bd9Sstevel@tonic-gate  execsql {
4167c478bd9Sstevel@tonic-gate    DETACH db2;
4177c478bd9Sstevel@tonic-gate  }
4187c478bd9Sstevel@tonic-gate  execsql {
4197c478bd9Sstevel@tonic-gate    CREATE TABLE t4(x);
4207c478bd9Sstevel@tonic-gate    CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
4217c478bd9Sstevel@tonic-gate      INSERT INTO t4 VALUES('db2.' || NEW.x);
4227c478bd9Sstevel@tonic-gate    END;
4237c478bd9Sstevel@tonic-gate    INSERT INTO t3 VALUES(6,7);
4247c478bd9Sstevel@tonic-gate    SELECT * FROM t4;
4257c478bd9Sstevel@tonic-gate  } db2
4267c478bd9Sstevel@tonic-gate} {db2.6}
4277c478bd9Sstevel@tonic-gatedo_test attach-4.7 {
4287c478bd9Sstevel@tonic-gate  execsql {
4297c478bd9Sstevel@tonic-gate    CREATE TABLE t4(y);
4307c478bd9Sstevel@tonic-gate    CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
4317c478bd9Sstevel@tonic-gate      INSERT INTO t4 VALUES('main.' || NEW.a);
4327c478bd9Sstevel@tonic-gate    END;
4337c478bd9Sstevel@tonic-gate    INSERT INTO main.t3 VALUES(11,12);
4347c478bd9Sstevel@tonic-gate    SELECT * FROM main.t4;
4357c478bd9Sstevel@tonic-gate  }
4367c478bd9Sstevel@tonic-gate} {main.11}
4377c478bd9Sstevel@tonic-gatedo_test attach-4.8 {
4387c478bd9Sstevel@tonic-gate  execsql {
4397c478bd9Sstevel@tonic-gate    ATTACH DATABASE 'test2.db' AS db2;
4407c478bd9Sstevel@tonic-gate    INSERT INTO db2.t3 VALUES(13,14);
4417c478bd9Sstevel@tonic-gate    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
4427c478bd9Sstevel@tonic-gate  }
4437c478bd9Sstevel@tonic-gate} {db2.6 db2.13 main.11}
4447c478bd9Sstevel@tonic-gatedo_test attach-4.9 {
4457c478bd9Sstevel@tonic-gate  execsql {
4467c478bd9Sstevel@tonic-gate    INSERT INTO main.t3 VALUES(15,16);
4477c478bd9Sstevel@tonic-gate    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
4487c478bd9Sstevel@tonic-gate  }
4497c478bd9Sstevel@tonic-gate} {db2.6 db2.13 main.11 main.15}
4507c478bd9Sstevel@tonic-gatedo_test attach-4.10 {
4517c478bd9Sstevel@tonic-gate  execsql {
4527c478bd9Sstevel@tonic-gate    DETACH DATABASE db2;
4537c478bd9Sstevel@tonic-gate  }
4547c478bd9Sstevel@tonic-gate  execsql {
4557c478bd9Sstevel@tonic-gate    CREATE VIEW v3 AS SELECT x*100+y FROM t3;
4567c478bd9Sstevel@tonic-gate    SELECT * FROM v3;
4577c478bd9Sstevel@tonic-gate  } db2
4587c478bd9Sstevel@tonic-gate} {102 910 607 1314}
4597c478bd9Sstevel@tonic-gatedo_test attach-4.11 {
4607c478bd9Sstevel@tonic-gate  execsql {
4617c478bd9Sstevel@tonic-gate    CREATE VIEW v3 AS SELECT a*100+b FROM t3;
4627c478bd9Sstevel@tonic-gate    SELECT * FROM v3;
4637c478bd9Sstevel@tonic-gate  }
4647c478bd9Sstevel@tonic-gate} {910 1112 1516}
4657c478bd9Sstevel@tonic-gatedo_test attach-4.12 {
4667c478bd9Sstevel@tonic-gate  execsql {
4677c478bd9Sstevel@tonic-gate    ATTACH DATABASE 'test2.db' AS db2;
4687c478bd9Sstevel@tonic-gate    SELECT * FROM db2.v3;
4697c478bd9Sstevel@tonic-gate  }
4707c478bd9Sstevel@tonic-gate} {102 910 607 1314}
4717c478bd9Sstevel@tonic-gatedo_test attach-4.13 {
4727c478bd9Sstevel@tonic-gate  execsql {
4737c478bd9Sstevel@tonic-gate    SELECT * FROM main.v3;
4747c478bd9Sstevel@tonic-gate  }
4757c478bd9Sstevel@tonic-gate} {910 1112 1516}
4767c478bd9Sstevel@tonic-gate
4777c478bd9Sstevel@tonic-gate# Tests for the sqliteFix...() routines in attach.c
4787c478bd9Sstevel@tonic-gate#
4797c478bd9Sstevel@tonic-gatedo_test attach-5.1 {
4807c478bd9Sstevel@tonic-gate  db close
4817c478bd9Sstevel@tonic-gate  sqlite db test.db
4827c478bd9Sstevel@tonic-gate  db2 close
4837c478bd9Sstevel@tonic-gate  file delete -force test2.db
4847c478bd9Sstevel@tonic-gate  sqlite db2 test2.db
4857c478bd9Sstevel@tonic-gate  catchsql {
4867c478bd9Sstevel@tonic-gate    ATTACH DATABASE 'test.db' AS orig;
4877c478bd9Sstevel@tonic-gate    CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN;
4887c478bd9Sstevel@tonic-gate      SELECT 'no-op';
4897c478bd9Sstevel@tonic-gate    END;
4907c478bd9Sstevel@tonic-gate  } db2
4917c478bd9Sstevel@tonic-gate} {1 {triggers may not be added to auxiliary database orig}}
4927c478bd9Sstevel@tonic-gatedo_test attach-5.2 {
4937c478bd9Sstevel@tonic-gate  catchsql {
4947c478bd9Sstevel@tonic-gate    CREATE TABLE t5(x,y);
4957c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
4967c478bd9Sstevel@tonic-gate      SELECT 'no-op';
4977c478bd9Sstevel@tonic-gate    END;
4987c478bd9Sstevel@tonic-gate  } db2
4997c478bd9Sstevel@tonic-gate} {0 {}}
5007c478bd9Sstevel@tonic-gatedo_test attach-5.3 {
5017c478bd9Sstevel@tonic-gate  catchsql {
5027c478bd9Sstevel@tonic-gate    DROP TRIGGER r5;
5037c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
5047c478bd9Sstevel@tonic-gate      SELECT 'no-op' FROM orig.t1;
5057c478bd9Sstevel@tonic-gate    END;
5067c478bd9Sstevel@tonic-gate  } db2
5077c478bd9Sstevel@tonic-gate} {1 {trigger r5 cannot reference objects in database orig}}
5087c478bd9Sstevel@tonic-gatedo_test attach-5.4 {
5097c478bd9Sstevel@tonic-gate  catchsql {
5107c478bd9Sstevel@tonic-gate    CREATE TEMP TABLE t6(p,q,r);
5117c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
5127c478bd9Sstevel@tonic-gate      SELECT 'no-op' FROM temp.t6;
5137c478bd9Sstevel@tonic-gate    END;
5147c478bd9Sstevel@tonic-gate  } db2
5157c478bd9Sstevel@tonic-gate} {1 {trigger r5 cannot reference objects in database temp}}
5167c478bd9Sstevel@tonic-gatedo_test attach-5.5 {
5177c478bd9Sstevel@tonic-gate  catchsql {
5187c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
5197c478bd9Sstevel@tonic-gate      SELECT 'no-op' || (SELECT * FROM temp.t6);
5207c478bd9Sstevel@tonic-gate    END;
5217c478bd9Sstevel@tonic-gate  } db2
5227c478bd9Sstevel@tonic-gate} {1 {trigger r5 cannot reference objects in database temp}}
5237c478bd9Sstevel@tonic-gatedo_test attach-5.6 {
5247c478bd9Sstevel@tonic-gate  catchsql {
5257c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
5267c478bd9Sstevel@tonic-gate      SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
5277c478bd9Sstevel@tonic-gate    END;
5287c478bd9Sstevel@tonic-gate  } db2
5297c478bd9Sstevel@tonic-gate} {1 {trigger r5 cannot reference objects in database temp}}
5307c478bd9Sstevel@tonic-gatedo_test attach-5.7 {
5317c478bd9Sstevel@tonic-gate  catchsql {
5327c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
5337c478bd9Sstevel@tonic-gate      SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6);
5347c478bd9Sstevel@tonic-gate    END;
5357c478bd9Sstevel@tonic-gate  } db2
5367c478bd9Sstevel@tonic-gate} {1 {trigger r5 cannot reference objects in database temp}}
5377c478bd9Sstevel@tonic-gatedo_test attach-5.7 {
5387c478bd9Sstevel@tonic-gate  catchsql {
5397c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
5407c478bd9Sstevel@tonic-gate      SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1;
5417c478bd9Sstevel@tonic-gate    END;
5427c478bd9Sstevel@tonic-gate  } db2
5437c478bd9Sstevel@tonic-gate} {1 {trigger r5 cannot reference objects in database temp}}
5447c478bd9Sstevel@tonic-gatedo_test attach-5.8 {
5457c478bd9Sstevel@tonic-gate  catchsql {
5467c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
5477c478bd9Sstevel@tonic-gate      INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5);
5487c478bd9Sstevel@tonic-gate    END;
5497c478bd9Sstevel@tonic-gate  } db2
5507c478bd9Sstevel@tonic-gate} {1 {trigger r5 cannot reference objects in database temp}}
5517c478bd9Sstevel@tonic-gatedo_test attach-5.9 {
5527c478bd9Sstevel@tonic-gate  catchsql {
5537c478bd9Sstevel@tonic-gate    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
5547c478bd9Sstevel@tonic-gate      DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
5557c478bd9Sstevel@tonic-gate    END;
5567c478bd9Sstevel@tonic-gate  } db2
5577c478bd9Sstevel@tonic-gate} {1 {trigger r5 cannot reference objects in database temp}}
5587c478bd9Sstevel@tonic-gate
5597c478bd9Sstevel@tonic-gate# Check to make sure we get a sensible error if unable to open
5607c478bd9Sstevel@tonic-gate# the file that we are trying to attach.
5617c478bd9Sstevel@tonic-gate#
5627c478bd9Sstevel@tonic-gatedo_test attach-6.1 {
5637c478bd9Sstevel@tonic-gate  catchsql {
5647c478bd9Sstevel@tonic-gate    ATTACH DATABASE 'no-such-file' AS nosuch;
5657c478bd9Sstevel@tonic-gate  }
5667c478bd9Sstevel@tonic-gate} {1 {cannot attach empty database: nosuch}}
5677c478bd9Sstevel@tonic-gatefile delete -force no-such-file
5687c478bd9Sstevel@tonic-gateif {$tcl_platform(platform)=="unix"} {
5697c478bd9Sstevel@tonic-gate  do_test attach-6.2 {
5707c478bd9Sstevel@tonic-gate    sqlite dbx cannot-read
5717c478bd9Sstevel@tonic-gate    dbx eval {CREATE TABLE t1(a,b,c)}
5727c478bd9Sstevel@tonic-gate    dbx close
5737c478bd9Sstevel@tonic-gate    file attributes cannot-read -permission 0000
5747c478bd9Sstevel@tonic-gate    catchsql {
5757c478bd9Sstevel@tonic-gate      ATTACH DATABASE 'cannot-read' AS noread;
5767c478bd9Sstevel@tonic-gate    }
5777c478bd9Sstevel@tonic-gate  } {1 {unable to open database: cannot-read}}
5787c478bd9Sstevel@tonic-gate  file delete -force cannot-read
5797c478bd9Sstevel@tonic-gate}
5807c478bd9Sstevel@tonic-gate
5817c478bd9Sstevel@tonic-gatefor {set i 2} {$i<=15} {incr i} {
5827c478bd9Sstevel@tonic-gate  catch {db$i close}
5837c478bd9Sstevel@tonic-gate}
5847c478bd9Sstevel@tonic-gatefile delete -force test2.db
5857c478bd9Sstevel@tonic-gate
5867c478bd9Sstevel@tonic-gate
5877c478bd9Sstevel@tonic-gatefinish_test
588