SQLite

    }
    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;
    sqlite3UpsertAnalyze(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
    ** following pseudocode (template 4):
    **
    **         rewind temp table, if empty goto D

      sqlite3MayAbort(pParse);
    }else
#endif
    {
      int isReplace;    /* Set to true if constraints may cause a replace */
      int bUseSeek;     /* True to use OPFLAG_SEEKRESULT */
      sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
          regIns, 0, ipkColumn>=0, onError, endOfLoop, &isReplace, 0, pUpsert
      );
      sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);

      /* Set the OPFLAG_USESEEKRESULT flag if either (a) there are no REPLACE
      ** constraints or (b) there are no triggers and this table is not a
      ** parent table in a foreign key constraint. It is safe to set the
      ** flag in the second case as if any REPLACE constraint is hit, an

  int iIdxCur,         /* First index cursor */
  int regNewData,      /* First register in a range holding values to insert */
  int regOldData,      /* Previous content.  0 for INSERTs */
  u8 pkChng,           /* Non-zero if the rowid or PRIMARY KEY changed */
  u8 overrideError,    /* Override onError to this if not OE_Default */
  int ignoreDest,      /* Jump to this label on an OE_Ignore resolution */
  int *pbMayReplace,   /* OUT: Set to true if constraint may cause a replace */
  int *aiChng,         /* column i is unchanged if aiChng[i]<0 */
  Upsert *pUpsert      /* ON CONFLICT clauses, if any.  NULL otherwise */
){
  Vdbe *v;             /* VDBE under constrution */
  Index *pIdx;         /* Pointer to one of the indices */
  Index *pPk = 0;      /* The PRIMARY KEY index */
  sqlite3 *db;         /* Database connection */
  int i;               /* loop counter */
  int ix;              /* Index loop counter */

%type upsert {Upsert*}
%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
upsert(A) ::= . { A = 0; }
upsert(A) ::= upsert(X) ON CONFLICT LP sortlist(Y) RP
              DO UPDATE SET setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,X,Y,Z,W); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON DUPLICATE KEY UPDATE setlist(Z) where_opt(W).
              { A = sqlite3UpsertNew(pParse->db,X,0,Z,W); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON CONFLICT LP sortlist(Y) RP DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,X,Y,0,0); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,X,0,0,0); /*X-overwrites-A*/ }

%type insert_cmd {int}

#define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
#define NC_Complex   0x2000  /* True if a function or subquery seen */

/*
** An instance of the following object describes a single ON CONFLICT
** clause in an upsert.  A list of these objects may be attached to
** an INSERT statement in order to form an upsert.
**
** The pUpsertTarget field is only set if the ON CONFLICT clause includes
** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the
** conflict-target clause.)
**
** pUpsertSet is the list of column=expr terms of the UPDATE statement. 
** The pUpsertSet field is NULL for a ON CONFLICT DO NOTHING.  The
** pUpsertWhere is the WHERE clause for the UPDATE and is NULL if the
** WHERE clause is omitted.
**
** The pUpsertIdx is a transient pointer to the unique index described
** by pUpsertTarget.  If pUpsertTarget describes the rowid, then pUpsertIdx
** will be NULL.  pUpsertIdx does not own the Index object it points to.
** Care must be taken to ensure that the Index object does not expire while
** the pointer is valid.
*/
struct Upsert {
  ExprList *pUpsertTarget;  /* Optional description of conflicting index */
  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
  Expr *pUpsertWhere;       /* WHERE clause for the ON CONFLICT UPDATE */
  Index *pUpsertIdx;        /* UNIQUE index referenced by pUpsertTarget */
  Upsert *pUpsertNext;      /* Next ON CONFLICT clause in the list */
};

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**

int sqlite3IsRowid(const char*);
void sqlite3GenerateRowDelete(
    Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8,int);
void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*, int);
int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);
void sqlite3ResolvePartIdxLabel(Parse*,int);
void sqlite3GenerateConstraintChecks(Parse*,Table*,int*,int,int,int,int,
                                     u8,u8,int,int*,int*,Upsert*);
#ifdef SQLITE_ENABLE_NULL_TRIM
  void sqlite3SetMakeRecordP5(Vdbe*,Table*);
#else
# define sqlite3SetMakeRecordP5(A,B)
#endif
void sqlite3CompleteInsertion(Parse*,Table*,int,int,int,int*,int,int,int);
int sqlite3OpenTableAndIndices(Parse*, Table*, int, u8, int, u8*, int*, int*);

