Subject Re: [firebird-support] Maintainance of stored procedures
Author David Johnson
Big A-men Helen! Preach it sister!

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.
To me, that sounds like a very good thing. Suicide is not a long term
survival strategy. It also sounds to me like you need to get some DBA's
with experience, not jumped up interns.

I'll add that the effective use of an ORM depends on clean database
design and administrative practices. Using the ORM to mask the real
problem (insufficient rigor in design) is a one-way ticket to support
h...ades. Ask me about what inheriting sole 24x7 support for a poorly
designed and executed but mission critical app is like - for five years
- off list - after you install industrial strength earplugs and install
a cuss filter.

Much of the flexibility in data types embedded in the SQL standard are a
result of the early days of computing, when storage was a high cost
item. Commodity storage and modern programming languages have made the
plethora of data types available in the SQL standard pretty much
obsolete. Since commodity storage costs about $1.00 per gigabyte now,
the only reason to tightly limit your column width is for performance
(and until Firebird 2, the index node byte limit).

For 90+ percent of your work, the only data types that you need are:
32 or 64 bit integers (pick one depending on your application),
numeric (15,4) for currency,
VARCHAR's with lots of extra room, and
double precision floating point

I do not use triggers or stored procedures extensively. Most of my work
now is in Java, using the Jaybird JDBC drivers. Nevertheless, a change
to the type of a column is still a high impact change that requires full
regression testing (xUnit is your friend) and is considered risky
because it is easy to hit an unexpected dependency.

I would go so far as to say that a data type change after I have
deployed a product would have me questioning the fundamental design
parameters of the application.

On Fri, 2005-08-19 at 09:56 +1000, Helen Borrie wrote:
> At 09:08 PM 18/08/2005 +0000, you wrote:
> >Hi,
> >we have just completed our database design,and stored procedures
> >also.Now,there were some issues...some columns datatypee was required
> >to be changed..but Firebird is not allowing it as it says there are
> >dependencies due to stored we have to drop all those
> >stored procedures..
> The "R" in "RDBMS" stands for "relational" in "Relational Database
> Management System". If an RDBMS is implemented soundly, the "Management"
> will provide systemic support for all dependencies in databases. This
> includes preventing anyone from making changes that will break dependencies.
> >Our database team is worried it might become
> >difficult to maintain database and stored procedures..are
> >their some ways to handle such scenarios?
> Definitely. Database teams need to follow effective organisational
> practices. The fact that the discipline of database system integrity has
> your team worried seems to indicate that it has a need to address change
> control.
> >P.S because of such problems..I dont like SP's...dynamicSQL using an
> >ORM tool is the way to go..
> Statements like this throw me into despair. ORM tools are handy, but they
> will never circumvent problems that arise from an inconsistent, unstable
> design and poor object management practice. One of their worse aspects is
> that they allow anyone to be the SYSDBA. This is antithetical to handling
> the scenarios that have got your database team worried.
> One of the prime objects of a database management system is to enable the
> abstraction of business rules so that they are enforced by the db system,
> independently of whatever application code is thrown at it. There *are*
> well-recognised ways to achieve this externally, through solid design and
> careful layering of application code in a 3+n tier architecture, with
> centurions armed and ready at the gate.
> In 2-tier client/server it is risky business at best to rely on client code
> to implement rules.
> ./heLen
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Visit and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
> Also search the knowledgebases at
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links