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]

Ruby Tuesdays wrote:

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.

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]

"Ruby Tuesdays" <No************ **********@yaho o.com> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...

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.
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
Is PostgreSQL any better/worse? Or is that any other choice beside the two? Thanks.

[Erwin Moller]

Tony Marston wrote:

"Ruby Tuesdays" <No************ **********@yaho o.com> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...

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.

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?

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]

"Tony Marston" <to**@NOSPAM.de mon.co.uk> writes:

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.

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]

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" <to**@NOSPAM.de mon.co.uk> wrote in message
news:c6******** **********@news .demon.co.uk...

"Ruby Tuesdays" <No************ **********@yaho o.com> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...

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.
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

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

two?

Thanks.

[Tony Marston]

"Useko Netsumi" <us************ *****@earthlink .net> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...

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?
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
Just my 2cents back from my college days.
For simple and less-dynamic-update application, you might not need tri
"Tony Marston" <to**@NOSPAM.de mon.co.uk> wrote in message
news:c6******** **********@news .demon.co.uk...

"Ruby Tuesdays" <No************ **********@yaho o.com> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...

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.

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

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

two?

Thanks.

[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(tri gger).

Just my 2cents.
"Tony Marston" <to**@NOSPAM.de mon.co.uk> wrote in message
news:c6******** ***********@new s.demon.co.uk.. .

"Useko Netsumi" <us************ *****@earthlink .net> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...

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?

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

Just my 2cents back from my college days.
For simple and less-dynamic-update application, you might not need tri
"Tony Marston" <to**@NOSPAM.de mon.co.uk> wrote in message
news:c6******** **********@news .demon.co.uk...

"Ruby Tuesdays" <No************ **********@yaho o.com> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...
> 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.

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

> Is PostgreSQL any better/worse? Or is that any other choice beside

the two?
> Thanks.
>
>

[Tony Marston]

"Useko Netsumi" <us************ *****@earthlink .net> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...

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(tri gger).

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]

to**@NOSPAM.dem on.co.uk says...

"Ruby Tuesdays" <No************ **********@yaho o.com> wrote in message
news:c6******** ****@ID-205437.news.uni-berlin.de...

Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.
Sorry, can't confidently recommend. Am spoilt, have Oracle.
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.
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 "applicatio n 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