DatabasePanel::hightlight() - Code Metrics - Inspection of "update database panel" - recca0120/laravel-tracy - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( b79de5...a8cca6 )

by recca

created 2017-03-08 05:04 UTC

DatabasePanel::hightlight() C

↳ Parent: Project

Complexity

Conditions	16
Paths	1

Size

Total Lines	63
Code Lines	38

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	28
CRAP Score	16

Importance

Changes

Metric	Value
cc	16
eloc	38
nc	1
nop	3
dl	0
loc	63
ccs	28
cts	28
cp	1
crap	16
rs	6.0607
c	0
b	0
f	0

How to fix Long Method Complexity

<?php

namespace Recca0120\LaravelTracy\Panels;

use PDO;
use DateTime;
use Exception;

class DatabasePanel extends AbstractSubscriablePanel implements IAjaxPanel
{
    /**
     * $queries.
     *
     * @var array
     */
    protected $queries = [];

    /**
     * $totalTime.
     *
     * @var float
     */
    protected $totalTime = 0.0;

    /**
     * $counter.
     *
     * @var int
     */
    protected $counter = 0;

    /**
     * KEYWORDS1.
     *
     * @var string
     */
    const KEYWORDS1 = 'SELECT|(?:ON\s+DUPLICATE\s+KEY)?UPDATE|INSERT(?:\s+INTO)?|REPLACE(?:\s+INTO)?|DELETE|CALL|UNION|FROM|WHERE|HAVING|GROUP\s+BY|ORDER\s+BY|LIMIT|OFFSET|SET|VALUES|LEFT\s+JOIN|INNER\s+JOIN|TRUNCATE';

    /**
     * KEYWORDS2.
     *
     * @var string
     */
    const KEYWORDS2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|[RI]?LIKE|REGEXP|TRUE|FALSE';

    /**
     * explain sql.
     *
     * @param PDO $pdo
     * @param string $sql
     * @param array $bindings
     * @return array
     */
    public static function explain(PDO $pdo, $sql, $bindings = [])
    {
        $explains = [];
        if (preg_match('#\s*\(?\s*SELECT\s#iA', $sql)) {
            $statement = $pdo->prepare('EXPLAIN '.$sql);
            $statement->execute($bindings);
            $explains = $statement->fetchAll(PDO::FETCH_CLASS);
        }

        return $explains;
    }

