1: # 2005 November 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 file is testing CHECK constraints
13: #
14: # $Id: check.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: # Only run these tests if the build includes support for CHECK constraints
20: ifcapable !check {
21: finish_test
22: return
23: }
24:
25: do_test check-1.1 {
26: execsql {
27: CREATE TABLE t1(
28: x INTEGER CHECK( x<5 ),
29: y REAL CHECK( y>x )
30: );
31: }
32: } {}
33: do_test check-1.2 {
34: execsql {
35: INSERT INTO t1 VALUES(3,4);
36: SELECT * FROM t1;
37: }
38: } {3 4.0}
39: do_test check-1.3 {
40: catchsql {
41: INSERT INTO t1 VALUES(6,7);
42: }
43: } {1 {constraint failed}}
44: do_test check-1.4 {
45: execsql {
46: SELECT * FROM t1;
47: }
48: } {3 4.0}
49: do_test check-1.5 {
50: catchsql {
51: INSERT INTO t1 VALUES(4,3);
52: }
53: } {1 {constraint failed}}
54: do_test check-1.6 {
55: execsql {
56: SELECT * FROM t1;
57: }
58: } {3 4.0}
59: do_test check-1.7 {
60: catchsql {
61: INSERT INTO t1 VALUES(NULL,6);
62: }
63: } {0 {}}
64: do_test check-1.8 {
65: execsql {
66: SELECT * FROM t1;
67: }
68: } {3 4.0 {} 6.0}
69: do_test check-1.9 {
70: catchsql {
71: INSERT INTO t1 VALUES(2,NULL);
72: }
73: } {0 {}}
74: do_test check-1.10 {
75: execsql {
76: SELECT * FROM t1;
77: }
78: } {3 4.0 {} 6.0 2 {}}
79: do_test check-1.11 {
80: execsql {
81: DELETE FROM t1 WHERE x IS NULL OR x!=3;
82: UPDATE t1 SET x=2 WHERE x==3;
83: SELECT * FROM t1;
84: }
85: } {2 4.0}
86: do_test check-1.12 {
87: catchsql {
88: UPDATE t1 SET x=7 WHERE x==2
89: }
90: } {1 {constraint failed}}
91: do_test check-1.13 {
92: execsql {
93: SELECT * FROM t1;
94: }
95: } {2 4.0}
96: do_test check-1.14 {
97: catchsql {
98: UPDATE t1 SET x=5 WHERE x==2
99: }
100: } {1 {constraint failed}}
101: do_test check-1.15 {
102: execsql {
103: SELECT * FROM t1;
104: }
105: } {2 4.0}
106: do_test check-1.16 {
107: catchsql {
108: UPDATE t1 SET x=4, y=11 WHERE x==2
109: }
110: } {0 {}}
111: do_test check-1.17 {
112: execsql {
113: SELECT * FROM t1;
114: }
115: } {4 11.0}
116:
117: do_test check-2.1 {
118: execsql {
119: CREATE TABLE t2(
120: x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ),
121: y REAL CHECK( typeof(coalesce(y,0.1))=='real' ),
122: z TEXT CHECK( typeof(coalesce(z,''))=='text' )
123: );
124: }
125: } {}
126: do_test check-2.2 {
127: execsql {
128: INSERT INTO t2 VALUES(1,2.2,'three');
129: SELECT * FROM t2;
130: }
131: } {1 2.2 three}
132: db close
133: sqlite3 db test.db
134: do_test check-2.3 {
135: execsql {
136: INSERT INTO t2 VALUES(NULL, NULL, NULL);
137: SELECT * FROM t2;
138: }
139: } {1 2.2 three {} {} {}}
140: do_test check-2.4 {
141: catchsql {
142: INSERT INTO t2 VALUES(1.1, NULL, NULL);
143: }
144: } {1 {constraint failed}}
145: do_test check-2.5 {
146: catchsql {
147: INSERT INTO t2 VALUES(NULL, 5, NULL);
148: }
149: } {1 {constraint failed}}
150: do_test check-2.6 {
151: catchsql {
152: INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
153: }
154: } {1 {constraint failed}}
155:
156: ifcapable subquery {
157: do_test check-3.1 {
158: catchsql {
159: CREATE TABLE t3(
160: x, y, z,
161: CHECK( x<(SELECT min(x) FROM t1) )
162: );
163: }
164: } {1 {subqueries prohibited in CHECK constraints}}
165: }
166:
167: do_test check-3.2 {
168: execsql {
169: SELECT name FROM sqlite_master ORDER BY name
170: }
171: } {t1 t2}
172: do_test check-3.3 {
173: catchsql {
174: CREATE TABLE t3(
175: x, y, z,
176: CHECK( q<x )
177: );
178: }
179: } {1 {no such column: q}}
180: do_test check-3.4 {
181: execsql {
182: SELECT name FROM sqlite_master ORDER BY name
183: }
184: } {t1 t2}
185: do_test check-3.5 {
186: catchsql {
187: CREATE TABLE t3(
188: x, y, z,
189: CHECK( t2.x<x )
190: );
191: }
192: } {1 {no such column: t2.x}}
193: do_test check-3.6 {
194: execsql {
195: SELECT name FROM sqlite_master ORDER BY name
196: }
197: } {t1 t2}
198: do_test check-3.7 {
199: catchsql {
200: CREATE TABLE t3(
201: x, y, z,
202: CHECK( t3.x<25 )
203: );
204: }
205: } {0 {}}
206: do_test check-3.8 {
207: execsql {
208: INSERT INTO t3 VALUES(1,2,3);
209: SELECT * FROM t3;
210: }
211: } {1 2 3}
212: do_test check-3.9 {
213: catchsql {
214: INSERT INTO t3 VALUES(111,222,333);
215: }
216: } {1 {constraint failed}}
217:
218: do_test check-4.1 {
219: execsql {
220: CREATE TABLE t4(x, y,
221: CHECK (
222: x+y==11
223: OR x*y==12
224: OR x/y BETWEEN 5 AND 8
225: OR -x==y+10
226: )
227: );
228: }
229: } {}
230: do_test check-4.2 {
231: execsql {
232: INSERT INTO t4 VALUES(1,10);
233: SELECT * FROM t4
234: }
235: } {1 10}
236: do_test check-4.3 {
237: execsql {
238: UPDATE t4 SET x=4, y=3;
239: SELECT * FROM t4
240: }
241: } {4 3}
242: do_test check-4.4 {
243: execsql {
244: UPDATE t4 SET x=12, y=2;
245: SELECT * FROM t4
246: }
247: } {12 2}
248: do_test check-4.5 {
249: execsql {
250: UPDATE t4 SET x=12, y=-22;
251: SELECT * FROM t4
252: }
253: } {12 -22}
254: do_test check-4.6 {
255: catchsql {
256: UPDATE t4 SET x=0, y=1;
257: }
258: } {1 {constraint failed}}
259: do_test check-4.7 {
260: execsql {
261: SELECT * FROM t4;
262: }
263: } {12 -22}
264: do_test check-4.8 {
265: execsql {
266: PRAGMA ignore_check_constraints=ON;
267: UPDATE t4 SET x=0, y=1;
268: SELECT * FROM t4;
269: }
270: } {0 1}
271: do_test check-4.9 {
272: catchsql {
273: PRAGMA ignore_check_constraints=OFF;
274: UPDATE t4 SET x=0, y=2;
275: }
276: } {1 {constraint failed}}
277: ifcapable vacuum {
278: do_test check_4.10 {
279: catchsql {
280: VACUUM
281: }
282: } {0 {}}
283: }
284:
285: do_test check-5.1 {
286: catchsql {
287: CREATE TABLE t5(x, y,
288: CHECK( x*y<:abc )
289: );
290: }
291: } {1 {parameters prohibited in CHECK constraints}}
292: do_test check-5.2 {
293: catchsql {
294: CREATE TABLE t5(x, y,
295: CHECK( x*y<? )
296: );
297: }
298: } {1 {parameters prohibited in CHECK constraints}}
299:
300: ifcapable conflict {
301:
302: do_test check-6.1 {
303: execsql {SELECT * FROM t1}
304: } {4 11.0}
305: do_test check-6.2 {
306: execsql {
307: UPDATE OR IGNORE t1 SET x=5;
308: SELECT * FROM t1;
309: }
310: } {4 11.0}
311: do_test check-6.3 {
312: execsql {
313: INSERT OR IGNORE INTO t1 VALUES(5,4.0);
314: SELECT * FROM t1;
315: }
316: } {4 11.0}
317: do_test check-6.4 {
318: execsql {
319: INSERT OR IGNORE INTO t1 VALUES(2,20.0);
320: SELECT * FROM t1;
321: }
322: } {4 11.0 2 20.0}
323: do_test check-6.5 {
324: catchsql {
325: UPDATE OR FAIL t1 SET x=7-x, y=y+1;
326: }
327: } {1 {constraint failed}}
328: do_test check-6.6 {
329: execsql {
330: SELECT * FROM t1;
331: }
332: } {3 12.0 2 20.0}
333: do_test check-6.7 {
334: catchsql {
335: BEGIN;
336: INSERT INTO t1 VALUES(1,30.0);
337: INSERT OR ROLLBACK INTO t1 VALUES(8,40.0);
338: }
339: } {1 {constraint failed}}
340: do_test check-6.8 {
341: catchsql {
342: COMMIT;
343: }
344: } {1 {cannot commit - no transaction is active}}
345: do_test check-6.9 {
346: execsql {
347: SELECT * FROM t1
348: }
349: } {3 12.0 2 20.0}
350:
351: do_test check-6.11 {
352: execsql {SELECT * FROM t1}
353: } {3 12.0 2 20.0}
354: do_test check-6.12 {
355: catchsql {
356: REPLACE INTO t1 VALUES(6,7);
357: }
358: } {1 {constraint failed}}
359: do_test check-6.13 {
360: execsql {SELECT * FROM t1}
361: } {3 12.0 2 20.0}
362: do_test check-6.14 {
363: catchsql {
364: INSERT OR IGNORE INTO t1 VALUES(6,7);
365: }
366: } {0 {}}
367: do_test check-6.15 {
368: execsql {SELECT * FROM t1}
369: } {3 12.0 2 20.0}
370:
371:
372: }
373:
374: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to check.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