SQLite

Check-in [d39e65fe70]
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
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: d39e65fe702a2e11477f2d996f77404f45763368
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
Unified Diff Ignore Whitespace Patch
Changes to ext/session/sqlite3session.c.
2967
2968
2969
2970
2971
2972
2973
2974

2975
2976
2977
2978
2979
2980
2981
2982
  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);
      }







|
>
|







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

6254
6255
6256
6257
6258
6259
6260
6261
** 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

#define SQLITE_DBSTATUS_MAX                  9   /* Largest defined DBSTATUS */


/*
** CAPI3REF: Prepared Statement Status
**
** ^(Each prepared statement maintains various
** [SQLITE_STMTSTATUS counters] that measure the number







>
>
>
>
>
>












>
|







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
7285
7286
7287
7288
7289
7290
7291
7292
7293
7294
7295
7296
7297
7298
  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
#endif

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif







<
<












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
1352

1353
1354
1355
1356
1357
1358
1359
    { "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         }

  };
  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;







|
>







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
1502
1503
  *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; }







<
<
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