Adding a profiler to a Db class.

Code Naked

Monday, August 18. 2008

Posted by Matthew Purdon in PHP

Comments (0)
Trackbacks (0)

Adding a profiler to a Db class.

First of all, in case you don't know me that well, you need to know that I am a big fan of unit testing. Although I like the idea of using a database abstraction layer, I have come to dislike PDO for several reasons. First of all it's behavior is not the same for every database driver that you use. This is not a fault of PDO itself so much as the authors of the database specific drivers that are not able to implement the entire feature set. Secondly, I am not sure I buy into this "You can just switch databases and all you have to do is change the config" argument. Any application that either has a DBA on the team or cares at all about performance will have tailored SQL to get the most out of the database - which as you know tends to be the main bottleneck in an application. The main reason that I dislike PDO is that it is such a pain to mock it while unit testing. For some reason it was deemed necessary to have PDO connect on construct rather than either having an explicit connect() method or a lazy connect when one is needed. Even though you can specify that you don't run the constructor when you mock PDO, you then run into internal trouble as some settings are not set up properly and you get errors. At work, I tend to just wrap PDO with my own Db class that proxies calls to the internal PDO instance so that I can mock my Db class and everything plays nicely.

For my own junk, I tend to always go with MySQL as my database so I don't really like the idea of adding a wrapper to a wrapper to a wrapper in order to talk to it. I use MySQLi as my db class, but I still wrap it so that I can do lazy connects. The reason for this is so that once my cache is warmed up, and the user has logged in, I don't even connect to the database during a normal session. This helps tremendously as the connection to the database is the most expensive part of database interaction. If I do need to perform any write operations, they are performed by the Unit of Work, which doesn't kick in until the dipatchLoopShutdown. (I still have a few tweaks in regards to caching with my Unit of Work implementation before I show you how that works.) This also helps to ensure that if I do need to open a connection, it's for the shortest amount of time possible.

First, let's take a look at the MySQLi wrapper.

class Naked_Db
{
    /**
     * @var mysqli $mysqli
     */
    protected $mysqli;

/**
     * @var boolean $connected
     */
    protected $connected;

/**
     * @var boolean $inTransaction
     */
    protected $inTransaction;

/**
     * @var string $host
     */
    protected $host;

/**
     * @var string $username
     */
    protected $username;

/**
     * @var string $password
     */
    protected $password;

/**
     * @var string $db
     */
    protected $db;

/**
     * @var naked_Db_profiler $profiler
     */
    protected $profiler;

/**
     * Constructor
     */
    public function __construct($host, $username, $password, $db)
    {
        $this->host = $host;
        $this->username = $username;
        $this->password = $password;
        $this->db = $db;

// Get the MySQLi object prepared to connect
        $this->mysqli = mysqli_init();
        $this->connected = false;

$this->profiler = new Naked_Db_Profiler($this, true);
    }

/**
     * Connect to the database
     *
     * @throws Naked_Db_Exception
     */
    public function connect()
    {
        if (!$this->isConnected()) {
            // Connect for real now
            $this->connected = $this->mysqli->real_connect(
                                    $this->host,
                                    $this->username,
                                    $this->password,
                                    $this->db);

if (mysqli_connect_errno()) {
                throw new Naked_Db_Exception('Could not connect: ' . mysqli_connect_error());
            }

$backtrace = debug_backtrace();
            $message = "Naked_Db: Connected to database {$this->db} from " .
                       "{$backtrace[2]['class']}::{$backtrace[2]['function']} " .
                       "({$backtrace[1]['class']}::{$backtrace[1]['function']})";
            Naked_App::getWorkspace()->getLogger()->info($message);
        }
    }

/**
     * Close a connection to the database
     */
    public function close()
    {
        $this->mysqli->close();
        $this->connected = false;
        Naked_App::getWorkspace()->getLogger()->info("Naked_Db: Disconnected from database {$this->db}");
    }

/**
     * Determine if we are connected to the database
     *
     * @return boolean
     */
    public function isConnected()
    {
        return $this->connected > 0;
    }

/**
     * Perform a SQL query
     *
     * @param string $sql
     * @return Naked_Db_Result
     * @throws Naked_Db_Exception
     */
    public function query($sql)
    {
        $this->connect();

$queryId = $this->profiler->queryStart($sql);
        $result = $this->mysqli->query($sql);

if (true === $result) {
            $this->profiler->queryEnd($queryId, $this->mysqli->affected_rows);
            return true;
        } else if ($result instanceof mysqli_result ) {
            $this->profiler->queryEnd($queryId, $result->num_rows);
            return new Naked_Db_Result($result);
        } else {
            throw new Naked_Db_Exception($this->mysqli->error);
        }
    }

/**
     * Quote and escape the provided value
     *
     * Sucks that we need a connection to do this...
     *
     * @param mixed $value
     * @return string
     */
    public function quote($value)
    {
        $this->connect();
        $value = $this->mysqli->escape_string($value);

return "'{$value}'";
    }

/**
     * Escape the provided value
     *
     * @param mixed $value
     * @return string
     */
    public function escape($value)
    {
        $this->connect();
        return $this->mysqli->escape_string($value);
    }

/**
     * Create a prepared statement from the provided SQL
     *
     * @param string $sql
     * @return Naked_Db_Statement
     */
    public function prepare($sql)
    {
        $this->connect();

$statement = $this->mysqli->prepare($sql);

if(false === $statement) {
            throw new Naked_Db_Exception($this->mysqli->error);
        }

return new Naked_Db_Statement($statement, $sql, $this->profiler);
    }

/**
     * Begin a transaction
     */
    public function beginTransaction()
    {
        $this->connect();

// Turn off autocommit.
        $this->mysqli->autocommit(false);

// Begin the transaction
        $this->mysqli->query('BEGIN;');

$this->inTransaction = true;
    }

/**
     * Commit the active transaction
     */
    public function commit()
    {
        if (!$this->inTransaction) {
            throw new Naked_Db_Exception('Attempted to commit when no transaction started');
        }

$this->mysqli->commit();
        $this->inTransaction = false;
    }

/**
     * Roll back the active transaction
     */
    public function rollback()
    {
        if (!$this->inTransaction) {
            throw new Naked_Db_Exception('Attempted to commit when no transaction started');
        }

$this->mysqli->rollback();
        $this->inTransaction = false;
    }

/**
     * @return Naked_Db_Profiler
     */
    public function getProfiler()
    {
        return $this->profiler;
    }
}

