[Phpmyadmin-devel] RFE #759 use aliases in SQL export for tables and columns
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 -- Regards, Chirayu Chiripal phpMyAdmin Intern - Google Summer of Code 2014 https://chirayuchiripal.wordpress.com/
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)
On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc@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. -- Regards, Chirayu Chiripal phpMyAdmin Intern - Google Summer of Code 2014 https://chirayuchiripal.wordpress.com/
Chirayu Chiripal a écrit :
On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc@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)
Hi, On Fri, May 23, 2014 at 12:52 AM, Marc Delisle <marc@infomarc.info> wrote:
Chirayu Chiripal a écrit :
On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc@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? -- Regards, Chirayu Chiripal phpMyAdmin Intern - Google Summer of Code 2014 https://chirayuchiripal.wordpress.com/
Le 2014-05-24 14:52, Chirayu Chiripal a écrit :
Hi,
On Fri, May 23, 2014 at 12:52 AM, Marc Delisle <marc@infomarc.info <mailto:marc@infomarc.info>> wrote:
Chirayu Chiripal a écrit : > On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc@infomarc.info <mailto:marc@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
On Sun, May 25, 2014 at 4:05 AM, Marc Delisle <marc@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@infomarc.info <mailto:marc@infomarc.info>> wrote:
Chirayu Chiripal a écrit : > On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc@infomarc.info <mailto:marc@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/
Hi, On 5/25/14 9:18 AM, Chirayu Chiripal wrote:
On Sun, May 25, 2014 at 4:05 AM, Marc Delisle <marc@infomarc.info <mailto:marc@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@infomarc.info <mailto:marc@infomarc.info> > <mailto:marc@infomarc.info <mailto:marc@infomarc.info>>> wrote: > > Chirayu Chiripal a écrit : > > On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc@infomarc.info <mailto:marc@infomarc.info> > <mailto:marc@infomarc.info <mailto:marc@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?
-- Regards, Chirayu Chiripal phpMyAdmin Intern - Google Summer of Code 2014 https://chirayuchiripal.wordpress.com/
------------------------------------------------------------------------------ "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available Simple to use. Nothing to install. Get started now for free." http://p.sf.net/sfu/SauceLabs
_______________________________________________ Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
Hi, On Tue, Jun 3, 2014 at 7:43 PM, Isaac Bennetch <bennetch@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@infomarc.info <mailto:marc@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@infomarc.info <mailto:marc@infomarc.info> > <mailto:marc@infomarc.info <mailto:marc@infomarc.info>>> wrote: > > Chirayu Chiripal a écrit : > > On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc@infomarc.info <mailto:marc@infomarc.info> > <mailto:marc@infomarc.info <mailto:marc@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/
Hello Isaac Sir, On Fri, Jun 6, 2014 at 5:14 PM, Chirayu Chiripal <chirayu.chiripal@gmail.com
wrote:
Hi,
On Tue, Jun 3, 2014 at 7:43 PM, Isaac Bennetch <bennetch@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@infomarc.info <mailto:marc@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@infomarc.info <mailto:marc@infomarc.info> > <mailto:marc@infomarc.info <mailto:marc@infomarc.info>>> wrote: > > Chirayu Chiripal a écrit : > > On Fri, May 23, 2014 at 12:35 AM, Marc Delisle <marc@infomarc.info <mailto:marc@infomarc.info> > <mailto:marc@infomarc.info <mailto:marc@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/
participants (3)
-
Chirayu Chiripal -
Isaac Bennetch -
Marc Delisle