Mise en place de relation SQL/Web: Annexe - Version améliorée des scripts Perl

Page précédente Page suivante Table des matières

7. Annexe - Version améliorée des scripts Perl

Voici donc les scripts Perl complets, avec toutes les améliorations de style en HTML ainsi que des exemples de la sortie produite :

7.1 base.pl

Voici ce que génère ce script sur mon système : base.pl.html


#!/usr/bin/perl

use CGI;
$html = new CGI;

print $html->header;
print "<HTML>\n";
print "<HEAD>\n";
print "<TITLE>Liste des bases et tables du systeme</TITLE>\n";
print "</HEAD>\n";
print "<BODY>\n";
print "<CENTER>\n";

@bases = `/usr/bin/psql -d template1 -c "\\l"`;

print "<FORM ACTION=\"/cgi-bin/criteres.pl\" METHOD=POST>\n";

foreach (@bases) {
   s/ //g;
   if ((m/\|/) and (!m/template1/) and (!m/datname/)) {
      ($base) = split (/[|]/);
      print "<TABLE BORDER WIDTH=\"50%\">\n";
      print "<CAPTION>\n";
      print "<B>$base</B>\n";
      print "</CAPTION>\n";
      print "<TR>\n";
      @tables = `/usr/bin/psql -d $base -c "\\dt"`;
      foreach (@tables) {
   s/ //g;
   ($temp0, $temp1, $temp2) = split (/[|]/);
   if (($temp2) and ($temp2 ne Relation)) {
      print "<TD>\n";
      print "<INPUT TYPE=radio NAME=radio VALUE=\"$temp2:$base\">\n";
      print " $temp2\n";
      print "</TD>\n";
         }
      }
      print "</TR>\n";
   print "</TABLE>\n";
    }
       }

print "<P>\n";
print "<INPUT TYPE=submit VALUE=\"Module d interrogation\">\n";
print "<INPUT TYPE=reset VALUE=\"Remise  zero\">\n";
print "</FORM>\n";
print "</CENTER>\n";
print "</BODY>\n";
print "</HTML>\n";

exit;

7.2 criteres.pl

Voici ce que génère ce script sur mon système : criteres.pl.html


#!/usr/bin/perl

use CGI;
$html = new CGI;

sub simple {
   print "<TD>";
   print "$_[0] (de type <B>$_[1]</B>)";
   print "</TD>";
   print "<TD>";
   print "<INPUT TYPE=text NAME=input$_[0] SIZE=10>";
   print "</TD>\n";
}

sub double {
   print "<TD>";
   print "$_[0] (de type <B>$_[1]</B>)";
   print "</TD>";
   print "<TD>";
   &listesignes($_[0], 1);
   print "<INPUT TYPE=text NAME=input$_[0]inf SIZE=10>";
   print "</TD>";
   print "<TD>";
   &listesignes($_[0], 2);
   print "<INPUT TYPE=text NAME=input$_[0]sup SIZE=10>";
   print "</TD>\n";
}

sub listesignes {
   if ($_[1] == 1) {
      print "<SELECT NAME=liste$_[0]inf SIZE=1>";
      print "<OPTION> &gt;";
      print "<OPTION> &gt;=";
    }
   elsif ($_[1] == 2) {
      print "<SELECT NAME=liste$_[0]sup SIZE=1>";
      print "<OPTION> &lt;";
      print "<OPTION> &lt;=";
       }
   print "</SELECT>\n";
}

print $html->header;

print "<HTML>";
print "<HEAD>\n";
print "<TITLE> Creation de liste dynamique </TITLE>\n";
print "</HEAD>";
print "<BODY>\n";
print "<CENTER><B><FONT SIZE=\"+1\">";
print "Veuillez entrer vos criteres :";
print "</FONT></B></CENTER>\n";
print "<HR>\n";

$parametre = $html->param('radio');
($psqltable, $psqlbase) = split (/:/, $parametre);

@listechamps = `psql -d $psqlbase -c "\\d $psqltable"`;

foreach (@listechamps) {
   s/ //g;
   ($temp0, $temp1, $temp2) = split (/[|]/);
   if (($temp1) and ($temp1 ne Field)) {
      push (@champs, $temp1);
      push (@types, $temp2);
     }
   }

print "<CENTER><B>";
print "Choisissez les champs  afficher";
print "</B></CENTER>\n";

print "<FORM ACTION=\"/cgi-bin/interrog.pl\" METHOD=POST>\n";

print "<TABLE BORDER WIDTH=\"100%\">\n";
print "<TR>\n";

$i = 0;
foreach (@champs) {
   print "<TD><CENTER><B>";
   print "<INPUT TYPE=checkbox NAME=check$champs[$i] VALUE=value$i CHECKED> $champs[$i]";
   print "</B></CENTER></TD>\n";
   $i++
  }

print "</TR></TABLE>\n";

