DBQueryBuilder::buildLimitFragment() - Code Metrics - Inspection of "DOCS Upgrading 3.x docs on session stage param" - silverstripe/silverstripe-framework - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 3 (#8214)

by Ingo

created 2018-06-24 23:11 UTC

DBQueryBuilder::buildLimitFragment() B

↳ Parent: DBQueryBuilder

Complexity

Conditions	8
Paths	5

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	8
nc	5
nop	2
dl	0
loc	26
rs	8.4444
c	0
b	0
f	0

<?php

/**
 * Builds a SQL query string from a SQLExpression object
 *
 * @package framework
 * @subpackage model
 */
class DBQueryBuilder {

	/**
	 * Determines the line separator to use.
	 *
	 * @return string Non-empty whitespace character
	 */
	public function getSeparator() {
		return "\n ";
	}

	/**
	 * Builds a sql query with the specified connection
	 *
	 * @param SQLExpression $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string The resulting SQL as a string
	 */
	public function buildSQL(SQLExpression $query, &$parameters) {
		$sql = null;
		$parameters = array();

		// Ignore null queries
		if($query->isEmpty()) return null;

		if($query instanceof SQLSelect) {
			$sql = $this->buildSelectQuery($query, $parameters);
		} elseif($query instanceof SQLDelete) {
			$sql = $this->buildDeleteQuery($query, $parameters);
		} elseif($query instanceof SQLInsert) {
			$sql = $this->buildInsertQuery($query, $parameters);
		} elseif($query instanceof SQLUpdate) {
			$sql = $this->buildUpdateQuery($query, $parameters);
		} else {
			user_error("Not implemented: query generation for type " . $query->getType());
		}
		return $sql;
	}

	/**
	 * Builds a query from a SQLSelect expression
	 *
	 * @param SQLSelect $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed SQL string
	 */
	protected function buildSelectQuery(SQLSelect $query, array &$parameters) {
		$sql  = $this->buildSelectFragment($query, $parameters);
		$sql .= $this->buildFromFragment($query, $parameters);
		$sql .= $this->buildWhereFragment($query, $parameters);
		$sql .= $this->buildGroupByFragment($query, $parameters);
		$sql .= $this->buildHavingFragment($query, $parameters);
		$sql .= $this->buildOrderByFragment($query, $parameters);
		$sql .= $this->buildLimitFragment($query, $parameters);
		return $sql;
	}

	/**
	 * Builds a query from a SQLDelete expression
	 *
	 * @param SQLDelete $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed SQL string
	 */
	protected function buildDeleteQuery(SQLDelete $query, array &$parameters) {
		$sql  = $this->buildDeleteFragment($query, $parameters);
		$sql .= $this->buildFromFragment($query, $parameters);
		$sql .= $this->buildWhereFragment($query, $parameters);
		return $sql;
	}

	/**
	 * Builds a query from a SQLInsert expression
	 *
	 * @param SQLInsert $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed SQL string
	 */
	protected function buildInsertQuery(SQLInsert $query, array &$parameters) {
		$nl = $this->getSeparator();
		$into = $query->getInto();

		// Column identifiers
		$columns = $query->getColumns();
		$sql = "INSERT INTO {$into}{$nl}(" . implode(', ', $columns) . ")";

		// Values
		$sql .= "{$nl}VALUES";

		// Build all rows
		$rowParts = array();
		foreach($query->getRows() as $row) {
			// Build all columns in this row
			$assignments = $row->getAssignments();
			// Join SET components together, considering parameters
			$parts = array();
			foreach($columns as $column) {
				// Check if this column has a value for this row
				if(isset($assignments[$column])) {
					// Assigment is a single item array, expand with a loop here
					foreach($assignments[$column] as $assignmentSQL => $assignmentParameters) {
						$parts[] = $assignmentSQL;
						$parameters = array_merge($parameters, $assignmentParameters);
						break;
					}
				} else {
					// This row is missing a value for a column used by another row
					$parts[] = '?';
					$parameters[] = null;
				}
			}
			$rowParts[] = '(' . implode(', ', $parts) . ')';
		}
		$sql .= $nl . implode(",$nl", $rowParts);

		return $sql;
	}

	/**
	 * Builds a query from a SQLUpdate expression
	 *
	 * @param SQLUpdate $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed SQL string
	 */
	protected function buildUpdateQuery(SQLUpdate $query, array &$parameters) {
		$sql  = $this->buildUpdateFragment($query, $parameters);
		$sql .= $this->buildWhereFragment($query, $parameters);
		return $sql;
	}

	/**
	 * Returns the SELECT clauses ready for inserting into a query.
	 *
	 * @param SQLSelect $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed select part of statement
	 */
	protected function buildSelectFragment(SQLSelect $query, array &$parameters) {

		$distinct = $query->getDistinct();
		$select = $query->getSelect();
		$clauses = array();

		foreach ($select as $alias => $field) {
			// Don't include redundant aliases.
			$fieldAlias = "\"{$alias}\"";
			if ($alias === $field || substr($field, -strlen($fieldAlias)) === $fieldAlias) {
				$clauses[] = $field;
			} else {
				$clauses[] = "$field AS $fieldAlias";
			}
		}

		$text = 'SELECT ';
		if ($distinct) $text .= 'DISTINCT ';
		return $text .= implode(', ', $clauses);
	}

	/**
	 * Return the DELETE clause ready for inserting into a query.
	 *
	 * @param SQLExpression $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed delete part of statement
	 */
	public function buildDeleteFragment(SQLDelete $query, array &$parameters) {

		$text = 'DELETE';

		// If doing a multiple table delete then list the target deletion tables here
		// Note that some schemas don't support multiple table deletion
		$delete = $query->getDelete();
		if(!empty($delete)) {
			$text .= ' ' . implode(', ', $delete);
		}
		return $text;
	}

	/**
	 * Return the UPDATE clause ready for inserting into a query.
	 *
	 * @param SQLExpression $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed from part of statement
	 */
	public function buildUpdateFragment(SQLUpdate $query, array &$parameters) {
		$table = $query->getTable();
		$text = "UPDATE $table";

		// Join SET components together, considering parameters
		$parts = array();
		foreach($query->getAssignments() as $column => $assignment) {
			// Assigment is a single item array, expand with a loop here
			foreach($assignment as $assignmentSQL => $assignmentParameters) {
				$parts[] = "$column = $assignmentSQL";
				$parameters = array_merge($parameters, $assignmentParameters);
				break;
			}
		}
		$nl = $this->getSeparator();
		$text .= "{$nl}SET " . implode(', ', $parts);
		return $text;
	}

	/**
	 * Return the FROM clause ready for inserting into a query.
	 *
	 * @param SQLExpression $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed from part of statement
	 */
	public function buildFromFragment(SQLConditionalExpression $query, array &$parameters) {
		$from = $query->getJoins($joinParameters);
		$parameters = array_merge($parameters, $joinParameters);
		$nl = $this->getSeparator();
		return  "{$nl}FROM " . implode(' ', $from);
	}

	/**
	 * Returns the WHERE clauses ready for inserting into a query.
	 *
	 * @param SQLExpression $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed where condition
	 */
	public function buildWhereFragment(SQLConditionalExpression $query, array &$parameters) {
		// Get parameterised elements
		$where = $query->getWhereParameterised($whereParameters);
		if(empty($where)) return '';

		// Join conditions
		$connective = $query->getConnective();
		$parameters = array_merge($parameters, $whereParameters);
		$nl = $this->getSeparator();
		return "{$nl}WHERE (" . implode("){$nl}{$connective} (", $where) . ")";
	}

	/**
	 * Returns the ORDER BY clauses ready for inserting into a query.
	 *
	 * @param SQLSelect $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed order by part of statement
	 */
	public function buildOrderByFragment(SQLSelect $query, array &$parameters) {

		$orderBy = $query->getOrderBy();
		if(empty($orderBy)) return '';

		// Build orders, each with direction considered
		$statements = array();
		foreach ($orderBy as $clause => $dir) {
			$statements[] = trim("$clause $dir");
		}

		$nl = $this->getSeparator();
		return "{$nl}ORDER BY " . implode(', ', $statements);
	}

	/**
	 * Returns the GROUP BY clauses ready for inserting into a query.
	 *
	 * @param SQLSelect $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string Completed group part of statement
	 */
	public function buildGroupByFragment(SQLSelect $query, array &$parameters) {

		$groupBy = $query->getGroupBy();
		if(empty($groupBy)) return '';

		$nl = $this->getSeparator();
		return "{$nl}GROUP BY " . implode(', ', $groupBy);
	}

	/**
	 * Returns the HAVING clauses ready for inserting into a query.
	 *
	 * @param SQLSelect $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string
	 */
	public function buildHavingFragment(SQLSelect $query, array &$parameters) {
		$having = $query->getHavingParameterised($havingParameters);
		if(empty($having)) return '';

		// Generate having, considering parameters present
		$connective = $query->getConnective();
		$parameters = array_merge($parameters, $havingParameters);
		$nl = $this->getSeparator();
		return "{$nl}HAVING (" . implode("){$nl}{$connective} (", $having) . ")";
	}

	/**
	 * Return the LIMIT clause ready for inserting into a query.
	 *
	 * @param SQLSelect $query The expression object to build from
	 * @param array $parameters Out parameter for the resulting query parameters
	 * @return string The finalised limit SQL fragment
	 */
	public function buildLimitFragment(SQLSelect $query, array &$parameters) {

		$nl = $this->getSeparator();

		// Ensure limit is given
		$limit = $query->getLimit();
		if(empty($limit)) return '';

		// For literal values return this as the limit SQL
		if (!is_array($limit)) {
			return "{$nl}LIMIT $limit";
		}

		// Assert that the array version provides the 'limit' key
		if (!isset($limit['limit']) || !is_numeric($limit['limit'])) {
			throw new InvalidArgumentException(
				'DBQueryBuilder::buildLimitSQL(): Wrong format for $limit: '. var_export($limit, true)
			);
		}

		// Format the array limit, given an optional start key
		$clause = "{$nl}LIMIT {$limit['limit']}";
		if(isset($limit['start']) && is_numeric($limit['start']) && $limit['start'] !== 0) {
			$clause .= " OFFSET {$limit['start']}";
		}
		return $clause;
	}
}


1			<?php
2
3			/**
4			* Builds a SQL query string from a SQLExpression object
5			*
6			* @package framework
7			* @subpackage model
8			*/
9			class DBQueryBuilder {
10
11			/**
12			* Determines the line separator to use.
13			*
14			* @return string Non-empty whitespace character
15			*/
16			public function getSeparator() {
17			return "\n ";
18			}
19
20			/**
21			* Builds a sql query with the specified connection
22			*
23			* @param SQLExpression $query The expression object to build from
24			* @param array $parameters Out parameter for the resulting query parameters
25			* @return string The resulting SQL as a string
26			*/
27			public function buildSQL(SQLExpression $query, &$parameters) {
28			$sql = null;
29			$parameters = array();
30
31			// Ignore null queries
32			if($query->isEmpty()) return null;
33
34			if($query instanceof SQLSelect) {
35			$sql = $this->buildSelectQuery($query, $parameters);
36			} elseif($query instanceof SQLDelete) {
37			$sql = $this->buildDeleteQuery($query, $parameters);
38			} elseif($query instanceof SQLInsert) {
39			$sql = $this->buildInsertQuery($query, $parameters);
40			} elseif($query instanceof SQLUpdate) {
41			$sql = $this->buildUpdateQuery($query, $parameters);
42			} else {
43			user_error("Not implemented: query generation for type " . $query->getType());
44			}
45			return $sql;
46			}
47
48			/**
49			* Builds a query from a SQLSelect expression
50			*
51			* @param SQLSelect $query The expression object to build from
52			* @param array $parameters Out parameter for the resulting query parameters
53			* @return string Completed SQL string
54			*/
55			protected function buildSelectQuery(SQLSelect $query, array &$parameters) {
56			$sql = $this->buildSelectFragment($query, $parameters);
57			$sql .= $this->buildFromFragment($query, $parameters);
58			$sql .= $this->buildWhereFragment($query, $parameters);
59			$sql .= $this->buildGroupByFragment($query, $parameters);
60			$sql .= $this->buildHavingFragment($query, $parameters);
61			$sql .= $this->buildOrderByFragment($query, $parameters);
62			$sql .= $this->buildLimitFragment($query, $parameters);
63			return $sql;
64			}
65
66			/**
67			* Builds a query from a SQLDelete expression
68			*
69			* @param SQLDelete $query The expression object to build from
70			* @param array $parameters Out parameter for the resulting query parameters
71			* @return string Completed SQL string
72			*/
73			protected function buildDeleteQuery(SQLDelete $query, array &$parameters) {
74			$sql = $this->buildDeleteFragment($query, $parameters);
75			$sql .= $this->buildFromFragment($query, $parameters);
76			$sql .= $this->buildWhereFragment($query, $parameters);
77			return $sql;
78			}
79
80			/**
81			* Builds a query from a SQLInsert expression
82			*
83			* @param SQLInsert $query The expression object to build from
84			* @param array $parameters Out parameter for the resulting query parameters
85			* @return string Completed SQL string
86			*/
87			protected function buildInsertQuery(SQLInsert $query, array &$parameters) {
88			$nl = $this->getSeparator();
89			$into = $query->getInto();
90
91			// Column identifiers
92			$columns = $query->getColumns();
93			$sql = "INSERT INTO {$into}{$nl}(" . implode(', ', $columns) . ")";
94
95			// Values
96			$sql .= "{$nl}VALUES";
97
98			// Build all rows
99			$rowParts = array();
100			foreach($query->getRows() as $row) {
101			// Build all columns in this row
102			$assignments = $row->getAssignments();
103			// Join SET components together, considering parameters
104			$parts = array();
105			foreach($columns as $column) {
106			// Check if this column has a value for this row
107			if(isset($assignments[$column])) {
108			// Assigment is a single item array, expand with a loop here
109			foreach($assignments[$column] as $assignmentSQL => $assignmentParameters) {
110			$parts[] = $assignmentSQL;
111			$parameters = array_merge($parameters, $assignmentParameters);
112			break;
113			}
114			} else {
115			// This row is missing a value for a column used by another row
116			$parts[] = '?';
117			$parameters[] = null;
118			}
119			}
120			$rowParts[] = '(' . implode(', ', $parts) . ')';
121			}
122			$sql .= $nl . implode(",$nl", $rowParts);
123
124			return $sql;
125			}
126
127			/**
128			* Builds a query from a SQLUpdate expression
129			*
130			* @param SQLUpdate $query The expression object to build from
131			* @param array $parameters Out parameter for the resulting query parameters
132			* @return string Completed SQL string
133			*/
134			protected function buildUpdateQuery(SQLUpdate $query, array &$parameters) {
135			$sql = $this->buildUpdateFragment($query, $parameters);
136			$sql .= $this->buildWhereFragment($query, $parameters);
137			return $sql;
138			}
139
140			/**
141			* Returns the SELECT clauses ready for inserting into a query.
142			*
143			* @param SQLSelect $query The expression object to build from
144			* @param array $parameters Out parameter for the resulting query parameters
145			* @return string Completed select part of statement
146			*/
147			protected function buildSelectFragment(SQLSelect $query, array &$parameters) {
			0 ignored issues – show Unused Code introduced 2017-11-29 12:58 UTC by Report Bug Copy Issue Report The parameter `$parameters` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
148			$distinct = $query->getDistinct();
149			$select = $query->getSelect();
150			$clauses = array();
151
152			foreach ($select as $alias => $field) {
153			// Don't include redundant aliases.
154			$fieldAlias = "\"{$alias}\"";
155			if ($alias === $field \|\| substr($field, -strlen($fieldAlias)) === $fieldAlias) {
156			$clauses[] = $field;
157			} else {
158			$clauses[] = "$field AS $fieldAlias";
159			}
160			}
161
162			$text = 'SELECT ';
163			if ($distinct) $text .= 'DISTINCT ';
164			return $text .= implode(', ', $clauses);
165			}
166
167			/**
168			* Return the DELETE clause ready for inserting into a query.
169			*
170			* @param SQLExpression $query The expression object to build from
171			* @param array $parameters Out parameter for the resulting query parameters
172			* @return string Completed delete part of statement
173			*/
174			public function buildDeleteFragment(SQLDelete $query, array &$parameters) {
			0 ignored issues – show Unused Code introduced 2017-11-29 12:58 UTC by Report Bug Copy Issue Report The parameter `$parameters` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
175			$text = 'DELETE';
176
177			// If doing a multiple table delete then list the target deletion tables here
178			// Note that some schemas don't support multiple table deletion
179			$delete = $query->getDelete();
180			if(!empty($delete)) {
181			$text .= ' ' . implode(', ', $delete);
182			}
183			return $text;
184			}
185
186			/**
187			* Return the UPDATE clause ready for inserting into a query.
188			*
189			* @param SQLExpression $query The expression object to build from
190			* @param array $parameters Out parameter for the resulting query parameters
191			* @return string Completed from part of statement
192			*/
193			public function buildUpdateFragment(SQLUpdate $query, array &$parameters) {
194			$table = $query->getTable();
195			$text = "UPDATE $table";
196
197			// Join SET components together, considering parameters
198			$parts = array();
199			foreach($query->getAssignments() as $column => $assignment) {
200			// Assigment is a single item array, expand with a loop here
201			foreach($assignment as $assignmentSQL => $assignmentParameters) {
202			$parts[] = "$column = $assignmentSQL";
203			$parameters = array_merge($parameters, $assignmentParameters);
204			break;
205			}
206			}
207			$nl = $this->getSeparator();
208			$text .= "{$nl}SET " . implode(', ', $parts);
209			return $text;
210			}
211
212			/**
213			* Return the FROM clause ready for inserting into a query.
214			*
215			* @param SQLExpression $query The expression object to build from
216			* @param array $parameters Out parameter for the resulting query parameters
217			* @return string Completed from part of statement
218			*/
219			public function buildFromFragment(SQLConditionalExpression $query, array &$parameters) {
220			$from = $query->getJoins($joinParameters);
221			$parameters = array_merge($parameters, $joinParameters);
222			$nl = $this->getSeparator();
223			return "{$nl}FROM " . implode(' ', $from);
224			}
225
226			/**
227			* Returns the WHERE clauses ready for inserting into a query.
228			*
229			* @param SQLExpression $query The expression object to build from
230			* @param array $parameters Out parameter for the resulting query parameters
231			* @return string Completed where condition
232			*/
233			public function buildWhereFragment(SQLConditionalExpression $query, array &$parameters) {
234			// Get parameterised elements
235			$where = $query->getWhereParameterised($whereParameters);
236			if(empty($where)) return '';
237
238			// Join conditions
239			$connective = $query->getConnective();
240			$parameters = array_merge($parameters, $whereParameters);
241			$nl = $this->getSeparator();
242			return "{$nl}WHERE (" . implode("){$nl}{$connective} (", $where) . ")";
243			}
244
245			/**
246			* Returns the ORDER BY clauses ready for inserting into a query.
247			*
248			* @param SQLSelect $query The expression object to build from
249			* @param array $parameters Out parameter for the resulting query parameters
250			* @return string Completed order by part of statement
251			*/
252			public function buildOrderByFragment(SQLSelect $query, array &$parameters) {
			0 ignored issues – show Unused Code introduced 2017-11-29 12:58 UTC by Report Bug Copy Issue Report The parameter `$parameters` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
253			$orderBy = $query->getOrderBy();
254			if(empty($orderBy)) return '';
255
256			// Build orders, each with direction considered
257			$statements = array();
258			foreach ($orderBy as $clause => $dir) {
259			$statements[] = trim("$clause $dir");
260			}
261
262			$nl = $this->getSeparator();
263			return "{$nl}ORDER BY " . implode(', ', $statements);
264			}
265
266			/**
267			* Returns the GROUP BY clauses ready for inserting into a query.
268			*
269			* @param SQLSelect $query The expression object to build from
270			* @param array $parameters Out parameter for the resulting query parameters
271			* @return string Completed group part of statement
272			*/
273			public function buildGroupByFragment(SQLSelect $query, array &$parameters) {
			0 ignored issues – show Unused Code introduced 2017-11-29 12:58 UTC by Report Bug Copy Issue Report The parameter `$parameters` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
274			$groupBy = $query->getGroupBy();
275			if(empty($groupBy)) return '';
276
277			$nl = $this->getSeparator();
278			return "{$nl}GROUP BY " . implode(', ', $groupBy);
279			}
280
281			/**
282			* Returns the HAVING clauses ready for inserting into a query.
283			*
284			* @param SQLSelect $query The expression object to build from
285			* @param array $parameters Out parameter for the resulting query parameters
286			* @return string
287			*/
288			public function buildHavingFragment(SQLSelect $query, array &$parameters) {
289			$having = $query->getHavingParameterised($havingParameters);
290			if(empty($having)) return '';
291
292			// Generate having, considering parameters present
293			$connective = $query->getConnective();
294			$parameters = array_merge($parameters, $havingParameters);
295			$nl = $this->getSeparator();
296			return "{$nl}HAVING (" . implode("){$nl}{$connective} (", $having) . ")";
297			}
298
299			/**
300			* Return the LIMIT clause ready for inserting into a query.
301			*
302			* @param SQLSelect $query The expression object to build from
303			* @param array $parameters Out parameter for the resulting query parameters
304			* @return string The finalised limit SQL fragment
305			*/
306			public function buildLimitFragment(SQLSelect $query, array &$parameters) {
			0 ignored issues – show Unused Code introduced 2017-11-29 12:58 UTC by Report Bug Copy Issue Report The parameter `$parameters` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
307			$nl = $this->getSeparator();
308
309			// Ensure limit is given
310			$limit = $query->getLimit();
311			if(empty($limit)) return '';
312
313			// For literal values return this as the limit SQL
314			if (!is_array($limit)) {
315			return "{$nl}LIMIT $limit";
316			}
317
318			// Assert that the array version provides the 'limit' key
319			if (!isset($limit['limit']) \|\| !is_numeric($limit['limit'])) {
320			throw new InvalidArgumentException(
321			'DBQueryBuilder::buildLimitSQL(): Wrong format for $limit: '. var_export($limit, true)
322			);
323			}
324
325			// Format the array limit, given an optional start key
326			$clause = "{$nl}LIMIT {$limit['limit']}";
327			if(isset($limit['start']) && is_numeric($limit['start']) && $limit['start'] !== 0) {
328			$clause .= " OFFSET {$limit['start']}";
329			}
330			return $clause;
331			}
332			}
333

silverstripe / silverstripe-framework

Pull Request — 3 (#8214)

DBQueryBuilder::buildLimitFragment() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like