Yii2DbPanel.php

Go to the documentation of this file.

<?php

class Yii2DbPanel extends Yii2DebugPanel
{
        public $insertParamValues = true;
        public $canExplain = true;

        public function init()
        {
                $this->_logsEnabled = true;
                $this->_logsLevels = CLogger::LEVEL_PROFILE;
                $this->_logsCategories = 'system.db.CDbCommand.*';
                parent::init();
        }

        public function getName()
        {
                return 'Database';
        }

        public function getSummary()
        {
                $timings = $this->calculateTimings();
                $count = count($timings);
                $time = 0;
                foreach ($timings as $timing) {
                        $time += $timing[4];
                }
                if (!$count) return '';
                return $this->render(dirname(__FILE__) . '/../views/panels/db_bar.php', array(
                        'count' => $count,
                        'time' => number_format($time * 1000) . ' ms',
                ));
        }

        public function getDetail()
        {
                return $this->render(dirname(__FILE__) . '/../views/panels/db.php', array(
                        'queries' => $this->getQueriesInfo(),
                        'queriesCount' => count($this->calculateTimings()),
                        'resume' => $this->getResumeInfo(),
                        'resumeCount' => count($this->calculateResume()),
                        'connectionsCount' => count($this->data['connections']),
                        'connections' => $this->getConnectionsInfo(),
                ));
        }

        protected function getQueriesInfo()
        {
                $items = array();
                foreach ($this->calculateTimings() as $timing) {
                        $items[] = array(
                                'time' => date('H:i:s.', $timing[3]) . sprintf('%03d', (int)(($timing[3] - (int)$timing[3]) * 1000)),
                                'duration' => sprintf('%.1f ms', $timing[4] * 1000),
                                'procedure' => $this->formatSql($timing[1], $this->insertParamValues),
                        );
                }
                return $items;
        }

        protected function getResumeInfo()
        {
                $items = array();
                foreach ($this->calculateResume() as $item) {
                        $items[] = array(
                                'procedure' => $item[0],
                                'count' => $item[1],
                                'total' => sprintf('%.1f ms', $item[2] * 1000),
                                'avg' => sprintf('%.1f ms', $item[2] * 1000 / $item[1]),
                                'min' => sprintf('%.1f ms', $item[3] * 1000),
                                'max' => sprintf('%.1f ms', $item[4] * 1000),
                        );
                }
                return $items;
        }

        protected function getConnectionsInfo()
        {
                $connections = array();
                foreach ($this->data['connections'] as $id => $connection) {
                        if ($connection['driver'] == 'mysql' && isset($connection['info'])) {
                                foreach (explode('  ', $connection['info']) as $line) {
                                        list($key, $value) = explode(': ', $line, 2);
                                        $connection[$key] = $value;
                                }
                                unset($connection['info']);
                        }
                        $connections[$id] = $connection;
                }
                return $connections;
        }

        private $_timings;

        protected function calculateTimings()
        {
                if ($this->_timings !== null) {
                        return $this->_timings;
                }
                $messages = $this->data['messages'];
                $timings = array();
                $stack = array();
                foreach ($messages as $i => $log) {
                        list($token, , $category, $timestamp) = $log;
                        $log[4] = $i;
                        if (strpos($token, 'begin:') === 0) {
                                $log[0] = $token = substr($token, 6);
                                $stack[] = $log;
                        } elseif (strpos($token, 'end:') === 0) {
                                $log[0] = $token = substr($token, 4);
                                if (($last = array_pop($stack)) !== null && $last[0] === $token) {
                                        $timings[$last[4]] = array(count($stack), $token, $category, $last[3], $timestamp - $last[3]);
                                }
                        }
                }
                $now = microtime(true);
                while (($last = array_pop($stack)) !== null) {
                        $delta = $now - $last[3];
                        $timings[$last[4]] = array(count($stack), $last[0], $last[2], $last[3], $delta);
                }
                ksort($timings);
                return $this->_timings = $timings;
        }

        private $_resume;

        protected function calculateResume()
        {
                if ($this->_resume !== null) {
                        return $this->_resume;
                }
                $resume = array();
                foreach ($this->calculateTimings() as $timing) {
                        $duration = $timing[4];
                        $query = $this->formatSql($timing[1], $this->insertParamValues);
                        $key = md5($query);
                        if (!isset($resume[$key])) {
                                $resume[$key] = array($query, 1, $duration, $duration, $duration);
                        } else {
                                $resume[$key][1]++;
                                $resume[$key][2] += $duration;
                                if ($resume[$key][3] > $duration) $resume[$key][3] = $duration;
                                if ($resume[$key][4] < $duration) $resume[$key][4] = $duration;
                        }
                }
                usort($resume, array($this, 'compareResume'));
                return $this->_resume = $resume;
        }

        private function compareResume($a, $b)
        {
                if ($a[2] == $b[2]) return 0;
                return $a[2] < $b[2] ? 1 : -1;
        }

        public function formatSql($message, $insertParams)
        {
                $sqlStart = strpos($message, '(') + 1;
                $sqlEnd = strrpos($message , ')');
                $sql = substr($message, $sqlStart, $sqlEnd - $sqlStart);
                if (strpos($sql, '. Bound with ') !== false) {
                        list($query, $params) = explode('. Bound with ', $sql);
                        if (!$insertParams) return $query;
                        $sql = $this->insertParamsToSql($query, $this->parseParamsSql($params));
                }
                return $sql;
        }

