edito cinnk magazine fevrier 2016

Février

C'est l'hiver, Cinnk magazine a revêtu son plus beau pull pour vous réchauffer avec une douillette sélection d'articles.

Du SQL pour pimenter Joomla! - Partie 2

Lors de mon précédent article concernant mySQL dans un environnement Joomla, je m'étais borné à camper les outils disponibles et mettre en œuvre, de manière détaillée, une méthodologie permettant d'écrire, tester et sauvegarder les requêtes dont nous avions besoin pour enrichir notre site Joomla! sans nous compliquer la vie.

Pour autant les requêtes présentées étaient d’un niveau très simple. Je n'avais abordé que l'ordre SQL le plus accessible et le moins dangereux : SELECT et uniquement en m'appuyant sur des colonnes de sélections contenant des informations simples et atomiques. Ce qui nous a permis de faire quelques requêtes élémentaires comme par exemple lister ou comptabiliser les articles par auteur, ou par date ….

Du SQL pour pimenter Joomla!

Christopher Michel - Flickr

Il ne nous faut pas pour autant perdre de vue que Joomla! stocke 90% de l’information concernant les pages web de notre site dans cette même base de données mySQL. Base mySQL, dans laquelle il enregistre également ses propres meta-données. Il serait bien dommage de se contenter d’accéder à quelques tables et de ne pas en profiter pour essayer de manipuler les articles de notre site, ainsi que leur contenu. Loin de moi l’idée de vous faire écrire ces articles via des requêtes SQL (quoique ?), il est beaucoup plus simple de passer par un éditeur web WYSIWYG efficace, mais il est des cas où des traitements de masse peuvent s’appliquer sur le contenu des articles et où l'usage de quelques requêtes SQL bien ciblées apportent bien être et confort supplémentaire.

A.    Rechercher dans les articles

Lors d’un précédent article : Où sont mes données ? j'avais présenté la manière dont joomla! enregistre les données nécessaires à la présentation des articles affichés par notre site, nous avions pu voir que le contenu HTML complet des articles étaient enregistrées dans deux des colonnes de la table ##_content : introtext et fulltext. Ces deux colonnes sont définies comme de type BLOB (mySQL les qualifie de mediumtext)  ce qui signifie qu'on peut y stocker jusqu'à 16Mo de données non structurées ! Difficile d'effectuer comme dans l'article précédent des requêtes s'appuyant sur l'égalité ou la comparaison du contenu des colonnes !

