tests postgres - clavier ouvert

🗄️2025.10.17
tests postgres

Est-ce que ma requête est correcte?

Je ne suis pas un grand spécialiste des bases de données PostgreSQL. Des trois cours que je donne ce quadrimestre, c’est certainement celui dans lequel je suis le moins à l’aise. Je sais comprendre et expliquer tous les concepts théoriques, mais pouvoir rapidement vérifier si une requête fonctionnne exactement comme prévu, si elle répond exactement aux critères de l’énoncé, ce n’est pas toujours évident.

Je me suis demandé: est-ce qu’il y a moyen de faire l’équivalent en SQL des “tests unitaires” qu’on peut retrouver dans les différents languages de programmation auxquels je suis plus habitués? Cela me permettrait de mettre en place des procédures relativement simples pour vérifier qu’une solution (la mienne ou celle d’un.e étudiant.e) est correcte… ou de pointer dans la direction de l’erreur dans le cas contraire.

Je suis tombé sur un article prometteur: “Unit Testing and TDD With PostgreSQL is Easy”, par un certain Vedran Bilopavlović. Nous n’avons pas nécessairement la même notion de easy, mais je dois reconnaître que la solution est, en tout cas, raisonnable. L’idée est d’utiliser une procédure SQL qui va:

Insérer des données de test.
Exécuter la requête à tester.
Utiliser des ASSERT pour vérifier si le résultat est celui attendu.

La bonne nouvelle, c’est que ça marche assez bien. La mauvaise, c’est que c’est clairement quand même un peu lourd à écrire. Mais c’est sans doute une question d’habitude plus qu’autre chose. En tout cas je suis prêt à tenter un peu. Prenons un exemple avec quelque chose qui piège pas mal d’étudiant.e.s débutant.e.s: la différence entre un LEFT JOIN et un INNER JOIN.

Imaginons un schéma avec trois tables: pizza, garniture et garniture_pizza. Une pizza aura un identifiant, un nom et un prix. Une garniture aura un identifiant et un nom. Et on fera le lien entre les deux pour pouvoir associer des pizzas à leurs garniture(s):

BEGIN;
CREATE TABLE IF NOT EXISTS pizza (
    id_pizza int,
    nom varchar(50) UNIQUE,
    prix float not null,
    PRIMARY KEY (id_pizza)
);
CREATE TABLE IF NOT EXISTS garniture (
    id_garniture int PRIMARY KEY,
    nom varchar(50) UNIQUE
);
CREATE TABLE IF NOT EXISTS garniture_pizza(
    id_pizza int,
    id_garniture int,
    PRIMARY KEY(id_pizza, id_garniture)
);
COMMIT;

Pour respecter les principes du “test driven development”, on va commencer par écrire les tests avant d’écrire la requête. Imaginons qu’on cherche à récupérer le nombre de pizzas dans laquelle chaque garniture est utilisée. Quels sont les différents cas à tester?

Une garniture g est utilisée sur n>=1 pizzas -> on doit voir aparraître le tuple (g, n) dans la réponse.
Une garniture g n’est utilisée sur aucune pizza -> on doit voir apparaître le tuple (g, 0) dans la réponse.
Une pizza p n’a aucun ingrédient -> elle ne doit pas affecter le résultat de la requête.

J’ai donc besoin d’insérer dans mes données, au minimum:

Trois pizzas (1, 2, 3)
Deux garnitures (1, 2)
Liens (pizza, garniture): ((1, 1), (2, 1))

On pourrait ne mettre que deux pizzas, mais le nombre de pizzas serait alors égal au nombre de garniture, ce qui peut rendre des tests sur le “nombre de résultats obtenus” moins évidents.

Mettons ça dans une procédure, en commençant par supprimer toutes les données (pour s’assurer qu’on n’a bien uniquement nos données de test), et en terminant par un rollback (pour remettre la base de données dans son état initial). Il va de soi que je ne mettrais pas cette procédure dans une base de données en production, on va éviter de supprimer toutes les données par erreur!

CREATE OR REPLACE PROCEDURE test_nb_pizzas_par_garniture() AS $$
    BEGIN
        -- supprimer toutes les données
        TRUNCATE pizza, garniture, garniture_pizza;

        -- données minimales pour le test
        INSERT INTO pizza VALUES (1, 'pizza 1', 0),
                                 (2, 'pizza 2', 0),
                                 (3, 'pizza 3', 0);
        INSERT INTO garniture VALUES (1, 'garniture 1'),
                                     (2, 'garniture_2');
        INSERT INTO garniture_pizza VALUES (1, 1),
                                           (2, 1);

        ROLLBACK;
    END;
$$ LANGUAGE plpgsql;

call test_nb_pizzas_par_garniture();

Si on exécute ceci dans une base PostgreSQL, on va: - supprimer les données existantes. - ajouter les données de test. - annuler tout et remettre la base dans son état pré-exécution.

On doit maintenant ajouter la requête et les tests. Pour commencer, on imagine que notre requête se trouve dans une fonction nb_pizzas_par_garniture, qu’on définira plus tard. On peut alors récupérer les résultats de cette requête dans notre procédure de test avec:

CREATE TEMP TABLE result ON COMMIT DROP AS
SELECT * FROM nb_pizzas_par_garniture();

On stocke ainsi le résultat dans une table temporaire, qui sera éliminée à la fin de la transaction. On peut maintenant passer aux tests. La syntaxe des ASSERT est: ASSERT [expr][, msg], où si l’expression est fausse on affiche une erreur et, optionnellement, le message. On doit tester:

