Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the handling of "PRAGMA count_changes=ON" with UPSERT. Also improved the implementation of count_changes in other places, without changing the behavior. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
c6f71115eb933c2aee295bc31e513911 |
User & Date: | drh 2018-04-19 23:52:39.113 |
Context
2018-04-20
| ||
00:40 | Minor simplification of the cursor allocation logic for update. (check-in: fdf71be658 user: drh tags: trunk) | |
2018-04-19
| ||
23:52 | Fix the handling of "PRAGMA count_changes=ON" with UPSERT. Also improved the implementation of count_changes in other places, without changing the behavior. (check-in: c6f71115eb user: drh tags: trunk) | |
21:29 | Minor simplification to the upsert logic. (check-in: e657c1d60f user: drh tags: trunk) | |
Changes
Changes to src/delete.c.
︙ | ︙ | |||
234 235 236 237 238 239 240 | int iDataCur = 0; /* VDBE cursor for the canonical data source */ int iIdxCur = 0; /* Cursor number of the first index */ int nIdx; /* Number of indices */ sqlite3 *db; /* Main database structure */ AuthContext sContext; /* Authorization context */ NameContext sNC; /* Name context to resolve expressions in */ int iDb; /* Database number */ | | | 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 | int iDataCur = 0; /* VDBE cursor for the canonical data source */ int iIdxCur = 0; /* Cursor number of the first index */ int nIdx; /* Number of indices */ sqlite3 *db; /* Main database structure */ AuthContext sContext; /* Authorization context */ NameContext sNC; /* Name context to resolve expressions in */ int iDb; /* Database number */ int memCnt = 0; /* Memory cell used for change counting */ int rcauth; /* Value returned by authorization callback */ int eOnePass; /* ONEPASS_OFF or _SINGLE or _MULTI */ int aiCurOnePass[2]; /* The write cursors opened by WHERE_ONEPASS */ u8 *aToOpen = 0; /* Open cursor iTabCur+j if aToOpen[j] is true */ Index *pPk; /* The PRIMARY KEY index on the table */ int iPk = 0; /* First of nPk registers holding PRIMARY KEY value */ i16 nPk = 1; /* Number of columns in the PRIMARY KEY */ |
︙ | ︙ | |||
367 368 369 370 371 372 373 | if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto delete_from_cleanup; } /* Initialize the counter of the number of rows deleted, if ** we are counting rows. */ | | > > > | 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 | if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto delete_from_cleanup; } /* Initialize the counter of the number of rows deleted, if ** we are counting rows. */ if( (db->flags & SQLITE_CountRows)!=0 && !pParse->nested && !pParse->pTriggerTab ){ 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, |
︙ | ︙ | |||
395 396 397 398 399 400 401 | #ifdef SQLITE_ENABLE_PREUPDATE_HOOK && db->xPreUpdateCallback==0 #endif ){ assert( !isView ); sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName); if( HasRowid(pTab) ){ | | | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 | #ifdef SQLITE_ENABLE_PREUPDATE_HOOK && db->xPreUpdateCallback==0 #endif ){ assert( !isView ); sqlite3TableLock(pParse, iDb, pTab->tnum, 1, pTab->zName); if( HasRowid(pTab) ){ sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt ? memCnt : -1, 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 |
︙ | ︙ | |||
442 443 444 445 446 447 448 | pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1); if( pWInfo==0 ) goto delete_from_cleanup; eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI ); assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF ); /* Keep track of the number of rows to be deleted */ | | | 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 | pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, wcf, iTabCur+1); if( pWInfo==0 ) goto delete_from_cleanup; eOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass); assert( IsVirtual(pTab)==0 || eOnePass!=ONEPASS_MULTI ); assert( IsVirtual(pTab) || bComplex || eOnePass!=ONEPASS_OFF ); /* Keep track of the number of rows to be deleted */ if( memCnt ){ sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); } /* Extract the rowid or primary key for the current row */ if( pPk ){ for(i=0; i<nPk; i++){ assert( pPk->aiColumn[i]>=0 ); |
︙ | ︙ | |||
585 586 587 588 589 590 591 | 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. */ | | | 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 | 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( memCnt ){ sqlite3VdbeAddOp2(v, OP_ResultRow, memCnt, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC); } delete_from_cleanup: sqlite3AuthContextPop(&sContext); |
︙ | ︙ |
Changes to src/insert.c.
︙ | ︙ | |||
780 781 782 783 784 785 786 | if( pColumn!=0 && nColumn!=pColumn->nId ){ sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId); goto insert_cleanup; } /* Initialize the count of rows to be inserted */ | | > > > | 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 | if( pColumn!=0 && nColumn!=pColumn->nId ){ sqlite3ErrorMsg(pParse, "%d values for %d columns", nColumn, pColumn->nId); goto insert_cleanup; } /* Initialize the count of rows to be inserted */ if( (db->flags & SQLITE_CountRows)!=0 && !pParse->nested && !pParse->pTriggerTab ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } /* If this is not a view, open the table and and all indices */ if( !isView ){ int nIdx; |
︙ | ︙ | |||
1036 1037 1038 1039 1040 1041 1042 | regIns, aRegIdx, 0, appendFlag, bUseSeek ); } } /* Update the count of rows that are inserted */ | | | 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 | regIns, aRegIdx, 0, appendFlag, bUseSeek ); } } /* Update the count of rows that are inserted */ if( regRowCount ){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } if( pTrigger ){ /* Code AFTER triggers */ sqlite3CodeRowTrigger(pParse, pTrigger, TK_INSERT, 0, TRIGGER_AFTER, pTab, regData-2-pTab->nCol, onError, endOfLoop); |
︙ | ︙ | |||
1073 1074 1075 1076 1077 1078 1079 | } /* ** Return the number of rows inserted. If this routine is ** generating code because of a call to sqlite3NestedParse(), do not ** invoke the callback function. */ | | | 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 | } /* ** Return the number of rows inserted. If this routine is ** generating code because of a call to sqlite3NestedParse(), do not ** invoke the callback function. */ if( regRowCount ){ sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC); } insert_cleanup: sqlite3SrcListDelete(db, pTabList); |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
380 381 382 383 384 385 386 | updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere, onError); goto update_cleanup; } #endif /* Initialize the count of updated rows */ | > | > > > | 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 | updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef, pWhere, onError); goto update_cleanup; } #endif /* Initialize the count of updated rows */ if( (db->flags&SQLITE_CountRows)!=0 && !pParse->pTriggerTab && !pParse->nested && !pUpsert ){ regRowCount = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount); } if( HasRowid(pTab) ){ sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid); }else{ |
︙ | ︙ | |||
695 696 697 698 699 700 701 | if( hasFK ){ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey); } } /* Increment the row counter */ | | | 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 | if( hasFK ){ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey); } } /* Increment the row counter */ if( regRowCount ){ sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1); } sqlite3CodeRowTrigger(pParse, pTrigger, TK_UPDATE, pChanges, TRIGGER_AFTER, pTab, regOldRowid, onError, labelContinue); /* Repeat the above with the next record to be updated, until |
︙ | ︙ | |||
727 728 729 730 731 732 733 | ** autoincrement tables. */ if( pParse->nested==0 && pParse->pTriggerTab==0 ){ sqlite3AutoincrementEnd(pParse); } /* | | < | | | 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 | ** autoincrement tables. */ if( pParse->nested==0 && pParse->pTriggerTab==0 ){ sqlite3AutoincrementEnd(pParse); } /* ** Return the number of rows that were changed, if we are tracking ** that information. */ if( regRowCount ){ sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1); sqlite3VdbeSetNumCols(v, 1); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC); } update_cleanup: sqlite3AuthContextPop(&sContext); |
︙ | ︙ |
Changes to test/upsert1.test.
︙ | ︙ | |||
84 85 86 87 88 89 90 91 92 | } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} do_execsql_test upsert1-320 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20) ON CONFLICT(b) WHERE b>10 DO NOTHING; SELECT *, 'x' FROM t1 ORDER BY b, a; } {1 2 0 x 3 2 0 x 4 20 0 x} finish_test | > > > > > > > > > > > > > > > | 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} do_execsql_test upsert1-320 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20) ON CONFLICT(b) WHERE b>10 DO NOTHING; SELECT *, 'x' FROM t1 ORDER BY b, a; } {1 2 0 x 3 2 0 x 4 20 0 x} # Upsert works with count_changes=on; do_execsql_test upsert1-400 { DROP TABLE IF EXISTS t2; CREATE TABLE t2(a TEXT UNIQUE, b INT DEFAULT 1); INSERT INTO t2(a) VALUES('one'),('two'),('three'); PRAGMA count_changes=ON; INSERT INTO t2(a) VALUES('one'),('one'),('three'),('four') ON CONFLICT(a) DO UPDATE SET b=b+1; } {1} do_execsql_test upsert1-410 { PRAGMA count_changes=OFF; SELECT a, b FROM t2 ORDER BY a; } {four 1 one 3 three 2 two 1} finish_test |