The quest for opensource database...

[Ruby Tuesdays]

Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.

At the moment the project is small/medium, but it will grow in size both
data, users, and number of transactions. I'm using MySQL for right now but
it lack of trigger, stored procedures, etc ... it sometimes slows the
project.

Is PostgreSQL any better/worse? Or is that any other choice beside the two?
Thanks.

[Erwin Moller]

Re: The quest for opensource database...

Ruby Tuesdays wrote:
[color=blue]
> Perhaps you database guru able to suggest what would be a good choice for
> opensource database platform to use to develop projects.
>
> At the moment the project is small/medium, but it will grow in size both
> data, users, and number of transactions. I'm using MySQL for right now but
> it lack of trigger, stored procedures, etc ... it sometimes slows the
> project.
>
> Is PostgreSQL any better/worse? Or is that any other choice beside the
> two? Thanks.[/color]

Postgresql is VERY robust and VERY rich with features.
You'll love it. I know I do. :-)

Triggers are implemented. As are serial (autonumber), PK, FK, and all other
types of constraints you can conjure up.

A very strong language for scripting stored procedures is implemented too.
(named: plpgsql)

exporting, backing up, everything is there.

Redhat even developed a very nice GUI for Postgresql 7.3, which they call
RHDB (RedHatDataBase ) which is actually Postgresql 7.3 (i didn't find any
differences)
It is called Database Administrator for Postgres Redhat edition3.

I am still running their alpha, but it works like a charm, so why update?

I hope I sound enthousiastic enough, because I really like postgresql. :-)

Regards,
Erwin Moller

[Tony Marston]

Re: The quest for opensource database...


"Ruby Tuesdays" <NoSpamPlease_r ubytuzdaiz@yaho o.com> wrote in message
news:c63aer$7ck 4g$1@ID-205437.news.uni-berlin.de...[color=blue]
> Perhaps you database guru able to suggest what would be a good choice for
> opensource database platform to use to develop projects.
>
> At the moment the project is small/medium, but it will grow in size both
> data, users, and number of transactions. I'm using MySQL for right now but
> it lack of trigger, stored procedures, etc ... it sometimes slows the
> project.[/color]

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.

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?

--
Tony Marston

http://www.tonymarston.net

[color=blue]
> Is PostgreSQL any better/worse? Or is that any other choice beside the[/color]
two?[color=blue]
> Thanks.
>
>[/color]

[Erwin Moller]

Re: The quest for opensource database...

Tony Marston wrote:
[color=blue]
>
> "Ruby Tuesdays" <NoSpamPlease_r ubytuzdaiz@yaho o.com> wrote in message
> news:c63aer$7ck 4g$1@ID-205437.news.uni-berlin.de...[color=green]
>> Perhaps you database guru able to suggest what would be a good choice for
>> opensource database platform to use to develop projects.
>>
>> At the moment the project is small/medium, but it will grow in size both
>> data, users, and number of transactions. I'm using MySQL for right now
>> but it lack of trigger, stored procedures, etc ... it sometimes slows the
>> project.[/color]
>
> 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.
>
> 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?
>[/color]

