On 13-2-2019 16:58, Kevin Stanton Kevin.Stanton@...
[firebird-support] wrote:

> I’m starting to look into migrating from 1.56 to either 2.5 or directly to 3.0. I’ve bought the Migration Guide but haven’t read it yet.
>
> It concerns me that I would/will have to recompile all SPs and triggers. Is there an automated way to do this? I have 909 SPs and 785 triggers. This will be one heck of a task with my installation base.

The choice is to either carefully and exhaustively verify if all your
stored procedures and triggers continue to work correctly, and only
recreate those that you know need it, or recreate the whole shebang (you
will still need to carefully and exhaustively verify them).

Firebird itself has no way to recreate stored procedure and triggers
automatically, although it is possible to craft scripts that rebuild it
based on the database metadata (assuming the source blobs haven't been
cleared). I would recommend against doing that automatically and instead
you should use known good and verified scripts for the actual upgrade,
if only to avoid running into issue with things that are now no longer
syntactically valid or unquoted use of identifiers that are now reserved
words.

Some people advocate that you shouldn't upgrade a database at all, but
instead recreate it and pump the data over. In a situation with a lot of
deployments that might also fix problems with diverging structure (but
if that is the case, the migration path will be more prone to errors).

Especially when taking the big step from 1.5, it might make sense to
take it as an opportunity to improve your database design, and get rid
of things that are no longer needed (eg use built-in functions instead
of UDFs, switch from dialect 1 to dialect 3 if you are still on dialect
1), etc. and then migrate the data from the old to the new.

Firebird 1.5 to 2.0 was already a big step with a lot of new features,
but also some breaking changes, sometimes subtle, sometimes not. Don't
expect going from 1.5 to be simply backing up and restoring, especially
not with 909 stored procedures and 785 triggers, and more so if you use
a lot of UDFs, or relied on some of the ambiguous behavior that is no
longer valid, used unquoted identifiers that are now reserved words, etc.

And realize that Firebird 2.0 was released 12 years ago. A lot of
migration knowledge from 1.5 to 2.0 may have been forgotten since (or at
best not easily recalled). You should really carefully study the 2.0,
2.1, 2.5 and 3.0 release notes and the Firebird 2 installation and
migration guide, and as you bought it, Carlos' Migration Guide to
Firebird 3.

Mark
--
Mark Rotteveel