xref: /sqlite-3.40.0/test/alter3.test (revision a8e41eca) 
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  sqlite3_db_config db LEGACY_FILE_FORMAT 1
58  execsql {
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    INSERT INTO t1 VALUES(1,2);
120  }
121  catchsql {
122    ALTER TABLE t1 ADD c PRIMARY KEY;
123  }
124} {1 {Cannot add a PRIMARY KEY column}}
125do_test alter3-2.2 {
126  catchsql {
127    ALTER TABLE t1 ADD c UNIQUE
128  }
129} {1 {Cannot add a UNIQUE column}}
130do_test alter3-2.3 {
131  catchsql {
132    ALTER TABLE t1 ADD b VARCHAR(10)
133  }
134} {1 {duplicate column name: b}}
135do_test alter3-2.3 {
136  catchsql {
137    ALTER TABLE t1 ADD c NOT NULL;
138  }
139} {1 {Cannot add a NOT NULL column with default value NULL}}
140do_test alter3-2.4 {
141  catchsql {
142    ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
143  }
144} {0 {}}
145ifcapable view {
146  do_test alter3-2.5 {
147    execsql {
148      CREATE VIEW v1 AS SELECT * FROM t1;
149    }
150    catchsql {
151      alter table v1 add column d;
152    }
153  } {1 {Cannot add a column to a view}}
154}
155do_test alter3-2.6 {
156  catchsql {
157    alter table t1 add column d DEFAULT CURRENT_TIME;
158  }
159} {1 {Cannot add a column with non-constant default}}
160do_test alter3-2.99 {
161  execsql {
162    DROP TABLE t1;
163  }
164} {}
165
166do_test alter3-3.1 {
167  execsql {
168    CREATE TABLE t1(a, b);
169    INSERT INTO t1 VALUES(1, 100);
170    INSERT INTO t1 VALUES(2, 300);
171    SELECT * FROM t1;
172  }
173} {1 100 2 300}
174do_test alter3-3.1 {
175  execsql {
176    PRAGMA schema_version = 10;
177  }
178} {}
179do_test alter3-3.2 {
180  execsql {
181    ALTER TABLE t1 ADD c;
182    SELECT * FROM t1;
183  }
184} {1 100 {} 2 300 {}}
185if {!$has_codec} {
186  do_test alter3-3.3 {
187    get_file_format
188  } {3}
189}
190ifcapable schema_version {
191  do_test alter3-3.4 {
192    execsql {
193      PRAGMA schema_version;
194    }
195  } {11}
196}
197
198do_test alter3-4.1 {
199  db close
200  forcedelete test.db
201  set ::DB [sqlite3 db test.db]
202  sqlite3_db_config db LEGACY_FILE_FORMAT 1
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
239ifcapable attach {
240  do_test alter3-5.1 {
241    forcedelete test2.db
242    forcedelete test2.db-journal
243    execsql {
244      CREATE TABLE t1(a, b);
245      INSERT INTO t1 VALUES(1, 'one');
246      INSERT INTO t1 VALUES(2, 'two');
247      ATTACH 'test2.db' AS aux;
248      CREATE TABLE aux.t1 AS SELECT * FROM t1;
249      PRAGMA aux.schema_version = 30;
250      SELECT sql FROM aux.sqlite_master;
251    }
252  } {{CREATE TABLE t1(a,b)}}
253  do_test alter3-5.2 {
254    execsql {
255      ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
256      SELECT sql FROM aux.sqlite_master;
257    }
258  } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
259  do_test alter3-5.3 {
260    execsql {
261      SELECT * FROM aux.t1;
262    }
263  } {1 one {} 2 two {}}
264  ifcapable schema_version {
265    do_test alter3-5.4 {
266      execsql {
267        PRAGMA aux.schema_version;
268      }
269    } {31}
270  }
271  if {!$has_codec} {
272    do_test alter3-5.5 {
273      list [get_file_format test2.db] [get_file_format]
274    } {3 3}
275  }
276  do_test alter3-5.6 {
277    execsql {
278      ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
279      SELECT sql FROM aux.sqlite_master;
280    }
281  } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
282  do_test alter3-5.7 {
283    execsql {
284      SELECT * FROM aux.t1;
285    }
286  } {1 one {} 1000 2 two {} 1000}
287  ifcapable schema_version {
288    do_test alter3-5.8 {
289      execsql {
290        PRAGMA aux.schema_version;
291      }
292    } {32}
293  }
294  do_test alter3-5.9 {
295    execsql {
296      SELECT * FROM t1;
297    }
298  } {1 one 2 two}
299  do_test alter3-5.99 {
300    execsql {
301      DROP TABLE aux.t1;
302      DROP TABLE t1;
303    }
304  } {}
305}
306
307#----------------------------------------------------------------
308# Test that the table schema is correctly reloaded when a column
309# is added to a table.
310#
311ifcapable trigger&&tempdb {
312  do_test alter3-6.1 {
313    execsql {
314      CREATE TABLE t1(a, b);
315      CREATE TABLE log(trig, a, b);
316
317      CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
318        INSERT INTO log VALUES('a', new.a, new.b);
319      END;
320      CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
321        INSERT INTO log VALUES('b', new.a, new.b);
322      END;
323
324      INSERT INTO t1 VALUES(1, 2);
325      SELECT * FROM log;
326    }
327  } {b 1 2 a 1 2}
328  do_test alter3-6.2 {
329    execsql {
330      ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
331      INSERT INTO t1(a, b) VALUES(3, 4);
332      SELECT * FROM log;
333    }
334  } {b 1 2 a 1 2 b 3 4 a 3 4}
335}
336
337if {!$has_codec} {
338  ifcapable vacuum {
339    do_test alter3-7.1 {
340      execsql {
341        VACUUM;
342      }
343      get_file_format
344    } {1}
345    do_test alter3-7.2 {
346      execsql {
347        CREATE TABLE abc(a, b, c);
348        ALTER TABLE abc ADD d DEFAULT NULL;
349      }
350      get_file_format
351    } {3}
352    do_test alter3-7.3 {
353      execsql {
354        ALTER TABLE abc ADD e DEFAULT 10;
355      }
356      get_file_format
357    } {3}
358    do_test alter3-7.4 {
359      execsql {
360        ALTER TABLE abc ADD f DEFAULT NULL;
361      }
362      get_file_format
363    } {3}
364    do_test alter3-7.5 {
365      execsql {
366        VACUUM;
367      }
368      get_file_format
369    } {1}
370  }
371}
372
373# Ticket #1183 - Make sure adding columns to large tables does not cause
374# memory corruption (as was the case before this bug was fixed).
375do_test alter3-8.1 {
376  execsql {
377    CREATE TABLE t4(c1);
378  }
379} {}
380set ::sql ""
381do_test alter3-8.2 {
382  set cols c1
383  for {set i 2} {$i < 100} {incr i} {
384    execsql "
385      ALTER TABLE t4 ADD c$i
386    "
387    lappend cols c$i
388  }
389  set ::sql "CREATE TABLE t4([join $cols {, }])"
390  list
391} {}
392do_test alter3-8.2 {
393  execsql {
394    SELECT sql FROM sqlite_master WHERE name = 't4';
395  }
396} [list $::sql]
397
398finish_test
399