The quest for opensource database

ruby-talk
21

Well, this is just abstraction against raw SQL, my problem was where to
put the constructed query. Putting it in initialize is slow, consider
the following scenario:

There is a forum with 30 posts.
Each post has an user associated with it.
Each user has a profile associate with it.

The data is stored in three tables, posts, users, and profiles. If I
were to put the query in the initialize, that’s 3 queries for each
post, and can quickly spiral out of control (especially since a good
amount of time might be wasted on pulling the same user again and
again).

What I do now is to define two class methods ‘select_one’ and
‘select_all’ (to look uniform with DBI, which I use) in the classes in
question (in this case, Post, User, and User::Profile). These methods
fetch data (a WHERE clause is passed in as an argument) from the
database and pass a Hash to initialize. ‘select_all’ returns an array.

‘select_all’ is where the optimization happens, the query plan for the
scenario described might be something like the following:

  1. In Post#select_all, select a list of all posts, then call
    User#select_all with the criterion being all uids which appear in the
    list of posts gotten.
  2. In User#select_all, select a list of the users given by the
    criterion, then call User::Profile#select_all with the criterion again
    being all the uids which appear.
  3. In User::Profile#select_all, fetch all the profiles described by the
    criterion and return them.

This select_all chain improves performance significantly, but I
question its OO-ness. For 30 posts, for example, this is 3 queries vs
3*30 queries.

While criteria doesn’t seem to solve my problem (well, present a better
solution), I think I’ll adopt it. The more abstraction the merrier.
Thanks!

···

On Apr 20, 2004, at 18:25, Ryan Pavlik wrote:

There are a couple problems here, and a few solutions to them, as
well. I’ll shamelessly plug Criteria here. Make sure you use the SVN
version:

–
Lo-lee-ta: the tip of the tongue taking a trip of three
steps down the palate to tap, at three, on the teeth.
Lo. Lee. Ta. GUO Shu-yu shu@rufuran.org

22

My application is web-based, and I just avoid having objects which
represent database data persist for more than one request. This is why
I need fast select. Unless I go distributed, caching things with ruby
procedures and triggers isn’t enough, because if I use mod_ruby for
this web app, it’s very likely that more than one copy of the app is
run as Apache pre-forks. If child #3 handles some request where some
data was input and it caches that input and updates the database, none
of the other children see that cached object, and unless they read the
database every request, they’ll be hopelessly out of sync.

I hope what I said made sense.

···

On Apr 20, 2004, at 18:25, Ryan Pavlik wrote:

…generate 3 UPDATE statements, but only one instead. The only thing
you need to watch out for is making sure someone doesn’t update the
database behind you; for this I’m not sure what you can do. (Maybe
some magic with stored ruby procedures and triggers… dunno.)

–
Lo-lee-ta: the tip of the tongue taking a trip of three
steps down the palate to tap, at three, on the teeth.
Lo. Lee. Ta. GUO Shu-yu shu@rufuran.org

23
···

On Wed, 21 Apr 2004, Shu-yu Guo wrote:

As a friend has told me again and again, always choose data integrity
over speed.

I’m not convinced by the speed argument- I haven’t found anything quicker
for the data I typically store. In other words: postgresql has always been
so fast that I haven’t needed to care.

One other important factor is transactions - by default, every postgres
operation has its own transaction, which means that if you wrap several
operations in a single transaction, things actually get faster…

Baruch’s Observation:
If all you have is a hammer, everything looks like a nail.
Rasputin :: Jack of All Trades - Master of Nuns

24

“Useko Netsumi” usenets_remote_this@earthlink.net wrote in message
news:c66462$8ber2$1@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
Using PHP Objects to access your Database Tables (Part 2) 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

Just my 2cents back from my college days.

For simple and less-dynamic-update application, you might not need tri
“Tony Marston” tony@NOSPAM.demon.co.uk wrote in message
news:c65jm0$nl$1$8302bc10@news.demon.co.uk…

“Ruby Tuesdays” NoSpamPlease_rubytuzdaiz@yahoo.com wrote in message
news:c63aer$7ck4g$1@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.

25

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.ibm.com wrote in message
news:c68tvh$svp$1@hanover.torolab.ibm.com…

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

26

“Useko Netsumi” usenets_remote_this@earthlink.net wrote in message news:c66462$8ber2$1@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?

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

