Combiner Perl et PostgreSQL

Gazette Linux n°67 — Juin 2001

Claire Boussard

Adaptation française  

Frédéric Marchal

Correction du DocBook 

Article paru dans le n°67 de la Gazette Linux de juin 2001.

Cet article est publié selon les termes de la Open Publication License. La Linux Gazette n'est ni produite, ni sponsorisée, ni avalisée par notre hébergeur principal, SSC, Inc.


Table des matières

Introduction
Télécharger et installer Perl
Télécharger et installer PostgreSQL avec Perl
Exemples de commandes perl/sql
Mettre en place les tables et les procédures pl/perl pour les procédures pl/pgsql « Insertion », « Modification », « Suppression »
La procédure pl/sql pour l'insertion (insert)
La procédure pl/sql pour la modification (update)
La procédure pl/sql pour la suppression (delete)
Quelques points à prendre en considération
Étendre les permissions dans Perl
Conclusion
Références
À propos de l'auteur

Introduction

PostgreSQL a parcouru un long chemin jusqu'à la version 7.1. J'ai dû attendre pour avoir la possibilité de manipuler des objets de grande taille. Dans les premières versions, la taille des champs des tableaux était limitée à 32k. Sinon, il fallait utiliser des astuces compliquées pour manipuler de grands objets.

Finalement, j'ai décidé d'installer Perl en inclusion dans PostgreSQL parce que PostgreSQL a tout ce que j'aime :

  1. un vrai langage pour les procédures stockées (pl/pgSQL),

  2. une manipulation agréable des objets de grande taille,

  3. la possibilité d'inclure des instructions Perl,

  4. une grande similitude avec Oracle à beaucoup de points de vue, ce qui rend la transition d'Oracle à PostgreSQL, ou vice versa, très raisonnable,

  5. beaucoup des fonctionnalités avancées que je désire avoir sur un serveur de bases de données,

  6. un livre libre sur le web. Je tiens beaucoup à la documentation libre.

Globalement, la procédure a été pénible à cause de petits ajustements à faire ici et là. Voici le schéma de base :

  1. Installer Perl 5.6.1. Garder les valeurs par défaut à l'exception de deux changements.

  2. Installer PostgreSQL après avoir installé Perl

  3. Installer Perl sur PostgreSQL et appliquer un correctif

Télécharger et installer Perl

Prenez soin d'installer Perl avant PostgreSQL. Je ne sais pas si dans les dernières versions de Redhat 7.1 ou de Debian, libperl est un module partagé.

cd /usr/local/src
lynx --source http://www.tcu-inc.com/perl5.6.1.tgz > perl-5.6.1.tgz
tar -zxvf perl-5.6.1.tgz
cd perl-5.6.1
rm -f config.sh Policy.sh
sh Configure

Remplacer le préfixe par défaut « /usr/local » par « /usr ». Puis, quand il pose la question « Build a shared libperl.so (y/n) [n] » (construire la librairie partagée libperl.so), répondre « y » (oui). Appuyer sur entrée pour répondre aux autres questions.

make
make install

Télécharger et installer PostgreSQL avec Perl

Quand j'ai téléchargé PostgreSQL, j'en ai profité pour essayer les interfaces pour Tcl, C, Python et ODBC. Je n'ai pas essayé Java, mais c'est une possibilité. Si vous prévoyez d'utiliser Perl avec PostgreSQL, je vous recommande de télécharger aussi DBI et DBD::Pg à partir de cpan.perl.com.

Vous pouvez choisir Tcl et Perl parmi les langages procéduraux. Vous pouvez vraiment exécuter des instructions Perl et Tcl à l'intérieur de requêtes SQL. Vous disposez aussi du langage procédural Pl/pgSQL (similaire à pl/sql). Voici les étapes que j'ai suivies pour installer PostgreSQL avec Perl. Voici un fichier texte contenant la même information.


### En premier lieu, il faut compiler Perl comme un module dynamique. Si vous 
### ne le faites pas, vous pourrez installer PostgreSQL, mais vous n'aurez pas
### l'interface pl/sql

cd /usr/local/src
lynx --source ftp://postgresql.readysetnet.com/pub/postgresql/v7.1.1/postgresql-7.1.1.tar.gz > postgresql-7.1.1.tar.gz
tar -zxvf postgresql-7.1.1.tar.gz
cd postgresql-7.1.1