Very good advise, Tony.
I second that opinion 100%.
Been there too. :-(

Stick to constrainst like foreign keys (which do fire a trigger I think when
the relevant colums are updated/inserted) and avoid writing your own.
And stick to constraints like CHECK to make sure the data inserted makes
sense to you instead of triggers.
Stored procedures do often shorten the developmenttime , but are hard to
debug.
It is often easy to do 'some quick and dirty' solution using stored
procedures, but when something changes in the application (which happens
all the time) you have to debug those SP's too.

Regards,
Erwin Moller

[Jerry Sievers]

Re: The quest for opensource database...

"Tony Marston" <tony@NOSPAM.de mon.co.uk> writes:
[color=blue]
> 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.[/color]


Right! Gratuitous use of triggers, SPs and the like results in
indirection that can make a system impossible to understand. Use with
care.

But is this a vote in favor of a less advanced system that prevents
the temtation? I don't think so.

YMMV


--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

[Useko Netsumi]

Re: The quest for opensource database...

May be we can get away with the strored procedures. Why would one do
manipulation of the data inside a database engine, right? The function of
database is to store and retrieve information, that's it. Beyond that you
really stretch it and this where scripting language such as Ruby comes in.

But for trigger, to write a commercial grade transaction processing
application, you definitely need trigger. Trigger will aid
programmers/designer to ensure certain consistency checking happen
before/after certain opearation. How would you do that outside the database
engine withouth kludging it?

Just my 2cents back from my college days.


For simple and less-dynamic-update application, you might not need tri
"Tony Marston" <tony@NOSPAM.de mon.co.uk> wrote in message
news:c65jm0$nl$ 1$8302bc10@news .demon.co.uk...[color=blue]
>
> "Ruby Tuesdays" <NoSpamPlease_r ubytuzdaiz@yaho o.com> wrote in message
> news:c63aer$7ck 4g$1@ID-205437.news.uni-berlin.de...[color=green]
> > Perhaps you database guru able to suggest what would be a good choice[/color][/color]
for[color=blue][color=green]
> > opensource database platform to use to develop projects.
> >
> > At the moment the project is small/medium, but it will grow in size both
> > data, users, and number of transactions. I'm using MySQL for right now[/color][/color]
but[color=blue][color=green]
> > it lack of trigger, stored procedures, etc ... it sometimes slows the
> > project.[/color]
>
> 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[/color]
to[color=blue]
> keep track of what was being fired where.
>
> 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[/color]
across[color=blue]
> an interactive debugger for database procedures and triggers?
>
> --
> Tony Marston
>
> http://www.tonymarston.net
>
>
>[color=green]
> > Is PostgreSQL any better/worse? Or is that any other choice beside the[/color]
> two?[color=green]
> > Thanks.
> >
> >[/color]
>
>[/color]

[Tony Marston]

Re: The quest for opensource database...


"Useko Netsumi" <usenets_remote _this@earthlink .net> wrote in message
news:c66462$8be r2$1@ID-205437.news.uni-berlin.de...[color=blue]
> May be we can get away with the strored procedures. Why would one do
> manipulation of the data inside a database engine, right? The function of
> database is to store and retrieve information, that's it. Beyond that you
> really stretch it and this where scripting language such as Ruby comes in.
>
> But for trigger, to write a commercial grade transaction processing
> application, you definitely need trigger. Trigger will aid
> programmers/designer to ensure certain consistency checking happen
> before/after certain opearation. How would you do that outside the[/color]
database[color=blue]
> engine withouth kludging it?[/color]

I personally create a separate class for each database table and define the
constraints within that class. I then have standard code which is used to
deal with these constraints at the appropriate time (see
http://www.tonymarston.co.uk/php-mys...eobjects2.html for some
examples). In this way I achieve the following objectives:
(1) The constraint details and business logic for a database table are
contained in the same class, therefore everything is in one place instead of
being scattered about hither and yon.
(2) I am in complete control when it comes to implementing the constraints,
so I have the power to change them at runtime should the need arise.
(3) If I have a problem I can always step through with my debugger.

--
Tony Marston

http://www.tonymarston.net

[color=blue]
> Just my 2cents back from my college days.
>
>
> For simple and less-dynamic-update application, you might not need tri
> "Tony Marston" <tony@NOSPAM.de mon.co.uk> wrote in message
> news:c65jm0$nl$ 1$8302bc10@news .demon.co.uk...[color=green]
> >
> > "Ruby Tuesdays" <NoSpamPlease_r ubytuzdaiz@yaho o.com> wrote in message
> > news:c63aer$7ck 4g$1@ID-205437.news.uni-berlin.de...[color=darkred]
> > > Perhaps you database guru able to suggest what would be a good choice[/color][/color]
> for[color=green][color=darkred]
> > > opensource database platform to use to develop projects.
> > >
> > > At the moment the project is small/medium, but it will grow in size[/color][/color][/color]
both[color=blue][color=green][color=darkred]
> > > data, users, and number of transactions. I'm using MySQL for right now[/color][/color]
> but[color=green][color=darkred]
> > > it lack of trigger, stored procedures, etc ... it sometimes slows the
> > > project.[/color]
> >
> > You do not need stored procedures or database triggers to write[/color][/color]
successful[color=blue][color=green]
> > applications. I once had to maintain a system that was built around
> > procedures and triggers, and it was a nightmare. The problem was that[/color][/color]
one[color=blue][color=green]
> > trigger/procedure updated several tables, which fired more triggers[/color][/color]
which[color=blue][color=green]
> > contained more updates which fired more triggers ..... It was impossible[/color]
> to[color=green]
> > keep track of what was being fired where.
> >
> > Another reason I prefer to put all my business logic into PHP code[/color][/color]
instead[color=blue][color=green]
> > of triggers is that PHP code is a lot easier to debug. Have you come[/color]
> across[color=green]
> > an interactive debugger for database procedures and triggers?
> >
> > --
> > Tony Marston
> >
> > http://www.tonymarston.net
> >
> >
> >[color=darkred]
> > > Is PostgreSQL any better/worse? Or is that any other choice beside the[/color]
> > two?[color=darkred]
> > > Thanks.
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Useko Netsumi]

