Query::getWhereCollateClause() - Code Metrics - Inspection of "Moved methods between interfaces." - lagdo/dbadmin-driver - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — main ( e95517...d1bd15 )

by Thierry

created 2024-01-25 20:00 UTC

Query::getWhereCollateClause() A

↳ Parent: Query

Complexity

Conditions	4
Paths	6

Size

Total Lines	7
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	4
eloc	4
nc	6
nop	3
dl	0
loc	7
rs	10
c	0
b	0
f	0

<?php

namespace Lagdo\DbAdmin\Driver\Db;

use Exception;
use Lagdo\DbAdmin\Driver\Entity\TableFieldEntity;
use Lagdo\DbAdmin\Driver\Entity\TableSelectEntity;
use Lagdo\DbAdmin\Driver\Entity\TableEntity;
use Lagdo\DbAdmin\Driver\DriverInterface;
use Lagdo\DbAdmin\Driver\UtilInterface;
use Lagdo\DbAdmin\Driver\TranslatorInterface;

use function implode;
use function array_keys;
use function preg_match;
use function preg_replace;
use function substr;
use function strlen;

abstract class Query implements QueryInterface
{
    /**
     * @var DriverInterface
     */
    protected $driver;

    /**
     * @var UtilInterface
     */
    protected $util;

    /**
     * @var TranslatorInterface
     */
    protected $trans;

    /**
     * The constructor
     *
     * @param DriverInterface $driver
     * @param UtilInterface $util
     * @param TranslatorInterface $trans
     */
    public function __construct(DriverInterface $driver, UtilInterface $util, TranslatorInterface $trans)
    {
        $this->driver = $driver;
        $this->util = $util;
        $this->trans = $trans;
    }

    /**
     * @inheritDoc
     */
    public function schema()
    {
        return '';
    }

    /**
     * Formulate SQL modification query with limit 1
     *
     * @param string $table
     * @param string $query Everything after UPDATE or DELETE
     * @param string $where
     *
     * @return string
     */
    abstract protected function limitToOne(string $table, string $query, string $where);

    /**
     * @inheritDoc
     */
    public function select(string $table, array $select, array $where,
        array $group, array $order = [], int $limit = 1, int $page = 0)
    {
        $entity = new TableSelectEntity($table, $select, $where, $group, $order, $limit, $page);
        $query = $this->driver->buildSelectQuery($entity);
        // $this->start = intval(microtime(true));
        return $this->driver->execute($query);
    }

    /**
     * @inheritDoc
     */
    public function insert(string $table, array $values)
    {
        $table = $this->driver->table($table);
        if (empty($values)) {
            $result = $this->driver->execute("INSERT INTO $table DEFAULT VALUES");
            return $result !== false;
        }
        $result = $this->driver->execute("INSERT INTO $table (" .
            implode(', ', array_keys($values)) . ') VALUES (' . implode(', ', $values) . ')');
        return $result !== false;
    }

    /**
     * @inheritDoc
     */
    public function update(string $table, array $values, string $queryWhere, int $limit = 0)
    {
        $assignments = [];
        foreach ($values as $name => $value) {
            $assignments[] = "$name = $value";
        }
        $query = $this->driver->table($table) . ' SET ' . implode(', ', $assignments);
        if (!$limit) {
            $result = $this->driver->execute('UPDATE ' . $query . $queryWhere);
            return $result !== false;
        }
        $result = $this->driver->execute('UPDATE' . $this->limitToOne($table, $query, $queryWhere));
        return $result !== false;
    }

    /**
     * @inheritDoc
     */
    public function delete(string $table, string $queryWhere, int $limit = 0)
    {
        $query = 'FROM ' . $this->driver->table($table);
        if (!$limit) {
            $result = $this->driver->execute("DELETE $query $queryWhere");
            return $result !== false;
        }
        $result = $this->driver->execute('DELETE' . $this->limitToOne($table, $query, $queryWhere));
        return $result !== false;
    }

    /**
     * @inheritDoc
     */
    public function explain(ConnectionInterface $connection, string $query)
    {
        return false;
    }

    /**
     * @inheritDoc
     */
    public function slowQuery(string $query, int $timeout)
    {
        return null;
    }

    /**
     * @inheritDoc
     */
    public function countRows(TableEntity $tableStatus, array $where)
    {
        return null;
    }