### Il nous faut quelques variables d'environnement, qu'on pourra mettre dans
####  le .profile de l'utilisateur postgres par la suite

PATH=/usr/local/pg711/bin:$PATH
export PATH
export LD_LIBRARY_PATH=/usr/local/pg711/lib
export PGDATA=/usr/local/pg711/data
export PGLIB=/usr/local/pg711/lib
export POSTGRES_HOME=/usr/local/pg711

  ### Ce script a pour fonction de supprimer toute installation antérieure
  ### Je l'ai écrit ainsi de façon à pouvoir le débugger, s'il ne marche pas
  ### du premier coup

  #### Ignorez tous les messages d'erreur disant que le serveur de bases de  
  ### données ne tourne pas, probablement, vous n'en avez pas qui tourne
su -c '/usr/local/pg711/bin/initdb -D /usr/local/pg711/data -l logfile stop' postgres
  ### Ignorez tout message d'erreur disant que cet utilisateur existe déjà
adduser postgres
rm -rvf /usr/local/pg711

  ### Maintenant rendons postgres propriétaire du répertoire de destination 
mkdir /usr/local/pg711 
chown postgres /usr/local/pg711

  ### Ignorez les erreurs liées à clean 
make clean
  ### Compiler et installer PostgreSQL
./configure --prefix=/usr/local/pg711 --with-perl --with-tcl --with-CXX --with-python --enable-odbc 
make
make install

  ### Installer l'interface Perl pour PostgreSQL
gmake -C src/interfaces/perl5 install
cd /usr/local/src/postgresql-7.1.1/src/interfaces/perl5
perl Makefile.PL
make 
  ### Décommenter la ligne suivante si vous voulez le tester 
##  su -c 'make test' postgres
make install

  ### Donner la propriété de tous les fichiers à l'utilisateur postgres
chown -R postgres /usr/local/pg711

  ### Initialiser les bases de données
su -c '/usr/local/pg711/bin/initdb -D /usr/local/pg711/data' postgres

  ### Démarrer le serveur de bases de données
su -c '/usr/local/pg711/bin/pg_ctl -D /usr/local/pg711/data -l logfile start' postgres

  ### Les interfaces pour Perl, Tcl, et Pl/sql devraient avoir été créées 
  ### Ajoutons les maintenant 
  
su -c 'createlang plpgsql template1' postgres
su -c 'createlang pltcl template1' postgres

### Nous supposons maintenant que Perl 5.6.1 est installé correctement
rm -f /usr/local/pg711/lib/libperl.so
ln -s /usr/lib/perl5/5.6.1/i686-linux/CORE/libperl.so \
  /usr/local/pg711/lib/libperl.so
su -c 'createlang plperl template1' postgres

  ### Si tout a été réalisé correctement, chaque nouvelle base de données sera 
  ### générée par duplication de template1 et disposera de Perl, Tcl et PL/PGSQL

  ### Encore un truc
su -c 'createdb postgres' postgres

Dans le répertoire personnel de l'utilisateur postgres, créez un fichier du nom de .profile et mettez dedans ce qui suit.

#!/usr/bin

PATH=/usr/local/pg711/bin:$PATH
export PATH
export LD_LIBRARY_PATH=/usr/local/pg711/lib
export PGDATA=/usr/local/pg711/data
export PGLIB=/usr/local/pg711/lib
export POSTGRES_HOME=/usr/local/pg711

Puis, exécutez la commande,

chmod 755 .profile

Exemples de commandes perl/sql

Exécutez les commandes de http://www.ca.postgresql.org/users-lounge/docs/7.1/programmer/plperl-use.html.

Comme je vous ai fait créer la base de données « postgres », vous n'avez plus qu'à taper ces deux commandes en tant qu'utilisateur root au début pour vous retrouver dans l'interface psql.

su -l postgres
psql

Ceci suppose que vous avez correctement rempli le .profile de l'utilisateur postgres. Si ce n'est pas le cas, faites :

su -l postgres
PATH=/usr/local/pg711/bin:$PATH
export PATH
export LD_LIBRARY_PATH=/usr/local/pg711/lib
export PGDATA=/usr/local/pg711/data
export PGLIB=/usr/local/pg711/lib
export POSTGRES_HOME=/usr/local/pg711
psql

