Intéressé par des cours d'informatique en ligne ?
Visitez mon nouveau site https://www.yesik.it !

Dans cet article, nous allons voir comment envoyer un mail en réponse à un événement dans une base de données gérée par Apache Derby.

L'objectif ici ne sera pas d'étudier le fonctionnement de Derby ou JavaMail, mais plutôt de voir comment intégrer ces deux technologies. En particulier, on suppose ici que vous avez déjà installé Derby, et que vous avez un minimum de connaissance en base de données.

La base

L'application qui va nous servir de support est celle d'une gestion d'inventaire dans un magasin. Et nous allons concentrer notre attention sur la table chargée d'indiquer pour chaque produit la quantité en stock.

Tout le but de cet exercice sera d'envoyer un mail au responsable de l'approvisionnement quand la quantité en stock d'un produit tombera sous un seuil pré-défini. Pour bien cerner le problème, il convient aussi de préciser que plusieurs applications ont potentiellement accès à la base de données. Or, on ne souhaite pas répliquer le code de gestion des ruptures de stock dans chaque application. C'est pourquoi il a semblé plus souhaitable que ce soit au niveau de la base de données que soit détecté et géré ce type d'événement.

Lancer le serveur

Tout d'abord, nous allons lancer le serveur Derby à partir du répertoire destiné à héberger notre base:

sh$ mkdir stock
sh$ cd stock
sh$ export CLASSPATH="${CLASSPATH:-.}:${JAVAMAIL_HOME}/mail.jar" # Uniquement si JavaMail n'est pas déjà dans le CLASSPATH
sh$ ${DERBY_HOME}/bin/startNetworkServer -noSecurityManager
2009-08-14 22:41:30.686 GMT : Apache Derby Network Server - 10.5.1.1 - (764942) started and ready to accept connections on port 1527

Remarquez au passage que nous préparons ici le terrain pour la suite des événements, en ajoutant JavaMail au CLASSPATH (si nécessaire), et en lançant le serveur Derby sans gestionnaire de sécurité. Si ce dernier point vous fait – à juste titre – sauter au plafond: rassurez-vous, nous reviendrons là dessus à la fin de cet article.

Lancer le client et créer la table

Tout au long de cet article, nous allons utiliser ij comme client. D'abord pour la création des tables, puis ultérieurement pour simuler les différentes applications utilisant la base.

Lançons donc ij pour créer la base et notre table des produits:

sh$ ${DERBY_HOME}/bin/ij
ij version 10.5
ij> CONNECT 'jdbc:derby://localhost:1527/GestionStock;create=true';
ij> CREATE TABLE Produits (
>         ref char(8) PRIMARY KEY NOT NULL,
>         designation varchar(30) NOT NULL,
>         stock int NOT NULL DEFAULT 0,
>         minStock int NOT NULL DEFAULT 10);
0 rows inserted/updated/deleted
ij> INSERT INTO Produits ( ref, designation, stock )
>       VALUES ('DD01XX11', 'Disque Dur 1Go', 14),            -- 14 disques durs 1Go en stock
>              ('DD08XX22', 'Disque Dur 8Go', 7),             --  7 disques durs 8Go en stock
>              ('MBP4XX33', 'Carte Mère P4 1.5GHz', 10),      -- 10 cartes mère en stock
>              ('VBX70044', 'Carte Vidéo RADONDON X700', 12); -- 12 cartes vidéo X700 en stock
4 rows inserted/updated/deleted

Méthode Java

Abandonnons maintenant Derby pour quelques instants, et intéressons nous à l'autre extrême de notre développement: le code Java.

Pour être utilisable par Derby, une méthode Java doit être définie sous la forme d'une méthode de classe publique (static public ...). La classe à laquelle appartient la méthode doit également avoir une visibilité publique (public class ...).

Nous allons donc écrire une méthode répondant à ces contraintes pour remplir notre objectif, à savoir envoyer un mail. A nouveau, il ne s'agit pas d'un tutoriel sur JavaMail mais plutôt de voir comment envoyer un mail à partir de Derby. Néanmoins, JavaMail est suffisamment bien fait pour que le code soit clair, même sans connaissance préalable:

import java.util.Properties;
 
import javax.mail.Session;
import javax.mail.Message;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.InternetAddress;
import javax.mail.Transport;
 
import javax.mail.internet.AddressException;
import javax.mail.NoSuchProviderException;
import javax.mail.MessagingException;
 
