Une autre représentation d'arbre

[GAEREL]

Bonjour à tous.

J'ai une base oracle qui gère des Projets, découpés en Phase puis Activités et Taches. (particularité, les Taches peuvent être à tous les niveaux sauf la racine).

En gros :
Projet
++++ Phase 1
++++++++ Activité 1
++++++++++++ Tache 1
++++++++ Activité 2
++++++++++++ Tache 2
++++ Phase 2
++++++++ Tache 3
++++++++ Activité 3
++++++++++++ Tache 4

malheureusement pour moi ceci est représenté ainsi dans la table :

Code :

1
2
3
4
5
6
7
CREATE TABLE TASK ( 
    ID           NUMBER(10,0) NULL,
    NOM          VARCHAR2(450) NULL,
    NIVEAU       NUMBER(5,0) NULL,
    SEQUENCE     NUMBER(5,0) NULL,
    EST_TACHE    NUMBER(10,0) DEFAULT 0 NOT NULL
    );

soit, pour mon exemple:

Code :

1
2
3
4
5
6
7
8
9
10
ID ID_PROJET NOM        NIVEAU SEQUENCE EST_TACHE
1  1         Phase 1      1         1        0
2  1         Activité 1   2         2        0
3  1         Tache 1      3         3        1
4  1         Activité 2   2         4        0
5  1         Tache 2      3         5        1
6  1         Phase 2      1         6        0
7  1         Tache 3      2         7        1
8  1         Activité 3   2         8        0
9  1         Tache 4      3        9        1

La hiérarchie est donc représentée par les 2 colonnes NIVEAU et SEQUENCE qui représente la profondeur et "l'ordre" des feuilles.

Ma question (on y arrive) est la suivante.
Quelle est la meilleure facon de représenter mes données sous la forme suivante :

Code :

1
2
3
4
5
6
7
8
9
10
ID_PROJET ID_PHASE ID_ACTIVITE ID_TACHE NOM
1         1        NULL        NULL     Phase 1
1         1        2           NULL     Activité 1
1         1        2           3        Tache 1
1         1        4           NULL     Activité 2
1         1        4           5        Tache 2
1         6        NULL        NULL     Phase 2
1         6        NULL        7        Tache 3
1         6        8           NULL     Activité 3
1         6        8           9       Tache 4

Je suis sous Oracle 10.2
C'est chaud non ?

je l'ai fait mais c'est horrible...
des UNION + des SELECT MAX(ID) KEEP (dense_rank LAST ORDER BY NIVEAU), etc...

[CinePhil]

Ce que tu cherches à faire est de la cosmétique et n'est pas le boulot du SGBD mais celui du logiciel qui présente les données à l'utilisateur.

[GAEREL]

Envoyé par CinePhil

Ce que tu cherches à faire est de la cosmétique et n'est pas le boulot du SGBD mais celui du logiciel qui présente les données à l'utilisateur.

Certes mais de nombreux reportings vont vouloir cette présentation.
Afin de ne pas devoir refaire cette "conversion de forme" à chaque fois, j'essaie de déporter cela dans une vue qui s'appuierait sur mes tables ce qui sera plus "lisible" pour les développeurs

[pacmann]

Salut,

Cinephile, tu trouves que savoir à quelle activité est rattaché une tâche, a quelle phase est rattachée une activité c'est cosmétique ?

Pas moi.

Par contre, la modélisation est douteuse... je vais essayer de sortir la requête, ça a l'air drôle

[EDIT] Tiens, l'activité 3 ne devrait-elle pas être de niveau 3 et la tâche 4 de niveau 2 ?

[punkoff]

Vu la modélisation .....

Moi j'ai fait ca :
ca marche avec l'exemple mais faudrai un truc un peu plus complexe

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
WITH recursive tmp(id_projet, id_phase, id_activite, id_tache , nom, act_seq) AS (
SELECT id_projet, seq, cast(NULL AS integer), cast(NULL AS integer), nom, seq  
FROM task
WHERE seq = 1
union ALL
SELECT a.id_projet, 
case when b.niveau = 1 then b.seq else a.id_phase end,
case when b.niveau = 2 then b.seq else case when b.niveau = 1 then NULL else a.id_activite end end, 
case when b.niveau = 3 then b.seq else NULL end,
b.nom,
b.seq
FROM tmp a
INNER JOIN task b ON a.id_projet = b.id_projet AND a.act_seq + 1 = b.seq)
 
 
SELECT * FROM tmp

