SQLite: Check-in [c37f39d1]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Enhance UPSERT so that the UPDATE uses the same set of cursors as the INSERT.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA3-256:	c37f39d18d41ae5ba6c4561d87cbbf71f3b6896b86cc5cff9cdf046b02dc521a
User & Date:	drh 2018-04-20 13:18:51

Context

2018-04-20
15:34		Add test cases for UPSERT. And a fix for a "REPLACE INTO ... ON CONFLICT" statement where the new row conflicts with both the IPK and the ON CONFLICT indexes. check-in: d8eb9f8d user: dan tags: trunk
13:18		Enhance UPSERT so that the UPDATE uses the same set of cursors as the INSERT. check-in: c37f39d1 user: drh tags: trunk
00:40		Minor simplification of the cursor allocation logic for update. check-in: fdf71be6 user: drh tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

    }
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert ){
    pTabList->a[0].iCursor = iDataCur;
    pUpsert->pUpsertSrc = pTabList;
    pUpsert->regData = regData;


    if( pUpsert->pUpsertTarget ){
      sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
    }
  }
#endif

>
>

    }
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert ){
    pTabList->a[0].iCursor = iDataCur;
    pUpsert->pUpsertSrc = pTabList;
    pUpsert->regData = regData;
    pUpsert->iDataCur = iDataCur;
    pUpsert->iIdxCur = iIdxCur;
    if( pUpsert->pUpsertTarget ){
      sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
    }
  }
#endif

Changes to src/sqliteInt.h.

*/
struct Upsert {
  ExprList *pUpsertTarget;  /* Optional description of conflicting index */
  Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */
  Index *pUpsertIdx;        /* Constraint that pUpsertTarget identifies */
  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */




  SrcList *pUpsertSrc;      /* Table to be updated */
  int regData;              /* First register holding array of VALUES */


};

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.








>
>
>
>


>
>

*/
struct Upsert {
  ExprList *pUpsertTarget;  /* Optional description of conflicting index */
  Expr *pUpsertTargetWhere; /* WHERE clause for partial index targets */
  Index *pUpsertIdx;        /* Constraint that pUpsertTarget identifies */
  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */
  /* The fields above comprise the parse tree for the upsert clause.
  ** The fields below are used to transfer information from the INSERT
  ** processing down into the UPDATE processing while generating code.
  ** Upsert owns the memory allocated above, but not the memory below. */
  SrcList *pUpsertSrc;      /* Table to be updated */
  int regData;              /* First register holding array of VALUES */
  int iDataCur;             /* Index of the data cursor */
  int iIdxCur;              /* Index of the first index cursor */
};

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.

Changes to src/update.c.

  testcase( pPk!=0 && pPk!=pTab->pIndex );
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
    if( pPk==pIdx ){
      iDataCur = pParse->nTab;
    }
    pParse->nTab++;
  }





  pTabList->a[0].iCursor = iDataCur;

  /* Allocate space for aXRef[], aRegIdx[], and aToOpen[].  
  ** Initialize aXRef[] and aToOpen[] to their default values.
  */
  aXRef = sqlite3DbMallocRawNN(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 );
  if( aXRef==0 ) goto update_cleanup;
................................................................................
  }

  if( eOnePass!=ONEPASS_MULTI ){
    sqlite3WhereEnd(pWInfo);
  }

  labelBreak = sqlite3VdbeMakeLabel(v);
  if( !isView ){
    int addrOnce = 0;

    /* Open every index that needs updating. */
    if( eOnePass!=ONEPASS_OFF ){
      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0;
      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0;
    }








>
>
>
>
>







 







|

  testcase( pPk!=0 && pPk!=pTab->pIndex );
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){
    if( pPk==pIdx ){
      iDataCur = pParse->nTab;
    }
    pParse->nTab++;
  }
  if( pUpsert ){
    iDataCur = pUpsert->iDataCur;
    iIdxCur = pUpsert->iIdxCur;
    pParse->nTab = iBaseCur;
  }
  pTabList->a[0].iCursor = iDataCur;

  /* Allocate space for aXRef[], aRegIdx[], and aToOpen[].  
  ** Initialize aXRef[] and aToOpen[] to their default values.
  */
  aXRef = sqlite3DbMallocRawNN(db, sizeof(int) * (pTab->nCol+nIdx) + nIdx+2 );
  if( aXRef==0 ) goto update_cleanup;
................................................................................
  }

  if( eOnePass!=ONEPASS_MULTI ){
    sqlite3WhereEnd(pWInfo);
  }

  labelBreak = sqlite3VdbeMakeLabel(v);
  if( !isView && pUpsert==0 ){
    int addrOnce = 0;

    /* Open every index that needs updating. */
    if( eOnePass!=ONEPASS_OFF ){
      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iBaseCur] = 0;
      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iBaseCur] = 0;
    }

Changes to src/upsert.c.

      sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey);
    }else{
      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey);
    }
    pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
    if( pE1 ){
      pE1->pTab = pTab;
      pE1->iTable = pParse->nTab;
      pE1->iColumn = -1;
    }
    pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
    if( pE2 ){
      pE2->iTable = regKey;
      pE2->affinity = SQLITE_AFF_INTEGER;
    }
    pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
  }else{
    /* a WITHOUT ROWID table */
    int i, j;
    int iTab = pParse->nTab+1;
    Index *pX;
    for(pX=pTab->pIndex; ALWAYS(pX) && !IsPrimaryKeyIndex(pX); pX=pX->pNext){
      iTab++;
    }
    for(i=0; i<pIdx->nKeyCol; i++){
      regKey = ++pParse->nMem;
      sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regKey);
      j = pIdx->aiColumn[i];
      VdbeComment((v, "%s", pTab->aCol[j].zName));
      pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
      if( pE1 ){
        pE1->pTab = pTab;
        pE1->iTable = iTab;
        pE1->iColumn = j;
      }
      pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
      if( pE2 ){
        pE2->iTable = regKey;
        pE2->affinity = pTab->zColAff[j];
      }








|











<
<
<
<
<








|

      sqlite3VdbeAddOp2(v, OP_IdxRowid, iCur, regKey);
    }else{
      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regKey);
    }
    pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
    if( pE1 ){
      pE1->pTab = pTab;
      pE1->iTable = pUpsert->iDataCur;
      pE1->iColumn = -1;
    }
    pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
    if( pE2 ){
      pE2->iTable = regKey;
      pE2->affinity = SQLITE_AFF_INTEGER;
    }
    pWhere = sqlite3ExprAnd(db,pWhere,sqlite3PExpr(pParse, TK_EQ, pE1, pE2));
  }else{
    /* a WITHOUT ROWID table */
    int i, j;





    for(i=0; i<pIdx->nKeyCol; i++){
      regKey = ++pParse->nMem;
      sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regKey);
      j = pIdx->aiColumn[i];
      VdbeComment((v, "%s", pTab->aCol[j].zName));
      pE1 = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
      if( pE1 ){
        pE1->pTab = pTab;
        pE1->iTable = pUpsert->iDataCur;
        pE1->iColumn = j;
      }
      pE2 = sqlite3ExprAlloc(db, TK_REGISTER, 0, 0);
      if( pE2 ){
        pE2->iTable = regKey;
        pE2->affinity = pTab->zColAff[j];
      }