Annotation of embedaddon/sqlite3/test/attach3.test, revision 1.1.1.1
1.1 misho 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.18 2007/10/09 08:29:32 danielk1977 Exp $
16: #
17:
18: set testdir [file dirname $argv0]
19: source $testdir/tester.tcl
20:
21: ifcapable !attach {
22: finish_test
23: return
24: }
25:
26: # The tests in this file were written before SQLite supported recursive
27: # trigger invocation, and some tests depend on that to pass. So disable
28: # recursive triggers for this file.
29: catchsql { pragma recursive_triggers = off }
30:
31: # Create tables t1 and t2 in the main database
32: execsql {
33: CREATE TABLE t1(a, b);
34: CREATE TABLE t2(c, d);
35: }
36:
37: # Create tables t1 and t2 in database file test2.db
38: forcedelete test2.db
39: forcedelete test2.db-journal
40: sqlite3 db2 test2.db
41: execsql {
42: CREATE TABLE t1(a, b);
43: CREATE TABLE t2(c, d);
44: } db2
45: db2 close
46:
47: # Create a table in the auxilary database.
48: do_test attach3-1.1 {
49: execsql {
50: ATTACH 'test2.db' AS aux;
51: }
52: } {}
53: do_test attach3-1.2 {
54: execsql {
55: CREATE TABLE aux.t3(e, f);
56: }
57: } {}
58: do_test attach3-1.3 {
59: execsql {
60: SELECT * FROM sqlite_master WHERE name = 't3';
61: }
62: } {}
63: do_test attach3-1.4 {
64: execsql {
65: SELECT * FROM aux.sqlite_master WHERE name = 't3';
66: }
67: } "table t3 t3 [expr $AUTOVACUUM?5:4] {CREATE TABLE t3(e, f)}"
68: do_test attach3-1.5 {
69: execsql {
70: INSERT INTO t3 VALUES(1, 2);
71: SELECT * FROM t3;
72: }
73: } {1 2}
74:
75: # Create an index on the auxilary database table.
76: do_test attach3-2.1 {
77: execsql {
78: CREATE INDEX aux.i1 on t3(e);
79: }
80: } {}
81: do_test attach3-2.2 {
82: execsql {
83: SELECT * FROM sqlite_master WHERE name = 'i1';
84: }
85: } {}
86: do_test attach3-2.3 {
87: execsql {
88: SELECT * FROM aux.sqlite_master WHERE name = 'i1';
89: }
90: } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
91:
92: # Drop the index on the aux database table.
93: do_test attach3-3.1 {
94: execsql {
95: DROP INDEX aux.i1;
96: SELECT * FROM aux.sqlite_master WHERE name = 'i1';
97: }
98: } {}
99: do_test attach3-3.2 {
100: execsql {
101: CREATE INDEX aux.i1 on t3(e);
102: SELECT * FROM aux.sqlite_master WHERE name = 'i1';
103: }
104: } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
105: do_test attach3-3.3 {
106: execsql {
107: DROP INDEX i1;
108: SELECT * FROM aux.sqlite_master WHERE name = 'i1';
109: }
110: } {}
111:
112: # Drop tables t1 and t2 in the auxilary database.
113: do_test attach3-4.1 {
114: execsql {
115: DROP TABLE aux.t1;
116: SELECT name FROM aux.sqlite_master;
117: }
118: } {t2 t3}
119: do_test attach3-4.2 {
120: # This will drop main.t2
121: execsql {
122: DROP TABLE t2;
123: SELECT name FROM aux.sqlite_master;
124: }
125: } {t2 t3}
126: do_test attach3-4.3 {
127: execsql {
128: DROP TABLE t2;
129: SELECT name FROM aux.sqlite_master;
130: }
131: } {t3}
132:
133: # Create a view in the auxilary database.
134: ifcapable view {
135: do_test attach3-5.1 {
136: execsql {
137: CREATE VIEW aux.v1 AS SELECT * FROM t3;
138: }
139: } {}
140: do_test attach3-5.2 {
141: execsql {
142: SELECT * FROM aux.sqlite_master WHERE name = 'v1';
143: }
144: } {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}}
145: do_test attach3-5.3 {
146: execsql {
147: INSERT INTO aux.t3 VALUES('hello', 'world');
148: SELECT * FROM v1;
149: }
150: } {1 2 hello world}
151:
152: # Drop the view
153: do_test attach3-6.1 {
154: execsql {
155: DROP VIEW aux.v1;
156: }
157: } {}
158: do_test attach3-6.2 {
159: execsql {
160: SELECT * FROM aux.sqlite_master WHERE name = 'v1';
161: }
162: } {}
163: } ;# ifcapable view
164:
165: ifcapable {trigger} {
166: # Create a trigger in the auxilary database.
167: do_test attach3-7.1 {
168: execsql {
169: CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN
170: INSERT INTO t3 VALUES(new.e*2, new.f*2);
171: END;
172: }
173: } {}
174: do_test attach3-7.2 {
175: execsql {
176: DELETE FROM t3;
177: INSERT INTO t3 VALUES(10, 20);
178: SELECT * FROM t3;
179: }
180: } {10 20 20 40}
181: do_test attach3-5.3 {
182: execsql {
183: SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
184: }
185: } {trigger tr1 t3 0 {CREATE TRIGGER tr1 AFTER INSERT ON t3 BEGIN
186: INSERT INTO t3 VALUES(new.e*2, new.f*2);
187: END}}
188:
189: # Drop the trigger
190: do_test attach3-8.1 {
191: execsql {
192: DROP TRIGGER aux.tr1;
193: }
194: } {}
195: do_test attach3-8.2 {
196: execsql {
197: SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
198: }
199: } {}
200:
201: ifcapable tempdb {
202: # Try to trick SQLite into dropping the wrong temp trigger.
203: do_test attach3-9.0 {
204: execsql {
205: CREATE TABLE main.t4(a, b, c);
206: CREATE TABLE aux.t4(a, b, c);
207: CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN
208: SELECT 'hello world';
209: END;
210: SELECT count(*) FROM sqlite_temp_master;
211: }
212: } {1}
213: do_test attach3-9.1 {
214: execsql {
215: DROP TABLE main.t4;
216: SELECT count(*) FROM sqlite_temp_master;
217: }
218: } {1}
219: do_test attach3-9.2 {
220: execsql {
221: DROP TABLE aux.t4;
222: SELECT count(*) FROM sqlite_temp_master;
223: }
224: } {0}
225: }
226: } ;# endif trigger
227:
228: # Make sure the aux.sqlite_master table is read-only
229: do_test attach3-10.0 {
230: catchsql {
231: INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
232: }
233: } {1 {table sqlite_master may not be modified}}
234:
235: # Failure to attach leaves us in a workable state.
236: # Ticket #811
237: #
238: do_test attach3-11.0 {
239: catchsql {
240: ATTACH DATABASE '/nodir/nofile.x' AS notadb;
241: }
242: } {1 {unable to open database: /nodir/nofile.x}}
243: do_test attach3-11.1 {
244: catchsql {
245: ATTACH DATABASE ':memory:' AS notadb;
246: }
247: } {0 {}}
248: do_test attach3-11.2 {
249: catchsql {
250: DETACH DATABASE notadb;
251: }
252: } {0 {}}
253:
254: # Return a list of attached databases
255: #
256: proc db_list {} {
257: set x [execsql {
258: PRAGMA database_list;
259: }]
260: set y {}
261: foreach {n id file} $x {lappend y $id}
262: return $y
263: }
264:
265: ifcapable schema_pragmas&&tempdb {
266:
267: ifcapable !trigger {
268: execsql {create temp table dummy(dummy)}
269: }
270:
271: # Ticket #1825
272: #
273: do_test attach3-12.1 {
274: db_list
275: } {main temp aux}
276: do_test attach3-12.2 {
277: execsql {
278: ATTACH DATABASE ? AS ?
279: }
280: db_list
281: } {main temp aux {}}
282: do_test attach3-12.3 {
283: execsql {
284: DETACH aux
285: }
286: db_list
287: } {main temp {}}
288: do_test attach3-12.4 {
289: execsql {
290: DETACH ?
291: }
292: db_list
293: } {main temp}
294: do_test attach3-12.5 {
295: execsql {
296: ATTACH DATABASE '' AS ''
297: }
298: db_list
299: } {main temp {}}
300: do_test attach3-12.6 {
301: execsql {
302: DETACH ''
303: }
304: db_list
305: } {main temp}
306: do_test attach3-12.7 {
307: execsql {
308: ATTACH DATABASE '' AS ?
309: }
310: db_list
311: } {main temp {}}
312: do_test attach3-12.8 {
313: execsql {
314: DETACH ''
315: }
316: db_list
317: } {main temp}
318: do_test attach3-12.9 {
319: execsql {
320: ATTACH DATABASE '' AS NULL
321: }
322: db_list
323: } {main temp {}}
324: do_test attach3-12.10 {
325: breakpoint
326: execsql {
327: DETACH ?
328: }
329: db_list
330: } {main temp}
331: do_test attach3-12.11 {
332: catchsql {
333: DETACH NULL
334: }
335: } {1 {no such database: }}
336: do_test attach3-12.12 {
337: catchsql {
338: ATTACH null AS null;
339: ATTACH '' AS '';
340: }
341: } {1 {database is already in use}}
342: do_test attach3-12.13 {
343: db_list
344: } {main temp {}}
345: do_test attach3-12.14 {
346: execsql {
347: DETACH '';
348: }
349: db_list
350: } {main temp}
351:
352: } ;# ifcapable pragma
353:
354: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to attach3.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test