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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | upsert |
| Files: | files | file ages | folders |
| SHA3-256: |
e4396c540a22fbc087a01050a32bfad5 |
| User & Date: | drh 2018-04-12 17:28:06 |
Context
|
2018-04-12
| ||
| 19:51 | Simple comment clarification. No code or logic changes. (check-in: a26f9c9f user: drh tags: upsert) | |
| 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) | |
Changes
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
....
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
4283
4284
|
** 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.
**
................................................................................
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
|
>
|
|
|
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
....
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
4283
4284
4285
|
** 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.
**
................................................................................
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
|