Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of rmoazeni@gmail.com designates
 66.249.92.173 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=eaBZBicp/pJBJ2uYE1GeOEJjs7iX+KgrnPoLeoAV3x5STzgVULWlOzhE3kw76L6aR5q05gF18kwnuWzZRV0VHYFVFcNxWcjTKJISV6MAzv4QlQzIeeP147qfMXOtqrO5LlS9ZGOHWXxQ+vDsctK/dZGFUl0lbWS/Rm3d+CZ1+FY=
Message-ID: <926963240606290325l635a2a0bw152df6a1a0aadcf@mail.gmail.com>
Date: Thu, 29 Jun 2006 03:25:31 -0700
From: "Ramin Moazeni" <rmoazeni@gmail.com>
To: derby-dev@db.apache.org
Subject: Re: MySQL to Derby Migration Tool
In-Reply-To: <44A1AB20.40609@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <926963240606212004g243a462ai7e9129b9ba31c42a@mail.gmail.com>
	 <44A1AB20.40609@sun.com>

Hi David,

Thanks for your comments.
My comments inline....

Thanks,
Ramin

On 6/27/06, David Van Couvering <David.Vancouvering@sun.com> wrote:
> Hi, Ramin.  Great document!  Very nicely done.
>
> Here are my comments:
>
> - You're a UML use case guy.  Great!  Wish there was more of that in
> software design, especially for tools.
>
> - What's the mapping from MySql YEAR to Derby?
Derby does not seem to have a YEAR data type. We can
probably use INTEGER instead....Any thoughts?

>
> - Can you explain a little more how DdlUtils takes care of data type
> mapping?

ddlutils tool uses JDBC datatypes rather than SQL datatypes which are
database dependent. Looking at
http://db.apache.org/ddlutils/database-support.html, explains mapping
between JDBC types and SQL types.
For example, a TIINYINT datatype from MySQL, will be mapped to SMALLINT
in Derby.

I will explain this in detail in the document.
>
> - Can you explain a little more what you mean by "A possible solution
> would be to execute database-specific commands to capture required
> information." when you say DdlUtils doesn't support constraints,
> triggers, stored procedures, etc.

In the case of MySQL, we can use the SHOW command to capture the exact
string to re-create the original object. For example
SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION
can be used to capture the original statement used to create function/procedure.

I will add these information to the document.
>
> - I guess I don't follow the application flow very well when using
> DdlUtils.  What exactly are the steps taken by the user and/or the tool
> using DdlUtils?

I probably need another usecase diagram for ddlutils. I will add that
to the document.
>
> - What is *your* recommended approach (DatabaseMetadata vs. DdlUtils)
> and why?
I would think a combination of ddlutils and DatabaseMetadata approach
is needed to handle cases not supported by ddlutils like stroed
procedures.

I will add a section to the document on this.
>
> - Can you talk more to what happens if there is an error during
> migration at various phases. What steps does the tool take to set things
> right?  Can you end up with a half-created database?  What do you do to
> fix the errors and start over?  I suspect migration is often a
> repetitive effort as you catch migration/mapping errors, fix things, and
> try again...  This should be as painless as possible.

Sure, I will add this information as well.
>
> - Can you please show the proposed syntax and other details (input
> files, config files, env variables, exit status) for the CLI?

Ok.

>
> - Does the CLI have a "headless" mode (e.g. no interactive prompts) so
> you can run it in a script?
Good suggestion.....I was not thinking about CLI with headless mode.
I will add information on this.

>
> - What is the syntax for starting the tool?  Will you make use of
> derbyrun.jar?

I will need to investigate this.

>
> - Can you cover quickly how you'll address internationalizing the GUI?
>
Sure
>
> - Can you explain briefly how, using this architecture, you can plug in
> other data sources besides MySQL?
>
Ok.
>
> - You might want to touch on the impact to existing JDBC applications
> that are running against MySQL, which you want to migrate to Derby.  How
> are *those* migrated, and how much or how little does this tool help
> with that?  If anything, just make it clear that application migration
> is out of the scope of this tool.
>
 Ok
>
> - What mechanism are you going to use to do the data transfer?  Simple
> JDBC inserts?  Bulk insert?  For large databases the mechanism you use
> can have a large impact on performance, and may actually prevent some
> databases from migrating.  It would be good to have a section on
> performance and what approaches you will take to improve performance and
> test performance.
I will add a section on performance.
>
> - What about migration of users, groups, permissions, etc. -- is that
> out of scope?
Good suggestion....I did not think about this.
I will add it to the docuemtn

>
> - Perhaps it would be good to be *very* explicit about what you plan to
> migrate and what you *don't* plan to migrate in this tool, and what you
> recommend for users for the bits that aren't being migrated
> (applications, authorizations, triggers, whatever is on that list).
>

Ok

> - This may not be your itch to scratch, but can you address what you're
> planning to do to (or not do) to address governmental accessibility
> requirements (called Section 508)?  See
> http://java.sun.com/products/jfc/accessibility/index.jsp.  At a minimum
> it would be great if you don't do anything that makes it difficult for
> someone else to make the tool accessible.
>
Ok. I will look into this.
>
> Thanks!
>
> David
>
> Ramin Moazeni wrote:
> > Hello,
> >
> > I am a Google Summer of code participant working on the Derby
> > Migration tool project. The High level design document is posted at
> > http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument
> >
> > There are 2 approaches described in the document -- one based on the use
> > DatabaseMetaData class and the other is based on the use ddlUtils tool
> > (http://db.apache.org/ddlutils) which is also an apache project.
> >
> > I would appreciate your feedback and comments....
> >
> > Thanks much
> > Ramin Moazeni
>