[Phpmyadmin-devel] RFE #759 use aliases in SQL export for tables and columns

[Chirayu Chiripal]

On Sun, May 25, 2014 at 4:05 AM, Marc Delisle <marc at infomarc.info> wrote:

> Le 2014-05-24 14:52, Chirayu Chiripal a écrit :
> > Hi,
> >
> > On Fri, May 23, 2014 at 12:52 AM, Marc Delisle <marc at infomarc.info
> > <mailto:marc at infomarc.info>> wrote:
> >
> >     Chirayu Chiripal a écrit :
> >     > On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc at infomarc.info
> >     <mailto:marc at infomarc.info>> wrote:
> >     >
> >     >> Chirayu Chiripal a écrit :
> >     >>> Hi,
> >     >>>
> >     >>> While analyzing the current behavior of export feature, few
> >     doubts has
> >     >>> arisen in my mind regarding it. In sql_export.png, There are two
> >     export
> >     >>> buttons, button 1 & button 2 respectively. When exporting using
> >     Button 1
> >     >>> shown in sql_export.png, the resulting sql file had all the rows
> (20
> >     >> rows)
> >     >>> present in the table (See QA_4_2_Button_1_export.sql) whereas
> when
> >     >> Button 2
> >     >>> was used then only the rows (10 rows) displayed in results
> >     according to
> >     >> the
> >     >>> LIMIT condition in the query were in the sql file (See
> >     >>> QA_4_2_Button_2_export.sql). Is this the correct behavior or
> >     both should
> >     >>> have generated identical sql export file?
> >     >>>
> >     >>> SQL query which was used: SELECT * FROM `abcd` WHERE 1 limit 10
> >     >>>
> >     >> Button 1 means to export the rows that you have selected via each
> >     row's
> >     >> checkbox.
> >     >>
> >     >> --
> >     >> Marc Delisle (phpMyAdmin)
> >     >>
> >     >
> >     > What if I didn't selected any rows?
> >     >
> >     > IMHO, In this case it should not export rows which are not in the
> >     resultset
> >     > of the query but it is exporting all the rows of the table.
> >
> >     Correct, it should not export anything.
> >
> >     --
> >     Marc Delisle (phpMyAdmin)
> >
> >
> >
> > Currently, there is no SQL Export for queries involving multiple tables
> > such as join query. So, does that mean aliases is to be considered only
> > in SELECT statements involving one table? or Am I missing something here?
>
> Indeed, exporting a join query makes no sense to me.
>
>
> --
> Marc Delisle | phpMyAdmin
>
> A table maybe having triggers present, so while exporting even old column
and table names have to be replaced by alias which is a bit problem here
because current SQL parser does not provide information about aliases in
such cases with PMA_SQP_analyze(). Maybe some of these approaches can be
used:

1) We can get token array by using PMA_SQP_parse() to get tokens which have
to processed which is bit problematic because result of PMA_SQP_parse()
detects 'TRIGGER', 'BEFORE' few keywords used in 'CREATE TRIGGER' statement
as an identifier rather than keyword and it becomes difficult to decide
whether it was used as column or table name or it is used as keyword. Maybe
we can first look for SELECT, INSERT, DELETE, UPDATE, SET, NEW, OLD
keywords first then consider identifier followed by them. I am not sure
whether this kind of approach can give us correct results every time.

2) Or we can separate different SQL statements such as SELECT, INSERT, SET
etc. inside create query of trigger into separate queries and then parse
them using PMA_SQP_analyze() and then substitute aliases. I have no idea
how to separate them as trigger body may have various constructs present in
them such as loops, if-then-else conditions etc.

Any other suggestions about how to extract column and table names from the
create queries of trigger?

-- 
Regards,
Chirayu Chiripal
phpMyAdmin Intern - Google Summer of Code 2014
https://chirayuchiripal.wordpress.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20140525/7ecb120b/attachment.html>