sql ~ exécuter et afficher des requêtes SQL

L'idée de cette action m'est vue lors de la création des actions pour récupérer des informations pour la documentation d'un site.
Pourquoi ne pas avoir une action qui permette d'exprimer simplement une requête SQL SELECT et de mettre en forme le résultat.

Outre la présentation de données, elle permettrait de répondre simplement à des questions que tous webmasters se posent comme "Quels sont les articles avec un titre trop court pour la SEO"

➠ sql : Requête SQL avec mise en forme et tri

Cliquer pour lire la documentation

syntaxe {up sql=nom_table | ...}
Terminologie:
row : ligne de la table
col : cellule. Colonne de la table
tag ou motclé : ##nomcol##
author LOMART version UP-1.8 license GNU/GPLv3 credit script Stupid Table de joequery tags Joomla
  • sql: nom de la table
  • select = *: listes des colonnes
  • where:
  • order:
  • group:
  • innerjoin:
  • outerjoin:
  • leftjoin:
  • rightjoin:
  • setlimit:
  • presentation = table: présentation du résultat : list,table,div ou 0
  • header: 1 ou vide pour utiliser les mots-clés, sinon titres séparés par point-virgules
  • sort: type de tri par colonne sous la forme: i,3-f-s. i:int, s:string, f:float. ,3 indique un tri secondaire sur la 3e colonne
  • sort-first: nom ou position de la colonne triée en premier
  • col: alignement et largeur des colonnes sous la forme x-D-C5-100 pour rien-droite-centre 5%-100% (voir doc)
  • template: modèle mise en page
  • no-content-html = aucun résultat:
  • main-class = up: classe(s) pour bloc
  • main-style: style inline pour bloc
  • item-class: classe(s) pour ligne
  • item-style: style inline pour ligne
  • css-head (base-css): style ajouté dans le HEAD de la page
  • dbinfos: vide= liste des tables, nom_table=liste des colonnes

    Voici un exemple obtenu avec cette action. Il s'agit de la liste des articles du site dont la longueur du titre est inférieure à 10 caractères.

    947Langage0
    1428Donation0

    Nous allons découvrir étape par étape comment obtenir ce résultat. Les impatients peuvent découvrir les shortcodes utilisés en cliquant ICI

    Attention, cette action demande un minimum de connaissance du langage SQL.
    Dans cette démo, je vais expliquer comment utiliser l'action en considérant que vous comprenez le SQL.
    Une erreur de saisie se traduira par une erreur fatale.
    Je vous recommande vivement de construire le shortcode petit à petit en testant chacune des étapes.

    La requête

    Le plus simple

    Pour expliquer le principe, récupérer la liste des langages installés sur le site.

    {up sql=languages}

    Avec ce simple shortcode, nous obtenons le résultat ci-contre.

    Il suffit d'indiquer la table comme argument principal. Le préfixe '#__' est facultatif.
    Par défaut, toutes les colonnes et lignes de la table sont affichées dans un tableau.
    Pour les contenus longs ou avec du code HTML, seuls les 100 premiers caractères sont affichés sans mise en forme.

    lang_id asset_id lang_code title title_native sef image description metakey metadesc sitename published access ordering
    10en-GBEnglish (en-GB)English (United Kingdom)enen_gb113
    20fr-FRFrançais (fr-FR)Français (France)frfr_fr112
    3378de-DEGerman (Germany)Deutsch (Deutschland)dede_de011

    Il me semble difficile de faire plus simple pour connaitre le contenu d'une table sans avoir besoin d'aller jouer avec PhpMyAdmin !

    Avec des options simples

    Il n'est pas toujours nécessaire ni souhaitable d'afficher toutes les colonnes et lignes d'une table.

    Pour la démo, affichons les 5 derniers articles du site sous la forme d'une liste.

    • 8 : upbtn-makefile ~ fichiers pour bouton éditeur crée le : 2020-07-02 15:15:53
    • 17 : Version 2.1 crée le : 2020-06-27 19:01:04
    • 10 : Plugin bouton éditeur crée le : 2020-06-23 07:13:11
    • 17 : Version 2.0 = 100 actions crée le : 2020-05-31 19:01:04
    • 8 : div ~ un bloc visible en wysiwyg crée le : 2020-05-31 06:37:37
    {up sql=content | setlimit=5 | order=created DESC
    | presentation=list | template=##catid## : ##title## [small]crée le : ##created##[/small] | header}

    La première ligne contient les options pour définir la requête SQL. Vous noterez que l'action respecte la terminologie JDatabase et SQL. Ce shortcode va créer la requête :

    SELECT * FROM #__content ORDER BY created DESC LIMIT 5

    La seconde ligne contient les options pour la mise en forme. Présentation demande un affichage sous forme de liste et template est un modèle au format contenant le nom des champs (colonnes) entre un double signe dièse : ID de la catégorie, titre de l'article et date de création. Il est possible de styler cette liste en utilisant le simili-HTML de UP que j'appelle bbcode

    Avec jointure

    Dans l'exemple précédent, il aurait été préférable d'avoir le nom de la catégorie plutôt que son ID.
    Pour cela, il faut faire une jointure entre la table content et categories

    Actionsupbtn-makefile ~ fichiers pour bouton éditeur2020-07-02 15:15:53
    NewsVersion 2.12020-06-27 19:01:04
    DocumentationPlugin bouton éditeur2020-06-23 07:13:11
    NewsVersion 2.0 = 100 actions2020-05-31 19:01:04
    Actionsdiv ~ un bloc visible en wysiwyg2020-05-31 06:37:37

    {up sql=#__content AS art
     | select=art.title AS title, cat.title AS catname, art.created AS date
     | innerjoin=#__categories AS cat ON cat.id = art.catid
     | setlimit=5
     | order=created DESC
     | template=##catname## ##title## ##date##
    }

    La présence de 2 tables oblige l'utilisation d'alias pour identifier les colonnes.
    Pour les habitués du SQL, ce shortcode doit se comprendre facilement. Pour les autres, consultez les nombreux tutos disponibles sur le web et en particulier celui-ci : Sélection de données en utilisant JDatabase

    La table principale est passée comme argument de from et on utilise innerjoin pour relier la table catégorie à content.
    Les champs pour la mise en page reprennent les alias définis dans le select. N'ayant pas spécifié une présentation en liste, on retrouve notre tableau par défaut.

    Les données JSON

    Joomla utilise des chaines JSON pour stocker des informations. C'est le cas, par exemple, pour les images  d'un article.


    Présentation
    {up sql=content | where=id='2' 
     | template=[img src="##images.image_intro##"][br]##title##
    | presentation=div | main-class=tc}

    Pour accéder à une donnée JSON, il suffit d'indiquer le nom de la colonne, un point et la clé JSON.

    J'ai également utilisé une clause where pour avoir un résultat qui a une image 😀.

    Pour mon unique fiche, une présentation dans une balise div est judicieuse ainsi que la classe pour centrer le tout. Noter l'utilisation du bbcode pour éviter leur interprétation par les éditeurs wysiwyg.

    Important : les données JSON sont récupérées  au moment de l'affichage des résultats
    Il est donc impossible de les utiliser comme critères de la requête SQL.

    Trier la table

    En présentation table, il est possible de cliquer sur les titres des colonnes pour trier le contenu.

    ID Etat Titre Publié le Position Module
    90LM-Custom-SITE2018-06-03 07:31:22debugmod_lmcustom
    135Test article-category0000-00-00 00:00:00demo-up-modulemod_lmcustom
    142Donation0000-00-00 00:00:00footer-droitmod_custom
    1Menu footer2018-06-10 18:32:33footer1mod_menu
    94Langage0000-00-00 00:00:00footer1mod_languages
    16Login Form2017-09-06 16:08:31loginmod_login
    137GTranslate0000-00-00 00:00:00menumod_gtranslate
    128Menu mobile0000-00-00 00:00:00offcanvasmod_menu
    17Breadcrumbs2017-09-16 12:48:18position-2mod_breadcrumbs
    138Test module right0000-00-00 00:00:00rightmod_lmcustom

    {up sql=modules | select=id,position,published,title,publish_up,module | where=client_id=0 | order=position
    | template=##id## ##published## ##title## ##publish_up## ##position## ##module##
    | header=ID,Etat,Titre,Publié le,Position,Module
    | col=d5-c5
    | published-format=list[-2:icon-trash, 0:icon-unpublish t-rouge, 1:icon-publish t-vert
    | published-model=<span class="%s"></span>
    | sort=i-s,3-s-s-s

     

    1. les options pour construire la requête SQL
    2. on précise les colonnes pour s'assurer de leur ordre d'affichage
    3. pour éviter d'avoir les noms des champs SQL comme titres, on les indique avec l'option header
    4. col permet de fixer la largeur et la justification des colonnes.
    5. pour éviter les valeurs numériques pour l'état, format définit les classes qui seront insérées par le modèle. Voir 'format des données' ci-après.
    6. le model est utilisé pour construire le contenu de la cellule. Voir 'Modèle d'affichage' ci-après.
    7. sort est l'option qui contient les directives pour le tri

    L'argument de sort est une liste des critères par colonnes.
    Dans notre exemple, i pour la première colonne spécifie que le contenu est un entier pour utiliser la méthode adéquate.
    Les autres types sont s (string) pour une chaine ou f (float) pour les nombres

    Tri multi-colonnes

    s,3 pour la deuxième colonne permet de demander un premier tri alphanumérique de la colonne suivi d'un tri de la troisième colonne (le titre).

    Valeur de tri dissociée de l'affichage

    La colonne 'Etat' montre également que la colonne bien que vide (juste une classe) est triée selon sa valeur originale passée comme attribut de la cellule du tableau. C'est d'ailleurs pour cela que l'on doit faire un tri alphanumérique qui est le format de cet attribut.
    UP spécifie automatiquement la valeur de tri pour toutes les colonnes ayant une définition de format de type list ou date.

    Ordre de tri initial

    Au chargement de la page, la table est triée selon l'ordre de la requête SQL. Vous pouvez le voir grâce au petit triangle dans son titre.

    La mise en forme

    Les données brutes fournies par la requête méritent d'être enrichies pour un meilleur aspect visuel :  un état corbeille est plus parlant que -2, 2018-10-26 16:27:55 n'est pas très joli pour une date, ...

    Dans une vue liste, il est souvent utile de préfixer le résultat. "Accès admin" informe plus que "admin" seul.

    Pour mettre en évidence certains résultats, pouvoir styler une ligne ou une cellule serait également bienvenue.

    Format des données

    S'il est facile d'afficher du texte simple, certaines données nécessitent une interprétation.

    Nous pouvons l'indiquer à l'aide d'une option qui reprend le nom de la colonne suivi de '-format'. Exemple : title-format.
    L'argument de cette option peut être un des types ci-dessous, suivi d'un argument entre crochets.

    text

    Il n'est pas toujours souhaitable d'afficher du contenu HTML tel quel. Le type text va supprimer toutes les balises HTML avec en option la possibilité de limiter la longueur du texte.

    Exemple : introtext-format=text[100] va afficher les 100 premiers caractères non formatés de l'introtext

    replace

    Ce type permet de remplacer du texte par un autre. La recherche ne tient pas compte de la case.

    La syntaxe complète (proche de la fonction php str_ireplace) est : xxx-format=replace[old1,old2:new1,new2]

     Exemple pour supprimer le préfixe d'un nom de module : module-format=replace[mod_]

    Pour remplacer up par Universal Plugin: xxxx-format=replace[up:Universal PLugin]

    liste de valeurs

    C'est le cas des statuts published ou des niveaux d'accès.

    L'option published-format=list[-2:Corbeille,0:Dépublié,1:Publié] va indiquer à l'action qu'il faut remplacer les valeurs retournées par la requête par celles de l'option. Donc pour la valeur -2, le champ ##published## affichera Corbeille.

    Une variante est d'indiquer des noms de classe (iconfont) qui seront utilisés dans le template ou le modèle (voir ci-après) avec ces options.

    | published-format=list[-2:icon-trash, 0:icon-unpublish t-rouge, 1:icon-publish t-vert
    | template=<span class="##published##"></span>
    OU | published-model=<span class="%s"></span>
    min
    max

    Affiche l'argument si la valeur est supérieure à (min) ou inférieure à (max).

    date

    Précise le format à appliquer à une date

    Exemple : created-format=date[%e %B %Y] le format est celui de la fonction php strftime

    Code : %y ➜ AAAA, %Y ➜ AA, %m ➜ 01-12, %M ➜ Jan-Dec, %F ➜ Janvier-Décembre, %d ➜ 01-31, %j ➜ 1-31, %D ➜ Lun-Dim, %l ➜ Lundi-Dimanche,
    %g ➜ 1-12, %h ➜ 01-12, %G ➜ 0-23, %H ➜ 00-23, %i ➜ 00-59, %s ➜ 00-59 (secondes),

    image

    On considère que la valeur de la colonne est une image.  xxx-format=img[60]  retourne le code HTML pour afficher l'image inscrite dans un carré de 60px.

    Si la colonne xxx retourne 'mon-image.jpg', le code généré sera <img src="/mon-image.jpg" alt="Mon image" width="60px">

    regex

    Affiche l'argument si la valeur répond à la recherche regex. Ce type est surtout utilisé pour affecter des styles en fonctions du contenu. Voir ci-après.

    xxx-format=regex[#^joomla#:texte

    Modèle d'affichage

    Il permet d'ajouter du texte avant/après la valeur  et de formater les nombres selon les règles pour la fonction php sprintf.

    On utilise une option qui reprend le nom de la colonne suivi de '-model'. Exemple : title-model.

    Exemple : tva-model=TVA: %0.2f € pour une valeur de 18.1 affichera : TVA: 18,10 €

    Par défaut, si la valeur est vide ou égale à zéro, rien ne sera affiché.
    Il est possible de changer ce comportement en ajoutant le modèle à la suite :  tva-model=TVA: %0.2f € ; EXPORT

    Attention  si vous utilisez un point-virgule, il faut mettre entre guillemets les 2 alternatives et doubler les éventuels guillemets doubles à l'intérieur

    Exemple : published-model="[span class=""%s tc""]&#x25a0;[/span]"

    ico-crayon bien que le HTML soit autorisé dans les options acceptant des balises, j'utilise du bbcode pour me protéger d'une interprétation par les éditeurs wysiwyg

    Style sous conditions

    Pour mettre en évidence certaines données, il est possible d'attribuer des classes à des lignes et/ou colonnes en fonction de leur contenu.

    Pour la démonstration, je vais reprendre la table utilisée pour le tri. Les lignes seront colorées selon leur état et les cellules seront mises en évidence si elles contiennent un mot.

    ID Etat Titre Publié le Position Module
    90LM-Custom-SITE 3 June 2018debugmod_lmcustom
    135Test article-category30 November -1demo-up-modulemod_lmcustom
    142Donation30 November -1footer-droitmod_custom
    1Menu footer10 June 2018footer1mod_menu
    94Langage30 November -1footer1mod_languages
    16Login Form 6 September 2017loginmod_login
    137GTranslate30 November -1menumod_gtranslate
    128Menu mobile30 November -1offcanvasmod_menu
    17Breadcrumbs16 September 2017position-2mod_breadcrumbs
    138Test module right30 November -1rightmod_lmcustom

    {up sql=modules | select=id,position,published,title,publish_up,module | where=client_id=0 | order=position
    | header=ID,Etat,Titre,Publié le,Position,Module
    | template=##id## ##published## ##title## ##publish_up## ##position## ##module##
    | col=d5-c5
    | sort=i-s,3-s-s-s
    | published-format=list[-2:icon-trash, 0:icon-unpublish t-rouge, 1:icon-publish t-vert
    | published-model=[span class="%s"][/span]
    | publish_up-format=date[%e %B %Y]
    | published-rowclass=list[-2:bg-gris, 0:bg-rougeClair
    | module-colclass=regex[#mod_lm#:bg-rouge t-jaune,#login#:bg-vert
    }
    Les premières lignes, identiques à l'exemple sur le tri, servent à sélectionner et afficher dans une table les données.

    ligne 9 : le suffixe -rowclass au nom de la colonne indique que nous mettons une condition sur la donnée de celle-ci. Selon le statut published, la ligne sera rouge ou grise ... ou sans changement !

    ligne 10 : le suffixe -colclass va agir sur la colonne (la cellule TD). Nous utilisons ici le type regex. Si le mot "mod_lm" est trouvé, la cellule sera sur fond rouge et si c'est le mot "login", le fond sera vert.

    Il n'est pas possible de mettre des conditions portant sur plusieurs colonnes.

    Aide

     Conscient de la difficulté d'utiliser cette action, j'ai mis en place plusieurs mécanismes d'aide.

    Debug

    En plus des informations habituelles, l'ajout d'une option debug au shortcode va afficher la requête SQL.
    Un bon moyen pour comprendre pourquoi le résultat n'est pas celui attendu.


    Exemple pour la démo requête avec jointure

    Erreur SQL

    Lors d'une erreur "fatale" SQL, le message est intercepté et affiché au début de la page

    Exemple d'utilisation d'une table inexistante

    Erreur de saisie

    Bien qu'il soit possible d'utiliser des options non prévues, un contrôle de cohérence est réalisé.

    Si vous utilisez un nom qui ne correspond pas à une colonne ou un suffixe (-format, -model) non autorisé, vous serez prévenu.

    Exemple : les erreurs détectées dans le shortcode ci-dessous

    {up sql=content | template=##idd## ##title##| title-xxx=text | xxxtitle-model=titre: %s | title-format=badtype[12]}

    Aide à la saisie

    Il est délicat de se souvenir du nom exact des tables et de ses colonnes.

    Pour éviter d'aller fouiller dans PhpMyAdmin, cette action dispose d'une option dbinfos qui affiche des informations sur la base de données courante dans le bloc des messages Joomla en haut de la page.

    {up sql | dbinfos}  affiche la liste des tables

    {up sql=#_nom_table | dbinfos}  affiche le nom et le type des colonnes de la table indiquée. Les clés sont soulignées.