public class MailHelper {
    public static int sendLowStockMessage(String ref, String designation, int qty) 
			throws NoSuchProviderException, AddressException, MessagingException {
	Properties		props	    = new Properties();
	props.setProperty("mail.from", "gestion-stock@chicoree.fr");
	props.setProperty("mail.host", "localhost");
 
	// Prépare une session JavaMail avec les propriétés initialisées ci-dessus
	Session		session	    = Session.getInstance(props);
 
	// Création du message
	Message		message	    = new MimeMessage(session);
	InternetAddress	recipient   = new InternetAddress("sylvain@localhost");
	message.setRecipient(Message.RecipientType.TO, recipient);
	message.setSubject(String.format("%s (%s): stock insuffisant", designation, ref, qty));
	message.setText(String.format("Attention, le stock de l'article %s (%s) est insuffisant%n"
				      +  "Reste seulement %d en stock%n", 
				      designation, ref, qty));
 
	// Connexion au serveur de mail et transmission du message
	Transport.send(message);
 
	return 1; // Une ``fonction SQL'' doit renvoyer un résultat...
    }
 
    public static void main(String[] args) throws Exception {
	sendLowStockMessage("RR001122", "Truc en plume", 2);
    }
}

Quand à la compilation, rien de notable ici. Si ce n'est que, toujours pour satisfaire Derby, nous allons devoir inclure le fichier Java compilé dans une archive JAR:

sh$ export CLASSPATH="${CLASSPATH:-.}:${JAVAMAIL_HOME}/mail.jar"
sh$ javac MailHelper.java
sh$ jar cf MailHelper.jar MailHelper.class # Derby requiert que les classes utilisateurs soient enregistrées dans un JAR

SQL+Java

Invoquer une méthode Java à partir de Derby

Nous allons maintenant essayer de faire cohabiter les mondes Java et SQL. Dans Derby, cela implique plusieurs étapes:

  1. Tout d'abord, il faut importer la (ou les) classe(s) Java dans la base de données;
  2. Ensuite, il faut modifier le CLASSPATH de la base de données pour permettre à Derby de localiser ces classes;
  3. Enfin, il faut créer une fonction ou une procédure SQL pour chaque méthode Java que vous souhaitez utiliser.

Fonction vs procédure

SQL distingue les notions de fonction et de procédure. Les deux se différencient par les caractéristiques suivantes:

  • Une fonction renvoie un résultat. Une procédure ne renvoie rien.
  • Une fonction SQL est crée par la commande CREATE FUNCTION, une procédure par CREATE PROCEDURE.
  • Enfin, une fonction peut être appelée dans une requête SQL comme SELECT. Alors qu'une procédure ne peut être exécutée que par la commande SQL CALL.

Ce dernier point est extrêmement important, puisque si nous souhaitons exécuter une commande juste pour certaines lignes d'une table, celle-ci devra être exécutée dans une requête SELECT ... WHERE – ce qui disqualifie d'emblée une procédure.

Sans plus tergiverser, voici la séquence des opérations à effectuer pour importer notre méthode Java dans Derby et lui faire correspondre une fonction SQL:

sh$ $DERBY_HOME/bin/ij
ij version 10.5
ij> CONNECT 'jdbc:derby://localhost:1527/GestionStock';

ij> -- Importe le JAR dans la base:
ij> CALL sqlj.install_jar(
> 	'/path/to/MailHelper.jar', -- Le chemin vers le JAR
> 	'APP.MailHelper',          -- Le nom sous lequel il sera connu par Derby
> 	0);
Statement executed.

ij> -- Modifie le CLASSPATH de la base
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
> 	'derby.database.classpath', -- Change le classpath de la base
> 	'APP.MailHelper');          -- pour designer notre JAR
Statement executed.

ij> -- Crée une fonction SQL externe qui appellera notre méthode Java
ij> CREATE FUNCTION SEND_LOW_STOCK_MESSAGE(
> 		ref varchar(255),
> 		designation varchar(255),
> 		qty int)
> 	RETURNS int
> 	PARAMETER STYLE JAVA -- Les paramètres seront convertis en types Java
> 	LANGUAGE JAVA        -- Fonction externe Java
> 	NO SQL               -- La fonction n'exécutera aucune requête SQL
> 	EXTERNAL NAME 'MailHelper.sendLowStockMessage';
0 rows inserted/updated/deleted

Invocation manuelle de la fonction SQL

Une fois ces opérations effectuées, il devient possible d'invoquer la méthode statique Java MailHelper.sendLowStockMessage via la fonction SQL SEND_LOW_STOCK_MESSAGE:

ij> VALUES SEND_LOW_STOCK_MESSAGE('SPAM0000', 'Spam', 2);
1          
-----------
1          

