/ Check-in [c48f64d8]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Update the upsert parsing so that it accepts conflict-target labels using the PostgreSQL syntax, and also accepts the MySQL "ON DUPLICATE KEY" syntax.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: c48f64d8ae2625a19267b02e4a173a07766934235ad3052cd9d38f5ecbcbfbc3
User & Date: drh 2018-04-12 15:43:05
Context
2018-04-12
17:28
Extend the upsert syntax to allow a WHERE clause on the UPDATE. check-in: e4396c54 user: drh tags: upsert
15:43
Update the upsert parsing so that it accepts conflict-target labels using the PostgreSQL syntax, and also accepts the MySQL "ON DUPLICATE KEY" syntax. check-in: c48f64d8 user: drh tags: upsert
13:15
Add the Upsert object for holding upsert clause information. check-in: d83eaed5 user: drh tags: upsert
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

4498
4499
4500
4501
4502
4503
4504
4505
4506


























  if( p==0 ) return 0;
  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
  if( pNew==0 ) return 0;
  pNew->pUpsertTarget = sqlite3ExprListDup(db, p->pUpsertTarget, 0);
  pNew->pUpsertSet = sqlite3ExprListDup(db, p->pUpsertSet, 0);
  pNew->pUpsertNext = sqlite3UpsertDup(db, p->pUpsertNext);
  return pNew;
}
#endif /* SQLITE_OMIT_UPSERT */



































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
4498
4499
4500
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
4516
4517
4518
4519
4520
4521
4522
4523
4524
4525
4526
4527
4528
4529
4530
4531
4532
  if( p==0 ) return 0;
  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
  if( pNew==0 ) return 0;
  pNew->pUpsertTarget = sqlite3ExprListDup(db, p->pUpsertTarget, 0);
  pNew->pUpsertSet = sqlite3ExprListDup(db, p->pUpsertSet, 0);
  pNew->pUpsertNext = sqlite3UpsertDup(db, p->pUpsertNext);
  return pNew;
}
#endif /* SQLITE_OMIT_UPSERT */

#ifndef SQLITE_OMIT_UPSERT
/*
** Create a new Upsert object.
*/
Upsert *sqlite3UpsertNew(
  sqlite3 *db,           /* Determines which memory allocator to use */
  Upsert *pPrior,        /* Append new upsert to the end of this one */
  ExprList *pTarget,     /* Target argument to ON CONFLICT, or NULL */
  ExprList *pSet         /* UPDATE columns, or NULL for a DO NOTHING */
){
  Upsert *pNew;
  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
  if( pNew==0 ){
    sqlite3UpsertDelete(db, pPrior);
    sqlite3ExprListDelete(db, pTarget);
    sqlite3ExprListDelete(db, pSet);
    return 0;
  }else{
    pNew->pUpsertTarget = pTarget;
    pNew->pUpsertSet = pSet;
    pNew->pUpsertNext = pPrior;
  }
  return pNew;
}
#endif /* SQLITE_OMIT_UPSERT */

Changes to src/parse.y.

201
202
203
204
205
206
207
208

209
210
211
212
213
214
215
...
865
866
867
868
869
870
871
872





873

874
875
876
877
878
879
880

// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
  CONFLICT DATABASE DEFERRED DESC DETACH DO EACH END EXCLUSIVE EXPLAIN FAIL FOR

  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
  ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif SQLITE_OMIT_COMPOUND_SELECT
  REINDEX RENAME CTIME_KW IF
................................................................................
{
  sqlite3Insert(pParse, X, 0, F, R, 0);
}

%type upsert {Upsert*}
%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
upsert(A) ::= . { A = 0; }
upsert(A) ::= ON CONFLICT DO UPDATE SET setlist. { A = 0; }





upsert(A) ::= ON CONFLICT DO NOTHING. { A = 0; }


%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}

%type idlist_opt {IdList*}
%destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}







|
>







 







|
>
>
>
>
>
|
>







201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
...
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887

// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
  CONFLICT DATABASE DEFERRED DESC DETACH DO DUPLICATE
  EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
  QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
  ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif SQLITE_OMIT_COMPOUND_SELECT
  REINDEX RENAME CTIME_KW IF
................................................................................
{
  sqlite3Insert(pParse, X, 0, F, R, 0);
}

%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).
              { A = sqlite3UpsertNew(pParse->db,X,Y,Z); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON DUPLIATE KEY UPDATE setlist(Z).
              { A = sqlite3UpsertNew(pParse->db,X,0,Z); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON CONFLICT LP sortlist(Y) RP DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,X,Y,0); /*X-overwrites-A*/ }
upsert(A) ::= upsert(X) ON CONFLICT DO NOTHING.
              { A = sqlite3UpsertNew(pParse->db,X,0,0); /*X-overwrites-A*/ }

%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}

%type idlist_opt {IdList*}
%destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}

Changes to src/sqliteInt.h.

4266
4267
4268
4269
4270
4271
4272

4273
4274
4275

4276
4277
4278
4279
4280
4281
4282
4283
4284
  void sqlite3WithDelete(sqlite3*,With*);
  void sqlite3WithPush(Parse*, With*, u8);
#else
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)
#endif
#ifndef SQLITE_OMIT_UPSERT

  void sqlite3UpsertDelete(sqlite3*,Upsert*);
  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
#else

#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y) ((Upsert*)0)
#endif


/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In







>



>

|







4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
4283
4284
4285
4286
  void sqlite3WithDelete(sqlite3*,With*);
  void sqlite3WithPush(Parse*, With*, u8);
#else
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(x,y)
#endif
#ifndef SQLITE_OMIT_UPSERT
  Upsert *sqlite3UpsertNew(sqlite3*,Upsert*,ExprList*,ExprList*);
  void sqlite3UpsertDelete(sqlite3*,Upsert*);
  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
#else
#define sqlite3UpsertNew(x,y,z)   ((Upsert*)0)
#define sqlite3UpsertDelete(x,y)
#define sqlite3UpsertDup(x,y)     ((Upsert*)0)
#endif


/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In

Changes to tool/mkkeywordhash.c.

139
140
141
142
143
144
145





146
147
148
149
150
151
152
...
182
183
184
185
186
187
188
189
190

191
192
193
194
195
196
197
...
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
#  define AUTOVACUUM 0x00020000
#endif
#ifdef SQLITE_OMIT_CTE
#  define CTE        0
#else
#  define CTE        0x00040000
#endif






