Cgi and Database (stick to standards)

[Sivakatirswami]

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:
>
> http://lists.runrev.com/pipermail/use-revolution/2008-January/106071.html
>
> 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
> RDBMS.
>
> Bernard
>
>
>   
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) 
into  tSQLQuery
     put tSQLQuery into url "file:/tmp/Verify.sql"
     put format("psql -F $\'\\t\' yourDbase -f /tmp/Verify.sql") into 
command_string

  # run the SQL commands and send the results back to the calling program
      set shellCommand to "/bin/sh"
      put shell(command_string)  after tSQLQueryResult
      return tSQLQueryResult
end insertEntry

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

Have fun...

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

<div id="linkList">
        <!--#exec cgi="/cgi-bin/local_nav_include.cgi" -->
</div>

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!