使用 PostgreSQL 8.4.14 数据库,我有一个表格来表示如下所示的树形结构:
CREATE TABLE unit (
id bigint NOT NULL PRIMARY KEY,
name varchar(64) NOT NULL,
parent_id bigint,
FOREIGN KEY (parent_id) REFERENCES unit (id)
);
INSERT INTO unit VALUES (1, 'parent', NULL), (2, 'child', 1)
, (3, 'grandchild A', 2), (4, 'grandchild B', 2);
id | name | parent_id
----+--------------+-----------
1 | parent |
2 | child | 1
3 | grandchild A | 2
4 | grandchild B | 2
我希望为这些单元创建一个访问控制列表,每个单元都可以有自己的ACL,或者从最近的具有自己ACL的祖先继承。
CREATE TABLE acl (
unit_id bigint NOT NULL PRIMARY KEY,
FOREIGN KEY (unit_id) REFERENCES unit (id)
);
INSERT INTO acl VALUES (1), (4);
unit_id
---------
1
4
我使用一个视图来确定一个单元是否从祖先那里继承它的ACL:
CREATE VIEW inheriting_acl AS
SELECT u.id AS unit_id, COUNT(a.*) = 0 AS inheriting
FROM unit AS u
LEFT JOIN acl AS a ON a.unit_id = u.id
GROUP BY u.id;
unit_id | inheriting
---------+------------
1 | f
2 | t
3 | t
4 | f
我的问题是:如何获取最近的单位,该单位不从祖先继承ACL? 我期望的结果应类似于以下表格/视图:
unit_id | acl
---------+------------
1 | 1
2 | 1
3 | 1
4 | 4