SQL Graph練習

以員工表做SQL Graph Database測試insert/update/delete的功能。

Query:

案例:查詢第一階層的資料,看誰要report給ManagerA和ManagerA的老闆是誰

SELECT 
E.[ID],E.[Name],E.[ParentId],E1.[ID],E1.[Name],E1.[ParentId]
FROM 
    myEmpNode e, myEmpNode e1, myEmpReportsTo m 
WHERE 
    MATCH(e-(m)->e1)
and e.[Name]='ManagerA';
ID Name ParentId ID Name ParentId
2 managerA 1 5 empA 2
2 managerA 1 6 empB 2

案例:查詢第二層資料

SELECT 
E.[ID],E.[Name],E.[ParentId],E1.[ID],E1.[Name],E1.[ParentId],E2.[ID],e2.[Name],E2.[ParentId]
FROM 
    myEmpNode e, myEmpNode e1, myEmpReportsTo m ,myEmpReportsTo m1, myEmpNode e2
WHERE 
    MATCH(e-(m)->e1-(m1)->e2)
and e.[Name]='ManagerA';
ID Name ParentId ID Name ParentId ID Name ParentId
2 managerA 1 6 empB 2 8 empD 6
2 managerA 1 6 empB 2 9 empE 6

Delete:

案例:解除EmpC與ManagerB的主管部屬關係(myEmpReportsTo)

方法1:用$edge_id刪除

找出關係(myEmpReportsTo)的$edge_id

select * from myEmpReportsTo;

DELETE myEmpReportsTo WHERE $edge_id ='{"type":"edge","schema":"dbo","table":"myEmpReportsTo","id":7}';

方法2:用內建含數(System Functions)

DECLARE @obj INT = OBJECT_ID('dbo.myEmpReportsTo');
DELETE myEmpReportsTo WHERE $edge_id = EDGE_ID_FROM_PARTS(@obj, 7);

Update:

案例:因EmpB離職, 一併解除與EmpD和EmpE的部屬關係;並將D/E改掛到A下;

Step0:直接更新empD的主管為empA-->會失敗, 無法直接更新$node_id/$from_id/$to_id/$edge_id

UPDATE myEmpReportsTo
SET $from_id = (SELECT $node_id FROM myEmpNode WHERE ID = 5)
WHERE GRAPH_ID_FROM_NODE_ID($to_id) = 7;

訊息 271,層級 16,狀態 1,行 8
The column "$from_id_9931E5F4CF464AD3826ACEA80425B631" cannot be modified because it is either a computed column or is the result of a UNION operator.

Step1:移除B與D/E的關係

DELETE myEmpReportsTo
FROM myEmpNode empH, myEmpNode empL, myEmpReportsTo rpt
WHERE MATCH(empH-(rpt)->empL)
    AND empH.[Name] = 'EmpB';

Step2:刪除EmpB資料

--找出node_id
select * from myEmpNode where Name='empB';
$node_id_7C1EDEE8A57C47BAACAE74B1B68A5CA1 ID Name ParentId Path
{"type":"node","schema":"dbo","table":"myEmpNode","id":5} 6 empB 2 /1/2/
DECLARE @obj INT = OBJECT_ID('dbo.myEmpNode');
DELETE myEmpNode WHERE $node_id = NODE_ID_FROM_PARTS(@obj, 5);


Step3:新增empD/empE與empA的部屬關係

方法一:

INSERT INTO myEmpReportsTo ($from_id, $to_id, [Path]) VALUES 
    ((SELECT $node_id FROM myEmpNode WHERE ID = 5), (SELECT $node_id FROM myEmpNode WHERE ID = 8),'/1/1/'),
    ((SELECT $node_id FROM myEmpNode WHERE ID = 5), (SELECT $node_id FROM myEmpNode WHERE ID = 9),'/1/1/');

方法二:

DECLARE @obj1 INT = OBJECT_ID('dbo.myEmpNode');
INSERT INTO myEmpReportsTo ($from_id, $to_id,[Path]) VALUES 
(NODE_ID_FROM_PARTS(@obj1, 4), NODE_ID_FROM_PARTS(@obj1, 7),'/1/1/'),
(NODE_ID_FROM_PARTS(@obj1, 4), NODE_ID_FROM_PARTS(@obj1, 8),'/1/1/');


Step4:查詢empD/empE的報告主管

SELECT 
E.[ID],E.[Name],E.[ParentId],E1.[ID],E1.[Name],E1.[ParentId]
FROM 
    myEmpNode e, myEmpNode e1, myEmpReportsTo m 
WHERE 
    MATCH(e-(m)->e1)
and e1.[Name] in ('empD','empE');
ID Name ParentId ID Name ParentId
5 empA 2 8 empD 6
5 empA 2 9 empE 6


測試資料建置SQL:

drop table myEmp;

CREATE TABLE dbo.myEmp(
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[ParentId] [int],
[Path] nvarchar(50)
);

INSERT INTO myEmp values ('1','boss',null,'/');
INSERT INTO myEmp values ('2','managerA','1','/1/');
INSERT INTO myEmp values ('3','managerB','1','/2/');
INSERT INTO myEmp values ('4','managerC','1','/3/');
INSERT INTO myEmp values ('5','empA','2','/1/1/');
INSERT INTO myEmp values ('6','empB','2','/1/2/');
INSERT INTO myEmp values ('7','empC','3','/2/3/');
INSERT INTO myEmp values ('8','empD','6','/1/1/2/');
INSERT INTO myEmp values ('9','empE','6','/1/1/3/');

drop table dbo.myEmpNode;
--定義Node table
CREATE TABLE dbo.myEmpNode(
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[ParentId] [int],
[Path] [varchar](50),
) AS NODE;

INSERT INTO myEmpNode([ID],[Name],[ParentId],[Path]) select [ID],[Name],[ParentId],[Path] from myEmp;
--select * from myEmpNode;


drop table myEmpReportsTo;
--定義Edge table
CREATE TABLE myEmpReportsTo(Path varchar(100)) AS EDGE;

--使用ID和parentID定義關聯性
INSERT INTO myEmpReportsTo 
SELECT e.$node_id, m.$node_id,e.Path
FROM dbo.myEmpNode e 
inner JOIN dbo.myEmpNode m 
ON e.[ID] = m.[ParentId];  

select * from myEmpReportsTo;