La fonction suivante vous permet de chercher parmi des données et renvoit une copie du nom si ce nom contient le texte que vous cherchez, en tenant compte, ou non, de la casse.

drop function search_name(employee,text,integer);
CREATE FUNCTION search_name(employee,text,integer) RETURNS text AS '
    my $emp = shift;
    my $Text = shift;
    my $Case = shift;

    if (($Case > 0) && ($emp->{''name''} =~ /\\Q$Text\\E/i)) 
      { return $emp->{''name''}; }
    elsif ($Case > 0) {return "";}
    elsif ($emp->{''name''} =~ /\\Q$Text\\E/) 
       {    return $emp->{''name''}; }
    else { return "";}
' LANGUAGE 'plperl';

insert into EMPLOYEE values ('John Doe',10000,1);
insert into EMPLOYEE values ('Jane Doe',10000,1);
insert into EMPLOYEE values ('Giny Majiny',10000,1);

select name,search_name(employee,'j',0) from employee;
select name,search_name(employee,'j',1) from employee;

select name from employee where search_name(employee,'j',1) = name;
select name from employee where search_name(employee,'j',0) = name;

Bien sûr, cette fonction est un peu ridicule. Elle devrait seulement renvoyer 0 pour faux et 1 pour vrai, mais pour des raisons esthétiques, je lui fait renvoyer une copie du nom.

Mettre en place les tables et les procédures pl/perl pour les procédures pl/pgsql « Insertion », « Modification », « Suppression »

Pour avoir une copie des commandes SQL pour cette section, procurez vous SQL_setup.txt. Il y a plusieurs choses que je voudrais faire.

  1. Créer des procédures « Insertion », « Modification », « Suppression », qui sauvegarderont tous les changements dans une table d'historique ou de sauvegarde. On enregistrera ainsi tout ce qui se passe. Il faut une gestion des erreurs raisonnable. On pourrait faire une gestion des erreurs plus fine mais alors mes procédures stockées deviendraient trop grosses.

  2. Utiliser des procédures Perl pour filtrer les données à insérer dans les tables. Bien sûr, on pourrait utiliser des requêtes SQL, mais les instructions Perl sont tellement plus faciles à lire pour moi.

Je ne crois pas qu'il soit possible d'avoir des procédures Perl qui exécutent les requêtes insert, update, delete ou select (insérer, modifier, supprimer ou sélectionner). La seule chose que j'ai réussi à faire en Perl est de prendre plusieurs valeurs et de renvoyer une valeur unique. De toutes façons, vous ne devriez pas avoir besoin de Perl pour exécuter SQL. Vous n'êtes pas en train d'utiliser Perl pour exécuter des requêtes, mais pour modifier des données, agir comme un filtre, ou tester les erreurs. Utilisez pl/pgsql pour traiter toutes les requêtes et n'utilisez Perl que pour manipuler les données, sans agir directement sur la base de données.

Ci-dessous, j'ai trois tables: jobs, jobs_backup, et contact. Je vais créer des procédures stockées pour la table « jobs » seulement. Les deux procédures Perl ont seulement pour fonction de vérifier que nous avons des données valides en entrée, de supprimer les caractères non imprimables et de se débarrasser des blancs. C'est pl/pgsql que nous utilisons pour exécuter effectivement les requêtes insérer, modifier et supprimer.

En prenant pour base cette méthode de manipulation des données, vous pouvez traiter n'importe lequel de vos tableaux.

L'une des choses que je dois surveiller est d'avoir des noms uniques pour les jobs. Je ne veux pas que deux jobs du même recruteur aient le même nom. Là, cela devient un peu plus compliqué, mais cela marche bien.

Je pourrais aussi utiliser une contrainte de clé étrangère de façon qu'on ne puisse pas avoir une valeur de contact_id dans la table jobs qui n'existe pas aussi dans la table contact. Le seul problème est qu'il pourrait arriver qu'on détruise accidentellement des contact_id dans contact et alors ça serait vraiment la pagaille. La meilleure solution est d'ajouter une colonne « active » dans les tables jobs et contact grâce à laquelle vous activez et désactivez les objets. De cette façon vous ne détruirez jamais des identificateurs uniques.

 --- Créer la table jobs  
 --- Une bonne suggestion serait d'avoir une contrainte de clé étrangère
 --- par rapport à la table contact
