azure - 递归关系查询

Question

我希望实现图表来映射 Azure SQL 中应用程序的角色层次结构。如果将其展开，该图将看起来像一棵树。父级能够管理树上属于它的任何角色。

所以我有一个角色节点表和一个 canmanage 边缘表。

我熟悉查询第一级和第二级关系，但是我需要一个查询，可以在其中放入任何角色并接收属于该角色的所有子级的列表。

我熟悉 NEO4J 中的此类操作，但我没有找到任何有关如何在 Azure SQL 中完成此操作的文档。

如何运行递归查询来获取具有特定角色名称或 ID 的所有子角色？

Answer 1

这可以通过使用 new graph database capabilities 从 SQL Server 2017 和 Azure SQL DB 实现。以及新的 MATCH 子句来模拟这种类型的关系。不幸的是，在 v1 中，多态性和传递闭包本身并未包含在内，但可以使用递归查询来实现。如果您查看最后一个查询，它会保留您作为顶级管理器输入的参数，并迭代其余的参数。

示例脚本:

USE tempdb
GO

-- NODES
DROP TABLE IF EXISTS dbo.roles

-- EDGES
DROP TABLE IF EXISTS dbo.canManage
DROP TABLE IF EXISTS dbo.isManagedBy
GO

CREATE TABLE dbo.roles (
    roleId      INT PRIMARY KEY,
    roleName    VARCHAR(20) UNIQUE NOT NULL
    ) AS NODE

CREATE TABLE dbo.canManage AS EDGE;
CREATE TABLE dbo.isManagedBy AS EDGE;
GO


-- Populate node table
INSERT INTO dbo.roles ( roleId, roleName )
VALUES
    ( 1, 'CEO' ),
    ( 2, 'VP 1' ),
    ( 3, 'VP 2' ),
    ( 4, 'Sales Manager 1' ),
    ( 5, 'Sales Manager 2' ),
    ( 6, 'Ops Manager 1' ),
    ( 7, 'Ops Manager 2' ),
    ( 8, 'Sales Lead 1' ),
    ( 9, 'Salesperson 1' ),
    ( 10, 'Salesperson 2' ),
    ( 11, 'Salesperson 3' )
GO


-- Populate edge table
INSERT INTO dbo.canManage ( $from_id, $to_id )
SELECT ceo.$node_id, VPs.$node_id
FROM dbo.roles ceo
    CROSS JOIN dbo.roles VPs
WHERE ceo.roleName = 'CEO'
  AND VPs.roleName Like 'VP%'


-- VP 1 manages Sales Managers
INSERT INTO dbo.canManage ( $from_id, $to_id )
SELECT a.$node_id, b.$node_id
FROM dbo.roles a
    CROSS JOIN dbo.roles b
WHERE a.roleName = 'VP 1'
  AND b.roleName Like 'Sales Manager%'


-- VP 2 manages Ops Managers
INSERT INTO dbo.canManage ( $from_id, $to_id )
SELECT a.$node_id, b.$node_id
FROM dbo.roles a
    CROSS JOIN dbo.roles b
WHERE a.roleName = 'VP 2'
  AND b.roleName Like 'Ops Manager%'


-- Sales Manger 1 manages Sales Leads
INSERT INTO dbo.canManage ( $from_id, $to_id )
SELECT a.$node_id, b.$node_id
FROM dbo.roles a
    CROSS JOIN dbo.roles b
WHERE a.roleName = 'Sales Manager 1'
  AND b.roleName Like 'Sales Lead%'


-- Sales Leads 1 manages all salespersons
INSERT INTO dbo.canManage ( $from_id, $to_id )
SELECT a.$node_id, b.$node_id
FROM dbo.roles a
    CROSS JOIN dbo.roles b
WHERE a.roleName = 'Sales Lead 1'
  AND b.roleName Like 'Salesperson%'


-- Create the inverse edge / relationship
INSERT INTO dbo.isManagedBy ( $from_id, $to_id )
SELECT $to_id, $from_id
FROM dbo.canManage 
GO



-- Now write the graph queries:

-- Manages
SELECT FORMATMESSAGE( '%s manages %s', r1.roleName, r2.roleName ) manages
FROM dbo.roles r1, dbo.canManage canManage, dbo.roles r2
WHERE MATCH ( r1-(canManage)->r2 )


-- Same manager
SELECT FORMATMESSAGE( '%s and %s have the same manager %s', r1.roleName, r3.roleName, r2.roleName )
FROM dbo.roles r1, dbo.isManagedBy m1, dbo.roles r2, dbo.isManagedBy m2, dbo.roles r3
WHERE MATCH ( r1-(m1)->r2<-(m2)-r3 )
AND r1.$node_id < r3.$node_id



-- Recursive
-- walk the tree ... CEO manages everyone ...
;WITH cte AS (
SELECT 1 xlevel, r1.roleName manager, r2.roleName managed
FROM dbo.roles r1, dbo.canManage canManage, dbo.roles r2
WHERE MATCH ( r1-(canManage)->r2 )
AND r1.roleName = 'CEO'

UNION ALL

SELECT c.xlevel + 1, r1.roleName, r2.roleName
FROM cte c, dbo.roles r1, dbo.canManage canManage, dbo.roles r2
WHERE c.managed = r1.roleName
  AND MATCH ( r1-(canManage)->r2 )
)
SELECT *
FROM cte
ORDER BY xlevel, manager, managed



;WITH cte AS (
SELECT 1 xlevel, r1.roleName manager, r2.roleName managed
FROM dbo.roles r1, dbo.canManage canManage, dbo.roles r2
WHERE MATCH ( r1-(canManage)->r2 )
AND r1.roleName = 'CEO'

UNION ALL

SELECT c.xlevel + 1, c.manager, r2.roleName
FROM cte c, dbo.roles r1, dbo.canManage canManage, dbo.roles r2
WHERE c.managed = r1.roleName
  AND MATCH ( r1-(canManage)->r2 )
)
SELECT *
FROM cte
ORDER BY xlevel, manager, managed