Re: The quest for opensource database...

"Tony Marston" <[e-mail]> wrote in message
news:c6amar$f5l$1$[e-mail]...
>
> "Useko Netsumi" <[e-mail]> wrote in message
> news:c69j32$9rkga$[e-mail]...
> > Dan, I think that is what I want to convey to Tony that sure any expert
on
> > any programming language can write anything with that programming
> language,
> > but is it the wise thing to do though when other has done it and thought
> > about that particular function for quite sometimes.
> >
> > Tony, I'm sure that you can do almost everything with PHP, but do you
> think
> > it is wise? Just a question from an experience user. Thanks
>
> Yes, it is wise, IMHO, for the reasons I have already stated:
> (a) I like to keep all my code in PHP modules rather than spread them over
> database triggers and stored procedures. This is what "encapsulation" is
all
> about.
> (b) I can often write code faster in PHP than SQL, so I have no incentive
to
> write SQL other than what is contained within my PHP code.
> (c) There are things you can do in PHP that you cannot do in SQL.
> (d) Debugging triggers or procedures is not easy, so if you have a problem
> it can be very difficult to track down which unit it is in. If all the
code
> is within PHP then it is a simple matter of stepping through with your
> interactive debugger.
> (e) Code inside triggers or procedures MAY execute faster than PHP code,
but
> where the most expensive item nowadays is the cost of the developer then
> this is the area where the greatest savings can be made.
>
> Just my tuppence worth.
>
> --
> Tony Marston
>
> http://www.tonymarston.net
>
>
>
> > "Dan Scott" <[e-mail]> wrote in message
> > news:c68tvh$svp$[e-mail]...
> > > Tony Marston wrote:
> > >
> > > > You do not need stored procedures or database triggers to write
> > successful
> > > > applications. I once had to maintain a system that was built around
> > > > procedures and triggers, and it was a nightmare. The problem was
that
> > one
> > > > trigger/procedure updated several tables, which fired more triggers
> > which
> > > > contained more updates which fired more triggers ..... It was
> impossible
> > to
> > > > keep track of what was being fired where.
> > >
> > > Design of complex systems is, necessarily, complex. One approach is to
> > > consolidate all of the logic at one layer -- but that can result in
> > > significant performance differences for an app that uses stored
> > > procedures / triggers / functions to avoid communications overhead of
> > > the client-server interactions and to take advantage of the built-in
> > > optimizations the SQL engine can use for stored procedures /
functions.
> > >
> > > > Another reason I prefer to put all my business logic into PHP code
> > instead
> > > > of triggers is that PHP code is a lot easier to debug. Have you come
> > across
> > > > an interactive debugger for database procedures and triggers?
> > >
> > > Not quite on topic, because it's not an open source database, but DB2
> > > does includes an interactive debugger for stored procedures in the DB2
> > > Development Center
> > >
> >
>
(http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/
> > ad/t0007399.htm)
> > >
> > > In some cases I would argue that issuing a couple of CALL and SELECT
> > > statements is a lot easier than trying to figure out whether you've
> > > introduced a problem in your PHP code or in your SQL statements within
> > > the PHP code.
> > >
> > > Dan
> >
> >
>
>

Tony,

I find SPs are useful when invoking complex routine queries against the dbms
on machine 'B' from the PHP instance on machine 'A', when the alterantive is
to load gazillions of rows across the wire from 'B' to 'A' in order to
process them locally.

Triggers are more of a problem. I hate using triggers to perform relational
tasks, such as updating table 'C' in response to an update to table 'D'.
However, triggers can be useful when the update to table 'D' needs to
trigger an external event in the environment of the dbms, especially again
when the dbms machine is not the same as the machine running the script. An
example is a change management workflow system I wrote, where a change to
the status of a change request needs to cause an email to be sent to the
next person in the flow. I implemented this using a trigger on the CR table
and had the dbms server figure out who to email and then send the mail
directly, instead of handing the information back over the wire to a remote
client with an (unknown?) breed and version of mailer software and trusting
the client to send the mail.

In general, I agree with your sentiments, but like all tools, I think SPs
and triggers have their place - it just is not good design to use them to
save thinking about relational integrity and cascading effecs.

Just my $0.02
Doug
--
Remove the blots from my address to reply