xref: /sqlite-3.40.0/test/alter3.test (revision 3bdca9c9) 
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.9 2006/01/17 09:35: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))}}
90ifcapable foreignkey {
91  do_test alter3-1.6 {
92    execsql {
93      CREATE TABLE t2(a, b, UNIQUE(a, b));
94      ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
95      SELECT sql FROM sqlite_master WHERE tbl_name = 't2' AND type = 'table';
96    }
97  } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
98}
99do_test alter3-1.7 {
100  execsql {
101    CREATE TABLE t3(a, b, UNIQUE(a, b));
102    ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
103    SELECT sql FROM sqlite_master WHERE tbl_name = 't3' AND type = 'table';
104  }
105} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
106do_test alter3-1.99 {
107  catchsql {
108    # May not exist if foriegn-keys are omitted at compile time.
109    DROP TABLE t2;
110  }
111  execsql {
112    DROP TABLE abc;
113    DROP TABLE t1;
114    DROP TABLE t3;
115  }
116} {}
117
118do_test alter3-2.1 {
119  execsql {
120    CREATE TABLE t1(a, b);
121  }
122  catchsql {
123    ALTER TABLE t1 ADD c PRIMARY KEY;
124  }
125} {1 {Cannot add a PRIMARY KEY column}}
126do_test alter3-2.2 {
127  catchsql {
128    ALTER TABLE t1 ADD c UNIQUE
129  }
130} {1 {Cannot add a UNIQUE column}}
131do_test alter3-2.3 {
132  catchsql {
133    ALTER TABLE t1 ADD b VARCHAR(10)
134  }
135} {1 {duplicate column name: b}}
136do_test alter3-2.3 {
137  catchsql {
138    ALTER TABLE t1 ADD c NOT NULL;
139  }
140} {1 {Cannot add a NOT NULL column with default value NULL}}
141do_test alter3-2.4 {
142  catchsql {
143    ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
144  }
145} {0 {}}
146ifcapable view {
147  do_test alter3-2.5 {
148    execsql {
149      CREATE VIEW v1 AS SELECT * FROM t1;
150    }
151    catchsql {
152      alter table v1 add column d;
153    }
154  } {1 {Cannot add a column to a view}}
155}
156do_test alter3-2.6 {
157  catchsql {
158    alter table t1 add column d DEFAULT CURRENT_TIME;
159  }
160} {1 {Cannot add a column with non-constant default}}
161do_test alter3-2.99 {
162  execsql {
163    DROP TABLE t1;
164  }
165} {}
166
167do_test alter3-3.1 {
168  execsql {
169    CREATE TABLE t1(a, b);
170    INSERT INTO t1 VALUES(1, 100);
171    INSERT INTO t1 VALUES(2, 300);
172    SELECT * FROM t1;
173  }
174} {1 100 2 300}
175do_test alter3-3.1 {
176  execsql {
177    PRAGMA schema_version = 10;
178  }
179} {}
180do_test alter3-3.2 {
181  execsql {
182    ALTER TABLE t1 ADD c;
183    SELECT * FROM t1;
184  }
185} {1 100 {} 2 300 {}}
186if {!$has_codec} {
187  do_test alter3-3.3 {
188    get_file_format
189  } {3}
190}
191ifcapable schema_version {
192  do_test alter3-3.4 {
193    execsql {
194      PRAGMA schema_version;
195    }
196  } {11}
197}
198
199do_test alter3-4.1 {
200  db close
201  file delete -force test.db
202  set ::DB [sqlite3 db test.db]
203  execsql {
204    CREATE TABLE t1(a, b);
205    INSERT INTO t1 VALUES(1, 100);
206    INSERT INTO t1 VALUES(2, 300);
207    SELECT * FROM t1;
208  }
209} {1 100 2 300}
210do_test alter3-4.1 {
211  execsql {
212    PRAGMA schema_version = 20;
213  }
214} {}
215do_test alter3-4.2 {
216  execsql {
217    ALTER TABLE t1 ADD c DEFAULT 'hello world';
218    SELECT * FROM t1;
219  }
220} {1 100 {hello world} 2 300 {hello world}}
221if {!$has_codec} {
222  do_test alter3-4.3 {
223    get_file_format
224  } {3}
225}
226ifcapable schema_version {
227  do_test alter3-4.4 {
228    execsql {
229      PRAGMA schema_version;
230    }
231  } {21}
232}
233do_test alter3-4.99 {
234  execsql {
235    DROP TABLE t1;
236  }
237} {}
238
239do_test alter3-5.1 {
240  file delete -force test2.db
241  file delete -force 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)}}
252do_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))}}
258do_test alter3-5.3 {
259  execsql {
260    SELECT * FROM aux.t1;
261  }
262} {1 one {} 2 two {}}
263ifcapable schema_version {
264  do_test alter3-5.4 {
265    execsql {
266      PRAGMA aux.schema_version;
267    }
268  } {31}
269}
270if {!$has_codec} {
271  do_test alter3-5.5 {
272    list [get_file_format test2.db] [get_file_format]
273  } {2 3}
274}
275do_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)}}
281do_test alter3-5.7 {
282  execsql {
283    SELECT * FROM aux.t1;
284  }
285} {1 one {} 1000 2 two {} 1000}
286ifcapable schema_version {
287  do_test alter3-5.8 {
288    execsql {
289      PRAGMA aux.schema_version;
290    }
291  } {32}
292}
293do_test alter3-5.9 {
294  execsql {
295    SELECT * FROM t1;
296  }
297} {1 one 2 two}
298do_test alter3-5.99 {
299  execsql {
300    DROP TABLE aux.t1;
301    DROP TABLE t1;
302  }
303} {}
304
305#----------------------------------------------------------------
306# Test that the table schema is correctly reloaded when a column
307# is added to a table.
308#
309ifcapable 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
335if {!$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    } {2}
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).
373do_test alter3-8.1 {
374  execsql {
375    CREATE TABLE t4(c1);
376  }
377} {}
378set ::sql ""
379do_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} {}
390do_test alter3-8.2 {
391  execsql {
392    SELECT sql FROM sqlite_master WHERE name = 't4';
393  }
394} [list $::sql]
395
396finish_test
397