wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2005 February 18 |
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 that SQLite can handle a subtle |
||
13 | # file format change that may be used in the future to implement |
||
14 | # "ALTER TABLE ... ADD COLUMN". |
||
15 | # |
||
16 | # $Id: alter2.test,v 1.14 2009/04/07 14:14:22 danielk1977 Exp $ |
||
17 | # |
||
18 | |||
19 | set testdir [file dirname $argv0] |
||
20 | source $testdir/tester.tcl |
||
21 | |||
22 | # We have to have pragmas in order to do this test |
||
23 | ifcapable {!pragma} return |
||
24 | |||
25 | # Do not use a codec for tests in this file, as the database file is |
||
26 | # manipulated directly using tcl scripts. See proc [set_file_format]. |
||
27 | # |
||
28 | do_not_use_codec |
||
29 | |||
30 | # The file format change affects the way row-records stored in tables (but |
||
31 | # not indices) are interpreted. Before version 3.1.3, a row-record for a |
||
32 | # table with N columns was guaranteed to contain exactly N fields. As |
||
33 | # of version 3.1.3, the record may contain up to N fields. In this case |
||
34 | # the M fields that are present are the values for the left-most M |
||
35 | # columns. The (N-M) rightmost columns contain NULL. |
||
36 | # |
||
37 | # If any records in the database contain less fields than their table |
||
38 | # has columns, then the file-format meta value should be set to (at least) 2. |
||
39 | # |
||
40 | |||
41 | # This procedure sets the value of the file-format in file 'test.db' |
||
42 | # to $newval. Also, the schema cookie is incremented. |
||
43 | # |
||
44 | proc set_file_format {newval} { |
||
45 | hexio_write test.db 44 [hexio_render_int32 $newval] |
||
46 | set schemacookie [hexio_get_int [hexio_read test.db 40 4]] |
||
47 | incr schemacookie |
||
48 | hexio_write test.db 40 [hexio_render_int32 $schemacookie] |
||
49 | return {} |
||
50 | } |
||
51 | |||
52 | # This procedure returns the value of the file-format in file 'test.db'. |
||
53 | # |
||
54 | proc get_file_format {{fname test.db}} { |
||
55 | return [hexio_get_int [hexio_read $fname 44 4]] |
||
56 | } |
||
57 | |||
58 | # This procedure sets the SQL statement stored for table $tbl in the |
||
59 | # sqlite_master table of file 'test.db' to $sql. Also set the file format |
||
60 | # to the supplied value. This is 2 if the added column has a default that is |
||
61 | # NULL, or 3 otherwise. |
||
62 | # |
||
63 | proc alter_table {tbl sql {file_format 2}} { |
||
64 | sqlite3 dbat test.db |
||
65 | set s [string map {' ''} $sql] |
||
66 | set t [string map {' ''} $tbl] |
||
67 | dbat eval [subst { |
||
68 | PRAGMA writable_schema = 1; |
||
69 | UPDATE sqlite_master SET sql = '$s' WHERE name = '$t' AND type = 'table'; |
||
70 | PRAGMA writable_schema = 0; |
||
71 | }] |
||
72 | dbat close |
||
73 | set_file_format 2 |
||
74 | } |
||
75 | |||
76 | # Create bogus application-defined functions for functions used |
||
77 | # internally by ALTER TABLE, to ensure that ALTER TABLE falls back |
||
78 | # to the built-in functions. |
||
79 | # |
||
80 | proc failing_app_func {args} {error "bad function"} |
||
81 | do_test alter2-1.0 { |
||
82 | db func substr failing_app_func |
||
83 | db func like failing_app_func |
||
84 | db func sqlite_rename_table failing_app_func |
||
85 | db func sqlite_rename_trigger failing_app_func |
||
86 | db func sqlite_rename_parent failing_app_func |
||
87 | catchsql {SELECT substr('abcdefg',1,3)} |
||
88 | } {1 {bad function}} |
||
89 | |||
90 | |||
91 | #----------------------------------------------------------------------- |
||
92 | # Some basic tests to make sure short rows are handled. |
||
93 | # |
||
94 | do_test alter2-1.1 { |
||
95 | execsql { |
||
96 | CREATE TABLE abc(a, b); |
||
97 | INSERT INTO abc VALUES(1, 2); |
||
98 | INSERT INTO abc VALUES(3, 4); |
||
99 | INSERT INTO abc VALUES(5, 6); |
||
100 | } |
||
101 | } {} |
||
102 | do_test alter2-1.2 { |
||
103 | # ALTER TABLE abc ADD COLUMN c; |
||
104 | alter_table abc {CREATE TABLE abc(a, b, c);} |
||
105 | } {} |
||
106 | do_test alter2-1.3 { |
||
107 | execsql { |
||
108 | SELECT * FROM abc; |
||
109 | } |
||
110 | } {1 2 {} 3 4 {} 5 6 {}} |
||
111 | do_test alter2-1.4 { |
||
112 | execsql { |
||
113 | UPDATE abc SET c = 10 WHERE a = 1; |
||
114 | SELECT * FROM abc; |
||
115 | } |
||
116 | } {1 2 10 3 4 {} 5 6 {}} |
||
117 | do_test alter2-1.5 { |
||
118 | execsql { |
||
119 | CREATE INDEX abc_i ON abc(c); |
||
120 | } |
||
121 | } {} |
||
122 | do_test alter2-1.6 { |
||
123 | execsql { |
||
124 | SELECT c FROM abc ORDER BY c; |
||
125 | } |
||
126 | } {{} {} 10} |
||
127 | do_test alter2-1.7 { |
||
128 | execsql { |
||
129 | SELECT * FROM abc WHERE c = 10; |
||
130 | } |
||
131 | } {1 2 10} |
||
132 | do_test alter2-1.8 { |
||
133 | execsql { |
||
134 | SELECT sum(a), c FROM abc GROUP BY c; |
||
135 | } |
||
136 | } {8 {} 1 10} |
||
137 | do_test alter2-1.9 { |
||
138 | # ALTER TABLE abc ADD COLUMN d; |
||
139 | alter_table abc {CREATE TABLE abc(a, b, c, d);} |
||
140 | execsql { SELECT * FROM abc; } |
||
141 | execsql { |
||
142 | UPDATE abc SET d = 11 WHERE c IS NULL AND a<4; |
||
143 | SELECT * FROM abc; |
||
144 | } |
||
145 | } {1 2 10 {} 3 4 {} 11 5 6 {} {}} |
||
146 | do_test alter2-1.10 { |
||
147 | execsql { |
||
148 | SELECT typeof(d) FROM abc; |
||
149 | } |
||
150 | } {null integer null} |
||
151 | do_test alter2-1.99 { |
||
152 | execsql { |
||
153 | DROP TABLE abc; |
||
154 | } |
||
155 | } {} |
||
156 | |||
157 | #----------------------------------------------------------------------- |
||
158 | # Test that views work when the underlying table structure is changed. |
||
159 | # |
||
160 | ifcapable view { |
||
161 | do_test alter2-2.1 { |
||
162 | execsql { |
||
163 | CREATE TABLE abc2(a, b, c); |
||
164 | INSERT INTO abc2 VALUES(1, 2, 10); |
||
165 | INSERT INTO abc2 VALUES(3, 4, NULL); |
||
166 | INSERT INTO abc2 VALUES(5, 6, NULL); |
||
167 | CREATE VIEW abc2_v AS SELECT * FROM abc2; |
||
168 | SELECT * FROM abc2_v; |
||
169 | } |
||
170 | } {1 2 10 3 4 {} 5 6 {}} |
||
171 | do_test alter2-2.2 { |
||
172 | # ALTER TABLE abc ADD COLUMN d; |
||
173 | alter_table abc2 {CREATE TABLE abc2(a, b, c, d);} |
||
174 | execsql { |
||
175 | SELECT * FROM abc2_v; |
||
176 | } |
||
177 | } {1 2 10 {} 3 4 {} {} 5 6 {} {}} |
||
178 | do_test alter2-2.3 { |
||
179 | execsql { |
||
180 | DROP TABLE abc2; |
||
181 | DROP VIEW abc2_v; |
||
182 | } |
||
183 | } {} |
||
184 | } |
||
185 | |||
186 | #----------------------------------------------------------------------- |
||
187 | # Test that triggers work when a short row is copied to the old.* |
||
188 | # trigger pseudo-table. |
||
189 | # |
||
190 | ifcapable trigger { |
||
191 | do_test alter2-3.1 { |
||
192 | execsql { |
||
193 | CREATE TABLE abc3(a, b); |
||
194 | CREATE TABLE blog(o, n); |
||
195 | CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN |
||
196 | INSERT INTO blog VALUES(old.b, new.b); |
||
197 | END; |
||
198 | } |
||
199 | } {} |
||
200 | do_test alter2-3.2 { |
||
201 | execsql { |
||
202 | INSERT INTO abc3 VALUES(1, 4); |
||
203 | UPDATE abc3 SET b = 2 WHERE b = 4; |
||
204 | SELECT * FROM blog; |
||
205 | } |
||
206 | } {4 2} |
||
207 | do_test alter2-3.3 { |
||
208 | execsql { |
||
209 | INSERT INTO abc3 VALUES(3, 4); |
||
210 | INSERT INTO abc3 VALUES(5, 6); |
||
211 | } |
||
212 | alter_table abc3 {CREATE TABLE abc3(a, b, c);} |
||
213 | execsql { |
||
214 | SELECT * FROM abc3; |
||
215 | } |
||
216 | } {1 2 {} 3 4 {} 5 6 {}} |
||
217 | do_test alter2-3.4 { |
||
218 | execsql { |
||
219 | UPDATE abc3 SET b = b*2 WHERE a<4; |
||
220 | SELECT * FROM abc3; |
||
221 | } |
||
222 | } {1 4 {} 3 8 {} 5 6 {}} |
||
223 | do_test alter2-3.5 { |
||
224 | execsql { |
||
225 | SELECT * FROM blog; |
||
226 | } |
||
227 | } {4 2 2 4 4 8} |
||
228 | |||
229 | do_test alter2-3.6 { |
||
230 | execsql { |
||
231 | CREATE TABLE clog(o, n); |
||
232 | CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN |
||
233 | INSERT INTO clog VALUES(old.c, new.c); |
||
234 | END; |
||
235 | UPDATE abc3 SET c = a*2; |
||
236 | SELECT * FROM clog; |
||
237 | } |
||
238 | } {{} 2 {} 6 {} 10} |
||
239 | } else { |
||
240 | execsql { CREATE TABLE abc3(a, b); } |
||
241 | } |
||
242 | |||
243 | #--------------------------------------------------------------------- |
||
244 | # Check that an error occurs if the database is upgraded to a file |
||
245 | # format that SQLite does not support (in this case 5). Note: The |
||
246 | # file format is checked each time the schema is read, so changing the |
||
247 | # file format requires incrementing the schema cookie. |
||
248 | # |
||
249 | do_test alter2-4.1 { |
||
250 | db close |
||
251 | set_file_format 5 |
||
252 | catch { sqlite3 db test.db } |
||
253 | set {} {} |
||
254 | } {} |
||
255 | do_test alter2-4.2 { |
||
256 | # We have to run two queries here because the Tcl interface uses |
||
257 | # sqlite3_prepare_v2(). In this case, the first query encounters an |
||
258 | # SQLITE_SCHEMA error. Then, when trying to recompile the statement, the |
||
259 | # "unsupported file format" error is encountered. So the error code |
||
260 | # returned is SQLITE_SCHEMA, not SQLITE_ERROR as required by the following |
||
261 | # test case. |
||
262 | # |
||
263 | # When the query is attempted a second time, the same error message is |
||
264 | # returned but the error code is SQLITE_ERROR, because the unsupported |
||
265 | # file format was detected during a call to sqlite3_prepare(), not |
||
266 | # sqlite3_step(). |
||
267 | # |
||
268 | catchsql { SELECT * FROM sqlite_master; } |
||
269 | catchsql { SELECT * FROM sqlite_master; } |
||
270 | } {1 {unsupported file format}} |
||
271 | do_test alter2-4.3 { |
||
272 | sqlite3_errcode db |
||
273 | } {SQLITE_ERROR} |
||
274 | do_test alter2-4.4 { |
||
275 | set ::DB [sqlite3_connection_pointer db] |
||
276 | catchsql { |
||
277 | SELECT * FROM sqlite_master; |
||
278 | } |
||
279 | } {1 {unsupported file format}} |
||
280 | do_test alter2-4.5 { |
||
281 | sqlite3_errcode db |
||
282 | } {SQLITE_ERROR} |
||
283 | |||
284 | #--------------------------------------------------------------------- |
||
285 | # Check that executing VACUUM on a file with file-format version 2 |
||
286 | # resets the file format to 1. |
||
287 | # |
||
288 | set default_file_format [expr $SQLITE_DEFAULT_FILE_FORMAT==4 ? 4 : 1] |
||
289 | ifcapable vacuum { |
||
290 | do_test alter2-5.1 { |
||
291 | set_file_format 2 |
||
292 | db close |
||
293 | sqlite3 db test.db |
||
294 | execsql {SELECT 1 FROM sqlite_master LIMIT 1;} |
||
295 | get_file_format |
||
296 | } {2} |
||
297 | do_test alter2-5.2 { |
||
298 | execsql { VACUUM } |
||
299 | } {} |
||
300 | do_test alter2-5.3 { |
||
301 | get_file_format |
||
302 | } $default_file_format |
||
303 | } |
||
304 | |||
305 | #--------------------------------------------------------------------- |
||
306 | # Test that when a database with file-format 2 is opened, new |
||
307 | # databases are still created with file-format 1. |
||
308 | # |
||
309 | do_test alter2-6.1 { |
||
310 | db close |
||
311 | set_file_format 2 |
||
312 | sqlite3 db test.db |
||
313 | get_file_format |
||
314 | } {2} |
||
315 | ifcapable attach { |
||
316 | do_test alter2-6.2 { |
||
317 | file delete -force test2.db-journal |
||
318 | file delete -force test2.db |
||
319 | execsql { |
||
320 | ATTACH 'test2.db' AS aux; |
||
321 | CREATE TABLE aux.t1(a, b); |
||
322 | } |
||
323 | get_file_format test2.db |
||
324 | } $default_file_format |
||
325 | } |
||
326 | do_test alter2-6.3 { |
||
327 | execsql { |
||
328 | CREATE TABLE t1(a, b); |
||
329 | } |
||
330 | get_file_format |
||
331 | } {2} |
||
332 | |||
333 | #--------------------------------------------------------------------- |
||
334 | # Test that types and values for columns added with default values |
||
335 | # other than NULL work with SELECT statements. |
||
336 | # |
||
337 | do_test alter2-7.1 { |
||
338 | execsql { |
||
339 | DROP TABLE t1; |
||
340 | CREATE TABLE t1(a); |
||
341 | INSERT INTO t1 VALUES(1); |
||
342 | INSERT INTO t1 VALUES(2); |
||
343 | INSERT INTO t1 VALUES(3); |
||
344 | INSERT INTO t1 VALUES(4); |
||
345 | SELECT * FROM t1; |
||
346 | } |
||
347 | } {1 2 3 4} |
||
348 | do_test alter2-7.2 { |
||
349 | set sql {CREATE TABLE t1(a, b DEFAULT '123', c INTEGER DEFAULT '123')} |
||
350 | alter_table t1 $sql 3 |
||
351 | execsql { |
||
352 | SELECT * FROM t1 LIMIT 1; |
||
353 | } |
||
354 | } {1 123 123} |
||
355 | do_test alter2-7.3 { |
||
356 | execsql { |
||
357 | SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; |
||
358 | } |
||
359 | } {1 integer 123 text 123 integer} |
||
360 | do_test alter2-7.4 { |
||
361 | execsql { |
||
362 | SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; |
||
363 | } |
||
364 | } {1 integer 123 text 123 integer} |
||
365 | do_test alter2-7.5 { |
||
366 | set sql {CREATE TABLE t1(a, b DEFAULT -123.0, c VARCHAR(10) default 5)} |
||
367 | alter_table t1 $sql 3 |
||
368 | execsql { |
||
369 | SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; |
||
370 | } |
||
371 | } {1 integer -123 integer 5 text} |
||
372 | |||
373 | #----------------------------------------------------------------------- |
||
374 | # Test that UPDATE trigger tables work with default values, and that when |
||
375 | # a row is updated the default values are correctly transfered to the |
||
376 | # new row. |
||
377 | # |
||
378 | ifcapable trigger { |
||
379 | db function set_val {set ::val} |
||
380 | do_test alter2-8.1 { |
||
381 | execsql { |
||
382 | CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN |
||
383 | SELECT set_val( |
||
384 | old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c)||' '|| |
||
385 | new.b||' '||typeof(new.b)||' '||new.c||' '||typeof(new.c) |
||
386 | ); |
||
387 | END; |
||
388 | } |
||
389 | list |
||
390 | } {} |
||
391 | } |
||
392 | do_test alter2-8.2 { |
||
393 | execsql { |
||
394 | UPDATE t1 SET c = 10 WHERE a = 1; |
||
395 | SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1 LIMIT 1; |
||
396 | } |
||
397 | } {1 integer -123 integer 10 text} |
||
398 | ifcapable trigger { |
||
399 | do_test alter2-8.3 { |
||
400 | set ::val |
||
401 | } {-123 integer 5 text -123 integer 10 text} |
||
402 | } |
||
403 | |||
404 | #----------------------------------------------------------------------- |
||
405 | # Test that DELETE trigger tables work with default values, and that when |
||
406 | # a row is updated the default values are correctly transfered to the |
||
407 | # new row. |
||
408 | # |
||
409 | ifcapable trigger { |
||
410 | do_test alter2-9.1 { |
||
411 | execsql { |
||
412 | CREATE TRIGGER trig2 BEFORE DELETE ON t1 BEGIN |
||
413 | SELECT set_val( |
||
414 | old.b||' '||typeof(old.b)||' '||old.c||' '||typeof(old.c) |
||
415 | ); |
||
416 | END; |
||
417 | } |
||
418 | list |
||
419 | } {} |
||
420 | do_test alter2-9.2 { |
||
421 | execsql { |
||
422 | DELETE FROM t1 WHERE a = 2; |
||
423 | } |
||
424 | set ::val |
||
425 | } {-123 integer 5 text} |
||
426 | } |
||
427 | |||
428 | #----------------------------------------------------------------------- |
||
429 | # Test creating an index on a column added with a default value. |
||
430 | # |
||
431 | ifcapable bloblit { |
||
432 | do_test alter2-10.1 { |
||
433 | execsql { |
||
434 | CREATE TABLE t2(a); |
||
435 | INSERT INTO t2 VALUES('a'); |
||
436 | INSERT INTO t2 VALUES('b'); |
||
437 | INSERT INTO t2 VALUES('c'); |
||
438 | INSERT INTO t2 VALUES('d'); |
||
439 | } |
||
440 | alter_table t2 {CREATE TABLE t2(a, b DEFAULT X'ABCD', c DEFAULT NULL);} 3 |
||
441 | catchsql { |
||
442 | SELECT * FROM sqlite_master; |
||
443 | } |
||
444 | execsql { |
||
445 | SELECT quote(a), quote(b), quote(c) FROM t2 LIMIT 1; |
||
446 | } |
||
447 | } {'a' X'ABCD' NULL} |
||
448 | do_test alter2-10.2 { |
||
449 | execsql { |
||
450 | CREATE INDEX i1 ON t2(b); |
||
451 | SELECT a FROM t2 WHERE b = X'ABCD'; |
||
452 | } |
||
453 | } {a b c d} |
||
454 | do_test alter2-10.3 { |
||
455 | execsql { |
||
456 | DELETE FROM t2 WHERE a = 'c'; |
||
457 | SELECT a FROM t2 WHERE b = X'ABCD'; |
||
458 | } |
||
459 | } {a b d} |
||
460 | do_test alter2-10.4 { |
||
461 | execsql { |
||
462 | SELECT count(b) FROM t2 WHERE b = X'ABCD'; |
||
463 | } |
||
464 | } {3} |
||
465 | } |
||
466 | |||
467 | finish_test |