/ Check-in [80969643]
Login

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

Overview
Comment:Begin adding upsert logic. This is an incremental check-in.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256:809696434097e62e8ef486c7478b5eb62c0cf1342522a5584939fade82821410
User & Date: drh 2018-04-13 01:15:09
Context
2018-04-13
13:06
Back off of the extended upsert syntax that allows multiple ON CONFLICT clauses. The syntax now is exactly as in PostgreSQL and MySQL. Add support for WHERE clauses on the conflict-target phrase, for partial indexes. check-in: 2c1b1987 user: drh tags: upsert
01:15
Begin adding upsert logic. This is an incremental check-in. check-in: 80969643 user: drh tags: upsert
2018-04-12
21:42
Break out the upsert code into a separate source file. check-in: 389806b0 user: drh tags: upsert
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

800
801
802
803
804
805
806







807
808
809
810
811
812
813
....
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
....
1238
1239
1240
1241
1242
1243
1244
1245

1246
1247
1248
1249
1250
1251
1252
    }
    for(i=0, pIdx=pTab->pIndex; i<nIdx; pIdx=pIdx->pNext, i++){
      assert( pIdx );
      aRegIdx[i] = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }
  }








  /* 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
      );
      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 */

){
  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 */







>
>
>
>
>
>
>







 







|







 







|
>







800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
....
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
....
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
    }
    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 */

Changes to src/parse.y.

869
870
871
872
873
874
875
876
877
878
879
880
881
882
883

%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 DUPLIATE 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}







|







869
870
871
872
873
874
875
876
877
878
879
880
881
882
883

%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}

Changes to src/sqliteInt.h.

2709
2710
2711
2712
2713
2714
2715















2716
2717
2718
2719
2720

2721
2722
2723
2724
2725
2726
2727
....
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
3883
3884
3885
3886
3887
....
4270
4271
4272
4273
4274
4275
4276

4277
4278
4279
4280
4281
4282
4283
#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.















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

  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*);
#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*);

#else
#define sqlite3UpsertNew(x,y,z,w) ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)     ((Upsert*)0)
#endif









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





>







 







|







 







>







2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
....
3889
3890
3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
....
4286
4287
4288
4289
4290
4291
4292
4293
4294
4295
4296
4297
4298
4299
4300
#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


Changes to src/update.c.

622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
  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);

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

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







|







622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
  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.  */

Changes to src/upsert.c.

64
65
66
67
68
69
70
71


























































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



























































#endif /* SQLITE_OMIT_UPSERT */








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

64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
    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 */

Added test/upsert1.test.





























































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# 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