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 || pTerm->iColumn==pTab->iPKey) 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 if( pIdx->aiColumn[ii]==XN_EXPR ){ 152 assert( pIdx->aColExpr!=0 ); 153 assert( pIdx->aColExpr->nExpr>ii ); 154 pExpr = pIdx->aColExpr->a[ii].pExpr; 155 }else{ 156 sCol[1].iColumn = pIdx->aiColumn[ii]; 157 sCol[0].u.zToken = (char*)pIdx->azColl[ii]; 158 pExpr = &sCol[0]; 159 } 160 for(jj=0; jj<nn; jj++){ 161 if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr,iCursor)<2 ){ 162 break; /* Column ii of the index matches column jj of target */ 163 } 164 } 165 if( jj>=nn ){ 166 /* The target contains no match for column jj of the index */ 167 break; 168 } 169 } 170 if( ii<nn ){ 171 /* Column ii of the index did not match any term of the conflict target. 172 ** Continue the search with the next index. */ 173 continue; 174 } 175 pUpsert->pUpsertIdx = pIdx; 176 return SQLITE_OK; 177 } 178 sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any " 179 "PRIMARY KEY or UNIQUE constraint"); 180 return SQLITE_ERROR; 181 } 182 183 /* 184 ** Generate bytecode that does an UPDATE as part of an upsert. 185 ** 186 ** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK. 187 ** In this case parameter iCur is a cursor open on the table b-tree that 188 ** currently points to the conflicting table row. Otherwise, if pIdx 189 ** is not NULL, then pIdx is the constraint that failed and iCur is a 190 ** cursor points to the conflicting row. 191 */ 192 void sqlite3UpsertDoUpdate( 193 Parse *pParse, /* The parsing and code-generating context */ 194 Upsert *pUpsert, /* The ON CONFLICT clause for the upsert */ 195 Table *pTab, /* The table being updated */ 196 Index *pIdx, /* The UNIQUE constraint that failed */ 197 int iCur /* Cursor for pIdx (or pTab if pIdx==NULL) */ 198 ){ 199 Vdbe *v = pParse->pVdbe; 200 sqlite3 *db = pParse->db; 201 int regKey; /* Register(s) containing the key */ 202 Expr *pWhere; /* Where clause for the UPDATE */ 203 Expr *pE1, *pE2; 204 SrcList *pSrc; /* FROM clause for the UPDATE */ 205 206 assert( v!=0 ); 207 VdbeNoopComment((v, "Begin DO UPDATE of UPSERT")); 208 pWhere = sqlite3ExprDup(db, pUpsert->pUpsertWhere, 0); 209 if( pIdx==0 || HasRowid(pTab) ){ 210 /* We are dealing with an IPK */ 211 regKey = ++pParse->nMem; 212 if( pIdx ){ 213 sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey); 214 }else{ 215 sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey); 216 } 217 pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0); 218 if( pE1 ){ 219 pE1->pTab = pTab; 220 pE1->iTable = pParse->nTab; 221 pE1->iColumn = -1; 222 } 223 pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0); 224 if( pE2 ){ 225 pE2->iTable = regKey; 226 pE2->affinity = SQLITE_AFF_INTEGER; 227 } 228 pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2)); 229 }else{ 230 /* a WITHOUT ROWID table */ 231 int i, j; 232 int iTab = pParse->nTab+1; 233 Index *pX; 234 for(pX=pTab->pIndex; ALWAYS(pX) && !IsPrimaryKeyIndex(pX); pX=pX->pNext){ 235 iTab++; 236 } 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 = iTab; 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 pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0); 257 sqlite3Update(pParse, pSrc, 258 sqlite3ExprListDup(db, pUpsert->pUpsertSet, 0), 259 pWhere, OE_Abort, 0, 0, pUpsert); 260 VdbeNoopComment((v, "End DO UPDATE of UPSERT")); 261 } 262 263 #endif /* SQLITE_OMIT_UPSERT */ 264