    /**
     * Returns syntax highlighted SQL command.
     *
     *
     * @param string $sql
     * @param array $bindings
     * @param \PDO $pdo
     * @return string
     */
    public static function hightlight($sql, array $bindings = [], PDO $pdo = null)
    {
        // insert new lines
        $sql = " $sql ";
        $sql = preg_replace("#(?<=[\\s,(])(".static::KEYWORDS1.")(?=[\\s,)])#i", "\n\$1", $sql);

        // reduce spaces
        $sql = preg_replace('#[ \t]{2,}#', ' ', $sql);

        // syntax highlight
        $sql = htmlspecialchars($sql, ENT_IGNORE, 'UTF-8');
        $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])(".static::KEYWORDS1.")(?=[\\s,)])|(?<=[\\s,(=])(".static::KEYWORDS2.")(?=[\\s,)=])#is", function ($matches) {
            if (! empty($matches[1])) { // comment
                return '<em style="color:gray">'.$matches[1].'</em>';
            } elseif (! empty($matches[2])) { // error
                return '<strong style="color:red">'.$matches[2].'</strong>';
            } elseif (! empty($matches[3])) { // most important keywords
                return '<strong style="color:blue; text-transform: uppercase;">'.$matches[3].'</strong>';
            } elseif (! empty($matches[4])) { // other keywords
                return '<strong style="color:green">'.$matches[4].'</strong>';
            }
        }, $sql);

        $bindings = array_map(function ($binding) use ($pdo) {
            if (is_array($binding) === true) {
                $binding = implode(', ', array_map(function ($value) {
                    return is_string($value) === true ? htmlspecialchars('\''.$value.'\'', ENT_NOQUOTES, 'UTF-8') : $value;
                }, $binding));

                return htmlspecialchars('('.$binding.')', ENT_NOQUOTES, 'UTF-8');
            }

            if (is_string($binding) === true && (preg_match('#[^\x09\x0A\x0D\x20-\x7E\xA0-\x{10FFFF}]#u', $binding) || preg_last_error())) {
                return '<i title="Length '.strlen($binding).' bytes">&lt;binary&gt;</i>';
            }

            if (is_string($binding)) {
                $text = htmlspecialchars($pdo ? $pdo->quote($binding) : '\''.$binding.'\'', ENT_NOQUOTES, 'UTF-8');

                return '<span title="Length '.strlen($text).' characters">'.$text.'</span>';
            }

            if (is_resource($binding)) {
                $type = get_resource_type($binding);
                if ($type === 'stream') {
                    $info = stream_get_meta_data($binding);
                }

                return '<i'.(isset($info['uri']) ? ' title="'.htmlspecialchars($info['uri'], ENT_NOQUOTES, 'UTF-8').'"' : null)
                    .'>&lt;'.htmlspecialchars($type, ENT_NOQUOTES, 'UTF-8').' resource&gt;</i> ';
            }

            if ($binding instanceof DateTime) {
                return htmlspecialchars('\''.$binding->format('Y-m-d H:i:s').'\'', ENT_NOQUOTES, 'UTF-8');
            }

            return htmlspecialchars($binding, ENT_NOQUOTES, 'UTF-8');
        }, $bindings);

        $sql = str_replace(['%', '?'], ['%%', '%s'], $sql);

        return '<div><code>'.nl2br(trim(vsprintf($sql, $bindings))).'</code></div>';
    }

    /**
     * perform quer analysis hint.
     *
     * @param string $sql
     * @param string $version
     * @param float $driver
     * @return array
     */
    public static function performQueryAnalysis($sql, $version = null, $driver = null)
    {
        $hints = [];
        if (preg_match('/^\\s*SELECT\\s*`?[a-zA-Z0-9]*`?\\.?\\*/i', $sql)) {
            $hints[] = 'Use <code>SELECT *</code> only if you need all columns from table';
        }
        if (preg_match('/ORDER BY RAND()/i', $sql)) {
            $hints[] = '<code>ORDER BY RAND()</code> is slow, try to avoid if you can.
                You can <a href="http://stackoverflow.com/questions/2663710/how-does-mysqls-order-by-rand-work">read this</a>
                or <a href="http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function">this</a>';
        }
        if (strpos($sql, '!=') !== false) {
            $hints[] = 'The <code>!=</code> operator is not standard. Use the <code>&lt;&gt;</code> operator to test for inequality instead.';
        }
        if (stripos($sql, 'WHERE') === false) {
            $hints[] = 'The <code>SELECT</code> statement has no <code>WHERE</code> clause and could examine many more rows than intended';
        }
        if (preg_match('/LIMIT\\s/i', $sql) && stripos($sql, 'ORDER BY') === false) {
            $hints[] = '<code>LIMIT</code> without <code>ORDER BY</code> causes non-deterministic results, depending on the query execution plan';
        }
        if (preg_match('/LIKE\\s[\'"](%.*?)[\'"]/i', $sql, $matches)) {
            $hints[] = 'An argument has a leading wildcard character: <code>'.$matches[1].'</code>.
                The predicate with this argument is not sargable and cannot use an index if one exists.';
        }
        if ($version < 5.5 && $driver === 'mysql') {
            if (preg_match('/\\sIN\\s*\\(\\s*SELECT/i', $sql)) {
                $hints[] = '<code>IN()</code> and <code>NOT IN()</code> subqueries are poorly optimized in that MySQL version : '.$version.
                    '. MySQL executes the subquery as a dependent subquery for each row in the outer query';
            }
        }

        return $hints;
    }

    /**
     * subscribe.
     */
    protected function subscribe()
    {
        $events = $this->laravel['events'];
        if (version_compare($this->laravel->version(), 5.2, '>=') === true) {
            $events->listen('Illuminate\Database\Events\QueryExecuted', function ($event) {
                $this->logQuery(
                    $event->sql,
                    $event->bindings,
                    $event->time,
                    $event->connectionName,
                    $event->connection->getPdo()
                );
            });
        } else {
            $events->listen('illuminate.query', function ($sql, $bindings, $time, $connectionName) {
                $this->logQuery(
                    $sql,
                    $bindings,
                    $time,
                    $connectionName,
                    $this->laravel['db']->connection($connectionName)->getPdo()
                );
            });
        }
    }

    /**
     * logQuery.
     *
     * @param string $sql
     * @param array $bindings
     * @param int $time
     * @param string $name
     * @param PDO $pdo
     * @param string $driver
     * @return $this
     */
    public function logQuery($sql, $bindings = [], $time = 0, $name = null, PDO $pdo = null, $driver = 'mysql')
    {
        ++$this->counter;
        $this->totalTime += $time;
        $source = self::findSource();
        $editorLink = self::editorLink($source);
        $this->queries[] = [
            'sql' => $sql,
            'bindings' => $bindings,
            'time' => $time,
            'name' => $name,
            'pdo' => $pdo,
            'driver' => $driver,
            'source' => $source,
            'editorLink' => $editorLink,
            'formattedSql' => null,
            'fullSql' => null,
        ];

        return $this;
    }

    /**
     * getAttributes.
     *
     * @return array
     */
    protected function getAttributes()
    {
        $queries = [];
        foreach ($this->queries as $query) {
            $sql = $query['sql'];
            $bindings = $query['bindings'];
            $pdo = $query['pdo'];
            $driver = $query['driver'];
            $version = 0;

            $hightlight = static::hightlight($sql, $bindings, $pdo);
            $explains = [];
            $hints = [];
            if ($pdo instanceof PDO) {
                $driver = $this->getDatabaseDriver($pdo);
                if ($driver === 'mysql') {
                    $version = $this->getDatabaseVersion($pdo);
                    $explains = static::explain($pdo, $sql, $bindings);
                    $hints = static::performQueryAnalysis($sql, $version, $driver);
                }
            }

            $queries[] = array_merge($query, compact('hightlight', 'explains', 'hints', 'driver', 'version'));
        }

        return [
            'counter' => $this->counter,
            'totalTime' => $this->totalTime,
            'queries' => $queries,
        ];
    }

    /**
     * getDatabaseDriver.
     *
     * @param \PDO $pdo
     * @return string
     */
    protected function getDatabaseDriver(PDO $pdo)
    {
        try {
            $driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
        } catch (Exception $e) {
            $driver = null;
        }

        return $driver;
    }

    /**
     * getDatabaseVersion.
     *
     * @param \PDO $pdo
     * @return string
     */
    protected function getDatabaseVersion(PDO $pdo)
    {
        try {
            $version = $pdo->getAttribute(PDO::ATTR_SERVER_VERSION);
        } catch (Exception $e) {
            $version = 0;
        }

        return $version;
    }
}


