OpenJPA and Schema Migration

[Jesper Skov <js...@jyskebank.dk>]

Hi,

I have what I suspect may be a silly question, but which I nevertheless have been unable to find a good answer to.

I have written a PVR system in Java using OpenEJB+OpenJPA for persistence to a mysql database.

The entities in use are mostly TV schedule information, when to record a given program etc. Most entities are auto-generated by pulling in TV schedules from external sources. But there is a bit of user generated data, namely favorite programs, programs flagged for recording and a few preferences.


My problem is simply that I cannot figure out how to make schema migration when I push a new version of the software to the "production system".

Up till now, I have dropped the database and manually re-entered favorites and marked programs for recording. But it is obviously not a long-term solution.
And it is not even a good short-term solution, as the primary customer of the system is my wife. And I lose brownie points, if a program she has scheduled for recording is forgotten in an update :)



When deciding to go the JPA way, I must admit I did not investigate how to handle this issue. And even if I had, I guess I would have been just as stumped as I am now.

Is my google search terms just wrong? Am I using the wrong terminology? Or is everybody else magically patching their database scheme outside the application during an update? Or did everybody else just get their model right in the first version? :)


Any pointers appreciated!

Cheers,
Jesper Skov

Re: OpenJPA and Schema Migration

[Kevin Sutter <kw...@gmail.com>]

Hi Jesper,
Changing the schema of the database requires some effort.  From a purely JPA
perspective, it's fairly straight forward to modify the schema.  But, to
migrate your data is a bit more work.  The most common mechanism is to read
from your old schema and write to your new schema.  By using schema name
qualifiers, you should be able to keep your "old" data separate from your
"new" data until you complete the migration.

Kevin

On Tue, Oct 12, 2010 at 6:41 AM, Jesper Skov <js...@jyskebank.dk> wrote:

> Hi,
>
> I have what I suspect may be a silly question, but which I nevertheless
> have been unable to find a good answer to.
>
> I have written a PVR system in Java using OpenEJB+OpenJPA for persistence
> to a mysql database.
>
> The entities in use are mostly TV schedule information, when to record a
> given program etc. Most entities are auto-generated by pulling in TV
> schedules from external sources. But there is a bit of user generated data,
> namely favorite programs, programs flagged for recording and a few
> preferences.
>
>
> My problem is simply that I cannot figure out how to make schema migration
> when I push a new version of the software to the "production system".
>
> Up till now, I have dropped the database and manually re-entered favorites
> and marked programs for recording. But it is obviously not a long-term
> solution.
> And it is not even a good short-term solution, as the primary customer of
> the system is my wife. And I lose brownie points, if a program she has
> scheduled for recording is forgotten in an update :)
>
>
>
> When deciding to go the JPA way, I must admit I did not investigate how to
> handle this issue. And even if I had, I guess I would have been just as
> stumped as I am now.
>
> Is my google search terms just wrong? Am I using the wrong terminology? Or
> is everybody else magically patching their database scheme outside the
> application during an update? Or did everybody else just get their model
> right in the first version? :)
>
>
> Any pointers appreciated!
>
> Cheers,
> Jesper Skov
>
>