##### Requêtes SQL et AJAX ##### Pour apprivoiser le système de requêtes SQL, nous allons insérer une dernière fonctionnalité sur notre Hello World. .. note:: Cette fonctionnalité n'est pas utile en elle-même, mais elle permet de mieux connaître la façon d'exécuter des requêtes ``SQL`` dans une base de données et vous prépare ainsi pour la suite de ce tutoriel. Lorsque nous cliquerons sur le bouton de la page d'accueil, l'application enregistrera la date de clic dans une base de données. La page d'accueil affichera toutes les dates de clic et s'actualisera en temps réel. phpMyAdmin ----- Pour commencer, démarrez votre serveur ``WAMP``, ``MAMP`` ou ``XXAMP`` (selon votre système d'exploitation) et allez sur la page d'accueil du serveur ``Apache``. Pour ce faire, il cliquez sur le bouton prévu à cet effet depuis l'interface logicielle : .. image:: structure_base/8.png :height: 10000 px :width: 200 px :scale: 60 % .. warning:: Il est possible que votre logiciel ne veuille pas se lancer si le port est déjà utilisé par votre serveur ``node``. Pour régler ce problème, il suffit d'aller dans les préférences et de changer le port du serveur ``Apache`` : .. image:: structure_base/9.png :height: 10000 px :width: 800 px :scale: 60 % Sur la page principale, vous obtenez les informations nécessaires pour connecter la base de données au script ``nodeJS`` : .. image:: structure_base/10.png :height: 10000 px :width: 800 px :scale: 60 % .. note:: Les informations sont les mêmes sur ``Windows`` et sur ``OSX``, il y a juste le mot de passe qui change. Sur ``OSX``, le mot de passe par défaut est **root** et sur ``Windows``, il n'y a pas de mot de passe. Vous pouvez accéder à ``phpMyAdmin`` en entrant l'adresse suivante : ``http://localhost:votre_port/phpMyAdmin``. L'interface de cette application apparaît alors. Commençons par créer une base de données toute simple du nom de ``hello_world`` pour enregister les dates de clics. Insérons-y une table du nom de "clics" avec deux collones, ``id`` et ``date``, sans oublier de cocher la case ``auto_increment`` pour le champ ``id``. .. image:: structure_base/12.png :height: 10000 px :width: 600 px :scale: 80 % Code ----- Maintenant que la base de données est prête, commençons à coder ! Il nous faudra tout d'abord un nouveau fichier du nom de ``sql.js`` qui ira dans le dossier ``serveur``. .. only:: html .. code-block:: tree :emphasize-lines: 7 . ├── app.js ├── node_modules ├── serveur │   ├── routes.js │   ├── socket.js │   └── sql.js ├── static └── views .. only:: latex .. code-block:: sh :emphasize-lines: 7 . |-- app.js |-- node_modules |-- serveur | |-- routes.js | |-- socket.js | `-- sql.js |-- static `-- views Pour éviter de devoir changer à plusieurs endroits les données d'accès à la base de données, nous créons une fonction qui les retourne : .. captionup:: ./serveur/sql.js .. code-block:: javascript exports.pool = function(mysql) { // informations pour se connecter à la base de données var pool = mysql.createPool({ host : 'localhost', user : 'root', // mettre '' comme mot de passe sur Windows password : 'root', database : 'hello_world', charset : 'UTF8_UNICODE_CI', multipleStatements: true }); // à l'appel de cette fonction, ces informations sont retournées return pool; } Puis, une fonction pour exécuter des requêtes ``SQL`` ainsi qu'une autre pour gérer les erreurs : .. captionup:: ./serveur/sql.js .. code-block:: javascript exports.requete = function(mysql, sql, requete_sql, callback) { // nous récupérons les données de la fonction pool() écrite plus haut // nous utilisons getConnection() pour avoir une connection pool sql.pool(mysql).getConnection(function(err, connection) { /* lorsque nous avons les informations sur cette connection, nous exécutons la requête */ connection.query(requete_sql, function(err, results) { // nous affichons les erreurs, s'il y en a, à l'aide d'une autre fonction sql.query_error(err); /* nous testons l'existence du paramètre callback, qui est facultatif si nous ne voulons pas obtenir le résultat de la requête (par exemple pour les UPDATE) */ if(typeof(callback) !== 'undefined') { // s'il existe, nous renvoyons les résultats dans une fonction de callback callback(results); } // nous libérons la connection pour éviter de surcharger le pool connection.destroy(); }); }); } // cette fonction permet d'afficher une erreur dans la requête SQL exports.query_error = function(erreur) { if (erreur) { console.log('query error : ' + erreur.stack); } } .. note:: Dans la fonction ``exports.requete``, en plus des arguments ``requete_sql`` et ``callback`` qui contiennent respectivement la requête ``SQL`` et la fonction appelée pour retourner les résultats d'une requête, il existe deux autres arguments qui ne sont pas forcément évidents. ``mysql`` et ``sql`` sont des dépendances nécessaires pour que les instructions effectuées dans la fonction ``exports.requete`` s'exécutent correctement. ``sql`` contient les fonctions du fichier ``./serveur/sql.js`` (``sql.pool`` en l'occurence) et ``mysql`` le module ``node-mysql`` qui permet d'exécuter les requêtes ``SQL``. Ensuite, nous entrons dans le fichier ``socket.js`` et y insérons une requête ``SQL`` qui s'exécutera lorsque le client cliquera sur le bouton. Toutefois, le fichier ``socket.js`` doit charger le module ``node-mysql`` et le fichier ``sql.js``. Nous ajoutons donc deux paramètres dans sa fonction principale : .. captionup:: ./serveur/socket.js .. code-block:: javascript exports.f = function(io, mysql, sql) { // ... } .. note:: Les arguments que nous joignons à cette fonction sont obligatoires, car la fonction ``sql.requete_sql`` en a besoin, comme nous l'avons vu précédemment. Le fichier ``app.js`` doit également contenir d'autres ``require()`` pour accéder à ces éléments et les envoyer dans les fichiers ``socket.js`` et ``sql.js`` : .. captionup:: ./app.js .. code-block:: javascript var http = require('http'); var path = require('path'); var express = require('express'); // récupération du module node-mysql var mysql = require('mysql'); var app = express(); var server = http.Server(app); var io = require('socket.io')(server); var routes = require('./serveur/routes.js'); var socket = require('./serveur/socket.js'); // récupération des fonctions du fichier sql.js var sql = require('./serveur/sql.js'); routes.f(app, __dirname, mysql, sql); socket.f(io, mysql, sql); server.listen(8888); .. note:: Vous observez que les paramètres ``sql`` et ``mysql`` ont été ajoutés dans l'appel des fonctions routes.f() et socket.f(). Le fichier ``sql.js`` se comporte différemment de ``routes.js`` et ``socket.js`` ; nous n'appelons pas une fonction principale directement, mais nous donnons aux fonctions principales (``socket.f`` et ``routes.f``) les éléments (ou paramètres) pour appeler les fonctions qui se trouvent dans ``sql.js``. Nous pouvons enfin taper notre requête sql : .. captionup:: ./serveur/socket.js .. code-block:: javascript // à placer juste après console.log('Le client a cliqué sur le bouton !') var requete_sql = 'INSERT INTO clics(date) VALUES(NOW())'; sql.requete(mysql, sql, requete_sql); Grâce aux fonctions créées précédemment, écrire une requête ``SQL`` et l'exécuter ne prend que peu de lignes de code. Lecture de données depuis MySQL **** Chaque fois que l'utilisateur clique sur le bouton, la date du clic est enregistrée dans la base de données. Mais comment récupérer ces dates ? Nous avons deux possibilités : ``socket.io`` et ``EJS``. avec socket.io ..... Nous ajoutons une requête ``SQL`` pour récupérer les données lorsque le client envoie un message : .. captionup:: ./serveur/socket.js .. code-block:: javascript socket.on('recuperation_dates', function() { // requête SQL var requete_sql = 'SELECT * FROM clics ORDER BY id'; sql.requete(mysql, sql, requete_sql, function(results) { // le client récupère ensuite les données socket.emit('dates', results); }); }); Ensuite, du côté client, nous formatons les données de la manière suivante : .. captionup:: ./views/index.ejs .. code-block:: html

