AbstractSqlTranslator::resolvesPlaceholder() - Code Metrics - Inspection of "Improved upon c6cb8b28 by accounting for booleans." - darya/framework - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( c6cb8b...4117ed )

by Chris

created 2016-01-17 21:36 UTC

AbstractSqlTranslator::resolvesPlaceholder() A

↳ Parent: AbstractSqlTranslator

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?php
namespace Darya\Database\Query;

use Exception;
use Darya\Database;
use Darya\Database\Query\Translator;
use Darya\Storage;

/**
 * An abstract query translator that prepares SQL common across more than one
 * RDBMS.
 * 
 * TODO: Separate the switch statement cases out into their own methods.
 * 
 * @author Chris Andrew <[email protected]>
 */
abstract class AbstractSqlTranslator implements Translator {
	
	/**
	 * @var array Filter comparison operators
	 */
	protected $operators = array('>=', '<=', '>', '<', '=', '!=', '<>', 'in', 'not in', 'is', 'is not', 'like', 'not like');
	
	/**
	 * Concatenates the given set of strings that aren't empty.
	 * 
	 * Runs implode() after filtering out empty elements.
	 * 
	 * Delimiter defaults to a single whitespace character.
	 * 
	 * @param array  $strings
	 * @param string $delimiter [optional]
	 * @return string
	 */
	protected static function concatenate($strings, $delimiter = ' ') {
		$strings = array_filter($strings, function($value) {
			return !empty($value);
		});
		
		return implode($delimiter, $strings);
	}
	
	/**
	 * Determine whether the given limit and offset will make a difference to
	 * a statement.
	 * 
	 * Simply determines whether both are non-zero integers.
	 * 
	 * @param int $limit
	 * @param int $offset
	 * @return bool
	 */
	protected static function limitIsUseful($limit, $offset) {
		return (int) $limit !== 0 || (int) $offset !== 0;
	}
	
	/**
	 * Translate the given storage query into a MySQL query.
	 * 
	 * @param Storage\Query $storageQuery
	 * @return Database\Query
	 * @throws Exception
	 */
	public function translate(Storage\Query $storageQuery) {
		$type = $storageQuery->type;
		
		switch ($type) {
			case Storage\Query::CREATE:
				$query = new Database\Query(
					$this->prepareInsert($storageQuery->resource, $storageQuery->data),
					$this->parameters($storageQuery)
				);
				
				break;
			case Storage\Query::READ:
				$query = new Database\Query(
					$this->prepareSelect($storageQuery->resource,
						$this->prepareColumns($storageQuery->fields),
						$this->prepareWhere($storageQuery->filter),
						$this->prepareOrderBy($storageQuery->order),
						$this->prepareLimit($storageQuery->limit, $storageQuery->offset),
						$storageQuery->distinct
					),
					$this->parameters($storageQuery)
				);
				
				break;
			case Storage\Query::UPDATE:

				$query = new Database\Query(
					$this->prepareUpdate($storageQuery->resource, $storageQuery->data,
						$this->prepareWhere($storageQuery->filter),
						$this->prepareLimit($storageQuery->limit, $storageQuery->offset)
					),
					$this->parameters($storageQuery)
				);
				
				break;
			case Storage\Query::DELETE:

				$query = new Database\Query(
					$this->prepareDelete($storageQuery->resource,
						$this->prepareWhere($storageQuery->filter),
						$this->prepareLimit($storageQuery->limit, $storageQuery->offset)
					),
					$this->parameters($storageQuery)
				);
				
				break;
		}
		
		if (!isset($query)) {
			throw new Exception("Could not translate query of unsupported type '$type'");
		}
		
		
		return $query;
	}
	
	/**
	 * Escape the given identifier.
	 * 
	 * If the value is an array, it is recursively escaped.
	 * 
	 * If the value is not a string, it is returned unmodified.
	 * 
	 * @param mixed $identifier
	 * @return mixed
	 */
	abstract protected function identifier($identifier);
	
