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