ConnectorAdapter::getSelectQueryForExpression() - Code Metrics - Inspection of "Add more control over the SQL queries (order, grou..." - Gixx/WebHemi - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 37b412...8220b9 )

by Gabor

created 2017-03-29 06:42 UTC

ConnectorAdapter::getSelectQueryForExpression() B

↳ Parent: ConnectorAdapter

Complexity

Conditions	5
Paths	12

Size

Total Lines	34
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	16
CRAP Score	5

Importance

Changes

Metric	Value
dl	0
loc	34
ccs	16
cts	16
cp	1
rs	8.439
c	0
b	0
f	0
cc	5
eloc	20
nc	12
nop	3
crap	5

<?php
/**
 * WebHemi.
 *
 * PHP version 7.1
 *
 * @copyright 2012 - 2017 Gixx-web (http://www.gixx-web.com)
 * @license   https://opensource.org/licenses/MIT The MIT License (MIT)
 *
 * @link      http://www.gixx-web.com
 */
declare(strict_types = 1);

namespace WebHemi\Data\Connector\PDO\MySQL;

use InvalidArgumentException;
use PDO;
use PDOStatement;
use RuntimeException;
use WebHemi\Data\ConnectorInterface;
use WebHemi\Data\DriverInterface;

/**
 * Class ConnectorAdapter.
 */
class ConnectorAdapter implements ConnectorInterface
{
    /** @var PDO */
    protected $dataDriver;
    /** @var string */
    protected $dataGroup = null;
    /** @var string */
    protected $idKey = null;

    /**
     * ConnectorAdapter constructor.
     *
     * @param DriverInterface $dataDriver
     * @throws InvalidArgumentException
     */
    public function __construct(DriverInterface $dataDriver)
    {
        if (!$dataDriver instanceof DriverAdapter) {
            $type = gettype($dataDriver);

            if ($type == 'object') {
                $type = get_class($dataDriver);
            }

            $message = sprintf(
                'Can\'t create %s instance. The parameter must be an instance of MySQLDriver, %s given.',
                __CLASS__,
                $type
            );

            throw new InvalidArgumentException($message, 1001);
        }

        $this->dataDriver = $dataDriver;
    }

    /**
     * Returns the Data Storage instance.
     *
     * @return DriverInterface
     */
    public function getDataDriver() : DriverInterface
    {
        return $this->dataDriver;
    }

    /**
     * Set adapter data group. For Databases this can be the Tables.
     *
     * @param string $dataGroup
     * @return ConnectorInterface
     */
    public function setDataGroup(string $dataGroup) : ConnectorInterface
    {
        $this->dataGroup = $dataGroup;

        return $this;
    }

    /**
     * Set adapter ID key. For Databases this can be the Primary key. Only simple key is allowed.
     *
     * @param string $idKey
     * @return ConnectorInterface
     */
    public function setIdKey(string $idKey) : ConnectorInterface
    {
        $this->idKey = $idKey;

        return $this;
    }

    /**
     * Get exactly one "row" of data according to the expression.
     *
     * @param int $identifier
     * @return array
     *
     * @codeCoverageIgnore Don't test external library.
     */
    public function getData(int $identifier) : array
    {
        $queryBinds = [];

        $query = $this->getSelectQueryForExpression(
            [$this->idKey => $identifier],
            $queryBinds,
            [self::OPTION_LIMIT => 1, self::OPTION_OFFSET => 0]
        );
        $statement = $this->dataDriver->prepare($query);
        $this->bindValuesToStatement($statement, $queryBinds);
        $statement->execute();

        $data = $statement->fetch(PDO::FETCH_ASSOC);

        return $data ? $data : [];
    }

    /**
     * Get a set of data according to the expression and the chunk.
     *
     * @param array $expression
     * @param array $options
     * @return array
     *
     * @codeCoverageIgnore Don't test external library.
     */
    public function getDataSet(array $expression, array $options = []) : array
    {
        $queryBinds = [];

        $query = $this->getSelectQueryForExpression($expression, $queryBinds, $options);
        $statement = $this->dataDriver->prepare($query);

        $this->bindValuesToStatement($statement, $queryBinds);
        $statement->execute();

        $data = $statement->fetchAll(PDO::FETCH_ASSOC);

        return $data ? $data : [];
    }

