Comment l'algèbre relationnelle fait tourner Klaro Cards

Il y a quelques années, quand j'ai rejoint Klaro Cards, j'ai écrit en termes généraux mon enthousiasme pour la façon dont l'algèbre relationnelle est utilisée dans la base de code de Klaro. Aujourd'hui, j'aimerais vous montrer concrètement comment cela fonctionne.

Ce ne sont pas les wrappers de bases de données, les Object Relational Mappers (ORM) et autres outils censés faciliter le travail avec les bases SQL qui manquent. J'ai aussi remarqué une certaine mode des langages « comme SQL mais en mieux » ces derniers temps. Malheureusement, très peu de ces projets tirent parti des fondations solides du modèle relationnel sur lequel reposent les bases de données relationnelles. S'ils le faisaient, ils découvriraient que pratiquement tout ce dont nous avons besoin est déjà là.

La librairie Bmg, que nous utilisons pour Klaro Cards, emprunte bien cette voie, et elle n'est pas seulement plus puissante qu'un wrapper de base de données classique : c'est une véritable amélioration de SQL. Bmg implémente une algèbre relationnelle, c'est-à-dire un modèle mathématique extrêmement solide pour travailler avec des données relationnelles. Voyons à quoi cela ressemble en pratique.

Notre exemple

Quand vous êtes connecté à Klaro Cards, vous voyez sur la gauche une rangée d'icônes indiquant les projets dont vous êtes membre. Cette liste est triée par date de dernière interaction avec chaque projet, la plus récente en haut. La tâche est donc la suivante :

Obtenir les projets dont l'utilisateur courant est membre, dans l'ordre de l'interaction la plus récente.

Deux tables sont impliquées : project_events et projects. project_events est un journal d'événements enregistrant les interactions des utilisateurs avec les projets : invitation reçue, invitation acceptée, ajout ou modification de cartes, etc. projects contient les informations de projet que nous voulons afficher. C'est assez direct, mais pas entièrement trivial en SQL, ni donc dans la plupart des wrappers SQL ou des ORM. Déroulons le processus avec l'algèbre relationnelle et Bmg.

Obtenir une relation

Bmg.sequel(:project_events, SEQUEL_DATABASE)
  .restrict(user: requester.id)

La première chose à noter ici, c'est que nous spécifions une source particulière de relations : une base de données relationnelle accédée via la librairie Ruby Sequel. Nous aurions tout aussi bien pu spécifier un fichier CSV ou un tableur, et toutes les mêmes opérations auraient été disponibles. Dans Bmg, c'est toujours « des relations en entrée, des relations en sortie ».

La seconde ligne est simple : nous limitons la relation aux tuples/lignes où user est l'utilisateur courant. Rien de très sophistiqué, mais regardons le SQL produit par Bmg (avec l'aide de la librairie Sequel) :

SELECT
  "t1"."project",
  "t1"."user",
  "t1"."timestamp",
  "t1"."eventType",
  "t1"."nickname",
  ...
FROM
  "project_events" AS "t1"
WHERE
  "t1"."user" = 123

Assez classique. Mais remarquez aussi ce que nous n'avons pas eu à faire : nous n'avons pas eu à mettre en place des classes, des entités ou une quelconque structure qui restreindrait ce que nous allons faire des données. Nous ne sommes pas forcés de voir nos données à travers une forme canonique, et c'est en fait l'une des motivations originelles du modèle relationnel, il y a un demi-siècle.

Projection

Et ensuite ? Nous n'avons pas besoin de tous ces attributs. Projetons la relation sur un sous-ensemble d'attributs :

Bmg.sequel(:project_events, SEQUEL_DATABASE)
  .restrict(user: requester.id)
  .project([:project, :timestamp])

Nous n'avons besoin que de project et timestamp. Regardons la requête SQL générée :

SELECT DISTINCT
  "t1"."project",
  "t1"."timestamp"
FROM
  "project_events" AS "t1"
WHERE
  "t1"."user" = 123

Voici quelque chose d'intéressant : Bmg infère que cette requête peut retourner des lignes en doublon, parce qu'une partie de la clé primaire a été omise, à savoir l'attribut nickname qui, avec project, forme la clé primaire :

