Best way to do set arithmetic in SQLite

Question

Member-Role is N:N relationship.

Member           MemberRole       Role
+----------+     +----------+     +----------+
| Id       |     | MemberId |     | Id       |
| ...      |     | RoleId   |     | Name     |
+----------+     +----------+     +----------+

There are ~15,000 members, which have a varying and possibly zero number of roles.
There are ~50 roles.

Outside of the database, I have allow-deny lists I need to check against the database. A list might look like

+a -b +c +d

This means:

If has role a,
1. ALLOW
Else,
1. If has role b,
  1. DENY
2. Else,
  1. If has role c,
    1. ALLOW
  2. Else,
    1. If has role d,
      1. ALLOW
    2. Else,
      1. DENY

For example, someone with roles a and d would allowed, while someone with roles b and d would be disallowed.

The lists can easily be converted into a mathematical equation by starting from the end, applying a union (∪) for + terms and a difference (-) for - terms.

+a -b +c +d ⇒ ( ( ( ∅ ∪ d ) ∪ c ) - b ) ∪ a

Any trailing deny (-) can be ignored, so we know the first will always be a union.

+a -b +c +d ⇒ ( ( d ∪ c ) - b ) ∪ a

From that, I can build the following query:

       SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @d
UNION  SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @c
EXCEPT SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @b
UNION  SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @a

Since x - y = x ∩ y', we can also derive

+a -b +c +d ⇒ ( ( d ∪ c ) ∩ b' ) ∪ a

From that, I can build the following query:

SELECT `Id`
  FROM `Member`
 WHERE (
           (
               EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @d )
               OR
               EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @c )
           )
           AND
           NOT EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @b )
       )
       OR
       EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @a )

What's the best way to check if a member is allowed? (The answer will usually be yes.) Would using WITH help?

Note that the items of the allow-deny list can be role ids (number) or role names (not a number).

@wildplasser Like in the query I've just added to the question? — ikegami, Nov 01 '21 at 01:04
@wildplasser These queries will be build dynamically since the lists will come from config files. That's a tad trickier, but doable. — ikegami, Nov 01 '21 at 01:13
I didn't say I couldn't. I just said it would be a bit trickier. It would require adding look ahead, which isn't that hard, but it is an extra element. /// It wouldn't help much in the example (combining only two of the EXISTS). but it would actually help more in practice. — ikegami, Nov 01 '21 at 01:17

id'7238 · Answer 1 · 2021-11-01T06:40:01.787

This allow rule +a -b +c +d can be described by logical expression a + !b(c + d).

WITH MemberRole_acl AS (
  SELECT memberId,
    SUM(roleId = 'a') AS rolesA,
    SUM(roleId = 'b') AS rolesB,
    SUM(roleId = 'c') AS rolesC,
    SUM(roleId = 'd') AS rolesD
  FROM MemberRole
  GROUP BY memberId
)
SELECT m.*
FROM Member m
JOIN MemberRole_acl r ON r.memberId = m.id
WHERE rolesA OR NOT rolesB AND (rolesC OR rolesD)

db<>fiddle

score 1 · Answer 2 · answered Nov 01 '21 at 12:19

If you have the names of the roles as @a, @b, @c and @d, you can join the tables, aggregate and set the conditions in the HAVING clause:

SELECT mr.memberid
FROM MemberRole mr INNER JOIN Role r
ON r.Id = mr.RoleId
WHERE r.Name IN (@a, @b, @c, @d)
GROUP BY mr.memberid
HAVING MAX(r.Name = @a) OR NOT MAX(r.Name = @b);

If you have the ids of the roles as @a, @b, @c and @d, it is simpler:

SELECT memberid
FROM MemberRole 
WHERE RoleId IN (@a, @b, @c, @d)
GROUP BY memberid
HAVING MAX(RoleId = @a) OR NOT MAX(RoleId = @b);

Best way to do set arithmetic in SQLite

2 Answers2