Re: [sqlite] Changes on sqlite3 parser and why not ?

[Richard Hipp]

On 4/17/18, petern  wrote:
> 3. In tables with serially related data it can be cumbersome to reference
> columns in the working table when columns must be computed from existing
> rows in the same table.

I don't understand this point.  Can you provide an example?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changes on sqlite3 parser and why not ?

[petern]

Some points on working table alias:

1. If upsert goes to trunk, there is already a INSERT table alias test case
(do_execsql_test upsert3-210)  that must succeed:

https://www.sqlite.org/src/info/907b5a37c539ea67

2. Obviously PostgreSQL already supports a working table alias universally:

https://www.postgresql.org/docs/9.5/static/sql-insert.html
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]

https://www.postgresql.org/docs/9.5/static/sql-update.html
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]

https://www.postgresql.org/docs/9.5/static/sql-delete.html
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]

3. In tables with serially related data it can be cumbersome to reference
columns in the working table when columns must be computed from existing
rows in the same table.  The INSERT/UPDATE/DELETE working table alias would
be very helpful and informative for readability in these situations.

Peter



On Tue, Apr 17, 2018 at 9:58 AM, Domingo Alvarez Duarte 
wrote:

> Hello Richard !
>
> Now that you are making changes on sqlite3 parser could you please add the
> table alias to delete/insert/update ?
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Changes on sqlite3 parser and why not ?

[Domingo Alvarez Duarte]

Hello Richard !

Now that you are making changes on sqlite3 parser could you please add 
the table alias to delete/insert/update ?


I already have it working and got conflicts with the latest changes, 
they are not big or complicated but it helps make some queries easier.


Cheers !

fossil diff parse.y
Index: src/parse.y
==
--- src/parse.y
+++ src/parse.y
@@ -779,20 +779,20 @@
  {A = sqlite3PExpr(pParse,TK_LIMIT,Y,X);}

 /// The DELETE statement 
/

 //
 %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with DELETE FROM fullname(X) indexed_opt(I) where_opt(W)
+cmd ::= with DELETE FROM fullname(X) as(Z) indexed_opt(I) where_opt(W)
 orderby_opt(O) limit_opt(L). {
   sqlite3SrcListIndexedBy(pParse, X, );
-  sqlite3DeleteFrom(pParse,X,W,O,L);
+  sqlite3DeleteFrom(pParse,X,,W,O,L);
 }
 %endif
 %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
+cmd ::= with DELETE FROM fullname(X) as(Z) indexed_opt(I) where_opt(W). {
   sqlite3SrcListIndexedBy(pParse, X, );
-  sqlite3DeleteFrom(pParse,X,W,0,0);
+  sqlite3DeleteFrom(pParse,X,,W,0,0);
 }
 %endif

 %type where_opt {Expr*}
 %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
@@ -801,23 +801,23 @@
 where_opt(A) ::= WHERE expr(X).   {A = X;}

 // The UPDATE command 


 //
 %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
+cmd ::= with UPDATE orconf(R) fullname(X) as(Z) indexed_opt(I) SET 
setlist(Y)

 where_opt(W) orderby_opt(O) limit_opt(L).  {
   sqlite3SrcListIndexedBy(pParse, X, );
   sqlite3ExprListCheckLength(pParse,Y,"set list");
-  sqlite3Update(pParse,X,Y,W,R,O,L);
+  sqlite3Update(pParse,X,,Y,W,R,O,L);
 }
 %endif
 %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
+cmd ::= with UPDATE orconf(R) fullname(X) as(Z) indexed_opt(I) SET 
setlist(Y)

 where_opt(W).  {
   sqlite3SrcListIndexedBy(pParse, X, );
   sqlite3ExprListCheckLength(pParse,Y,"set list");
-  sqlite3Update(pParse,X,Y,W,R,0,0);
+  sqlite3Update(pParse,X,,Y,W,R,0,0);
 }
 %endif

 %type setlist {ExprList*}
 %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}


fossil diff update.c
Index: src/update.c
==
--- src/update.c
+++ src/update.c
@@ -87,10 +87,11 @@
 *    onError   pTabList  pChanges pWhere
 */
 void sqlite3Update(
   Parse *pParse, /* The parser context */
   SrcList *pTabList, /* The table in which we should change things */
+  Token *pAlias,  /* The right-hand side of the AS subexpression */
   ExprList *pChanges,    /* Things to be changed */
   Expr *pWhere,  /* The WHERE clause.  May be null */
   int onError,   /* How to handle constraint errors */
   ExprList *pOrderBy,    /* ORDER BY clause. May be null */
   Expr *pLimit   /* LIMIT clause. May be null */
@@ -123,10 +124,11 @@
   int hasFK; /* True if foreign key processing is required */
   int labelBreak;    /* Jump here to break out of UPDATE loop */
   int labelContinue; /* Jump here to continue next step of UPDATE 
loop */

   int flags; /* Flags for sqlite3WhereBegin() */

+  struct SrcList_item *pItem; /*To namage table alias*/
 #ifndef SQLITE_OMIT_TRIGGER
   int isView;    /* True when updating a view (INSTEAD OF 
trigger) */

   Trigger *pTrigger; /* List of triggers on pTab, if required */
   int tmask; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
 #endif
@@ -153,10 +155,16 @@
   if( pParse->nErr || db->mallocFailed ){
 goto update_cleanup;
   }
   assert( pTabList->nSrc==1 );

+  /*Manage table alias*/
+  pItem = >a[pTabList->nSrc-1];
+  if( pAlias && pAlias->n ){
+    pItem->zAlias = sqlite3NameFromToken(db, pAlias);
+  }
+
   /* Locate the table which we want to update.
   */
   pTab = sqlite3SrcListLookup(pParse, pTabList);
   if( pTab==0 ) goto update_cleanup;
   iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);

fossil diff delete.c
Index: src/delete.c
==
--- src/delete.c
+++ src/delete.c
@@ -219,10 +219,11 @@
 **  pTabList  pWhere
 */
 void sqlite3DeleteFrom(
   Parse *pParse, /* The parser context */
   SrcList *pTabList, /* The table from which we should delete 
things */

+  Token *pAlias,  /* The right-hand side of the AS subexpression */
   Expr *pWhere,  /* The WHERE clause.  May be null */
   ExprList *pOrderBy,    /* ORDER BY clause. May be null */
   Expr *pLimit   /* LIMIT clause. May be null */
 ){
   Vdbe *v;   /* The virtual database engine */
@@