Home > Sysadmin > Du modèle EAV, ou comment s’arracher les cheveux, pour plein de raisons

Du modèle EAV, ou comment s’arracher les cheveux, pour plein de raisons

January 2nd, 2012 Leave a comment Go to comments

Dans le cadre du projet SOIF (qui fera l’objet d’un prochain billet), je travaille actuellement sur un job de transformation d’une base Puppet facts pour injecter dans iTop.

Avant tout, un peu de contexte : Puppet intègre la commande facter qui renvoie tout un ensemble d’informations sous la forme attribut=valeur, et qui est facilement extensible, bref c’est très intéressant. Là où le bat blesse, c’est que – logiquement – ces informations sont stockées en base de données en utilisant le modèle de conception EAV pour Entity Attribute Value.

Ce qui donne :

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   | MUL | NULL    |                |
| updated_at | datetime     | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

et

mysql> desc fact_values;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| id           | int(11)  | NO   | PRI | NULL    | auto_increment |
| value        | text     | NO   |     | NULL    |                |
| fact_name_id | int(11)  | NO   | MUL | NULL    |                |
| host_id      | int(11)  | NO   | MUL | NULL    |                |
+--------------+----------+------+-----+---------+----------------+

Donc, pour obtenir par exemple la fact serialnumber pour un hôte donné, cela donne quelque chose du genre :


select fact_values.value FROM fact_values LEFT JOIN hosts ON fact_values.host_id = hosts.id LEFT JOIN fact_names ON fact_values.fact_name_id = fact_names.id WHERE hosts.name = 'xxx' AND fact_names.name = 'serialnumber';

Mais maintenant, retour à mon objectif : travailler par lot, c’est à dire pour tous les enregistrements de la table hosts. Se pose donc la question de comment faire pour obtenir la liste de tous les hôtes, avec la valeur serialnumber, à froid, pour l’expert SQL que je ne suis pas, mon premier jet à donné :

SELECT hosts.id, fact_values.value FROM hosts LEFT JOIN fact_values ON hosts.id = fact_values.host_id LEFT JOIN fact_names ON fact_names.id = fact_values.fact_name_id WHERE fact_names.name = 'serialnumber';

qui semble fonctionné à priori, mais, MAIS, cela ne renvoie que les enregistrements pour lesquels il existe la valeur serialnumber.

Après de nombreuses recherches, il semblerait que la seule solution à mon problème soit :

SELECT 
  hosts.id, 
  (SELECT fact_values.value
    FROM fact_values
    JOIN fact_names
     ON fact_names.id = fact_values.fact_name_id
    WHERE fact_names.name = 'serialnumber' 
     AND fact_values.host_id = hosts.id) as serialnumber,
FROM hosts;

et là, on le voit tout de suite, ça devient compliqué. Pourquoi ? parce que je n’ai pas seulement besoin du serialnumber mais de nombreux autres champs, et pour chaque champ que je veux, je dois donc rajouter un nouveau SELECT, je vous laisse deviner l’impact sur les performances.

Bref, EAV, ça pue. Bien évidemment si vous avez une autre solution, n’hésitez pas à commenter !

Categories: Sysadmin Tags: , ,
  1. Camille
    January 2nd, 2012 at 15:50 | #1

    Salut Asyd et bonne année 2012 :D

    J’ai pas pu m’empêcher de m’intéresser à ton post. Une des bases sur laquelle je tape est en EAV également et effectivement ça pue !

    En fait, ce que tu cherches n’est pas très clair. Si tu tiens absolument à avoir un select qui renvoie une colonne par attribut et la valeur NULL lorsque cet attribut n’a pas de valeur, effectivement il me semble que tu as la meilleure solution.

    En revanche si tu adaptes ton batch de façon à affecter une valeur par défaut à chaque paire host/attribut, tu n’as plus qu’à mettre à jour ces valeurs avec le résultat d’une simple requête du style :

    SELECT hosts.id, fact_names.name, fact_values.value FROM hosts INNER JOIN fact_values ON hosts.id = fact_values.host_id LEFT JOIN fact_names ON fact_names.id = fact_values.fact_name_id WHERE fact_names.name in (‘serialnumber’,’uptime’);

    Pour résumer, ma réponse à ton post et mon avis seraient d’adapter ton script à la structure EAV plutôt que l’inverse. Mais c’est peut-être plus simple de faire un gros select ? D’ailleurs, avec de bons index, les perfs ne seront pas forcément pourries.

    ++

  2. January 3rd, 2012 at 06:29 | #2

    @Camille

    Salut Camille ! Meilleurs voeux à toi !

    Alors, pour ce que je cherche à faire, c’est avoir la liste de tous les hosts, et une par ligne, pour faire un job de synchronisation et tant qu’a faire j’ai pas envie de mettre trop d’intelligence dans le job, c’est pour ça que je veux avoir une table à plat !

    Intéressante ta requête sinon, mais c’est toujours pareil, ça ne renvoie que les hosts où il existe une fact_values pour les fact_names donnés.

  3. Yannick
    January 3rd, 2012 at 13:13 | #3

    A froid, à la place d’un LEFT JOIN, j’aurais fait un RIGHT JOIN

  4. January 5th, 2012 at 14:05 | #4

    @Yannick

    d’après la doc de MySQL, c’est pareil :

    RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

  5. Yannick
    January 12th, 2012 at 13:26 | #5

    @asyd
    Si MySQL marche moins bien que SQL Server…

  6. Vince
    October 8th, 2013 at 13:22 | #6

    Je pense que le problème est lié au where filtrant que tu ajoutes à la fin (le where impose que les résultant contiennent un name avec ‘serialnumber’). Il vaut mieux faire le filtre directement au niveau de la jointure facultative (LEFT JOIN qui implique que je garde ce qui est à gauche si rien ne vient le remettre en cause).

    En gros, sans tester :
    SELECT hosts.id, fact_values.value
    FROM hosts
    LEFT JOIN fact_values ON hosts.id = fact_values.host_id
    LEFT JOIN fact_names ON fact_names.id = fact_values.fact_name_id AND fact_names.name = ‘serialnumber’;

  1. No trackbacks yet.