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