wasCSharpSQLite – Blame information for rev 1
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | using System; |
2 | using System.Diagnostics; |
||
3 | using System.Text; |
||
4 | |||
5 | using Bitmask = System.UInt64; |
||
6 | using u8 = System.Byte; |
||
7 | using u32 = System.UInt32; |
||
8 | |||
9 | namespace Community.CsharpSqlite |
||
10 | { |
||
11 | public partial class Sqlite3 |
||
12 | { |
||
13 | /* |
||
14 | ** |
||
15 | ** The author disclaims copyright to this source code. In place of |
||
16 | ** a legal notice, here is a blessing: |
||
17 | ** |
||
18 | ** May you do good and not evil. |
||
19 | ** May you find forgiveness for yourself and forgive others. |
||
20 | ** May you share freely, never taking more than you give. |
||
21 | ** |
||
22 | ************************************************************************* |
||
23 | ** This file contains code used by the compiler to add foreign key |
||
24 | ** support to compiled SQL statements. |
||
25 | ************************************************************************* |
||
26 | ** Included in SQLite3 port to C#-SQLite; 2008 Noah B Hart |
||
27 | ** C#-SQLite is an independent reimplementation of the SQLite software library |
||
28 | ** |
||
29 | ** SQLITE_SOURCE_ID: 2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c936272862f32f2 |
||
30 | ** |
||
31 | ************************************************************************* */ |
||
32 | //#include "sqliteInt.h" |
||
33 | |||
34 | #if !SQLITE_OMIT_FOREIGN_KEY |
||
35 | #if !SQLITE_OMIT_TRIGGER |
||
36 | |||
37 | /* |
||
38 | ** Deferred and Immediate FKs |
||
39 | ** -------------------------- |
||
40 | ** |
||
41 | ** Foreign keys in SQLite come in two flavours: deferred and immediate. |
||
42 | ** If an immediate foreign key constraint is violated, SQLITE_CONSTRAINT |
||
43 | ** is returned and the current statement transaction rolled back. If a |
||
44 | ** deferred foreign key constraint is violated, no action is taken |
||
45 | ** immediately. However if the application attempts to commit the |
||
46 | ** transaction before fixing the constraint violation, the attempt fails. |
||
47 | ** |
||
48 | ** Deferred constraints are implemented using a simple counter associated |
||
49 | ** with the database handle. The counter is set to zero each time a |
||
50 | ** database transaction is opened. Each time a statement is executed |
||
51 | ** that causes a foreign key violation, the counter is incremented. Each |
||
52 | ** time a statement is executed that removes an existing violation from |
||
53 | ** the database, the counter is decremented. When the transaction is |
||
54 | ** committed, the commit fails if the current value of the counter is |
||
55 | ** greater than zero. This scheme has two big drawbacks: |
||
56 | ** |
||
57 | ** * When a commit fails due to a deferred foreign key constraint, |
||
58 | ** there is no way to tell which foreign constraint is not satisfied, |
||
59 | ** or which row it is not satisfied for. |
||
60 | ** |
||
61 | ** * If the database contains foreign key violations when the |
||
62 | ** transaction is opened, this may cause the mechanism to malfunction. |
||
63 | ** |
||
64 | ** Despite these problems, this approach is adopted as it seems simpler |
||
65 | ** than the alternatives. |
||
66 | ** |
||
67 | ** INSERT operations: |
||
68 | ** |
||
69 | ** I.1) For each FK for which the table is the child table, search |
||
70 | ** the parent table for a match. If none is found increment the |
||
71 | ** constraint counter. |
||
72 | ** |
||
73 | ** I.2) For each FK for which the table is the parent table, |
||
74 | ** search the child table for rows that correspond to the new |
||
75 | ** row in the parent table. Decrement the counter for each row |
||
76 | ** found (as the constraint is now satisfied). |
||
77 | ** |
||
78 | ** DELETE operations: |
||
79 | ** |
||
80 | ** D.1) For each FK for which the table is the child table, |
||
81 | ** search the parent table for a row that corresponds to the |
||
82 | ** deleted row in the child table. If such a row is not found, |
||
83 | ** decrement the counter. |
||
84 | ** |
||
85 | ** D.2) For each FK for which the table is the parent table, search |
||
86 | ** the child table for rows that correspond to the deleted row |
||
87 | ** in the parent table. For each found increment the counter. |
||
88 | ** |
||
89 | ** UPDATE operations: |
||
90 | ** |
||
91 | ** An UPDATE command requires that all 4 steps above are taken, but only |
||
92 | ** for FK constraints for which the affected columns are actually |
||
93 | ** modified (values must be compared at runtime). |
||
94 | ** |
||
95 | ** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2. |
||
96 | ** This simplifies the implementation a bit. |
||
97 | ** |
||
98 | ** For the purposes of immediate FK constraints, the OR REPLACE conflict |
||
99 | ** resolution is considered to delete rows before the new row is inserted. |
||
100 | ** If a delete caused by OR REPLACE violates an FK constraint, an exception |
||
101 | ** is thrown, even if the FK constraint would be satisfied after the new |
||
102 | ** row is inserted. |
||
103 | ** |
||
104 | ** Immediate constraints are usually handled similarly. The only difference |
||
105 | ** is that the counter used is stored as part of each individual statement |
||
106 | ** object (struct Vdbe). If, after the statement has run, its immediate |
||
107 | ** constraint counter is greater than zero, it returns SQLITE_CONSTRAINT |
||
108 | ** and the statement transaction is rolled back. An exception is an INSERT |
||
109 | ** statement that inserts a single row only (no triggers). In this case, |
||
110 | ** instead of using a counter, an exception is thrown immediately if the |
||
111 | ** INSERT violates a foreign key constraint. This is necessary as such |
||
112 | ** an INSERT does not open a statement transaction. |
||
113 | ** |
||
114 | ** TODO: How should dropping a table be handled? How should renaming a |
||
115 | ** table be handled? |
||
116 | ** |
||
117 | ** |
||
118 | ** Query API Notes |
||
119 | ** --------------- |
||
120 | ** |
||
121 | ** Before coding an UPDATE or DELETE row operation, the code-generator |
||
122 | ** for those two operations needs to know whether or not the operation |
||
123 | ** requires any FK processing and, if so, which columns of the original |
||
124 | ** row are required by the FK processing VDBE code (i.e. if FKs were |
||
125 | ** implemented using triggers, which of the old.* columns would be |
||
126 | ** accessed). No information is required by the code-generator before |
||
127 | ** coding an INSERT operation. The functions used by the UPDATE/DELETE |
||
128 | ** generation code to query for this information are: |
||
129 | ** |
||
130 | ** sqlite3FkRequired() - Test to see if FK processing is required. |
||
131 | ** sqlite3FkOldmask() - Query for the set of required old.* columns. |
||
132 | ** |
||
133 | ** |
||
134 | ** Externally accessible module functions |
||
135 | ** -------------------------------------- |
||
136 | ** |
||
137 | ** sqlite3FkCheck() - Check for foreign key violations. |
||
138 | ** sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions. |
||
139 | ** sqlite3FkDelete() - Delete an FKey structure. |
||
140 | */ |
||
141 | |||
142 | /* |
||
143 | ** VDBE Calling Convention |
||
144 | ** ----------------------- |
||
145 | ** |
||
146 | ** Example: |
||
147 | ** |
||
148 | ** For the following INSERT statement: |
||
149 | ** |
||
150 | ** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c); |
||
151 | ** INSERT INTO t1 VALUES(1, 2, 3.1); |
||
152 | ** |
||
153 | ** Register (x): 2 (type integer) |
||
154 | ** Register (x+1): 1 (type integer) |
||
155 | ** Register (x+2): NULL (type NULL) |
||
156 | ** Register (x+3): 3.1 (type real) |
||
157 | */ |
||
158 | |||
159 | /* |
||
160 | ** A foreign key constraint requires that the key columns in the parent |
||
161 | ** table are collectively subject to a UNIQUE or PRIMARY KEY constraint. |
||
162 | ** Given that pParent is the parent table for foreign key constraint pFKey, |
||
163 | ** search the schema a unique index on the parent key columns. |
||
164 | ** |
||
165 | ** If successful, zero is returned. If the parent key is an INTEGER PRIMARY |
||
166 | ** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx |
||
167 | ** is set to point to the unique index. |
||
168 | ** |
||
169 | ** If the parent key consists of a single column (the foreign key constraint |
||
170 | ** is not a composite foreign key), refput variable *paiCol is set to NULL. |
||
171 | ** Otherwise, it is set to point to an allocated array of size N, where |
||
172 | ** N is the number of columns in the parent key. The first element of the |
||
173 | ** array is the index of the child table column that is mapped by the FK |
||
174 | ** constraint to the parent table column stored in the left-most column |
||
175 | ** of index *ppIdx. The second element of the array is the index of the |
||
176 | ** child table column that corresponds to the second left-most column of |
||
177 | ** *ppIdx, and so on. |
||
178 | ** |
||
179 | ** If the required index cannot be found, either because: |
||
180 | ** |
||
181 | ** 1) The named parent key columns do not exist, or |
||
182 | ** |
||
183 | ** 2) The named parent key columns do exist, but are not subject to a |
||
184 | ** UNIQUE or PRIMARY KEY constraint, or |
||
185 | ** |
||
186 | ** 3) No parent key columns were provided explicitly as part of the |
||
187 | ** foreign key definition, and the parent table does not have a |
||
188 | ** PRIMARY KEY, or |
||
189 | ** |
||
190 | ** 4) No parent key columns were provided explicitly as part of the |
||
191 | ** foreign key definition, and the PRIMARY KEY of the parent table |
||
192 | ** consists of a different number of columns to the child key in |
||
193 | ** the child table. |
||
194 | ** |
||
195 | ** then non-zero is returned, and a "foreign key mismatch" error loaded |
||
196 | ** into pParse. If an OOM error occurs, non-zero is returned and the |
||
197 | ** pParse.db.mallocFailed flag is set. |
||
198 | */ |
||
199 | static int locateFkeyIndex( |
||
200 | Parse pParse, /* Parse context to store any error in */ |
||
201 | Table pParent, /* Parent table of FK constraint pFKey */ |
||
202 | FKey pFKey, /* Foreign key to find index for */ |
||
203 | out Index ppIdx, /* OUT: Unique index on parent table */ |
||
204 | out int[] paiCol /* OUT: Map of index columns in pFKey */ |
||
205 | ) |
||
206 | { |
||
207 | Index pIdx = null; /* Value to return via *ppIdx */ |
||
208 | ppIdx = null; |
||
209 | int[] aiCol = null; /* Value to return via *paiCol */ |
||
210 | paiCol = null; |
||
211 | |||
212 | int nCol = pFKey.nCol; /* Number of columns in parent key */ |
||
213 | string zKey = pFKey.aCol[0].zCol; /* Name of left-most parent key column */ |
||
214 | |||
215 | /* The caller is responsible for zeroing output parameters. */ |
||
216 | //assert( ppIdx && *ppIdx==0 ); |
||
217 | //assert( !paiCol || *paiCol==0 ); |
||
218 | Debug.Assert( pParse != null ); |
||
219 | |||
220 | /* If this is a non-composite (single column) foreign key, check if it |
||
221 | ** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx |
||
222 | ** and *paiCol set to zero and return early. |
||
223 | ** |
||
224 | ** Otherwise, for a composite foreign key (more than one column), allocate |
||
225 | ** space for the aiCol array (returned via output parameter *paiCol). |
||
226 | ** Non-composite foreign keys do not require the aiCol array. |
||
227 | */ |
||
228 | if ( nCol == 1 ) |
||
229 | { |
||
230 | /* The FK maps to the IPK if any of the following are true: |
||
231 | ** |
||
232 | ** 1) There is an INTEGER PRIMARY KEY column and the FK is implicitly |
||
233 | ** mapped to the primary key of table pParent, or |
||
234 | ** 2) The FK is explicitly mapped to a column declared as INTEGER |
||
235 | ** PRIMARY KEY. |
||
236 | */ |
||
237 | if ( pParent.iPKey >= 0 ) |
||
238 | { |
||
239 | if ( null == zKey ) |
||
240 | return 0; |
||
241 | if ( pParent.aCol[pParent.iPKey].zName.Equals( zKey ,StringComparison.OrdinalIgnoreCase ) ) |
||
242 | return 0; |
||
243 | } |
||
244 | } |
||
245 | else //if( paiCol ){ |
||
246 | { |
||
247 | Debug.Assert( nCol > 1 ); |
||
248 | aiCol = new int[nCol];// (int*)sqlite3DbMallocRaw( pParse.db, nCol * sizeof( int ) ); |
||
249 | //if( !aiCol ) return 1; |
||
250 | paiCol = aiCol; |
||
251 | } |
||
252 | |||
253 | for ( pIdx = pParent.pIndex; pIdx != null; pIdx = pIdx.pNext ) |
||
254 | { |
||
255 | if ( pIdx.nColumn == nCol && pIdx.onError != OE_None ) |
||
256 | { |
||
257 | /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number |
||
258 | ** of columns. If each indexed column corresponds to a foreign key |
||
259 | ** column of pFKey, then this index is a winner. */ |
||
260 | |||
261 | if ( zKey == null ) |
||
262 | { |
||
263 | /* If zKey is NULL, then this foreign key is implicitly mapped to |
||
264 | ** the PRIMARY KEY of table pParent. The PRIMARY KEY index may be |
||
265 | ** identified by the test (Index.autoIndex==2). */ |
||
266 | if ( pIdx.autoIndex == 2 ) |
||
267 | { |
||
268 | if ( aiCol != null ) |
||
269 | { |
||
270 | int i; |
||
271 | for ( i = 0; i < nCol; i++ ) |
||
272 | aiCol[i] = pFKey.aCol[i].iFrom; |
||
273 | } |
||
274 | break; |
||
275 | } |
||
276 | } |
||
277 | else |
||
278 | { |
||
279 | /* If zKey is non-NULL, then this foreign key was declared to |
||
280 | ** map to an explicit list of columns in table pParent. Check if this |
||
281 | ** index matches those columns. Also, check that the index uses |
||
282 | ** the default collation sequences for each column. */ |
||
283 | int i, j; |
||
284 | for ( i = 0; i < nCol; i++ ) |
||
285 | { |
||
286 | int iCol = pIdx.aiColumn[i]; /* Index of column in parent tbl */ |
||
287 | string zDfltColl; /* Def. collation for column */ |
||
288 | string zIdxCol; /* Name of indexed column */ |
||
289 | |||
290 | /* If the index uses a collation sequence that is different from |
||
291 | ** the default collation sequence for the column, this index is |
||
292 | ** unusable. Bail out early in this case. */ |
||
293 | zDfltColl = pParent.aCol[iCol].zColl; |
||
294 | if ( string.IsNullOrEmpty( zDfltColl ) ) |
||
295 | { |
||
296 | zDfltColl = "BINARY"; |
||
297 | } |
||
298 | if ( !pIdx.azColl[i].Equals( zDfltColl ,StringComparison.OrdinalIgnoreCase ) ) |
||
299 | break; |
||
300 | |||
301 | zIdxCol = pParent.aCol[iCol].zName; |
||
302 | for ( j = 0; j < nCol; j++ ) |
||
303 | { |
||
304 | if ( pFKey.aCol[j].zCol.Equals( zIdxCol ,StringComparison.OrdinalIgnoreCase ) ) |
||
305 | { |
||
306 | if ( aiCol != null ) |
||
307 | aiCol[i] = pFKey.aCol[j].iFrom; |
||
308 | break; |
||
309 | } |
||
310 | } |
||
311 | if ( j == nCol ) |
||
312 | break; |
||
313 | } |
||
314 | if ( i == nCol ) |
||
315 | break; /* pIdx is usable */ |
||
316 | } |
||
317 | } |
||
318 | } |
||
319 | |||
320 | if ( null == pIdx ) |
||
321 | { |
||
322 | if ( 0 == pParse.disableTriggers ) |
||
323 | { |
||
324 | sqlite3ErrorMsg( pParse, "foreign key mismatch" ); |
||
325 | } |
||
326 | sqlite3DbFree( pParse.db, ref aiCol ); |
||
327 | return 1; |
||
328 | } |
||
329 | |||
330 | ppIdx = pIdx; |
||
331 | return 0; |
||
332 | } |
||
333 | |||
334 | /* |
||
335 | ** This function is called when a row is inserted into or deleted from the |
||
336 | ** child table of foreign key constraint pFKey. If an SQL UPDATE is executed |
||
337 | ** on the child table of pFKey, this function is invoked twice for each row |
||
338 | ** affected - once to "delete" the old row, and then again to "insert" the |
||
339 | ** new row. |
||
340 | ** |
||
341 | ** Each time it is called, this function generates VDBE code to locate the |
||
342 | ** row in the parent table that corresponds to the row being inserted into |
||
343 | ** or deleted from the child table. If the parent row can be found, no |
||
344 | ** special action is taken. Otherwise, if the parent row can *not* be |
||
345 | ** found in the parent table: |
||
346 | ** |
||
347 | ** Operation | FK type | Action taken |
||
348 | ** -------------------------------------------------------------------------- |
||
349 | ** INSERT immediate Increment the "immediate constraint counter". |
||
350 | ** |
||
351 | ** DELETE immediate Decrement the "immediate constraint counter". |
||
352 | ** |
||
353 | ** INSERT deferred Increment the "deferred constraint counter". |
||
354 | ** |
||
355 | ** DELETE deferred Decrement the "deferred constraint counter". |
||
356 | ** |
||
357 | ** These operations are identified in the comment at the top of this file |
||
358 | ** (fkey.c) as "I.1" and "D.1". |
||
359 | */ |
||
360 | static void fkLookupParent( |
||
361 | Parse pParse, /* Parse context */ |
||
362 | int iDb, /* Index of database housing pTab */ |
||
363 | Table pTab, /* Parent table of FK pFKey */ |
||
364 | Index pIdx, /* Unique index on parent key columns in pTab */ |
||
365 | FKey pFKey, /* Foreign key constraint */ |
||
366 | int[] aiCol, /* Map from parent key columns to child table columns */ |
||
367 | int regData, /* Address of array containing child table row */ |
||
368 | int nIncr, /* Increment constraint counter by this */ |
||
369 | int isIgnore /* If true, pretend pTab contains all NULL values */ |
||
370 | ) |
||
371 | { |
||
372 | int i; /* Iterator variable */ |
||
373 | Vdbe v = sqlite3GetVdbe( pParse ); /* Vdbe to add code to */ |
||
374 | int iCur = pParse.nTab - 1; /* Cursor number to use */ |
||
375 | int iOk = sqlite3VdbeMakeLabel( v ); /* jump here if parent key found */ |
||
376 | |||
377 | /* If nIncr is less than zero, then check at runtime if there are any |
||
378 | ** outstanding constraints to resolve. If there are not, there is no need |
||
379 | ** to check if deleting this row resolves any outstanding violations. |
||
380 | ** |
||
381 | ** Check if any of the key columns in the child table row are NULL. If |
||
382 | ** any are, then the constraint is considered satisfied. No need to |
||
383 | ** search for a matching row in the parent table. */ |
||
384 | if ( nIncr < 0 ) |
||
385 | { |
||
386 | sqlite3VdbeAddOp2( v, OP_FkIfZero, pFKey.isDeferred, iOk ); |
||
387 | } |
||
388 | for ( i = 0; i < pFKey.nCol; i++ ) |
||
389 | { |
||
390 | int iReg = aiCol[i] + regData + 1; |
||
391 | sqlite3VdbeAddOp2( v, OP_IsNull, iReg, iOk ); |
||
392 | } |
||
393 | |||
394 | if ( isIgnore == 0 ) |
||
395 | { |
||
396 | if ( pIdx == null ) |
||
397 | { |
||
398 | /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY |
||
399 | ** column of the parent table (table pTab). */ |
||
400 | int iMustBeInt; /* Address of MustBeInt instruction */ |
||
401 | int regTemp = sqlite3GetTempReg( pParse ); |
||
402 | |||
403 | /* Invoke MustBeInt to coerce the child key value to an integer (i.e. |
||
404 | ** apply the affinity of the parent key). If this fails, then there |
||
405 | ** is no matching parent key. Before using MustBeInt, make a copy of |
||
406 | ** the value. Otherwise, the value inserted into the child key column |
||
407 | ** will have INTEGER affinity applied to it, which may not be correct. */ |
||
408 | sqlite3VdbeAddOp2( v, OP_SCopy, aiCol[0] + 1 + regData, regTemp ); |
||
409 | iMustBeInt = sqlite3VdbeAddOp2( v, OP_MustBeInt, regTemp, 0 ); |
||
410 | |||
411 | /* If the parent table is the same as the child table, and we are about |
||
412 | ** to increment the constraint-counter (i.e. this is an INSERT operation), |
||
413 | ** then check if the row being inserted matches itself. If so, do not |
||
414 | ** increment the constraint-counter. */ |
||
415 | if ( pTab == pFKey.pFrom && nIncr == 1 ) |
||
416 | { |
||
417 | sqlite3VdbeAddOp3( v, OP_Eq, regData, iOk, regTemp ); |
||
418 | } |
||
419 | |||
420 | sqlite3OpenTable( pParse, iCur, iDb, pTab, OP_OpenRead ); |
||
421 | sqlite3VdbeAddOp3( v, OP_NotExists, iCur, 0, regTemp ); |
||
422 | sqlite3VdbeAddOp2( v, OP_Goto, 0, iOk ); |
||
423 | sqlite3VdbeJumpHere( v, sqlite3VdbeCurrentAddr( v ) - 2 ); |
||
424 | sqlite3VdbeJumpHere( v, iMustBeInt ); |
||
425 | sqlite3ReleaseTempReg( pParse, regTemp ); |
||
426 | } |
||
427 | else |
||
428 | { |
||
429 | int nCol = pFKey.nCol; |
||
430 | int regTemp = sqlite3GetTempRange( pParse, nCol ); |
||
431 | int regRec = sqlite3GetTempReg( pParse ); |
||
432 | KeyInfo pKey = sqlite3IndexKeyinfo( pParse, pIdx ); |
||
433 | |||
434 | sqlite3VdbeAddOp3( v, OP_OpenRead, iCur, pIdx.tnum, iDb ); |
||
435 | sqlite3VdbeChangeP4( v, -1, pKey, P4_KEYINFO_HANDOFF ); |
||
436 | for ( i = 0; i < nCol; i++ ) |
||
437 | { |
||
438 | sqlite3VdbeAddOp2( v, OP_Copy, aiCol[i] + 1 + regData, regTemp + i ); |
||
439 | } |
||
440 | |||
441 | /* If the parent table is the same as the child table, and we are about |
||
442 | ** to increment the constraint-counter (i.e. this is an INSERT operation), |
||
443 | ** then check if the row being inserted matches itself. If so, do not |
||
444 | ** increment the constraint-counter. |
||
445 | ** |
||
446 | ** If any of the parent-key values are NULL, then the row cannot match |
||
447 | ** itself. So set JUMPIFNULL to make sure we do the OP_Found if any |
||
448 | ** of the parent-key values are NULL (at this point it is known that |
||
449 | ** none of the child key values are). |
||
450 | */ |
||
451 | if ( pTab == pFKey.pFrom && nIncr == 1 ) |
||
452 | { |
||
453 | int iJump = sqlite3VdbeCurrentAddr( v ) + nCol + 1; |
||
454 | for ( i = 0; i < nCol; i++ ) |
||
455 | { |
||
456 | int iChild = aiCol[i] + 1 + regData; |
||
457 | int iParent = pIdx.aiColumn[i] + 1 + regData; |
||
458 | Debug.Assert( aiCol[i] != pTab.iPKey ); |
||
459 | if ( pIdx.aiColumn[i] == pTab.iPKey ) |
||
460 | { |
||
461 | /* The parent key is a composite key that includes the IPK column */ |
||
462 | iParent = regData; |
||
463 | } |
||
464 | sqlite3VdbeAddOp3( v, OP_Ne, iChild, iJump, iParent ); |
||
465 | sqlite3VdbeChangeP5( v, SQLITE_JUMPIFNULL ); |
||
466 | } |
||
467 | sqlite3VdbeAddOp2( v, OP_Goto, 0, iOk ); |
||
468 | } |
||
469 | |||
470 | sqlite3VdbeAddOp3( v, OP_MakeRecord, regTemp, nCol, regRec ); |
||
471 | sqlite3VdbeChangeP4( v, -1, sqlite3IndexAffinityStr( v, pIdx ), P4_TRANSIENT ); |
||
472 | sqlite3VdbeAddOp4Int( v, OP_Found, iCur, iOk, regRec, 0 ); |
||
473 | |||
474 | sqlite3ReleaseTempReg( pParse, regRec ); |
||
475 | sqlite3ReleaseTempRange( pParse, regTemp, nCol ); |
||
476 | } |
||
477 | } |
||
478 | |||
479 | if ( 0 == pFKey.isDeferred && null == pParse.pToplevel && 0 == pParse.isMultiWrite ) |
||
480 | { |
||
481 | /* Special case: If this is an INSERT statement that will insert exactly |
||
482 | ** one row into the table, raise a constraint immediately instead of |
||
483 | ** incrementing a counter. This is necessary as the VM code is being |
||
484 | ** generated for will not open a statement transaction. */ |
||
485 | Debug.Assert( nIncr == 1 ); |
||
486 | sqlite3HaltConstraint( |
||
487 | pParse, OE_Abort, "foreign key constraint failed", P4_STATIC |
||
488 | ); |
||
489 | } |
||
490 | else |
||
491 | { |
||
492 | if ( nIncr > 0 && pFKey.isDeferred == 0 ) |
||
493 | { |
||
494 | sqlite3ParseToplevel( pParse ).mayAbort = 1; |
||
495 | } |
||
496 | sqlite3VdbeAddOp2( v, OP_FkCounter, pFKey.isDeferred, nIncr ); |
||
497 | } |
||
498 | |||
499 | sqlite3VdbeResolveLabel( v, iOk ); |
||
500 | sqlite3VdbeAddOp1( v, OP_Close, iCur ); |
||
501 | } |
||
502 | |||
503 | /* |
||
504 | ** This function is called to generate code executed when a row is deleted |
||
505 | ** from the parent table of foreign key constraint pFKey and, if pFKey is |
||
506 | ** deferred, when a row is inserted into the same table. When generating |
||
507 | ** code for an SQL UPDATE operation, this function may be called twice - |
||
508 | ** once to "delete" the old row and once to "insert" the new row. |
||
509 | ** |
||
510 | ** The code generated by this function scans through the rows in the child |
||
511 | ** table that correspond to the parent table row being deleted or inserted. |
||
512 | ** For each child row found, one of the following actions is taken: |
||
513 | ** |
||
514 | ** Operation | FK type | Action taken |
||
515 | ** -------------------------------------------------------------------------- |
||
516 | ** DELETE immediate Increment the "immediate constraint counter". |
||
517 | ** Or, if the ON (UPDATE|DELETE) action is RESTRICT, |
||
518 | ** throw a "foreign key constraint failed" exception. |
||
519 | ** |
||
520 | ** INSERT immediate Decrement the "immediate constraint counter". |
||
521 | ** |
||
522 | ** DELETE deferred Increment the "deferred constraint counter". |
||
523 | ** Or, if the ON (UPDATE|DELETE) action is RESTRICT, |
||
524 | ** throw a "foreign key constraint failed" exception. |
||
525 | ** |
||
526 | ** INSERT deferred Decrement the "deferred constraint counter". |
||
527 | ** |
||
528 | ** These operations are identified in the comment at the top of this file |
||
529 | ** (fkey.c) as "I.2" and "D.2". |
||
530 | */ |
||
531 | static void fkScanChildren( |
||
532 | Parse pParse, /* Parse context */ |
||
533 | SrcList pSrc, /* SrcList containing the table to scan */ |
||
534 | Table pTab, |
||
535 | Index pIdx, /* Foreign key index */ |
||
536 | FKey pFKey, /* Foreign key relationship */ |
||
537 | int[] aiCol, /* Map from pIdx cols to child table cols */ |
||
538 | int regData, /* Referenced table data starts here */ |
||
539 | int nIncr /* Amount to increment deferred counter by */ |
||
540 | ) |
||
541 | { |
||
542 | sqlite3 db = pParse.db; /* Database handle */ |
||
543 | int i; /* Iterator variable */ |
||
544 | Expr pWhere = null; /* WHERE clause to scan with */ |
||
545 | NameContext sNameContext; /* Context used to resolve WHERE clause */ |
||
546 | WhereInfo pWInfo; /* Context used by sqlite3WhereXXX() */ |
||
547 | int iFkIfZero = 0; /* Address of OP_FkIfZero */ |
||
548 | Vdbe v = sqlite3GetVdbe( pParse ); |
||
549 | |||
550 | Debug.Assert( null == pIdx || pIdx.pTable == pTab ); |
||
551 | |||
552 | if ( nIncr < 0 ) |
||
553 | { |
||
554 | iFkIfZero = sqlite3VdbeAddOp2( v, OP_FkIfZero, pFKey.isDeferred, 0 ); |
||
555 | } |
||
556 | |||
557 | /* Create an Expr object representing an SQL expression like: |
||
558 | ** |
||
559 | ** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ... |
||
560 | ** |
||
561 | ** The collation sequence used for the comparison should be that of |
||
562 | ** the parent key columns. The affinity of the parent key column should |
||
563 | ** be applied to each child key value before the comparison takes place. |
||
564 | */ |
||
565 | for ( i = 0; i < pFKey.nCol; i++ ) |
||
566 | { |
||
567 | Expr pLeft; /* Value from parent table row */ |
||
568 | Expr pRight; /* Column ref to child table */ |
||
569 | Expr pEq; /* Expression (pLeft = pRight) */ |
||
570 | int iCol; /* Index of column in child table */ |
||
571 | string zCol; /* Name of column in child table */ |
||
572 | |||
573 | pLeft = sqlite3Expr( db, TK_REGISTER, null ); |
||
574 | if ( pLeft != null ) |
||
575 | { |
||
576 | /* Set the collation sequence and affinity of the LHS of each TK_EQ |
||
577 | ** expression to the parent key column defaults. */ |
||
578 | if ( pIdx != null ) |
||
579 | { |
||
580 | Column pCol; |
||
581 | iCol = pIdx.aiColumn[i]; |
||
582 | pCol = pTab.aCol[iCol]; |
||
583 | if ( pTab.iPKey == iCol ) |
||
584 | iCol = -1; |
||
585 | pLeft.iTable = regData + iCol + 1; |
||
586 | pLeft.affinity = pCol.affinity; |
||
587 | pLeft.pColl = sqlite3LocateCollSeq( pParse, pCol.zColl ); |
||
588 | } |
||
589 | else |
||
590 | { |
||
591 | pLeft.iTable = regData; |
||
592 | pLeft.affinity = SQLITE_AFF_INTEGER; |
||
593 | } |
||
594 | } |
||
595 | iCol = aiCol != null ? aiCol[i] : pFKey.aCol[0].iFrom; |
||
596 | Debug.Assert( iCol >= 0 ); |
||
597 | zCol = pFKey.pFrom.aCol[iCol].zName; |
||
598 | pRight = sqlite3Expr( db, TK_ID, zCol ); |
||
599 | pEq = sqlite3PExpr( pParse, TK_EQ, pLeft, pRight, 0 ); |
||
600 | pWhere = sqlite3ExprAnd( db, pWhere, pEq ); |
||
601 | } |
||
602 | |||
603 | /* If the child table is the same as the parent table, and this scan |
||
604 | ** is taking place as part of a DELETE operation (operation D.2), omit the |
||
605 | ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE |
||
606 | ** clause, where $rowid is the rowid of the row being deleted. */ |
||
607 | if ( pTab == pFKey.pFrom && nIncr > 0 ) |
||
608 | { |
||
609 | Expr pEq; /* Expression (pLeft = pRight) */ |
||
610 | Expr pLeft; /* Value from parent table row */ |
||
611 | Expr pRight; /* Column ref to child table */ |
||
612 | pLeft = sqlite3Expr( db, TK_REGISTER, null ); |
||
613 | pRight = sqlite3Expr( db, TK_COLUMN, null ); |
||
614 | if ( pLeft != null && pRight != null ) |
||
615 | { |
||
616 | pLeft.iTable = regData; |
||
617 | pLeft.affinity = SQLITE_AFF_INTEGER; |
||
618 | pRight.iTable = pSrc.a[0].iCursor; |
||
619 | pRight.iColumn = -1; |
||
620 | } |
||
621 | pEq = sqlite3PExpr( pParse, TK_NE, pLeft, pRight, 0 ); |
||
622 | pWhere = sqlite3ExprAnd( db, pWhere, pEq ); |
||
623 | } |
||
624 | |||
625 | /* Resolve the references in the WHERE clause. */ |
||
626 | sNameContext = new NameContext();// memset( &sNameContext, 0, sizeof( NameContext ) ); |
||
627 | sNameContext.pSrcList = pSrc; |
||
628 | sNameContext.pParse = pParse; |
||
629 | sqlite3ResolveExprNames( sNameContext, ref pWhere ); |
||
630 | |||
631 | /* Create VDBE to loop through the entries in pSrc that match the WHERE |
||
632 | ** clause. If the constraint is not deferred, throw an exception for |
||
633 | ** each row found. Otherwise, for deferred constraints, increment the |
||
634 | ** deferred constraint counter by nIncr for each row selected. */ |
||
635 | ExprList elDummy = null; |
||
636 | pWInfo = sqlite3WhereBegin( pParse, pSrc, pWhere, ref elDummy, 0 ); |
||
637 | if ( nIncr > 0 && pFKey.isDeferred == 0 ) |
||
638 | { |
||
639 | sqlite3ParseToplevel( pParse ).mayAbort = 1; |
||
640 | } |
||
641 | sqlite3VdbeAddOp2( v, OP_FkCounter, pFKey.isDeferred, nIncr ); |
||
642 | if ( pWInfo != null ) |
||
643 | { |
||
644 | sqlite3WhereEnd( pWInfo ); |
||
645 | } |
||
646 | |||
647 | /* Clean up the WHERE clause constructed above. */ |
||
648 | sqlite3ExprDelete( db, ref pWhere ); |
||
649 | if ( iFkIfZero != 0 ) |
||
650 | { |
||
651 | sqlite3VdbeJumpHere( v, iFkIfZero ); |
||
652 | } |
||
653 | } |
||
654 | |||
655 | /* |
||
656 | ** This function returns a pointer to the head of a linked list of FK |
||
657 | ** constraints for which table pTab is the parent table. For example, |
||
658 | ** given the following schema: |
||
659 | ** |
||
660 | ** CREATE TABLE t1(a PRIMARY KEY); |
||
661 | ** CREATE TABLE t2(b REFERENCES t1(a); |
||
662 | ** |
||
663 | ** Calling this function with table "t1" as an argument returns a pointer |
||
664 | ** to the FKey structure representing the foreign key constraint on table |
||
665 | ** "t2". Calling this function with "t2" as the argument would return a |
||
666 | ** NULL pointer (as there are no FK constraints for which t2 is the parent |
||
667 | ** table). |
||
668 | */ |
||
669 | static FKey sqlite3FkReferences( Table pTab ) |
||
670 | { |
||
671 | int nName = sqlite3Strlen30( pTab.zName ); |
||
672 | return sqlite3HashFind( pTab.pSchema.fkeyHash, pTab.zName, nName, (FKey)null ); |
||
673 | } |
||
674 | |||
675 | /* |
||
676 | ** The second argument is a Trigger structure allocated by the |
||
677 | ** fkActionTrigger() routine. This function deletes the Trigger structure |
||
678 | ** and all of its sub-components. |
||
679 | ** |
||
680 | ** The Trigger structure or any of its sub-components may be allocated from |
||
681 | ** the lookaside buffer belonging to database handle dbMem. |
||
682 | */ |
||
683 | static void fkTriggerDelete( sqlite3 dbMem, Trigger p ) |
||
684 | { |
||
685 | if ( p != null ) |
||
686 | { |
||
687 | TriggerStep pStep = p.step_list; |
||
688 | sqlite3ExprDelete( dbMem, ref pStep.pWhere ); |
||
689 | sqlite3ExprListDelete( dbMem, ref pStep.pExprList ); |
||
690 | sqlite3SelectDelete( dbMem, ref pStep.pSelect ); |
||
691 | sqlite3ExprDelete( dbMem, ref p.pWhen ); |
||
692 | sqlite3DbFree( dbMem, ref p ); |
||
693 | } |
||
694 | } |
||
695 | |||
696 | /* |
||
697 | ** This function is called to generate code that runs when table pTab is |
||
698 | ** being dropped from the database. The SrcList passed as the second argument |
||
699 | ** to this function contains a single entry guaranteed to resolve to |
||
700 | ** table pTab. |
||
701 | ** |
||
702 | ** Normally, no code is required. However, if either |
||
703 | ** |
||
704 | ** (a) The table is the parent table of a FK constraint, or |
||
705 | ** (b) The table is the child table of a deferred FK constraint and it is |
||
706 | ** determined at runtime that there are outstanding deferred FK |
||
707 | ** constraint violations in the database, |
||
708 | ** |
||
709 | ** then the equivalent of "DELETE FROM <tbl>" is executed before dropping |
||
710 | ** the table from the database. Triggers are disabled while running this |
||
711 | ** DELETE, but foreign key actions are not. |
||
712 | */ |
||
713 | static void sqlite3FkDropTable( Parse pParse, SrcList pName, Table pTab ) |
||
714 | { |
||
715 | sqlite3 db = pParse.db; |
||
716 | if ( ( db.flags & SQLITE_ForeignKeys ) != 0 && !IsVirtual( pTab ) && null == pTab.pSelect ) |
||
717 | { |
||
718 | int iSkip = 0; |
||
719 | Vdbe v = sqlite3GetVdbe( pParse ); |
||
720 | |||
721 | Debug.Assert( v != null ); /* VDBE has already been allocated */ |
||
722 | if ( sqlite3FkReferences( pTab ) == null ) |
||
723 | { |
||
724 | /* Search for a deferred foreign key constraint for which this table |
||
725 | ** is the child table. If one cannot be found, return without |
||
726 | ** generating any VDBE code. If one can be found, then jump over |
||
727 | ** the entire DELETE if there are no outstanding deferred constraints |
||
728 | ** when this statement is run. */ |
||
729 | FKey p; |
||
730 | for ( p = pTab.pFKey; p != null; p = p.pNextFrom ) |
||
731 | { |
||
732 | if ( p.isDeferred != 0 ) |
||
733 | break; |
||
734 | } |
||
735 | if ( null == p ) |
||
736 | return; |
||
737 | iSkip = sqlite3VdbeMakeLabel( v ); |
||
738 | sqlite3VdbeAddOp2( v, OP_FkIfZero, 1, iSkip ); |
||
739 | } |
||
740 | |||
741 | pParse.disableTriggers = 1; |
||
742 | sqlite3DeleteFrom( pParse, sqlite3SrcListDup( db, pName, 0 ), null ); |
||
743 | pParse.disableTriggers = 0; |
||
744 | |||
745 | /* If the DELETE has generated immediate foreign key constraint |
||
746 | ** violations, halt the VDBE and return an error at this point, before |
||
747 | ** any modifications to the schema are made. This is because statement |
||
748 | ** transactions are not able to rollback schema changes. */ |
||
749 | sqlite3VdbeAddOp2( v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr( v ) + 2 ); |
||
750 | sqlite3HaltConstraint( |
||
751 | pParse, OE_Abort, "foreign key constraint failed", P4_STATIC |
||
752 | ); |
||
753 | |||
754 | if ( iSkip != 0 ) |
||
755 | { |
||
756 | sqlite3VdbeResolveLabel( v, iSkip ); |
||
757 | } |
||
758 | } |
||
759 | } |
||
760 | |||
761 | /* |
||
762 | ** This function is called when inserting, deleting or updating a row of |
||
763 | ** table pTab to generate VDBE code to perform foreign key constraint |
||
764 | ** processing for the operation. |
||
765 | ** |
||
766 | ** For a DELETE operation, parameter regOld is passed the index of the |
||
767 | ** first register in an array of (pTab.nCol+1) registers containing the |
||
768 | ** rowid of the row being deleted, followed by each of the column values |
||
769 | ** of the row being deleted, from left to right. Parameter regNew is passed |
||
770 | ** zero in this case. |
||
771 | ** |
||
772 | ** For an INSERT operation, regOld is passed zero and regNew is passed the |
||
773 | ** first register of an array of (pTab.nCol+1) registers containing the new |
||
774 | ** row data. |
||
775 | ** |
||
776 | ** For an UPDATE operation, this function is called twice. Once before |
||
777 | ** the original record is deleted from the table using the calling convention |
||
778 | ** described for DELETE. Then again after the original record is deleted |
||
779 | ** but before the new record is inserted using the INSERT convention. |
||
780 | */ |
||
781 | static void sqlite3FkCheck( |
||
782 | Parse pParse, /* Parse context */ |
||
783 | Table pTab, /* Row is being deleted from this table */ |
||
784 | int regOld, /* Previous row data is stored here */ |
||
785 | int regNew /* New row data is stored here */ |
||
786 | ) |
||
787 | { |
||
788 | sqlite3 db = pParse.db; /* Database handle */ |
||
789 | FKey pFKey; /* Used to iterate through FKs */ |
||
790 | int iDb; /* Index of database containing pTab */ |
||
791 | string zDb; /* Name of database containing pTab */ |
||
792 | int isIgnoreErrors = pParse.disableTriggers; |
||
793 | |||
794 | /* Exactly one of regOld and regNew should be non-zero. */ |
||
795 | Debug.Assert( ( regOld == 0 ) != ( regNew == 0 ) ); |
||
796 | |||
797 | /* If foreign-keys are disabled, this function is a no-op. */ |
||
798 | if ( ( db.flags & SQLITE_ForeignKeys ) == 0 ) |
||
799 | return; |
||
800 | |||
801 | iDb = sqlite3SchemaToIndex( db, pTab.pSchema ); |
||
802 | zDb = db.aDb[iDb].zName; |
||
803 | |||
804 | /* Loop through all the foreign key constraints for which pTab is the |
||
805 | ** child table (the table that the foreign key definition is part of). */ |
||
806 | for ( pFKey = pTab.pFKey; pFKey != null; pFKey = pFKey.pNextFrom ) |
||
807 | { |
||
808 | Table pTo; /* Parent table of foreign key pFKey */ |
||
809 | Index pIdx = null; /* Index on key columns in pTo */ |
||
810 | int[] aiFree = null; |
||
811 | int[] aiCol; |
||
812 | int iCol; |
||
813 | int i; |
||
814 | int isIgnore = 0; |
||
815 | |||
816 | /* Find the parent table of this foreign key. Also find a unique index |
||
817 | ** on the parent key columns in the parent table. If either of these |
||
818 | ** schema items cannot be located, set an error in pParse and return |
||
819 | ** early. */ |
||
820 | if ( pParse.disableTriggers != 0 ) |
||
821 | { |
||
822 | pTo = sqlite3FindTable( db, pFKey.zTo, zDb ); |
||
823 | } |
||
824 | else |
||
825 | { |
||
826 | pTo = sqlite3LocateTable( pParse, 0, pFKey.zTo, zDb ); |
||
827 | } |
||
828 | if ( null == pTo || locateFkeyIndex( pParse, pTo, pFKey, out pIdx, out aiFree ) != 0 ) |
||
829 | { |
||
830 | if ( 0 == isIgnoreErrors /* || db.mallocFailed */) |
||
831 | return; |
||
832 | continue; |
||
833 | } |
||
834 | Debug.Assert( pFKey.nCol == 1 || ( aiFree != null && pIdx != null ) ); |
||
835 | |||
836 | if ( aiFree != null ) |
||
837 | { |
||
838 | aiCol = aiFree; |
||
839 | } |
||
840 | else |
||
841 | { |
||
842 | iCol = pFKey.aCol[0].iFrom; |
||
843 | aiCol = new int[1]; |
||
844 | aiCol[0] = iCol; |
||
845 | } |
||
846 | for ( i = 0; i < pFKey.nCol; i++ ) |
||
847 | { |
||
848 | if ( aiCol[i] == pTab.iPKey ) |
||
849 | { |
||
850 | aiCol[i] = -1; |
||
851 | } |
||
852 | #if !SQLITE_OMIT_AUTHORIZATION |
||
853 | /* Request permission to read the parent key columns. If the |
||
854 | ** authorization callback returns SQLITE_IGNORE, behave as if any |
||
855 | ** values read from the parent table are NULL. */ |
||
856 | if( db.xAuth ){ |
||
857 | int rcauth; |
||
858 | char *zCol = pTo.aCol[pIdx ? pIdx.aiColumn[i] : pTo.iPKey].zName; |
||
859 | rcauth = sqlite3AuthReadCol(pParse, pTo.zName, zCol, iDb); |
||
860 | isIgnore = (rcauth==SQLITE_IGNORE); |
||
861 | } |
||
862 | #endif |
||
863 | } |
||
864 | |||
865 | /* Take a shared-cache advisory read-lock on the parent table. Allocate |
||
866 | ** a cursor to use to search the unique index on the parent key columns |
||
867 | ** in the parent table. */ |
||
868 | sqlite3TableLock( pParse, iDb, pTo.tnum, 0, pTo.zName ); |
||
869 | pParse.nTab++; |
||
870 | |||
871 | if ( regOld != 0 ) |
||
872 | { |
||
873 | /* A row is being removed from the child table. Search for the parent. |
||
874 | ** If the parent does not exist, removing the child row resolves an |
||
875 | ** outstanding foreign key constraint violation. */ |
||
876 | fkLookupParent( pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1, isIgnore ); |
||
877 | } |
||
878 | if ( regNew != 0 ) |
||
879 | { |
||
880 | /* A row is being added to the child table. If a parent row cannot |
||
881 | ** be found, adding the child row has violated the FK constraint. */ |
||
882 | fkLookupParent( pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1, isIgnore ); |
||
883 | } |
||
884 | |||
885 | sqlite3DbFree( db, ref aiFree ); |
||
886 | } |
||
887 | |||
888 | /* Loop through all the foreign key constraints that refer to this table */ |
||
889 | for ( pFKey = sqlite3FkReferences( pTab ); pFKey != null; pFKey = pFKey.pNextTo ) |
||
890 | { |
||
891 | Index pIdx = null; /* Foreign key index for pFKey */ |
||
892 | SrcList pSrc; |
||
893 | int[] aiCol = null; |
||
894 | |||
895 | if ( 0 == pFKey.isDeferred && null == pParse.pToplevel && 0 == pParse.isMultiWrite ) |
||
896 | { |
||
897 | Debug.Assert( regOld == 0 && regNew != 0 ); |
||
898 | /* Inserting a single row into a parent table cannot cause an immediate |
||
899 | ** foreign key violation. So do nothing in this case. */ |
||
900 | continue; |
||
901 | } |
||
902 | |||
903 | if ( locateFkeyIndex( pParse, pTab, pFKey, out pIdx, out aiCol ) != 0 ) |
||
904 | { |
||
905 | if ( 0 == isIgnoreErrors /*|| db.mallocFailed */) |
||
906 | return; |
||
907 | continue; |
||
908 | } |
||
909 | Debug.Assert( aiCol != null || pFKey.nCol == 1 ); |
||
910 | |||
911 | /* Create a SrcList structure containing a single table (the table |
||
912 | ** the foreign key that refers to this table is attached to). This |
||
913 | ** is required for the sqlite3WhereXXX() interface. */ |
||
914 | pSrc = sqlite3SrcListAppend( db, 0, null, null ); |
||
915 | if ( pSrc != null ) |
||
916 | { |
||
917 | SrcList_item pItem = pSrc.a[0]; |
||
918 | pItem.pTab = pFKey.pFrom; |
||
919 | pItem.zName = pFKey.pFrom.zName; |
||
920 | pItem.pTab.nRef++; |
||
921 | pItem.iCursor = pParse.nTab++; |
||
922 | |||
923 | if ( regNew != 0 ) |
||
924 | { |
||
925 | fkScanChildren( pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1 ); |
||
926 | } |
||
927 | if ( regOld != 0 ) |
||
928 | { |
||
929 | /* If there is a RESTRICT action configured for the current operation |
||
930 | ** on the parent table of this FK, then throw an exception |
||
931 | ** immediately if the FK constraint is violated, even if this is a |
||
932 | ** deferred trigger. That's what RESTRICT means. To defer checking |
||
933 | ** the constraint, the FK should specify NO ACTION (represented |
||
934 | ** using OE_None). NO ACTION is the default. */ |
||
935 | fkScanChildren( pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1 ); |
||
936 | } |
||
937 | pItem.zName = null; |
||
938 | sqlite3SrcListDelete( db, ref pSrc ); |
||
939 | } |
||
940 | sqlite3DbFree( db, ref aiCol ); |
||
941 | } |
||
942 | } |
||
943 | |||
944 | //#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x))) |
||
945 | static uint COLUMN_MASK( int x ) |
||
946 | { |
||
947 | return ( ( x ) > 31 ) ? 0xffffffff : ( (u32)1 << ( x ) ); |
||
948 | } |
||
949 | |||
950 | /* |
||
951 | ** This function is called before generating code to update or delete a |
||
952 | ** row contained in table pTab. |
||
953 | */ |
||
954 | static u32 sqlite3FkOldmask( |
||
955 | Parse pParse, /* Parse context */ |
||
956 | Table pTab /* Table being modified */ |
||
957 | ) |
||
958 | { |
||
959 | u32 mask = 0; |
||
960 | if ( ( pParse.db.flags & SQLITE_ForeignKeys ) != 0 ) |
||
961 | { |
||
962 | FKey p; |
||
963 | int i; |
||
964 | for ( p = pTab.pFKey; p != null; p = p.pNextFrom ) |
||
965 | { |
||
966 | for ( i = 0; i < p.nCol; i++ ) |
||
967 | mask |= COLUMN_MASK( p.aCol[i].iFrom ); |
||
968 | } |
||
969 | for ( p = sqlite3FkReferences( pTab ); p != null; p = p.pNextTo ) |
||
970 | { |
||
971 | Index pIdx; |
||
972 | int[] iDummy; |
||
973 | locateFkeyIndex( pParse, pTab, p, out pIdx, out iDummy ); |
||
974 | if ( pIdx != null ) |
||
975 | { |
||
976 | for ( i = 0; i < pIdx.nColumn; i++ ) |
||
977 | mask |= COLUMN_MASK( pIdx.aiColumn[i] ); |
||
978 | } |
||
979 | } |
||
980 | } |
||
981 | return mask; |
||
982 | } |
||
983 | |||
984 | /* |
||
985 | ** This function is called before generating code to update or delete a |
||
986 | ** row contained in table pTab. If the operation is a DELETE, then |
||
987 | ** parameter aChange is passed a NULL value. For an UPDATE, aChange points |
||
988 | ** to an array of size N, where N is the number of columns in table pTab. |
||
989 | ** If the i'th column is not modified by the UPDATE, then the corresponding |
||
990 | ** entry in the aChange[] array is set to -1. If the column is modified, |
||
991 | ** the value is 0 or greater. Parameter chngRowid is set to true if the |
||
992 | ** UPDATE statement modifies the rowid fields of the table. |
||
993 | ** |
||
994 | ** If any foreign key processing will be required, this function returns |
||
995 | ** true. If there is no foreign key related processing, this function |
||
996 | ** returns false. |
||
997 | */ |
||
998 | static int sqlite3FkRequired( |
||
999 | Parse pParse, /* Parse context */ |
||
1000 | Table pTab, /* Table being modified */ |
||
1001 | int[] aChange, /* Non-NULL for UPDATE operations */ |
||
1002 | int chngRowid /* True for UPDATE that affects rowid */ |
||
1003 | ) |
||
1004 | { |
||
1005 | if ( ( pParse.db.flags & SQLITE_ForeignKeys ) != 0 ) |
||
1006 | { |
||
1007 | if ( null == aChange ) |
||
1008 | { |
||
1009 | /* A DELETE operation. Foreign key processing is required if the |
||
1010 | ** table in question is either the child or parent table for any |
||
1011 | ** foreign key constraint. */ |
||
1012 | return ( sqlite3FkReferences( pTab ) != null || pTab.pFKey != null ) ? 1 : 0; |
||
1013 | } |
||
1014 | else |
||
1015 | { |
||
1016 | /* This is an UPDATE. Foreign key processing is only required if the |
||
1017 | ** operation modifies one or more child or parent key columns. */ |
||
1018 | int i; |
||
1019 | FKey p; |
||
1020 | |||
1021 | /* Check if any child key columns are being modified. */ |
||
1022 | for ( p = pTab.pFKey; p != null; p = p.pNextFrom ) |
||
1023 | { |
||
1024 | for ( i = 0; i < p.nCol; i++ ) |
||
1025 | { |
||
1026 | int iChildKey = p.aCol[i].iFrom; |
||
1027 | if ( aChange[iChildKey] >= 0 ) |
||
1028 | return 1; |
||
1029 | if ( iChildKey == pTab.iPKey && chngRowid != 0 ) |
||
1030 | return 1; |
||
1031 | } |
||
1032 | } |
||
1033 | |||
1034 | /* Check if any parent key columns are being modified. */ |
||
1035 | for ( p = sqlite3FkReferences( pTab ); p != null; p = p.pNextTo ) |
||
1036 | { |
||
1037 | for ( i = 0; i < p.nCol; i++ ) |
||
1038 | { |
||
1039 | string zKey = p.aCol[i].zCol; |
||
1040 | int iKey; |
||
1041 | for ( iKey = 0; iKey < pTab.nCol; iKey++ ) |
||
1042 | { |
||
1043 | Column pCol = pTab.aCol[iKey]; |
||
1044 | if ( ( !string.IsNullOrEmpty( zKey ) ? pCol.zName.Equals( zKey, StringComparison.OrdinalIgnoreCase ) : pCol.isPrimKey != 0 ) ) |
||
1045 | { |
||
1046 | if ( aChange[iKey] >= 0 ) |
||
1047 | return 1; |
||
1048 | if ( iKey == pTab.iPKey && chngRowid != 0 ) |
||
1049 | return 1; |
||
1050 | } |
||
1051 | } |
||
1052 | } |
||
1053 | } |
||
1054 | } |
||
1055 | } |
||
1056 | return 0; |
||
1057 | } |
||
1058 | |||
1059 | /* |
||
1060 | ** This function is called when an UPDATE or DELETE operation is being |
||
1061 | ** compiled on table pTab, which is the parent table of foreign-key pFKey. |
||
1062 | ** If the current operation is an UPDATE, then the pChanges parameter is |
||
1063 | ** passed a pointer to the list of columns being modified. If it is a |
||
1064 | ** DELETE, pChanges is passed a NULL pointer. |
||
1065 | ** |
||
1066 | ** It returns a pointer to a Trigger structure containing a trigger |
||
1067 | ** equivalent to the ON UPDATE or ON DELETE action specified by pFKey. |
||
1068 | ** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is |
||
1069 | ** returned (these actions require no special handling by the triggers |
||
1070 | ** sub-system, code for them is created by fkScanChildren()). |
||
1071 | ** |
||
1072 | ** For example, if pFKey is the foreign key and pTab is table "p" in |
||
1073 | ** the following schema: |
||
1074 | ** |
||
1075 | ** CREATE TABLE p(pk PRIMARY KEY); |
||
1076 | ** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE); |
||
1077 | ** |
||
1078 | ** then the returned trigger structure is equivalent to: |
||
1079 | ** |
||
1080 | ** CREATE TRIGGER ... DELETE ON p BEGIN |
||
1081 | ** DELETE FROM c WHERE ck = old.pk; |
||
1082 | ** END; |
||
1083 | ** |
||
1084 | ** The returned pointer is cached as part of the foreign key object. It |
||
1085 | ** is eventually freed along with the rest of the foreign key object by |
||
1086 | ** sqlite3FkDelete(). |
||
1087 | */ |
||
1088 | static Trigger fkActionTrigger( |
||
1089 | Parse pParse, /* Parse context */ |
||
1090 | Table pTab, /* Table being updated or deleted from */ |
||
1091 | FKey pFKey, /* Foreign key to get action for */ |
||
1092 | ExprList pChanges /* Change-list for UPDATE, NULL for DELETE */ |
||
1093 | ) |
||
1094 | { |
||
1095 | sqlite3 db = pParse.db; /* Database handle */ |
||
1096 | int action; /* One of OE_None, OE_Cascade etc. */ |
||
1097 | Trigger pTrigger; /* Trigger definition to return */ |
||
1098 | int iAction = ( pChanges != null ) ? 1 : 0; /* 1 for UPDATE, 0 for DELETE */ |
||
1099 | |||
1100 | action = pFKey.aAction[iAction]; |
||
1101 | pTrigger = pFKey.apTrigger[iAction]; |
||
1102 | |||
1103 | if ( action != OE_None && null == pTrigger ) |
||
1104 | { |
||
1105 | u8 enableLookaside; /* Copy of db.lookaside.bEnabled */ |
||
1106 | string zFrom; /* Name of child table */ |
||
1107 | int nFrom; /* Length in bytes of zFrom */ |
||
1108 | Index pIdx = null; /* Parent key index for this FK */ |
||
1109 | int[] aiCol = null; /* child table cols . parent key cols */ |
||
1110 | TriggerStep pStep = null; /* First (only) step of trigger program */ |
||
1111 | Expr pWhere = null; /* WHERE clause of trigger step */ |
||
1112 | ExprList pList = null; /* Changes list if ON UPDATE CASCADE */ |
||
1113 | Select pSelect = null; /* If RESTRICT, "SELECT RAISE(...)" */ |
||
1114 | int i; /* Iterator variable */ |
||
1115 | Expr pWhen = null; /* WHEN clause for the trigger */ |
||
1116 | |||
1117 | if ( locateFkeyIndex( pParse, pTab, pFKey, out pIdx, out aiCol ) != 0 ) |
||
1118 | return null; |
||
1119 | Debug.Assert( aiCol != null || pFKey.nCol == 1 ); |
||
1120 | |||
1121 | for ( i = 0; i < pFKey.nCol; i++ ) |
||
1122 | { |
||
1123 | Token tOld = new Token( "old", 3 ); /* Literal "old" token */ |
||
1124 | Token tNew = new Token( "new", 3 ); /* Literal "new" token */ |
||
1125 | Token tFromCol = new Token(); /* Name of column in child table */ |
||
1126 | Token tToCol = new Token(); /* Name of column in parent table */ |
||
1127 | int iFromCol; /* Idx of column in child table */ |
||
1128 | Expr pEq; /* tFromCol = OLD.tToCol */ |
||
1129 | |||
1130 | iFromCol = aiCol != null ? aiCol[i] : pFKey.aCol[0].iFrom; |
||
1131 | Debug.Assert( iFromCol >= 0 ); |
||
1132 | tToCol.z = pIdx != null ? pTab.aCol[pIdx.aiColumn[i]].zName : "oid"; |
||
1133 | tFromCol.z = pFKey.pFrom.aCol[iFromCol].zName; |
||
1134 | |||
1135 | tToCol.n = sqlite3Strlen30( tToCol.z ); |
||
1136 | tFromCol.n = sqlite3Strlen30( tFromCol.z ); |
||
1137 | |||
1138 | /* Create the expression "OLD.zToCol = zFromCol". It is important |
||
1139 | ** that the "OLD.zToCol" term is on the LHS of the = operator, so |
||
1140 | ** that the affinity and collation sequence associated with the |
||
1141 | ** parent table are used for the comparison. */ |
||
1142 | pEq = sqlite3PExpr( pParse, TK_EQ, |
||
1143 | sqlite3PExpr( pParse, TK_DOT, |
||
1144 | sqlite3PExpr( pParse, TK_ID, null, null, tOld ), |
||
1145 | sqlite3PExpr( pParse, TK_ID, null, null, tToCol ) |
||
1146 | , 0 ), |
||
1147 | sqlite3PExpr( pParse, TK_ID, null, null, tFromCol ) |
||
1148 | , 0 ); |
||
1149 | pWhere = sqlite3ExprAnd( db, pWhere, pEq ); |
||
1150 | |||
1151 | /* For ON UPDATE, construct the next term of the WHEN clause. |
||
1152 | ** The final WHEN clause will be like this: |
||
1153 | ** |
||
1154 | ** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN) |
||
1155 | */ |
||
1156 | if ( pChanges != null ) |
||
1157 | { |
||
1158 | pEq = sqlite3PExpr( pParse, TK_IS, |
||
1159 | sqlite3PExpr( pParse, TK_DOT, |
||
1160 | sqlite3PExpr( pParse, TK_ID, null, null, tOld ), |
||
1161 | sqlite3PExpr( pParse, TK_ID, null, null, tToCol ), |
||
1162 | |||
1163 | sqlite3PExpr( pParse, TK_DOT, |
||
1164 | sqlite3PExpr( pParse, TK_ID, null, null, tNew ), |
||
1165 | sqlite3PExpr( pParse, TK_ID, null, null, tToCol ), |
||
1166 | |||
1167 | |||
1168 | pWhen = sqlite3ExprAnd( db, pWhen, pEq ); |
||
1169 | } |
||
1170 | |||
1171 | if ( action != OE_Restrict && ( action != OE_Cascade || pChanges != null ) ) |
||
1172 | { |
||
1173 | Expr pNew; |
||
1174 | if ( action == OE_Cascade ) |
||
1175 | { |
||
1176 | pNew = sqlite3PExpr( pParse, TK_DOT, |
||
1177 | sqlite3PExpr( pParse, TK_ID, null, null, tNew ), |
||
1178 | sqlite3PExpr( pParse, TK_ID, null, null, tToCol ) |
||
1179 | , 0 ); |
||
1180 | } |
||
1181 | else if ( action == OE_SetDflt ) |
||
1182 | { |
||
1183 | Expr pDflt = pFKey.pFrom.aCol[iFromCol].pDflt; |
||
1184 | if ( pDflt != null ) |
||
1185 | { |
||
1186 | pNew = sqlite3ExprDup( db, pDflt, 0 ); |
||
1187 | } |
||
1188 | else |
||
1189 | { |
||
1190 | pNew = sqlite3PExpr( pParse, TK_NULL, 0, 0, 0 ); |
||
1191 | } |
||
1192 | } |
||
1193 | else |
||
1194 | { |
||
1195 | pNew = sqlite3PExpr( pParse, TK_NULL, 0, 0, 0 ); |
||
1196 | } |
||
1197 | pList = sqlite3ExprListAppend( pParse, pList, pNew ); |
||
1198 | sqlite3ExprListSetName( pParse, pList, tFromCol, 0 ); |
||
1199 | } |
||
1200 | } |
||
1201 | sqlite3DbFree( db, ref aiCol ); |
||
1202 | |||
1203 | zFrom = pFKey.pFrom.zName; |
||
1204 | nFrom = sqlite3Strlen30( zFrom ); |
||
1205 | |||
1206 | if ( action == OE_Restrict ) |
||
1207 | { |
||
1208 | Token tFrom = new Token(); |
||
1209 | Expr pRaise; |
||
1210 | |||
1211 | tFrom.z = zFrom; |
||
1212 | tFrom.n = nFrom; |
||
1213 | pRaise = sqlite3Expr( db, TK_RAISE, "foreign key constraint failed" ); |
||
1214 | if ( pRaise != null ) |
||
1215 | { |
||
1216 | pRaise.affinity = (char)OE_Abort; |
||
1217 | } |
||
1218 | pSelect = sqlite3SelectNew( pParse, |
||
1219 | sqlite3ExprListAppend( pParse, 0, pRaise ), |
||
1220 | sqlite3SrcListAppend( db, 0, tFrom, null ), |
||
1221 | pWhere, |
||
1222 | null, null, null, 0, null, null |
||
1223 | ); |
||
1224 | pWhere = null; |
||
1225 | } |
||
1226 | |||
1227 | /* Disable lookaside memory allocation */ |
||
1228 | enableLookaside = db.lookaside.bEnabled; |
||
1229 | db.lookaside.bEnabled = 0; |
||
1230 | |||
1231 | pTrigger = new Trigger(); |
||
1232 | //(Trigger*)sqlite3DbMallocZero( db, |
||
1233 | // sizeof( Trigger ) + /* struct Trigger */ |
||
1234 | // sizeof( TriggerStep ) + /* Single step in trigger program */ |
||
1235 | // nFrom + 1 /* Space for pStep.target.z */ |
||
1236 | // ); |
||
1237 | //if ( pTrigger ) |
||
1238 | { |
||
1239 | |||
1240 | pStep = pTrigger.step_list = new TriggerStep();// = (TriggerStep)pTrigger[1]; |
||
1241 | //pStep.target.z = pStep[1]; |
||
1242 | pStep.target.n = nFrom; |
||
1243 | pStep.target.z = zFrom;// memcpy( (char*)pStep.target.z, zFrom, nFrom ); |
||
1244 | |||
1245 | pStep.pWhere = sqlite3ExprDup( db, pWhere, EXPRDUP_REDUCE ); |
||
1246 | pStep.pExprList = sqlite3ExprListDup( db, pList, EXPRDUP_REDUCE ); |
||
1247 | pStep.pSelect = sqlite3SelectDup( db, pSelect, EXPRDUP_REDUCE ); |
||
1248 | if ( pWhen != null ) |
||
1249 | { |
||
1250 | pWhen = sqlite3PExpr( pParse, TK_NOT, pWhen, 0, 0 ); |
||
1251 | pTrigger.pWhen = sqlite3ExprDup( db, pWhen, EXPRDUP_REDUCE ); |
||
1252 | } |
||
1253 | } |
||
1254 | |||
1255 | /* Re-enable the lookaside buffer, if it was disabled earlier. */ |
||
1256 | db.lookaside.bEnabled = enableLookaside; |
||
1257 | |||
1258 | sqlite3ExprDelete( db, ref pWhere ); |
||
1259 | sqlite3ExprDelete( db, ref pWhen ); |
||
1260 | sqlite3ExprListDelete( db, ref pList ); |
||
1261 | sqlite3SelectDelete( db, ref pSelect ); |
||
1262 | //if ( db.mallocFailed == 1 ) |
||
1263 | //{ |
||
1264 | // fkTriggerDelete( db, pTrigger ); |
||
1265 | // return 0; |
||
1266 | //} |
||
1267 | |||
1268 | switch ( action ) |
||
1269 | { |
||
1270 | case OE_Restrict: |
||
1271 | pStep.op = TK_SELECT; |
||
1272 | break; |
||
1273 | case OE_Cascade: |
||
1274 | if ( null == pChanges ) |
||
1275 | { |
||
1276 | pStep.op = TK_DELETE; |
||
1277 | break; |
||
1278 | } |
||
1279 | goto default; |
||
1280 | default: |
||
1281 | pStep.op = TK_UPDATE; |
||
1282 | break; |
||
1283 | } |
||
1284 | pStep.pTrig = pTrigger; |
||
1285 | pTrigger.pSchema = pTab.pSchema; |
||
1286 | pTrigger.pTabSchema = pTab.pSchema; |
||
1287 | pFKey.apTrigger[iAction] = pTrigger; |
||
1288 | pTrigger.op = (byte)( pChanges != null ? TK_UPDATE : TK_DELETE ); |
||
1289 | } |
||
1290 | |||
1291 | return pTrigger; |
||
1292 | } |
||
1293 | |||
1294 | /* |
||
1295 | ** This function is called when deleting or updating a row to implement |
||
1296 | ** any required CASCADE, SET NULL or SET DEFAULT actions. |
||
1297 | */ |
||
1298 | static void sqlite3FkActions( |
||
1299 | Parse pParse, /* Parse context */ |
||
1300 | Table pTab, /* Table being updated or deleted from */ |
||
1301 | ExprList pChanges, /* Change-list for UPDATE, NULL for DELETE */ |
||
1302 | int regOld /* Address of array containing old row */ |
||
1303 | ) |
||
1304 | { |
||
1305 | /* If foreign-key support is enabled, iterate through all FKs that |
||
1306 | ** refer to table pTab. If there is an action a6ssociated with the FK |
||
1307 | ** for this operation (either update or delete), invoke the associated |
||
1308 | ** trigger sub-program. */ |
||
1309 | if ( ( pParse.db.flags & SQLITE_ForeignKeys ) != 0 ) |
||
1310 | { |
||
1311 | FKey pFKey; /* Iterator variable */ |
||
1312 | for ( pFKey = sqlite3FkReferences( pTab ); pFKey != null; pFKey = pFKey.pNextTo ) |
||
1313 | { |
||
1314 | Trigger pAction = fkActionTrigger( pParse, pTab, pFKey, pChanges ); |
||
1315 | if ( pAction != null ) |
||
1316 | { |
||
1317 | sqlite3CodeRowTriggerDirect( pParse, pAction, pTab, regOld, OE_Abort, 0 ); |
||
1318 | } |
||
1319 | } |
||
1320 | } |
||
1321 | } |
||
1322 | |||
1323 | #endif //* ifndef SQLITE_OMIT_TRIGGER */ |
||
1324 | |||
1325 | /* |
||
1326 | ** Free all memory associated with foreign key definitions attached to |
||
1327 | ** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash |
||
1328 | ** hash table. |
||
1329 | */ |
||
1330 | static void sqlite3FkDelete( sqlite3 db, Table pTab ) |
||
1331 | { |
||
1332 | FKey pFKey; /* Iterator variable */ |
||
1333 | FKey pNext; /* Copy of pFKey.pNextFrom */ |
||
1334 | |||
1335 | Debug.Assert( db == null || sqlite3SchemaMutexHeld( db, 0, pTab.pSchema ) ); |
||
1336 | for ( pFKey = pTab.pFKey; pFKey != null; pFKey = pNext ) |
||
1337 | { |
||
1338 | |||
1339 | /* Remove the FK from the fkeyHash hash table. */ |
||
1340 | //if ( null == db || db.pnBytesFreed == 0 ) |
||
1341 | { |
||
1342 | if ( pFKey.pPrevTo != null ) |
||
1343 | { |
||
1344 | pFKey.pPrevTo.pNextTo = pFKey.pNextTo; |
||
1345 | } |
||
1346 | else |
||
1347 | { |
||
1348 | FKey p = pFKey.pNextTo; |
||
1349 | string z = ( p != null ? pFKey.pNextTo.zTo : pFKey.zTo ); |
||
1350 | sqlite3HashInsert( ref pTab.pSchema.fkeyHash, z, sqlite3Strlen30( z ), p ); |
||
1351 | } |
||
1352 | if ( pFKey.pNextTo != null ) |
||
1353 | { |
||
1354 | pFKey.pNextTo.pPrevTo = pFKey.pPrevTo; |
||
1355 | } |
||
1356 | } |
||
1357 | |||
1358 | /* EV: R-30323-21917 Each foreign key constraint in SQLite is |
||
1359 | ** classified as either immediate or deferred. |
||
1360 | */ |
||
1361 | Debug.Assert( pFKey.isDeferred == 0 || pFKey.isDeferred == 1 ); |
||
1362 | |||
1363 | /* Delete any triggers created to implement actions for this FK. */ |
||
1364 | #if !SQLITE_OMIT_TRIGGER |
||
1365 | fkTriggerDelete( db, pFKey.apTrigger[0] ); |
||
1366 | fkTriggerDelete( db, pFKey.apTrigger[1] ); |
||
1367 | #endif |
||
1368 | |||
1369 | pNext = pFKey.pNextFrom; |
||
1370 | sqlite3DbFree( db, ref pFKey ); |
||
1371 | } |
||
1372 | } |
||
1373 | #endif //* ifndef SQLITE_OMIT_FOREIGN_KEY */ |
||
1374 | } |
||
1375 | } |