blob: 04e83b09f143c6037db03b73db170b5d0b311a92 [file] [log] [blame]
dan1da40a32009-09-19 17:00:31 +00001/*
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 contains code used by the compiler to add foreign key
12** support to compiled SQL statements.
13*/
14#include "sqliteInt.h"
15
16#ifndef SQLITE_OMIT_FOREIGN_KEY
dan75cbd982009-09-21 16:06:03 +000017#ifndef SQLITE_OMIT_TRIGGER
dan1da40a32009-09-19 17:00:31 +000018
19/*
20** Deferred and Immediate FKs
21** --------------------------
22**
23** Foreign keys in SQLite come in two flavours: deferred and immediate.
dan8a2fff72009-09-23 18:07:22 +000024** If an immediate foreign key constraint is violated, SQLITE_CONSTRAINT
25** is returned and the current statement transaction rolled back. If a
dan1da40a32009-09-19 17:00:31 +000026** deferred foreign key constraint is violated, no action is taken
27** immediately. However if the application attempts to commit the
28** transaction before fixing the constraint violation, the attempt fails.
29**
30** Deferred constraints are implemented using a simple counter associated
31** with the database handle. The counter is set to zero each time a
32** database transaction is opened. Each time a statement is executed
33** that causes a foreign key violation, the counter is incremented. Each
34** time a statement is executed that removes an existing violation from
35** the database, the counter is decremented. When the transaction is
36** committed, the commit fails if the current value of the counter is
37** greater than zero. This scheme has two big drawbacks:
38**
39** * When a commit fails due to a deferred foreign key constraint,
40** there is no way to tell which foreign constraint is not satisfied,
41** or which row it is not satisfied for.
42**
43** * If the database contains foreign key violations when the
44** transaction is opened, this may cause the mechanism to malfunction.
45**
46** Despite these problems, this approach is adopted as it seems simpler
47** than the alternatives.
48**
49** INSERT operations:
50**
dan8099ce62009-09-23 08:43:35 +000051** I.1) For each FK for which the table is the child table, search
dan8a2fff72009-09-23 18:07:22 +000052** the parent table for a match. If none is found increment the
53** constraint counter.
dan1da40a32009-09-19 17:00:31 +000054**
dan8a2fff72009-09-23 18:07:22 +000055** I.2) For each FK for which the table is the parent table,
dan8099ce62009-09-23 08:43:35 +000056** search the child table for rows that correspond to the new
57** row in the parent table. Decrement the counter for each row
dan1da40a32009-09-19 17:00:31 +000058** found (as the constraint is now satisfied).
59**
60** DELETE operations:
61**
dan8a2fff72009-09-23 18:07:22 +000062** D.1) For each FK for which the table is the child table,
dan8099ce62009-09-23 08:43:35 +000063** search the parent table for a row that corresponds to the
64** deleted row in the child table. If such a row is not found,
dan1da40a32009-09-19 17:00:31 +000065** decrement the counter.
66**
dan8099ce62009-09-23 08:43:35 +000067** D.2) For each FK for which the table is the parent table, search
68** the child table for rows that correspond to the deleted row
dan8a2fff72009-09-23 18:07:22 +000069** in the parent table. For each found increment the counter.
dan1da40a32009-09-19 17:00:31 +000070**
71** UPDATE operations:
72**
73** An UPDATE command requires that all 4 steps above are taken, but only
74** for FK constraints for which the affected columns are actually
75** modified (values must be compared at runtime).
76**
77** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2.
78** This simplifies the implementation a bit.
79**
80** For the purposes of immediate FK constraints, the OR REPLACE conflict
81** resolution is considered to delete rows before the new row is inserted.
82** If a delete caused by OR REPLACE violates an FK constraint, an exception
83** is thrown, even if the FK constraint would be satisfied after the new
84** row is inserted.
85**
danbd747832009-09-25 12:00:01 +000086** Immediate constraints are usually handled similarly. The only difference
87** is that the counter used is stored as part of each individual statement
88** object (struct Vdbe). If, after the statement has run, its immediate
89** constraint counter is greater than zero, it returns SQLITE_CONSTRAINT
90** and the statement transaction is rolled back. An exception is an INSERT
91** statement that inserts a single row only (no triggers). In this case,
92** instead of using a counter, an exception is thrown immediately if the
93** INSERT violates a foreign key constraint. This is necessary as such
94** an INSERT does not open a statement transaction.
95**
dan1da40a32009-09-19 17:00:31 +000096** TODO: How should dropping a table be handled? How should renaming a
97** table be handled?
dan8099ce62009-09-23 08:43:35 +000098**
99**
dan1da40a32009-09-19 17:00:31 +0000100** Query API Notes
101** ---------------
102**
103** Before coding an UPDATE or DELETE row operation, the code-generator
104** for those two operations needs to know whether or not the operation
105** requires any FK processing and, if so, which columns of the original
106** row are required by the FK processing VDBE code (i.e. if FKs were
107** implemented using triggers, which of the old.* columns would be
108** accessed). No information is required by the code-generator before
dan8099ce62009-09-23 08:43:35 +0000109** coding an INSERT operation. The functions used by the UPDATE/DELETE
110** generation code to query for this information are:
dan1da40a32009-09-19 17:00:31 +0000111**
dan8099ce62009-09-23 08:43:35 +0000112** sqlite3FkRequired() - Test to see if FK processing is required.
113** sqlite3FkOldmask() - Query for the set of required old.* columns.
114**
115**
116** Externally accessible module functions
117** --------------------------------------
118**
119** sqlite3FkCheck() - Check for foreign key violations.
120** sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions.
121** sqlite3FkDelete() - Delete an FKey structure.
dan1da40a32009-09-19 17:00:31 +0000122*/
123
124/*
125** VDBE Calling Convention
126** -----------------------
127**
128** Example:
129**
130** For the following INSERT statement:
131**
132** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);
133** INSERT INTO t1 VALUES(1, 2, 3.1);
134**
135** Register (x): 2 (type integer)
136** Register (x+1): 1 (type integer)
137** Register (x+2): NULL (type NULL)
138** Register (x+3): 3.1 (type real)
139*/
140
141/*
dan8099ce62009-09-23 08:43:35 +0000142** A foreign key constraint requires that the key columns in the parent
dan1da40a32009-09-19 17:00:31 +0000143** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
dan8099ce62009-09-23 08:43:35 +0000144** Given that pParent is the parent table for foreign key constraint pFKey,
145** search the schema a unique index on the parent key columns.
dan1da40a32009-09-19 17:00:31 +0000146**
dan8099ce62009-09-23 08:43:35 +0000147** If successful, zero is returned. If the parent key is an INTEGER PRIMARY
148** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx
149** is set to point to the unique index.
150**
151** If the parent key consists of a single column (the foreign key constraint
152** is not a composite foreign key), output variable *paiCol is set to NULL.
153** Otherwise, it is set to point to an allocated array of size N, where
154** N is the number of columns in the parent key. The first element of the
155** array is the index of the child table column that is mapped by the FK
156** constraint to the parent table column stored in the left-most column
157** of index *ppIdx. The second element of the array is the index of the
158** child table column that corresponds to the second left-most column of
159** *ppIdx, and so on.
160**
161** If the required index cannot be found, either because:
162**
163** 1) The named parent key columns do not exist, or
164**
165** 2) The named parent key columns do exist, but are not subject to a
166** UNIQUE or PRIMARY KEY constraint, or
167**
168** 3) No parent key columns were provided explicitly as part of the
169** foreign key definition, and the parent table does not have a
170** PRIMARY KEY, or
171**
172** 4) No parent key columns were provided explicitly as part of the
173** foreign key definition, and the PRIMARY KEY of the parent table
174** consists of a a different number of columns to the child key in
175** the child table.
176**
177** then non-zero is returned, and a "foreign key mismatch" error loaded
178** into pParse. If an OOM error occurs, non-zero is returned and the
179** pParse->db->mallocFailed flag is set.
dan1da40a32009-09-19 17:00:31 +0000180*/
181static int locateFkeyIndex(
182 Parse *pParse, /* Parse context to store any error in */
dan8099ce62009-09-23 08:43:35 +0000183 Table *pParent, /* Parent table of FK constraint pFKey */
dan1da40a32009-09-19 17:00:31 +0000184 FKey *pFKey, /* Foreign key to find index for */
dan8099ce62009-09-23 08:43:35 +0000185 Index **ppIdx, /* OUT: Unique index on parent table */
dan1da40a32009-09-19 17:00:31 +0000186 int **paiCol /* OUT: Map of index columns in pFKey */
187){
dan8099ce62009-09-23 08:43:35 +0000188 Index *pIdx = 0; /* Value to return via *ppIdx */
189 int *aiCol = 0; /* Value to return via *paiCol */
190 int nCol = pFKey->nCol; /* Number of columns in parent key */
191 char *zKey = pFKey->aCol[0].zCol; /* Name of left-most parent key column */
dan1da40a32009-09-19 17:00:31 +0000192
193 /* The caller is responsible for zeroing output parameters. */
194 assert( ppIdx && *ppIdx==0 );
195 assert( !paiCol || *paiCol==0 );
196
197 /* If this is a non-composite (single column) foreign key, check if it
dan8099ce62009-09-23 08:43:35 +0000198 ** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx
dan1da40a32009-09-19 17:00:31 +0000199 ** and *paiCol set to zero and return early.
200 **
201 ** Otherwise, for a composite foreign key (more than one column), allocate
202 ** space for the aiCol array (returned via output parameter *paiCol).
203 ** Non-composite foreign keys do not require the aiCol array.
204 */
205 if( nCol==1 ){
206 /* The FK maps to the IPK if any of the following are true:
207 **
dand981d442009-09-23 13:59:17 +0000208 ** 1) There is an INTEGER PRIMARY KEY column and the FK is implicitly
209 ** mapped to the primary key of table pParent, or
210 ** 2) The FK is explicitly mapped to a column declared as INTEGER
dan1da40a32009-09-19 17:00:31 +0000211 ** PRIMARY KEY.
212 */
dan8099ce62009-09-23 08:43:35 +0000213 if( pParent->iPKey>=0 ){
214 if( !zKey ) return 0;
215 if( !sqlite3StrICmp(pParent->aCol[pParent->iPKey].zName, zKey) ) return 0;
dan1da40a32009-09-19 17:00:31 +0000216 }
217 }else if( paiCol ){
218 assert( nCol>1 );
219 aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int));
220 if( !aiCol ) return 1;
221 *paiCol = aiCol;
222 }
223
dan8099ce62009-09-23 08:43:35 +0000224 for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){
dan1da40a32009-09-19 17:00:31 +0000225 if( pIdx->nColumn==nCol && pIdx->onError!=OE_None ){
226 /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
227 ** of columns. If each indexed column corresponds to a foreign key
228 ** column of pFKey, then this index is a winner. */
229
dan8099ce62009-09-23 08:43:35 +0000230 if( zKey==0 ){
231 /* If zKey is NULL, then this foreign key is implicitly mapped to
232 ** the PRIMARY KEY of table pParent. The PRIMARY KEY index may be
dan1da40a32009-09-19 17:00:31 +0000233 ** identified by the test (Index.autoIndex==2). */
234 if( pIdx->autoIndex==2 ){
dan8a2fff72009-09-23 18:07:22 +0000235 if( aiCol ){
236 int i;
237 for(i=0; i<nCol; i++) aiCol[i] = pFKey->aCol[i].iFrom;
238 }
dan1da40a32009-09-19 17:00:31 +0000239 break;
240 }
241 }else{
dan8099ce62009-09-23 08:43:35 +0000242 /* If zKey is non-NULL, then this foreign key was declared to
243 ** map to an explicit list of columns in table pParent. Check if this
dan1da40a32009-09-19 17:00:31 +0000244 ** index matches those columns. */
245 int i, j;
246 for(i=0; i<nCol; i++){
dan8099ce62009-09-23 08:43:35 +0000247 char *zIdxCol = pParent->aCol[pIdx->aiColumn[i]].zName;
dan1da40a32009-09-19 17:00:31 +0000248 for(j=0; j<nCol; j++){
249 if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
250 if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
251 break;
252 }
253 }
254 if( j==nCol ) break;
255 }
256 if( i==nCol ) break; /* pIdx is usable */
257 }
258 }
259 }
260
261 if( pParse && !pIdx ){
danf0662562009-09-28 18:52:11 +0000262 if( !pParse->disableTriggers ){
263 sqlite3ErrorMsg(pParse, "foreign key mismatch");
264 }
dan1da40a32009-09-19 17:00:31 +0000265 sqlite3DbFree(pParse->db, aiCol);
266 return 1;
267 }
268
269 *ppIdx = pIdx;
270 return 0;
271}
272
dan8099ce62009-09-23 08:43:35 +0000273/*
danbd747832009-09-25 12:00:01 +0000274** This function is called when a row is inserted into or deleted from the
275** child table of foreign key constraint pFKey. If an SQL UPDATE is executed
276** on the child table of pFKey, this function is invoked twice for each row
dan8099ce62009-09-23 08:43:35 +0000277** affected - once to "delete" the old row, and then again to "insert" the
278** new row.
279**
280** Each time it is called, this function generates VDBE code to locate the
281** row in the parent table that corresponds to the row being inserted into
282** or deleted from the child table. If the parent row can be found, no
283** special action is taken. Otherwise, if the parent row can *not* be
284** found in the parent table:
285**
286** Operation | FK type | Action taken
287** --------------------------------------------------------------------------
danbd747832009-09-25 12:00:01 +0000288** INSERT immediate Increment the "immediate constraint counter".
289**
290** DELETE immediate Decrement the "immediate constraint counter".
dan8099ce62009-09-23 08:43:35 +0000291**
292** INSERT deferred Increment the "deferred constraint counter".
293**
294** DELETE deferred Decrement the "deferred constraint counter".
295**
danbd747832009-09-25 12:00:01 +0000296** These operations are identified in the comment at the top of this file
297** (fkey.c) as "I.1" and "D.1".
dan8099ce62009-09-23 08:43:35 +0000298*/
299static void fkLookupParent(
dan1da40a32009-09-19 17:00:31 +0000300 Parse *pParse, /* Parse context */
301 int iDb, /* Index of database housing pTab */
dan8099ce62009-09-23 08:43:35 +0000302 Table *pTab, /* Parent table of FK pFKey */
303 Index *pIdx, /* Unique index on parent key columns in pTab */
304 FKey *pFKey, /* Foreign key constraint */
305 int *aiCol, /* Map from parent key columns to child table columns */
306 int regData, /* Address of array containing child table row */
dan32b09f22009-09-23 17:29:59 +0000307 int nIncr /* Increment constraint counter by this */
dan1da40a32009-09-19 17:00:31 +0000308){
dan8099ce62009-09-23 08:43:35 +0000309 int i; /* Iterator variable */
310 Vdbe *v = sqlite3GetVdbe(pParse); /* Vdbe to add code to */
311 int iCur = pParse->nTab - 1; /* Cursor number to use */
312 int iOk = sqlite3VdbeMakeLabel(v); /* jump here if parent key found */
dan1da40a32009-09-19 17:00:31 +0000313
dan0ff297e2009-09-25 17:03:14 +0000314 /* If nIncr is less than zero, then check at runtime if there are any
315 ** outstanding constraints to resolve. If there are not, there is no need
316 ** to check if deleting this row resolves any outstanding violations.
317 **
318 ** Check if any of the key columns in the child table row are NULL. If
319 ** any are, then the constraint is considered satisfied. No need to
320 ** search for a matching row in the parent table. */
321 if( nIncr<0 ){
322 sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk);
323 }
dan1da40a32009-09-19 17:00:31 +0000324 for(i=0; i<pFKey->nCol; i++){
dan36062642009-09-21 18:56:23 +0000325 int iReg = aiCol[i] + regData + 1;
dan1da40a32009-09-19 17:00:31 +0000326 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk);
327 }
328
329 if( pIdx==0 ){
dan8099ce62009-09-23 08:43:35 +0000330 /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY
331 ** column of the parent table (table pTab). */
dan9277efa2009-09-28 11:54:21 +0000332 int iMustBeInt; /* Address of MustBeInt instruction */
dan140026b2009-09-24 18:19:41 +0000333 int regTemp = sqlite3GetTempReg(pParse);
334
335 /* Invoke MustBeInt to coerce the child key value to an integer (i.e.
336 ** apply the affinity of the parent key). If this fails, then there
337 ** is no matching parent key. Before using MustBeInt, make a copy of
338 ** the value. Otherwise, the value inserted into the child key column
339 ** will have INTEGER affinity applied to it, which may not be correct. */
340 sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[0]+1+regData, regTemp);
dan9277efa2009-09-28 11:54:21 +0000341 iMustBeInt = sqlite3VdbeAddOp2(v, OP_MustBeInt, regTemp, 0);
342
343 /* If the parent table is the same as the child table, and we are about
344 ** to increment the constraint-counter (i.e. this is an INSERT operation),
345 ** then check if the row being inserted matches itself. If so, do not
346 ** increment the constraint-counter. */
347 if( pTab==pFKey->pFrom && nIncr==1 ){
348 sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp);
349 }
350
dan1da40a32009-09-19 17:00:31 +0000351 sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
dan140026b2009-09-24 18:19:41 +0000352 sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp);
dan1da40a32009-09-19 17:00:31 +0000353 sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
354 sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
dan9277efa2009-09-28 11:54:21 +0000355 sqlite3VdbeJumpHere(v, iMustBeInt);
dan140026b2009-09-24 18:19:41 +0000356 sqlite3ReleaseTempReg(pParse, regTemp);
dan1da40a32009-09-19 17:00:31 +0000357 }else{
dan140026b2009-09-24 18:19:41 +0000358 int nCol = pFKey->nCol;
359 int regTemp = sqlite3GetTempRange(pParse, nCol);
dan1da40a32009-09-19 17:00:31 +0000360 int regRec = sqlite3GetTempReg(pParse);
361 KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
362
363 sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb);
364 sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
dan9277efa2009-09-28 11:54:21 +0000365 for(i=0; i<nCol; i++){
dan140026b2009-09-24 18:19:41 +0000366 sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[i]+1+regData, regTemp+i);
dan1da40a32009-09-19 17:00:31 +0000367 }
dan9277efa2009-09-28 11:54:21 +0000368
369 /* If the parent table is the same as the child table, and we are about
370 ** to increment the constraint-counter (i.e. this is an INSERT operation),
371 ** then check if the row being inserted matches itself. If so, do not
372 ** increment the constraint-counter. */
373 if( pTab==pFKey->pFrom && nIncr==1 ){
374 int iJump = sqlite3VdbeCurrentAddr(v) + nCol + 1;
375 for(i=0; i<nCol; i++){
376 int iChild = aiCol[i]+1+regData;
377 int iParent = pIdx->aiColumn[i]+1+regData;
378 sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent);
379 }
380 sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
381 }
382
dan140026b2009-09-24 18:19:41 +0000383 sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec);
384 sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
dan1da40a32009-09-19 17:00:31 +0000385 sqlite3VdbeAddOp3(v, OP_Found, iCur, iOk, regRec);
dan9277efa2009-09-28 11:54:21 +0000386
dan1da40a32009-09-19 17:00:31 +0000387 sqlite3ReleaseTempReg(pParse, regRec);
dan140026b2009-09-24 18:19:41 +0000388 sqlite3ReleaseTempRange(pParse, regTemp, nCol);
dan1da40a32009-09-19 17:00:31 +0000389 }
390
dan32b09f22009-09-23 17:29:59 +0000391 if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
392 /* Special case: If this is an INSERT statement that will insert exactly
393 ** one row into the table, raise a constraint immediately instead of
394 ** incrementing a counter. This is necessary as the VM code is being
395 ** generated for will not open a statement transaction. */
396 assert( nIncr==1 );
dan1da40a32009-09-19 17:00:31 +0000397 sqlite3HaltConstraint(
398 pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
399 );
dan32b09f22009-09-23 17:29:59 +0000400 }else{
401 if( nIncr>0 && pFKey->isDeferred==0 ){
402 sqlite3ParseToplevel(pParse)->mayAbort = 1;
403 }
dan0ff297e2009-09-25 17:03:14 +0000404 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
dan1da40a32009-09-19 17:00:31 +0000405 }
406
407 sqlite3VdbeResolveLabel(v, iOk);
408}
409
dan8099ce62009-09-23 08:43:35 +0000410/*
411** This function is called to generate code executed when a row is deleted
412** from the parent table of foreign key constraint pFKey and, if pFKey is
413** deferred, when a row is inserted into the same table. When generating
414** code for an SQL UPDATE operation, this function may be called twice -
415** once to "delete" the old row and once to "insert" the new row.
416**
417** The code generated by this function scans through the rows in the child
418** table that correspond to the parent table row being deleted or inserted.
419** For each child row found, one of the following actions is taken:
420**
421** Operation | FK type | Action taken
422** --------------------------------------------------------------------------
danbd747832009-09-25 12:00:01 +0000423** DELETE immediate Increment the "immediate constraint counter".
424** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
425** throw a "foreign key constraint failed" exception.
426**
427** INSERT immediate Decrement the "immediate constraint counter".
dan8099ce62009-09-23 08:43:35 +0000428**
429** DELETE deferred Increment the "deferred constraint counter".
430** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
431** throw a "foreign key constraint failed" exception.
432**
433** INSERT deferred Decrement the "deferred constraint counter".
434**
danbd747832009-09-25 12:00:01 +0000435** These operations are identified in the comment at the top of this file
436** (fkey.c) as "I.2" and "D.2".
dan8099ce62009-09-23 08:43:35 +0000437*/
438static void fkScanChildren(
dan1da40a32009-09-19 17:00:31 +0000439 Parse *pParse, /* Parse context */
440 SrcList *pSrc, /* SrcList containing the table to scan */
dan9277efa2009-09-28 11:54:21 +0000441 Table *pTab,
dan1da40a32009-09-19 17:00:31 +0000442 Index *pIdx, /* Foreign key index */
443 FKey *pFKey, /* Foreign key relationship */
dan8099ce62009-09-23 08:43:35 +0000444 int *aiCol, /* Map from pIdx cols to child table cols */
dan1da40a32009-09-19 17:00:31 +0000445 int regData, /* Referenced table data starts here */
446 int nIncr /* Amount to increment deferred counter by */
447){
448 sqlite3 *db = pParse->db; /* Database handle */
449 int i; /* Iterator variable */
450 Expr *pWhere = 0; /* WHERE clause to scan with */
451 NameContext sNameContext; /* Context used to resolve WHERE clause */
452 WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */
dan0ff297e2009-09-25 17:03:14 +0000453 int iFkIfZero = 0; /* Address of OP_FkIfZero */
454 Vdbe *v = sqlite3GetVdbe(pParse);
455
dan9277efa2009-09-28 11:54:21 +0000456 assert( !pIdx || pIdx->pTable==pTab );
457
dan0ff297e2009-09-25 17:03:14 +0000458 if( nIncr<0 ){
459 iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);
460 }
dan1da40a32009-09-19 17:00:31 +0000461
danbd747832009-09-25 12:00:01 +0000462 /* Create an Expr object representing an SQL expression like:
463 **
464 ** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
465 **
466 ** The collation sequence used for the comparison should be that of
467 ** the parent key columns. The affinity of the parent key column should
468 ** be applied to each child key value before the comparison takes place.
469 */
dan1da40a32009-09-19 17:00:31 +0000470 for(i=0; i<pFKey->nCol; i++){
dan8099ce62009-09-23 08:43:35 +0000471 Expr *pLeft; /* Value from parent table row */
472 Expr *pRight; /* Column ref to child table */
dan1da40a32009-09-19 17:00:31 +0000473 Expr *pEq; /* Expression (pLeft = pRight) */
dan8099ce62009-09-23 08:43:35 +0000474 int iCol; /* Index of column in child table */
475 const char *zCol; /* Name of column in child table */
dan1da40a32009-09-19 17:00:31 +0000476
477 pLeft = sqlite3Expr(db, TK_REGISTER, 0);
478 if( pLeft ){
danbd747832009-09-25 12:00:01 +0000479 /* Set the collation sequence and affinity of the LHS of each TK_EQ
480 ** expression to the parent key column defaults. */
dan140026b2009-09-24 18:19:41 +0000481 if( pIdx ){
482 int iCol = pIdx->aiColumn[i];
483 Column *pCol = &pIdx->pTable->aCol[iCol];
484 pLeft->iTable = regData+iCol+1;
485 pLeft->affinity = pCol->affinity;
486 pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
487 }else{
488 pLeft->iTable = regData;
489 pLeft->affinity = SQLITE_AFF_INTEGER;
490 }
dan1da40a32009-09-19 17:00:31 +0000491 }
492 iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
dana8f0bf62009-09-23 12:06:52 +0000493 assert( iCol>=0 );
494 zCol = pFKey->pFrom->aCol[iCol].zName;
dan1da40a32009-09-19 17:00:31 +0000495 pRight = sqlite3Expr(db, TK_ID, zCol);
496 pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
497 pWhere = sqlite3ExprAnd(db, pWhere, pEq);
498 }
499
dan9277efa2009-09-28 11:54:21 +0000500 /* If the child table is the same as the parent table, and this scan
501 ** is taking place as part of a DELETE operation (operation D.2), omit the
502 ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE
503 ** clause, where $rowid is the rowid of the row being deleted. */
504 if( pTab==pFKey->pFrom && nIncr>0 ){
505 Expr *pEq; /* Expression (pLeft = pRight) */
506 Expr *pLeft; /* Value from parent table row */
507 Expr *pRight; /* Column ref to child table */
508 pLeft = sqlite3Expr(db, TK_REGISTER, 0);
509 pRight = sqlite3Expr(db, TK_COLUMN, 0);
510 if( pLeft && pRight ){
511 pLeft->iTable = regData;
512 pLeft->affinity = SQLITE_AFF_INTEGER;
513 pRight->iTable = pSrc->a[0].iCursor;
514 pRight->iColumn = -1;
515 }
516 pEq = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0);
517 pWhere = sqlite3ExprAnd(db, pWhere, pEq);
518 }
519
dan1da40a32009-09-19 17:00:31 +0000520 /* Resolve the references in the WHERE clause. */
521 memset(&sNameContext, 0, sizeof(NameContext));
522 sNameContext.pSrcList = pSrc;
523 sNameContext.pParse = pParse;
524 sqlite3ResolveExprNames(&sNameContext, pWhere);
525
526 /* Create VDBE to loop through the entries in pSrc that match the WHERE
527 ** clause. If the constraint is not deferred, throw an exception for
528 ** each row found. Otherwise, for deferred constraints, increment the
529 ** deferred constraint counter by nIncr for each row selected. */
530 pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0);
dan32b09f22009-09-23 17:29:59 +0000531 if( nIncr==0 ){
danbd747832009-09-25 12:00:01 +0000532 /* Special case: A RESTRICT Action. Throw an error immediately if one
533 ** of these is encountered. */
dan1da40a32009-09-19 17:00:31 +0000534 sqlite3HaltConstraint(
535 pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
536 );
dan32b09f22009-09-23 17:29:59 +0000537 }else{
538 if( nIncr>0 && pFKey->isDeferred==0 ){
539 sqlite3ParseToplevel(pParse)->mayAbort = 1;
540 }
dan0ff297e2009-09-25 17:03:14 +0000541 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
dan1da40a32009-09-19 17:00:31 +0000542 }
danf59c5ca2009-09-22 16:55:38 +0000543 if( pWInfo ){
544 sqlite3WhereEnd(pWInfo);
545 }
dan1da40a32009-09-19 17:00:31 +0000546
547 /* Clean up the WHERE clause constructed above. */
548 sqlite3ExprDelete(db, pWhere);
dan0ff297e2009-09-25 17:03:14 +0000549 if( iFkIfZero ){
550 sqlite3VdbeJumpHere(v, iFkIfZero);
551 }
dan1da40a32009-09-19 17:00:31 +0000552}
553
554/*
555** This function returns a pointer to the head of a linked list of FK
dan8099ce62009-09-23 08:43:35 +0000556** constraints for which table pTab is the parent table. For example,
dan1da40a32009-09-19 17:00:31 +0000557** given the following schema:
558**
559** CREATE TABLE t1(a PRIMARY KEY);
560** CREATE TABLE t2(b REFERENCES t1(a);
561**
562** Calling this function with table "t1" as an argument returns a pointer
563** to the FKey structure representing the foreign key constraint on table
564** "t2". Calling this function with "t2" as the argument would return a
dan8099ce62009-09-23 08:43:35 +0000565** NULL pointer (as there are no FK constraints for which t2 is the parent
566** table).
dan1da40a32009-09-19 17:00:31 +0000567*/
dan432cc5b2009-09-26 17:51:48 +0000568FKey *sqlite3FkReferences(Table *pTab){
dan1da40a32009-09-19 17:00:31 +0000569 int nName = sqlite3Strlen30(pTab->zName);
570 return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName);
571}
572
dan8099ce62009-09-23 08:43:35 +0000573/*
574** The second argument is a Trigger structure allocated by the
575** fkActionTrigger() routine. This function deletes the Trigger structure
576** and all of its sub-components.
577**
578** The Trigger structure or any of its sub-components may be allocated from
579** the lookaside buffer belonging to database handle dbMem.
580*/
dan75cbd982009-09-21 16:06:03 +0000581static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){
582 if( p ){
583 TriggerStep *pStep = p->step_list;
584 sqlite3ExprDelete(dbMem, pStep->pWhere);
585 sqlite3ExprListDelete(dbMem, pStep->pExprList);
dan9277efa2009-09-28 11:54:21 +0000586 sqlite3SelectDelete(dbMem, pStep->pSelect);
drh788536b2009-09-23 03:01:58 +0000587 sqlite3ExprDelete(dbMem, p->pWhen);
dan75cbd982009-09-21 16:06:03 +0000588 sqlite3DbFree(dbMem, p);
589 }
590}
591
dan8099ce62009-09-23 08:43:35 +0000592/*
dand66c8302009-09-28 14:49:01 +0000593** This function is called to generate code that runs when table pTab is
594** being dropped from the database. The SrcList passed as the second argument
595** to this function contains a single entry guaranteed to resolve to
596** table pTab.
597**
598** Normally, no code is required. However, if either
599**
600** (a) The table is the parent table of a FK constraint, or
601** (b) The table is the child table of a deferred FK constraint and it is
602** determined at runtime that there are outstanding deferred FK
603** constraint violations in the database,
604**
605** then the equivalent of "DELETE FROM <tbl>" is executed before dropping
606** the table from the database. Triggers are disabled while running this
607** DELETE, but foreign key actions are not.
608*/
609void sqlite3FkDropTable(Parse *pParse, SrcList *pName, Table *pTab){
610 sqlite3 *db = pParse->db;
611 if( (db->flags&SQLITE_ForeignKeys) && !IsVirtual(pTab) && !pTab->pSelect ){
612 int iSkip = 0;
613 Vdbe *v = sqlite3GetVdbe(pParse);
614
615 assert( v ); /* VDBE has already been allocated */
616 if( sqlite3FkReferences(pTab)==0 ){
617 /* Search for a deferred foreign key constraint for which this table
618 ** is the child table. If one cannot be found, return without
619 ** generating any VDBE code. If one can be found, then jump over
620 ** the entire DELETE if there are no outstanding deferred constraints
621 ** when this statement is run. */
622 FKey *p;
623 for(p=pTab->pFKey; p; p=p->pNextFrom){
624 if( p->isDeferred ) break;
625 }
626 if( !p ) return;
627 iSkip = sqlite3VdbeMakeLabel(v);
628 sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip);
629 }
630
631 pParse->disableTriggers = 1;
632 sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0);
633 pParse->disableTriggers = 0;
634
635 /* If the DELETE has generated immediate foreign key constraint
636 ** violations, halt the VDBE and return an error at this point, before
637 ** any modifications to the schema are made. This is because statement
638 ** transactions are not able to rollback schema changes. */
639 sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
640 sqlite3HaltConstraint(
641 pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
642 );
643
644 if( iSkip ){
645 sqlite3VdbeResolveLabel(v, iSkip);
646 }
647 }
648}
649
650/*
dan8099ce62009-09-23 08:43:35 +0000651** This function is called when inserting, deleting or updating a row of
652** table pTab to generate VDBE code to perform foreign key constraint
653** processing for the operation.
654**
655** For a DELETE operation, parameter regOld is passed the index of the
656** first register in an array of (pTab->nCol+1) registers containing the
657** rowid of the row being deleted, followed by each of the column values
658** of the row being deleted, from left to right. Parameter regNew is passed
659** zero in this case.
660**
dan8099ce62009-09-23 08:43:35 +0000661** For an INSERT operation, regOld is passed zero and regNew is passed the
662** first register of an array of (pTab->nCol+1) registers containing the new
663** row data.
664**
dan9277efa2009-09-28 11:54:21 +0000665** For an UPDATE operation, this function is called twice. Once before
666** the original record is deleted from the table using the calling convention
667** described for DELETE. Then again after the original record is deleted
668** but before the new record is inserted using the INSERT convention. In
669** both cases parameter pChanges is passed the list of columns being
670** updated by the statement.
dan8099ce62009-09-23 08:43:35 +0000671*/
dan1da40a32009-09-19 17:00:31 +0000672void sqlite3FkCheck(
673 Parse *pParse, /* Parse context */
674 Table *pTab, /* Row is being deleted from this table */
675 ExprList *pChanges, /* Changed columns if this is an UPDATE */
676 int regOld, /* Previous row data is stored here */
677 int regNew /* New row data is stored here */
678){
679 sqlite3 *db = pParse->db; /* Database handle */
680 Vdbe *v; /* VM to write code to */
681 FKey *pFKey; /* Used to iterate through FKs */
682 int iDb; /* Index of database containing pTab */
683 const char *zDb; /* Name of database containing pTab */
danf0662562009-09-28 18:52:11 +0000684 int isIgnoreErrors = pParse->disableTriggers;
dan1da40a32009-09-19 17:00:31 +0000685
dan792e9202009-09-29 11:28:51 +0000686 /* Exactly one of regOld and regNew should be non-zero. */
687 assert( (regOld==0)!=(regNew==0) );
dan1da40a32009-09-19 17:00:31 +0000688
689 /* If foreign-keys are disabled, this function is a no-op. */
690 if( (db->flags&SQLITE_ForeignKeys)==0 ) return;
691
692 v = sqlite3GetVdbe(pParse);
693 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
694 zDb = db->aDb[iDb].zName;
695
dan8099ce62009-09-23 08:43:35 +0000696 /* Loop through all the foreign key constraints for which pTab is the
697 ** child table (the table that the foreign key definition is part of). */
dan1da40a32009-09-19 17:00:31 +0000698 for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
dan8099ce62009-09-23 08:43:35 +0000699 Table *pTo; /* Parent table of foreign key pFKey */
dan1da40a32009-09-19 17:00:31 +0000700 Index *pIdx = 0; /* Index on key columns in pTo */
dan36062642009-09-21 18:56:23 +0000701 int *aiFree = 0;
702 int *aiCol;
703 int iCol;
704 int i;
dan1da40a32009-09-19 17:00:31 +0000705
dan8099ce62009-09-23 08:43:35 +0000706 /* Find the parent table of this foreign key. Also find a unique index
707 ** on the parent key columns in the parent table. If either of these
708 ** schema items cannot be located, set an error in pParse and return
709 ** early. */
danf0662562009-09-28 18:52:11 +0000710 if( pParse->disableTriggers ){
711 pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
712 }else{
713 pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
714 }
715 if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){
716 if( !isIgnoreErrors || db->mallocFailed ) return;
717 continue;
718 }
dan36062642009-09-21 18:56:23 +0000719 assert( pFKey->nCol==1 || (aiFree && pIdx) );
dan1da40a32009-09-19 17:00:31 +0000720
721 /* If the key does not overlap with the pChanges list, skip this FK. */
722 if( pChanges ){
723 /* TODO */
724 }
725
dan36062642009-09-21 18:56:23 +0000726 if( aiFree ){
727 aiCol = aiFree;
728 }else{
729 iCol = pFKey->aCol[0].iFrom;
730 aiCol = &iCol;
731 }
732 for(i=0; i<pFKey->nCol; i++){
733 if( aiCol[i]==pTab->iPKey ){
734 aiCol[i] = -1;
735 }
736 }
737
dan8099ce62009-09-23 08:43:35 +0000738 /* Take a shared-cache advisory read-lock on the parent table. Allocate
739 ** a cursor to use to search the unique index on the parent key columns
740 ** in the parent table. */
dan1da40a32009-09-19 17:00:31 +0000741 sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
742 pParse->nTab++;
743
dan32b09f22009-09-23 17:29:59 +0000744 if( regOld!=0 ){
745 /* A row is being removed from the child table. Search for the parent.
746 ** If the parent does not exist, removing the child row resolves an
747 ** outstanding foreign key constraint violation. */
dan8099ce62009-09-23 08:43:35 +0000748 fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1);
dan1da40a32009-09-19 17:00:31 +0000749 }
750 if( regNew!=0 ){
dan32b09f22009-09-23 17:29:59 +0000751 /* A row is being added to the child table. If a parent row cannot
752 ** be found, adding the child row has violated the FK constraint. */
dan8099ce62009-09-23 08:43:35 +0000753 fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1);
dan1da40a32009-09-19 17:00:31 +0000754 }
755
dan36062642009-09-21 18:56:23 +0000756 sqlite3DbFree(db, aiFree);
dan1da40a32009-09-19 17:00:31 +0000757 }
758
759 /* Loop through all the foreign key constraints that refer to this table */
dan432cc5b2009-09-26 17:51:48 +0000760 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
dan1da40a32009-09-19 17:00:31 +0000761 Index *pIdx = 0; /* Foreign key index for pFKey */
762 SrcList *pSrc;
763 int *aiCol = 0;
764
dan32b09f22009-09-23 17:29:59 +0000765 if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
766 assert( regOld==0 && regNew!=0 );
767 /* Inserting a single row into a parent table cannot cause an immediate
768 ** foreign key violation. So do nothing in this case. */
danf0662562009-09-28 18:52:11 +0000769 continue;
dan1da40a32009-09-19 17:00:31 +0000770 }
771
danf0662562009-09-28 18:52:11 +0000772 if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
773 if( !isIgnoreErrors || db->mallocFailed ) return;
774 continue;
775 }
dan1da40a32009-09-19 17:00:31 +0000776 assert( aiCol || pFKey->nCol==1 );
777
dan8099ce62009-09-23 08:43:35 +0000778 /* Check if this update statement has modified any of the child key
779 ** columns for this foreign key constraint. If it has not, there is
780 ** no need to search the child table for rows in violation. This is
dan1da40a32009-09-19 17:00:31 +0000781 ** just an optimization. Things would work fine without this check. */
782 if( pChanges ){
783 /* TODO */
784 }
785
786 /* Create a SrcList structure containing a single table (the table
787 ** the foreign key that refers to this table is attached to). This
788 ** is required for the sqlite3WhereXXX() interface. */
789 pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
danf59c5ca2009-09-22 16:55:38 +0000790 if( pSrc ){
791 pSrc->a->pTab = pFKey->pFrom;
792 pSrc->a->pTab->nRef++;
793 pSrc->a->iCursor = pParse->nTab++;
794
dan32b09f22009-09-23 17:29:59 +0000795 if( regNew!=0 ){
dan9277efa2009-09-28 11:54:21 +0000796 fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1);
danf59c5ca2009-09-22 16:55:38 +0000797 }
798 if( regOld!=0 ){
799 /* If there is a RESTRICT action configured for the current operation
dan8099ce62009-09-23 08:43:35 +0000800 ** on the parent table of this FK, then throw an exception
danf59c5ca2009-09-22 16:55:38 +0000801 ** immediately if the FK constraint is violated, even if this is a
802 ** deferred trigger. That's what RESTRICT means. To defer checking
803 ** the constraint, the FK should specify NO ACTION (represented
804 ** using OE_None). NO ACTION is the default. */
dan9277efa2009-09-28 11:54:21 +0000805 fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1);
danf59c5ca2009-09-22 16:55:38 +0000806 }
807
danf59c5ca2009-09-22 16:55:38 +0000808 sqlite3SrcListDelete(db, pSrc);
dan1da40a32009-09-19 17:00:31 +0000809 }
dan1da40a32009-09-19 17:00:31 +0000810 sqlite3DbFree(db, aiCol);
811 }
812}
813
814#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
815
816/*
817** This function is called before generating code to update or delete a
818** row contained in table pTab. If the operation is an update, then
819** pChanges is a pointer to the list of columns to modify. If this is a
820** delete, then pChanges is NULL.
821*/
822u32 sqlite3FkOldmask(
823 Parse *pParse, /* Parse context */
824 Table *pTab, /* Table being modified */
825 ExprList *pChanges /* Non-NULL for UPDATE operations */
826){
827 u32 mask = 0;
828 if( pParse->db->flags&SQLITE_ForeignKeys ){
829 FKey *p;
830 int i;
831 for(p=pTab->pFKey; p; p=p->pNextFrom){
dan32b09f22009-09-23 17:29:59 +0000832 for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
dan1da40a32009-09-19 17:00:31 +0000833 }
dan432cc5b2009-09-26 17:51:48 +0000834 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
dan1da40a32009-09-19 17:00:31 +0000835 Index *pIdx = 0;
danf0662562009-09-28 18:52:11 +0000836 locateFkeyIndex(pParse, pTab, p, &pIdx, 0);
dan1da40a32009-09-19 17:00:31 +0000837 if( pIdx ){
838 for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
839 }
840 }
841 }
842 return mask;
843}
844
845/*
846** This function is called before generating code to update or delete a
847** row contained in table pTab. If the operation is an update, then
848** pChanges is a pointer to the list of columns to modify. If this is a
849** delete, then pChanges is NULL.
850**
851** If any foreign key processing will be required, this function returns
852** true. If there is no foreign key related processing, this function
853** returns false.
854*/
855int sqlite3FkRequired(
856 Parse *pParse, /* Parse context */
857 Table *pTab, /* Table being modified */
858 ExprList *pChanges /* Non-NULL for UPDATE operations */
859){
860 if( pParse->db->flags&SQLITE_ForeignKeys ){
dan432cc5b2009-09-26 17:51:48 +0000861 if( sqlite3FkReferences(pTab) || pTab->pFKey ) return 1;
dan1da40a32009-09-19 17:00:31 +0000862 }
863 return 0;
864}
865
dan8099ce62009-09-23 08:43:35 +0000866/*
867** This function is called when an UPDATE or DELETE operation is being
868** compiled on table pTab, which is the parent table of foreign-key pFKey.
869** If the current operation is an UPDATE, then the pChanges parameter is
870** passed a pointer to the list of columns being modified. If it is a
871** DELETE, pChanges is passed a NULL pointer.
872**
873** It returns a pointer to a Trigger structure containing a trigger
874** equivalent to the ON UPDATE or ON DELETE action specified by pFKey.
875** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is
876** returned (these actions require no special handling by the triggers
877** sub-system, code for them is created by fkScanChildren()).
878**
879** For example, if pFKey is the foreign key and pTab is table "p" in
880** the following schema:
881**
882** CREATE TABLE p(pk PRIMARY KEY);
883** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE);
884**
885** then the returned trigger structure is equivalent to:
886**
887** CREATE TRIGGER ... DELETE ON p BEGIN
888** DELETE FROM c WHERE ck = old.pk;
889** END;
890**
891** The returned pointer is cached as part of the foreign key object. It
892** is eventually freed along with the rest of the foreign key object by
893** sqlite3FkDelete().
894*/
dan1da40a32009-09-19 17:00:31 +0000895static Trigger *fkActionTrigger(
dan8099ce62009-09-23 08:43:35 +0000896 Parse *pParse, /* Parse context */
dan1da40a32009-09-19 17:00:31 +0000897 Table *pTab, /* Table being updated or deleted from */
898 FKey *pFKey, /* Foreign key to get action for */
899 ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */
900){
901 sqlite3 *db = pParse->db; /* Database handle */
dan29c7f9c2009-09-22 15:53:47 +0000902 int action; /* One of OE_None, OE_Cascade etc. */
903 Trigger *pTrigger; /* Trigger definition to return */
dan8099ce62009-09-23 08:43:35 +0000904 int iAction = (pChanges!=0); /* 1 for UPDATE, 0 for DELETE */
dan1da40a32009-09-19 17:00:31 +0000905
dan8099ce62009-09-23 08:43:35 +0000906 action = pFKey->aAction[iAction];
907 pTrigger = pFKey->apTrigger[iAction];
dan1da40a32009-09-19 17:00:31 +0000908
dan9277efa2009-09-28 11:54:21 +0000909 if( action!=OE_None && !pTrigger ){
dan29c7f9c2009-09-22 15:53:47 +0000910 u8 enableLookaside; /* Copy of db->lookaside.bEnabled */
dan8099ce62009-09-23 08:43:35 +0000911 char const *zFrom; /* Name of child table */
dan1da40a32009-09-19 17:00:31 +0000912 int nFrom; /* Length in bytes of zFrom */
dan29c7f9c2009-09-22 15:53:47 +0000913 Index *pIdx = 0; /* Parent key index for this FK */
914 int *aiCol = 0; /* child table cols -> parent key cols */
915 TriggerStep *pStep; /* First (only) step of trigger program */
916 Expr *pWhere = 0; /* WHERE clause of trigger step */
917 ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */
dan9277efa2009-09-28 11:54:21 +0000918 Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */
dan29c7f9c2009-09-22 15:53:47 +0000919 int i; /* Iterator variable */
drh788536b2009-09-23 03:01:58 +0000920 Expr *pWhen = 0; /* WHEN clause for the trigger */
dan1da40a32009-09-19 17:00:31 +0000921
922 if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
923 assert( aiCol || pFKey->nCol==1 );
924
dan1da40a32009-09-19 17:00:31 +0000925 for(i=0; i<pFKey->nCol; i++){
dan1da40a32009-09-19 17:00:31 +0000926 Token tOld = { "old", 3 }; /* Literal "old" token */
927 Token tNew = { "new", 3 }; /* Literal "new" token */
dan8099ce62009-09-23 08:43:35 +0000928 Token tFromCol; /* Name of column in child table */
929 Token tToCol; /* Name of column in parent table */
930 int iFromCol; /* Idx of column in child table */
dan29c7f9c2009-09-22 15:53:47 +0000931 Expr *pEq; /* tFromCol = OLD.tToCol */
dan1da40a32009-09-19 17:00:31 +0000932
933 iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
dana8f0bf62009-09-23 12:06:52 +0000934 assert( iFromCol>=0 );
dan1da40a32009-09-19 17:00:31 +0000935 tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
dana8f0bf62009-09-23 12:06:52 +0000936 tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName;
dan1da40a32009-09-19 17:00:31 +0000937
938 tToCol.n = sqlite3Strlen30(tToCol.z);
939 tFromCol.n = sqlite3Strlen30(tFromCol.z);
940
941 /* Create the expression "zFromCol = OLD.zToCol" */
942 pEq = sqlite3PExpr(pParse, TK_EQ,
943 sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol),
944 sqlite3PExpr(pParse, TK_DOT,
945 sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
946 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
947 , 0)
948 , 0);
dan29c7f9c2009-09-22 15:53:47 +0000949 pWhere = sqlite3ExprAnd(db, pWhere, pEq);
dan1da40a32009-09-19 17:00:31 +0000950
drh788536b2009-09-23 03:01:58 +0000951 /* For ON UPDATE, construct the next term of the WHEN clause.
952 ** The final WHEN clause will be like this:
953 **
954 ** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)
955 */
956 if( pChanges ){
957 pEq = sqlite3PExpr(pParse, TK_IS,
958 sqlite3PExpr(pParse, TK_DOT,
959 sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
960 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
961 0),
962 sqlite3PExpr(pParse, TK_DOT,
963 sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
964 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
965 0),
966 0);
967 pWhen = sqlite3ExprAnd(db, pWhen, pEq);
968 }
969
dan9277efa2009-09-28 11:54:21 +0000970 if( action!=OE_Restrict && (action!=OE_Cascade || pChanges) ){
dan1da40a32009-09-19 17:00:31 +0000971 Expr *pNew;
972 if( action==OE_Cascade ){
973 pNew = sqlite3PExpr(pParse, TK_DOT,
974 sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
975 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
976 , 0);
977 }else if( action==OE_SetDflt ){
dan934ce302009-09-22 16:08:58 +0000978 Expr *pDflt = pFKey->pFrom->aCol[iFromCol].pDflt;
dan1da40a32009-09-19 17:00:31 +0000979 if( pDflt ){
980 pNew = sqlite3ExprDup(db, pDflt, 0);
981 }else{
982 pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
983 }
984 }else{
985 pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
986 }
987 pList = sqlite3ExprListAppend(pParse, pList, pNew);
988 sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
989 }
990 }
dan29c7f9c2009-09-22 15:53:47 +0000991 sqlite3DbFree(db, aiCol);
dan1da40a32009-09-19 17:00:31 +0000992
dan9277efa2009-09-28 11:54:21 +0000993 zFrom = pFKey->pFrom->zName;
994 nFrom = sqlite3Strlen30(zFrom);
995
996 if( action==OE_Restrict ){
997 Token tFrom;
998 Expr *pRaise;
999
1000 tFrom.z = zFrom;
1001 tFrom.n = nFrom;
1002 pRaise = sqlite3Expr(db, TK_RAISE, "foreign key constraint failed");
1003 if( pRaise ){
1004 pRaise->affinity = OE_Abort;
1005 }
1006 pSelect = sqlite3SelectNew(pParse,
1007 sqlite3ExprListAppend(pParse, 0, pRaise),
1008 sqlite3SrcListAppend(db, 0, &tFrom, 0),
1009 pWhere,
1010 0, 0, 0, 0, 0, 0
1011 );
1012 pWhere = 0;
1013 }
1014
drh1f638ce2009-09-24 13:48:10 +00001015 /* In the current implementation, pTab->dbMem==0 for all tables except
1016 ** for temporary tables used to describe subqueries. And temporary
1017 ** tables do not have foreign key constraints. Hence, pTab->dbMem
1018 ** should always be 0 there.
1019 */
dan29c7f9c2009-09-22 15:53:47 +00001020 enableLookaside = db->lookaside.bEnabled;
drh46803c32009-09-24 14:27:33 +00001021 db->lookaside.bEnabled = 0;
dan29c7f9c2009-09-22 15:53:47 +00001022
dan29c7f9c2009-09-22 15:53:47 +00001023 pTrigger = (Trigger *)sqlite3DbMallocZero(db,
1024 sizeof(Trigger) + /* struct Trigger */
1025 sizeof(TriggerStep) + /* Single step in trigger program */
1026 nFrom + 1 /* Space for pStep->target.z */
1027 );
1028 if( pTrigger ){
1029 pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
1030 pStep->target.z = (char *)&pStep[1];
1031 pStep->target.n = nFrom;
1032 memcpy((char *)pStep->target.z, zFrom, nFrom);
1033
1034 pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
1035 pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE);
dan9277efa2009-09-28 11:54:21 +00001036 pStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
drh788536b2009-09-23 03:01:58 +00001037 if( pWhen ){
1038 pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0, 0);
1039 pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE);
1040 }
dan29c7f9c2009-09-22 15:53:47 +00001041 }
1042
1043 /* Re-enable the lookaside buffer, if it was disabled earlier. */
1044 db->lookaside.bEnabled = enableLookaside;
1045
drh788536b2009-09-23 03:01:58 +00001046 sqlite3ExprDelete(db, pWhere);
1047 sqlite3ExprDelete(db, pWhen);
1048 sqlite3ExprListDelete(db, pList);
dan9277efa2009-09-28 11:54:21 +00001049 sqlite3SelectDelete(db, pSelect);
dan29c7f9c2009-09-22 15:53:47 +00001050 if( db->mallocFailed==1 ){
1051 fkTriggerDelete(db, pTrigger);
1052 return 0;
1053 }
dan1da40a32009-09-19 17:00:31 +00001054
dan9277efa2009-09-28 11:54:21 +00001055 switch( action ){
1056 case OE_Restrict:
1057 pStep->op = TK_SELECT;
1058 break;
1059 case OE_Cascade:
1060 if( !pChanges ){
1061 pStep->op = TK_DELETE;
1062 break;
1063 }
1064 default:
1065 pStep->op = TK_UPDATE;
1066 }
dan1da40a32009-09-19 17:00:31 +00001067 pStep->pTrig = pTrigger;
1068 pTrigger->pSchema = pTab->pSchema;
1069 pTrigger->pTabSchema = pTab->pSchema;
dan8099ce62009-09-23 08:43:35 +00001070 pFKey->apTrigger[iAction] = pTrigger;
1071 pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE);
dan1da40a32009-09-19 17:00:31 +00001072 }
1073
1074 return pTrigger;
1075}
1076
dan1da40a32009-09-19 17:00:31 +00001077/*
1078** This function is called when deleting or updating a row to implement
1079** any required CASCADE, SET NULL or SET DEFAULT actions.
1080*/
1081void sqlite3FkActions(
1082 Parse *pParse, /* Parse context */
1083 Table *pTab, /* Table being updated or deleted from */
1084 ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */
1085 int regOld /* Address of array containing old row */
1086){
1087 /* If foreign-key support is enabled, iterate through all FKs that
1088 ** refer to table pTab. If there is an action associated with the FK
1089 ** for this operation (either update or delete), invoke the associated
1090 ** trigger sub-program. */
1091 if( pParse->db->flags&SQLITE_ForeignKeys ){
1092 FKey *pFKey; /* Iterator variable */
dan432cc5b2009-09-26 17:51:48 +00001093 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
dan1da40a32009-09-19 17:00:31 +00001094 Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
1095 if( pAction ){
1096 sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);
1097 }
1098 }
1099 }
1100}
1101
dan75cbd982009-09-21 16:06:03 +00001102#endif /* ifndef SQLITE_OMIT_TRIGGER */
1103
dan1da40a32009-09-19 17:00:31 +00001104/*
1105** Free all memory associated with foreign key definitions attached to
1106** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
1107** hash table.
1108*/
1109void sqlite3FkDelete(Table *pTab){
1110 FKey *pFKey; /* Iterator variable */
1111 FKey *pNext; /* Copy of pFKey->pNextFrom */
1112
1113 for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){
1114
1115 /* Remove the FK from the fkeyHash hash table. */
1116 if( pFKey->pPrevTo ){
1117 pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
1118 }else{
1119 void *data = (void *)pFKey->pNextTo;
1120 const char *z = (data ? pFKey->pNextTo->zTo : pFKey->zTo);
1121 sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, sqlite3Strlen30(z), data);
1122 }
1123 if( pFKey->pNextTo ){
1124 pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
1125 }
1126
1127 /* Delete any triggers created to implement actions for this FK. */
dan75cbd982009-09-21 16:06:03 +00001128#ifndef SQLITE_OMIT_TRIGGER
dan8099ce62009-09-23 08:43:35 +00001129 fkTriggerDelete(pTab->dbMem, pFKey->apTrigger[0]);
1130 fkTriggerDelete(pTab->dbMem, pFKey->apTrigger[1]);
dan75cbd982009-09-21 16:06:03 +00001131#endif
dan1da40a32009-09-19 17:00:31 +00001132
1133 /* Delete the memory allocated for the FK structure. */
1134 pNext = pFKey->pNextFrom;
1135 sqlite3DbFree(pTab->dbMem, pFKey);
1136 }
1137}
dan75cbd982009-09-21 16:06:03 +00001138#endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */