Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Check in implementation of foreign key constraints. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d5d399811876391642937edeb9e8434d |
User & Date: | dan 2009-09-19 17:00:31.000 |
Context
2009-09-19
| ||
17:59 | Add fkey.c to the autoconf and amalgamation build systems. (check-in: aab7a4b3c9 user: dan tags: trunk) | |
17:00 | Check in implementation of foreign key constraints. (check-in: d5d3998118 user: dan tags: trunk) | |
2009-09-17
| ||
00:41 | When coding a trigger, assume that the "oldmask" requires all columns until we know otherwise. That pessimistic assumption assures that all necessary parameters are available on a cascading delete trigger. Ticket [e25d9ea771f] (check-in: 03e464be51 user: drh tags: trunk) | |
Changes
Changes to main.mk.
︙ | ︙ | |||
48 49 50 51 52 53 54 | TCCX += -I$(TOP)/ext/rtree -I$(TOP)/ext/icu -I$(TOP)/ext/fts3 TCCX += -I$(TOP)/ext/async # Object files for the SQLite library. # LIBOBJ+= alter.o analyze.o attach.o auth.o \ backup.o bitvec.o btmutex.o btree.o build.o \ | | | 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | TCCX += -I$(TOP)/ext/rtree -I$(TOP)/ext/icu -I$(TOP)/ext/fts3 TCCX += -I$(TOP)/ext/async # Object files for the SQLite library. # LIBOBJ+= alter.o analyze.o attach.o auth.o \ backup.o bitvec.o btmutex.o btree.o build.o \ callback.o complete.o date.o delete.o expr.o fault.o fkey.o \ fts3.o fts3_expr.o fts3_hash.o fts3_icu.o fts3_porter.o \ fts3_tokenizer.o fts3_tokenizer1.o \ func.o global.o hash.o \ icu.o insert.o journal.o legacy.o loadext.o \ main.o malloc.o mem0.o mem1.o mem2.o mem3.o mem5.o \ memjournal.o \ mutex.o mutex_noop.o mutex_os2.o mutex_unix.o mutex_w32.o \ |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
500 501 502 503 504 505 506 | ** This routine just deletes the data structure. It does not unlink ** the table data structure from the hash table. But it does destroy ** memory structures of the indices and foreign keys associated with ** the table. */ void sqlite3DeleteTable(Table *pTable){ Index *pIndex, *pNext; | < | 500 501 502 503 504 505 506 507 508 509 510 511 512 513 | ** This routine just deletes the data structure. It does not unlink ** the table data structure from the hash table. But it does destroy ** memory structures of the indices and foreign keys associated with ** the table. */ void sqlite3DeleteTable(Table *pTable){ Index *pIndex, *pNext; sqlite3 *db; if( pTable==0 ) return; db = pTable->dbMem; testcase( db==0 ); /* Do not delete the table until the reference count reaches zero. */ |
︙ | ︙ | |||
522 523 524 525 526 527 528 | */ for(pIndex = pTable->pIndex; pIndex; pIndex=pNext){ pNext = pIndex->pNext; assert( pIndex->pSchema==pTable->pSchema ); sqlite3DeleteIndex(pIndex); } | < | < < | < < | 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 | */ for(pIndex = pTable->pIndex; pIndex; pIndex=pNext){ pNext = pIndex->pNext; assert( pIndex->pSchema==pTable->pSchema ); sqlite3DeleteIndex(pIndex); } /* Delete any foreign keys attached to this table. */ sqlite3FkDelete(pTable); /* Delete the Table structure itself. */ sqliteResetColumnNames(pTable); sqlite3DbFree(db, pTable->zName); sqlite3DbFree(db, pTable->zColAff); sqlite3SelectDelete(db, pTable->pSelect); |
︙ | ︙ | |||
1170 1171 1172 1173 1174 1175 1176 | pTab->tabFlags |= autoInc*TF_Autoincrement; }else if( autoInc ){ #ifndef SQLITE_OMIT_AUTOINCREMENT sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an " "INTEGER PRIMARY KEY"); #endif }else{ | > | > > > | 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 | pTab->tabFlags |= autoInc*TF_Autoincrement; }else if( autoInc ){ #ifndef SQLITE_OMIT_AUTOINCREMENT sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an " "INTEGER PRIMARY KEY"); #endif }else{ Index *p; p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0); if( p ){ p->autoIndex = 2; } pList = 0; } primary_key_exit: sqlite3ExprListDelete(pParse->db, pList); return; } |
︙ | ︙ | |||
2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 | Token *pTo, /* Name of the other table */ ExprList *pToCol, /* Columns in the other table */ int flags /* Conflict resolution algorithms. */ ){ sqlite3 *db = pParse->db; #ifndef SQLITE_OMIT_FOREIGN_KEY FKey *pFKey = 0; Table *p = pParse->pNewTable; int nByte; int i; int nCol; char *z; assert( pTo!=0 ); | > | 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 | Token *pTo, /* Name of the other table */ ExprList *pToCol, /* Columns in the other table */ int flags /* Conflict resolution algorithms. */ ){ sqlite3 *db = pParse->db; #ifndef SQLITE_OMIT_FOREIGN_KEY FKey *pFKey = 0; FKey *pNextTo; Table *p = pParse->pNewTable; int nByte; int i; int nCol; char *z; assert( pTo!=0 ); |
︙ | ︙ | |||
2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 | z += n+1; } } pFKey->isDeferred = 0; pFKey->deleteConf = (u8)(flags & 0xff); pFKey->updateConf = (u8)((flags >> 8 ) & 0xff); pFKey->insertConf = (u8)((flags >> 16 ) & 0xff); /* Link the foreign key to the table as the last step. */ p->pFKey = pFKey; pFKey = 0; fk_end: | > > > > > > > > > > | 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 | z += n+1; } } pFKey->isDeferred = 0; pFKey->deleteConf = (u8)(flags & 0xff); pFKey->updateConf = (u8)((flags >> 8 ) & 0xff); pFKey->insertConf = (u8)((flags >> 16 ) & 0xff); pNextTo = (FKey *)sqlite3HashInsert(&p->pSchema->fkeyHash, pFKey->zTo, sqlite3Strlen30(pFKey->zTo), (void *)pFKey ); if( pNextTo==pFKey ) goto fk_end; if( pNextTo ){ assert( pNextTo->pPrevTo==0 ); pFKey->pNextTo = pNextTo; pNextTo->pPrevTo = pFKey; } /* Link the foreign key to the table as the last step. */ p->pFKey = pFKey; pFKey = 0; fk_end: |
︙ | ︙ | |||
2346 2347 2348 2349 2350 2351 2352 2353 | ** UNIQUE constraint. If pTable and pIndex are NULL, use pParse->pNewTable ** as the table to be indexed. pParse->pNewTable is a table that is ** currently being constructed by a CREATE TABLE statement. ** ** pList is a list of columns to be indexed. pList will be NULL if this ** is a primary key or unique-constraint on the most recent column added ** to the table currently under construction. */ | > > > > | > | 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 | ** UNIQUE constraint. If pTable and pIndex are NULL, use pParse->pNewTable ** as the table to be indexed. pParse->pNewTable is a table that is ** currently being constructed by a CREATE TABLE statement. ** ** pList is a list of columns to be indexed. pList will be NULL if this ** is a primary key or unique-constraint on the most recent column added ** to the table currently under construction. ** ** If the index is created successfully, return a pointer to the new Index ** structure. This is used by sqlite3AddPrimaryKey() to mark the index ** as the tables primary key (Index.autoIndex==2). */ Index *sqlite3CreateIndex( Parse *pParse, /* All information about this parse */ Token *pName1, /* First part of index name. May be NULL */ Token *pName2, /* Second part of index name. May be NULL */ SrcList *pTblName, /* Table to index. Use pParse->pNewTable if 0 */ ExprList *pList, /* A list of columns to be indexed */ int onError, /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ Token *pStart, /* The CREATE token that begins this statement */ Token *pEnd, /* The ")" that closes the CREATE INDEX statement */ int sortOrder, /* Sort order of primary key when pList==NULL */ int ifNotExist /* Omit error if index already exists */ ){ Index *pRet = 0; /* Pointer to return */ Table *pTab = 0; /* Table to be indexed */ Index *pIndex = 0; /* The index to be created */ char *zName = 0; /* Name of the index */ int nName; /* Number of characters in zName */ int i, j; Token nullId; /* Fake token for an empty ID list */ DbFixer sFix; /* For assigning database names to pTable */ |
︙ | ︙ | |||
2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 | Index *pOther = pTab->pIndex; while( pOther->pNext && pOther->pNext->onError!=OE_Replace ){ pOther = pOther->pNext; } pIndex->pNext = pOther->pNext; pOther->pNext = pIndex; } pIndex = 0; } /* Clean up before exiting */ exit_create_index: if( pIndex ){ sqlite3_free(pIndex->zColAff); sqlite3DbFree(db, pIndex); } sqlite3ExprListDelete(db, pList); sqlite3SrcListDelete(db, pTblName); sqlite3DbFree(db, zName); | > | | 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 | Index *pOther = pTab->pIndex; while( pOther->pNext && pOther->pNext->onError!=OE_Replace ){ pOther = pOther->pNext; } pIndex->pNext = pOther->pNext; pOther->pNext = pIndex; } pRet = pIndex; pIndex = 0; } /* Clean up before exiting */ exit_create_index: if( pIndex ){ sqlite3_free(pIndex->zColAff); sqlite3DbFree(db, pIndex); } sqlite3ExprListDelete(db, pList); sqlite3SrcListDelete(db, pTblName); sqlite3DbFree(db, zName); return pRet; } /* ** Fill the Index.aiRowEst[] array with default information - information ** to be used when we have not run the ANALYZE command. ** ** aiRowEst[0] is suppose to contain the number of elements in the index. |
︙ | ︙ |
Changes to src/callback.c.
︙ | ︙ | |||
419 420 421 422 423 424 425 426 427 428 429 430 431 432 | sqlite3HashInit(&pSchema->tblHash); for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){ Table *pTab = sqliteHashData(pElem); assert( pTab->dbMem==0 ); sqlite3DeleteTable(pTab); } sqlite3HashClear(&temp1); pSchema->pSeqTab = 0; pSchema->flags &= ~DB_SchemaLoaded; } /* ** Find and return the schema associated with a BTree. Create ** a new one if necessary. | > | 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 | sqlite3HashInit(&pSchema->tblHash); for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){ Table *pTab = sqliteHashData(pElem); assert( pTab->dbMem==0 ); sqlite3DeleteTable(pTab); } sqlite3HashClear(&temp1); sqlite3HashClear(&pSchema->fkeyHash); pSchema->pSeqTab = 0; pSchema->flags &= ~DB_SchemaLoaded; } /* ** Find and return the schema associated with a BTree. Create ** a new one if necessary. |
︙ | ︙ | |||
440 441 442 443 444 445 446 447 448 449 450 | } if( !p ){ db->mallocFailed = 1; }else if ( 0==p->file_format ){ sqlite3HashInit(&p->tblHash); sqlite3HashInit(&p->idxHash); sqlite3HashInit(&p->trigHash); p->enc = SQLITE_UTF8; } return p; } | > | 441 442 443 444 445 446 447 448 449 450 451 452 | } if( !p ){ db->mallocFailed = 1; }else if ( 0==p->file_format ){ sqlite3HashInit(&p->tblHash); sqlite3HashInit(&p->idxHash); sqlite3HashInit(&p->trigHash); sqlite3HashInit(&p->fkeyHash); p->enc = SQLITE_UTF8; } return p; } |
Changes to src/delete.c.
︙ | ︙ | |||
304 305 306 307 308 309 310 | /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ){ goto delete_from_cleanup; } if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); | | | 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 | /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ){ goto delete_from_cleanup; } if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, iDb); /* If we are trying to delete from a view, realize that view into ** a ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, iCur); |
︙ | ︙ | |||
337 338 339 340 341 342 343 | } #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special case: A DELETE without a WHERE clause deletes everything. ** It is easier just to erase the whole table. Prior to version 3.6.5, ** this optimization caused the row change count (the value returned by ** API function sqlite3_count_changes) to be set incorrectly. */ | | > > | 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 | } #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special case: A DELETE without a WHERE clause deletes everything. ** It is easier just to erase the whole table. Prior to version 3.6.5, ** this optimization caused the row change count (the value returned by ** API function sqlite3_count_changes) to be set incorrectly. */ if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) && 0==sqlite3FkRequired(pParse, pTab, 0) ){ assert( !isView ); sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt, pTab->zName, P4_STATIC); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ assert( pIdx->pSchema==pTab->pSchema ); sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb); } |
︙ | ︙ | |||
442 443 444 445 446 447 448 | ** This routine generates VDBE code that causes a single row of a ** single table to be deleted. ** ** The VDBE must be in a particular state when this routine is called. ** These are the requirements: ** ** 1. A read/write cursor pointing to pTab, the table containing the row | | | 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 | ** This routine generates VDBE code that causes a single row of a ** single table to be deleted. ** ** The VDBE must be in a particular state when this routine is called. ** These are the requirements: ** ** 1. A read/write cursor pointing to pTab, the table containing the row ** to be deleted, must be opened as cursor number $iCur. ** ** 2. Read/write cursors for all indices of pTab must be open as ** cursor number base+i for the i-th index. ** ** 3. The record number of the row to be deleted must be stored in ** memory cell iRowid. ** |
︙ | ︙ | |||
477 478 479 480 481 482 483 | ** (this can happen if a trigger program has already deleted it), do ** not attempt to delete it or fire any DELETE triggers. */ iLabel = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); /* If there are any triggers to fire, allocate a range of registers to ** use for the old.* references in the triggers. */ | | | | | > > > > > > > > > > | < | | | < | 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 | ** (this can happen if a trigger program has already deleted it), do ** not attempt to delete it or fire any DELETE triggers. */ iLabel = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); /* If there are any triggers to fire, allocate a range of registers to ** use for the old.* references in the triggers. */ if( sqlite3FkRequired(pParse, pTab, 0) || pTrigger ){ u32 mask; /* Mask of OLD.* columns in use */ int iCol; /* Iterator used while populating OLD.* */ /* TODO: Could use temporary registers here. Also could attempt to ** avoid copying the contents of the rowid register. */ mask = sqlite3TriggerOldmask(pParse, pTrigger, 0, pTab, onconf); iOld = pParse->nMem+1; pParse->nMem += (1 + pTab->nCol); /* Populate the OLD.* pseudo-table register array. These values will be ** used by any BEFORE and AFTER triggers that exist. */ sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld); for(iCol=0; iCol<pTab->nCol; iCol++){ if( 1 || mask==0xffffffff || mask&(1<<iCol) ){ int iTarget = iOld + iCol + 1; sqlite3VdbeAddOp3(v, OP_Column, iCur, iCol, iTarget); sqlite3ColumnDefault(v, pTab, iCol, iTarget); } } /* Invoke BEFORE DELETE trigger programs. */ sqlite3CodeRowTrigger(pParse, pTrigger, TK_DELETE, 0, TRIGGER_BEFORE, pTab, -1, iOld, onconf, iLabel ); /* Seek the cursor to the row to be deleted again. It may be that ** the BEFORE triggers coded above have already removed the row ** being deleted. Do not attempt to delete the row a second time, and ** do not fire AFTER triggers. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); /* Do FK processing. This call checks that any FK constraints that ** refer to this table (i.e. constraints attached to other tables) ** are not violated by deleting this row. */ sqlite3FkCheck(pParse, pTab, 0, iOld, 0); } /* Delete the index and table entries. Skip this step if pTab is really ** a view (in which case the only effect of the DELETE statement is to ** fire the INSTEAD OF triggers). */ if( pTab->pSelect==0 ){ sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0); sqlite3VdbeAddOp2(v, OP_Delete, iCur, (count?OPFLAG_NCHANGE:0)); if( count ){ sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC); } } /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key ** to the row just deleted. */ sqlite3FkActions(pParse, pTab, 0, iOld); /* Invoke AFTER DELETE trigger programs. */ sqlite3CodeRowTrigger(pParse, pTrigger, TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, iOld, onconf, iLabel ); /* Jump here if the row had already been deleted before any BEFORE ** trigger programs were invoked. Or if a trigger program throws a ** RAISE(IGNORE) exception. */ sqlite3VdbeResolveLabel(v, iLabel); } |
︙ | ︙ |
Added src/fkey.c.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 | /* ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains code used by the compiler to add foreign key ** support to compiled SQL statements. */ #include "sqliteInt.h" #ifndef SQLITE_OMIT_FOREIGN_KEY /* ** Deferred and Immediate FKs ** -------------------------- ** ** Foreign keys in SQLite come in two flavours: deferred and immediate. ** If an immediate foreign key constraint is violated, an OP_Halt is ** executed and the current statement transaction rolled back. If a ** deferred foreign key constraint is violated, no action is taken ** immediately. However if the application attempts to commit the ** transaction before fixing the constraint violation, the attempt fails. ** ** Deferred constraints are implemented using a simple counter associated ** with the database handle. The counter is set to zero each time a ** database transaction is opened. Each time a statement is executed ** that causes a foreign key violation, the counter is incremented. Each ** time a statement is executed that removes an existing violation from ** the database, the counter is decremented. When the transaction is ** committed, the commit fails if the current value of the counter is ** greater than zero. This scheme has two big drawbacks: ** ** * When a commit fails due to a deferred foreign key constraint, ** there is no way to tell which foreign constraint is not satisfied, ** or which row it is not satisfied for. ** ** * If the database contains foreign key violations when the ** transaction is opened, this may cause the mechanism to malfunction. ** ** Despite these problems, this approach is adopted as it seems simpler ** than the alternatives. ** ** INSERT operations: ** ** I.1) For each FK for which the table is the referencing table, search ** the referenced table for a match. If none is found, throw an ** exception for an immediate FK, or increment the counter for a ** deferred FK. ** ** I.2) For each deferred FK for which the table is the referenced table, ** search the referencing table for rows that correspond to the new ** row in the referenced table. Decrement the counter for each row ** found (as the constraint is now satisfied). ** ** DELETE operations: ** ** D.1) For each deferred FK for which the table is the referencing table, ** search the referenced table for a row that corresponds to the ** deleted row in the referencing table. If such a row is not found, ** decrement the counter. ** ** D.2) For each FK for which the table is the referenced table, search ** the referencing table for rows that correspond to the deleted row ** in the referenced table. For each found, throw an exception for an ** immediate FK, or increment the counter for a deferred FK. ** ** UPDATE operations: ** ** An UPDATE command requires that all 4 steps above are taken, but only ** for FK constraints for which the affected columns are actually ** modified (values must be compared at runtime). ** ** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2. ** This simplifies the implementation a bit. ** ** For the purposes of immediate FK constraints, the OR REPLACE conflict ** resolution is considered to delete rows before the new row is inserted. ** If a delete caused by OR REPLACE violates an FK constraint, an exception ** is thrown, even if the FK constraint would be satisfied after the new ** row is inserted. ** ** TODO: How should dropping a table be handled? How should renaming a ** table be handled? */ /* ** Query API Notes ** --------------- ** ** Before coding an UPDATE or DELETE row operation, the code-generator ** for those two operations needs to know whether or not the operation ** requires any FK processing and, if so, which columns of the original ** row are required by the FK processing VDBE code (i.e. if FKs were ** implemented using triggers, which of the old.* columns would be ** accessed). No information is required by the code-generator before ** coding an INSERT operation. ** */ /* ** VDBE Calling Convention ** ----------------------- ** ** Example: ** ** For the following INSERT statement: ** ** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c); ** INSERT INTO t1 VALUES(1, 2, 3.1); ** ** Register (x): 2 (type integer) ** Register (x+1): 1 (type integer) ** Register (x+2): NULL (type NULL) ** Register (x+3): 3.1 (type real) */ /* ** ON UPDATE and ON DELETE clauses ** ------------------------------- */ /* ** Externally accessible module functions ** -------------------------------------- ** ** sqlite3FkRequired() ** sqlite3FkOldmask() ** ** sqlite3FkCheck() ** sqlite3FkActions() ** ** sqlite3FkDelete() ** */ /* ** A foreign key constraint requires that the key columns in the referenced ** table are collectively subject to a UNIQUE or PRIMARY KEY constraint. ** Given that pTo is the referenced table for foreign key constraint ** pFKey, check that the columns in pTo are indeed subject to a such a ** constraint. If they are not, return non-zero and leave an error in pParse. ** ** If an error does not occur, return zero. */ static int locateFkeyIndex( Parse *pParse, /* Parse context to store any error in */ Table *pTo, /* Referenced table */ FKey *pFKey, /* Foreign key to find index for */ Index **ppIdx, /* OUT: Unique index on referenced table */ int **paiCol /* OUT: Map of index columns in pFKey */ ){ Index *pIdx = 0; int *aiCol = 0; int nCol = pFKey->nCol; char *zFirst = pFKey->aCol[0].zCol; /* The caller is responsible for zeroing output parameters. */ assert( ppIdx && *ppIdx==0 ); assert( !paiCol || *paiCol==0 ); /* If this is a non-composite (single column) foreign key, check if it ** maps to the INTEGER PRIMARY KEY of table pTo. If so, leave *ppIdx ** and *paiCol set to zero and return early. ** ** Otherwise, for a composite foreign key (more than one column), allocate ** space for the aiCol array (returned via output parameter *paiCol). ** Non-composite foreign keys do not require the aiCol array. */ if( nCol==1 ){ /* The FK maps to the IPK if any of the following are true: ** ** 1) The FK is explicitly mapped to "rowid", "oid" or "_rowid_", or ** 2) There is an explicit INTEGER PRIMARY KEY column and the FK is ** implicitly mapped to the primary key of table pTo, or ** 3) The FK is explicitly mapped to a column declared as INTEGER ** PRIMARY KEY. */ if( zFirst && sqlite3IsRowid(zFirst) ) return 0; if( pTo->iPKey>=0 ){ if( !zFirst ) return 0; if( !sqlite3StrICmp(pTo->aCol[pTo->iPKey].zName, zFirst) ) return 0; } }else if( paiCol ){ assert( nCol>1 ); aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int)); if( !aiCol ) return 1; *paiCol = aiCol; } for(pIdx=pTo->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->nColumn==nCol && pIdx->onError!=OE_None ){ /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number ** of columns. If each indexed column corresponds to a foreign key ** column of pFKey, then this index is a winner. */ if( zFirst==0 ){ /* If zFirst is NULL, then this foreign key is implicitly mapped to ** the PRIMARY KEY of table pTo. The PRIMARY KEY index may be ** identified by the test (Index.autoIndex==2). */ if( pIdx->autoIndex==2 ){ if( aiCol ) memcpy(aiCol, pIdx->aiColumn, sizeof(int)*nCol); break; } }else{ /* If zFirst is non-NULL, then this foreign key was declared to ** map to an explicit list of columns in table pTo. Check if this ** index matches those columns. */ int i, j; for(i=0; i<nCol; i++){ char *zIdxCol = pTo->aCol[pIdx->aiColumn[i]].zName; for(j=0; j<nCol; j++){ if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){ if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom; break; } } if( j==nCol ) break; } if( i==nCol ) break; /* pIdx is usable */ } } } if( pParse && !pIdx ){ sqlite3ErrorMsg(pParse, "foreign key mismatch"); sqlite3DbFree(pParse->db, aiCol); return 1; } *ppIdx = pIdx; return 0; } static void fkCheckReference( Parse *pParse, /* Parse context */ int iDb, /* Index of database housing pTab */ Table *pTab, /* Table referenced by FK pFKey */ Index *pIdx, /* Index ensuring uniqueness of FK in pTab */ FKey *pFKey, /* Foreign key to check */ int *aiCol, /* Map from FK column to referencing table column */ int regData, /* Address of array containing referencing row */ int nIncr /* If deferred FK, increment counter by this */ ){ int i; Vdbe *v = sqlite3GetVdbe(pParse); int iCur = pParse->nTab - 1; int iOk = sqlite3VdbeMakeLabel(v); assert( pFKey->isDeferred || nIncr==1 ); /* Check if any of the key columns in the referencing table are ** NULL. If any are, then the constraint is satisfied. No need ** to search for a matching row in the referenced table. */ for(i=0; i<pFKey->nCol; i++){ int iReg = pFKey->aCol[i].iFrom + regData + 1; sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); } if( pIdx==0 ){ /* If pIdx is NULL, then the foreign key constraint references the ** INTEGER PRIMARY KEY column in the referenced table (table pTab). */ int iReg = pFKey->aCol[0].iFrom + regData + 1; sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iReg); sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk); sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); }else{ int regRec = sqlite3GetTempReg(pParse); KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb); sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF); if( aiCol ){ int nCol = pFKey->nCol; int regTemp = sqlite3GetTempRange(pParse, nCol); for(i=0; i<nCol; i++){ sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[i]+1+regData, regTemp+i); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec); sqlite3ReleaseTempRange(pParse, regTemp, nCol); }else{ int iReg = pFKey->aCol[0].iFrom + regData + 1; sqlite3VdbeAddOp3(v, OP_MakeRecord, iReg, 1, regRec); sqlite3IndexAffinityStr(v, pIdx); } sqlite3VdbeAddOp3(v, OP_Found, iCur, iOk, regRec); sqlite3ReleaseTempReg(pParse, regRec); } if( pFKey->isDeferred ){ assert( nIncr==1 || nIncr==-1 ); sqlite3VdbeAddOp1(v, OP_DeferredCons, nIncr); }else{ sqlite3HaltConstraint( pParse, OE_Abort, "foreign key constraint failed", P4_STATIC ); } sqlite3VdbeResolveLabel(v, iOk); } static void fkScanReferences( Parse *pParse, /* Parse context */ SrcList *pSrc, /* SrcList containing the table to scan */ Index *pIdx, /* Foreign key index */ FKey *pFKey, /* Foreign key relationship */ int *aiCol, /* Map from FK to referenced table columns */ int regData, /* Referenced table data starts here */ int nIncr /* Amount to increment deferred counter by */ ){ sqlite3 *db = pParse->db; /* Database handle */ int i; /* Iterator variable */ Expr *pWhere = 0; /* WHERE clause to scan with */ NameContext sNameContext; /* Context used to resolve WHERE clause */ WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */ for(i=0; i<pFKey->nCol; i++){ Expr *pLeft; /* Value from deleted row */ Expr *pRight; /* Column ref to referencing table */ Expr *pEq; /* Expression (pLeft = pRight) */ int iCol; /* Index of column in referencing table */ const char *zCol; /* Name of column in referencing table */ pLeft = sqlite3Expr(db, TK_REGISTER, 0); if( pLeft ){ pLeft->iTable = (pIdx ? (regData+pIdx->aiColumn[i]+1) : regData); } iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; if( iCol<0 ){ zCol = "rowid"; }else{ zCol = pFKey->pFrom->aCol[iCol].zName; } pRight = sqlite3Expr(db, TK_ID, zCol); pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0); pWhere = sqlite3ExprAnd(db, pWhere, pEq); } /* Resolve the references in the WHERE clause. */ memset(&sNameContext, 0, sizeof(NameContext)); sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. If the constraint is not deferred, throw an exception for ** each row found. Otherwise, for deferred constraints, increment the ** deferred constraint counter by nIncr for each row selected. */ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0); if( pFKey->isDeferred && nIncr ){ assert( nIncr==1 || nIncr==-1 ); sqlite3VdbeAddOp1(pParse->pVdbe, OP_DeferredCons, nIncr); }else{ assert( nIncr==1 || nIncr==0 ); sqlite3HaltConstraint( pParse, OE_Abort, "foreign key constraint failed", P4_STATIC ); } sqlite3WhereEnd(pWInfo); /* Clean up the WHERE clause constructed above. */ sqlite3ExprDelete(db, pWhere); } /* ** This function returns a pointer to the head of a linked list of FK ** constraints that refer to the table passed as an argument. For example, ** given the following schema: ** ** CREATE TABLE t1(a PRIMARY KEY); ** CREATE TABLE t2(b REFERENCES t1(a); ** ** Calling this function with table "t1" as an argument returns a pointer ** to the FKey structure representing the foreign key constraint on table ** "t2". Calling this function with "t2" as the argument would return a ** NULL pointer (as there are no FK constraints that refer to t2). */ static FKey *fkRefering(Table *pTab){ int nName = sqlite3Strlen30(pTab->zName); return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName); } void sqlite3FkCheck( Parse *pParse, /* Parse context */ Table *pTab, /* Row is being deleted from this table */ ExprList *pChanges, /* Changed columns if this is an UPDATE */ int regOld, /* Previous row data is stored here */ int regNew /* New row data is stored here */ ){ sqlite3 *db = pParse->db; /* Database handle */ Vdbe *v; /* VM to write code to */ FKey *pFKey; /* Used to iterate through FKs */ int iDb; /* Index of database containing pTab */ const char *zDb; /* Name of database containing pTab */ assert( ( pChanges && regOld && regNew) /* UPDATE operation */ || (!pChanges && !regOld && regNew) /* INSERT operation */ || (!pChanges && regOld && !regNew) /* DELETE operation */ ); /* If foreign-keys are disabled, this function is a no-op. */ if( (db->flags&SQLITE_ForeignKeys)==0 ) return; v = sqlite3GetVdbe(pParse); iDb = sqlite3SchemaToIndex(db, pTab->pSchema); zDb = db->aDb[iDb].zName; /* Loop through all the foreign key constraints attached to the table. */ for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){ Table *pTo; /* Table referenced by this FK */ Index *pIdx = 0; /* Index on key columns in pTo */ int *aiCol = 0; if( pFKey->isDeferred==0 && regNew==0 ) continue; /* Find the table this foreign key references. Also find a unique ** index on the referenced table that corresponds to the key columns. ** If either of these things cannot be located, set an error in pParse ** and return early. */ pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb); if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiCol) ) return; assert( pFKey->nCol==1 || (aiCol && pIdx) ); /* If the key does not overlap with the pChanges list, skip this FK. */ if( pChanges ){ /* TODO */ } /* Take a shared-cache advisory read-lock on the referenced table. ** Allocate a cursor to use to search the unique index on the FK ** columns in the referenced table. */ sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName); pParse->nTab++; if( regOld!=0 && pFKey->isDeferred ){ fkCheckReference(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1); } if( regNew!=0 ){ fkCheckReference(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1); } sqlite3DbFree(db, aiCol); } /* Loop through all the foreign key constraints that refer to this table */ for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){ int iGoto; /* Address of OP_Goto instruction */ Index *pIdx = 0; /* Foreign key index for pFKey */ SrcList *pSrc; int *aiCol = 0; /* For immediate constraints, skip this scan if: ** ** 1) this is an INSERT operation, or ** 2) an UPDATE operation and the FK action is a trigger-action, or ** 3) a DELETE operation and the FK action is a trigger-action. ** ** A "trigger-action" is one of CASCADE, SET DEFAULT or SET NULL. */ if( pFKey->isDeferred==0 ){ if( regOld==0 ) continue; /* 1 */ if( regNew!=0 && pFKey->updateConf>OE_Restrict ) continue; /* 2 */ if( regNew==0 && pFKey->deleteConf>OE_Restrict ) continue; /* 3 */ } if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return; assert( aiCol || pFKey->nCol==1 ); /* Check if this update statement has modified any of the key columns ** for this foreign key constraint. If it has not, there is no need ** to search the referencing table for rows in violation. This is ** just an optimization. Things would work fine without this check. */ if( pChanges ){ /* TODO */ } /* Create a SrcList structure containing a single table (the table ** the foreign key that refers to this table is attached to). This ** is required for the sqlite3WhereXXX() interface. */ pSrc = sqlite3SrcListAppend(db, 0, 0, 0); if( !pSrc ) return; pSrc->a->pTab = pFKey->pFrom; pSrc->a->pTab->nRef++; pSrc->a->iCursor = pParse->nTab++; /* If this is an UPDATE, and none of the columns associated with this ** FK have been modified, do not scan the referencing table. Unlike ** the compile-time test implemented above, this is not just an ** optimization. It is required so that immediate foreign keys do not ** throw exceptions when the user executes a statement like: ** ** UPDATE refd_table SET refd_column = refd_column */ if( pChanges ){ int i; int iJump = sqlite3VdbeCurrentAddr(v) + pFKey->nCol + 1; for(i=0; i<pFKey->nCol; i++){ int iOff = (pIdx ? pIdx->aiColumn[i] : -1) + 1; sqlite3VdbeAddOp3(v, OP_Ne, regOld+iOff, iJump, regNew+iOff); } iGoto = sqlite3VdbeAddOp0(v, OP_Goto); } if( regNew!=0 && pFKey->isDeferred ){ fkScanReferences(pParse, pSrc, pIdx, pFKey, aiCol, regNew, -1); } if( regOld!=0 ){ /* If there is a RESTRICT action configured for the current operation ** on the referenced table of this FK, then throw an exception ** immediately if the FK constraint is violated, even if this is a ** deferred trigger. That's what RESTRICT means. To defer checking ** the constraint, the FK should specify NO ACTION (represented ** using OE_None). NO ACTION is the default. */ fkScanReferences(pParse, pSrc, pIdx, pFKey, aiCol, regOld, (pChanges!=0 && pFKey->updateConf!=OE_Restrict) || (pChanges==0 && pFKey->deleteConf!=OE_Restrict) ); } if( pChanges ){ sqlite3VdbeJumpHere(v, iGoto); } sqlite3SrcListDelete(db, pSrc); sqlite3DbFree(db, aiCol); } } #define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x))) /* ** This function is called before generating code to update or delete a ** row contained in table pTab. If the operation is an update, then ** pChanges is a pointer to the list of columns to modify. If this is a ** delete, then pChanges is NULL. */ u32 sqlite3FkOldmask( Parse *pParse, /* Parse context */ Table *pTab, /* Table being modified */ ExprList *pChanges /* Non-NULL for UPDATE operations */ ){ u32 mask = 0; if( pParse->db->flags&SQLITE_ForeignKeys ){ FKey *p; int i; for(p=pTab->pFKey; p; p=p->pNextFrom){ if( pChanges || p->isDeferred ){ for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom); } } for(p=fkRefering(pTab); p; p=p->pNextTo){ Index *pIdx = 0; locateFkeyIndex(0, pTab, p, &pIdx, 0); if( pIdx ){ for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]); } } } return mask; } /* ** This function is called before generating code to update or delete a ** row contained in table pTab. If the operation is an update, then ** pChanges is a pointer to the list of columns to modify. If this is a ** delete, then pChanges is NULL. ** ** If any foreign key processing will be required, this function returns ** true. If there is no foreign key related processing, this function ** returns false. */ int sqlite3FkRequired( Parse *pParse, /* Parse context */ Table *pTab, /* Table being modified */ ExprList *pChanges /* Non-NULL for UPDATE operations */ ){ if( pParse->db->flags&SQLITE_ForeignKeys ){ FKey *p; for(p=pTab->pFKey; p; p=p->pNextFrom){ if( pChanges || p->isDeferred ) return 1; } if( fkRefering(pTab) ) return 1; } return 0; } static Trigger *fkActionTrigger( Parse *pParse, Table *pTab, /* Table being updated or deleted from */ FKey *pFKey, /* Foreign key to get action for */ ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */ ){ sqlite3 *db = pParse->db; /* Database handle */ int action; Trigger *pTrigger; if( pChanges ){ action = pFKey->updateConf; pTrigger = pFKey->pOnUpdate; }else{ action = pFKey->deleteConf; pTrigger = pFKey->pOnDelete; } assert( OE_SetNull>OE_Restrict && OE_SetDflt>OE_Restrict ); assert( OE_Cascade>OE_Restrict && OE_None<OE_Restrict ); if( action>OE_Restrict && !pTrigger ){ char const *zFrom; /* Name of referencing table */ int nFrom; /* Length in bytes of zFrom */ Index *pIdx = 0; int *aiCol = 0; TriggerStep *pStep; sqlite3 *dbMem = pTab->dbMem; Expr *pWhere = 0; ExprList *pList = 0; int i; if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0; assert( aiCol || pFKey->nCol==1 ); assert( dbMem==0 || dbMem==pParse->db ); zFrom = pFKey->pFrom->zName; nFrom = sqlite3Strlen30(zFrom); pTrigger = (Trigger *)sqlite3DbMallocZero(dbMem, sizeof(Trigger) + /* struct Trigger */ sizeof(TriggerStep) + /* Single step in trigger program */ nFrom + 1 /* Space for pStep->target.z */ ); if( !pTrigger ){ pParse->db->mallocFailed = 1; return 0; } pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1]; pStep->target.z = (char *)&pStep[1]; pStep->target.n = nFrom; memcpy((char *)pStep->target.z, zFrom, nFrom); for(i=0; i<pFKey->nCol; i++){ Expr *pEq; int iFromCol; /* Idx of column in referencing table */ Token tFromCol; /* Name of column in referencing table */ Token tToCol; /* Name of column in referenced table */ Token tOld = { "old", 3 }; /* Literal "old" token */ Token tNew = { "new", 3 }; /* Literal "new" token */ iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid"; tFromCol.z = iFromCol<0 ? "oid" : pFKey->pFrom->aCol[iFromCol].zName; tToCol.n = sqlite3Strlen30(tToCol.z); tFromCol.n = sqlite3Strlen30(tFromCol.z); /* Create the expression "zFromCol = OLD.zToCol" */ pEq = sqlite3PExpr(pParse, TK_EQ, sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol), sqlite3PExpr(pParse, TK_DOT, sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld), sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol) , 0) , 0); pWhere = sqlite3ExprAnd(pParse->db, pWhere, pEq); if( action!=OE_Cascade || pChanges ){ Expr *pNew; if( action==OE_Cascade ){ pNew = sqlite3PExpr(pParse, TK_DOT, sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew), sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol) , 0); }else if( action==OE_SetDflt ){ Expr *pDflt = pIdx ? 0 : pTab->aCol[pIdx->aiColumn[i]].pDflt; if( pDflt ){ pNew = sqlite3ExprDup(db, pDflt, 0); }else{ pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0); } }else{ pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0); } pList = sqlite3ExprListAppend(pParse, pList, pNew); sqlite3ExprListSetName(pParse, pList, &tFromCol, 0); } } sqlite3DbFree(pParse->db, aiCol); pStep->pWhere = sqlite3ExprDup(dbMem, pWhere, EXPRDUP_REDUCE); pStep->pExprList = sqlite3ExprListDup(dbMem, pList, EXPRDUP_REDUCE); sqlite3ExprDelete(pParse->db, pWhere); sqlite3ExprListDelete(pParse->db, pList); pStep->op = (action!=OE_Cascade || pChanges) ? TK_UPDATE : TK_DELETE; pStep->pTrig = pTrigger; pTrigger->pSchema = pTab->pSchema; pTrigger->pTabSchema = pTab->pSchema; if( pChanges ){ pFKey->pOnUpdate = pTrigger; pTrigger->op = TK_UPDATE; pStep->op = TK_UPDATE; }else{ pFKey->pOnDelete = pTrigger; pTrigger->op = TK_DELETE; pStep->op = (action==OE_Cascade)?TK_DELETE:TK_UPDATE; } } return pTrigger; } static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){ if( p ){ TriggerStep *pStep = p->step_list; sqlite3ExprDelete(dbMem, pStep->pWhere); sqlite3ExprListDelete(dbMem, pStep->pExprList); sqlite3DbFree(dbMem, p); } } /* ** This function is called when deleting or updating a row to implement ** any required CASCADE, SET NULL or SET DEFAULT actions. */ void sqlite3FkActions( Parse *pParse, /* Parse context */ Table *pTab, /* Table being updated or deleted from */ ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */ int regOld /* Address of array containing old row */ ){ /* If foreign-key support is enabled, iterate through all FKs that ** refer to table pTab. If there is an action associated with the FK ** for this operation (either update or delete), invoke the associated ** trigger sub-program. */ if( pParse->db->flags&SQLITE_ForeignKeys ){ FKey *pFKey; /* Iterator variable */ for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){ Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges); if( pAction ){ sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0); } } } } /* ** Free all memory associated with foreign key definitions attached to ** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash ** hash table. */ void sqlite3FkDelete(Table *pTab){ FKey *pFKey; /* Iterator variable */ FKey *pNext; /* Copy of pFKey->pNextFrom */ for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){ /* Remove the FK from the fkeyHash hash table. */ if( pFKey->pPrevTo ){ pFKey->pPrevTo->pNextTo = pFKey->pNextTo; }else{ void *data = (void *)pFKey->pNextTo; const char *z = (data ? pFKey->pNextTo->zTo : pFKey->zTo); sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, sqlite3Strlen30(z), data); } if( pFKey->pNextTo ){ pFKey->pNextTo->pPrevTo = pFKey->pPrevTo; } /* Delete any triggers created to implement actions for this FK. */ fkTriggerDelete(pTab->dbMem, pFKey->pOnDelete); fkTriggerDelete(pTab->dbMem, pFKey->pOnUpdate); /* Delete the memory allocated for the FK structure. */ pNext = pFKey->pNextFrom; sqlite3DbFree(pTab->dbMem, pFKey); } } #endif |
Changes to src/insert.c.
︙ | ︙ | |||
975 976 977 978 979 980 981 982 983 984 985 986 987 988 | }else #endif { int isReplace; /* Set to true if constraints may cause a replace */ sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx, keyColumn>=0, 0, onError, endOfLoop, &isReplace ); sqlite3CompleteInsertion( pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0 ); } } /* Update the count of rows that are inserted | > | 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 | }else #endif { int isReplace; /* Set to true if constraints may cause a replace */ sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx, keyColumn>=0, 0, onError, endOfLoop, &isReplace ); sqlite3FkCheck(pParse, pTab, 0, 0, regIns); sqlite3CompleteInsertion( pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0 ); } } /* Update the count of rows that are inserted |
︙ | ︙ | |||
1373 1374 1375 1376 1377 1378 1379 | seenReplace = 1; break; } } sqlite3VdbeJumpHere(v, j3); sqlite3ReleaseTempReg(pParse, regR); } | | | 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 | seenReplace = 1; break; } } sqlite3VdbeJumpHere(v, j3); sqlite3ReleaseTempReg(pParse, regR); } if( pbMayReplace ){ *pbMayReplace = seenReplace; } } /* ** This routine generates code to finish the INSERT or UPDATE operation |
︙ | ︙ |
Changes to src/main.c.
︙ | ︙ | |||
726 727 728 729 730 731 732 733 734 735 736 737 738 739 | sqlite3VtabRollback(db); sqlite3EndBenignMalloc(); if( db->flags&SQLITE_InternChanges ){ sqlite3ExpirePreparedStatements(db); sqlite3ResetInternalSchema(db, 0); } /* If one has been configured, invoke the rollback-hook callback */ if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){ db->xRollbackCallback(db->pRollbackArg); } } | > > > | 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 | sqlite3VtabRollback(db); sqlite3EndBenignMalloc(); if( db->flags&SQLITE_InternChanges ){ sqlite3ExpirePreparedStatements(db); sqlite3ResetInternalSchema(db, 0); } /* Any deferred constraint violations have now been resolved. */ db->nDeferredCons = 0; /* If one has been configured, invoke the rollback-hook callback */ if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){ db->xRollbackCallback(db->pRollbackArg); } } |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
192 193 194 195 196 197 198 | id(A) ::= INDEXED(X). {A = X;} // The following directive causes tokens ABORT, AFTER, ASC, etc. to // fallback to ID if they will not parse as their original value. // This obviates the need for the "id" nonterminal. // %fallback ID | | | | | 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 | id(A) ::= INDEXED(X). {A = X;} // The following directive causes tokens ABORT, AFTER, ASC, etc. to // fallback to ID if they will not parse as their original value. // This obviates the need for the "id" nonterminal. // %fallback ID ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL %ifdef SQLITE_OMIT_COMPOUND_SELECT EXCEPT INTERSECT UNION %endif SQLITE_OMIT_COMPOUND_SELECT REINDEX RENAME CTIME_KW IF . |
︙ | ︙ | |||
310 311 312 313 314 315 316 | // The next group of rules parses the arguments to a REFERENCES clause // that determine if the referential integrity checking is deferred or // or immediate and which determine what action to take if a ref-integ // check fails. // %type refargs {int} | | < > | | 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 | // The next group of rules parses the arguments to a REFERENCES clause // that determine if the referential integrity checking is deferred or // or immediate and which determine what action to take if a ref-integ // check fails. // %type refargs {int} refargs(A) ::= . { A = OE_None * 0x010101; } refargs(A) ::= refargs(X) refarg(Y). { A = (X & ~Y.mask) | Y.value; } %type refarg {struct {int value; int mask;}} refarg(A) ::= MATCH nm. { A.value = 0; A.mask = 0x000000; } refarg(A) ::= ON DELETE refact(X). { A.value = X; A.mask = 0x0000ff; } refarg(A) ::= ON UPDATE refact(X). { A.value = X<<8; A.mask = 0x00ff00; } %type refact {int} refact(A) ::= SET NULL. { A = OE_SetNull; } refact(A) ::= SET DEFAULT. { A = OE_SetDflt; } refact(A) ::= CASCADE. { A = OE_Cascade; } refact(A) ::= RESTRICT. { A = OE_Restrict; } refact(A) ::= NO ACTION. { A = OE_None; } %type defer_subclause {int} defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt. {A = 0;} defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X). {A = X;} %type init_deferred_pred_opt {int} init_deferred_pred_opt(A) ::= . {A = 0;} init_deferred_pred_opt(A) ::= INITIALLY DEFERRED. {A = 1;} init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE. {A = 0;} // For the time being, the only constraint we care about is the primary |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
187 188 189 190 191 192 193 194 195 196 197 198 199 200 | { "writable_schema", SQLITE_WriteSchema|SQLITE_RecoveryMode }, { "omit_readlock", SQLITE_NoReadlock }, /* TODO: Maybe it shouldn't be possible to change the ReadUncommitted ** flag if there are any active statements. */ { "read_uncommitted", SQLITE_ReadUncommitted }, { "recursive_triggers", SQLITE_RecTriggers }, }; int i; const struct sPragmaType *p; for(i=0, p=aPragma; i<ArraySize(aPragma); i++, p++){ if( sqlite3StrICmp(zLeft, p->zName)==0 ){ sqlite3 *db = pParse->db; Vdbe *v; | > > > | 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 | { "writable_schema", SQLITE_WriteSchema|SQLITE_RecoveryMode }, { "omit_readlock", SQLITE_NoReadlock }, /* TODO: Maybe it shouldn't be possible to change the ReadUncommitted ** flag if there are any active statements. */ { "read_uncommitted", SQLITE_ReadUncommitted }, { "recursive_triggers", SQLITE_RecTriggers }, /* TODO: Prevent this flag from being set if not in auto-commit mode? */ { "foreign_keys", SQLITE_ForeignKeys }, }; int i; const struct sPragmaType *p; for(i=0, p=aPragma; i<ArraySize(aPragma); i++, p++){ if( sqlite3StrICmp(zLeft, p->zName)==0 ){ sqlite3 *db = pParse->db; Vdbe *v; |
︙ | ︙ | |||
227 228 229 230 231 232 233 | /* ** Return a human-readable name for a constraint resolution action. */ static const char *actionName(u8 action){ const char *zName; switch( action ){ | | | | > | | | 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 | /* ** Return a human-readable name for a constraint resolution action. */ static const char *actionName(u8 action){ const char *zName; switch( action ){ case OE_SetNull: zName = "SET NULL"; break; case OE_SetDflt: zName = "SET DEFAULT"; break; case OE_Cascade: zName = "CASCADE"; break; case OE_Restrict: zName = "RESTRICT"; break; default: zName = "NO ACTION"; assert( action==OE_None ); break; } return zName; } /* ** Process a pragma statement. ** |
︙ | ︙ |
Changes to src/shell.c.
︙ | ︙ | |||
847 848 849 850 851 852 853 | */ "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n" " EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n" "BEGIN\n" " /delete_action/\n" "END;\n" | | | 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 | */ "CREATE TRIGGER /name/_delete_referenced BEFORE DELETE ON /ref/ WHEN\n" " EXISTS (SELECT 1 FROM /tbl/ WHERE /cond2/)\n" "BEGIN\n" " /delete_action/\n" "END;\n" /* The "AFTER UPDATE ON <referenced>" trigger. This trigger's job ** is to detect when the key columns of a row in the referenced table ** to which one or more rows in the referencing table correspond are ** updated. The action taken depends on the value of the 'ON UPDATE' ** clause. */ "CREATE TRIGGER /name/_update_referenced AFTER\n" " UPDATE OF /fkey_list/ ON /ref/ WHEN \n" |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
666 667 668 669 670 671 672 673 674 675 676 677 678 679 | ** statements. */ struct Schema { int schema_cookie; /* Database schema version number for this file */ Hash tblHash; /* All tables indexed by name */ Hash idxHash; /* All (named) indices indexed by name */ Hash trigHash; /* All triggers indexed by name */ Table *pSeqTab; /* The sqlite_sequence table used by AUTOINCREMENT */ u8 file_format; /* Schema format version for this file */ u8 enc; /* Text encoding used by this database */ u16 flags; /* Flags associated with this schema */ int cache_size; /* Number of pages to use in the cache */ #ifndef SQLITE_OMIT_VIRTUALTABLE sqlite3 *db; /* "Owner" connection. See comment above */ | > | 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 | ** statements. */ struct Schema { int schema_cookie; /* Database schema version number for this file */ Hash tblHash; /* All tables indexed by name */ Hash idxHash; /* All (named) indices indexed by name */ Hash trigHash; /* All triggers indexed by name */ Hash fkeyHash; /* All foreign keys by referenced table name */ Table *pSeqTab; /* The sqlite_sequence table used by AUTOINCREMENT */ u8 file_format; /* Schema format version for this file */ u8 enc; /* Text encoding used by this database */ u16 flags; /* Flags associated with this schema */ int cache_size; /* Number of pages to use in the cache */ #ifndef SQLITE_OMIT_VIRTUALTABLE sqlite3 *db; /* "Owner" connection. See comment above */ |
︙ | ︙ | |||
856 857 858 859 860 861 862 863 864 865 866 867 868 869 | BusyHandler busyHandler; /* Busy callback */ int busyTimeout; /* Busy handler timeout, in msec */ Db aDbStatic[2]; /* Static space for the 2 default backends */ Savepoint *pSavepoint; /* List of active savepoints */ int nSavepoint; /* Number of non-transaction savepoints */ int nStatement; /* Number of nested statement-transactions */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY /* The following variables are all protected by the STATIC_MASTER ** mutex, not by sqlite3.mutex. They are used by code in notify.c. ** ** When X.pUnlockConnection==Y, that means that X is waiting for Y to ** unlock so that it can proceed. | > > > | 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 | BusyHandler busyHandler; /* Busy callback */ int busyTimeout; /* Busy handler timeout, in msec */ Db aDbStatic[2]; /* Static space for the 2 default backends */ Savepoint *pSavepoint; /* List of active savepoints */ int nSavepoint; /* Number of non-transaction savepoints */ int nStatement; /* Number of nested statement-transactions */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ #ifndef SQLITE_OMIT_FOREIGN_KEY i64 nDeferredCons; /* Net deferred constraints this transaction. */ #endif #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY /* The following variables are all protected by the STATIC_MASTER ** mutex, not by sqlite3.mutex. They are used by code in notify.c. ** ** When X.pUnlockConnection==Y, that means that X is waiting for Y to ** unlock so that it can proceed. |
︙ | ︙ | |||
912 913 914 915 916 917 918 919 920 921 922 923 924 925 | #define SQLITE_LegacyFileFmt 0x00008000 /* Create new databases in format 1 */ #define SQLITE_FullFSync 0x00010000 /* Use full fsync on the backend */ #define SQLITE_LoadExtension 0x00020000 /* Enable load_extension */ #define SQLITE_RecoveryMode 0x00040000 /* Ignore schema errors */ #define SQLITE_ReverseOrder 0x00100000 /* Reverse unordered SELECTs */ #define SQLITE_RecTriggers 0x00200000 /* Enable recursive triggers */ /* ** Possible values for the sqlite.magic field. ** The numbers are obtained at random and have no special meaning, other ** than being distinct from one another. */ #define SQLITE_MAGIC_OPEN 0xa029a697 /* Database is open */ | > | 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 | #define SQLITE_LegacyFileFmt 0x00008000 /* Create new databases in format 1 */ #define SQLITE_FullFSync 0x00010000 /* Use full fsync on the backend */ #define SQLITE_LoadExtension 0x00020000 /* Enable load_extension */ #define SQLITE_RecoveryMode 0x00040000 /* Ignore schema errors */ #define SQLITE_ReverseOrder 0x00100000 /* Reverse unordered SELECTs */ #define SQLITE_RecTriggers 0x00200000 /* Enable recursive triggers */ #define SQLITE_ForeignKeys 0x00400000 /* Enforce foreign key constraints */ /* ** Possible values for the sqlite.magic field. ** The numbers are obtained at random and have no special meaning, other ** than being distinct from one another. */ #define SQLITE_MAGIC_OPEN 0xa029a697 /* Database is open */ |
︙ | ︙ | |||
998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 | ** All current savepoints are stored in a linked list starting at ** sqlite3.pSavepoint. The first element in the list is the most recently ** opened savepoint. Savepoints are added to the list by the vdbe ** OP_Savepoint instruction. */ struct Savepoint { char *zName; /* Savepoint name (nul-terminated) */ Savepoint *pNext; /* Parent savepoint (if any) */ }; /* ** The following are used as the second parameter to sqlite3Savepoint(), ** and as the P1 argument to the OP_Savepoint instruction. */ | > | 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 | ** All current savepoints are stored in a linked list starting at ** sqlite3.pSavepoint. The first element in the list is the most recently ** opened savepoint. Savepoints are added to the list by the vdbe ** OP_Savepoint instruction. */ struct Savepoint { char *zName; /* Savepoint name (nul-terminated) */ int nDeferredCons; /* Number of deferred fk violations */ Savepoint *pNext; /* Parent savepoint (if any) */ }; /* ** The following are used as the second parameter to sqlite3Savepoint(), ** and as the P1 argument to the OP_Savepoint instruction. */ |
︙ | ︙ | |||
1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 | ** which is attached to the from-table. The to-table need not exist when ** the from-table is created. The existence of the to-table is not checked. */ struct FKey { Table *pFrom; /* The table that contains the REFERENCES clause */ FKey *pNextFrom; /* Next foreign key in pFrom */ char *zTo; /* Name of table that the key points to */ int nCol; /* Number of columns in this key */ u8 isDeferred; /* True if constraint checking is deferred till COMMIT */ u8 updateConf; /* How to resolve conflicts that occur on UPDATE */ u8 deleteConf; /* How to resolve conflicts that occur on DELETE */ u8 insertConf; /* How to resolve conflicts that occur on INSERT */ struct sColMap { /* Mapping of columns in pFrom to columns in zTo */ int iFrom; /* Index of column in pFrom */ char *zCol; /* Name of column in zTo. If 0 use PRIMARY KEY */ } aCol[1]; /* One entry for each of nCol column s */ }; /* | > > > > | 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 | ** which is attached to the from-table. The to-table need not exist when ** the from-table is created. The existence of the to-table is not checked. */ struct FKey { Table *pFrom; /* The table that contains the REFERENCES clause */ FKey *pNextFrom; /* Next foreign key in pFrom */ char *zTo; /* Name of table that the key points to */ FKey *pNextTo; /* Next foreign key on table named zTo */ FKey *pPrevTo; /* Previous foreign key on table named zTo */ int nCol; /* Number of columns in this key */ u8 isDeferred; /* True if constraint checking is deferred till COMMIT */ u8 updateConf; /* How to resolve conflicts that occur on UPDATE */ u8 deleteConf; /* How to resolve conflicts that occur on DELETE */ u8 insertConf; /* How to resolve conflicts that occur on INSERT */ Trigger *pOnUpdate; /* Trigger for AFTER UPDATE ON zTo */ Trigger *pOnDelete; /* Trigger for AFTER DELETE ON zTo */ struct sColMap { /* Mapping of columns in pFrom to columns in zTo */ int iFrom; /* Index of column in pFrom */ char *zCol; /* Name of column in zTo. If 0 use PRIMARY KEY */ } aCol[1]; /* One entry for each of nCol column s */ }; /* |
︙ | ︙ | |||
2582 2583 2584 2585 2586 2587 2588 | Token*, Select*, Expr*, IdList*); void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *); int sqlite3IndexedByLookup(Parse *, struct SrcList_item *); void sqlite3SrcListShiftJoinType(SrcList*); void sqlite3SrcListAssignCursors(Parse*, SrcList*); void sqlite3IdListDelete(sqlite3*, IdList*); void sqlite3SrcListDelete(sqlite3*, SrcList*); | | | 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 | Token*, Select*, Expr*, IdList*); void sqlite3SrcListIndexedBy(Parse *, SrcList *, Token *); int sqlite3IndexedByLookup(Parse *, struct SrcList_item *); void sqlite3SrcListShiftJoinType(SrcList*); void sqlite3SrcListAssignCursors(Parse*, SrcList*); void sqlite3IdListDelete(sqlite3*, IdList*); void sqlite3SrcListDelete(sqlite3*, SrcList*); Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Token*, int, int); void sqlite3DropIndex(Parse*, SrcList*, int); int sqlite3Select(Parse*, Select*, SelectDest*); Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, Expr*,ExprList*,int,Expr*,Expr*); void sqlite3SelectDelete(sqlite3*, Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); |
︙ | ︙ | |||
2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 2705 | void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*); void sqlite3DropTrigger(Parse*, SrcList*, int); void sqlite3DropTriggerPtr(Parse*, Trigger*); Trigger *sqlite3TriggersExist(Parse *, Table*, int, ExprList*, int *pMask); Trigger *sqlite3TriggerList(Parse *, Table *); void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *, int, int, int, int); void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*); void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*); TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*); TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*, ExprList*,Select*,u8); TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8); TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*); void sqlite3DeleteTrigger(sqlite3*, Trigger*); void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*); | > | | | 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 | void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*); void sqlite3DropTrigger(Parse*, SrcList*, int); void sqlite3DropTriggerPtr(Parse*, Trigger*); Trigger *sqlite3TriggersExist(Parse *, Table*, int, ExprList*, int *pMask); Trigger *sqlite3TriggerList(Parse *, Table *); void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *, int, int, int, int); void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int); void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*); void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*); TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*); TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*, ExprList*,Select*,u8); TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8); TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*); void sqlite3DeleteTrigger(sqlite3*, Trigger*); void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*); u32 sqlite3TriggerOldmask(Parse*,Trigger*,ExprList*,Table*,int); # define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p)) #else # define sqlite3TriggersExist(B,C,D,E,F) 0 # define sqlite3DeleteTrigger(A,B) # define sqlite3DropTriggerPtr(A,B) # define sqlite3UnlinkAndDeleteTrigger(A,B,C) # define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I,J) # define sqlite3TriggerList(X, Y) 0 # define sqlite3ParseToplevel(p) p # define sqlite3TriggerOldmask(A,B,C,D,E) 0 #endif int sqlite3JoinType(Parse*, Token*, Token*, Token*); void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int); void sqlite3DeferForeignKey(Parse*, int); #ifndef SQLITE_OMIT_AUTHORIZATION void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*); |
︙ | ︙ | |||
2927 2928 2929 2930 2931 2932 2933 | int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *); int sqlite3Reprepare(Vdbe*); void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*); CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *); int sqlite3TempInMemory(const sqlite3*); VTable *sqlite3GetVTable(sqlite3*, Table*); | | > > > > > > > > > > > > | 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 | int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *); int sqlite3Reprepare(Vdbe*); void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*); CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *); int sqlite3TempInMemory(const sqlite3*); VTable *sqlite3GetVTable(sqlite3*, Table*); #ifndef SQLITE_OMIT_FOREIGN_KEY void sqlite3FkCheck(Parse*, Table*, ExprList*, int, int); void sqlite3FkActions(Parse*, Table*, ExprList*, int); void sqlite3FkDelete(Table*); int sqlite3FkRequired(Parse*, Table*, ExprList*); u32 sqlite3FkOldmask(Parse*, Table*, ExprList*); #else #define sqlite3FkCheck(a,b,c,d,e) #define sqlite3FkActions(a,b,c,d) #define sqlite3FkDelete(a) #define sqlite3FkRequired(a,b,c) 0 #define sqlite3FkOldmask(a,b,c) 0 #endif /* ** Available fault injectors. Should be numbered beginning with 0. */ #define SQLITE_FAULTINJECTOR_MALLOC 0 #define SQLITE_FAULTINJECTOR_COUNT 1 |
︙ | ︙ |
Changes to src/trigger.c.
︙ | ︙ | |||
791 792 793 794 795 796 797 | Expr *pWhen = 0; /* Duplicate of trigger WHEN expression */ Vdbe *v; /* Temporary VM */ NameContext sNC; /* Name context for sub-vdbe */ SubProgram *pProgram = 0; /* Sub-vdbe for trigger program */ Parse *pSubParse; /* Parse context for sub-vdbe */ int iEndTrigger = 0; /* Label to jump to if WHEN is false */ | | | 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 | Expr *pWhen = 0; /* Duplicate of trigger WHEN expression */ Vdbe *v; /* Temporary VM */ NameContext sNC; /* Name context for sub-vdbe */ SubProgram *pProgram = 0; /* Sub-vdbe for trigger program */ Parse *pSubParse; /* Parse context for sub-vdbe */ int iEndTrigger = 0; /* Label to jump to if WHEN is false */ assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) ); /* Allocate the TriggerPrg and SubProgram objects. To ensure that they ** are freed if an error occurs, link them into the Parse.pTriggerPrg ** list of the top-level Parse object sooner rather than later. */ pPrg = sqlite3DbMallocZero(db, sizeof(TriggerPrg)); if( !pPrg ) return 0; pPrg->pNext = pTop->pTriggerPrg; |
︙ | ︙ | |||
892 893 894 895 896 897 898 | Trigger *pTrigger, /* Trigger to code */ Table *pTab, /* The table trigger pTrigger is attached to */ int orconf /* ON CONFLICT algorithm. */ ){ Parse *pRoot = sqlite3ParseToplevel(pParse); TriggerPrg *pPrg; | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 | Trigger *pTrigger, /* Trigger to code */ Table *pTab, /* The table trigger pTrigger is attached to */ int orconf /* ON CONFLICT algorithm. */ ){ Parse *pRoot = sqlite3ParseToplevel(pParse); TriggerPrg *pPrg; assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) ); /* It may be that this trigger has already been coded (or is in the ** process of being coded). If this is the case, then an entry with ** a matching TriggerPrg.pTrigger field will be present somewhere ** in the Parse.pTriggerPrg list. Search for such an entry. */ for(pPrg=pRoot->pTriggerPrg; pPrg && (pPrg->pTrigger!=pTrigger || pPrg->orconf!=orconf); pPrg=pPrg->pNext ); /* If an existing TriggerPrg could not be located, create a new one. */ if( !pPrg ){ pPrg = codeRowTrigger(pParse, pTrigger, pTab, orconf); } return pPrg; } void sqlite3CodeRowTriggerDirect( Parse *pParse, /* Parse context */ Trigger *p, /* Trigger to code */ Table *pTab, /* The table to code triggers from */ int oldIdx, /* The indice of the "old" row to access */ int orconf, /* ON CONFLICT policy */ int ignoreJump /* Instruction to jump to for RAISE(IGNORE) */ ){ Vdbe *v = sqlite3GetVdbe(pParse); /* Main VM */ TriggerPrg *pPrg; pPrg = getRowTrigger(pParse, p, pTab, orconf); assert( pPrg || pParse->nErr || pParse->db->mallocFailed ); /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program ** is a pointer to the sub-vdbe containing the trigger program. */ if( pPrg ){ sqlite3VdbeAddOp3(v, OP_Program, oldIdx, ignoreJump, ++pParse->nMem); pPrg->pProgram->nRef++; sqlite3VdbeChangeP4(v, -1, (const char *)pPrg->pProgram, P4_SUBPROGRAM); VdbeComment( (v, "Call: %s.%s", (p->zName?p->zName:"fkey"), onErrorText(orconf))); /* Set the P5 operand of the OP_Program instruction to non-zero if ** recursive invocation of this trigger program is disallowed. Recursive ** invocation is disallowed if (a) the sub-program is really a trigger, ** not a foreign key action, and (b) the flag to enable recursive triggers ** is clear. */ sqlite3VdbeChangeP5(v, p->zName && !(pParse->db->flags&SQLITE_RecTriggers)); } } /* ** This is called to code FOR EACH ROW triggers. ** ** When the code that this function generates is executed, the following ** must be true: ** |
︙ | ︙ | |||
972 973 974 975 976 977 978 | || p->pSchema==pParse->db->aDb[1].pSchema ); /* Determine whether we should code this trigger */ if( p->op==op && p->tr_tm==tr_tm && checkColumnOverlap(p->pColumns,pChanges) ){ | < < < < | < < < < < < < < | 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 | || p->pSchema==pParse->db->aDb[1].pSchema ); /* Determine whether we should code this trigger */ if( p->op==op && p->tr_tm==tr_tm && checkColumnOverlap(p->pColumns,pChanges) ){ sqlite3CodeRowTriggerDirect(pParse, p, pTab, oldIdx, orconf, ignoreJump); } } } /* ** Triggers fired by UPDATE or DELETE statements may access values stored ** in the old.* pseudo-table. This function returns a 32-bit bitmask |
︙ | ︙ | |||
1011 1012 1013 1014 1015 1016 1017 | ** by triggers. The caller must always assume that it is. ** ** There is no equivalent function for new.* references. */ u32 sqlite3TriggerOldmask( Parse *pParse, /* Parse context */ Trigger *pTrigger, /* List of triggers on table pTab */ | < > < | 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 | ** by triggers. The caller must always assume that it is. ** ** There is no equivalent function for new.* references. */ u32 sqlite3TriggerOldmask( Parse *pParse, /* Parse context */ Trigger *pTrigger, /* List of triggers on table pTab */ ExprList *pChanges, /* Changes list for any UPDATE OF triggers */ Table *pTab, /* The table to code triggers from */ int orconf /* Default ON CONFLICT policy for trigger steps */ ){ const int op = pChanges ? TK_UPDATE : TK_DELETE; u32 mask = 0; Trigger *p; for(p=pTrigger; p; p=p->pNext){ if( p->op==op && checkColumnOverlap(p->pColumns,pChanges) ){ TriggerPrg *pPrg; pPrg = getRowTrigger(pParse, p, pTab, orconf); if( pPrg ){ mask |= pPrg->oldmask; } |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
111 112 113 114 115 116 117 118 119 120 121 122 | Expr *pRowidExpr = 0; /* Expression defining the new record number */ int openAll = 0; /* True if all indices need to be opened */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int j1; /* Addresses of jump instructions */ int okOnePass; /* True for one-pass algorithm without the FIFO */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* Trying to update a view */ Trigger *pTrigger; /* List of triggers on pTab, if required */ #endif | > < | 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | Expr *pRowidExpr = 0; /* Expression defining the new record number */ int openAll = 0; /* True if all indices need to be opened */ AuthContext sContext; /* The authorization context */ NameContext sNC; /* The name-context to resolve expressions in */ int iDb; /* Database containing the table being updated */ int j1; /* Addresses of jump instructions */ int okOnePass; /* True for one-pass algorithm without the FIFO */ int hasFK; /* True if foreign key processing is required */ #ifndef SQLITE_OMIT_TRIGGER int isView; /* Trying to update a view */ Trigger *pTrigger; /* List of triggers on pTab, if required */ #endif /* Register Allocations */ int regRowCount = 0; /* A count of rows changed */ int regOldRowid; /* The old rowid */ int regNewRowid; /* The new rowid */ int regNew; int regOld = 0; |
︙ | ︙ | |||
154 155 156 157 158 159 160 161 162 163 164 165 166 167 | # define pTrigger 0 # define isView 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){ goto update_cleanup; } | > > | 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 | # define pTrigger 0 # define isView 0 #endif #ifdef SQLITE_OMIT_VIEW # undef isView # define isView 0 #endif hasFK = sqlite3FkRequired(pParse, pTab, pChanges); if( sqlite3ViewGetColumnNames(pParse, pTab) ){ goto update_cleanup; } if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){ goto update_cleanup; } |
︙ | ︙ | |||
269 270 271 272 273 274 275 | pTabList = 0; goto update_cleanup; } #endif /* Allocate required registers. */ regOldRowid = regNewRowid = ++pParse->nMem; | | | < < < < | 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 | pTabList = 0; goto update_cleanup; } #endif /* Allocate required registers. */ regOldRowid = regNewRowid = ++pParse->nMem; if( pTrigger || hasFK ){ regOld = pParse->nMem + 1; pParse->nMem += pTab->nCol; } if( chngRowid || pTrigger || hasFK ){ regNewRowid = ++pParse->nMem; } regNew = pParse->nMem + 1; pParse->nMem += pTab->nCol; regRec = ++pParse->nMem; /* Start the view context. */ if( isView ){ sqlite3AuthContextPush(pParse, &sContext, pTab->zName); } /* If we are trying to update a view, realize that view into ** a ephemeral table. */ #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) if( isView ){ sqlite3MaterializeView(pParse, pTab, pWhere, iCur); } |
︙ | ︙ | |||
373 374 375 376 377 378 379 380 381 382 | addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, 0, regOldRowid); } /* Make cursor iCur point to the record that is being updated. If ** this record does not exist for some reason (deleted by a trigger, ** for example, then jump to the next iteration of the RowSet loop. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); /* If there are triggers on this table, populate an array of registers ** with the required old.* column data. */ | > > > > > > > > > > | > > < < < < < < < | < < < | > | 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 | addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, regRowSet, 0, regOldRowid); } /* Make cursor iCur point to the record that is being updated. If ** this record does not exist for some reason (deleted by a trigger, ** for example, then jump to the next iteration of the RowSet loop. */ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid); /* If the record number will change, set register regNewRowid to ** contain the new value. If the record number is not being modified, ** then regNewRowid is the same register as regOldRowid, which is ** already populated. */ assert( chngRowid || pTrigger || hasFK || regOldRowid==regNewRowid ); if( chngRowid ){ sqlite3ExprCode(pParse, pRowidExpr, regNewRowid); sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid); } /* If there are triggers on this table, populate an array of registers ** with the required old.* column data. */ if( hasFK || pTrigger ){ u32 oldmask = sqlite3FkOldmask(pParse, pTab, pChanges); oldmask |= sqlite3TriggerOldmask(pParse, pTrigger, pChanges, pTab, onError); for(i=0; i<pTab->nCol; i++){ if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){ sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i); sqlite3ColumnDefault(v, pTab, i, regOld+i); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i); } } if( chngRowid==0 ){ sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid); } } /* Populate the array of registers beginning at regNew with the new ** row data. This array is used to check constaints, create the new ** table and index records, and as the values for any new.* references ** made by triggers. */ for(i=0; i<pTab->nCol; i++){ |
︙ | ︙ | |||
439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 | if( !isView ){ /* Do constraint checks. */ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); /* Delete the index entries associated with the current record. */ j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); /* If changing the record number, delete the old record. */ if( chngRowid ){ sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); } sqlite3VdbeJumpHere(v, j1); /* Insert the new index entries and the new record. */ sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0); } /* Increment the row counter */ if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } | > > > > > > > > | 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 | if( !isView ){ /* Do constraint checks. */ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); /* Do FK constraint checks. */ sqlite3FkCheck(pParse, pTab, pChanges, regOldRowid, regNewRowid); /* Delete the index entries associated with the current record. */ j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); /* If changing the record number, delete the old record. */ if( chngRowid ){ sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); } sqlite3VdbeJumpHere(v, j1); /* Insert the new index entries and the new record. */ sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0); /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key ** to the row just updated. */ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); } /* Increment the row counter */ if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
868 869 870 871 872 873 874 | p->rc = pOp->p1; p->errorAction = (u8)pOp->p2; p->pc = pc; if( pOp->p4.z ){ sqlite3SetString(&p->zErrMsg, db, "%s", pOp->p4.z); } rc = sqlite3VdbeHalt(p); | | > > | 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 | p->rc = pOp->p1; p->errorAction = (u8)pOp->p2; p->pc = pc; if( pOp->p4.z ){ sqlite3SetString(&p->zErrMsg, db, "%s", pOp->p4.z); } rc = sqlite3VdbeHalt(p); assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR ); if( rc==SQLITE_BUSY ){ p->rc = rc = SQLITE_BUSY; }else{ assert( rc==SQLITE_OK || p->rc==SQLITE_CONSTRAINT ); assert( rc==SQLITE_OK || db->nDeferredCons>0 ); rc = p->rc ? SQLITE_ERROR : SQLITE_DONE; } goto vdbe_return; } /* Opcode: Integer P1 P2 * * * ** |
︙ | ︙ | |||
2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 | db->isTransactionSavepoint = 1; }else{ db->nSavepoint++; } /* Link the new savepoint into the database handle's list. */ pNew->pNext = db->pSavepoint; db->pSavepoint = pNew; } } }else{ iSavepoint = 0; /* Find the named savepoint. If there is no such savepoint, then an | > | 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 | db->isTransactionSavepoint = 1; }else{ db->nSavepoint++; } /* Link the new savepoint into the database handle's list. */ pNew->pNext = db->pSavepoint; pNew->nDeferredCons = db->nDeferredCons; db->pSavepoint = pNew; } } }else{ iSavepoint = 0; /* Find the named savepoint. If there is no such savepoint, then an |
︙ | ︙ | |||
2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 | /* Determine whether or not this is a transaction savepoint. If so, ** and this is a RELEASE command, then the current transaction ** is committed. */ int isTransaction = pSavepoint->pNext==0 && db->isTransactionSavepoint; if( isTransaction && p1==SAVEPOINT_RELEASE ){ db->autoCommit = 1; if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){ p->pc = pc; db->autoCommit = 0; p->rc = rc = SQLITE_BUSY; goto vdbe_return; } | > > > | 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 | /* Determine whether or not this is a transaction savepoint. If so, ** and this is a RELEASE command, then the current transaction ** is committed. */ int isTransaction = pSavepoint->pNext==0 && db->isTransactionSavepoint; if( isTransaction && p1==SAVEPOINT_RELEASE ){ if( (rc = sqlite3VdbeCheckDeferred(p))!=SQLITE_OK ){ goto vdbe_return; } db->autoCommit = 1; if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){ p->pc = pc; db->autoCommit = 0; p->rc = rc = SQLITE_BUSY; goto vdbe_return; } |
︙ | ︙ | |||
2573 2574 2575 2576 2577 2578 2579 | while( db->pSavepoint!=pSavepoint ){ pTmp = db->pSavepoint; db->pSavepoint = pTmp->pNext; sqlite3DbFree(db, pTmp); db->nSavepoint--; } | | > > > > > | 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 | while( db->pSavepoint!=pSavepoint ){ pTmp = db->pSavepoint; db->pSavepoint = pTmp->pNext; sqlite3DbFree(db, pTmp); db->nSavepoint--; } /* If it is a RELEASE, then destroy the savepoint being operated on ** too. If it is a ROLLBACK TO, then set the number of deferred ** constraint violations present in the database to the value stored ** when the savepoint was created. */ if( p1==SAVEPOINT_RELEASE ){ assert( pSavepoint==db->pSavepoint ); db->pSavepoint = pSavepoint->pNext; sqlite3DbFree(db, pSavepoint); if( !isTransaction ){ db->nSavepoint--; } }else{ db->nDeferredCons = pSavepoint->nDeferredCons; } } } break; } |
︙ | ︙ | |||
2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 | "SQL statements in progress"); rc = SQLITE_BUSY; }else if( desiredAutoCommit!=db->autoCommit ){ if( iRollback ){ assert( desiredAutoCommit==1 ); sqlite3RollbackAll(db); db->autoCommit = 1; }else{ db->autoCommit = (u8)desiredAutoCommit; if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){ p->pc = pc; db->autoCommit = (u8)(1-desiredAutoCommit); p->rc = rc = SQLITE_BUSY; goto vdbe_return; | > > | 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 | "SQL statements in progress"); rc = SQLITE_BUSY; }else if( desiredAutoCommit!=db->autoCommit ){ if( iRollback ){ assert( desiredAutoCommit==1 ); sqlite3RollbackAll(db); db->autoCommit = 1; }else if( (rc = sqlite3VdbeCheckDeferred(p))!=SQLITE_OK ){ goto vdbe_return; }else{ db->autoCommit = (u8)desiredAutoCommit; if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){ p->pc = pc; db->autoCommit = (u8)(1-desiredAutoCommit); p->rc = rc = SQLITE_BUSY; goto vdbe_return; |
︙ | ︙ | |||
2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 | assert( sqlite3BtreeIsInTrans(pBt) ); if( p->iStatement==0 ){ assert( db->nStatement>=0 && db->nSavepoint>=0 ); db->nStatement++; p->iStatement = db->nSavepoint + db->nStatement; } rc = sqlite3BtreeBeginStmt(pBt, p->iStatement); } } break; } /* Opcode: ReadCookie P1 P2 P3 * * ** | > > > > > | 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 | assert( sqlite3BtreeIsInTrans(pBt) ); if( p->iStatement==0 ){ assert( db->nStatement>=0 && db->nSavepoint>=0 ); db->nStatement++; p->iStatement = db->nSavepoint + db->nStatement; } rc = sqlite3BtreeBeginStmt(pBt, p->iStatement); /* Store the current value of the database handles deferred constraint ** counter. If the statement transaction needs to be rolled back, ** the value of this counter needs to be restored too. */ p->nStmtDefCons = db->nDeferredCons; } } break; } /* Opcode: ReadCookie P1 P2 P3 * * ** |
︙ | ︙ | |||
4729 4730 4731 4732 4733 4734 4735 | SubProgram *pProgram; /* Sub-program to execute */ void *t; /* Token identifying trigger */ pProgram = pOp->p4.pProgram; pRt = &p->aMem[pOp->p3]; assert( pProgram->nOp>0 ); | | | > | | < | | 4747 4748 4749 4750 4751 4752 4753 4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 4768 4769 4770 4771 4772 | SubProgram *pProgram; /* Sub-program to execute */ void *t; /* Token identifying trigger */ pProgram = pOp->p4.pProgram; pRt = &p->aMem[pOp->p3]; assert( pProgram->nOp>0 ); /* If the p5 flag is clear, then recursive invocation of triggers is ** disabled for backwards compatibility (p5 is set if this sub-program ** is really a trigger, not a foreign key action, and the flag set ** and cleared by the "PRAGMA recursive_triggers" command is clear). ** ** It is recursive invocation of triggers, at the SQL level, that is ** disabled. In some cases a single trigger may generate more than one ** SubProgram (if the trigger may be executed with more than one different ** ON CONFLICT algorithm). SubProgram structures associated with a ** single trigger all have the same value for the SubProgram.token ** variable. */ if( pOp->p5 ){ t = pProgram->token; for(pFrame=p->pFrame; pFrame && pFrame->token!=t; pFrame=pFrame->pParent); if( pFrame ) break; } if( p->nFrame>db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){ rc = SQLITE_ERROR; |
︙ | ︙ | |||
4838 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 | pIn = &pFrame->aMem[pOp->p1 + pFrame->aOp[pFrame->pc].p1]; sqlite3VdbeMemShallowCopy(pOut, pIn, MEM_Ephem); break; } #endif /* #ifndef SQLITE_OMIT_TRIGGER */ #ifndef SQLITE_OMIT_AUTOINCREMENT /* Opcode: MemMax P1 P2 * * * ** ** P1 is a register in the root frame of this VM (the root frame is ** different from the current frame if this instruction is being executed ** within a sub-program). Set the value of register P1 to the maximum of ** its current value and the value in register P2. | > > > > > > > > > > > > | 4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 4877 4878 4879 4880 4881 | pIn = &pFrame->aMem[pOp->p1 + pFrame->aOp[pFrame->pc].p1]; sqlite3VdbeMemShallowCopy(pOut, pIn, MEM_Ephem); break; } #endif /* #ifndef SQLITE_OMIT_TRIGGER */ #ifndef SQLITE_OMIT_FOREIGN_KEY /* Opcode: DeferredCons P1 * * * * ** ** Increment the database handles "deferred constraint violation" counter ** by P1 (P1 may be negative or positive). */ case OP_DeferredCons: { db->nDeferredCons += pOp->p1; break; } #endif /* #ifndef SQLITE_OMIT_FOREIGN_KEY */ #ifndef SQLITE_OMIT_AUTOINCREMENT /* Opcode: MemMax P1 P2 * * * ** ** P1 is a register in the root frame of this VM (the root frame is ** different from the current frame if this instruction is being executed ** within a sub-program). Set the value of register P1 to the maximum of ** its current value and the value in register P2. |
︙ | ︙ |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
311 312 313 314 315 316 317 318 319 320 321 322 323 324 | int nChange; /* Number of db changes made since last reset */ int btreeMask; /* Bitmask of db->aDb[] entries referenced */ i64 startTime; /* Time when query started - used for profiling */ BtreeMutexArray aMutex; /* An array of Btree used here and needing locks */ int aCounter[2]; /* Counters used by sqlite3_stmt_status() */ char *zSql; /* Text of the SQL statement that generated this */ void *pFree; /* Free this when deleting the vdbe */ int iStatement; /* Statement number (or 0 if has not opened stmt) */ #ifdef SQLITE_DEBUG FILE *trace; /* Write an execution trace here, if not NULL */ #endif VdbeFrame *pFrame; /* Parent frame */ int nFrame; /* Number of frames in pFrame list */ }; | > | 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 | int nChange; /* Number of db changes made since last reset */ int btreeMask; /* Bitmask of db->aDb[] entries referenced */ i64 startTime; /* Time when query started - used for profiling */ BtreeMutexArray aMutex; /* An array of Btree used here and needing locks */ int aCounter[2]; /* Counters used by sqlite3_stmt_status() */ char *zSql; /* Text of the SQL statement that generated this */ void *pFree; /* Free this when deleting the vdbe */ i64 nStmtDefCons; /* Number of def. constraints when stmt started */ int iStatement; /* Statement number (or 0 if has not opened stmt) */ #ifdef SQLITE_DEBUG FILE *trace; /* Write an execution trace here, if not NULL */ #endif VdbeFrame *pFrame; /* Parent frame */ int nFrame; /* Number of frames in pFrame list */ }; |
︙ | ︙ | |||
382 383 384 385 386 387 388 389 390 391 392 393 394 395 | int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve); int sqlite3VdbeCloseStatement(Vdbe *, int); void sqlite3VdbeFrameDelete(VdbeFrame*); int sqlite3VdbeFrameRestore(VdbeFrame *); #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT int sqlite3VdbeReleaseBuffers(Vdbe *p); #endif #ifndef SQLITE_OMIT_SHARED_CACHE void sqlite3VdbeMutexArrayEnter(Vdbe *p); #else # define sqlite3VdbeMutexArrayEnter(p) #endif | > > > > > > | 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 | int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve); int sqlite3VdbeCloseStatement(Vdbe *, int); void sqlite3VdbeFrameDelete(VdbeFrame*); int sqlite3VdbeFrameRestore(VdbeFrame *); #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT int sqlite3VdbeReleaseBuffers(Vdbe *p); #endif #ifndef SQLITE_OMIT_FOREIGN_KEY int sqlite3VdbeCheckDeferred(Vdbe *); #else # define sqlite3VdbeCheckDeferred(p) 0 #endif #ifndef SQLITE_OMIT_SHARED_CACHE void sqlite3VdbeMutexArrayEnter(Vdbe *p); #else # define sqlite3VdbeMutexArrayEnter(p) #endif |
︙ | ︙ |
Changes to src/vdbeapi.c.
︙ | ︙ | |||
318 319 320 321 322 323 324 325 326 327 328 329 330 331 | /* If there are no other statements currently running, then ** reset the interrupt flag. This prevents a call to sqlite3_interrupt ** from interrupting a statement that has not yet started. */ if( db->activeVdbeCnt==0 ){ db->u1.isInterrupted = 0; } #ifndef SQLITE_OMIT_TRACE if( db->xProfile && !db->init.busy ){ double rNow; sqlite3OsCurrentTime(db->pVfs, &rNow); p->startTime = (u64)((rNow - (int)rNow)*3600.0*24.0*1000000000.0); } | > > | 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 | /* If there are no other statements currently running, then ** reset the interrupt flag. This prevents a call to sqlite3_interrupt ** from interrupting a statement that has not yet started. */ if( db->activeVdbeCnt==0 ){ db->u1.isInterrupted = 0; } assert( db->writeVdbeCnt>0 || db->autoCommit==0 || db->nDeferredCons==0 ); #ifndef SQLITE_OMIT_TRACE if( db->xProfile && !db->init.busy ){ double rNow; sqlite3OsCurrentTime(db->pVfs, &rNow); p->startTime = (u64)((rNow - (int)rNow)*3600.0*24.0*1000000000.0); } |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 | if( rc==SQLITE_OK ){ rc = rc2; } } } db->nStatement--; p->iStatement = 0; } return rc; } /* ** If SQLite is compiled to support shared-cache mode and to be threadsafe, ** this routine obtains the mutex associated with each BtShared structure | > > > > > > > | 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 | if( rc==SQLITE_OK ){ rc = rc2; } } } db->nStatement--; p->iStatement = 0; /* If the statement transaction is being rolled back, also restore the ** database handles deferred constraint counter to the value it had when ** the statement transaction was opened. */ if( eOp==SAVEPOINT_ROLLBACK ){ db->nDeferredCons = p->nStmtDefCons; } } return rc; } /* ** If SQLite is compiled to support shared-cache mode and to be threadsafe, ** this routine obtains the mutex associated with each BtShared structure |
︙ | ︙ | |||
1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 | sqlite3BtreeMutexArrayEnter(&p->aMutex); #else sqlite3BtreeEnterAll(p->db); #endif } #endif /* ** This routine is called the when a VDBE tries to halt. If the VDBE ** has made changes and is in autocommit mode, then commit those ** changes. If a rollback is needed, then do the rollback. ** ** This routine is the only way to move the state of a VM from ** SQLITE_MAGIC_RUN to SQLITE_MAGIC_HALT. It is harmless to | > > > > > > > > > > > > > > > > > > > > > > | 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 | sqlite3BtreeMutexArrayEnter(&p->aMutex); #else sqlite3BtreeEnterAll(p->db); #endif } #endif /* ** This function is called when a transaction opened by the database ** handle associated with the VM passed as an argument is about to be ** committed. If there are outstanding deferred foreign key constraint ** violations, return SQLITE_ERROR. Otherwise, SQLITE_OK. ** ** If there are outstanding FK violations and this function returns ** SQLITE_ERROR, set the result of the VM to SQLITE_CONSTRAINT and write ** an error message to it. Then return SQLITE_ERROR. */ #ifndef SQLITE_OMIT_FOREIGN_KEY int sqlite3VdbeCheckDeferred(Vdbe *p){ sqlite3 *db = p->db; if( db->nDeferredCons ){ p->rc = SQLITE_CONSTRAINT; sqlite3SetString(&p->zErrMsg, db, "foreign key constraint failed"); return SQLITE_ERROR; } return SQLITE_OK; } #endif /* ** This routine is called the when a VDBE tries to halt. If the VDBE ** has made changes and is in autocommit mode, then commit those ** changes. If a rollback is needed, then do the rollback. ** ** This routine is the only way to move the state of a VM from ** SQLITE_MAGIC_RUN to SQLITE_MAGIC_HALT. It is harmless to |
︙ | ︙ | |||
2008 2009 2010 2011 2012 2013 2014 | ** above has occurred. */ if( !sqlite3VtabInSync(db) && db->autoCommit && db->writeVdbeCnt==(p->readOnly==0) ){ if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){ | > > > > | | > | < > | 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 | ** above has occurred. */ if( !sqlite3VtabInSync(db) && db->autoCommit && db->writeVdbeCnt==(p->readOnly==0) ){ if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){ if( sqlite3VdbeCheckDeferred(p) ){ sqlite3BtreeMutexArrayLeave(&p->aMutex); return SQLITE_ERROR; } /* The auto-commit flag is true, the vdbe program was successful ** or hit an 'OR FAIL' constraint and there are no deferred foreign ** key constraints to hold up the transaction. This means a commit ** is required. */ rc = vdbeCommit(db, p); if( rc==SQLITE_BUSY ){ sqlite3BtreeMutexArrayLeave(&p->aMutex); return SQLITE_BUSY; }else if( rc!=SQLITE_OK ){ p->rc = rc; sqlite3RollbackAll(db); }else{ db->nDeferredCons = 0; sqlite3CommitInternalChanges(db); } }else{ sqlite3RollbackAll(db); } db->nStatement = 0; }else if( eStatementOp==0 ){ |
︙ | ︙ |
Changes to src/vdbeblob.c.
︙ | ︙ | |||
140 141 142 143 144 145 146 | rc = SQLITE_ERROR; (void)sqlite3SafetyOff(db); sqlite3BtreeLeaveAll(db); goto blob_open_out; } /* If the value is being opened for writing, check that the | > | | < > > > > > > > > > > > > > > < < | < < < < > > > > > > > > | 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 | rc = SQLITE_ERROR; (void)sqlite3SafetyOff(db); sqlite3BtreeLeaveAll(db); goto blob_open_out; } /* If the value is being opened for writing, check that the ** column is not indexed, and that it is not part of a foreign key. ** It is against the rules to open a column to which either of these ** descriptions applies for writing. */ if( flags ){ const char *zFault = 0; Index *pIdx; #ifndef SQLITE_OMIT_FOREIGN_KEY if( db->flags&SQLITE_ForeignKeys ){ FKey *pFKey; for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){ int j; for(j=0; j<pFKey->nCol; j++){ if( pFKey->aCol[j].iFrom==iCol ){ zFault = "foreign key"; } } } } #endif for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int j; for(j=0; j<pIdx->nColumn; j++){ if( pIdx->aiColumn[j]==iCol ){ zFault = "indexed"; } } } if( zFault ){ sqlite3DbFree(db, zErr); zErr = sqlite3MPrintf(db, "cannot open %s column for writing", zFault); rc = SQLITE_ERROR; (void)sqlite3SafetyOff(db); sqlite3BtreeLeaveAll(db); goto blob_open_out; } } v = sqlite3VdbeCreate(db); if( v ){ int iDb = sqlite3SchemaToIndex(db, pTab->pSchema); sqlite3VdbeAddOpList(v, sizeof(openBlob)/sizeof(VdbeOpList), openBlob); flags = !!flags; /* flags = (flags ? 1 : 0); */ |
︙ | ︙ |
Changes to test/auth.test.
︙ | ︙ | |||
2244 2245 2246 2247 2248 2249 2250 | set authargs {} execsql { UPDATE v1 SET x=1 WHERE x=117 } set authargs } [list \ SQLITE_UPDATE v1 x main {} \ | < < < > > > > | 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 | set authargs {} execsql { UPDATE v1 SET x=1 WHERE x=117 } set authargs } [list \ SQLITE_UPDATE v1 x main {} \ SQLITE_SELECT {} {} {} v1 \ SQLITE_READ t2 a main v1 \ SQLITE_READ t2 b main v1 \ SQLITE_SELECT {} {} {} {} \ SQLITE_READ v1 x main v1 \ SQLITE_INSERT v1chng {} main r2 \ SQLITE_READ v1 x main r2 \ SQLITE_READ v1 x main r2 \ ] do_test auth-4.4 { execsql { CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN INSERT INTO v1chng VALUES(OLD.x,NULL); END; SELECT * FROM v1; } |
︙ | ︙ |
Changes to test/fkey1.test.
︙ | ︙ | |||
42 43 44 45 46 47 48 | y TEXT ); } } {} do_test fkey1-1.2 { execsql { CREATE TABLE t3( | | | 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | y TEXT ); } } {} do_test fkey1-1.2 { execsql { CREATE TABLE t3( a INTEGER REFERENCES t2, b INTEGER REFERENCES t1, FOREIGN KEY (a,b) REFERENCES t2(x,y) ); } } {} do_test fkey1-2.1 { |
︙ | ︙ | |||
76 77 78 79 80 81 82 | CREATE TABLE t5(a PRIMARY KEY, b, c); CREATE TABLE t6( d REFERENCES t5, e REFERENCES t5(c) ); PRAGMA foreign_key_list(t6); } | | | | | | | | 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | CREATE TABLE t5(a PRIMARY KEY, b, c); CREATE TABLE t6( d REFERENCES t5, e REFERENCES t5(c) ); PRAGMA foreign_key_list(t6); } } [concat \ {0 0 t5 e c {NO ACTION} {NO ACTION} NONE} \ {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE} \ ] do_test fkey1-3.2 { execsql { CREATE TABLE t7(d, e, f, FOREIGN KEY (d, e) REFERENCES t5(a, b) ); PRAGMA foreign_key_list(t7); } } [concat \ {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \ {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \ ] do_test fkey1-3.3 { execsql { CREATE TABLE t8(d, e, f, FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL ); PRAGMA foreign_key_list(t8); |
︙ | ︙ |
Added test/fkey2.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 | # 2009 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for foreign keys. # set testdir [file dirname $argv0] source $testdir/tester.tcl #------------------------------------------------------------------------- # Test structure: # # fkey2-1.*: Simple tests to check that immediate and deferred foreign key # constraints work when not inside a transaction. # # fkey2-2.*: Tests to verify that deferred foreign keys work inside # explicit transactions (i.e that processing really is deferred). # # fkey2-3.*: Tests that a statement transaction is rolled back if an # immediate foreign key constraint is violated. # # fkey2-4.*: Test that FK actions may recurse even when recursive triggers # are disabled. # # fkey2-5.*: Check that if foreign-keys are enabled, it is not possible # to write to an FK column using the incremental blob API. # # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey # command. Recycled to test the built-in implementation. # proc drop_all_tables {{db db}} { set tbls [execsql {SELECT name FROM sqlite_master WHERE type = 'table'}] foreach t [execsql { SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT like 'sqlite_%' }] { execsql "DROP TABLE $t" } } execsql { PRAGMA foreign_keys = on } set FkeySimpleSchema { PRAGMA foreign_keys = on; CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(c REFERENCES t1(a) /D/ , d); CREATE TABLE t3(a PRIMARY KEY, b); CREATE TABLE t4(c REFERENCES t3 /D/, d); CREATE TABLE t5(a INTEGER PRIMARY KEY, b); CREATE TABLE t6(c REFERENCES t5(oid) /D/, d); CREATE TABLE t7(a, b INTEGER PRIMARY KEY); CREATE TABLE t8(c REFERENCES t7 /D/, d); CREATE TABLE t9(a REFERENCES nosuchtable, b); CREATE TABLE t10(a REFERENCES t9(c) /D/, b); } set FkeySimpleTests { 1.1 "INSERT INTO t2 VALUES(1, 3)" {1 {foreign key constraint failed}} 1.2 "INSERT INTO t1 VALUES(1, 2)" {0 {}} 1.3 "INSERT INTO t2 VALUES(1, 3)" {0 {}} 1.4 "INSERT INTO t2 VALUES(2, 4)" {1 {foreign key constraint failed}} 1.5 "INSERT INTO t2 VALUES(NULL, 4)" {0 {}} 1.6 "UPDATE t2 SET c=2 WHERE d=4" {1 {foreign key constraint failed}} 1.7 "UPDATE t2 SET c=1 WHERE d=4" {0 {}} 1.9 "UPDATE t2 SET c=1 WHERE d=4" {0 {}} 1.10 "UPDATE t2 SET c=NULL WHERE d=4" {0 {}} 1.11 "DELETE FROM t1 WHERE a=1" {1 {foreign key constraint failed}} 1.12 "UPDATE t1 SET a = 2" {1 {foreign key constraint failed}} 1.13 "UPDATE t1 SET a = 1" {0 {}} 2.1 "INSERT INTO t4 VALUES(1, 3)" {1 {foreign key constraint failed}} 2.2 "INSERT INTO t3 VALUES(1, 2)" {0 {}} 2.3 "INSERT INTO t4 VALUES(1, 3)" {0 {}} 3.1 "INSERT INTO t6 VALUES(1, 3)" {1 {foreign key constraint failed}} 3.2 "INSERT INTO t5 VALUES(1, 2)" {0 {}} 3.3 "INSERT INTO t6 VALUES(1, 3)" {0 {}} 4.1 "INSERT INTO t8 VALUES(1, 3)" {1 {foreign key constraint failed}} 4.2 "INSERT INTO t7 VALUES(2, 1)" {0 {}} 4.3 "INSERT INTO t8 VALUES(1, 3)" {0 {}} 4.4 "INSERT INTO t8 VALUES(2, 4)" {1 {foreign key constraint failed}} 4.5 "INSERT INTO t8 VALUES(NULL, 4)" {0 {}} 4.6 "UPDATE t8 SET c=2 WHERE d=4" {1 {foreign key constraint failed}} 4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} 4.9 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} 4.10 "UPDATE t8 SET c=NULL WHERE d=4" {0 {}} 4.11 "DELETE FROM t7 WHERE b=1" {1 {foreign key constraint failed}} 4.12 "UPDATE t7 SET b = 2" {1 {foreign key constraint failed}} 4.13 "UPDATE t7 SET b = 1" {0 {}} 5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}} 5.2 "INSERT INTO t10 VALUES(1, 3)" {1 {foreign key mismatch}} } do_test fkey2-1.1.0 { execsql [string map {/D/ {}} $FkeySimpleSchema] } {} foreach {tn zSql res} $FkeySimpleTests { do_test fkey2-1.1.$tn { catchsql $zSql } $res } drop_all_tables do_test fkey2-1.2.0 { execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema ] } {} foreach {tn zSql res} $FkeySimpleTests { do_test fkey2-1.2.$tn { catchsql $zSql } $res } drop_all_tables #------------------------------------------------------------------------- # This section (test cases fkey2-2.*) contains tests to check that the # deferred foreign key constraint logic works. # proc fkey2-2-test {tn nocommit sql {res {}}} { if {$res eq "FKV"} { set expected {1 {foreign key constraint failed}} } else { set expected [list 0 $res] } do_test fkey2-2.$tn [list catchsql $sql] $expected if {$nocommit} { do_test fkey2-2.${tn}c { catchsql COMMIT } {1 {foreign key constraint failed}} } } fkey2-2-test 1 0 { CREATE TABLE node( nodeid PRIMARY KEY, parent REFERENCES node DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE leaf( cellid PRIMARY KEY, parent REFERENCES node DEFERRABLE INITIALLY DEFERRED ); } fkey2-2-test 1 0 "INSERT INTO node VALUES(1, 0)" FKV fkey2-2-test 2 0 "BEGIN" fkey2-2-test 3 1 "INSERT INTO node VALUES(1, 0)" fkey2-2-test 4 0 "UPDATE node SET parent = NULL" fkey2-2-test 5 0 "COMMIT" fkey2-2-test 6 0 "SELECT * FROM node" {1 {}} fkey2-2-test 7 0 "BEGIN" fkey2-2-test 8 1 "INSERT INTO leaf VALUES('a', 2)" fkey2-2-test 9 1 "INSERT INTO node VALUES(2, 0)" fkey2-2-test 10 0 "UPDATE node SET parent = 1 WHERE nodeid = 2" fkey2-2-test 11 0 "COMMIT" fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1} fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2} fkey2-2-test 14 0 "BEGIN" fkey2-2-test 15 1 "DELETE FROM node WHERE nodeid = 2" fkey2-2-test 16 0 "INSERT INTO node VALUES(2, NULL)" fkey2-2-test 17 0 "COMMIT" fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}} fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2} fkey2-2-test 20 0 "BEGIN" fkey2-2-test 21 0 "INSERT INTO leaf VALUES('b', 1)" fkey2-2-test 22 0 "SAVEPOINT save" fkey2-2-test 23 0 "DELETE FROM node WHERE nodeid = 1" fkey2-2-test 24 0 "ROLLBACK TO save" fkey2-2-test 25 0 "COMMIT" fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}} fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1} fkey2-2-test 28 0 "BEGIN" fkey2-2-test 29 0 "INSERT INTO leaf VALUES('c', 1)" fkey2-2-test 30 0 "SAVEPOINT save" fkey2-2-test 31 0 "DELETE FROM node WHERE nodeid = 1" fkey2-2-test 32 1 "RELEASE save" fkey2-2-test 33 1 "DELETE FROM leaf WHERE cellid = 'b'" fkey2-2-test 34 0 "DELETE FROM leaf WHERE cellid = 'c'" fkey2-2-test 35 0 "COMMIT" fkey2-2-test 36 0 "SELECT * FROM node" {2 {}} fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2} fkey2-2-test 38 0 "SAVEPOINT outer" fkey2-2-test 39 1 "INSERT INTO leaf VALUES('d', 3)" fkey2-2-test 40 1 "RELEASE outer" FKV fkey2-2-test 41 1 "INSERT INTO leaf VALUES('e', 3)" fkey2-2-test 42 0 "INSERT INTO node VALUES(3, 2)" fkey2-2-test 43 0 "RELEASE outer" fkey2-2-test 44 0 "SAVEPOINT outer" fkey2-2-test 45 1 "DELETE FROM node WHERE nodeid=3" fkey2-2-test 47 0 "INSERT INTO node VALUES(3, 2)" fkey2-2-test 48 0 "ROLLBACK TO outer" fkey2-2-test 49 0 "RELEASE outer" fkey2-2-test 50 0 "SAVEPOINT outer" fkey2-2-test 51 1 "INSERT INTO leaf VALUES('f', 4)" fkey2-2-test 52 1 "SAVEPOINT inner" fkey2-2-test 53 1 "INSERT INTO leaf VALUES('g', 4)" fkey2-2-test 54 1 "RELEASE outer" FKV fkey2-2-test 55 1 "ROLLBACK TO inner" fkey2-2-test 56 0 "COMMIT" FKV fkey2-2-test 57 0 "INSERT INTO node VALUES(4, NULL)" fkey2-2-test 58 0 "RELEASE outer" fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}} fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4} # The following set of tests check that if a statement that affects # multiple rows violates some foreign key constraints, then strikes a # constraint that causes the statement-transaction to be rolled back, # the deferred constraint counter is correctly reset to the value it # had before the statement-transaction was opened. # fkey2-2-test 61 0 "BEGIN" fkey2-2-test 62 0 "DELETE FROM leaf" fkey2-2-test 63 0 "DELETE FROM node" fkey2-2-test 64 1 "INSERT INTO leaf VALUES('a', 1)" fkey2-2-test 65 1 "INSERT INTO leaf VALUES('b', 2)" fkey2-2-test 66 1 "INSERT INTO leaf VALUES('c', 1)" do_test fkey2-2-test-67 { catchsql "INSERT INTO node SELECT parent, 3 FROM leaf" } {1 {column nodeid is not unique}} fkey2-2-test 68 0 "COMMIT" FKV fkey2-2-test 69 1 "INSERT INTO node VALUES(1, NULL)" fkey2-2-test 70 0 "INSERT INTO node VALUES(2, NULL)" fkey2-2-test 71 0 "COMMIT" fkey2-2-test 72 0 "BEGIN" fkey2-2-test 73 1 "DELETE FROM node" fkey2-2-test 74 0 "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf" fkey2-2-test 75 0 "COMMIT" #------------------------------------------------------------------------- # Test cases fkey2-3.* test that a program that executes foreign key # actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints # opens a statement transaction if required. # # fkey2-3.1.*: Test UPDATE statements. # fkey2-3.2.*: Test DELETE statements. # drop_all_tables do_test fkey2-3.1.1 { execsql { CREATE TABLE ab(a PRIMARY KEY, b); CREATE TABLE cd( c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE, d ); CREATE TABLE ef( e REFERENCES cd ON UPDATE CASCADE, f, CHECK (e!=5) ); } } {} do_test fkey2-3.1.2 { execsql { INSERT INTO ab VALUES(1, 'b'); INSERT INTO cd VALUES(1, 'd'); INSERT INTO ef VALUES(1, 'e'); } } {} do_test fkey2-3.1.3 { catchsql { UPDATE ab SET a = 5 } } {1 {constraint failed}} do_test fkey2-3.1.4 { execsql { SELECT * FROM ab } } {1 b} do_test fkey2-3.1.4 { execsql BEGIN; catchsql { UPDATE ab SET a = 5 } } {1 {constraint failed}} do_test fkey2-3.1.5 { execsql COMMIT; execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef } } {1 b 1 d 1 e} do_test fkey2-3.2.1 { execsql BEGIN; catchsql { DELETE FROM ab } } {1 {foreign key constraint failed}} do_test fkey2-3.2.2 { execsql COMMIT execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef } } {1 b 1 d 1 e} #------------------------------------------------------------------------- # Test cases fkey2-4.* test that recursive foreign key actions # (i.e. CASCADE) are allowed even if recursive triggers are disabled. # drop_all_tables do_test fkey2-4.1 { execsql { CREATE TABLE t1( node PRIMARY KEY, parent REFERENCES t1 ON DELETE CASCADE ); CREATE TABLE t2(node PRIMARY KEY, parent); CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN DELETE FROM t2 WHERE parent = old.node; END; INSERT INTO t1 VALUES(1, NULL); INSERT INTO t1 VALUES(2, 1); INSERT INTO t1 VALUES(3, 1); INSERT INTO t1 VALUES(4, 2); INSERT INTO t1 VALUES(5, 2); INSERT INTO t1 VALUES(6, 3); INSERT INTO t1 VALUES(7, 3); INSERT INTO t2 SELECT * FROM t1; } } {} do_test fkey2-4.2 { execsql { PRAGMA recursive_triggers = off } execsql { BEGIN; DELETE FROM t1 WHERE node = 1; SELECT node FROM t1; } } {} do_test fkey2-4.3 { execsql { DELETE FROM t2 WHERE node = 1; SELECT node FROM t2; ROLLBACK; } } {4 5 6 7} do_test fkey2-4.4 { execsql { PRAGMA recursive_triggers = on } execsql { BEGIN; DELETE FROM t1 WHERE node = 1; SELECT node FROM t1; } } {} do_test fkey2-4.3 { execsql { DELETE FROM t2 WHERE node = 1; SELECT node FROM t2; ROLLBACK; } } {} #------------------------------------------------------------------------- # Test cases fkey2-5.* verify that the incremental blob API may not # write to a foreign key column while foreign-keys are enabled. # drop_all_tables do_test fkey2-5.1 { execsql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a)); INSERT INTO t1 VALUES('hello', 'world'); INSERT INTO t2 VALUES('key', 'hello'); } } {} do_test fkey2-5.2 { set rc [catch { set fd [db incrblob t2 b 1] } msg] list $rc $msg } {1 {cannot open foreign key column for writing}} do_test fkey2-5.3 { set rc [catch { set fd [db incrblob -readonly t2 b 1] } msg] close $fd set rc } {0} do_test fkey2-5.4 { execsql { PRAGMA foreign_keys = off } set rc [catch { set fd [db incrblob t2 b 1] } msg] close $fd set rc } {0} do_test fkey2-5.5 { execsql { PRAGMA foreign_keys = on } } {} #------------------------------------------------------------------------- # The following block of tests, those prefixed with "fkey2-genfkey.", are # the same tests that were used to test the ".genfkey" command provided # by the shell tool. So these tests show that the built-in foreign key # implementation is more or less compatible with the triggers generated # by genfkey. # drop_all_tables do_test fkey2-genfkey.1.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); CREATE TABLE t2(e REFERENCES t1, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); } } {} do_test fkey2-genfkey.1.2 { catchsql { INSERT INTO t2 VALUES(1, 2) } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.3 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t2 VALUES(1, 2); } } {} do_test fkey2-genfkey.1.4 { execsql { INSERT INTO t2 VALUES(NULL, 3) } } {} do_test fkey2-genfkey.1.5 { catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.6 { execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } } {} do_test fkey2-genfkey.1.7 { execsql { UPDATE t2 SET e = NULL WHERE f = 3 } } {} do_test fkey2-genfkey.1.8 { catchsql { UPDATE t1 SET a = 10 } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.9 { catchsql { UPDATE t1 SET a = NULL } } {1 {datatype mismatch}} do_test fkey2-genfkey.1.10 { catchsql { DELETE FROM t1 } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.11 { execsql { UPDATE t2 SET e = NULL } } {} do_test fkey2-genfkey.1.12 { execsql { UPDATE t1 SET a = 10; DELETE FROM t1; DELETE FROM t2; } } {} do_test fkey2-genfkey.1.13 { execsql { INSERT INTO t3 VALUES(1, NULL, NULL); INSERT INTO t3 VALUES(1, 2, NULL); INSERT INTO t3 VALUES(1, NULL, 3); } } {} do_test fkey2-genfkey.1.14 { catchsql { INSERT INTO t3 VALUES(3, 1, 4) } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.15 { execsql { INSERT INTO t1 VALUES(1, 1, 4); INSERT INTO t3 VALUES(3, 1, 4); } } {} do_test fkey2-genfkey.1.16 { catchsql { DELETE FROM t1 } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.17 { catchsql { UPDATE t1 SET b = 10} } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.18 { execsql { UPDATE t1 SET a = 10} } {} do_test fkey2-genfkey.1.19 { catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} } {1 {foreign key constraint failed}} drop_all_tables do_test fkey2-genfkey.2.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE ); } } {} do_test fkey2-genfkey.2.2 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(4, 'four'); } } {} do_test fkey2-genfkey.2.3 { execsql { UPDATE t1 SET a = 2 WHERE a = 1; SELECT * FROM t2; } } {2 one 4 four} do_test fkey2-genfkey.2.4 { execsql { DELETE FROM t1 WHERE a = 4; SELECT * FROM t2; } } {2 one} do_test fkey2-genfkey.2.5 { execsql { INSERT INTO t3 VALUES('hello', 2, 3); UPDATE t1 SET c = 2; SELECT * FROM t3; } } {hello 2 2} do_test fkey2-genfkey.2.6 { execsql { DELETE FROM t1; SELECT * FROM t3; } } {} drop_all_tables do_test fkey2-genfkey.3.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL ); } } {} do_test fkey2-genfkey.3.2 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(4, 'four'); } } {} do_test fkey2-genfkey.3.3 { execsql { UPDATE t1 SET a = 2 WHERE a = 1; SELECT * FROM t2; } } {{} one 4 four} do_test fkey2-genfkey.3.4 { execsql { DELETE FROM t1 WHERE a = 4; SELECT * FROM t2; } } {{} one {} four} do_test fkey2-genfkey.3.5 { execsql { INSERT INTO t3 VALUES('hello', 2, 3); UPDATE t1 SET c = 2; SELECT * FROM t3; } } {hello {} {}} do_test fkey2-genfkey.3.6 { execsql { UPDATE t3 SET h = 2, i = 2; DELETE FROM t1; SELECT * FROM t3; } } {hello {} {}} finish_test |
Changes to test/pragma.test.
︙ | ︙ | |||
529 530 531 532 533 534 535 | db nullvalue {} ifcapable {foreignkey} { do_test pragma-6.3.1 { execsql { CREATE TABLE t3(a int references t2(b), b UNIQUE); pragma foreign_key_list(t3); } | | | 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 | db nullvalue {} ifcapable {foreignkey} { do_test pragma-6.3.1 { execsql { CREATE TABLE t3(a int references t2(b), b UNIQUE); pragma foreign_key_list(t3); } } {0 0 t2 a b {NO ACTION} {NO ACTION} NONE} do_test pragma-6.3.2 { execsql { pragma foreign_key_list; } } {} do_test pragma-6.3.3 { execsql { |
︙ | ︙ |
test/progress.test became executable.
︙ | ︙ |
Changes to test/trigger3.test.
︙ | ︙ | |||
24 25 26 27 28 29 30 | catchsql { pragma recursive_triggers = off } # Test that we can cause ROLLBACK, FAIL and ABORT correctly # catchsql { CREATE TABLE tbl(a, b ,c) } execsql { CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 | catchsql { pragma recursive_triggers = off } # Test that we can cause ROLLBACK, FAIL and ABORT correctly # catchsql { CREATE TABLE tbl(a, b ,c) } execsql { CREATE TRIGGER before_tbl_insert BEFORE INSERT ON tbl BEGIN SELECT CASE WHEN (new.a = 4) THEN RAISE(IGNORE) END; END; CREATE TRIGGER after_tbl_insert AFTER INSERT ON tbl BEGIN SELECT CASE WHEN (new.a = 1) THEN RAISE(ABORT, 'Trigger abort') WHEN (new.a = 2) THEN RAISE(FAIL, 'Trigger fail') WHEN (new.a = 3) THEN RAISE(ROLLBACK, 'Trigger rollback') END; END; } # ABORT do_test trigger3-1.1 { catchsql { BEGIN; INSERT INTO tbl VALUES (5, 5, 6); INSERT INTO tbl VALUES (1, 5, 6); } } {1 {Trigger abort}} do_test trigger3-1.2 { execsql { SELECT * FROM tbl; ROLLBACK; } } {5 5 6} do_test trigger3-1.3 { execsql {SELECT * FROM tbl} } {} # FAIL do_test trigger3-2.1 { catchsql { BEGIN; INSERT INTO tbl VALUES (5, 5, 6); INSERT INTO tbl VALUES (2, 5, 6); } } {1 {Trigger fail}} do_test trigger3-2.2 { execsql { SELECT * FROM tbl; ROLLBACK; } } {5 5 6 2 5 6} # ROLLBACK do_test trigger3-3.1 { catchsql { BEGIN; INSERT INTO tbl VALUES (5, 5, 6); INSERT INTO tbl VALUES (3, 5, 6); } } {1 {Trigger rollback}} do_test trigger3-3.2 { execsql { SELECT * FROM tbl; } } {} # Verify that a ROLLBACK trigger works like a FAIL trigger if # we are not within a transaction. Ticket #3035. # do_test trigger3-3.3 { catchsql {COMMIT} catchsql { INSERT INTO tbl VALUES (3, 9, 10); } } {1 {Trigger rollback}} do_test trigger3-3.4 { execsql {SELECT * FROM tbl} } {} # IGNORE do_test trigger3-4.1 { catchsql { BEGIN; INSERT INTO tbl VALUES (5, 5, 6); INSERT INTO tbl VALUES (4, 5, 6); } } {0 {}} do_test trigger3-4.2 { execsql { SELECT * FROM tbl; ROLLBACK; } } {5 5 6} # Check that we can also do RAISE(IGNORE) for UPDATE and DELETE execsql {DROP TABLE tbl;} execsql {CREATE TABLE tbl (a, b, c);} execsql {INSERT INTO tbl VALUES(1, 2, 3);} execsql {INSERT INTO tbl VALUES(4, 5, 6);} execsql { CREATE TRIGGER before_tbl_update BEFORE UPDATE ON tbl BEGIN SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; END; CREATE TRIGGER before_tbl_delete BEFORE DELETE ON tbl BEGIN SELECT CASE WHEN (old.a = 1) THEN RAISE(IGNORE) END; END; } do_test trigger3-5.1 { execsql { UPDATE tbl SET c = 10; SELECT * FROM tbl; } } {1 2 3 4 5 10} do_test trigger3-5.2 { execsql { DELETE FROM tbl; SELECT * FROM tbl; } } {1 2 3} # Check that RAISE(IGNORE) works correctly for nested triggers: execsql {CREATE TABLE tbl2(a, b, c)} execsql { CREATE TRIGGER after_tbl2_insert AFTER INSERT ON tbl2 BEGIN UPDATE tbl SET c = 10; INSERT INTO tbl2 VALUES (new.a, new.b, new.c); END; } do_test trigger3-6 { execsql { INSERT INTO tbl2 VALUES (1, 2, 3); SELECT * FROM tbl2; SELECT * FROM tbl; } } {1 2 3 1 2 3 1 2 3} # Check that things also work for view-triggers ifcapable view { execsql {CREATE VIEW tbl_view AS SELECT * FROM tbl} execsql { CREATE TRIGGER tbl_view_insert INSTEAD OF INSERT ON tbl_view BEGIN SELECT CASE WHEN (new.a = 1) THEN RAISE(ROLLBACK, 'View rollback') WHEN (new.a = 2) THEN RAISE(IGNORE) WHEN (new.a = 3) THEN RAISE(ABORT, 'View abort') END; END; } do_test trigger3-7.1 { catchsql { INSERT INTO tbl_view VALUES(1, 2, 3); } } {1 {View rollback}} do_test trigger3-7.2 { catchsql { INSERT INTO tbl_view VALUES(2, 2, 3); } } {0 {}} do_test trigger3-7.3 { catchsql { INSERT INTO tbl_view VALUES(3, 2, 3); } } {1 {View abort}} } ;# ifcapable view integrity_check trigger3-8.1 catchsql { DROP TABLE tbl; } catchsql { DROP TABLE tbl2; } catchsql { DROP VIEW tbl_view; } finish_test |
tool/mkopts.tcl became executable.
︙ | ︙ |