    /**
     * @inheritDoc
     */
    public function convertSearch(string $idf, array $val, TableFieldEntity $field)
    {
        return $idf;
    }

    /**
     * @inheritDoc
     */
    public function view(string $name)
    {
        return [];
    }

    /**
     * Remove current user definer from SQL command
     *
     * @param string $query
     *
     * @return string
     */
    public function removeDefiner(string $query): string
    {
        return preg_replace('~^([A-Z =]+) DEFINER=`' .
            preg_replace('~@(.*)~', '`@`(%|\1)', $this->user()) .
            '`~', '\1', $query); //! proper escaping of user
    }

    /**
     * Query printed after execution in the message
     *
     * @param string $query Executed query
     *
     * @return string
     */
    private function queryToLog(string $query/*, string $time*/): string
    {
        if (strlen($query) > 1e6) {
            // [\x80-\xFF] - valid UTF-8, \n - can end by one-line comment
            $query = preg_replace('~[\x80-\xFF]+$~', '', substr($query, 0, 1e6)) . "\n…";
        }
        return $query;
    }

    /**
     * Execute query
     *
     * @param string $query
     * @param bool $execute
     * @param bool $failed
     *
     * @return bool
     * @throws Exception
     */
    public function executeQuery(string $query, bool $execute = true,
        bool $failed = false/*, string $time = ''*/): bool
    {
        if ($execute) {
            // $start = microtime(true);
            $failed = !$this->driver->execute($query);
            // $time = $this->trans->formatTime($start);
        }
        if ($failed) {
            $sql = '';
            if ($query) {
                $sql = $this->queryToLog($query/*, $time*/);
            }
            throw new Exception($this->driver->error() . $sql);
        }
        return true;
    }

    /**
     * @param TableFieldEntity $field
     * @param string $column
     * @param string $value
     *
     * @return string
     */
    private function getWhereColumnClause(TableFieldEntity $field, string $column, string $value): string
    {
        $bUseSqlLike = $this->driver->jush() === 'sql' && is_numeric($value) && preg_match('~\.~', $value);
        return $column . ($bUseSqlLike ?
            // LIKE because of floats but slow with ints
            " LIKE " . $this->driver->quote($value) :
            ($this->driver->jush() === 'mssql' ?
                // LIKE because of text
                " LIKE " . $this->driver->quote(preg_replace('~[_%[]~', '[\0]', $value)) :
                //! enum and set
                " = " . $this->driver->unconvertField($field, $this->driver->quote($value))));
    }

    /**
     * @param TableFieldEntity $field
     * @param string $column
     * @param string $value
     *
     * @return string
     */
    private function getWhereCollateClause(TableFieldEntity $field, string $column, string $value): string
    {
        $bCollate = $this->driver->jush() === 'sql' &&
            preg_match('~char|text~', $field->type) && preg_match("~[^ -@]~", $value);
        return !$bCollate ? '' :
            // not just [a-z] to catch non-ASCII characters
            "$column = " . $this->driver->quote($value) . " COLLATE " . $this->driver->charset() . "_bin";
    }

    /**
     * Create SQL condition from parsed query string
     *
     * @param array $where Parsed query string
     * @param array<TableFieldEntity> $fields
     *
     * @return string
     */
    public function where(array $where, array $fields = []): string
    {
        $clauses = [];
        $wheres = $where["where"] ?? [];
        foreach ((array) $wheres as $key => $value) {
            $key = $this->util->bracketEscape($key, 1); // 1 - back
            $column = $this->util->escapeKey($key);
            $clauses[] = $this->getWhereColumnClause($fields[$key], $column, $value);
            if (($clause = $this->getWhereCollateClause($fields[$key], $column, $value))) {
                $clauses[] = $clause;
            }
        }
        $nulls = $where["null"] ?? [];
        foreach ((array) $nulls as $key) {
            $clauses[] = $this->util->escapeKey($key) . " IS NULL";
        }
        return implode(" AND ", $clauses);
    }
}


