Query::getWhereCollateClause() - Code Metrics - lagdo/dbadmin-driver - Measure and Improve Code Quality continuously with Scrutinizer

Query::getWhereCollateClause() A
last analyzed 2025-01-22 21:08 UTC

↳ Parent: Query

Complexity

Conditions	4
Paths	6

Size

Total Lines	7
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?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\Utils\Utils;

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 Utils
     */
    protected $utils;

    /**
     * The constructor
     *
     * @param DriverInterface $driver
     * @param Utils $utils
     */
    public function __construct(DriverInterface $driver, Utils $utils)
    {
        $this->driver = $driver;
        $this->utils = $utils;
    }

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

lagdo / dbadmin-driver

Query::getWhereCollateClause() A last analyzed 2025-01-22 21:08 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

Query::getWhereCollateClause() A
last analyzed 2025-01-22 21:08 UTC