create sequence Job_Sequence;
drop table jobs;
create table jobs (
job_id int4 unique DEFAULT nextval('Job_Sequence'),
contact_id int4,
job_no int4,
job_name  text,
job_location text 
);
CREATE UNIQUE INDEX job_index ON jobs (job_name, contact_id);

-- C'est une vraie table de sauvegarde
-- Chaque fois qu'un changement se produit, l'insérer dans cette table
-- Cela ne concerne pas seulement les suppressions, 
-- mais aussi les insertions et les modifications
-- Cela devient une table d'historique, pas seulement de sauvegarde
-- Nous enregistrons même le résultat final
create sequence Backup_Job_Sequence;
drop table jobs_backup;
create table jobs_backup (
backup_id int4 unique DEFAULT nextval('Backup_Job_Sequence'),
action text CHECK (action in ('insert','update','delete','')),
error_code int4,
job_id int4,
contact_id int4,
job_no int4,
job_name  text,
job_location text
);

create sequence Contact_Sequence;
drop table contact;
create table contact (
contact_id int4  UNIQUE DEFAULT nextval('Contact_Sequence'),
name text unique,
phone text,
website text
);

 --- Insérer deux valeurs pour les contacts
 --- Je ne fais pas de procédures stockées pour cette table,
 --- seulement pour jobs 
insert into contact (name,phone,website) 
  values ('Mark Nielsen','(408) 891-6485','http://www.gnujobs.com');
insert into contact (name,phone,website)
  values ('Joe Shmoe','(1234) 111-1111','http://www.gnujobs.net');
insert into contact (name,phone,website)
  values ('Lolix.org','(12345) 111-1111','http://www.lolix.org');


 --- Sélectionner l'information dans contact pour voir si elle y est
select * from contact;

 --- Créons une fonction Perl (probablement inutile)
 --- pour vérifier si les données en entrée ne sont pas vides

drop function job_values_verify (int4,text,text);
CREATE FUNCTION  job_values_verify (int4,text,text) RETURNS int4 AS '
    my $Contact_Id = shift;
    my $Job_Name = shift;
    my $Job_Description = shift;
    my $Error = 0;
    if ($Contact_Id < 1) {$Error = -100;}
    if (!($Job_Name =~ /[a-z0-9]/i)) {$Error = -101;}
    if (!($Job_Description =~ /[a-z0-9]/i)) {$Error = -102;}
  return $Error;
' LANGUAGE 'plperl';

drop function clean_text (text);
CREATE FUNCTION  clean_text (text) RETURNS text AS '
  my $Text = shift;
    # Se débarrasser des espaces au début 
  $Text =~ s/^\\s+//;
    # Se débarrasser des espaces à la fin
  $Text =~ s/\\s+$//;
    # Se débarrasser de tout ce qui n'est pas du texte
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
    # Remplacer plusieurs blancs à la suite par un seul espace 
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Juste pour montrer comment cette fonction fait le nettoyage
select clean_text ('       ,./a<>?aaa aa      !@#$%^&*()_+| ');
--

La procédure pl/sql pour l'insertion (insert)

Pour avoir une copie des commandes SQL pour cette section, procurez vous SQL_insert.txt.

drop function insert_job (int4,text,text);
CREATE FUNCTION insert_job (int4,text,text) RETURNS int2 AS '
DECLARE
    c_id_ins int4; j_name_ins text;  l_ins text; 
    job_id1 int4; oid1 int4; test_id int4 := 0; j_no_ins int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD; record4 RECORD;