#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)
#endif
#ifndef SQLITE_OMIT_UPSERT
  Upsert *sqlite3UpsertNew(sqlite3*,Upsert*,ExprList*,ExprList*,Expr*);
  void sqlite3UpsertDelete(sqlite3*,Upsert*);
  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
  int sqlite3UpsertAnalyze(Parse*,SrcList*,Upsert*);
#else
#define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)     ((Upsert*)0)
#endif

  if( !isView ){
    int addr1 = 0;        /* Address of jump instruction */

    /* Do constraint checks. */
    assert( regOldRowid>0 );
    sqlite3GenerateConstraintChecks(pParse, pTab, aRegIdx, iDataCur, iIdxCur,
        regNewRowid, regOldRowid, chngKey, onError, labelContinue, &bReplace,
        aXRef, 0);

    /* Do FK constraint checks. */
    if( hasFK ){
      sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngKey);
    }

    /* Delete the index entries associated with the current record.  */

    pNew->pUpsertTarget = pTarget;
    pNew->pUpsertSet = pSet;
    pNew->pUpsertNext = pPrior;
    pNew->pUpsertWhere = pWhere;
  }
  return pNew;
}

/*
** Analyze the ON CONFLICT clause(s) described by pUpsert.  Resolve all
** symbols in the conflict-target clausees.  Fill in the pUpsertIdx pointers.
**
** Return non-zero if there are errors.
*/
int sqlite3UpsertAnalyze(
  Parse *pParse,     /* The parsing context */
  SrcList *pTabList, /* Table into which we are inserting */
  Upsert *pUpsert    /* The list of ON CONFLICT clauses */
){
  NameContext sNC;
  Upsert *p;
  Table *pTab;
  Index *pIdx;
  int rc = SQLITE_OK;
  int nDoNothing = 0;

  assert( pTabList->nSrc==1 );
  assert( pTabList->a[0].pTab!=0 );
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  pTab = pTabList->a[0].pTab;
  for(p=pUpsert; p; p=p->pUpsertNext){
    if( p->pUpsertTarget==0 ){
      if( p->pUpsertSet ){
        /* This is a MySQL-style ON DUPLICATE KEY clause.  The ON DUPLICATE
        ** KEY clause can only be used if there is exactly one uniqueness
        ** constraint and/or PRIMARY KEY */
        int nUnique = 0;
        for(pIdx = pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( IsUniqueIndex(pIdx) ){
            p->pUpsertIdx = pIdx;
            nUnique++;
          }
        }
        if( pTab->iPKey>=0 ) nUnique++;
        if( nUnique!=0 ){
          sqlite3ErrorMsg(pParse, "ON DUPLICATE KEY may only be used if there "
               "is exactly one UNIQUE or PRIMARY KEY constraint");
          return SQLITE_ERROR;
        }
      }else{
        nDoNothing++;
        if( nDoNothing>1 ){
          sqlite3ErrorMsg(pParse, "multiple unconstrained DO NOTHING clauses");
          return SQLITE_ERROR;
        }
      }
      continue;
    }
    rc = sqlite3ResolveExprListNames(&sNC, p->pUpsertTarget);
    if( rc ) return rc;
  }
  return rc;
}

#endif /* SQLITE_OMIT_UPSERT */

# 2018-04-12
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for UPSERT

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix zipfile

do_execsql_test upsert1-100 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
  SELECT * FROM t1;
} {1 2}
do_execsql_test upsert1-101 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
  SELECT * FROM t1;
} {2 3}
do_execsql_test upsert1-102 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(3,4)
      ON CONFLICT(a) DO NOTHING
      ON CONFLICT(b) DO NOTHING;
  SELECT * FROM t1;
} {3 4}
do_catchsql_test upsert1-110 {
  DELETE FROM t1;
  INSERT INTO t1(a,b) VALUES(3,4)
      ON CONFLICT DO NOTHING
      ON CONFLICT DO NOTHING;
} {1 {multiple unconstrained DO NOTHING clauses}}
do_catchsql_test upsert1-120 {
  INSERT INTO t1(a,b) VALUES(3,4)
      ON DUPLICATE KEY UPDATE a=99;
} {1 {ON DUPLICATE KEY may only be used if there is exactly one UNIQUE or PRIMARY KEY constraint}}

finish_test