ActiveRecord Debugging Library

[eluser]Phil Sturgeon[/eluser]
I recently wrote an article on how to debug your ActiveRecord queries but I think it could be even easier.

Here is a rough protoype for a VERY simple debugging library that will help output ANY ActiveRecord query.

Code:

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
* CodeIgniter
*
* An open source application development framework for PHP 4.3.2 or newer
*
* @package        CodeIgniter
* @author        ExpressionEngine Dev Team
* @copyright    Copyright (c) 2008, EllisLab, Inc.
* @license        http://ellislab.com/codeigniter/user-guide/license.html
* @link        http://codeigniter.com
* @since        Version 1.0
* @filesource
*/

// ------------------------------------------------------------------------

/**
* DB Debug Class
*
* Helps you debug your ActiveRecord queries over mail, log or screen
*
* @package        CodeIgniter
* @subpackage    Libraries
* @category    Database
* @author        Phil Sturgeon < [email protected] >
* @link        
*/

class DB_debug extends CI_DB_active_record {
    
    var $_email; // Used to send emails from the server
    var $_mode;
    var $_run_query = FALSE;
    
    function mode($mode, $email = NULL)
    {
        $this->_mode = $mode;
        $this->_email = $email;
        
        return $this;
    }
    
    function run()
    {
        $this->_run_query = TRUE;
        
        return $this;
    }
    
    function query($sql)
    {
        log_message('debug', 'DB Deug class produced: '.htmlentities($sql) );
        
        switch($this->_mode)
        {
            case 'email':
                $this->load->library('email');

                $this->email->to($this->_email);
                
                $this->email->subject('SQL Debug: '.time());
                $this->email->message('SQL query: '.$sql);
                
                $this->email->send();
            break;
            
            case 'echo':
                echo $sql;
            break;
            
            case 'exit':
                exit($sql);
            break;
        }
        
    }
}

/* End of file DB_active_rec.php */
/* Location: ./system/application/libraries/DB_debug.php */

Theoretically (untested) you could run any of your normal commands such as get, getwhere, update, select, etc through $this->db_debug->get(); then it would work fine. For example:

Code:

$this->db->set('something', $variable);
$this->db->where('whatever', 'stuff');

// Just output to screen
$this->db_debug->mode('echo');
$this->db_debug->update('table');

// Stop page from going any further
$this->db_debug->mode('exit');
$this->db_debug->update('table');

// E-mail you the query
$this->db_debug->mode('email', '[email protected]');
$this->db_debug->update('table');

All of these will be logged in the CodeIgniter logs too.

Totally untested, just looking for feedback.

[eluser]Phil Sturgeon[/eluser]
Forgot to mention, this will stop it from running the command. Perhaps I could add another mode or option that would allow it to run the query using parent::query()?

[eluser]Yorick Peterse[/eluser]
I'd suggest you change addslashes() with mysql_real_escape_string() as addslashes can forget to backslash certain bytevalues that can be used for SQL injections.

The second is that I can't really see the query() function being called, or is that just me ?

[eluser]Phil Sturgeon[/eluser]
query() is used by all of the ActiveRecord functions such as update, delete, etc as ActiveRecord is simply there to build your SQL queries for you. Therefore replacing this central function with the debugging code stops it from running the queery and sends it to scree, log, email etc instead.

As for addslashes, this is not important. This query is just going into a textfile anyhow, so it probably doesn't even need anything (other than perhaps htmlentities to stop weird chars getting in there).

[eluser]Yorick Peterse[/eluser]
[quote author="Phil Sturgeon" date="1243880290"]query() is used by all of the ActiveRecord functions such as update, delete, etc as ActiveRecord is simply there to build your SQL queries for you. Therefore replacing this central function with the debugging code stops it from running the queery and sends it to scree, log, email etc instead.

As for addslashes, this is not important. This query is just going into a textfile anyhow, so it probably doesn't even need anything (other than perhaps htmlentities to stop weird chars getting in there).[/quote]

*slaps face* Ofcourse, should've known that xD

[eluser]johnwbaxter[/eluser]
Oh you should definitely make it so that it carries on running the query (or at least allow an extra variable to be passed in to switch that on and off on demand.)

[eluser]Phil Sturgeon[/eluser]
Was wondering if there is much point, as if you want it to run you could just use db->last_query. Hmm...

More opinions on that?

If running the query instead of just outputting should be an option, should it be defaulted to true or false?

[eluser]johnwbaxter[/eluser]
Well because the db->last_query does not e-mail you the sql or write it to the log file? It should probably be defaulted to outputting the output.

Those were tough questions! Phew, i need a sit down.

[eluser]Phil Sturgeon[/eluser]
Ha, true. Ok will think about the best syntax to do that and wrap this one up soon. Probably not tonight, BBQ!

[eluser]johnwbaxter[/eluser]
oh nice, someone a few doors down from me is having one, all i can smell is relish and roasting meat. God i'm hungry now....