Hierarchikus adatok kezelése SQL Server segítségével – 1. rész

calendar 2012-02-19 · 3 perc olvasás · SQL SQL Server T-SQL  ·
Megosztás: linkedin copy

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.

1CREATE TABLE [dbo].[EmployeeSelfJoin]
2(
3    [EmployeeID] int NOT NULL PRIMARY KEY,
4    [ManagerID] int NULL REFERENCES [dbo].[EmployeeSelfJoin]([EmployeeID]),
5    [EmployeeName] nvarchar(255)
6             
7);
8GO

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:
1UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 1
2WHERE [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:

1UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 26
2WHERE [ManagerID] = 160;
3 
4UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 1
5WHERE [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:

 1SELECT
 2    P.[BusinessEntityID],
 3    NULL,
 4    P.[LastName] + ' , ' + P.[FirstName]
 5FROM
 6    [HumanResources].[Employee] E
 7JOIN
 8    [dbo].[Person] P ON E.[BusinessEntityID] = P.[BusinessEntityID]
 9WHERE
10     E.[OrganizationNode].GetAncestor(1) IS NULL
11UNION
12SELECT
13    P.[BusinessEntityID],
14    MGR.[BusinessEntityID],
15    P.[LastName] + ' , ' + P.[FirstName]
16FROM
17    [HumanResources].[Employee] E
18JOIN
19    [dbo].[Person] P ON E.[BusinessEntityID] = P.[BusinessEntityID]
20CROSS APPLY
21    (SELECT [BusinessEntityID] FROM [HumanResources].[Employee] WHERE
22     [OrganizationNode] = E.[OrganizationNode].GetAncestor(1)) AS [MGR];