src/upsert.c

   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   Upsert *pAll       /* Complete list of all ON CONFLICT clauses */
  95 ){
  96   Table *pTab;            /* That table into which we are inserting */
  97   int rc;                 /* Result code */
  98   int iCursor;            /* Cursor used by pTab */
  99   Index *pIdx;            /* One of the indexes of pTab */
 100   ExprList *pTarget;      /* The conflict-target clause */
 101   Expr *pTerm;            /* One term of the conflict-target clause */
 102   NameContext sNC;        /* Context for resolving symbolic names */
 103   Expr sCol[2];           /* Index column converted into an Expr */
 104   int nClause = 0;        /* Counter of ON CONFLICT clauses */
 105
 106   assert( pTabList->nSrc==1 );
 107   assert( pTabList->a[0].pTab!=0 );
 108   assert( pUpsert!=0 );
 109   assert( pUpsert->pUpsertTarget!=0 );
 110
 111   /* Resolve all symbolic names in the conflict-target clause, which
 112   ** includes both the list of columns and the optional partial-index
 113   ** WHERE clause.
 114   */
 115   memset(&sNC, 0, sizeof(sNC));
 116   sNC.pParse = pParse;
 117   sNC.pSrcList = pTabList;
 118   for(; pUpsert && pUpsert->pUpsertTarget;
 119         pUpsert=pUpsert->pNextUpsert, nClause++){
 120     rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
 121     if( rc ) return rc;
 122     rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
 123     if( rc ) return rc;
 124
 125     /* Check to see if the conflict target matches the rowid. */
 126     pTab = pTabList->a[0].pTab;
 127     pTarget = pUpsert->pUpsertTarget;
 128     iCursor = pTabList->a[0].iCursor;
 129     if( HasRowid(pTab)
 130      && pTarget->nExpr==1
 131      && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
 132      && pTerm->iColumn==XN_ROWID
 133     ){
 134       /* The conflict-target is the rowid of the primary table */
 135       assert( pUpsert->pUpsertIdx==0 );
 136       continue;
 137     }
 138
 139     /* Initialize sCol[0..1] to be an expression parse tree for a
 140     ** single column of an index.  The sCol[0] node will be the TK_COLLATE
 141     ** operator and sCol[1] will be the TK_COLUMN operator.  Code below
 142     ** will populate the specific collation and column number values
 143     ** prior to comparing against the conflict-target expression.
 144     */
 145     memset(sCol, 0, sizeof(sCol));
 146     sCol[0].op = TK_COLLATE;
 147     sCol[0].pLeft = &sCol[1];
 148     sCol[1].op = TK_COLUMN;
 149     sCol[1].iTable = pTabList->a[0].iCursor;
 150
 151     /* Check for matches against other indexes */
 152     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
 153       int ii, jj, nn;
 154       if( !IsUniqueIndex(pIdx) ) continue;
 155       if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
 156       if( pIdx->pPartIdxWhere ){
 157         if( pUpsert->pUpsertTargetWhere==0 ) continue;
 158         if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
 159                                pIdx->pPartIdxWhere, iCursor)!=0 ){
 160           continue;
 161         }
 162       }
 163       nn = pIdx->nKeyCol;
 164       for(ii=0; ii<nn; ii++){
 165         Expr *pExpr;
 166         sCol[0].u.zToken = (char*)pIdx->azColl[ii];
 167         if( pIdx->aiColumn[ii]==XN_EXPR ){
 168           assert( pIdx->aColExpr!=0 );
 169           assert( pIdx->aColExpr->nExpr>ii );
 170           assert( pIdx->bHasExpr );
 171           pExpr = pIdx->aColExpr->a[ii].pExpr;
 172           if( pExpr->op!=TK_COLLATE ){
 173             sCol[0].pLeft = pExpr;
 174             pExpr = &sCol[0];
 175           }
 176         }else{
 177           sCol[0].pLeft = &sCol[1];
 178           sCol[1].iColumn = pIdx->aiColumn[ii];
 179           pExpr = &sCol[0];
 180         }
 181         for(jj=0; jj<nn; jj++){
 182           if( sqlite3ExprCompare(0,pTarget->a[jj].pExpr,pExpr,iCursor)<2 ){
 183             break;  /* Column ii of the index matches column jj of target */
 184           }
 185         }
 186         if( jj>=nn ){
 187           /* The target contains no match for column jj of the index */
 188           break;
 189         }
 190       }
 191       if( ii<nn ){
 192         /* Column ii of the index did not match any term of the conflict target.
 193         ** Continue the search with the next index. */
 194         continue;
 195       }
 196       pUpsert->pUpsertIdx = pIdx;
 197       if( sqlite3UpsertOfIndex(pAll,pIdx)!=pUpsert ){
 198         /* Really this should be an error.  The isDup ON CONFLICT clause will
 199         ** never fire.  But this problem was not discovered until three years
 200         ** after multi-CONFLICT upsert was added, and so we silently ignore
 201         ** the problem to prevent breaking applications that might actually
 202         ** have redundant ON CONFLICT clauses. */
 203         pUpsert->isDup = 1;
 204       }
 205       break;
 206     }
 207     if( pUpsert->pUpsertIdx==0 ){
 208       char zWhich[16];
 209       if( nClause==0 && pUpsert->pNextUpsert==0 ){
 210         zWhich[0] = 0;
 211       }else{
 212         sqlite3_snprintf(sizeof(zWhich),zWhich,"%r ", nClause+1);
 213       }
 214       sqlite3ErrorMsg(pParse, "%sON CONFLICT clause does not match any "
 215                               "PRIMARY KEY or UNIQUE constraint", zWhich);
 216       return SQLITE_ERROR;
 217     }
 218   }
 219   return SQLITE_OK;
 220 }
 221
 222 /*
 223 ** Return true if pUpsert is the last ON CONFLICT clause with a
 224 ** conflict target, or if pUpsert is followed by another ON CONFLICT
 225 ** clause that targets the INTEGER PRIMARY KEY.
 226 */
 227 int sqlite3UpsertNextIsIPK(Upsert *pUpsert){
 228   Upsert *pNext;
 229   if( NEVER(pUpsert==0) ) return 0;
 230   pNext = pUpsert->pNextUpsert;
 231   while( 1 /*exit-by-return*/ ){
 232     if( pNext==0 ) return 1;
 233     if( pNext->pUpsertTarget==0 ) return 1;
 234     if( pNext->pUpsertIdx==0 ) return 1;
 235     if( !pNext->isDup ) return 0;
 236     pNext = pNext->pNextUpsert;
 237   }
 238   return 0;
 239 }
 240
 241 /*
 242 ** Given the list of ON CONFLICT clauses described by pUpsert, and
 243 ** a particular index pIdx, return a pointer to the particular ON CONFLICT
 244 ** clause that applies to the index.  Or, if the index is not subject to
 245 ** any ON CONFLICT clause, return NULL.
 246 */
 247 Upsert *sqlite3UpsertOfIndex(Upsert *pUpsert, Index *pIdx){
 248   while(
 249       pUpsert
 250    && pUpsert->pUpsertTarget!=0
 251    && pUpsert->pUpsertIdx!=pIdx
 252   ){
 253      pUpsert = pUpsert->pNextUpsert;
 254   }
 255   return pUpsert;
 256 }
 257
 258 /*
 259 ** Generate bytecode that does an UPDATE as part of an upsert.
 260 **
 261 ** If pIdx is NULL, then the UNIQUE constraint that failed was the IPK.
 262 ** In this case parameter iCur is a cursor open on the table b-tree that
 263 ** currently points to the conflicting table row. Otherwise, if pIdx
 264 ** is not NULL, then pIdx is the constraint that failed and iCur is a
 265 ** cursor points to the conflicting row.
 266 */
 267 void sqlite3UpsertDoUpdate(
 268   Parse *pParse,        /* The parsing and code-generating context */
 269   Upsert *pUpsert,      /* The ON CONFLICT clause for the upsert */
 270   Table *pTab,          /* The table being updated */
 271   Index *pIdx,          /* The UNIQUE constraint that failed */
 272   int iCur              /* Cursor for pIdx (or pTab if pIdx==NULL) */
 273 ){
 274   Vdbe *v = pParse->pVdbe;
 275   sqlite3 *db = pParse->db;
 276   SrcList *pSrc;            /* FROM clause for the UPDATE */
 277   int iDataCur;
 278   int i;
 279   Upsert *pTop = pUpsert;
 280
 281   assert( v!=0 );
 282   assert( pUpsert!=0 );
 283   iDataCur = pUpsert->iDataCur;
 284   pUpsert = sqlite3UpsertOfIndex(pTop, pIdx);
 285   VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
 286   if( pIdx && iCur!=iDataCur ){
 287     if( HasRowid(pTab) ){
 288       int regRowid = sqlite3GetTempReg(pParse);
 289       sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regRowid);
 290       sqlite3VdbeAddOp3(v, OP_SeekRowid, iDataCur, 0, regRowid);
 291       VdbeCoverage(v);
 292       sqlite3ReleaseTempReg(pParse, regRowid);
 293     }else{
 294       Index *pPk = sqlite3PrimaryKeyIndex(pTab);
 295       int nPk = pPk->nKeyCol;
 296       int iPk = pParse->nMem+1;
 297       pParse->nMem += nPk;
 298       for(i=0; i<nPk; i++){
 299         int k;
 300         assert( pPk->aiColumn[i]>=0 );
 301         k = sqlite3TableColumnToIndex(pIdx, pPk->aiColumn[i]);
 302         sqlite3VdbeAddOp3(v, OP_Column, iCur, k, iPk+i);
 303         VdbeComment((v, "%s.%s", pIdx->zName,
 304                     pTab->aCol[pPk->aiColumn[i]].zCnName));
 305       }
 306       sqlite3VdbeVerifyAbortable(v, OE_Abort);
 307       i = sqlite3VdbeAddOp4Int(v, OP_Found, iDataCur, 0, iPk, nPk);
 308       VdbeCoverage(v);
 309       sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CORRUPT, OE_Abort, 0,
 310             "corrupt database", P4_STATIC);
 311       sqlite3MayAbort(pParse);
 312       sqlite3VdbeJumpHere(v, i);
 313     }
 314   }
 315   /* pUpsert does not own pTop->pUpsertSrc - the outer INSERT statement does.
 316   ** So we have to make a copy before passing it down into sqlite3Update() */
 317   pSrc = sqlite3SrcListDup(db, pTop->pUpsertSrc, 0);
 318   /* excluded.* columns of type REAL need to be converted to a hard real */
 319   for(i=0; i<pTab->nCol; i++){
 320     if( pTab->aCol[i].affinity==SQLITE_AFF_REAL ){
 321       sqlite3VdbeAddOp1(v, OP_RealAffinity, pTop->regData+i);
 322     }
 323   }
 324   sqlite3Update(pParse, pSrc, sqlite3ExprListDup(db,pUpsert->pUpsertSet,0),
 325       sqlite3ExprDup(db,pUpsert->pUpsertWhere,0), OE_Abort, 0, 0, pUpsert);
 326   VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
 327 }
 328
 329 #endif /* SQLITE_OMIT_UPSERT */