Tutoriel : Requête Préparée

Avant de continuer dans les explications théoriques, précisons ce que ne sont pas les requêtes préparées. Les requêtes préparées ne sont pas des procédures stockées (Procédures stockées : en terme simple on peut comparer ça à des requêtes préparées mais globales, ainsi il est possible de préparer des procédures et de les appeler quelle que soit la session), en termes simples cela veut dire que la requête qui sera stockée ne le sera que pour la session ('thread'), ainsi lors de l'utilisation ou lors de la mise en place d'un système se servant de ce système, demandez-vous si cela correspond bien à vos besoins.

Schéma d'une requête normale :
1 : envoi de la requête par le client vers le serveur
2 : compilation de la requête
3 : plan d'exécution par le serveur
4 : exécution de la requête
5 : résultat du serveur vers le client

Schéma d'une requête préparée :
Phase 1 :
1 : envoi de la requête à préparer
2 : compilation de la requête
3 : plan d'exécution par le serveur
4 : stockage de la requête compilée en mémoire
5 : retour d'un identifiant de requête au client
Phase 2 :
1 : le client demande l'exécution de la requête avec l'identifiant
2 : exécution
3 : résultat du serveur au client

Comme nous le voyons, la requête préparée se fait avec plus d'étapes, ce qui explique qu'elle ne devient intéressante que dans le cadre d'une utilisation répétée d'une requête.

Outre cet aspect purement technique, il existe deux autres raisons qui peuvent justifier l'utilisation d'une requête préparée :
- limiter la bande passante utilisée entre le client et le serveur : dû au fait que l'échange d'informations est limité au strict minimum.
- éviter les injections Sql : cela concerne la sécurité et évite que les informations rentrées par un client (à travers un formulaire par exemple) soient interprétées.

Comme nous l'avons vu ce type de requête est limité à la session (voir thread) d'un utilisateur.

Afin de comprendre ce qui est mis en jeu, simulons une personne visitant une page écrite en Php que nous appelerons ma_page.php qui contiendra des requêtes à la base de données (les requêtes faites dedans ont peu d'importance, l'intérêt étant d'ouvrir une connexion à la base de données).

Bien sûr afin de pouvoir effectuer les requêtes contenues dans cette page, il sera nécessaire pour la page d'ouvrir une connexion (en Php cela se fait à l'aide de mysql_connect).
Lors de l'appel à cette fonction, plusieurs paramètres vont être fournis qui, s'ils sont corrects permettront à la page d'ouvrir une session. Cette session ouverte aura un numéro au niveau de MySQL. Ainsi si plusieurs personnes consultent cette même page en même temps, MySQL sera capable de les distinguer (si vous lancez MySQL en ligne de commande vous pourrez d'ailleurs remarquer qu'un id de session vous est alloué).
Cela implique donc que chaque session peut travailler en parallèle des autres ce qui permet d'éviter le chaos qu'il pourrait régner sans ce système.

Voilà pour la théorie mais pourquoi je vous explique cela me direz-vous ?
Tous simplement car il faut bien comprendre qu'une fois que cette connexion sera lancée, une session sera créée et que dans le cas de Php (avec un comportement par défaut) la connexion se terminera soit avec la rencontre de la fonction mysql_close, soit à la fin de la page.
Ainsi durant cet intervalle, si une requête ayant toujours le même aspect (dont seuls les paramètres changent) est répétée plusieurs fois alors il est intéressant d'utiliser ce système.

Cette partie va vous montrer comment procéder afin de mettre en place ce système, lors de vos tests, je vous conseille fortement de tester la requête directement en ligne de commande.

Préparation d'une procédure :

PREPARE identifiant FROM 'requete' ;

Ceci est la forme de base pour déclarer une requête préparée, ceci dit ce n'est pas tout car bien sûr l'intérêt de ce type de choses est de pouvoir déclarer des paramètres dans la requête mais rien de mieux qu'un exemple afin de vous faire comprendre :

PREPARE test FROM 'SELECT * FROM matable WHERE ID=?';

Comme vous pouvez le remarquer la requête n'a rien d'exceptionnel si ce n'est le '?' qui apparaît à la place du numéro d'id auquel on pourrait s'attendre, et c'est ce '?' qui va vous permettre de rendre vos requêtes paramétrables à souhait.
Le '?' présent dans la requête représente donc une variable.

