SqlServer::prepareAnsiOffsetSelect() - Code Metrics - Inspection of "Merge branch 'sql-server-offset'" - darya/framework - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 92c9ee...05b920 )

by Chris

created 2017-04-16 23:46 UTC

SqlServer::prepareAnsiOffsetSelect() B

↳ Parent: SqlServer

Complexity

Conditions	2
Paths	2

Size

Total Lines	41
Code Lines	27

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	27
nc	2
nop	1
dl	0
loc	41
rs	8.8571
c	0
b	0
f	0

<?php
namespace Darya\Database\Query\Translator;

use Darya\Database;
use Darya\Database\Query\AbstractSqlTranslator;
use Darya\Storage;

/**
 * Darya's SQL Server query translator.
 *
 * TODO: Bracketed identifiers; [identifier].[identifier]
 *
 * @author Chris Andrew <[email protected]>
 */
class SqlServer extends AbstractSqlTranslator
{
	/**
	 * Translate a query that reads records.
	 *
	 * @param Storage\Query $query
	 * @return Database\Query
	 */
	protected function translateRead(Storage\Query $query)
	{
		// Unfortunately, we have to deal with SQL Server's awkwardness if an
		// offset is given
		if ($query->offset > 0) {
			return new Database\Query(
				$this->prepareAnsiOffsetSelect($query),
				$this->parameters($query)
			);
		}
		
		return parent::translateRead($query);
	}
	
	/**
	 * Resolve the given value as an identifier.
	 * 
	 * @param mixed $identifier
	 * @return mixed
	 */
	protected function resolveIdentifier($identifier)
	{
		return $identifier;
	}
	
	/**
	 * Prepare a LIMIT clause using the given limit and offset.
	 * 
	 * @param int $limit  [optional]
	 * @param int $offset [optional]
	 * @return string
	 */
	protected function prepareLimit($limit = 0, $offset = 0)
	{
		if (!static::limitIsUseful($limit, $offset)) {
			return null;
		}
		
		$limit = (int) $limit;
		
		return "TOP $limit";
	}
	
	/**
	 * Prepare a SELECT statement using the given columns, table, clauses and
	 * options.
	 * 
	 * @param string       $table
	 * @param array|string $columns
	 * @param string       $joins     [optional]
	 * @param string       $where     [optional]
	 * @param string       $order     [optional]
	 * @param string       $limit     [optional]
	 * @param string       $groupings [optional]
	 * @param string       $having    [optional]
	 * @param bool         $distinct  [optional]
	 * @return string
	 */
	protected function prepareSelect(

		$table,
		$columns,
		$joins = null,
		$where = null,
		$order = null,
		$limit = null,
		$groupings = null,
		$having = null,
		$distinct = false
	) {
		$table = $this->identifier($table);
		
		$distinct = $distinct ? 'DISTINCT' : '';
		
		return static::concatenate(array('SELECT', $distinct, $limit, $columns, 'FROM', $table, $joins, $where, $groupings, $having, $order));
	}
	
	/**
	 * Prepare the column selection for an ANSI offset select statement.
	 *
	 * Cheers Microsoft.
	 *
	 * @param string $columns
	 * @param array|string $order
	 * @return string
	 */
	protected function prepareAnsiOffsetSelectColumns($columns, $order)
	{
		// An order by clause is required by ANSI offset select statements; we
		// can trick SQL Server into behaving by selecting 0 if none is given
		$orderBy = empty($order) ? 'ORDER BY (SELECT 0)' : $this->prepareOrderBy($order);
		
		return implode(', ', array(
			$columns,
			"ROW_NUMBER() OVER ({$orderBy}) row_number"
		));
	}
	
