Soci (Soczó Zsolt) szakmai blogja

2008.01.23.

Ingyenes SQL Server 2008 könyv

Filed under: Adatbázisok,Könyvek,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 14:30

Lesz, ha kész lesz. :)

Addig is, már letölthető egy fejezet.

Nem megbízható constraintek okozta teljesítményproblémák

Igen érdekes írás.

2008.01.22.

SQL Server 2008 újdonságok 16. – térbeli adattípusok 2.

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 17:32

Az 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

starea.png

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))

stboundary1.png

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))

stboundary.png

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.

stbuffer1.png

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.

stbuffer2.png

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)

stcentroid1.png

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];

stconvexhull1.png

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

stdifference.png

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

Filed under: Élet — Soczó Zsolt @ 09:37

A 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…?

2008.01.21.

SQL Server 2008 újdonságok 15. – térbeli adattípusok 1.

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 09:59

Az ú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:

Geometry lekérdezés kimenete vizualizálva

Folyt. köv.

Nem töltsd le filmet – reklám (humor)

Filed under: Élet,Fun — Soczó Zsolt @ 09:43

http://www.youtube.com/watch?v=3OyoFVGvks4

2008.01.19.

SQL Server 2008 újdonságok 14. – HierarchyID adattípus 5.

Filed under: Adatbázisok,SQL Server 2008 — Soczó Zsolt @ 11:25

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.

2008.01.17.

A nap híre: .NET Framework Library Source Code now available

Filed under: .NET,CLR,Debugging,Szakmai élet — Soczó Zsolt @ 13:54

Ma mindenkinél erről lehet majd hallani, örülünk, böngészünk, debugolunk.

2008.01.14.

SQL Server 2008 újdonságok 13. – HierarchyID adattípus 4.

Filed under: Adatbázisok,SQL Server 2008 — Soczó Zsolt @ 18:41

Pá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?

2008.01.11.

C++/CLI addin reflectorhoz

Filed under: .NET,C++/CLI,Szakmai élet — Soczó Zsolt @ 11:23

Ha 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…

2008.01.09.

SQL Server 2008 újdonságok 12. – HierarchyID adattípus 3.

Filed under: Adatbázisok,SQL Server 2008 — Soczó Zsolt @ 13:09

Lá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:

Közvetlen utódok lekérdezésének végrehajtási terve

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:

Közvetlen utódok lekérdezésének végrehajtási terve nagyszámú indirekt utód esetén

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):

Mélységi bejárás alapján elkészített index (ez az alap a HieararchyID-n)

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:

Szintenkénti bejárás alapján elkészített index (számított, idexelt oszloppal képezhető)

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:

Közvetlen utódok lekérdezésének végrehajtási terve nagyszámú indirekt utód esetén, breadth-first indexxel megtámogatva

Á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.

2008.01.08.

SQL Server 2008 újdonságok 11. – HierarchyId adattípus 2.

Filed under: Adatbázisok,SQL Server 2008 — Soczó Zsolt @ 10:25

Mire 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:

Szűrés végrehajtási terve HierarchyID alapján

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()) [/source] 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: [source='sql'] 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 [/source] 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. [source='sql'] create unique nonclustered index IDX_EmployeeID on HumanResources.NewOrg(EmployeeID) go create nonclustered index IDX_ManagerID on HumanResources.NewOrg(ManagerID) include (EmployeeID, LoginID, Title) [/source] (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 rekurzív CTE végrehajtási terve

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.

2008.01.07.

Cool SQL Server blokkoláselemző script

Filed under: Adatbázisok,SQL Server 2005,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 20:56

A 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.

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 16:31

Egy é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. :)

2008.01.04.

Élet 3 gyerkőccel

Filed under: Élet,Személyes — Soczó Zsolt @ 22:18

BUÉ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.

« Older Posts

Powered by WordPress