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