Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to requirements marks. No changes to code. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
8a0366285b94dc43d932736e7b1eedb7 |
User & Date: | drh 2013-11-09 19:47:15.808 |
Context
2013-11-09
| ||
21:19 | Updates to documentation on sqlite3_last_insert_rowid(). No changes to code. (check-in: a4c5804efc user: drh tags: trunk) | |
19:47 | Updates to requirements marks. No changes to code. (check-in: 8a0366285b 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: b1abb2b078 user: drh tags: trunk) | |
Changes
Changes to src/func.c.
︙ | ︙ | |||
134 135 136 137 138 139 140 | 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 ){ | | | | 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 | # 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. # | | | | 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 | # 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)" {} |
︙ | ︙ | |||
1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 | 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 | > > > > > > > > > > > | 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 | 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 |
︙ | ︙ | |||
1156 1157 1158 1159 1160 1161 1162 | 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} } | | | | 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 | 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 { |
︙ | ︙ | |||
1248 1249 1250 1251 1252 1253 1254 | 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)" {} } | | > | | 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 | 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 |
︙ | ︙ | |||
1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 | 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))" {} } | > > > > > > > > | | | < | < | 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 | 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'); |
︙ | ︙ | |||
1323 1324 1325 1326 1327 1328 1329 | 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}} } | | | | | | | < | | 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 | 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 { |
︙ | ︙ | |||
1691 1692 1693 1694 1695 1696 1697 | DELETE FROM $tbl; INSERT INTO $tbl ($col) VALUES(0); SELECT (rowid==$col) FROM $tbl; DELETE FROM $tbl; }]] 0 } | | | | | | 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 | 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 { |
︙ | ︙ |