1: # 2009 February 2
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: alter4.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
17: #
18:
19: set testdir [file dirname $argv0]
20:
21: source $testdir/tester.tcl
22:
23: # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
24: ifcapable !altertable {
25: finish_test
26: return
27: }
28:
29:
30: # Test Organisation:
31: # ------------------
32: #
33: # alter4-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
34: # alter4-2.*: Test error messages.
35: # alter4-3.*: Test adding columns with default value NULL.
36: # alter4-4.*: Test adding columns with default values other than NULL.
37: # alter4-5.*: Test adding columns to tables in ATTACHed databases.
38: # alter4-6.*: Test that temp triggers are not accidentally dropped.
39: # alter4-7.*: Test that VACUUM resets the file-format.
40: #
41:
42: do_test alter4-1.1 {
43: execsql {
44: CREATE TEMP TABLE abc(a, b, c);
45: SELECT sql FROM sqlite_temp_master;
46: }
47: } {{CREATE TABLE abc(a, b, c)}}
48: do_test alter4-1.2 {
49: execsql {ALTER TABLE abc ADD d INTEGER;}
50: execsql {
51: SELECT sql FROM sqlite_temp_master;
52: }
53: } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
54: do_test alter4-1.3 {
55: execsql {ALTER TABLE abc ADD e}
56: execsql {
57: SELECT sql FROM sqlite_temp_master;
58: }
59: } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
60: do_test alter4-1.4 {
61: execsql {
62: CREATE TABLE temp.t1(a, b);
63: ALTER TABLE t1 ADD c;
64: SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
65: }
66: } {{CREATE TABLE t1(a, b, c)}}
67: do_test alter4-1.5 {
68: execsql {
69: ALTER TABLE t1 ADD d CHECK (a>d);
70: SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
71: }
72: } {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
73: ifcapable foreignkey {
74: do_test alter4-1.6 {
75: execsql {
76: CREATE TEMP TABLE t2(a, b, UNIQUE(a, b));
77: ALTER TABLE t2 ADD c REFERENCES t1(c) ;
78: SELECT sql FROM sqlite_temp_master
79: WHERE tbl_name = 't2' AND type = 'table';
80: }
81: } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
82: }
83: do_test alter4-1.7 {
84: execsql {
85: CREATE TEMPORARY TABLE t3(a, b, UNIQUE(a, b));
86: ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
87: SELECT sql FROM sqlite_temp_master
88: WHERE tbl_name = 't3' AND type = 'table';
89: }
90: } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
91: do_test alter4-1.99 {
92: catchsql {
93: # May not exist if foriegn-keys are omitted at compile time.
94: DROP TABLE t2;
95: }
96: execsql {
97: DROP TABLE abc;
98: DROP TABLE t1;
99: DROP TABLE t3;
100: }
101: } {}
102:
103: do_test alter4-2.1 {
104: execsql {
105: CREATE TABLE temp.t1(a, b);
106: }
107: catchsql {
108: ALTER TABLE t1 ADD c PRIMARY KEY;
109: }
110: } {1 {Cannot add a PRIMARY KEY column}}
111: do_test alter4-2.2 {
112: catchsql {
113: ALTER TABLE t1 ADD c UNIQUE
114: }
115: } {1 {Cannot add a UNIQUE column}}
116: do_test alter4-2.3 {
117: catchsql {
118: ALTER TABLE t1 ADD b VARCHAR(10)
119: }
120: } {1 {duplicate column name: b}}
121: do_test alter4-2.3 {
122: catchsql {
123: ALTER TABLE t1 ADD c NOT NULL;
124: }
125: } {1 {Cannot add a NOT NULL column with default value NULL}}
126: do_test alter4-2.4 {
127: catchsql {
128: ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
129: }
130: } {0 {}}
131: ifcapable view {
132: do_test alter4-2.5 {
133: execsql {
134: CREATE TEMPORARY VIEW v1 AS SELECT * FROM t1;
135: }
136: catchsql {
137: alter table v1 add column d;
138: }
139: } {1 {Cannot add a column to a view}}
140: }
141: do_test alter4-2.6 {
142: catchsql {
143: alter table t1 add column d DEFAULT CURRENT_TIME;
144: }
145: } {1 {Cannot add a column with non-constant default}}
146: do_test alter4-2.99 {
147: execsql {
148: DROP TABLE t1;
149: }
150: } {}
151:
152: do_test alter4-3.1 {
153: execsql {
154: CREATE TEMP TABLE t1(a, b);
155: INSERT INTO t1 VALUES(1, 100);
156: INSERT INTO t1 VALUES(2, 300);
157: SELECT * FROM t1;
158: }
159: } {1 100 2 300}
160: do_test alter4-3.1 {
161: execsql {
162: PRAGMA schema_version = 10;
163: }
164: } {}
165: do_test alter4-3.2 {
166: execsql {
167: ALTER TABLE t1 ADD c;
168: SELECT * FROM t1;
169: }
170: } {1 100 {} 2 300 {}}
171: ifcapable schema_version {
172: do_test alter4-3.4 {
173: execsql {
174: PRAGMA schema_version;
175: }
176: } {10}
177: }
178:
179: do_test alter4-4.1 {
180: db close
181: forcedelete test.db
182: set ::DB [sqlite3 db test.db]
183: execsql {
184: CREATE TEMP TABLE t1(a, b);
185: INSERT INTO t1 VALUES(1, 100);
186: INSERT INTO t1 VALUES(2, 300);
187: SELECT * FROM t1;
188: }
189: } {1 100 2 300}
190: do_test alter4-4.1 {
191: execsql {
192: PRAGMA schema_version = 20;
193: }
194: } {}
195: do_test alter4-4.2 {
196: execsql {
197: ALTER TABLE t1 ADD c DEFAULT 'hello world';
198: SELECT * FROM t1;
199: }
200: } {1 100 {hello world} 2 300 {hello world}}
201: ifcapable schema_version {
202: do_test alter4-4.4 {
203: execsql {
204: PRAGMA schema_version;
205: }
206: } {20}
207: }
208: do_test alter4-4.99 {
209: execsql {
210: DROP TABLE t1;
211: }
212: } {}
213:
214: ifcapable attach {
215: do_test alter4-5.1 {
216: forcedelete test2.db
217: forcedelete test2.db-journal
218: execsql {
219: CREATE TEMP TABLE t1(a, b);
220: INSERT INTO t1 VALUES(1, 'one');
221: INSERT INTO t1 VALUES(2, 'two');
222: ATTACH 'test2.db' AS aux;
223: CREATE TABLE aux.t1 AS SELECT * FROM t1;
224: PRAGMA aux.schema_version = 30;
225: SELECT sql FROM aux.sqlite_master;
226: }
227: } {{CREATE TABLE t1(a,b)}}
228: do_test alter4-5.2 {
229: execsql {
230: ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
231: SELECT sql FROM aux.sqlite_master;
232: }
233: } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
234: do_test alter4-5.3 {
235: execsql {
236: SELECT * FROM aux.t1;
237: }
238: } {1 one {} 2 two {}}
239: ifcapable schema_version {
240: do_test alter4-5.4 {
241: execsql {
242: PRAGMA aux.schema_version;
243: }
244: } {31}
245: }
246: do_test alter4-5.6 {
247: execsql {
248: ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
249: SELECT sql FROM aux.sqlite_master;
250: }
251: } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
252: do_test alter4-5.7 {
253: execsql {
254: SELECT * FROM aux.t1;
255: }
256: } {1 one {} 1000 2 two {} 1000}
257: ifcapable schema_version {
258: do_test alter4-5.8 {
259: execsql {
260: PRAGMA aux.schema_version;
261: }
262: } {32}
263: }
264: do_test alter4-5.9 {
265: execsql {
266: SELECT * FROM t1;
267: }
268: } {1 one 2 two}
269: do_test alter4-5.99 {
270: execsql {
271: DROP TABLE aux.t1;
272: DROP TABLE t1;
273: }
274: } {}
275: }
276:
277: #----------------------------------------------------------------
278: # Test that the table schema is correctly reloaded when a column
279: # is added to a table.
280: #
281: ifcapable trigger&&tempdb {
282: do_test alter4-6.1 {
283: execsql {
284: CREATE TEMP TABLE t1(a, b);
285: CREATE TEMP TABLE log(trig, a, b);
286:
287: CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
288: INSERT INTO log VALUES('a', new.a, new.b);
289: END;
290: CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
291: INSERT INTO log VALUES('b', new.a, new.b);
292: END;
293:
294: INSERT INTO t1 VALUES(1, 2);
295: SELECT * FROM log;
296: }
297: } {b 1 2 a 1 2}
298: do_test alter4-6.2 {
299: execsql {
300: ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
301: INSERT INTO t1(a, b) VALUES(3, 4);
302: SELECT * FROM log;
303: }
304: } {b 1 2 a 1 2 b 3 4 a 3 4}
305: }
306:
307: # Ticket #1183 - Make sure adding columns to large tables does not cause
308: # memory corruption (as was the case before this bug was fixed).
309: do_test alter4-8.1 {
310: execsql {
311: CREATE TEMP TABLE t4(c1);
312: }
313: } {}
314: set ::sql ""
315: do_test alter4-8.2 {
316: set cols c1
317: for {set i 2} {$i < 100} {incr i} {
318: execsql "
319: ALTER TABLE t4 ADD c$i
320: "
321: lappend cols c$i
322: }
323: set ::sql "CREATE TABLE t4([join $cols {, }])"
324: list
325: } {}
326: do_test alter4-8.2 {
327: execsql {
328: SELECT sql FROM sqlite_temp_master WHERE name = 't4';
329: }
330: } [list $::sql]
331:
332: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to alter4.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