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