1 row selected

Vous le voyez, le résultat de la fonction n'a rien de très excitant – mais l'important est qu'elle a pour effet de bord d'envoyer un mail. Comme on peut le constater sur la copie d'écran ci-dessous.

Le message a bien été envoyé, participant par là même au remplissage de la boite aux lettres du pauvre responsable de l'approvisionnment...

Invocation sur un événement

Nous allons maintenant automatiser l'envoi des mails. Pour cela nous allons utiliser un TRIGGER (Dans une base de données, un TRIGGER est une action déclenchée lors de la modification d'une table.) chargé d'appeler notre fonction automatiquement lorsque le nombre d'éléments en stock franchit le seuil d'alerte:

ij> CREATE TRIGGER LowStockAlert
>    AFTER UPDATE                    -- Le TRIGGER est déclenché après un UPDATE...
>    ON Produits                     -- ... sur la table Produits
>    REFERENCING NEW_TABLE AS NEW    -- Dans la requête les nouvelles données seront connues sous le nom NEW
>    FOR EACH STATEMENT              -- Le TRIGGER se déclenche une fois par requête
>    SELECT SEND_LOW_STOCK_MESSAGE(ref, designation, stock) 
>        FROM NEW 
>        WHERE stock < minStock

La syntaxe de la commande CREATE TRIGGER est relativement complexe. Mais en quelques mots, celle-ci crée un TRIGGER chargé d'effectuer la requête SELECT indiqué après chaque mise à jour de la table Produits.

Pour chaque ligne modifiée satisfaisant la clause WHERE, la fonction SQL SEND_LOW_STOCK_MESSAGE est évaluée, ce qui déclenche l'appel à la méthode Java MailHelper.sendLowStockMessage. C'est ainsi qu'un mail est envoyé pour chaque produit dont le stock est insuffisant.