project_events_pkey PRIMARY KEY, btree (project, nickname)

Une vraie relation a une sémantique d'ensemble, ce qui signifie qu'il ne peut pas y avoir de doublons. Comme le dit l'adage : dire une chose deux fois ne la rend pas plus vraie. Dans notre exemple, si l'utilisatrice 123 a interagi avec son projet blog le lundi à 17h21, nous n'avons besoin que d'un seul tuple (blog, lundi 17h21), même si elle a d'une manière ou d'une autre eu plusieurs activités de projet exactement au même instant. Bmg insère donc la condition selon laquelle nous ne voulons qu'un ensemble de tuples aux valeurs distinctes. En fait, Bmg spécifie toujours la condition DISTINCT, sauf quand il peut inférer des clés primaires ou des index uniques que ce n'est pas nécessaire.

Prédicats

Ensuite, nous voulons restreindre encore la relation :

Bmg.sequel(:project_events, SEQUEL_DATABASE)
  .restrict(user: requester.id)
  .restrict(Predicate.neq(:eventType, 'destroy') | Predicate.neq(:cancelledAt, NULL))
  .project([:project, :timestamp])

Nous ajoutons une condition sur les tuples sélectionnés : nous voulons filtrer les événements de type destroy, sauf s'ils sont également annulés. Remarquez comment le prédicat utilisé dans la nouvelle opération restrict est construit. La disjonction logique (OR) n'est pas traitée comme une fonctionnalité SQL, parce qu'elle n'en est pas une. C'est plutôt une opération fondamentale de la logique. Dans Bmg, nous pouvons construire des prédicats de manière naturelle et, puisqu'ils ont une structure propre, Bmg peut les compiler en SQL.

Il vaut la peine de noter que certains ORM peinent même à ce niveau de base. Par exemple, puisque leur raison d'être est de construire des objets à partir de lignes de données, ils peuvent vous forcer à créer une nouvelle classe de Data Transfer Objects juste pour pouvoir spécifier une liste de sélection (comme nous le faisons ici avec l'opération project). De même, puisque les ORM essaient généralement de faire correspondre chaque appel de méthode directement à une clause ou un opérateur SQL, construire des conditions complexes (comme la disjonction de notre second restrict) peut devenir laborieux. Les bénéfices de Bmg ne tiennent pas à diverses commodités ou à une syntaxe modernisée : ils découlent tous d'une vue algébrique complètement unifiée des données.

Avant de continuer, voyons où nous en sommes côté SQL généré :

SELECT DISTINCT
  "t1"."project",
  "t1"."timestamp"
FROM
  "project_events" AS "t1"
WHERE
  "t1"."user" = 123
AND (
  ("eventType" != "destroy") OR ("cancelledAt" IS NOT NULL) 
)

Résumons !

Essayons maintenant quelque chose d'un peu plus compliqué. Nous voulons les tuples ordonnés par timestamp, vous vous souvenez ? Dans Bmg, cela se fait avec l'opération summarize :

Bmg.sequel(:project_events, SEQUEL_DATABASE)
  .restrict(user: requester.id)
  .restrict(Predicate.neq(:eventType, 'destroy') | Predicate.neq(:cancelledAt, NULL))
  .project([:project, :timestamp])
  .summarize([:project], :timestamp => :max)

On peut comprendre cela comme le fait d'extraire l'attribut project de la relation, et de l'apparier avec une valeur calculée dérivée de cette même relation. Ici, nous voulons, pour chaque project, obtenir le plus grand timestamp, autrement dit le moment le plus récent où l'utilisateur s'est activement engagé dans le projet.

En SQL, cela se traduit par une fonction d'agrégation accompagnée d'une clause GROUP BY :

SELECT DISTINCT
  "t1"."project",
  max("t1"."timestamp") AS "timestamp"
FROM
  "project_events" AS "t1"
WHERE
  "t1"."user" = 123
