gateway WWW - Oraperl

decoux@moulon.inra.fr (ts)
Date: Thu, 22 Jul 93 09:42:18 +0200
From: decoux@moulon.inra.fr (ts)
Message-id: <9307220742.AA07335@moulon.moulon.inra.fr>
To: sanders@bsdi.com
Cc: www-talk@nxoc01.cern.ch
Subject: gateway WWW - Oraperl
Status: RO



 URL of this document is          http://moulon.inra.fr/oracle/www_oraperl_eng.html
 URL de la version francaise est  http://moulon.inra.fr/oracle/www_oraperl.html

Gateway WWW - Oraperl for Plexus 2.2
====================================

Characteristics
+++++++++++++++

 o only the condition of the WHERE clause is required in the search text 
 o links to other queries can be made.  
 o multiple styles (glossaries, lists, plaintext, ...) 
 o queries to multiple databases are possible.  
 o only the server knows the access details for the database (database name,
   username, password).  
 o no modifications for the clients, only the server is upgraded.  
 o URL of the document must begin by "/oracle".  

Syntax
++++++

 o With each document (suffix ".html") is associated a file with suffix ".sql".
   This file defines the format of the output to be returned.  The file contains
   HTML text with possible keywords : 
    o <unesc> condition of the WHERE clause (HTUnEscape) 
    o <esc> encoded condition (HTEscape) 
    o tag <sql "file"> where "file" is the pathname of an oraperl script.  

   The macros <unesc> and <esc> are replaced by the search text entered by the
   user in escaped (URL) or unescaped (as typed) form.

 o the oraperl script can define: 
    o "format STDOUT = ..." this format is used to display the returned
      values.  Columns of the SELECT clause, are specified by variable
      "@column" (or "$column[0], $column[1], ...) 
    o "format STDOUT_TOP = ..." (optionnal) 
    o "$statement" : this variable define the SELECT clause.  It can have
      one, or many, keywords "<unesc>", each used for the condition of the
      WHERE clause.  
    o "$database" : database name (default 'A') 
    o "$username" : oracle username (default "SCOTT") 
    o "$password" : password (default "TIGER") 
    o "$maxselect" : maximum number of returned row (default 40).  
    o it must have the following line : 

      require "server/oracle_search.pl";

Example of syntax
+++++++++++++++++

* file "projet.sql"

<h1>Projets associes a <UNESC></h1><ul>
<sql oracle/A/projet_sql.pl>
</ul><p><h2>Caracteristiques associees</h2>
<h3><a href="http://moulon.inra.fr:80/oracle/A/biblio.sql?<esc>">
Bibliographie</a></h3>
<h3><a href="http://moulon.inra.fr:80/oracle/A/carte.sql?<esc>">
Carte</a></h3>
<h3><a href="http://moulon.inra.fr:80/oracle/A/dispositif.sql?<esc>">
Dispositif</a></h3>
<h2><a href="http://moulon.inra.fr:80/oracle/A/projet.lis?<esc>">
Liste </a>des reponses</h2>

* script oracle/A/projet_sql.pl

#! /usr/local/bin/oraperl
format STDOUT =
  <li><a href='@<<<<<<<<<<<<<<<'>  projet = @<<<<<<<<<<<<<<<<<<<<<<<<<<
  $column[0], $column[1]
  type = @<<<<<<<<<<<<<<<<<<<<<< responsable = @<<<<<<<<<<<<<<<<<<<<<<<
  $column[2], $column[3]
  </a>
.
$statement = 'select rowid,projet,type,responsable from projets where <unesc>';
require "server/oracle_search.pl";
1;

Source
++++++

* oracle.pl :

#! /usr/local/bin/perl

sub do_oracle {
  local($_, $query, $version) = @_;
  local($keyword) = (undef);
  do { &retrieve($_); return; } unless $query;
  $keyword = $query;
  if(($keyword =~ m#%#) && ($keyword !~ m#\+#)) {
    $keyword =~ s/%(\d+)/pack(c,hex($1))/eg;
  } else {
    $keyword =~ s/\+/ /g;
    $keyword =~ s/%(\d+)/pack(c,hex($1))/eg if $version;
  }

  s/\.html$/\.sql/;
  -e $_ || &error('not_found', "document \`\`$_\'\' does not exist");
  open(file,$_);
  &main'MIME_header('ok', 'text/html');
  print '<head><title>', $keyword,'</title><isindex></head><body>',"\n";
  while(<file>) {
    if(($Avant,$script,$Apres) = ( m#(.*)<sql\s+(.*)\s*>(.*)#)) {
      print $Avant;
      $SIG{CHLD} = '';
      system "/usr/local/bin/oraperl $script \"$keyword\"";
      if($? != 0) { 
        print "</BODY>";
        close(file);
        $SIG{CHLD} = "reaper";
        return 1;
      }
      $SIG{CHLD} = "reaper";
      print $Apres;
      next;
    }
    s/<esc>/$query/gio;
    s/<unesc>/$keyword/gio;
    print ;
  }
  close(file);
  print "</BODY>\n";
  1;
}

1;

* oracle_search.pl :

#! /usr/local/bin/oraperl
$statement = ($statement)?$statement:'select * from emp';
$database = ($database)?$database:'A';
$user = ($user)?$user:'scott';
$password = ($password)?$password:'tiger';
$maxselect = ($maxselect)?$maxselect:40;
$statement =~ s/<unesc>/@ARGV/gio;
#
$lda = &ora_login($database,$user,$password) || &oracle_erreur($ora_errstr);
$csr = &ora_open($lda,$statement) || &oracle_erreur($ora_errstr);
while (($maxselect--) && (@column = &ora_fetch($csr))) { write; }
warn $ora_errstr if $ora_errno;
&oracle_erreur("fetch error: $ora_errstr") if $ora_errno;
do ora_close($csr) || &oracle_erreur("can't close cursor");
do ora_logoff($lda) || &oracle_erreur("can't log off Oracle");

sub oracle_erreur {
  print "Oracle Server Error :",@_,"<p>",$statement;
  exit 3;
}
1;

Installation
++++++++++++

* Copy file "oracle.pl", "oracle_search.pl" in "$http_sdir".

* Change line 24 of "oracle.pl", to adapt to your local installation of "oraperl": 

    24        system "/usr/local/bin/oraperl $script \"$keyword\"";
                      ^^^^^^^^^^^^^^^^^^^^^^
                          change it !!!

* Add in "plexus.conf" : 

$map{'oracle'}='require "$http_sdir/oracle.pl";&do_oracle($_,$query,$version)';

BUGS
++++

* I'm a beginner in perl : probably many bugs.  


 Guy Decoux