/ Check-in [d39e65fe]
Login

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: d39e65fe702a2e11477f2d996f77404f45763368
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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, &notUsed, 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