Subject Re: [firebird-support] Re: Maintainance of stored procedures
Author David Johnson
No problem ... holy cows are best served over the coals with lots of
BBQ sauce. :o)

On Fri, 2005-08-19 at 07:37 +0000, stefanbalzter wrote:
> --- David Johnson <johnson_d@c...> wrote:
> > The complaint is that the tool stopped the database team from slitting
> > their own throats, and the rest of the company's throats with theirs.
> I say! Hasn't anybody (not even Helen :-) ever thought of the
> possibility that changing the database structure might not be owed to
> poor design but to, let's say, extensions and new features that are
> demanded by customers?

Extensions, yes. Changes to existing columns in a productions system,
only if the original column was actually defined incorrectly for the
process - usually in 15 year old designs where limiting DASD usage took
priority over the "10% of cases" where the data structure was too short.
Of course the net result was that 90% of the on-call programmer's time
was spent supporting those 10% of cases ...

> Don't you *ever* buy an upgrade to any
> application you have? Why should a database be the only object that
> has to be perfect from the start? What if your developing environment
> had prevented you from making changes in Firebird 1.0, stating that
> altering a function would violate its dependencies?

For applications programmers piece that does this is called a compiler.
You have to get your dependencies sorted out before build time, run the
build script, and if any dependencies are not sorted out either the
compiler will tell you (good) or things will blow up at runtime (bad).

To manage dependencies we use change control procedures and tools. The
days of manually typing in every command are, thankfully, over.

> There would have
> never been a Firebird 1.5! And if anybody had complained about that,
> people would have told him that it was his own fault that Firebird 1.0
> was so poorly developed that it needed to be changed!!

If an upgrade to software demands a change in the stored data type, the
original software was constructed around a poor design decision.

> No - anything you work out with and for a PC must have the ability to
> be continually developed! And I have a hard time myself with FB in
> that matter.

Continually developed - yes. Alterations to existing data - as near
never as possible.

>From a business perspective (not technical), when you allow arbitrary
alterations to the data you are interrupting an audit trail. Altering
the definition of existing columns in a database is an easy way to
accidentally lose or alter the data in those columns, without an audit

No audit trail in the post-Enron world means people go to jail if money
is involved. In business, the only reason to record something is that
money is involved. Ergo, in a business support system you need a really
good reason to change any existing data, and it should be difficult
enough to change data structures that you think really hard so that you
do it as little as possible.

If I need to change an existing column in one of my own designs (not one
inherited from other sources), my preferred route is to construct a new
table. The old data is guaranteed intact, available, and untouched. This
is done for business reasons, not technical. No alteration means no

The technical mechanism I use is naming conventions - every table name
ends in a version number. When a new table goes in to place, so does
its supporting code. The old data is never altered except
(occasionally) to invalidate a row.

> Compare it, for instance, to postgresql: You can change a
> stored procedure in any way you like, and it has never ever corrupted
> my database (not to mention slit my throat) that I was able to do so
> (you can even use domains as data types for input parameters, a BIG
> "everything would break down if we did this" no-no in FB).
> On the contrary, it has enabled me to update my application in a
> simple and effective way. With Firebird, I meanwhile prefer writing
> new functions and just ignoring the old ones, since it is virtually
> impossible to change anything in an existing function (and I mean
> ANYTHING... we are not talking about changing the return data type or
> such things, just any internal calculation within the SP).

Then there is room for technical improvement. I'm not a heavy
SP/trigger user so I haven't noticed this. Have you offered to build
some of these features for upcoming releases of Firebird? From reading
other suggestions on this list, there are a number of short-term
automations, and others are constructing the exact tools you are asking

> Sorry for attacking a holy cow if I did so, but I seem to be not the
> only one whom these things bug.
> Stefan