SQLite

Check-in [a9080bc8b8]
Login

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

Overview
Comment:First cut at logic to perform DO UPDATE for rowid tables.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: a9080bc8b8c5f3b399eb1819bb5009581f178d85bb2b2cca7bc16a7b81b06863
User & Date: drh 2018-04-13 21:55:22.055
Context
2018-04-14
20:24
Make sure constraint checks occur in the correct order, even in the presence of upserts. (check-in: 07fb30c3de user: drh tags: upsert)
2018-04-13
21:55
First cut at logic to perform DO UPDATE for rowid tables. (check-in: a9080bc8b8 user: drh tags: upsert)
18:59
Add infrastructure for doing an UPDATE as part of an UPSERT. Still no actual UPDATE code, however. (check-in: 6d3017f92b user: drh tags: upsert)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/insert.c.
801
802
803
804
805
806
807
808
809


810

811
812
813
814
815
816
817
    for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
      assert( pIdx );
      aRegIdx[i] = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert && pUpsert->pUpsertTarget ){
    pTabList->a[0].iCursor = iDataCur;


    sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);

  }
#endif


  /* This is the top of the main insertion loop */
  if( useTempTable ){
    /* This block codes the top of loop only.  The complete loop is the







|

>
>
|
>







801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
    for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
      assert( pIdx );
      aRegIdx[i] = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }
  }
#ifndef SQLITE_OMIT_UPSERT
  if( pUpsert ){
    pTabList->a[0].iCursor = iDataCur;
    pUpsert->pUpsertSrc = pTabList;
    if( pUpsert->pUpsertTarget ){
      sqlite3UpsertAnalyzeTarget(pParse, pTabList, pUpsert);
    }
  }
#endif


  /* This is the top of the main insertion loop */
  if( useTempTable ){
    /* This block codes the top of loop only.  The complete loop is the
Changes to src/sqliteInt.h.
2724
2725
2726
2727
2728
2729
2730

2731
2732
2733
2734
2735
2736
2737
*/
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 */

};

/*
** 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.







>







2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
*/
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 */
};

/*
** 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/upsert.c.
185
186
187
188
189
190
191
192
193
194






195
196





























197
198
199
200
*/
void sqlite3UpsertDoUpdate(
  Parse *pParse,        /* The parsing and code-generating context */
  Upsert *pUpsert,      /* The ON CONFLICT clause for the upsert */
  Table *pTab,          /* The table being updated */
  Index *pIdx,          /* The UNIQUE constraint that failed */
  int iDataCur,         /* Cursor for the pTab, table being updated */
  int iIdxCur           /* Cursor for the pIdx */
){
  Vdbe *v = pParse->pVdbe;






  assert( v!=0 );
  VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));





























  VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
}

#endif /* SQLITE_OMIT_UPSERT */







|


>
>
>
>
>
>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
*/
void sqlite3UpsertDoUpdate(
  Parse *pParse,        /* The parsing and code-generating context */
  Upsert *pUpsert,      /* The ON CONFLICT clause for the upsert */
  Table *pTab,          /* The table being updated */
  Index *pIdx,          /* The UNIQUE constraint that failed */
  int iDataCur,         /* Cursor for the pTab, table being updated */
  int iIdxCur           /* Cursor for pIdx */
){
  Vdbe *v = pParse->pVdbe;
  sqlite3 *db = pParse->db;
  int regKey;               /* Register(s) containing the key */
  Expr *pWhere;             /* Where clause for the UPDATE */
  Expr *pE1, *pE2;
  SrcList *pSrc;            /* FROM clause for the UPDATE */

  assert( v!=0 );
  VdbeNoopComment((v, "Begin DO UPDATE of UPSERT"));
  pWhere = sqlite3ExprDup(db, pUpsert->pUpsertWhere, 0);
  if( pIdx==0 || HasRowid(pTab) ){
    /* We are dealing with an IPK */
    regKey = ++pParse->nMem;
    if( pIdx ){
      sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, regKey);
    }else{
      sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, 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));
    pSrc = sqlite3SrcListDup(db, pUpsert->pUpsertSrc, 0);
    sqlite3Update(pParse, pSrc, 
        sqlite3ExprListDup(db, pUpsert->pUpsertSet, 0),
        pWhere, OE_Abort, 0, 0);
  }else{
    /* a WITHOUT ROWID table */
    sqlite3ExprDelete(db, pWhere);
  }
  VdbeNoopComment((v, "End DO UPDATE of UPSERT"));
}

#endif /* SQLITE_OMIT_UPSERT */