Look also at the french version of this document.
This is an adaptation of Arthur Secret's work.
Characteristics
---------------
* only the condition of the WHERE clause is required
in the search text
* links to other queries can be made.
* multiple styles (glossaries, lists, plaintext, ...)
* queries to multiple databases are possible.
* only the server knows the access details for the
database (database name, username, password).
* no modifications for the clients, only the server is upgraded.
* possible access control to restrict what systems can
query a specific table (adaptation of "tcp_wrapper"
- Wietse Venema (wietse@wzv.win.tue.nl)).
* "oracle/" must be specified in the pathname of the document.
Syntax
------
With each document (suffix ".html") is associated a file with
suffix
".sql". This file defines both the SQL statement to be used
when a query is done on the file, and also the format of the output
to be returned. The file format is :
* head (optional) : normal text with possible keywords
- <unesc> condition of the WHERE clause (HTUnEscape)
- <esc> encoded condition (HTEscape).
The macros <unesc> and <esc> are replaced by the
search text entered by the user in escaped (URL)
or unescaped (as typed) form.
* the SELECT clause is given between tags <SELECT ...> </SELECT>.
Syntax
is <SELECT option = string, ...>. The following options can be used
(all
optional) :
- type=tableau : for output like
col1 col2 ... coln
-------------------
aaa aaa ... aaa
bbb bb ... b
...
ccc c ... cc
- database : database name (default A)
- username : oracle username (default SCOTT)
- password : password (default TIGER)
- format : default C format to apply to each returned value
(default, "%s=%s")
- maxselect: maximum number of returned row (default 40).
The names of options are not case sensitive. The <select>
block is expanded to the returned results.
* this can be repeated ("head", "<select> block", "head", "
<select> block", ...)
In the SELECT clause, you can specify (in comment /* */) a C
"printf" format string (after
the column name, or alias) to display the returned value. This format
can have 3 %s (like default format of tag <SELECT>). These are used
to output the following string values:
- 1st %s for column name (or alias, if specified)
- 2nd and 3rd %s for the returned value (see example below).
The SELECT clause is also a C format whith a maximum of 8
occurrences of "%s", each used for the condition of the WHERE clause.
Text can be put before (and after) each row.
Example
-------
The following document "http://moulon.inra.fr/oracle/A/projet.html"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~
Projets
DESCRIPTION D'UN PROJET
Interrogation de la relation projets[1] sur un quelconque de ces
champs,
afin de retrouver les caracteristiques associees telles que
description,
bibliographie, ou la carte.
Attention : mefiez vous des "faux-amis".
Certains champs peuvent preter a confusion lors de
l'interrogation, par
exemple le champ responsable figurant a la fois dans les relations
projets
et dispositif. Pour eviter de tels problemes, si vous vous doutez
d'une
confusion possible, prefixez le champ par le nom de la relation.
Par exemple, "projets.responsable like 'ts%'"
-------
Retour au menu[2].
[End]
FIND <keywords>, 1-2, Back, Up, Quit, or Help:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
is associated with file "projet.sql" :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
<h1>Projets associes a <unesc></h1><ul>
<select>
/*<li>*/ select rowid
/*%0.0s<a
href=http://moulon.inra.fr:80/oracle/A/projet.row?rowid='%s'>*/,
projet "prj", type "type" , responsable "resp"/*%s=%s*/
from projets
where %s/*</a>
*/
</select>
</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>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
Query "FIND projet like 'P%'" gives :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
projet
like 'P%'
PROJETS ASSOCIES A PROJET LIKE 'P%'
prj=P1type=type P1resp=ts (decoux@moulon.inra.fr)[1]
prj=P2type=type P2resp=decoux[2]
prj=P3type=type P3resp=ts[3]
Caracteristiques associees
BIBLIOGRAPHIE[4]
CARTE[5]
DISPOSITIF[6]
Liste [7] des reponses
[End]
FIND <keywords>, 1-7, Back, Up, Quit, or Help:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
i.e. :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
<ISINDEX><TITLE>projet like 'P%'</TITLE>
<h1>Projets associes a projet like 'P%'</h1><ul>
<li><a
href=http://moulon.inra.fr:80/oracle/A/projet.row?rowid='000002BF.0000
.0003'>prj=P1type=type P1resp=ts (decoux@moulon.inra.fr)</a>
<li><a
href=http://moulon.inra.fr:80/oracle/A/projet.row?rowid='000002BF.0001
.0003'>prj=P2type=type P2resp=decoux</a>
<li><a
href=http://moulon.inra.fr:80/oracle/A/projet.row?rowid='000002BF.0003
.0003'>prj=P3type=type P3resp=ts</a>
</ul><p><h2>Caracteristiques associees</h2>
<h3>
<a
href=http://moulon.inra.fr:80/oracle/A/biblio.sql?projet%20like%20%27P
%25%27>Bibliographie</a>
</h3>
<h3><a
href=http://moulon.inra.fr:80/oracle/A/carte.sql?projet%20like%20%27P%
25%27>Carte</a></h3>
<h3><a
href=http://moulon.inra.fr:80/oracle/A/dispositif.sql?projet%20like%20
%27P%25%27>dispositif</a>
</h3>
<h2><a
href=http://moulon.inra.fr:80/oracle/A/projet.lis?projet%20like%20%27P
%25%27>Liste </a>
des reponses</h2>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
Files are :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
* projet.row
<h2>Description generale d'un projet</h2>
<select format="%s = %s<p>
">
select projet,type,t1d,t2d,morpho,physio,qtl,responsable,mere,pere,
CARTE/*%s = <a
href=http://moulon.inra.fr:80/oracle/A/carte.row?carte='%s'>%s</a><p>*
/,remarques
from projets
where %s
</select>
* biblio.sql
<h2>Bibliographie pour <unesc></h2><ul>
<select>
/*<li>*/select unique biblio.rowid "-"
/*%0.0s<a
href=http://moulon.inra.fr:80/oracle/A/biblio.row?rowid='%s'>*/,
ref , auteurs from projet_biblio, biblio, projets
where (num_ref = ref) and (num_prj=projet) and %s /*</a>
*/
</select>
</ul><p><h1><a
href=http://moulon.inra.fr:80/oracle/A/biblio.lis?<esc>>Liste
</a> des reponses</h1>
* carte.sql :
<h2>Cartes pour <unesc></h2><ul>
<select>
/*<li>*/select cartes.rowid "-"
/*%0.0s<a
href=http://moulon.inra.fr:80/oracle/A/carte.row?rowid='%s'>*/,
cartes.carte , population "pop"
from cartes, projets
where (projets.carte = cartes.carte) and %s/*</a>
*/
</select>
</ul><p><h1><a
href=http://moulon.inra.fr:80/oracle/A/carte.lis?<esc>>Liste
</a> des reponses</h1>
* dispositif.sql
<h2>Dispositifs pour <unesc></h2><ul>
<select format="%s=%s">
/*<li>*/select unique dispositif.rowid "-"
/*%0.0s<a
href=http://moulon.inra.fr:80/oracle/A/dispositif.row?dispositif.rowid
='%s'>*/,
experience "exp" , test
from dispositif_projet, dispositif, projets
where (num_exp = experience) and (num_prj = projet) and (%s)/*</a>
*/
</select>
</ul><p><h1><a
href=http://moulon.inra.fr:80/oracle/A/dispositif.lis?<esc>>Liste
</a>
des reponses</h1>
<p>Voir aussi les
<a
href=http://moulon.inra.fr:80/oracle/A/caract.html>caracteristiques</a
>
associees a chaque dispositif.
* projet.lis :
Reponse a la question <unesc><pre>
<select format="%s = %s
">
select * from projets where %s/*//
*/
</select>
</pre>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Guy Decoux
INRA
Laboratoire de Genetique Vegetale
Ferme du Moulon
91190 Gif sur Yvette
Tel : (33 1) 69 41 97 37
Fax : (33 1) 69 41 27 90
e-mail : decoux@moulon.inra.fr
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
p.s.
Please note :
1) I program like i speak english (very BAD, excuse me)
2) A french and comprehensible version of this text is
http://moulon.inra.fr/oracle/www_oracle.html