| blob | 712960c0eaf9e3cffacc97995329fb1bb92e993d |
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 #
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # We have to have pragmas in order to do this test
23 ifcapable {!pragma} return
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
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 #
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 }
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 }
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 }
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}}
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 } {}
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 }
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}
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 }
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}
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 }
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}
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}
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 }
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 }
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 }
467 finish_test