Par contre, compte tenu du contexte particulier (tout ou partie du contenu d'un article de notre site), il il peut être intéressant d'y rechercher :

  • un nom de marque
  • tout ou partie d'une url interne ou externe
  • le nom ou le chemin menant à une image affichée dans l'article
  • ou tout simplement une séquence de caractères ou de mots quelconques

mySQL avec plus ou moins de variations envers le langage SQL standard met à notre disposition trois grandes commandes de recherches dans les colonnes de type TEXT :

  • LIKE
  • REGEXP
  • MATCH

 Il s'agit bien entendu pour ces trois commandes de limiter le nombre lignes retournées par la requête SELECT en configurant la clause WHERE.

watchful banner cinnk 770x100

commande LIKE

C'est la plus simple, mais aussi la moins performante des trois. Sur des bases de données de taille normale, elle donne toutefois de très bons résultats, tout en consommant pas mal de ressources sur le serveur de base de données. Deux types de wildcards permettent de spécifier des clauses WHERE déjà très puissantes :

  • % qui remplace de zéro à n occurrences de n'importe quels caractères
  • _ qui remplace un et un seul caractère quel qu'il soit

Nous pouvons ainsi rechercher les articles contenant le mot Joomla! n'importe où dans leur contenu :

SELECT id, title, introtext, `fulltext`, date(created) as 'Créé le' FROM `br8l3_content` WHERE introtext LIKE '%joomla!%'

 Nota : méfiez vous dans ce genre de requêtes de deux petites choses : la colonne fulltext de la table ##_content est aussi un mot réservé pour mySQL et il faut l'encadrer du caractère spécial de délimitation des champs (par défaut l'apostrophe inversé : ` ), d'autre part le contenu des articles peut être réparti dans deux colonnes et pour être tout à fait précis il faut bien spécifier notre requête :

SELECT id, title, introtext, `fulltext`, date(created) as 'Créé le' FROM `br8l3_content` WHERE introtext LIKE '%joomla!%' OR `fulltext` LIKE '%joomla!%'

 Ce qui retourne un résultat plus cohérent avec une installation Joomla! neuve avec ses données de présentation habituelles :

commande LIKE

commande REGEXP

Beaucoup plus puissante, mais également nécessitant une (assez) bonne connaissance de la manière dont mySQL traite les expressions régulières, la commande REGEXP permet de faire absolument n'importe quoi en terme de recherche dans votre table ##_content ou dans n'importe quelle autre table comportant une ou des colonnes de type TEXT. L'excellent DBReplacer de NoNumber en fait un usage de tout premier ordre !

Rechercher par exemple dans vos articles un bout de code contenant une référence vers une image référencée à l'extérieur de votre site (pas beau ça !) :

SELECT id, title, introtext, date(created) as 'Créé le' FROM `br8l3_content` WHERE where introtext regexp ']*src="http://"'

 ou encore toutes les images de type .png ou .gif (mais pas les autres) :

SELECT id, title, introtext, date(created) as 'Créé le' FROM `br8l3_content` WHERE introtext regexp ']*src="[^>]*.(gif|png)"'

REGEXP est extraordinairement puissant et laisse une large place à l'imagination. On peut juste regretter que la norme POSIX n'accepte pas la présence du ? en tant que modificateur des quantificateurs * ou + ce qui amène à une syntaxe quelque peu (encore plus) tordue.

commande MATCH

C'est la plus puissante des trois, Son usage est essentiellement d'effectuer de la recherche en texte intégral (entendez par là que vous pourrez effectuer une recherche de type Google) sur une ou plusieurs colonnes d'une table. La contrepartie est qu'il faut définir le ou les index qui vont bien sur cette ou ces colonnes. Pas très facile dans une environnement mutualisé.

SELECT id, title MATCH (introtext, `fulltext`) AGAINST ('dernière version joomla!') ORDER BY DATE(modified)

B.    Rechercher partout ailleurs.

Bien évidemment il n'y a pas que le contenu textuel des articles qui peut être source d'inspiration pour rechercher et traiter de l'information. Je parle en particulier des articles et de la table ##_content, puisqu'elle commune à tous nos sites et donc à toutes nos bases mySQL. Mais il est bien évident que selon votre site, vos besoins et votre imagination, vous pourriez (par exemple) ...

 Si vous utilisez Komento ...

... souhaitez rechercher tous les commentaires commençant par 'Merci' (ma mégalomanie qui reprend le dessus !) et afficher le commentaire, le nom de l'auteur, son adresse email et le titre de l'article supportant le commentaire. Pour ça, il vous suffira d'exécuter la requête suivante (Vous noterez l'absence de wildcard en début de texte recherché, mais sa présence en fin compte tenu de la prescription de départ) :

SELECT c1.comment, c1.name, c1.email, a1.title 
    FROM `gucl0_komento_comments` c1 
    INNER JOIN gucl0_content a1 ON c1.cid = a1.id 
    WHERE comment LIKE 'Merci%'

sqlforjoomla 202

Si vous utilisez Kunena

... rechercher dans les commentaires de votre site et remonter jusqu'au articles associés. Kunena est juste un petit peu plus subtil, du fait de l'imbrication des tables. Les sujets sont dans la table #_kunena_messages et les contenus dans la table #_kunena_messages_text. Il faut donc bien cibler ce que vous cherchez et selon ce que vous souhaitez afficher, bien suivre les index. En l'occurrence, nous allons essayer de rechercher dans le texte et retrouver les posts qui contiennent ce texte pour en afficher le titre et l'auteur et, pour compliquer un peu la chose : la date de création du post. Le texte recherché doit contenir à la fois les termes 'minus' et 'photo' (private joke !). Ces termes doivent être recherchés dans la table ##_kunena_messages_text qui va nous servir à sélectionner nos données références, le reste de l'information étant extraite de la table ##_kunena_messages.

Un examen des tables utilisées par Kunena suggère que la colonne mesid de la table ##_kunena_messages_text est un index de retour pointant sur la table ##_kunena_messages. A partir de là, les choses sont relativement simples si l'on sait décoder le format de date utilisé par Kunena dans cette table. mySQL propose le type TIMESTAMP pour gérer de manière précise l'horodatage, la function FROM_UNIXTIME() permet de récupérer un format DATETIME depuis un format TIMESTAMP, le reste est plutôt simple :

SELECT k1.mesid, k1.message,k2.subject, k2.name, DATE_FORMAT(FROM_UNIXTIME(k2.time), '%d.%m.%Y') as 'Créé le' 
FROM `gucl0_kunena_messages_text` k1
INNER JOIN gucl0_kunena_messages k2 ON k1.mesid = k2.id
  WHERE k1.message LIKE '%minus%photo%'

 Ce qui nous donne le résultat  attendu :

sqlforjoomla 203

