xref: /sqlite-3.40.0/test/alter3.test (revision aef0bf64) 
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.7 2005/12/30 16:28:02 danielk1977 Exp $
17#
18
19set testdir [file dirname $argv0]
20
21source $testdir/tester.tcl
22
23# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
24ifcapable !altertable {
25  finish_test
26  return
27}
28
29# Determine if there is a codec available on this test.
30#
31if {[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#
52proc get_file_format {{fname test.db}} {
53  set bt [btree_open $fname 10 0]
54  set meta [btree_get_meta $bt]
55  btree_close $bt
56  lindex $meta 2
57}
58
59do_test alter3-1.1 {
60  execsql {
61    CREATE TABLE abc(a, b, c);
62    SELECT sql FROM sqlite_master;
63  }
64} {{CREATE TABLE abc(a, b, c)}}
65do_test alter3-1.2 {
66  execsql {ALTER TABLE abc ADD d INTEGER;}
67  execsql {
68    SELECT sql FROM sqlite_master;
69  }
70} {{CREATE TABLE abc(a, b, c, d INTEGER)}}
71do_test alter3-1.3 {
72  execsql {ALTER TABLE abc ADD e}
73  execsql {
74    SELECT sql FROM sqlite_master;
75  }
76} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
77do_test alter3-1.4 {
78  execsql {
79    CREATE TABLE main.t1(a, b);
80    ALTER TABLE t1 ADD c;
81    SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
82  }
83} {{CREATE TABLE t1(a, b, c)}}
84do_test alter3-1.5 {
85  execsql {
86    ALTER TABLE t1 ADD d CHECK (a>d);
87    SELECT sql FROM sqlite_master WHERE tbl_name = 't1';
88  }
89} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
90do_test alter3-1.6 {
91  execsql {
92    CREATE TABLE t2(a, b, UNIQUE(a, b));
93    ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
94    SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';
95  }
96} {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
97do_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))}}
104do_test alter3-1.99 {
105  execsql {
106    DROP TABLE abc;
107    DROP TABLE t1;
108    DROP TABLE t2;
109    DROP TABLE t3;
110  }
111} {}
112
113do_test alter3-2.1 {
114  execsql {
115    CREATE TABLE t1(a, b);
116  }
117  catchsql {
118    ALTER TABLE t1 ADD c PRIMARY KEY;
119  }
120} {1 {Cannot add a PRIMARY KEY column}}
121do_test alter3-2.2 {
122  catchsql {
123    ALTER TABLE t1 ADD c UNIQUE
124  }
125} {1 {Cannot add a UNIQUE column}}
126do_test alter3-2.3 {
127  catchsql {
128    ALTER TABLE t1 ADD b VARCHAR(10)
129  }
130} {1 {duplicate column name: b}}
131do_test alter3-2.3 {
132  catchsql {
133    ALTER TABLE t1 ADD c NOT NULL;
134  }
135} {1 {Cannot add a NOT NULL column with default value NULL}}
136do_test alter3-2.4 {
137  catchsql {
138    ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
139  }
140} {0 {}}
141do_test alter3-2.5 {
142  execsql {
143    CREATE VIEW v1 AS SELECT * FROM t1;
144  }
145  catchsql {
146    alter table v1 add column d;
147  }
148} {1 {Cannot add a column to a view}}
149do_test alter3-2.6 {
150  catchsql {
151    alter table t1 add column d DEFAULT CURRENT_TIME;
152  }
153} {1 {Cannot add a column with non-constant default}}
154do_test alter3-2.99 {
155  execsql {
156    DROP TABLE t1;
157  }
158} {}
159
160do_test alter3-3.1 {
161  execsql {
162    CREATE TABLE t1(a, b);
163    INSERT INTO t1 VALUES(1, 100);
164    INSERT INTO t1 VALUES(2, 300);
165    SELECT * FROM t1;
166  }
167} {1 100 2 300}
168do_test alter3-3.1 {
169  execsql {
170    PRAGMA schema_version = 10;
171  }
172} {}
173do_test alter3-3.2 {
174  execsql {
175    ALTER TABLE t1 ADD c;
176    SELECT * FROM t1;
177  }
178} {1 100 {} 2 300 {}}
179if {!$has_codec} {
180  do_test alter3-3.3 {
181    get_file_format
182  } {3}
183}
184do_test alter3-3.4 {
185  execsql {
186    PRAGMA schema_version;
187  }
188} {11}
189
190do_test alter3-4.1 {
191  db close
192  file delete -force test.db
193  set ::DB [sqlite3 db test.db]
194  execsql {
195    CREATE TABLE t1(a, b);
196    INSERT INTO t1 VALUES(1, 100);
197    INSERT INTO t1 VALUES(2, 300);
198    SELECT * FROM t1;
199  }
200} {1 100 2 300}
201do_test alter3-4.1 {
202  execsql {
203    PRAGMA schema_version = 20;
204  }
205} {}
206do_test alter3-4.2 {
207  execsql {
208    ALTER TABLE t1 ADD c DEFAULT 'hello world';
209    SELECT * FROM t1;
210  }
211} {1 100 {hello world} 2 300 {hello world}}
212if {!$has_codec} {
213  do_test alter3-4.3 {
214    get_file_format
215  } {3}
216}
217do_test alter3-4.4 {
218  execsql {
219    PRAGMA schema_version;
220  }
221} {21}
222do_test alter3-4.99 {
223  execsql {
224    DROP TABLE t1;
225  }
226} {}
227
228do_test alter3-5.1 {
229  file delete -force test2.db
230  file delete -force test2.db-journal
231  execsql {
232    CREATE TABLE t1(a, b);
233    INSERT INTO t1 VALUES(1, 'one');
234    INSERT INTO t1 VALUES(2, 'two');
235    ATTACH 'test2.db' AS aux;
236    CREATE TABLE aux.t1 AS SELECT * FROM t1;
237    PRAGMA aux.schema_version = 30;
238    SELECT sql FROM aux.sqlite_master;
239  }
240} {{CREATE TABLE t1(a,b)}}
241do_test alter3-5.2 {
242  execsql {
243    ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
244    SELECT sql FROM aux.sqlite_master;
245  }
246} {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
247do_test alter3-5.3 {
248  execsql {
249    SELECT * FROM aux.t1;
250  }
251} {1 one {} 2 two {}}
252do_test alter3-5.4 {
253  execsql {
254    PRAGMA aux.schema_version;
255  }
256} {31}
257if {!$has_codec} {
258  do_test alter3-5.5 {
259    list [get_file_format test2.db] [get_file_format]
260  } {2 3}
261}
262do_test alter3-5.6 {
263  execsql {
264    ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
265    SELECT sql FROM aux.sqlite_master;
266  }
267} {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
268do_test alter3-5.7 {
269  execsql {
270    SELECT * FROM aux.t1;
271  }
272} {1 one {} 1000 2 two {} 1000}
273do_test alter3-5.8 {
274  execsql {
275    PRAGMA aux.schema_version;
276  }
277} {32}
278do_test alter3-5.9 {
279  execsql {
280    SELECT * FROM t1;
281  }
282} {1 one 2 two}
283do_test alter3-5.99 {
284  execsql {
285    DROP TABLE aux.t1;
286    DROP TABLE t1;
287  }
288} {}
289
290#----------------------------------------------------------------
291# Test that the table schema is correctly reloaded when a column
292# is added to a table.
293#
294ifcapable trigger&&tempdb {
295  do_test alter3-6.1 {
296    execsql {
297      CREATE TABLE t1(a, b);
298      CREATE TABLE log(trig, a, b);
299
300      CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
301        INSERT INTO log VALUES('a', new.a, new.b);
302      END;
303      CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
304        INSERT INTO log VALUES('b', new.a, new.b);
305      END;
306
307      INSERT INTO t1 VALUES(1, 2);
308      SELECT * FROM log;
309    }
310  } {b 1 2 a 1 2}
311  do_test alter3-6.2 {
312    execsql {
313      ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
314      INSERT INTO t1(a, b) VALUES(3, 4);
315      SELECT * FROM log;
316    }
317  } {b 1 2 a 1 2 b 3 4 a 3 4}
318}
319
320if {!$has_codec} {
321  ifcapable vacuum {
322    do_test alter3-7.1 {
323      execsql {
324        VACUUM;
325      }
326      get_file_format
327    } {1}
328    do_test alter3-7.2 {
329      execsql {
330        CREATE TABLE abc(a, b, c);
331        ALTER TABLE abc ADD d DEFAULT NULL;
332      }
333      get_file_format
334    } {2}
335    do_test alter3-7.3 {
336      execsql {
337        ALTER TABLE abc ADD e DEFAULT 10;
338      }
339      get_file_format
340    } {3}
341    do_test alter3-7.4 {
342      execsql {
343        ALTER TABLE abc ADD f DEFAULT NULL;
344      }
345      get_file_format
346    } {3}
347    do_test alter3-7.5 {
348      execsql {
349        VACUUM;
350      }
351      get_file_format
352    } {1}
353  }
354}
355
356# Ticket #1183 - Make sure adding columns to large tables does not cause
357# memory corruption (as was the case before this bug was fixed).
358do_test alter3-8.1 {
359  execsql {
360    CREATE TABLE t4(c1);
361  }
362} {}
363set ::sql ""
364do_test alter3-8.2 {
365  set cols c1
366  for {set i 2} {$i < 100} {incr i} {
367    execsql "
368      ALTER TABLE t4 ADD c$i
369    "
370    lappend cols c$i
371  }
372  set ::sql "CREATE TABLE t4([join $cols {, }])"
373  list
374} {}
375do_test alter3-8.2 {
376  execsql {
377    SELECT sql FROM sqlite_master WHERE name = 't4';
378  }
379} [list $::sql]
380
381finish_test
382