Dans mon code "seq" c'est la colonne sequence.
J'ai aussi changé l'id 7 de votre exemple vu que c'est une tache et qu'il a le niveau 2 au lieu de 3 (je suppose, m'enfin si ça n'est pas le cas il faudra juste faire un test sur "est_tache" au lieu de niveau pour régler le problème.)

résultat :

Code :

1
2
3
4
5
6
7
8
9
10
 
1;1;;;"Phase 1";1
1;1;2;;"Activité 1";2
1;1;2;3;"Tache 1";3
1;1;4;;"Activité 2";4
1;1;4;5;"Tache 2";5
1;6;;;"Phase 2";6
1;6;;7;"Tache 3";7
1;6;8;;"Activité 3";8
1;6;8;9;"Tache 4";9

edit :
requete si le jeu de donné de départ est bon

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
WITH recursive tmp(id_projet, id_phase, id_activite, id_tache , nom, act_seq) AS (
SELECT id_projet, seq, cast(NULL AS integer), cast(NULL AS integer), nom, seq  
FROM task
WHERE seq = 1
union ALL
SELECT a.id_projet, 
case when b.niveau = 1 then b.seq else a.id_phase end,
case when (b.niveau = 2 AND b.est_tache = 0) then b.seq else case when b.niveau = 1 then NULL else a.id_activite end end, 
case when b.est_tache = 1 then b.seq else NULL end,
b.nom,
b.seq
FROM tmp a
INNER JOIN task b ON a.id_projet = b.id_projet AND a.act_seq + 1 = b.seq)
 
 
SELECT * FROM tmp

[pacmann]

Punkoff, recursive with, c'est pas avant 11g (R2 ?)



[EDIT]
Je me suis vautré, je vais corriger ma requête

[punkoff]

pfff !! c'est le forum SQL !!

Mais tu as raison oui, j'oubli tout le temps ce détail avec les postes Oracle.

[pacmann]

Voilà

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
WITH task AS (
SELECT     1     id,     1     id_projet, 'Phase 1' nom,     1     niveau,     1     sequence,     0     est_tache FROM DUAL UNION ALL
SELECT     2     id,     1     id_projet, 'Activité 1' nom,     2     niveau,     2     sequence,     0     est_tache FROM DUAL UNION ALL
SELECT     3     id,     1     id_projet, 'Tache 1' nom,     3     niveau,     3     sequence,     1     est_tache FROM DUAL UNION ALL
SELECT     4     id,     1     id_projet, 'Activité 2' nom,     2     niveau,     4     sequence,     0     est_tache FROM DUAL UNION ALL
SELECT     5     id,     1     id_projet, 'Tache 2' nom,     3     niveau,     5     sequence,     1     est_tache FROM DUAL UNION ALL
SELECT     6     id,     1     id_projet, 'Phase 2' nom,     1     niveau,     6     sequence,     0     est_tache FROM DUAL UNION ALL
SELECT     7     id,     1     id_projet, 'Tache 3' nom,     2     niveau,     7     sequence,     1     est_tache FROM DUAL UNION ALL
SELECT     8     id,     1     id_projet, 'Activité 3' nom,     2     niveau,     8     sequence,     0     est_tache FROM DUAL UNION ALL
SELECT     9     id,     1     id_projet, 'Tache 4' nom, 	3	 niveau, 	9	 sequence, 	1	 est_tache FROM DUAL)
, u AS (
SELECT a.*, max(case niveau when 1 then sequence else 0 end) over(partition BY id_projet ORDER BY sequence) phase 
FROM task a)
SELECT u.*, case when niveau = 2 AND est_tache = 1 then NULL else nullif(max(case niveau when 2 then sequence else 0 end) over(partition BY id_projet, phase ORDER BY sequence), 0) end activite
,case est_tache when 1 then sequence end tache
FROM u

Se baser sur la séquence et le niveau, et faire du fenêtrage...

[EDIT] Zut, j'avais pas vu qu'une tâche n'était pas forcément rattachée à une activité...