BEGIN
   j_name_ins := $2; l_ins  := $3; c_id_ins := $1;

     -- Nous exécutons maintenant quelques procédures Perl. 
     -- Ce ne sont que des exemples
     -- Effacer le nom du job
   SELECT INTO record4 clean_text(j_name_ins) as text1;
   j_name_ins = record4.text1;
     -- Effacer la localisation du job
   SELECT INTO record4 clean_text(l_ins) as text1;
   l_ins = record4.text1;
     -- Vérifier que les valeurs à insérer sont correctes
   SELECT INTO record4 job_values_verify (c_id_ins, j_name_ins, l_ins) as no;
   IF record4.no < 0 THEN return (record3.no); END IF;

     -- Regarder si les noms sont uniques, sinon renvoit 0.
   FOR record1 IN SELECT job_id FROM jobs  
      where contact_id = c_id_ins and job_name = j_name_ins
      LOOP
      test_id := record1.job_id;
   END LOOP;
     -- Si le job_id est null, très bien, sinon interrompre et renvoyer -1;
   IF test_id > 0 THEN return (-1); END IF;

   FOR record3 IN SELECT max(job_no) from jobs_backup where contact_id = c_id_ins
      LOOP
      IF record3.max IS NULL THEN j_no_ins := 0; END IF;
      IF record3.max > -1 THEN j_no_ins = record3.max + 1; END IF;
   END LOOP;

     -- Insérer les données. Le job_id est déterminé par la séquence.
   insert into jobs (contact_id, job_no, job_name, job_location)
        values (c_id_ins, j_no_ins, j_name_ins, l_ins);
     -- Récupérer l'oid unique de la ligne qu'on vient juste d'insérer 
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Récupérer l'id du job. Ne pas utiliser SELECT INTO,
     -- car il faut affecter une valeur à record2
   FOR record2 IN SELECT job_id FROM jobs where oid = oid1
      LOOP
      job_id1 := record2.job_id;
   END LOOP;
   
     -- Si job_id1 est NULL, l'insertion a échoué ou quelque chose a raté
   IF job_id1 is NULL THEN return (-2); END IF;
     -- Il doit aussi être supérieur à 0, sinon il y a quelque chose de faux
   IF job_id1 < 1 THEN return (-3); END IF;

     -- Tout s'est bien passé, renvoyer la valeur de job_id1 en tant que job_id
   insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code)
        values (c_id_ins, j_no_ins, j_name_ins, l_ins, ''insert'', job_id1);
   return (job_id1);
END;
' LANGUAGE 'plpgsql';
select insert_job (1,'Job Title 1','Boston, MA');
select insert_job (1,'Job Title 2','San Jose, CA');
select insert_job (2,'Job Title 1','Columbus, Ohio');
select insert_job (2,'Job Title 2','Houston, TX');
select insert_job (3,'Job Title 1','Denver, CO');
select insert_job (3,'Job Title 2','New York, NT');
select * from jobs;

La procédure pl/sql pour la modification (update)

Pour avoir une copie des commandes SQL pour cette section, procurez vous SQL_update.txt. La procédure Modifier doit tester s'il y a déjà un job qui porte le nom que nous voulons attribuer au job courant. Si c'est le cas, nous ne voulons rien changer, (sauf si le job_id est le même). Vous souvenez-vous qu'il y a une contrainte d'unicité sur le nom pour le même recruteur ?

drop function update_job (int4,text,text,int4);
CREATE FUNCTION update_job (int4,text,text,int4) RETURNS int2 AS '
DECLARE
    c_id_ins int4; j_name_ins text;  l_ins text; 
    job_id1 ALIAS FOR $4; oid1 int4; test_id int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD; record4 RECORD; record5 RECORD;  
    return_int4 int4 := 0; job_no1 int4 := 0;
