I'm currently evaluating Hibernate and couldn't find info about the following.

I wonder whether Hibernate's DB-Schema tools allow alteration of the data in the DB tables,
in addition to the schema update.

In other words, does Hibernate offer any help with the update of table data between releases
of a product (e.g. deletion of a column, move of an entire column from one table to another,
recalculation of field in an existing table, etc

No it does not. You can use SchemaUpdate to adjust your database schema, but it surely is not powerful enough to work on already in production databases.

Thanks for the prompt reply.

I wonder if this feature will be available in the upcoming releases of Hibernate.

Thanks,
Daphna

Most likely not.

Please note that the reason for this is the bad quality and diversity of JDBC metadata provided by current JDBC drivers. With better metadata, schema evolution would be much easier to do.

Can anyone recommend a good (preferably open source) tool to do this?

Or what about generating a new schema and then moving the old data into it? Is this a feasible use case for Hibernate 3's XML capabilities? (I'm just now starting to evaluate a migration from Hibernate 2 to 3 for our application.)

Thanks.

I haven't used it, but I just started looking at the Enhydra Octopus project.

http://octopus.objectweb.org/index.html

It might be worth looking at. Maybe someone else already has experience/opinions they could share about the project.

You could have two sets of mappings with two a separate session factories each. This way you will have two domain models and you can do your data migration in java.

Of course this is a lot slower than some plain sql update statements. But if you have to do a lot of businesslogic it may be worth the performance loss.

HTH
Ernst

Thanks for these suggestions, but I'm looking to automate the process as much as possible. Ideally, the tool would synchronize one schema to another and let us supply defaults for things like new NOT NULL fields, etc.

I apologize for going slightly off-topic from Hibernate, but I think this topic must be relevant to many Hibernate developers that need to deal with schema upgrades.

Agree, is it that no one has cracked this yet? What is the smartest way to deliver applicaiton upgrades to the customers you support without imposing a draconian process of incremental sql scripts (and the errors they will bring) on your developers?

Perhaps the answer is that so far there is none?

Since I wrote that last message, my company has implemented its own strategy to do this. It's really not too hard to manage once a process and basic framework is in place.

Basically, developers can make changes to the schema at any time by modifying the Hibernate mappings. We continue to use Hibernate to generate new (not upgraded) schemas, and every developer works with her own copy, so it's pretty impossible for them to forget this step.

To support upgrades, I added a new table to the schema that holds the current repository database version number. Then, every week or so, I create a special Java class called a "delta class" that implements a very simple interface (one method called "run"). There is one delta class per repository version. The job of the class is to implement all of the upgrade logic for changes made in the prior week. This isn't as hard as it sounds because you can use a tool like TOAD for Oracle to automatically detect differences between two schemas and generate a synchronization SQL script. The script has to be tweaked a little to do things like supply defaults for new NOT NULL fields, but mostly it can be dropped in place and executed by the delta class.

Next, I wrote a program that reads in the current repository version number and uses reflection to discover all of the delta classes. Then, it invokes each delta class in sequence starting from the one following the repository's current version number. Add water, stir, and now you have an upgraded repository. I can test the upgrade by using TOAD (again) to make sure there are no diffs compared to a fresh schema.

Of course, upgrades only work if the upgrade logic is completely up-to-date. We don't even try to support this on every nightly build of our app. Instead, we have the concept of an "upgrade checkpoint" build, which usually comes right after I add a delta class, and we tell QA that they can only upgrade between two checkpoint builds. We publish a list of them on an internal web site. Customers only see checkpoint builds.

I'm leaving out a lot of details, but hopefully this gives you some ideas. I've come to the conclusion that looking for a completely automated solution is a fool's errand. There's invariably some upgrade logic that MUST be written by a human, like columns moving from one table to another, data (non-schema) changes, etc. (Data changes in particular can't be detected automatically. Developers have to point them out.) But this approach automates most of the mundane stuff (with the help of TOAD), and still provides a way to inject custom upgrade logic when needed. All in all, this takes about a half-hour per week of my time to maintain.