Organize your servers and services, Hierarchical management of arbitrary nodes

Post date: Dec 8, 2009 3:07:57 PM

USE SystemDoc

USE masterDROP DATABASE SystemDoc

CREATE DATABASE SystemDoc

CREATE TABLE NodeTypes

( NodeTypeId INT IDENTITY(1, 1), Name VARCHAR(200) NOT NULL, PRIMARY KEY(NodeTypeId))CREATE TABLE Node

( NodeId INT IDENTITY(1, 1) NOT NULL, NodeTypeId INT NOT NULL, Name VARCHAR(200) NOT NULL, ParentNodeId INT NULL, PRIMARY KEY(NodeId), FOREIGN KEY(ParentNodeId) REFERENCES Node(NodeId), FOREIGN KEY(NodeTypeId) REFERENCES NodeTypes(NodeTypeId) )CREATE TABLE NodeInfoTypes

( NodeInfoTypeId INT IDENTITY(1, 1), NodeInfoType VARCHAR(100), PRIMARY KEY(NodeInfoTypeId))CREATE TABLE NodeInfo

( NodeId INT NOT NULL, NodeInfoTypeId INT NOT NULL, InfoText VARCHAR(500) NOT NULL, PRIMARY KEY(NodeId, NodeInfoTypeId), FOREIGN KEY(NodeId) REFERENCES Node(NodeId), FOREIGN KEY(NodeInfoTypeId) REFERENCES NodeInfoTypes(NodeInfoTypeId))CREATE TABLE RelationshipTypes

( RelationshipTypeId INT IDENTITY(1, 1) NOT NULL, RelationShip VARCHAR(100) NOT NULL, PRIMARY KEY(RelationshipTypeId))CREATE TABLE NodeRelationship

( NodeId INT NOT NULL, RelatedToNodeId INT NOT NULL, RelationshipTypeId INT, Comment VARCHAR(500) NULL, PRIMARY KEY(NodeId, RelatedToNodeId), FOREIGN KEY(NodeId) REFERENCES Node(NodeId), FOREIGN KEY(RelatedToNodeId) REFERENCES Node(NodeId), FOREIGN KEY(RelationshipTypeId) REFERENCES RelationshipTypes(RelationshipTypeId))INSERT NodeTypes(Name)SELECT 'Rackskåp' UNION ALLSELECT 'Physical Computer' UNION ALLSELECT 'Virtual machine' UNION ALLSELECT 'Windows 2003 Server' UNION ALLSELECT 'Windows 2002 Server' UNION ALLSELECT 'Web server' UNION ALLSELECT 'Web site' UNION ALLSELECT 'Web service' UNION ALLSELECT 'SQL Server' UNION ALLSELECT 'Database' UNION ALLSELECT 'Subversion Server' UNION ALLSELECT 'FTP Server' UNION ALLSELECT 'Mail Server'INSERT NodeInfoTypes(NodeInfoType)

SELECT 'Info' UNION ALLSELECT 'IP' UNION ALLSELECT 'More 1' UNION ALLSELECT 'More 2' UNION ALLSELECT 'Racknummer' UNION ALLSELECT 'MAC' union allselect 'File path to Documentation' union allselect 'URL to Web based documentation'SELECT * FROM node

SELECT * FROM nodeinfo

SELECT * FROM nodeTypes

EXEC dbo.ListAllChildren 5INNER JOIN NodeTypes

--select * from dbo.SelRecursiveChildren(5)GOCREATE FUNCTION dbo.SelRecursiveChildren(@NodeId INT)RETURNS@res TABLE ( NodeId INT ,Name VARCHAR(200) ,NodeTypeId INT ,ParentNodeId INT)AS--alter procedure dbo.ListAllChildren(@NodeId int)--asBEGIN

WITH Nodd AS

( SELECT NodeId ,Name

,NodeTypeId

,ParentNodeId

FROM Node

WHERE NodeId = @NodeId UNION ALL SELECT n.NodeId

,n.Name

,n.NodeTypeId

,n.ParentNodeId

FROM Node n

INNER JOIN Nodd b

ON n.ParentNodeId = b.NodeId

)INSERT @resSELECT * FROM nodd

RETURNEND

-- läser även "relationer" rekursivtWITH Nodd AS -- varning för cirkulärt rekursiva relationer( SELECT n.NodeId ,n.Name ,n.NodeTypeId ,n.ParentNodeId ,nr.RelatedToNodeId as RelatedTo ,nr.RelationshipTypeId ,nr.Comment FROM Node n INNER JOIN NodeRelationship nr ON nr.NodeId = n.NodeId WHERE n.NodeId = 9 UNION ALL SELECT nod.NodeId ,nod.Name ,nod.NodeTypeId ,nod.ParentNodeId ,nr.RelatedToNodeId as RelatedTo ,nr.RelationshipTypeId ,nr.Comment FROM Nodd n INNER JOIN Node nod ON n.RelatedTo = nod.NodeId INNER JOIN NodeRelationship nr ON nr.NodeId = nod.NodeId )select * from nodd