BEGIN
   j_name_ins := $2; l_ins  := $3; c_id_ins := $1;

     -- Quelques procédures Perl 
     -- Supprimer le nom du job
   SELECT INTO record4 clean_text(j_name_ins) as text1;
   j_name_ins = record4.text1;
     -- Supprimer la localisation du job 
   SELECT INTO record5 clean_text(l_ins) as text1;
   l_ins = record5.text1;
     -- Vérifier que les valeurs à insérer sont correctes
   SELECT INTO record3 job_values_verify (c_id_ins, j_name_ins, l_ins) as no;
   IF record3.no < 0 THEN return (record3.no); END IF;

     -- Regarder si ce nom de job est en double pour ce contact
   FOR record1 IN SELECT job_id FROM jobs  
      where contact_id = c_id_ins and job_name = j_name_ins
        and job_id != job_id1
      LOOP
      test_id := record1.job_id;
   END LOOP;
     -- Si le job_id est null, très bien, sinon interrompre et renvoyer -1;
   IF test_id > 0 THEN return (-1); END IF;

     -- Regarder si le job existe, sinon renvoyer -2.
   FOR record1 IN SELECT * FROM jobs where job_id = job_id1  
      LOOP
      update jobs set contact_id = c_id_ins,  
        job_name = j_name_ins, job_location = l_ins
	where job_id = job_id1;
      GET DIAGNOSTICS return_int4 = ROW_COUNT;
      test_id := 1;
      job_no1 := record1.job_no;
   END LOOP;

     -- Si le job n'existe pas, qu'est-ce qu'on est en train de modifier? 
     -- renvoyer une erreur
   IF test_id = 0 THEN return (-2); END IF;

     -- Tout s'est bien passé, renvoyer return_int4.
   insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code, job_id)
        values (c_id_ins, job_no1, j_name_ins, l_ins, ''update'', return_int4, job_id1);
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
select update_job (3,'Changing title and owner.','Boston, MA',1);
select * from jobs;
  -- Vous devriez avoir une erreur ici, car vous allez tenter de dupliquer
  -- le nom et le contact id. 
select update_job (3,'Changing title and owner.','Boston, MA',1);

La procédure pl/sql pour la suppression (delete)

Pour avoir une copie des commandes SQL pour cette section, procurez vous SQL_delete.txt.

drop function delete_job (int4);
CREATE FUNCTION delete_job (int4) RETURNS int2 AS '
DECLARE
    job_id1 ALIAS FOR $1;
    job_exists int4 := 0;
    job_backup_exists int4 := 0;
    record1 RECORD; 
    return_int4 int4 :=0;
BEGIN
     -- Si le job_id1 n'est pas supérieur à 0, renvoyer une erreur
   IF job_id1 < 1 THEN return -1; END IF;

     -- Si le job est trouvé, le détruire, enregistrer qu'il a été trouvé, 
     -- et le sauvegarder. Je n'aime pas trop utiliser LOOP pour une seule 
     -- ligne, mais j'avais mes raisons
   FOR record1 IN SELECT * FROM jobs where job_id = job_id1
      LOOP
      delete from jobs where job_id = job_id1;  
      GET DIAGNOSTICS return_int4 = ROW_COUNT;       
      job_exists := 1;
      insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code, job_id)
        values (record1.contact_id, record1.job_no, record1.job_name, 
	  record1.job_location, ''delete'', return_int4, record1.job_id);
   END LOOP;

     -- Si job_exists == 0, renvoyer une erreur
     -- Cela signifie que le job n'a jamais existé
   IF job_exists = 0 THEN return (-1); END IF;

     -- Nous avons obtenu cela il y a longtemps, il faut que cela soit vrai,
     -- renvoit ROW_COUNT.   
   return (return_int4)
END;
' LANGUAGE 'plpgsql';
select delete_job (1);
select * from jobs;
  --- Nous l'avons déjà détruit, nous devrions avoir une erreur cette fois
select delete_job (1);

Quelques points à prendre en considération

Si vous installez Perl 5.6.1, vérifiez que tout va bien avec mod_perl, ou n'importe quel autre module que vous avez installé vous même auparavant. Installer Perl 5.6.1 peut casser des modules que vous avez utilisés précédemment avec d'autres versions de Perl. Je ne sais pas, soyez prudents. Vous allez peut-être devoir recompiler certains modules.