Re: The quest for opensource database...

But isn't that supposedly the function of the database trigger - that is to
ensure that all contraint are accounted for without kludging it?

But I do get your point though. I'm sure with expertise such as yours, you
can replace those stored procedures/trigger function in PHP or any scripting
language but isn't that stretching it a bit too far?

With stored procedures/trigger, script programmer can concentrate more in
the flow/design of the application where the database programmer can
concentrate on the data storage(and simple data manipulation using strored
procedures) and data constraints(tri gger).

Just my 2cents.


"Tony Marston" <tony@NOSPAM.de mon.co.uk> wrote in message
news:c669n9$kui $1$830fa7a5@new s.demon.co.uk.. .[color=blue]
>
> "Useko Netsumi" <usenets_remote _this@earthlink .net> wrote in message
> news:c66462$8be r2$1@ID-205437.news.uni-berlin.de...[color=green]
> > May be we can get away with the strored procedures. Why would one do
> > manipulation of the data inside a database engine, right? The function[/color][/color]
of[color=blue][color=green]
> > database is to store and retrieve information, that's it. Beyond that[/color][/color]
you[color=blue][color=green]
> > really stretch it and this where scripting language such as Ruby comes[/color][/color]
in.[color=blue][color=green]
> >
> > But for trigger, to write a commercial grade transaction processing
> > application, you definitely need trigger. Trigger will aid
> > programmers/designer to ensure certain consistency checking happen
> > before/after certain opearation. How would you do that outside the[/color]
> database[color=green]
> > engine withouth kludging it?[/color]
>
> I personally create a separate class for each database table and define[/color]
the[color=blue]
> constraints within that class. I then have standard code which is used to
> deal with these constraints at the appropriate time (see
> http://www.tonymarston.co.uk/php-mys...eobjects2.html for some
> examples). In this way I achieve the following objectives:
> (1) The constraint details and business logic for a database table are
> contained in the same class, therefore everything is in one place instead[/color]
of[color=blue]
> being scattered about hither and yon.
> (2) I am in complete control when it comes to implementing the[/color]
constraints,[color=blue]
> so I have the power to change them at runtime should the need arise.
> (3) If I have a problem I can always step through with my debugger.
>
> --
> Tony Marston
>
> http://www.tonymarston.net
>
>
>[color=green]
> > Just my 2cents back from my college days.
> >
> >
> > For simple and less-dynamic-update application, you might not need tri
> > "Tony Marston" <tony@NOSPAM.de mon.co.uk> wrote in message
> > news:c65jm0$nl$ 1$8302bc10@news .demon.co.uk...[color=darkred]
> > >
> > > "Ruby Tuesdays" <NoSpamPlease_r ubytuzdaiz@yaho o.com> wrote in message
> > > news:c63aer$7ck 4g$1@ID-205437.news.uni-berlin.de...
> > > > Perhaps you database guru able to suggest what would be a good[/color][/color][/color]
choice[color=blue][color=green]
> > for[color=darkred]
> > > > opensource database platform to use to develop projects.
> > > >
> > > > At the moment the project is small/medium, but it will grow in size[/color][/color]
> both[color=green][color=darkred]
> > > > data, users, and number of transactions. I'm using MySQL for right[/color][/color][/color]
now[color=blue][color=green]
> > but[color=darkred]
> > > > it lack of trigger, stored procedures, etc ... it sometimes slows[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > project.
> > >
> > > You do not need stored procedures or database triggers to write[/color][/color]
> successful[color=green][color=darkred]
> > > applications. I once had to maintain a system that was built around
> > > procedures and triggers, and it was a nightmare. The problem was that[/color][/color]
> one[color=green][color=darkred]
> > > trigger/procedure updated several tables, which fired more triggers[/color][/color]
> which[color=green][color=darkred]
> > > contained more updates which fired more triggers ..... It was[/color][/color][/color]
impossible[color=blue][color=green]
> > to[color=darkred]
> > > keep track of what was being fired where.
> > >
> > > Another reason I prefer to put all my business logic into PHP code[/color][/color]
> instead[color=green][color=darkred]
> > > of triggers is that PHP code is a lot easier to debug. Have you come[/color]
> > across[color=darkred]
> > > an interactive debugger for database procedures and triggers?
> > >
> > > --
> > > Tony Marston
> > >
> > > http://www.tonymarston.net
> > >
> > >
> > >
> > > > Is PostgreSQL any better/worse? Or is that any other choice beside[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > two?
> > > > Thanks.
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Tony Marston]

Re: The quest for opensource database...


"Useko Netsumi" <usenets_remote _this@earthlink .net> wrote in message
news:c66fl8$8i9 4l$1@ID-205437.news.uni-berlin.de...[color=blue]
> But isn't that supposedly the function of the database trigger - that is[/color]
to[color=blue]
> ensure that all contraint are accounted for without kludging it?
>
> But I do get your point though. I'm sure with expertise such as yours, you
> can replace those stored procedures/trigger function in PHP or any[/color]
scripting[color=blue]
> language but isn't that stretching it a bit too far?[/color]

It is not stretching anything at all. In my early days of programming, which
was before relational databases were fashionable, none of the database
management systems had triggers or procedures, so I learned to write entire
applications without them.
[color=blue]
> With stored procedures/trigger, script programmer can concentrate more in
> the flow/design of the application where the database programmer can
> concentrate on the data storage(and simple data manipulation using strored
> procedures) and data constraints(tri gger).[/color]

I don't work on projects where the database, business logic and screen
layout are dealt with by separate people as this is a recipe for disaster.
There is nothing in a stored procedure or database trigger that I cannot
achieve more easily with PHP code, it is easier to control and it is far
easier to debug.

--
Tony Marston

http://www.tonymarston.net

[gmuldoon]

Re: The quest for opensource database...

tony@NOSPAM.dem on.co.uk says...[color=blue]
>
> "Ruby Tuesdays" <NoSpamPlease_r ubytuzdaiz@yaho o.com> wrote in message
> news:c63aer$7ck 4g$1@ID-205437.news.uni-berlin.de...[color=green]
> > Perhaps you database guru able to suggest what would be a good choice for
> > opensource database platform to use to develop projects.[/color][/color]

Sorry, can't confidently recommend. Am spoilt, have Oracle.
[color=blue][color=green]
> > At the moment the project is small/medium, but it will grow in size both
> > data, users, and number of transactions. I'm using MySQL for right now but
> > it lack of trigger, stored procedures, etc ... it sometimes slows the
> > project.[/color]
>
> You do not need stored procedures or database triggers to write successful
> applications.[/color]

But they can be very beneficial.
[color=blue]
> 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.[/color]

Bad design/code is bad design/code no matter at what "layer" it is
written. Doesn't mean you should throw the baby out with the bath
water.
[color=blue]
> 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.[/color]

I use stored procedures and triggers for things like serialized
transactions, audit logs and complex calculations. In general (in Oracle
at least) nearly everything that can be done inside the "database
level" is more efficient, sometimes massively so, than the same thing
written at the PHP "applicatio n level".

I've fought similar anti-database-level-coding arguments against the use
of database referential integrity constraints.
[color=blue]
> Have you come across
> an interactive debugger for database procedures and triggers?[/color]

For Oracle many exist, if you have the necessary $$$.

Geoff M

[Dan Scott]

Re: The quest for opensource database...

Tony Marston wrote:
[color=blue]
> 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.[/color]

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.
[color=blue]
> 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?[/color]

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/infoce...d/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

[Useko Netsumi]

Re: The quest for opensource database...

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

"Dan Scott" <dan.scott@ca.i bm.com> wrote in message
news:c68tvh$svp $1@hanover.toro lab.ibm.com...[color=blue]
> Tony Marston wrote:
>[color=green]
> > You do not need stored procedures or database triggers to write[/color][/color]
successful[color=blue][color=green]
> > applications. I once had to maintain a system that was built around
> > procedures and triggers, and it was a nightmare. The problem was that[/color][/color]
one[color=blue][color=green]
> > trigger/procedure updated several tables, which fired more triggers[/color][/color]
which[color=blue][color=green]
> > contained more updates which fired more triggers ..... It was impossible[/color][/color]
to[color=blue][color=green]
> > keep track of what was being fired where.[/color]
>
> 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.
>[color=green]
> > Another reason I prefer to put all my business logic into PHP code[/color][/color]
instead[color=blue][color=green]
> > of triggers is that PHP code is a lot easier to debug. Have you come[/color][/color]
across[color=blue][color=green]
> > an interactive debugger for database procedures and triggers?[/color]
>
> 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
>[/color]
(http://publib.boulder.ibm.com/infoce...m.db2.udb.doc/
ad/t0007399.htm)[color=blue]
>
> 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[/color]

[Tony Marston]

Re: The quest for opensource database...


"Useko Netsumi" <usenets_remote _this@earthlink .net> wrote in message
news:c69j32$9rk ga$1@ID-205437.news.uni-berlin.de...[color=blue]
> 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[/color]
language,[color=blue]
> 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[/color]
think[color=blue]
> it is wise? Just a question from an experience user. Thanks[/color]

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 "encapsulat ion" 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

[color=blue]
> "Dan Scott" <dan.scott@ca.i bm.com> wrote in message
> news:c68tvh$svp $1@hanover.toro lab.ibm.com...[color=green]
> > Tony Marston wrote:
> >[color=darkred]
> > > You do not need stored procedures or database triggers to write[/color][/color]
> successful[color=green][color=darkred]
> > > applications. I once had to maintain a system that was built around
> > > procedures and triggers, and it was a nightmare. The problem was that[/color][/color]
> one[color=green][color=darkred]
> > > trigger/procedure updated several tables, which fired more triggers[/color][/color]
> which[color=green][color=darkred]
> > > contained more updates which fired more triggers ..... It was[/color][/color][/color]
impossible[color=blue]
> to[color=green][color=darkred]
> > > keep track of what was being fired where.[/color]
> >
> > 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.
> >[color=darkred]
> > > Another reason I prefer to put all my business logic into PHP code[/color][/color]
> instead[color=green][color=darkred]
> > > of triggers is that PHP code is a lot easier to debug. Have you come[/color][/color]
> across[color=green][color=darkred]
> > > an interactive debugger for database procedures and triggers?[/color]
> >
> > 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
> >[/color]
>[/color]
(http://publib.boulder.ibm.com/infoce...m.db2.udb.doc/[color=blue]
> ad/t0007399.htm)[color=green]
> >
> > 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[/color]
>
>[/color]

[Francis Hwang]

Re: The quest for opensource database...

"Useko Netsumi" <usenets_remote _this@earthlink .net> wrote in message news:<c66462$8b er2$1@ID-205437.news.uni-berlin.de>...[color=blue]
> May be we can get away with the strored procedures. Why would one do
> manipulation of the data inside a database engine, right? The function of
> database is to store and retrieve information, that's it. Beyond that you
> really stretch it and this where scripting language such as Ruby comes in.
>
> But for trigger, to write a commercial grade transaction processing
> application, you definitely need trigger. Trigger will aid
> programmers/designer to ensure certain consistency checking happen
> before/after certain opearation. How would you do that outside the database
> engine withouth kludging it?
>[/color]

Well, you could write an object-relational mapping layer that executes
triggers in the code before hitting the database. Then you could write
a mock database underneath that which allows you to actually unit-test
your triggers without having to slowdown to hit a live database. I
did:

http://lafcadio.rubyforge.org/

This is newer stuff and no dobut a lot less robust than, say, the
stuff you get in Oracle. But I use this feature routinely in
production work, and I really really really like being able to unit
test my triggers.

Francis

[Doug Hutcheson]

Re: The quest for opensource database...

"Tony Marston" <tony@NOSPAM.de mon.co.uk> wrote in message
news:c6amar$f5l $1$8300dec7@new s.demon.co.uk.. .[color=blue]
>
> "Useko Netsumi" <usenets_remote _this@earthlink .net> wrote in message
> news:c69j32$9rk ga$1@ID-205437.news.uni-berlin.de...[color=green]
> > Dan, I think that is what I want to convey to Tony that sure any expert[/color][/color]
on[color=blue][color=green]
> > any programming language can write anything with that programming[/color]
> language,[color=green]
> > 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[/color]
> think[color=green]
> > it is wise? Just a question from an experience user. Thanks[/color]
>
> 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 "encapsulat ion" is[/color]
all[color=blue]
> about.
> (b) I can often write code faster in PHP than SQL, so I have no incentive[/color]
to[color=blue]
> 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[/color]
code[color=blue]
> 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,[/color]
but[color=blue]
> 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
>
>
>[color=green]
> > "Dan Scott" <dan.scott@ca.i bm.com> wrote in message
> > news:c68tvh$svp $1@hanover.toro lab.ibm.com...[color=darkred]
> > > Tony Marston wrote:
> > >
> > > > You do not need stored procedures or database triggers to write[/color]
> > successful[color=darkred]
> > > > applications. I once had to maintain a system that was built around
> > > > procedures and triggers, and it was a nightmare. The problem was[/color][/color][/color]
that[color=blue][color=green]
> > one[color=darkred]
> > > > trigger/procedure updated several tables, which fired more triggers[/color]
> > which[color=darkred]
> > > > contained more updates which fired more triggers ..... It was[/color][/color]
> impossible[color=green]
> > to[color=darkred]
> > > > 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 /[/color][/color][/color]
functions.[color=blue][color=green][color=darkred]
> > >
> > > > Another reason I prefer to put all my business logic into PHP code[/color]
> > instead[color=darkred]
> > > > of triggers is that PHP code is a lot easier to debug. Have you come[/color]
> > across[color=darkred]
> > > > 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
> > >[/color]
> >[/color]
>[/color]
(http://publib.boulder.ibm.com/infoce...m.db2.udb.doc/[color=blue][color=green]
> > ad/t0007399.htm)[color=darkred]
> > >
> > > 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[/color]
> >
> >[/color]
>
>[/color]

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