Le classement : une fonction SQL

Écrire la fonction meilleurs_scores, qui permet à tous les joueurs de voir les meilleures parties sans jamais exposer de données personnelles.

Objectifs de la séance

• comprendre pourquoi le classement ne peut pas se lire directement dans la table partie ;
• écrire une fonction SQL publique qui renvoie un tableau de résultats (returns table) ;
• comparer des valeurs qui peuvent être nulles avec is not distinct from ;
• trier selon plusieurs critères et plafonner le nombre de lignes renvoyées ;
• ne renvoyer que des informations sans risque (le pseudo, jamais l'identifiant du joueur).

---

Petite vérification

Avant de commencer, vérifiez si vous savez...

1. jouer une partie de 3 questions (thème « Réseaux et Wi-Fi », niveau Débutant) jusqu'à l'écran de résultat, puis lire le score et le temps affichés, et recharger la page (touche F5 dans le navigateur) pour vérifier que le score ne change pas
2. retrouver votre partie dans Supabase > Table Editor, table partie, et vérifier que les colonnes terminee_le, score et duree_secondes sont remplies
3. citer le nom de la fonction SQL qui calcule le score côté serveur, et dire ce que signifie le mot « idempotente » pour cette fonction

📌 Réponse attendue

Vous devez être connecté pour voir le contenu.

Email : Mot de passe :

---

Notions théoriques

Pourquoi une fonction pour le classement ?

Le classement doit être public : n'importe quel joueur, même sans pseudo, peut le consulter. Or les règles de sécurité (RLS) mises en place au tutoriel 8 interdisent à un joueur de lire les parties des autres : la table partie ne lui montre que ses lignes, et la table joueur ne lui montre que sa ligne.

Deux mauvaises solutions s'offrent alors :

• ouvrir la lecture de partie et de joueur à tout le monde : n'importe qui pourrait alors lire les identifiants (uuid) de tous les joueurs, et toutes les colonnes ;
• calculer le classement dans l'application : il faudrait lui donner accès à toutes les parties, ce qui revient à la même faille.

La bonne solution est la même que pour demarrer_partie et terminer_partie : une fonction SQL qui s'exécute sur le serveur et qui ne renvoie que ce que l'on a décidé de montrer : le pseudo, le score, la durée et la date. Rien d'autre.

Une fonction SQL qui renvoie un tableau

Jusqu'ici, vos fonctions (language plpgsql) renvoyaient un seul objet JSON. Pour un classement, on veut plusieurs lignes et plusieurs colonnes : on écrit returns table (...), qui décrit les colonnes du résultat, comme une table.

create or replace function public.meilleurs_scores(
  p_theme_code text,
  p_niveau     integer,
  p_limite     integer default 20
)
returns table (
  pseudo         text,
  score          smallint,
  nb_questions   smallint,
  duree_secondes integer,
  terminee_le    timestamptz
)
language sql
stable
security definer
set search_path = ''
as $$
  -- ... une seule requête select ...
$$;

• language sql : le corps de la fonction est une requête SQL (pas de variables ni de if). C'est plus simple et plus rapide que plpgsql quand une seule requête suffit.
• stable : la fonction ne modifie rien dans la base. PostgreSQL peut ainsi l'optimiser.
• p_limite integer default 20 : un paramètre facultatif (valeur par défaut). Le préfixe p_ distingue les paramètres des colonnes.
• $$ ... $$ : les deux signes dollar encadrent le corps de la fonction. Cela évite de doubler les apostrophes à l'intérieur.

security definer, search_path et droits d'exécution

Rappel de la séance sur les fonctions RPC :

• security definer : la fonction s'exécute avec les droits de son propriétaire (et non de celui qui l'appelle). Elle peut donc lire toutes les parties, même si la RLS les cache à l'appelant. C'est le seul moyen de contourner la RLS, de façon contrôlée : la fonction décide de ce qu'elle renvoie.
• set search_path = '' : empêche un attaquant de faire utiliser à la fonction un objet portant le même nom, placé dans un autre schéma. C'est pourquoi toutes les tables sont écrites avec leur schéma (public.partie).
• revoke execute ... from public puis grant execute ... to anon, authenticated : par défaut, tout le monde peut exécuter une fonction ; on retire ce droit, puis on le redonne explicitement aux deux rôles voulus. Ici, anon (visiteur non connecté) et authenticated (joueur connecté) peuvent lire le classement.

Une fonction security definer doit toujours se méfier de ses paramètres

Une fonction security definer a plus de droits que son appelant. Ne renvoyez jamais plus que nécessaire, et vérifiez ou bornez chaque paramètre. Ici, la limite de lignes est plafonnée à 50 : sans cela, un appel avec p_limite = 1000000 obligerait le serveur à renvoyer toute la table.

Comparer avec null : is not distinct from

