PostgreSQL est une base de données au niveau de l’entreprise. C'est un logiciel open-source, qui rivalise avec quelques fonctions des produits d'oracle. Donc ce n’est pas une surprise que de plus en plus de projets délèguent de telles fonctionnalités importantes comme le "data-mining", exploration de données et le traitement de données vers PostgreSQL. Et ce qui est le plus important, la conception architecturale de cette base de données et très puissante et conforme au principe KISS, de sorte qu'il est vraiment très passionnant de faire face à la programmation interne PGSQL, ainsi que de la maintenir.

L'un des projets dont je m'occupe est basé sur PostgreSQL et le protail Liferay, basé à son tour sur Tomcat. En fait, il y a une architecture 3-tier, où Tomcat joue le rôle du serveur web, c'est à dire qu'il est fondamentalement un front-end qui répond à des requêtes de partout dans le monde. Les pages web de Liferay peuvent être :(a) au format HTML, (b) JSP(java server Pages), (c) ou peuvent être programmées comme des servlets. Les deux derniers scénarios vous obligent d'avoir un EDI(environnement de développement intégré) avec JSP-,portlet-, JDBC-binding déployés. Fondamentalement, autant JSP,que portlet contiennent du code, qui récupère simplement des données sql d'une instance d'une base de données( par exemple portail d'information) et prépare des pages html, qui affiche la météo d'aujourd'hui, ou les taux de change. Cependant, vous pourriez être intéressé par la génération de la même page sans faire des efforts fastidieux tels que le téléchargement et l'installation d'un EDI et la programmation d'une nouvelle servlet et la déployer à la fin. Comment y arriver ? Il suffit d'exécuter les requêtes SQL en arrière-plan, c'est à dire dans l'espace du système d'exploitation où réside les serveurs Tomcat et PostgreSQL . Vous pouvez la programmer en bash , python ou tout autre langage de script. Dans mon cas je génère une page HTML, qui contient des milliers de lignes de texte, et je la retourner au moteur de base de données du CMS Liferay de sorte que le contenu HTML doit être affiché par Liferay lui-même. J'ai aussi prévu avec cron combien de fois de régénérer cette page d'information, de sorte que Liferay affiche toujours en temps réel des actualités, des taux, etc.

Il y a un client natif, qui vient avec le serveur PostgreSQL, appelé psql. Bien que psql est une application console, il y a essentiellement les mêmes fonctionnalités que son homologue PGAdmin une application avec GUI basée sur GTK. Si vous ne l'avez pas installé sur votre système, s’il vous plaît lancez aptitude (pour Debian) :

# aptitude install postgresql-client

Lecture des listes de paquets...

Construction de l'arbre des dépendances...

Lecture des informations d'état...

Lecture de l'information d'état étendu...

Initialisation de l'état des paquets...

Écriture de l'information d'état étendu...

Les archives suivants seront installés:

  postgresql-client postgresql-client-8.3{a} postgresql-client-common{a} 

0 paquets mis à jour, 3 nouvellement installés, 0 à enlever et 6 non mis à jour.

Il est nécessaire de télécharger 2028kB d'archives. Après dépaquetage,  5276kB seront utilisés.

Voulez vous continuer? [Y/n/?]

(1). Le paquet PostgreSQL se compose de psql ainsi que d'autres utilités auxiliaires.

Il va installer psql,ainsi que les services publics pg* (pg_dump, pg_restore et autres).

Sûrement, vous pouvez installer une application graphique GUI aussi bien pour effectuer des tâches complexes, comme l'analyse de données :

# aptitude install pgadmin3

Figure 1. PgAdmin - application graphique pour la manipulation des requêtes SQL.

A l'aide de psql, vous pouvez très facilement exécuter n'importe quelle requête SQL, comme ceci:

psql -q -n -h 127.0.0.1 news_lportal -U root -c "select userid, emailaddress from user_"

lorsqu'il est utilisé :

Alternativement, vous pouvez exécuter psql avec l’opérateur update, comme ceci :

psql -q -n -h 127.0.0.1 news_lportal -U root -c "update journalarticle set content = '<H1>Hello, World!</H1>' where id_ = 24326"

où ID avec le numéro 24326 est mon document HTML créé précédemment par Liferay et stocké dans la base de données PostgreSQL news-lportal.

De cette façon, vous pouvez rafraîchir toute information stockée à l’intérieur de la table journalArticle. La seule chose qu'il faut retenir est l'ID correct de votre article.

Cependant, dans la vraie vie cette astuce de "update" ne fonctionnera pas comme il faut. J'ai préparé un script avec "update" (import_table.sh), où le contenu du fichier table_news.html doit être transféré dans PostgreSQL.

#!/bin/sh

    ct=`cat table_news.html`

    psql -t -l -q -n -h 127.0.0.1 news_lportal -U root -c "update journalarticle set content = '$ct' where id_ = 24326;"

(2). Script très simple avec import (import-table.sh), première version.

argh! Il n'a pas fonctionné. Le client PsotgreSQL a refusé d’exécuter une commande update.

$ ./import_table.sh

./import_table.sh: line 4: /usr/bin/psql: Argument list too long

A première vue, le fichier table-news.html semble être bon. Mais un examen plus attentif montre un autre rattrapage, le fichier est un peu trop grand, 400 kb de taille.

$ file table_news.html

table_news.html: UTF-8 Unicode text, with very long lines

$ cat table_news.html | wc

    617    2505  408460

Y a t-il un mécanisme pour charger n'importe quel fichier texte plus grand que 2kb en base de données. Heureusement,PostgreSQL il y a des fonctions d'import/export qui faciliteront la communication avec les opérations d'entrée/sortie sur fichier. Déclarons notre propre procédure get_text_document_portal() qui va charger n'importe quel fichier texte dans la base de données.