    /**
     * Get the number of matched data in the set according to the expression.
     *
     * @param array $expression
     * @return int
     *
     * @codeCoverageIgnore Don't test external library.
     */
    public function getDataCardinality(array $expression) : int
    {
        $queryBinds = [];

        $query = $this->getSelectQueryForExpression($expression, $queryBinds, []);
        $statement = $this->dataDriver->prepare($query);
        $this->bindValuesToStatement($statement, $queryBinds);
        $statement->execute();

        return $statement->rowCount();
    }

    /**
     * Builds SQL query from the expression.
     *
     * @param array $expression
     * @param array $queryBinds
     * @param array $options
     * @return string
     */
    protected function getSelectQueryForExpression(
        array $expression,
        array&$queryBinds,
        array $options = []
    ) : string {
        $query = "SELECT * FROM {$this->dataGroup}";

        // Prepare WHERE expression.
        if (!empty($expression)) {
            $query .= $this->getWhereExpression($expression, $queryBinds);
        }

        $group = $this->getQueryGroup($options);
        $having = $this->getQueryHaving($options);

        if (!empty($group)) {
            $query .= " GROUP BY {$group}";

            if (!empty($having)) {
                $query .= " HAVING {$having}";
            }
        }

        $query .= " ORDER BY {$this->getQueryOrder($options)}";

        $limit = $this->getQueryLimit($options);

        if ($limit > 0) {
            $query .= " LIMIT {$limit}";
            $query .= " OFFSET {$this->getQueryOffset($options)}";
        }

        return $query;
    }

    /**
     * Gets the GROUP BY expression.
     *
     * @param array $options
     * @return string
     */
    protected function getQueryGroup(array $options) : string
    {
        return $options[self::OPTION_GROUP] ?? '';
    }

    /**
     * Gets the HAVING expression only when the GROUP BY option exists.
     *
     * @param array $options
     * @return string
     */
    protected function getQueryHaving(array $options) : string
    {
        return isset($options[self::OPTION_GROUP]) ? $options[self::OPTION_HAVING] : '';
    }

    /**
     * Gets the ORDER BY expression. The default value is the primary key.
     *
     * @param array $options
     * @return string
     */
    protected function getQueryOrder(array $options) : string
    {
        return $options[self::OPTION_ORDER] ?? $this->idKey;
    }

    /**
     * Gets the LIMIT expression.
     *
     * @param array $options
     * @return int
     */
    protected function getQueryLimit(array $options) : int
    {
        return $options[self::OPTION_LIMIT] ?? 0;
    }

    /**
     * Gets the OFFSET expression.
     *
     * @param array $options
     * @return int
     */
    protected function getQueryOffset(array $options) : int
    {
        return $options[self::OPTION_OFFSET] ?? 0;
    }

    /**
     * Creates a WHERE expression for the SQL query.
     *
     * @param array $expression
     * @param array $queryBinds
     * @return string
     */
    protected function getWhereExpression(array $expression, array&$queryBinds) : string
    {
        $whereExpression = '';
        $queryParams = [];

        foreach ($expression as $column => $value) {
            $this->setParamsAndBinds($column, $value, $queryParams, $queryBinds);
        }

        if (!empty($queryParams)) {
            $whereExpression = ' WHERE '.implode(' AND ', $queryParams);
        }

        return $whereExpression;
    }

    /**
     * Set the query params and quaery bindings according to the `column` and `value`.
     *
     * @param string $column
     * @param mixed  $value
     * @param array  $queryParams
     * @param array  $queryBinds
     */
    protected function setParamsAndBinds(string $column, $value, array&$queryParams, array&$queryBinds) : void
    {
        if (is_array($value)) {
            $queryParams[] = $this->getInColumnCondition($column, count($value));
            $queryBinds = array_merge($queryBinds, $value);
        } elseif (strpos($column, ' LIKE') !== false || (is_string($value) && strpos($value, '%') !== false)) {
            $queryParams[] = $this->getLikeColumnCondition($column);
            $queryBinds[] = $value;
        } else {
            $queryParams[] = $this->getSimpleColumnCondition($column);
            $queryBinds[] = $value;
        }
    }

