Použití sloupce typu hierarchyid pro stromovou strukturu

Tomáš Holan       26. 8. 2014       SQL, Databáze       6189 zobrazení

Pokud chceme v MS SQL Serveru ukládat nějakou hierarchickou strukturu, můžeme s výhodou použít datový typ hierarchyid (v MS SQL Serveru je od verze 2008).

Pokud je ale touto hierarchickou strukturou strom, samotná definice sloupce typu hierarchyid nám nezajistí udržování potřebné datové integrity tj. automaticky nám nezajistí, že libovolná ukládaná data budou vždy opravdu tvořit validní strom. Konkrétně se jedna o zajištění těchto podmínek:

  • Uložené hodnoty sloupce typu hierarchyid musí být jedinečné.
  • Pokud v tabulce existuje záznam s nějakou hodnotou sloupce typu hierarchyid, musí v této tabulce existovat záznam s hodnotou odpovídající přímému předkovi.
  • Záznam odpovídající přímému předkovi nějakého jiného záznamu nejde odstranit dokud tyto jiné záznamy v tabulce existují.

(Žádná z těchto podmínek není u sloupce typu hierarchyid automaticky vynucená - více naleznete v části Limitations of hierarchyid v dokumentaci na MSDN.)

Co tedy musíme udělat, aby jsme splnění těchto podmínek vynutili?

Ukážeme si to rovnou na příkladu. Nejprve si vytvořme tabulku Node pro reprezentaci stromové struktury:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.Node(
	NodeID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(100) NOT NULL,
	NodeHID hierarchyid NOT NULL,
 CONSTRAINT PK_Node PRIMARY KEY CLUSTERED 
(
	NodeID ASC
))
GO

Zajištění první podmínky na jedinečnost HID je jednoduché – stačí nad sloupcem NodeHID doplnit UNIQUE constraint:

ALTER TABLE dbo.Node ADD CONSTRAINT
	U_Node_NodeHID UNIQUE NONCLUSTERED 
	(
	NodeHID
	)
GO

Zajištění druhé a třetí podmínky je o něco málo složitější. Potřebujeme udělat následující:

  1. Do tabulky Node doplnit odvozený sloupec, který bude vracet HID předka.

Pokud navíc nebudeme chtít do tabulky ukládat pouze jediný kořenový prvek - záznam s HID ‘’ (výsledek volání hierarchyid::GetRoot()), ale budeme chtít jako první úroveň ukládat rovnou několik “kořenových” prvků s HID ‘/1/’, ‘/2/’ apod., musíme ve výrazu odvozeného sloupce ParentNodeHID pro tyto prvky první úrovně vracet hodnotu NULL. Výraz pro odvozený sloupec pak bude:

CASE WHEN NodeHID.GetLevel()=1 THEN NULL ELSE NodeHID.GetAncestor(1) END
  1. Tento odvozený sloupec musí být vytvořen s klauzulí PERSISTED.
  2. Do tabulky Node doplníme FOREIGN KEY constraint mezi sloupci NodeHID a ParentNodeHID.
ALTER TABLE dbo.Node ADD
	ParentNodeHID  AS CASE WHEN NodeHID.GetLevel() = 1 THEN NULL ELSE NodeHID.GetAncestor(1) END PERSISTED 
GO
ALTER TABLE dbo.Node WITH CHECK ADD CONSTRAINT FK_Node_Node FOREIGN KEY(ParentNodeHID)
	REFERENCES dbo.Node (NodeHID)
GO

Nyní nám již SQL Server nedovolí ukládat hodnoty hierarchyid, pro které nemáme uložené nadřazené prvky (s výjimkou první úrovně), ani vnitřní uzly stromu odstranit.

Pokud by jsme dále chtěli, aby pod jedním uzlem neexistovali dva prvky se stejným názvem (sloupec Name), můžeme k tomu také využít odvozený sloupec ParentNodeHID a doplnit druhý UNIQUE constraint:

ALTER TABLE dbo.Node ADD CONSTRAINT
	U_Node_ParentNodeHID_Name UNIQUE NONCLUSTERED 
	(
	ParentNodeHID,
	Name
	)
GO

Výsledný SQL skript pro vytvoření tabulky Node včetně všech omezení vypadá takto:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.Node(
	NodeID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(100) NOT NULL,
	NodeHID hierarchyid NOT NULL,
	ParentNodeHID  AS CASE WHEN NodeHID.GetLevel()=1 THEN NULL ELSE NodeHID.GetAncestor(1) END PERSISTED,
 CONSTRAINT PK_Node PRIMARY KEY CLUSTERED 
(
	NodeID ASC
))
GO
ALTER TABLE dbo.Node ADD CONSTRAINT
	U_Node_NodeHID UNIQUE NONCLUSTERED 
	(
	NodeHID
	)
GO
ALTER TABLE dbo.Node WITH CHECK ADD CONSTRAINT FK_Node_Node FOREIGN KEY(ParentNodeHID)
	REFERENCES dbo.Node (NodeHID)
GO
ALTER TABLE dbo.Node ADD CONSTRAINT
	U_Node_ParentNodeHID_Name UNIQUE NONCLUSTERED 
	(
	ParentNodeHID,
	Name
	)
GO

 

hodnocení článku

0       Hodnotit mohou jen registrované uživatelé.

 

Nový příspěvek

 

Příspěvky zaslané pod tento článek se neobjeví hned, ale až po schválení administrátorem.

Podpora EF

A zatím žádná oficiální podpora v EF...

nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět

jj, podpora v EF není.

Já to zatím řešil tak, že na operace, kde z té tabulky zrovna hierarchyid dotazovat nepotřebuju mám entitu bez toho jednoho sloupce a pro ostatní operace volám z EF uložené procedury.

nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět
                       
Nadpis:
Antispam: Komu se občas házejí perly?
Příspěvek bude publikován pod identitou   anonym.

Nyní zakládáte pod článkem nové diskusní vlákno.
Pokud chcete reagovat na jiný příspěvek, klikněte na tlačítko "Odpovědět" u některého diskusního příspěvku.

Nyní odpovídáte na příspěvek pod článkem. Nebo chcete raději založit nové vlákno?

 

  • Administrátoři si vyhrazují právo komentáře upravovat či mazat bez udání důvodu.
    Mazány budou zejména komentáře obsahující vulgarity nebo porušující pravidla publikování.
  • Pokud nejste zaregistrováni, Vaše IP adresa bude zveřejněna. Pokud s tímto nesouhlasíte, příspěvek neodesílejte.

Příspěvky zaslané pod tento článek se neobjeví hned, ale až po schválení administrátorem.

přihlásit pomocí externího účtu

přihlásit pomocí jména a hesla

Uživatel:  
Heslo:  

zapomenuté heslo

 

založit nový uživatelský účet

zaregistrujte se

 
zavřít

Nahlásit spam

Opravdu chcete tento příspěvek nahlásit pro porušování pravidel fóra?

Nahlásit Zrušit

Chyba

zavřít

feedback