Debian Codegraph @

test/alter4.test

raw
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 2009/02/02 18:03:22 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
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.1b {
49 execsql {
50 SELECT sql FROM temp.sqlite_master;
51 }
52 } {{CREATE TABLE abc(a, b, c)}}
53 do_test alter4-1.2 {
54 execsql {ALTER TABLE abc ADD d INTEGER;}
55 execsql {
56 SELECT sql FROM sqlite_temp_master;
57 }
58 } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
59 do_test alter4-1.2b {
60 execsql {
61 SELECT sql FROM temp.sqlite_master;
62 }
63 } {{CREATE TABLE abc(a, b, c, d INTEGER)}}
64 do_test alter4-1.3 {
65 execsql {ALTER TABLE abc ADD e}
66 execsql {
67 SELECT sql FROM sqlite_temp_master;
68 }
69 } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
70 do_test alter4-1.3b {
71 execsql {
72 SELECT sql FROM temp.sqlite_master;
73 }
74 } {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
75 do_test alter4-1.4 {
76 execsql {
77 CREATE TABLE temp.t1(a, b);
78 ALTER TABLE t1 ADD c;
79 SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
80 }
81 } {{CREATE TABLE t1(a, b, c)}}
82 do_test alter4-1.4b {
83 execsql {
84 SELECT sql FROM temp.sqlite_master WHERE tbl_name = 't1';
85 }
86 } {{CREATE TABLE t1(a, b, c)}}
87 do_test alter4-1.5 {
88 execsql {
89 ALTER TABLE t1 ADD d CHECK (a>d);
90 SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
91 }
92 } {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
93 ifcapable foreignkey {
94 do_test alter4-1.6 {
95 execsql {
96 CREATE TEMP TABLE t2(a, b, UNIQUE(a, b));
97 ALTER TABLE t2 ADD c REFERENCES t1(c) ;
98 SELECT sql FROM sqlite_temp_master
99 WHERE tbl_name = 't2' AND type = 'table';
100 }
101 } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
102 }
103 do_test alter4-1.7 {
104 execsql {
105 CREATE TEMPORARY TABLE t3(a, b, UNIQUE(a, b));
106 ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
107 SELECT sql FROM sqlite_temp_master
108 WHERE tbl_name = 't3' AND type = 'table';
109 }
110 } {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
111 do_test alter4-1.99 {
112 catchsql {
113 # May not exist if foreign-keys are omitted at compile time.
114 DROP TABLE t2;
115 }
116 execsql {
117 DROP TABLE abc;
118 DROP TABLE t1;
119 DROP TABLE t3;
120 }
121 } {}
122
123 do_test alter4-2.1 {
124 execsql {
125 CREATE TABLE temp.t1(a, b);
126 INSERT INTO t1 VALUES(1,2);
127 }
128 catchsql {
129 ALTER TABLE t1 ADD c PRIMARY KEY;
130 }
131 } {1 {Cannot add a PRIMARY KEY column}}
132 do_test alter4-2.2 {
133 catchsql {
134 ALTER TABLE t1 ADD c UNIQUE
135 }
136 } {1 {Cannot add a UNIQUE column}}
137 do_test alter4-2.3 {
138 catchsql {
139 ALTER TABLE t1 ADD b VARCHAR(10)
140 }
141 } {1 {duplicate column name: b}}
142 do_test alter4-2.3 {
143 catchsql {
144 ALTER TABLE t1 ADD c NOT NULL;
145 }
146 } {1 {Cannot add a NOT NULL column with default value NULL}}
147 do_test alter4-2.4 {
148 catchsql {
149 ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
150 }
151 } {0 {}}
152 ifcapable view {
153 do_test alter4-2.5 {
154 execsql {
155 CREATE TEMPORARY VIEW v1 AS SELECT * FROM t1;
156 }
157 catchsql {
158 alter table v1 add column d;
159 }
160 } {1 {Cannot add a column to a view}}
161 }
162 do_test alter4-2.6 {
163 catchsql {
164 alter table t1 add column d DEFAULT CURRENT_TIME;
165 }
166 } {1 {Cannot add a column with non-constant default}}
167 do_test alter4-2.7 {
168 catchsql {
169 alter table t1 add column d default (-5+1);
170 }
171 } {1 {Cannot add a column with non-constant default}}
172 do_test alter4-2.99 {
173 execsql {
174 DROP TABLE t1;
175 }
176 } {}
177
178 do_test alter4-3.1 {
179 execsql {
180 CREATE TEMP TABLE t1(a, b);
181 INSERT INTO t1 VALUES(1, 100);
182 INSERT INTO t1 VALUES(2, 300);
183 SELECT * FROM t1;
184 }
185 } {1 100 2 300}
186 do_test alter4-3.1 {
187 execsql {
188 PRAGMA schema_version = 10;
189 }
190 } {}
191 do_test alter4-3.2 {
192 execsql {
193 ALTER TABLE t1 ADD c;
194 SELECT * FROM t1;
195 }
196 } {1 100 {} 2 300 {}}
197 ifcapable schema_version {
198 do_test alter4-3.4 {
199 execsql {
200 PRAGMA schema_version;
201 }
202 } {10}
203 }
204
205 do_test alter4-4.1 {
206 db close
207 forcedelete test.db
208 set ::DB [sqlite3 db test.db]
209 execsql {
210 CREATE TEMP TABLE t1(a, b);
211 INSERT INTO t1 VALUES(1, 100);
212 INSERT INTO t1 VALUES(2, 300);
213 SELECT * FROM t1;
214 }
215 } {1 100 2 300}
216 do_test alter4-4.1 {
217 execsql {
218 PRAGMA schema_version = 20;
219 }
220 } {}
221 do_test alter4-4.2 {
222 execsql {
223 ALTER TABLE t1 ADD c DEFAULT 'hello world';
224 SELECT * FROM t1;
225 }
226 } {1 100 {hello world} 2 300 {hello world}}
227 ifcapable schema_version {
228 do_test alter4-4.4 {
229 execsql {
230 PRAGMA schema_version;
231 }
232 } {20}
233 }
234 do_test alter4-4.99 {
235 execsql {
236 DROP TABLE t1;
237 }
238 } {}
239
240 ifcapable attach {
241 do_test alter4-5.1 {
242 forcedelete test2.db
243 forcedelete test2.db-journal
244 execsql {
245 CREATE TEMP TABLE t1(a, b);
246 INSERT INTO t1 VALUES(1, 'one');
247 INSERT INTO t1 VALUES(2, 'two');
248 ATTACH 'test2.db' AS aux;
249 CREATE TABLE aux.t1 AS SELECT * FROM t1;
250 PRAGMA aux.schema_version = 30;
251 SELECT sql FROM aux.sqlite_master;
252 }
253 } {{CREATE TABLE t1(a,b)}}
254 do_test alter4-5.2 {
255 execsql {
256 ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
257 SELECT sql FROM aux.sqlite_master;
258 }
259 } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
260 do_test alter4-5.3 {
261 execsql {
262 SELECT * FROM aux.t1;
263 }
264 } {1 one {} 2 two {}}
265 ifcapable schema_version {
266 do_test alter4-5.4 {
267 execsql {
268 PRAGMA aux.schema_version;
269 }
270 } {31}
271 }
272 do_test alter4-5.6 {
273 execsql {
274 ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
275 SELECT sql FROM aux.sqlite_master;
276 }
277 } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
278 do_test alter4-5.7 {
279 execsql {
280 SELECT * FROM aux.t1;
281 }
282 } {1 one {} 1000 2 two {} 1000}
283 ifcapable schema_version {
284 do_test alter4-5.8 {
285 execsql {
286 PRAGMA aux.schema_version;
287 }
288 } {32}
289 }
290 do_test alter4-5.9 {
291 execsql {
292 SELECT * FROM t1;
293 }
294 } {1 one 2 two}
295 do_test alter4-5.99 {
296 execsql {
297 DROP TABLE aux.t1;
298 DROP TABLE t1;
299 }
300 } {}
301 }
302
303 #----------------------------------------------------------------
304 # Test that the table schema is correctly reloaded when a column
305 # is added to a table.
306 #
307 ifcapable trigger&&tempdb {
308 do_test alter4-6.1 {
309 execsql {
310 CREATE TEMP TABLE t1(a, b);
311 CREATE TEMP TABLE log(trig, a, b);
312
313 CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
314 INSERT INTO log VALUES('a', new.a, new.b);
315 END;
316 CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
317 INSERT INTO log VALUES('b', new.a, new.b);
318 END;
319
320 INSERT INTO t1 VALUES(1, 2);
321 SELECT * FROM log ORDER BY trig, a, b;
322 }
323 } {a 1 2 b 1 2}
324 do_test alter4-6.2 {
325 execsql {
326 ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
327 INSERT INTO t1(a, b) VALUES(3, 4);
328 SELECT * FROM log ORDER BY trig, a, b;
329 }
330 } {a 1 2 a 3 4 b 1 2 b 3 4}
331 }
332
333 # Ticket #1183 - Make sure adding columns to large tables does not cause
334 # memory corruption (as was the case before this bug was fixed).
335 do_test alter4-8.1 {
336 execsql {
337 CREATE TEMP TABLE t4(c1);
338 }
339 } {}
340 set ::sql ""
341 do_test alter4-8.2 {
342 set cols c1
343 for {set i 2} {$i < 100} {incr i} {
344 execsql "
345 ALTER TABLE t4 ADD c$i
346 "
347 lappend cols c$i
348 }
349 set ::sql "CREATE TABLE t4([join $cols {, }])"
350 list
351 } {}
352 do_test alter4-8.2 {
353 execsql {
354 SELECT sql FROM sqlite_temp_master WHERE name = 't4';
355 }
356 } [list $::sql]
357
358
359 # Test that a default value equal to -1 multipied by the smallest possible
360 # 64-bit integer is correctly converted to a real.
361 do_execsql_test alter4-9.1 {
362 CREATE TABLE t5(
363 a INTEGER DEFAULT -9223372036854775808,
364 b INTEGER DEFAULT (-(-9223372036854775808))
365 );
366 INSERT INTO t5 DEFAULT VALUES;
367 }
368
369 do_execsql_test alter4-9.2 { SELECT typeof(a), a, typeof(b), b FROM t5; } {
370 integer -9223372036854775808
371 real 9.22337203685478e+18
372 }
373
374 do_execsql_test alter4-9.3 {
375 ALTER TABLE t5 ADD COLUMN c INTEGER DEFAULT (-(-9223372036854775808));
376 SELECT typeof(c), c FROM t5;
377 } {real 9.22337203685478e+18}
378
379 # Confirm that doing an ALTER TABLE on a legacy format database
380 # does not corrupt DESC indexes.
381 #
382 # Ticket https://sqlite.org/src/tktview/f68bf68513a1c
383 #
384 do_test alter4-10.1 {
385 db close
386 sqlite3 db :memory:
387 sqlite3_db_config db LEGACY_FILE_FORMAT 1
388 db eval {
389 CREATE TABLE t1(a,b,c);
390 CREATE INDEX t1a ON t1(a DESC);
391 INSERT INTO t1 VALUES(1,2,3);
392 INSERT INTO t1 VALUES(2,3,4);
393 ALTER TABLE t1 ADD COLUMN d;
394 PRAGMA integrity_check;
395 }
396 } {ok}
397
398 reset_db
399 do_execsql_test alter4-11.0 {
400 CREATE TABLE t1(c INTEGER PRIMARY KEY, d);
401 INSERT INTO t1(c,d) VALUES(1,2);
402 PRAGMA foreign_keys = on;
403 ALTER TABLE t1 ADD COLUMN e;
404 }
405
406 do_execsql_test alter4-11.1 {
407 ALTER TABLE t1 ADD COLUMN f REFERENCES t1;
408 }
409
410 do_catchsql_test alter4-11.2 {
411 ALTER TABLE t1 ADD COLUMN g REFERENCES t1 DEFAULT 4;
412 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
413
414 do_catchsql_test alter4-11.3 {
415 ALTER TABLE t2 ADD COLUMN g;
416 } {1 {no such table: t2}}
417
418 ifcapable fts5 {
419 do_execsql_test alter4-11.4 {
420 CREATE VIRTUAL TABLE fff USING fts5(f);
421 }
422 do_catchsql_test alter4-11.2 {
423 ALTER TABLE fff ADD COLUMN g;
424 } {1 {virtual tables may not be altered}}
425 }
426
427 finish_test