Prérequis

J’utilise dans cet article des exemples utilisant PostgreSQL, mais les principes de base sont adaptables à toutes les bases de données relationnelles.

Je vous montre une version générique (et simplifiée), à adapter selon vos besoins.

Notre exemple

L’exemple utilisé concerne un blog fictif, utilisant les langues disponibles stockées dans une table languages et stockant la référence des articles dans la table posts. La table t_posts entre en jeu pour tous les champs traduisibles.

Grandes lignes

Le principe est simple : pour chaque entrée dans la table posts, il y a au moins une autre entrée dans la table t_posts comportant des champs traduits dans une langue.

Chaque entrée dans t_posts comporte une clé étrangère obligatoire vers la table posts et une autre également obligatoire vers la table languages, en tâchant de contrôler l’unicité de cette association de clés.

La table stockant les langues supportées

Il est temps de déclarer notre table :

CREATE TABLE languages(
    id      SERIAL PRIMARY KEY,
    code    TEXT NOT NULL,
    name    TEXT NOT NULL
    UNIQUE(code)
);

Remplissons cette table :

INSERT INTO languages(code, name)
VALUES      ('fr', 'Français'),
            ('en', 'English'),
            ('de', 'Deutsch')
;

Vérifions son contenu :

 id | code |   name   
----+------+----------
  1 | fr   | Français
  2 | en   | English 
  3 | de   | Deutsch 
(3 rows)

Parfait, les langues autorisées pour le site sont correctement en place comme nous le souhaitions.

La table racine des articles

Définissons maintenant la table stockant les données communes entre chaque version d’un même article.

Nous allons être simple : nous utiliserons un champ pour le soft delete et un champ pour la clé primaire. Et c’est tout !

CREATE TABLE posts(
    id          SERIAL PRIMARY KEY,
    deleted_at  TIMESTAMPTZ
);

La table contenant les versions dans différentes langues des articles

Cette table sera plus fournie, car elle devra :

• avoir une référence non nulle vers la table posts
• avoir une référence non nulle vers la table languages
• ne pas avoir de doublon sur l’ensemble des deux clés étrangères ci-dessus
• chaque nouvelle entrée ne sera pas publiée par défaut (brouillon systématique)

Concrétisons cela :

CREATE TABLE t_posts(
    id              SERIAL PRIMARY KEY,
    post_id         INTEGER NOT NULL REFERENCES posts(id),
    language_code   TEXT NOT NULL REFERENCES languages(code),
    title           TEXT NOT NULL,
    content         TEXT NOT NULL DEFAULT '',
    is_published    BOOLEAN NOT NULL DEFAULT 'f',

    UNIQUE (
        post_id,
        language_code
    )
);

Le problème des entrées fantômes…

Notre façon de séparer les champs liés à une version et le reste, est que nous risquons de créer des entrées fantômes dans la table posts sans jamais avoir de traductions.

Nous devons donc inclure une sécurité consistant à ne jamais ajouter d’entrée dans la table posts sans avoir au moins une entrée créer en même temps dans la table t_posts.

Comment faire cela ?

Soit nous mettons un trigger créant, lors d’une insertion, une entrée dans la table posts si on ne fourni pas de post_id et balançant cette valeur en tant que post_id pour l’insertion.

Avantage : se fait de façon transparente et est non bloquant.

Inconvénient : favorise la création de doublons potentiels car on ne se réfère pas à une entrée de la table posts. Cela n’empêche également pas la création d’entrées dans la table posts sans au moins un élément traduit dans t_posts.

C’est donc une mauvaise piste.

Soit nous nous facilitons la tâche avec la création de fonctions et un verrouillage empêchant l’ajout directe dans la table posts :

• une fonction pour la création d’un nouveau post dans une langue,
• et une fonction pour l’ajout d’une traduction à un post existant.
• Un trigger sur la table posts pour empêcher toute commande INSERT directement.

Fonction pour l’ajout d’un nouvel article

La fonction prendra le minimum nécessaire pour définir un nouvel article : le titre et le code langue. En retour, la fonction fournit la valeur de la clé primaire de la table posts, afin d’être pratique et de pouvoir enchaîner facilement sur une édition ou l’ajout d’une version dans une autre langue.

CREATE OR REPLACE FUNCTION new_post(
    IN arg_title            TEXT,
    IN arg_language_code    TEXT
)
RETURNS INTEGER AS $$
DECLARE
    pid     INTEGER;
BEGIN
    INSERT INTO posts (id) VALUES(DEFAULT) RETURNING id INTO pid;
 
    INSERT INTO t_posts (post_id, title, language_code)
    VALUES      (pid, TRIM(arg_title), arg_language_code);

    RETURN pid;
END;
$$
LANGUAGE plpgsql;

Voilà notre première fonction créée.

Fonction pour ajouter une nouvelle traduction à un article existant

Cette fonction ressemble beaucoup à la précédente, sauf qu’en arguments d’entrées il y en a un supplémentaire pour l’ID de l’entrée dans posts et la valeur de retour est celle de la nouvelle entrée dans la table t_posts, pour pouvoir passer à l’édition tout de suite après par exemple.