Déclaration de la variable :

SET @mavariable=X ;

Bien sûr dans le cadre de l'exemple ci-dessus, le X vaudra un chiffre et 'mavariable' sera le nom de cette variable.

Exécution de la requête :

EXECUTE test USING @mavariable ;

On va demander l'exécution de la requête préparée ayant en identifiant du nom de 'test' et on va lui assigner la variable '@mavariable' (rappelons que le @ représente dans MySQL une variable utilisateur de session)

Comme vous le voyez l'utilisation est relativement simple cependant il vous appartient de faire attention dans le cas où vous assigneriez une requête préparée avec un nom qui correspondrait à une requête préparée déjà existante, effectivement, si cette nouvelle requête échoue, alors l'ancienne requête préparée ne sera plus disponible (MySQL effectue un DROP PREPARE identifiant ; avant de procéder à la mise en place de la nouvelle requête).

Suppression d'une requête :
Vous pouvez supprimer une requête préparée à l'aide de :

DROP PREPARE identifiant ;

ou

DEALLOCATE PREPARE identifiant ;

Connaître les utilisateurs connectés au serveur :
Une fois connecté avec une session en ligne de commande, tapez :

SHOW processlist;

Exemple :

PREPARE test FROM 'SELECT * FROM matable WHERE ID = ? AND nombre > ?'

L'appel se fera alors par :

EXECUTE test USING @mavariable , @mavariable2 ;

Ici nous allons un peu nous amuser en manipulant des sessions afin de voir exactement ce qu'il ce passe, ce qui permettra à ceux n'ayant pas tout compris de lever le voile à travers une manipulation qui se veut simple mais néanmoins complète.

Nécessaire :
- 1 serveur MySQL 4.1 ou supérieur (disponible sur le site officiel www-fr.mysql.com) avec si possible une installation par défaut.
- la base de données WORLD en .sql disponible sur le site officiel MySQL.

