[Phpmyadmin-devel] Suggestions for Import/Export Changes

[Derek Schaefer]

On Tue, Jun 30, 2009 at 12:53 PM, Marc Delisle<marc at 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 at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel
>