/ Check-in [9b22905b]
Login

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

Overview
Comment:Demonstration of how the parser can be augmented to recognize a PostgreSQL-style UPSERT. This check-in implements parsing only.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: 9b22905b15791170998a5d1bcf42c7b60b5064f6848fff827bd55e864bf724aa
User & Date: drh 2018-04-06 19:36:49
Context
2018-04-07
15:04
More complete parsing of UPSERT, including UPSERT within a trigger. The sqlite3Insert() logic to actually perform the UPSERT is not yet implemented, however. check-in: 5cc2a5a3 user: drh tags: upsert
2018-04-06
19:36
Demonstration of how the parser can be augmented to recognize a PostgreSQL-style UPSERT. This check-in implements parsing only. check-in: 9b22905b user: drh tags: upsert
19:12
Enhance LEMON to show precendence of symbols and all rules in the report that is generated in parallel to the parser. check-in: 602fbd81 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

   235    235   %right ESCAPE.
   236    236   %left BITAND BITOR LSHIFT RSHIFT.
   237    237   %left PLUS MINUS.
   238    238   %left STAR SLASH REM.
   239    239   %left CONCAT.
   240    240   %left COLLATE.
   241    241   %right BITNOT.
          242  +%nonassoc ON.
   242    243   
   243    244   // An IDENTIFIER can be a generic identifier, or one of several
   244    245   // keywords.  Any non-standard keyword can also be an identifier.
   245    246   //
   246    247   %token_class id  ID|INDEXED.
   247    248   
   248    249   
................................................................................
   674    675   joinop(X) ::= JOIN_KW(A) JOIN.
   675    676                     {X = sqlite3JoinType(pParse,&A,0,0);  /*X-overwrites-A*/}
   676    677   joinop(X) ::= JOIN_KW(A) nm(B) JOIN.
   677    678                     {X = sqlite3JoinType(pParse,&A,&B,0); /*X-overwrites-A*/}
   678    679   joinop(X) ::= JOIN_KW(A) nm(B) nm(C) JOIN.
   679    680                     {X = sqlite3JoinType(pParse,&A,&B,&C);/*X-overwrites-A*/}
   680    681   
          682  +// There is a parsing abiguity in an upsert statement that uses a
          683  +// SELECT on the RHS of a the INSERT:
          684  +//
          685  +//      INSERT INTO tab SELECT * FROM aaa JOIN bbb ON CONFLICT ...
          686  +//                                        here ----^^
          687  +//
          688  +// When the ON token is encountered, the parser does not know if it is
          689  +// the beginning of an ON CONFLICT clause, or the beginning of an ON
          690  +// clause associated with the JOIN.  The conflict is resolved in favor
          691  +// of the JOIN.  If an ON CONFLICT clause is intended, insert a dummy
          692  +// WHERE clause in between, like this:
          693  +//
          694  +//      INSERT INTO tab SELECT * FROM aaa JOIN bbb WHERE true ON CONFLICT ...
          695  +//
          696  +// The [AND] and [OR] precedence marks in the rules for on_opt cause the
          697  +// ON in this context to always be interpreted as belonging to the JOIN.
          698  +//
   681    699   %type on_opt {Expr*}
   682    700   %destructor on_opt {sqlite3ExprDelete(pParse->db, $$);}
   683         -on_opt(N) ::= ON expr(E).   {N = E;}
   684         -on_opt(N) ::= .             {N = 0;}
          701  +on_opt(N) ::= ON expr(E).  {N = E;}
          702  +on_opt(N) ::= .     [OR]   {N = 0;}
   685    703   
   686    704   // Note that this block abuses the Token type just a little. If there is
   687    705   // no "INDEXED BY" clause, the returned token is empty (z==0 && n==0). If
   688    706   // there is an INDEXED BY clause, then the token is populated as per normal,
   689    707   // with z pointing to the token data and n containing the number of bytes
   690    708   // in the token.
   691    709   //
................................................................................
   820    838   }
   821    839   setlist(A) ::= LP idlist(X) RP EQ expr(Y). {
   822    840     A = sqlite3ExprListAppendVector(pParse, 0, X, Y);
   823    841   }
   824    842   
   825    843   ////////////////////////// The INSERT command /////////////////////////////////
   826    844   //
   827         -cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S). {
          845  +cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S) upsert. {
   828    846     sqlite3WithPush(pParse, W, 1);
   829    847     sqlite3Insert(pParse, X, S, F, R);
   830    848   }
   831    849   cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
   832    850   {
   833    851     sqlite3WithPush(pParse, W, 1);
   834    852     sqlite3Insert(pParse, X, 0, F, R);
   835    853   }
   836    854   
          855  +upsert ::= .
          856  +upsert ::= ON CONFLICT SET setlist.
          857  +
   837    858   %type insert_cmd {int}
   838    859   insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
   839    860   insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}
   840    861   
   841    862   %type idlist_opt {IdList*}
   842    863   %destructor idlist_opt {sqlite3IdListDelete(pParse->db, $$);}
   843    864   %type idlist {IdList*}