Article complet: Le type HierarchyID de sql serveur 2008

05/03/2009

20:00:00, Catégories: Récapitulatif .NET, Récapitulatif, Récapitulatif SGBD, SQL Server, Sql Serveur, 768 mots

, nico-pyright(c)

[.NET][SGBD][SQL Server] Le type HierarchyID de sql serveur 2008

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 :

[Suite:]

Qu'on va créer ainsi

CREATE TABLE Repertoire
(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 :

CREATE PROCEDURE AjoutDisque
(
        @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 :

CREATE PROCEDURE AjoutRepertoire
(
        @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 :

CREATE PROCEDURE AjoutFichier
(
        @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

-- creation de disque
        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
        exec AjoutDisque 'd:\'

set @idDisque = @@IDENTITY

-- creation du repertoire d:\abc\
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 :

select @niveau = NiveauRepertoire from Repertoire
        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 :