1			<?php
2
3			namespace Lagdo\DbAdmin\Driver\Db;
4
5			use Exception;
6			use Lagdo\DbAdmin\Driver\Entity\TableFieldEntity;
7			use Lagdo\DbAdmin\Driver\Entity\TableSelectEntity;
8			use Lagdo\DbAdmin\Driver\Entity\TableEntity;
9			use Lagdo\DbAdmin\Driver\DriverInterface;
10			use Lagdo\DbAdmin\Driver\UtilInterface;
11			use Lagdo\DbAdmin\Driver\TranslatorInterface;
12
13			use function implode;
14			use function array_keys;
15			use function preg_match;
16			use function preg_replace;
17			use function substr;
18			use function strlen;
19
20			abstract class Query implements QueryInterface
21			{
22			/**
23			* @var DriverInterface
24			*/
25			protected $driver;
26
27			/**
28			* @var UtilInterface
29			*/
30			protected $util;
31
32			/**
33			* @var TranslatorInterface
34			*/
35			protected $trans;
36
37			/**
38			* The constructor
39			*
40			* @param DriverInterface $driver
41			* @param UtilInterface $util
42			* @param TranslatorInterface $trans
43			*/
44			public function __construct(DriverInterface $driver, UtilInterface $util, TranslatorInterface $trans)
45			{
46			$this->driver = $driver;
47			$this->util = $util;
48			$this->trans = $trans;
49			}
50
51			/**
52			* @inheritDoc
53			*/
54			public function schema()
55			{
56			return '';
57			}
58
59			/**
60			* Formulate SQL modification query with limit 1
61			*
62			* @param string $table
63			* @param string $query Everything after UPDATE or DELETE
64			* @param string $where
65			*
66			* @return string
67			*/
68			abstract protected function limitToOne(string $table, string $query, string $where);
69
70			/**
71			* @inheritDoc
72			*/
73			public function select(string $table, array $select, array $where,
74			array $group, array $order = [], int $limit = 1, int $page = 0)
75			{
76			$entity = new TableSelectEntity($table, $select, $where, $group, $order, $limit, $page);
77			$query = $this->driver->buildSelectQuery($entity);
78			// $this->start = intval(microtime(true));
79			return $this->driver->execute($query);
80			}
81
82			/**
83			* @inheritDoc
84			*/
85			public function insert(string $table, array $values)
86			{
87			$table = $this->driver->table($table);
88			if (empty($values)) {
89			$result = $this->driver->execute("INSERT INTO $table DEFAULT VALUES");
90			return $result !== false;
91			}
92			$result = $this->driver->execute("INSERT INTO $table (" .
93			implode(', ', array_keys($values)) . ') VALUES (' . implode(', ', $values) . ')');
94			return $result !== false;
95			}
96
97			/**
98			* @inheritDoc
99			*/
100			public function update(string $table, array $values, string $queryWhere, int $limit = 0)
101			{
102			$assignments = [];
103			foreach ($values as $name => $value) {
104			$assignments[] = "$name = $value";
105			}
106			$query = $this->driver->table($table) . ' SET ' . implode(', ', $assignments);
107			if (!$limit) {
108			$result = $this->driver->execute('UPDATE ' . $query . $queryWhere);
109			return $result !== false;
110			}
111			$result = $this->driver->execute('UPDATE' . $this->limitToOne($table, $query, $queryWhere));
112			return $result !== false;
113			}
114
115			/**
116			* @inheritDoc
117			*/
118			public function delete(string $table, string $queryWhere, int $limit = 0)
119			{
120			$query = 'FROM ' . $this->driver->table($table);
121			if (!$limit) {
122			$result = $this->driver->execute("DELETE $query $queryWhere");
123			return $result !== false;
124			}
125			$result = $this->driver->execute('DELETE' . $this->limitToOne($table, $query, $queryWhere));
126			return $result !== false;
127			}
128
129			/**
130			* @inheritDoc
131			*/
132			public function explain(ConnectionInterface $connection, string $query)
133			{
134			return false;
135			}
136
137			/**
138			* @inheritDoc
139			*/
140			public function slowQuery(string $query, int $timeout)
141			{
142			return null;
143			}
144
145			/**
146			* @inheritDoc
147			*/
148			public function countRows(TableEntity $tableStatus, array $where)
149			{
150			return null;
151			}
152
153			/**
154			* @inheritDoc
155			*/
156			public function convertSearch(string $idf, array $val, TableFieldEntity $field)
157			{
158			return $idf;
159			}
160
161			/**
162			* @inheritDoc
163			*/
164			public function view(string $name)
165			{
166			return [];
167			}
168
169			/**
170			* Remove current user definer from SQL command
171			*
172			* @param string $query
173			*
174			* @return string
175			*/
176			public function removeDefiner(string $query): string
177			{
178			return preg_replace('~^([A-Z =]+) DEFINER=`' .
179			preg_replace('~@(.*)~', '`@`(%\|\1)', $this->user()) .
180			'`~', '\1', $query); //! proper escaping of user
181			}
182
183			/**
184			* Query printed after execution in the message
185			*
186			* @param string $query Executed query
187			*
188			* @return string
189			*/
190			private function queryToLog(string $query/, string $time/): string
191			{
192			if (strlen($query) > 1e6) {
193			// [\x80-\xFF] - valid UTF-8, \n - can end by one-line comment
194			$query = preg_replace('~[\x80-\xFF]+$~', '', substr($query, 0, 1e6)) . "\n…";
195			}
196			return $query;
197			}
198
199			/**
200			* Execute query
201			*
202			* @param string $query
203			* @param bool $execute
204			* @param bool $failed
205			*
206			* @return bool
207			* @throws Exception
208			*/
209			public function executeQuery(string $query, bool $execute = true,
210			bool $failed = false/, string $time = ''/): bool
211			{
212			if ($execute) {
213			// $start = microtime(true);
214			$failed = !$this->driver->execute($query);
215			// $time = $this->trans->formatTime($start);
216			}
217			if ($failed) {
218			$sql = '';
219			if ($query) {
220			$sql = $this->queryToLog($query/, $time/);
221			}
222			throw new Exception($this->driver->error() . $sql);
223			}
224			return true;
225			}
226
227			/**
228			* @param TableFieldEntity $field
229			* @param string $column
230			* @param string $value
231			*
232			* @return string
233			*/
234			private function getWhereColumnClause(TableFieldEntity $field, string $column, string $value): string
235			{
236			$bUseSqlLike = $this->driver->jush() === 'sql' && is_numeric($value) && preg_match('~\.~', $value);
237			return $column . ($bUseSqlLike ?
238			// LIKE because of floats but slow with ints
239			" LIKE " . $this->driver->quote($value) :
240			($this->driver->jush() === 'mssql' ?
241			// LIKE because of text
242			" LIKE " . $this->driver->quote(preg_replace('~[_%[]~', '[\0]', $value)) :
243			//! enum and set
244			" = " . $this->driver->unconvertField($field, $this->driver->quote($value))));
245			}
246
247			/**
248			* @param TableFieldEntity $field
249			* @param string $column
250			* @param string $value
251			*
252			* @return string
253			*/
254			private function getWhereCollateClause(TableFieldEntity $field, string $column, string $value): string
255			{
256			$bCollate = $this->driver->jush() === 'sql' &&
257			preg_match('~char\|text~', $field->type) && preg_match("~[^ -@]~", $value);
258			return !$bCollate ? '' :
259			// not just [a-z] to catch non-ASCII characters
260			"$column = " . $this->driver->quote($value) . " COLLATE " . $this->driver->charset() . "_bin";
261			}
262
263			/**
264			* Create SQL condition from parsed query string
265			*
266			* @param array $where Parsed query string
267			* @param array<TableFieldEntity> $fields
268			*
269			* @return string
270			*/
271			public function where(array $where, array $fields = []): string
272			{
273			$clauses = [];
274			$wheres = $where["where"] ?? [];
275			foreach ((array) $wheres as $key => $value) {
276			$key = $this->util->bracketEscape($key, 1); // 1 - back
277			$column = $this->util->escapeKey($key);
278			$clauses[] = $this->getWhereColumnClause($fields[$key], $column, $value);
279			if (($clause = $this->getWhereCollateClause($fields[$key], $column, $value))) {
280			$clauses[] = $clause;
281			}
282			}
283			$nulls = $where["null"] ?? [];
284			foreach ((array) $nulls as $key) {
285			$clauses[] = $this->util->escapeKey($key) . " IS NULL";
286			}
287			return implode(" AND ", $clauses);
288			}
289			}
290

lagdo / dbadmin-driver

Push — main ( e95517...d1bd15 )

Query::getWhereCollateClause() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like