	/**
	 * Prepare the given value.
	 * 
	 * If the value is an array, it is recursively prepared.
	 * 
	 * @param array|string $value
	 * @return array|string
	 */
	protected function value($value) {
		if (is_array($value)) {
			return array_map(array($this, 'value'), $value);
		}
		
		return $this->resolve($value);
	}
	
	/**
	 * Resolve a placeholder or constant for the given parameter value.
	 * 
	 * @param mixed $value
	 * @return string
	 */
	protected function resolve($value) {
		if ($value === null) {
			return 'NULL';
		}
		
		if (is_bool($value)) {
			return $value ? 'TRUE' : 'FALSE';
		}
		
		return '?';
	}
	
	/**
	 * Determine whether the given value resolves a placeholder.
	 * 
	 * @param mixed $value
	 * @return bool
	 */
	protected function resolvesPlaceholder($value) {
		return $this->resolve($value) === '?';
	}
	
	/**
	 * Prepare the given columns as a string.
	 * 
	 * @param array|string $columns
	 * @return string
	 */
	protected function prepareColumns($columns) {
		if (empty($columns)) {
			return '*';
		}
		
		$columns = (array) $this->identifier($columns);
		
		return implode(', ', $columns);
	}
	
	/**
	 * Prepare a default operator for the given value.
	 * 
	 * @param string $operator
	 * @param mixed  $value
	 * @return string
	 */
	protected function prepareOperator($operator, $value) {
		$operator = in_array(strtolower($operator), $this->operators) ? strtoupper($operator) : '=';
		
		if (!$this->resolvesPlaceholder($value)) {

			if ($operator === '=') {
				$operator = 'IS';
			}
			
			if ($operator === '!=') {
				$operator = 'IS NOT';
			}
		}
		
		if (is_array($value)) {
			if ($operator === '=') {
				$operator = 'IN';
			}
		
			if ($operator === '!=') {
				$operator = 'NOT IN';
			}
		}
		
		return $operator;
	}
	
	/**
	 * Prepare an individual filter condition.
	 * 
	 * @param string $column
	 * @param mixed  $value
	 * @return string
	 */
	protected function prepareFilter($column, $value) {
		list($column, $operator) = array_pad(explode(' ', $column, 2), 2, null);
		
		$column = $this->prepareColumns($column);
		$operator = $this->prepareOperator($operator, $value);
		$value = $this->value($value);
		
		if (is_array($value)) {
			$value = "(" . implode(", ", $value) . ")";
		}
		
		return "$column $operator $value";
	}
	
	/**
	 * Prepare a WHERE clause using the given filter and comparison operator.
	 * 
	 * Example filter key-values and their SQL equivalents:
	 *     'id'        => 1,       // id = '1'
	 *     'name like' => 'Chris', // name LIKE 'Chris'
	 *     'count >'   => 10,      // count > '10'
	 *     'type in'   => [1, 2],  // type IN (1, 2)
	 *     'type'      => [3, 4]   // type IN (3, 4)
	 * 
	 * Comparison operator between conditions defaults to 'AND'.
	 * 
	 * @param array  $filter
	 * @param string $comparison   [optional]
	 * @param bool   $excludeWhere [optional]
	 * @return string
	 */
	protected function prepareWhere(array $filter, $comparison = 'AND', $excludeWhere = false) {
		$conditions = array();
		
		foreach ($filter as $column => $value) {
			if (strtolower($column) == 'or') {
				$conditions[] = '(' . $this->prepareWhere($value, 'OR', true) . ')';
			} else {
				$conditions[] = $this->prepareFilter($column, $value);
			}
		}
		
		if (!count($conditions)) {
			return null;
		}
		
		$where = $excludeWhere ? '' : 'WHERE ';
		$where .= implode(" $comparison ", $conditions);
		
		return $where;
	}
	
	/**
	 * Prepare an individual order condition.
	 * 
	 * @param string $column
	 * @param string $direction [optional]
	 * @return string
	 */
	protected function prepareOrder($column, $direction = null) {
		$column = $this->identifier($column);
		$direction = $direction !== null ? strtoupper($direction) : 'ASC';
		
		return !empty($column) ? "$column $direction" : null;
	}
	