AND (
  ("eventType" != "destroy") OR ("cancelledAt" IS NOT NULL) 
GROUP BY
  "t1"."project"

Là encore, summarize n'est pas qu'un raccourci malin pour SELECT ... agrégation ... GROUP BY. C'est une opération algébrique qui envoie une relation sur une autre relation. Tout l'intérêt ici est de penser en relations, là où les librairies ORM vous font passer votre temps à réfléchir à la façon d'assembler du code qui produira le SQL que vous auriez voulu écrire.

Jointure

Allons maintenant chercher les véritables attributs de projet pour les tuples soigneusement sélectionnés. Pour cela, assignons d'abord une variable à la relation que nous avons déjà construite :

events = Bmg.sequel(:project_events, SEQUEL_DATABASE)
  .restrict(user: requester.id)
  ...

Il nous faut ensuite une autre relation sur laquelle joindre :

projects = Bmg.sequel(:projects, SEQUEL_DATABASE)

Et joindre les relations est aussi simple que :

projects.join(events, :id => :project)

Rappelez-vous : nous joignons une relation simple (projects) avec une relation que nous avons construite en restreignant, projetant et résumant une relation simple (project_events). Comment cela se traduit-il en SQL ?

WITH "t3" AS (
  SELECT DISTINCT
    "t2"."project" AS "id",
    max("t2"."timestamp") AS "timestamp"
  FROM
    "project_events" AS "t2"
  WHERE
    "t2"."user" = 123
  AND (
    ("eventType" != "destroy") OR ("cancelledAt" IS NOT NULL) 
  GROUP BY
    "t2"."project"
)
SELECT
  "t1"."id",
  "t1"."name",
  "t1"."subdomain",
  "t1"."owner",
  -- Beaucoup d'attributs
  -- ...
  "t3"."timestamp"
FROM
  "projects" AS "t1"
INNER JOIN "t3" ON ("t1"."id" = "t3"."id")

Ouh là, que vient-il de se passer ? Toute la relation events est désormais enveloppée dans une Common Table Expression (entre les parenthèses qui suivent WITH "t3" AS) — une sorte de table temporaire ou de sous-requête qui mime la relation dérivée que nous avons construite dans Bmg. Normalement, Bmg essaie de fusionner les relations en une seule expression SQL, mais dans ce cas il a déterminé que ce n'était pas possible, puisque l'une d'elles contient une clause GROUP BY. Le principe crucial, encore une fois, est que les relations peuvent toujours être modifiées, combinées et recombinées, produisant de nouvelles relations. Comme le dit la documentation de Bmg :

Bmg permet toujours de chaîner les opérateurs. Si ce n'est pas le cas, c'est un bug.

Oui, Bmg peut compiler des relations en SQL, mais ce n'est pas une amélioration à moitié cuite de SQL qui finirait par être pire que lui. Au lieu de vous forcer à faire de la rétro-ingénierie sur le SQL que vous auriez voulu écrire au départ, Bmg nous permet de penser et de coder en termes de relations. C'est tout simplement le modèle relationnel tel qu'il devait être.

Conclusion

Le lecteur attentif (bonjour !) aura remarqué que nous n'avons atteint que la moitié de notre objectif annoncé :

Obtenir les projets dont l'utilisateur courant est membre, dans l'ordre de l'interaction la plus récente.

Il n'y a aucune opération d'ordonnancement ici, donc nous ne retournons toujours pas les projets triés. Comment cela se fait-il ? Là encore, les relations ont une sémantique d'ensemble et, tout comme elles ne peuvent pas contenir de doublons, elles ne sont pas ordonnées. Les tuples produits par ce code contiennent bien l'attribut timestamp nécessaire pour les ordonner, mais le tri lui-même a lieu en dehors de Bmg.

Il serait merveilleux de disposer d'une forme d'algèbre relationnelle intégrée aux bases de données relationnelles. Mais pour l'instant, Bmg est à ma connaissance la seule implémentation d'algèbre relationnelle utilisée en production et éprouvée pendant plusieurs années. Si vous êtes utilisateur de Ruby, vous devriez l'essayer ! Vous n'aurez plus jamais à choisir entre du SQL bancal et des wrappers SQL bancals.