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: |
e4396c540a22fbc087a01050a32bfad5 |
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
Changes to src/build.c.
︙ | ︙ | |||
4479 4480 4481 4482 4483 4484 4485 4486 4487 4488 4489 4490 4491 4492 4493 | ** 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 /* | > | < < | > | | | < > | | > > > | 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 | { sqlite3Insert(pParse, X, 0, F, R, 0); } %type upsert {Upsert*} %destructor upsert {sqlite3UpsertDelete(pParse->db,$$);} upsert(A) ::= . { A = 0; } | | > | | | | | | 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 | void sqlite3WithDelete(sqlite3*,With*); void sqlite3WithPush(Parse*, With*, u8); #else #define sqlite3WithPush(x,y,z) #define sqlite3WithDelete(x,y) #endif #ifndef SQLITE_OMIT_UPSERT | | | | 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 |
︙ | ︙ |