1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
|
# 2003 July 1
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this script is testing the ATTACH and DETACH commands
# and schema changes to attached databases.
#
# $Id: attach3.test,v 1.15 2005/03/29 03:11:00 danielk1977 Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Create tables t1 and t2 in the main database
execsql {
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d);
}
# Create tables t1 and t2 in database file test2.db
file delete -force test2.db
file delete -force test2.db-journal
sqlite3 db2 test2.db
execsql {
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d);
} db2
db2 close
# Create a table in the auxilary database.
do_test attach3-1.1 {
execsql {
ATTACH 'test2.db' AS aux;
}
} {}
do_test attach3-1.2 {
execsql {
CREATE TABLE aux.t3(e, f);
}
} {}
do_test attach3-1.3 {
execsql {
SELECT * FROM sqlite_master WHERE name = 't3';
}
} {}
do_test attach3-1.4 {
execsql {
SELECT * FROM aux.sqlite_master WHERE name = 't3';
}
} "table t3 t3 [expr $AUTOVACUUM?5:4] {CREATE TABLE t3(e, f)}"
do_test attach3-1.5 {
execsql {
INSERT INTO t3 VALUES(1, 2);
SELECT * FROM t3;
}
} {1 2}
# Create an index on the auxilary database table.
do_test attach3-2.1 {
execsql {
CREATE INDEX aux.i1 on t3(e);
}
} {}
do_test attach3-2.2 {
execsql {
SELECT * FROM sqlite_master WHERE name = 'i1';
}
} {}
do_test attach3-2.3 {
execsql {
SELECT * FROM aux.sqlite_master WHERE name = 'i1';
}
} "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
# Drop the index on the aux database table.
do_test attach3-3.1 {
execsql {
DROP INDEX aux.i1;
SELECT * FROM aux.sqlite_master WHERE name = 'i1';
}
} {}
do_test attach3-3.2 {
execsql {
CREATE INDEX aux.i1 on t3(e);
SELECT * FROM aux.sqlite_master WHERE name = 'i1';
}
} "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
do_test attach3-3.3 {
execsql {
DROP INDEX i1;
SELECT * FROM aux.sqlite_master WHERE name = 'i1';
}
} {}
# Drop tables t1 and t2 in the auxilary database.
do_test attach3-4.1 {
execsql {
DROP TABLE aux.t1;
SELECT name FROM aux.sqlite_master;
}
} {t2 t3}
do_test attach3-4.2 {
# This will drop main.t2
execsql {
DROP TABLE t2;
SELECT name FROM aux.sqlite_master;
}
} {t2 t3}
do_test attach3-4.3 {
execsql {
DROP TABLE t2;
SELECT name FROM aux.sqlite_master;
}
} {t3}
# Create a view in the auxilary database.
ifcapable view {
do_test attach3-5.1 {
execsql {
CREATE VIEW aux.v1 AS SELECT * FROM t3;
}
} {}
do_test attach3-5.2 {
execsql {
SELECT * FROM aux.sqlite_master WHERE name = 'v1';
}
} {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}}
do_test attach3-5.3 {
execsql {
INSERT INTO aux.t3 VALUES('hello', 'world');
SELECT * FROM v1;
}
} {1 2 hello world}
# Drop the view
do_test attach3-6.1 {
execsql {
DROP VIEW aux.v1;
}
} {}
do_test attach3-6.2 {
execsql {
SELECT * FROM aux.sqlite_master WHERE name = 'v1';
}
} {}
} ;# ifcapable view
ifcapable {trigger} {
# Create a trigger in the auxilary database.
do_test attach3-7.1 {
execsql {
CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN
INSERT INTO t3 VALUES(new.e*2, new.f*2);
END;
}
} {}
do_test attach3-7.2 {
execsql {
DELETE FROM t3;
INSERT INTO t3 VALUES(10, 20);
SELECT * FROM t3;
}
} {10 20 20 40}
do_test attach3-5.3 {
execsql {
SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
}
} {trigger tr1 t3 0 {CREATE TRIGGER tr1 AFTER INSERT ON t3 BEGIN
INSERT INTO t3 VALUES(new.e*2, new.f*2);
END}}
# Drop the trigger
do_test attach3-8.1 {
execsql {
DROP TRIGGER aux.tr1;
}
} {}
do_test attach3-8.2 {
execsql {
SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
}
} {}
ifcapable tempdb {
# Try to trick SQLite into dropping the wrong temp trigger.
do_test attach3-9.0 {
execsql {
CREATE TABLE main.t4(a, b, c);
CREATE TABLE aux.t4(a, b, c);
CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN
SELECT 'hello world';
END;
SELECT count(*) FROM sqlite_temp_master;
}
} {1}
do_test attach3-9.1 {
execsql {
DROP TABLE main.t4;
SELECT count(*) FROM sqlite_temp_master;
}
} {1}
do_test attach3-9.2 {
execsql {
DROP TABLE aux.t4;
SELECT count(*) FROM sqlite_temp_master;
}
} {0}
}
} ;# endif trigger
# Make sure the aux.sqlite_master table is read-only
do_test attach3-10.0 {
catchsql {
INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
}
} {1 {table sqlite_master may not be modified}}
# Failure to attach leaves us in a workable state.
# Ticket #811
#
do_test attach3-11.0 {
catchsql {
ATTACH DATABASE '/nodir/nofile.x' AS notadb;
}
} {1 {unable to open database: /nodir/nofile.x}}
do_test attach3-11.1 {
catchsql {
ATTACH DATABASE ':memory:' AS notadb;
}
} {0 {}}
do_test attach3-11.2 {
catchsql {
DETACH DATABASE notadb;
}
} {0 {}}
finish_test
|
