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

Changes to src/insert.c.

   484    484   **      D: cleanup
   485    485   */
   486    486   void sqlite3Insert(
   487    487     Parse *pParse,        /* Parser context */
   488    488     SrcList *pTabList,    /* Name of table into which we are inserting */
   489    489     Select *pSelect,      /* A SELECT statement to use as the data source */
   490    490     IdList *pColumn,      /* Column names corresponding to IDLIST. */
   491         -  int onError           /* How to handle constraint errors */
          491  +  int onError,          /* How to handle constraint errors */
          492  +  ExprList *pUpsert     /* Upsert values */
   492    493   ){
   493    494     sqlite3 *db;          /* The main database structure */
   494    495     Table *pTab;          /* The table to insert into.  aka TABLE */
   495    496     int i, j;             /* Loop counters */
   496    497     Vdbe *v;              /* Generate code into this virtual machine */
   497    498     Index *pIdx;          /* For looping over indices of the table */
   498    499     int nColumn;          /* Number of columns in the data */
................................................................................
   522    523     int *aRegIdx = 0;     /* One register allocated to each index */
   523    524   
   524    525   #ifndef SQLITE_OMIT_TRIGGER
   525    526     int isView;                 /* True if attempting to insert into a view */
   526    527     Trigger *pTrigger;          /* List of triggers on pTab, if required */
   527    528     int tmask;                  /* Mask of trigger times */
   528    529   #endif
          530  +
          531  +  /* The conflict resolution type is always OE_Update or OE_Replace when
          532  +  ** there is an upsert clause */
          533  +  assert( onError==OE_Update || pUpsert==0 );
          534  +  assert( OE_Update==OE_Replace );
   529    535   
   530    536     db = pParse->db;
   531    537     if( pParse->nErr || db->mallocFailed ){
   532    538       goto insert_cleanup;
   533    539     }
   534    540     dest.iSDParm = 0;  /* Suppress a harmless compiler warning */
   535    541   
................................................................................
  1070   1076       sqlite3VdbeSetNumCols(v, 1);
  1071   1077       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
  1072   1078     }
  1073   1079   
  1074   1080   insert_cleanup:
  1075   1081     sqlite3SrcListDelete(db, pTabList);
  1076   1082     sqlite3ExprListDelete(db, pList);
         1083  +  sqlite3ExprListDelete(db, pUpsert);
  1077   1084     sqlite3SelectDelete(db, pSelect);
  1078   1085     sqlite3IdListDelete(db, pColumn);
  1079   1086     sqlite3DbFree(db, aRegIdx);
  1080   1087   }
  1081   1088   
  1082   1089   /* Make sure "isView" and other macros defined above are undefined. Otherwise
  1083   1090   ** they may interfere with compilation of other functions in this file

Changes to src/parse.y.

    94     94   **
    95     95   **      UPDATE ON (a,b,c)
    96     96   **
    97     97   ** Then the "b" IdList records the list "a,b,c".
    98     98   */
    99     99   struct TrigEvent { int a; IdList * b; };
   100    100   
          101  +/*
          102  +** An instance of this object holds the argument of the ON CONFLICT
          103  +** clause of an UPSERT.
          104  +**
          105  +** The ON CONFLICT clause takes three forms, identified by the Upsert.e
          106  +** field:
          107  +**
          108  +**   OE_None:      No ON CONFLICT clause
          109  +**   OE_Ignore:    ON CONFLICT DO NOTHING
          110  +**   OE_Update:    ON CONFLICT DO UPDATE ...
          111  +*/
          112  +struct Upsert {
          113  +  ExprList *p;    /* column=expr entries for the UPDATE.  Or NULL */
          114  +  int e;          /* OE_None, OE_Replace, or OE_Ignore */
          115  +};
          116  +
   101    117   /*
   102    118   ** Disable lookaside memory allocation for objects that might be
   103    119   ** shared across database connections.
   104    120   */
   105    121   static void disableLookaside(Parse *pParse){
   106    122     pParse->disableLookaside++;
   107    123     pParse->db->lookaside.bDisable++;
................................................................................
   201    217   
   202    218   // The following directive causes tokens ABORT, AFTER, ASC, etc. to
   203    219   // fallback to ID if they will not parse as their original value.
   204    220   // This obviates the need for the "id" nonterminal.
   205    221   //
   206    222   %fallback ID
   207    223     ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
   208         -  CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
          224  +  CONFLICT DATABASE DEFERRED DESC DETACH DO EACH END EXCLUSIVE EXPLAIN FAIL FOR
   209    225     IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
   210    226     QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
   211    227     ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
   212    228   %ifdef SQLITE_OMIT_COMPOUND_SELECT
   213    229     EXCEPT INTERSECT UNION
   214    230   %endif SQLITE_OMIT_COMPOUND_SELECT
   215    231     REINDEX RENAME CTIME_KW IF
................................................................................
   838    854   }
   839    855   setlist(A) ::= LP idlist(X) RP EQ expr(Y). {
   840    856     A = sqlite3ExprListAppendVector(pParse, 0, X, Y);
   841    857   }
   842    858   
   843    859   ////////////////////////// The INSERT command /////////////////////////////////
   844    860   //
   845         -cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S) upsert. {
          861  +cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S)
          862  +        upsert(U). {
   846    863     sqlite3WithPush(pParse, W, 1);
   847         -  sqlite3Insert(pParse, X, S, F, R);
          864  +  sqlite3Insert(pParse, X, S, F, upsertType(pParse, R, U.e), U.p);
   848    865   }
   849    866   cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
   850    867   {
   851    868     sqlite3WithPush(pParse, W, 1);
   852         -  sqlite3Insert(pParse, X, 0, F, R);
          869  +  sqlite3Insert(pParse, X, 0, F, R, 0);
   853    870   }
   854    871   
   855         -upsert ::= .
   856         -upsert ::= ON CONFLICT SET setlist.
          872  +%type upsert {struct Upsert}
          873  +%destructor upsert {sqlite3ExprListDelete(pParse->db,$$.p);}
          874  +upsert(A) ::= . {
          875  +  A.p = 0;
          876  +  A.e = OE_None;
          877  +}
          878  +upsert(A) ::= ON CONFLICT DO UPDATE SET setlist(X). {
          879  +  A.p = X;  /*A-overwrites-X*/
          880  +  A.e = OE_Update;
          881  +}
          882  +upsert(A) ::= ON CONFLICT DO NOTHING. {
          883  +  A.p = 0;
          884  +  A.e = OE_Ignore;
          885  +}
          886  +
          887  +%include {
          888  +  /* Compute and return the correct conflict resolution strategy for an
          889  +  ** INSERT statement.  If the statement begins with REPLACE or with
          890  +  ** INSERT OR, and it contains an ON CONFLICT clause, throw an error.
          891  +  */
          892  +  static int upsertType(Parse *pParse, int orconf, int upsertType){
          893  +    if( upsertType!=OE_None ){
          894  +      if( orconf!=OE_Default ){
          895  +        sqlite3ErrorMsg(pParse, "ON CONFLICT clause not allowed");
          896  +      }
          897  +      return upsertType;
          898  +    }else{
          899  +      return orconf;
          900  +    }
          901  +  }
          902  +}
   857    903   
   858    904   %type insert_cmd {int}
   859    905   insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
   860    906   insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}
   861    907   
   862    908   %type idlist_opt {IdList*}
   863    909   %destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}
