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