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