................................................................................
  1396   1442   // UPDATE 
  1397   1443   trigger_cmd(A) ::=
  1398   1444      UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E).  
  1399   1445      {A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R, B.z, E);}
  1400   1446   
  1401   1447   // INSERT
  1402   1448   trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO
  1403         -                      trnm(X) idlist_opt(F) select(S) scanpt(Z).
  1404         -   {A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,B,Z);/*A-overwrites-R*/}
  1405         -
         1449  +                      trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). {
         1450  +   A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,upsertType(pParse,R,U.e),
         1451  +                                U.p,B,Z);/*A-overwrites-R*/
         1452  +}
  1406   1453   // DELETE
  1407   1454   trigger_cmd(A) ::= DELETE(B) FROM trnm(X) tridxby where_opt(Y) scanpt(E).
  1408   1455      {A = sqlite3TriggerDeleteStep(pParse->db, &X, Y, B.z, E);}
  1409   1456   
  1410   1457   // SELECT
  1411   1458   trigger_cmd(A) ::= scanpt(B) select(X) scanpt(E).
  1412   1459      {A = sqlite3TriggerSelectStep(pParse->db, X, B, E); /*A-overwrites-X*/}

Changes to src/sqliteInt.h.

  2042   2042   */
  2043   2043   #define OE_None     0   /* There is no constraint to check */
  2044   2044   #define OE_Rollback 1   /* Fail the operation and rollback the transaction */
  2045   2045   #define OE_Abort    2   /* Back out changes but do no rollback transaction */
  2046   2046   #define OE_Fail     3   /* Stop the operation but leave all prior changes */
  2047   2047   #define OE_Ignore   4   /* Ignore the error. Do not do the INSERT or UPDATE */
  2048   2048   #define OE_Replace  5   /* Delete existing record, then do INSERT or UPDATE */
         2049  +#define OE_Update   5   /* An UPSERT.  Same value as OE_Replace. */
  2049   2050   
  2050   2051   #define OE_Restrict 6   /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
  2051   2052   #define OE_SetNull  7   /* Set the foreign key value to NULL */
  2052   2053   #define OE_SetDflt  8   /* Set the foreign key value to its default */
  2053   2054   #define OE_Cascade  9   /* Cascade the changes */
  2054   2055   
  2055   2056   #define OE_Default  10  /* Do whatever the default action is */
