We use JSF over Tomcat along with a backing proprietary DB.
I want to know how the addition of new database fields or/and stored procedures should be handled when we deploy a new version of the application.
We deploy by sending a WAR file to the hosting Team, as such our WAR should be able to setup the stored procedure when it starts.
What are the possible appropriate strategies to be able to incrementally update the database?
I agree with @FrustratedWithFormsDesigner in that it is quite possible, AND that the script for updating your database structure should not be brought along with your application code. Because of this your application would require other permissions allowing to create tables, alter tables, etc. which you most probably don't need during the "normal" usage of your application. So, in terms of security, not a good thing, since your application should only have the permissions needed to work properly.
Anyway, back to the main point. It is quite possible to properly do that by creating a special table in your database, let's say X_VERSION, in which you will simply have the current version of the database schema (it may be a timestamp too). Whenever you alter your database schema, you put that change into a SQL script named [Timestamp-XXX-WhateverItIsYouAreDoing.sql], where:
Whether your database updating program is in your application or outside does not matter for the following steps; whenever you deliver a new version of your application, your update program should do this:
Loop over the scripts, and for each script
When all scripts are done, you know your application can safely start.
Bonus: If you want to have a safe way to go back to a previous version, whenever you create a script altering your database, always create somewhere a script that will allow you to go back to the previous version. It may sometimes be a bit hard (like removing a column for example, not all DBMS allow this), but always try. You will then be prepared to revert to a previous version if necessary.
Jelayn's answer is pretty good, and represents roughly standard practice, esp in the Rails world where a similar solution is baked into the framework. However, the state of the art, as practiced by Facebook, Flickr, Heroku, and IMVU - a pioneer in this - is a bit more advanced.
The problem with the standard practice is that it assumes you can write 2 versions of code, and switch from one to the other with a quick migration as you deploy. There's lots of problems with this:
If you have a lot of data, a migration will take time. It takes months for Facebook to migrate data, but unless you have very little data, it's rare for the migration to be instant. The solution to this is to migrate slowly. IMVU pioneered the strategy here:
during a migration, your code needs to support the old schema and the new one. So you should write the code that supports both, then migrate, then remove the old code in a separate deploy.
upon a failed migration, you should decide whether you can migrate in both directions or just forwards. IMVU goes forward only I think - so if they have a bug in their migration, they fix it instead of rolling it back. I personally prefer fixing, but there are good arguments for rolling it back too.
This stuff is all highly related to continuous deployment as well, and a lot of those strategies can help. For example, a common CD technique is selectively enable new code only for a small user base to give it more testing, and only then enable it for larger and larger audiences (typically, you use a DB setting for each feature). This practice allows you gain more confidence in your migrations, esp when they are more complicated than just adding a field, and without betting all your data on a relatively untested migration.
Source: market research and talking to customers while making my Continuous Integration and Deployment start-up.
I usually have db changes deployed outside the main application, usually via a script that is executed by the DBA.
I suppose you could do the changes from inside your application, if your application has permission to modify the database structure. I guess what would have to happen is your application would start up, some new procedure would run at startup, make all the necessary database changes and then probably restart. The trick is to make sure the changes only happen once. This could probably be done by checking a version number in a special table and using that to decide if the database should be modified.
It also adds db maintenance activities to the main application, which IMO is not a good idea because the application is probably designed to do something else (bloggin, banking, medical health records, online hotel reservations, etc...). Sometimes you may want to keep your database deployments completely separate from the core application.
From a technical point of view, it's quite possible to do this. Should it be done? That's a different question.
The two standard approaches for database updates that I've seen applied are:
In either case, the end result should be the same. However, the approach you take should reflect the principles that your development group prefers - automated vs manual, etc.
