L’exemple se basera sur un contenu de type blog tout simple, mais multilingue :
- Un article pourra être dans une ou plusieurs langues.
- On peut ajouter de nouvelles langues.
- On peut ajouter des tags aux articles.
- Les tags peuvent être dans plusieurs langues également.
Attention, pour les développeurs allergiques au SQL, ne passez pas votre chemin, vous allez voir qu’on peut faire des choses sympas avec du code bien pensé côté DB et quelques déclencheurs, et oui, même avec SQLite, on peut faire des trucs cools.
Les langues à utiliser
Voici le schéma pour la table languages pré-remplie avec deux langues :
CREATE TABLE languages (
code TEXT PRIMARY KEY UNIQUE,
name TEXT
);
INSERT INTO languages (code, name)
VALUES ('en', 'English'),
('fr', 'Français');Pour la clé primaire, on ne s’embête pas ici, on prend le code ISO à deux lettres de la langue.
Les tables concernant les articles
Voici le schéma de la table articles :
CREATE TABLE articles (
id INTEGER PRIMARY KEY UNIQUE,
created_at DATETIME DEFAULT (datetime('now') ) NOT NULL,
deleted_at DATETIME
);Elle est très simple et n’a que le champ id pour identifier l’article, le champ de la date de création, created_at et celui de soft delete, deleted_at pour éviter des suppression réelles. C’est la table suivante qui contiendra plus d’informations.
La table contenant les versions des articles dans différentes langues est la suivante :
CREATE TABLE translated_articles (
id INTEGER PRIMARY KEY UNIQUE,
article_id INTEGER REFERENCES articles (id),
language_code TEXT REFERENCES languages (code),
title TEXT NOT NULL,
content TEXT NOT NULL,
is_published BOOLEAN NOT NULL DEFAULT(0) CHECK (is_published IN (0, 1) ),
published_at DATETIME,
modified_at DATETIME DEFAULT (datetime('now') ) NOT NULL,
UNIQUE (
article_id,
language_code
)
);Ajoutons-y un premier déclencheur pour ajouter une date automatiquement si lors de l’insertion d’une nouvelle traduction d’article on spécifie immédiatement le statut de publication à publié :
CREATE TRIGGER on_insert_article
AFTER INSERT
ON translated_articles
FOR EACH ROW
WHEN NEW.is_published = 1 AND
NEW.published_at IS NULL
BEGIN
UPDATE translated_articles
SET published_at = datetime('now')
WHERE id = NEW.id;
END;
Créons maintenant un second déclencheur pour cette table qui permet de mettre la date de publication automatiquement à jour si on change le statut de publication d’un article de non publié à publié et seulement si la date de publication est inexistante :
CREATE TRIGGER on_update_article_publication_status
AFTER UPDATE OF is_published
ON translated_articles
FOR EACH ROW
WHEN OLD.is_published = 0 AND
NEW.is_published = 1 AND
OLD.published_at IS NULL
BEGIN
UPDATE translated_articles
SET published_at = datetime('now')
WHERE id = OLD.id;
END;Enfin, un dernier déclencheur permet d’ajouter une entrée à la table articles
si on ajoute un élément dans la table translated_articles avec le champ
article_id à NULL :
CREATE TRIGGER on_insert_article_without_parent
AFTER INSERT
ON translated_articles
FOR EACH ROW
WHEN NEW.article_id IS NULL
BEGIN
INSERT INTO articles (
id,
created_at
)
VALUES (
NULL,
NEW.modified_at
);
UPDATE translated_articles
SET article_id = last_insert_rowid()
WHERE id = NEW.id;
END;Étiquettes thématiques
Ajoutons une table tags pour associer des étiquettes thématiques aux articles :
CREATE TABLE tags (
id INTEGER PRIMARY KEY UNIQUE,
created_at DATETIME DEFAULT (datetime('now') ) NOT NULL,
deleted_at DATETIME
);Et son équivalent pour les traductions disponibles :
CREATE TABLE translated_tags (
id INTEGER PRIMARY KEY UNIQUE,
tag_id INTEGER REFERENCES tags (id),
language_code TEXT REFERENCES languages (code),
title TEXT NOT NULL,
modified_at DATETIME DEFAULT (datetime('now') ) NOT NULL,
UNIQUE (
tag_id,
language_code
)
);Ajoutons un déclencheur pour créer une entrée dans la table tags si translated_tags.tag.id = NULL :
CREATE TRIGGER on_insert_tag_without_parent
AFTER INSERT
ON translated_tags
FOR EACH ROW
WHEN NEW.tag_id IS NULL
BEGIN
INSERT INTO tags (
id,
created_at
)
VALUES (
NULL,
NEW.modified_at
);
UPDATE translated_tags
SET tag_id = last_insert_rowid()
WHERE id = NEW.id;
END;Et bien sûr, pour finir avec les schémas DB, la table liant les tags aux articles, en s’assurant de ne pas introduire plusieurs fois le même tag :
CREATE TABLE articles_tags (
id INTEGER PRIMARY KEY UNIQUE,
article_id INTEGER REFERENCES articles (id),
tag_id INTEGER REFERENCES tags (id),
UNIQUE (
tag_id,
article_id
)
);Bon, et les listes alors ?
Petite cerise sur le gâteau SQL, créons une vue pour avoir le listing des articles publiées. Cette liste des articles doit contenir les informations utiles qui apparaitront sur le site pour les visiteurs, il faut donc avoir disponible dans les résultats de la requête :
- le titre et le contenu de l’article
- la date de publication de l’article par langue
- la date de modification de l’article par langue
- la liste des tags associés à la traduction de l’article dans la bonne langue (si l’article est en FR, il faut n’afficher que la version des tags en FR)
- le code de la langue (et son nom optionnellement)
- on trie par ordre antichronologique par rapport à la date de publication des articles
Cette vue peut ressembler au code suivant :
CREATE VIEW v_public_articles_listing AS
SELECT a.id AS id,
ta.language_code AS language_code,
l.name AS language_name,
ta.title AS title,
ta.content AS content,
ta.published_at AS published_at,
GROUP_CONCAT(tt.title, ', ') AS tag_title,
ta.modified_at AS modified_at
FROM translated_articles AS ta
LEFT JOIN
articles AS a ON ta.article_id = a.id AND
a.deleted_at IS NULL
LEFT JOIN
languages AS l ON ta.language_code = l.code
LEFT JOIN
articles_tags AS at ON a.id = at.article_id
LEFT JOIN
tags AS t ON t.id = at.tag_id AND
t.deleted_at IS NULL
LEFT JOIN
translated_tags AS tt ON tt.tag_id = t.id AND
tt.language_code = ta.language_code
WHERE ta.is_published = 1
GROUP BY ta.id
ORDER BY ta.published_at DESC;Créez un fichier blog/my.db et exécutez-y toutes les commandes SQL précédentes dans l’ordre. Comme je suis gentil voici directement le fichier SQL pour vous lancer :
mkdir blog
cd blog
wget https://cogito-ergo-dev.fr/blog/6543/du-sql-non-invasif-dans-du-code-php-avec-yesql/start.sql
cat start.sql | sqlite3 my.dbConclusion
Parfait, on a la base… de la base ! Pour aller plus loin, je vous laisse agrémenter le code d’améliorations comme par exemple l’ajout de slug, l’utilisation de l’extension JSON pour l’aggrégation des tags avec leur slug et leur nom, la mise en place d’une table d’audit, la mise en place du table d’auteurs et faire les liaisons adéquates.
Bref, je vous ai fait une petite présentation de SQLite dans le contexte de la réalisation d’un petit site.
