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