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