| OLD | NEW |
|---|
| (Empty) |
| 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 set ::testprefix check | |
| 19 | |
| 20 # Only run these tests if the build includes support for CHECK constraints | |
| 21 ifcapable !check { | |
| 22 finish_test | |
| 23 return | |
| 24 } | |
| 25 | |
| 26 do_test check-1.1 { | |
| 27 execsql { | |
| 28 CREATE TABLE t1( | |
| 29 x INTEGER CHECK( x<5 ), | |
| 30 y REAL CHECK( y>x ) | |
| 31 ); | |
| 32 } | |
| 33 } {} | |
| 34 do_test check-1.2 { | |
| 35 execsql { | |
| 36 INSERT INTO t1 VALUES(3,4); | |
| 37 SELECT * FROM t1; | |
| 38 } | |
| 39 } {3 4.0} | |
| 40 do_test check-1.3 { | |
| 41 catchsql { | |
| 42 INSERT INTO t1 VALUES(6,7); | |
| 43 } | |
| 44 } {1 {CHECK constraint failed: t1}} | |
| 45 do_test check-1.4 { | |
| 46 execsql { | |
| 47 SELECT * FROM t1; | |
| 48 } | |
| 49 } {3 4.0} | |
| 50 do_test check-1.5 { | |
| 51 catchsql { | |
| 52 INSERT INTO t1 VALUES(4,3); | |
| 53 } | |
| 54 } {1 {CHECK constraint failed: t1}} | |
| 55 do_test check-1.6 { | |
| 56 execsql { | |
| 57 SELECT * FROM t1; | |
| 58 } | |
| 59 } {3 4.0} | |
| 60 do_test check-1.7 { | |
| 61 catchsql { | |
| 62 INSERT INTO t1 VALUES(NULL,6); | |
| 63 } | |
| 64 } {0 {}} | |
| 65 do_test check-1.8 { | |
| 66 execsql { | |
| 67 SELECT * FROM t1; | |
| 68 } | |
| 69 } {3 4.0 {} 6.0} | |
| 70 do_test check-1.9 { | |
| 71 catchsql { | |
| 72 INSERT INTO t1 VALUES(2,NULL); | |
| 73 } | |
| 74 } {0 {}} | |
| 75 do_test check-1.10 { | |
| 76 execsql { | |
| 77 SELECT * FROM t1; | |
| 78 } | |
| 79 } {3 4.0 {} 6.0 2 {}} | |
| 80 do_test check-1.11 { | |
| 81 execsql { | |
| 82 DELETE FROM t1 WHERE x IS NULL OR x!=3; | |
| 83 UPDATE t1 SET x=2 WHERE x==3; | |
| 84 SELECT * FROM t1; | |
| 85 } | |
| 86 } {2 4.0} | |
| 87 do_test check-1.12 { | |
| 88 catchsql { | |
| 89 UPDATE t1 SET x=7 WHERE x==2 | |
| 90 } | |
| 91 } {1 {CHECK constraint failed: t1}} | |
| 92 do_test check-1.13 { | |
| 93 execsql { | |
| 94 SELECT * FROM t1; | |
| 95 } | |
| 96 } {2 4.0} | |
| 97 do_test check-1.14 { | |
| 98 catchsql { | |
| 99 UPDATE t1 SET x=5 WHERE x==2 | |
| 100 } | |
| 101 } {1 {CHECK constraint failed: t1}} | |
| 102 do_test check-1.15 { | |
| 103 execsql { | |
| 104 SELECT * FROM t1; | |
| 105 } | |
| 106 } {2 4.0} | |
| 107 do_test check-1.16 { | |
| 108 catchsql { | |
| 109 UPDATE t1 SET x=4, y=11 WHERE x==2 | |
| 110 } | |
| 111 } {0 {}} | |
| 112 do_test check-1.17 { | |
| 113 execsql { | |
| 114 SELECT * FROM t1; | |
| 115 } | |
| 116 } {4 11.0} | |
| 117 | |
| 118 do_test check-2.1 { | |
| 119 execsql { | |
| 120 CREATE TABLE t2( | |
| 121 x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ), | |
| 122 y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ), | |
| 123 z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' ) | |
| 124 ); | |
| 125 } | |
| 126 } {} | |
| 127 do_test check-2.2 { | |
| 128 execsql { | |
| 129 INSERT INTO t2 VALUES(1,2.2,'three'); | |
| 130 SELECT * FROM t2; | |
| 131 } | |
| 132 } {1 2.2 three} | |
| 133 db close | |
| 134 sqlite3 db test.db | |
| 135 do_test check-2.3 { | |
| 136 execsql { | |
| 137 INSERT INTO t2 VALUES(NULL, NULL, NULL); | |
| 138 SELECT * FROM t2; | |
| 139 } | |
| 140 } {1 2.2 three {} {} {}} | |
| 141 do_test check-2.4 { | |
| 142 catchsql { | |
| 143 INSERT INTO t2 VALUES(1.1, NULL, NULL); | |
| 144 } | |
| 145 } {1 {CHECK constraint failed: one}} | |
| 146 do_test check-2.5 { | |
| 147 catchsql { | |
| 148 INSERT INTO t2 VALUES(NULL, 5, NULL); | |
| 149 } | |
| 150 } {1 {CHECK constraint failed: two}} | |
| 151 do_test check-2.6 { | |
| 152 catchsql { | |
| 153 INSERT INTO t2 VALUES(NULL, NULL, 3.14159); | |
| 154 } | |
| 155 } {1 {CHECK constraint failed: three}} | |
| 156 | |
| 157 # Undocumented behavior: The CONSTRAINT name clause can follow a constraint. | |
| 158 # Such a clause is ignored. But the parser must accept it for backwards | |
| 159 # compatibility. | |
| 160 # | |
| 161 do_test check-2.10 { | |
| 162 execsql { | |
| 163 CREATE TABLE t2b( | |
| 164 x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one, | |
| 165 y TEXT PRIMARY KEY constraint two, | |
| 166 z INTEGER, | |
| 167 UNIQUE(x,z) constraint three | |
| 168 ); | |
| 169 } | |
| 170 } {} | |
| 171 do_test check-2.11 { | |
| 172 catchsql { | |
| 173 INSERT INTO t2b VALUES('xyzzy','hi',5); | |
| 174 } | |
| 175 } {1 {CHECK constraint failed: t2b}} | |
| 176 do_test check-2.12 { | |
| 177 execsql { | |
| 178 CREATE TABLE t2c( | |
| 179 x INTEGER CONSTRAINT x_one CONSTRAINT x_two | |
| 180 CHECK( typeof(coalesce(x,0))=='integer' ) | |
| 181 CONSTRAINT x_two CONSTRAINT x_three, | |
| 182 y INTEGER, z INTEGER, | |
| 183 CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two | |
| 184 ); | |
| 185 } | |
| 186 } {} | |
| 187 do_test check-2.13 { | |
| 188 catchsql { | |
| 189 INSERT INTO t2c VALUES('xyzzy',7,8); | |
| 190 } | |
| 191 } {1 {CHECK constraint failed: x_two}} | |
| 192 do_test check-2.cleanup { | |
| 193 execsql { | |
| 194 DROP TABLE IF EXISTS t2b; | |
| 195 DROP TABLE IF EXISTS t2c; | |
| 196 } | |
| 197 } {} | |
| 198 | |
| 199 ifcapable subquery { | |
| 200 do_test check-3.1 { | |
| 201 catchsql { | |
| 202 CREATE TABLE t3( | |
| 203 x, y, z, | |
| 204 CHECK( x<(SELECT min(x) FROM t1) ) | |
| 205 ); | |
| 206 } | |
| 207 } {1 {subqueries prohibited in CHECK constraints}} | |
| 208 } | |
| 209 | |
| 210 do_test check-3.2 { | |
| 211 execsql { | |
| 212 SELECT name FROM sqlite_master ORDER BY name | |
| 213 } | |
| 214 } {t1 t2} | |
| 215 do_test check-3.3 { | |
| 216 catchsql { | |
| 217 CREATE TABLE t3( | |
| 218 x, y, z, | |
| 219 CHECK( q<x ) | |
| 220 ); | |
| 221 } | |
| 222 } {1 {no such column: q}} | |
| 223 do_test check-3.4 { | |
| 224 execsql { | |
| 225 SELECT name FROM sqlite_master ORDER BY name | |
| 226 } | |
| 227 } {t1 t2} | |
| 228 do_test check-3.5 { | |
| 229 catchsql { | |
| 230 CREATE TABLE t3( | |
| 231 x, y, z, | |
| 232 CHECK( t2.x<x ) | |
| 233 ); | |
| 234 } | |
| 235 } {1 {no such column: t2.x}} | |
| 236 do_test check-3.6 { | |
| 237 execsql { | |
| 238 SELECT name FROM sqlite_master ORDER BY name | |
| 239 } | |
| 240 } {t1 t2} | |
| 241 do_test check-3.7 { | |
| 242 catchsql { | |
| 243 CREATE TABLE t3( | |
| 244 x, y, z, | |
| 245 CHECK( t3.x<25 ) | |
| 246 ); | |
| 247 } | |
| 248 } {0 {}} | |
| 249 do_test check-3.8 { | |
| 250 execsql { | |
| 251 INSERT INTO t3 VALUES(1,2,3); | |
| 252 SELECT * FROM t3; | |
| 253 } | |
| 254 } {1 2 3} | |
| 255 do_test check-3.9 { | |
| 256 catchsql { | |
| 257 INSERT INTO t3 VALUES(111,222,333); | |
| 258 } | |
| 259 } {1 {CHECK constraint failed: t3}} | |
| 260 | |
| 261 do_test check-4.1 { | |
| 262 execsql { | |
| 263 CREATE TABLE t4(x, y, | |
| 264 CHECK ( | |
| 265 x+y==11 | |
| 266 OR x*y==12 | |
| 267 OR x/y BETWEEN 5 AND 8 | |
| 268 OR -x==y+10 | |
| 269 ) | |
| 270 ); | |
| 271 } | |
| 272 } {} | |
| 273 do_test check-4.2 { | |
| 274 execsql { | |
| 275 INSERT INTO t4 VALUES(1,10); | |
| 276 SELECT * FROM t4 | |
| 277 } | |
| 278 } {1 10} | |
| 279 do_test check-4.3 { | |
| 280 execsql { | |
| 281 UPDATE t4 SET x=4, y=3; | |
| 282 SELECT * FROM t4 | |
| 283 } | |
| 284 } {4 3} | |
| 285 do_test check-4.4 { | |
| 286 execsql { | |
| 287 UPDATE t4 SET x=12, y=2; | |
| 288 SELECT * FROM t4 | |
| 289 } | |
| 290 } {12 2} | |
| 291 do_test check-4.5 { | |
| 292 execsql { | |
| 293 UPDATE t4 SET x=12, y=-22; | |
| 294 SELECT * FROM t4 | |
| 295 } | |
| 296 } {12 -22} | |
| 297 do_test check-4.6 { | |
| 298 catchsql { | |
| 299 UPDATE t4 SET x=0, y=1; | |
| 300 } | |
| 301 } {1 {CHECK constraint failed: t4}} | |
| 302 do_test check-4.7 { | |
| 303 execsql { | |
| 304 SELECT * FROM t4; | |
| 305 } | |
| 306 } {12 -22} | |
| 307 do_test check-4.8 { | |
| 308 execsql { | |
| 309 PRAGMA ignore_check_constraints=ON; | |
| 310 UPDATE t4 SET x=0, y=1; | |
| 311 SELECT * FROM t4; | |
| 312 } | |
| 313 } {0 1} | |
| 314 do_test check-4.9 { | |
| 315 catchsql { | |
| 316 PRAGMA ignore_check_constraints=OFF; | |
| 317 UPDATE t4 SET x=0, y=2; | |
| 318 } | |
| 319 } {1 {CHECK constraint failed: t4}} | |
| 320 ifcapable vacuum { | |
| 321 do_test check_4.10 { | |
| 322 catchsql { | |
| 323 VACUUM | |
| 324 } | |
| 325 } {0 {}} | |
| 326 } | |
| 327 | |
| 328 do_test check-5.1 { | |
| 329 catchsql { | |
| 330 CREATE TABLE t5(x, y, | |
| 331 CHECK( x*y<:abc ) | |
| 332 ); | |
| 333 } | |
| 334 } {1 {parameters prohibited in CHECK constraints}} | |
| 335 do_test check-5.2 { | |
| 336 catchsql { | |
| 337 CREATE TABLE t5(x, y, | |
| 338 CHECK( x*y<? ) | |
| 339 ); | |
| 340 } | |
| 341 } {1 {parameters prohibited in CHECK constraints}} | |
| 342 | |
| 343 ifcapable conflict { | |
| 344 | |
| 345 do_test check-6.1 { | |
| 346 execsql {SELECT * FROM t1} | |
| 347 } {4 11.0} | |
| 348 do_test check-6.2 { | |
| 349 execsql { | |
| 350 UPDATE OR IGNORE t1 SET x=5; | |
| 351 SELECT * FROM t1; | |
| 352 } | |
| 353 } {4 11.0} | |
| 354 do_test check-6.3 { | |
| 355 execsql { | |
| 356 INSERT OR IGNORE INTO t1 VALUES(5,4.0); | |
| 357 SELECT * FROM t1; | |
| 358 } | |
| 359 } {4 11.0} | |
| 360 do_test check-6.4 { | |
| 361 execsql { | |
| 362 INSERT OR IGNORE INTO t1 VALUES(2,20.0); | |
| 363 SELECT * FROM t1; | |
| 364 } | |
| 365 } {4 11.0 2 20.0} | |
| 366 do_test check-6.5 { | |
| 367 catchsql { | |
| 368 UPDATE OR FAIL t1 SET x=7-x, y=y+1; | |
| 369 } | |
| 370 } {1 {CHECK constraint failed: t1}} | |
| 371 do_test check-6.6 { | |
| 372 execsql { | |
| 373 SELECT * FROM t1; | |
| 374 } | |
| 375 } {3 12.0 2 20.0} | |
| 376 do_test check-6.7 { | |
| 377 catchsql { | |
| 378 BEGIN; | |
| 379 INSERT INTO t1 VALUES(1,30.0); | |
| 380 INSERT OR ROLLBACK INTO t1 VALUES(8,40.0); | |
| 381 } | |
| 382 } {1 {CHECK constraint failed: t1}} | |
| 383 do_test check-6.8 { | |
| 384 catchsql { | |
| 385 COMMIT; | |
| 386 } | |
| 387 } {1 {cannot commit - no transaction is active}} | |
| 388 do_test check-6.9 { | |
| 389 execsql { | |
| 390 SELECT * FROM t1 | |
| 391 } | |
| 392 } {3 12.0 2 20.0} | |
| 393 | |
| 394 do_test check-6.11 { | |
| 395 execsql {SELECT * FROM t1} | |
| 396 } {3 12.0 2 20.0} | |
| 397 do_test check-6.12 { | |
| 398 catchsql { | |
| 399 REPLACE INTO t1 VALUES(6,7); | |
| 400 } | |
| 401 } {1 {CHECK constraint failed: t1}} | |
| 402 do_test check-6.13 { | |
| 403 execsql {SELECT * FROM t1} | |
| 404 } {3 12.0 2 20.0} | |
| 405 do_test check-6.14 { | |
| 406 catchsql { | |
| 407 INSERT OR IGNORE INTO t1 VALUES(6,7); | |
| 408 } | |
| 409 } {0 {}} | |
| 410 do_test check-6.15 { | |
| 411 execsql {SELECT * FROM t1} | |
| 412 } {3 12.0 2 20.0} | |
| 413 | |
| 414 | |
| 415 } | |
| 416 | |
| 417 #-------------------------------------------------------------------------- | |
| 418 # If a connection opens a database that contains a CHECK constraint that | |
| 419 # uses an unknown UDF, the schema should not be considered malformed. | |
| 420 # Attempting to modify the table should fail (since the CHECK constraint | |
| 421 # cannot be tested). | |
| 422 # | |
| 423 reset_db | |
| 424 proc myfunc {x} {expr $x < 10} | |
| 425 db func myfunc myfunc | |
| 426 | |
| 427 do_execsql_test 7.1 { CREATE TABLE t6(a CHECK (myfunc(a))) } | |
| 428 do_execsql_test 7.2 { INSERT INTO t6 VALUES(9) } | |
| 429 do_catchsql_test 7.3 { INSERT INTO t6 VALUES(11) } \ | |
| 430 {1 {CHECK constraint failed: t6}} | |
| 431 | |
| 432 do_test 7.4 { | |
| 433 sqlite3 db2 test.db | |
| 434 execsql { SELECT * FROM t6 } db2 | |
| 435 } {9} | |
| 436 | |
| 437 do_test 7.5 { | |
| 438 catchsql { INSERT INTO t6 VALUES(8) } db2 | |
| 439 } {1 {unknown function: myfunc()}} | |
| 440 | |
| 441 do_test 7.6 { | |
| 442 catchsql { CREATE TABLE t7(a CHECK (myfunc(a))) } db2 | |
| 443 } {1 {no such function: myfunc}} | |
| 444 | |
| 445 do_test 7.7 { | |
| 446 db2 func myfunc myfunc | |
| 447 execsql { INSERT INTO t6 VALUES(8) } db2 | |
| 448 } {} | |
| 449 | |
| 450 do_test 7.8 { | |
| 451 db2 func myfunc myfunc | |
| 452 catchsql { INSERT INTO t6 VALUES(12) } db2 | |
| 453 } {1 {CHECK constraint failed: t6}} | |
| 454 | |
| 455 # 2013-08-02: Silently ignore database name qualifiers in CHECK constraints. | |
| 456 # | |
| 457 do_execsql_test 8.1 { | |
| 458 CREATE TABLE t810(a, CHECK( main.t810.a>0 )); | |
| 459 CREATE TABLE t811(b, CHECK( xyzzy.t811.b BETWEEN 5 AND 10 )); | |
| 460 } {} | |
| 461 | |
| 462 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