Résultat attendu, mais erroné ! Ou en tout cas pas tout à fait correct, pour s'en convaincre il suffit de remplacer dans la clause LIKE '%minus%photo%' par '%photo%minus%' qui retourne d'autres résultats. En l'occurrence nous sommes tombés dans un des vieux pièges de ce genre de requête ou l'ordre de la recherche a une influence sur le résultat. Si c'est souhaité : tant mieux, sinon ....

....sinon il est peut être temps de faire appel à notre vieil ami les expressions régulières qui nous permet d'obtenir en une seule passe, le résultat désiré :

SELECT k1.mesid, k1.message,k2.subject, k2.name, DATE_FORMAT(FROM_UNIXTIME(k2.time), '%d.%m.%Y') as 'Créé le' 
FROM `gucl0_kunena_messages_text` k1
INNER JOIN gucl0_kunena_messages k2 ON k1.mesid = k2.id
  WHERE k1.message REGEXP '([^>]*minus[^>]*photo)|([^>]*photo[^>]*minus)'

 Ce qui nous donne :

sqlforjoomla 204

Ce qui donne un résultat à défaut de bon : plus copieux et à vérifier (quand même) en détail.

C.    un nouvel outil

Afin de préparer les prochains articles, nous allons sauvegarder la requête utilisée précédemment sur une installation Joomla! neuve et étendre nos outils disponibles à l'aide d'une petite manoeuvre :

SELECT id, title, introtext, `fulltext`, date(created) as 'Créé le' 
FROM `br8l3_content`
WHERE introtext LIKE '%joomla!%' OR `fulltext` LIKE '%joomla!%'

 sqlforjoomla 205

Lorsque le résultat de votre requête vous convient honorablement, plutôt que de sauvegarder le texte de votre requête comme nous l'avons vus lors de l'article précédent sous forme de texte, il est possible de créer une vue SQL de cette requête, en cliquant sur le lien disponible en dessous du résultat de la requête.

Quelle utilité, me direz-vous ? Lorsque vous saissisez dans la fenêtre ad-hoc, une requête SQL pour l'exécuter sur votre base de données, tout un mécanisme assez lourd se met en place :

  • contrôle de la syntaxe
  • calcul de la meilleure méthode d'accès aux données possible
  • calcul des index et optimisation du plan de chargement
  • pour finir : exécution de la requête

En stockant sous forme de vue SQL votre requête, vous stockez également les trois premières étapes de ce travail, étapes qui n'auront plus à être refait (je schématise volontairement), vous avez en quelque sorte : compilé votre requête.

phpMyAdmin vous pose alors quelques questions subsidiaires quand à cette requête que vous souhaitez stoker dans une vue SQL :

 sqlforjoomla 206

Bien que vous pouvez vous essayez aux autres options, la seule obligation incontournable est de donner un nom à notre vue, nom que vous allez désormais trouver sous la liste des tables de la base de données et que vous allez pouvoir exécuter à loisir, éditer et même supprimer facilement.

 sqlforjoomla 207

Vous pouvez désormais ré exécuter votre requête simplement en cliquant sur son nom dans l'arborescence, ou, via l'onglet SQL classique en tapant SELECT * FROM nomdelavue.

Conclusion toute provisoire

Avec un peu d'habitude, vous pourrez assez facilement effectuer des recherches et des sélections non seulement sur les données du noyau de Joomla! mais également sur la plupart des extensions disponibles sur le marché présentes dans votre site. N'hésitez pas à manipuler vos données et à les torturer dans tous les sens, l'ordre SQL SELECT ne peut absolument faire aucun dégât sur votre base de données, par contre, c'est la base indispensable pour progresser et aller plus loin dans les solutions à vos problèmes. Maintenant que, à défaut d'en maîtriser toutes les subtilités, vous avez pu en aborder les principes généraux, il nous reste à transposer ce que nous avons vu pour sélectionner des données à supprimer ou à modifier, voire pour créer de nouveaux enregistrements dans de nouvelles tables.

 Donc : ... à très bientôt pour la suite !

Notez cet article:
3
Le sondage du mois
Cinnk magazine Abonnez-vous!

Commentaires 2

 
marie-louise le lundi 22 février 2016 13:42

Merci c'est génial cette recherche ! Juste ce que j'avais besoin d'utiliser en plus.

Merci c'est génial cette recherche ! Juste ce que j'avais besoin d'utiliser en plus.
Simon le lundi 22 février 2016 14:30

Bonjour,

merci pour ton commentaire ;)

Bonjour, merci pour ton commentaire ;)
Déjà inscrit ? Connectez-vous ici
Guest
samedi 18 novembre 2017
Si vous souhaitez vous inscrire, veuillez saisir un nom d'utilisateur, mot de passe et nom.

Image Captcha

Sur ce site, nous utilisons des cookies.