xref: /sqlite-3.40.0/test/alter4.test (revision bdb339ff) 
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# 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# alter4-1.*: Test that ALTER TABLE correctly modifies the CREATE TABLE sql.
42# alter4-2.*: Test error messages.
43# alter4-3.*: Test adding columns with default value NULL.
44# alter4-4.*: Test adding columns with default values other than NULL.
45# alter4-5.*: Test adding columns to tables in ATTACHed databases.
46# alter4-6.*: Test that temp triggers are not accidentally dropped.
47# alter4-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 alter4-1.1 {
57  execsql {
58    CREATE TEMP TABLE abc(a, b, c);
59    SELECT sql FROM sqlite_temp_master;
60  }
61} {{CREATE TABLE abc(a, b, c)}}
62do_test alter4-1.2 {
63  execsql {ALTER TABLE abc ADD d INTEGER;}
64  execsql {
65    SELECT sql FROM sqlite_temp_master;
66  }
67} {{CREATE TABLE abc(a, b, c, d INTEGER)}}
68do_test alter4-1.3 {
69  execsql {ALTER TABLE abc ADD e}
70  execsql {
71    SELECT sql FROM sqlite_temp_master;
72  }
73} {{CREATE TABLE abc(a, b, c, d INTEGER, e)}}
74do_test alter4-1.4 {
75  execsql {
76    CREATE TABLE temp.t1(a, b);
77    ALTER TABLE t1 ADD c;
78    SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
79  }
80} {{CREATE TABLE t1(a, b, c)}}
81do_test alter4-1.5 {
82  execsql {
83    ALTER TABLE t1 ADD d CHECK (a>d);
84    SELECT sql FROM sqlite_temp_master WHERE tbl_name = 't1';
85  }
86} {{CREATE TABLE t1(a, b, c, d CHECK (a>d))}}
87ifcapable foreignkey {
88  do_test alter4-1.6 {
89    execsql {
90      CREATE TEMP TABLE t2(a, b, UNIQUE(a, b));
91      ALTER TABLE t2 ADD c REFERENCES t1(c)  ;
92      SELECT sql FROM sqlite_temp_master
93       WHERE tbl_name = 't2' AND type = 'table';
94    }
95  } {{CREATE TABLE t2(a, b, c REFERENCES t1(c), UNIQUE(a, b))}}
96}
97do_test alter4-1.7 {
98  execsql {
99    CREATE TEMPORARY TABLE t3(a, b, UNIQUE(a, b));
100    ALTER TABLE t3 ADD COLUMN c VARCHAR(10, 20);
101    SELECT sql FROM sqlite_temp_master
102     WHERE tbl_name = 't3' AND type = 'table';
103  }
104} {{CREATE TABLE t3(a, b, c VARCHAR(10, 20), UNIQUE(a, b))}}
105do_test alter4-1.99 {
106  catchsql {
107    # May not exist if foriegn-keys are omitted at compile time.
108    DROP TABLE t2;
109  }
110  execsql {
111    DROP TABLE abc;
112    DROP TABLE t1;
113    DROP TABLE t3;
114  }
115} {}
116
117do_test alter4-2.1 {
118  execsql {
119    CREATE TABLE temp.t1(a, b);
120  }
121  catchsql {
122    ALTER TABLE t1 ADD c PRIMARY KEY;
123  }
124} {1 {Cannot add a PRIMARY KEY column}}
125do_test alter4-2.2 {
126  catchsql {
127    ALTER TABLE t1 ADD c UNIQUE
128  }
129} {1 {Cannot add a UNIQUE column}}
130do_test alter4-2.3 {
131  catchsql {
132    ALTER TABLE t1 ADD b VARCHAR(10)
133  }
134} {1 {duplicate column name: b}}
135do_test alter4-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 alter4-2.4 {
141  catchsql {
142    ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
143  }
144} {0 {}}
145ifcapable view {
146  do_test alter4-2.5 {
147    execsql {
148      CREATE TEMPORARY 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 alter4-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 alter4-2.99 {
161  execsql {
162    DROP TABLE t1;
163  }
164} {}
165
166do_test alter4-3.1 {
167  execsql {
168    CREATE TEMP 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 alter4-3.1 {
175  execsql {
176    PRAGMA schema_version = 10;
177  }
178} {}
179do_test alter4-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 alter4-3.3 {
187    get_file_format
188  } {3}
189}
190ifcapable schema_version {
191  do_test alter4-3.4 {
192    execsql {
193      PRAGMA schema_version;
194    }
195  } {10}
196}
197
198do_test alter4-4.1 {
199  db close
200  file delete -force test.db
201  set ::DB [sqlite3 db test.db]
202  execsql {
203    CREATE TEMP 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 alter4-4.1 {
210  execsql {
211    PRAGMA schema_version = 20;
212  }
213} {}
214do_test alter4-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 alter4-4.3 {
222    get_file_format
223  } {3}
224}
225ifcapable schema_version {
226  do_test alter4-4.4 {
227    execsql {
228      PRAGMA schema_version;
229    }
230  } {20}
231}
232do_test alter4-4.99 {
233  execsql {
234    DROP TABLE t1;
235  }
236} {}
237
238ifcapable attach {
239  do_test alter4-5.1 {
240    file delete -force test2.db
241    file delete -force test2.db-journal
242    execsql {
243      CREATE TEMP 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 alter4-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 alter4-5.3 {
259    execsql {
260      SELECT * FROM aux.t1;
261    }
262  } {1 one {} 2 two {}}
263  ifcapable schema_version {
264    do_test alter4-5.4 {
265      execsql {
266        PRAGMA aux.schema_version;
267      }
268    } {31}
269  }
270  if {!$has_codec} {
271    do_test alter4-5.5 {
272      list [get_file_format test2.db] [get_file_format]
273    } {2 3}
274  }
275  do_test alter4-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 alter4-5.7 {
282    execsql {
283      SELECT * FROM aux.t1;
284    }
285  } {1 one {} 1000 2 two {} 1000}
286  ifcapable schema_version {
287    do_test alter4-5.8 {
288      execsql {
289        PRAGMA aux.schema_version;
290      }
291    } {32}
292  }
293  do_test alter4-5.9 {
294    execsql {
295      SELECT * FROM t1;
296    }
297  } {1 one 2 two}
298  do_test alter4-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 alter4-6.1 {
312    execsql {
313      CREATE TEMP TABLE t1(a, b);
314      CREATE TEMP 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 alter4-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 alter4-7.1 {
339      execsql {
340        VACUUM;
341      }
342      get_file_format
343    } {1}
344    do_test alter4-7.2 {
345      execsql {
346        CREATE TEMP TABLE abc(a, b, c);
347        ALTER TABLE abc ADD d DEFAULT NULL;
348      }
349      get_file_format
350    } {2}
351    do_test alter4-7.3 {
352      execsql {
353        ALTER TABLE abc ADD e DEFAULT 10;
354      }
355      get_file_format
356    } {3}
357    do_test alter4-7.4 {
358      execsql {
359        ALTER TABLE abc ADD f DEFAULT NULL;
360      }
361      get_file_format
362    } {3}
363    do_test alter4-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 alter4-8.1 {
375  execsql {
376    CREATE TEMP TABLE t4(c1);
377  }
378} {}
379set ::sql ""
380do_test alter4-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 alter4-8.2 {
392  execsql {
393    SELECT sql FROM sqlite_temp_master WHERE name = 't4';
394  }
395} [list $::sql]
396
397finish_test
398