| blob | 28d293e24bca55bc4c81b5c5308f99e337981261 |
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 #
19 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
23 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
24 ifcapable !altertable {
25 finish_test
26 return
27 }
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 }
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 #
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 }
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 } {}
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 } {}
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 }
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 } {}
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 }
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);
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;
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 }
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 }
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]
397 finish_test