Magento var/debug/pdo_mysql.log SQL Statement Parser

varDebugPdoMysqlLog.php

<?php

class varDebugPdoMysqlLog
{
    private $_fileName = 'var/debug/pdo_mysql.log.newdesign';
    private $_filterKeys = array('datetime', 'pid', 'type', 'SQL', 'BIND', 'AFF', 'TIME');
    // private $_filterKeys = array('type', 'SQL', 'BIND');
    private $_skipTables = array(
          'admin_user'
        , 'eav_entity_store'
        , 'enterprise_admin_passwords'
        , 'sales_flat_quote'
        , 'sales_flat_quote_address'
        , 'sales_flat_quote_item'
        , 'sales_flat_quote_item_option'
        , 'sales_flat_quote_payment'
        , 'report_event'
        , 'enterprise_logging_event'
        , 'enterprise_logging_event_changes'
        , 'captcha_log'
        , 'catalogrule_affected_product'
        , 'catalogrule_group_website'
        , 'catalogrule_product_price'
        , 'catalogrule_product_price_tmp'
        , 'enterprise_catalogpermissions_index'
        , 'enterprise_targetrule_index'
        , 'enterprise_targetrule_index_crosssell'
        , 'enterprise_targetrule_index_related'
        , 'enterprise_targetrule_index_upsell'
        , 'enterprise_targetrule_product'
        , 'tag_summary'
    );
    private $_filterTables = array(
          'catalog_category_entity'
        , 'catalog_category_entity_int'
        , 'catalog_category_entity_text'
        , 'catalog_category_entity_varchar'
        , 'catalog_category_product'
        , 'catalog_product_entity'
        , 'catalog_product_entity_int'
        , 'catalog_product_entity_media_gallery'
        , 'catalog_product_entity_media_gallery_value'
        , 'catalog_product_entity_varchar'
        , 'catalogsearch_fulltext'
        , 'cms_block'
        , 'cms_page'
        , 'core_config_data'
    );
    private $DB_USERNAME = 'USERNAME';
    private $DB_PASSWORD = 'PASSWORD';
    private $DSN = 'mysql:host=localhost;port=3306;dbname=DATABASE';
    
    private $_fileHandle;
    private $_data = array();
    
    public function __construct($limit)
    {
        $this->_fileHandle = $this->_getFileHandler();
        $_lineBuffer = '';
        while (($line = fgets($this->_fileHandle)) !== false)
        {
            $this->_exitIfCounterReach($limit);
            if ("\n" !== $line)
            {
                $_lineBuffer .= $line;
                continue;
            }
            $lines = $_lineBuffer;
            $_lineBuffer = ''; // reset line buffer
            
            if ($this->_skipBeforeParsing($lines))
                continue;
            $data = $this->_parse($lines);
            if ($this->_filterData($data))
                $this->_data[] = $data;
        }
        $this->_filterEmptyTransactions();
    }

    private function _exitIfCounterReach($limit)
    {
        static $_lineCounter = 0;
        empty($limit) || $limit === ++ $_lineCounter && exit(1);
    }

    private function _getFileHandler()
    {
        $this->_fileHandle = fopen($this->_fileName, 'r');
        if (empty($this->_fileHandle))
            throw new Exception('error opening the file.');
        return $this->_fileHandle;
    }
    
    /**
     * @return boolean true to skip lines, false to add the lines for parsing
     **/
    private function _skipBeforeParsing($lines)
    {
        if (strpos($lines, 'TRANSACTION')) return false;
        if (! preg_match('/SQL: (INSERT|UPDATE|DELETE)/', $lines)) return true;
        return false;
    }

    private function _filterData($data)
    {
        return 'QUERY' != $data['type']
            || in_array($data["table"], $this->_filterTables);
    }

    private function _parse($lines)
    {
        $lines = explode("\n", $lines);
        $data = array();
        $datetime = explode('##', array_shift($lines));
        $data['datetime'] = trim($datetime[1]);
        $pid_type = explode('##', array_shift($lines));
        $data['pid'] = trim($pid_type[1]);
        $data['type'] = trim($pid_type[2]);
        foreach ($lines as $line)
        {
            switch(strstr($line, ':', 1))
            {
                case 'SQL':
                case 'BIND':
                case 'AFF':
                case 'TIME':
                    $key = strstr($line, ':', 1);
                    $data[$key] = str_replace($key . ': ', '', $line);
                    break;
                default:
                    // continue last key
                    $data[$key] .= $line;
                    break;
            }
        }
        return $this->_processRawData($data);
    }
    