27
> My application is web-based, and I just avoid having objects which > represent database data persist for more than one request. This is why > I need fast select. Unless I go distributed, caching things with ruby > procedures and triggers isn't enough, because if I use mod_ruby for > this web app, it's very likely that more than one copy of the app is > run as Apache pre-forks. If child #3 handles some request where some > data was input and it caches that input and updates the database, none > of the other children see that cached object, and unless they read the > database every request, they'll be hopelessly out of sync. > > I hope what I said made sense.

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:

*  There are no longer sessions on the backend, so you don't have
   to worry about transaction-oriented programming and kludgery so
   much.

*  Data is retained as necessary, so access is fast.

*  You haven't tied yourself to the web as a frontend, and can
   write yourself "real" application frontends.

*  You don't have to touch CGI or HTML much if you do it right,
   which means focusing on core functionality.

Anyhow, this may or may not be an option for you, but it definitely
does help if you can do it.

···

On Wed, 21 Apr 2004 10:30:49 +0900 Shu-yu Guo shu@rufuran.org wrote:

–
Ryan Pavlik rpav@mephle.com

“Feh. At least we can agree that if anything goes wrong,
it was due to your acute negativism” - 8BT

28

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” tony@NOSPAM.demon.co.uk wrote in message
news:c669n9$kui$1$830fa7a5@news.demon.co.uk…

“Useko Netsumi” usenets_remote_this@earthlink.net wrote in message
news:c66462$8ber2$1@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-mysql/databaseobjects2.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” tony@NOSPAM.demon.co.uk wrote in message
news:c65jm0$nl$1$8302bc10@news.demon.co.uk…

“Ruby Tuesdays” NoSpamPlease_rubytuzdaiz@yahoo.com wrote in message
news:c63aer$7ck4g$1@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.

29

“Useko Netsumi” usenets_remote_this@earthlink.net wrote in message
news:c69j32$9rkga$1@ID-205437.news.uni-berlin.de…

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” dan.scott@ca.ibm.com wrote in message
news:c68tvh$svp$1@hanover.torolab.ibm.com…

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

30

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:

*  There are no longer sessions on the backend, so you don't have
   to worry about transaction-oriented programming and kludgery so
   much.

*  Data is retained as necessary, so access is fast.

*  You haven't tied yourself to the web as a frontend, and can
   write yourself "real" application frontends.

*  You don't have to touch CGI or HTML much if you do it right,
   which means focusing on core functionality.

Anyhow, this may or may not be an option for you, but it definitely
does help if you can do it.

Ah the wonders of a DRb backend server with a cgi client and a gui client…

Now if only I could figure out how to effectively use DRb with ssl other
then through ssh or stunnel and it would be wonderful for everyone.

Charlie

31

“Useko Netsumi” usenets_remote_this@earthlink.net wrote in message
news:c66fl8$8i94l$1@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(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.

···

–
Tony Marston

32

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.

This only make sense if you have one client language though, surely?
If the tax calculation system changes, it’s a lot easier to just change a
stored procedure on the database than patch PHP/java/c clients individually.
It also gets backed up with the data it operates on.

Speaking with my sysadmin hat on, of course.
Our developers never think of this either :slight_smile:

···

–
Pity the meek, for they shall inherit the earth.
– Don Marquis
Rasputin :: Jack of All Trades - Master of Nuns

33

“Tony Marston” tony@NOSPAM.demon.co.uk wrote in message
news:c6amar$f5l$1$8300dec7@news.demon.co.uk…

“Useko Netsumi” usenets_remote_this@earthlink.net wrote in message
news:c69j32$9rkga$1@ID-205437.news.uni-berlin.de…

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” dan.scott@ca.ibm.com wrote in message
news:c68tvh$svp$1@hanover.torolab.ibm.com…

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

34

Hi Ryan,

Ryan Pavlik wrote:

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?

···

–
Sascha Ebach Hexatex Holistic-Webdesign
Hugo-Junkers-Str. 26 50739 Köln
Web: http://www.hexatex.de mailto:se@hexatex.de
Tel: 0221 / 5994393 Fax: 0221 / 5994394

35

(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

···

On May 5, 2004, at 12:08 AM, Sascha Ebach wrote:

Hi Ryan,

Ryan Pavlik wrote:

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?