Re: Postgresql and Ruby - any opensource software - Mailing list pgsql-general
From | Lincoln Yeoh |
---|---|
Subject | Re: Postgresql and Ruby - any opensource software |
Date | |
Msg-id | 5.1.0.14.1.20021212021000.0273e600@mbox.jaring.my Whole thread Raw |
In response to | Re: Postgresql and Ruby - any opensource software done (Markus Jais <markusjais@yahoo.de>) |
List | pgsql-general |
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.
pgsql-general by date: