Re: Postgresql and Ruby - any opensource software

At 12:07 AM 12/11/02 +0100, Markus Jais wrote:

> > Markus Jais wrote:
> > > Hello
> > >
> > > I am looking for an open source program developend with Postgresql
> > > and Ruby in order to learn something about how to use the two
> > > together.
> > >
> > > does anybody have a URL where I can find something useful ?
>I was looking for real application like content management systems or
>other web application or any other applaction where Ruby is used as the
>programming language and Postgresql as the RDBMS.
>
>I think both are very powerful tools and would be a great combination.

My guess is using Ruby+Postgresql shouldn't be too different from using
Postgresql and some other scripting language. Maybe you could check out the
Python/Perl sites for ideas.

In general it's usually best to use a standard DB interface - ODBC, JDBC,
DBI. That way you can deal with different RDBMSes with a bit less pain.
Also use bind variables/prepared queries (whatever that DB interface
supports) wherever possible for automatic DB quoting (I hope Ruby DBI does
the quoting/escaping for you! Test it out!) - that makes it harder for
naughty people to send their own naughty queries to your DB. Exception:
LIKE wildcard characters might not be quoted automatically, so do your own
if necessary.

As for web apps, the basic tips: when performance is an issue, persistent
DB connections can help. Though postgresql's connection setup time is
pretty fast compared to many other RDBMSes, it's still significant.

Not sure if you can do persistent DB connections properly with mod_ruby,
might have to do some extra coding and testing if you have different apps
connecting to different DBs.

So an alternative you may wish to consider is FCGI for ruby. You can set up
your DB connections before the fcgi loop. I've used FCGI in perl - and I do
it this way:

maininit
daemonise (fork children)
childinit
connect to DB
while FCGI request (and not too old)
BEGIN transaction
call main CGI style program.
catch/handle any errors
ROLLBACK transaction.
}
exit (if too old, or error requires it)

Explanations:
main cgi style program: this is what you'd normally write for a CGI or
mod_ruby program, except the init and DB stuff are before the loop.

daemonise: this forks , children return, parent hangs around monitoring
children and config files etc. Optional: to increase shared mem among FCGI
processes but adds complexity, because if Apache does the forking and
management, I believe the FCGI processes won't share as much mem.

BEGIN transaction: Do this after you receive a connection, not before so
that your 'now'::timestamp is valid.

ROLLBACK: If the main program didn't explicitly commit anything, it gets
rolled back. For commits in the main program you could do COMMIT; followed
by a BEGIN; so that a rollback without a BEGIN doesn't cause postgresql
grumbles, and so if you unintentionally do any other DB stuff after the
commit, it will be in a transaction and will be rolled back. Same for
rollbacks in the main program, follow them with a BEGIN.
NOTE: perl DBI has no BEGIN, have to rollback or commit to do one, so I do
two DBI rollbacks. Why not wait till the next connection to rollback+begin?
Because if you lock tables or rows they'll be locked till then. The ruby
DBI doc mentions ROLLBACK and COMMIT, but doesn't mention BEGIN, so dunno
how they do it.

Lots of the ruby stuff seems rather new and not as polished, I'm not sure
how robust they are, so you probably have a fair bit of testing to do.
Ruby's fcgi is probably less used than mod_ruby, so may not be as well
tested, then again it should be a lot simpler so could have fewer bugs.

By the way, make sure you keep your cgi parameter/data filtering separate.
What goes into your program should be filtered so your program can handle
it, then what goes to the DB should be filtered another way (remember bind
variables/prepared queries!) and what goes to the web browser should be
filtered accordingly (whether it's a form field, URL, or HTML). Do this and
you'll avoid a fair bit of misery. Otherwise you'll see silly stuff like
backslashes in front of everything - data polluted, or someone could
vandalise your app/db.

Hope this helps,
Link.