> JR> If you don't have a lot of external users, the IBExpert Database > Comparer tool can actually make the updates after creating the script. This > may be available only in the paid version of the tool. It's the best tool I > have found JR> for Firebird databases, it's not too expensive. The only > downside is that the payment process is pretty archaic, but I have done it a > couple of times now and it worked ok. > I was actually using this until a couple of months ago, but then I trashed > my installation and was reluctant to buy another. Their support people told > me that my licence had 'expired' - I guess they meant support licence. So > rather than fork out another few hundred Euros, I thought I try a different > tack. > > I may buy another one because it was very nice, if a little unstructured. > > But with many users (as we hope to have) maybe Svein's suggestion is right > because then I can automate it. > > However, I am where I am. Maybe I'll use Database Comparer to get myself > back to a consistent position and then use Svein's suggestion.
+1. While it might be OK for development or to consolidate existing databases to a common denominator once, relying on a metadata compare tool in the field, which produces some kind of incremental script, that gets executed automatically against a production database, is IMHO a can of worm. I simply wouldn't trust such a solution. While you can use a visual development tool ala DBW for development, I'm in favour of manually maintained incremental scripts when it comes to deployment. For example: * Have a selectable stored procedure in your database with some kind of version information, last executed script number etc. E.g.: SET TERM ^^ ; CREATE OR ALTER PROCEDURE S_GET_VERSION returns ( MAJOR_VERSION SmallInt, MINOR_VERSION SmallInt, LAST_SCRIPT_NUMBER Integer, LAST_SCRIPT_FILE Varchar(255)) AS begin MAJOR_VERSION = 2; MINOR_VERSION = 5; LAST_SCRIPT_NUMBER = 53; LAST_SCRIPT_FILE = '0053_alter_project_event_rule_changes_for_server_event_rule.sql'; SUSPEND; end ^^ SET TERM ; ^^ commit; * Maintain and prepare incremental scripts with a script number in the file name, e.g. 0001_...sql, 0002_...sql etc. Each script must have a new version of the version SP in place, which reflects the current script number. * Upon program start, fetch the last script number from the SP * If a check results in that scripts are pending, make sure that nobody else is connected to the database, otherwise gracefully stop your application with a proper message dialog to shutdown other connections * If you have exclusive access, execute the pending scripts and proceed with application startup I tend to include the SQL scripts via one script component per script on a TDataModule (in Delphi speak) directly in the EXE and not in the file system. But that's just a matter of personal taste. -- With regards, Thomas Steinmaurer http://www.upscene.com/
