Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -486,11 +486,12 @@ void sqlite3Insert( Parse *pParse, /* Parser context */ SrcList *pTabList, /* Name of table into which we are inserting */ Select *pSelect, /* A SELECT statement to use as the data source */ IdList *pColumn, /* Column names corresponding to IDLIST. */ - int onError /* How to handle constraint errors */ + int onError, /* How to handle constraint errors */ + ExprList *pUpsert /* Upsert values */ ){ sqlite3 *db; /* The main database structure */ Table *pTab; /* The table to insert into. aka TABLE */ int i, j; /* Loop counters */ Vdbe *v; /* Generate code into this virtual machine */ @@ -524,10 +525,15 @@ #ifndef SQLITE_OMIT_TRIGGER int isView; /* True if attempting to insert into a view */ Trigger *pTrigger; /* List of triggers on pTab, if required */ int tmask; /* Mask of trigger times */ #endif + + /* The conflict resolution type is always OE_Update or OE_Replace when + ** there is an upsert clause */ + assert( onError==OE_Update || pUpsert==0 ); + assert( OE_Update==OE_Replace ); db = pParse->db; if( pParse->nErr || db->mallocFailed ){ goto insert_cleanup; } @@ -1072,10 +1078,11 @@ } insert_cleanup: sqlite3SrcListDelete(db, pTabList); sqlite3ExprListDelete(db, pList); + sqlite3ExprListDelete(db, pUpsert); sqlite3SelectDelete(db, pSelect); sqlite3IdListDelete(db, pColumn); sqlite3DbFree(db, aRegIdx); } Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -96,10 +96,26 @@ ** ** Then the "b" IdList records the list "a,b,c". */ struct TrigEvent { int a; IdList * b; }; +/* +** An instance of this object holds the argument of the ON CONFLICT +** clause of an UPSERT. +** +** The ON CONFLICT clause takes three forms, identified by the Upsert.e +** field: +** +** OE_None: No ON CONFLICT clause +** OE_Ignore: ON CONFLICT DO NOTHING +** OE_Update: ON CONFLICT DO UPDATE ... +*/ +struct Upsert { + ExprList *p; /* column=expr entries for the UPDATE. Or NULL */ + int e; /* OE_None, OE_Replace, or OE_Ignore */ +}; + /* ** Disable lookaside memory allocation for objects that might be ** shared across database connections. */ static void disableLookaside(Parse *pParse){ @@ -203,11 +219,11 @@ // 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 EACH END EXCLUSIVE EXPLAIN FAIL FOR + 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 @@ -840,22 +856,52 @@ A = sqlite3ExprListAppendVector(pParse, 0, X, Y); } ////////////////////////// The INSERT command ///////////////////////////////// // -cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S) upsert. { +cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S) + upsert(U). { sqlite3WithPush(pParse, W, 1); - sqlite3Insert(pParse, X, S, F, R); + sqlite3Insert(pParse, X, S, F, upsertType(pParse, R, U.e), U.p); } cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES. { sqlite3WithPush(pParse, W, 1); - sqlite3Insert(pParse, X, 0, F, R); + sqlite3Insert(pParse, X, 0, F, R, 0); } -upsert ::= . -upsert ::= ON CONFLICT SET setlist. +%type upsert {struct Upsert} +%destructor upsert {sqlite3ExprListDelete(pParse->db,$$.p);} +upsert(A) ::= . { + A.p = 0; + A.e = OE_None; +} +upsert(A) ::= ON CONFLICT DO UPDATE SET setlist(X). { + A.p = X; /*A-overwrites-X*/ + A.e = OE_Update; +} +upsert(A) ::= ON CONFLICT DO NOTHING. { + A.p = 0; + A.e = OE_Ignore; +} + +%include { + /* Compute and return the correct conflict resolution strategy for an + ** INSERT statement. If the statement begins with REPLACE or with + ** INSERT OR, and it contains an ON CONFLICT clause, throw an error. + */ + static int upsertType(Parse *pParse, int orconf, int upsertType){ + if( upsertType!=OE_None ){ + if( orconf!=OE_Default ){ + sqlite3ErrorMsg(pParse, "ON CONFLICT clause not allowed"); + } + return upsertType; + }else{ + return orconf; + } + } +} %type insert_cmd {int} insert_cmd(A) ::= INSERT orconf(R). {A = R;} insert_cmd(A) ::= REPLACE. {A = OE_Replace;} @@ -1398,13 +1444,14 @@ UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E). {A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R, B.z, E);} // INSERT trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO - trnm(X) idlist_opt(F) select(S) scanpt(Z). - {A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,B,Z);/*A-overwrites-R*/} - + trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). { + A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,upsertType(pParse,R,U.e), + U.p,B,Z);/*A-overwrites-R*/ +} // DELETE trigger_cmd(A) ::= DELETE(B) FROM trnm(X) tridxby where_opt(Y) scanpt(E). {A = sqlite3TriggerDeleteStep(pParse->db, &X, Y, B.z, E);} // SELECT Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2044,10 +2044,11 @@ #define OE_Rollback 1 /* Fail the operation and rollback the transaction */ #define OE_Abort 2 /* Back out changes but do no rollback transaction */ #define OE_Fail 3 /* Stop the operation but leave all prior changes */ #define OE_Ignore 4 /* Ignore the error. Do not do the INSERT or UPDATE */ #define OE_Replace 5 /* Delete existing record, then do INSERT or UPDATE */ +#define OE_Update 5 /* An UPSERT. Same value as OE_Replace. */ #define OE_Restrict 6 /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */ #define OE_SetNull 7 /* Set the foreign key value to NULL */ #define OE_SetDflt 8 /* Set the foreign key value to its default */ #define OE_Cascade 9 /* Cascade the changes */ @@ -3205,11 +3206,11 @@ u8 orconf; /* OE_Rollback etc. */ Trigger *pTrig; /* The trigger that this step is a part of */ Select *pSelect; /* SELECT statement or RHS of INSERT INTO SELECT ... */ char *zTarget; /* Target table for DELETE, UPDATE, INSERT */ Expr *pWhere; /* The WHERE clause for DELETE or UPDATE steps */ - ExprList *pExprList; /* SET clause for UPDATE. */ + ExprList *pExprList; /* SET clause for UPDATE or UPSERT. */ IdList *pIdList; /* Column names for INSERT */ char *zSpan; /* Original SQL text of this command */ TriggerStep *pNext; /* Next in the link-list */ TriggerStep *pLast; /* Last element in link-list. Valid for 1st elem only */ }; @@ -3738,11 +3739,11 @@ void sqlite3AutoincrementEnd(Parse *pParse); #else # define sqlite3AutoincrementBegin(X) # define sqlite3AutoincrementEnd(X) #endif -void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int); +void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, ExprList*); void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*); IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*); int sqlite3IdListIndex(IdList*,const char*); SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int); SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*); @@ -3914,11 +3915,12 @@ void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*); void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*); TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*, const char*,const char*); TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*, - Select*,u8,const char*,const char*); + Select*,u8,ExprList*, + const char*,const char*); TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8, const char*,const char*); TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*, const char*,const char*); void sqlite3DeleteTrigger(sqlite3*, Trigger*); Index: src/trigger.c ================================================================== --- src/trigger.c +++ src/trigger.c @@ -414,10 +414,11 @@ sqlite3 *db, /* The database connection */ Token *pTableName, /* Name of the table into which we insert */ IdList *pColumn, /* List of columns in pTableName to insert into */ Select *pSelect, /* A SELECT statement that supplies values */ u8 orconf, /* The conflict algorithm (OE_Abort, OE_Replace, etc.) */ + ExprList *pUpsert, /* Upsert values */ const char *zStart, /* Start of SQL text */ const char *zEnd /* End of SQL text */ ){ TriggerStep *pTriggerStep; @@ -428,10 +429,11 @@ pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE); pTriggerStep->pIdList = pColumn; pTriggerStep->orconf = orconf; }else{ sqlite3IdListDelete(db, pColumn); + sqlite3ExprListDelete(db, pUpsert); } sqlite3SelectDelete(db, pSelect); return pTriggerStep; } @@ -753,11 +755,12 @@ case TK_INSERT: { sqlite3Insert(pParse, targetSrcList(pParse, pStep), sqlite3SelectDup(db, pStep->pSelect, 0), sqlite3IdListDup(db, pStep->pIdList), - pParse->eOrconf + pStep->pExprList ? OE_Update : pParse->eOrconf, + sqlite3ExprListDup(db, pStep->pExprList, 0) ); break; } case TK_DELETE: { sqlite3DeleteFrom(pParse, Index: tool/mkkeywordhash.c ================================================================== --- tool/mkkeywordhash.c +++ tool/mkkeywordhash.c @@ -184,10 +184,11 @@ { "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 }, @@ -224,10 +225,11 @@ { "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 },