xref: /sqlite-3.40.0/test/attach3.test (revision 48dec7e2) 
1# 2003 July 1
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
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.
9#
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.
14#
15# $Id: attach3.test,v 1.3 2004/05/28 12:33:32 danielk1977 Exp $
16#
17
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# Create tables t1 and t2 in the main database
23execsql {
24  CREATE TABLE t1(a, b);
25  CREATE TABLE t2(c, d);
26}
27
28# Create tables t1 and t2 in database file test2.db
29file delete -force test2.db
30sqlite db2 test2.db
31execsql {
32  CREATE TABLE t1(a, b);
33  CREATE TABLE t2(c, d);
34} db2
35db2 close
36
37# Create a table in the auxilary database.
38do_test attach3-1.1 {
39  execsql {
40    ATTACH 'test2.db' AS aux;
41  }
42} {}
43do_test attach3-1.2 {
44  execsql {
45    CREATE TABLE aux.t3(e, f);
46  }
47} {}
48do_test attach3-1.3 {
49  execsql {
50    SELECT * FROM sqlite_master WHERE name = 't3';
51  }
52} {}
53do_test attach3-1.4 {
54  execsql {
55    SELECT * FROM aux.sqlite_master WHERE name = 't3';
56  }
57} {table t3 t3 4 {CREATE TABLE t3(e, f)}}
58do_test attach3-1.5 {
59  execsql {
60    INSERT INTO t3 VALUES(1, 2);
61    SELECT * FROM t3;
62  }
63} {1 2}
64
65# Create an index on the auxilary database table.
66do_test attach4-2.1 {
67  execsql {
68    CREATE INDEX aux.i1 on t3(e);
69  }
70} {}
71execsql {
72  pragma vdbe_trace = off;
73}
74do_test attach4-2.2 {
75  execsql {
76    SELECT * FROM sqlite_master WHERE name = 'i1';
77  }
78} {}
79do_test attach4-2.3 {
80  execsql {
81    SELECT * FROM aux.sqlite_master WHERE name = 'i1';
82  }
83} {index i1 t3 5 {CREATE INDEX i1 on t3(e)}}
84
85# Drop the index on the aux database table.
86do_test attach4-3.1 {
87  execsql {
88    DROP INDEX aux.i1;
89    SELECT * FROM aux.sqlite_master WHERE name = 'i1';
90  }
91} {}
92do_test attach4-3.2 {
93  execsql {
94    CREATE INDEX aux.i1 on t3(e);
95    SELECT * FROM aux.sqlite_master WHERE name = 'i1';
96  }
97} {index i1 t3 5 {CREATE INDEX i1 on t3(e)}}
98do_test attach4-3.3 {
99  execsql {
100    DROP INDEX i1;
101    SELECT * FROM aux.sqlite_master WHERE name = 'i1';
102  }
103} {}
104
105# Drop tables t1 and t2 in the auxilary database.
106do_test attach4-4.1 {
107  execsql {
108    DROP TABLE aux.t1;
109    SELECT name FROM aux.sqlite_master;
110  }
111} {t2 t3}
112do_test attach4-4.2 {
113  # This will drop main.t2
114  execsql {
115    DROP TABLE t2;
116    SELECT name FROM aux.sqlite_master;
117  }
118} {t2 t3}
119do_test attach4-4.3 {
120  execsql {
121    DROP TABLE t2;
122    SELECT name FROM aux.sqlite_master;
123  }
124} {t3}
125
126# Create a view in the auxilary database.
127do_test attach4-5.1 {
128  execsql {
129    CREATE VIEW aux.v1 AS SELECT * FROM t3;
130  }
131} {}
132do_test attach4-5.2 {
133  execsql {
134    SELECT * FROM aux.sqlite_master WHERE name = 'v1';
135  }
136} {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}}
137do_test attach4-5.3 {
138  execsql {
139    INSERT INTO aux.t3 VALUES('hello', 'world');
140    SELECT * FROM v1;
141  }
142} {1 2 hello world}
143
144# Drop the view
145do_test attach4-6.1 {
146  execsql {
147    DROP VIEW aux.v1;
148  }
149} {}
150do_test attach4-5.2 {
151  execsql {
152    SELECT * FROM aux.sqlite_master WHERE name = 'v1';
153  }
154} {}
155
156finish_test
157
158
159
160
161