Akademia SQL - Część 17: Dane hierarchiczne w SQL Server 2008 – informacje podstawowe

struktura organizacyjna firmy,

system katalogów i plików,

struktura projektu informatycznego, np.: projekt, grupa zadań, zadania.

utworzenie w tabeli nowej kolumny, której wartość w rekordzie wskazuje na rekord nadrzędny w tej samej lub innej tabeli,

utworzenie w tabeli nowej kolumny, w której dla każdego rekordu przechowywana jest ścieżka określająca pozycję elementu w hierarchii,

przechowywanie danych hierarchicznych w formacie XML zapisanych w osobnej kolumnie.

Uruchom konsolę SQL Server Management Studio (SSMS) i połącz się z wybranym serwerem SQL:

Rysunek 2: Konsola SQL Server Management Studio (SSMS).

Otwórz nowe okno zapytania, wybierając opcję New Query z głównego menu.

Wpisz i uruchom poniższe zapytanie, które wyświetli informacje o pracownikach firmy AdventureWorks. Każdy z pracowników posiada unikalny identyfikator zapisany w kolumnie EmployeeID oraz identyfikator przełożonego (w kolumnie ManagerID). Osoba, która w kolumnie ManagerID ma zapisaną wartość NULL, jest właścicielem firmy, czyli nie posiada przełożonego.

use AdventureWorks
go
SELECT EmployeeID,ManagerID,LoginID
FROM HumanResources.Employee

Kolejny fragment kodu tworzy hierarchię pracowników, korzystając z Common Table Expressions:

WITH CTE_Employee AS
(
	SELECT EmployeeID,ManagerID,LoginID, 0 as POZIOM
	FROM HumanResources.Employee 
	WHERE ManagerID IS NULL
	UNION ALL
	SELECT HR.EmployeeID,HR.ManagerID,HR.LoginID, POZIOM+1
	FROM HumanResources.Employee HR JOIN CTE_Employee C
	ON HR.ManagerID = C.EmployeeID
)
SELECT * FROM CTE_Employee
ORDER BY POZIOM,ManagerID,EmployeeID

Zmienna POZIOM określa, na jakim poziomie drzewa znajduje się aktualny pracownik. W tym przypadku drzewo ma pięć poziomów. Na pierwszym jest właściciel (ken0), jego można uznać za korzeń drzewa. Na ostatnim poziomie znajduje się 190 pracowników, którzy są liśćmi. Sprawdzić to można, wykonując następujące zapytanie:

WITH CTE_Employee AS
(
	SELECT EmployeeID,ManagerID,LoginID, 0 as POZIOM
	FROM HumanResources.Employee 
	WHERE ManagerID IS NULL
	UNION ALL
	SELECT HR.EmployeeID,HR.ManagerID,HR.LoginID, POZIOM+1
	FROM HumanResources.Employee HR JOIN CTE_Employee C
	ON HR.ManagerID = C.EmployeeID
)
SELECT COUNT(EmployeeID) as [Liczba pracownikow],POZIOM FROM CTE_Employee
GROUP BY POZIOM
ORDER BY POZIOM
Liczba pracownikow POZIOM
------------------ -----------
1                  0
6                  1
27                 2
66                 3
190                4

(5 row(s) affected)

Omawiane rozwiązanie charakteryzuje się zwięzłym zapisem i czytelnością kodu. Brakuje w nim jednak informacji o ścieżce od każdego pracownika do właściciela firmy. Innymi słowy: pracownik adventure-works\ben0 o identyfikatorze 261 ma przełożonego o numerze 274 (adventure-works\sheela0), który z kolei jest podwładnym pracownika o numerze 71 (adventure-works\wendy0). Ten ma przełożonego o numerze 140 (adventure-works\laura1), który jest bezpośrednim podwładnym właściciela firmy. Istotna byłaby więc informacja zapisana przy pierwszym z wymienionych pracowników: 1/140/71/274/261.

Poniższe zapytanie prezentuje, w jaki sposób dynamicznie zbudować ścieżkę, o której mowa w poprzednim punkcie:

DECLARE @root AS INT;
SET @root = 1;

WITH CTE_Employee
AS
(
  SELECT EmployeeID, ManagerID,LoginID, 0 AS poziom,
    CAST('.' + CAST(EmployeeID AS VARCHAR(10)) + '.'
         AS VARCHAR(MAX)) AS sciezka
  FROM HumanResources.Employee 
  WHERE ManagerID IS NULL

  UNION ALL

  SELECT HR.EmployeeID, HR.ManagerID,HR.LoginID, poziom + 1,
    CAST(C.sciezka + CAST(HR.EmployeeID AS VARCHAR(10)) + '.'
         AS VARCHAR(MAX)) AS sciezka
  FROM HumanResources.Employee  HR JOIN CTE_Employee AS C
    ON HR.ManagerID = C.EmployeeID
)
SELECT *
FROM CTE_Employee
ORDER BY POZIOM,ManagerID,EmployeeID,sciezka;

Dla pracownika adventure-works\ben0 o identyfikatorze 261 otrzymujemy następujący wynik:

EmployeeID  ManagerID   LoginID               poziom      sciezka
----------- ----------- ------------------    ----------- -------------------
261         274         adventure-works\ben0  4           .109.140.71.274.261.

Podobne operacje dotyczące hierarchii pracowników w firmie wykonane zostaną za pomocą typu danych HIERARCHYID.

Otwórz nowe okno zapytania, wybierając opcję New Query z głównego menu.

Utwórz tabelę dbo.Organizacja, korzystając z podanego niżej kodu. Informacje o hierarchii pracowników będą w niej przechowywane w polu EmployeeID typu HIERARCHYID:

