Re: [Treebase-devel] TreeBASE migration project

[Hilmar L. <hl...@du...>]

On Apr 23, 2009, at 4:58 PM, Rutger Vos wrote:

>> It's not a good way to manage schema versions, though. I.e., there
>> would be none, and accordingly, you then can't have software that  
>> uses
>> the database directly rather than through Hibernate and still relies
>> on anything, and you can't make any direct modifications to the  
>> schema.
>
> So how do we make the jump to a situation where we have a versioned
> schema? The way I imagined it would be to let hibernate generate a
> pg-compatible schema file, run it on a fresh pg instance and stick the
> file in the repository.

That's a possibility. It would require that the webapp code actually  
runs w/o error to that point.

Another possibility is to take the schema dump from DB2 and convert  
from there. The schema dump should be easy to obtain, so that's where  
I would start. If the result looks like a lot of work, I would then  
try the hibernate route.


> Now we've made the jump. Then - what happens if we need to alter  
> one table (e.g. we need to make the "abstract" field of "article"  
> longer)? We run an "alter table'" command an paste the command at  
> the bottom of the schema file? Or is this a point where we change  
> the original "create" command for the "article" table and
> rerun the whole script (which might involve dropping and reloading the
> whole database)?

Yes and no. Yes, you do change the original CREATE command (so that  
your master script for instantiating the database from scratch stays  
up-to-date).

To migrate existing database instances, you write a script that  
applies all necessary changes from the previous release to the next,  
without dropping or losing data. There are examples for how this  
looks like in BioSQL:

http://tinyurl.com/cxwz6f
http://tinyurl.com/csx2mj

	-hilmar



-- 
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:- hlapp at duke dot edu :
===========================================================