/ Check-in [d83eaed5]
Login

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

Overview
Comment:Add the Upsert object for holding upsert clause information.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: d83eaed539b274c2abd650d07522f491865d4917acbb64d05d01b3ba5c3cd446
User & Date: drh 2018-04-12 13:15:43
Context
2018-04-12
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
12:25
Merge changes from trunk. check-in: 9f6f1180 user: drh tags: upsert
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  4469   4469         sqlite3SelectDelete(db, pCte->pSelect);
  4470   4470         sqlite3DbFree(db, pCte->zName);
  4471   4471       }
  4472   4472       sqlite3DbFree(db, pWith);
  4473   4473     }
  4474   4474   }
  4475   4475   #endif /* !defined(SQLITE_OMIT_CTE) */
         4476  +
         4477  +#ifndef SQLITE_OMIT_UPSERT
         4478  +/*
         4479  +** Free a list of Upsert objects
         4480  +*/
         4481  +void sqlite3UpsertDelete(sqlite3 *db, Upsert *p){
         4482  +  while( p ){
         4483  +    Upsert *pNext = p->pUpsertNext;
         4484  +    sqlite3ExprListDelete(db, p->pUpsertTarget);
         4485  +    sqlite3ExprListDelete(db, p->pUpsertSet);
         4486  +    sqlite3DbFree(db, p);
         4487  +    p = pNext;
         4488  +  }
         4489  +}
         4490  +#endif /* SQLITE_OMIT_UPSERT */
         4491  +
         4492  +#ifndef SQLITE_OMIT_UPSERT
         4493  +/*
         4494  +** Duplicate an Upsert object
         4495  +*/
         4496  +Upsert *sqlite3UpsertDup(sqlite3 *db, Upsert *p){
         4497  +  Upsert *pNew;
         4498  +  if( p==0 ) return 0;
         4499  +  pNew = sqlite3DbMallocRaw(db, sizeof(Upsert));
         4500  +  if( pNew==0 ) return 0;
         4501  +  pNew->pUpsertTarget = sqlite3ExprListDup(db, p->pUpsertTarget, 0);
         4502  +  pNew->pUpsertSet = sqlite3ExprListDup(db, p->pUpsertSet, 0);
         4503  +  pNew->pUpsertNext = sqlite3UpsertDup(db, p->pUpsertNext);
         4504  +  return pNew;
         4505  +}
         4506  +#endif /* SQLITE_OMIT_UPSERT */