Hello World !



.. captionup:: ./static/js/index.js .. code-block:: javascript socket.emit('recuperation_dates'); socket.on('dates', function(results) { // boucle pour parcourir l'array "results" for(var i = 0; i < results.length; i++) { byId('dates').innerHTML += results[i].date + '
'; } }); .. note:: Il est important de comprendre comment ``node-mysql`` envoie ses données. Elles se trouvent dans un tableau et chaque élément de ce tableau contient des objets portant le nom des champs de la table appelée lors de la requête ``SQL``. Un exemple avec quelques dates : .. code-block:: guess [ { id: 15, date: Tue Jan 12 2016 17:01:10 GMT+0100 (CET) }, { id: 16, date: Tue Jan 12 2016 17:08:46 GMT+0100 (CET) }, { id: 17, date: Tue Jan 12 2016 17:08:46 GMT+0100 (CET) }, { id: 18, date: Tue Jan 12 2016 17:08:47 GMT+0100 (CET) }, { id: 19, date: Tue Jan 12 2016 17:08:47 GMT+0100 (CET) } ] Cette technique fonctionne, mais elle comprend toutefois quelques inconvénients : - Le client charge la page, puis envoie une requête au serveur qui envoie une requête à la base de données, qui renvoie au serveur les informations que le serveur va enfin envoyer au client. Si le verbe "envoyer" est beaucoup utilisé dans la phrase précédente, c'est parce qu'il y a trop de transferts. Ceci aura pour effet de créer un léger délai depuis le moment où la page est chargée et le moment où le contenu s'affiche. - Une fois le contenu d'une requête ``SQL`` obtenu sur le client, ce contenu doit être formatté pour qu'il s'affiche sur la page. Ici, nous avons utilisé la propriété ``.innerHTML`` avec l'ajout d'un ``
`` pour créer un retour à la ligne à chaque nouvelle date. Mais admettons que le formatage en ``HTML`` soit beaucoup plus conséquent ; avec une classe ``Bootstrap`` et des boutons par exemple, nous devrions mettre tout ce code ``HTML`` dans une chaîne de caractères ``Javascript`` ! Le code ``HTML`` se trouverait alors dans un fichier ``Javascript``, ce qui rendrait la modification plus compliquée. .. note:: Cela ne signifie pas que nous ne pouvons pas utiliser ``socket.io`` pour transmettre des données ; cette technique doit juste servir à charger de petites informations (comme le nombre de likes d'un post ou le contenu d'un message qui vient d'être modifié) qui ne nécessitent pas beaucoup de formatage. avec EJS ..... Mais alors, comment faire pour transférer des données importantes au client ? En utilisant EJS (et AJAX si besoin est). Pour cela, nous créons une nouvelle fonction dans ``sql.js``. Celle-ci permet de récupérer les dates et pour les envoyer à l'aide d'une fonction de ``callback`` : .. captionup:: ./serveur/sql.js .. code-block:: javascript exports.date = function(mysql, sql, callback) { var requete_sql = 'SELECT * FROM clics ORDER BY id'; sql.requete(mysql, sql, requete_sql, function(results) { callback(results); }); } Puis, nous modifions la fonction ``app.get`` dans ``routes.js`` afin que la date soit retournée au chargement de la page. Pour ce faire, nous ajoutons un objet contenant les résultats de la requête ``SQL`` à la fonction ``res.render()`` : .. captionup:: ./serveur/routes.js .. code-block:: javascript // affichage de la page index si on entre localhost/ app.get('/', function(req, res) { sql.date(mysql, sql, function(results) { res.render('index.ejs', { results : results }); }); }); .. important:: Comme nous avons besoin de la variable ``mysql`` dans le fichier ``routes.js``, nous devons modifier les paramètres de la fonction principale de ce fichier : .. captionup:: ./serveur/routes.js .. code-block:: javascript exports.f = function(app, dossier, mysql, sql) { // ... } Les données étant dès lors envoyées avec ``EJS``, il ne reste plus qu'à les traiter dans le fichier ``index.ejs`` : .. captionup:: ./views/index.ejs .. code-block:: guess
<% for (var i = 0; i < results.length; i++) { %>
<%= results[i].date %> <% } %>
.. warning:: Il faut bien différencier les ``<%`` des ``<%=`` lorsque vous utilisez ``EJS``. Les ``<%=`` permettent d'écrire le contenu d'une variable ``EJS`` sur la page, tandis que les ``<%`` décrivent simplement une instruction ``EJS`` (comme une fonction, une boucle...). Dans les deux cas, la fin d'une instruction ``EJS`` se termine par ``%>``. Si nous testons le code, nous obtenons, après quelques clics sur le bouton et un rechargement de page, le résultat suivant : .. image:: structure_base/14.png :height: 10000 px :width: 700 px :scale: 60 % Mais il y a un inconvénient : il est nécessaire de recharger la page pour que les dates de clic s'actualisent. Et nous aimerions une application "en temps réel", donc sans de telles contraintes. ``Socket.io`` et ``AJAX`` sont donc notre solution. Requêtes AJAX ----- Tout d'abord, un petit rappel sur cette technologie : ``AJAX`` est similaire à ``socket.io`` ; il fonctionne de manière asynchrome et fait aussi des requêtes. Ce système permet, entre autres, d'enregister le contenu d'une page web dans une variable. Cela fonctionne comme un chargement "interne", sans avoir besoin de raffraîchir complètement la page web du client. Concrètement, dans cet exemple, nous utiliserons ``AJAX`` pour aller chercher une page créée avec ``EJS`` et l'insérer dans la page web actuelle. La page créée avec ``EJS`` contiendra la dernière date entrée dans la base de données. La requête ``AJAX`` s'exécutera lorsque ``socket.io`` dira au client "quelqu'un a cliqué sur le bouton". .. note:: Comme le résultat de la requête ``SQL`` n'est pas long (c'est juste une date), nous pourrions nous contenter d'utiliser ``socket.io`` pour charger la dernière date. Mais nous le ferons avec ``socket.io`` + ``EJS`` + ``AJAX`` pour être ensuite capable reproduire cette méthode pour des requêtes plus conséquentes. D'abord, nous créons une nouvelle page web (``derniere_date.ejs``) dans le dossier ``views``. Dans celle-ci, nous insérons le code suivant : .. captionup:: ./views/derniere_date.ejs .. code-block:: guess <% if (typeof(results[0].date) !== 'undefined') { %>
<%= results[0].date %> <% } %> .. note:: Le terme "page web" n'est pas tout-à-fait exact pour désigner ``derniere_date``. Il s'agit plutôt d'une partie d'une page web, puisque son code ne contient pas de balise ````. Ensuite, nous modifions la fonction qui récupère les dates (qui se trouve dans ``sql.js``) pour qu'elle fasse une requête avec la dernière date lorsqu'on le lui demande : .. captionup:: .serveur/sql.js .. code-block:: javascript exports.date = function(mysql, sql, callback, derniere_date) { // teste si l'attribut derniere_date est true ou false if (!derniere_date) { var requete_sql = 'SELECT * FROM clics ORDER BY id'; } else { // nous demandons de choisir le premier élément dans // l'ordre décroissant, c'est-à-dire le dernier élément var requete_sql = 'SELECT * FROM clics ORDER BY id DESC LIMIT 0,1' } sql.requete(mysql, sql, requete_sql, function(results) { callback(results); }); } Puis, nous ajoutons une route pour charger le fichier ``derniere_date.ejs`` en lui envoyant le retour de la requête ``SQL`` : .. captionup:: ./serveur/routes.js .. code-block:: javascript app.get('/derniere_date', function(req, res) { sql.date(mysql, sql, function(results) { res.render('derniere_date.ejs', { results : results }); }, true); }); .. warning:: Il faut insérer cet ``app.get()`` **avant** celui qui s'occupe de charger la page d'erreur. Sinon, ``derniere_date.ejs`` ne se chargera jamais. A présent, en allant sur ``http://localhost:8888/derniere_date``, vous voyez apparaître la dernière date de clic. Mais pour l'afficher dans la page, nous devons encore écrire un peu de code. Il faut créer une fonction dans ``index.js`` qui s'occupera de récupérer le contenu de la page ``derniere_date.ejs`` pour l'afficher dans la page ``index.ejs`` lorsque ``socket.io`` le lui demandera. .. captionup:: ./static/js/index.js .. code-block:: javascript socket.on('charger_derniere_date', function() { var xhr = new XMLHttpRequest(); xhr.open('GET', 'derniere_date'); xhr.addEventListener('readystatechange', function() { if (xhr.readyState === 4 && xhr.status === 200) { // on ajoute le contenu de derniere_date.ejs aux dates existantes byId('dates').innerHTML += xhr.responseText; // rappel : byId() = document.getElementById() } }); xhr.send(null); }); .. only:: html .. note:: Si vous ne comprenez pas complètement ce code, c'est tout à fait normal. Il utilise ``XMLHttpRequest``, qui est l'implémentation d'``AJAX`` en dans le moteur ``javascript`` du navigateur web. Nous n'utiliserons pas des fonctionnalités plus poussées d'``AJAX`` dans cette application, mais si vous souhaitez mieux connaître le fonctionnement de ``XMLHttpRequest``, vous pouvez consulter `cette page`_ qui explique, entre autres, ce bout de code. .. only:: latex .. note:: Si vous ne comprenez pas complètement ce code, c'est tout à fait normal. Il utilise ``XMLHttpRequest``, qui est l'implémentation d'``AJAX`` en dans le moteur ``javascript`` du navigateur web. Nous n'utiliserons pas des fonctionnalités plus poussées d'``AJAX`` dans cette application, mais si vous souhaitez mieux connaître le fonctionnement de ``XMLHttpRequest``, vous pouvez consulter une page - trouvable avec ce lien : http://j.mp/1pnuwo6 - qui explique, entre autre, ce bout de code. Il ne reste plus qu'à écrire un ``socket.emit()`` dans ``socket.js`` : .. captionup:: ./serveur/socket.js .. code-block:: javascript socket.on('bouton_client', function() { console.log('Le client a cliqué sur le bouton !') var requete_sql = 'INSERT INTO clics(date) VALUES(NOW())'; sql.requete(mysql, sql, requete_sql); // avec la fonction socket.emit le client actuel charge la nouvelle date socket.emit('charger_derniere_date'); /* avec la fonction socket.broadcast.emit, la nouvelle date est également envoyée aux autres clients */ socket.broadcast.emit('charger_derniere_date'); }); Et voilà ! Désormais, lorsque vous cliquez sur le bouton, la nouvelle date de clic apparaît directement à la suite des autres. .. only:: html .. tip:: Vous pouvez télécharger le programme actuel en cliquant sur les liens ci-dessous : - `Programme utilisant socket.io`_ - `Programme utilisant AJAX`_ .. only:: latex .. tip:: Vous pouvez télécharger le programme actuel en utilisant les liens suivants : - Programme utilisant ``socket.io`` : http://j.mp/1RkG48B - Programme utilisant ``AJAX`` : http://j.mp/1S3RZV7 .. liens .. _Programme utilisant socket.io: http://tutoriel-forum.s3-website-eu-west-1.amazonaws.com/hw_sql-socketio.zip .. _Programme utilisant AJAX: http://tutoriel-forum.s3-website-eu-west-1.amazonaws.com/hw_sql-ajax.zip .. _cette page: https://openclassrooms.com/courses/dynamisez-vos-sites-web-avec-javascript/xmlhttprequest-1