{"id":395,"date":"2008-01-19T11:25:49","date_gmt":"2008-01-19T10:25:49","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2008\/01\/19\/sql-server-2008-ujdonsagok-13-hierarchyid-adattipus-5\/"},"modified":"2008-01-25T10:24:35","modified_gmt":"2008-01-25T09:24:35","slug":"sql-server-2008-ujdonsagok-13-hierarchyid-adattipus-5","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/01\/19\/sql-server-2008-ujdonsagok-13-hierarchyid-adattipus-5\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 14. – HierarchyID adatt\u00edpus 5."},"content":{"rendered":"

Nem akarom un\u00e1sig taglalni a t\u00edpust, de az el\u0151z\u0151 r\u00e9sz<\/a> hib\u00e1j\u00e1t szeretn\u00e9m kijav\u00edtani.
\nOtt \u00e1t akartam helyezni egy node-ot, aminek voltak gyermekelemei egy m\u00e1sik sz\u00fcl\u0151 al\u00e1. Nagyvonal\u00faan ezt egy sor update-el\u00e9s\u00e9vel oldottam meg, amellyel \u00faj HierarchyID-t gener\u00e1ltam a node-nak.
\nIgen \u00e1m, de a HiearchyID nem olyan, mint a rel\u00e1ci\u00f3san \u00f6sszerakott rekurz\u00edv kompoz\u00edci\u00f3, att\u00f3l, hogy egy node HiearchyID-j\u00e9t friss\u00edtem m\u00e9g a gyerekek k\u00e9rd\u00e9se nincs megoldva, s\u0151t, \u00e1rv\u00e1n, sz\u00fcl\u0151 n\u00e9lk\u00fcl maradnak.<\/p>\n

Ebben a r\u00e9szben arra keresek megold\u00e1st, hogy
\n1. Hogyan kell helyesen egy teljes r\u00e9szf\u00e1t \u00e1tmozgatni m\u00e1sik sz\u00fcl\u0151 al\u00e1?
\n2. Hogyan lehetne biztos\u00edtani, hogy az el\u0151z\u0151 r\u00e9szben elk\u00f6vetett hiba ellen valamilyen integrit\u00e1si szab\u00e1ly be\u00e9p\u00edt\u00e9s\u00e9vel v\u00e9dekezzen az adatb\u00e1zis?<\/p>\n

Kezdj\u00fck az els\u0151 ponttal. A teljes r\u00e9szfa \u00e1thelyez\u00e9se megoldhat\u00f3 lenne az eddig felhaszn\u00e1lt f\u00fcggv\u00e9nyekkel, de nem t\u00fal hat\u00e9konyan. Gondoljuk \u00e1t a k\u00f6vetkez\u0151t. \u00c1lljon a mozgatand\u00f3 fa\u00e1g a k\u00f6vetkez\u0151 sorokb\u00f3l:<\/p>\n

\/1\/1\/
\n\/1\/1\/1\/
\n\/1\/1\/2\/
\n\/1\/1\/2\/1
\nAzaz, az \/1\/1\/ a sz\u00fcl\u0151, ennek van h\u00e1rom gyereke, k\u00e9t szintre elosztva. Ugye \/1\/1\/ sz\u00fcl\u0151je \/1\/. Tegy\u00fck fel, hogy \/2\/ al\u00e1 akarjuk berakni \/1\/1\/-et \u00e9s most m\u00e1r helyesen a gyerekeit is.<\/p>\n

Ekkor az \u00faj ID-k \u00edgy n\u00e9zn\u00e9nek ki:<\/p>\n

\/2<\/strong>\/1\/
\n\/2<\/strong>\/1\/1\/
\n\/2<\/strong>\/1\/2\/
\n\/2<\/strong>\/1\/2\/1<\/p>\n

Azaz, a teljes r\u00e9szfa \u00f6sszes sor\u00e1t update-elni kell, a r\u00e9szfa gy\u00f6ker\u00e9t kicser\u00e9lve (vastagon szedve). Ezt megtehetn\u00e9nk \u00fagy is, hogy stringg\u00e9 \u00e1talak\u00edtjuk az el\u00e9r\u00e9si utakat, majd \u00f6sszekolb\u00e1szoljuk a k\u00edv\u00e1natos ID-t, de ez el\u00e9g gagyi lenne. Szerencs\u00e9re van hat\u00e9konyabb t\u00e1mogat\u00e1s, a Reparent met\u00f3dus k\u00e9p\u00e9ben. L\u00e1ssuk:<\/p>\n

\r\nbegin tran\r\n\r\ndeclare @jozsi hierarchyid = \r\n(select OrgNode \r\nfrom HumanResources.NewOrg \r\nwhere LoginID = 'adventure-works\\dylan0') --\/2\/1\/4\/\r\n\r\ndeclare @jozsimostanifonoke hierarchyid = @jozsi.GetAncestor(1) --\/2\/1\/\r\n\r\ndeclare @jozsiujfonoke hierarchyid = \r\n(select OrgNode \r\nfrom HumanResources.NewOrg \r\nwhere LoginID = 'adventure-works\\brian3') --\/6\/\r\n\r\n--J\u00f3zsi \u00e9s a beosztottjai, a mozgatand\u00f3 r\u00e9szfa\r\nselect\r\nOrgNode.ToString() Path, OrgLevel, \r\nEmployeeId, ManagerID, LoginID\r\nfrom HumanResources.NewOrg\r\nwhere @jozsi.IsDescendant(OrgNode) = 1 \r\n\r\nselect \r\n@jozsi.ToString() Jozsi,\r\n@jozsimostanifonoke.ToString() JozsiMostaniFonoke,\r\n@jozsiujfonoke.ToString() JozsiUjFonoke\r\n\r\n--Teszt, read-only \u00e1thelyez\u00e9s\r\nselect\r\nOrgNode.ToString() Path, \r\nOrgNode.Reparent(@jozsimostanifonoke, @jozsiujfonoke).ToString() NewPath,\r\nEmployeeId, ManagerID, LoginID\r\nfrom HumanResources.NewOrg\r\nwhere @jozsi.IsDescendant(OrgNode) = 1\r\norder by OrgNode\r\n\r\n--Val\u00f3di update\r\nupdate HumanResources.NewOrg\r\nset OrgNode = OrgNode.Reparent(@jozsimostanifonoke, @jozsiujfonoke)\r\nwhere @jozsi.IsDescendant(OrgNode) = 1\r\n\r\n--Visszaolvas\u00e1s, csak, hogy l\u00e1ssuk mire ment\u00fcnk\r\nselect \r\nOrgNode.ToString() Path, OrgLevel, \r\nEmployeeId, ManagerID, LoginID\r\nfrom HumanResources.NewOrg\r\norder by OrgNode\r\n\r\nrollback tran\r\n<\/pre>\n
J\u00f3zsi \u00e9s beosztottjai:<\/p>\n
\r\nPath                           EmployeeId  ManagerID   LoginID\r\n------------------------------ ----------- ----------- ------------------------------\r\n\/2\/1\/4\/                        158         3           adventure-works\\dylan0\r\n\/2\/1\/4\/1\/                      79          158         adventure-works\\diane1\r\n\/2\/1\/4\/2\/                      114         158         adventure-works\\gigi0\r\n\/2\/1\/4\/3\/                      217         158         adventure-works\\michael6\r\n<\/pre>\n
A mozgat\u00e1s szerepl\u0151i:<\/p>\n
\r\nJozsi                          JozsiMostaniFonoke             JozsiUjFonoke\r\n------------------------------ ------------------------------ ------------------------------\r\n\/2\/1\/4\/                        \/2\/1\/                          \/6\/\r\n<\/pre>\n
A select-ben gener\u00e1lt ID-k (NewPath oszlop):<\/p>\n
\r\nPath                           NewPath                        EmployeeId  ManagerID   LoginID\r\n------------------------------ ------------------------------ ----------- ----------- ------------------------------\r\n\/2\/1\/4\/                        \/6\/4\/                          158         3           adventure-works\\dylan0\r\n\/2\/1\/4\/1\/                      \/6\/4\/1\/                        79          158         adventure-works\\diane1\r\n\/2\/1\/4\/2\/                      \/6\/4\/2\/                        114         158         adventure-works\\gigi0\r\n\/2\/1\/4\/3\/                      \/6\/4\/3\/                        217         158         adventure-works\\michael6\r\n<\/pre>\n
M\u0171k\u00f6dik, de az\u00e9rt j\u00f3val nagyobb macera \u00e9s k\u00f6lts\u00e9g, mint a rel\u00e1ci\u00f3s esetben, sz\u00f3val ha sokszor kell a r\u00e9szf\u00e1kat \u00e1tpakolgatni, akkor a hagyom\u00e1nyos rel\u00e1ci\u00f3s megold\u00e1s hat\u00e9konyabb (szerintem ez nem tipikus).
\nA p\u00e9ld\u00e1b\u00f3l sejthet\u0151, hogy az\u00e9rt \u00edrt\u00e1k \u00fagy meg az IsDescendant-ot, hogy mag\u00e1t a k\u00e9rd\u00e9ses node-ot is visszaadja, hogy a teljes r\u00e9szf\u00e1t kijel\u00f6lje, a gy\u00f6keret is, \u00edgy egyszer\u0171bb a where felt\u00e9tel (m\u00e1sk\u00e9pp kellene bele egy or @OrgNode = @jozsi).<\/p>\n
A probl\u00e9ma j\u00f3val huncutabb\u00e1 v\u00e1lik azonban, ha a famozgat\u00e1sban tetsz\u0151leges \u00faj sz\u00fcl\u0151t meg lehet adni. \u00c9n a p\u00e9ld\u00e1ban sz\u00e1nd\u00e9kosan \u00fagy v\u00e1lasztottam meg az \u00faj f\u0151n\u00f6k\u00f6t, hogy ne legyenek gyermekei \/6\/4\/..\/, mert akkor duplik\u00e1lt kulccsal le\u00e1ll az update. Ebben az esetben \u00e1t kellene massz\u00edrozni alacsonyabb szinten is a node-ok sorsz\u00e1m\u00e1t a mozgat\u00e1s el\u0151tt. Ezt meghagyom gyakorlatnak az olvas\u00f3nak (nem \u00edgy szokt\u00e1k a k\u00f6nyvek kiker\u00fclni azokat a k\u00e9rd\u00e9seket, amelyeket nincs kedv\u00fck kidolgozni? ;).<\/p>\n
T\u00e9rj\u00fcnk \u00e1t a m\u00e1sodik k\u00e9rd\u00e9sre, milyen integrit\u00e1sv\u00e9delmet lehetne berakni a HierarchyID-k kezel\u00e9s\u00e9be? Egyf\u00e9le m\u00e1r van, primary key van az oszlopon, \u00edgy legal\u00e1bb duplik\u00e1lt ID-ink nem lehetnek. De az el\u0151bb l\u00e1ttuk, hogy \u00e1rva gyerekek igen. A rekurz\u00edv rel\u00e1ci\u00f3s esetben egy not nullos foreign key meg tudja akad\u00e1lyozni az \u00e1rv\u00e1kat, deklarat\u00edvan. Itt mit lehet tenni? Lehet\u0151leg ne haszn\u00e1ljunk triggert, azt nem szeretj\u00fck.<\/p>\n
Az elv ugye az, hogy mindenkinek kell legyen sz\u00fcl\u0151je. Ha minden sorhoz k\u00e9pezz\u00fck a sz\u00fcl\u0151t a GetAncestor(1) seg\u00edts\u00e9g\u00e9vel, akkor m\u00e1r csak egy foreign key contraint kell, ami betartatja, hogy legyen ilyen sz\u00fcl\u0151 a primary key-ek, azaz az ID-k k\u00f6z\u00f6tt. SQL-\u00fcl egy \u00edgy hangzik:<\/p>\n
\r\nalter table HumanResources.NewOrg\r\nadd ParentId AS OrgNode.GetAncestor(1) persisted\r\nconstraint FK_Parent \r\nreferences HumanResources.NewOrg(OrgNode)\r\n<\/pre>\n
Ravasz, mi? A persisted az\u00e9rt kell, mert ha nincs, ezt mondja a szerver:<\/p>\n
\r\nOnly 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.\r\n<\/pre>\n
Mit jelent a peristed? Nem egy virtu\u00e1lis, sz\u00e1m\u00edtott oszlop j\u00f6n l\u00e9tre, hanem egy h\u00fas-v\u00e9r, let\u00e1rolt oszlop, amiben a sz\u00e1m\u00edtott \u00e9rt\u00e9ket a h\u00e9tt\u00e9rben a db friss\u00edti, ha kell (valamif\u00e9le bels\u0151 triggerrel). A contraint r\u00e9sz gondolom vil\u00e1gos.<\/p>\n
L\u00e1ssuk, t\u00e9nyleg v\u00e9d-e a szuperoszlop?<\/p>\n
Rontsuk vissza az el\u0151z\u0151 r\u00e9sz sz\u00ednvonal\u00e1ra a kor\u00e1bbi update-et:<\/p>\n
\r\nupdate HumanResources.NewOrg\r\nset OrgNode = OrgNode.Reparent(@jozsimostanifonoke, @jozsiujfonoke)\r\nwhere @jozsi = OrgNode --.IsDescendant(OrgNode) = 1\r\n<\/pre>\n
Csak J\u00f3zsit mozgatjuk, a csal\u00e1d le van szarva. Na mi lesz ebb\u0151l?<\/p>\n
\r\nThe UPDATE statement conflicted with the SAME TABLE REFERENCE \r\nconstraint "FK_Parent". The conflict occurred in database \r\n"AdventureWorks", table "HumanResources.NewOrg", column 'ParentId'.\r\n<\/pre>\n
Na, most m\u00e1s sokkal jobban \u00e9rzem magam. Az\u00e9rt z\u00e1r\u00e1sul \u00e9rdemes m\u00e9g azon elgondolkodni, hogy a t\u00f6bbsoros update-n\u00e9l a szerver hogy nem akad bele az \u00faj contraint\u00fcnkbe? Honnan tudja, hogy nem csak J\u00f3zsi lesz mozgatva, de a teljes rezesbanda is? A kulcssz\u00f3 a deferred constraint checking. Az SQL Server 2008 ezt NEM fogja tudni \u00e1ltal\u00e1ban. Ez arr\u00f3l sz\u00f3lna, hogy begin tran ut\u00e1n \u00fagy h\u00e1gjuk meg a constrainteket ahogy akarjuk, de mire eljutunk a commitig m\u00e1r mindennek klappolni kell. J\u00f3 lenne ez, de ez nincs \u00e9s nem is lesz a 2008-ban. Viszont egy update-en bel\u00fcl, \u00f6nmag\u00e1ra hivatkoz\u00f3 t\u00e1bl\u00e1k eset\u00e9n, mint a p\u00e9ld\u00e1ban, m\u00e9gis csak ezt teszik. M\u00e1sk\u00e9nt sok update, pl. update pk = pk + 1 se menne (gondolkodjunk rajta).
\nA t\u00e9m\u00e1t az Inside SQL Server 2000 j\u00f3l feldolgozza, interim violation-re keresve k\u00f6nnyen megtal\u00e1lhat\u00f3 benne. Az SQL 7-es verzi\u00f3 kinn van az msn\u00e9l<\/a> is, pont a megfelel\u0151 fejezet.<\/p>\n
Z\u00e1r\u00e1sul egy link a t\u00e9m\u00e1ban<\/a> a BOL-ra. Ha valaki elmagyar\u00e1zn\u00e1 nekem, hogy r\u00e9szfa-mozgat\u00e1skor mi\u00e9rt keresik ki az \u00faj sz\u00fcl\u0151 utols\u00f3 gyerek\u00e9t, \u00e9s az al\u00e1 rakj\u00e1k be a r\u00e9szfa gy\u00f6ker\u00e9t, nem az \u00faj sz\u00fcl\u0151 al\u00e1, megk\u00f6sz\u00f6nn\u00e9m.<\/p>\n
Ps. \u00e9n marha, most vettem \u00e9szre, hogy elfelejtettem publik\u00e1lni ezt a postot, pedig m\u00e1r vagy 4 napja meg\u00edrtam.<\/p>\n","protected":false},"excerpt":{"rendered":"
Nem akarom un\u00e1sig taglalni a t\u00edpust, de az el\u0151z\u0151 r\u00e9sz hib\u00e1j\u00e1t szeretn\u00e9m kijav\u00edtani. Ott \u00e1t akartam helyezni egy node-ot, aminek voltak gyermekelemei…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,58],"tags":[],"class_list":["post-395","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/395","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=395"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/395\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=395"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}