Changes on sqlite3 parser and why not ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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, &I);
-  sqlite3DeleteFrom(pParse,X,W,O,L);
+  sqlite3DeleteFrom(pParse,X,&Z,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, &I);
-  sqlite3DeleteFrom(pParse,X,W,0,0);
+  sqlite3DeleteFrom(pParse,X,&Z,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, &I);
    sqlite3ExprListCheckLength(pParse,Y,"set list");
-  sqlite3Update(pParse,X,Y,W,R,O,L);
+  sqlite3Update(pParse,X,&Z,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, &I);
    sqlite3ExprListCheckLength(pParse,Y,"set list");
-  sqlite3Update(pParse,X,Y,W,R,0,0);
+  sqlite3Update(pParse,X,&Z,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 = &pTabList->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 */
@@ -253,11 +254,12 @@
    int addrBypass = 0;    /* Address of jump over the delete logic */
    int addrLoop = 0;      /* Top of the delete loop */
    int addrEphOpen = 0;   /* Instruction to open the Ephemeral table */
    int bComplex;          /* True if there are triggers or FKs or
                           ** subqueries in the WHERE clause */
-
+  struct SrcList_item *pItem; /*To namage table alias*/
+
  #ifndef SQLITE_OMIT_TRIGGER
    int isView;                  /* True if attempting to delete from a
view */
    Trigger *pTrigger;           /* List of table triggers, if required */
  #endif

@@ -266,10 +268,15 @@
    if( pParse->nErr || db->mallocFailed ){
      goto delete_from_cleanup;
    }
    assert( pTabList->nSrc==1 );

+  /*Manage table alias*/
+  pItem = &pTabList->a[pTabList->nSrc-1];
+  if( pAlias && pAlias->n ){
+    pItem->zAlias = sqlite3NameFromToken(db, pAlias);
+  }

    /* Locate the table which we want to delete.  This table has to be
    ** put in an SrcList structure because some of the subroutines we
    ** will be calling are designed to work with multiple tables and expect
    ** an SrcList* parameter instead of just a Table* parameter.

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 */
@@ -253,11 +254,12 @@
    int addrBypass = 0;    /* Address of jump over the delete logic */
    int addrLoop = 0;      /* Top of the delete loop */
    int addrEphOpen = 0;   /* Instruction to open the Ephemeral table */
    int bComplex;          /* True if there are triggers or FKs or
                           ** subqueries in the WHERE clause */
-
+  struct SrcList_item *pItem; /*To namage table alias*/
+
  #ifndef SQLITE_OMIT_TRIGGER
    int isView;                  /* True if attempting to delete from a
view */
    Trigger *pTrigger;           /* List of table triggers, if required */
  #endif

@@ -266,10 +268,15 @@
    if( pParse->nErr || db->mallocFailed ){
      goto delete_from_cleanup;
    }
    assert( pTabList->nSrc==1 );

+  /*Manage table alias*/
+  pItem = &pTabList->a[pTabList->nSrc-1];
+  if( pAlias && pAlias->n ){
+    pItem->zAlias = sqlite3NameFromToken(db, pAlias);
+  }

    /* Locate the table which we want to delete.  This table has to be
    ** put in an SrcList structure because some of the subroutines we
    ** will be calling are designed to work with multiple tables and expect
    ** an SrcList* parameter instead of just a Table* parameter.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changes on sqlite3 parser and why not ?

Richard Hipp-3
On 4/17/18, petern <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users