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: |
c48f64d8ae2625a19267b02e4a173a07 |
| 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
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 }, |