SQL question. So I've been meaning to ask you guys if a solution I came up with is actually an anti-pattern (or whatever term the cool kids use these days) or can be improved in some way.
The situation: I have a
RBAC system, that hierarchical trees of "stuff you can do". It's really fine grained, so every
CRUD operation has its own "Action", those actions are grouped into Tasks, and Tasks into Roles. Except for being in that strict hierarchy, there's no real difference between those three. Then I have a table where users are assigned one or more Roles, and then want to know if that user has access to a certain action.
This "can I see/do X" can be called multiple times in a pageview, so what I needed was a way to read really fast through a tree, to see if one of the branches/leaves matches an item X. Writing to the tree may be expensive, as that won't happen often.
A solution: After some searching I found Closure Tables (
http://www.slideshare.net/billkarwin/models-for-hierarchical-data, Start at page 40) to be the closest match to what I need. However, my data implies that a certain branch can be included in the same tree multiple times in different locations, something that the Closure table does not provide for. So I modified it as such:
To the Paths table I added a field "
length". This field has only three significant values: 0, 1, many. This will allow me to add multiple tree branches to a tree, and delete the correct one if I delete it again (this was a problem with the original closure table, as you wouldn't know the location of the particular subtree you deleted was in).
I now have three tables:
access_right contains the combinations of userid and roles
access_item contains the RBAC items
access_path contains the hierarchy of the access_items.
Now I can use the following SQL commands:
To see if user ":userid" has access to action ":itemname" (this one is called many times):
SELECT id FROM access_item i WHERE i.name LIKE :itemname AND i.id IN
(SELECT p.descendant FROM access_path p WHERE p.ancestor IN
(SELECT r.role_id FROM access_right r WHERE r.userid = :userid )
OR p.ancestor = (SELECT u.id FROM access_item u WHERE u.name = 'Default')) LIMIT 1The last part makes sure that if an action is assigned to the "Default" role everyone will always have access to it.
To add a child/subtree to a tree/item first check if there is no loop using the above statement. Then loop over the entire subtree you want to add and do the following (:parentid is the element we're adding to, $child is the element (top of the subtree) we're adding).
$grandChildren = AccessPath::model()->findAllByAttributes(array('ancestor' => $child->id)); // Gets an array of all Path items that have $child->id as an ancestor, including $child->id itself).
foreach ($grandChildren as $grandchild) {
INSERT INTO access_path (ancestor, descendant, length)
SELECT ancestor, :childid, (length+1+{$grandchild->length}) FROM access_path
WHERE descendant = :parentid
}
This code adds a path for every descendant of "child" to every ancestor of "parent" with the correct length.
Deletion is a bit more complicated than in "normal" Closure tables as we now need to make sure that we delete only one reference from all ancestors to all descendants, and specifically one from child to parent where the length is "1".
This is actual code instead of pseudo, also because I hope my comments are good enough for someone who has not worked with this framework to understand. We're now removing a "Child" item from a "Parent" item, which means removing an entire tree. The array param in "execute" replaces the strings (like :pid and :cid) in the SQL with the values of the variables, in a safe way.
// The Ancestors of the Parent item (including parent item itself
$ancestorIds = AccessPath::model()->getCommandBuilder()->
createSqlCommand("SELECT ancestor FROM {$this->pathTable}
WHERE descendant = :itemid", array(
':itemid' => $item->id,
)
)->queryColumn();
// The Descendants of the child item (including child item itself
$childIds = AccessPath::model()->getCommandBuilder()->
createSqlCommand("SELECT descendant FROM {$this->pathTable}
WHERE ancestor = :childid", array(
':childid' => $child->id,
)
)->queryColumn();
// Now, remove exactly ONE reference from every child item to every parent item
// Prepare statement (Prepared statements are a lot faster)
$comm = AccessPath::model()->getCommandBuilder()->
createSqlCommand("DELETE FROM {$this->pathTable} WHERE ancestor = :pid AND descendant = :cid AND length > 1 LIMIT 1");
foreach ($ancestorIds as $pid) {
foreach ($childIds as $cid) {
// At the direct connection, make sure the "length" property is 1, so we have the right one
if ($pid == $item->id && $cid == $child->id) {
AccessPath::model()->getCommandBuilder()->
createSqlCommand("DELETE FROM {$this->pathTable} WHERE ancestor = :pid AND descendant = :cid AND length = 1 LIMIT 1")
->execute(array(':pid' => $pid, ':cid' => $cid));
;
} else {
$comm->execute(array(':pid' => $pid, ':cid' => $cid));
}
}
}Edit: The reason the DELETE statement can't be done in a single uber-statement is the "LIMIT 1" requirement. I need only to delete one of each unique combination, and there's no way to do that in MySQL as far as I know, it will delete all matches.
Question: It seems to be working, but am I making any obvious mistakes here, doing something completely wrong, or can this be improved in any way?
Author
Topic: if self.isCoder(): post() #Programming Thread (Read 906712 times)