Just Code‎ > ‎

Organize your servers and services, Hierarchical management of arbitrary nodes

posted Dec 8, 2009, 7:07 AM by Peter Henell   [ updated Dec 11, 2009, 6:52 AM ]
USE SystemDoc
USE master DROP 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 ALL SELECT 'Physical Computer' UNION ALL SELECT 'Virtual machine' UNION ALL SELECT 'Windows 2003 Server' UNION ALL SELECT 'Windows 2002 Server' UNION ALL SELECT 'Web server' UNION ALL SELECT 'Web site' UNION ALL SELECT 'Web service' UNION ALL SELECT 'SQL Server' UNION ALL SELECT 'Database' UNION ALL SELECT 'Subversion Server' UNION ALL SELECT 'FTP Server' UNION ALL SELECT 'Mail Server' INSERT NodeInfoTypes(NodeInfoType)
SELECT 'Info' UNION ALL SELECT 'IP' UNION ALL SELECT 'More 1' UNION ALL SELECT 'More 2' UNION ALL SELECT 'Racknummer' UNION ALL SELECT 'MAC' union all select 'File path to Documentation' union all select 'URL to Web based documentation' SELECT * FROM node
SELECT * FROM nodeinfo
SELECT * FROM nodeTypes

EXEC dbo.ListAllChildren 5 INNER JOIN NodeTypes


--select * from dbo.SelRecursiveChildren(5) GO CREATE FUNCTION dbo.SelRecursiveChildren(@NodeId INT) RETURNS @res TABLE ( NodeId INT ,Name VARCHAR(200) ,NodeTypeId INT ,ParentNodeId INT ) AS --alter procedure dbo.ListAllChildren(@NodeId int) --as BEGIN
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 @res SELECT * FROM nodd
RETURN END




-- läser även "relationer" rekursivt
WITH 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
Comments