Jointure extrême et hiérarchie

**LEK** · 27/09/2012, 21h37

Bonsoir,
je possède un programme gérant une structure de fichiers arborescente telle que les "répertoires" fils (feuilles) peuvent "descendre" de plusieurs "répertoires" parents.
La structure est telle que je n'ai au maximum que deux niveaux : les répertoires parents et enfants.
Par exemple en "image" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
 -----------------------             	 ---------------------
| Rep 1 :  1.BIN,2.ASC   |		| Rep 5 : 5.ASC,3.TXT |
 -----------------------   		 ---------------------	
   |	|			              |
   |	 -------------------------------------
   |		      |
   |		 -----------------	
   |		| Rep 4 : 5.ASC	|
   |		 -----------------	
   |     -----------------
   |---| Rep 2 : 3.TXT   |
   |     -----------------
   |
   |     -----------------
   |---| Rep 3 : 4.BIN    |
         -----------------

Je m'escrime depuis hier à écrire une requête me permettant de déterminer l'ensemble des fichiers par "répertoires" fils
additionné à l'ensemble des fichiers présents dans leur répertoires parents.
Mais certains types de fichiers ne peuvent être hérités des répertoires parents....
La requête que j'ai pour l'instant outre le fait qu'elle ne me ramène pas tout à fait le bon résultat et très peu performante
(2 minutes à l'exécution , sûrement du au produit cartésien que j'ai été obligé de faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
with 
    typeDeFichiers as 
    (
        select 'TXT' as TypeFichier, 1 as inheritable  from dual
        UNION        
        select 'BIN' as TypeFichier, 0 as inheritable  from dual
        UNION        
        select 'ASC' as TypeFichier, 0 as inheritable  from dual
        UNION        
        select 'ZIP' as TypeFichier, 0 as inheritable  from dual        
    ),
    contenuRep as 
    (
        select 1 as IdFolder , 1 as IdFichier , 'BIN' as TypeFichier from dual
        UNION        
        select 1 as IdFolder , 2 as IdFichier , 'ASC' as TypeFichier from dual
        UNION        
        select 2 as IdFolder , 3 as IdFichier , 'TXT' as TypeFichier from dual
        UNION        
        select 3 as IdFolder , 4 as IdFichier , 'BIN' as TypeFichier from dual        
        UNION        
        select 4 as IdFolder , 5 as IdFichier , 'ASC' as TypeFichier from dual        
        UNION        
        select 5 as IdFolder , 5 as IdFichier , 'ASC' as TypeFichier from dual        
        UNION        
        select 5 as IdFolder , 3 as IdFichier , 'TXT' as TypeFichier from dual        
    ),    
    structureRep as 
    (
        select 1 as IdFolder , null as IdFolderParent from dual            
        UNION
        select 2 as IdFolder , 1    as IdFolderParent from dual                    
        UNION
        select 3 as IdFolder , 1    as IdFolderParent from dual            
        UNION
        select 4 as IdFolder , 1    as IdFolderParent from dual                    
        UNION
        select 5 as IdFolder , null    as IdFolderParent from dual                    
        UNION
        select 4 as IdFolder , 5    as IdFolderParent from dual                    
    )
 
  --ensemble des fichiers des répertoires fils + les fichiers éventuellement hériés des répertoires parents
   SELECT DISTINCT
    IdFolderFils,        
    NVL(fils.IdFichier,pere.IdFichier) AS IdFichier
     FROM
   (
   SELECT 
             IdFolder ,  IdFichier , TypeFichier 
     FROM   contenuRep
   ) pere
   ,(
   SELECT IdFolder ,  IdFichier , TypeFichier 
     FROM   contenuRep
   ) fils
  ,(
      SELECT * FROM typeDeFichiers,      
           (
           SELECT IdFolder IdFolderFils, IdFolderParent
           from structureRep where IdFolderParent is not null
           )
 
   ) repDir --liste des répertoires fils et ensemble des type de fichiers qu'ils pourraient contenir
 
   WHERE repDir.TypeFichier = pere.TypeFichier(+)
     AND repDir.TypeFichier = fils.TypeFichier(+)
     AND repDir.IdFolderParent  = pere.IdFolder(+)
     AND repDir.IdFolderFils= fils.IdFolder(+)
     -- au moins un fichier à hériter
     AND (pere.IdFichier IS NOT NULL OR fils.IdFichier IS NOT NULL)
    -- si le seul fichier proposé en héritage par le dossier parent est un fichier inhéritable
    -- on ne le prend pas
     AND NOT (pere.IdFichier IS NOT NULL AND fils.IdFichier IS NULL AND repDir.inheritable= 1)    
order by 1,2;

Voilà mon résultat actuel, il me manque le fichier 2.ASC du répertoire 4 hérité de son parent 1....

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
IDFOLDERFILS	      IDFICHIER
2			1
2			2
2			3
3			2
3			4
4			1
4			5

Pour info, j'ai tenté une approche via "connect by prior", mais j'ai rebroussé chemin en n'y arrivant pas.
Auriez-vous une idée de comment améliorer ma requête (en terme de justesse et/ou de perf) ?
Merci pour toute info,
Lek.

**LEK** · 28/09/2012, 11h22

Y a-t-il une autre technique que le produit cartésien pour gérer ce genre de cas ?

**Waldar** · 28/09/2012, 11h35

Probablement, mais votre besoin est loin d'être clair.
Matérialisez uniquement votre problème et laissez le reste de côté (par exemple les types de fichiers qui n'ont pas de valeur ajoutée au problème).

**LEK** · 28/09/2012, 11h43

Bon, je vais essayer d'utiliser une autre formulation.
Si j'ai la structure suivante représentant des répertoires (Rep 1,Rep 2,...) peuplés de fichiers (1.BIN,2.ASC,...) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
 -----------------------             	 ---------------------
| Rep 1 :  1.BIN,2.ASC   |		| Rep 5 : 5.ASC,3.TXT |
 -----------------------   		 ---------------------	
   |	|			              |
   |	 -------------------------------------
   |		      |
   |		 -----------------	
   |		| Rep 4 : 5.ASC	|
   |		 -----------------	
   |     -----------------
   |---| Rep 2 : 3.TXT   |
   |     -----------------
   |
   |     -----------------
   |---| Rep 3 : 4.BIN    |
         -----------------

Je veux ecrire une requete qui me donnerait la liste des fichiers des répertoires 2,3,4 plus la liste des fichiers hérités de leur répertoires parents.
Mais certains fichiers comme les fichiers avec l'extention TXT ne peuvent pas être hérités d'un répertoire parent.
Dites moi si ce n'est pas assez clair...
Merci de votre attention.

**Waldar** · 28/09/2012, 11h56

Je pense avoir compris. En sortie vous voulez :

Rep2 : 3.txt (car dans ce répertoire)
Rep2 : 1.bin (hérite de Rep1)
Rep2 : 2.asc (hérite de Rep1)
Rep3 : 4.bin (car dans ce répertoire)
Rep3 : 1.bin (hérite de Rep1)
Rep3 : 2.asc (hérite de Rep1)
Rep4 : 5.asc (car dans ce répertoire)
Rep4 : 5.asc (hérite de Rep5)
Rep4 : 1.bin (hérite de Rep1)
Rep4 : 2.asc (hérite de Rep1)

Pour 5.asc de Rep4, vous le voulez deux fois ou une fois ?

**skuatamad** · 28/09/2012, 12h04

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select distinct s.IdFolder, ct.IdFichier
  from structureRep s
  join (select c.IdFolder, t.inheritable, IdFichier
          from contenuRep c 
          join typeDeFichiers t on t.TypeFichier = c.TypeFichier) ct
    on ct.IdFolder  = s.IdFolder
    or (ct.IdFolder = s.IdFolderParent and ct.inheritable = 0)
 where s.IdFolderParent is not null
 order by s.IdFolder, ct.IdFichier
 
  IDFOLDER  IDFICHIER
---------- ----------
         2          1
         2          2
         2          3
         3          1
         3          2
         3          4
         4          1
         4          2
         4          5
 
9 rows selected.

Si j'ai bien compris c'est le flag à 0 qui dit héritable ?
Donc pour moi il y avait également une erreur pour le rep 3 qui n'héritait pas du fichier 1.BIN alors qu'il est héritable.

Après je ne sais pas si c'est bon mais je ne vois pas l'intérêt des jointures externes ou produit cartésien.

**LEK** · 28/09/2012, 17h31

Bonjour,
excusez-moi pour ce retour tardif, mais j'étais pressé de tester sur ma base perso...
@Waldar, c'était effectivement cela mon besoin initial.
@skuatamad, mille et un merci, c'est effectivement la requête que j'aurais voulu écrire et comme tu l'indiques le produit cartésien était inutile : j'en avais la nette sensation mais sans savoir comment modifier ma requête... Maintenant que je l'ai sous les yeux ça me parait clairement évident...

Encore merci à vous deux!
Et bon début de week-end.

Jointure extrême et hiérarchie

SQL Oracle

Discussions similaires

Partager

Partager