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