PostgreSQL:查找元素的权限，遍历到root [英] PostgreSQL: Find permission for element, traverse up to root

问题描述

这是我使用的数据库结构

Here is the DB Structure I'm using

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的权限，我可以直接查询权限表并获取读/写/删除值.但是，如果我想要 4 的权限，它不存在于权限表中，所以我需要找到父级，即 2，因为我有 2 的许可，所以我可以退货.

Question is, if I want the permission for 6, I can directly query Permission table and get the Read/Write/Delete value. However if I want the permission for 4, it is not present in permission table, so I need to find the parent, which is 2, since I have the permission for 2 I can return it.

比较棘手，如果我想要 3 的权限，我检查权限表，它不存在，请参阅父 (1)，它不存在，寻找它的父节点(0-Root)，并返回值.

More tricky, If I want the permission for 3, I check in permission table, it is not present, see for the parent (1), which is not present, go for it's parent which is (0-Root), and return the value.

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

This can be for any level, imagine we do not have records 2, 5, 6 in permission table, so when I lookup for 6, then I need to traverse all the way up to root to get the permission.

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

Note: We always have the permission present for Root.

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

I want this to be done in DB layer than application layer, so any help in writing SQL query (recursive) or Stored Procedure would be great.

谢谢！！

推荐答案

您可以使用 递归 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 记录时终止.

The RECURSIVE CTE terminates when a Permission record has been retrieved from the database.

此处演示

这篇关于PostgreSQL:查找元素的权限，遍历到root的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！