[Phpmyadmin-devel] RFE #759 use aliases in SQL export for tables and columns
Chirayu Chiripal
chirayu.chiripal at gmail.com
Mon Jun 9 11:03:37 CEST 2014
Hello Isaac Sir,
On Fri, Jun 6, 2014 at 5:14 PM, Chirayu Chiripal <chirayu.chiripal at gmail.com
> wrote:
> 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?
>
> I have almost completed this feature request, currently I am adding few
more test cases, updating documentation and will fix few minor known
issues. Meanwhile, if you get time to review the code, you can see the code
here: https://github.com/D-storm/phpmyadmin/tree/FR-759. After completing
the work what I have mentioned above, I will squash the commits then I'll
check travis, coveralls, and scrutinizer reports and will open up a pull
request. I will complete this by today itself.
>
>> > --
>> > 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/20140609/f98bda23/attachment.html>
More information about the Developers
mailing list