PostgreSQL:查找元素的权限，遍历到根

Question

这是我正在使用的数据库结构

Item
----
ID [PK]
Name
Desc

Links
-----
ID [FK]
LID [FK] -- Link ID
LType    -- Link Type (Parent, Alias)

Permission 
----------
ID [FK]
CanRead
CanWrite
CanDelete


Let's assume, we have the below data in the table
Item Table
-----------
ID  Name    Desc
=================
0   Root    Base Item
1   One     First
2   Two     Second
3   Three   Third
4   Four    Forth
5   Five    Fifth
6   Six     Sixth

Links Table
-----------
ID  LID     LType   
==================
1   0       Parent
2   0       Parent
3   1       Parent
4   2       Parent
5   4       Parent
6   5       Parent

0 
|- 1 
|   |- 3
|- 2
    |- 4
        |- 5
            |- 6

Permission Table
-----------------
ID  CanRead     CanWrite    CanDelete
=====================================
0   T           T           T
2   T           F           F
5   T           T           F
6   F           F           F

问题是，如果我想要6的权限，我可以直接查询Permission表并获取Read/Write/Delete值。但是，如果我想要 4 的权限，它不存在于权限表中，所以我需要找到父级，即 2，因为我有 2 的许可，所以我可以归还它。

比较棘手，如果我想要 3 的权限，我检查权限表，它不存在，查看不存在的父 (1)，寻找它的父级(0-Root)，并返回值。

这可以适用于任何级别，假设我们在权限表中没有记录 2、5、6，所以当我查找 6 时，我需要一直遍历到 root 以获得权​​限。

注意:我们始终拥有 Root 权限。

我希望这在数据库层而不是应用程序层完成，因此在编写 SQL 查询(递归)或存储过程方面的任何帮助都会很棒。

谢谢!!

Answer 1

您可以使用 RECURSIVE CTE为此:

WITH RECURSIVE Perms(ID, Name, ParentID, CanRead, CanWrite, CanDelete) AS (
   SELECT i.ID, i.Name, l.LID AS ParentID, p.CanRead, p.CanWrite, p.CanDelete
   FROM Item AS i
   LEFT JOIN Permission AS p ON i.ID = p.ID
   LEFT JOIN Links AS l ON i.ID = l.ID
),  GET_PERMS(ID, ParentID, CanRead, CanWrite, CanDelete) AS (
    -- Anchor member: Try to get Read/Write/Delete values from Permission table
    SELECT ID, ParentID, CanRead, CanWrite, CanDelete
    FROM Perms
    WHERE ID = 3

  UNION ALL

    -- Recursive member: terminate if the previous level yielded a `NOT NULL` result
    SELECT p.ID, p.ParentID, p.CanRead, p.CanWrite, p.CanDelete
    FROM GET_PERMS AS gp 
    INNER JOIN Perms AS p ON gp.ParentID = p.ID    
    WHERE gp.CanRead IS NULL
)
SELECT CanRead, CanWrite, CanDelete 
FROM GET_PERMS
WHERE CanRead IS NOT NULL

RECURSIVE CTE 在从数据库中检索到 Permission 记录时终止。

Demo here