    /**
     * Gets a simple condition for the column.
     *
     * @param string $column
     * @return string 'my_column = ?'
     */
    protected function getSimpleColumnCondition(string $column) : string
    {
        return strpos($column, '?') === false ? "{$column} = ?" : $column;
    }

    /**
     * Gets a 'LIKE' condition for the column.
     *
     * Allows special cases:
     * @example  ['my_column LIKE ?' => 'some value%']
     * @example  ['my_column LIKE' => 'some value%']
     * @example  ['my_column' => 'some value%']
     *
     * @param string $column
     * @return string 'my_column LIKE ?'
     */
    protected function getLikeColumnCondition(string $column) : string
    {
        list($columnNameOnly) = explode(' ', $column);

        return $columnNameOnly.' LIKE ?';
    }

    /**
     * Gets an 'IN' condition for the column.
     *
     * Allows special cases:
     * @example  ['my_column IN (?)' => [1,2,3]]
     * @example  ['my_column IN ?' => [1,2,3]]
     * @example  ['my_column IN' => [1,2,3]]
     * @example  ['my_column' => [1,2,3]]
     *
     * @param string $column
     * @param int    $parameterCount
     * @return string 'my_column IN (?,?,?)'
     */
    protected function getInColumnCondition(string $column, int $parameterCount = 1) : string
    {
        list($columnNameOnly) = explode(' ', $column);

        $inParameters = str_repeat('?,', $parameterCount - 1).'?';

        return $columnNameOnly.' IN ('.$inParameters.')';
    }

    /**
     * Insert or update entity in the storage.
     *
     * @param int   $identifier
     * @param array $data
     * @throws RuntimeException
     * @return int The ID of the saved entity in the storage
     *
     * @codeCoverageIgnore Don't test external library.
     */
    public function saveData(? int $identifier = null, array $data) : int
    {
        if (empty($identifier)) {
            $query = "INSERT INTO {$this->dataGroup}";
        } else {
            $query = "UPDATE {$this->dataGroup}";
        }

        $queryData = [];
        $queryBind = [];

        foreach ($data as $fieldName => $value) {
            $queryData[] = "{$fieldName} = ?";
            $queryBind[] = $value;
        }

        $query .= ' SET '.implode(', ', $queryData);

        if (!empty($identifier)) {
            $query .= " WHERE {$this->idKey} = ?";
            $queryBind[] = $identifier;
        }

        $statement = $this->dataDriver->prepare($query);
        if (!$statement) {
            throw new RuntimeException('Query error', 1002);
        }
        $this->bindValuesToStatement($statement, $queryBind);
        $statement->execute();

        return empty($identifier) ? (int) $this->dataDriver->lastInsertId() : $identifier;
    }

    /**
     * Binds values to the statement.
     *
     * @param PDOStatement $statement
     * @param array        $queryBind
     * @return void
     *
     * @codeCoverageIgnore Don't test external library.
     */
    protected function bindValuesToStatement(PDOStatement&$statement, array $queryBind) : void
    {
        foreach ($queryBind as $index => $data) {
            $paramType = PDO::PARAM_STR;

            if (is_null($data)) {
                $paramType = PDO::PARAM_NULL;
            } elseif (is_numeric($data)) {
                $paramType = PDO::PARAM_INT;
            }

            $statement->bindValue($index + 1, $data, $paramType);
        }
    }

    /**
     * Removes an entity from the storage.
     *
     * @param int $identifier
     * @return bool
     *
     * @codeCoverageIgnore Don't test external library.
     */
    public function deleteData(int $identifier) : bool
    {
        $statement = $this->dataDriver->prepare("DELETE FROM WHERE {$this->idKey} = ?");

        return $statement->execute([$identifier]);
    }
}