Bien, maintenant que vous avez votre serveur MySQL installé, il est temps de passer à la manipulation proprement dite, pour cela nous allons ouvrir une première fenêtre MySQL en ligne de commande (Démarrer->Programmes->MySQL->MySQL Server 4.1->MySQL Command Line Client.

Là, si vous avez effectué correctement la manipulation, vous devriez avoir une petite fenêtre qui est ouverte vous demandant un mot de passe (celui que vous avez mis à l'installation), une fois le mot de passe accepté, vous arrivez sur le prompt de MySQL qui se présente sous la forme : mysql>, ce prompt est juste là comme point de repère et n'implique rien, vous pouvez donc le changer à votre convenance, dans notre cas, nous allons le renommer afin de nous y retrouver parmi les différentes fenêtres qui seront ouvertes en fin de manipulation.
Tapez =>prompt fenetre1> et validez.
Comme vous pouvez le remarquer, le prompt a changé :)
Maintenant que ceci est fait, ouvrez une deuxième fenêtre de la même manière et changez-en le prompt en fenetre2>.

A ce point de la manipulation, nous avons deux fenêtres MySQL qui sont ouvertes si tout est correct.

Rendez-vous sur la première fenêtre et tapez =>SHOW processlist; et validez.
Un tableau s'affiche vous récapitulant des informations, en l'occurence, cela vous indique que vous avez deux sessions de lancées avec respectivement le numéro de session, l'utilisateur connecté, l'hôte et son port de connexion, la base de données où se trouve l'utilisateur (égale à NULL pour le moment ce qui est normal), et diverses autres choses dont le info qui vous permet de voir la session qui a effectué le SHOW processlist;.
A ce stade, nous voyons que chaque session est normalement indépendante de l'autre mais afin de nous en assurer, passons à la suite de la manipulation.

Bien maintenant dans une des deux fenêtres, entrez ceci :

CREATE DATABASE world;
USE world;

et validez dans les deux fenêtres. Ceci nous a permis de créer une base de données appelée world et de se rendre dessus.
Il est maintenant nécessaire de la remplir, pour cela nous allons avoir besoin du world.sql que vous avez normalement récupéré (voir Nécessaire), ouvrez le répertoire où il est stocké.
Maintenant, tapez => source mais sans valider puis faites glisser le fichier world.sql dans la fenêtre ou vous avez tapé ceci. Vous devriez donc maintenant être en présence d'une chose dans ce genre :
source "lecteur: epertoire\world.sql"
Bon ce n'est pas fini, virez les guillemets puis ajoutez ; à la fin. Vous êtes désormais en présence de quelque chose sous la forme :
source lecteur: epertoire\world.sql;
Validez et patientez un petit peu, les tables et les enregistrements sont en cours de création.

Nous allons vérifier que tous s'est bien passé en tapant ceci :
SHOW TABLES;
là vous aurez 3 tables si tout s'est déroulé correctement (de toute façon dans le cas contraire vous aurez 1-N bips vous signalant des erreurs, ce en quoi vous avez fait une mauvaise manipulation).

Bien passons maintenant à la partie finale qui est notre but. Tester les requêtes préparées (oui on y arrive enfin o_O).

Première étape, une requête préparée qui nous permettra de récupérer des pays.
Mettez-vous sur l'une des deux fenêtres sur laquelle vous resterez pour le moment et tapez ceci :

PREPARE pays FROM 'SELECT Name FROM Country WHERE Name LIKE ?';
SET @p='%fr%';
EXECUTE pays USING @p;

puis validez.
Comme vous le voyez ceci vous sort la liste des pays contenant fr dans leur nom.
Vous pouvez bien sùr remplacer la valeur de la variable afin de trouver autre chose et faire des tests par vous-même. Ainsi, une fois que vous aurez modifié la variable à l'aide de SET, vous n'aurez plus qu'à rappeler la requête avec EXECUTE.
Remarquez qu'un oubli du @ dans la requête EXECUTE ne provoquera qu'une erreur vous signalant une mauvaise syntaxe.

Bien maintenant passez à l'autre fenêtre et essayez de lancer une requête à l'aide :

EXECUTE pays USING @p;

Vous aurez alors l'erreur suivante :
ERROR 1243 (HY000): Unknown prepared statement handler (pays) given to EXECUTE
Ceci est dû au fait que la requête préparée demandée n'est pas trouvée.

Conclusion, comme nous l'avons vu tout au long de ce tutorial, ceci est dû au fait que si effectivement la requête existe dans la session où elle a été déclarée, dans une autre session elle ne sera pas accessible.

Cette partie est là afin de développer tous les termes qui peuvent paraître obscurs de prime abord.

Thread : on parle de thread (flux en Français) dans ce cas afin de désigner une session ouverte par un utilisateur. Cette session est régie par des règles qui dépendent des paramètres du serveur mais aussi des moteurs de stockages utilisés (sachant que ceci peut aussi être réglé au niveau du serveur).

Moteur de Stockage : il existe 5 types disponibles avec MySQL qui sont MyISAM, InnoDB, BDB, MERGE, HEAP. Ces moteurs ont des caractéristiques qui leur sont propres et qui devront être choisies en fonction des besoins de la personne qui met en place la base de données.
Les spécificités de chaque moteur seront traitées dans un futur tutoriel pour des raisons pratiques :)

Ligne de commande : on parle d'interface en ligne de commande afin de désigner les processus sans interface et qui ne proposent comme toute interactivité qu'un 'prompt'.
Par exemple une fois connecté en ligne de commande à MySQL, vous aurez le prompt : mysql>
Il est à remarquer que sous WindowsXP, vous pouvez lancer l'interface en ligne de commande à partir de : Démarrer->Exécuter->cmd et OK.

Variables spécifiques à MySQL : MySQL possède deux types de variables qui peuvent intervenir.
Les variables à portée globale, qui sont désignées par @@ et les variables à portée de sessions, qui comme le nom l'indique ne sont valables que dans l'espace de la session et qui peuvent donc varier d'une connexion à l'autre.
Exemples de variables pour changer l'heure selon le fuseau horaire :
- Changer le fuseau horaire pour toutes les sessions

SET @@time_zone='+01:00';

- Changer le fuseau horaire pour la session courante

SET @time_zone='+03:00';

Dans le cas ci-dessus, si l'utilisateur de la session qui a changé l'heure pour son fuseau souhaite savoir quel est son décalage avec le serveur, alors il saura qu'il a 2H d'avance (+03:00 - +01:00).

Finalement on s'aperçoit que ce type de requêtes peut s'avérer très utile quand il s'agit d'optimiser des requêtes qui seront appelées plusieurs fois durant la session d'un utilisateur mais dont l'usage reste restreint.