print "<TABLE BORDER WIDTH=\"100%\">\n";
print "<TR>\n";
print "<TD><CENTER><B>";
print "Eliminer les doublons : \n";
print "<INPUT TYPE=checkbox NAME=distinct VALUE=distinct CHECKED>\n";
print "</B></CENTER></TD>\n";
print "</TR></TABLE>\n";

print "<DIV ALIGN=right>";
print "<TABLE BORDER WIDTH=\"100%\">\n";
$i = 0;
foreach (@champs) {
   print "<TR>\n";
   if ($types[$i] =~ /text|varchar/)
   { &simple($champs[$i], $types[$i]); }
   if ($types[$i] =~ /date|time|float|int/)
   { &double($champs[$i], $types[$i]); }
   print "</TR>\n";
   $i++;
   }

print "</TABLE></DIV>\n";

print "<INPUT TYPE=hidden NAME=base VALUE=$psqlbase>\n";
print "<INPUT TYPE=hidden NAME=table VALUE=$psqltable>\n";

print "<CENTER>\n";
print "<INPUT TYPE=submit VALUE=\"Interroger la base\">\n";
print "<INPUT TYPE=reset VALUE=\"Remettre  zero\">\n";
print "</CENTER>\n";
print "</FORM>";
print "</BODY>";
print "</HTML>";

exit;

7.3 interrog.pl

Voici ce que génère ce script sur mon système : interrog.pl.html


#!/usr/bin/perl

use CGI;
use DBI;
$html = new CGI;

# Les variables base et table :
$psqlbase = $html->param('base');
$psqltable = $html->param('table');

print $html->header;

print "<HTML>";
print "<HEAD>\n";
print "<TITLE> Creation de liste dynamique </TITLE>\n";
print "</HEAD>";
print "<BODY>\n";
print "<CENTER><B><FONT SIZE=\"+1\">";
print "<A NAME=DEBUT>Resultat de la requete</A>";
print "</FONT></B></CENTER>";
print "<DIV ALIGN=right><A HREF=\"#FIN\">FIN</A></DIV>\n";
print "<HR>\n";

@parametre = $html->param();

foreach (@parametre) {

   if (m/^check/) {
      s/^check//g;
      $listechamps .= "$_" . ", ";
      }

   elsif (m/^input/) {
      $temp0 = $html->param($_);
      s/^input//g;
      if ((m/inf$/) and ($temp0)) {
   s/inf$//;
   $criteres0 .= "Fourchette de debut $_ : $temp0 ";
   $temp1 = $html->param("liste" . $_ . "inf");
   $criteres1 .= "and $_ $temp1 '$temp0' ";
          }
   elsif ((m/sup$/) and ($temp0)) {
      s/sup$//;
      $criteres0 .= "Fourchette de fin $_ : $temp0 ";
      $temp1 = $html->param("liste" . $_ . "sup");
      $criteres1 .= "and $_ $temp1 '$temp0' ";
          }
   elsif ($temp0) {
       $criteres0 .= "Parametre $_ : $temp0 ";
       $criteres1 .= "and $_ ~~ '$temp0' ";
      }
         }
      }

$listechamps =~ s/, $//;
$criteres1 =~ s/^and/where/;
$distinct = $html->param('distinct');

print "<CENTER><B>";
print "Criteres :";
print "</B><BR>";
print "$criteres0";
print "<BR>";

if ($distinct) {
  print "<B>Mode DISTINCT active</B><BR>";
         }
else {
  print "<B>Mode DISTINCT non active</B><BR>";
     }

print "SELECT $distinct $listechamps FROM $psqltable $criteres1";
print "</CENTER>\n";

print "<HR>\n";
print "<PRE>\n";

$heure0 = `date '+%X'`;

$dbh = DBI->connect("dbi:Pg:dbname=$psqlbase", , ) or die print "$DBI::errstr"; 

$cursor0 = $dbh->prepare("SELECT $distinct $listechamps FROM $psqltable $criteres1");
$cursor0->execute or die print "$DBI::errstr";
while ( @row0 = $cursor0->fetchrow ) {
   print "@row0\n";
      }
$cursor0->finish;

$cursor1 = $dbh->prepare("SELECT $distinct count(*) FROM $psqltable $criteres1");
$cursor1->execute or die print "$DBI::errstr";

$nbligne = $cursor1->fetchrow;
$cursor1->finish;

$dbh->disconnect;

$heure1 = `date '+%X'`;

print "</PRE>\n";
print "<HR>\n";

print "<DIV ALIGN=right><A HREF=\"#DEBUT\">DEBUT</A></DIV>\n";
print "<CENTER><B>";
print "<A NAME=FIN>$nbligne Lignes</A><BR>";
print "Heure de debut : $heure0<BR>";
print "Heure de fin   : $heure1<BR>";
print "</B></CENTER>\n";

print "</BODY>";
print "</HTML>";

exit;

Page précédente Page suivante Table des matières