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 :

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

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

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.

Puis, exécutez la commande,

chmod 755 .profile

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.

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

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      !@#$%^&*()_+| ');
--

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;

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);

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);

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.

                "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] }]); }"

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

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();

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

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.