1 <?php
2 /**
3 * @package Joomla.Platform
4 * @subpackage Database
5 *
6 * @copyright Copyright (C) 2005 - 2017 Open Source Matters, Inc. All rights reserved.
7 * @license GNU General Public License version 2 or later; see LICENSE
8 */
9
10 defined('JPATH_PLATFORM') or die;
11
12 /**
13 * SQLite Query Building Class.
14 *
15 * @since 12.1
16 */
17 class JDatabaseQuerySqlite extends JDatabaseQueryPdo implements JDatabaseQueryPreparable, JDatabaseQueryLimitable
18 {
19 /**
20 * @var integer The offset for the result set.
21 * @since 12.1
22 */
23 protected $offset;
24
25 /**
26 * @var integer The limit for the result set.
27 * @since 12.1
28 */
29 protected $limit;
30
31 /**
32 * @var array Bounded object array
33 * @since 12.1
34 */
35 protected $bounded = array();
36
37 /**
38 * Method to add a variable to an internal array that will be bound to a prepared SQL statement before query execution. Also
39 * removes a variable that has been bounded from the internal bounded array when the passed in value is null.
40 *
41 * @param string|integer $key The key that will be used in your SQL query to reference the value. Usually of
42 * the form ':key', but can also be an integer.
43 * @param mixed &$value The value that will be bound. The value is passed by reference to support output
44 * parameters such as those possible with stored procedures.
45 * @param integer $dataType Constant corresponding to a SQL datatype.
46 * @param integer $length The length of the variable. Usually required for OUTPUT parameters.
47 * @param array $driverOptions Optional driver options to be used.
48 *
49 * @return JDatabaseQuerySqlite
50 *
51 * @since 12.1
52 */
53 public function bind($key = null, &$value = null, $dataType = PDO::PARAM_STR, $length = 0, $driverOptions = array())
54 {
55 // Case 1: Empty Key (reset $bounded array)
56 if (empty($key))
57 {
58 $this->bounded = array();
59
60 return $this;
61 }
62
63 // Case 2: Key Provided, null value (unset key from $bounded array)
64 if (is_null($value))
65 {
66 if (isset($this->bounded[$key]))
67 {
68 unset($this->bounded[$key]);
69 }
70
71 return $this;
72 }
73
74 $obj = new stdClass;
75
76 $obj->value = &$value;
77 $obj->dataType = $dataType;
78 $obj->length = $length;
79 $obj->driverOptions = $driverOptions;
80
81 // Case 3: Simply add the Key/Value into the bounded array
82 $this->bounded[$key] = $obj;
83
84 return $this;
85 }
86
87 /**
88 * Retrieves the bound parameters array when key is null and returns it by reference. If a key is provided then that item is
89 * returned.
90 *
91 * @param mixed $key The bounded variable key to retrieve.
92 *
93 * @return mixed
94 *
95 * @since 12.1
96 */
97 public function &getBounded($key = null)
98 {
99 if (empty($key))
100 {
101 return $this->bounded;
102 }
103 else
104 {
105 if (isset($this->bounded[$key]))
106 {
107 return $this->bounded[$key];
108 }
109 }
110 }
111
112 /**
113 * Gets the number of characters in a string.
114 *
115 * Note, use 'length' to find the number of bytes in a string.
116 *
117 * Usage:
118 * $query->select($query->charLength('a'));
119 *
120 * @param string $field A value.
121 * @param string $operator Comparison operator between charLength integer value and $condition
122 * @param string $condition Integer value to compare charLength with.
123 *
124 * @return string The required char length call.
125 *
126 * @since 13.1
127 */
128 public function charLength($field, $operator = null, $condition = null)
129 {
130 return 'length(' . $field . ')' . (isset($operator) && isset($condition) ? ' ' . $operator . ' ' . $condition : '');
131 }
132
133 /**
134 * Clear data from the query or a specific clause of the query.
135 *
136 * @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
137 *
138 * @return JDatabaseQuerySqlite Returns this object to allow chaining.
139 *
140 * @since 12.1
141 */
142 public function clear($clause = null)
143 {
144 switch ($clause)
145 {
146 case null:
147 $this->bounded = array();
148 break;
149 }
150
151 parent::clear($clause);
152
153 return $this;
154 }
155
156 /**
157 * Concatenates an array of column names or values.
158 *
159 * Usage:
160 * $query->select($query->concatenate(array('a', 'b')));
161 *
162 * @param array $values An array of values to concatenate.
163 * @param string $separator As separator to place between each value.
164 *
165 * @return string The concatenated values.
166 *
167 * @since 11.1
168 */
169 public function concatenate($values, $separator = null)
170 {
171 if ($separator)
172 {
173 return implode(' || ' . $this->quote($separator) . ' || ', $values);
174 }
175 else
176 {
177 return implode(' || ', $values);
178 }
179 }
180
181 /**
182 * Method to modify a query already in string format with the needed
183 * additions to make the query limited to a particular number of
184 * results, or start at a particular offset. This method is used
185 * automatically by the __toString() method if it detects that the
186 * query implements the JDatabaseQueryLimitable interface.
187 *
188 * @param string $query The query in string format
189 * @param integer $limit The limit for the result set
190 * @param integer $offset The offset for the result set
191 *
192 * @return string
193 *
194 * @since 12.1
195 */
196 public function processLimit($query, $limit, $offset = 0)
197 {
198 if ($limit > 0 || $offset > 0)
199 {
200 $query .= ' LIMIT ' . $offset . ', ' . $limit;
201 }
202
203 return $query;
204 }
205
206 /**
207 * Sets the offset and limit for the result set, if the database driver supports it.
208 *
209 * Usage:
210 * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
211 * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)
212 *
213 * @param integer $limit The limit for the result set
214 * @param integer $offset The offset for the result set
215 *
216 * @return JDatabaseQuerySqlite Returns this object to allow chaining.
217 *
218 * @since 12.1
219 */
220 public function setLimit($limit = 0, $offset = 0)
221 {
222 $this->limit = (int) $limit;
223 $this->offset = (int) $offset;
224
225 return $this;
226 }
227
228 /**
229 * Add to the current date and time.
230 * Usage:
231 * $query->select($query->dateAdd());
232 * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
233 *
234 * @param datetime $date The date or datetime to add to
235 * @param string $interval The string representation of the appropriate number of units
236 * @param string $datePart The part of the date to perform the addition on
237 *
238 * @return string The string with the appropriate sql for addition of dates
239 *
240 * @since 13.1
241 * @link http://www.sqlite.org/lang_datefunc.html
242 */
243 public function dateAdd($date, $interval, $datePart)
244 {
245 // SQLite does not support microseconds as a separate unit. Convert the interval to seconds
246 if (strcasecmp($datePart, 'microseconds') == 0)
247 {
248 $interval = .001 * $interval;
249 $datePart = 'seconds';
250 }
251
252 if (substr($interval, 0, 1) != '-')
253 {
254 return "datetime('" . $date . "', '+" . $interval . " " . $datePart . "')";
255 }
256 else
257 {
258 return "datetime('" . $date . "', '" . $interval . " " . $datePart . "')";
259 }
260 }
261
262 /**
263 * Gets the current date and time.
264 *
265 * Usage:
266 * $query->where('published_up < '.$query->currentTimestamp());
267 *
268 * @return string
269 *
270 * @since 3.4
271 */
272 public function currentTimestamp()
273 {
274 return 'CURRENT_TIMESTAMP';
275 }
276
277 /**
278 * Magic function to convert the query to a string.
279 *
280 * @return string The completed query.
281 *
282 * @since 11.1
283 */
284 public function __toString()
285 {
286 switch ($this->type)
287 {
288 case 'select':
289 if ($this->selectRowNumber)
290 {
291 $orderBy = $this->selectRowNumber['orderBy'];
292 $orderColumnAlias = $this->selectRowNumber['orderColumnAlias'];
293
294 $column = "ROW_NUMBER() AS $orderColumnAlias";
295
296 if ($this->select === null)
297 {
298 $query = PHP_EOL . "SELECT 1"
299 . (string) $this->from
300 . (string) $this->where;
301 }
302 else
303 {
304 $tmpOffset = $this->offset;
305 $tmpLimit = $this->limit;
306 $this->offset = 0;
307 $this->limit = 0;
308 $tmpOrder = $this->order;
309 $this->order = null;
310 $query = parent::__toString();
311 $column = "w.*, $column";
312 $this->order = $tmpOrder;
313 $this->offset = $tmpOffset;
314 $this->limit = $tmpLimit;
315 }
316
317 // Special sqlite query to count ROW_NUMBER
318 $query = PHP_EOL . "SELECT $column"
319 . PHP_EOL . "FROM ($query" . PHP_EOL . "ORDER BY $orderBy"
320 . PHP_EOL . ") AS w,(SELECT ROW_NUMBER(0)) AS r"
321 // Forbid to flatten subqueries.
322 . ((string) $this->order ?: PHP_EOL . 'ORDER BY NULL');
323
324 return $this->processLimit($query, $this->limit, $this->offset);
325 }
326
327 break;
328
329 case 'update':
330 if ($this->join)
331 {
332 $table = $this->update->getElements();
333 $table = $table[0];
334
335 $tableName = explode(' ', $table);
336 $tableName = $tableName[0];
337
338 if ($this->columns === null)
339 {
340 $fields = $this->db->getTableColumns($tableName);
341
342 foreach ($fields as $key => $value)
343 {
344 $fields[$key] = $key;
345 }
346
347 $this->columns = new JDatabaseQueryElement('()', $fields);
348 }
349
350 $fields = $this->columns->getElements();
351 $elements = $this->set->getElements();
352
353 foreach ($elements as $nameValue)
354 {
355 $setArray = explode(' = ', $nameValue, 2);
356
357 if ($setArray[0][0] === '`')
358 {
359 // Unquote column name
360 $setArray[0] = substr($setArray[0], 1, -1);
361 }
362
363 $fields[$setArray[0]] = $setArray[1];
364 }
365
366 $select = new JDatabaseQuerySqlite($this->db);
367 $select->select(array_values($fields))
368 ->from($table);
369
370 $select->join = $this->join;
371 $select->where = $this->where;
372
373 return 'INSERT OR REPLACE INTO ' . $tableName
374 . ' (' . implode(',', array_keys($fields)) . ')'
375 . (string) $select;
376 }
377 }
378
379 return parent::__toString();
380 }
381
382 /**
383 * Return the number of the current row.
384 *
385 * @param string $orderBy An expression of ordering for window function.
386 * @param string $orderColumnAlias An alias for new ordering column.
387 *
388 * @return JDatabaseQuery Returns this object to allow chaining.
389 *
390 * @since 3.7.0
391 * @throws RuntimeException
392 */
393 public function selectRowNumber($orderBy, $orderColumnAlias)
394 {
395 $this->validateRowNumber($orderBy, $orderColumnAlias);
396
397 return $this;
398 }
399 }
400