/*
** These are the keywords
*/
static Keyword aKeywordTable[] = {
  { "ABORT",            "TK_ABORT",        CONFLICT|TRIGGER       },
  { "ACTION",           "TK_ACTION",       FKEY                   },
................................................................................
  { "DEFAULT",          "TK_DEFAULT",      ALWAYS                 },
  { "DEFERRED",         "TK_DEFERRED",     ALWAYS                 },
  { "DEFERRABLE",       "TK_DEFERRABLE",   FKEY                   },
  { "DELETE",           "TK_DELETE",       ALWAYS                 },
  { "DESC",             "TK_DESC",         ALWAYS                 },
  { "DETACH",           "TK_DETACH",       ATTACH                 },
  { "DISTINCT",         "TK_DISTINCT",     ALWAYS                 },
  { "DO",               "TK_DO",           ALWAYS                 },
  { "DROP",             "TK_DROP",         ALWAYS                 },

  { "END",              "TK_END",          ALWAYS                 },
  { "EACH",             "TK_EACH",         TRIGGER                },
  { "ELSE",             "TK_ELSE",         ALWAYS                 },
  { "ESCAPE",           "TK_ESCAPE",       ALWAYS                 },
  { "EXCEPT",           "TK_EXCEPT",       COMPOUND               },
  { "EXCLUSIVE",        "TK_EXCLUSIVE",    ALWAYS                 },
  { "EXISTS",           "TK_EXISTS",       ALWAYS                 },
................................................................................
  { "LEFT",             "TK_JOIN_KW",      ALWAYS                 },
  { "LIKE",             "TK_LIKE_KW",      ALWAYS                 },
  { "LIMIT",            "TK_LIMIT",        ALWAYS                 },
  { "MATCH",            "TK_MATCH",        ALWAYS                 },
  { "NATURAL",          "TK_JOIN_KW",      ALWAYS                 },
  { "NO",               "TK_NO",           FKEY                   },
  { "NOT",              "TK_NOT",          ALWAYS                 },
  { "NOTHING",          "TK_NOTHING",      ALWAYS                 },
  { "NOTNULL",          "TK_NOTNULL",      ALWAYS                 },
  { "NULL",             "TK_NULL",         ALWAYS                 },
  { "OF",               "TK_OF",           ALWAYS                 },
  { "OFFSET",           "TK_OFFSET",       ALWAYS                 },
  { "ON",               "TK_ON",           ALWAYS                 },
  { "OR",               "TK_OR",           ALWAYS                 },
  { "ORDER",            "TK_ORDER",        ALWAYS                 },







>
>
>
>
>







 







|

>







 







|







139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
...
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
...
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
#  define AUTOVACUUM 0x00020000
#endif
#ifdef SQLITE_OMIT_CTE
#  define CTE        0
#else
#  define CTE        0x00040000
#endif
#ifdef SQLITE_OMIT_UPSERT
#  define UPSERT     0
#else
#  define UPSERT     0x00080000
#endif

/*
** These are the keywords
*/
static Keyword aKeywordTable[] = {
  { "ABORT",            "TK_ABORT",        CONFLICT|TRIGGER       },
  { "ACTION",           "TK_ACTION",       FKEY                   },
................................................................................
  { "DEFAULT",          "TK_DEFAULT",      ALWAYS                 },
  { "DEFERRED",         "TK_DEFERRED",     ALWAYS                 },
  { "DEFERRABLE",       "TK_DEFERRABLE",   FKEY                   },
  { "DELETE",           "TK_DELETE",       ALWAYS                 },
  { "DESC",             "TK_DESC",         ALWAYS                 },
  { "DETACH",           "TK_DETACH",       ATTACH                 },
  { "DISTINCT",         "TK_DISTINCT",     ALWAYS                 },
  { "DO",               "TK_DO",           UPSERT                 },
  { "DROP",             "TK_DROP",         ALWAYS                 },
  { "DUPLICATE",        "TK_DUPLICATE",    UPSERT                 },
  { "END",              "TK_END",          ALWAYS                 },
  { "EACH",             "TK_EACH",         TRIGGER                },
  { "ELSE",             "TK_ELSE",         ALWAYS                 },
  { "ESCAPE",           "TK_ESCAPE",       ALWAYS                 },
  { "EXCEPT",           "TK_EXCEPT",       COMPOUND               },
  { "EXCLUSIVE",        "TK_EXCLUSIVE",    ALWAYS                 },
  { "EXISTS",           "TK_EXISTS",       ALWAYS                 },
................................................................................
  { "LEFT",             "TK_JOIN_KW",      ALWAYS                 },
  { "LIKE",             "TK_LIKE_KW",      ALWAYS                 },
  { "LIMIT",            "TK_LIMIT",        ALWAYS                 },
  { "MATCH",            "TK_MATCH",        ALWAYS                 },
  { "NATURAL",          "TK_JOIN_KW",      ALWAYS                 },
  { "NO",               "TK_NO",           FKEY                   },
  { "NOT",              "TK_NOT",          ALWAYS                 },
  { "NOTHING",          "TK_NOTHING",      UPSERT                 },
  { "NOTNULL",          "TK_NOTNULL",      ALWAYS                 },
  { "NULL",             "TK_NULL",         ALWAYS                 },
  { "OF",               "TK_OF",           ALWAYS                 },
  { "OFFSET",           "TK_OFFSET",       ALWAYS                 },
  { "ON",               "TK_ON",           ALWAYS                 },
  { "OR",               "TK_OR",           ALWAYS                 },
  { "ORDER",            "TK_ORDER",        ALWAYS                 },