Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Updates to requirements marks. No changes to code. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
8a0366285b94dc43d932736e7b1eedb7 |
| User & Date: | drh 2013-11-09 19:47:15 |
Context
|
2013-11-09
| ||
| 21:19 | Updates to documentation on sqlite3_last_insert_rowid(). No changes to code. check-in: a4c5804e user: drh tags: trunk | |
| 19:47 | Updates to requirements marks. No changes to code. check-in: 8a036628 user: drh tags: trunk | |
| 18:15 | Throw an error if AUTOINCREMENT appears in a WITHOUT ROWID table. Updates to API documentation to discuss WITHOUT ROWID. check-in: b1abb2b0 user: drh tags: trunk | |
Changes
Changes to src/func.c.
134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
assert( argc==1 );
UNUSED_PARAMETER(argc);
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
i64 iVal = sqlite3_value_int64(argv[0]);
if( iVal<0 ){
if( (iVal<<1)==0 ){
/* IMP: R-35460-15084 If X is the integer -9223372036854775807 then
** abs(X) throws an integer overflow error since there is no
** equivalent positive 64-bit two complement value. */
sqlite3_result_error(context, "integer overflow", -1);
return;
}
iVal = -iVal;
}
sqlite3_result_int64(context, iVal);
|
| | |
134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
assert( argc==1 );
UNUSED_PARAMETER(argc);
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
i64 iVal = sqlite3_value_int64(argv[0]);
if( iVal<0 ){
if( (iVal<<1)==0 ){
/* IMP: R-31676-45509 If X is the integer -9223372036854775808
** then abs(X) throws an integer overflow error since there is no
** equivalent positive 64-bit two complement value. */
sqlite3_result_error(context, "integer overflow", -1);
return;
}
iVal = -iVal;
}
sqlite3_result_int64(context, iVal);
|
Changes to test/e_createtable.test.
1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 .... 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 .... 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 .... 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 .... 1271 1272 1273 1274 1275 1276 1277 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 .... 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 .... 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 |
# Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT # NULL and CHECK constraints). # # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one # PRIMARY KEY. # # EVIDENCE-OF: R-18080-47271 If there is more than one PRIMARY KEY # clause in a single CREATE TABLE statement, it is an error. # # To test the two above, show that zero primary keys is Ok, one primary # key is Ok, and two or more primary keys is an error. # drop_all_tables do_createtable_tests 4.1.1 { 1 "CREATE TABLE t1(a, b, c)" {} ................................................................................ 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {} 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {} 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {} 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {} 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {} 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {} } proc table_pk {tbl} { set pk [list] db eval "pragma table_info($tbl)" a { if {$a(pk)} { lappend pk $a(name) } } set pk ................................................................................ 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a} 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a} 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c} 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b} } # EVIDENCE-OF: R-33986-09410 Each row in a table with a primary key must # feature a unique combination of values in its primary key columns. # # EVIDENCE-OF: R-39102-06737 If an INSERT or UPDATE statement attempts # to modify the table content so that two or more rows feature identical # primary key values, it is a constraint violation. # drop_all_tables do_execsql_test 4.3.0 { ................................................................................ 11 "INSERT INTO t2 VALUES('brambles', NULL)" {} 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {} 13 "INSERT INTO t2 VALUES(NULL, NULL)" {} 14 "INSERT INTO t2 VALUES(NULL, NULL)" {} } # EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY # SQLite allows NULL values in a PRIMARY KEY column. # # If the column is an integer primary key, attempting to insert a NULL # into the column triggers the auto-increment behavior. Attempting # to use UPDATE to set an ipk column to a NULL value is an error. # do_createtable_tests 4.5.1 { 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3 ................................................................................ INSERT INTO t3 VALUES('x', NULL, 'y'); 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'); ................................................................................ 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{t4.a, t4.b, t4.c}} 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{t4.a, t4.b, t4.c}} 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{t4.a, t4.b, t4.c}} 14 "UPDATE t4 SET a=0, b=0, c=0" {{t4.a, t4.b, t4.c}} } # 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 { ................................................................................ DELETE FROM $tbl; INSERT INTO $tbl ($col) VALUES(0); SELECT (rowid==$col) FROM $tbl; DELETE FROM $tbl; }]] 0 } # EVIDENCE-OF: R-53738-31673 With one exception, if a table has a # primary key that consists of a single column, and the declared type of # that column is "INTEGER" in any mixture of upper and lower case, then # the column becomes an alias for the rowid. # # EVIDENCE-OF: R-45951-08347 if the declaration of a column with # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does # not become an alias for the rowid and is not classified as an integer # primary key. # do_createtable_tests 5.3 -tclquery { |
| | > > > > > > > > > > > | | | > | > > > > > > > > | | | | < < | | | | | | < | | | | | |
1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 .... 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 .... 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 .... 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 .... 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 .... 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 .... 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 |
# Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT # NULL and CHECK constraints). # # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one # PRIMARY KEY. # # EVIDENCE-OF: R-62315-57691 An error is rasied if more than one PRIMARY # KEY clause appears in a CREATE TABLE statement. # # To test the two above, show that zero primary keys is Ok, one primary # key is Ok, and two or more primary keys is an error. # drop_all_tables do_createtable_tests 4.1.1 { 1 "CREATE TABLE t1(a, b, c)" {} ................................................................................ 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {} 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {} 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {} 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {} 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {} 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {} } # EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary # tables but is required for WITHOUT ROWID tables. # do_catchsql_test 4.1.3 { CREATE TABLE t6(a, b); --ok } {0 {}} do_catchsql_test 4.1.4 { CREATE TABLE t7(a, b) WITHOUT ROWID; --Error, no PRIMARY KEY } {1 {PRIMARY KEY missing on table t7}} proc table_pk {tbl} { set pk [list] db eval "pragma table_info($tbl)" a { if {$a(pk)} { lappend pk $a(name) } } set pk ................................................................................ 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a} 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a} 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c} 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b} } # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must # have a unique combination of values in its primary key columns. # # EVIDENCE-OF: R-39102-06737 If an INSERT or UPDATE statement attempts # to modify the table content so that two or more rows feature identical # primary key values, it is a constraint violation. # drop_all_tables do_execsql_test 4.3.0 { ................................................................................ 11 "INSERT INTO t2 VALUES('brambles', NULL)" {} 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {} 13 "INSERT INTO t2 VALUES(NULL, NULL)" {} 14 "INSERT INTO t2 VALUES(NULL, NULL)" {} } # EVIDENCE-OF: R-35113-43214 Unless the column is an INTEGER PRIMARY KEY # or the table is a WITHOUT ROWID table or the column is declared NOT # NULL, SQLite allows NULL values in a PRIMARY KEY column. # # If the column is an integer primary key, attempting to insert a NULL # into the column triggers the auto-increment behavior. Attempting # to use UPDATE to set an ipk column to a NULL value is an error. # do_createtable_tests 4.5.1 { 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3 ................................................................................ INSERT INTO t3 VALUES('x', NULL, 'y'); 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}} do_catchsql_test 4.5.4 { CREATE TABLE t4(s, u INT PRIMARY KEY, v) WITHOUT ROWID; INSERT INTO t4 VALUES(1, NULL, 2); } {1 {NOT NULL constraint failed: t4.u}} do_catchsql_test 4.5.5 { CREATE TABLE t5(s, u INT PRIMARY KEY NOT NULL, v); INSERT INTO t5 VALUES(1, NULL, 2); } {1 {NOT NULL constraint failed: t5.u}} # 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-30981-64168 For each UNIQUE constraint on the table, # each row must contain a unique combination of values in the columns # identified by the UNIQUE constraint. # # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must # have a unique combination of values in its primary key columns. # 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'); ................................................................................ 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{t4.a, t4.b, t4.c}} 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{t4.a, t4.b, t4.c}} 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{t4.a, t4.b, t4.c}} 14 "UPDATE t4 SET a=0, b=0, c=0" {{t4.a, t4.b, t4.c}} } # EVIDENCE-OF: R-00404-17670 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-55820-29984 In most cases, UNIQUE and PRIMARY KEY # constraints are implemented by creating a unique index in the # database. 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 } # Obsolete: 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 { ................................................................................ DELETE FROM $tbl; INSERT INTO $tbl ($col) VALUES(0); SELECT (rowid==$col) FROM $tbl; DELETE FROM $tbl; }]] 0 } # EVIDENCE-OF: R-47901-33947 With one exception noted below, if a rowid # table has a primary key that consists of a single column and the # declared type of that column is "INTEGER" in any mixture of upper and # lower case, then the column becomes an alias for the rowid. # # EVIDENCE-OF: R-45951-08347 if the declaration of a column with # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does # not become an alias for the rowid and is not classified as an integer # primary key. # do_createtable_tests 5.3 -tclquery { |