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