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