	/**
	 * Prepare an ORDER BY clause using the given order.
	 * 
	 * Example order key-values:
	 *     'column',
	 *     'other_column'   => 'ASC',
	 *     'another_column' => 'DESC
	 * 
	 * Ordered ascending by default.
	 * 
	 * @param array|string $order
	 * @return string
	 */
	protected function prepareOrderBy($order) {
		$conditions = array();
		
		foreach ((array) $order as $key => $value) {
			if (is_numeric($key)) {
				$conditions[] = $this->prepareOrder($value);
			} else {
				$conditions[] = $this->prepareOrder($key, $value);
			}
		}
		
		return count($conditions) ? 'ORDER BY ' . implode(', ', $conditions) : null;
	}
	
	/**
	 * Prepare a SELECT statement using the given columns, table, clauses and
	 * options.
	 * 
	 * @param string       $table
	 * @param array|string $columns
	 * @param string       $where    [optional]
	 * @param string       $order    [optional]
	 * @param string       $limit    [optional]
	 * @param bool         $distinct [optional]
	 * @return string
	 */
	abstract protected function prepareSelect($table, $columns, $where = null, $order = null, $limit = null, $distinct = false);
	
	/**
	 * Prepare an INSERT INTO statement using the given table and data.
	 * 
	 * @param string $table
	 * @param array  $data
	 * @return string
	 */
	protected function prepareInsert($table, array $data) {
		$table = $this->identifier($table);
		
		$columns = $this->identifier(array_keys($data));
		$values  = $this->value(array_values($data));
		
		$columns = "(" . implode(", ", $columns) . ")";
		$values  = "(" . implode(", ", $values) . ")";
		
		return static::concatenate(array('INSERT INTO', $table, $columns, 'VALUES', $values));
	}
	
	/**
	 * Prepare an UPDATE statement with the given table, data and clauses.
	 * 
	 * @param string $table
	 * @param array  $data
	 * @param string $where [optional]
	 * @param string $limit [optional]
	 * @return string
	 */
	abstract protected function prepareUpdate($table, $data, $where = null, $limit = null);
	
	/**
	 * Prepare a DELETE statement with the given table and clauses.
	 * 
	 * @param string $table
	 * @param string $where [optional]
	 * @param string $limit [optional]
	 * @return string
	 */
	abstract protected function prepareDelete($table, $where = null, $limit = null);
	
	/**
	 * Prepare the given filter as an array of prepared query parameters.
	 * 
	 * @return array
	 */
	protected function filterParameters($filter) {
		$parameters = array();
		
		foreach ($filter as $index => $value) {
			if (is_array($value)) {
				if (strtolower($index) === 'or') {
					$parameters = array_merge($parameters, $this->filterParameters($value));
				} else {
					foreach ($value as $in) {
						if ($this->resolvesPlaceholder($value)) {
							$parameters[] = $in;
						}
					}
				}
			} else {
				if ($this->resolvesPlaceholder($value)) {
					$parameters[] = $value;
				}
			}
		}
		
		return $parameters;
	}
	
	/**
	 * Retrieve an array of parameters from the given query for executing a
	 * prepared query.
	 * 
	 * @param Storage\Query $query
	 * @return array
	 */
	public function parameters(Storage\Query $query) {
		$parameters = array();
		
		foreach ($query->data as $value) {
			if ($this->resolvesPlaceholder($value)) {
				$parameters[] = $value;
			}
		}
		
		$parameters = array_merge($parameters, $this->filterParameters($query->filter));
		
		return $parameters;
	}
	
}


