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 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   SrcList *pSrc;            /* FROM clause for the UPDATE */
 207   int iDataCur = pUpsert->iDataCur;
 208
 209   assert( v!=0 );
 210   VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
 211   if( pIdx && iCur!=iDataCur ){
 212     if( HasRowid(pTab) ){
 213       int regRowid = sqlite3GetTempReg(pParse);
 214       sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regRowid);
 215       sqlite3VdbeAddOp3(v, OP_SeekRowid, iDataCur, 0, regRowid);
 216       VdbeCoverage(v);
 217       sqlite3ReleaseTempReg(pParse, regRowid);
 218     }else{
 219       Index *pPk = sqlite3PrimaryKeyIndex(pTab);
 220       int nPk = pPk->nKeyCol;
 221       int iPk = pParse->nMem+1;
 222       int i;
 223       pParse->nMem += nPk;
 224       for(i=0; i<nPk; i++){
 225         int k;
 226         assert( pPk->aiColumn[i]>=0 );
 227         k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[i]);
 228         sqlite3VdbeAddOp3(v, OP_Column, iCur, k, iPk+i);
 229         VdbeComment((v, "%s.%s", pIdx->zName,
 230                     pTab->aCol[pPk->aiColumn[i]].zName));
 231       }
 232       sqlite3VdbeVerifyAbortable(v, OE_Abort);
 233       i = sqlite3VdbeAddOp4Int(v, OP_Found, iDataCur, 0, iPk, nPk);
 234       VdbeCoverage(v);
 235       sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CORRUPT, OE_Abort, 0,
 236             "corrupt database", P4_STATIC);
 237       sqlite3VdbeJumpHere(v, i);
 238     }
 239   }
 240   /* pUpsert does not own pUpsertSrc - the outer INSERT statement does.  So
 241   ** we have to make a copy before passing it down into sqlite3Update() */
 242   pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0);
 243   sqlite3Update(pParse, pSrc, pUpsert->pUpsertSet,
 244       pUpsert->pUpsertWhere, OE_Abort, 0, 0, pUpsert);
 245   pUpsert->pUpsertSet = 0;    /* Will have been deleted by sqlite3Update() */
 246   pUpsert->pUpsertWhere = 0;  /* Will have been deleted by sqlite3Update() */
 247   VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
 248 }
 249
 250 #endif /* SQLITE_OMIT_UPSERT */