Du SQL non invasif dans du code PHP avec YeSQL

php

sql

yesql

La conception de site web avec PHP passe le plus souvent par l’utilisation d’une base de données SQL, comme MySQL, MariaDB, PostgreSQL ou même SQLite. Pour utiliser les requêtes, soit on utilise un ORM, soit on requête directement. Mais une troisième voie est possible.

14 novembre 2019

10 min.

Pourquoi ?

L’astuce que je propose est tout simplement d’isoler les requêtes SQL dans… des fichiers SQL. Le langage SQL est très bon pour les requêtes en base de données, alors, pourquoi réinventer la roue ? En particulier en voyant l’infâme DQL de Doctrine, à un moment, il faut peut-être se demander à quoi bon faire des surcouches qui évitent de faire du SQL si c’est pour y revenir au final et créer un pseudo-langage qui l’imite… C’est marcher sur la tête.

Prérequis

Pour ma petite présentation, je vais me baser sur l’utilisation de la base de donnée SQLite définie dans l’article SQLite pour un blog. Il y aura donc une bonne dose de SQL au début pour savoir de quoi on va parler histoire d’avoir des exemples un peu plus complets qu’un « Coucou Hello World ! ».

Ensuite, on utilisera du code PHP tout simple, sans framework en particulier.

Yesql utilise PDO pour son fonctionnement, vous devrez donc avoir PDO fonctionnel sur votre intallation PHP, ce qui est le cas par défaut en principe car PDO et PDO_SQLITE sont activés par défaut depuis PHP 5.1.0.

Pour installer Yesql on utilisera composer.

Les portions de code PHP se concentreront sur le côté backend, je ne m’attarderai pas sur les formulaires, car ce n’est pas le sujet.

Le dossier du projet s’appellera arbitrairement blog.

Je suppose également que le système utilisé est de type UNIX-like, un Linux ou un BSD.

Les fichiers des requêtes SQL

Bien. Maintenant, nous allons créer un dossier pour stocker nos fichiers SQL qui serviront pour les requêtes ainsi que les fichiers à utiliser :

mkdir queries
touch queries/crud_article.sql
touch queries/crud_tag.sql
touch queries/link.sql
touch queries/list_article.sql
touch queries/list_tag.sql

Les fichiers crud_*.sql contiendront les opérations de création, de mise à jour et de suppression des articles et de tags.

Le fichier link.sql contient les requêtes permettant de gérer les associations articles/tags.

Les fichiers list_*.sql contiendront les différents sélections des articles et des tags.

Ce sont ces fichiers qui seront utilisés par Yesql pour une utilisation directe via PHP, nous verrons cela un peu plus loin dans cet article.

Notez que c’est une façon de faire, vous pouvez tout à fait organiser ces fichiers de requête comme bon il vous semble.

Voyons ce qu’il y aura comme requêtes :

ajout d’article
modification d’article
suppression d’article (en soft delete)
ajout de tag
modification de tag
suppression de tag (en soft delete aussi)
liste des articles pour les administrateur⋅rice⋅s du site
liste des tags pur les administrateur⋅rice⋅s
liste des articles pour les visiteur⋅euse⋅s du site
liste des tags pour les visiteur⋅euse⋅s du site

CRUD pour les articles

Dans notre cas, lors de la création d’un article, il faut au minimum la langue et le titre.

La définition de la table et les déclencheurs permettent la réalisation d’un brouillon dans une langue avec création en cascade d’une entrée dans la table articles et translated_articles, ce dernier avec le statut de publication à non publié.

La requête de création est alors toute simple, avec quelques petits garde-fous :

-- name: createArticle(code, title)
INSERT INTO translated_articles (language_code, title)
VALUES      (TRIM(LOWER(:code)), TRIM(:title));

Analysons ce que nous avons :

la ligne de commentaire doit respecter une syntaxe particulière pour que Yesql récupère ce bout de code SQL et le traduise en une méthode PHP.
Ici, name: introduit le nom de la méthode qui existera du côté PHP, et les arguments seront ceux utilisées par la méthode. Ces arguments sont ici repris dans le SQL en variables commençant par un :.

Bien, vous voyez la facilité et le côté pratique de Yesql ? On verra d’autres possibilités plus loin, comme les one to many ou encore l’indication de la classe PHP à instancier pour chaque entrées de la DB retournées.

Nous avons vu la création, passons maintenant à la mise à jour d’une entrée !

-- name: updateArticle(id, code, title, content, isPublished)
UPDATE  translated_articles
SET     title   = TRIM(:title)
        content = TRIM(:content)
        is_published = :isPublished
WHERE
        article_id = :id
        AND
        language_code = :code
