我是sql的新手，正在尝试在这里遵循此示例：http://net.tutsplus.com/tutorials/php/a-better-login-system/
所以问题的要点是，
- 有一些
permissions允许访问资源 - 有些
roles可以有多个permissions - 有些
users可能具有多个roles和多个permissions
数据库表如下所示：
permission role user role_permissions user_roles和user_permissions
以下是创建表的代码：
CREATE TABLE "permission" (
"id" SERIAL PRIMARY KEY,
"permission_key" VARCHAR(32) NOT NULL UNIQUE,
"permission_name" VARCHAR(32) NOT NULL);
CREATE TABLE "role" (
"id" SERIAL PRIMARY KEY,
"role_name" varchar(32) NOT NULL);
CREATE TABLE "role_permissions" (
"id" SERIAL PRIMARY KEY,
"role_id" INTEGER NOT NULL,
"permission_id" INTEGER NOT NULL,
"value" BOOLEAN NOT NULL DEFAULT FALSE,
"created_date" DATE NOT NULL,
UNIQUE ("role_id","permission_id"));
CREATE TABLE "user" (
"id" SERIAL PRIMARY KEY,
"username" VARCHAR(32) UNIQUE);
CREATE TABLE "user_permissions" (
"id" SERIAL PRIMARY KEY,
"user_id" INTEGER NOT NULL,
"permission_id" INTEGER NOT NULL,
"value" BOOLEAN NOT NULL DEFAULT FALSE,
"created_date" DATE NOT NULL,
UNIQUE ("user_id","permission_id"));
CREATE TABLE "user_roles" (
"id" SERIAL PRIMARY KEY,
"user_id" INTEGER NOT NULL,
"role_id" INTEGER NOT NULL,
"created_date" DATE NOT NULL,
UNIQUE ("user_id", "role_id"));
我的问题是：
我希望能够在sql语句中编写以下内容：
"给我找到所有可用于
NAME________ROLE的PERMISSIONS""给我找到所有可用于
NAME________USER的PERMISSIONS"
我知道我可以使用 ID 来匹配所有内容，但我想改用名称，因为我认为"为我找到用户 x 的所有权限"更有意义
另外，对于第二个问题，请注意，用户可以通过两种方式获得权限：
User > Role > Permission
User > Permission
为了简洁起见，我更愿意在一个语句中得到结果。
另外，如果有人知道如何将其转换为 Korma 查询，我将不胜感激。
我必须做出一些选择。 如果同时存在"用户"和"角色"权限，则"用户"权限适用。我用 zuser 和 zrole 替换了用户和角色，因为它们是 postgres 中的保留词，我不喜欢引用。该查询在当前形式下不是很东，但它似乎有效。数据是虚构的。
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path='tmp';
CREATE TABLE permission
( id SERIAL PRIMARY KEY
, permission_key VARCHAR(32) NOT NULL UNIQUE
, permission_name VARCHAR(32) NOT NULL
);
INSERT INTO permission(id,permission_key, permission_name) VALUES
(1, 'Eat', 'Eat' ) , (2, 'Drink', 'Drink' )
,(3, 'Shit', 'Shit' ) , (4, 'Urinate', 'Urinate' )
;
CREATE TABLE zrole
( id SERIAL PRIMARY KEY
, role_name varchar(32) NOT NULL
);
INSERT INTO zrole(id, role_name) VALUES
(1, 'Manager'), (2, 'Employee'), (3, 'Client') , (4, 'Visitor')
;
CREATE TABLE zuser
( id SERIAL PRIMARY KEY
, username VARCHAR(32) UNIQUE
);
INSERT INTO zuser(id, username) VALUES
(1, 'Jan Kees de Jager'), (2, 'Wildplasser'), (3, 'Joop') , (4, 'Mina')
;
CREATE TABLE role_permissions
( id SERIAL PRIMARY KEY
, role_id INTEGER NOT NULL REFERENCES zrole(id)
, permission_id INTEGER NOT NULL REFERENCES permission(id)
, created_date DATE NOT NULL
, value BOOLEAN NOT NULL DEFAULT FALSE
, UNIQUE (role_id,permission_id)
);
INSERT INTO role_permissions( id, role_id, permission_id, created_date, value) VALUES
(1,1,1, '2012-01-01', True )
,(2,1,2, '2012-01-01', False )
,(3,2,2, '2012-01-01', True )
,(4,2,3, '2012-01-01', False )
,(5,2,4, '2012-01-01', True )
,(6,3,2, '2012-01-01', True )
,(7,3,3, '2012-01-01', True )
,(8,3,4, '2012-01-01', True )
,(9,4,2, '2012-01-01', True )
,(10,4,3, '2012-01-01', True )
,(11,4,4, '2012-01-01', True )
;
CREATE TABLE user_permissions
( id SERIAL PRIMARY KEY
, user_id INTEGER NOT NULL REFERENCES zuser(id)
, permission_id INTEGER NOT NULL REFERENCES permission(id)
, created_date DATE NOT NULL
, value BOOLEAN NOT NULL DEFAULT FALSE
, UNIQUE (user_id,permission_id)
);
INSERT INTO user_permissions( id, user_id, permission_id, created_date, value) VALUES
(1,1,1, '2012-01-01', False )
,(2,1,2, '2012-01-01', False )
,(3,2,2, '2012-01-01', True )
,(4,3,2, '2012-01-01', True )
,(5,4,1, '2012-01-01', True )
;
CREATE TABLE user_roles
( id SERIAL PRIMARY KEY
, user_id INTEGER NOT NULL REFERENCES zuser(id)
, role_id INTEGER NOT NULL REFERENCES zrole(id)
, created_date DATE NOT NULL
, UNIQUE (user_id, role_id)
);
INSERT INTO user_roles (id, user_id, role_id, created_date) VALUES
(1,1,1, '2010-01-01' )
,(2,2,2, '2010-01-01' )
,(3,3,4, '2010-01-01' )
,(4,4,3, '2010-01-01' )
-- uncomment the next line to add a duplicate role
-- ,(5,2,4, '2010-01-01' )
;
WITH lutser AS (
SELECT up.user_id AS user_id
, up.permission_id AS permission_id
, up.value AS uval
FROM user_permissions up
)
, roler AS (
SELECT
ur.user_id AS user_id
, rp.permission_id AS permission_id
, rp.value AS rval
FROM user_roles ur
JOIN role_permissions rp ON rp.role_id = ur.role_id
)
SELECT us.username
, pe.permission_name
, pe.id AS permission_id
, lu.uval AS uval
, ro.rval AS rval
, COALESCE(lu.uval , ro.rval) AS tval
FROM lutser lu
FULL JOIN roler ro ON ro.user_id = lu.user_id
AND ro.permission_id = lu.permission_id
JOIN zuser us ON us.id = COALESCE(lu.user_id ,ro.user_id)
JOIN permission pe ON pe.id = COALESCE(ro.permission_id , lu.permission_id)
;
结果：
username | permission_name | permission_id | uval | rval | tval
-------------------+-----------------+---------------+------+------+------
Jan Kees de Jager | Eat | 1 | f | t | f
Jan Kees de Jager | Drink | 2 | f | f | f
Wildplasser | Drink | 2 | t | t | t
Wildplasser | Shit | 3 | | f | f
Wildplasser | Urinate | 4 | | t | t
Joop | Drink | 2 | t | t | t
Joop | Shit | 3 | | t | t
Joop | Urinate | 4 | | t | t
Mina | Eat | 1 | t | | t
Mina | Drink | 2 | | t | t
Mina | Shit | 3 | | t | t
Mina | Urinate | 4 | | t | t
(12 rows)
顺便说一句：上面的查询仍然不正确。如果用户属于多个角色，则查询将为该用户生成多行。需要向角色子查询添加 distinct/max（）。
更新：为了解决每人重复角色的问题，我创建了这个双重嵌套 CTE：
WITH lutser AS (
WITH aggr AS (
WITH rope AS (
SELECT DISTINCT
ur.user_id AS user_id
, rp.permission_id AS permission_id
, rp.value AS value
FROM user_roles ur
JOIN role_permissions rp ON rp.role_id = ur.role_id
GROUP BY ur.user_id , rp.permission_id , rp.value
)
SELECT user_id,permission_id, value
FROM rope yes
WHERE yes.value = True
UNION ALL
SELECT user_id,permission_id, value
FROM rope nono
WHERE nono.value = False
AND NOT EXISTS (SELECT * FROM rope nx
WHERE nx.user_id= nono.user_id
AND nx.permission_id= nono.permission_id
AND nx.value = True
)
)
SELECT COALESCE(up.user_id , ag.user_id) AS user_id
, COALESCE(up.permission_id , ag.permission_id) AS permission_id
, up.value AS uval
, ag.value AS rval
FROM user_permissions up
FULL JOIN aggr ag ON ag.user_id = up.user_id AND ag.permission_id = up.permission_id
)
SELECT us.username
, pe.permission_name
, lu.uval AS uval
, lu.rval AS rval
, COALESCE(lu.uval , lu.rval) AS tval
FROM lutser lu
JOIN zuser us ON us.id = lu.user_id
JOIN permission pe ON pe.id = lu.permission_id
;
它的功能可以通过向user_role表添加/取消注释数据行,(5,2,4, '2010-01-01' )来显示。同样，我必须做出选择：如果一个用户存在两个角色，并且存在冲突的真值，那么True一个获胜。我认为查询可以简化/美化，但至少它现在可以正常工作。