Debian Codegraph @

test/alter2.test

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