Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add tests for CHECK and UNIQUE constraints to e_createtable.test. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
fb8db5581d884e5eb380480bc08d2106 |
User & Date: | dan 2010-10-01 19:04:37.000 |
Context
2010-10-04
| ||
10:45 | Add tests for table-constraints to e_createtable.test. (check-in: 70f511872e user: dan tags: trunk) | |
2010-10-01
| ||
19:04 | Add tests for CHECK and UNIQUE constraints to e_createtable.test. (check-in: fb8db5581d user: dan tags: trunk) | |
17:23 | Add #ifndef USE_SYSTEM_SQLITE...#endif around the bundled SQLite library for the TCL bindings. (check-in: 8d85584a4e user: drh tags: trunk) | |
Changes
Changes to test/e_createtable.test.
︙ | ︙ | |||
1278 1279 1280 1281 1282 1283 1284 | SELECT u FROM t3; } {1 2} do_catchsql_test 4.5.3 { INSERT INTO t3 VALUES(2, 5, 3); UPDATE t3 SET u = NULL WHERE s = 2; } {1 {datatype mismatch}} | > > > > > > > > > > > | > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 | SELECT u FROM t3; } {1 2} do_catchsql_test 4.5.3 { INSERT INTO t3 VALUES(2, 5, 3); UPDATE t3 SET u = NULL WHERE s = 2; } {1 {datatype mismatch}} # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY # KEY constraint, except that a single table may have any number of # UNIQUE constraints. # drop_all_tables do_createtable_tests 4.6 { 1 "CREATE TABLE t1(a UNIQUE, b UNIQUE)" {} 2 "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))" {} 3 "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {} 4 "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))" {} } # EVIDENCE-OF: R-55240-58877 For each UNIQUE constraint on the table, # each row must feature a unique combination of values in the columns # identified by the UNIQUE constraint. # # EVIDENCE-OF: R-47733-51480 If an INSERT or UPDATE statement attempts # to modify the table content so that two or more rows feature identical # values in a set of columns that are subject to a UNIQUE constraint, it # is a constraint violation. # do_execsql_test 4.7.0 { INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(4.3, 5.5); INSERT INTO t1 VALUES('reveal', 'variableness'); INSERT INTO t1 VALUES(X'123456', X'654321'); INSERT INTO t4 VALUES('xyx', 1, 1); INSERT INTO t4 VALUES('xyx', 2, 1); INSERT INTO t4 VALUES('uvw', 1, 1); } do_createtable_tests 4.7.1 -error { %s not unique } { 1 "INSERT INTO t1 VALUES(1, 'one')" {{column a is}} 2 "INSERT INTO t1 VALUES(4.3, 'two')" {{column a is}} 3 "INSERT INTO t1 VALUES('reveal', 'three')" {{column a is}} 4 "INSERT INTO t1 VALUES(X'123456', 'four')" {{column a is}} 5 "UPDATE t1 SET a = 1 WHERE rowid=2" {{column a is}} 6 "UPDATE t1 SET a = 4.3 WHERE rowid=3" {{column a is}} 7 "UPDATE t1 SET a = 'reveal' WHERE rowid=4" {{column a is}} 8 "UPDATE t1 SET a = X'123456' WHERE rowid=1" {{column a is}} 9 "INSERT INTO t4 VALUES('xyx', 1, 1)" {{columns a, b, c are}} 10 "INSERT INTO t4 VALUES('xyx', 2, 1)" {{columns a, b, c are}} 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{columns a, b, c are}} 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{columns a, b, c are}} 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{columns a, b, c are}} 14 "UPDATE t4 SET a=0, b=0, c=0" {{columns a, b, c are}} } # EVIDENCE-OF: R-21289-11559 As with PRIMARY KEY constraints, for the # purposes of UNIQUE constraints NULL values are considered distinct # from all other values (including other NULLs). # do_createtable_tests 4.8 { 1 "INSERT INTO t1 VALUES(NULL, NULL)" {} 2 "INSERT INTO t1 VALUES(NULL, NULL)" {} 3 "UPDATE t1 SET a = NULL" {} 4 "UPDATE t1 SET b = NULL" {} 5 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {} 6 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {} 7 "UPDATE t4 SET a = NULL" {} 8 "UPDATE t4 SET b = NULL" {} 9 "UPDATE t4 SET c = NULL" {} } # EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both # UNIQUE and PRIMARY KEY constraints are implemented by creating an # index in the database (in the same way as a "CREATE UNIQUE INDEX" # statement would). do_createtable_tests 4.9 -repair drop_all_tables -query { SELECT count(*) FROM sqlite_master WHERE type='index' } { 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1 2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0 3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2 } # EVIDENCE-OF: R-02252-33116 Such an index is used like any other index # in the database to optimize queries. # do_execsql_test 4.10.0 { CREATE TABLE t1(a, b PRIMARY KEY); CREATE TABLE t2(a, b, c, UNIQUE(b, c)); } do_createtable_tests 4.10 { 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" {0 0 {TABLE t1 WITH INDEX sqlite_autoindex_t1_1}} 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" {0 0 {TABLE t2 WITH INDEX sqlite_autoindex_t2_1 ORDER BY}} 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" {0 0 {TABLE t2 WITH INDEX sqlite_autoindex_t2_1}} } # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a # column definition or specified as a table constraint. In practice it # makes no difference. # # All the tests that deal with CHECK constraints below (4.11.* and # 4.12.*) are run once for a table with the check constraint attached # to a column definition, and once with a table where the check # condition is specified as a table constraint. # # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the # table or an existing row is updated, the expression associated with # each CHECK constraint is evaluated and cast to a NUMERIC value in the # same way as a CAST expression. If the result is zero (integer value 0 # or real value 0.0), then a constraint violation has occurred. # drop_all_tables do_execsql_test 4.11 { CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 )); CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 )); INSERT INTO x1 VALUES('x', 'xx'); INSERT INTO x1 VALUES('y', 'yy'); INSERT INTO t1 SELECT * FROM x1; CREATE TABLE x2(a CHECK( a||b ), b); CREATE TABLE t2(a, b, CHECK( a||b )); INSERT INTO x2 VALUES(1, 'xx'); INSERT INTO x2 VALUES(1, 'yy'); INSERT INTO t2 SELECT * FROM x2; } do_createtable_tests 4.11 -error {constraint failed} { 1a "INSERT INTO x1 VALUES('one', 0)" {} 1b "INSERT INTO t1 VALUES('one', -4.0)" {} 2a "INSERT INTO x2 VALUES('abc', 1)" {} 2b "INSERT INTO t2 VALUES('abc', 1)" {} 3a "INSERT INTO x2 VALUES(0, 'abc')" {} 3b "INSERT INTO t2 VALUES(0, 'abc')" {} 4a "UPDATE t1 SET b=-1 WHERE rowid=1" {} 4b "UPDATE x1 SET b=-1 WHERE rowid=1" {} 4a "UPDATE x2 SET a='' WHERE rowid=1" {} 4b "UPDATE t2 SET a='' WHERE rowid=1" {} } # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL, # or any other non-zero value, it is not a constraint violation. # do_createtable_tests 4.12 { 1a "INSERT INTO x1 VALUES('one', NULL)" {} 1b "INSERT INTO t1 VALUES('one', NULL)" {} 2a "INSERT INTO x1 VALUES('one', 2)" {} 2b "INSERT INTO t1 VALUES('one', 2)" {} 3a "INSERT INTO x2 VALUES(1, 'abc')" {} 3b "INSERT INTO t2 VALUES(1, 'abc')" {} } finish_test |
Changes to test/tester.tcl.
︙ | ︙ | |||
341 342 343 344 345 346 347 | if {[info exists ::testprefix] && [string is digit [string range $testname 0 0]] } { set testname "${::testprefix}-$testname" } } | | | 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 | if {[info exists ::testprefix] && [string is digit [string range $testname 0 0]] } { set testname "${::testprefix}-$testname" } } proc do_execsql_test {testname sql {result {}}} { fix_testname testname uplevel do_test $testname [list "execsql {$sql}"] [list $result] } proc do_catchsql_test {testname sql result} { fix_testname testname uplevel do_test $testname [list "catchsql {$sql}"] [list $result] } |
︙ | ︙ |