Determine MySQL query type in Zend Framework

Question

Can anyone tell me how to determine query type i.e. select, update, delete or insert before it is executed over MySQL.

I strongly believe internally Zend Framework might be using mysql*_query function to execute them.

I need a centralized function which will return the query type before the execution.

I am using three files for every database table

I am giving you the example.

Say I want to create model for categories table.

So I will create following files

DbTable/Categories.php

class Application_Model_DbTable_Categories extends Zend_Db_Table_Abstract {
    protected $_name = 'categories';
    protected $_dependentTables = array('Application_Model_DbTable_Videos');
}

Categories.php

class Application_Model_Categories extends Application_Model_CommonGetterSetter {
    protected $_type = array('id' => 'int', 'name' => 'string', 'slug' => 'string', 'status' => 'int');

    public function __construct(array $options = null) {
        parent::__construct($options, __CLASS__);
    }
}

CategoriesMapper.php

class Application_Model_CategoriesMapper {

    protected $_dbTable;

    public function setDbTable($dbTable) {

        if (is_string($dbTable)) {
            $dbTable = new $dbTable();
        }

        if (!$dbTable instanceof Zend_Db_Table_Abstract) {
            throw new Exception('Invalid table data gateway provided');
        }

        $this->_dbTable = $dbTable;
        return $this;
    }



    public function getDbTable() {
        if (null === $this->_dbTable) {
            $this->setDbTable('Application_Model_DbTable_Categories');
        }
        return $this->_dbTable;
    }



    public function save(Application_Model_Categories $categories) {
        $data = array(
                'name'      => $categories->name,
                'slug'      => $categories->slug,
                'status'    => $categories->status,
        );

        if (@$categories->id <= 0) {
            return $this->getDbTable()->insert($data);
        } else {
            $this->getDbTable()->update($data, array('id = ?' => $categories->id));
            return $categories->id;
        }
    }

CommonGetterSetter.php

class Application_Model_CommonGetterSetter {

    protected $_data = array();
    private $_class_name;

    public function __construct(array $options = null, $class_name = null) {
        if (is_array($options)) {
            foreach ($options as $key => $value) {
                $this->$key = $value;
            }
            $this->_class_name = $class_name;
        }
    }

    public function __set($name, $value) {
        if (!array_key_exists($name, $this->_type)) {
            throw new Exception("Invalid {$this->_class_name} property". $name);
        }
        else {
            settype($value, $this->_type[$name]);
            $this->_data[$name] = $value;
        }
    }

    public function __get($name) {
        if (!array_key_exists($name, $this->_type)) {
            throw new Exception("Invalid {$this->_class_name} property". $name);
        }
        else {
            return $this->_data[$name];
        }
    }
}

So I want to find out which query was executed , where and what should i write?

Mny thanks in advance.

I know the code is bit long but that was to give a complete idea.

Answer 1

I strongly believe internally Zend Framework might be using mysql*_query function to execute them.

ZF's Database class does not have support for the decrepit mysql extension. It has adapters for PDO and mysqli. You'll know which of these you are using because you have to expressly specify one in your code or configuration.

So I want to find out which query was executed

I think that you're going to be better served by rethinking your design. It's not entirely clear what is going on here, nor is it clear why you think you need to know the query type, nor is it clear where you expect to need to know the query type.

Please review the Zend_Db_Table documentation, as it looks like you might have missed the point somewhere along the way.

In specific, there are already methods provided by Zend_Db_Table for inserts, deletes and updates. If you need to change the behavior of the appropriate method, you should do so by simply overriding it in your class.

Further, it looks like you're trying to build some sort of smart column type definition system. You don't need to do that, as again, Zend_Db_Table already provides this information to you, and even lets you hard-code it if you'd rather it not determine this information automatically.

ZF is a complicated beast. If you're going to use it, I suggest that you fully understand everything it can do. Building non-standard, redundant architecture is only going to complicate things for you later on.

Answer 2

EDIT : I was looking at the code for the Zend_Db_Table and Zend_Db_Table_Abstract and my second solution seems much more difficult now.

Hey guys,

If you want to give yourself a bit of a headache, what you can do is keep using the multidb resource to create two connections, one to the slave and one for the master.

Set the default connection to the one that will be used the most.

Then, with the other connection you can fetch it manually every time you need to use it and deal directly with the adapter instead of going through the DbTable class.

For example, create another method for the mapper classes called getOtherDbAdapter() that gets the other adapter. Then you can use the adapter from there to perform selects or inserts.

OH!

Another solution is that you can extend extend the class Zend_Db_Table or Zend_Db_Table_Abstract and create another variable for the second db connection.

Then, copy and paste the implementation code for all the fetch/select/insert/update methods from the Zend_Db_Table_Abstract class and have it use the the other adapter whenever you see the code performing the operations you want to have redirected to the other db.