wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2007 January 24
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 file is testing the INSERT transfer optimization.
13 #
14 # $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $
15  
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18  
19 ifcapable !view||!subquery {
20 finish_test
21 return
22 }
23  
24 # The sqlite3_xferopt_count variable is incremented whenever the
25 # insert transfer optimization applies.
26 #
27 # This procedure runs a test to see if the sqlite3_xferopt_count is
28 # set to N.
29 #
30 proc xferopt_test {testname N} {
31 do_test $testname {set ::sqlite3_xferopt_count} $N
32 }
33  
34 # Create tables used for testing.
35 #
36 execsql {
37 PRAGMA legacy_file_format = 0;
38 CREATE TABLE t1(a int, b int, check(b>a));
39 CREATE TABLE t2(x int, y int);
40 CREATE VIEW v2 AS SELECT y, x FROM t2;
41 CREATE TABLE t3(a int, b int);
42 }
43  
44 # Ticket #2252. Make sure the an INSERT from identical tables
45 # does not violate constraints.
46 #
47 do_test insert4-1.1 {
48 set sqlite3_xferopt_count 0
49 execsql {
50 DELETE FROM t1;
51 DELETE FROM t2;
52 INSERT INTO t2 VALUES(9,1);
53 }
54 catchsql {
55 INSERT INTO t1 SELECT * FROM t2;
56 }
57 } {1 {constraint failed}}
58 xferopt_test insert4-1.2 0
59 do_test insert4-1.3 {
60 execsql {
61 SELECT * FROM t1;
62 }
63 } {}
64  
65 # Tests to make sure that the transfer optimization is not occurring
66 # when it is not a valid optimization.
67 #
68 # The SELECT must be against a real table.
69 do_test insert4-2.1.1 {
70 execsql {
71 DELETE FROM t1;
72 INSERT INTO t1 SELECT 4, 8;
73 SELECT * FROM t1;
74 }
75 } {4 8}
76 xferopt_test insert4-2.1.2 0
77 do_test insert4-2.2.1 {
78 catchsql {
79 DELETE FROM t1;
80 INSERT INTO t1 SELECT * FROM v2;
81 SELECT * FROM t1;
82 }
83 } {0 {1 9}}
84 xferopt_test insert4-2.2.2 0
85  
86 # Do not run the transfer optimization if there is a LIMIT clause
87 #
88 do_test insert4-2.3.1 {
89 execsql {
90 DELETE FROM t2;
91 INSERT INTO t2 VALUES(9,1);
92 INSERT INTO t2 SELECT y, x FROM t2;
93 INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
94 SELECT * FROM t3;
95 }
96 } {9 1}
97 xferopt_test insert4-2.3.2 0
98 do_test insert4-2.3.3 {
99 catchsql {
100 DELETE FROM t1;
101 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
102 SELECT * FROM t1;
103 }
104 } {1 {constraint failed}}
105 xferopt_test insert4-2.3.4 0
106  
107 # Do not run the transfer optimization if there is a DISTINCT
108 #
109 do_test insert4-2.4.1 {
110 execsql {
111 DELETE FROM t3;
112 INSERT INTO t3 SELECT DISTINCT * FROM t2;
113 SELECT * FROM t3;
114 }
115 } {1 9 9 1}
116 xferopt_test insert4-2.4.2 0
117 do_test insert4-2.4.3 {
118 catchsql {
119 DELETE FROM t1;
120 INSERT INTO t1 SELECT DISTINCT * FROM t2;
121 }
122 } {1 {constraint failed}}
123 xferopt_test insert4-2.4.4 0
124  
125 # The following procedure constructs two tables then tries to transfer
126 # data from one table to the other. Checks are made to make sure the
127 # transfer is successful and that the transfer optimization was used or
128 # not, as appropriate.
129 #
130 # xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA
131 #
132 # The TESTID argument is the symbolic name for this test. The XFER-USED
133 # argument is true if the transfer optimization should be employed and
134 # false if not. INIT-DATA is a single row of data that is to be
135 # transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for
136 # the destination and source tables.
137 #
138 proc xfer_check {testid xferused initdata destschema srcschema} {
139 execsql "CREATE TABLE dest($destschema)"
140 execsql "CREATE TABLE src($srcschema)"
141 execsql "INSERT INTO src VALUES([join $initdata ,])"
142 set ::sqlite3_xferopt_count 0
143 do_test $testid.1 {
144 execsql {
145 INSERT INTO dest SELECT * FROM src;
146 SELECT * FROM dest;
147 }
148 } $initdata
149 do_test $testid.2 {
150 set ::sqlite3_xferopt_count
151 } $xferused
152 execsql {
153 DROP TABLE dest;
154 DROP TABLE src;
155 }
156 }
157  
158  
159 # Do run the transfer optimization if tables have identical
160 # CHECK constraints.
161 #
162 xfer_check insert4-3.1 1 {1 9} \
163 {a int, b int CHECK(b>a)} \
164 {x int, y int CHECK(y>x)}
165 xfer_check insert4-3.2 1 {1 9} \
166 {a int, b int CHECK(b>a)} \
167 {x int CHECK(y>x), y int}
168  
169 # Do run the transfer optimization if the destination table lacks
170 # any CHECK constraints regardless of whether or not there are CHECK
171 # constraints on the source table.
172 #
173 xfer_check insert4-3.3 1 {1 9} \
174 {a int, b int} \
175 {x int, y int CHECK(y>x)}
176  
177 # Do run the transfer optimization if the destination table omits
178 # NOT NULL constraints that the source table has.
179 #
180 xfer_check insert4-3.4 0 {1 9} \
181 {a int, b int CHECK(b>a)} \
182 {x int, y int}
183  
184 # Do not run the optimization if the destination has NOT NULL
185 # constraints that the source table lacks.
186 #
187 xfer_check insert4-3.5 0 {1 9} \
188 {a int, b int NOT NULL} \
189 {x int, y int}
190 xfer_check insert4-3.6 0 {1 9} \
191 {a int, b int NOT NULL} \
192 {x int NOT NULL, y int}
193 xfer_check insert4-3.7 0 {1 9} \
194 {a int NOT NULL, b int NOT NULL} \
195 {x int NOT NULL, y int}
196 xfer_check insert4-3.8 0 {1 9} \
197 {a int NOT NULL, b int} \
198 {x int, y int}
199  
200  
201 # Do run the transfer optimization if the destination table and
202 # source table have the same NOT NULL constraints or if the
203 # source table has extra NOT NULL constraints.
204 #
205 xfer_check insert4-3.9 1 {1 9} \
206 {a int, b int} \
207 {x int NOT NULL, y int}
208 xfer_check insert4-3.10 1 {1 9} \
209 {a int, b int} \
210 {x int NOT NULL, y int NOT NULL}
211 xfer_check insert4-3.11 1 {1 9} \
212 {a int NOT NULL, b int} \
213 {x int NOT NULL, y int NOT NULL}
214 xfer_check insert4-3.12 1 {1 9} \
215 {a int, b int NOT NULL} \
216 {x int NOT NULL, y int NOT NULL}
217  
218 # Do not run the optimization if any corresponding table
219 # columns have different affinities.
220 #
221 xfer_check insert4-3.20 0 {1 9} \
222 {a text, b int} \
223 {x int, b int}
224 xfer_check insert4-3.21 0 {1 9} \
225 {a int, b int} \
226 {x text, b int}
227  
228 # "int" and "integer" are equivalent so the optimization should
229 # run here.
230 #
231 xfer_check insert4-3.22 1 {1 9} \
232 {a int, b int} \
233 {x integer, b int}
234  
235 # Ticket #2291.
236 #
237  
238 do_test insert4-4.1a {
239 execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))}
240 } {}
241 ifcapable vacuum {
242 do_test insert4-4.1b {
243 execsql {
244 INSERT INTO t4 VALUES(NULL,0);
245 INSERT INTO t4 VALUES(NULL,1);
246 INSERT INTO t4 VALUES(NULL,1);
247 VACUUM;
248 }
249 } {}
250 }
251  
252 # Check some error conditions:
253 #
254 do_test insert4-5.1 {
255 # Table does not exist.
256 catchsql { INSERT INTO t2 SELECT * FROM nosuchtable }
257 } {1 {no such table: nosuchtable}}
258 do_test insert4-5.2 {
259 # Number of columns does not match.
260 catchsql {
261 CREATE TABLE t5(a, b, c);
262 INSERT INTO t4 SELECT * FROM t5;
263 }
264 } {1 {table t4 has 2 columns but 3 values were supplied}}
265  
266 do_test insert4-6.1 {
267 set ::sqlite3_xferopt_count 0
268 execsql {
269 CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase);
270 CREATE INDEX t2_i1 ON t2(x ASC, y DESC);
271 CREATE INDEX t3_i1 ON t3(a, b);
272 INSERT INTO t2 SELECT * FROM t3;
273 }
274 set ::sqlite3_xferopt_count
275 } {0}
276 do_test insert4-6.2 {
277 set ::sqlite3_xferopt_count 0
278 execsql {
279 DROP INDEX t2_i2;
280 INSERT INTO t2 SELECT * FROM t3;
281 }
282 set ::sqlite3_xferopt_count
283 } {0}
284 do_test insert4-6.3 {
285 set ::sqlite3_xferopt_count 0
286 execsql {
287 DROP INDEX t2_i1;
288 CREATE INDEX t2_i1 ON t2(x ASC, y ASC);
289 INSERT INTO t2 SELECT * FROM t3;
290 }
291 set ::sqlite3_xferopt_count
292 } {1}
293 do_test insert4-6.4 {
294 set ::sqlite3_xferopt_count 0
295 execsql {
296 DROP INDEX t2_i1;
297 CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM);
298 INSERT INTO t2 SELECT * FROM t3;
299 }
300 set ::sqlite3_xferopt_count
301 } {0}
302  
303  
304 do_test insert4-6.5 {
305 execsql {
306 CREATE TABLE t6a(x CHECK( x<>'abc' ));
307 INSERT INTO t6a VALUES('ABC');
308 SELECT * FROM t6a;
309 }
310 } {ABC}
311 do_test insert4-6.6 {
312 execsql {
313 CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase ));
314 }
315 catchsql {
316 INSERT INTO t6b SELECT * FROM t6a;
317 }
318 } {1 {constraint failed}}
319 do_test insert4-6.7 {
320 execsql {
321 DROP TABLE t6b;
322 CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' ));
323 }
324 catchsql {
325 INSERT INTO t6b SELECT * FROM t6a;
326 }
327 } {1 {constraint failed}}
328  
329 # Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
330 # Disable the xfer optimization if the destination table contains
331 # a foreign key constraint
332 #
333 ifcapable foreignkey {
334 do_test insert4-7.1 {
335 set ::sqlite3_xferopt_count 0
336 execsql {
337 CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123);
338 CREATE TABLE t7b(y INTEGER REFERENCES t7a);
339 CREATE TABLE t7c(z INT); INSERT INTO t7c VALUES(234);
340 INSERT INTO t7b SELECT * FROM t7c;
341 SELECT * FROM t7b;
342 }
343 } {234}
344 do_test insert4-7.2 {
345 set ::sqlite3_xferopt_count
346 } {1}
347 do_test insert4-7.3 {
348 set ::sqlite3_xferopt_count 0
349 execsql {
350 DELETE FROM t7b;
351 PRAGMA foreign_keys=ON;
352 }
353 catchsql {
354 INSERT INTO t7b SELECT * FROM t7c;
355 }
356 } {1 {foreign key constraint failed}}
357 do_test insert4-7.4 {
358 execsql {SELECT * FROM t7b}
359 } {}
360 do_test insert4-7.5 {
361 set ::sqlite3_xferopt_count
362 } {0}
363 do_test insert4-7.6 {
364 set ::sqlite3_xferopt_count 0
365 execsql {
366 DELETE FROM t7b; DELETE FROM t7c;
367 INSERT INTO t7c VALUES(123);
368 INSERT INTO t7b SELECT * FROM t7c;
369 SELECT * FROM t7b;
370 }
371 } {123}
372 do_test insert4-7.7 {
373 set ::sqlite3_xferopt_count
374 } {0}
375 do_test insert4-7.7 {
376 set ::sqlite3_xferopt_count 0
377 execsql {
378 PRAGMA foreign_keys=OFF;
379 DELETE FROM t7b;
380 INSERT INTO t7b SELECT * FROM t7c;
381 SELECT * FROM t7b;
382 }
383 } {123}
384 do_test insert4-7.8 {
385 set ::sqlite3_xferopt_count
386 } {1}
387 }
388  
389 finish_test