Dans la table partie, theme_code vaut NULL quand la partie a été jouée avec « Tous les thèmes » (et niveau vaut NULL pour « Tous les niveaux »). Côté application, le mot "tous" devient null dans la requête.

Le piège : en SQL, null = null ne donne pas « vrai », mais « inconnu » (null), et une ligne dont la condition est « inconnue » est rejetée. L'opérateur is not distinct from traite null comme une valeur comme les autres.

a	b	a = b	a is not distinct from b
1	1	vrai	vrai
1	2	faux	faux
null	null	inconnu (ligne rejetée)	vrai
1	null	inconnu (ligne rejetée)	faux

Ainsi, p.theme_code is not distinct from p_theme_code fonctionne dans les deux cas : un thème précis ('reseaux') ou « tous » (null).

Trier selon plusieurs critères

Une clause order by peut contenir plusieurs critères, séparés par des virgules : le suivant ne sert qu'à départager les égalités du précédent.

1. le meilleur pourcentage de réussite (score / nb_questions) ;
2. à pourcentage égal, le plus grand nombre de bonnes réponses ;
3. à score égal, la partie la plus rapide (duree_secondes croissante) ;
4. enfin, la première arrivée (terminee_le croissante).

Pourquoi ::numeric ?

score et nb_questions sont des entiers : en SQL, 2 / 3 entre deux entiers vaut 0 (division entière). Écrire score::numeric / nb_questions convertit d'abord score en nombre décimal : le résultat vaut alors 0.666.... L'écriture valeur::type s'appelle un cast (une conversion de type).

Plafonner le nombre de lignes

limit fixe le nombre maximal de lignes renvoyées. Pour que l'application ne puisse pas demander n'importe quoi, la fonction borne elle-même la valeur reçue :

limit least(greatest(coalesce(p_limite, 20), 1), 50)

On lit de l'intérieur vers l'extérieur :

• coalesce(p_limite, 20) : si p_limite vaut null, on prend 20 ;
• greatest(..., 1) : au minimum 1 ;
• least(..., 50) : au maximum 50.

Ne jamais exposer plus que nécessaire (RGPD)

