Az utóbbi időben igen sok esetben kellett hierarchikus adatokkal foglalkoznom, illetve interjúk során is fel szoktam tenni ezzel kapcsolatban kérdéseket. Az alábbiakban szeretném bemutatni, hogy a különböző SQL Server verziók esetében ezen adattípusokat hogyan lehet/érdemes kezelni.
Mi lehet hierarchikus adat?
Ezt az egyszerű kérdést talán a legjobban egy példával lehet megválaszolni: ilyen adat lehet egy családfa vagy egy vállalat szervezeti felépítése. Maga a hierarchia jelentése a Wikipedia szerint:
A hierarchia (görögül: hierarchia (ἱεραρχία) (….)) olyan elrendezése elemeknek (tárgyak, nevek, értékek, kategóriák, stb.), amelyben az elemek egymáshoz képest alul, felül vagy egy szinten helyezkednek el.
Absztraktabb módon: hierarchia egyszerűen egy rendezett halmaz vagy egy aciklikus irányított gráf.
Alapvetően 3 féle módon tárolhatunk hierarchikus adatokat:
- Saját magára hivatkozó (SELF JOIN) tábla segítségével: ez működik SQL 2005 előtti verziókkal is, de csak az SQL Server 2005 és újabb verziók támogatják a rekurzív lekérdezéseket. Ennek a funkciónak a hiánya komplex, nehezen karbantartható lekérdezésekhez vezethet.
- XML adattípus használatával: csak SQL Server 2005 és újabb verziók esetén.
- hierarchyid adattípussal: csak SQL Server 2008 és újabb verziók esetén érhető el ez az adattípus.
A három módot egy példán keresztül mutatnám be: egy cég szervezeti feléítését kell eltárolni, ahol több eseményre és információra vagyok kíváncsi:
- Szeretném megtudni, hogy egy-egy személy hanyadik szinten van a szervezeti felépítésben.
- Mi történik akkor, ha valakit áthelyeznek? Itt egy másik kérdés is felmerül: ki veszi át a helyét, illetve szervezetileg hozzátartozó személyek is mennek vagy nem?
Saját magára hivatkozó (SELF JOIN) tábla
Talán ez a legegyszerűbb megvalósítása a hierarchikus adatok tárolásának, de nem feltétlenül a legoptimálisabb is. Talán a legnagyobb előnye, hogy az SQL Server 2005 előtti verziókkal is működik. Az alábbi tábla struktúra egy példa az ilyen típusú adatok tárolásához
CREATE TABLE [dbo].[EmployeeSelfJoin]
(
[EmployeeID] int NOT NULL PRIMARY KEY,
[ManagerID] int NULL REFERENCES [dbo].[EmployeeSelfJoin]([EmployeeID]),
[EmployeeName] nvarchar(255)
);
GO
Szeretném megtudni, hogy ki, melyik szinten helyezkedik el:
WITH OrgCTE AS
(
SELECT
[EmployeeID],
[ManagerID],
[EmployeeName],
1 AS [Level]
FROM
[dbo].[EmployeeSelfJoin]
WHERE
[ManagerID] IS NULL
UNION ALL
SELECT
A.[EmployeeID],
A.[ManagerID],
A.[EmployeeName],
B.[Level] + 1
FROM
[dbo].[EmployeeSelfJoin] A
JOIN
OrgCTE B ON A.[ManagerID] = B.[EmployeeID]
)
SELECT * FROM OrgCTE ORDER BY [Level] ASC;
Ez gyakorlatilag a GetLevel() függvénynek felel meg a hierarchyid típusnál. Mi történik akkor, ha valakit áthelyeznek?
A hierarchiában való mozgatásnak 2 esete van:
- Az alárendelt elemek is mennek a mozgatott elemmel,
- Az alárendelt elemek nem mennek a mozgatott elemmel, másik elem alárendeltségébe kerülnek vagy nem
.
Az első esetben egyszerű a dolgunk mivel csak egy helyen – ManagerID – kell változtatni az adatokon:
UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 1
WHERE [EmployeeID] = 160;
A második esetben már nem olyan egyszerű a helyzet. Ez a fajta módszer nem engedi meg, hogy “Manager” nélkül maradjanak elemek, így itt már 2 lépésben van lehetőség a változást megcsinálni: a sorrend ahogyan csináljuk csak az alkalmazástól és az üzleti szabályoktól függ (+ tranzakciós szint!). Első lépésben az alárendelt elemeket rendezem át, majd ugyan úgy, mint az első esetben a kiválasztott elemet helyezem át:
UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 26
WHERE [ManagerID] = 160;
UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 1
WHERE [EmployeeID] = 160;
Konvertálás egyik típusból a másikba
A fenti példákat az AdventureWorks2008R2 adatbázis HumanResources.Employee táblájával készítettem el. Az első esetben hierarchyid típusból kellett SELF JOIN típusú táblába adatokat töltenem. Az adatokat az alábbi lekérdezéssel lehetett előállítani:
SELECT
P.[BusinessEntityID],
NULL,
P.[LastName] + ' , ' + P.[FirstName]
FROM
[HumanResources].[Employee] E
JOIN
[dbo].[Person] P ON E.[BusinessEntityID] = P.[BusinessEntityID]
WHERE
E.[OrganizationNode].GetAncestor(1) IS NULL
UNION
SELECT
P.[BusinessEntityID],
MGR.[BusinessEntityID],
P.[LastName] + ' , ' + P.[FirstName]
FROM
[HumanResources].[Employee] E
JOIN
[dbo].[Person] P ON E.[BusinessEntityID] = P.[BusinessEntityID]
CROSS APPLY
(SELECT [BusinessEntityID] FROM [HumanResources].[Employee] WHERE
[OrganizationNode] = E.[OrganizationNode].GetAncestor(1)) AS [MGR];
A teljes script innen letölthető: sqlhierarchy-selfjoin.sql (2.80 kb)