mars
2009
Nonon, ca ne sera pas un cours sur le type HierarchyID, je ne suis aucunement DBA
Mais comme tout développeur, j’ai besoin de modéliser des tables de base de données et d’écrire du SQL.
J’ai pu lire 2-3 petites choses sur le net sur les HierarchyID mais toujours le même exemple, celui de la hiérarchie entre les employés d’une boite.
Je vous livre ici le fruit de mes recherches.
Je me propose ici de modéliser un disque dur, c’est à dire un volume, composé de dossiers ou de fichiers, qui peuvent eux même contenir des sous-dossiers ou des fichiers.
On aura donc trois tables :
Qu’on va créer ainsi
(IDRepertoire INT PRIMARY KEY NOT NULL IDENTITY,
Nom VARCHAR(255) NOT NULL,
NiveauRepertoire HIERARCHYID) CREATE TABLE Disque
(IDDisque INT PRIMARY KEY NOT NULL IDENTITY,
Nom VARCHAR(255) NOT NULL,
IDRepertoire int NOT NULL,
constraint FK_REP_DISK foreign key (IDRepertoire) references Repertoire (IDRepertoire) )
CREATE TABLE Fichier
(IDFichier INT PRIMARY KEY NOT NULL IDENTITY,
Nom VARCHAR(255) NOT NULL,
IDRepertoire int NOT NULL,
constraint FK_REP_FICHIER foreign key (IDRepertoire) references Repertoire (IDRepertoire) )
INSERT INTO Repertoire (Nom, NiveauRepertoire)
VALUES (‘root’, HIERARCHYID::GetRoot())
Notez qu’on insère au moment de la création des tables, un noeud de niveau root.
Ajouter un disque consistera à créer un répertoire à la racine du root et d’insérer une valeur dans la table disque, d’où la procédure stockée suivante :
(
@nomDisque varchar(255)
)
AS
BEGIN declare @niveau hierarchyid
set @niveau = HIERARCHYID::GetRoot()
exec AjoutRepertoire @niveau, @nomDisque
insert into Disque (Nom, IDRepertoire)
values (@nomDisque, @@IDENTITY)
END
Pour créer un répertoire, on ajoutera un noeud sous le noeud parent, et après un éventuel frère :
(
@parent Hierarchyid,
@nomRepertoire varchar(255)
)
AS
BEGIN declare @frere HIERARCHYID
SELECT @frere = Max(NiveauRepertoire)
FROM Repertoire
WHERE NiveauRepertoire.GetAncestor(1) = @parent;
INSERT INTO Repertoire (Nom, NiveauRepertoire)
VALUES (@nomRepertoire, @parent.GetDescendant(@frere, NULL)) END
Enfin, pour créer un fichier, on fera la relation avec son répertoire conteneur :
(
@IDRepertoire int,
@nomFichier varchar(255)
)
AS
BEGIN
INSERT INTO
Fichier (Nom, IDRepertoire)VALUES (@nomFichier, @IDRepertoire) END
Maintenant, je vais pouvoir créer la structure suivante :
- c:\test\fichier1.txt
- c:\test\fichier2.txt
- c:\windows\system32\dll1.dll
- c:\windows\system32\dll2.dll
- c:\windows\Microsoft.Net\fichier.exe
- d:\abc\def.txt
exec AjoutDisque ‘c:\’ declare @idDisque int = @@IDENTITY – creation du repertoire c:\test\
declare @niveau hierarchyid
select @niveau = Repertoire.NiveauRepertoire from Repertoire where Repertoire.IDRepertoire = (select Disque.IDRepertoire from Disque where IDDisque = @idDisque)
exec [AjoutRepertoire] @niveau, ‘test\’ – creation du fichier c:\test\fichier1.txt
– creation du fichier c:\test\fichier2.txt
declare @reperoireID int = @@IDENTITY
exec [AjoutFichier] @reperoireID, ‘fichier1.txt’
exec [AjoutFichier] @reperoireID, ‘fichier2.txt’ – creation du repertoire c:\windows\
select @niveau = Repertoire.NiveauRepertoire from Repertoire where Repertoire.IDRepertoire = (select Disque.IDRepertoire from Disque where IDDisque = @idDisque)
exec [AjoutRepertoire] @niveau, ‘windows\’ – creation du repertoire c:\windows\system32\
declare @reperoireID2 int
set @reperoireID2 = @@IDENTITY
select @niveau = Repertoire.NiveauRepertoire from Repertoire where Repertoire.IDRepertoire = @reperoireID2
exec [AjoutRepertoire] @niveau, ‘system32\’
– creation du fichier c:\windows\system32\dll1.dll
– creation du fichier c:\windows\system32\dll2.dll
set @reperoireID = @@IDENTITY
exec [AjoutFichier] @reperoireID, ‘dll1.dll’
exec [AjoutFichier] @reperoireID, ‘dll2.dll’ – creation du repertoire c:\windows\Microsoft.NET\
exec [AjoutRepertoire] @niveau, ‘Microsoft.NET\’
– creation du fichier c:\windows\Microsoft.NET\fichier.exe
set @reperoireID = @@IDENTITY
exec [AjoutFichier] @reperoireID, ‘fichier.exe’
——————
– creation de disque
select @niveau = Repertoire.NiveauRepertoire from Repertoire where Repertoire.IDRepertoire = (select Disque.IDRepertoire from Disque where IDDisque = @idDisque)
exec [AjoutRepertoire] @niveau, ‘abc\’ – creation du fichier d:\abc\def.txt
set @reperoireID = @@IDENTITY
exec [AjoutFichier] @reperoireID, ‘def.txt’
Maintenant, on pourra simplement récupérer tous les sous noeuds d’un disque, avec :
INNER JOIN Disque on Disque.IDRepertoire = Repertoire.IDRepertoire
where Disque.Nom = ‘c:\’
select *, NiveauRepertoire.GetAncestor(1) as ancestor from Repertoire
LEFT JOIN Fichier on Fichier.IDRepertoire = Repertoire.IDRepertoire
Where NiveauRepertoire.IsDescendantOf(@niveau) = 1 select @niveau = NiveauRepertoire from Repertoire
INNER JOIN Disque on Disque.IDRepertoire = Repertoire.IDRepertoire
where Disque.Nom = ‘d:\’
select *, NiveauRepertoire.GetAncestor(1) as ancestor from Repertoire
LEFT JOIN Fichier on Fichier.IDRepertoire = Repertoire.IDRepertoire
Where NiveauRepertoire.IsDescendantOf(@niveau) = 1
ce qui nous donne comme résultat :
Voilà pour ma maigre contribution sur les hierarchyID.
Il y a peut-être une meilleure façon de faire, dans ce cas, n’hésitez pas à me donner votre solution
Commentaires récents
- [Tests] Arrange Act Assert, une traduction ? dans
- [UnitTest][C#] Tester une méthode privée dans
- Récupérer une valeur d’un contrôle depuis une autre Form / inclusions croisées et déclaration anticipée dans
- Tutoriel : Utiliser la ListBox et l’Isolated Storage dans vos applications Windows Phone 7 avec Silverlight dans
- Tutoriel : Utiliser la ListBox et l’Isolated Storage dans vos applications Windows Phone 7 avec Silverlight dans
Archives
- janvier 2013
- avril 2012
- janvier 2012
- juin 2011
- janvier 2011
- décembre 2010
- novembre 2010
- septembre 2010
- juin 2010
- mars 2010
- février 2010
- janvier 2010
- décembre 2009
- novembre 2009
- octobre 2009
- septembre 2009
- août 2009
- juillet 2009
- mai 2009
- avril 2009
- mars 2009
- janvier 2009
- décembre 2008
- novembre 2008
- octobre 2008
- septembre 2008
- août 2008
- juillet 2008
- juin 2008
- mai 2008
- avril 2008
- mars 2008
- février 2008
- janvier 2008
- décembre 2007
- novembre 2007
- octobre 2007
- septembre 2007
- août 2007
- juillet 2007
- juin 2007
- mai 2007