Pretty simple really as it essentially just wraps the MySQLi class. There are a couple of things to note however. First of all, if you are wondering what Naked_App::getWorkspace() is ... I'll explain that in my next post. Basically think of it like a registry that houses all of the tools I need to get work done in an application such as a database connection, a logger, and access control list etc. Another thing to note is that I am calling $this->connect() at the start of each method that requires that I am connected to the database. This is how I implement the lazy connect functionality. To make sure that I am not connecting needlessly I also log the fact that I connected and which methods forced me to connect. This can help me track down caching issues among other things. You can see what a typical log message looks like in FirePHP here:

I also wrap two other MySQLi classes for convenience. The first is the mysqli_result object:

class Naked_Db_Result
{
    /**
     * @var Mysqli_result $result
     */
    protected $result;

/**
     * Constructor
     * 
     * @param mysqli_result $result
     */
    public function __construct($result)
    {
        $this->result = $result;
    }

/**
     * Fetch an object from the result set
     * 
     * @return stdClass
     */
    public function fetch()
    {
        return $this->result->fetch_object();
    }

/**
     * Fetch an array of objects fromt he result set
     * 
     * @return array
     */
    public function fetchAll()
    {
        $results = new ArrayObject();

while ($dataObject = $this->result->fetch_object()) {
            $results->append($dataObject);
        }

return $results;
    }

/**
     * Return the number of rows in the result set
     * 
     * @return integer
     */
    public function numRows()
    {
        return $this->result->num_rows;
    }

/**
     * Close the result set
     */
    public function close()
    {
        $this->result->close();
    }
}

I basically just use it to be able to fetch one or more data objects from the result set. I like to use objects as much as I can. The other class that I wrap is the mysqli_stmt class, which is basically a prepared statement. I have a really simple one at the moment, you can see it here:

class Naked_Db_Statement
{
    /**
     * @var mysqli_stmt $statement
     */
    protected $statement;

/**
     * @var string $sql
     */
    protected $sql;

/**
     * @var Naked_Db_Profiler $profiler
     */
    protected $profiler;

/**
     * Constructor
     *
     * @param mysqli_stmt $statement
     * @param string $sql
     * @param Naked_Db_Profiler $profiler
     * @return new naked_Db_Statement
     */
    public function __construct(mysqli_stmt $statement, $sql, $profiler=null)
    {
        $this->statement = $statement;
        $this->sql = $sql;
        $this->profiler = $profiler;
    }

/**
     * Execute the prepared statement
     *
     * @return boolean
     */
    public function execute()
    {
        $queryId = $this->profiler->queryStart($this->sql);
        $result = $this->statement->execute();
        $this->profiler->queryEnd($queryId, $this->affectedRows());
        return $result;
    }

/**
     * Return the id from last insert
     *
     * @return integer
     */
    public function lastInsertId()
    {
        return $this->statement->insert_id();
    }

/**
     * Return the number of rows affected by the last operation
     */
    public function affectedRows()
    {
        return $this->statement->affected_rows();
    }
}

