1
2
3
4
5
6
7
8
9
10
11
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
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
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
|
/*
** 2018-04-12
**
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a blessing:
**
** May you do good and not evil.
** May you find forgiveness for yourself and forgive others.
** May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code to implement various aspects of UPSERT
** processing and handling of the Upsert object.
*/
#include "sqliteInt.h"
#ifndef SQLITE_OMIT_UPSERT
/*
** Free a list of Upsert objects
*/
static void SQLITE_NOINLINE upsertDelete(sqlite3 *db, Upsert *p){
do{
Upsert *pNext = p->pNextUpsert;
sqlite3ExprListDelete(db, p->pUpsertTarget);
sqlite3ExprDelete(db, p->pUpsertTargetWhere);
sqlite3ExprListDelete(db, p->pUpsertSet);
sqlite3ExprDelete(db, p->pUpsertWhere);
sqlite3DbFree(db, p->pToFree);
sqlite3DbFree(db, p);
p = pNext;
}while( p );
}
void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
if( p ) upsertDelete(db, p);
}
/*
** Duplicate an Upsert object.
*/
Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
if( p==0 ) return 0;
return sqlite3UpsertNew(db,
sqlite3ExprListDup(db, p->pUpsertTarget, 0),
sqlite3ExprDup(db, p->pUpsertTargetWhere, 0),
sqlite3ExprListDup(db, p->pUpsertSet, 0),
sqlite3ExprDup(db, p->pUpsertWhere, 0),
sqlite3UpsertDup(db, p->pNextUpsert)
);
}
/*
** Create a new Upsert object.
*/
Upsert *sqlite3UpsertNew(
sqlite3 *db, /* Determines which memory allocator to use */
ExprList *pTarget, /* Target argument to ON CONFLICT, or NULL */
Expr *pTargetWhere, /* Optional WHERE clause on the target */
ExprList *pSet, /* UPDATE columns, or NULL for a DO NOTHING */
Expr *pWhere, /* WHERE clause for the ON CONFLICT UPDATE */
Upsert *pNext /* Next ON CONFLICT clause in the list */
){
Upsert *pNew;
pNew = sqlite3DbMallocZero(db, sizeof(Upsert));
if( pNew==0 ){
sqlite3ExprListDelete(db, pTarget);
sqlite3ExprDelete(db, pTargetWhere);
sqlite3ExprListDelete(db, pSet);
sqlite3ExprDelete(db, pWhere);
sqlite3UpsertDelete(db, pNext);
return 0;
}else{
pNew->pUpsertTarget = pTarget;
pNew->pUpsertTargetWhere = pTargetWhere;
pNew->pUpsertSet = pSet;
pNew->pUpsertWhere = pWhere;
pNew->isDoUpdate = pSet!=0;
pNew->pNextUpsert = pNext;
}
return pNew;
}
/*
** Analyze the ON CONFLICT clause described by pUpsert. Resolve all
** symbols in the conflict-target.
**
** Return SQLITE_OK if everything works, or an error code is something
** is wrong.
*/
int sqlite3UpsertAnalyzeTarget(
Parse *pParse, /* The parsing context */
SrcList *pTabList, /* Table into which we are inserting */
Upsert *pUpsert /* The ON CONFLICT clauses */
){
Table *pTab; /* That table into which we are inserting */
int rc; /* Result code */
int iCursor; /* Cursor used by pTab */
Index *pIdx; /* One of the indexes of pTab */
ExprList *pTarget; /* The conflict-target clause */
Expr *pTerm; /* One term of the conflict-target clause */
NameContext sNC; /* Context for resolving symbolic names */
Expr sCol[2]; /* Index column converted into an Expr */
int nClause = 0; /* Counter of ON CONFLICT clauses */
assert( pTabList->nSrc==1 );
assert( pTabList->a[0].pTab!=0 );
assert( pUpsert!=0 );
assert( pUpsert->pUpsertTarget!=0 );
/* Resolve all symbolic names in the conflict-target clause, which
** includes both the list of columns and the optional partial-index
** WHERE clause.
*/
memset(&sNC, 0, sizeof(sNC));
sNC.pParse = pParse;
sNC.pSrcList = pTabList;
for(; pUpsert && pUpsert->pUpsertTarget;
pUpsert=pUpsert->pNextUpsert, nClause++){
rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
if( rc ) return rc;
rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
if( rc ) return rc;
/* Check to see if the conflict target matches the rowid. */
pTab = pTabList->a[0].pTab;
pTarget = pUpsert->pUpsertTarget;
iCursor = pTabList->a[0].iCursor;
if( HasRowid(pTab)
&& pTarget->nExpr==1
&& (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
&& pTerm->iColumn==XN_ROWID
){
/* The conflict-target is the rowid of the primary table */
assert( pUpsert->pUpsertIdx==0 );
continue;
}
/* Initialize sCol[0..1] to be an expression parse tree for a
** single column of an index. The sCol[0] node will be the TK_COLLATE
** operator and sCol[1] will be the TK_COLUMN operator. Code below
** will populate the specific collation and column number values
** prior to comparing against the conflict-target expression.
*/
memset(sCol, 0, sizeof(sCol));
sCol[0].op = TK_COLLATE;
sCol[0].pLeft = &sCol[1];
sCol[1].op = TK_COLUMN;
sCol[1].iTable = pTabList->a[0].iCursor;
/* Check for matches against other indexes */
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
int ii, jj, nn;
if( !IsUniqueIndex(pIdx) ) continue;
if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
if( pIdx->pPartIdxWhere ){
if( pUpsert->pUpsertTargetWhere==0 ) continue;
if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
pIdx->pPartIdxWhere, iCursor)!=0 ){
continue;
}
}
nn = pIdx->nKeyCol;
for(ii=0; ii<nn; ii++){
Expr *pExpr;
sCol[0].u.zToken = (char*)pIdx->azColl[ii];
if( pIdx->aiColumn[ii]==XN_EXPR ){
assert( pIdx->aColExpr!=0 );
assert( pIdx->aColExpr->nExpr>ii );
assert( pIdx->bHasExpr );
pExpr = pIdx->aColExpr->a[ii].pExpr;
if( pExpr->op!=TK_COLLATE ){
sCol[0].pLeft = pExpr;
pExpr = &sCol[0];
}
}else{
sCol[0].pLeft = &sCol[1];
sCol[1].iColumn = pIdx->aiColumn[ii];
pExpr = &sCol[0];
}
for(jj=0; jj<nn; jj++){
if( sqlite3ExprCompare(0,pTarget->a[jj].pExpr,pExpr,iCursor)<2 ){
break; /* Column ii of the index matches column jj of target */
}
}
if( jj>=nn ){
/* The target contains no match for column jj of the index */
break;
}
}
if( ii<nn ){
/* Column ii of the index did not match any term of the conflict target.
** Continue the search with the next index. */
continue;
}
pUpsert->pUpsertIdx = pIdx;
break;
}
if( pUpsert->pUpsertIdx==0 ){
char zWhich[16];
if( nClause==0 && pUpsert->pNextUpsert==0 ){
zWhich[0] = 0;
}else{
sqlite3_snprintf(sizeof(zWhich),zWhich,"%r ", nClause+1);
}
sqlite3ErrorMsg(pParse, "%sON CONFLICT clause does not match any "
"PRIMARY KEY or UNIQUE constraint", zWhich);
return SQLITE_ERROR;
}
}
return SQLITE_OK;
}
/*
** Return true if pUpsert is the last ON CONFLICT clause with a
** conflict target, or if pUpsert is followed by another ON CONFLICT
** clause that targets the INTEGER PRIMARY KEY.
*/
int sqlite3UpsertNextIsIPK(Upsert *pUpsert){
Upsert *pNext;
if( NEVER(pUpsert==0) ) return 0;
pNext = pUpsert->pNextUpsert;
if( pNext==0 ) return 1;
if( pNext->pUpsertTarget==0 ) return 1;
if( pNext->pUpsertIdx==0 ) return 1;
return 0;
}
/*
** Given the list of ON CONFLICT clauses described by pUpsert, and
** a particular index pIdx, return a pointer to the particular ON CONFLICT
** clause that applies to the index. Or, if the index is not subject to
** any ON CONFLICT clause, return NULL.
*/
Upsert *sqlite3UpsertOfIndex(Upsert *pUpsert, Index *pIdx){
while(
pUpsert
&& pUpsert->pUpsertTarget!=0
&& pUpsert->pUpsertIdx!=pIdx
){
pUpsert = pUpsert->pNextUpsert;
}
return pUpsert;
}
/*
** Generate bytecode that does an UPDATE as part of an upsert.
**
** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK.
** In this case parameter iCur is a cursor open on the table b-tree that
** currently points to the conflicting table row. Otherwise, if pIdx
** is not NULL, then pIdx is the constraint that failed and iCur is a
** cursor points to the conflicting row.
*/
void sqlite3UpsertDoUpdate(
Parse *pParse, /* The parsing and code-generating context */
Upsert *pUpsert, /* The ON CONFLICT clause for the upsert */
Table *pTab, /* The table being updated */
Index *pIdx, /* The UNIQUE constraint that failed */
int iCur /* Cursor for pIdx (or pTab if pIdx==NULL) */
){
Vdbe *v = pParse->pVdbe;
sqlite3 *db = pParse->db;
SrcList *pSrc; /* FROM clause for the UPDATE */
int iDataCur;
int i;
Upsert *pTop = pUpsert;
assert( v!=0 );
assert( pUpsert!=0 );
iDataCur = pUpsert->iDataCur;
pUpsert = sqlite3UpsertOfIndex(pTop, pIdx);
VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
if( pIdx && iCur!=iDataCur ){
if( HasRowid(pTab) ){
int regRowid = sqlite3GetTempReg(pParse);
sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regRowid);
sqlite3VdbeAddOp3(v, OP_SeekRowid, iDataCur, 0, regRowid);
VdbeCoverage(v);
sqlite3ReleaseTempReg(pParse, regRowid);
}else{
Index *pPk = sqlite3PrimaryKeyIndex(pTab);
int nPk = pPk->nKeyCol;
int iPk = pParse->nMem+1;
pParse->nMem += nPk;
for(i=0; i<nPk; i++){
int k;
assert( pPk->aiColumn[i]>=0 );
k = sqlite3TableColumnToIndex(pIdx, pPk->aiColumn[i]);
sqlite3VdbeAddOp3(v, OP_Column, iCur, k, iPk+i);
VdbeComment((v, "%s.%s", pIdx->zName,
pTab->aCol[pPk->aiColumn[i]].zCnName));
}
sqlite3VdbeVerifyAbortable(v, OE_Abort);
i = sqlite3VdbeAddOp4Int(v, OP_Found, iDataCur, 0, iPk, nPk);
VdbeCoverage(v);
sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CORRUPT, OE_Abort, 0,
"corrupt database", P4_STATIC);
sqlite3MayAbort(pParse);
sqlite3VdbeJumpHere(v, i);
}
}
/* pUpsert does not own pTop->pUpsertSrc - the outer INSERT statement does.
** So we have to make a copy before passing it down into sqlite3Update() */
pSrc = sqlite3SrcListDup(db, pTop->pUpsertSrc, 0);
/* excluded.* columns of type REAL need to be converted to a hard real */
for(i=0; i<pTab->nCol; i++){
if( pTab->aCol[i].affinity==SQLITE_AFF_REAL ){
sqlite3VdbeAddOp1(v, OP_RealAffinity, pTop->regData+i);
}
}
sqlite3Update(pParse, pSrc, sqlite3ExprListDup(db,pUpsert->pUpsertSet,0),
sqlite3ExprDup(db,pUpsert->pUpsertWhere,0), OE_Abort, 0, 0, pUpsert);
VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
}
#endif /* SQLITE_OMIT_UPSERT */
|