1		<?php
2		namespace Darya\Database\Query;
3
4		use Exception;
5		use Darya\Database;
6		use Darya\Database\Query\Translator;
7		use Darya\Storage;
8
9		/**
10		* An abstract query translator that prepares SQL common across more than one
11		* RDBMS.
12		*
13		* TODO: Separate the switch statement cases out into their own methods.
14		*
15		* @author Chris Andrew <[email protected]>
16		*/
17		abstract class AbstractSqlTranslator implements Translator {
18
19		/**
20		* @var array Filter comparison operators
21		*/
22		protected $operators = array('>=', '<=', '>', '<', '=', '!=', '<>', 'in', 'not in', 'is', 'is not', 'like', 'not like');
23
24		/**
25		* Concatenates the given set of strings that aren't empty.
26		*
27		* Runs implode() after filtering out empty elements.
28		*
29		* Delimiter defaults to a single whitespace character.
30		*
31		* @param array $strings
32		* @param string $delimiter [optional]
33		* @return string
34		*/
35		protected static function concatenate($strings, $delimiter = ' ') {
36		$strings = array_filter($strings, function($value) {
37		return !empty($value);
38		});
39
40		return implode($delimiter, $strings);
41		}
42
43		/**
44		* Determine whether the given limit and offset will make a difference to
45		* a statement.
46		*
47		* Simply determines whether both are non-zero integers.
48		*
49		* @param int $limit
50		* @param int $offset
51		* @return bool
52		*/
53		protected static function limitIsUseful($limit, $offset) {
54		return (int) $limit !== 0 \|\| (int) $offset !== 0;
55		}
56
57		/**
58		* Translate the given storage query into a MySQL query.
59		*
60		* @param Storage\Query $storageQuery
61		* @return Database\Query
62		* @throws Exception
63		*/
64		public function translate(Storage\Query $storageQuery) {
65		$type = $storageQuery->type;
66
67		switch ($type) {
68		case Storage\Query::CREATE:
69		$query = new Database\Query(
70		$this->prepareInsert($storageQuery->resource, $storageQuery->data),
71		$this->parameters($storageQuery)
72		);
73
74		break;
75		case Storage\Query::READ:
76		$query = new Database\Query(
77		$this->prepareSelect($storageQuery->resource,
78		$this->prepareColumns($storageQuery->fields),
79		$this->prepareWhere($storageQuery->filter),
80		$this->prepareOrderBy($storageQuery->order),
81		$this->prepareLimit($storageQuery->limit, $storageQuery->offset),
82		$storageQuery->distinct
83		),
84		$this->parameters($storageQuery)
85		);
86
87		break;
88	View Code Duplication	case Storage\Query::UPDATE:
		0 ignored issues – show Duplication introduced 2015-11-24 14:38 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
89		$query = new Database\Query(
90		$this->prepareUpdate($storageQuery->resource, $storageQuery->data,
91		$this->prepareWhere($storageQuery->filter),
92		$this->prepareLimit($storageQuery->limit, $storageQuery->offset)
93		),
94		$this->parameters($storageQuery)
95		);
96
97		break;
98	View Code Duplication	case Storage\Query::DELETE:
		0 ignored issues – show Duplication introduced 2015-11-24 14:38 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
99		$query = new Database\Query(
100		$this->prepareDelete($storageQuery->resource,
101		$this->prepareWhere($storageQuery->filter),
102		$this->prepareLimit($storageQuery->limit, $storageQuery->offset)
103		),
104		$this->parameters($storageQuery)
105		);
106
107		break;
108		}
109
110		if (!isset($query)) {
111		throw new Exception("Could not translate query of unsupported type '$type'");
112		}
113
114
115		return $query;
116		}
117
118		/**
119		* Escape the given identifier.
120		*
121		* If the value is an array, it is recursively escaped.
122		*
123		* If the value is not a string, it is returned unmodified.
124		*
125		* @param mixed $identifier
126		* @return mixed
127		*/
128		abstract protected function identifier($identifier);
129
130		/**
131		* Prepare the given value.
132		*
133		* If the value is an array, it is recursively prepared.
134		*
135		* @param array\|string $value
136		* @return array\|string
137		*/
138		protected function value($value) {
139		if (is_array($value)) {
140		return array_map(array($this, 'value'), $value);
141		}
142
143		return $this->resolve($value);
144		}
145
146		/**
147		* Resolve a placeholder or constant for the given parameter value.
148		*
149		* @param mixed $value
150		* @return string
151		*/
152		protected function resolve($value) {
153		if ($value === null) {
154		return 'NULL';
155		}
156
157		if (is_bool($value)) {
158		return $value ? 'TRUE' : 'FALSE';
159		}
160
161		return '?';
162		}
163
164		/**
165		* Determine whether the given value resolves a placeholder.
166		*
167		* @param mixed $value
168		* @return bool
169		*/
170		protected function resolvesPlaceholder($value) {
171		return $this->resolve($value) === '?';
172		}
173
174		/**
175		* Prepare the given columns as a string.
176		*
177		* @param array\|string $columns
178		* @return string
179		*/
180		protected function prepareColumns($columns) {
181		if (empty($columns)) {
182		return '*';
183		}
184
185		$columns = (array) $this->identifier($columns);
186
187		return implode(', ', $columns);
188		}
189
190		/**
191		* Prepare a default operator for the given value.
192		*
193		* @param string $operator
194		* @param mixed $value
195		* @return string
196		*/
197		protected function prepareOperator($operator, $value) {
198		$operator = in_array(strtolower($operator), $this->operators) ? strtoupper($operator) : '=';
199
200	View Code Duplication	if (!$this->resolvesPlaceholder($value)) {
		0 ignored issues – show Duplication introduced 2016-01-17 21:39 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
201		if ($operator === '=') {
202		$operator = 'IS';
203		}
204
205		if ($operator === '!=') {
206		$operator = 'IS NOT';
207		}
208		}
209
210		if (is_array($value)) {
211		if ($operator === '=') {
212		$operator = 'IN';
213		}
214
215		if ($operator === '!=') {
216		$operator = 'NOT IN';
217		}
218		}
219
220		return $operator;
221		}
222
223		/**
224		* Prepare an individual filter condition.
225		*
226		* @param string $column
227		* @param mixed $value
228		* @return string
229		*/
230		protected function prepareFilter($column, $value) {
231		list($column, $operator) = array_pad(explode(' ', $column, 2), 2, null);
232
233		$column = $this->prepareColumns($column);
234		$operator = $this->prepareOperator($operator, $value);
235		$value = $this->value($value);
236
237		if (is_array($value)) {
238		$value = "(" . implode(", ", $value) . ")";
239		}
240
241		return "$column $operator $value";
242		}
243
244		/**
245		* Prepare a WHERE clause using the given filter and comparison operator.
246		*
247		* Example filter key-values and their SQL equivalents:
248		* 'id' => 1, // id = '1'
249		* 'name like' => 'Chris', // name LIKE 'Chris'
250		* 'count >' => 10, // count > '10'
251		* 'type in' => [1, 2], // type IN (1, 2)
252		* 'type' => [3, 4] // type IN (3, 4)
253		*
254		* Comparison operator between conditions defaults to 'AND'.
255		*
256		* @param array $filter
257		* @param string $comparison [optional]
258		* @param bool $excludeWhere [optional]
259		* @return string
260		*/
261		protected function prepareWhere(array $filter, $comparison = 'AND', $excludeWhere = false) {
262		$conditions = array();
263
264		foreach ($filter as $column => $value) {
265		if (strtolower($column) == 'or') {
266		$conditions[] = '(' . $this->prepareWhere($value, 'OR', true) . ')';
267		} else {
268		$conditions[] = $this->prepareFilter($column, $value);
269		}
270		}
271
272		if (!count($conditions)) {
273		return null;
274		}
275
276		$where = $excludeWhere ? '' : 'WHERE ';
277		$where .= implode(" $comparison ", $conditions);
278
279		return $where;
280		}
281
282		/**
283		* Prepare an individual order condition.
284		*
285		* @param string $column
286		* @param string $direction [optional]
287		* @return string
288		*/
289		protected function prepareOrder($column, $direction = null) {
290		$column = $this->identifier($column);
291		$direction = $direction !== null ? strtoupper($direction) : 'ASC';
292
293		return !empty($column) ? "$column $direction" : null;
294		}
295
296		/**
297		* Prepare an ORDER BY clause using the given order.
298		*
299		* Example order key-values:
300		* 'column',
301		* 'other_column' => 'ASC',
302		* 'another_column' => 'DESC
303		*
304		* Ordered ascending by default.
305		*
306		* @param array\|string $order
307		* @return string
308		*/
309		protected function prepareOrderBy($order) {
310		$conditions = array();
311
312		foreach ((array) $order as $key => $value) {
313		if (is_numeric($key)) {
314		$conditions[] = $this->prepareOrder($value);
315		} else {
316		$conditions[] = $this->prepareOrder($key, $value);
317		}
318		}
319
320		return count($conditions) ? 'ORDER BY ' . implode(', ', $conditions) : null;
321		}
322
323		/**
324		* Prepare a SELECT statement using the given columns, table, clauses and
325		* options.
326		*
327		* @param string $table
328		* @param array\|string $columns
329		* @param string $where [optional]
330		* @param string $order [optional]
331		* @param string $limit [optional]
332		* @param bool $distinct [optional]
333		* @return string
334		*/
335		abstract protected function prepareSelect($table, $columns, $where = null, $order = null, $limit = null, $distinct = false);
336
337		/**
338		* Prepare an INSERT INTO statement using the given table and data.
339		*
340		* @param string $table
341		* @param array $data
342		* @return string
343		*/
344		protected function prepareInsert($table, array $data) {
345		$table = $this->identifier($table);
346
347		$columns = $this->identifier(array_keys($data));
348		$values = $this->value(array_values($data));
349
350		$columns = "(" . implode(", ", $columns) . ")";
351		$values = "(" . implode(", ", $values) . ")";
352
353		return static::concatenate(array('INSERT INTO', $table, $columns, 'VALUES', $values));
354		}
355
356		/**
357		* Prepare an UPDATE statement with the given table, data and clauses.
358		*
359		* @param string $table
360		* @param array $data
361		* @param string $where [optional]
362		* @param string $limit [optional]
363		* @return string
364		*/
365		abstract protected function prepareUpdate($table, $data, $where = null, $limit = null);
366
367		/**
368		* Prepare a DELETE statement with the given table and clauses.
369		*
370		* @param string $table
371		* @param string $where [optional]
372		* @param string $limit [optional]
373		* @return string
374		*/
375		abstract protected function prepareDelete($table, $where = null, $limit = null);
376
377		/**
378		* Prepare the given filter as an array of prepared query parameters.
379		*
380		* @return array
381		*/
382		protected function filterParameters($filter) {
383		$parameters = array();
384
385		foreach ($filter as $index => $value) {
386		if (is_array($value)) {
387		if (strtolower($index) === 'or') {
388		$parameters = array_merge($parameters, $this->filterParameters($value));
389		} else {
390		foreach ($value as $in) {
391		if ($this->resolvesPlaceholder($value)) {
392		$parameters[] = $in;
393		}
394		}
395		}
396		} else {
397		if ($this->resolvesPlaceholder($value)) {
398		$parameters[] = $value;
399		}
400		}
401		}
402
403		return $parameters;
404		}
405
406		/**
407		* Retrieve an array of parameters from the given query for executing a
408		* prepared query.
409		*
410		* @param Storage\Query $query
411		* @return array
412		*/
413		public function parameters(Storage\Query $query) {
414		$parameters = array();
415
416		foreach ($query->data as $value) {
417		if ($this->resolvesPlaceholder($value)) {
418		$parameters[] = $value;
419		}
420		}
421
422		$parameters = array_merge($parameters, $this->filterParameters($query->filter));
423
424		return $parameters;
425		}
426
427		}
428

darya / framework

Push — master ( c6cb8b...4117ed )

AbstractSqlTranslator::resolvesPlaceholder() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like