xref: /sqlite-3.40.0/test/alter4.test (revision a8e41eca) 
1# 2009 February 2
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: alter4.test,v 1.1 2009/02/02 18:03:22 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
30# Test Organisation:
31# ------------------
32#
33# alter4-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
34# alter4-2.*: Test error messages.
35# alter4-3.*: Test adding columns with default value NULL.
36# alter4-4.*: Test adding columns with default values other than NULL.
37# alter4-5.*: Test adding columns to tables in ATTACHed databases.
38# alter4-6.*: Test that temp triggers are not accidentally dropped.
39# alter4-7.*: Test that VACUUM resets the file-format.
40#
41
42do_test alter4-1.1 {
43  execsql {
44    CREATE TEMP TABLE abc(a, b, c);
45    SELECT sql FROM sqlite_temp_master;
46  }
47} {{CREATE TABLE abc(a, b, c)}}
48do_test alter4-1.1b {
49  execsql {
50    SELECT sql FROM temp.sqlite_master;
51  }
52} {{CREATE TABLE abc(a, b, c)}}
53do_test alter4-1.2 {
54  execsql {ALTER TABLE abc ADD d INTEGER;}
55  execsql {
56    SELECT sql FROM sqlite_temp_master;
57  }
58} {{CREATE TABLE abc(a, b, c, d INTEGER)}}
59do_test alter4-1.2b {
60  execsql {
61    SELECT sql FROM temp.sqlite_master;
62  }
63} {{CREATE TABLE abc(a, b, c, d INTEGER)}}
64do_test alter4-1.3 {
65  execsql {ALTER TABLE abc ADD e}
66  execsql {
67    SELECT sql FROM sqlite_temp_master;
68  }
69} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
70do_test alter4-1.3b {
71  execsql {
72    SELECT sql FROM temp.sqlite_master;
73  }
74} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
75do_test alter4-1.4 {
76  execsql {
77    CREATE TABLE temp.t1(a, b);
78    ALTER TABLE t1 ADD c;
79    SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
80  }
81} {{CREATE TABLE t1(a, b, c)}}
82do_test alter4-1.4b {
83  execsql {
84    SELECT sql FROM temp.sqlite_master WHERE tbl_name = 't1';
85  }
86} {{CREATE TABLE t1(a, b, c)}}
87do_test alter4-1.5 {
88  execsql {
89    ALTER TABLE t1 ADD d CHECK (a>d);
90    SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
91  }
92} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
93ifcapable foreignkey {
94  do_test alter4-1.6 {
95    execsql {
96      CREATE TEMP TABLE t2(a, b, UNIQUE(a, b));
97      ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
98      SELECT sql FROM sqlite_temp_master
99       WHERE tbl_name = 't2' AND type = 'table';
100    }
101  } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
102}
103do_test alter4-1.7 {
104  execsql {
105    CREATE TEMPORARY TABLE t3(a, b, UNIQUE(a, b));
106    ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
107    SELECT sql FROM sqlite_temp_master
108     WHERE tbl_name = 't3' AND type = 'table';
109  }
110} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
111do_test alter4-1.99 {
112  catchsql {
113    # May not exist if foriegn-keys are omitted at compile time.
114    DROP TABLE t2;
115  }
116  execsql {
117    DROP TABLE abc;
118    DROP TABLE t1;
119    DROP TABLE t3;
120  }
121} {}
122
123do_test alter4-2.1 {
124  execsql {
125    CREATE TABLE temp.t1(a, b);
126    INSERT INTO t1 VALUES(1,2);
127  }
128  catchsql {
129    ALTER TABLE t1 ADD c PRIMARY KEY;
130  }
131} {1 {Cannot add a PRIMARY KEY column}}
132do_test alter4-2.2 {
133  catchsql {
134    ALTER TABLE t1 ADD c UNIQUE
135  }
136} {1 {Cannot add a UNIQUE column}}
137do_test alter4-2.3 {
138  catchsql {
139    ALTER TABLE t1 ADD b VARCHAR(10)
140  }
141} {1 {duplicate column name: b}}
142do_test alter4-2.3 {
143  catchsql {
144    ALTER TABLE t1 ADD c NOT NULL;
145  }
146} {1 {Cannot add a NOT NULL column with default value NULL}}
147do_test alter4-2.4 {
148  catchsql {
149    ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
150  }
151} {0 {}}
152ifcapable view {
153  do_test alter4-2.5 {
154    execsql {
155      CREATE TEMPORARY VIEW v1 AS SELECT * FROM t1;
156    }
157    catchsql {
158      alter table v1 add column d;
159    }
160  } {1 {Cannot add a column to a view}}
161}
162do_test alter4-2.6 {
163  catchsql {
164    alter table t1 add column d DEFAULT CURRENT_TIME;
165  }
166} {1 {Cannot add a column with non-constant default}}
167do_test alter4-2.7 {
168  catchsql {
169    alter table t1 add column d default (-5+1);
170  }
171} {1 {Cannot add a column with non-constant default}}
172do_test alter4-2.99 {
173  execsql {
174    DROP TABLE t1;
175  }
176} {}
177
178do_test alter4-3.1 {
179  execsql {
180    CREATE TEMP TABLE t1(a, b);
181    INSERT INTO t1 VALUES(1, 100);
182    INSERT INTO t1 VALUES(2, 300);
183    SELECT * FROM t1;
184  }
185} {1 100 2 300}
186do_test alter4-3.1 {
187  execsql {
188    PRAGMA schema_version = 10;
189  }
190} {}
191do_test alter4-3.2 {
192  execsql {
193    ALTER TABLE t1 ADD c;
194    SELECT * FROM t1;
195  }
196} {1 100 {} 2 300 {}}
197ifcapable schema_version {
198  do_test alter4-3.4 {
199    execsql {
200      PRAGMA schema_version;
201    }
202  } {10}
203}
204
205do_test alter4-4.1 {
206  db close
207  forcedelete test.db
208  set ::DB [sqlite3 db test.db]
209  execsql {
210    CREATE TEMP TABLE t1(a, b);
211    INSERT INTO t1 VALUES(1, 100);
212    INSERT INTO t1 VALUES(2, 300);
213    SELECT * FROM t1;
214  }
215} {1 100 2 300}
216do_test alter4-4.1 {
217  execsql {
218    PRAGMA schema_version = 20;
219  }
220} {}
221do_test alter4-4.2 {
222  execsql {
223    ALTER TABLE t1 ADD c DEFAULT 'hello world';
224    SELECT * FROM t1;
225  }
226} {1 100 {hello world} 2 300 {hello world}}
227ifcapable schema_version {
228  do_test alter4-4.4 {
229    execsql {
230      PRAGMA schema_version;
231    }
232  } {20}
233}
234do_test alter4-4.99 {
235  execsql {
236    DROP TABLE t1;
237  }
238} {}
239
240ifcapable attach {
241  do_test alter4-5.1 {
242    forcedelete test2.db
243    forcedelete test2.db-journal
244    execsql {
245      CREATE TEMP TABLE t1(a, b);
246      INSERT INTO t1 VALUES(1, 'one');
247      INSERT INTO t1 VALUES(2, 'two');
248      ATTACH 'test2.db' AS aux;
249      CREATE TABLE aux.t1 AS SELECT * FROM t1;
250      PRAGMA aux.schema_version = 30;
251      SELECT sql FROM aux.sqlite_master;
252    }
253  } {{CREATE TABLE t1(a,b)}}
254  do_test alter4-5.2 {
255    execsql {
256      ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
257      SELECT sql FROM aux.sqlite_master;
258    }
259  } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
260  do_test alter4-5.3 {
261    execsql {
262      SELECT * FROM aux.t1;
263    }
264  } {1 one {} 2 two {}}
265  ifcapable schema_version {
266    do_test alter4-5.4 {
267      execsql {
268        PRAGMA aux.schema_version;
269      }
270    } {31}
271  }
272  do_test alter4-5.6 {
273    execsql {
274      ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
275      SELECT sql FROM aux.sqlite_master;
276    }
277  } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
278  do_test alter4-5.7 {
279    execsql {
280      SELECT * FROM aux.t1;
281    }
282  } {1 one {} 1000 2 two {} 1000}
283  ifcapable schema_version {
284    do_test alter4-5.8 {
285      execsql {
286        PRAGMA aux.schema_version;
287      }
288    } {32}
289  }
290  do_test alter4-5.9 {
291    execsql {
292      SELECT * FROM t1;
293    }
294  } {1 one 2 two}
295  do_test alter4-5.99 {
296    execsql {
297      DROP TABLE aux.t1;
298      DROP TABLE t1;
299    }
300  } {}
301}
302
303#----------------------------------------------------------------
304# Test that the table schema is correctly reloaded when a column
305# is added to a table.
306#
307ifcapable trigger&&tempdb {
308  do_test alter4-6.1 {
309    execsql {
310      CREATE TEMP TABLE t1(a, b);
311      CREATE TEMP TABLE log(trig, a, b);
312
313      CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
314        INSERT INTO log VALUES('a', new.a, new.b);
315      END;
316      CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
317        INSERT INTO log VALUES('b', new.a, new.b);
318      END;
319
320      INSERT INTO t1 VALUES(1, 2);
321      SELECT * FROM log;
322    }
323  } {b 1 2 a 1 2}
324  do_test alter4-6.2 {
325    execsql {
326      ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
327      INSERT INTO t1(a, b) VALUES(3, 4);
328      SELECT * FROM log;
329    }
330  } {b 1 2 a 1 2 b 3 4 a 3 4}
331}
332
333# Ticket #1183 - Make sure adding columns to large tables does not cause
334# memory corruption (as was the case before this bug was fixed).
335do_test alter4-8.1 {
336  execsql {
337    CREATE TEMP TABLE t4(c1);
338  }
339} {}
340set ::sql ""
341do_test alter4-8.2 {
342  set cols c1
343  for {set i 2} {$i < 100} {incr i} {
344    execsql "
345      ALTER TABLE t4 ADD c$i
346    "
347    lappend cols c$i
348  }
349  set ::sql "CREATE TABLE t4([join $cols {, }])"
350  list
351} {}
352do_test alter4-8.2 {
353  execsql {
354    SELECT sql FROM sqlite_temp_master WHERE name = 't4';
355  }
356} [list $::sql]
357
358
359# Test that a default value equal to -1 multipied by the smallest possible
360# 64-bit integer is correctly converted to a real.
361do_execsql_test alter4-9.1 {
362  CREATE TABLE t5(
363    a INTEGER DEFAULT -9223372036854775808,
364    b INTEGER DEFAULT (-(-9223372036854775808))
365  );
366  INSERT INTO t5 DEFAULT VALUES;
367}
368
369do_execsql_test alter4-9.2 { SELECT typeof(a), a, typeof(b), b FROM t5; } {
370  integer -9223372036854775808
371  real     9.22337203685478e+18
372}
373
374do_execsql_test alter4-9.3 {
375  ALTER TABLE t5 ADD COLUMN c INTEGER DEFAULT (-(-9223372036854775808));
376  SELECT typeof(c), c FROM t5;
377} {real 9.22337203685478e+18}
378
379# Confirm that doing an ALTER TABLE on a legacy format database
380# does not corrupt DESC indexes.
381#
382# Ticket https://www.sqlite.org/src/tktview/f68bf68513a1c
383#
384do_test alter4-10.1 {
385  db close
386  sqlite3 db :memory:
387  sqlite3_db_config db LEGACY_FILE_FORMAT 1
388  db eval {
389    CREATE TABLE t1(a,b,c);
390    CREATE INDEX t1a ON t1(a DESC);
391    INSERT INTO t1 VALUES(1,2,3);
392    INSERT INTO t1 VALUES(2,3,4);
393    ALTER TABLE t1 ADD COLUMN d;
394    PRAGMA integrity_check;
395  }
396} {ok}
397
398reset_db
399do_execsql_test alter4-11.0 {
400  CREATE TABLE t1(c INTEGER PRIMARY KEY, d);
401  INSERT INTO t1(c,d) VALUES(1,2);
402  PRAGMA foreign_keys = on;
403  ALTER TABLE t1 ADD COLUMN e;
404}
405
406do_execsql_test alter4-11.1 {
407  ALTER TABLE t1 ADD COLUMN f REFERENCES t1;
408}
409
410do_catchsql_test alter4-11.2 {
411  ALTER TABLE t1 ADD COLUMN g REFERENCES t1 DEFAULT 4;
412} {1 {Cannot add a REFERENCES column with non-NULL default value}}
413
414do_catchsql_test alter4-11.3 {
415  ALTER TABLE t2 ADD COLUMN g;
416} {1 {no such table: t2}}
417
418ifcapable fts5 {
419  do_execsql_test alter4-11.4 {
420    CREATE VIRTUAL TABLE fff USING fts5(f);
421  }
422  do_catchsql_test alter4-11.2 {
423    ALTER TABLE fff ADD COLUMN g;
424  } {1 {virtual tables may not be altered}}
425}
426
427finish_test
428