use AdventureWorks
GO
CREATE TABLE dbo.Organizacja
(
	Pracownik hierarchyid NOT NULL,
	Nazwa nvarchar(50) NOT NULL
)

Do tabeli zostanie dodana kolumna Poziom, w której przechowywane będą informacje o poziomie pracownika w hierarchii:

ALTER TABLE dbo.Organizacja 
ADD Poziom AS Pracownik.GetLevel()

Dodaj klucz główny do tabeli dbo.Organizacja, który utworzony jest na polu Pracownika. Operacja ta jest konieczna, ponieważ w następnym kroku będzie tworzona kolumna wyliczeniowa Przelozony:

ALTER TABLE dbo.Organizacja ADD CONSTRAINT
	PK_Organizacja PRIMARY KEY
	(
		Pracownik
	)

Utwórz kolumnę wyliczeniową Przelozony, która będzie kolumną wyliczeniową trwałą (jej wartość będzie przechowywana w tabeli). Wartość w tej kolumnie jest pobierana z metody GetAncestor(), która pozwala obliczyć osobę nadrzędną w hierarchii i jako parametr przyjmuje liczbę poziomów, o którą należy się przesunąć „w górę”, żeby uzyskać wynik:

ALTER TABLE dbo.Organizacja
ADD Przelozony AS Pracownik.GetAncestor(1) PERSISTED
	REFERENCES dbo.Organizacja(Pracownik)

Spójrz na poniższe zapytanie. Jest ono oparte na

WITH CTE_Hierarchia 
AS
(
    SELECT EmployeeID, LoginID, hierarchyid::GetRoot() AS Pracownik
    FROM HumanResources.Employee
	WHERE ManagerID IS NULL
    UNION ALL
    SELECT HR.EmployeeID, HR.LoginID, C.Pracownik.GetDescendant(null, null)
    FROM HumanResources.Employee AS HR
		INNER JOIN CTE_Hierarchia AS C
			ON HR.ManagerID= C.EmployeeID
)
Select *
From CTE_Hierarchia

EmployeeID  LoginID                       Pracownik
----------- -----------------------------------------
109         adventure-works\ken0          0x
6           adventure-works\david0        0x58
12          adventure-works\terri0        0x58
42          adventure-works\jean0         0x58
140         adventure-works\laura1        0x58
148         adventure-works\james1        0x58
273         adventure-works\brian3        0x58
268         adventure-works\stephen0      0x5AC0
284         adventure-works\amy0          0x5AC0
288         adventure-works\syed0         0x5AC0
290         adventure-works\lynn0         0x5AD6

------------------------------------------------

Dane w kolumnie Pracownik nie są unikalne i nie mogą posłużyć do zbudowania klucza głównego w tabeli dbo.Organizacja, w której ten klucz miał być oparty tylko na kolumnie Pracownik. Można jednak zmienić sposób wyznaczania kolumny Pracownik, dodając do niej informację o kolumnie EmployeeID, która na pewno zawiera dane unikalne:

WITH CTE_Hierarchia 
AS
(
    SELECT EmployeeID, LoginID, hierarchyid::GetRoot() AS Node
    FROM HumanResources.Employee
	WHERE ManagerID IS NULL
    UNION ALL
    SELECT HR.EmployeeID, HR.LoginID, hierarchyId::Parse(c.Node.ToString() + Cast(HR.EmployeeID as varchar(5)) + '/')
    FROM HumanResources.Employee AS HR
		INNER JOIN CTE_Hierarchia AS C
			ON HR.ManagerID= C.EmployeeID
)
INSERT INTO dbo.Organizacja(Pracownik,Nazwa)
Select Node, LoginID 
From CTE_Hierarchia

Sprawdźmy zatem uzyskane wyniki. Zwróć uwagę, że dane typu HIERARCHYID są rzeczywiście w zapisane w formacie binarnym – kolumny Pracownik i Przelozony. Jeśli chcesz zobaczyć, jak wygląda taka informacja w formacie tekstowym, wywołaj na odpowiedniej kolumnie metodę ToString():

SELECT Pracownik,Nazwa,Poziom,Przelozony,Pracownik.ToString() as Sciezka
FROM dbo.Organizacja
ORDER BY Poziom

Rysunek 3: Wynik działania zapytania.

Jeśli chciałbyś się dowiedzieć, kto jest szefem firmy, możesz wykonać zapytanie, w którym skorzystasz ze statycznej metody HIERARCHYID::GetRoot():

SELECT *
FROM dbo.Organizacja
WHERE Pracownik = HIERARCHYID::GetRoot();

Rysunek 4: Wynik działania zapytania.

Jeśli chcesz wyświetlić informacje o tym, kto jest przełożonym danego pracownika, musisz skorzystać z metody IsDescendantOf():

Declare @Pracownik As HierarchyId
Select @Pracownik = Pracownik From dbo.Organizacja
Where Nazwa = 'adventure-works\grant0'

Select *
From dbo.Organizacja
Where @Pracownik.IsDescendantOf(Pracownik) = 1

Rysunek 5: Wynik działania zapytania.

Typ danych HIERARCHYID umożliwia wykonywanie w zasadzie wszystkich operacji, które są potrzebne podczas manipulowania strukturami drzewiastymi. Nie są to jednak operacje proste, wymagają doświadczenia i dlatego nie zostaną zaprezentowane w tej lekcji. W literaturze pokazano miejsca, gdzie można odnaleźć praktyczne przykłady zastosowań typu danych HIERARCHYID.