	/**
	 * Prepare an ANSI offset select statement.
	 *
	 * Cheers Microsoft.
	 *
	 * @param Storage\Query $query
	 * @return string
	 */
	protected function prepareAnsiOffsetSelect(Storage\Query $query)
	{
		// Prepare RDBMS specific clauses if we need to
		$joins = null;
		$groupBy = null;
		$having = null;
		
		if ($query instanceof Database\Storage\Query) {
			$joins = $this->prepareJoins($query->joins);
			$groupBy = $this->prepareGroupBy($query->groupings);
			$having = $this->prepareHaving($query->having);
		}
		
		// Build the inner query without its limit, but with the row number and
		// order by clause included with the columns
		$columns = $this->prepareColumns($query->fields);
		
		$innerSelect = $this->prepareSelect(
			$query->resource,
			$this->prepareAnsiOffsetSelectColumns($columns, $query->order),
			$joins,
			$this->prepareWhere($query->filter),
			null,
			null,
			$groupBy,
			$having,
			$query->distinct
		);
		
		// Construct the outer query that uses the row_number from the inner
		// query to achieve the desired offset
		return static::concatenate(array(
			'SELECT',
			$this->prepareLimit($query->limit),
			$columns,
			'FROM',
			"($innerSelect)",
			'query_results',
			"WHERE row_number > $query->offset"
		));
	}
	
	/**
	 * 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
	 */
	protected function prepareUpdate($table, $data, $where = null, $limit = null)

	{
		$table = $this->identifier($table);
		
		foreach ($data as $key => $value) {
			$column = $this->identifier($key);
			$value = $this->value($value);
			$data[$key] = "$column = $value";
		}
		
		$values = implode(', ', $data);
		
		return static::concatenate(array('UPDATE', $limit, $table, 'SET', $values, $where));
	}
	
	/**
	 * Prepare a DELETE statement with the given table and clauses.
	 * 
	 * @param string $table
	 * @param string $where [optional]
	 * @param string $limit [optional]
	 * @return string
	 */
	protected function prepareDelete($table, $where = null, $limit = null)

	{
		$table = $this->identifier($table);
		
		if ($table == '*' || !$table || !$where) {
			return null;
		}
		
		return static::concatenate(array('DELETE', $limit, 'FROM', $table, $where));
	}
}


