3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this script is testing the ATTACH and DETACH commands
13 # and schema changes to attached databases.
15 # $Id: attach3.test,v 1.15 2005/03/29 03:11:00 danielk1977 Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # Create tables t1 and t2 in the main database
24 CREATE TABLE t1(a, b);
25 CREATE TABLE t2(c, d);
28 # Create tables t1 and t2 in database file test2.db
29 file delete -force test2.db
30 file delete -force test2.db-journal
33 CREATE TABLE t1(a, b);
34 CREATE TABLE t2(c, d);
38 # Create a table in the auxilary database.
41 ATTACH 'test2.db' AS aux;
46 CREATE TABLE aux.t3(e, f);
51 SELECT * FROM sqlite_master WHERE name = 't3';
56 SELECT * FROM aux.sqlite_master WHERE name = 't3';
58 } "table t3 t3 [expr $AUTOVACUUM?5:4] {CREATE TABLE t3(e, f)}"
61 INSERT INTO t3 VALUES(1, 2);
66 # Create an index on the auxilary database table.
69 CREATE INDEX aux.i1 on t3(e);
74 SELECT * FROM sqlite_master WHERE name = 'i1';
79 SELECT * FROM aux.sqlite_master WHERE name = 'i1';
81 } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
83 # Drop the index on the aux database table.
87 SELECT * FROM aux.sqlite_master WHERE name = 'i1';
92 CREATE INDEX aux.i1 on t3(e);
93 SELECT * FROM aux.sqlite_master WHERE name = 'i1';
95 } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
99 SELECT * FROM aux.sqlite_master WHERE name = 'i1';
103 # Drop tables t1 and t2 in the auxilary database.
104 do_test attach3-4.1 {
107 SELECT name FROM aux.sqlite_master;
110 do_test attach3-4.2 {
111 # This will drop main.t2
114 SELECT name FROM aux.sqlite_master;
117 do_test attach3-4.3 {
120 SELECT name FROM aux.sqlite_master;
124 # Create a view in the auxilary database.
126 do_test attach3-5.1 {
128 CREATE VIEW aux.v1 AS SELECT * FROM t3;
131 do_test attach3-5.2 {
133 SELECT * FROM aux.sqlite_master WHERE name = 'v1';
135 } {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}}
136 do_test attach3-5.3 {
138 INSERT INTO aux.t3 VALUES('hello', 'world');
144 do_test attach3-6.1 {
149 do_test attach3-6.2 {
151 SELECT * FROM aux.sqlite_master WHERE name = 'v1';
156 ifcapable {trigger} {
157 # Create a trigger in the auxilary database.
158 do_test attach3-7.1 {
160 CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN
161 INSERT INTO t3 VALUES(new.e*2, new.f*2);
165 do_test attach3-7.2 {
168 INSERT INTO t3 VALUES(10, 20);
172 do_test attach3-5.3 {
174 SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
176 } {trigger tr1 t3 0 {CREATE TRIGGER tr1 AFTER INSERT ON t3 BEGIN
177 INSERT INTO t3 VALUES(new.e*2, new.f*2);
181 do_test attach3-8.1 {
183 DROP TRIGGER aux.tr1;
186 do_test attach3-8.2 {
188 SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
193 # Try to trick SQLite into dropping the wrong temp trigger.
194 do_test attach3-9.0 {
196 CREATE TABLE main.t4(a, b, c);
197 CREATE TABLE aux.t4(a, b, c);
198 CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN
199 SELECT 'hello world';
201 SELECT count(*) FROM sqlite_temp_master;
204 do_test attach3-9.1 {
207 SELECT count(*) FROM sqlite_temp_master;
210 do_test attach3-9.2 {
213 SELECT count(*) FROM sqlite_temp_master;
219 # Make sure the aux.sqlite_master table is read-only
220 do_test attach3-10.0 {
222 INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
224 } {1 {table sqlite_master may not be modified}}
226 # Failure to attach leaves us in a workable state.
229 do_test attach3-11.0 {
231 ATTACH DATABASE '/nodir/nofile.x' AS notadb;
233 } {1 {unable to open database: /nodir/nofile.x}}
234 do_test attach3-11.1 {
236 ATTACH DATABASE ':memory:' AS notadb;
239 do_test attach3-11.2 {
241 DETACH DATABASE notadb;