Que l’on trouve 2 garnitures
Que la garniture 1 apparaît sur 2 pizzas
Que la garniture 2 apparaît sur 0 pizzas

Traduisons cela en SQL:

-- 2 garnitures trouvées
ASSERT (SELECT COUNT(*) FROM result) = 2,
    '2 garnitures attendues, reçu ' || (SELECT COUNT(*) FROM result)::text;
-- garniture 1 -> 2 pizzas
ASSERT (SELECT nb_pizzas FROM result WHERE id_garniture = 1) = 2,
    '2 pizzas attendues pour la garniture 1, reçu ' || (SELECT nb_pizzas FROM result WHERE id_garniture = 1)::text;
-- garniture 2 -> 0 pizzas
ASSERT (SELECT nb_pizzas FROM result WHERE id_garniture = 2) = 0,
    '0 pizzas attendues pour la garniture 2, reçu ' || (SELECT nb_pizzas FROM result WHERE id_garniture = 2)::text;

La syntaxe n’est pas la plus lisible, mais ça passe. || permet de concaténer des chaînes de caractères, et ::text de caster les entiers de nb_pizzas en chaînes de caractères. Commençons par écrire une requête bidon pour vérifier que notre test fonctionne (sans oublier de la mettre dans une fonction):

CREATE OR REPLACE FUNCTION nb_pizzas_par_garniture()
RETURNS TABLE (
    id_garniture int,
    nb_pizzas int
) language sql as $$
-- LA REQUÊTE EST ICI:
SELECT 0, 0;
-- FIN DE LA REQUÊTE
$$;

On peut maintenant maintenant appeler la procédure de test: call test_nb_pizzas_par_garniture();, qui va bien planter dès le premier test en renvoyant le message: ERROR: 2 garnitures attendues, reçu 1.

Imaginons maintenant un.e étudiant.e qui utilise un INNER JOIN pour répondre à la question:

-- Mauvais JOIN
SELECT g.id_garniture, COUNT(gp.id_pizza)
FROM garniture g
INNER JOIN garniture_pizza gp ON gp.id_garniture = g.id_garniture
GROUP BY g.id_garniture

Il aura bien une erreur: ERROR: 2 garnitures attendues, reçu 1, car le INNER JOIN aura éliminé la garniture 2. S’iel fait un LEFT JOIN en laissant le reste identique, tous le tests passeront. S’il se trompe dans le COUNT et écrit:

-- Mauvais COUNT
SELECT g.id_garniture, COUNT(g.id_garniture)
FROM garniture g
LEFT JOIN garniture_pizza gp ON gp.id_garniture = g.id_garniture
GROUP BY g.id_garniture

Iel aura: ERROR: 0 pizzas attendues pour la garniture 2, reçu 1.

Le code complet avec la requête correcte:

CREATE OR REPLACE FUNCTION nb_pizzas_par_garniture()
RETURNS TABLE (
    id_garniture int,
    nb_pizzas int
) language sql as $$
-- LA REQUÊTE EST ICI:
SELECT g.id_garniture, COUNT(gp.id_garniture)
FROM garniture g
LEFT JOIN garniture_pizza gp ON gp.id_garniture = g.id_garniture
GROUP BY g.id_garniture
-- FIN DE LA REQUÊTE
$$;

CREATE OR REPLACE PROCEDURE test_nb_pizzas_par_garniture() AS $$
    BEGIN
        -- supprimer toutes les données
        TRUNCATE pizza, garniture, garniture_pizza;

        -- données minimales pour le test
        INSERT INTO pizza VALUES (1, 'pizza 1', 0),
                                 (2, 'pizza 2', 0),
                                 (3, 'pizza 3', 0);
        INSERT INTO garniture VALUES (1, 'garniture 1'),
                                     (2, 'garniture_2');
        INSERT INTO garniture_pizza VALUES (1, 1),
                                           (2, 1);

        CREATE TEMP TABLE result ON COMMIT DROP AS
        SELECT * FROM nb_pizzas_par_garniture();

        -- 2 garnitures trouvées
        ASSERT (SELECT COUNT(*) FROM result) = 2,
            '2 garnitures attendues, reçu ' || (SELECT COUNT(*) FROM result)::text;
        -- garniture 1 -> 2 pizzas
        ASSERT (SELECT nb_pizzas FROM result WHERE id_garniture = 1) = 2,
            '2 pizzas attendues pour la garniture 1, reçu ' || (SELECT nb_pizzas FROM result WHERE id_garniture = 1)::text;
        -- garniture 2 -> 0 pizzas
        ASSERT (SELECT nb_pizzas FROM result WHERE id_garniture = 2) = 0,
            '0 pizzas attendues pour la garniture 2, reçu ' || (SELECT nb_pizzas FROM result WHERE id_garniture = 2)::text;


        ROLLBACK;
    END;
$$ LANGUAGE plpgsql;

call test_nb_pizzas_par_garniture();

Est-ce que c’est beaucoup de boulot juste pour tester une requête? Oui, mais je peux voir des cas où cela vaudrait la peine. Par exemple dans un examen ou une interro, pouvoir valider rapidement que les résultats attendus ont été obtenus peut éviter des erreurs de correction.

Évidemment, ces tests ne détecteront pas les codes sous-optimaux. Par exemple, je pourrais ici ajouter une jointure inutile à pizza et compter sur p.id_pizza, et les tests passeront.

Mais je trouve que l’idée reste intéressante.

[lien permanent]

Commentaires, remarques, erreurs qu'il faut absolument me faire remarquer? Contactez-moi sur Mastodon ou par mail (adrien@adfoucart.be)

🗄️2025.10.17tests postgres

🗄️2025.10.17
tests postgres