Voici un récapitulatif des points à prendre en compte.

  1. Créer une contrainte de clé étrangère de façon à être sûr que le contact_id des jobs existe dans la table contact.

  2. Ne jamais détruire de ligne dans les tables contact et job. Ajouter plutôt une colonne pour les désactiver. Donnez à cette colonne le nom de « active » si 0 veut dire inactif et 1 actif.

  3. Vous pouvez combiner insert/update dans la même procédure. Si vos données sont toujours pertinentes à 100%, alors exécutez systématiquement update, si update ne trouve pas le job, il l'insèrera pour vous. Dans certains cas, cela peut-être utile.

  4. Chaque fois que vous pouvez tester une condition d'erreur, faites-le. Même si j'ai été très raisonnable dans le test des erreurs, on peut en faire davantage.

  5. Oubliez votre intention d'utiliser des procédures Perl pour exécuter directement des requêtes SQL. Ne les utilisez que pour manipuler des données.

  6. Utilisez pl/pgsql pour associer des procédures Perl avec des requêtes SQL

  7. Vous devriez mettre en place votre procédure comme une transaction (faire un rollback) pour le cas où insertion, modification ou suppression échoueraient pour une raison inconnue.

  8. Je ne sais pas combien de mémoire est utilisée par une procédure Perl, ni combien est libérée lorsque son exécution est terminée, donc je ne sais pas quelle est la surcharge apportée par l'exécution de procédures Perl. Pour mes besoins, les procédures pl/pgsql se révèlent toujours plus rapides que l'exécution manuelle de requêtes SQL au moyen de scripts Perl côté serveur web. Comme je suis tourné dans la bonne direction, je veux bien m'acommoder de toute surcharge due aux procédures Perl. A part cela, je peux certainement prendre des commandes SQL très complexes et les réduire à quelques lignes de code Perl. Si je compare une utilisation à bon escient de pl/pgsql, du SQL standard et pl/perl, je vois un gain de puissance significatif et peu d'inconvénients.

Étendre les permissions dans Perl

Ce que je m'apprête à faire est une très mauvaise chose. Il s'agit de s'affranchir de certaines mesures de sécurité dans Perl, de façon à pouvoir faire plus de choses.

Tout d'abord, ajoutez cette méthode juste après la méthode « permit » dans Safe.pm. Chez moi, Safe.pm est situé à /usr/local/src/perl-5.6.1/lib/Safe.pm. Modifier un module que vous n'avez pas créé vous même signifie que si jamais vous effectuez une mise à jour de ce module, vous perdrez vos modifications. Je le répète, vous risquez de BOUZILLER LE TRAVAIL DE L'UN DE VOS AMIS QUI PROGRAMME SUR LA MÊME MACHINE QUE VOUS. Encore une fois, je suis en train de faire quelque chose de dégueulasse, et vous ne devriez pas m'imiter.

sub permit_all {
    my $obj = shift;
    $obj->{Mask} = invert_opset full_opset;
}

Deuxièmement, arrêtez votre serveur de bases de données.

Troisièmement, recompilez plperl avec quelques changements.

Faites les changements suivants dans le fichier plperl.c. Depuis ici...

                "require Safe; SPI::bootstrap();"
                "sub ::mksafefunc { my $x = new Safe; $x->permit_only(':default');$x->permit(':base_math');"
                "$x->share(qw[&elog &DEBUG &NOTICE &NOIND &ERROR]);"
                " return $x->reval(qq[sub { $_[0] }]); }"

...jusque là (vous pouvez trouver cela dans le fichier New_plperl.txt)

Maintenant, recompilez plperl et installez-le.

cd /usr/local/src/postgresql-7.1.1/src/pl/plperl
rm -f *.o
make 
make install

Quatrièmement, redémarrez le serveur de bases de données PostgreSQL.

Voyez si vous pouvez ouvrir un shell,

drop function ls_bad ();
CREATE FUNCTION  ls_bad () RETURNS text AS '
my @Temp = `ls /tmp`;
my $List = "@Temp";
$List =~ s/\n/ /g;
  return $List;
' LANGUAGE 'plperl';
select ls_bad();

Si vous obtenez le contenu de votre répertoire /tmp, c'est que vous pouvez ouvrir un shell et c'est très dangereux.

Pendant toute une journée, j'ai essayé de comprendre comment faire marcher DynaLoader avec pl/perl. Au départ, j'ai lu la documentation expliquant comment inclure du Perl dans du C, et ce n'est pas difficile. Il y a même une page de man qui expose cela. Mais je n'ai pas cessé de rencontrer des problèmes. Récemment j'ai essayé de ne pas utiliser le module Safe.pm en même temps, mais cela ne m'a pas mené très loin. J'étais bien prêt de réussir à compiler Dynaloader avec plperl mais j'ai renoncé. Après avoir perdu une journée, j'espère que quelqu'un d'autre va essayer.

