/libraries/joomla/database/query/sqlsrv.php

  1<?php
  2/**
  3 * @package     Joomla.Platform
  4 * @subpackage  Database
  5 *
  6 * @copyright   Copyright (C) 2005 - 2015 Open Source Matters, Inc. All rights reserved.
  7 * @license     GNU General Public License version 2 or later; see LICENSE
  8 */
  9
 10defined('JPATH_PLATFORM') or die;
 11
 12/**
 13 * Query Building Class.
 14 *
 15 * @since  11.1
 16 */
 17class JDatabaseQuerySqlsrv extends JDatabaseQuery implements JDatabaseQueryLimitable
 18{
 19	/**
 20	 * The character(s) used to quote SQL statement names such as table names or field names,
 21	 * etc.  The child classes should define this as necessary.  If a single character string the
 22	 * same character is used for both sides of the quoted name, else the first character will be
 23	 * used for the opening quote and the second for the closing quote.
 24	 *
 25	 * @var    string
 26	 * @since  11.1
 27	 */
 28	protected $name_quotes = '`';
 29
 30	/**
 31	 * The null or zero representation of a timestamp for the database driver.  This should be
 32	 * defined in child classes to hold the appropriate value for the engine.
 33	 *
 34	 * @var    string
 35	 * @since  11.1
 36	 */
 37	protected $null_date = '1900-01-01 00:00:00';
 38
 39	/**
 40	 * @var    integer  The affected row limit for the current SQL statement.
 41	 * @since  3.2 CMS
 42	 */
 43	protected $limit = 0;
 44
 45	/**
 46	 * @var    integer  The affected row offset to apply for the current SQL statement.
 47	 * @since  3.2 CMS
 48	 */
 49	protected $offset = 0;
 50
 51	/**
 52	 * Magic function to convert the query to a string.
 53	 *
 54	 * @return  string	The completed query.
 55	 *
 56	 * @since   11.1
 57	 */
 58	public function __toString()
 59	{
 60		$query = '';
 61
 62		switch ($this->type)
 63		{
 64			case 'select':
 65				$query .= (string) $this->select;
 66				$query .= (string) $this->from;
 67
 68				if ($this->join)
 69				{
 70					// Special case for joins
 71					foreach ($this->join as $join)
 72					{
 73						$query .= (string) $join;
 74					}
 75				}
 76
 77				if ($this->where)
 78				{
 79					$query .= (string) $this->where;
 80				}
 81
 82				if ($this->group)
 83				{
 84					$query .= (string) $this->group;
 85				}
 86
 87				if ($this->order)
 88				{
 89					$query .= (string) $this->order;
 90				}
 91
 92				if ($this->having)
 93				{
 94					$query .= (string) $this->having;
 95				}
 96
 97				if ($this instanceof JDatabaseQueryLimitable && ($this->limit > 0 || $this->offset > 0))
 98				{
 99					$query = $this->processLimit($query, $this->limit, $this->offset);
100				}
101
102				break;
103
104			case 'insert':
105				$query .= (string) $this->insert;
106
107				// Set method
108				if ($this->set)
109				{
110					$query .= (string) $this->set;
111				}
112				// Columns-Values method
113				elseif ($this->values)
114				{
115					if ($this->columns)
116					{
117						$query .= (string) $this->columns;
118					}
119
120					$elements = $this->insert->getElements();
121					$tableName = array_shift($elements);
122
123					$query .= 'VALUES ';
124					$query .= (string) $this->values;
125
126					if ($this->autoIncrementField)
127					{
128						$query = 'SET IDENTITY_INSERT ' . $tableName . ' ON;' . $query . 'SET IDENTITY_INSERT ' . $tableName . ' OFF;';
129					}
130
131					if ($this->where)
132					{
133						$query .= (string) $this->where;
134					}
135				}
136
137				break;
138
139			case 'delete':
140				$query .= (string) $this->delete;
141				$query .= (string) $this->from;
142
143				if ($this->join)
144				{
145					// Special case for joins
146					foreach ($this->join as $join)
147					{
148						$query .= (string) $join;
149					}
150				}
151
152				if ($this->where)
153				{
154					$query .= (string) $this->where;
155				}
156
157				if ($this->order)
158				{
159					$query .= (string) $this->order;
160				}
161
162				break;
163
164			case 'update':
165				$query .= (string) $this->update;
166
167				if ($this->join)
168				{
169					// Special case for joins
170					foreach ($this->join as $join)
171					{
172						$query .= (string) $join;
173					}
174				}
175
176				$query .= (string) $this->set;
177
178				if ($this->where)
179				{
180					$query .= (string) $this->where;
181				}
182
183				if ($this->order)
184				{
185					$query .= (string) $this->order;
186				}
187
188				break;
189
190			default:
191				$query = parent::__toString();
192				break;
193		}
194
195		return $query;
196	}
197
198	/**
199	 * Casts a value to a char.
200	 *
201	 * Ensure that the value is properly quoted before passing to the method.
202	 *
203	 * @param   string  $value  The value to cast as a char.
204	 *
205	 * @return  string  Returns the cast value.
206	 *
207	 * @since   11.1
208	 */
209	public function castAsChar($value)
210	{
211		return 'CAST(' . $value . ' as NVARCHAR(10))';
212	}
213
214	/**
215	 * Gets the function to determine the length of a character string.
216	 *
217	 * @param   string  $field      A value.
218	 * @param   string  $operator   Comparison operator between charLength integer value and $condition
219	 * @param   string  $condition  Integer value to compare charLength with.
220	 *
221	 * @return  string  The required char length call.
222	 *
223	 * @since 11.1
224	 */
225	public function charLength($field, $operator = null, $condition = null)
226	{
227		return 'DATALENGTH(' . $field . ')' . (isset($operator) && isset($condition) ? ' ' . $operator . ' ' . $condition : '');
228	}
229
230	/**
231	 * Concatenates an array of column names or values.
232	 *
233	 * @param   array   $values     An array of values to concatenate.
234	 * @param   string  $separator  As separator to place between each value.
235	 *
236	 * @return  string  The concatenated values.
237	 *
238	 * @since   11.1
239	 */
240	public function concatenate($values, $separator = null)
241	{
242		if ($separator)
243		{
244			return '(' . implode('+' . $this->quote($separator) . '+', $values) . ')';
245		}
246		else
247		{
248			return '(' . implode('+', $values) . ')';
249		}
250	}
251
252	/**
253	 * Gets the current date and time.
254	 *
255	 * @return  string
256	 *
257	 * @since   11.1
258	 */
259	public function currentTimestamp()
260	{
261		return 'GETDATE()';
262	}
263
264	/**
265	 * Get the length of a string in bytes.
266	 *
267	 * @param   string  $value  The string to measure.
268	 *
269	 * @return  integer
270	 *
271	 * @since   11.1
272	 */
273	public function length($value)
274	{
275		return 'LEN(' . $value . ')';
276	}
277
278	/**
279	 * Add to the current date and time.
280	 * Usage:
281	 * $query->select($query->dateAdd());
282	 * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
283	 *
284	 * @param   datetime  $date      The date to add to; type may be time or datetime.
285	 * @param   string    $interval  The string representation of the appropriate number of units
286	 * @param   string    $datePart  The part of the date to perform the addition on
287	 *
288	 * @return  string  The string with the appropriate sql for addition of dates
289	 *
290	 * @since   13.1
291	 * @note    Not all drivers support all units.
292	 * @link    http://msdn.microsoft.com/en-us/library/ms186819.aspx for more information
293	 */
294	public function dateAdd($date, $interval, $datePart)
295	{
296		return "DATEADD('" . $datePart . "', '" . $interval . "', '" . $date . "'" . ')';
297	}
298
299	/**
300	 * Method to modify a query already in string format with the needed
301	 * additions to make the query limited to a particular number of
302	 * results, or start at a particular offset.
303	 *
304	 * @param   string   $query   The query in string format
305	 * @param   integer  $limit   The limit for the result set
306	 * @param   integer  $offset  The offset for the result set
307	 *
308	 * @return  string
309	 *
310	 * @since   12.1
311	 */
312	public function processLimit($query, $limit, $offset = 0)
313	{
314		if ($limit == 0 && $offset == 0)
315		{
316			return $query;
317		}
318
319		$start = $offset + 1;
320		$end   = $offset + $limit;
321
322		$orderBy = stristr($query, 'ORDER BY');
323
324		if (is_null($orderBy) || empty($orderBy))
325		{
326			$orderBy = 'ORDER BY (select 0)';
327		}
328
329		$query = str_ireplace($orderBy, '', $query);
330
331		$rowNumberText = ', ROW_NUMBER() OVER (' . $orderBy . ') AS RowNumber FROM ';
332
333		$query = preg_replace('/\sFROM\s/i', $rowNumberText, $query, 1);
334		$query = 'SELECT * FROM (' . $query . ') A WHERE A.RowNumber BETWEEN ' . $start . ' AND ' . $end;
335
336		return $query;
337	}
338
339	/**
340	 * Sets the offset and limit for the result set, if the database driver supports it.
341	 *
342	 * Usage:
343	 * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
344	 * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)
345	 *
346	 * @param   integer  $limit   The limit for the result set
347	 * @param   integer  $offset  The offset for the result set
348	 *
349	 * @return  JDatabaseQuery  Returns this object to allow chaining.
350	 *
351	 * @since   12.1
352	 */
353	public function setLimit($limit = 0, $offset = 0)
354	{
355		$this->limit  = (int) $limit;
356		$this->offset = (int) $offset;
357
358		return $this;
359	}
360}