Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | If recursive-triggers are enabled, fire DELETE triggers if database rows are removed as a result of OR REPLACE conflict resolution. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
85cb0c94a63eda5f059ebe40887c7af9 |
User & Date: | dan 2009-09-08 15:55:16.000 |
Context
2009-09-08
| ||
19:15 | Combine the OP_Statement and OP_Transaction opcodes. (check-in: aec9dbd8d2 user: dan tags: trunk) | |
15:55 | If recursive-triggers are enabled, fire DELETE triggers if database rows are removed as a result of OR REPLACE conflict resolution. (check-in: 85cb0c94a6 user: dan tags: trunk) | |
13:40 | Additional simplifications in support of structural testing. (check-in: 4ab8c841f8 user: drh tags: trunk) | |
Changes
Changes to src/delete.c.
︙ | ︙ | |||
334 335 336 337 338 339 340 | if( db->flags & SQLITE_CountRows ){ memCnt = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt); } #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special case: A DELETE without a WHERE clause deletes everything. | | | | < | 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 | if( db->flags & SQLITE_CountRows ){ memCnt = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt); } #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) ){ 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); } }else #endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */ /* The usual case: There is a WHERE clause so we have to scan through ** the table and pick which records to delete. */ { int iRowSet = ++pParse->nMem; /* Register for rowset of rows to delete */ int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ int regRowid; /* Actual register containing rowids */ /* Collect rowids of every row to be deleted. */ sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere,0,WHERE_DUPLICATES_OK); if( pWInfo==0 ) goto delete_from_cleanup; |
︙ | ︙ | |||
383 384 385 386 387 388 389 | ** triggers. */ if( !isView ){ sqlite3OpenTableAndIndices(pParse, pTab, iCur, OP_OpenWrite); } addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, end, iRowid); | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | | | | | | | > | | < < < < < < | 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 | ** triggers. */ if( !isView ){ sqlite3OpenTableAndIndices(pParse, pTab, iCur, OP_OpenWrite); } addr = sqlite3VdbeAddOp3(v, OP_RowSetRead, iRowSet, end, iRowid); /* Delete the row */ #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTab) ){ const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); sqlite3VtabMakeWritable(pParse, pTab); sqlite3VdbeAddOp4(v, OP_VUpdate, 0, 1, iRowid, pVTab, P4_VTAB); }else #endif { int count = (pParse->nested==0); /* True to count changes */ sqlite3GenerateRowDelete(pParse, pTab, iCur, iRowid, count, pTrigger, OE_Default); } /* End of the delete loop */ sqlite3VdbeAddOp2(v, OP_Goto, 0, addr); sqlite3VdbeResolveLabel(v, end); /* Close the cursors open on the table and its indexes. */ if( !isView && !IsVirtual(pTab) ){ |
︙ | ︙ | |||
454 455 456 457 458 459 460 | ** maximum rowid counter values recorded while inserting into ** autoincrement tables. */ if( pParse->nested==0 && pParse->pTriggerTab==0 ){ sqlite3AutoincrementEnd(pParse); } | < | | 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 | ** maximum rowid counter values recorded while inserting into ** autoincrement tables. */ if( pParse->nested==0 && pParse->pTriggerTab==0 ){ sqlite3AutoincrementEnd(pParse); } /* Return the number of rows that were deleted. If this routine is ** generating code because of a call to sqlite3NestedParse(), do not ** invoke the callback function. */ if( (db->flags&SQLITE_CountRows) && !pParse->nested && !pParse->pTriggerTab ){ sqlite3VdbeAddOp2(v, OP_ResultRow, memCnt, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC); |
︙ | ︙ | |||
488 489 490 491 492 493 494 | ** ** 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. ** | < | | | > > > > > | > > | > > > > > | > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > | > > > > > > | | | | | > > > > > > > > > > > > | | 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 | ** ** 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. ** ** This routine generates code to remove both the table record and all ** index entries that point to that record. */ void sqlite3GenerateRowDelete( Parse *pParse, /* Parsing context */ Table *pTab, /* Table containing the row to be deleted */ int iCur, /* Cursor number for the table */ int iRowid, /* Memory cell that contains the rowid to delete */ int count, /* If non-zero, increment the row change counter */ Trigger *pTrigger, /* List of triggers to (potentially) fire */ int onconf /* Default ON CONFLICT policy for triggers */ ){ Vdbe *v = pParse->pVdbe; /* Vdbe */ int iOld; /* First register in OLD.* array */ int iLabel; /* Label resolved to end of generated code */ /* Vdbe is guaranteed to have been allocated by this stage. */ assert( v ); /* Seek cursor iCur to the row to delete. If this row no longer exists ** (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( 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, TK_DELETE, 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( mask==0xffffffff || mask&(1<<iCol) ){ int iTarget = iOld + iCol + 1; sqlite3VdbeAddOp3(v, OP_Column, iCur, iCol, iTarget); sqlite3ColumnDefault(v, pTab, iCol, iTarget); } } /* Invoke any BEFORE 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); } /* 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); } } /* Invoke AFTER triggers. */ if( pTrigger ){ 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); } /* ** This routine generates VDBE code that causes the deletion of all ** index entries associated with a single row of a single table. ** ** The VDBE must be in a particular state when this routine is called. |
︙ | ︙ |
Changes to src/insert.c.
︙ | ︙ | |||
1237 1238 1239 1240 1241 1242 1243 | case OE_Abort: case OE_Fail: { sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, onError, 0, "PRIMARY KEY must be unique", P4_STATIC); break; } case OE_Replace: { | > > > > > > > > > > > > > > > > > > | > | 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 | case OE_Abort: case OE_Fail: { sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, onError, 0, "PRIMARY KEY must be unique", P4_STATIC); break; } case OE_Replace: { /* If there are DELETE triggers on this table and the ** recursive-triggers flag is set, call GenerateRowDelete() to ** remove the conflicting row from the the table. This will fire ** the triggers and remove both the table and index b-tree entries. ** ** Otherwise, if there are no triggers or the recursive-triggers ** flag is not set, call GenerateRowIndexDelete(). This removes ** the index b-tree entries only. The table b-tree entry will be ** replaced by the new entry when it is inserted. */ Trigger *pTrigger = 0; if( pParse->db->flags&SQLITE_RecTriggers ){ pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); } if( pTrigger ){ sqlite3GenerateRowDelete( pParse, pTab, baseCur, regRowid, 0, pTrigger, OE_Replace ); }else{ sqlite3GenerateRowIndexDelete(pParse, pTab, baseCur, 0); } seenReplace = 1; break; } case OE_Ignore: { assert( seenReplace==0 ); sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); break; |
︙ | ︙ | |||
1295 1296 1297 1298 1299 1300 1301 | onError = OE_Abort; } if( seenReplace ){ if( onError==OE_Ignore ) onError = OE_Replace; else if( onError==OE_Fail ) onError = OE_Abort; } | < | 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 | onError = OE_Abort; } if( seenReplace ){ if( onError==OE_Ignore ) onError = OE_Replace; else if( onError==OE_Fail ) onError = OE_Abort; } /* Check to see if the new index entry will be unique */ regR = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp2(v, OP_SCopy, regOldRowid, regR); j3 = sqlite3VdbeAddOp4(v, OP_IsUnique, baseCur+iCur+1, 0, regR, SQLITE_INT_TO_PTR(regIdx), P4_INT32); sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1); |
︙ | ︙ | |||
1338 1339 1340 1341 1342 1343 1344 1345 | } case OE_Ignore: { assert( seenReplace==0 ); sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); break; } default: { assert( onError==OE_Replace ); | > > > > | > > | 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 | } case OE_Ignore: { assert( seenReplace==0 ); sqlite3VdbeAddOp2(v, OP_Goto, 0, ignoreDest); break; } default: { Trigger *pTrigger = 0; assert( onError==OE_Replace ); if( pParse->db->flags&SQLITE_RecTriggers ){ pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0); } sqlite3GenerateRowDelete( pParse, pTab, baseCur, regR, 0, pTrigger, OE_Replace ); seenReplace = 1; break; } } sqlite3VdbeJumpHere(v, j3); sqlite3ReleaseTempReg(pParse, regR); } |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2643 2644 2645 2646 2647 2648 2649 | void sqlite3Savepoint(Parse*, int, Token*); void sqlite3CloseSavepoints(sqlite3 *); int sqlite3ExprIsConstant(Expr*); int sqlite3ExprIsConstantNotJoin(Expr*); int sqlite3ExprIsConstantOrFunction(Expr*); int sqlite3ExprIsInteger(Expr*, int*); int sqlite3IsRowid(const char*); | | | 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 | void sqlite3Savepoint(Parse*, int, Token*); void sqlite3CloseSavepoints(sqlite3 *); int sqlite3ExprIsConstant(Expr*); int sqlite3ExprIsConstantNotJoin(Expr*); int sqlite3ExprIsConstantOrFunction(Expr*); int sqlite3ExprIsInteger(Expr*, int*); int sqlite3IsRowid(const char*); void sqlite3GenerateRowDelete(Parse*, Table*, int, int, int, Trigger *, int); void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int*); int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int); void sqlite3GenerateConstraintChecks(Parse*,Table*,int,int, int*,int,int,int,int,int*); void sqlite3CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int); int sqlite3OpenTableAndIndices(Parse*, Table*, int, int); void sqlite3BeginWriteOperation(Parse*, int, int); |
︙ | ︙ |
Changes to test/triggerC.test.
︙ | ︙ | |||
12 13 14 15 16 17 18 19 20 21 22 23 24 25 | set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable {!trigger} { finish_test return } # Enable recursive triggers for this file. # execsql { PRAGMA recursive_triggers = on } #sqlite3_db_config_lookaside db 0 0 0 | > > > > > > > > > > > > > > > > > > | 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 | set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable {!trigger} { finish_test return } #------------------------------------------------------------------------- # Test organization: # # triggerC-1.*: Haphazardly designed trigger related tests that were useful # during an upgrade of the triggers sub-system. # # triggerC-2.*: # # triggerC-3.*: # # triggerC-4.*: # # triggerC-5.*: Test that when recursive triggers are enabled DELETE # triggers are fired when rows are deleted as part of OR # REPLACE conflict resolution. And that they are not fired # if recursive triggers are not enabled. # # Enable recursive triggers for this file. # execsql { PRAGMA recursive_triggers = on } #sqlite3_db_config_lookaside db 0 0 0 |
︙ | ︙ | |||
526 527 528 529 530 531 532 533 534 | eval concat [execsql " DELETE FROM log; $insert ; SELECT * FROM log; "] } [join $log " "] } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | eval concat [execsql " DELETE FROM log; $insert ; SELECT * FROM log; "] } [join $log " "] } #------------------------------------------------------------------------- # This block of tests, triggerC-5.*, test that DELETE triggers are fired # if a row is deleted as a result of OR REPLACE conflict resolution. # do_test triggerC-5.1.0 { execsql { DROP TABLE IF EXISTS t5; CREATE TABLE t5(a INTEGER PRIMARY KEY, b); CREATE UNIQUE INDEX t5i ON t5(b); INSERT INTO t5 VALUES(1, 'a'); INSERT INTO t5 VALUES(2, 'b'); INSERT INTO t5 VALUES(3, 'c'); CREATE TABLE t5g(a, b, c); CREATE TRIGGER t5t BEFORE DELETE ON t5 BEGIN INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5)); END; } } {} foreach {n dml t5g t5} { 1 "DELETE FROM t5 WHERE a=2" {2 b 3} {1 a 3 c} 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {2 b 3} {1 a 2 d 3 c} 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {2 b 3} {1 a 2 c} 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {2 b 3} {1 a 3 c 4 b} 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {2 b 3} {1 a 3 b} 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {2 b 3 3 c 2} {1 a 2 c} 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 3 2 b 2} {1 b} } { do_test triggerC-5.1.$n { execsql " BEGIN; $dml ; SELECT * FROM t5g; SELECT * FROM t5; ROLLBACK; " } [concat $t5g $t5] } do_test triggerC-5.2.0 { execsql { DROP TRIGGER t5t; CREATE TRIGGER t5t AFTER DELETE ON t5 BEGIN INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5)); END; } } {} foreach {n dml t5g t5} { 1 "DELETE FROM t5 WHERE a=2" {2 b 2} {1 a 3 c} 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {2 b 2} {1 a 2 d 3 c} 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {2 b 2} {1 a 2 c} 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {2 b 2} {1 a 3 c 4 b} 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {2 b 2} {1 a 3 b} 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {2 b 2 3 c 1} {1 a 2 c} 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 2 2 b 1} {1 b} } { do_test triggerC-5.2.$n { execsql " BEGIN; $dml ; SELECT * FROM t5g; SELECT * FROM t5; ROLLBACK; " } [concat $t5g $t5] } do_test triggerC-5.3.0 { execsql { PRAGMA recursive_triggers = off } } {} foreach {n dml t5g t5} { 1 "DELETE FROM t5 WHERE a=2" {2 b 2} {1 a 3 c} 2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')" {} {1 a 2 d 3 c} 3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3" {} {1 a 2 c} 4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')" {} {1 a 3 c 4 b} 5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'" {} {1 a 3 b} 6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')" {} {1 a 2 c} 7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {} {1 b} } { do_test triggerC-5.3.$n { execsql " BEGIN; $dml ; SELECT * FROM t5g; SELECT * FROM t5; ROLLBACK; " } [concat $t5g $t5] } do_test triggerC-5.3.8 { execsql { PRAGMA recursive_triggers = on } } {} #------------------------------------------------------------------------- # This block of tests, triggerC-6.*, tests that "PRAGMA recursive_triggers" # statements return the current value of the recursive triggers flag. # do_test triggerC-6.1 { execsql { PRAGMA recursive_triggers } } {1} do_test triggerC-6.2 { execsql { PRAGMA recursive_triggers = off; PRAGMA recursive_triggers; } } {0} do_test triggerC-6.3 { execsql { PRAGMA recursive_triggers = on; PRAGMA recursive_triggers; } } {1} finish_test |