databse schema update

[triathlon98]

Automatic database schema update is now possible with the new <alter-table>true</alter-table> feature in jbossjdbc-cmp.xml.

It is assumed that alter-table is used in combination with create-table.
When checking the table structure, the new tables are being added,
and fields which did not exist are being added (by comparing with the existing table in the datasource).

Field which do not exist in the new definition but which existed in the database are being removed. This is somewhat dangerous and assumes that all the fields are represented by the EJB. It would probably be best to make this configurable in some way.

Tables are not removed as this may cause too big a data loss. However, link tables which are not declared in the schema cause a warning.

At the field level, for String fields it is checked whether the newly declared length is longer than the current length. If that is the case, the length is increased. There are no other type or precision conversions.

Basically this should make small changes to the database automatic when deploying, making life easier when upgrading your applications.

Joachim

[triathlon98]

Heiko Rupp wrote :

one one side, I really see this as a cool feature during development.
On the other side, this is very dangerous.

You e.g. have two columns street and housenumber. Now you delete
housenumber as you wish to have both fields in one column.
The data in housenumber is deleted, but the data in street is
not updated to now include the housenmber. So in the next run
you might end up looking for lost data.
Perhaps a fat warning should be printed when columns are removed.

Answer :

True, this is a feature which is to be used with caution. However, I do not think it is development only. In may case, I will have many identical systems (not a cluster) at clients, and having to manually update the DB structure can be very time consuming in that case.

However, as you said, data loss is possible by using alter-table. I tried to prevent it somewhat by not automaticall removing tables or links.

I could add the warnings you mention, but which warnings do I have to include? Just the warnings which indicate that a field has been removes, or also warn when a string field has been modified?

Joachim

[pilhuhn]

I'd include both at log level warning, as those events don't happen often, but have a certain severity.

[kevinconner]

It is dangerous to do this automatically.

I modified an earlier version of jboss to prevent the deployment of beans that did not match the db schema in some way. I found this to be a better solution as the majority of the changes made by my team were rarely as straight forward as adding or removing a field.

The removal of fields usually meant that others had to be updated based on the data and even the simplest of additions usually meant that a default value was to be used.

Kev

[triathlon98]

"Kevin Conner" wrote:
It is dangerous to do this automatically.

Don't worry, it is an option. The update is only done automatically when you turn thison explicitly.

Joachim

[triathlon98]

"pilhuhn" wrote:
I'd include both at log level warning, as those events don't happen often, but have a certain severity.

Ok, done.

Joachim

[kevinconner]

"triathlon98" wrote:

Don't worry, it is an option. The update is only done automatically when you turn thison explicitly.

Joachim

I'm aware of this but it is still an option that can be turned on by accident. One of my colleagues recently turned on the 'remove-table' option by accident, actually it was his jalopy formatter that did it, and it resulted in the removal of a few of our tables. Of course we only discovered this when the system was rebooted :-).

The point I was trying to make is that it there is often more to the change than just adding or removing a field, it usually involves transforms of some sort. And while adding a field is certainly safer than removing one, it still doesn't handle default values.

This is why we changed our version of the code to prevent the deployment, it was telling us that we hadn't performed the correct transform and that the current database schema didn't match the beans.

Any chance you can add this to the code base? How about a 'validate-table' option?