................................................................................
  3203   3204   struct TriggerStep {
  3204   3205     u8 op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
  3205   3206     u8 orconf;           /* OE_Rollback etc. */
  3206   3207     Trigger *pTrig;      /* The trigger that this step is a part of */
  3207   3208     Select *pSelect;     /* SELECT statement or RHS of INSERT INTO SELECT ... */
  3208   3209     char *zTarget;       /* Target table for DELETE, UPDATE, INSERT */
  3209   3210     Expr *pWhere;        /* The WHERE clause for DELETE or UPDATE steps */
  3210         -  ExprList *pExprList; /* SET clause for UPDATE. */
         3211  +  ExprList *pExprList; /* SET clause for UPDATE or UPSERT. */
  3211   3212     IdList *pIdList;     /* Column names for INSERT */
  3212   3213     char *zSpan;         /* Original SQL text of this command */
  3213   3214     TriggerStep *pNext;  /* Next in the link-list */
  3214   3215     TriggerStep *pLast;  /* Last element in link-list. Valid for 1st elem only */
  3215   3216   };
  3216   3217   
  3217   3218   /*
................................................................................
  3736   3737   #ifndef SQLITE_OMIT_AUTOINCREMENT
  3737   3738     void sqlite3AutoincrementBegin(Parse *pParse);
  3738   3739     void sqlite3AutoincrementEnd(Parse *pParse);
  3739   3740   #else
  3740   3741   # define sqlite3AutoincrementBegin(X)
  3741   3742   # define sqlite3AutoincrementEnd(X)
  3742   3743   #endif
  3743         -void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int);
         3744  +void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, ExprList*);
  3744   3745   void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*);
  3745   3746   IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
  3746   3747   int sqlite3IdListIndex(IdList*,const char*);
  3747   3748   SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int);
  3748   3749   SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
  3749   3750   SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,
  3750   3751                                         Token*, Select*, Expr*, IdList*);
................................................................................
  3912   3913                               int, int, int);
  3913   3914     void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int);
  3914   3915     void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
  3915   3916     void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*);
  3916   3917     TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*,
  3917   3918                                           const char*,const char*);
  3918   3919     TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
  3919         -                                        Select*,u8,const char*,const char*);
         3920  +                                        Select*,u8,ExprList*,
         3921  +                                        const char*,const char*);
  3920   3922     TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8,
  3921   3923                                           const char*,const char*);
  3922   3924     TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*,
  3923   3925                                           const char*,const char*);
  3924   3926     void sqlite3DeleteTrigger(sqlite3*, Trigger*);
  3925   3927     void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
  3926   3928     u32 sqlite3TriggerColmask(Parse*,Trigger*,ExprList*,int,int,Table*,int);

Changes to src/trigger.c.

   412    412   */
   413    413   TriggerStep *sqlite3TriggerInsertStep(
   414    414     sqlite3 *db,        /* The database connection */
   415    415     Token *pTableName,  /* Name of the table into which we insert */
   416    416     IdList *pColumn,    /* List of columns in pTableName to insert into */
   417    417     Select *pSelect,    /* A SELECT statement that supplies values */
   418    418     u8 orconf,          /* The conflict algorithm (OE_Abort, OE_Replace, etc.) */
          419  +  ExprList *pUpsert,  /* Upsert values */
   419    420     const char *zStart, /* Start of SQL text */
   420    421     const char *zEnd    /* End of SQL text */
   421    422   ){
   422    423     TriggerStep *pTriggerStep;
   423    424   
   424    425     assert(pSelect != 0 || db->mallocFailed);
   425    426   
................................................................................
   426    427     pTriggerStep = triggerStepAllocate(db, TK_INSERT, pTableName, zStart, zEnd);
   427    428     if( pTriggerStep ){
   428    429       pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
   429    430       pTriggerStep->pIdList = pColumn;
   430    431       pTriggerStep->orconf = orconf;
   431    432     }else{
   432    433       sqlite3IdListDelete(db, pColumn);
          434  +    sqlite3ExprListDelete(db, pUpsert);
   433    435     }
   434    436     sqlite3SelectDelete(db, pSelect);
   435    437   
   436    438     return pTriggerStep;
   437    439   }
   438    440   
   439    441   /*
................................................................................
   751    753           break;
   752    754         }
   753    755         case TK_INSERT: {
   754    756           sqlite3Insert(pParse, 
   755    757             targetSrcList(pParse, pStep),
   756    758             sqlite3SelectDup(db, pStep->pSelect, 0), 
   757    759             sqlite3IdListDup(db, pStep->pIdList), 
   758         -          pParse->eOrconf
          760  +          pStep->pExprList ? OE_Update : pParse->eOrconf,
          761  +          sqlite3ExprListDup(db, pStep->pExprList, 0)
   759    762           );
   760    763           break;
   761    764         }
   762    765         case TK_DELETE: {
   763    766           sqlite3DeleteFrom(pParse, 
   764    767             targetSrcList(pParse, pStep),
   765    768             sqlite3ExprDup(db, pStep->pWhere, 0), 0, 0

Changes to tool/mkkeywordhash.c.

   182    182     { "DEFAULT",          "TK_DEFAULT",      ALWAYS                 },
   183    183     { "DEFERRED",         "TK_DEFERRED",     ALWAYS                 },
   184    184     { "DEFERRABLE",       "TK_DEFERRABLE",   FKEY                   },
   185    185     { "DELETE",           "TK_DELETE",       ALWAYS                 },
   186    186     { "DESC",             "TK_DESC",         ALWAYS                 },
   187    187     { "DETACH",           "TK_DETACH",       ATTACH                 },
   188    188     { "DISTINCT",         "TK_DISTINCT",     ALWAYS                 },
          189  +  { "DO",               "TK_DO",           ALWAYS                 },
   189    190     { "DROP",             "TK_DROP",         ALWAYS                 },
   190    191     { "END",              "TK_END",          ALWAYS                 },
   191    192     { "EACH",             "TK_EACH",         TRIGGER                },
   192    193     { "ELSE",             "TK_ELSE",         ALWAYS                 },
   193    194     { "ESCAPE",           "TK_ESCAPE",       ALWAYS                 },
   194    195     { "EXCEPT",           "TK_EXCEPT",       COMPOUND               },
   195    196     { "EXCLUSIVE",        "TK_EXCLUSIVE",    ALWAYS                 },
................................................................................
   222    223     { "LEFT",             "TK_JOIN_KW",      ALWAYS                 },
   223    224     { "LIKE",             "TK_LIKE_KW",      ALWAYS                 },
   224    225     { "LIMIT",            "TK_LIMIT",        ALWAYS                 },
   225    226     { "MATCH",            "TK_MATCH",        ALWAYS                 },
   226    227     { "NATURAL",          "TK_JOIN_KW",      ALWAYS                 },
   227    228     { "NO",               "TK_NO",           FKEY                   },
   228    229     { "NOT",              "TK_NOT",          ALWAYS                 },
          230  +  { "NOTHING",          "TK_NOTHING",      ALWAYS                 },
   229    231     { "NOTNULL",          "TK_NOTNULL",      ALWAYS                 },
   230    232     { "NULL",             "TK_NULL",         ALWAYS                 },
   231    233     { "OF",               "TK_OF",           ALWAYS                 },
   232    234     { "OFFSET",           "TK_OFFSET",       ALWAYS                 },
   233    235     { "ON",               "TK_ON",           ALWAYS                 },
   234    236     { "OR",               "TK_OR",           ALWAYS                 },
   235    237     { "ORDER",            "TK_ORDER",        ALWAYS                 },