Changes to src/insert.c.

   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    491     int onError,          /* How to handle constraint errors */
   492         -  ExprList *pUpsert     /* Upsert values */
          492  +  Upsert *pUpsert       /* ON CONFLICT clauses for upsert, or NULL */
   493    493   ){
   494    494     sqlite3 *db;          /* The main database structure */
   495    495     Table *pTab;          /* The table to insert into.  aka TABLE */
   496    496     int i, j;             /* Loop counters */
   497    497     Vdbe *v;              /* Generate code into this virtual machine */
   498    498     Index *pIdx;          /* For looping over indices of the table */
   499    499     int nColumn;          /* Number of columns in the data */
................................................................................
   524    524   
   525    525   #ifndef SQLITE_OMIT_TRIGGER
   526    526     int isView;                 /* True if attempting to insert into a view */
   527    527     Trigger *pTrigger;          /* List of triggers on pTab, if required */
   528    528     int tmask;                  /* Mask of trigger times */
   529    529   #endif
   530    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 );
   535         -
   536    531     db = pParse->db;
   537    532     if( pParse->nErr || db->mallocFailed ){
   538    533       goto insert_cleanup;
   539    534     }
   540    535     dest.iSDParm = 0;  /* Suppress a harmless compiler warning */
   541    536   
   542    537     /* If the Select object is really just a simple VALUES() list with a
................................................................................
  1076   1071       sqlite3VdbeSetNumCols(v, 1);
  1077   1072       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted", SQLITE_STATIC);
  1078   1073     }
  1079   1074   
  1080   1075   insert_cleanup:
  1081   1076     sqlite3SrcListDelete(db, pTabList);
  1082   1077     sqlite3ExprListDelete(db, pList);
  1083         -  sqlite3ExprListDelete(db, pUpsert);
         1078  +  sqlite3UpsertDelete(db, pUpsert);
  1084   1079     sqlite3SelectDelete(db, pSelect);
  1085   1080     sqlite3IdListDelete(db, pColumn);
  1086   1081     sqlite3DbFree(db, aRegIdx);
  1087   1082   }
  1088   1083   
  1089   1084   /* Make sure "isView" and other macros defined above are undefined. Otherwise
  1090   1085   ** 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         -
   117    101   /*
   118    102   ** Disable lookaside memory allocation for objects that might be
   119    103   ** shared across database connections.
   120    104   */
   121    105   static void disableLookaside(Parse *pParse){
   122    106     pParse->disableLookaside++;
   123    107     pParse->db->lookaside.bDisable++;
................................................................................
   871    855     A = sqlite3ExprListAppendVector(pParse, 0, X, Y);
   872    856   }
   873    857   
   874    858   ////////////////////////// The INSERT command /////////////////////////////////
   875    859   //
   876    860   cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S)
   877    861           upsert(U). {
   878         -  sqlite3Insert(pParse, X, S, F, upsertType(pParse, R, U.e), U.p);
          862  +  sqlite3Insert(pParse, X, S, F, R, U);
   879    863   }
   880    864   cmd ::= with insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
   881    865   {
   882    866     sqlite3Insert(pParse, X, 0, F, R, 0);
   883    867   }
   884    868   
   885         -%type upsert {struct Upsert}
   886         -%destructor upsert {sqlite3ExprListDelete(pParse->db,$$.p);}
   887         -upsert(A) ::= . {
   888         -  A.p = 0;
   889         -  A.e = OE_None;
   890         -}
   891         -upsert(A) ::= ON CONFLICT DO UPDATE SET setlist(X). {
   892         -  A.p = X;  /*A-overwrites-X*/
   893         -  A.e = OE_Update;
   894         -}
   895         -upsert(A) ::= ON CONFLICT DO NOTHING. {
   896         -  A.p = 0;
   897         -  A.e = OE_Ignore;
   898         -}
   899         -
   900         -%include {
   901         -  /* Compute and return the correct conflict resolution strategy for an
   902         -  ** INSERT statement.  If the statement begins with REPLACE or with
   903         -  ** INSERT OR, and it contains an ON CONFLICT clause, throw an error.
   904         -  */
   905         -  static int upsertType(Parse *pParse, int orconf, int upsertType){
   906         -    if( upsertType!=OE_None ){
   907         -      if( orconf!=OE_Default ){
   908         -        sqlite3ErrorMsg(pParse, "ON CONFLICT clause not allowed");
   909         -      }
   910         -      return upsertType;
   911         -    }else{
   912         -      return orconf;
   913         -    }
   914         -  }
   915         -}
          869  +%type upsert {Upsert*}
          870  +%destructor upsert {sqlite3UpsertDelete(pParse->db,$$);}
          871  +upsert(A) ::= . { A = 0; }
          872  +upsert(A) ::= ON CONFLICT DO UPDATE SET setlist. { A = 0; }
          873  +upsert(A) ::= ON CONFLICT DO NOTHING. { A = 0; }
   916    874   
   917    875   %type insert_cmd {int}
   918    876   insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
   919    877   insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}
   920    878   
   921    879   %type idlist_opt {IdList*}
   922    880   %destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}
................................................................................
  1456   1414   trigger_cmd(A) ::=
  1457   1415      UPDATE(B) orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z) scanpt(E).  
  1458   1416      {A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R, B.z, E);}
  1459   1417   
  1460   1418   // INSERT
  1461   1419   trigger_cmd(A) ::= scanpt(B) insert_cmd(R) INTO
  1462   1420                         trnm(X) idlist_opt(F) select(S) upsert(U) scanpt(Z). {
  1463         -   A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,upsertType(pParse,R,U.e),
  1464         -                                U.p,B,Z);/*A-overwrites-R*/
         1421  +   A = sqlite3TriggerInsertStep(pParse->db,&X,F,S,R,U,B,Z);/*A-overwrites-R*/
  1465   1422   }
  1466   1423   // DELETE
  1467   1424   trigger_cmd(A) ::= DELETE(B) FROM trnm(X) tridxby where_opt(Y) scanpt(E).
  1468   1425      {A = sqlite3TriggerDeleteStep(pParse->db, &X, Y, B.z, E);}
  1469   1426   
  1470   1427   // SELECT
  1471   1428   trigger_cmd(A) ::= scanpt(B) select(X) scanpt(E).