[punkoff]

Envoyé par pacmann

[EDIT] Zut, j'avais pas vu qu'une tâche n'était pas forcément rattachée à une activité...

Avec un autre partition by id_project, tache, activité pour déterminer la tâche ca devrait le faire


edit: ah oui, c'est mieux !

[pacmann]

Bah en fait, j'avais bidouillé en ajoutant un CASE

[GAEREL]

Impressionnant, je ne m'attendais pas à une telle rapidité dans les réponses...

Je soupçonne cependant un pb.
Je teste et je vous en reparle

Euh.. Merci quand même

[GAEREL]

Voici mon jeu de test sous une forme plus... visuelle :



Voici la table correspondante pour les tests :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH TACHE AS (
SELECT 445 AS ID, 121 AS ID_PROJET, 'Activites transverses' AS NOM, 1 AS NIVEAU, 1 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 447 AS ID, 121 AS ID_PROJET, 'Coordination et Management' AS NOM, 2 AS NIVEAU, 2 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 448 AS ID, 121 AS ID_PROJET, 'Suivi Projet' AS NOM, 3 AS NIVEAU, 3 AS SEQ, 1 AS EST_TACHE FROM DUAL UNION ALL
SELECT 555 AS ID, 121 AS ID_PROJET, 'Conception' AS NOM, 1 AS NIVEAU, 4 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 589 AS ID, 121 AS ID_PROJET, 'Réunions Expression Besoins' AS NOM, 2 AS NIVEAU, 5 AS SEQ, 1 AS EST_TACHE FROM DUAL UNION ALL
SELECT 557 AS ID, 121 AS ID_PROJET, 'Spécifications fonctionnelles' AS NOM, 2 AS NIVEAU, 6 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 558 AS ID, 121 AS ID_PROJET, 'Rédaction des Spécifications' AS NOM, 3 AS NIVEAU, 7 AS SEQ, 1 AS EST_TACHE FROM DUAL UNION ALL
SELECT 560 AS ID, 121 AS ID_PROJET, 'Réalisation et Qualification' AS NOM, 1 AS NIVEAU, 8 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 562 AS ID, 121 AS ID_PROJET, 'Réalisation et tests unitaires' AS NOM, 2 AS NIVEAU, 9 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 552 AS ID, 121 AS ID_PROJET, 'Réalisation Prog 1' AS NOM, 3 AS NIVEAU, 10 AS SEQ, 1 AS EST_TACHE FROM DUAL UNION ALL
SELECT 563 AS ID, 121 AS ID_PROJET, 'Réalisation Prog 2' AS NOM, 3 AS NIVEAU, 11 AS SEQ, 1 AS EST_TACHE FROM DUAL UNION ALL
SELECT 564 AS ID, 121 AS ID_PROJET, 'Intégration et qualification interne' AS NOM, 2 AS NIVEAU, 12 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 551 AS ID, 121 AS ID_PROJET, 'Intégration' AS NOM, 3 AS NIVEAU, 13 AS SEQ, 1 AS EST_TACHE FROM DUAL UNION ALL
SELECT 565 AS ID, 121 AS ID_PROJET, 'Qualification' AS NOM, 3 AS NIVEAU, 14 AS SEQ, 1 AS EST_TACHE FROM DUAL UNION ALL
SELECT 614 AS ID, 121 AS ID_PROJET, 'Recette' AS NOM, 1 AS NIVEAU, 15 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 613 AS ID, 121 AS ID_PROJET, 'Recette par le client' AS NOM, 2 AS NIVEAU, 16 AS SEQ, 1 AS EST_TACHE FROM DUAL UNION ALL
SELECT 572 AS ID, 121 AS ID_PROJET, 'Déploiement et Généralisation' AS NOM, 1 AS NIVEAU, 17 AS SEQ, 0 AS EST_TACHE FROM DUAL UNION ALL
SELECT 576 AS ID, 121 AS ID_PROJET, 'Mise en production' AS NOM, 2 AS NIVEAU, 18 AS SEQ, 1 AS EST_TACHE FROM DUAL)

