The quest for opensource database...

[Useko Netsumi]

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(trigger).

Just my 2cents.

 

[Tony Marston]

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?

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.

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(trigger).

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.

 

[gmuldoon]

(e-mail address removed) says...
Sorry, can't confidently recommend. Am spoilt, have Oracle.

You do not need stored procedures or database triggers to write successful
applications.

But they can be very beneficial.

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.

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.

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.

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 "application level".

I've fought similar anti-database-level-coding arguments against the use
of database referential integrity constraints.

Have you come across
an interactive debugger for database procedures and triggers?

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

Geoff M

 

[Dan Scott]

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

 

[Francis Hwang]

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?

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]

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



(http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/

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

 

[Sascha Ebach]

Hi Ryan,

It does... I have found that approach to be rather performance-
inhibited as you seem to be finding as well. What I did is make the
CGI very thin, and do all the work in a persistent backend. The
benefits are manifold:

Could you elobarate on what you mean by persistent backend and how it
avoids the syncronization problems?

 

[Mark Hubbart]

Hi Ryan,



Could you elobarate on what you mean by persistent backend and how it
avoids the syncronization problems?

(I'm not Ryan, but since he hasn't answered (yet))

I think he's talking about a separate server running in the background,
which handles all logic and data requests, not serving them in html
format, just data.

If you build an app that serves data, processes logins, etc., it makes
it easier to deal with cached data, without having to worry about
keeping things in sync. The thin cgi front-end requests the data for
the page, and the persistent app determines whether to send cached data
or not. This allows you to keep everything in sync, and has several
fringe benefits: easier sessions, simplified logic, better abstraction.

If you have a decent-sized webapp to build, and your host allows
persistant processes, this might be the way to go. It makes it so you
are coding two separate projects: the app, and the website frontend.
This separation can make things much simpler, IMHO.

cheers,
--Mark

 

[Rick Caldwell]

You might also look at what was the SAP database that MySQL took over
and renamed MaxDB. Look at http://www.mysql.com/products/maxdb/

It has:
Views
Server-side cursors
Stored procedures and triggers
Auto fallover to standby server
and others

I haven't used but it is worth checking out.

Rick

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.

--
Have you noticed that a "slight tax increase" costs us at least two
hundred dollars a year, but a "substantial tax cut" saves us maybe
thirty cents?

(e-mail address removed)