;

Le titre, le contenu et le statut de publication peut très bien changer lors d’une modification, on prend ici les 3 champs en compte.

Bien sur, si du côté front end vous utilisez des éléments en AJAX pour mettre à jour des éléments par morceaux, rien ne vous empêche de faire des requêtes séparées pour chaque champ :

-- name: updateArticleTitle(id, code, title)
UPDATE  translated_articles
SET     title   = TRIM(:title)
WHERE
        article_id = :id
        AND
        language_code = :code
;

-- name: updateArticleContent(id, code, content)
UPDATE  translated_articles
SET     content   = TRIM(:content)
WHERE
        article_id = :id
        AND
        language_code = :code
;

-- name: updateArticleSetStatus(id, code, isPublished)
UPDATE  translated_articles
SET     is_published   = :isPublished
WHERE
        article_id = :id
        AND
        language_code = :code
;

-- name: updateArticleSwitchStatus(id, code)
UPDATE  translated_articles
SET     is_published   = is_published = ((is_published | 1) - (is_published & 1))
WHERE
        article_id = :id
        AND
        language_code = :code
;

Vous noterez la petite astuce dans la dernière requête qui permet d’alterner la valeur du champ is_published. Cette astuce a été mentionnée par Dimitry Ivanov.

Nous avons vu la création et la mise à jour d’un article, voyons maintenant la suppression, en soft delete dans notre cas :

-- name: deleteArticle(id)
UPDATE  articles
SET     deleted_at = datetime('now')
WHERE   id = :id;

CRUD pour les tags

Pour les tags, C’est assez facile également, voici ce qu’on peut avoir :

-- name: createTag(code, title)
INSERT INTO translated_tags (language_code, title)
VALUES      (TRIM(LOWER(:code)), TRIM(:title));

-- name: updateTag(id, code, title)
UPDATE  translated_tags
SET     title = TRIM(:title)
WHERE   tag_id = :id
        AND
        language_code = :code
;

-- name: deleteTag(id)
UPDATE  tags
SET     deleted_at = datetime('now')
WHERE   id = :id;

Note : On peut aller plus loin encore en proposant l’ajout de plusieurs nouveaux tags d’un coup via les Common Table Expressions et des tags entrés dans un champs de formulaire avec des virgules comme séparateur, mais déjà que cet article parle beaucoup de SQL, pas besoin d’en rajouter. J’en parlerai toutefois dans un prochain article.

Gérer les associations entre les articles et les tags

Un tag peut être associé à plusieurs articles, un article peut être associé à plusieurs tags. C’est pourquoi une table d’association a été créée plus tôt dans l’article mentionné au début.

Il y a plusieurs manières de voir comment gérer les tags. Nous allons considérer les cas les plus simples.

Pour associer un tag existant à un article, faite ce qui suit :

-- name: associateTag(articleId, tagId)
INSERT INTO articles_tags (article_id, tag_id)
VALUES (:articleId, :tagId);

Pour supprimer l’association d’un tag, c’est tout simplement :

-- name: separate(articleId, tagId)
DELETE FROM articles_tags
WHERE       article_id = :article_id
            AND
            tag_id = :tagId;

Pour supprimer tous les tags d’un article, faites le Rambo avec ça :

-- name: separateAllTagsOnArticle(articleId)
DELETE FROM articles_tags
WHERE       article_id = :articleId;

Pour supprimer toutes les associations d’un tag, faites ceci :

-- name: separateAllArticlesFromTag(tagId)
DELETE FROM articles_tags
WHERE       tag_id = :tagId;

Listes des différentes données

Pour lister les articles pour le public, il suffit de faire un SELECT en mettant une limite et un offset en utilisant la vue SQL créée précédemment, ainsi que la langue voulue :

-- name: listPublicArticles(code, amount, offset)
SELECT  *
FROM    v_public_articles_listing
WHERE   language_code = :code
LIMIT   :offset , :amount;
;

Pour juste la page d’accueil du blog, on peut se contenter de fixer la limite en dur :

-- name: listArticlesForHomePage(code)
SELECT  *
FROM    v_public_articles_listing
WHERE   language_code = :code
LIMIT   5;

Pour afficher un article public :

-- name: showPublicArticle(code, id)
SELECT  *
FROM    v_public_articles_listing
WHERE   language_code = :code
        AND
        id = :id
;

Pas besoin de spécifier l’ordre d’affichage, puisque dans la vue, cela a été déjà spécifié.

Dans l’admin, les articles seront affichés par langue ou non :

-- name: listAdminArticlesByLanguage(code, amount, offset)
SELECT  *
FROM    translated_articles
WHERE   language_code = :code
LIMIT   :offset , :amount;

