CODEWITHSUNDEEP
sqlsqliteset-operations
ikegami
ikegami

Reputation: 385764

Best way to do set arithmetic in SQLite

Member-Role is N:N relationship.

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

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:

  1. If has role a,
    1. ALLOW
  2. 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).

Upvotes: 0

Views: 243

Answers (2)

forpas
forpas

Reputation: 164089

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);

Upvotes: 1

id'7238
id'7238

Reputation: 2593

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

Upvotes: 2

Related Questions