jump to navigation

Optimizing your queries August 31, 2006

Posted by rossoft in CakePHP.
8 comments

I’m working on a project with CakePHP & MySQL 5, but it needs to be
DBMS independant. I don’t like putting raw queries with Model->query.
Some complex queries require some mysql custom functions. Most of that functions are available in all the common DBMS, but have a different syntax.
For that purposes I use Mysql 5 Views. CakePHP hasn’t support for them yet, but I’ve reported a ticket for fixing it.
Mysql Views with CakePHP are very powerful.
You can create a view that links several tables with complex ‘not exists’ where clausules, then rename all the tables ‘id’ fields to model_id. Now you can use standard CakePHP belongsTo relations to retrieve the other fields that you need.
With this way you can create very complex queries with minimum overhead.
For maximum performance, you must create indexes. For seeing where you need them, use the query ‘EXPLAIN SELECT ….’.
It will return several rows, with a lot of info for each operation that the DBMS must do for retrieve your query. In the column ‘type’ it says how addresses the data. If it shows the ‘all’ type, then that’s the worst system. If you see lots of them, create indexes in the fields that you use in your where clausules and execute again the ‘explain’ statement for seeing if there’re improvement.
With some indexes and good views, you can have the most complex queries with good performance and DBMS independence.

Advertisements

MySecurity Component August 23, 2006

Posted by rossoft in CakePHP.
8 comments

Some tricks for extra security in cake.

If you call http://example.com/users/render/delete (you’ve an UsersController) you will get rendered the delete.thtml view.
Try it at home with any controller.
The render() method from Controller gets called. I’m not sure if something dangerous can be done, but some other functions can be called like redirect. For forbidding all the methods from Controller base class, use this component.

<?php
/**
 * MySecurity.php
 * Some security things for Cake
 *
 * Features:
 * – The public functions from controller & object now can’t be called from url
 * –
 *
 * @author RosSoft
 * @version 0.1
 * @license MIT
 *
 * @package components
 */

class MySecurityComponent extends Object
{
    var $components=array(‘Security’);

    /**
     * Extra forbidden actions
     *
     * @var array $forbidden_actions
     */
    var $forbidden_actions=array();

    function startup(&$controller)
    {
        $this->forbidden_actions=am($this->forbidden_actions, get_class_methods(‘Controller’));

        $this->Security->startup($controller);
        if (in_array($controller->action,$this->forbidden_actions))
        {
            $this->Security->blackHoleCallback=null;
            $this->Security->blackHole($this);
        }
    }
}
?>

Log messages to SQL debug window August 23, 2006

Posted by rossoft in CakePHP.
2 comments

My models have custom methods that do a lot of findAll(), save() etc. because I want to encapsulate that login on the models. Then the controller calls some of them in one action.

Sometimes, the SQL debug window has a lot of queries, and I don’t know what queries belongs to some method.

Now, I can do
$this->MyModel->_log_info(‘test begin’);
$this->MyModel->my_custom_method_that_does_a_lot_of_things();
$this->MyModel->_log_info(‘end begin’);

And I see the text ‘test begin’ and ‘end begin’ inside the SQL Debug Window, and between there’s the queries that belongs to ‘my_custom_method’

     /**
     * Logs the message to the sql debug window
     *
     * @param string $msg Message to show
     */
    function _log_info($msg)
    {
        if (DEBUG)
        {
            $db =& ConnectionManager::getDataSource($this->useDbConfig);
            $db->error=”;
             $db->affected=”;
            $db->numRows=”;
            $db->took=”;
            $db->logQuery(“<span class=\”query_log\”>[{$this->name}] $msg</span>”);
        }
    }

Working with HABTM associations August 23, 2006

Posted by rossoft in CakePHP.
24 comments

I’ve done some functions for working with HABTM associations.
For example:
You’ve the model Post and the model Tag. Post HABTM Tag.
You want to add the Tag with id 12 to the Post with id 3. Then you can do it easy with
$this->Post->addAssoc(3,’Tag’,12);

Now you want to delete the association between the Post 3 and the Tag 15? $this->Post->deleteAssoc(3,’Tag’,15);

You can even add more than one tag at once:
$this->Post->addAssoc(3,’Tag’,array(11,12));

Put these functions in your AppModel:
    /**
     * Adds to a HABTM association some instances
     *
     * @param integer $id The id of the record in this model
     * @param mixed $assoc_name The name of the HABTM association
     * @param mixed $assoc_id The associated id or an array of id’s to be added
     * @return boolean Success
     */
    function addAssoc($id,$assoc_name,$assoc_id)
    {
        $data=$this->_auxAssoc($id,$assoc_name);
        if (!is_array($assoc_id)) $assoc_id=array($assoc_id);
        $data[$assoc_name][$assoc_name]=am($data[$assoc_name][$assoc_name],$assoc_id);
        return $this->save($data);
    }

    /**
     * Deletes from a HABTM association some instances
     *
     * @param integer $id The id of the record in this model
     * @param mixed $assoc_name The name of the HABTM association
     * @param mixed $assoc_id The associated id or an array of id’s to be removed
     * @return boolean Success
     */
    function deleteAssoc($id,$assoc_name,$assoc_id)
    {
        $data=$this->_auxAssoc($id,$assoc_name);
        if (!is_array($assoc_id)) $assoc_id=array($assoc_id);
        $result=array();
        foreach ($data[$assoc_name][$assoc_name] as $id)
        {
            if (!in_array($id, $assoc_id)) $result[]=$id;
        }
        $data[$assoc_name][$assoc_name]=$result;
        return $this->save($data);
    }

    /**
     * Returns the data associated with a HABTM in an array
     * suitable for save without deleting the current relationships
     *
     * @param integer $id The id of the record in this model
     * @param mixed $assoc_name The name of the HABTM association
     * @return array Data array with current HABTM association intact
     */
    function _auxAssoc($id,$assoc_name)
    {
        //disable query cache
        $back_cache=$this->cacheQueries;
        $this->cacheQueries=false;

        $this->recursive=1;
        $this->unbindAll(array(‘hasAndBelongsToMany’=>array($assoc_name)));
        $data=$this->findById($id);
        $assoc_data=array();
        foreach ($data[$assoc_name] as $assoc)
        {
            $assoc_data[]=$assoc[‘id’];
        }
        unset($data[$assoc_name]);
        $data[$assoc_name][$assoc_name]=$assoc_data;

        //restore previous setting of query cache
        $this->cacheQueries=$back_cache;

        return $data;
    }

Updated packages in cakeforge August 23, 2006

Posted by rossoft in CakePHP.
5 comments

Hi! I’ve been out because my internet connection at home was down đŸ˜¦ Now I return with some improvements in the HeadHelper and CJS Templates. They’re in cakeforge (CJS Templates version 2.1)

The syntax has changed a bit in HeadHelper (read the version changes) and don’t forget to read the installation instructions for CJS Templates.