Petite particularité qui ruine un peu votre solution :
Vous êtes partis du principe que les ID étaient croissants, ce n'est pas le cas (mon jeu de tests était trop simple en effet, désolé).
L'ID est une PK créée par le système, dans ce nouveau jeu de données j'ai créé la Phase "Recette" après les autres taches.
Les ID ne sont alors plus croissants.

Voici les données de la table TACHE :


Votre solution me donne alors :

Les deux dernières lignes sont fausses, la Phase correspondant est la 572, pas la 614,
la 572 est une Phase, elle ne peut pas avoir d'id_activite.

[pacmann]

Ben pourquoi tu as pris l'ID ?

(C'est bien ton SEQ qui correspond à "sequence" dans ma requête ?)

[GAEREL]

Envoyé par pacmann

Ben pourquoi tu as pris l'ID ?

(C'est bien ton SEQ qui correspond à "sequence" dans ma requête ?)

Pour faire des jointures...

si je veux le libelle de l’activité ou de la phase correspondant à une tache donnée...

[pacmann]

Nan, ce que je veux dire, c'est que ton ID n'est certes pas croissant, mais ta séquence l'est, non ?
Donc pour construire la phase et le reste, il faut se baser sur la séquence et non sur l'ID !
(On est bien d'accord que ta séquence est unique sur le projet ?)

[GAEREL]

Envoyé par pacmann

Nan, ce que je veux dire, c'est que ton ID n'est certes pas croissant, mais ta séquence l'est, non ?
Donc pour construire la phase et le reste, il faut se baser sur la séquence et non sur l'ID !
(On est bien d'accord que ta séquence est unique sur le projet ?)

Oui la séquence est unique. (et croissante, pour donner l'ordre)

euh je suis un peu paumé alors...

quel est l'ordre SQL ?

[pacmann]

Ben tu prends ma requête d'avant, et tu remplaces "sequence" par "seq" ?

[Rei Ichido]

J'ai une question : que se passe-t-il si vous devez insérer une nouvelle feuille dans votre arbre ? Vous décalez toutes les valeur de "sequence" a posteriori ? Ça semble franchement horrible.

Il ne serait pas envisageable d'avoir une table qui ressemble à une table d'arbre en rajoutant une colonne id_pere ? Parce que à terme, je ne vois que des problèmes à ce genre de tables.

[GAEREL]

Envoyé par Rei Ichido

J'ai une question : que se passe-t-il si vous devez insérer une nouvelle feuille dans votre arbre ? Vous décalez toutes les valeur de "sequence" a posteriori ? Ça semble franchement horrible.

Il ne serait pas envisageable d'avoir une table qui ressemble à une table d'arbre en rajoutant une colonne id_pere ? Parce que à terme, je ne vois que des problèmes à ce genre de tables.

je confirme que ce n'est pas simple...
Mais il s'agit d'un progiciel, je n'ai pas la main sur le schéma...
D'où mon choix d'ajouter des vues qui "tapent" sur ce schéma pour en simplifier l'utilisation.

En réponse au dernier post de Pacmann :
l'ordre est donc :

Code :

1
2
3
4
5
6
7
 copier/coller la définition de TACHE, 
TMP AS (
    SELECT a.*, max(case niveau when 1 then seq else 0 end) over(partition BY id_projet ORDER BY seq) phase 
FROM TACHE A)
SELECT TMP.*, case when niveau = 2 AND est_tache = 1 then NULL else nullif(max(case niveau when 2 then seq else 0 end) over(partition BY id_projet, phase ORDER BY seq), 0) end activite
,case est_tache when 1 then seq end tache
FROM TMP

Résultat ok mais je ne peux pas faire de jointures directes avec les PK de la table pour, par exemple, trouver le libelle/nom de la phase correspondant à une tache donnée.

Pas trop grave, je ferai une double jointure sur id_projet ET sequence.
Mais il me faudrait un index... (sur une vue???, je rappelle que le but ultime est de mettre ca dans une vue)

[pacmann]

Alors tu peux faire un truc complètement barbare si tu connais la valeur max d'ID (celle qu'elle ne dépassera jamais).
Genre si ça dépasse pas 999, tu fais
seq * 1000 + ID à la place de seq : ça garde le même classement au niveau des order by, max et tout.
Et à la fin, ton ID est le reste de la division par 1000 de ton machin bricolé