|
 |
|
 |
Cet article se base sur des tests et recherches réalisés dans la version 8.1.5 d'Oracle.
Tout au long de cet article, nous allons nous intéresser à une structure de données permettant de créer un Forum de discussion, matérialisé par la table suivante : === SQL> create table messages ( 2 id number, 3 id_parent number, 4 titre varchar2(50), 5 auteur varchar2(30), 6 dateheure date, 7 texte long); Table créée. SQL> alter table messages add primary key (id); Table modifiée. SQL> alter table messages modify dateheure default sysdate; Table modifiée. === La colonne id_parent contient NULL si le message est le premier message d'un fil de discussion, sinon elle contient l'id du message auquel le message répond. La colonne dateheure a pour valeur par défaut la date système du serveur de base de données. On pourra donc se permettre de l'ignorer lors de l'insertion des messages. Nous n'allons pas nous attarder sur l'enregistrement des messages dans la table (ordre SQL "insert"). Nous n'allons nous intéresser qu'à la façon dont les données sont lues dans la table, donc aux ordres SQL "select" qu'il faudra exécuter pour obtenir les résultats escomptés. Voici un exemple de contenu de la table messages : insert into messages (id, id_parent, titre, auteur, texte) values (1, NULL, 'Combien d''oeufs dans la pate à crêpes ?', 'John', 'Quelqu''un sait-il combien il faut d''oeufs ?'); insert into messages (id, id_parent, titre, auteur, texte) values (2, NULL, 'Sondage : votre marque de lait préférée', 'John', 'Dites-moi quelle est votre marque de lait préférée ?'); insert into messages (id, id_parent, titre, auteur, texte) values (3,1, 'Re : Combien d''oeufs dans la pate à crêpes ?', 'Martine', 'Je crois que c''est 2 par personne non ?'); insert into messages (id, id_parent, titre, auteur, texte) values (4,3, 'C''est sûrement ça', 'John', 'Tu dois avoir raison, merci !'); insert into messages (id, id_parent, titre, auteur, texte) values (5,2, 'Re : Sondage : votre marque de lait préférée', 'Fifi', 'Moi c''est Lait Blanc'); insert into messages (id, id_parent, titre, auteur, texte) values (6,2, 'Re : Sondage : votre marque de lait préférée', 'Petrus', 'SuperMilk'); insert into messages (id, id_parent, titre, auteur, texte) values (7,4, 'Re : C''est sûrement ça', 'Martine', 'De rien ;o)'); insert into messages (id, id_parent, titre, auteur, texte) values (8,2, 'Re : Sondage : votre marque de lait préférée', 'Fifi', 'Au fait pourquoi ce sondage ?'); insert into messages (id, id_parent, titre, auteur, texte) values (9,2, 'Re : Sondage : votre marque de lait préférée', 'Enrico', 'Le lait de chèvre'); insert into messages (id, id_parent, titre, auteur, texte) values (10,8, 'Re : Sondage : votre marque de lait préférée', 'John', 'Je suis statisticien de formation et j''ai rien d''autre à faire'); insert into messages (id, id_parent, titre, auteur, texte) values (11, NULL, 'Qui a vu le match de volley hier soir ?', 'Petrus', 'Si qq''un connaît le score, merci de nous le donner...'); insert into messages (id, id_parent, titre, auteur, texte) values (12,10, 'Re : Sondage : votre marque de lait préférée', 'Fifi', 'Ok'); insert into messages (id, id_parent, titre, auteur, texte) values (13,3, 'Re : Combien d''oeufs dans la pate à crêpes ?', 'Fifi', 'Oui c''est 2 par personne'); Ce qui nous donne le contenu suivant (par ordre chronologique de création des messages) : ID ID_P TITRE AUTEUR HEURE ------ --------------------------------------------- ------------------ 1 Combien d'oeufs dans la pate à crêpes ? John 16:58:20 2 Sondage : votre marque de lait préférée John 16:58:37 3 1 Re : Combien d'oeufs dans la pate à crêpes ? Martine 16:58:51 4 3 C'est sûrement ça John 16:59:00 5 2 Re : Sondage : votre marque de lait préférée Fifi 16:59:08 6 2 Re : Sondage : votre marque de lait préférée Petrus 16:59:14 7 4 Re : C'est sûrement ça Martine 16:59:20 8 2 Re : Sondage : votre marque de lait préférée Fifi 16:59:26 9 2 Re : Sondage : votre marque de lait préférée Enrico 16:59:31 10 8 Re : Sondage : votre marque de lait préférée John 16:59:45 11 Qui a vu le match de volley hier soir ? Petrus 16:59:51 12 10 Re : Sondage : votre marque de lait préférée Fifi 16:59:59 13 3 Re : Combien d'oeufs dans la pate à crêpes ? Fifi 17:00:03 Ainsi : - les deux premiers messages sont des débuts de fil de discussion - le message ID=3 répond au message ID=1 car son ID_PARENT=1 - le message ID=13 répond au message ID=3 car son ID_PARENT=3 - etc... Notre but est donc d'obtenir une hiérarchie de la forme : ID=1 |-ID=3 | |-ID=4 | | |-ID=7 | |-ID=13 ID=2 |-ID=5 |-ID=6 |-ID=8 | |-ID=10 | | |-ID=12 |-ID=9 ID=11 Le lien entre un message et son parent est bien sûr fait avec les colonnes ID et ID_PARENT. Voici la syntaxe de l'ordre SQL permettant de récupérer la hiérarchie souhaitée : ===
SQL> select id, id_parent, titre
2 from messages
3 start with id_parent is null
4 connect by id_parent = prior id;
ID ID_PARENT TITRE
--------- --------- --------------------------------------------------
1 Combien d'oeufs dans la pate à crêpes ?
3 1 Re : Combien d'oeufs dans la pate à crêpes ?
4 3 C'est sûrement ça
7 4 Re : C'est sûrement ça
13 3 Re : Combien d'oeufs dans la pate à crêpes ?
2 Sondage : votre marque de lait préférée
5 2 Re : Sondage : votre marque de lait préférée
6 2 Re : Sondage : votre marque de lait préférée
8 2 Re : Sondage : votre marque de lait préférée
10 8 Re : Sondage : votre marque de lait préférée
12 10 Re : Sondage : votre marque de lait préférée
9 2 Re : Sondage : votre marque de lait préférée
11 Qui a vu le match de volley hier soir ?
13 ligne(s) sélectionnée(s).
===
Notre objectif est atteint : nous avons les messages dans le bon ordre. Une requête hiérarchique permet donc de trier des enregistrements en utilisant des notions d'arborescence inter-enregistrement.
Dans une hiérarchie, on peut avoir un élément A qui a pour enfant un élément B, qui lui-même a pour enfant un élément C, etc... On peut alors dire que l'élément C est au niveau n+2 par rapport au niveau n de l'élément A. Afin de mieux voir la profondeur de hiérarchie, nous allons utiliser la pseudo-colonne LEVEL, qui permet de savoir à quel niveau hiérarchique on se trouve : ===
SQL> select level, titre, auteur
2 from messages
3 start with id_parent is null
4 connect by id_parent = prior id;
LEVEL TITRE AUTEUR
--------- -------------------------------------------------- ---------
1 Combien d'oeufs dans la pate à crêpes ? John
2 Re : Combien d'oeufs dans la pate à crêpes ? Martine
3 C'est sûrement ça John
4 Re : C'est sûrement ça Martine
3 Re : Combien d'oeufs dans la pate à crêpes ? Fifi
1 Sondage : votre marque de lait préférée John
2 Re : Sondage : votre marque de lait préférée Fifi
2 Re : Sondage : votre marque de lait préférée Petrus
2 Re : Sondage : votre marque de lait préférée Fifi
3 Re : Sondage : votre marque de lait préférée John
4 Re : Sondage : votre marque de lait préférée Fifi
2 Re : Sondage : votre marque de lait préférée Enrico
1 Qui a vu le match de volley hier soir ? Petrus
13 ligne(s) sélectionnée(s).
===
Pour plus de clarté, nous allons faire précéder chaque titre d'un nombre d'espaces égal au level qui lui correspond. Cela génèrera une indentation du plus bel effet ! ===
SQL>
1 select id, id_parent, rpad(' ', level-1) || titre as titre, auteur
2 from messages
3 start with id_parent is null
4 connect by id_parent = prior id;
ID ID_PARENT TITRE AUTEUR
--- --------- ------------------------------------------------------ ----------
1 Combien d'oeufs dans la pate à crêpes ? John
3 1 Re : Combien d'oeufs dans la pate à crêpes ? Martine
4 3 C'est sûrement ça John
7 4 Re : C'est sûrement ça Martine
13 3 Re : Combien d'oeufs dans la pate à crêpes ? Fifi
2 Sondage : votre marque de lait préférée John
5 2 Re : Sondage : votre marque de lait préférée Fifi
6 2 Re : Sondage : votre marque de lait préférée Petrus
8 2 Re : Sondage : votre marque de lait préférée Fifi
10 8 Re : Sondage : votre marque de lait préférée John
12 10 Re : Sondage : votre marque de lait préférée Fifi
9 2 Re : Sondage : votre marque de lait préférée Enrico
11 Qui a vu le match de volley hier soir ? Petrus
13 ligne(s) sélectionnée(s).
===
Nous venons donc de voir à quoi peut servir une requête hiérarchique. Parmis les applications possible de cet outil puissant : - les forums avec fil de discussion (notre exemple) - hiérarchie d'un groupe de personnes (chefs de service - employés) - gestion de catégories et sous-catégories pour organiser des informations - ...
La clause START WITH permet d'indiquer la condition qui détermine quels enregistrement sont au niveau 1 (aucun enregistrement au-dessus d'eux dans la hiérarchie). Si on n'avait voulu lister que les fils de discussion initiés par John, avec leurs réponses, voici la requête qu'il aurait fallu exécuter : ===
SQL>
1 select id, id_parent, rpad(' ', level-1) || titre as titre, auteur
2 from messages
3 start with id_parent is null and auteur = 'John'
4* connect by id_parent = prior id
ID ID_PARENT TITRE AUTEUR
--- --------- ------------------------------------------------------ ----------
1 Combien d'oeufs dans la pate à crêpes ? John
3 1 Re : Combien d'oeufs dans la pate à crêpes ? Martine
4 3 C'est sûrement ça John
7 4 Re : C'est sûrement ça Martine
13 3 Re : Combien d'oeufs dans la pate à crêpes ? Fifi
2 Sondage : votre marque de lait préférée John
5 2 Re : Sondage : votre marque de lait préférée Fifi
6 2 Re : Sondage : votre marque de lait préférée Petrus
8 2 Re : Sondage : votre marque de lait préférée Fifi
10 8 Re : Sondage : votre marque de lait préférée John
12 10 Re : Sondage : votre marque de lait préférée Fifi
9 2 Re : Sondage : votre marque de lait préférée Enrico
12 ligne(s) sélectionnée(s).
===
Le 13ème et dernier message n'a pas été sélectionné car il ne fait pas partie d'un fil de discussion initié par John. Les requêtes hiérarchiques sont donc non seulement un moyen de trier les enregistrements, mais aussi un moyen de sélectionner des enregistrements (même sans utilisation de la clause where). La clause CONNECT BY permet d'indiquer la condition qui lie un enregistrement à son enregistrement père. "connect by id_parent = prior id" doit se lire "la relation père-fils entre deux enregistrements est définie par le fait que la colonne id_parent de l'enregistrement fils est égale à la colonne id de l'enregistrement père" (c'est le mot clé PRIOR qui indique que l'on fait référence au père).
Article écrit par Tittom
|