Le pseudo est le seul élément d'identité publié. La fonction ne renvoie ni l'identifiant (uuid) du joueur, ni son adresse (il n'y en a pas : la connexion est anonyme). Un classement public est une publication de données : on applique le principe de minimisation du RGPD, qui dit de ne collecter et de ne diffuser que les données strictement nécessaires.

---

TP pour réfléchir et résoudre des problèmes

Votre mission : écrire le script sql/09_classement.sql, l'exécuter dans Supabase, puis le tester, d'abord dans l'éditeur SQL, puis comme le ferait un visiteur anonyme. Ce tutoriel se fait sur sa propre branche Git.

Étape 1 — Créer la branche du tutoriel

Comme pour les tutoriels précédents, on ne travaille pas directement sur main. Revenez sur main, mettez-la à jour, puis créez la branche feature/classement (le -c signifie create) :

git switch main
git pull

Bonne pratique - Une branche par ensemble de changements

Si le classement ne fonctionne pas comme prévu, main reste intacte : la branche n'est fusionnée qu'une fois le travail vérifié.

Étape 2 — Déclarer les paramètres de la fonction

Créez le fichier sql/09_classement.sql, avec d'abord un commentaire d'en-tête, puis le début de la fonction :

-- =====================================================================
-- 09_classement.sql : meilleurs scores pour un thème et un niveau
-- =====================================================================
-- Le classement est PUBLIC (même sans être connecté) : la fonction ne
-- renvoie que des informations sans risque (pseudo, score, durée, date),
-- jamais l'identifiant (uuid) des joueurs.
-- p_theme_code / p_niveau à null = parties jouées avec "Tous".

Complétez la ligne qui déclare le troisième paramètre, facultatif, avec la valeur 20 par défaut :

Bonne pratique - Préfixer les paramètres

Le préfixe p_ évite toute confusion entre un paramètre (p_niveau) et une colonne (niveau) dans la requête. Sans lui, PostgreSQL pourrait signaler un nom « ambigu ».

Étape 3 — Décrire les colonnes renvoyées et les options

Ajoutez, à la suite, les colonnes du résultat et les options de la fonction :

returns table (
  pseudo         text,
  score          smallint,
  nb_questions   smallint,
  duree_secondes integer,
  terminee_le    timestamptz
)

Puis les options. Complétez d'abord le mot qui permet à la fonction de lire les parties de tous les joueurs :

Complétez maintenant la ligne qui verrouille le chemin de recherche des objets :

Bonne pratique - Toujours fixer le search_path d'une fonction security definer

Sans cette ligne, un utilisateur malveillant pourrait créer, dans un autre schéma, une table du même nom que celles de la fonction : celle-ci s'exécuterait alors avec les droits de son propriétaire sur la mauvaise table. Avec search_path = '', tout objet doit être écrit avec son schéma (public.partie).

Étape 4 — Écrire la requête : sélection, jointure et filtres

Voici le corps de la fonction (début) :

  select j.pseudo, p.score, p.nb_questions, p.duree_secondes, p.terminee_le
  from public.partie p
  join public.joueur j on j.id = p.joueur_id
  where p.terminee_le is not null
    -- "is not distinct from" : comme "=", mais null est égal à null
    and p.theme_code is not distinct from p_theme_code
    and p.niveau is not distinct from p_niveau::smallint

La jointure (join) relie chaque partie à son joueur pour récupérer le pseudo. La première condition élimine les parties inachevées (celles dont terminee_le est vide). Complétez-la :

Complétez ensuite la condition sur le niveau, sur le modèle de celle du thème. Le paramètre p_niveau est un integer, alors que la colonne niveau est un smallint : on convertit donc le paramètre avec ::smallint.

Bonne pratique - Ne jamais renvoyer joueur_id

Remarquez la première ligne du select : elle ne contient ni j.id ni p.joueur_id. Le pseudo suffit pour afficher un classement. Si l'identifiant était renvoyé, un visiteur pourrait suivre un même joueur d'une partie à l'autre, ou le retrouver ailleurs.

Étape 5 — Trier les parties

Ajoutez la clause de tri, avec les quatre critères dans l'ordre. Complétez le mot qui convertit le score en nombre décimal avant la division :

Bonne pratique - Un tri déterministe

Le dernier critère (terminee_le asc) départage même deux parties identiques à la seconde près : l'ordre est toujours le même d'un appel à l'autre. Sans lui, le classement pourrait changer d'ordre à chaque actualisation.

Étape 6 — Plafonner le nombre de lignes

Terminez la requête par la limite, puis fermez le corps de la fonction avec $$;. Complétez la ligne qui borne p_limite entre 1 et 50 :

Bonne pratique - Ne pas faire confiance au client

L'application demandera toujours 20 lignes, mais rien n'empêche quelqu'un d'appeler la fonction lui-même avec n'importe quelle valeur. La borne doit donc être appliquée par le serveur : c'est le même principe que pour le score.

Étape 7 — Régler les droits d'exécution

Ajoutez les deux dernières lignes du script. Elles retirent le droit d'exécution à tout le monde, puis le donnent à anon et authenticated :

Bonne pratique - Le principe du moindre privilège

On retire d'abord tous les droits, puis on donne seulement ceux qui sont nécessaires. Ici, anon et authenticated n'ont que le droit d'exécuter cette fonction : ils n'ont toujours aucun droit de lecture sur les tables partie et joueur.

Étape 8 — Exécuter et tester le script

1. Dans Supabase > SQL Editor, ouvrez une nouvelle requête, collez tout le contenu de sql/09_classement.sql et cliquez sur Run. Le message « Success. No rows returned » confirme que la fonction est créée.
2. Dans une nouvelle requête, appelez la fonction pour tous les thèmes et tous les niveaux :

Le résultat ne contient que vos parties terminées (et celles de vos camarades si vous partagez la même base). Vérifiez trois points :

• les colonnes sont pseudo, score, nb_questions, duree_secondes et terminee_le : aucun identifiant ;
• le même appel avec 'reseaux', 1 ne montre que les parties « Réseaux et Wi-Fi » jouées en niveau Débutant ;
• avec 100000 comme limite, le nombre de lignes ne dépasse jamais 50.

3. Testez enfin la fonction comme un visiteur anonyme, depuis un terminal (remplacez l'adresse du projet et la clé publishable par les vôtres, celles de votre fichier .env.local) :

curl -X POST "https://votre-projet.supabase.co/rest/v1/rpc/meilleurs_scores" \
  -H "apikey: sb_publishable_xxxxxxxxxxxxxxxxxxxx" \
  -H "Content-Type: application/json" \
  -d '{"p_theme_code": null, "p_niveau": null, "p_limite": 5}'

La réponse est un tableau JSON de 5 parties au plus. Les noms des paramètres du corps (p_theme_code, p_niveau, p_limite) sont exactement ceux de la fonction.

Sous Windows

Si le terminal de VS Code est PowerShell, utilisez plutôt le terminal Git Bash, ou remplacez curl par curl.exe et écrivez la commande sur une seule ligne (sans les \), en doublant les guillemets du JSON.

Bonne pratique - Tester comme un utilisateur sans droits

Dans l'éditeur SQL, vous êtes administrateur : tout fonctionne. Le test avec curl et la clé publishable utilise le rôle anon, le même que celui d'un visiteur : c'est lui qui prouve que les droits d'exécution sont bien réglés.

Étape 9 — Enregistrer votre travail

Ajoutez une ligne au tableau du fichier sql/README.md pour documenter le nouveau script :

| `09_classement.sql` | fonction publique `meilleurs_scores` (classement) |

Puis enregistrez le travail dans un commit et envoyez-le sur GitHub. Comme la branche feature/classement n'existe pas encore sur GitHub, le premier envoi se fait avec -u :

Bonne pratique - Versionner les scripts SQL

Un script SQL exécuté « à la main » dans un navigateur est vite oublié. Gardé dans le dépôt, il permet de reconstruire la base de données de zéro, de relire les changements et de les faire relire par un collègue.

📌 Une solution

Vous devez être connecté pour voir le contenu.

Email : Mot de passe :

---

Test de mémorisation/compréhension

Pourquoi la fonction meilleurs_scores est-elle déclarée security definer ?

Pour que le résultat soit chiffré avant d'être envoyé à l'application mobile

Pour accélérer la requête grâce à un index créé automatiquement

Pour qu'elle puisse lire les parties de tous les joueurs malgré la RLS

Pour empêcher les visiteurs non connectés d'appeler la fonction

Pour que PostgreSQL mémorise le résultat entre deux appels successifs

Que vaut la condition (null = null) en SQL ?

Vrai

Faux

Une erreur de syntaxe est signalée par PostgreSQL à l'exécution de la requête

Zéro, comme pour une division entière

Inconnu (null), donc la ligne est rejetée

Quel opérateur considère que deux valeurs null sont égales ?

= null (avec le signe égal suivi du mot null)

is not distinct from

like (le même opérateur que pour chercher un motif dans un texte)

coalesce

is null or =

Pourquoi écrit-on p.score::numeric / p.nb_questions dans le tri ?

Pour que la division donne un résultat décimal (2/3 vaut 0.666 et non 0)

Pour que le score soit affiché avec deux décimales dans l'application

Pour trier les parties par ordre alphabétique des pseudos

Pour convertir le score en texte avant de le comparer aux scores des autres joueurs

Pour éviter une division par zéro lorsqu'une partie n'a aucune question

Que fait limit least(greatest(coalesce(p_limite, 20), 1), 50) ?

Il renvoie toujours exactement 50 lignes

Il prend p_limite, ou 20 s'il est nul, et le ramène entre 1 et 50

Il refuse l'appel avec une erreur si p_limite dépasse 50 lignes demandées

Il trie les 50 meilleures parties par ordre décroissant de score

Il remplace p_limite par 1 lorsque aucun thème n'est choisi

Quelle information la fonction ne doit-elle JAMAIS renvoyer ?

Le pseudo du joueur

La durée de la partie en secondes

Le score obtenu

La date de fin de la partie

L'identifiant (uuid) du joueur

---

Exercices complémentaires

Exercice A : un thème qui n'existe pas

Dans l'éditeur SQL, exécutez select * from public.meilleurs_scores('pirate', 1, 5);. Que renvoie la fonction ? Pourquoi n'y a-t-il pas d'erreur ?

📌 Réponse attendue

Vous devez être connecté pour voir le contenu.

Email : Mot de passe :

Exercice B : qui est premier ?

Deux parties sont terminées : la partie X (2 bonnes réponses sur 3, en 20 secondes) et la partie Y (4 bonnes réponses sur 6, en 90 secondes). Laquelle apparaît en premier dans le classement ? Justifiez avec les critères du tri.

📌 Réponse attendue

Vous devez être connecté pour voir le contenu.

Email : Mot de passe :

---

Ce qu'il faut retenir

Notion	Résumé
returns table (...)	Une fonction peut renvoyer plusieurs lignes et colonnes, comme une table.
language sql	Le corps est une seule requête : plus simple qu'une fonction plpgsql quand cela suffit.
security definer	La fonction s'exécute avec les droits de son propriétaire : elle contourne la RLS de façon contrôlée.
set search_path = ''	Obligatoire avec security definer : empêche l'usage d'un objet homonyme dans un autre schéma.
is not distinct from	Comparaison qui considère null égal à null (contrairement à =).
order by multi-critères	Chaque critère départage les égalités du précédent ; le dernier rend le tri déterministe.
::numeric (cast)	Convertit un type : évite la division entière (2 / 3 vaut 0).
limit plafonné	Le serveur borne lui-même la limite demandée, sans faire confiance à l'application.
Minimisation (RGPD)	Un classement public ne montre que le pseudo, le score, la durée et la date, jamais un identifiant.

---

Aperçu de la prochaine séance

La fonction meilleurs_scores fonctionne, mais seuls ceux qui ont accès à l'éditeur SQL peuvent la voir. Dans la prochaine séance, vous allez l'appeler depuis l'application : un service, un hook, un composant pour une ligne et un nouvel écran qui affiche le classement dans une liste (FlatList), avec des filtres par thème et par niveau.