xref: /sqlite-3.40.0/test/attach3.test (revision a8858103) 
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.2 2004/05/28 12:11:21 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 the tables 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}
125do_test attach4-4.4 {
126  execsql {
127    DROP TABLE aux.t3;
128    SELECT * FROM aux.sqlite_master;
129  }
130} {}
131
132finish_test
133
134
135
136
137