Google SOC: MySQL to Derby Migration Tool

[Ramin Moazeni <rm...@gmail.com>]

Hello

I am a google summer of code participant. I am working on
MySQL to Derby Migration Tool project. One of the approaches
that we are considering is to use the ddlutils tool. Given the
fact that ddlutils is missing some features such as support for
migration of views and stored procedures, I was wondering what
people on this alias think about the effort that is needed to add
those features to ddlutils?

Comments are appreciated...

Thanks much
Ramin Moazeni

PS: The other approach is the use of DatabaseMetadata for migration.

Re: Google SOC: MySQL to Derby Migration Tool

[Thomas Dudziak <to...@gmail.com>]

Hi Ramin,

On 7/12/06, Ramin Moazeni <rm...@gmail.com> wrote:

> I am a google summer of code participant. I am working on
> MySQL to Derby Migration Tool project. One of the approaches
> that we are considering is to use the ddlutils tool. Given the
> fact that ddlutils is missing some features such as support for
> migration of views and stored procedures, I was wondering what
> people on this alias think about the effort that is needed to add
> those features to ddlutils?

DdlUtils will definitely provide support for such extended features as
far as it makes sense in a cross-database matter. E.g. views,
triggers, sequences, and even stored procedures can and should be
supported. In fact, there is an issue for that
(http://issues.apache.org/jira/browse/DDLUTILS-28) that is currently
targeted for the 1.1 release.

Adding them should not mean too much work. Basically it boils down to:

* define the XML elements (e.g. in
http://svn.apache.org/viewvc/db/ddlutils/trunk/src/schema/database.xsd?view=markup)

* define the corresponding API in the platform

* implement the reading and writing of the elements in the model
readers and sql builders

As far as whether it makes sense to you to extend DdlUtils instead of
use JDBC meta data etc., I have to say it depends. The approach that
DdlUtils takes right now, is to express everything in terms of JDBC
with database-specific overrides (e.g. for stored procedures you will
have database specific sub elements in the XML that contain the
definitions of the stored procedure for each database).
This however means that there might be a case where a direct
conversion via JDBC meta data might provide more detail, esp. when it
comes to data types. DdlUtils maps native types to JDBC types when
reading from a live database and maps them back to native types when
creating a database. This might loose some detail that you can
preserve when performing a direct conversion.
However. The main benefit (aside from already providing a lot of core
functionality that you'd need to implement) that DdlUtils would bring,
is that you would not be limited to MySql and Derby.

That being said, any help with DdlUtils is certainly appreciated :-)

cheers,
Tom