wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 file delete -force test2.db
39 file delete -force 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