Debian Codegraph @

test/alter3.test

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