    private function _processRawData($data)
    {
        $data = array_intersect_key($data, array_flip($this->_filterKeys));
        if (array_key_exists('BIND', $data) && $data['BIND'])
        {
            $data["BIND"] = $this->_reverseVarExport($data["BIND"]);
        }
        if (array_key_exists('SQL', $data) && $data['SQL'])
        {
            $_tmp = explode('`', $data['SQL'], 3);
            $data["table"] = $_tmp[1];
        }
        return $data;
    }
    
    private function _reverseVarExport($string)
    {
        $_tmp = array();
        eval ('$_tmp = ' . $string . ';');
        return is_array($_tmp) ? $_tmp : $string;
        
    }

    private function _filterEmptyTransactions()
    {
        $counter = 0;
        foreach ($this->_data as $key => $value)
        {
            switch($value['type'])
            {
                case "TRANSACTION BEGIN":
                    $counter = 0;
                    $lastKey = $key;
                    break;
                case "TRANSACTION COMMIT":
                    if (empty($counter))
                    {
                        unset($this->_data[$lastKey]);
                        unset($this->_data[$key]);
                    }
                    break;
                case "QUERY":
                default:
                    $counter++;
                    break;
            }
        }
        $this->_data = array_values($this->_data);
    }

    public function __destruct()
    {
        fclose($this->_fileHandle);
        if (false)
        {
            echo json_encode($this->_data);
        }
        else if (false)
        {
            // DEPRECATED, PdoMysql::interpolateQuery is NOT RELIABLE
            foreach ($this->_data as $data)
            {
                switch($data['type'])
                {
                    case "TRANSACTION BEGIN":
                        echo "BEGIN;\n";
                        break;
                    case "TRANSACTION COMMIT":
                        echo "COMMIT;\n";
                        break;
                    case "QUERY":
                        echo empty($data['BIND'])
                            ? $data['SQL']
                            : PdoMysql::interpolateQuery($data['SQL'], $data['BIND']);
                        echo ";\n";
                        break;
                }
            }
        }
        else
        {
            $pdo = new PdoMysql($this->DSN, $this->DB_USERNAME, $this->DB_PASSWORD);
            $pdo->save($this->_data);
        }
    }
}

class PdoMysql
{
    private $connection;
    public function __construct($dsn, $username, $password)
    {
        $this->connection = new PDO($dsn, $username, $password
            , array( PDO::ATTR_PERSISTENT => false));
    }

    public function save($data)
    {
        while ($data)
        {
            $_data = array_shift($data);
            switch($_data['type'])
            {
                case "TRANSACTION BEGIN":
                    $this->connection->beginTransaction();
                    break;
                case "TRANSACTION COMMIT":
                    $this->connection->commit();
                    break;
                case "QUERY":
                    try
                    {
                        $stmt = $this->connection->prepare($_data['SQL']);
                        empty($_data['BIND'])
                            ? $stmt->execute()
                            : $stmt->execute($_data['BIND']);
                        // error_log('SUCCESS: ' . $_data['datetime'].$_data['pid']);
                        echo '.';
                    } catch(PDOException $e )
                    {
                        error_log('FAILED: ' . $_data['datetime'].$_data['pid']);
                        error_log($e);
                    }
                    break;
                default:
                    error_log('Type not supported');
                    break;
            }
        }
    }

    /**
     * Replaces any parameter placeholders in a query with the value of that
     * parameter.
     * Useful for debugging. Assumes anonymous parameters from
     * $params are are in the same order as specified in $query
     *
     * @param string $query
     *            The sql query with parameter placeholders
     * @param array $params
     *            The array of substitution parameters
     * @return string The interpolated query
     */
    public static function interpolateQuery($query, $params)
    {
        $keys = array();
        
        // build a regular expression for each parameter
        foreach ($params as $key => $value) {
            $keys[] = is_string($key)
                ? '/:' . $key . '/'
                : '/[?]/';
            if (is_string($value))
            {
                $params[$key] = "'" . addslashes($value) . "'";
                // $params[$key] = mysql_escape_string($value);
            }
            else if (is_null($value))
            {
                $params[$key] = 'null';
            }
        }
        
        $query = preg_replace($keys, $params, $query, 1, $count);
        
        // trigger_error('replaced '.$count.' keys');
        
        return $query;
    }
}

$lineLimit = empty($argv[1]) ? 0 : (int) $argv[1];
new varDebugPdoMysqlLog($lineLimit);

Example usage:

$ php varDebugPdoMysqlLog.php 1000

where 1000 refers to the first X number of lines you want to parse