wasCSharpSQLite – Blame information for rev 7
?pathlinks?
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 |