Annotation of embedaddon/sqlite3/test/check.test, revision 1.1
1.1 ! misho 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.13 2009/06/05 17:09:12 drh 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