- Function: get_text_document_portal(character varying) 

-- DROP FUNCTION get_text_document_portal(character varying); 

CREATE OR REPLACE FUNCTION get_text_document_portal(p_filename character varying) 

  RETURNS text AS 

$BODY$ SELECT CAST(pg_read_file(E'liferay_import/' || $1 ,0, 100000000) AS TEXT); 

$BODY$ 

  LANGUAGE sql VOLATILE SECURITY DEFINER 

  COST 100; 

ALTER FUNCTION get_text_document_portal(character varying) OWNER TO postgres; 

(3). Notre nouvelle procédure va appeler la fonction pg_read_file() et va lire le fichier texte à partir du disque.

Pour charger un fichier texte dans une base de données nommées news-lportal, j'ai écrit le script ci-dessous (import-table-2.sh) qui prend un nom de fichier, dans cet exemple, table.text, en tant que paramètre de la procédure get_text_document_portal() et place son contenu dans le champ correspondant dans la table journalarticle.

#!/bin/sh

    psql -q -n  -h 127.0.0.1 news_lportal -U root -c "update journalarticle set content = get_text_document_portal('table.text') where id_  = 24326;"

(4). Le script "import" (import-table-2.sh) qui déclenche notre nouvelle procédure pgsql.

Tout ce que vous devez faire est de changer le fichier source HTML, nommé table.text et exécuter import-table-2.sh. S'il vous plaît prêtez attention à l'endroit où le fichier importé doit être placé, il s'agit d'un sous répertoire liferay-import de l'arbre /var/lib/postgresql/8.3/main/

$ ls -l /var/lib/postgresql/8.3/main/

total 48

-rw-------  1 postgres postgres    4 Nov  9 10:20 PG_VERSION

drwx------ 10 postgres postgres 4096 Nov 10 11:16 base

drwx------  2 postgres postgres 4096 Mar  4 16:44 global

drwx------  2 postgres postgres 4096 Dec  3 18:27 liferay_import

drwx------  2 postgres postgres 4096 Nov  9 10:20 pg_clog

drwx------  4 postgres postgres 4096 Nov  9 10:20 pg_multixact

drwx------  2 postgres postgres 4096 Mar  1 13:29 pg_subtrans

drwx------  2 postgres postgres 4096 Nov  9 10:20 pg_tblspc

drwx------  2 postgres postgres 4096 Nov  9 10:20 pg_twophase

drwx------  3 postgres postgres 4096 Mar  4 12:43 pg_xlog

-rw-------  1 postgres postgres  133 Feb 11 22:09 postmaster.opts

-rw-------  1 postgres postgres   53 Feb 11 22:09 postmaster.pid

lrwxrwxrwx  1 root     root       31 Nov  9 10:20 root.crt -> /etc/postgresql-common/root.crt

lrwxrwxrwx  1 root     root       36 Nov  9 10:20 server.crt -> /etc/ssl/certs/ssl-cert-snakeoil.pem

lrwxrwxrwx  1 root     root       38 Nov  9 10:20 server.key -> /etc/ssl/private/ssl-cert-snakeoil.key

(5). Inforamtion sur les propriétaires du pool de disque de stockage de PostgreSQL.

Elle est détenue par postgres et ne peut être modifiée que par cet utilisateur, ou par le compte root. Bien sûr, vous pouvez ajouter une entrée dans le crontab de root, mais une bonne pratique est de répartir la tâche entre les différents comptes. Les tâches de base de données seront affectées à Postgres uniquement, et toute autre tâche à Tomcat par exemple. Alors, comment l'utilisateur de Tomcat pourrait écrire dans le répertoire Liferay-import avec des bits d’accès de postgres. En créant un lien, un lien symbolique ne fonctionne pas, mais un lien réel fera l'affaire !

# ln /var/lib/postgresql/8.3/main/liferay_import/table.text	/home/tomcat/db/table.text

(6). Lien réel permet de dépasser les limitations de propriété causés par le lien symbolique.

#!/bin/sh



        /home/tomcat/db/prepare_table_news.sh > /home/tomcat/db/table.text

        /home/tomcat/db/import_table_2.sh

(7). Script(mk-db.sh) qui prépare arbitrairement un document HTML et le charge dans la base de données.

Houray ! Maintenant je peux placer une entrée dans le crontab de Tomcat et obtenir les actualités mises à jour chaque heure et cela se fait à partir d'un compte Tomcate.

$ crontab -l

# m h  dom mon dow   command

0 * * * * /home/tomcat/db/mk_db.sh > /dev/null

(8). Une entrée dans le crontab de Tomcat qui doit être exécuté chaque heure pour mettre à jour les actualités.

Il existe différentes approches pour fournir des informations à jour quand vous utilisez l’ protail Liferay et la technologie portlet. La première façon nécessite d'avoir un environnement de développement dédié pré-installé (IDE NetBeans avec portlets), tandis qu'une autre méthode n'a besoin que d'une connaissance de la programmation de script shell et d’être capable de créer correctement des requêtes SQL. Bien sûr, la meilleure façon est d'avoir un développeur à temps plein qui maîtrise l'EDI, les protlets Standards JSR168/JSR268, et qui programmerait n'importe quelle application web dont vous avez besoin, en particulier les pages HTML avec des informations qui changent dynamiquement. Cependant, vous pouvez obtenir les mêmes résultats beaucoup plus rapidement, il suffit de s'appuyer sur des outils de la console Linux.

[1] http://www.postgresql.org/

[2] « Practical PostgreSQL » — Joshua D. Drake, John C. Worsley, O'Reilly Media