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

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

7. Annexe - Version amélioré 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 système</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  zéro\">\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> Création de liste dynamique </TITLE>\n";
print "</HEAD>";
print "<BODY>\n";
print "<CENTER><B><FONT SIZE=\"+1\">";
print "Veuillez entrer vos critères :";
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> Création de liste dynamique </TITLE>\n";
print "</HEAD>";
print "<BODY>\n";
print "<CENTER><B><FONT SIZE=\"+1\">";
print "<A NAME=DEBUT>Résultat de la requête</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 début $_ : $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 "Critères :";
print "</B><BR>";
print "$criteres0";
print "<BR>";

if ($distinct) {
  print "<B>Mode DISTINCT activé</B><BR>";
               }
else {
  print "<B>Mode DISTINCT non activé</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 début : $heure0<BR>";
print "Heure de fin   : $heure1<BR>";
print "</B></CENTER>\n";

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

exit;

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