Comprenez que le TRIGGER est déclenché après chaque mise à jour. Par ailleurs, la requête SQL associée (SELECT ...) s'applique à toutes les lignes modifiées. Or, nous ne souhaitons envoyer un mail que pour certaines lignes de la table. Il est donc nécessaire d'utiliser une clause WHERE pour ne sélectionner que les lignes modifiées qui correspondent à nos critères (en l'occurrence, celles donc le stock actuel est inférieur au stock minimum souhaité).
Désormais, chaque mise à jour de la table via une requête UPDATE déclenchera le trigger. Et si une ou plusieurs des lignes modifiées correspondent à nos critères, un mail sera envoyé. Pour nous en assurer, simulons la vente de quelques articles (une carte mère – MBP4XX33, une carte vidéo – VBX70044 et un disque dur de 8Go – DD08XX22):

ij> -- Etat du stock avant la vente
ij> SELECT * FROM Produits;
REF     |DESIGNATION                   |STOCK      |MINSTOCK   
---------------------------------------------------------------
DD01XX11|Disque Dur 1Go                |14         |10         
DD08XX22|Disque Dur 8Go                |7          |10         
MBP4XX33|Carte Mère P4 1.5GHz          |10         |10         
VBX70044|Carte Vidéo RADONDON X700     |12         |10         

4 rows selected

ij> -- Vente de plusieurs articles
ij> UPDATE Produits              
> 	SET stock = stock-1
> 	WHERE ref IN (VALUES 'MBP4XX33', 'VBX70044', 'DD08XX22');
3 rows inserted/updated/deleted

ij> -- Etat du stock après la vente
ij> SELECT * FROM Produits;
REF     |DESIGNATION                   |STOCK      |MINSTOCK   
---------------------------------------------------------------
DD01XX11|Disque Dur 1Go                |14         |10         
DD08XX22|Disque Dur 8Go                |6          |10         
MBP4XX33|Carte Mère P4 1.5GHz          |9          |10         
VBX70044|Carte Vidéo RADONDON X700     |11         |10         

4 rows selected

Du point de vue du client, rien de spécial ne semble s'être produit. Pourtant, vous pourrez constater sur la copie d'écran ci-dessous qu'un mail a été envoyé par le serveur pour chacun des deux articles dont le stock est insuffisant.

Le serveur a bel et bien effectué la requête associée au TRIGGER en réponse à l'événement UPDATE.

Gestionnaire de sécurité

Au tout début de cet article, nous avons lancé le serveur Derby avec l'option -noSecurityManager. Celle-ci désactive le gestionnaire de sécurité Java2 (Java2 security manager). Ce gestionnaire fait partie intégrante de l'architecture de sécurité de Java. Son rôle étant de s'assurer qu'un programme ne fait que ce qu'il est autorisé à faire.

Par défaut, la politique de sécurité du serveur Derby n'autorise que le strict nécessaire à son fonctionnement. En particulier, ouvrir une socket pour se connecter à un serveur SMTP (Simple Mail Transfert Protocol – Un protocole utilisé pour envoyer des courriers électroniques.) ne fait pas partie des permissions octroyées par défaut à Derby. Vous pouvez le constater en lançant le serveur avec le gestionnaire de sécurité, puis en tentant une mise à jour de la table des produits en stock: au moment d'envoyer le mail, vous obtiendrez un message accès refusé:

sh$ ${DERBY_HOME}/bin/startNetworkServer
2009-08-17 13:20:58.964 GMT : Security manager installed using the Basic server security policy.
2009-08-17 13:20:59.400 GMT : Apache Derby Network Server - 10.5.1.1 - (764942) started and ready to accept connections on port 1527
sh$ ${DERBY_HOME}/bin/ij
ij version 10.5
ij> CONNECT 'jdbc:derby://localhost:1527/GestionStock';
ij> UPDATE Produits SET stock = 8 WHERE ref = 'VBX70044';
ERROR 38000: The exception 'java.security.AccessControlException: access denied
(java.net.SocketPermission 127.0.0.1:25 connect,resolve)' was thrown while evaluating an expression.
ERROR XJ001: Java exception: 'access denied (java.net.SocketPermission 127.0.0.1:25 connect,resolve): java.security.AccessControlException'.

Ceci dit, ça n'est certainement pas une bonne idée de purement et simplement désactiver le gestionnaire de sécurité, comme nous l'avons fait jusqu'à présent! Nous allons donc mettre en place une politique de sécurité personnalisée pour autoriser l'envoi de mail.

Politique de sécurité personnalisée

Derby est livré avec un fichier modèle qui peut servir de base à une politique de sécurité personnalisée:

sh$ cd /path/to/stock
sh$ cp ${DERBY_HOME}/demo/templates/server.policy stock.policy

Reste à ajouter au début de notre fichier de politique personnalisée la règle qui autorise la connexion à un serveur SMTP:

// Autorise la connexion à un serveur SMTP
grant 
{
    permission java.net.SocketPermission    "*:25",  "connect";
};

Piège:

Si vous connaissez déjà le gestionnaire de sécurité Java, vous avez remarqué que cette règle autorise les classes issues de n'importe quel JAR de l'application à se connecter à un serveur SMTP. Et effectivement, c'est loin d'être la panacée: en effet, un des principes de base de la sécurité est de ne donner que le minimum d'autorisations nécessaires au fonctionnement d'un système.

Malheureusement, il n'est pas possible pour l'instant avec Derby 10.5 de spécifier des permissions individuelles pour les archives JARs stockées dans la base de données. Cette limitation est enregistrée dans le système de suivi de la Fondation Apache sous le numéro DERBY-4354. Si vous pensez aussi que c'est un problème sérieux, n'hésitez pas à le signaler en votant pour...

Enfin, en examinant plus avant le fichier de politique de sécurité, vous constaterez que les règles sont données relativement à la propriété derby.install.url:

sh$ grep "codeBase" stock.policy
grant codeBase "${derby.install.url}derby.jar"
grant codeBase "${derby.install.url}derbynet.jar"

Bien sûr, vous pouvez éditer manuellement le fichier pour désigner le chemin d'installation de Derby sur votre système. Mais pour ma part, je préfère définir cette propriété lors de l'invocation de Derby. Enfin, il faudra aussi demander à Derby de charger notre politique de sécurité plutôt que celle par défaut. Tout ceci peut se faire en modifiant la variable d'environnement DERBY_OPTS. Ce qui donne dans le bash:

Options pour utiliser la politique de sécurité personnalisée
sh$ export DERBY_OPTS="${DERBY_OPTS} \
                          -Djava.security.manager \
                          -Djava.security.policy=stock.policy"

Options pour définir le répertoire d'installation de Derby
sh$ export DERBY_OPTS="${DERBY_OPTS} \
                          -Dderby.install.url=file://${DERBY_HOME}/lib/"

Et maintenant, on lance le serveur
sh$ /usr/local/lib/db-derby/bin/startNetworkServer
2009-08-17 14:11:40.226 GMT : Apache Derby Network Server - 10.5.1.1 - (764942) started and ready to accept connections on port 1527

Voilà: avec cette configuration, Derby peut envoyer un mail – tout en fonctionnant sous la surveillance du gestionnaire de sécurité Java.

Références