以員工表做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;