Si vous arrivez à faire marcher Dynaloader convenablement avec plperl, ou plus exactement si vous trouvez un moyen pour que je puisse charger n'importe quel module avec plperl, faites-le moi savoir. Je suis arrivé au point où je peux charger un module écrit purement en Perl, mais pas un module qui comprend des parties en C. J'aimerais pouvoir charger n'importe quel module. Je crois qu'il faut arrêter d'utiliser Safe.pm pour faciliter les choses. S'il vous plaît, envoyez un mail à articles@gnujobs.com si vous réussissez, cela m'intéresserait vraiment.

S'il vous plaît, ne faites pas cela. Je voulais seulement vous montrer comment vous pouvez contourner les mesures de sécurité si vous y tenez vraiment.

Conclusion

Combiner Perl et PL/PGSQL est VRAIMENT VRAIMENT COOL. Pourquoi ?

  1. J'aime utiliser Perl (ou tout autre langage — quelqu'un pour faire une interface en Python ?) pour manipuler des données, car SQL peut devenir vraiment pénible.

  2. L'association de Perl et PL/PGSQL peut faire que la plus grande partie du travail est faite du côté de la base de données, ce qui signifie qu'il y a moins code à écrire côté client. Par exemple, disons que vous avez un serveur web connecté à une base de données. Si le serveur de bases de données exécute plein de code Perl, vos script Perl sur le serveur web seront moins gros. C'est vrai pour toutes les procédures stockées en général. Est-ce que ce n'est pas beaucoup mieux d'exécuter une procédure stockée que d'avoir tous ces scripts Perl à côté du serveur web pour effectuer ces tâches (que la procédures stockée fait pour vous) ?

  3. Si vous faites toute la programmation Perl spécifique du côté de la base de données, alors vous aurez moins de travail, quel que soit le langage utilisé pour vous connecter à la base de données. Ainsi, la procédure stockée devient un objet que votre serveur web doit savoir comment utiliser, sans avoir besoin de comprendre comment il est fait. C'est chouette.

  4. Je suis en train d'utiliser progressivement davantage de procédures Perl (quand c'est pertinent) et je teste ainsi la stabilité de pl/perl.

  5. J'aimerais bien être capable de charger n'importe quel module avec plperl, rien que pour me marrer. Si vous trouvez un moyen de faire cela, envoyez un mail à articles@gnujobs.com s'il vous plaît.

PostgreSQL est de loin le serveur de base de données le plus cool que j'aie utilisé, MySQL vient juste derrière. Je n'ai jamais vraiment aimé travailler avec un quelconque serveur de base de données du commerce. Je vois tellement plus de potentiel dans PostgreSQL qu'en fait les serveurs du commerce sont en train d'imiter certaines des fonctionnalités de PostgreSQL. Je suis impatient de voir quelqu'un développer une interface Python pour créer des procédures en Python. Le chapitre 20 du dernier livre Programming Python (ISBN: 0-596-00085-5) explique comment inclure du Python dans du C. Quand j'aurai un peu de temps, l'année prochaine peut-être, j'aimerais essayer, à moins que quelqu'un me coupe l'herbe sous le pied ! Je crois que la possibilité d'avoir des procédures écrites dans différents langages prendra de la valeur dans le futur. Je suis aussi impatient de voir des procédures qui renvoient plus d'une valeur. C'est gênant de ne pouvoir renvoyer qu'une seule valeur. J'ai essayé de définir une fonction qui renvoit plus d'une valeur mais ça n'a pas marché. J'ai essayé d'avoir une fonction qui renvoit une structure, mais je ne suis pas allé très loin.

Références

Si cet article change, vous trouverez la version courante (en anglais) à http://www.gnujobs.com/Articles/20/Perl_PostgreSQL.html.

À propos de l'auteur

Mark Nielsen

Mark travaille comme consultant indépendant qui consacre du temps à des causes telles que GNUJobs.com, la rédaction d'articles, l'écriture de logiciel libre and il travaille comme volontaire à eastmont.net.

Adaptation française de la Gazette Linux

L'adaptation française de ce document a été réalisée dans le cadre du Projet de traduction de la Gazette Linux.

Vous pourrez lire d'autres articles traduits et en apprendre plus sur ce projet en visitant notre site : http://www.traduc.org/Gazette_Linux.

Si vous souhaitez apporter votre contribution, n'hésitez pas à nous rejoindre, nous serons heureux de vous accueillir.