> 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/