MC front end to PostgreSQL - lesson 2

[Sadhunathan Nadesan]

| Greetings all,
| 
| Building on the work of Pierre, I would like to share with you another

	That is Pierre Shores, master MC craftsman, whose native
	language seems to be French.  Mine's English so I'm writing
	docs.

| , the middle piece is a CGI program that
| connects the two.  So you need 3 pieces.  I'm going to show you the
| middle piece first.
| 

	As promised, here you go.  On my system this MC shell script
	was installed in the global CGI directory, /home/http/cgi-bin in
	my case), and referred to by web pages as domain-name/cgi-bin.
	The web calling convention leaves out the full path name, it
	makes the root of the http system look like root to the world.

	Your next step is to read this code.

---------------------cut here -----------------------
#!/usr/local/bin/mc

on startup

  put "Content-Type: text/plain" & cr & cr

  put emtpy into formdata
  if ($REQUEST_METHOD = "POST") then
    put empty into buffer
    repeat until (length(buffer) >= $CONTENT_LENGTH)
       read from stdin until empty
       put it after buffer
    end repeat
  else
    read from stdin until empty
    put it into buffer
  end if
  put urlDecode(buffer) into formdata 

  split formdata by "&" and "="
  put keys(formdata) into keywords

  switch formdata["action"]
    case add
      put "insert into azz (title, contents) values ('$TITLE', '$CONTENTS');" \
	   into the_query
      replace "$TITLE" with formdata["title"] in the_query
      replace "$CONTENTS" with formdata["contents"] in the_query
      put the_query into url "file:/tmp/azzadd.sql" 
      put "psql azz -f /tmp/azzadd.sql" into command_string
      break

    case search
      put "select title, contents from azz where title ~* '$TITLE' or contents ~* '$TITLE';" into the_query
      replace "$TITLE" with formdata["title"] in the_query
      put the_query into url "file:/tmp/azzsearch.sql" 
      put "psql azz -qt -f /tmp/azzsearch.sql" into command_string
      break

    case modify
      put "update azz set contents = '$CONTENTS' where title = '$TITLE';" into the_query
      replace "$TITLE" with formdata["title"] in the_query
      replace "$CONTENTS" with formdata["contents"] in the_query
      put the_query into url "file:/tmp/azzmodify.sql" 
      put "psql azz -f /tmp/azzmodify.sql" into command_string
      break

    case delete
      put "delete from azz where title = '$TITLE';" into the_query
      replace "$TITLE" with formdata["title"] in the_query
      put the_query into url "file:/tmp/azzdelete.sql" 
      put "psql azz -f /tmp/azzdelete.sql" into command_string
      break

    case list
      put "select title from azz;" into the_query
      put the_query into url "file:/tmp/azzlist.sql" 
      put "psql azz -qt -f /tmp/azzlist.sql" into command_string
      break

    default
      put "Unknown Action - call technical support"
  end switch

  set shellCommand to "/bin/sh"
  put shell(command_string)

end startup
---------------------cut here -----------------------


Ok, so what's it doing?

First, the data transmission from your front end MC application is
being read and decoded into the container 'formdata'.  This in turn is
being converted to an indexed array (using split) where the first
element of the array is the action to perform, and subsequent elements
are field-value pairs.  My simple app has only 2 fields of interest, a
title, and a contents.   (There is a 3rd field, serial index, but it's
for managing the data, ok to ignore it).

Wow, that was easy, collecting all our info into an array, better than
Perl!  (which uses chomp)

Next we have a case statement for each action.   Amazing, we are
supporting 5 different activities - inquire, list, add, modify, and 
delete - in only 67 lines of code.  Ok, not too much error protection
here but you can add your own.

The actions are translated into SQL statements and  written into
files.  Then SQL is invoked on the file.  With PostgreSQL, you can
call it's interactive SQL monitor, psql, from the command line with an
argument of a file name.    These two lines invoke the shell which in
turn invokes SQL:

  set shellCommand to "/bin/sh"
  put shell(command_string)

SQL sends it's output to standard out and beause of the statement at
the top of the program which first tells Apache that it's legal HTML
even though its plain text:

  put "Content-Type: text/plain" & cr & cr

then this text (SQL output) is returned to the calling program,
namely, your MC application.  Once you get it back you can do whatever
you want with it in your MC application.

Easy, eh?


Ok, that's lesson 2.  The hard part is over.

Sadhu