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