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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
c37f39d18d41ae5ba6c4561d87cbbf71 |
| User & Date: | drh 2018-04-20 13:18:51 |
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: d8eb9f8d user: dan tags: trunk | |
| 13:18 | Enhance UPSERT so that the UPDATE uses the same set of cursors as the INSERT. check-in: c37f39d1 user: drh tags: trunk | |
| 00:40 | Minor simplification of the cursor allocation logic for update. check-in: fdf71be6 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
...
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
|
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;
................................................................................
}
if( eOnePass!=ONEPASS_MULTI ){
sqlite3WhereEnd(pWInfo);
}
labelBreak = sqlite3VdbeMakeLabel(v);
if( !isView ){
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;
}
|
>
>
>
>
>
|
|
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
...
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
|
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;
................................................................................
}
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 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 |
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 = pParse->nTab;
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;
int iTab = pParse->nTab+1;
Index *pX;
for(pX=pTab->pIndex; ALWAYS(pX) && !IsPrimaryKeyIndex(pX); pX=pX->pNext){
iTab++;
}
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 = iTab;
pE1->iColumn = j;
}
pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
if( pE2 ){
pE2->iTable = regKey;
pE2->affinity = pTab->zColAff[j];
}
|
| < < < < < | |
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];
}
|