1		<?php
2
3		namespace Recca0120\LaravelTracy\Panels;
4
5		use PDO;
6		use DateTime;
7		use Exception;
8
9		class DatabasePanel extends AbstractSubscriablePanel implements IAjaxPanel
10		{
11		/**
12		* $queries.
13		*
14		* @var array
15		*/
16		protected $queries = [];
17
18		/**
19		* $totalTime.
20		*
21		* @var float
22		*/
23		protected $totalTime = 0.0;
24
25		/**
26		* $counter.
27		*
28		* @var int
29		*/
30		protected $counter = 0;
31
32		/**
33		* KEYWORDS1.
34		*
35		* @var string
36		*/
37		const KEYWORDS1 = 'SELECT\|(?:ON\s+DUPLICATE\s+KEY)?UPDATE\|INSERT(?:\s+INTO)?\|REPLACE(?:\s+INTO)?\|DELETE\|CALL\|UNION\|FROM\|WHERE\|HAVING\|GROUP\s+BY\|ORDER\s+BY\|LIMIT\|OFFSET\|SET\|VALUES\|LEFT\s+JOIN\|INNER\s+JOIN\|TRUNCATE';
38
39		/**
40		* KEYWORDS2.
41		*
42		* @var string
43		*/
44		const KEYWORDS2 = 'ALL\|DISTINCT\|DISTINCTROW\|IGNORE\|AS\|USING\|ON\|AND\|OR\|IN\|IS\|NOT\|NULL\|[RI]?LIKE\|REGEXP\|TRUE\|FALSE';
45
46		/**
47		* explain sql.
48		*
49		* @param PDO $pdo
50		* @param string $sql
51		* @param array $bindings
52		* @return array
53		*/
54	1	public static function explain(PDO $pdo, $sql, $bindings = [])
55		{
56	1	$explains = [];
57	1	if (preg_match('#\s\(?\sSELECT\s#iA', $sql)) {
58	1	$statement = $pdo->prepare('EXPLAIN '.$sql);
59	1	$statement->execute($bindings);
60	1	$explains = $statement->fetchAll(PDO::FETCH_CLASS);
61		}
62
63	1	return $explains;
64		}
65
66		/**
67		* Returns syntax highlighted SQL command.
68		*
69		*
70		* @param string $sql
71		* @param array $bindings
72		* @param \PDO $pdo
73		* @return string
74		*/
75	4	public static function hightlight($sql, array $bindings = [], PDO $pdo = null)
76		{
77		// insert new lines
78	4	$sql = " $sql ";
79	4	$sql = preg_replace("#(?<=[\\s,(])(".static::KEYWORDS1.")(?=[\\s,)])#i", "\n\$1", $sql);
80
81		// reduce spaces
82	4	$sql = preg_replace('#[ \t]{2,}#', ' ', $sql);
83
84		// syntax highlight
85	4	$sql = htmlspecialchars($sql, ENT_IGNORE, 'UTF-8');
86		$sql = preg_replace_callback("#(/\\.+?\\/)\|(\\\\.+?\\\\)\|(?<=[\\s,(])(".static::KEYWORDS1.")(?=[\\s,)])\|(?<=[\\s,(=])(".static::KEYWORDS2.")(?=[\\s,)=])#is", function ($matches) {
87	4	if (! empty($matches[1])) { // comment
88	1	return '<em style="color:gray">'.$matches[1].'</em>';
89	4	} elseif (! empty($matches[2])) { // error
90	1	return '<strong style="color:red">'.$matches[2].'</strong>';
91	4	} elseif (! empty($matches[3])) { // most important keywords
92	4	return '<strong style="color:blue; text-transform: uppercase;">'.$matches[3].'</strong>';
93	1	} elseif (! empty($matches[4])) { // other keywords
94	1	return '<strong style="color:green">'.$matches[4].'</strong>';
95		}
96	4	}, $sql);
97
98		$bindings = array_map(function ($binding) use ($pdo) {
99	3	if (is_array($binding) === true) {
100		$binding = implode(', ', array_map(function ($value) {
101	1	return is_string($value) === true ? htmlspecialchars('\''.$value.'\'', ENT_NOQUOTES, 'UTF-8') : $value;
102	1	}, $binding));
103
104	1	return htmlspecialchars('('.$binding.')', ENT_NOQUOTES, 'UTF-8');
105		}
106
107	3	if (is_string($binding) === true && (preg_match('#[^\x09\x0A\x0D\x20-\x7E\xA0-\x{10FFFF}]#u', $binding) \|\| preg_last_error())) {
108		return '<i title="Length '.strlen($binding).' bytes"><binary></i>';
109		}
110
111	3	if (is_string($binding)) {
112	3	$text = htmlspecialchars($pdo ? $pdo->quote($binding) : '\''.$binding.'\'', ENT_NOQUOTES, 'UTF-8');
113
114	3	return '<span title="Length '.strlen($text).' characters">'.$text.'</span>';
115		}
116
117	1	if (is_resource($binding)) {
118		$type = get_resource_type($binding);
119		if ($type === 'stream') {
120		$info = stream_get_meta_data($binding);
121		}
122
123		return '<i'.(isset($info['uri']) ? ' title="'.htmlspecialchars($info['uri'], ENT_NOQUOTES, 'UTF-8').'"' : null)
124		.'><'.htmlspecialchars($type, ENT_NOQUOTES, 'UTF-8').' resource></i> ';
125		}
126
127	1	if ($binding instanceof DateTime) {
128	1	return htmlspecialchars('\''.$binding->format('Y-m-d H:i:s').'\'', ENT_NOQUOTES, 'UTF-8');
129		}
130
131		return htmlspecialchars($binding, ENT_NOQUOTES, 'UTF-8');
132	4	}, $bindings);
133
134	4	$sql = str_replace(['%', '?'], ['%%', '%s'], $sql);
135
136	4	return '<div><code>'.nl2br(trim(vsprintf($sql, $bindings))).'</code></div>';
137		}
138
139		/**
140		* perform quer analysis hint.
141		*
142		* @param string $sql
143		* @param string $version
144		* @param float $driver
145		* @return array
146		*/
147	2	public static function performQueryAnalysis($sql, $version = null, $driver = null)
148		{
149	2	$hints = [];
150	2	if (preg_match('/^\\sSELECT\\s`?[a-zA-Z0-9]`?\\.?\\/i', $sql)) {
151	2	$hints[] = 'Use <code>SELECT *</code> only if you need all columns from table';
152		}
153	2	if (preg_match('/ORDER BY RAND()/i', $sql)) {
154	1	$hints[] = '<code>ORDER BY RAND()</code> is slow, try to avoid if you can.
155		You can <a href="http://stackoverflow.com/questions/2663710/how-does-mysqls-order-by-rand-work">read this</a>
156		or <a href="http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function">this</a>';
157		}
158	2	if (strpos($sql, '!=') !== false) {
159	1	$hints[] = 'The <code>!=</code> operator is not standard. Use the <code><></code> operator to test for inequality instead.';
160		}
161	2	if (stripos($sql, 'WHERE') === false) {
162	1	$hints[] = 'The <code>SELECT</code> statement has no <code>WHERE</code> clause and could examine many more rows than intended';
163		}
164	2	if (preg_match('/LIMIT\\s/i', $sql) && stripos($sql, 'ORDER BY') === false) {
165	1	$hints[] = '<code>LIMIT</code> without <code>ORDER BY</code> causes non-deterministic results, depending on the query execution plan';
166		}
167	2	if (preg_match('/LIKE\\s[\'"](%.*?)[\'"]/i', $sql, $matches)) {
168	1	$hints[] = 'An argument has a leading wildcard character: <code>'.$matches[1].'</code>.
169	1	The predicate with this argument is not sargable and cannot use an index if one exists.';
170		}
171	2	if ($version < 5.5 && $driver === 'mysql') {
172	2	if (preg_match('/\\sIN\\s\\(\\sSELECT/i', $sql)) {
173	1	$hints[] = '<code>IN()</code> and <code>NOT IN()</code> subqueries are poorly optimized in that MySQL version : '.$version.
174	1	'. MySQL executes the subquery as a dependent subquery for each row in the outer query';
175		}
176		}
177
178	2	return $hints;
179		}
180
181		/**
182		* subscribe.
183		*/
184	2	protected function subscribe()
185		{
186	2	$events = $this->laravel['events'];
187	2	if (version_compare($this->laravel->version(), 5.2, '>=') === true) {
188		$events->listen('Illuminate\Database\Events\QueryExecuted', function ($event) {
189	1	$this->logQuery(
190	1	$event->sql,
191	1	$event->bindings,
192	1	$event->time,
193	1	$event->connectionName,
194	1	$event->connection->getPdo()
195		);
196	1	});
197		} else {
198	1	$events->listen('illuminate.query', function ($sql, $bindings, $time, $connectionName) {
199	1	$this->logQuery(
200		$sql,
201		$bindings,
202		$time,
203		$connectionName,
204	1	$this->laravel['db']->connection($connectionName)->getPdo()
205		);
206	1	});
207		}
208	2	}
209
210		/**
211		* logQuery.
212		*
213		* @param string $sql
214		* @param array $bindings
215		* @param int $time
216		* @param string $name
217		* @param PDO $pdo
218		* @param string $driver
219		* @return $this
220		*/
221	3	public function logQuery($sql, $bindings = [], $time = 0, $name = null, PDO $pdo = null, $driver = 'mysql')
222		{
223	3	++$this->counter;
224	3	$this->totalTime += $time;
225	3	$source = self::findSource();
226	3	$editorLink = self::editorLink($source);
227	3	$this->queries[] = [
228	3	'sql' => $sql,
229	3	'bindings' => $bindings,
230	3	'time' => $time,
231	3	'name' => $name,
232	3	'pdo' => $pdo,
233	3	'driver' => $driver,
234	3	'source' => $source,
235	3	'editorLink' => $editorLink,
236		'formattedSql' => null,
237		'fullSql' => null,
238		];
239
240	3	return $this;
241		}
242
243		/**
244		* getAttributes.
245		*
246		* @return array
247		*/
248	3	protected function getAttributes()
249		{
250	3	$queries = [];
251	3	foreach ($this->queries as $query) {
252	3	$sql = $query['sql'];
253	3	$bindings = $query['bindings'];
254	3	$pdo = $query['pdo'];
255	3	$driver = $query['driver'];
256	3	$version = 0;
257
258	3	$hightlight = static::hightlight($sql, $bindings, $pdo);
259	3	$explains = [];
260	3	$hints = [];
261	3	if ($pdo instanceof PDO) {
262	2	$driver = $this->getDatabaseDriver($pdo);
263	2	if ($driver === 'mysql') {
264	1	$version = $this->getDatabaseVersion($pdo);
265	1	$explains = static::explain($pdo, $sql, $bindings);
266	1	$hints = static::performQueryAnalysis($sql, $version, $driver);
267		}
268		}
269
270	3	$queries[] = array_merge($query, compact('hightlight', 'explains', 'hints', 'driver', 'version'));
271		}
272
273		return [
274	3	'counter' => $this->counter,
275	3	'totalTime' => $this->totalTime,
276	3	'queries' => $queries,
277		];
278		}
279
280		/**
281		* getDatabaseDriver.
282		*
283		* @param \PDO $pdo
284		* @return string
285		*/
286	2	protected function getDatabaseDriver(PDO $pdo)
287		{
288		try {
289	2	$driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
290	1	} catch (Exception $e) {
291	1	$driver = null;
292		}
293
294	2	return $driver;
295		}
296
297		/**
298		* getDatabaseVersion.
299		*
300		* @param \PDO $pdo
301		* @return string
302		*/
303	1	protected function getDatabaseVersion(PDO $pdo)
304		{
305		try {
306	1	$version = $pdo->getAttribute(PDO::ATTR_SERVER_VERSION);
307	1	} catch (Exception $e) {
308	1	$version = 0;
309		}
310
311	1	return $version;
312		}
313		}
314

recca0120 / laravel-tracy

Push — master ( b79de5...a8cca6 )

DatabasePanel::hightlight() C

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like