Changes to src/sqliteInt.h.

  1091   1091   typedef struct TableLock TableLock;
  1092   1092   typedef struct Token Token;
  1093   1093   typedef struct TreeView TreeView;
  1094   1094   typedef struct Trigger Trigger;
  1095   1095   typedef struct TriggerPrg TriggerPrg;
  1096   1096   typedef struct TriggerStep TriggerStep;
  1097   1097   typedef struct UnpackedRecord UnpackedRecord;
         1098  +typedef struct Upsert Upsert;
  1098   1099   typedef struct VTable VTable;
  1099   1100   typedef struct VtabCtx VtabCtx;
  1100   1101   typedef struct Walker Walker;
  1101   1102   typedef struct WhereInfo WhereInfo;
  1102   1103   typedef struct With With;
  1103   1104   
  1104   1105   /* A VList object records a mapping between parameters/variables/wildcards
................................................................................
  2042   2043   */
  2043   2044   #define OE_None     0   /* There is no constraint to check */
  2044   2045   #define OE_Rollback 1   /* Fail the operation and rollback the transaction */
  2045   2046   #define OE_Abort    2   /* Back out changes but do no rollback transaction */
  2046   2047   #define OE_Fail     3   /* Stop the operation but leave all prior changes */
  2047   2048   #define OE_Ignore   4   /* Ignore the error. Do not do the INSERT or UPDATE */
  2048   2049   #define OE_Replace  5   /* Delete existing record, then do INSERT or UPDATE */
  2049         -#define OE_Update   5   /* An UPSERT.  Same value as OE_Replace. */
  2050   2050   
  2051   2051   #define OE_Restrict 6   /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */
  2052   2052   #define OE_SetNull  7   /* Set the foreign key value to NULL */
  2053   2053   #define OE_SetDflt  8   /* Set the foreign key value to its default */
  2054   2054   #define OE_Cascade  9   /* Cascade the changes */
  2055   2055   
  2056   2056   #define OE_Default  10  /* Do whatever the default action is */