CREATE OR REPLACE FUNCTION add_translation_to_post(
    IN arg_post_id          INTEGER,
    IN arg_title            TEXT,
    IN arg_language_code    TEXT
)
RETURNS INTEGER AS $$
DECLARE
    tpid     INTEGER;
BEGIN
    INSERT INTO t_posts (post_id, title, language_code)
    VALUES      (
                    arg_post_id,
                    TRIM(arg_title),
                    arg_language_code
                )
    RETURNING id INTO tpid;

    RETURN tpid;
END;
$$
LANGUAGE plpgsql;

Nous avons maintenant ce qu’il faut pour ajouter autant de traduction que nous le permet la table languages.

Trigger interdisant l’ajout direct

Le trigger que nous allons ajouter et appliquer à la table posts lèvera une erreur quand nous tenterons de créer directement des entrées dans posts via la commande INSERT et non via la fonction new_post.

En faisant ainsi, il n’y aura pas d’entrées fantômes.

Cette astuce est disponible dans sa version d’origine sur Stackoverflow.

CREATE FUNCTION prevent_natural_insert_on_posts() RETURNS trigger AS $$
DECLARE
    stack TEXT;
    fn    INTEGER;
BEGIN
    RAISE EXCEPTION 'secured';
  
    EXCEPTION WHEN OTHERS THEN
    BEGIN
        GET STACKED DIAGNOSTICS stack = PG_EXCEPTION_CONTEXT;
        
        fn := position('new_post' in stack);

        IF (fn <= 0) THEN
            RAISE EXCEPTION 'Expecting insert from new_post function'
            USING HINT = 'Use function to insert data';
        END IF;
        
        RETURN new;
    END;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_normal_insert BEFORE INSERT ON posts
FOR EACH ROW EXECUTE PROCEDURE prevent_natural_insert_on_posts();

Testons !

Voyons l’ajout direct sur la table posts, qui doit retourner une erreur :

INSERT INTO posts (id) VALUES (DEFAULT);

Nous obtenons bien un message d’erreur :

ERROR:  Expecting insert from new_post function
HINT:  Use function to insert data
CONTEXT:  PL/pgSQL function prevent_natural_insert_on_posts() line 15 at RAISE

Et un SELECT montre bien que la table posts est toujours vide.

Tentons alors d’ajouter une version à un article qui n’existe pas :

SELECT add_translation_to_post(42, 'It should fail!', 'en');

Et les contraintes ont bien été définies puisque nous obtenons cette erreur :

ERROR:  insert or update on table "t_posts" violates foreign key constraint "t_posts_post_id_fkey"
DETAIL:  Key (post_id)=(42) is not present in table "posts".
CONTEXT:  SQL statement "INSERT INTO t_posts (post_id, title, language_code)
    VALUES      (
                    arg_post_id,
                    TRIM(arg_title),
                    arg_language_code
                )
    RETURNING id"
PL/pgSQL function add_translation_to_post(integer,text,text) line 5 at SQL statement

Maintenant que nous avons fait le tour des contraintes bloquantes, essayons d’entrer du contenu sans erreur en créant un article et en y ajoutant deux autres versions :

SELECT new_post('Vous sortez du secteur américain', 'fr');

Retourne 2 dans mon cas, réutilisons-le :

SELECT add_translation_to_post(2, 'You are living american sector', 'en');
SELECT add_translation_to_post(2, 'Sie verlassen den Amerikanischen Sektor', 'de');

Voyons cela :

SELECT * FROM t_posts;

Nous donne :

 id | post_id | language_code |                  title                  | content | is_published 
----+---------+---------------+-----------------------------------------+---------+--------------
  2 |       2 | fr            | Vous sortez du secteur américain        |         | f
  3 |       2 | en            | You are living american sector          |         | f
  4 |       2 | de            | Sie verlassen den Amerikanischen Sektor |         | f
(3 rows)

Tentons de provoquer une erreur en ajoutant une version qui existe déjà :

SELECT add_translation_to_post(2, 'Hey! I am already here!', 'en');

…et l’erreur est bien levée :

ERROR:  duplicate key value violates unique constraint "t_posts_post_id_language_code_key"
DETAIL:  Key (post_id, language_code)=(2, en) already exists.
CONTEXT:  SQL statement "INSERT INTO t_posts (post_id, title, language_code)
    VALUES      (
                    arg_post_id,
                    TRIM(arg_title),
                    arg_language_code
                )
    RETURNING id"
PL/pgSQL function add_translation_to_post(integer,text,text) line 5 at SQL statement

Conclusion

J’espère que cette façon de gérer les traductions vous sera utile dans l’utilisation que vous faites de votre base de données.

Ce que je vous ai montré est une version simplifiée, généralement un peu plus de contraintes et de cas spéciaux sont présents en pratique. À vous d’adapter les fonctions et les contraintes en fonction de votre situation. Un petit exercice serait de généraliser le trigger empêchant l’insertion directe afin qu’elle fonctionne pour toutes les tables auxquelles elle seraient reliée.