Nem akarom unásig taglalni a típust, de az előző rész hibáját szeretném kijavítani.
Ott át akartam helyezni egy node-ot, aminek voltak gyermekelemei egy másik szülő alá. Nagyvonalúan ezt egy sor update-elésével oldottam meg, amellyel új HierarchyID-t generáltam a node-nak.
Igen ám, de a HiearchyID nem olyan, mint a relációsan összerakott rekurzív kompozíció, attól, hogy egy node HiearchyID-jét frissítem még a gyerekek kérdése nincs megoldva, sőt, árván, szülő nélkül maradnak.
Ebben a részben arra keresek megoldást, hogy
1. Hogyan kell helyesen egy teljes részfát átmozgatni másik szülő alá?
2. Hogyan lehetne biztosítani, hogy az előző részben elkövetett hiba ellen valamilyen integritási szabály beépítésével védekezzen az adatbázis?
Kezdjük az első ponttal. A teljes részfa áthelyezése megoldható lenne az eddig felhasznált függvényekkel, de nem túl hatékonyan. Gondoljuk át a következőt. Álljon a mozgatandó faág a következő sorokból:
/1/1/
/1/1/1/
/1/1/2/
/1/1/2/1
Azaz, az /1/1/ a szülő, ennek van három gyereke, két szintre elosztva. Ugye /1/1/ szülője /1/. Tegyük fel, hogy /2/ alá akarjuk berakni /1/1/-et és most már helyesen a gyerekeit is.
Ekkor az új ID-k így néznének ki:
/2/1/
/2/1/1/
/2/1/2/
/2/1/2/1
Azaz, a teljes részfa összes sorát update-elni kell, a részfa gyökerét kicserélve (vastagon szedve). Ezt megtehetnénk úgy is, hogy stringgé átalakítjuk az elérési utakat, majd összekolbászoljuk a kívánatos ID-t, de ez elég gagyi lenne. Szerencsére van hatékonyabb támogatás, a Reparent metódus képében. Lássuk:
begin tran declare @jozsi hierarchyid = (select OrgNode from HumanResources.NewOrg where LoginID = 'adventure-works\dylan0') --/2/1/4/ declare @jozsimostanifonoke hierarchyid = @jozsi.GetAncestor(1) --/2/1/ declare @jozsiujfonoke hierarchyid = (select OrgNode from HumanResources.NewOrg where LoginID = 'adventure-works\brian3') --/6/ --Józsi és a beosztottjai, a mozgatandó részfa select OrgNode.ToString() Path, OrgLevel, EmployeeId, ManagerID, LoginID from HumanResources.NewOrg where @jozsi.IsDescendant(OrgNode) = 1 select @jozsi.ToString() Jozsi, @jozsimostanifonoke.ToString() JozsiMostaniFonoke, @jozsiujfonoke.ToString() JozsiUjFonoke --Teszt, read-only áthelyezés select OrgNode.ToString() Path, OrgNode.Reparent(@jozsimostanifonoke, @jozsiujfonoke).ToString() NewPath, EmployeeId, ManagerID, LoginID from HumanResources.NewOrg where @jozsi.IsDescendant(OrgNode) = 1 order by OrgNode --Valódi update update HumanResources.NewOrg set OrgNode = OrgNode.Reparent(@jozsimostanifonoke, @jozsiujfonoke) where @jozsi.IsDescendant(OrgNode) = 1 --Visszaolvasás, csak, hogy lássuk mire mentünk select OrgNode.ToString() Path, OrgLevel, EmployeeId, ManagerID, LoginID from HumanResources.NewOrg order by OrgNode rollback tran
Józsi és beosztottjai:
Path EmployeeId ManagerID LoginID ------------------------------ ----------- ----------- ------------------------------ /2/1/4/ 158 3 adventure-works\dylan0 /2/1/4/1/ 79 158 adventure-works\diane1 /2/1/4/2/ 114 158 adventure-works\gigi0 /2/1/4/3/ 217 158 adventure-works\michael6
A mozgatás szereplői:
Jozsi JozsiMostaniFonoke JozsiUjFonoke ------------------------------ ------------------------------ ------------------------------ /2/1/4/ /2/1/ /6/
A select-ben generált ID-k (NewPath oszlop):
Path NewPath EmployeeId ManagerID LoginID ------------------------------ ------------------------------ ----------- ----------- ------------------------------ /2/1/4/ /6/4/ 158 3 adventure-works\dylan0 /2/1/4/1/ /6/4/1/ 79 158 adventure-works\diane1 /2/1/4/2/ /6/4/2/ 114 158 adventure-works\gigi0 /2/1/4/3/ /6/4/3/ 217 158 adventure-works\michael6
Működik, de azért jóval nagyobb macera és költség, mint a relációs esetben, szóval ha sokszor kell a részfákat átpakolgatni, akkor a hagyományos relációs megoldás hatékonyabb (szerintem ez nem tipikus).
A példából sejthető, hogy azért írták úgy meg az IsDescendant-ot, hogy magát a kérdéses node-ot is visszaadja, hogy a teljes részfát kijelölje, a gyökeret is, így egyszerűbb a where feltétel (másképp kellene bele egy or @OrgNode = @jozsi).
A probléma jóval huncutabbá válik azonban, ha a famozgatásban tetszőleges új szülőt meg lehet adni. Én a példában szándékosan úgy választottam meg az új főnököt, hogy ne legyenek gyermekei /6/4/../, mert akkor duplikált kulccsal leáll az update. Ebben az esetben át kellene masszírozni alacsonyabb szinten is a node-ok sorszámát a mozgatás előtt. Ezt meghagyom gyakorlatnak az olvasónak (nem így szokták a könyvek kikerülni azokat a kérdéseket, amelyeket nincs kedvük kidolgozni? ;).
Térjünk át a második kérdésre, milyen integritásvédelmet lehetne berakni a HierarchyID-k kezelésébe? Egyféle már van, primary key van az oszlopon, így legalább duplikált ID-ink nem lehetnek. De az előbb láttuk, hogy árva gyerekek igen. A rekurzív relációs esetben egy not nullos foreign key meg tudja akadályozni az árvákat, deklaratívan. Itt mit lehet tenni? Lehetőleg ne használjunk triggert, azt nem szeretjük.
Az elv ugye az, hogy mindenkinek kell legyen szülője. Ha minden sorhoz képezzük a szülőt a GetAncestor(1) segítségével, akkor már csak egy foreign key contraint kell, ami betartatja, hogy legyen ilyen szülő a primary key-ek, azaz az ID-k között. SQL-ül egy így hangzik:
alter table HumanResources.NewOrg add ParentId AS OrgNode.GetAncestor(1) persisted constraint FK_Parent references HumanResources.NewOrg(OrgNode)
Ravasz, mi? A persisted azért kell, mert ha nincs, ezt mondja a szerver:
Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted.
Mit jelent a peristed? Nem egy virtuális, számított oszlop jön létre, hanem egy hús-vér, letárolt oszlop, amiben a számított értéket a héttérben a db frissíti, ha kell (valamiféle belső triggerrel). A contraint rész gondolom világos.
Lássuk, tényleg véd-e a szuperoszlop?
Rontsuk vissza az előző rész színvonalára a korábbi update-et:
update HumanResources.NewOrg set OrgNode = OrgNode.Reparent(@jozsimostanifonoke, @jozsiujfonoke) where @jozsi = OrgNode --.IsDescendant(OrgNode) = 1
Csak Józsit mozgatjuk, a család le van szarva. Na mi lesz ebből?
The UPDATE statement conflicted with the SAME TABLE REFERENCE constraint "FK_Parent". The conflict occurred in database "AdventureWorks", table "HumanResources.NewOrg", column 'ParentId'.
Na, most más sokkal jobban érzem magam. Azért zárásul érdemes még azon elgondolkodni, hogy a többsoros update-nél a szerver hogy nem akad bele az új contraintünkbe? Honnan tudja, hogy nem csak Józsi lesz mozgatva, de a teljes rezesbanda is? A kulcsszó a deferred constraint checking. Az SQL Server 2008 ezt NEM fogja tudni általában. Ez arról szólna, hogy begin tran után úgy hágjuk meg a constrainteket ahogy akarjuk, de mire eljutunk a commitig már mindennek klappolni kell. Jó lenne ez, de ez nincs és nem is lesz a 2008-ban. Viszont egy update-en belül, önmagára hivatkozó táblák esetén, mint a példában, mégis csak ezt teszik. Másként sok update, pl. update pk = pk + 1 se menne (gondolkodjunk rajta).
A témát az Inside SQL Server 2000 jól feldolgozza, interim violation-re keresve könnyen megtalálható benne. Az SQL 7-es verzió kinn van az msnél is, pont a megfelelő fejezet.
Zárásul egy link a témában a BOL-ra. Ha valaki elmagyarázná nekem, hogy részfa-mozgatáskor miért keresik ki az új szülő utolsó gyerekét, és az alá rakják be a részfa gyökerét, nem az új szülő alá, megköszönném.
Ps. én marha, most vettem észre, hogy elfelejtettem publikálni ezt a postot, pedig már vagy 4 napja megírtam.
Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.