1		<?php
2		namespace Darya\Database\Query\Translator;
3
4		use Darya\Database;
5		use Darya\Database\Query\AbstractSqlTranslator;
6		use Darya\Storage;
7
8		/**
9		* Darya's SQL Server query translator.
10		*
11		* TODO: Bracketed identifiers; [identifier].[identifier]
12		*
13		* @author Chris Andrew <[email protected]>
14		*/
15		class SqlServer extends AbstractSqlTranslator
16		{
17		/**
18		* Translate a query that reads records.
19		*
20		* @param Storage\Query $query
21		* @return Database\Query
22		*/
23		protected function translateRead(Storage\Query $query)
24		{
25		// Unfortunately, we have to deal with SQL Server's awkwardness if an
26		// offset is given
27		if ($query->offset > 0) {
28		return new Database\Query(
29		$this->prepareAnsiOffsetSelect($query),
30		$this->parameters($query)
31		);
32		}
33
34		return parent::translateRead($query);
35		}
36
37		/**
38		* Resolve the given value as an identifier.
39		*
40		* @param mixed $identifier
41		* @return mixed
42		*/
43		protected function resolveIdentifier($identifier)
44		{
45		return $identifier;
46		}
47
48		/**
49		* Prepare a LIMIT clause using the given limit and offset.
50		*
51		* @param int $limit [optional]
52		* @param int $offset [optional]
53		* @return string
54		*/
55		protected function prepareLimit($limit = 0, $offset = 0)
56		{
57		if (!static::limitIsUseful($limit, $offset)) {
58		return null;
59		}
60
61		$limit = (int) $limit;
62
63		return "TOP $limit";
64		}
65
66		/**
67		* Prepare a SELECT statement using the given columns, table, clauses and
68		* options.
69		*
70		* @param string $table
71		* @param array\|string $columns
72		* @param string $joins [optional]
73		* @param string $where [optional]
74		* @param string $order [optional]
75		* @param string $limit [optional]
76		* @param string $groupings [optional]
77		* @param string $having [optional]
78		* @param bool $distinct [optional]
79		* @return string
80		*/
81	View Code Duplication	protected function prepareSelect(
		0 ignored issues – show Duplication introduced 2016-11-18 16:30 UTC by Report Bug Copy Issue Report This method seems to be duplicated in 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...
82		$table,
83		$columns,
84		$joins = null,
85		$where = null,
86		$order = null,
87		$limit = null,
88		$groupings = null,
89		$having = null,
90		$distinct = false
91		) {
92		$table = $this->identifier($table);
93
94		$distinct = $distinct ? 'DISTINCT' : '';
95
96		return static::concatenate(array('SELECT', $distinct, $limit, $columns, 'FROM', $table, $joins, $where, $groupings, $having, $order));
97		}
98
99		/**
100		* Prepare the column selection for an ANSI offset select statement.
101		*
102		* Cheers Microsoft.
103		*
104		* @param string $columns
105		* @param array\|string $order
106		* @return string
107		*/
108		protected function prepareAnsiOffsetSelectColumns($columns, $order)
109		{
110		// An order by clause is required by ANSI offset select statements; we
111		// can trick SQL Server into behaving by selecting 0 if none is given
112		$orderBy = empty($order) ? 'ORDER BY (SELECT 0)' : $this->prepareOrderBy($order);
113
114		return implode(', ', array(
115		$columns,
116		"ROW_NUMBER() OVER ({$orderBy}) row_number"
117		));
118		}
119
120		/**
121		* Prepare an ANSI offset select statement.
122		*
123		* Cheers Microsoft.
124		*
125		* @param Storage\Query $query
126		* @return string
127		*/
128		protected function prepareAnsiOffsetSelect(Storage\Query $query)
129		{
130		// Prepare RDBMS specific clauses if we need to
131		$joins = null;
132		$groupBy = null;
133		$having = null;
134
135		if ($query instanceof Database\Storage\Query) {
136		$joins = $this->prepareJoins($query->joins);
137		$groupBy = $this->prepareGroupBy($query->groupings);
138		$having = $this->prepareHaving($query->having);
139		}
140
141		// Build the inner query without its limit, but with the row number and
142		// order by clause included with the columns
143		$columns = $this->prepareColumns($query->fields);
144
145		$innerSelect = $this->prepareSelect(
146		$query->resource,
147		$this->prepareAnsiOffsetSelectColumns($columns, $query->order),
148		$joins,
149		$this->prepareWhere($query->filter),
150		null,
151		null,
152		$groupBy,
153		$having,
154		$query->distinct
155		);
156
157		// Construct the outer query that uses the row_number from the inner
158		// query to achieve the desired offset
159		return static::concatenate(array(
160		'SELECT',
161		$this->prepareLimit($query->limit),
162		$columns,
163		'FROM',
164		"($innerSelect)",
165		'query_results',
166		"WHERE row_number > $query->offset"
167		));
168		}
169
170		/**
171		* Prepare an UPDATE statement with the given table, data and clauses.
172		*
173		* @param string $table
174		* @param array $data
175		* @param string $where [optional]
176		* @param string $limit [optional]
177		* @return string
178		*/
179	View Code Duplication	protected function prepareUpdate($table, $data, $where = null, $limit = null)
		0 ignored issues – show Duplication introduced 2015-11-24 14:38 UTC by Report Bug Copy Issue Report This method seems to be duplicated in 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...
180		{
181		$table = $this->identifier($table);
182
183		foreach ($data as $key => $value) {
184		$column = $this->identifier($key);
185		$value = $this->value($value);
186		$data[$key] = "$column = $value";
187		}
188
189		$values = implode(', ', $data);
190
191		return static::concatenate(array('UPDATE', $limit, $table, 'SET', $values, $where));
192		}
193
194		/**
195		* Prepare a DELETE statement with the given table and clauses.
196		*
197		* @param string $table
198		* @param string $where [optional]
199		* @param string $limit [optional]
200		* @return string
201		*/
202	View Code Duplication	protected function prepareDelete($table, $where = null, $limit = null)
		0 ignored issues – show Duplication introduced 2015-11-24 14:38 UTC by Report Bug Copy Issue Report This method seems to be duplicated in 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...
203		{
204		$table = $this->identifier($table);
205
206		if ($table == '*' \|\| !$table \|\| !$where) {
207		return null;
208		}
209
210		return static::concatenate(array('DELETE', $limit, 'FROM', $table, $where));
211		}
212		}
213

darya / framework

Push — master ( 92c9ee...05b920 )

SqlServer::prepareAnsiOffsetSelect() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like