Ingyenes SQL Server 2008 könyv
Wednesday, January 23rd, 2008Lesz, ha kész lesz. :)
Addig is, már letölthető egy fejezet.
Archive for January, 2008
Nem megbízható constraintek okozta teljesítményproblémák
Wednesday, January 23rd, 2008SQL Server 2008 újdonságok 16. - térbeli adattípusok 2.
Tuesday, January 22nd, 2008Az előző rész végén volt egy kép, ami vizualizálta a geometriai alakzatokat. Ez a kis program a GeoQuery 2008, és rendkívül jó szolgálatot tesz megnézni, melyik függvény mire szolgál.
Ebben a részben bemutatok pár geometry metódust, szemérmetlenül ellopva a program beépített msdn példáit (köszönet a szerzőnek a feldolgozásért). Párat azért én is szültem. :)
A példákban a Thickness és a Color oszlopok csak a programnak szólnak, hogyan jelenítse meg az alakzatokat.
Területszámítás, STArea:
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g, @g.STArea() as [AreaInUnits], 0.1 as [Thickness];
8
Azaz van egy 3×3-as négyzetünk, amiben van egy 1×1-es luk. Így a területe 8, nem meglepő.
Határolóvonalak, STBoundary. Ez már nem teljesen triviális, a BOL sem segít egyelőre, csak a szabvány.
“-Point and MultiPoint instances do not have a boundary.
-LineString and MultiLineString boundaries are formed by the start points and end points, removing those that occur an even number of times.”
-The boundary of a Polygon consists of a set of LinearRings that make up its exterior and interior boundaries.”
DECLARE @g geometry;
SET @g = geometry::STPolyFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 1, 1 1))', 10);
SELECT 'Red' Color, 0.2 as [Thickness], @g
union all
select 'Yellow' Color, 0.1 as [Thickness], @g.STBoundary()
POLYGON((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 1, 1 1)) MULTILINESTRING((1 1, 1 2, 2 1, 1 1), (0 0, 3 0, 0 3, 0 0))
A piros az alakzat, a sárga a határolóvonala. A kettő egybeesik, csak az irányuk más, ez a kimenetből látszik, illetve az egyik sokszög, a másik vonalak halmaza.
Ugyanez vonalakkal:
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 0 2, 2 0)', 0);
SELECT @g, @g.STBoundary(), 0.1 as [Thickness];
LINESTRING(0 0, 2 2, 0 2, 2 0) MULTIPOINT((2,0), (0,0))
Látszik, hogy a szabványnak megfelelő a kimenet.
STBuffer. Azokat a pontokat adja vissza, amelyek egy alakzattól a megadott távolságra, vagy annál közelebb fekszenek.
Egyszerű vonallal:
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 4 0)', 0);
SELECT @g, @g.STBuffer(1), 0.1 as [Thickness];
Marha sok pontból álló POLYGON.
Bonyolultabb sokszöggel:
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 1, 1 1))', 0);
SELECT @g, 0.1 as [Thickness], 'Red' as Color
union all
select @g.STBuffer(1), 0.1 as [Thickness], 'Yellow' as Color;
Marha sok pontból álló POLYGON.
A sárga vonal a számított Buffer.
Középpont számítás, STCentroid. Matekosok utánakereshetnek, hogy számolják (érdekelne a link).
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g, @g.STCentroid(), 0.1 as [Thickness];
POINT(1.5 1.5)
Ez könnyű volt. Bonyolultabb alakzatra meg majd kiszámolja a gép. :)
Tartalmazza-e az egyik alakzat a másikat (teljesen), STContains:
DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g, @h, @g.STContains(@h), 0.1 as [Thickness];
1
Azt mondja, benne van, meglepő. Képet most nem mellékelek, láttunk már ilyen négyzetet.
Konkávból konvex, STConvexHull:
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 1 1, 2 2, 2 0, 0 0))', 0);
SELECT @g, 'Original' AS [Display], 'Blue' as [Color], 0.2 as [Thickness]
UNION ALL
SELECT @g.STConvexHull(), 'Hull', 'Green' as [Color], 0.1 as [Thickness];
A kék az eredeti, az zöld a kiegyengetett, konvexesített.
Metszi-e egymást két alakzat? STCrosses.
DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0)', 0);
SET @h = geometry::STGeomFromText('LINESTRING(0 0, 2 2)', 0);
SELECT @g, @h, @g.STCrosses(@h), 0.1 as [Thickness];
1
Különbségképzés, azok határolópontok által bekerített terület az egyik alakzatból, amelyek nincsenek benne egy másikban: STDifference.
DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT 'Original Polygons' as [Display], @g, @h, 'Green' as [Color], 0.2 as [Thickness]
UNION ALL
SELECT 'First minus second', null, @g.STDifference(@h), 'Blue' as [Color], 0.1 as [Thickness]
UNION ALL
SELECT 'Second minus first', null, @h.STDifference(@g), 'Orange' as [Color], 0.05 as [Thickness];
Original Polygons POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0)) POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1)) Green 0.20 First minus second NULL POLYGON ((0 0, 2 0, 2 1, 1 1, 1 2, 0 2, Blue 0.10 Second minus first NULL POLYGON ((2 1, 3 1, 3 3, 1 3, 1 2, 2 2, Orange 0.05
Jópofa. Az alsó zöld négyzet az egyik alakzatunk, a felső zöld a másik. A kék azt mutatja, ha az alsóból kivonjuk a felsőt, a sárga, ha a felsőből az alsót.
Folyt. köv.
Falopás
Tuesday, January 22nd, 2008A helyi újságban olvastam egy diósdi lakos levelét, amelyben arra panaszkodik, hogy ellopták a háza előtti fenyőfát. A rendőrség persze nem tud mit tenni (pesze, ha nincs felvétel a lopásról, tényleg mit tudna?).
A fényképen a fa kb. 80 cm magasságban van elvágva. Csak én gondolok arra, hogy…?
SQL Server 2008 újdonságok 15. - térbeli adattípusok 1.
Monday, January 21st, 2008Az új adattípusok tárgyalásának záró fázisához érkeztünk, már csak a két térbeli (spatial) típus maradt hátra: a geometry és a geography.
A geometry hagyományos, euklidészi, derékszögű, sík koordinátarendszerben dolgozik, míg a geograpy elliptikus, a Földön elhelyezkedő, földrajzi koordinátákat modellező típus (szélesség, hosszúság, stb.).
Koordinátákkal dolgozó emberkék most örülhetnek, végre natívan lehet a dolgaikat tárolni, és rengeteg műveletet (átfedik-e egymást alakzatok, milyen közel van, stb.) használhatnak rajtuk.
A típusok megértését eléggé megnehezíti, hogy a legtöbb átlagember számára ismeretlen szabványokra épülnek. Néhány fontosabbat felsorolok itt az elején. Open Geospatial Consortium (OGC)
Well-known text, WKT. Ez tulajdonképpen arról szól, hogyan lehet stringként leírni geometriai objektumokat.
Ennek párja a bináris leírás, Well-known binary, WKB. (Érdekes, a “jól ismert” fogalom mennyire relatív. :)
Pár WKT példa a korábbi wikis cikkből:
POINT(6 10)
LINESTRING(3 4,10 50,20 25)
POLYGON((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2))
MULTIPOINT(3.5 5.6,4.8 10.5)
MULTILINESTRING((3 4,10 50,20 25),(-5 -8,-10 -8,-15 -4))
MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2)),((3 3,6 2,6 4,3 3)))
GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10))
POINT ZM (1 1 5 60)
POINT M (1 1 80)
A legtöbb alakzat persze könnyen kitalálható a neve alapján, de a paraméterként megadott számok értelmezése nem triviális. Egyrészt kérdés az, hogy milyen mértékegységben dolgoznak (pl. méter), másrészt, hogy milyen vonatkoztatási ponthoz képest értelmezzük azokat. Nekem kézenfekvő lett volna Greenwich mint egyik koordináta nullpont, másik irányból az egyenlítő, harmadikként pedig a tengerszint, de persze az élet sokkal színesebb ennél, hisz mindenki szereti az otthonában látni a nullpontot, nála legyen a világ közepe. Legyen pusztavacsi a koordináta rendszer. :)
No, a típusoknál olyan vonatkoztatási pontokat, referenciapontokat, spatial reference identifier-t, SRID-t használnak, amelyeket a European Petroleum Survey Group (EPSG) standard definiál. Európa egy amerikai termékben? Nofene.
Hogy ne lógjon a levegőben ez az infó, meg legyen már egy kis SQL is, mindjárt nézzük is meg, milyen vonatkoztatási rendszereket ismer a szerver:
select * from sys.spatial_reference_systems
spatial_reference_id authority_name authorized_spatial_reference_id well_known_text unit_conversion_factor -------------------- -------------- ------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------------------- 4120 EPSG 4120 GEOGCS["Greek", DATUM["Greek", ELLIPSOID["Bessel 1841", 6377397.155, 299.1528128]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]] metre 1 4121 EPSG 4121 GEOGCS["GGRS87", DATUM["Greek Geodetic Reference System 1987", ELLIPSOID["GRS 1980", 6378137, 298.257222101]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]] metre 1 4122 EPSG 4122 GEOGCS["ATS77", DATUM["Average Terrestrial System 1977", ELLIPSOID["Average Terrestrial System 1977", 6378135, 298.257]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]] metre 1 4123 EPSG 4123 GEOGCS["KKJ", DATUM["Kartastokoordinaattijarjestelma (1966)", ELLIPSOID["International 1924", 6378388, 297]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]] metre 1 4124 EPSG 4124 GEOGCS["RT90", DATUM["Rikets koordinatsystem 1990", ELLIPSOID["Bessel 1841", 6377397.155, 299.1528128]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]] metre 1 ... 4326 EPSG 4326 GEOGCS["WGS 84", DATUM["World Geodetic System 1984", ELLIPSOID["WGS 84", 6378137, 298.257223563]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]] metre 1 ... 4237 EPSG 4237 GEOGCS["HD72", DATUM["Hungarian Datum 1972", ELLIPSOID["GRS 1967", 6378160, 298.247167427]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]] metre 1 ...
Az SQL Server 2008 alapértelmezett módon a 4326-os SRID-t használja, ami Greenwich-i kezőpontú, méterben és fokban dolgozik, és ez így van jól, szimpatikus választás. Laikusoknak WGS 84. (Megvan a poén a Dumb és Dumberből? Canis- laikusoknak kutya. :)
És egy utolsó szabványhivatkozás, aztán programolunk. A geometry típust az Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0-nak megfelelően implementálták, a standardot megnézve látszik, hogy a metódusok neve, stb. szépen illeszkedik a szabványhoz.
Ebben a részben kezdjünk az egyszerűbbel, a geometry típussal, mégiscsak egyszerűbb derékszögű rendszerben gondolkodni, mint egy elliptikus izén.
Hozzunk létre egy pontot reprezentáló változót:
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT (3 4)', 0);
select @g.ToString()
Kimenet:
POINT (3 4)
STGeomFromText a már hivatkozott WKT szövegből parsolja ki az alakzatot. Az SRID 0, azaz nem definiált lesz, geometrynél ez ok, geographynál viszont ez kötelező, csak kell egy kiindulópont, ha valami földi alakzatatot akarunk belőni. Használhattuk volna a specializáltabb STPointFromText metódust is.
Játszunk kicsit a pontokkal, rakjuk össze őket egy csapatba (STUnion):
declare @a geometry = geometry::STGeomFromText('POINT(0 0)', 0);
declare @b geometry = geometry::STGeomFromText('POINT(4 4)', 0);
select @a.STUnion(@b).ToString();
MULTIPOINT ((4 4), (0 0))
Csináljunk belőle vonalat:
select @a.STUnion(@b).STConvexHull().ToString();
LINESTRING (4 4, 0 0)
Mi az a Convex Hull, azaz konvex test?
“Az A halmazt konvexnek nevezzük, ha bármely két pontját összekötve az egyenes pontjai az halmazon belül maradnak.”
Szóval ez a metódus konvex testeket tud összerakni, ami most csak egy vonal lett, mivel 2 pontot adtunk meg. Adjunk meg 3-at, mit csinál belőle?
declare @c geometry = geometry::STGeomFromText('POINT(0 4)', 0);
select @a.STUnion(@b).STUnion(@c).STConvexHull().ToString();
POLYGON ((4 4, 0 4, 0 0, 4 4))
Ezt még elég könnyű elképzelni, ez egy csúcsán álló, derékszögű háromszög. Nem négyszög, mert az első és utolsó pontja is 4, 4.
Vizuálisok kedvéért a vonalunk és a háromszögünk:
Folyt. köv.
Nem töltsd le filmet - reklám (humor)
Monday, January 21st, 2008SQL Server 2008 újdonságok 14. - HierarchyID adattípus 5.
Saturday, January 19th, 2008Nem 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.
A nap híre: .NET Framework Library Source Code now available
Thursday, January 17th, 2008Ma mindenkinél erről lehet majd hallani, örülünk, böngészünk, debugolunk.
SQL Server 2008 újdonságok 13. - HierarchyID adattípus 4.
Monday, January 14th, 2008Pár záró gondolat a típusról.
Eddig mindig arra használtam, hogy egy hierarchiában a node-okhoz legyen közvetlen pointerünk, ezzel egyes lekérdezéseket jelentősen fel lehetett gyorsítani. Ennek ellenére ez a típus nem más, mint egy nagyon tömör, számunkra elérési útként értelmezhető adatot tároló valami. Hogy ez az elérési út passzoljon a tényleges hierarchiához csakis a mi felelősségünk, az égvilágon senki nem fogja biztosítani, hogy a HiearchyID-ben tárolt adatnak bármi köze is van a valósághoz. Nem olyan, mint pl. egy foreign key-jel védett kapcsolat, ahol azért csökken a baromkodás esélye.
A típus igen kompakt, pár bájtot vesz csak igénybe a tárolása, még nagyon nagy fák esetén is. Ha kicsit utána akarunk nézni, hogyan implementálták, akkor reflectorral meg kell nyitni a Microsoft.SqlServer.Types.dll assemblyt a GAC-ból, ebben vannak a CLR SQL típusok implementálva (a többi is, amelyekről a későbbi cikkekben még lesz szó).
A File Disassembler pluginnal teljes egészében vissza lehet fejteni forráskódra, kicsit nézzünk bele. Habár C++/CLI-ben írták, C#-ként mutatok be egy-két részletet, mégha így pár dolog csúnya is (nincs const a C#-ban pl.). Maga a típus így van deklarálva:
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered=true, MaxByteSize=0x37c, Name="SqlHierarchyId")]
public class SqlHierarchyId : IBinarySerialize, INullable
{
// Fields
private OrdPath ordpath;
...
}
Mivel a típus implementálja az IBinarySerialize interfészt, amikor le kell tárolni a típus adatait, a Write metódust hívja meg a szerver. Ennek lényegi része:
public void Write(BinaryWriter w)
{
w.Write(this.ordpath.m_bytes, 0, (this.ordpath.m_bitLength + 7) / 8);
}
Azaz az OrdPath m_bytes adattagja, ennek bináris szerkezete dönti el, hogy indexelésnél hogyan viselkedik a típus. Ezt ügyesen úgy rakták össze, hogy mélységi módon rendezze az általa reprezentált fát.
Láthatóan a lényeg igazából az OrdPath típusban van, az SqlHieararchyID csak egy facade hozzá.
Az OrdPath belülről már bonyolultabb:
internal class OrdPath
{
// Fields
public ushort m_bitLength;
public ushort[] m_bitOffsets;
public byte[] m_bytes;
public uint modopt(IsLong) m_level;
public uint modopt(IsLong) m_parentBitLengthIndex;
public uint modopt(IsLong) m_parsedLevels;
public SubType m_subType;
...
Amikor stringből képeznek HierarchyID-t, akkor is az OrdPath dolgozik (Parse() hívás vagy SQL CAST vagy CONVERT). SqlHiearchyId.Parse:
public static SqlHierarchyId Parse(SqlString input)
{
string chDottedString = input.Value;
...
SqlHierarchyId id = new SqlHierarchyId();
id.ordpath = <strong>new OrdPath(chDottedString);</strong>
return id;
}
Akit érdekel, az OrdPath konstruktorban megnézheti a konkrét bitkolbászolást, az már túl hosszú, hogy itt kielemezzem.
Érdemes még megnéznük, hogyan kell az adatokat módosítani, a fát kezelni a HierarchyID jelenlétében.
Csak kóstolóként mutatok egy példát, amiben Józsit előléptették, új főnököt kap a hierarchia magasabb szintjén.
begin tran declare @jozsi hierarchyid = (select OrgNode from HumanResources.NewOrg where LoginID = 'adventure-works\dylan0') --2/1/4 declare @jozsiujfonoke hierarchyid = (select OrgNode from HumanResources.NewOrg where LoginID = 'adventure-works\david0') --/1/ select @jozsi.ToString() Jozsi, --/2/1/4/ @jozsiujfonoke.ToString() JozsiUjFonoke, -- /1/ @jozsiujfonoke.GetDescendant(null, null).ToString() ElsoBeosztott, --/1/1/ @jozsiujfonoke.GetDescendant(null, @jozsiujfonoke.GetDescendant(null, null)).ToString() ElsoBeosztottElotti --/1/0/ update HumanResources.NewOrg set OrgNode = @jozsiujfonoke.GetDescendant( null, @jozsiujfonoke.GetDescendant(null, null)) where OrgNode = @jozsi rollback tran
Az egészben a GetDescendant metódus a kulcs. Ez sokféleképpen tud visszaadni gyereket, attól függően, hogy milyen két paramétert kap. A fontosabb esetek:
1. If parent is not NULL, and both child1 and child2 are NULL, returns a child of parent.
2. If parent and child1 are not NULL, and child2 is NULL, returns a child of parent greater than child1.
3. If parent and child2 are not NULL and child1 is NULL, returns a child of parent less than child2.
4. If parent, child1, and child2 are all not NULL, returns a child of parent greater than child1 and less than child2.
Az ElsoBeosztott az 1. szabály alapján képeztetett, az a child szemmel láthatóan az első jelenti (legalábbis most). Aztán a 3. szabály értelmében lekértem az ElsoBeosztottElotti id-t. Ide pozícionálom újra az update segítségével Józsit, így ő lesz az első gyerek a főnök alatt. Biztos boldog ettől. :)
UPDATE!
Babatologatás közben rájöttem, hogy a fenti példa hibás, elrontja a hierarchiát, mert csak egy node-ot mozgatok, nem egy komplett részfát, így árván maradnak Józsi beosztottjai. A következő részben bemutatom a javítást.
Zárásul érdekességként nézzük meg, hogy képezik a node azonosítót, ha két egész szám között már nincs hely további egésznek:
select @jozsiujfonoke.GetDescendant( @jozsiujfonoke.GetDescendant(null, @jozsiujfonoke.GetDescendant(null, null)).ToString(), @jozsiujfonoke.GetDescendant(null, null).ToString()).ToString()
Kimenet:
/1/0.1/
Kicsit vad, de mi mást lehetne kitalálni?
C++/CLI addin reflectorhoz
Friday, January 11th, 2008Ha egy kódot nem C#-ban, hanem C++/CLI-ben írtak, akkor reflectorral C#-ra visszafejtve elég ronda lesz, mert a C++-ban vannak dolgok, amik C#-ban nem reprezentálhatók, mint a const módosító.
Ezért jöhet jól a C++/CLI addin, amivel “eredetiben” nézhetjük meg az assemblyk belsejét. Az SQL Server 2008 típusait C++/CLI-ben írták, így érhető, miért kellett ez nekem…
SQL Server 2008 újdonságok 12. - HierarchyID adattípus 3.
Wednesday, January 9th, 2008Lássunk most egy olyan lekérdezést, amiben nem annyira fényes a HierarcyID.
Szeretném lekérdezni a közvetlen beosztottakat, azaz egy node első szintű gyerekeit, az indirekt utódok nem érdekelnek. Ehhez egy új metódust vetünk be, a GetAncestort, melynek paramétere a kívánt szintű előd, esetünkben 1, mert a közvetlen szülő érdekel:
declare @manager hierarchyid = (select OrgNode from HumanResources.NewOrg where LoginID = 'adventure-works\roberto0') select @manager.ToString() select cast(OrgNode as varchar(50)) as OrdPath, EmployeeID, LoginID, ManagerID, Title from HumanResources.NewOrg where OrgNode.GetAncestor(1) = @manager
Kimenet:
------------------------------ /2/1/ OrdPath EmployeeID LoginID ManagerID Title ------------------------------ ----------- ------------------------------ ----------- ------------------------------ /2/1/1/ 4 adventure-works\rob0 3 Senior Tool Designer /2/1/2/ 9 adventure-works\gail0 3 Design Engineer /2/1/3/ 11 adventure-works\jossef0 3 Design Engineer /2/1/4/ 158 adventure-works\dylan0 3 Research and Development Manag /2/1/5/ 263 adventure-works\ovidiu0 3 Senior Tool Designer /2/1/6/ 267 adventure-works\michael8 3 Senior Design Engineer /2/1/7/ 270 adventure-works\sharon0 3 Design Engineer
Szemre jónak néz ki a kimenet, csak az első szintű utódok jöttek le. Lássuk a végrehajtási tervet:
Látható, hogy a HierarchyID alapján már nem képes csak index seek-kel leválogatni a tartalmat, mert azzal csak az összes utódot tudja megszűrni, nekünk meg csak a közvetlen utódok kellenek. Ezért van ott a Filter operátor, ő az összes utódból leválogatja a kért szinten levőket.
Hogy ez mennyire nem hatékony úgy buktatható le, ha keresünk egy olyan embert, akinek nagyon sok idirekt beosztottja van, de csak kevés közvetlen. Nyilván a fa tetején ülnek ezek, nézzünk csak egy kis statisztikát:
select LoginID, OrgNode.ToString() as Path, (select COUNT(*) c from HumanResources.NewOrg i where o.OrgNode.IsDescendant(i.OrgNode) = 1) as TotalNumberOfDescedants from HumanResources.NewOrg o order by TotalNumberOfDescedants desc
Futtatva:
LoginID Path TotalNumberOfDescedants ------------------------------ ------------------------------ ----------------------- adventure-works\ken0 / 290 adventure-works\james1 /5/ 209 adventure-works\peter0 /5/1/ 185 adventure-works\laura1 /4/ 29 adventure-works\brian3 /6/ 18 adventure-works\jack0 /5/1/19/ 14 adventure-works\terri0 /2/ 14 ...
Ken0 a nagykutya, futtassuk le vele a kiinduló lekérdezést, és nézzük meg a tényleges (nem jósolt) végrehajtási tervet:
Látható, hogy vastag nyíl jön ki az index seekből, 290 sort válogat le az index, ami nem meglepő az előző táblázat alapján. A Filter ebből csak 6-ot tart meg. Tulajdonképpen bár index seek az operátor, a teljes táblát végignyalta (table scan), ennek megfelelően az IO költsége 9 lapolvasás, ekkora a tábla (ha precíz akarok lenni, a tábla 7 lapból áll, plusz 2 IO másra megy el).
Vessük ezt össze a relációs lekérdezéssel, ami triviális lesz:
declare @ManagerID int = ( select EmployeeID from HumanResources.NewOrg where LoginID = 'adventure-works\ken0') select * from HumanResources.NewOrg where ManagerID = @ManagerID
Ez pusztán 2 lapolvasással jár (csak a 2. select, az elsőt a méréseknél nem veszem figyelembe), jó, épít az előző részben felépített cover indexre. A végrehajtási terv pusztán egy index seekből áll, azért ilyen piszok hatékony.
Összegezve, direkt gyermekek lekérdezése esetén a relációs megoldás általában gyorsabb, mint a HierarchyID alapú. Persze, azért denormalizálással lehet még itt alakítani.
A HierarchyID indexe a következő sorrendben rendezi be az adatokat (BOL-ból lopva):
Ami nekünk kellene a direkt gyerekek hatékony szűréséhez, az a szélességi bejárás alapján rendezett index:
Készítsünk ilyet! Ehhez fel kell vennünk a táblába egy új, számított oszlopot, ami a node-ok szintjét számolja ki (GetLevel metódus):
alter table HumanResources.NewOrg add OrgLevel as OrgNode.GetLevel()
Csak, hogy lássuk, hogy is néz ez ki:
select OrgNode.ToString(), OrgLevel, * from HumanResources.NewOrg order by OrgNode
Path OrgLevel EmployeeId ManagerID ------------------------------ -------- ----------- ----------- / 0 109 NULL /1/ 1 6 109 /1/1/ 2 2 6 /1/2/ 2 46 6 /1/3/ 2 106 6 /1/4/ 2 119 6 /1/5/ 2 203 6 /1/6/ 2 269 6 /1/7/ 2 271 6 /1/8/ 2 272 6 /2/ 1 12 109 /2/1/ 2 3 12 /2/1/1/ 3 4 3 /2/1/2/ 3 9 3 /2/1/3/ 3 11 3 /2/1/4/ 3 158 3 /2/1/4/1/ 4 79 158
No, most jön az index a számított oszlopra:
create nonclustered index IDX_Org_Breadth_First ON HumanResources.NewOrg(OrgLevel, OrgNode) include (EmployeeID, LoginID, ManagerID, Title);
Megint egy bazi nagy index, nem biztos, hogy kell ennyi oszlop az include részbe, de így lefedtük a *-os lekérdezéseket is.
A kiinduló lekérdezés (ken0-val) végrehajtási terve alaposan megváltozik:
Állat, mi? Sima index seek lett, ráharapott az újdonsült indexünkre! Fel van arra készítve az optimizer, hogy a GetAncestor-t hatékonyan tudja végrehajtani, ha meg van támogatva egy jó kis breadth-first index-szel.
Amúgy 2 lapolvasásból áll így a lekérdezés, az eredeti 8 helyett. Mondanom sem kell, a poén az, hogy ha a tábla mondjuk 2%-a jön le a lekérdezés hatására, akkor ez az optimalizált verzió csak a tábla ötvenedét járja be, míg az eredeti az egészet! Nagy fák esetén ez brutális különbséget jelent, nem csak négyszereset, mint a példámban.
A témáról bővebben a BOL-ban lehet olvasni.
Ebben kielemzik a HierarchyID vs. rekurzív vs. xml lehetőségeket, ezt egyelőre nem részletezem tovább, annyi sok más újdonságról akarok még írni.
A következő részben még egyszer játszunk a HierarchyID-vel, nem csak lekérdezzük, de módosítjuk is a fát.
SQL Server 2008 újdonságok 11. - HierarchyId adattípus 2.
Tuesday, January 8th, 2008Mire jó a HierarchyID? Vannak műveletek, amelyeket gyorsabban lehet végrehajtani a segítségével, mivel a node-ok elérési útja van enkódolva az idben, így a felindexelt id alapján egyes lekérdezések hatékonyak lehetnek.
Nézzük meg pl. hogyan keresnénk meg egy adott ember összes direkt vagy indirekt beosztottját? Azaz, az adott node alatti részfát szeretnénk kiválasztani (az előző rész adataira alapozok).
declare @manager hierarchyid = (select OrgNode from HumanResources.NewOrg where LoginID = 'adventure-works\terri0') --select @manager.ToString() select cast(OrgNode as varchar(50)) as OrdPath, EmployeeID, LoginID, ManagerID, Title from HumanResources.NewOrg where @manager.IsDescendant(OrgNode) = 1 order by OrdPath
Kikeressük terri0 HierarchyID-jét, majd az IsDescendant metódus segítségével leszűrjük az utódait. Gyerekek, unokák, stb. (kicsit bizarr, hogy pont Terri nevű emberről szól a példánk… :). A függvény magát a kiinduló node-ot is visszaadja, azaz a DescendantOrSelf név precízebb név lenne (persze ez egy CTP verzió, ki tudja, hogy lesz még a véglegesben).
A kimenet:
OrdPath EmployeeID LoginID ManagerID Title ------------------------------ ----------- ------------------------------ ----------- ------------------------------ /2/ 12 adventure-works\terri0 109 Vice President of Engineering /2/1/ 3 adventure-works\roberto0 12 Engineering Manager /2/1/1/ 4 adventure-works\rob0 3 Senior Tool Designer /2/1/2/ 9 adventure-works\gail0 3 Design Engineer /2/1/3/ 11 adventure-works\jossef0 3 Design Engineer /2/1/4/ 158 adventure-works\dylan0 3 Research and Development Manag /2/1/4/1/ 79 adventure-works\diane1 158 Research and Development Engin /2/1/4/2/ 114 adventure-works\gigi0 158 Research and Development Engin /2/1/4/3/ 217 adventure-works\michael6 158 Research and Development Manag /2/1/5/ 263 adventure-works\ovidiu0 3 Senior Tool Designer /2/1/5/1/ 5 adventure-works\thierry0 263 Tool Designer /2/1/5/2/ 265 adventure-works\janice0 263 Tool Designer /2/1/6/ 267 adventure-works\michael8 3 Senior Design Engineer /2/1/7/ 270 adventure-works\sharon0 3 Design Engineer
Az OrdPathból jól látható, de az EmployeeID-k és ManagerID-k alapján szemre is kikereshető, hogy az összes utód szerepel a listában.
A lekérdezés költségének elemzéséhez kiveszem az OrdPath oszlopot, és a order by-t is, hogy tisztán a szűrés költségét lássuk. Illetve rakok egy indexet a LoginID oszlopra, hogy az első sor szűrése gyors legyen:
create nonclustered index IDX_LoginID on HumanResources.NewOrg(LoginID)
A csupasz lekérdezés:
declare @manager hierarchyid = (select OrgNode from HumanResources.NewOrg where LoginID = 'adventure-works\terri0') select EmployeeID, LoginID, ManagerID, Title from HumanResources.NewOrg where @manager.IsDescendant(OrgNode) = 1
A végrehajtási terv:
Mint látható (kattintani kell a képre) az IsDescedantra fel van készítve az optimizer (!), és a lekérdezést a leghatékonyabb módon, index seek-kel hajtja végre (alul, jobb oldalt). Nézzük meg a szűrőfeltételt is az index operátorhoz:
Seek Keys[1]: Start: [AdventureWorks].[HumanResources].[NewOrg].OrgNode >= Scalar Operator([@manager]), End: [AdventureWorks].[HumanResources].[NewOrg].OrgNode <= Scalar Operator([@manager].DescendantLimit())
Látható, hogy kihasználja azt, hogy az összes utód egy bagázsban van, mivel az OrdPath így van rendezve (emlékezzünk az előző részből, a HieararchyID oszlopunkon volt egy clustered index: constraint PK_NewOrg_OrgNode primary key clustered (OrgNode)). A DescendantLimit egy belső függvény, ez jelzi, hol váltanak szintet, meddig kell keresni. Ügyes. A lekérdezés költsége minimális, az első és a második rész összege 6.6 ms (két index seek). Persze, az igazán izgalmas, mekkora ennek a költsége a rekurzív CTE megoldáshoz képest, ami így nézne ki:
with Descendants(EmployeeID, LoginID, ManagerID, Title) as ( select EmployeeID, LoginID, ManagerID, Title from HumanResources.NewOrg where LoginID = 'adventure-works\terri0' union all select e.EmployeeID, e.LoginID, e.ManagerID, e.Title from HumanResources.NewOrg as e join Descendants as d on e.ManagerID = d.EmployeeID ) select * from Descendants
Legyünk igazságosak, a HieararchyID oszlopon volt egy clustered index, az persze megbikázta a lekérdezést mint állat. Szegény CTE-s megoldásunk meg join-ol keményen, kapjon hát hozzá olyan indexeket, amelyek rendesen megtámogatják. Mivel a ManagerID nem túl szelektív (egy adott ManagerID-re a tábla jelentős része visszajöhet), ezért annak included column-ot is tartalmazó nc indexet adok, hogy cover query-t tudjon csinálni a szerver.
create unique nonclustered index IDX_EmployeeID on HumanResources.NewOrg(EmployeeID) go create nonclustered index IDX_ManagerID on HumanResources.NewOrg(ManagerID) include (EmployeeID, LoginID, Title)
(Finomság: az OrgNode nincs benne az indexben, mivel az a clustered index kulcsa, így mindenképpen benne van minden nc indexben is, így kár lenne kétszer belerakni.)
Ezen megoldásban a kimenet sorrendje más, de az adatok benne ugyanazok, és mivel most is csak a szűz lekérdezés költségére koncentrálunk, az order by-t itt is kihagytuk.
A terv költsége 21 ms. Ezekkel az adatokkal a HierarchyID-s megoldás 3x gyorsabb. Minimum, ugyanis míg az első megoldás 5 lapolvasást igényelt, a CTE-s 118-at, és még egy temp táblát is használt! A gyakorlatban ez azt jelenti, hogy terhelt szervernél sokkal lassabb lesz, mint a 3-as szorzó.
Összegezve, ha egy node utódjait reprezentáló részfát akarunk leválogatni, akkor egy rendesen karbantartott és felindexelt HierarchyID oszlop hatékony lekérdezéseket tesz lehetővé, hatékonyabbat, mint a hagyományos rekurzív megoldás.
Zárásul egy figyelmeztetés. NEM minden esetben gyorsabb a HierarchyID a hagyományos rekurzív kompozícióval szemben, szóval ne halljam vissza, hogy soci azt mondta, dobjuk ki a ManagerID-t, és használjunk HiearchyID-t. Nem, a következő részben mutatok olyan esetet, amikor a hagyományos relációs megoldás gyorsabb. Nyilván olyan lekérdezést kell megfogalmazni, ami nem tudja kihasználni a HiearchyID mélységi bejárás alapú indexelését.
Cool SQL Server blokkoláselemző script
Monday, January 7th, 2008A cím magáért beszél, akinek időnként bele kell ezekbe a dolgokban kukkantani, nézze meg.
SQL Server 2008 újdonságok 10. - HierarchyId adattípus 1.
Monday, January 7th, 2008Egy érdekes típussal kezdünk most ismerkedni, amelyet kissé nehezen sikerült felfognom, tudjuk ezt be a koromnak. :)
HierarchyId. Mit sugall ez a név? Ő egy olyan típus, amely egy hierarchia, azaz egy fa egy adott pontját tudja megcímezni. Hogyan lehet relációs adatbázisban fát építeni? Pl. rekurzív, önhivatkozó táblával, mint a Northwind adatbázis Employees táblája, vagy az AdventureWorks adatbázis HumanResources.Employee táblája. Ez utóbbiban a ManagerID oszlop mutat a főnök EmployeeID-jára.
Az így felépített fa tetszőleges eleme jellemezhető egy úgynevezett OrdPath-szal. Ebben a gyermekelemeknek sorrendje van, mint pl. az xml infosetben, így a gyerekek megcímezhetők a szülő alatt sorszámukkal. 1/2/4 pl. a gyökér node 2. gyermekének a 4. gyerekét jelenti. Az előbbi linken vizualizálják is, érdemes megnézni.
Update: a link időnként nem elérhető, ezért tükröztem a doksit.
No, a HierarchyId egy olyan CLR típus (ez az első alkalom, hogy egy típust .NET-ben írt meg az MS), amely egy OrdPath-ot képes letárolni. A doksi ezt így nem írja le, de így talán könnyebb megérteni. Segítségével tulajdonképpen igen kompakt módon le lehet tárolni egy hierarchia node helyét egy fában. Normál esetben pl. rekurzív CTE-vel járhatunk be egy hierarchiát, hogy meghatározzuk az elérési útját egy node-nak. Ez elég lassú persze, minden szinthez kell egy JOIN. Egy táblában HierarchyId oszlop segítségével minden egyes, a fa egy node-ját reprezentáló sorhoz letárolhatjuk a sor mint fa-node a hierarchiában elfoglalt helyét, így rekurzió nélkül is azonnal látható, hol foglal helyet a hierarchiában az adott sor (mint node).
A HierarchyId felfogható egyfajta denormalizálási technikának is, hisz a hierarchia letárolható a már említett relációs módon is. Akár egyszerre is lehet használni a kettőt, de külön-külön is. Vannak esetek, amikor az egyik lesz hatékonyabb, van, amikor a másik. Ezeket majd későbbi részekben kielemzem, most egyelőre lássunk egyáltalán egy példát, mi a csuda ez a típus.
Induló adatokat úgy gyártok, hogy fogom az HumanResources.Employee táblát, ami relációs módon tárol szervezeti hierarchiát, és ezt átalakítom HierarchyId reprezentációra (a példa a BOL-beli minta alaján készült).
Készítsünk egy keskeny táblát, hogy ne zavarjon meg minket a sok adat:
select EmployeeID, LoginID, ManagerID, Title into HumanResources.EmployeeDemo from HumanResources.Employee;
Mit találunk benne?
SELECT Mgr.EmployeeID AS ManagerID, Mgr.LoginID AS Manager, --Mgr.Title as ManagerTitle, Emp.EmployeeID AS EmployeeID, Emp.LoginID as Employee --Emp.Title as EmployeeTitle FROM HumanResources.EmployeeDemo AS Emp LEFT JOIN HumanResources.EmployeeDemo AS Mgr ON Emp.ManagerID = Mgr.EmployeeID ORDER BY ManagerID, EmployeeID
ManagerID Manager EmployeeID Employee ----------- ------------------------------ ----------- ------------------------------ NULL NULL 109 adventure-works\ken0 3 adventure-works\roberto0 4 adventure-works\rob0 3 adventure-works\roberto0 9 adventure-works\gail0 ... 3 adventure-works\roberto0 270 adventure-works\sharon0 6 adventure-works\david0 2 adventure-works\kevin0 6 adventure-works\david0 46 adventure-works\sariya0 ... 6 adventure-works\david0 272 adventure-works\mary2 7 adventure-works\jolynn0 37 adventure-works\simon0 ...
Látható, hogy a 109-es sorszámú ken0 a főgyökér, a legnagyobb főnök. A 3-as kisfőnök beosztottjai 4, 9. stb. számú dolgozók.
No, hozzunk létre egy új táblát, ami ugyanezen adatokat tartalmazza, de most lesz minden node-ra egy közvetlen id-nk is:
create table HumanResources.NewOrg ( OrgNode hierarchyid, EmployeeId int, LoginID nvarchar(50), ManagerID int, Title nvarchar(100) constraint PK_NewOrg_OrgNode primary key clustered (OrgNode) )
Az OrgNode nevű oszlop lett HierarchyId adattípussal pluszként felvéve.
Most jön az adatok átbűvölése. A feladat, hogy rekurzívan járjuk be a hierarchiát, és menet közben generáljuk le a HierarchiId-ket. Azonban emlékezzünk rá, hogy az OrdPath sorrenddel dolgozik (benne van a nevében is, Order Path), így be kellene vezetni valamiféle sorszámozást, amely valamely (tetszőleges) szempont szerint sorbarendezi egy szülő (főnök) gyermekeit (beosztottak). Nem nagy ügy, köszönhetően a 2005-ben megjelent row_number függvénynek. Egy segédtáblában rakjuk össze a sorszámokat:
CREATE TABLE #Children ( EmployeeId int, ManagerID int, Num int ); go create clustered index tmpind on #Children(ManagerID, EmployeeID); go insert #Children (EmployeeID, ManagerID, Num) select EmployeeID, ManagerID, row_number() over (partition by ManagerID order by EmployeeID) from HumanResources.EmployeeDemo; go
Főnökök (partition by ManagerID) szerint csoportosítva generáljuk a sorszámokat a beosztottakhoz, EmpoyeeID szerint sorbarendezve (order by EmployeeID). Valójában tök mindegy, mi szerint rendezünk, hisz az eredeti táblában nem is volt sorrend, ne felejtsük el, a relációs adatbázisok halmazokkal, és nem sorrendezett adatokkal dolgoznak. Most viszont az OrdPath miatt ki kell alakítani valami sorrendet.
Kukkantsunk bele a generált táblába:
select ManagerId, EmployeeID, Num from #Children order by ManagerId, Num
ManagerId EmployeeID Num ----------- ----------- ----------- NULL 109 1 3 4 1 3 9 2 3 11 3 3 158 4 3 263 5 3 267 6 3 270 7 6 2 1 6 46 2 6 106 3
Az első sor HierarchyId-je / lenne, ő a gyökér. A második sor HierarchyId-ját nem tudjuk kapásból megmondani, mert ahhoz vissza kellene menni, hogy ki a 3-as ember főnöke, egészen a gyökérig visszalépdelve, és akkor a sorrend alapján már lehetne képezni egy OrdPath-ot. Nos, itt az ideje, hogy legeneráljuk az id-kat, egy rekurzív CTE-vel:
with paths(Path, EmployeeID)
as
(
--Gyökér
select
cast('/' as hierarchyid) as OrgNode,
EmployeeID
from #Children
where ManagerID is null
union all
--A gyökér alatti összes szint
select
cast(p.Path.ToString() + cast(c.Num as varchar(30)) + '/' AS hierarchyid),
c.EmployeeID
from #Children as c
join Paths as p
on c.ManagerID = p.EmployeeID
)
insert HumanResources.NewOrg
(OrgNode, O.EmployeeID, O.LoginID, O.ManagerID, O.Title)
select P.path, O.EmployeeID, O.LoginID, O.ManagerID, O.Title
from HumanResources.EmployeeDemo as O
join Paths as P
on O.EmployeeID = P.EmployeeID;
go
A HierarchyId-t ez a sor rakja össze:
cast(p.Path.ToString() + cast(c.Num as varchar(30)) + '/' AS hierarchyid)
Fogjuk a szülő elérési útját (p.Path), átalakítjuk stringgé (.ToString(), elég .NET-es? :), hozzáfűzzük a gyerek sorszámát és egy záró / jelet (+ cast(c.Num as varchar(30)) + ‘/’). Az eredő stringet vissza cast-oljuk HierarchyId-vá. Lehetne másképp is, mert van pár szülő-gyerek kezelő metódusa a típusnak, de erről majd egy másik részben írok.
Aki nem látott még CTE-et, a union feletti rész egyszer fut le, az generálja le a gyökér sort, a alatta levő rész pedig mindig az előző lépés által generált gyerek sorokra hajtódik végre, amíg be nem járja az összes szintet. Ez ugye egy rekurzív self-join.
Nézzünk bele a műbe:
SELECT OrgNode.ToString() AS LogicalNode, * FROM HumanResources.NewOrg ORDER BY LogicalNode;
LogicalNode OrgNode EmployeeId LoginID ManagerID Title ------------------------------ -------------------------------- ----------- ------------------------------ ----------- ------------------------------ / 0x 109 adventure-works\ken0 NULL Chief Executive Officer /1/ 0x58 6 adventure-works\david0 109 Marketing Manager /1/1/ 0x5AC0 2 adventure-works\kevin0 6 Marketing Assistant /1/2/ 0x5B40 46 adventure-works\sariya0 6 Marketing Specialist /1/3/ 0x5BC0 106 adventure-works\mary0 6 Marketing Specialist /1/4/ 0x5C20 119 adventure-works\jill0 6 Marketing Specialist /1/5/ 0x5C60 203 adventure-works\terry0 6 Marketing Specialist /1/6/ 0x5CA0 269 adventure-works\wanida0 6 Marketing Assistant /1/7/ 0x5CE0 271 adventure-works\john5 6 Marketing Specialist /1/8/ 0x5D10 272 adventure-works\mary2 6 Marketing Assistant /2/ 0x68 12 adventure-works\terri0 109 Vice President of Engineering /2/1/ 0x6AC0 3 adventure-works\roberto0 12 Engineering Manager /2/1/1/ 0x6AD6 4 adventure-works\rob0 3 Senior Tool Designer /2/1/2/ 0x6ADA 9 adventure-works\gail0 3 Design Engineer /2/1/3/ 0x6ADE 11 adventure-works\jossef0 3 Design Engineer /2/1/4/ 0x6AE1 158 adventure-works\dylan0 3 Research and Development Manag /2/1/4/1/ 0x6AE158 79 adventure-works\diane1 158 Research and Development Engin /2/1/4/2/ 0x6AE168 114 adventure-works\gigi0 158 Research and Development Engin
Szép, mi? De mi a csudára jó ez? Miért jó, hogy meg van minden sor OrdPath-ja (HierarchyId-je)? A következő részből kiderül. Stay tuned. :)
Élet 3 gyerkőccel
Friday, January 4th, 2008BUÉK.
Egyelőre nem SQL Server 2008-alok, mert éppen beteg mind a 3 gyerek. Többeknek mondtam már, hogy nem egyszerű az élet ikrekkel. Nos, tévedtem. Az ikrekkel -ha nem betegek- tök jól el lehet lenni. De ha van egy közel 4 éves bátyjuk, aki addig a világ közepe volt, és most meg kell tapasztalja az érzelmi és időbeli nélkülözést, na, ez már kihívás.
Szegény Bálintnak nagyon nehéz megélni ezt a váltást, és ha megszakadunk se tudunk rá már annyi időt szánni, mint eddig. Ettől persze szenved szegény, meg is lett az eredménye a sok konfrontációnak: most arc -és homloküreg gyulladása van. Most a nagyszülőknél és az unokatesóknál van 1 hete, és még ott lesz vagy egy hétig, persze szépen javul, mert ott újra azt az életet élheti, mint régen velünk. Kicsit tartok tőle, mi lesz, ha visszajön, de már gondolkodunk, hogyan kaphat exkluzív időt, amikor a babák nem köpnek bele a levesébe.
Most, hogy nincs itt, kb. a harmadára esett vissza a terheltségünk. Meglepő, de a vele való hadakozás sokkal több energiát visz el, mint a két baba együtt. Ezt addig nem vettük észre, míg itt volt velünk, csak sodródtunk, illetve vergődtünk. Ha tudtok tippeket adni, hogyan lehet az ilyen helyzeteket okosan kezelni, jó könyvet, saját tapasztalatot, akármit, akkor nagyon megköszönném.
Közben az egyik pici, Benedek hörghurutos lett, alig tudott aludni az utóbbi napokban, annyira nehezen vette a levegőt. Sok nehézségen keresztül küzdöttem már magam az életben (illetve együtt a feleségemmel), de az utóbbi pár nap pályázhat a legnehezebb időszakok toplistájára. Azt a tény viszony, hogy most blogolok, jelzi, hogy már enyhül a nyomás, kezdenek egyenesedni a dolgok.
Pannikának nagyon jó az immunrendszere, benne a vírus csak némi taknyot hozott elő, remélem nem romlik az állapota.
Szóval majd ha kicsit észhez térünk, jönnek újra az SQL infók. Addig is, BUÉK még egyszer.