................................................................................
  2705   2705   #define NC_InAggFunc 0x0008  /* True if analyzing arguments to an agg func */
  2706   2706   #define NC_HasAgg    0x0010  /* One or more aggregate functions seen */
  2707   2707   #define NC_IdxExpr   0x0020  /* True if resolving columns of CREATE INDEX */
  2708   2708   #define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
  2709   2709   #define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
  2710   2710   #define NC_Complex   0x2000  /* True if a function or subquery seen */
  2711   2711   
         2712  +/*
         2713  +** An instance of the following object describes a single ON CONFLICT
         2714  +** clause in an upsert.  A list of these objects may be attached to
         2715  +** an INSERT statement in order to form an upsert.
         2716  +*/
         2717  +struct Upsert {
         2718  +  ExprList *pUpsertTarget;  /* Optional description of conflicting index */
         2719  +  ExprList *pUpsertSet;     /* The SET clause from an ON CONFLICT UPDATE */
         2720  +  Upsert *pUpsertNext;      /* Next ON CONFLICT clause in the list */
         2721  +};
         2722  +
  2712   2723   /*
  2713   2724   ** An instance of the following structure contains all information
  2714   2725   ** needed to generate code for a single SELECT statement.
  2715   2726   **
  2716   2727   ** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
  2717   2728   ** If there is a LIMIT clause, the parser sets nLimit to the value of the
  2718   2729   ** limit and nOffset to the value of the offset (or 0 if there is not
................................................................................
  3204   3215   struct TriggerStep {
  3205   3216     u8 op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
  3206   3217     u8 orconf;           /* OE_Rollback etc. */
  3207   3218     Trigger *pTrig;      /* The trigger that this step is a part of */
  3208   3219     Select *pSelect;     /* SELECT statement or RHS of INSERT INTO SELECT ... */
  3209   3220     char *zTarget;       /* Target table for DELETE, UPDATE, INSERT */
  3210   3221     Expr *pWhere;        /* The WHERE clause for DELETE or UPDATE steps */
  3211         -  ExprList *pExprList; /* SET clause for UPDATE or UPSERT. */
         3222  +  ExprList *pExprList; /* SET clause for UPDATE */
  3212   3223     IdList *pIdList;     /* Column names for INSERT */
         3224  +  Upsert *pUpsert;     /* Upsert clauses on an INSERT */
  3213   3225     char *zSpan;         /* Original SQL text of this command */
  3214   3226     TriggerStep *pNext;  /* Next in the link-list */
  3215   3227     TriggerStep *pLast;  /* Last element in link-list. Valid for 1st elem only */
  3216   3228   };
  3217   3229   
  3218   3230   /*
  3219   3231   ** The following structure contains information used by the sqliteFix...
................................................................................
  3737   3749   #ifndef SQLITE_OMIT_AUTOINCREMENT
  3738   3750     void sqlite3AutoincrementBegin(Parse *pParse);
  3739   3751     void sqlite3AutoincrementEnd(Parse *pParse);
  3740   3752   #else
  3741   3753   # define sqlite3AutoincrementBegin(X)
  3742   3754   # define sqlite3AutoincrementEnd(X)
  3743   3755   #endif
  3744         -void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, ExprList*);
         3756  +void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, Upsert*);
  3745   3757   void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*);
  3746   3758   IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
  3747   3759   int sqlite3IdListIndex(IdList*,const char*);
  3748   3760   SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int);
  3749   3761   SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
  3750   3762   SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,
  3751   3763                                         Token*, Select*, Expr*, IdList*);
................................................................................
  3913   3925                               int, int, int);
  3914   3926     void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int);
  3915   3927     void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
  3916   3928     void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*);
  3917   3929     TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*,
  3918   3930                                           const char*,const char*);
  3919   3931     TriggerStep *sqlite3TriggerInsertStep(sqlite3*,Token*, IdList*,
  3920         -                                        Select*,u8,ExprList*,
         3932  +                                        Select*,u8,Upsert*,
  3921   3933                                           const char*,const char*);
  3922   3934     TriggerStep *sqlite3TriggerUpdateStep(sqlite3*,Token*,ExprList*, Expr*, u8,
  3923   3935                                           const char*,const char*);
  3924   3936     TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*,
  3925   3937                                           const char*,const char*);
  3926   3938     void sqlite3DeleteTrigger(sqlite3*, Trigger*);
  3927   3939     void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
................................................................................
  4253   4265     With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*);
  4254   4266     void sqlite3WithDelete(sqlite3*,With*);
  4255   4267     void sqlite3WithPush(Parse*, With*, u8);
  4256   4268   #else
  4257   4269   #define sqlite3WithPush(x,y,z)
  4258   4270   #define sqlite3WithDelete(x,y)
  4259   4271   #endif
         4272  +#ifndef SQLITE_OMIT_UPSERT
         4273  +  void sqlite3UpsertDelete(sqlite3*,Upsert*);
         4274  +  Upsert *sqlite3UpsertDup(sqlite3*,Upsert*);
         4275  +#else
         4276  +#define sqlite3UpsertDelete(x,y)
         4277  +#define sqlite3UpsertDup(x,y) ((Upsert*)0)
         4278  +#endif
         4279  +
  4260   4280   
  4261   4281   /* Declarations for functions in fkey.c. All of these are replaced by
  4262   4282   ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
  4263   4283   ** key functionality is available. If OMIT_TRIGGER is defined but
  4264   4284   ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
  4265   4285   ** this case foreign keys are parsed, but no other functionality is
  4266   4286   ** provided (enforcement of FK constraints requires the triggers sub-system).

Changes to src/trigger.c.

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