Cgi and Database (stick to standards)
katir at hindu.org
Sun Feb 10 16:19:57 CST 2008
Bernard Devlin wrote:
> I'm not sure what kind of database requirements you have. But in my
> tests using queries performing multiple joins on several tables
> containing between 1 million and 9 million rows each, Rev cgi
> completely blew away Java and relational databases:
> If you need multi-user/multi-application update/insert access to the
> data, then Rev is probably not the right solution. For other
> situations, I would say don't dismiss Rev's native data structures
> without thorough testing of the speed differences when compared to a
We use shell calls to PostGresSql on our server... from inside Rev CGI's
...it's easy if you
know your SQL language... just build a query and send it to the dbase,
get the result.
Then you don't need to worry about drivers and paths to them ....
Sorry for long post, but since Rev CGI is so hot...
Here's a snippet (with finesse from Andre who is a wizard with format...)
for a standard insert query, your insert values (params here) are coming
in from the POST:
function insertEntry pUser, pCode, pTime, pDescr
put format("INSERT INTO event (user_id, event_code, event_time,
description) VALUES ('%s','%s','%s','%s');",pUser, pCode, pTime, pDescr)
put tSQLQuery into url "file:/tmp/Verify.sql"
put format("psql -F $\'\\t\' yourDbase -f /tmp/Verify.sql") into
# run the SQL commands and send the results back to the calling program
set shellCommand to "/bin/sh"
put shell(command_string) after tSQLQueryResult
Note the little "trick" of writing the query to disk and then calling it
back by reading it into the SQL shell cmd.
You can also push unix style variables to shell like this:
put tWrappedMsg into tMsg
replace cr WITH "\n" in tMsg
put tMsg into $DailyHPIEmail
--put tMsg; exit to top
# Do shell stuff and send mail
set the shellcommand to "/bin/sh"
put "echo -e $DailyHPIEmail | sendmail -f hpi.list at hindu.org " & (fld
"to" of cd "staticText") into tCmd
this works on OSX with PostFix enabled for sending mail ...
But, we never bothered to try that for psql...or maybe we did but it
didn't work.. can't recall...anyway, the pattern of calling your query
from a file means you can keep nicely formated normal SQL queries in
files/libs (or on cards in stacks...) and you don't have to build them
run time in your CGI...they are easier to read, maintain and debug as
and a bit of history:
FWIW: There were discussions a year or two ago about how robust this
solution could be without a "persistent Revolution process" someone
replied that he had been using Rev CGI where a new instance was called
each time. he said it scaled up to the millions of hits. No problems.
We only use Rev CGI on our server...
(well almost... PHP is also being called by XOOPS and PMWiki) there
are no persistent processes. a new instance is called for *every* GET
request for every page at http:// www.himalayanacademy.com. We use
include exec SSI's to bring in page chunks, and these trigger Rev CGI's
for *every* single page that is delivered..
<!--#exec cgi="/cgi-bin/local_nav_include.cgi" -->
pulls in a unique set of side bar links depending on the "realm" where
the page lives
(anyone need that? email me off line)
and meanwhile also I have a custom 404 CGI and redirect that is
constantly firing to handle in coming redirects (mostly mapping short,
"sweet" URL's published in print to their real deep locations)
(I can also send you that too)
Meanwhile other CGI's are doing more robust stuff like processing Credit
card transactions and handling form submissions.... no problem... Now I
can't vouch for more than 30-50 instances a second... what would happen,
but we are in that range right now, Apache and the CPUs hardly blink...
and see no slow down whatsoever. I'm no expert but I think a persistent
Rev process will "die" on the first hard script error... so there are
advantages to just letting Apache load rev on every single call...watch
out for zombies ... if you don't close the cgi properly you can get hung
Rev CGI processes, but, because these are separate instances of Rev...
they don't terminate your web site... just slow it down as each zombie
starts eating the CPU speeds.. I once had up to 8 hung CGI processes
... Nothing "bad" happened... the sites were sluggish but functional
until I got back from a weekend and someone said "your site turned to
cold molasses" or worst case scenario.. I really blew it on a script for
one form and users are getting 404's for that one page only. Whereas if
your persistent Rev Process died and *all* your cgi's were tied to it.
then a single failure will bring down the entire framework for anything
but static HTML delivery... All pages using the "persistent processes"
will start returning errors... depending on how serious the problem is
(you have a wait or something blocking I think the whole engine
stops....) ... But, allowing Apache to call up a single instance of even
a "wonky" rev CGI (I can't figure out why some of them do not terminate
properly, but only sometimes......) for every POST or GET call and one
fails... the user hardly notices... he can just click "submit" a second
time and this time it works...
Go in to TOP and kill them and you are back in business... meanwhile
Apache keeps serving pages all the while... I've been cleaning out the
old "bad" cgi's and now we hardly ever see it...
Our web server uses 2 dual XEON processors so that's 4 CPU's on a
dedicated server...so that helps...if you are running on a shared
virtual space... not sure what happens. But someone observed that
today's CPU speeds far outstrip the network, hard disk I/O, and
Apache... so the "worry" about calling an instance of REvolution into
memory on every hit, is mostly theoretical... in fact, the CPU + REV
"engine" is way, way ahead of Apache, disk I/O and the outGoing pipe
speeds... no problem at all... and it is it's magnitudes faster than PHP
(and easier to code unless you are opting for canned CMS...) ....of
course, I'm sure there is a threshold, but I don't know what it would
be...disclaimer: if you are doing Gaussian Blurs in the background on
your web server.. all bets are off : point, obviously it depends on what
you are doing... But, we've been use Rev CGI for 9 years and MC before
that... no problems... its' wonderful...and only getting better!
More information about the use-livecode