[Phpmyadmin-devel] Suggestions for Import/Export Changes
Hello everyone, I have a couple matters that I wanted to get input from you all on. First, possibilities for the new import interface. For those who don't know much about my GSoC project, one of the goals was to create an intermediary interface for editing the structure of a new database/table set that was deduced from some import data. Via this interface the structure is to be altered and changed as necessary (dropping tables, changes keys, etc.) and once the user is satisfied, it is created and the data (now adapted to the new structure) in inserted. This involved storing all the data from the import in a PHP session so the source wouldn't have to be reanalyzed after the user made edits to the structure. I have been having some second thoughts regarding such an interface. Depending on the size of the import, the PHP session could take up a considerable amount of memory. Also, all database and table options are editable after creation via the current regular db/table interface, therefore I believe the intermediary interface would be redundant at best, and counterintuitive as worst. All the while being rather inefficient. I find that directing the user towards the newly created database and/or tables via a notification after a successful import is simple, effective, and vastly more efficient. Suggestions, thought, comments, concerns, anyone? Second, I would like to propose a new XML export format to better support my new import features. This new format will present the option to export the SQL creation code for each of the exported tables in the manner outlined below: <?xml version="1.0" encoding="utf-8" ?> <!-- - - phpMyAdmin XML Dump - ... etc. etc. etc. --> <pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/"> <pma:structure> <pma:db_name> <pma:tbl_name_1>CREATE TABLE `tbl_name_1`...</pma:tbl_name_1> <pma:tbl_name_2>CREATE TABLE `tbl_name_2`...</pma:tbl_name_2> <pma:tbl_name_3>CREATE TABLE `tbl_name_3`...</pma:tbl_name_3> </pma:db_name> </pma:structure> <db_name> <tbl_name_1> <col_1>data</col_1> <col_2>data</col_2> <col_3>data</col_3> </tbl_name_1> <tbl_name_2> <col_1>data</col_1> <col_2>data</col_2> <col_3>data</col_3> </tbl_name_2> <tbl_name_3> <col_1>data</col_1> <col_2>data</col_2> <col_3>data</col_3> </tbl_name_3> </db_name> </pma_xml_export> ...as opposed to the existing format: <?xml version="1.0" encoding="utf-8" ?> <!-- - - phpMyAdmin XML Dump - ... etc. etc. etc. --> <db_name> <tbl_name_1> <col_1>data</col_1> <col_2>data</col_2> <col_3>data</col_3> </tbl_name_1> <tbl_name_2> <col_1>data</col_1> <col_2>data</col_2> <col_3>data</col_3> </tbl_name_2> <tbl_name_3> <col_1>data</col_1> <col_2>data</col_2> <col_3>data</col_3> </tbl_name_3> </db_name> If you have any advice, I would love to hear from you. Thanks for your time and advice, Derek Schaefer
Derek Schaefer a écrit :
Hello everyone,
I have a couple matters that I wanted to get input from you all on.
First, possibilities for the new import interface. For those who don't know much about my GSoC project, one of the goals was to create an intermediary interface for editing the structure of a new database/table set that was deduced from some import data. Via this interface the structure is to be altered and changed as necessary (dropping tables, changes keys, etc.) and once the user is satisfied, it is created and the data (now adapted to the new structure) in inserted. This involved storing all the data from the import in a PHP session so the source wouldn't have to be reanalyzed after the user made edits to the structure. I have been having some second thoughts regarding such an interface. Depending on the size of the import, the PHP session could take up a considerable amount of memory. Also, all database and table options are editable after creation via the current regular db/table interface, therefore I believe the intermediary interface would be redundant at best, and counterintuitive as worst. All the while being rather inefficient. I find that directing the user towards the newly created database and/or tables via a notification after a successful import is simple, effective, and vastly more efficient.
Indeed I don't think that storing import data in session is a good idea. You could offer the user a two-pass dialog. Pass #1 tells the user that a table structure will be created, then imports a small amount of data (maybe using some timer), analyzes it and creates a structure. Then, the user can refine it by the regular structure interface. Finally, user chooses pass #2 which does the complete data import.
Suggestions, thought, comments, concerns, anyone?
Second, I would like to propose a new XML export format to better support my new import features. This new format will present the option to export the SQL creation code for each of the exported tables in the manner outlined below:
<?xml version="1.0" encoding="utf-8" ?> <!-- - - phpMyAdmin XML Dump - ... etc. etc. etc. -->
<pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/"> <pma:structure> <pma:db_name> <pma:tbl_name_1>CREATE TABLE `tbl_name_1`...</pma:tbl_name_1> <pma:tbl_name_2>CREATE TABLE `tbl_name_2`...</pma:tbl_name_2> <pma:tbl_name_3>CREATE TABLE `tbl_name_3`...</pma:tbl_name_3> </pma:db_name> </pma:structure>
Ok for me, but what happens with views? -- Marc Delisle http://infomarc.info
On Tue, Jun 30, 2009 at 12:53 PM, Marc Delisle<marc@infomarc.info> wrote:
Derek Schaefer a écrit :
Hello everyone,
I have a couple matters that I wanted to get input from you all on.
First, possibilities for the new import interface. For those who don't know much about my GSoC project, one of the goals was to create an intermediary interface for editing the structure of a new database/table set that was deduced from some import data. Via this interface the structure is to be altered and changed as necessary (dropping tables, changes keys, etc.) and once the user is satisfied, it is created and the data (now adapted to the new structure) in inserted. This involved storing all the data from the import in a PHP session so the source wouldn't have to be reanalyzed after the user made edits to the structure. I have been having some second thoughts regarding such an interface. Depending on the size of the import, the PHP session could take up a considerable amount of memory. Also, all database and table options are editable after creation via the current regular db/table interface, therefore I believe the intermediary interface would be redundant at best, and counterintuitive as worst. All the while being rather inefficient. I find that directing the user towards the newly created database and/or tables via a notification after a successful import is simple, effective, and vastly more efficient.
Indeed I don't think that storing import data in session is a good idea. You could offer the user a two-pass dialog. Pass #1 tells the user that a table structure will be created, then imports a small amount of data (maybe using some timer), analyzes it and creates a structure. Then, the user can refine it by the regular structure interface. Finally, user chooses pass #2 which does the complete data import.
I have played with a similar setup, and the main problem is that when only a portion of the data is analyzed, truncation (and therefore data loss) is likely to occur. For example, let's say we analyze the first 10 rows of a given table and we find that, for a column of type varchar, the longest entry we've come across is 15, thus the column will be of type varchar(15) for the first pass (the one that forms the structure that the user edits to his/her liking). The problem is that there could very well be an entry in the yet un-analyzed portion of the table that is longer than 15 characters, and when it is inserted it will be truncated. Of course, the structure could be adjusted on the second pass, but that almost entirely defeats the purpose of the first pass and the user editing the structure. I think the best course would be to provide some additional options alongside the existing import options.
Suggestions, thought, comments, concerns, anyone?
Second, I would like to propose a new XML export format to better support my new import features. This new format will present the option to export the SQL creation code for each of the exported tables in the manner outlined below:
<?xml version="1.0" encoding="utf-8" ?> <!-- - - phpMyAdmin XML Dump - ... etc. etc. etc. -->
<pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/"> <pma:structure> <pma:db_name> <pma:tbl_name_1>CREATE TABLE `tbl_name_1`...</pma:tbl_name_1> <pma:tbl_name_2>CREATE TABLE `tbl_name_2`...</pma:tbl_name_2> <pma:tbl_name_3>CREATE TABLE `tbl_name_3`...</pma:tbl_name_3> </pma:db_name> </pma:structure>
Ok for me, but what happens with views?
I had yet to try that, so I just did. In it's current state the new XML export plug-in will output the SQL code necessary to create the view (with a little extra fluff regarding the user, etc.), but will also output the data pulled by the view if those tables from which it draws are not exported alongside it (for example, when the view is exported by itself), although this is no longer very much of an issue as I have added options to be export the table/view structures (or not) and the data (or not). It could use some tweaking, but I don't think it's of terribly high priority at the moment.
-- Marc Delisle http://infomarc.info
------------------------------------------------------------------------------ _______________________________________________ Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
Hi Dne Mon, 29 Jun 2009 14:35:33 -0500 Derek Schaefer <derek.schaefer@gmail.com> napsal(a):
First, possibilities for the new import interface. For those who don't know much about my GSoC project, one of the goals was to create an intermediary interface for editing the structure of a new database/table set that was deduced from some import data. Via this interface the structure is to be altered and changed as necessary (dropping tables, changes keys, etc.) and once the user is satisfied, it is created and the data (now adapted to the new structure) in inserted. This involved storing all the data from the import in a PHP session so the source wouldn't have to be reanalyzed after the user made edits to the structure. I have been having some second thoughts regarding such an interface. Depending on the size of the import, the PHP session could take up a considerable amount of memory. Also, all database and table options are editable after creation via the current regular db/table interface, therefore I believe the intermediary interface would be redundant at best, and counterintuitive as worst. All the while being rather inefficient. I find that directing the user towards the newly created database and/or tables via a notification after a successful import is simple, effective, and vastly more efficient.
It might be an option. However converting some data later might be problematic, but I fail to find good example right now :-).
Second, I would like to propose a new XML export format to better support my new import features. This new format will present the option to export the SQL creation code for each of the exported tables in the manner outlined below:
<?xml version="1.0" encoding="utf-8" ?> <!-- - - phpMyAdmin XML Dump - ... etc. etc. etc. -->
<pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/"> <pma:structure> <pma:db_name> <pma:tbl_name_1>CREATE TABLE `tbl_name_1`...</pma:tbl_name_1>
Please make it proper XML then and do not (re)introduce variable tag names. <pma:structure> <pma:db name="databaze"> <pma:table name="tabulka">CREATE...</pma:table> ... <pma:view name="pohled">CREATE...</pma:view> <pma:trigger name="procedura">...</pma:trigger> </pma:db> </pma:structure> -- Michal Čihař | http://cihar.com | http://phpmyadmin.cz
On Wed, Jul 1, 2009 at 3:32 AM, Michal Čihař<michal@cihar.com> wrote:
Hi
Dne Mon, 29 Jun 2009 14:35:33 -0500 Derek Schaefer <derek.schaefer@gmail.com> napsal(a):
First, possibilities for the new import interface. For those who don't know much about my GSoC project, one of the goals was to create an intermediary interface for editing the structure of a new database/table set that was deduced from some import data. Via this interface the structure is to be altered and changed as necessary (dropping tables, changes keys, etc.) and once the user is satisfied, it is created and the data (now adapted to the new structure) in inserted. This involved storing all the data from the import in a PHP session so the source wouldn't have to be reanalyzed after the user made edits to the structure. I have been having some second thoughts regarding such an interface. Depending on the size of the import, the PHP session could take up a considerable amount of memory. Also, all database and table options are editable after creation via the current regular db/table interface, therefore I believe the intermediary interface would be redundant at best, and counterintuitive as worst. All the while being rather inefficient. I find that directing the user towards the newly created database and/or tables via a notification after a successful import is simple, effective, and vastly more efficient.
It might be an option. However converting some data later might be problematic, but I fail to find good example right now :-).
Alright, I'll run some tests and see if there are any problems. Should I move forward on this basis for the time being? I can always add the new interface(s) later if they are deemed desirable.
Second, I would like to propose a new XML export format to better support my new import features. This new format will present the option to export the SQL creation code for each of the exported tables in the manner outlined below:
<?xml version="1.0" encoding="utf-8" ?> <!-- - - phpMyAdmin XML Dump - ... etc. etc. etc. -->
<pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/"> <pma:structure> <pma:db_name> <pma:tbl_name_1>CREATE TABLE `tbl_name_1`...</pma:tbl_name_1>
Please make it proper XML then and do not (re)introduce variable tag names.
<pma:structure> <pma:db name="databaze"> <pma:table name="tabulka">CREATE...</pma:table> ... <pma:view name="pohled">CREATE...</pma:view> <pma:trigger name="procedura">...</pma:trigger> </pma:db> </pma:structure> Splendid, will do.
-- Michal Čihař | http://cihar.com | http://phpmyadmin.cz
------------------------------------------------------------------------------
_______________________________________________ Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
Hi, On 01.07.2009 10:32, Michal Čihař wrote:
Second, I would like to propose a new XML export format to better support my new import features. This new format will present the option to export the SQL creation code for each of the exported tables in the manner outlined below:
<?xml version="1.0" encoding="utf-8" ?> <!-- - - phpMyAdmin XML Dump - ... etc. etc. etc. -->
<pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/"> <pma:structure> <pma:db_name> <pma:tbl_name_1>CREATE TABLE `tbl_name_1`...</pma:tbl_name_1>
Please make it proper XML then and do not (re)introduce variable tag names.
<pma:structure> <pma:db name="databaze"> <pma:table name="tabulka">CREATE...</pma:table> ... <pma:view name="pohled">CREATE...</pma:view> <pma:trigger name="procedura">...</pma:trigger> </pma:db> </pma:structure>
will something like the following be too difficult? or inadequate for the purpose of the export? <pma:structure> <pma:db name="databaze"> <pma:table name="tabulka" engine="MyISAM"> <pma:field name="myfield" type="int(11)" .../> <pma:index name="myindex" type="BTREE" ...> <pma:field name="myfield" length="255"/> </pma:index> </pma:table> ... <pma:view name="pohled">CREATE...</pma:view> <pma:trigger name="procedura">...</pma:trigger> </pma:db> </pma:structure> -- Sebastian Mendel
Hello Sebastian, Thank you for the advice. I had considered such a schema (as it is more akin to proper XML), but ultimately I decided against it on the grounds that the plug-in supports the export of database, table, view, trigger, function, and procedure schemas. The problem is that between all of those there are simply too many options to manually account for. Therefore, I chose a hybrid schema that can be seen here: http://www.moonlapse.org/?p=472 I'm still open to suggestions, though. The above schema is merely what I am currently using. -Derek Schaefer On Mon, Jul 6, 2009 at 1:46 AM, Sebastian Mendel<lists@sebastianmendel.de> wrote:
Hi,
On 01.07.2009 10:32, Michal Čihař wrote:
Second, I would like to propose a new XML export format to better support my new import features. This new format will present the option to export the SQL creation code for each of the exported tables in the manner outlined below:
<?xml version="1.0" encoding="utf-8" ?> <!-- - - phpMyAdmin XML Dump - ... etc. etc. etc. -->
<pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/"> <pma:structure> <pma:db_name> <pma:tbl_name_1>CREATE TABLE `tbl_name_1`...</pma:tbl_name_1>
Please make it proper XML then and do not (re)introduce variable tag names.
<pma:structure> <pma:db name="databaze"> <pma:table name="tabulka">CREATE...</pma:table> ... <pma:view name="pohled">CREATE...</pma:view> <pma:trigger name="procedura">...</pma:trigger> </pma:db> </pma:structure>
will something like the following be too difficult? or inadequate for the purpose of the export?
<pma:structure> <pma:db name="databaze"> <pma:table name="tabulka" engine="MyISAM"> <pma:field name="myfield" type="int(11)" .../> <pma:index name="myindex" type="BTREE" ...> <pma:field name="myfield" length="255"/> </pma:index> </pma:table> ... <pma:view name="pohled">CREATE...</pma:view> <pma:trigger name="procedura">...</pma:trigger> </pma:db> </pma:structure>
-- Sebastian Mendel
------------------------------------------------------------------------------ _______________________________________________ Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
participants (4)
-
Derek Schaefer -
Marc Delisle -
Michal Čihař -
Sebastian Mendel