wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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  
19 set testdir [file dirname $argv0]
20  
21 source $testdir/tester.tcl
22  
23 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
24 ifcapable !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  
42 do_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)}}
48 do_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)}}
54 do_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)}}
60 do_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)}}
67 do_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))}}
73 ifcapable 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 }
83 do_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))}}
91 do_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  
103 do_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}}
111 do_test alter4-2.2 {
112 catchsql {
113 ALTER TABLE t1 ADD c UNIQUE
114 }
115 } {1 {Cannot add a UNIQUE column}}
116 do_test alter4-2.3 {
117 catchsql {
118 ALTER TABLE t1 ADD b VARCHAR(10)
119 }
120 } {1 {duplicate column name: b}}
121 do_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}}
126 do_test alter4-2.4 {
127 catchsql {
128 ALTER TABLE t1 ADD c NOT NULL DEFAULT 10;
129 }
130 } {0 {}}
131 ifcapable 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 }
141 do_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}}
146 do_test alter4-2.99 {
147 execsql {
148 DROP TABLE t1;
149 }
150 } {}
151  
152 do_test alter4-3.1 {
153 execsql {
154 CREATE TEMP TABLE t1(a, b);
155 INSERT INTO t1 VALUES(1, 100);
156 INSERT INTO t1 VALUES(2, 300);
157 SELECT * FROM t1;
158 }
159 } {1 100 2 300}
160 do_test alter4-3.1 {
161 execsql {
162 PRAGMA schema_version = 10;
163 }
164 } {}
165 do_test alter4-3.2 {
166 execsql {
167 ALTER TABLE t1 ADD c;
168 SELECT * FROM t1;
169 }
170 } {1 100 {} 2 300 {}}
171 ifcapable schema_version {
172 do_test alter4-3.4 {
173 execsql {
174 PRAGMA schema_version;
175 }
176 } {10}
177 }
178  
179 do_test alter4-4.1 {
180 db close
181 file delete -force test.db
182 set ::DB [sqlite3 db test.db]
183 execsql {
184 CREATE TEMP TABLE t1(a, b);
185 INSERT INTO t1 VALUES(1, 100);
186 INSERT INTO t1 VALUES(2, 300);
187 SELECT * FROM t1;
188 }
189 } {1 100 2 300}
190 do_test alter4-4.1 {
191 execsql {
192 PRAGMA schema_version = 20;
193 }
194 } {}
195 do_test alter4-4.2 {
196 execsql {
197 ALTER TABLE t1 ADD c DEFAULT 'hello world';
198 SELECT * FROM t1;
199 }
200 } {1 100 {hello world} 2 300 {hello world}}
201 ifcapable schema_version {
202 do_test alter4-4.4 {
203 execsql {
204 PRAGMA schema_version;
205 }
206 } {20}
207 }
208 do_test alter4-4.99 {
209 execsql {
210 DROP TABLE t1;
211 }
212 } {}
213  
214 ifcapable attach {
215 do_test alter4-5.1 {
216 file delete -force test2.db
217 file delete -force test2.db-journal
218 execsql {
219 CREATE TEMP TABLE t1(a, b);
220 INSERT INTO t1 VALUES(1, 'one');
221 INSERT INTO t1 VALUES(2, 'two');
222 ATTACH 'test2.db' AS aux;
223 CREATE TABLE aux.t1 AS SELECT * FROM t1;
224 PRAGMA aux.schema_version = 30;
225 SELECT sql FROM aux.sqlite_master;
226 }
227 } {{CREATE TABLE t1(a,b)}}
228 do_test alter4-5.2 {
229 execsql {
230 ALTER TABLE aux.t1 ADD COLUMN c VARCHAR(128);
231 SELECT sql FROM aux.sqlite_master;
232 }
233 } {{CREATE TABLE t1(a,b, c VARCHAR(128))}}
234 do_test alter4-5.3 {
235 execsql {
236 SELECT * FROM aux.t1;
237 }
238 } {1 one {} 2 two {}}
239 ifcapable schema_version {
240 do_test alter4-5.4 {
241 execsql {
242 PRAGMA aux.schema_version;
243 }
244 } {31}
245 }
246 do_test alter4-5.6 {
247 execsql {
248 ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
249 SELECT sql FROM aux.sqlite_master;
250 }
251 } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
252 do_test alter4-5.7 {
253 execsql {
254 SELECT * FROM aux.t1;
255 }
256 } {1 one {} 1000 2 two {} 1000}
257 ifcapable schema_version {
258 do_test alter4-5.8 {
259 execsql {
260 PRAGMA aux.schema_version;
261 }
262 } {32}
263 }
264 do_test alter4-5.9 {
265 execsql {
266 SELECT * FROM t1;
267 }
268 } {1 one 2 two}
269 do_test alter4-5.99 {
270 execsql {
271 DROP TABLE aux.t1;
272 DROP TABLE t1;
273 }
274 } {}
275 }
276  
277 #----------------------------------------------------------------
278 # Test that the table schema is correctly reloaded when a column
279 # is added to a table.
280 #
281 ifcapable trigger&&tempdb {
282 do_test alter4-6.1 {
283 execsql {
284 CREATE TEMP TABLE t1(a, b);
285 CREATE TEMP TABLE log(trig, a, b);
286  
287 CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
288 INSERT INTO log VALUES('a', new.a, new.b);
289 END;
290 CREATE TEMP TRIGGER t1_b AFTER INSERT ON t1 BEGIN
291 INSERT INTO log VALUES('b', new.a, new.b);
292 END;
293  
294 INSERT INTO t1 VALUES(1, 2);
295 SELECT * FROM log;
296 }
297 } {b 1 2 a 1 2}
298 do_test alter4-6.2 {
299 execsql {
300 ALTER TABLE t1 ADD COLUMN c DEFAULT 'c';
301 INSERT INTO t1(a, b) VALUES(3, 4);
302 SELECT * FROM log;
303 }
304 } {b 1 2 a 1 2 b 3 4 a 3 4}
305 }
306  
307 # Ticket #1183 - Make sure adding columns to large tables does not cause
308 # memory corruption (as was the case before this bug was fixed).
309 do_test alter4-8.1 {
310 execsql {
311 CREATE TEMP TABLE t4(c1);
312 }
313 } {}
314 set ::sql ""
315 do_test alter4-8.2 {
316 set cols c1
317 for {set i 2} {$i < 100} {incr i} {
318 execsql "
319 ALTER TABLE t4 ADD c$i
320 "
321 lappend cols c$i
322 }
323 set ::sql "CREATE TABLE t4([join $cols {, }])"
324 list
325 } {}
326 do_test alter4-8.2 {
327 execsql {
328 SELECT sql FROM sqlite_temp_master WHERE name = 't4';
329 }
330 } [list $::sql]
331  
332 finish_test