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.1.1.1 2012/02/21 17:04:16 misho 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: } {9 1 1 9}
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: # Ticket [676bc02b87176125635cb174d110b431581912bb]
390: # Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer
391: # optimization.
392: #
393: do_test insert4-8.1 {
394: execsql {
395: DROP TABLE IF EXISTS t1;
396: DROP TABLE IF EXISTS t2;
397: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
398: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
399: INSERT INTO t1 VALUES(1,2);
400: INSERT INTO t2 VALUES(1,3);
401: INSERT INTO t1 SELECT * FROM t2;
402: SELECT * FROM t1;
403: }
404: } {1 3}
405: do_test insert4-8.2 {
406: execsql {
407: DROP TABLE IF EXISTS t1;
408: DROP TABLE IF EXISTS t2;
409: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
410: CREATE TABLE t2(x, y);
411: INSERT INTO t1 VALUES(1,2);
412: INSERT INTO t2 VALUES(1,3);
413: INSERT INTO t1 SELECT * FROM t2;
414: SELECT * FROM t1;
415: }
416: } {1 3}
417: do_test insert4-8.3 {
418: execsql {
419: DROP TABLE IF EXISTS t1;
420: DROP TABLE IF EXISTS t2;
421: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
422: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
423: INSERT INTO t1 VALUES(1,2);
424: INSERT INTO t2 VALUES(1,3);
425: INSERT INTO t1 SELECT * FROM t2;
426: SELECT * FROM t1;
427: }
428: } {1 2}
429: do_test insert4-8.4 {
430: execsql {
431: DROP TABLE IF EXISTS t1;
432: DROP TABLE IF EXISTS t2;
433: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
434: CREATE TABLE t2(x, y);
435: INSERT INTO t1 VALUES(1,2);
436: INSERT INTO t2 VALUES(1,3);
437: INSERT INTO t1 SELECT * FROM t2;
438: SELECT * FROM t1;
439: }
440: } {1 2}
441: do_test insert4-8.5 {
442: execsql {
443: DROP TABLE IF EXISTS t1;
444: DROP TABLE IF EXISTS t2;
445: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
446: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
447: INSERT INTO t1 VALUES(1,2);
448: INSERT INTO t2 VALUES(-99,100);
449: INSERT INTO t2 VALUES(1,3);
450: SELECT * FROM t1;
451: }
452: catchsql {
453: INSERT INTO t1 SELECT * FROM t2;
454: }
455: } {1 {PRIMARY KEY must be unique}}
456: do_test insert4-8.6 {
457: execsql {
458: SELECT * FROM t1;
459: }
460: } {-99 100 1 2}
461: do_test insert4-8.7 {
462: execsql {
463: DROP TABLE IF EXISTS t1;
464: DROP TABLE IF EXISTS t2;
465: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
466: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
467: INSERT INTO t1 VALUES(1,2);
468: INSERT INTO t2 VALUES(-99,100);
469: INSERT INTO t2 VALUES(1,3);
470: SELECT * FROM t1;
471: }
472: catchsql {
473: INSERT INTO t1 SELECT * FROM t2;
474: }
475: } {1 {PRIMARY KEY must be unique}}
476: do_test insert4-8.8 {
477: execsql {
478: SELECT * FROM t1;
479: }
480: } {1 2}
481: do_test insert4-8.9 {
482: execsql {
483: DROP TABLE IF EXISTS t1;
484: DROP TABLE IF EXISTS t2;
485: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
486: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
487: INSERT INTO t1 VALUES(1,2);
488: INSERT INTO t2 VALUES(-99,100);
489: INSERT INTO t2 VALUES(1,3);
490: SELECT * FROM t1;
491: }
492: catchsql {
493: BEGIN;
494: INSERT INTO t1 VALUES(2,3);
495: INSERT INTO t1 SELECT * FROM t2;
496: }
497: } {1 {PRIMARY KEY must be unique}}
498: do_test insert4-8.10 {
499: catchsql {COMMIT}
500: } {1 {cannot commit - no transaction is active}}
501: do_test insert4-8.11 {
502: execsql {
503: SELECT * FROM t1;
504: }
505: } {1 2}
506:
507: do_test insert4-8.21 {
508: execsql {
509: DROP TABLE IF EXISTS t1;
510: DROP TABLE IF EXISTS t2;
511: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
512: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
513: INSERT INTO t2 VALUES(1,3);
514: INSERT INTO t1 SELECT * FROM t2;
515: SELECT * FROM t1;
516: }
517: } {1 3}
518: do_test insert4-8.22 {
519: execsql {
520: DROP TABLE IF EXISTS t1;
521: DROP TABLE IF EXISTS t2;
522: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
523: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
524: INSERT INTO t2 VALUES(1,3);
525: INSERT INTO t1 SELECT * FROM t2;
526: SELECT * FROM t1;
527: }
528: } {1 3}
529: do_test insert4-8.23 {
530: execsql {
531: DROP TABLE IF EXISTS t1;
532: DROP TABLE IF EXISTS t2;
533: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
534: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
535: INSERT INTO t2 VALUES(1,3);
536: INSERT INTO t1 SELECT * FROM t2;
537: SELECT * FROM t1;
538: }
539: } {1 3}
540: do_test insert4-8.24 {
541: execsql {
542: DROP TABLE IF EXISTS t1;
543: DROP TABLE IF EXISTS t2;
544: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
545: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
546: INSERT INTO t2 VALUES(1,3);
547: INSERT INTO t1 SELECT * FROM t2;
548: SELECT * FROM t1;
549: }
550: } {1 3}
551: do_test insert4-8.25 {
552: execsql {
553: DROP TABLE IF EXISTS t1;
554: DROP TABLE IF EXISTS t2;
555: CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
556: CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
557: INSERT INTO t2 VALUES(1,3);
558: INSERT INTO t1 SELECT * FROM t2;
559: SELECT * FROM t1;
560: }
561: } {1 3}
562:
563:
564: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to insert4.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test