Yii Framework Forum: Holy Cdbauthmanager Queries, Batman! - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Holy Cdbauthmanager Queries, Batman! Rate Topic: -----

#1 User is offline   Sarke 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 62
  • Joined: 25-October 10

Posted 04 November 2012 - 07:23 PM

Just implemented some rbac rules, with a few checks on the page (visible sections). 125 queries! This more than doubles the page generation time!

Why is this in the bug forum? Because any reasonable RBAC implementation in CDbAuthManager causes a severe slowdown on the site.

I will be workin on extending CDbAuthManager::checkAccessRecursive() to optimize it.
0

#2 User is offline   Sarke 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 62
  • Joined: 25-October 10

Posted 04 November 2012 - 08:52 PM

Ok, so I extenderd CDbAuthManager to load each of the tables only once. The way I have extended it is so that with these default options nothing will change if you use this instead of CDbAuthManager. So set the extra options like below.

The way CDbAuthManager operates by default is to load each item from the item table and from the relations table with it's own query, discarding the results when done. I call it "singleLoad", and it is very inefficient. Set these two options (singleLoadAuthItems and singleLoadAuthParents) to false will load it all in a query and keep the results.

For the assignments, it will be load from the DB each time a check is made, even if it hasn't changed. How often does the assignments change on the same page request? Set keepAuthAssignments to true to only load it once per request.

Here are some typical settings for protected/config/main.php:

'authManager'=>array(
    'class' => 'EDbAuthManager',
    'connectionID' => 'db',
    'assignmentTable' => 'rbac_assignments',
    'itemChildTable' => 'rbac_relations',
    'itemTable' => 'rbac_items',
    'showErrors' => YII_DEBUG,
    'singleLoadAuthItems' => false,
    'singleLoadAuthParents' => false,
    'keepAuthAssignments' => true,
),


And here is the code. checkAccessRecursive() is identical to the CDbAuthManager one, except I moved the part where the parents are loaded to getAuthParents(). I also overwrote getAuthItem() and getAuthAssignments().

class EDbAuthManager extends CDbAuthManager
{
    public $singleLoadAuthItems = true;
    public $singleLoadAuthParents = true;
    public $keepAuthAssignments = false;

    protected $_items;
    protected $_parents;
    protected $_assignments = array();

    protected function checkAccessRecursive($itemName, $userId, $params, $assignments)
    {
        if (($item = $this->getAuthItem($itemName)) === null)
            return false;

        Yii::trace('Checking permission "'.$item->getName().'"','system.web.auth.CDbAuthManager');

        if (!isset($params['userId']))
            $params['userId'] = $userId;

        if ($this->executeBizRule($item->getBizRule(), $params, $item->getData()))
        {
            if (in_array($itemName, $this->defaultRoles))
                return true;

            if (isset($assignments[$itemName]))
            {
                $assignment = $assignments[$itemName];
                if ($this->executeBizRule($assignment->getBizRule(), $params, $assignment->getData()))
                    return true;
            }

            foreach ($this->getAuthParents($itemName) as $parent)
            {
                if ($this->checkAccessRecursive($parent, $userId, $params, $assignments))
                    return true;
            }
        }

        return false;
    }

    public function getAuthItem($name)
    {
        if ($this->singleLoadAuthItems)
            return parent::getAuthItem($name);

        if (!isset($this->_items))
        {
            $rows = $this->db->createCommand()
                ->select()
                ->from($this->itemTable)
                ->queryAll();

            $this->_items = array();
            foreach($rows as $row)
            {
                if (($data = @unserialize($row['data'])) === false)
                    $data = null;

                $this->_items[$row['name']] = new CAuthItem($this, $row['name'], $row['type'], $row['description'], $row['bizrule'], $data);
            }
        }

        if (isset($this->_items[$name]))
            return $this->_items[$name];
        else
            return null;
    }

    public function getAuthAssignments($userId)
    {
        if ($this->keepAuthAssignments)
        {
            if (!isset($this->_assignments[$userId]))
                $this->_assignments[$userId] = parent::getAuthAssignments($userId);

            $assignments = $this->_assignments[$userId];
        }
        else
        {
            $assignments = parent::getAuthAssignments($userId);
        }

        return $assignments;
    }

    public function getAuthParents($itemName)
    {
        if ($this->singleLoadAuthParents)
        {
            return $this->db->createCommand()
                ->select('parent')
                ->from($this->itemChildTable)
                ->where('child=:name', array(':name'=>$itemName))
                ->queryColumn();
        }

        if (!isset($this->_parents))
        {
            $relations = $this->db->createCommand()
                ->select()
                ->from($this->itemChildTable)
                ->queryAll();

            $this->_parents = array();
            foreach ($relations as $row)
                $this->_parents[$row['child']][$row['parent']] = $row['parent'];
        }

        if (isset($this->_parents[$itemName]))
            return array_values($this->_parents[$itemName]);
        else
            return array();
    }

}

0

#3 User is offline   muayyad alsadi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 27-April 11

Posted 08 November 2012 - 07:07 AM

I've made a class called CachingDBAuthManager found here https://github.com/y...comment-9207350

it does a single query with left join fetching every thing at once

        $rows=$this->db->createCommand()
                    ->select('name, type, description, bizrule, data, parent')
                    ->from(array(
                    $this->itemTable,
                    $this->itemChildTable
                    ))
                    ->where('name=child')
                    ->queryAll();


this is the only query and it's done once aday by default

you may call purge in afterSave of itemTable and itemChildTable modules if any
to invalidate the cache

or made a command line that calls purge
    if (Yii::app()->authManager instanceof CachingDbAuthManager) Yii::app()->authManager->purgeCache();

0

#4 User is offline   Imre 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 29
  • Joined: 30-March 11

Posted 29 November 2012 - 10:04 AM

I am using this extension:
http://www.yiiframew...ddbauthmanager/
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users