Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance UPSERT so that the UPDATE uses the same set of cursors as the INSERT. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
c37f39d18d41ae5ba6c4561d87cbbf71 |
User & Date: | drh 2018-04-20 13:18:51.643 |
Context
2018-04-20
| ||
15:34 | Add test cases for UPSERT. And a fix for a "REPLACE INTO ... ON CONFLICT" statement where the new row conflicts with both the IPK and the ON CONFLICT indexes. (check-in: d8eb9f8d9b user: dan tags: trunk) | |
13:18 | Enhance UPSERT so that the UPDATE uses the same set of cursors as the INSERT. (check-in: c37f39d18d user: drh tags: trunk) | |
00:40 | Minor simplification of the cursor allocation logic for update. (check-in: fdf71be658 user: drh tags: trunk) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
808 809 810 811 812 813 814 815 816 817 818 819 820 821 | } } #ifndef SQLITE_OMIT_UPSERT if( pUpsert ){ pTabList->a[0].iCursor = iDataCur; pUpsert->pUpsertSrc = pTabList; pUpsert->regData = regData; if( pUpsert->pUpsertTarget ){ sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert); } } #endif | > > | 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 | } } #ifndef SQLITE_OMIT_UPSERT if( pUpsert ){ pTabList->a[0].iCursor = iDataCur; pUpsert->pUpsertSrc = pTabList; pUpsert->regData = regData; pUpsert->iDataCur = iDataCur; pUpsert->iIdxCur = iIdxCur; if( pUpsert->pUpsertTarget ){ sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert); } } #endif |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 | */ struct Upsert { ExprList *pUpsertTarget; /* Optional description of conflicting index */ Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */ Index *pUpsertIdx; /* Constraint that pUpsertTarget identifies */ ExprList *pUpsertSet; /* The SET clause from an ON CONFLICT UPDATE */ Expr *pUpsertWhere; /* WHERE clause for the ON CONFLICT UPDATE */ SrcList *pUpsertSrc; /* Table to be updated */ int regData; /* First register holding array of VALUES */ }; /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** ** nLimit is set to -1 if there is no LIMIT clause. nOffset is set to 0. | > > > > > > | 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 | */ struct Upsert { ExprList *pUpsertTarget; /* Optional description of conflicting index */ Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */ Index *pUpsertIdx; /* Constraint that pUpsertTarget identifies */ ExprList *pUpsertSet; /* The SET clause from an ON CONFLICT UPDATE */ Expr *pUpsertWhere; /* WHERE clause for the ON CONFLICT UPDATE */ /* The fields above comprise the parse tree for the upsert clause. ** The fields below are used to transfer information from the INSERT ** processing down into the UPDATE processing while generating code. ** Upsert owns the memory allocated above, but not the memory below. */ SrcList *pUpsertSrc; /* Table to be updated */ int regData; /* First register holding array of VALUES */ int iDataCur; /* Index of the data cursor */ int iIdxCur; /* Index of the first index cursor */ }; /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** ** nLimit is set to -1 if there is no LIMIT clause. nOffset is set to 0. |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
207 208 209 210 211 212 213 214 215 216 217 218 219 220 | testcase( pPk!=0 && pPk!=pTab->pIndex ); for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){ if( pPk==pIdx ){ iDataCur = pParse->nTab; } pParse->nTab++; } pTabList->a[0].iCursor = iDataCur; /* Allocate space for aXRef[], aRegIdx[], and aToOpen[]. ** Initialize aXRef[] and aToOpen[] to their default values. */ aXRef = sqlite3DbMallocRawNN(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 ); if( aXRef==0 ) goto update_cleanup; | > > > > > | 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 | testcase( pPk!=0 && pPk!=pTab->pIndex ); for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){ if( pPk==pIdx ){ iDataCur = pParse->nTab; } pParse->nTab++; } if( pUpsert ){ iDataCur = pUpsert->iDataCur; iIdxCur = pUpsert->iIdxCur; pParse->nTab = iBaseCur; } pTabList->a[0].iCursor = iDataCur; /* Allocate space for aXRef[], aRegIdx[], and aToOpen[]. ** Initialize aXRef[] and aToOpen[] to their default values. */ aXRef = sqlite3DbMallocRawNN(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 ); if( aXRef==0 ) goto update_cleanup; |
︙ | ︙ | |||
472 473 474 475 476 477 478 | } if( eOnePass!=ONEPASS_MULTI ){ sqlite3WhereEnd(pWInfo); } labelBreak = sqlite3VdbeMakeLabel(v); | | | 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 | } if( eOnePass!=ONEPASS_MULTI ){ sqlite3WhereEnd(pWInfo); } labelBreak = sqlite3VdbeMakeLabel(v); if( !isView && pUpsert==0 ){ int addrOnce = 0; /* Open every index that needs updating. */ if( eOnePass!=ONEPASS_OFF ){ if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0; if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0; } |
︙ | ︙ |
Changes to src/upsert.c.
︙ | ︙ | |||
218 219 220 221 222 223 224 | sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey); }else{ sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey); } pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0); if( pE1 ){ pE1->pTab = pTab; | | < < < < < | | 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 | sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey); }else{ sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey); } pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0); if( pE1 ){ pE1->pTab = pTab; pE1->iTable = pUpsert->iDataCur; pE1->iColumn = -1; } pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0); if( pE2 ){ pE2->iTable = regKey; pE2->affinity = SQLITE_AFF_INTEGER; } pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2)); }else{ /* a WITHOUT ROWID table */ int i, j; for(i=0; i<pIdx->nKeyCol; i++){ regKey = ++pParse->nMem; sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regKey); j = pIdx->aiColumn[i]; VdbeComment((v, "%s", pTab->aCol[j].zName)); pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0); if( pE1 ){ pE1->pTab = pTab; pE1->iTable = pUpsert->iDataCur; pE1->iColumn = j; } pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0); if( pE2 ){ pE2->iTable = regKey; pE2->affinity = pTab->zColAff[j]; } |
︙ | ︙ |