Pour les tags, dans la partie admin comme dans la partie publique, on utilise la même requête :

-- name: listTag(code, amount, offset)
SELECT  *
FROM    translated_tags
WHERE   language_code = :code
LIMIT   :offset , :amount;

Pour celles et ceux qui ont bloqué leur respiration à la vue de tout ce SQL, vous pouvez à nouveau respirer, j’en ai fini ! Oui enfin.

Pour les autres, j’ai plein de sujets intéressants dans les cartons concernant le SQL, SQLite et PostgreSQL qui iront plus loin que ce qui sert d’exemple ici.

Un peu de PHP maintenant…

Côté DB, on a tout ce qu’il nous faut : les tables, les vues et les requêtes qui nous sont nécessaires. Voyons maintenant comment utiliser ça avec PHP et Yesql.

Mise en bouche

Plaçons-nous à la racine de notre projet et utilisons composer pour installer Yesql :

cd blog
composer require nulpunkt/yesql-php

Comme je le rappelle, je ne fais pas l’application totalement, je vous mets juste les portions de codes qui nous intéressent.

Donc chargeons Yesql dans notre code, initialisons-le, et récupérons la liste publique des articles pour la page d’accueil.

<?php

require('./vendor/autoload.php');

// Initialisez PDO en premier
$pdo = new PDO('sqlite:///path/to/blog/my.db');

// Définissez le « repository » pour les listes d’article en chargeant le
// fichier contenant les requêtes nécessaires
$articlesListRepo = new Nulpunkt\Yesql\Repository($pdo, "queries/list_article.sql");

// Optionel, j’aime bien que les données DB retournées
// par PDO soient sous la forme d’objets
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
// Optionel, définit la façon dont on lève les erreurs
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Récupérons la listes de articles pour la page d’accueil !
$list = $articlesListRepo->listArticlesForHomePage('fr');

// Pour juger sur pièce :-)
var_dump($list);

Voilà, le nom défini dans le commentaire SQL correspond au nom de la méthode du repository créé par Yesql. Pas une ligne de code SQL dans le PHP, pas d’ORM.

Pas D’ORM ? Pas de panique, au lieu de vouloir à tout prix faire correspondre des objets à ce qui existe en modèles dans la base de données, le mieux est de faire des objets qui correspondent aux besoins d’un résultat de données. C’est l’approche qui a été retenue dans le projet Pomm, une lib qui est spécialisée dans l’interaction PostgreSQL avec PHP. Je vous recommande chaudement Pomm en lieu et place d’un ORM classique si vous utilisez PostgreSQL dans un projet PHP, c’est plus rapide, ça prend en compte les spécificités de PostgreSQL, etc. Fin de la digression PostgreSQL.

Stop ! Et si je veux utiliser ma classe pour les résultats ?

Je dis que c’est tout à fait possible, puisque PDO le permet. Et Yesql propose ça directement via le commentaire SQL.

Reprenons le cas de la page d’accueil. Imaginons une classe à utiliser en lieu et place des d’objets simples retournés par PDO. Cette classe comporterait notamment de quoi avoir des objets DateTime en lieu et place de chaîne de caractères du type 2019-01-01 00:00:00 pour les champs datetime de la table SQLite. On pourrait aussi découper la chaîne de tags pour en faire ce qu’on veut côté PHP.

Mieux : un trait qui permet de convertir les champs datetime de SQlite que l’on souhaite en object DateTime de PHP, facilitant la réutilisation dans d’autres classes. La même chose pour la découpe de la chaîne des tags. On pourrait alors avoir ce qui suit :

<?php

// Le fichier du Trait DateTime


use DateTime;
use DateTimeZone;

trait DateTimeTrait
{
    protected function convertToDateTime(array $fields)
    {
        foreach ($fields as $field) {
            if (!isset($this->$field)) {
                continue;
            }

            if (!is_string($this->$field) || empty($this->$field)) {
                continue;
            }

            $utc = new DateTime($this->$field, new DateTimeZone('UTC'));
            $this->$field = $utc->setTimezone(new DateTimeZone('Europe/Paris'));
        }
    }
}


// Le fichier du Trait SplitString

trait SplitString
{
    protected function splitStringToArray(array $fields)
    {
        foreach ($fields as $field) {
            if (!isset($this->$field)) {
                continue;
            }

            if (!is_string($this->$field) || empty($this->$field)) {
                continue;
            }

            $this->$field = array_map('trim', explode(',', $this->$field));
        }
    }
}

// Le fichier de la classe

use DateTimeTrait;

