README,v 1.2 1994/05/02 08:09:30 lindner Exp

This is the University of Minnesota's Gopher to SQL Gateway.

About Gophersql
---------------

Gophersql is a program which accepts gopher requests and translates
them into SQL requests.  It should be started from inetd.

It currently can talk to either a Sybase or Oracle Database Server.

Please report any bugs/enhancements to gopher@boombox.micro.umn.edu.


FILES
-----
Your archive should contain the following files:

   README	    This document
   Copyright	    The UofMN copyright on all this stuff.
   gophersql        The main gopher-->SQL script
   oracleglue.pl    The interface to oraperl
   sybaseglue.pl    The interface to sybperl
   insert.sql	    A sample events database
   examples/        Example customization features.


REQUIREMENTS
------------

For Sybase:

  You will need a machine with the Sybase client libraries (usually
  stored in /usr/sybase) and a special version of perl called sybperl
  available via anonymous ftp from ftp.demon.co.uk in
   /pub/perl/db/sybperl


For Oracle:

  You will need a machine with the SQL*Net client libraries and a
  special version of perl called "oraperl."  Oraperl is available via
  anonymous ftp from ftp.demon.co.uk in
    /pub/perl/db/oraperl

For username/password authentication:

  You will need the descrypt software, available via anonymous ftp
  from  chalmers.se in 

     /pub/unix/des/des-2.2.tar.Z


INSTALLATION
------------

You need to put the following line in /etc/services:

  gophersql    	8500/tcp

And the following line in /etc/inetd.conf (depending on your system type):

  gophersql  stream  tcp    nowait  nobody /usr/local/etc/gophersql  gophersql

After this step is completed you should restart the "inetd" process.
Use the ps command to find the process number and issue the following
command as root:

  kill -HUP process#


CONFIGURATION
-------------

The first thing you need to do is to edit the very top of the
gophersql script and make sure that the proper version of perl is
being used.  This will be either sybperl or oraperl.

You can then configure the gophersql interface in two ways, via
command line switches, or by setting values inside of the gophersql
script.

The following table summarizes the switch and the variable names used
for configuration.

Command   Perl        
Line      Variable      Description
--------- ------------- ----------------------------------------------
-T        $DBtype       The type of database, either 'oracle' or
			'sybase' currently

-h        $Ghost        The hostname of the gophersql gateway server

-p        $Gport        The port specified in /etc/services. Default
			is 8500

-U        $DBUser       The username to log into the database.  This
			username is for the database, not the
			operating system.

-P	  $DBPassword	The password to authenticate the database
			username

-S	  $DBServer     The database server name to log into.  In
			sybase this is defined in your interfaces
			file, in Oracle this is the hostname of the
			Oracle server.

-d        $DBDatabase	The name of the database on the SQL server to
			use.  Examples, pubs2 on Sybase

-a	  $DBauth	Use database authentication

	  $DBModules	Where the custom scripts and namelist files
			live. 
	  $DBglue	The directory where oracleglue.pl and
			sybaseglue.pl are located.

          $DBgatewaykey The gateway's secret key for encoding
			passwords when using the -a option.  

	  $DBAdmin      The name of the administrator.
        
          $DBAdminEmail	The email address of the gateway
			administrator. 

          $DBLanguage   The language that this server uses.


TESTING
-------

Once you think you have all the configuration done, test the gophersql
script.  First, test the syntax

	 perl -c gophersql

This will tell you if there are any syntax errors.

Next, try seeing if you can connect to the database.  Do the
following:

  ./gophersql
  tables            <--- Gophersql expects input, type tables and then return

Any error messages related to the database username, database name
etc. should be revealed in this test.

Once this feature works you can try using your gopher client to
connect to your gateway host on the port you specified in
/etc/services.   Try browsing, you should be able to execute all
options. 


CUSTOMIZING
-----------

This simplest way to customize the appearance of the server is to make
a Gopher directory on a regular gopher server that supports links.
You can use your gopher client on the gateway and select items that
should be useful, give them nice descriptive names and link them to
the server.  Here's an example links file for a Unix gopher server

-------------------------------------------------------------------
Type=1+
Name=Events for Today (directory)
Path=list EVENTVIEW.TITLE EVENTVIEW EVENTVIEW.edate = trunc(SYSDATE)
Host=arcwelder.micro.umn.edu
Port=8500
Numb=1
#
Type=0+
Name=Events for Today (file)
Path=get EVENTVIEW EVENTVIEW EVENTVIEW.EDATE = trunc(SYSDATE)
Host=arcwelder.micro.umn.edu
Port=8500
Numb=2
#
Type=1+
Name=Events sorted by Category
Path=list1 EVENTVIEW.CATEGORY->EVENTVIEW.TITLE
Host=arcwelder.micro.umn.edu
Port=8500
Numb=5
#
Type=1+
Name=Events sorted by Date
Path=list1 EVENTVIEW.EDATE->EVENTVIEW.TITLE
Host=arcwelder.micro.umn.edu
Port=8500
Numb=6
#
Type=1?
Name=Multiple Field Search, sorted by Event Title
Path=asklist EVENTVIEW.TITLE
Host=arcwelder.micro.umn.edu
Port=8500
-----------------------------------------------------------

There are other ways to customize the server other than just making
links.  Oftentimes you'll want to have a nice name displayed for the
name of a table or column.  You can do this by putting a file called
namelist.<dbname> in your $DBmodules directory.  Replace <dbname> with
the name of the database you're using.

Inside of a namelist file you can specify a table name, table.column
tuple, or column name.  After this put a colon and what you want the
name to be changed to.  Comments can be specified by the # character
at the beginning of the line.

Here's an excerpt from the namelist file for the pubs2 database:

----------------------------------------------------------------------
# Pretty names for tables
#
authors:Author List
titles:Book Titles
#
# Common column names
#
.au_id:Author ID Number
.title_id:Title ID Number
#
# Pretty names for specific columns
#
authors.au_lname:Author Last Name
authors.au_fname:Author First Name
-------------------------------------------------------------------


Another way to customize the appearance of the server is to write
custom perl code to format the records in your database.  Put your
custom code in a file called <TABLENAME>.module where the <TABLENAME>
is the base table for which you want to construct nice formatted
records. Put this file in the $DBmodules directory.  This function
will be called for all accesses to through the given base table.  Your
custom code may do joins that access other tables.  There's nothing
wrong with that.

Your could should be declared in a subroutine named <TABLENAME>_main.
At the end of the file you should have a the string '1;'  Your best
bet would be to modify one of the existing example files for your
table.

Another place where you might want to customize the server is for data
entry.  You do this in a similar way as the custom record formatting
code, except you put your code in a file called <TABLENAME>.insert

There are two customization functions you can put in here.  The first
TABLENAME_askblock will generate the askblock for the insertion of a
table.  This is useful if you want to get extra data, or supply
default values.

The other function is named TABLENAME_insert.  This function should
process the ASK block data from the client (which can be gotten from
the array $GASKBlock[]) and do whatever it wants.  Any data output in
this function will be shown to the user.