As a convention I only use prepared statements for write operations so I don't need to support results from a statement execute() call. The last piece of the puzzle and the point of the entire article is the profiler. Here it is:

class Naked_Db_Profiler
{
    /**
     * @var Naked_Db $db
     */
    protected $db;

/**
     * @var array $profiles
     */
    protected $profiles = array();

/**
     * @var integer $queryIndex
     */
    protected $queryIndex = 0;

/**
     * @var boolean $enabled
     */
    protected $enabled;

/**
     * Constructor
     *
     * @param Naked_Db $db
     * @param boolean $enabled
     */
    public function __construct(Naked_Db $db, $enabled=false)
    {
        $this->db = $db;
        $this->enabled = $enabled;
    }

/**
     * Get all profiled queries
     *
     * @return array
     */
    public function getProfiles()
    {
        return $this->profiles;
    }

/**
     * Profile the start of a query
     *
     * @return integer
     */
    public function queryStart($sql)
    {
        if (!$this->enabled) {
            return false;
        }

$this->profiles[$this->queryIndex]['start'] = $this->getTime();
        $this->profiles[$this->queryIndex]['sql'] = $sql;

return $this->queryIndex++;
    }

/**
     * Profile the end of a query
     *
     * @param integer $index
     * @param integer $numRows
     * @return boolean
     */
    public function queryEnd($index=null, $numRows=0)
    {
        // If we are not enabled or the index is invalid don't do anything
        if (!$this->enabled || false === $index) {
            return false;
        }

if (is_null($index)) {
            $index = $this->queryIndex;
        }

$this->profiles[$index]['num_rows'] = $numRows;
        $this->profiles[$index]['end'] = $this->getTime();

$elapsed = $this->profiles[$index]['end'] - $this->profiles[$index]['start'];
        $this->profiles[$index]['elapsed'] = number_format($elapsed, 6);

return true;
    }

/**
     * Get the explain plan for a given query
     *
     * @param integer $index
     * @return array
     * @throws Naked_Db_Exception
     */
    public function getExplainPlan($index=null)
    {
        if (is_null($index)) {
            $index = $this->queryIndex;
        }

$sql = $this->profiles[$index]['sql'];

// We cannot get an explain plan for queries with bound parameters
        if (preg_match('#= ?:#', $sql) || strpos($sql, '?') !== false) {
            throw new Naked_Db_Exception('Query is a prepared statment with bound parameters.');
        }

// We can only explain select statements
        if (strpos($sql, 'SELECT') === false) {
            throw new Naked_Db_Exception('Query is not a select statement');
        }

// We can't explain found_rows calls either
        if (strpos($sql, 'SELECT FOUND_ROWS()') !== false) {
            throw new Naked_Db_Exception('Query is for the number of found rows');
        }

// We are using preg_replace because we want to limit the number of replacements
        $sql = preg_replace('#SELECT#', 'EXPLAIN SELECT', $this->profiles[$index]['sql'], 1);

$results = $this->db->query($sql);
        $records = $results->fetchAll();
        unset($results);

return $records;
        if (is_null($index)) {
            $index = $this->queryIndex;
        }
    }

/**
     * Get an accurate timestamp
     *
     * @return float
     */
    protected function getTime()
    {
        return (float) array_sum(explode(' ', microtime(true)));
    }
}

Basically all it does it record the start and end times of a query as well as providing a means for getting the explain plans for queries that have already run. It doesn't actually perform the explain queries until you explicitly ask for them, that way i can keep the profiler overhead lower for the default usage. Speaking of usage, here's how I display database debug information:Now of course you know I don't just vomit this information all over my page, I log it to FirePHP. But that's a topic for another time!

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

No comments

Add Comment

Frontpage - Top level

Quicksearch

Archives

Syndicate This Blog

Blog Administration

Open login screen

Amazon Recommendations

Online shopping from the earth's biggest selection of books, magazines, music, DVDs, videos, electronics, computers, software, apparel & accessories, shoes, jewelry, tools & hardware, housewares, furniture, sporting goods, beauty & personal care, broadband & dsl, gourmet food & just about anything else.