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 * SQLite Query Building Class.
17 *
18 * @since 12.1
19 */
20 class FOFDatabaseQuerySqlite extends FOFDatabaseQueryPdo implements FOFDatabaseQueryPreparable, FOFDatabaseQueryLimitable
21 {
22 /**
23 * @var integer The offset for the result set.
24 * @since 12.1
25 */
26 protected $offset;
27
28 /**
29 * @var integer The limit for the result set.
30 * @since 12.1
31 */
32 protected $limit;
33
34 /**
35 * @var array Bounded object array
36 * @since 12.1
37 */
38 protected $bounded = array();
39
40 /**
41 * Method to add a variable to an internal array that will be bound to a prepared SQL statement before query execution. Also
42 * removes a variable that has been bounded from the internal bounded array when the passed in value is null.
43 *
44 * @param string|integer $key The key that will be used in your SQL query to reference the value. Usually of
45 * the form ':key', but can also be an integer.
46 * @param mixed &$value The value that will be bound. The value is passed by reference to support output
47 * parameters such as those possible with stored procedures.
48 * @param integer $dataType Constant corresponding to a SQL datatype.
49 * @param integer $length The length of the variable. Usually required for OUTPUT parameters.
50 * @param array $driverOptions Optional driver options to be used.
51 *
52 * @return FOFDatabaseQuerySqlite
53 *
54 * @since 12.1
55 */
56 public function bind($key = null, &$value = null, $dataType = PDO::PARAM_STR, $length = 0, $driverOptions = array())
57 {
58 // Case 1: Empty Key (reset $bounded array)
59 if (empty($key))
60 {
61 $this->bounded = array();
62
63 return $this;
64 }
65
66 // Case 2: Key Provided, null value (unset key from $bounded array)
67 if (is_null($value))
68 {
69 if (isset($this->bounded[$key]))
70 {
71 unset($this->bounded[$key]);
72 }
73
74 return $this;
75 }
76
77 $obj = new stdClass;
78
79 $obj->value = &$value;
80 $obj->dataType = $dataType;
81 $obj->length = $length;
82 $obj->driverOptions = $driverOptions;
83
84 // Case 3: Simply add the Key/Value into the bounded array
85 $this->bounded[$key] = $obj;
86
87 return $this;
88 }
89
90 /**
91 * Retrieves the bound parameters array when key is null and returns it by reference. If a key is provided then that item is
92 * returned.
93 *
94 * @param mixed $key The bounded variable key to retrieve.
95 *
96 * @return mixed
97 *
98 * @since 12.1
99 */
100 public function &getBounded($key = null)
101 {
102 if (empty($key))
103 {
104 return $this->bounded;
105 }
106 else
107 {
108 if (isset($this->bounded[$key]))
109 {
110 return $this->bounded[$key];
111 }
112 }
113 }
114
115 /**
116 * Gets the number of characters in a string.
117 *
118 * Note, use 'length' to find the number of bytes in a string.
119 *
120 * Usage:
121 * $query->select($query->charLength('a'));
122 *
123 * @param string $field A value.
124 * @param string $operator Comparison operator between charLength integer value and $condition
125 * @param string $condition Integer value to compare charLength with.
126 *
127 * @return string The required char length call.
128 *
129 * @since 13.1
130 */
131 public function charLength($field, $operator = null, $condition = null)
132 {
133 return 'length(' . $field . ')' . (isset($operator) && isset($condition) ? ' ' . $operator . ' ' . $condition : '');
134 }
135
136 /**
137 * Clear data from the query or a specific clause of the query.
138 *
139 * @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
140 *
141 * @return FOFDatabaseQuerySqlite Returns this object to allow chaining.
142 *
143 * @since 12.1
144 */
145 public function clear($clause = null)
146 {
147 switch ($clause)
148 {
149 case null:
150 $this->bounded = array();
151 break;
152 }
153
154 parent::clear($clause);
155
156 return $this;
157 }
158
159 /**
160 * Concatenates an array of column names or values.
161 *
162 * Usage:
163 * $query->select($query->concatenate(array('a', 'b')));
164 *
165 * @param array $values An array of values to concatenate.
166 * @param string $separator As separator to place between each value.
167 *
168 * @return string The concatenated values.
169 *
170 * @since 11.1
171 */
172 public function concatenate($values, $separator = null)
173 {
174 if ($separator)
175 {
176 return implode(' || ' . $this->quote($separator) . ' || ', $values);
177 }
178 else
179 {
180 return implode(' || ', $values);
181 }
182 }
183
184 /**
185 * Method to modify a query already in string format with the needed
186 * additions to make the query limited to a particular number of
187 * results, or start at a particular offset. This method is used
188 * automatically by the __toString() method if it detects that the
189 * query implements the FOFDatabaseQueryLimitable interface.
190 *
191 * @param string $query The query in string format
192 * @param integer $limit The limit for the result set
193 * @param integer $offset The offset for the result set
194 *
195 * @return string
196 *
197 * @since 12.1
198 */
199 public function processLimit($query, $limit, $offset = 0)
200 {
201 if ($limit > 0 || $offset > 0)
202 {
203 $query .= ' LIMIT ' . $offset . ', ' . $limit;
204 }
205
206 return $query;
207 }
208
209 /**
210 * Sets the offset and limit for the result set, if the database driver supports it.
211 *
212 * Usage:
213 * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
214 * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)
215 *
216 * @param integer $limit The limit for the result set
217 * @param integer $offset The offset for the result set
218 *
219 * @return FOFDatabaseQuerySqlite Returns this object to allow chaining.
220 *
221 * @since 12.1
222 */
223 public function setLimit($limit = 0, $offset = 0)
224 {
225 $this->limit = (int) $limit;
226 $this->offset = (int) $offset;
227
228 return $this;
229 }
230
231 /**
232 * Add to the current date and time.
233 * Usage:
234 * $query->select($query->dateAdd());
235 * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
236 *
237 * @param datetime $date The date or datetime to add to
238 * @param string $interval The string representation of the appropriate number of units
239 * @param string $datePart The part of the date to perform the addition on
240 *
241 * @return string The string with the appropriate sql for addition of dates
242 *
243 * @since 13.1
244 * @link http://www.sqlite.org/lang_datefunc.html
245 */
246 public function dateAdd($date, $interval, $datePart)
247 {
248 // SQLite does not support microseconds as a separate unit. Convert the interval to seconds
249 if (strcasecmp($datePart, 'microseconds') == 0)
250 {
251 $interval = .001 * $interval;
252 $datePart = 'seconds';
253 }
254
255 if (substr($interval, 0, 1) != '-')
256 {
257 return "datetime('" . $date . "', '+" . $interval . " " . $datePart . "')";
258 }
259 else
260 {
261 return "datetime('" . $date . "', '" . $interval . " " . $datePart . "')";
262 }
263 }
264
265 /**
266 * Gets the current date and time.
267 *
268 * Usage:
269 * $query->where('published_up < '.$query->currentTimestamp());
270 *
271 * @return string
272 *
273 * @since 3.4
274 */
275 public function currentTimestamp()
276 {
277 return 'CURRENT_TIMESTAMP';
278 }
279 }
280