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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
c6f71115eb933c2aee295bc31e513911 |
| User & Date: | drh 2018-04-19 23:52:39 |
Context
|
2018-04-20
| ||
| 00:40 | Minor simplification of the cursor allocation logic for update. check-in: fdf71be6 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: c6f71115 user: drh tags: trunk | |
| 21:29 | Minor simplification to the upsert logic. check-in: e657c1d6 user: drh tags: trunk | |
Changes
Changes to src/delete.c.
234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 ... 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 ... 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 ... 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 ... 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 |
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 = -1; /* 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 */ ................................................................................ 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 ){ 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, ................................................................................ #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, 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 ................................................................................ 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( db->flags & SQLITE_CountRows ){ 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 ); ................................................................................ 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); } delete_from_cleanup: sqlite3AuthContextPop(&sContext); |
| | > > > | | | |
234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 ... 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 ... 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 ... 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 ... 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 |
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 */ ................................................................................ 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, ................................................................................ #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 ................................................................................ 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 ); ................................................................................ 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 787 788 789 790 791 792 793 794 .... 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 .... 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 |
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 ){
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;
................................................................................
regIns, aRegIdx, 0, appendFlag, bUseSeek
);
}
}
/* Update the count of rows that are inserted
*/
if( (db->flags & SQLITE_CountRows)!=0 ){
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);
................................................................................
}
/*
** 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( (db->flags&SQLITE_CountRows) && !pParse->nested && !pParse->pTriggerTab ){
sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
sqlite3VdbeSetNumCols(v, 1);
sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
}
insert_cleanup:
sqlite3SrcListDelete(db, pTabList);
|
| > > > | | |
780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 .... 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 .... 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 |
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;
................................................................................
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);
................................................................................
}
/*
** 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 387 388 389 390 391 392 393 394 ... 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 ... 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 |
updateVirtualTable(pParse, pTabList, pTab, pChanges, pRowidExpr, aXRef,
pWhere, onError);
goto update_cleanup;
}
#endif
/* Initialize the count of updated rows */
if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
regRowCount = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowCount);
}
if( HasRowid(pTab) ){
sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
}else{
................................................................................
if( hasFK ){
sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngKey);
}
}
/* Increment the row counter
*/
if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
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
................................................................................
** autoincrement tables.
*/
if( pParse->nested==0 && pParse->pTriggerTab==0 ){
sqlite3AutoincrementEnd(pParse);
}
/*
** Return the number of rows that were changed. If this routine is
** generating code because of a call to sqlite3NestedParse(), do not
** invoke the callback function.
*/
if( (db->flags&SQLITE_CountRows) && !pParse->pTriggerTab && !pParse->nested ){
sqlite3VdbeAddOp2(v, OP_ResultRow, regRowCount, 1);
sqlite3VdbeSetNumCols(v, 1);
sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows updated", SQLITE_STATIC);
}
update_cleanup:
sqlite3AuthContextPop(&sContext);
|
> | > > > | | | < < > |
380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 ... 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 ... 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 |
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{
................................................................................
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
................................................................................
** 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
|