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

[Chirayu Chiripal]

Hi,


On Tue, Jun 3, 2014 at 7:43 PM, Isaac Bennetch <bennetch at gmail.com> wrote:

> Hi,
>
> On 5/25/14 9:18 AM, Chirayu Chiripal wrote:
> > On Sun, May 25, 2014 at 4:05 AM, Marc Delisle <marc at infomarc.info
> > <mailto: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>
> >     > <mailto: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>
> >     >     <mailto: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?
>
> I'll admit that PMA_SQP_parse() isn't something I'm very familiar with,
> so I welcome input from others, but I do have two thoughts to share.
>
> First, if we risk not doing something completely and correctly, we
> should either warn the user or in other ways help the user know to
> double-check their data. In this case, when a trigger is detected, a
> message could be displayed such as "It appears your table uses triggers;
> alias export may not work reliably in all cases." However, of course I
> prefer if we can find a solution that works correctly in all cases :)
>
> Secondly, it seems to me your first solution is better than the second
> option. Forgive me for not having tested it myself, but in general a
> properly-formatted query will escape table and database names with
> backticks ` so that keywords are easily identified as those without
> surrounding backticks. Does that not help us in this instance?
>

Are constraints such as foreign key always exported as separate queries?


> > --
> > 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/20140606/f35fb164/attachment.html>