Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Remove the undocumented sqlite3_foreign_key_check() API and replace it with sqlite3_db_status(db, SQLITE_DBSTATUS_DEFERRED_FKS, ...). Add test cases for the new functionality. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | sessions |
| Files: | files | file ages | folders |
| SHA1: |
d39e65fe702a2e11477f2d996f77404f |
| User & Date: | drh 2013-07-11 13:49:59 |
References
|
2013-07-11
| ||
| 15:03 | Add the "defer_foreign_keys" pragma and the SQLITE_DBSTATUS_DEFERRED_FKS value for sqlite3_db_status(). This is a cherry-pick of a sequence of five checkins in the sessions branch between [1d44e5d3c2] and [d39e65fe70]. (check-in: 527121ac user: drh tags: trunk) | |
Context
|
2013-07-11
| ||
| 15:31 | Merge recent trunk changes (such as the query_only PRAGMA, the defer_foreign_keys PRAGMA, and the SQLITE_DBSTATUS_DEFERRED_FKS parameter to sqlite3_db_status()) into the sessions branch. (check-in: 8dfc0b78 user: drh tags: sessions) | |
| 13:49 | Remove the undocumented sqlite3_foreign_key_check() API and replace it with sqlite3_db_status(db, SQLITE_DBSTATUS_DEFERRED_FKS, ...). Add test cases for the new functionality. (check-in: d39e65fe user: drh tags: sessions) | |
| 12:19 | Rename the SQLITE_DeferForeignKeys macro to shorter SQLITE_DeferFKs. Move the "defer_foreign_keys" pragma into the flagPragma() routine. (check-in: 3a2ab74c user: drh tags: sessions) | |
Changes
Changes to ext/session/sqlite3session.c.
2967 2967 if( rc==SQLITE_OK ){ 2968 2968 rc = sqlite3changeset_finalize(pIter); 2969 2969 }else{ 2970 2970 sqlite3changeset_finalize(pIter); 2971 2971 } 2972 2972 2973 2973 if( rc==SQLITE_OK ){ 2974 - int nFk = sqlite3_foreign_key_check(db); 2975 - if( nFk>0 ){ 2974 + int nFk, notUsed; 2975 + sqlite3_db_status(db, SQLITE_DBSTATUS_DEFERRED_FKS, &nFk, ¬Used, 0); 2976 + if( nFk!=0 ){ 2976 2977 int res = SQLITE_CHANGESET_ABORT; 2977 2978 if( xConflict ){ 2978 2979 sqlite3_changeset_iter sIter; 2979 2980 memset(&sIter, 0, sizeof(sIter)); 2980 2981 sIter.nCol = nFk; 2981 2982 res = xConflict(pCtx, SQLITE_CHANGESET_FOREIGN_KEY, &sIter); 2982 2983 }
Changes to src/sqlite.h.in.
6235 6235 ** wal file in wal mode databases, or the number of pages written to the 6236 6236 ** database file in rollback mode databases. Any pages written as part of 6237 6237 ** transaction rollback or database recovery operations are not included. 6238 6238 ** If an IO or other error occurs while writing a page to disk, the effect 6239 6239 ** on subsequent SQLITE_DBSTATUS_CACHE_WRITE requests is undefined). ^The 6240 6240 ** highwater mark associated with SQLITE_DBSTATUS_CACHE_WRITE is always 0. 6241 6241 ** </dd> 6242 +** 6243 +** [[SQLITE_DBSTATUS_DEFERRED_FKS]] ^(<dt>SQLITE_DBSTATUS_DEFERRED_FKS</dt> 6244 +** <dd>This parameter returns the zero for the current value if and only if 6245 +** there all foreign key constraints (deferred or immediate) have been 6246 +** resolved. The highwater mark is always 0. 6247 +** </dd> 6242 6248 ** </dl> 6243 6249 */ 6244 6250 #define SQLITE_DBSTATUS_LOOKASIDE_USED 0 6245 6251 #define SQLITE_DBSTATUS_CACHE_USED 1 6246 6252 #define SQLITE_DBSTATUS_SCHEMA_USED 2 6247 6253 #define SQLITE_DBSTATUS_STMT_USED 3 6248 6254 #define SQLITE_DBSTATUS_LOOKASIDE_HIT 4 6249 6255 #define SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE 5 6250 6256 #define SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL 6 6251 6257 #define SQLITE_DBSTATUS_CACHE_HIT 7 6252 6258 #define SQLITE_DBSTATUS_CACHE_MISS 8 6253 6259 #define SQLITE_DBSTATUS_CACHE_WRITE 9 6254 -#define SQLITE_DBSTATUS_MAX 9 /* Largest defined DBSTATUS */ 6260 +#define SQLITE_DBSTATUS_DEFERRED_FKS 10 6261 +#define SQLITE_DBSTATUS_MAX 10 /* Largest defined DBSTATUS */ 6255 6262 6256 6263 6257 6264 /* 6258 6265 ** CAPI3REF: Prepared Statement Status 6259 6266 ** 6260 6267 ** ^(Each prepared statement maintains various 6261 6268 ** [SQLITE_STMTSTATUS counters] that measure the number ................................................................................ 7278 7285 void* 7279 7286 ); 7280 7287 SQLITE_EXPERIMENTAL int sqlite3_preupdate_old(sqlite3 *, int, sqlite3_value **); 7281 7288 SQLITE_EXPERIMENTAL int sqlite3_preupdate_count(sqlite3 *); 7282 7289 SQLITE_EXPERIMENTAL int sqlite3_preupdate_depth(sqlite3 *); 7283 7290 SQLITE_EXPERIMENTAL int sqlite3_preupdate_new(sqlite3 *, int, sqlite3_value **); 7284 7291 7285 -int sqlite3_foreign_key_check(sqlite3 *db); 7286 - 7287 7292 /* 7288 7293 ** Undo the hack that converts floating point types to integer for 7289 7294 ** builds on processors without floating point support. 7290 7295 */ 7291 7296 #ifdef SQLITE_OMIT_FLOATING_POINT 7292 7297 # undef double 7293 7298 #endif 7294 7299 7295 7300 #ifdef __cplusplus 7296 7301 } /* End of the 'extern "C"' block */ 7297 7302 #endif 7298 7303 #endif
Changes to src/status.c.
238 238 sqlite3PagerCacheStat(pPager, op, resetFlag, &nRet); 239 239 } 240 240 } 241 241 *pHighwater = 0; 242 242 *pCurrent = nRet; 243 243 break; 244 244 } 245 + 246 + /* Set *pCurrent to non-zero if there are unresolved deferred foreign 247 + ** key constraints. Set *pCurrent to zero if all foreign key constraints 248 + ** have been satisfied. The *pHighwater is always set to zero. 249 + */ 250 + case SQLITE_DBSTATUS_DEFERRED_FKS: { 251 + *pHighwater = 0; 252 + *pCurrent = db->nDeferredImmCons>0 || db->nDeferredCons>0; 253 + break; 254 + } 245 255 246 256 default: { 247 257 rc = SQLITE_ERROR; 248 258 } 249 259 } 250 260 sqlite3_mutex_leave(db->mutex); 251 261 return rc; 252 262 }
Changes to src/test_malloc.c.
1345 1345 { "SCHEMA_USED", SQLITE_DBSTATUS_SCHEMA_USED }, 1346 1346 { "STMT_USED", SQLITE_DBSTATUS_STMT_USED }, 1347 1347 { "LOOKASIDE_HIT", SQLITE_DBSTATUS_LOOKASIDE_HIT }, 1348 1348 { "LOOKASIDE_MISS_SIZE", SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE }, 1349 1349 { "LOOKASIDE_MISS_FULL", SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL }, 1350 1350 { "CACHE_HIT", SQLITE_DBSTATUS_CACHE_HIT }, 1351 1351 { "CACHE_MISS", SQLITE_DBSTATUS_CACHE_MISS }, 1352 - { "CACHE_WRITE", SQLITE_DBSTATUS_CACHE_WRITE } 1352 + { "CACHE_WRITE", SQLITE_DBSTATUS_CACHE_WRITE }, 1353 + { "DEFERRED_FKS", SQLITE_DBSTATUS_DEFERRED_FKS } 1353 1354 }; 1354 1355 Tcl_Obj *pResult; 1355 1356 if( objc!=4 ){ 1356 1357 Tcl_WrongNumArgs(interp, 1, objv, "DB PARAMETER RESETFLAG"); 1357 1358 return TCL_ERROR; 1358 1359 } 1359 1360 if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
Changes to src/vdbeapi.c.
1495 1495 *ppValue = pMem; 1496 1496 1497 1497 preupdate_new_out: 1498 1498 sqlite3Error(db, rc, 0); 1499 1499 return sqlite3ApiExit(db, rc); 1500 1500 } 1501 1501 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */ 1502 - 1503 -int sqlite3_foreign_key_check(sqlite3 *db){ return db->nDeferredImmCons; }
Changes to test/fkey1.test.
113 113 ); 114 114 PRAGMA foreign_key_list(t9); 115 115 } 116 116 } [concat \ 117 117 {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \ 118 118 {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \ 119 119 ] 120 +do_test fkey1-3.5 { 121 + sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 122 +} {0 0 0} 120 123 121 124 finish_test
Added test/fkey6.test.
1 +# 2012 December 17 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. 12 +# 13 +# This file tests the PRAGMA defer_foreign_keys and 14 +# SQLITE_DBSTATUS_DEFERRED_FKS 15 +# 16 + 17 +set testdir [file dirname $argv0] 18 +source $testdir/tester.tcl 19 + 20 +ifcapable {!foreignkey} { 21 + finish_test 22 + return 23 +} 24 + 25 +do_execsql_test fkey6-1.1 { 26 + PRAGMA foreign_keys=ON; 27 + CREATE TABLE t1(x INTEGER PRIMARY KEY); 28 + CREATE TABLE t2(y INTEGER PRIMARY KEY, 29 + z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED); 30 + CREATE INDEX t2z ON t2(z); 31 + CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x)); 32 + CREATE INDEX t3v ON t3(v); 33 + INSERT INTO t1 VALUES(1),(2),(3),(4),(5); 34 + INSERT INTO t2 VALUES(1,1),(2,2); 35 + INSERT INTO t3 VALUES(3,3),(4,4); 36 +} {} 37 +do_test fkey6-1.2 { 38 + catchsql {DELETE FROM t1 WHERE x=2;} 39 +} {1 {foreign key constraint failed}} 40 +do_test fkey6-1.3 { 41 + sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 42 +} {0 0 0} 43 +do_test fkey6-1.4 { 44 + execsql { 45 + BEGIN; 46 + DELETE FROM t1 WHERE x=1; 47 + } 48 +} {} 49 +do_test fkey6-1.5.1 { 50 + sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1 51 +} {0 1 0} 52 +do_test fkey6-1.5.2 { 53 + sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 54 +} {0 1 0} 55 +do_test fkey6-1.6 { 56 + execsql { 57 + ROLLBACK; 58 + } 59 +} {} 60 +do_test fkey6-1.7 { 61 + sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 62 +} {0 0 0} 63 +do_test fkey6-1.8 { 64 + execsql { 65 + PRAGMA defer_foreign_keys=ON; 66 + BEGIN; 67 + DELETE FROM t1 WHERE x=3; 68 + } 69 +} {} 70 +do_test fkey6-1.9 { 71 + sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 72 +} {0 1 0} 73 +do_test fkey6-1.10 { 74 + execsql { 75 + ROLLBACK; 76 + PRAGMA defer_foreign_keys=OFF; 77 + BEGIN; 78 + } 79 + catchsql {DELETE FROM t1 WHERE x=3} 80 +} {1 {foreign key constraint failed}} 81 +db eval {ROLLBACK} 82 + 83 +do_test fkey6-1.20 { 84 + execsql { 85 + BEGIN; 86 + DELETE FROM t1 WHERE x=1; 87 + } 88 + sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 89 +} {0 1 0} 90 +do_test fkey6-1.21 { 91 + execsql { 92 + DELETE FROM t2 WHERE y=1; 93 + } 94 + sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 95 +} {0 0 0} 96 +do_test fkey6-1.22 { 97 + execsql { 98 + COMMIT; 99 + } 100 +} {} 101 + 102 + 103 +finish_test