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