Database upgrade + data transformation

[Igor Markovic <ig...@izecom.com>]

Hi all,

I want to do some database upgrading by doing the following:

Export old database (data).
Create new database (different schema).
Import data from old database into new database.

The new schema differs in:

New tables are added.
Some old tables are removed.
Table columns are added, removed or renamed.

I will have to import the data and do some transformations on it while doing the import. 

I have for instance a Id column with values that have to be changed to UUID values so I will have to recalculate the ids and replace the old ids with new ones, but I will also have to do this for the columns referencing to these ids.

And it will also be nice if it is possible to change the table/column names so I can import column values in renamed columns.

Is there some way to do this? 

Thanks,

Igor

Re: Database upgrade + data transformation

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

On 11/24/06, Igor Markovic <ig...@izecom.com> wrote:

> I want to do some database upgrading by doing the following:
>
> Export old database (data).
> Create new database (different schema).
> Import data from old database into new database.
>
> The new schema differs in:
>
> New tables are added.
> Some old tables are removed.
> Table columns are added, removed or renamed.

This is no problem, though there is no concept of renaming columns,
only of removing and adding (which is the same except for the data,
but see below).

> I will have to import the data and do some transformations on it while doing the import.
>
> I have for instance a Id column with values that have to be changed to UUID values so I will have to recalculate the ids and replace the old ids with new ones, but I will also have to do this for the columns referencing to these ids.
>
> And it will also be nice if it is possible to change the table/column names so I can import column values in renamed columns.

While this is outside of the scope of what DdlUtils, it is nonetheless
not difficult to achieve with DdlUtils.
Depending on how much data you have in the tables, there are two ways:

(1)
If there is not much data, then you can simply read the data into
memory (dyna beans), transform them (e.g. create new dyna beans for
the new model with the changed column names etc.) and write them to
the new tables.

(2)
If there is a lot of data, it might be better to write the data out to
XML and transform the data via XSLT to the new model (there is a sub
task that creates a dtd for a model which you can use for this). Since
the XML structure is quite simple, this should not be much work.
After it is transformed, simply re-insert it into the database.

cheers,
Tom