1		<?php
2		/**
3		* WebHemi.
4		*
5		* PHP version 7.1
6		*
7		* @copyright 2012 - 2017 Gixx-web (http://www.gixx-web.com)
8		* @license https://opensource.org/licenses/MIT The MIT License (MIT)
9		*
10		* @link http://www.gixx-web.com
11		*/
12		declare(strict_types = 1);
13
14		namespace WebHemi\Data\Connector\PDO\MySQL;
15
16		use InvalidArgumentException;
17		use PDO;
18		use PDOStatement;
19		use RuntimeException;
20		use WebHemi\Data\ConnectorInterface;
21		use WebHemi\Data\DriverInterface;
22
23		/**
24		* Class ConnectorAdapter.
25		*/
26		class ConnectorAdapter implements ConnectorInterface
27		{
28		/** @var PDO */
29		protected $dataDriver;
30		/** @var string */
31		protected $dataGroup = null;
32		/** @var string */
33		protected $idKey = null;
34
35		/**
36		* ConnectorAdapter constructor.
37		*
38		* @param DriverInterface $dataDriver
39		* @throws InvalidArgumentException
40		*/
41	16	public function __construct(DriverInterface $dataDriver)
42		{
43	16	if (!$dataDriver instanceof DriverAdapter) {
44	1	$type = gettype($dataDriver);
45
46	1	if ($type == 'object') {
47	1	$type = get_class($dataDriver);
48		}
49
50	1	$message = sprintf(
51	1	'Can\'t create %s instance. The parameter must be an instance of MySQLDriver, %s given.',
52	1	__CLASS__,
53		$type
54		);
55
56	1	throw new InvalidArgumentException($message, 1001);
57		}
58
59	16	$this->dataDriver = $dataDriver;
60	16	}
61
62		/**
63		* Returns the Data Storage instance.
64		*
65		* @return DriverInterface
66		*/
67	1	public function getDataDriver() : DriverInterface
68		{
69	1	return $this->dataDriver;
70		}
71
72		/**
73		* Set adapter data group. For Databases this can be the Tables.
74		*
75		* @param string $dataGroup
76		* @return ConnectorInterface
77		*/
78	11	public function setDataGroup(string $dataGroup) : ConnectorInterface
79		{
80	11	$this->dataGroup = $dataGroup;
81
82	11	return $this;
83		}
84
85		/**
86		* Set adapter ID key. For Databases this can be the Primary key. Only simple key is allowed.
87		*
88		* @param string $idKey
89		* @return ConnectorInterface
90		*/
91	11	public function setIdKey(string $idKey) : ConnectorInterface
92		{
93	11	$this->idKey = $idKey;
94
95	11	return $this;
96		}
97
98		/**
99		* Get exactly one "row" of data according to the expression.
100		*
101		* @param int $identifier
102		* @return array
103		*
104		* @codeCoverageIgnore Don't test external library.
105		*/
106		public function getData(int $identifier) : array
107		{
108		$queryBinds = [];
109
110		$query = $this->getSelectQueryForExpression(
111		[$this->idKey => $identifier],
112		$queryBinds,
113		[self::OPTION_LIMIT => 1, self::OPTION_OFFSET => 0]
114		);
115		$statement = $this->dataDriver->prepare($query);
116		$this->bindValuesToStatement($statement, $queryBinds);
117		$statement->execute();
118
119		$data = $statement->fetch(PDO::FETCH_ASSOC);
120
121		return $data ? $data : [];
122		}
123
124		/**
125		* Get a set of data according to the expression and the chunk.
126		*
127		* @param array $expression
128		* @param array $options
129		* @return array
130		*
131		* @codeCoverageIgnore Don't test external library.
132		*/
133		public function getDataSet(array $expression, array $options = []) : array
134		{
135		$queryBinds = [];
136
137		$query = $this->getSelectQueryForExpression($expression, $queryBinds, $options);
138		$statement = $this->dataDriver->prepare($query);
139
140		$this->bindValuesToStatement($statement, $queryBinds);
141		$statement->execute();
142
143		$data = $statement->fetchAll(PDO::FETCH_ASSOC);
144
145		return $data ? $data : [];
146		}
147
148		/**
149		* Get the number of matched data in the set according to the expression.
150		*
151		* @param array $expression
152		* @return int
153		*
154		* @codeCoverageIgnore Don't test external library.
155		*/
156		public function getDataCardinality(array $expression) : int
157		{
158		$queryBinds = [];
159
160		$query = $this->getSelectQueryForExpression($expression, $queryBinds, []);
161		$statement = $this->dataDriver->prepare($query);
162		$this->bindValuesToStatement($statement, $queryBinds);
163		$statement->execute();
164
165		return $statement->rowCount();
166		}
167
168		/**
169		* Builds SQL query from the expression.
170		*
171		* @param array $expression
172		* @param array $queryBinds
173		* @param array $options
174		* @return string
175		*/
176	9	protected function getSelectQueryForExpression(
177		array $expression,
178		array&$queryBinds,
179		array $options = []
180		) : string {
181	9	$query = "SELECT * FROM {$this->dataGroup}";
182
183		// Prepare WHERE expression.
184	9	if (!empty($expression)) {
185	8	$query .= $this->getWhereExpression($expression, $queryBinds);
186		}
187
188	9	$group = $this->getQueryGroup($options);
189	9	$having = $this->getQueryHaving($options);
190
191	9	if (!empty($group)) {
192	3	$query .= " GROUP BY {$group}";
193
194	3	if (!empty($having)) {
195	1	$query .= " HAVING {$having}";
196		}
197		}
198
199	9	$query .= " ORDER BY {$this->getQueryOrder($options)}";
200
201	9	$limit = $this->getQueryLimit($options);
202
203	9	if ($limit > 0) {
204	5	$query .= " LIMIT {$limit}";
205	5	$query .= " OFFSET {$this->getQueryOffset($options)}";
206		}
207
208	9	return $query;
209		}
210
211		/**
212		* Gets the GROUP BY expression.
213		*
214		* @param array $options
215		* @return string
216		*/
217	9	protected function getQueryGroup(array $options) : string
218		{
219	9	return $options[self::OPTION_GROUP] ?? '';
220		}
221
222		/**
223		* Gets the HAVING expression only when the GROUP BY option exists.
224		*
225		* @param array $options
226		* @return string
227		*/
228	9	protected function getQueryHaving(array $options) : string
229		{
230	9	return isset($options[self::OPTION_GROUP]) ? $options[self::OPTION_HAVING] : '';
231		}
232
233		/**
234		* Gets the ORDER BY expression. The default value is the primary key.
235		*
236		* @param array $options
237		* @return string
238		*/
239	9	protected function getQueryOrder(array $options) : string
240		{
241	9	return $options[self::OPTION_ORDER] ?? $this->idKey;
242		}
243
244		/**
245		* Gets the LIMIT expression.
246		*
247		* @param array $options
248		* @return int
249		*/
250	9	protected function getQueryLimit(array $options) : int
251		{
252	9	return $options[self::OPTION_LIMIT] ?? 0;
253		}
254
255		/**
256		* Gets the OFFSET expression.
257		*
258		* @param array $options
259		* @return int
260		*/
261	5	protected function getQueryOffset(array $options) : int
262		{
263	5	return $options[self::OPTION_OFFSET] ?? 0;
264		}
265
266		/**
267		* Creates a WHERE expression for the SQL query.
268		*
269		* @param array $expression
270		* @param array $queryBinds
271		* @return string
272		*/
273	12	protected function getWhereExpression(array $expression, array&$queryBinds) : string
274		{
275	12	$whereExpression = '';
276	12	$queryParams = [];
277
278	12	foreach ($expression as $column => $value) {
279	11	$this->setParamsAndBinds($column, $value, $queryParams, $queryBinds);
280		}
281
282	12	if (!empty($queryParams)) {
283	11	$whereExpression = ' WHERE '.implode(' AND ', $queryParams);
284		}
285
286	12	return $whereExpression;
287		}
288
289		/**
290		* Set the query params and quaery bindings according to the `column` and `value`.
291		*
292		* @param string $column
293		* @param mixed $value
294		* @param array $queryParams
295		* @param array $queryBinds
296		*/
297	11	protected function setParamsAndBinds(string $column, $value, array&$queryParams, array&$queryBinds) : void
298		{
299	11	if (is_array($value)) {
300	5	$queryParams[] = $this->getInColumnCondition($column, count($value));
301	5	$queryBinds = array_merge($queryBinds, $value);
302	11	} elseif (strpos($column, ' LIKE') !== false \|\| (is_string($value) && strpos($value, '%') !== false)) {
303	4	$queryParams[] = $this->getLikeColumnCondition($column);
304	4	$queryBinds[] = $value;
305		} else {
306	11	$queryParams[] = $this->getSimpleColumnCondition($column);
307	11	$queryBinds[] = $value;
308		}
309	11	}
310
311		/**
312		* Gets a simple condition for the column.
313		*
314		* @param string $column
315		* @return string 'my_column = ?'
316		*/
317	11	protected function getSimpleColumnCondition(string $column) : string
318		{
319	11	return strpos($column, '?') === false ? "{$column} = ?" : $column;
320		}
321
322		/**
323		* Gets a 'LIKE' condition for the column.
324		*
325		* Allows special cases:
326		* @example ['my_column LIKE ?' => 'some value%']
327		* @example ['my_column LIKE' => 'some value%']
328		* @example ['my_column' => 'some value%']
329		*
330		* @param string $column
331		* @return string 'my_column LIKE ?'
332		*/
333	4	protected function getLikeColumnCondition(string $column) : string
334		{
335	4	list($columnNameOnly) = explode(' ', $column);
336
337	4	return $columnNameOnly.' LIKE ?';
338		}
339
340		/**
341		* Gets an 'IN' condition for the column.
342		*
343		* Allows special cases:
344		* @example ['my_column IN (?)' => [1,2,3]]
345		* @example ['my_column IN ?' => [1,2,3]]
346		* @example ['my_column IN' => [1,2,3]]
347		* @example ['my_column' => [1,2,3]]
348		*
349		* @param string $column
350		* @param int $parameterCount
351		* @return string 'my_column IN (?,?,?)'
352		*/
353	5	protected function getInColumnCondition(string $column, int $parameterCount = 1) : string
354		{
355	5	list($columnNameOnly) = explode(' ', $column);
356
357	5	$inParameters = str_repeat('?,', $parameterCount - 1).'?';
358
359	5	return $columnNameOnly.' IN ('.$inParameters.')';
360		}
361
362		/**
363		* Insert or update entity in the storage.
364		*
365		* @param int $identifier
366		* @param array $data
367		* @throws RuntimeException
368		* @return int The ID of the saved entity in the storage
369		*
370		* @codeCoverageIgnore Don't test external library.
371		*/
372		public function saveData(? int $identifier = null, array $data) : int
373		{
374		if (empty($identifier)) {
375		$query = "INSERT INTO {$this->dataGroup}";
376		} else {
377		$query = "UPDATE {$this->dataGroup}";
378		}
379
380		$queryData = [];
381		$queryBind = [];
382
383		foreach ($data as $fieldName => $value) {
384		$queryData[] = "{$fieldName} = ?";
385		$queryBind[] = $value;
386		}
387
388		$query .= ' SET '.implode(', ', $queryData);
389
390		if (!empty($identifier)) {
391		$query .= " WHERE {$this->idKey} = ?";
392		$queryBind[] = $identifier;
393		}
394
395		$statement = $this->dataDriver->prepare($query);
396		if (!$statement) {
397		throw new RuntimeException('Query error', 1002);
398		}
399		$this->bindValuesToStatement($statement, $queryBind);
400		$statement->execute();
401
402		return empty($identifier) ? (int) $this->dataDriver->lastInsertId() : $identifier;
403		}
404
405		/**
406		* Binds values to the statement.
407		*
408		* @param PDOStatement $statement
409		* @param array $queryBind
410		* @return void
411		*
412		* @codeCoverageIgnore Don't test external library.
413		*/
414		protected function bindValuesToStatement(PDOStatement&$statement, array $queryBind) : void
415		{
416		foreach ($queryBind as $index => $data) {
417		$paramType = PDO::PARAM_STR;
418
419		if (is_null($data)) {
420		$paramType = PDO::PARAM_NULL;
421		} elseif (is_numeric($data)) {
422		$paramType = PDO::PARAM_INT;
423		}
424
425		$statement->bindValue($index + 1, $data, $paramType);
426		}
427		}
428
429		/**
430		* Removes an entity from the storage.
431		*
432		* @param int $identifier
433		* @return bool
434		*
435		* @codeCoverageIgnore Don't test external library.
436		*/
437		public function deleteData(int $identifier) : bool
438		{
439		$statement = $this->dataDriver->prepare("DELETE FROM WHERE {$this->idKey} = ?");
440
441		return $statement->execute([$identifier]);
442		}
443		}
444

Gixx / WebHemi

Push — master ( 37b412...8220b9 )

ConnectorAdapter::getSelectQueryForExpression() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like