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 pExpr = pIdx->aColExpr->a[ii].pExpr; 170 if( pExpr->op!=TK_COLLATE ){ 171 sCol[0].pLeft = pExpr; 172 pExpr = &sCol[0]; 173 } 174 }else{ 175 sCol[0].pLeft = &sCol[1]; 176 sCol[1].iColumn = pIdx->aiColumn[ii]; 177 pExpr = &sCol[0]; 178 } 179 for(jj=0; jj<nn; jj++){ 180 if( sqlite3ExprCompare(pParse,pTarget->a[jj].pExpr,pExpr,iCursor)<2 ){ 181 break; /* Column ii of the index matches column jj of target */ 182 } 183 } 184 if( jj>=nn ){ 185 /* The target contains no match for column jj of the index */ 186 break; 187 } 188 } 189 if( ii<nn ){ 190 /* Column ii of the index did not match any term of the conflict target. 191 ** Continue the search with the next index. */ 192 continue; 193 } 194 pUpsert->pUpsertIdx = pIdx; 195 break; 196 } 197 if( pUpsert->pUpsertIdx==0 ){ 198 char zWhich[16]; 199 if( nClause==0 && pUpsert->pNextUpsert==0 ){ 200 zWhich[0] = 0; 201 }else{ 202 sqlite3_snprintf(sizeof(zWhich),zWhich,"%r ", nClause+1); 203 } 204 sqlite3ErrorMsg(pParse, "%sON CONFLICT clause does not match any " 205 "PRIMARY KEY or UNIQUE constraint", zWhich); 206 return SQLITE_ERROR; 207 } 208 } 209 return SQLITE_OK; 210 } 211 212 /* 213 ** Return true if pUpsert is the last ON CONFLICT clause with a 214 ** conflict target, or if pUpsert is followed by another ON CONFLICT 215 ** clause that targets the INTEGER PRIMARY KEY. 216 */ 217 int sqlite3UpsertNextIsIPK(Upsert *pUpsert){ 218 Upsert *pNext; 219 if( NEVER(pUpsert==0) ) return 0; 220 pNext = pUpsert->pNextUpsert; 221 if( pNext==0 ) return 1; 222 if( pNext->pUpsertTarget==0 ) return 1; 223 if( pNext->pUpsertIdx==0 ) return 1; 224 return 0; 225 } 226 227 /* 228 ** Given the list of ON CONFLICT clauses described by pUpsert, and 229 ** a particular index pIdx, return a pointer to the particular ON CONFLICT 230 ** clause that applies to the index. Or, if the index is not subject to 231 ** any ON CONFLICT clause, return NULL. 232 */ 233 Upsert *sqlite3UpsertOfIndex(Upsert *pUpsert, Index *pIdx){ 234 while( 235 pUpsert 236 && pUpsert->pUpsertTarget!=0 237 && pUpsert->pUpsertIdx!=pIdx 238 ){ 239 pUpsert = pUpsert->pNextUpsert; 240 } 241 return pUpsert; 242 } 243 244 /* 245 ** Generate bytecode that does an UPDATE as part of an upsert. 246 ** 247 ** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK. 248 ** In this case parameter iCur is a cursor open on the table b-tree that 249 ** currently points to the conflicting table row. Otherwise, if pIdx 250 ** is not NULL, then pIdx is the constraint that failed and iCur is a 251 ** cursor points to the conflicting row. 252 */ 253 void sqlite3UpsertDoUpdate( 254 Parse *pParse, /* The parsing and code-generating context */ 255 Upsert *pUpsert, /* The ON CONFLICT clause for the upsert */ 256 Table *pTab, /* The table being updated */ 257 Index *pIdx, /* The UNIQUE constraint that failed */ 258 int iCur /* Cursor for pIdx (or pTab if pIdx==NULL) */ 259 ){ 260 Vdbe *v = pParse->pVdbe; 261 sqlite3 *db = pParse->db; 262 SrcList *pSrc; /* FROM clause for the UPDATE */ 263 int iDataCur; 264 int i; 265 Upsert *pTop = pUpsert; 266 267 assert( v!=0 ); 268 assert( pUpsert!=0 ); 269 iDataCur = pUpsert->iDataCur; 270 pUpsert = sqlite3UpsertOfIndex(pTop, pIdx); 271 VdbeNoopComment((v, "Begin DO UPDATE of UPSERT")); 272 if( pIdx && iCur!=iDataCur ){ 273 if( HasRowid(pTab) ){ 274 int regRowid = sqlite3GetTempReg(pParse); 275 sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regRowid); 276 sqlite3VdbeAddOp3(v, OP_SeekRowid, iDataCur, 0, regRowid); 277 VdbeCoverage(v); 278 sqlite3ReleaseTempReg(pParse, regRowid); 279 }else{ 280 Index *pPk = sqlite3PrimaryKeyIndex(pTab); 281 int nPk = pPk->nKeyCol; 282 int iPk = pParse->nMem+1; 283 pParse->nMem += nPk; 284 for(i=0; i<nPk; i++){ 285 int k; 286 assert( pPk->aiColumn[i]>=0 ); 287 k = sqlite3TableColumnToIndex(pIdx, pPk->aiColumn[i]); 288 sqlite3VdbeAddOp3(v, OP_Column, iCur, k, iPk+i); 289 VdbeComment((v, "%s.%s", pIdx->zName, 290 pTab->aCol[pPk->aiColumn[i]].zCnName)); 291 } 292 sqlite3VdbeVerifyAbortable(v, OE_Abort); 293 i = sqlite3VdbeAddOp4Int(v, OP_Found, iDataCur, 0, iPk, nPk); 294 VdbeCoverage(v); 295 sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CORRUPT, OE_Abort, 0, 296 "corrupt database", P4_STATIC); 297 sqlite3MayAbort(pParse); 298 sqlite3VdbeJumpHere(v, i); 299 } 300 } 301 /* pUpsert does not own pTop->pUpsertSrc - the outer INSERT statement does. 302 ** So we have to make a copy before passing it down into sqlite3Update() */ 303 pSrc = sqlite3SrcListDup(db, pTop->pUpsertSrc, 0); 304 /* excluded.* columns of type REAL need to be converted to a hard real */ 305 for(i=0; i<pTab->nCol; i++){ 306 if( pTab->aCol[i].affinity==SQLITE_AFF_REAL ){ 307 sqlite3VdbeAddOp1(v, OP_RealAffinity, pTop->regData+i); 308 } 309 } 310 sqlite3Update(pParse, pSrc, sqlite3ExprListDup(db,pUpsert->pUpsertSet,0), 311 sqlite3ExprDup(db,pUpsert->pUpsertWhere,0), OE_Abort, 0, 0, pUpsert); 312 VdbeNoopComment((v, "End DO UPDATE of UPSERT")); 313 } 314 315 #endif /* SQLITE_OMIT_UPSERT */ 316