Photo de Alejandro Escamilla sur Unsplash

Qui a dit que SQLite n’était pas fait pour la création de site web ? Bien au contraire ! Tout dépend de l’utilisation du site web en question. Voyons avec un exemple pas trop compliqué comment faire un blog très basique avec SQLite.


Partager l’article SQLite pour un blog sur les réseaux sociaux


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.db

Conclusion

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.