        private function parseParamsSql($params)
        {
                $binds = array();
                $pos = 0;
                while (preg_match('/((?:\:[a-z0-9\.\_\-]+)|\d+)\s*\=\s*/i', $params, $m, PREG_OFFSET_CAPTURE, $pos)) {
                        $start = $m[0][1] + strlen($m[0][0]);
                        $key = $m[1][0];
                        if (($params{$start} == '"') || ($params{$start} == "'")) {
                                $quote = $params{$start};
                                $pos = $start;
                                while (($pos = strpos($params, $quote, $pos + 1)) !== false) {
                                        $slashes = 0;
                                        while ($params{$pos - $slashes - 1} == '\\') $slashes++;
                                        if ($slashes % 2 == 0) {
                                                $binds[$key] = substr($params, $start, $pos - $start + 1);
                                                $pos++;
                                                break;
                                        }
                                }
                        } elseif (($end = strpos($params, ',', $start + 1)) !== false) {
                                $binds[$key] = substr($params, $start, $end - $start);
                                $pos = $end + 1;
                        } else {
                                $binds[$key] = substr($params, $start, strlen($params) - $start);
                                break;
                        }
                }
                return $binds;
        }

        private function insertParamsToSql($query, $params)
        {
                $sql = '';
                $pos = 0;
                do {
                        // Выявление ближайшей заэкранированной части строки
                        $quote = '';
                        if (preg_match('/[`"\']/', $query, $m, PREG_OFFSET_CAPTURE, $pos)) {
                                $qchar = $m[0][0];
                                $qbegin = $m[0][1];
                                $qend = $qbegin;
                                do {
                                        $sls = 0;
                                        if (($qend = strpos($query, $qchar, $qend + 1)) !== false) {
                                                while ($query{$qend - $sls - 1} == '\\') $sls++;
                                        } else {
                                                $qend = strlen($query) - 1;
                                        }
                                } while ($sls % 2);
                                $quote = substr($query, $qbegin, $qend - $qbegin + 1);
                                $token = substr($query, $pos, $qbegin - $pos);
                                $pos = $qend + 1;
                        } else {
                                $token = substr($query, $pos);
                        }
                        // Подстановка параметров в незаэкранированную часть SQL
                        $subsql = '';
                        $pind= 0;
                        $tpos = 0;
                        while (preg_match('/\:[a-z0-9\.\_\-]+|\?/i', $token, $m, PREG_OFFSET_CAPTURE, $tpos)) {
                                $key = $m[0][0];
                                if ($key == '?') $key = $pind++;
                                if (isset($params[$key])) {
                                        $value = $params[$key];
                                } else {
                                        $value = $m[0][0];
                                }
                                $subsql .= substr($token, $tpos, $m[0][1] - $tpos) . $value;
                                $tpos = $m[0][1] + strlen($m[0][0]);
                        }
                        $subsql .= substr($token, $tpos);
                        // Склейка
                        $sql .= $subsql . $quote;
                } while ($quote !== '');
                return $sql;
        }

        private $_hl;

        public function highlightSql($sql)
        {
                if ($this->_hl === null) {
                        $this->_hl = Yii::createComponent(array(
                                'class' => 'CTextHighlighter',
                                'language' => 'sql',
                                'showLineNumbers' => false,
                        ));
                }
                $html = $this->_hl->highlight($sql);
                return strip_tags($html, '<div>,<span>');
        }

        public function save()
        {
                $connections = array();
                foreach (Yii::app()->getComponents() as $id => $component) {
                        if ($component instanceof CDbConnection) {
                                /* @var CDbConnection $component */
                                $connections[$id] = array(
                                        'class' => get_class($component),
                                        'driver' => $component->getDriverName(),
                                );
                                try {
                                        $connections[$id]['server'] = $component->getServerVersion();
                                        $connections[$id]['info'] = $component->getServerInfo();
                                } catch (Exception $e) {}
                        }
                }

                return array(
                        'messages' => $this->getLogs(),
                        'connections' => $connections,
                );
        }

        public static function getExplainQuery($query, $driver)
        {
                if (preg_match('/^\s*SELECT/', $query)) {
                        switch ($driver) {
                                case 'mysql': return 'EXPLAIN ' . $query;
                                case 'pgsql': return 'EXPLAIN ' . $query;
                                case 'sqlite': return 'EXPLAIN QUERY PLAN ' . $query;
                                case 'oci': return 'EXPLAIN PLAN FOR ' . $query;
                        }
                }
                return null;
        }

        public static function explain($query, $connection)
        {
                $procedure = self::getExplainQuery($query, $connection->driverName);
                if ($procedure === null) {
                        throw new Exception('Explain not available');
                }
                switch ($connection->driverName) {
                        case 'oci':
                                $connection->createCommand($procedure)->execute();
                                return $connection->createCommand('SELECT * FROM table(dbms_xplan.display)')->queryAll();
                        default:
                                return $connection->createCommand($procedure)->queryAll();
                }
        }

        public function getExplainConnections($query)
        {
                $connections = array();
                foreach ($this->data['connections'] as $name => $connection) {
                        if (self::getExplainQuery($query, $connection['driver']) !== null) {
                                $connections[$name] = $connection;
                        }
                }
                return $connections;
        }

        public function messageByNum($number)
        {
                foreach ($this->calculateTimings() as $timing) {
                        if (!$number--) {
                                return $timing[1];
                        }
                }
                return null;
        }
}