class Article
{
    use DateTimeTrait;
    use SplitString;

    public function __construct()
    {
        $this->convertToDateTime(['published_at', 'modified_at']);
        $this->splitStringToArray(['tag_title']);
    }
}

Maintenant que nous avons notre classe, utilisons-la. Pour cela, il faut reprendre le SQL déclarant la requête pour la page d’accueil :

-- name: listArticlesForHomePage(code)
SELECT  *
FROM    v_public_articles_listing
WHERE   language_code = :code
LIMIT   5;

Et changeons le commentaire :

-- name: listArticlesForHomePage(code) rowClass: Article
SELECT  *
FROM    v_public_articles_listing
WHERE   language_code = :code
LIMIT   5;

Et voilà, chaque résultat pour cette requête sera sous la forme d’une instanciation de la classe Article.

Note : L’exemple ici est simple. Dans la vraie vie, la classe aura un espace de nom. Donc pensez à bien préciser le FQCN de la classe dans le commentaire SQL. Par exemple :

-- name: maMethode(var1, var2) rowClass: Espace\De\Nom\Complet\De\La\Classe

Un ou plusieurs ?

Parfois, dans les requêtes SQL, on se retrouve avec des SELECT … LIMIT 1;. Dans ce cas, pour éviter d’avoir un tableau d’un élément, autant retourner l’élément uniquement. C’est la que l’élément oneOrMany entre en jeu.

Reprenons la sélection d’un article public :

-- name: showPublicArticle(code, id)
SELECT  *
FROM    v_public_articles_listing
WHERE   language_code = :code
        AND
        id = :id
;

Changeons son commentaire en :

-- name: showPublicArticle(code, id) oneOrMany: one rowClass: Article
SELECT  *
FROM    v_public_articles_listing
WHERE   language_code = :code
        AND
        id = :id
;

En prime, j’y ai ajouté l’utilisation de la classe Article pour montrer le côté cumulatif.

Maintenant à vous de reprendre les éléments déjà présents pour étendre tout ça à votre sauce.

Et dans les autres languages de programmation ?

Cette lib est inspirée de sa version originale, développée pour Clojure.

Le côté pratique de l’approche proposée par cette lib a été repris dans des projets équivalent portant l’idée vers d’autres languages (avec quelques changements parfois). En voici quelqu’uns :

Il en existe un autre pour PHP, Yepsql
CL-Yesql pour Common Lisp
Jasql pour C#
Yesql pour Elixir
Eql pour Erlang
Yeshql pour Haskell
Dasql ou Shacql pour Java
Yesql pour NodeJS
Anosql pour Python
Yayql pour Ruby

Il y en a probablement plein d’autres…

Pour aller plus loin…

Dans cet article, je vous ai présenté des cas d’utilisations assez simples, mais sachez que vous pouvez aller plus loin avec l’utilisation d’une base de données autre que SQLite utilisée en support ici.

Je vous recommande ainsi chaudement, par exemple avec PosqtgreSQL ou MySQL, d’utiliser des procédures stockées conjointement à des vues. Cela fera l’objet d’un futur article.

Plus généralement, utilisez à fond les facultés de votre DBMS. Vous y gagnerez en temps de développement et en maintenance. En faisant ainsi, Yesql peut s’avérer être un bon choix de liant entre le code PHP et la base de données.

Je reviendrai sur Yesql dans de futurs articles, avec PostgreSQL cette fois-ci.

Photo de Jenelle Hayes sur Unsplash

Du SQL non invasif dans du code PHP avec YeSQL

Pourquoi ?

Prérequis

Les fichiers des requêtes SQL

CRUD pour les articles

CRUD pour les tags

Gérer les associations entre les articles et les tags

Listes des différentes données

Un peu de PHP maintenant…

Mise en bouche

Stop ! Et si je veux utiliser ma classe pour les résultats ?

Un ou plusieurs ?

Et dans les autres languages de programmation ?

Pour aller plus loin…

Talend et authentification Windows pour accéder à SQL Server

Oracle Database Express Edition avec Docker

Trois pièges de débutant sous Oracle

Du SQL non invasif dans du code PHP avec YeSQL

Partager l’article Du SQL non invasif dans du code PHP avec YeSQL sur les réseaux sociaux

Pourquoi ?

Prérequis

Les fichiers des requêtes SQL

CRUD pour les articles

CRUD pour les tags

Gérer les associations entre les articles et les tags

Listes des différentes données

Un peu de PHP maintenant…

Mise en bouche

Stop ! Et si je veux utiliser ma classe pour les résultats ?

Un ou plusieurs ?

Et dans les autres languages de programmation ?

Pour aller plus loin…