MC front end to PostgreSQL - lesson 1

[Sadhunathan Nadesan]

| However, one key to successful applications is data base design, so
| it is suggested you would put in some effort getting that right before
| building too much code for any real world application.


	Here we go for lesson 3.  Data base back end design, and
	creation. I'd like to emphasize the design.  Most complex
	projects that fail are due to lack of time spent on design and
	truly understanding the nature of the problem.	The users don't
	know what they want.	Not until they get something, then they
	begin to understand.  Design or data modeling is not actually
	covered in this lesson, I'm just emphasizing it is important.

	On the other hand, I would suggest you try building something
	very simple the first time, as simple as possible, until you
	get all 3 pieces working together.

	By the way this lesson is a lot of 'talk story so  you may
	skip it.  Don't say I didn't warn you.

	But just so you can understand this sample application, here's
	what I did:


	Background story
	..................

	Years ago I used cardfile on Windows to keep all kinds of
	information ... not only was it my phone and address book but
	anything else useful .. a serial number of a product?  some
	instructions on how to bleed my pump system?  The reminder
	notes to myself of all the 'to do' things before packing up
	from our house in San Diego and heading off for a month or more
	to our house in Hawaii .. etc.  I liked it because it was free
	form.  You just had a card name, and card contents (though the
	contents were limited, you could put images too).  Ah yes,
	just like a stack.  Whatdyaknow?

	Then a new version of Windoz came out with no card file.
	Arggg.  I copied over the executable and data from my previous
	version and kept using it.  Then another new version of Windoz
	came out which would not run this executble.  All my precious
	data was locked in this obsolete program with no export
	capability.  Double Arghhh!!  I found something on the net
	called azz cardfile, shareware that could import Windows card
	file data and was a lot more sophisticated.  And it can
	export.  So for more years I've continued collecting data in
	this program.

	But oh darn, when I'm at the office and I need something in
	there, gotta wait till I go home.  Oh darn, when I go to
	Hawaii I better remember to make a copy and take it else, if I
	make any changes to my last copy when I'm in Hawaii I now have
	2 out of synch collections of data and no easy way to
	synchronize (not impossible but) .. or if my wife wants to
	look something up she has to go to my computer .. or if I'm
	away from home I have no access ..... etc.  Like, it's really
	useful but it's locked onto my PC.

	Solution?  Bingo!  Enter the WEB and MC.  I simply make a data
	base back end for the data, an MC front end, and then I can
	access the same data from anywhere - from my mac laptop on the
	road, my wife from her Linux desktop machine - from work -
	from home in Hawaii ... etc.  Wow.  Instead of hauling the
	data around with me and worrying about multiple copies
	synchronized, I leave the data on the web server and access it
	from anywhere.


Creating a data base
--------------------

Yep, I needed a very simple data structure - a title or card name
field, and a contents field of unlimited text.  Actually, I made them
both type 'text' which is unlimited, to follow Pierre's example.  I
also followed his example on the indexing, creating a unique primary
key.

On my RH system I told it to install PostgreSQL when I installed RH so
that was cake.  At home I've got an old 6.5.3 version and the actual
SQL syntax is different, for example, no -U argment for user name to
psql, as shown in Pierre's example.  At work though I have the latest
and greatest.  In any case, if you need to start even before this
point and install a data base, I would refer you to postgresql.org.
They have it layed out cookbook, step by step.

Assuming you have the db software installed then, you create a data
base.  I called my azz.  I ran this command from the shell.


     createdb azz


Easy enough.  Now, you need to access this data base with the
interactive sql monitor called psql, so you type

     psql azz

You are now sort of 'in' the data base, and you will need to create a
table with fields  (columns).  Here is the command I used, and I
happened to name  the table azz also, not required though.

     create table azz (title text primary key, contents text, recordid serial);

You can type it all on one line as in the above or it looks nicer
formatted like this,

     create table azz (
	  title 	text primary key, 
	  contents 	text, 
	  recordid 	serial
     );


Ok, that's about it.  Except, one thing.  With 6.5 as mentioned there
is no option to specify a user in the command line.  Thus, the MC
application won't have permission to access data if you created the
data base under your regular login name.  So you have to figure out
who the MC application appears to be, according to Linux, when coming
in over the web.  That is a configurable Apache option I believe, and
you can write a simple shell script cgi  (which does a 'whoami'
command) to find out if you can't look it up.  In my case the user
name is 'nobody'.  Therefore, I had to grant data base access to
nobody, with this command (remember I'm still in psql)

     grant all on azz to 'nobody';


After that you can do a \d within psql and see your table structure as
follows

azz=> \d
Database    = azz
+------------------+----------------------------------+----------+
|  Owner           |             Relation             |   Type   |
+------------------+----------------------------------+----------+
| sadhu            | azz                              | table    |
| sadhu            | azz_pkey                         | index    |
| sadhu            | azz_recordid_key                 | index    |
| sadhu            | azz_recordid_seq                 | sequence |
+------------------+----------------------------------+----------+

Naturally, that will be your name over there.

Try inserting a little data, maybe deleting it back out, etc.  If you
need to start over you can use a destroydb command or a dropdb command
(depending on your version of postgres).  Again, postgresql.org has
easy to read documentation on sql commands.

That would be a wrap on lesson 3, the back end.

Sadhu