I understand hbm2ddl has some capability for doing DB upgrades - adding new columns and so forth - but is there a framework out there for doing more comprehensive database refactoring?

Ideally I want a system that would allow me to migrate databases between different versions of the product - specify what fields move where - what the defaults for new columns is - how classes should be split up or joined in the new schema - so on and so forth. At the moment migrating data between different versions of the applications particularly with Agile methodologies with regular releases is pretty much impossible. Generally we just have to blow away the DB and start again.

Does such a thing exist? Or does someone even thought about the requirements for such a system? I figured something that exported the old schema to XML then reimported it into the new system using Java5 annotations to specify any required data transformations may work - or at least allow an expansion of the current hbm2ddl functionality.

Here is a catalogue of DB refactorings (not all relevant to Hibernate)

http://www.agiledata.org/essays/databas ... talog.html

And I guess there additional ORM refactorings as well - (changing class hierarchies etc).

Alex

we don't have it, but tools.hibernate.org would be a good place to put such functionallity.

I would definitly like to see it and i have some notes (in human memory) on how it should be done.....but not so much time.


What basically should be done is to mimic the good practices of keeping a "delta" around between each release. Generating these delta's would be nice and is possible in two ways:

Load your configuration and perform a <hbm2ddl update="true"> against the jdbc schema and you get a "best guess".

A even better way would be to compare the old Configuration and new Configuration together with JDBC since it would give more context.

these delta's should/could then be represented as a Migration class which would be ordered by 'revision number' which then could be applied sequentially to upgrade the db. The migration class'es could be trivial execution of db specific SQL DDL and/or more high level operations expressed through hibernate's sql generation code (this last part is not polished nor public api yet, but at least some parts of it is).

Thank you for your reply - I understand that hbm2ddl would be able to cope with schema changes (new columns, tables etc) but the big problem for me is how you would deal with the DATA in the tables.

The scenario is that we having rolling releases of the application - and then incremental fixes after it has gone live as required. During testing we don't want the testers to have to recreate all their test data for each release - and once we have gone live we certainly can't afford to lose any of the live data. So we need to be able to specify not just what schema changes have happened but also how to transform the data between the two schemata - potentially creating moving data between tables, spltting rows into separate tables depending on certain criteria etc etc.

I appreciate this is extremely non-trivial - and hbm2ddl is a good start - but I wouldn't want to run it on a system with production data in it - at least not without thoroughly testing it first.

Have you any ideas on how to approach this issue?

what i posted also is what I would do to handle data migration. Each Migration should of course also contain the needed instructions for moving data. hbm2ddl does not currently do anything to create such data, but what I was suggesting is that you could at least use hbm2ddl to generate the initial version and then enhance it with the knowledge you have about how data transfer should occur. (hbm2ddl could of course also be enhanced to help identify the needs for any data migration)


...but none of this is ever going to be automagic! Human knowledge needs to be in the loop somewhere.

Patches welcome.

It's certainly a nasty and complex issue - I was just looking for

a) A framework to enable developers to quickly and easily specify the data changes as the make changes to the model (hence my thoughts were to use Java5 Annotations to do this) with an external module to extract the old data, apply the required changes, and then load it into the new schema. (Or maybe even better apply the changes in situ - but I think that is too ambitious)

b) Some ability to validate that all required data changes have been specified as model changes occur.

There is a solution of exporting all the data using JAXB (or equivalent) - applying an XSLT and then reimporting it - but that requires a complex XSLT to be written and kept in sync with model changes.

Not much really... - I appreciate that it will require human involvement - I just want something to make it easy to do - AND enforce that it is done.

This is exactly what the Migration class should be able to do. Much similar to what ROR is doing for migration...something similar should be very doable in Hibernate context. Just someone need to step up to the plate and do it ;)

btw. annotations is probably not the best thing for this...will get too crowded/db-specific I would think.



hmm...how would/could you validate that ?



sounds veery inefficient.

Thank you again for your reply - looking at the Migration stuff from ROR. It incorporates my own ideas about defining a set of possible refactors and listing them in one place - with an attached version number. Extending this framework to support more complex ORM refactors (changing class hierarchies etc) and getting this to work with Hibernate without all the ROR tools - is certainly going to be a fair amount of work. I'm also a little unclear about how well this might work in a multiple developer concurrent version control (SVN) system.

The reason I thought about using annotations to describe the migrations was for the same reason for using Hibernate Annotations - namely keeping the code and configuration in one spot. However - having separate migration code with some validation that it is in step with the actual model changes would be a neater solution in many ways. Perhaps there could be a compromise of creating a migration file for each release from the annotations as a separate build task - and removing them once the migration file has been successfully created and validated. I also feel that annotations would work better with SVN than a separate Migration file manually maintained.

By
I mean something in the build process that validates that the OLD SCHEMA + MIGRATION CODE = NEW SCHEMA - and possibly enforces that new entities are marked 'NEW' as of a specific version of the application - and so forth.

I appreciate the XSLT solution is pretty inefficient - but it has been proven to work the past albeit very very slowly. It also would be the easiest to get off the ground quickly.

Thank you again for your thoughts on this matter - from what I understand Hibernate is considerably more fully featured than ROR's active record system - and providing a similar migration framework would be fantastic but would be a bigger task.