Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Experimental change to the handling of foreign key constraint violations when applying a changeset: all foreign keys, immediate and deferred, are deferred until the end of the transaction (or sub-transaction) opened by the sqlite3changeset_apply(). A single call to the conflict-handler (if any) is made if any FK constraint violations are still present in the database at this point. The conflict-handler may choose to rollback the changeset or to apply it, constraint violations and all. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | sessions |
Files: | files | file ages | folders |
SHA1: |
1d44e5d3c2b1dc958442f9114a960b25 |
User & Date: | dan 2013-07-03 19:53:05.693 |
Original Comment: | Experimental change to the handling of foreign key constraint violations when applying a changeset: all foreign keys, immediate and deferred, are deferred until the end of the transaction (or sub-transaction) opened by the sqlite3changeset_apply(). A single call to the conflict-handler (if any) is made if any FK constraint violations are still present in the database at this point. The conflict-handler may choose to rollback the changeset, or to apply it, constraint violations and all. |
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-04
| ||
15:22 | Fix a bug preventing some FK constraint checking from being deferred until the end of changeset application. (check-in: 1452defb8c user: dan tags: sessions) | |
2013-07-03
| ||
19:53 | Experimental change to the handling of foreign key constraint violations when applying a changeset: all foreign keys, immediate and deferred, are deferred until the end of the transaction (or sub-transaction) opened by the sqlite3changeset_apply(). A single call to the conflict-handler (if any) is made if any FK constraint violations are still present in the database at this point. The conflict-handler may choose to rollback the changeset or to apply it, constraint violations and all. (check-in: 1d44e5d3c2 user: dan tags: sessions) | |
2013-07-02
| ||
20:23 | Fixes for the sessions module so that it works with sqlite3_extended_error_codes() set. (check-in: c2972b6aed user: dan tags: sessions) | |
Changes
Changes to ext/session/session1.test.
︙ | ︙ | |||
260 261 262 263 264 265 266 | } do_conflict_test 3.2.3 -tables t2 -sql { DELETE FROM t2 WHERE a = 1; DELETE FROM t2 WHERE a = 2; DELETE FROM t2 WHERE a = 3; DELETE FROM t2 WHERE a = 4; } -conflicts { | < > | | 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 | } do_conflict_test 3.2.3 -tables t2 -sql { DELETE FROM t2 WHERE a = 1; DELETE FROM t2 WHERE a = 2; DELETE FROM t2 WHERE a = 3; DELETE FROM t2 WHERE a = 4; } -conflicts { {DELETE t2 NOTFOUND {i 3 t three}} {DELETE t2 DATA {i 4 t four} {i 4 t five}} {FOREIGN_KEY 1} } do_execsql_test 3.2.4 "SELECT * FROM t2" {} do_db2_test 3.2.5 "SELECT * FROM t2" {4 five} # Test UPDATE changesets. # do_execsql_test 3.3.1 { CREATE TABLE t4(a, b, c, PRIMARY KEY(b, c)); INSERT INTO t4 VALUES(1, 2, 3); INSERT INTO t4 VALUES(4, 5, 6); |
︙ | ︙ |
Added ext/session/session9.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 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 | # 2013 July 04 # # 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 tests that the sessions module handles foreign key constraint # violations when applying changesets as required. # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } source [file join [file dirname [info script]] session_common.tcl] source $testdir/tester.tcl ifcapable !session {finish_test; return} set testprefix session9 #-------------------------------------------------------------------- # proc populate_db {} { drop_all_tables execsql { PRAGMA foreign_keys = 1; CREATE TABLE p1(a PRIMARY KEY, b); CREATE TABLE c1(a PRIMARY KEY, b REFERENCES p1); CREATE TABLE c2(a PRIMARY KEY, b REFERENCES p1 DEFERRABLE INITIALLY DEFERRED ); INSERT INTO p1 VALUES(1, 'one'); INSERT INTO p1 VALUES(2, 'two'); INSERT INTO p1 VALUES(3, 'three'); INSERT INTO p1 VALUES(4, 'four'); } } proc capture_changeset {sql} { sqlite3session S db main foreach t [db eval {SELECT name FROM sqlite_master WHERE type='table'}] { S attach $t } execsql $sql set ret [S changeset] S delete return $ret } do_test 1.1 { populate_db set cc [capture_changeset { INSERT INTO c1 VALUES('ii', 2); INSERT INTO c2 VALUES('iii', 3); }] set {} {} } {} proc xConflict {args} { lappend ::xConflict {*}$args return $::conflictret } foreach {tn delrow trans conflictargs conflictret} { 1 2 0 {FOREIGN_KEY 1} OMIT 2 3 0 {FOREIGN_KEY 1} OMIT 3 2 1 {FOREIGN_KEY 1} OMIT 4 3 1 {FOREIGN_KEY 1} OMIT 5 2 0 {FOREIGN_KEY 1} ABORT 6 3 0 {FOREIGN_KEY 1} ABORT 7 2 1 {FOREIGN_KEY 1} ABORT 8 3 1 {FOREIGN_KEY 1} ABORT } { set A(OMIT) {0 {}} set A(ABORT) {1 SQLITE_CONSTRAINT} do_test 1.2.$tn.1 { populate_db execsql { DELETE FROM p1 WHERE a=($delrow+0) } if {$trans} { execsql BEGIN } set ::xConflict [list] list [catch {sqlite3changeset_apply db $::cc xConflict} msg] $msg } $A($conflictret) do_test 1.2.$tn.2 { set ::xConflict } $conflictargs set A(OMIT) {1 1} set A(ABORT) {0 0} do_test 1.2.$tn.3 { execsql { SELECT count(*) FROM c1 UNION ALL SELECT count(*) FROM c2 } } $A($conflictret) do_test 1.2.$tn.4 { expr ![sqlite3_get_autocommit db] } $trans do_test 1.2.$tn.5 { if { $trans } { execsql COMMIT } } {} } #-------------------------------------------------------------------- # Test that closing a transaction clears the defer_foreign_keys flag. # foreach {tn open noclose close} { 1 BEGIN {} COMMIT 2 BEGIN {} ROLLBACK 3 {SAVEPOINT one} {} {RELEASE one} 4 {SAVEPOINT one} {ROLLBACK TO one} {RELEASE one} } { execsql $open do_execsql_test 2.$tn.1 { PRAGMA defer_foreign_keys } {0} do_execsql_test 2.$tn.2 { PRAGMA defer_foreign_keys = 1; PRAGMA defer_foreign_keys; } {1} execsql $noclose do_execsql_test 2.$tn.3 { PRAGMA defer_foreign_keys } {1} execsql $close do_execsql_test 2.$tn.4 { PRAGMA defer_foreign_keys } {0} } finish_test |
Changes to ext/session/sqlite3session.c.
1 2 | #if defined(SQLITE_ENABLE_SESSION) && defined(SQLITE_ENABLE_PREUPDATE_HOOK) | < | 1 2 3 4 5 6 7 8 9 | #if defined(SQLITE_ENABLE_SESSION) && defined(SQLITE_ENABLE_PREUPDATE_HOOK) #include "sqlite3session.h" #include <assert.h> #include <string.h> #ifndef SQLITE_AMALGAMATION # include "sqliteInt.h" # include "vdbeInt.h" |
︙ | ︙ | |||
2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 | } if( iVal<0 || iVal>=sqlite3_column_count(pIter->pConflict) ){ return SQLITE_RANGE; } *ppValue = sqlite3_column_value(pIter->pConflict, iVal); return SQLITE_OK; } /* ** Finalize an iterator allocated with sqlite3changeset_start(). ** ** This function may not be called on iterators passed to a conflict handler ** callback by changeset_apply(). */ | > > > > > > > > > > > > > > > > > > > > | 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 | } if( iVal<0 || iVal>=sqlite3_column_count(pIter->pConflict) ){ return SQLITE_RANGE; } *ppValue = sqlite3_column_value(pIter->pConflict, iVal); return SQLITE_OK; } /* ** This function may only be called with an iterator passed to an ** SQLITE_CHANGESET_FOREIGN_KEY conflict handler callback. In this case ** it sets the output variable to the total number of known foreign key ** violations in the destination database and returns SQLITE_OK. ** ** In all other cases this function returns SQLITE_MISUSE. */ int sqlite3changeset_fk_conflicts( sqlite3_changeset_iter *pIter, /* Changeset iterator */ int *pnOut /* OUT: Number of FK violations */ ){ if( pIter->pConflict || pIter->apValue ){ return SQLITE_MISUSE; } *pnOut = pIter->nCol; return SQLITE_OK; } /* ** Finalize an iterator allocated with sqlite3changeset_start(). ** ** This function may not be called on iterators passed to a conflict handler ** callback by changeset_apply(). */ |
︙ | ︙ | |||
2841 2842 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 | memset(&sApply, 0, sizeof(sApply)); rc = sqlite3changeset_start(&pIter, nChangeset, pChangeset); if( rc!=SQLITE_OK ) return rc; sqlite3_mutex_enter(sqlite3_db_mutex(db)); rc = sqlite3_exec(db, "SAVEPOINT changeset_apply", 0, 0, 0); while( rc==SQLITE_OK && SQLITE_ROW==sqlite3changeset_next(pIter) ){ int nCol; int op; int bReplace = 0; int bRetry = 0; const char *zNew; | > > > | 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 | memset(&sApply, 0, sizeof(sApply)); rc = sqlite3changeset_start(&pIter, nChangeset, pChangeset); if( rc!=SQLITE_OK ) return rc; sqlite3_mutex_enter(sqlite3_db_mutex(db)); rc = sqlite3_exec(db, "SAVEPOINT changeset_apply", 0, 0, 0); if( rc==SQLITE_OK ){ rc = sqlite3_exec(db, "PRAGMA defer_foreign_keys = 1", 0, 0, 0); } while( rc==SQLITE_OK && SQLITE_ROW==sqlite3changeset_next(pIter) ){ int nCol; int op; int bReplace = 0; int bRetry = 0; const char *zNew; |
︙ | ︙ | |||
2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 | if( rc==SQLITE_OK ){ rc = sqlite3changeset_finalize(pIter); }else{ sqlite3changeset_finalize(pIter); } if( rc==SQLITE_OK ){ rc = sqlite3_exec(db, "RELEASE changeset_apply", 0, 0, 0); }else{ sqlite3_exec(db, "ROLLBACK TO changeset_apply", 0, 0, 0); sqlite3_exec(db, "RELEASE changeset_apply", 0, 0, 0); } | > > > > > > > > > > > > > > > > > | 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 | if( rc==SQLITE_OK ){ rc = sqlite3changeset_finalize(pIter); }else{ sqlite3changeset_finalize(pIter); } if( rc==SQLITE_OK ){ int nFk = sqlite3_foreign_key_check(db); 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); } if( res!=SQLITE_CHANGESET_OMIT ){ rc = SQLITE_CONSTRAINT; } } } sqlite3_exec(db, "PRAGMA defer_foreign_keys = 0", 0, 0, 0); if( rc==SQLITE_OK ){ rc = sqlite3_exec(db, "RELEASE changeset_apply", 0, 0, 0); }else{ sqlite3_exec(db, "ROLLBACK TO changeset_apply", 0, 0, 0); sqlite3_exec(db, "RELEASE changeset_apply", 0, 0, 0); } |
︙ | ︙ |
Changes to ext/session/sqlite3session.h.
︙ | ︙ | |||
485 486 487 488 489 490 491 492 493 494 495 496 497 498 | */ int sqlite3changeset_conflict( sqlite3_changeset_iter *pIter, /* Changeset iterator */ int iVal, /* Column number */ sqlite3_value **ppValue /* OUT: Value from conflicting row */ ); /* ** CAPI3REF: Finalize A Changeset Iterator ** ** This function is used to finalize an iterator allocated with ** [sqlite3changeset_start()]. ** | > > > > > > > > > > > > > > > | 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 | */ int sqlite3changeset_conflict( sqlite3_changeset_iter *pIter, /* Changeset iterator */ int iVal, /* Column number */ sqlite3_value **ppValue /* OUT: Value from conflicting row */ ); /* ** CAPI3REF: Determine The Number Of Foreign Key Constraint Violations ** ** This function may only be called with an iterator passed to an ** SQLITE_CHANGESET_FOREIGN_KEY conflict handler callback. In this case ** it sets the output variable to the total number of known foreign key ** violations in the destination database and returns SQLITE_OK. ** ** In all other cases this function returns SQLITE_MISUSE. */ int sqlite3changeset_fk_conflicts( sqlite3_changeset_iter *pIter, /* Changeset iterator */ int *pnOut /* OUT: Number of FK violations */ ); /* ** CAPI3REF: Finalize A Changeset Iterator ** ** This function is used to finalize an iterator allocated with ** [sqlite3changeset_start()]. ** |
︙ | ︙ | |||
805 806 807 808 809 810 811 812 813 814 | ** <dt>SQLITE_CHANGESET_CONFLICT<dd> ** CHANGESET_CONFLICT is passed as the second argument to the conflict ** handler while processing an INSERT change if the operation would result ** in duplicate primary key values. ** ** The conflicting row in this case is the database row with the matching ** primary key. ** ** <dt>SQLITE_CHANGESET_CONSTRAINT<dd> ** If any other constraint violation occurs while applying a change (i.e. | > > > > > > > > > > > > > | | > | | | | > | 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 | ** <dt>SQLITE_CHANGESET_CONFLICT<dd> ** CHANGESET_CONFLICT is passed as the second argument to the conflict ** handler while processing an INSERT change if the operation would result ** in duplicate primary key values. ** ** The conflicting row in this case is the database row with the matching ** primary key. ** ** <dt>SQLITE_CHANGESET_FOREIGN_KEY<dd> ** If foreign key handling is enabled, and applying a changeset leaves the ** database in a state containing foreign key violations, the conflict ** handler is invoked with CHANGESET_FOREIGN_KEY as the second argument ** exactly once before the changeset is committed. If the conflict handler ** returns CHANGESET_OMIT, the changes, including those that caused the ** foreign key constraint violation, are committed. Or, if it returns ** CHANGESET_ABORT, the changeset is rolled back. ** ** No current or conflicting row information is provided. The only function ** it is possible to call on the supplied sqlite3_changeset_iter handle ** is sqlite3changeset_fk_conflicts(). ** ** <dt>SQLITE_CHANGESET_CONSTRAINT<dd> ** If any other constraint violation occurs while applying a change (i.e. ** a UNIQUE, CHECK or NOT NULL constraint), the conflict handler is ** invoked with CHANGESET_CONSTRAINT as the second argument. ** ** There is no conflicting row in this case. The results of invoking the ** sqlite3changeset_conflict() API are undefined. ** ** </dl> */ #define SQLITE_CHANGESET_DATA 1 #define SQLITE_CHANGESET_NOTFOUND 2 #define SQLITE_CHANGESET_CONFLICT 3 #define SQLITE_CHANGESET_CONSTRAINT 4 #define SQLITE_CHANGESET_FOREIGN_KEY 5 /* ** CAPI3REF: Constants Returned By The Conflict Handler ** ** A conflict handler callback must return one of the following three values. ** ** <dl> |
︙ | ︙ |
Changes to ext/session/test_session.c.
︙ | ︙ | |||
246 247 248 249 250 251 252 | int nCol; /* Number of columns in table zTab */ pEval = Tcl_DuplicateObj(p->pConflictScript); Tcl_IncrRefCount(pEval); sqlite3changeset_op(pIter, &zTab, &nCol, &op, 0); | > > > > > > > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > | 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 | int nCol; /* Number of columns in table zTab */ pEval = Tcl_DuplicateObj(p->pConflictScript); Tcl_IncrRefCount(pEval); sqlite3changeset_op(pIter, &zTab, &nCol, &op, 0); if( eConf==SQLITE_CHANGESET_FOREIGN_KEY ){ int nFk; sqlite3changeset_fk_conflicts(pIter, &nFk); Tcl_ListObjAppendElement(0, pEval, Tcl_NewStringObj("FOREIGN_KEY", -1)); Tcl_ListObjAppendElement(0, pEval, Tcl_NewIntObj(nFk)); }else{ /* Append the operation type. */ Tcl_ListObjAppendElement(0, pEval, Tcl_NewStringObj( op==SQLITE_INSERT ? "INSERT" : op==SQLITE_UPDATE ? "UPDATE" : "DELETE", -1 )); /* Append the table name. */ Tcl_ListObjAppendElement(0, pEval, Tcl_NewStringObj(zTab, -1)); /* Append the conflict type. */ switch( eConf ){ case SQLITE_CHANGESET_DATA: Tcl_ListObjAppendElement(interp, pEval,Tcl_NewStringObj("DATA",-1)); break; case SQLITE_CHANGESET_NOTFOUND: Tcl_ListObjAppendElement(interp, pEval,Tcl_NewStringObj("NOTFOUND",-1)); break; case SQLITE_CHANGESET_CONFLICT: Tcl_ListObjAppendElement(interp, pEval,Tcl_NewStringObj("CONFLICT",-1)); break; case SQLITE_CHANGESET_CONSTRAINT: Tcl_ListObjAppendElement(interp, pEval,Tcl_NewStringObj("CONSTRAINT",-1)); break; } /* If this is not an INSERT, append the old row */ if( op!=SQLITE_INSERT ){ int i; Tcl_Obj *pOld = Tcl_NewObj(); for(i=0; i<nCol; i++){ sqlite3_value *pVal; sqlite3changeset_old(pIter, i, &pVal); test_append_value(pOld, pVal); } Tcl_ListObjAppendElement(0, pEval, pOld); } /* If this is not a DELETE, append the new row */ if( op!=SQLITE_DELETE ){ int i; Tcl_Obj *pNew = Tcl_NewObj(); for(i=0; i<nCol; i++){ sqlite3_value *pVal; sqlite3changeset_new(pIter, i, &pVal); test_append_value(pNew, pVal); } Tcl_ListObjAppendElement(0, pEval, pNew); } /* If this is a CHANGESET_DATA or CHANGESET_CONFLICT conflict, append ** the conflicting row. */ if( eConf==SQLITE_CHANGESET_DATA || eConf==SQLITE_CHANGESET_CONFLICT ){ int i; Tcl_Obj *pConflict = Tcl_NewObj(); for(i=0; i<nCol; i++){ int rc; sqlite3_value *pVal; rc = sqlite3changeset_conflict(pIter, i, &pVal); assert( rc==SQLITE_OK ); test_append_value(pConflict, pVal); } Tcl_ListObjAppendElement(0, pEval, pConflict); } /*********************************************************************** ** This block is purely for testing some error conditions. */ if( eConf==SQLITE_CHANGESET_CONSTRAINT || eConf==SQLITE_CHANGESET_NOTFOUND ){ sqlite3_value *pVal; int rc = sqlite3changeset_conflict(pIter, 0, &pVal); assert( rc==SQLITE_MISUSE ); }else{ sqlite3_value *pVal; int rc = sqlite3changeset_conflict(pIter, -1, &pVal); assert( rc==SQLITE_RANGE ); rc = sqlite3changeset_conflict(pIter, nCol, &pVal); assert( rc==SQLITE_RANGE ); } if( op==SQLITE_DELETE ){ sqlite3_value *pVal; int rc = sqlite3changeset_new(pIter, 0, &pVal); assert( rc==SQLITE_MISUSE ); }else{ sqlite3_value *pVal; int rc = sqlite3changeset_new(pIter, -1, &pVal); assert( rc==SQLITE_RANGE ); rc = sqlite3changeset_new(pIter, nCol, &pVal); assert( rc==SQLITE_RANGE ); } if( op==SQLITE_INSERT ){ sqlite3_value *pVal; int rc = sqlite3changeset_old(pIter, 0, &pVal); assert( rc==SQLITE_MISUSE ); }else{ sqlite3_value *pVal; int rc = sqlite3changeset_old(pIter, -1, &pVal); assert( rc==SQLITE_RANGE ); rc = sqlite3changeset_old(pIter, nCol, &pVal); assert( rc==SQLITE_RANGE ); } /* End of testing block ***********************************************************************/ } if( TCL_OK!=Tcl_EvalObjEx(interp, pEval, TCL_EVAL_GLOBAL) ){ Tcl_BackgroundError(interp); }else{ Tcl_Obj *pRes = Tcl_GetObjResult(interp); if( test_obj_eq_string(pRes, "OMIT") || test_obj_eq_string(pRes, "") ){ ret = SQLITE_CHANGESET_OMIT; |
︙ | ︙ |
Changes to src/fkey.c.
︙ | ︙ | |||
418 419 420 421 422 423 424 | sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0); sqlite3ReleaseTempReg(pParse, regRec); sqlite3ReleaseTempRange(pParse, regTemp, nCol); } } | | > > > > | 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 | sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0); sqlite3ReleaseTempReg(pParse, regRec); sqlite3ReleaseTempRange(pParse, regTemp, nCol); } } if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite && !(pParse->db->flags & SQLITE_DeferForeignKeys) ){ /* Special case: If this is an INSERT statement that will insert exactly ** one row into the table, raise a constraint immediately instead of ** incrementing a counter. This is necessary as the VM code is being ** generated for will not open a statement transaction. */ assert( nIncr==1 ); sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY, OE_Abort, "foreign key constraint failed", P4_STATIC |
︙ | ︙ |
Changes to src/main.c.
︙ | ︙ | |||
1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 | sqlite3ExpirePreparedStatements(db); sqlite3ResetAllSchemasOfConnection(db); } sqlite3BtreeLeaveAll(db); /* Any deferred constraint violations have now been resolved. */ db->nDeferredCons = 0; /* If one has been configured, invoke the rollback-hook callback */ if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){ db->xRollbackCallback(db->pRollbackArg); } } | > > | 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 | sqlite3ExpirePreparedStatements(db); sqlite3ResetAllSchemasOfConnection(db); } sqlite3BtreeLeaveAll(db); /* Any deferred constraint violations have now been resolved. */ db->nDeferredCons = 0; db->nDeferredImmCons = 0; db->flags &= ~SQLITE_DeferForeignKeys; /* If one has been configured, invoke the rollback-hook callback */ if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){ db->xRollbackCallback(db->pRollbackArg); } } |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 | } ++i; pFK = pFK->pNextFrom; } } } }else #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ #ifndef SQLITE_OMIT_FOREIGN_KEY #ifndef SQLITE_OMIT_TRIGGER if( sqlite3StrICmp(zLeft, "foreign_key_check")==0 ){ FKey *pFK; /* A foreign key constraint */ Table *pTab; /* Child table contain "REFERENCES" keyword */ | > > > > > > > > > > > > > > > | 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 | } ++i; pFK = pFK->pNextFrom; } } } }else if( sqlite3StrICmp(zLeft, "defer_foreign_keys")==0 ){ if( zRight ){ if( sqlite3GetBoolean(zRight, 0) ){ db->flags |= SQLITE_DeferForeignKeys; }else{ db->flags &= ~SQLITE_DeferForeignKeys; db->nDeferredImmCons = 0; } sqlite3VdbeAddOp2(v, OP_Expire, 0, 0); }else{ int bVal = !!(db->flags & SQLITE_DeferForeignKeys); returnSingleInt(pParse, "defer_foreign_keys", bVal); } } #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ #ifndef SQLITE_OMIT_FOREIGN_KEY #ifndef SQLITE_OMIT_TRIGGER if( sqlite3StrICmp(zLeft, "foreign_key_check")==0 ){ FKey *pFK; /* A foreign key constraint */ Table *pTab; /* Child table contain "REFERENCES" keyword */ |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
7275 7276 7277 7278 7279 7280 7281 7282 7283 7284 7285 7286 7287 7288 | ), 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 | > > | 7275 7276 7277 7278 7279 7280 7281 7282 7283 7284 7285 7286 7287 7288 7289 7290 | ), 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 **); int sqlite3_foreign_key_check(sqlite3 *db); /* ** 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 |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
957 958 959 960 961 962 963 964 965 966 967 968 969 970 | BusyHandler busyHandler; /* Busy callback */ Db aDbStatic[2]; /* Static space for the 2 default backends */ Savepoint *pSavepoint; /* List of active savepoints */ int busyTimeout; /* Busy handler timeout, in msec */ int nSavepoint; /* Number of non-transaction savepoints */ int nStatement; /* Number of nested statement-transactions */ i64 nDeferredCons; /* Net deferred constraints this transaction. */ int *pnBytesFreed; /* If not NULL, increment this in DbFree() */ #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY /* The following variables are all protected by the STATIC_MASTER ** mutex, not by sqlite3.mutex. They are used by code in notify.c. ** ** When X.pUnlockConnection==Y, that means that X is waiting for Y to | > | 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 | BusyHandler busyHandler; /* Busy callback */ Db aDbStatic[2]; /* Static space for the 2 default backends */ Savepoint *pSavepoint; /* List of active savepoints */ int busyTimeout; /* Busy handler timeout, in msec */ int nSavepoint; /* Number of non-transaction savepoints */ int nStatement; /* Number of nested statement-transactions */ i64 nDeferredCons; /* Net deferred constraints this transaction. */ i64 nDeferredImmCons; /* Net deferred immediate constraints */ int *pnBytesFreed; /* If not NULL, increment this in DbFree() */ #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY /* The following variables are all protected by the STATIC_MASTER ** mutex, not by sqlite3.mutex. They are used by code in notify.c. ** ** When X.pUnlockConnection==Y, that means that X is waiting for Y to |
︙ | ︙ | |||
1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 | #define SQLITE_ReverseOrder 0x00010000 /* Reverse unordered SELECTs */ #define SQLITE_RecTriggers 0x00020000 /* Enable recursive triggers */ #define SQLITE_ForeignKeys 0x00040000 /* Enforce foreign key constraints */ #define SQLITE_AutoIndex 0x00080000 /* Enable automatic indexes */ #define SQLITE_PreferBuiltin 0x00100000 /* Preference to built-in funcs */ #define SQLITE_LoadExtension 0x00200000 /* Enable load_extension */ #define SQLITE_EnableTrigger 0x00400000 /* True to enable triggers */ /* ** Bits of the sqlite3.dbOptFlags field that are used by the ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to ** selectively disable various optimizations. */ #define SQLITE_QueryFlattener 0x0001 /* Query flattening */ | > | 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 | #define SQLITE_ReverseOrder 0x00010000 /* Reverse unordered SELECTs */ #define SQLITE_RecTriggers 0x00020000 /* Enable recursive triggers */ #define SQLITE_ForeignKeys 0x00040000 /* Enforce foreign key constraints */ #define SQLITE_AutoIndex 0x00080000 /* Enable automatic indexes */ #define SQLITE_PreferBuiltin 0x00100000 /* Preference to built-in funcs */ #define SQLITE_LoadExtension 0x00200000 /* Enable load_extension */ #define SQLITE_EnableTrigger 0x00400000 /* True to enable triggers */ #define SQLITE_DeferForeignKeys 0x00800000 /* ** Bits of the sqlite3.dbOptFlags field that are used by the ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to ** selectively disable various optimizations. */ #define SQLITE_QueryFlattener 0x0001 /* Query flattening */ |
︙ | ︙ | |||
1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 | ** sqlite3.pSavepoint. The first element in the list is the most recently ** opened savepoint. Savepoints are added to the list by the vdbe ** OP_Savepoint instruction. */ struct Savepoint { char *zName; /* Savepoint name (nul-terminated) */ i64 nDeferredCons; /* Number of deferred fk violations */ Savepoint *pNext; /* Parent savepoint (if any) */ }; /* ** The following are used as the second parameter to sqlite3Savepoint(), ** and as the P1 argument to the OP_Savepoint instruction. */ | > | 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 | ** sqlite3.pSavepoint. The first element in the list is the most recently ** opened savepoint. Savepoints are added to the list by the vdbe ** OP_Savepoint instruction. */ struct Savepoint { char *zName; /* Savepoint name (nul-terminated) */ i64 nDeferredCons; /* Number of deferred fk violations */ i64 nDeferredImmCons; /* Number of deferred imm fk. */ Savepoint *pNext; /* Parent savepoint (if any) */ }; /* ** The following are used as the second parameter to sqlite3Savepoint(), ** and as the P1 argument to the OP_Savepoint instruction. */ |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
874 875 876 877 878 879 880 | } rc = sqlite3VdbeHalt(p); assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR ); if( rc==SQLITE_BUSY ){ p->rc = rc = SQLITE_BUSY; }else{ assert( rc==SQLITE_OK || (p->rc&0xff)==SQLITE_CONSTRAINT ); | | | 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 | } rc = sqlite3VdbeHalt(p); assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR ); if( rc==SQLITE_BUSY ){ p->rc = rc = SQLITE_BUSY; }else{ assert( rc==SQLITE_OK || (p->rc&0xff)==SQLITE_CONSTRAINT ); assert( rc==SQLITE_OK || db->nDeferredCons>0 || db->nDeferredImmCons>0 ); rc = p->rc ? SQLITE_ERROR : SQLITE_DONE; } goto vdbe_return; } /* Opcode: Integer P1 P2 * * * ** |
︙ | ︙ | |||
2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 | db->nSavepoint++; } /* Link the new savepoint into the database handle's list. */ pNew->pNext = db->pSavepoint; db->pSavepoint = pNew; pNew->nDeferredCons = db->nDeferredCons; } } }else{ iSavepoint = 0; /* Find the named savepoint. If there is no such savepoint, then an ** an error is returned to the user. */ | > | 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 | db->nSavepoint++; } /* Link the new savepoint into the database handle's list. */ pNew->pNext = db->pSavepoint; db->pSavepoint = pNew; pNew->nDeferredCons = db->nDeferredCons; pNew->nDeferredImmCons = db->nDeferredImmCons; } } }else{ iSavepoint = 0; /* Find the named savepoint. If there is no such savepoint, then an ** an error is returned to the user. */ |
︙ | ︙ | |||
2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 | db->pSavepoint = pSavepoint->pNext; sqlite3DbFree(db, pSavepoint); if( !isTransaction ){ db->nSavepoint--; } }else{ db->nDeferredCons = pSavepoint->nDeferredCons; } if( !isTransaction ){ rc = sqlite3VtabSavepoint(db, p1, iSavepoint); if( rc!=SQLITE_OK ) goto abort_due_to_error; } } | > | 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 | db->pSavepoint = pSavepoint->pNext; sqlite3DbFree(db, pSavepoint); if( !isTransaction ){ db->nSavepoint--; } }else{ db->nDeferredCons = pSavepoint->nDeferredCons; db->nDeferredImmCons = pSavepoint->nDeferredImmCons; } if( !isTransaction ){ rc = sqlite3VtabSavepoint(db, p1, iSavepoint); if( rc!=SQLITE_OK ) goto abort_due_to_error; } } |
︙ | ︙ | |||
2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 | rc = sqlite3BtreeBeginStmt(pBt, p->iStatement); } /* Store the current value of the database handles deferred constraint ** counter. If the statement transaction needs to be rolled back, ** the value of this counter needs to be restored too. */ p->nStmtDefCons = db->nDeferredCons; } } break; } /* Opcode: ReadCookie P1 P2 P3 * * ** | > | 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 | rc = sqlite3BtreeBeginStmt(pBt, p->iStatement); } /* Store the current value of the database handles deferred constraint ** counter. If the statement transaction needs to be rolled back, ** the value of this counter needs to be restored too. */ p->nStmtDefCons = db->nDeferredCons; p->nStmtDefImmCons = db->nDeferredImmCons; } } break; } /* Opcode: ReadCookie P1 P2 P3 * * ** |
︙ | ︙ | |||
5315 5316 5317 5318 5319 5320 5321 | ** ** Increment a "constraint counter" by P2 (P2 may be negative or positive). ** If P1 is non-zero, the database constraint counter is incremented ** (deferred foreign key constraints). Otherwise, if P1 is zero, the ** statement counter is incremented (immediate foreign key constraints). */ case OP_FkCounter: { | > > | | | | 5318 5319 5320 5321 5322 5323 5324 5325 5326 5327 5328 5329 5330 5331 5332 5333 5334 5335 5336 5337 5338 5339 5340 5341 5342 5343 5344 5345 5346 5347 5348 5349 5350 5351 5352 5353 5354 5355 5356 5357 | ** ** Increment a "constraint counter" by P2 (P2 may be negative or positive). ** If P1 is non-zero, the database constraint counter is incremented ** (deferred foreign key constraints). Otherwise, if P1 is zero, the ** statement counter is incremented (immediate foreign key constraints). */ case OP_FkCounter: { if( db->flags & SQLITE_DeferForeignKeys ){ db->nDeferredImmCons += pOp->p2; }else if( pOp->p1 ){ db->nDeferredCons += pOp->p2; }else{ p->nFkConstraint += pOp->p2; } break; } /* Opcode: FkIfZero P1 P2 * * * ** ** This opcode tests if a foreign key constraint-counter is currently zero. ** If so, jump to instruction P2. Otherwise, fall through to the next ** instruction. ** ** If P1 is non-zero, then the jump is taken if the database constraint-counter ** is zero (the one that counts deferred constraint violations). If P1 is ** zero, the jump is taken if the statement constraint-counter is zero ** (immediate foreign key constraint violations). */ case OP_FkIfZero: { /* jump */ if( pOp->p1 ){ if( db->nDeferredCons==0 && db->nDeferredImmCons==0 ) pc = pOp->p2-1; }else{ if( p->nFkConstraint==0 && db->nDeferredImmCons==0 ) pc = pOp->p2-1; } break; } #endif /* #ifndef SQLITE_OMIT_FOREIGN_KEY */ #ifndef SQLITE_OMIT_AUTOINCREMENT /* Opcode: MemMax P1 P2 * * * |
︙ | ︙ |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
346 347 348 349 350 351 352 353 354 355 356 357 358 359 | int iStatement; /* Statement number (or 0 if has not opened stmt) */ int aCounter[4]; /* Counters used by sqlite3_stmt_status() */ #ifndef SQLITE_OMIT_TRACE i64 startTime; /* Time when query started - used for profiling */ #endif i64 nFkConstraint; /* Number of imm. FK constraints this VM */ i64 nStmtDefCons; /* Number of def. constraints when stmt started */ char *zSql; /* Text of the SQL statement that generated this */ void *pFree; /* Free this when deleting the vdbe */ #ifdef SQLITE_DEBUG FILE *trace; /* Write an execution trace here, if not NULL */ #endif #ifdef SQLITE_ENABLE_TREE_EXPLAIN Explain *pExplain; /* The explainer */ | > | 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 | int iStatement; /* Statement number (or 0 if has not opened stmt) */ int aCounter[4]; /* Counters used by sqlite3_stmt_status() */ #ifndef SQLITE_OMIT_TRACE i64 startTime; /* Time when query started - used for profiling */ #endif i64 nFkConstraint; /* Number of imm. FK constraints this VM */ i64 nStmtDefCons; /* Number of def. constraints when stmt started */ i64 nStmtDefImmCons; /* Number of def. imm constraints when stmt started */ char *zSql; /* Text of the SQL statement that generated this */ void *pFree; /* Free this when deleting the vdbe */ #ifdef SQLITE_DEBUG FILE *trace; /* Write an execution trace here, if not NULL */ #endif #ifdef SQLITE_ENABLE_TREE_EXPLAIN Explain *pExplain; /* The explainer */ |
︙ | ︙ |
Changes to src/vdbeapi.c.
︙ | ︙ | |||
382 383 384 385 386 387 388 | ** reset the interrupt flag. This prevents a call to sqlite3_interrupt ** from interrupting a statement that has not yet started. */ if( db->activeVdbeCnt==0 ){ db->u1.isInterrupted = 0; } | | > > | 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 | ** reset the interrupt flag. This prevents a call to sqlite3_interrupt ** from interrupting a statement that has not yet started. */ if( db->activeVdbeCnt==0 ){ db->u1.isInterrupted = 0; } assert( db->writeVdbeCnt>0 || db->autoCommit==0 || (db->nDeferredCons==0 && db->nDeferredImmCons==0) ); #ifndef SQLITE_OMIT_TRACE if( db->xProfile && !db->init.busy ){ sqlite3OsCurrentTimeInt64(db->pVfs, &p->startTime); } #endif |
︙ | ︙ | |||
1492 1493 1494 1495 1496 1497 1498 | *ppValue = pMem; preupdate_new_out: sqlite3Error(db, rc, 0); return sqlite3ApiExit(db, rc); } #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */ | > > | 1494 1495 1496 1497 1498 1499 1500 1501 1502 | *ppValue = pMem; preupdate_new_out: sqlite3Error(db, rc, 0); return sqlite3ApiExit(db, rc); } #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */ int sqlite3_foreign_key_check(sqlite3 *db){ return db->nDeferredImmCons; } |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 | } /* If the statement transaction is being rolled back, also restore the ** database handles deferred constraint counter to the value it had when ** the statement transaction was opened. */ if( eOp==SAVEPOINT_ROLLBACK ){ db->nDeferredCons = p->nStmtDefCons; } } return rc; } /* ** This function is called when a transaction opened by the database ** handle associated with the VM passed as an argument is about to be ** committed. If there are outstanding deferred foreign key constraint ** violations, return SQLITE_ERROR. Otherwise, SQLITE_OK. ** ** If there are outstanding FK violations and this function returns ** SQLITE_ERROR, set the result of the VM to SQLITE_CONSTRAINT_FOREIGNKEY ** and write an error message to it. Then return SQLITE_ERROR. */ #ifndef SQLITE_OMIT_FOREIGN_KEY int sqlite3VdbeCheckFk(Vdbe *p, int deferred){ sqlite3 *db = p->db; | > > | > | 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 | } /* If the statement transaction is being rolled back, also restore the ** database handles deferred constraint counter to the value it had when ** the statement transaction was opened. */ if( eOp==SAVEPOINT_ROLLBACK ){ db->nDeferredCons = p->nStmtDefCons; db->nDeferredImmCons = p->nStmtDefImmCons; } } return rc; } /* ** This function is called when a transaction opened by the database ** handle associated with the VM passed as an argument is about to be ** committed. If there are outstanding deferred foreign key constraint ** violations, return SQLITE_ERROR. Otherwise, SQLITE_OK. ** ** If there are outstanding FK violations and this function returns ** SQLITE_ERROR, set the result of the VM to SQLITE_CONSTRAINT_FOREIGNKEY ** and write an error message to it. Then return SQLITE_ERROR. */ #ifndef SQLITE_OMIT_FOREIGN_KEY int sqlite3VdbeCheckFk(Vdbe *p, int deferred){ sqlite3 *db = p->db; if( (deferred && (db->nDeferredCons+db->nDeferredImmCons)>0) || (!deferred && p->nFkConstraint>0) ){ p->rc = SQLITE_CONSTRAINT_FOREIGNKEY; p->errorAction = OE_Abort; sqlite3SetString(&p->zErrMsg, db, "foreign key constraint failed"); return SQLITE_ERROR; } return SQLITE_OK; } |
︙ | ︙ | |||
2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 | sqlite3VdbeLeave(p); return SQLITE_BUSY; }else if( rc!=SQLITE_OK ){ p->rc = rc; sqlite3RollbackAll(db, SQLITE_OK); }else{ db->nDeferredCons = 0; sqlite3CommitInternalChanges(db); } }else{ sqlite3RollbackAll(db, SQLITE_OK); } db->nStatement = 0; }else if( eStatementOp==0 ){ | > > | 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 | sqlite3VdbeLeave(p); return SQLITE_BUSY; }else if( rc!=SQLITE_OK ){ p->rc = rc; sqlite3RollbackAll(db, SQLITE_OK); }else{ db->nDeferredCons = 0; db->nDeferredImmCons = 0; db->flags &= ~SQLITE_DeferForeignKeys; sqlite3CommitInternalChanges(db); } }else{ sqlite3RollbackAll(db, SQLITE_OK); } db->nStatement = 0; }else if( eStatementOp==0 ){ |
︙ | ︙ |