SQLite

Check-in [e4396c540a]
Login

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

Overview
Comment:Extend the upsert syntax to allow a WHERE clause on the UPDATE.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: e4396c540a22fbc087a01050a32bfad514259d700c2381d7ac912580d7dca00f
User & Date: drh 2018-04-12 17:28:06.890
Context
2018-04-12
19:51
Simple comment clarification. No code or logic changes. (check-in: a26f9c9f89 user: drh tags: upsert)
17:28
Extend the upsert syntax to allow a WHERE clause on the UPDATE. (check-in: e4396c540a 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: c48f64d8ae user: drh tags: upsert)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
4479
4480
4481
4482
4483
4484
4485

4486
4487
4488
4489
4490
4491
4492
4493
4494
4495
4496
4497
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
** Free a list of Upsert objects
*/
void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
  while( p ){
    Upsert *pNext = p->pUpsertNext;
    sqlite3ExprListDelete(db, p->pUpsertTarget);
    sqlite3ExprListDelete(db, p->pUpsertSet);

    sqlite3DbFree(db, p);
    p = pNext;
  }
}
#endif /* SQLITE_OMIT_UPSERT */

#ifndef SQLITE_OMIT_UPSERT
/*
** Duplicate an Upsert object
*/
Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
  Upsert *pNew;
  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 */







>








|


<

<
|
>
|
|
|
<
>









|

|
>







>





>




4479
4480
4481
4482
4483
4484
4485
4486
4487
4488
4489
4490
4491
4492
4493
4494
4495
4496
4497

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
4533
4534
4535
** Free a list of Upsert objects
*/
void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
  while( p ){
    Upsert *pNext = p->pUpsertNext;
    sqlite3ExprListDelete(db, p->pUpsertTarget);
    sqlite3ExprListDelete(db, p->pUpsertSet);
    sqlite3ExprDelete(db, p->pUpsertWhere);
    sqlite3DbFree(db, p);
    p = pNext;
  }
}
#endif /* SQLITE_OMIT_UPSERT */

#ifndef SQLITE_OMIT_UPSERT
/*
** Duplicate an Upsert object.
*/
Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){

  if( p==0 ) return 0;

  return sqlite3UpsertNew(db,
           sqlite3UpsertDup(db, p->pUpsertNext),
           sqlite3ExprListDup(db, p->pUpsertTarget, 0),
           sqlite3ExprListDup(db, p->pUpsertSet, 0),
           sqlite3ExprDup(db, p->pUpsertWhere, 0)

         );
}
#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 this upsert to the end of the new one */
  ExprList *pTarget,     /* Target argument to ON CONFLICT, or NULL */
  ExprList *pSet,        /* UPDATE columns, or NULL for a DO NOTHING */
  Expr *pWhere           /* WHERE clause for the ON CONFLICT UPDATE */
){
  Upsert *pNew;
  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
  if( pNew==0 ){
    sqlite3UpsertDelete(db, pPrior);
    sqlite3ExprListDelete(db, pTarget);
    sqlite3ExprListDelete(db, pSet);
    sqlite3ExprDelete(db, pWhere);
    return 0;
  }else{
    pNew->pUpsertTarget = pTarget;
    pNew->pUpsertSet = pSet;
    pNew->pUpsertNext = pPrior;
    pNew->pUpsertWhere = pWhere;
  }
  return pNew;
}
#endif /* SQLITE_OMIT_UPSERT */
Changes to src/parse.y.
866
867
868
869
870
871
872
873

874
875
876
877
878
879
880
881
882
883
884
885
886
887
{
  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, $$);}







|
>
|
|
|

|

|







866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
{
  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) 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}
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.
2713
2714
2715
2716
2717
2718
2719

2720
2721
2722
2723
2724
2725
2726
** 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 */

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







>







2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
** 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.
**
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
  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







|



|







4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
4283
4284
4285
  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*,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


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