1 <?php
2
3 namespace Alo\Db;
4
5 use Alo\Traversables\ArrayObj;
6
7 if (!defined('GEN_START')) {
8 http_response_code(404);
9 } else {
10
11 /**
12 * SQL resultset handler. The resultset must be in array form, i.e. an array of associative arrays
13 * @author Art <a.molcanovas@gmail.com>
14 */
15 class Resultset extends ArrayObj {
16
17 /**
18 * Defines a modifier as "equals"
19 * @var string
20 */
21 const MOD_EQUALS = '=';
22
23 /**
24 * Defines a modifier as "doesn't equal"
25 * @var string
26 */
27 const MOD_NOT_EQUALS = '!=';
28
29 /**
30 * Defines a modifier as "greater than"
31 * @var string
32 */
33 const MOD_GT = '>';
34
35 /**
36 * Defines a modifier as "greater than or equal to"
37 * @var string
38 */
39 const MOD_GET = '>=';
40
41 /**
42 * Defines a modifier as "lower than"
43 * @var string
44 */
45 const MOD_LT = '<';
46
47 /**
48 * Defines a modifier as "lower than or equal to"
49 * @var string
50 */
51 const MOD_LET = '<=';
52
53 /**
54 * Initialises our resultset handler
55 * @author Art <a.molcanovas@gmail.com>
56 *
57 * @param array $resultset The resultset returned
58 */
59 function __construct(array $resultset) {
60 parent::__construct($resultset);
61 }
62
63 /**
64 * Get values matching the filter
65 * @author Art <a.molcanovas@gmail.com>
66 *
67 * @param array $spec The filter associative array where the keys are column names and the values are
68 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
69 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
70 * 'qux']]
71 * @param int $limit Maximum number of rows to match
72 *
73 * @return array
74 */
75 function getWhere(array $spec, $limit = PHP_INT_MAX) {
76 $count = 0;
77 $return = [];
78
79 foreach ($this->data as $row) {
80 $passed = true;
81 foreach ($spec as $specKey => $specItem) {
82 if (!self::compare(get($row[$specKey]), get($specItem[0]), get($specItem[1]))) {
83 $passed = false;
84 break;
85 }
86 }
87
88 if ($passed) {
89 $return[] = $row;
90 if ((++$count) >= $limit) {
91 break;
92 }
93 }
94 }
95
96 return $return;
97 }
98
99 /**
100 * Checks if a the row column matches the filter
101 * @author Art <a.molcanovas@gmail.com>
102 *
103 * @param mixed $value The value in the row
104 * @param string $modifier The modifier ('=','>', '>=' etc)
105 * @param mixed $modValue What the modifier is checking against
106 *
107 * @return bool
108 */
109 protected static function compare($value, $modifier, $modValue) {
110 switch ($modifier) {
111 case self::MOD_EQUALS:
112 return $value == $modValue;
113 case self::MOD_NOT_EQUALS:
114 return $value != $modValue;
115 case self::MOD_LT:
116 return $value < $modValue;
117 case self::MOD_LET:
118 return $value <= $modValue;
119 case self::MOD_GT:
120 return $value > $modValue;
121 case self::MOD_GET:
122 return $value >= $modValue;
123 default:
124 return false;
125 }
126 }
127
128 /**
129 * Only keeps vallues in the resultset that match the filter
130 * @author Art <a.molcanovas@gmail.com>
131 *
132 * @param array $spec The filter associative array where the keys are column names and the values are
133 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
134 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
135 * 'qux']]
136 * @param int $limit Maximum number of rows to match
137 *
138 * @return Resultset
139 */
140 function keepWhere(array $spec, $limit = PHP_INT_MAX) {
141 $this->data = $this->getWhere($spec, $limit);
142
143 return $this;
144 }
145
146 /**
147 * Deletes values from the resultset that match the filter
148 * @author Art <a.molcanovas@gmail.com>
149 *
150 * @param array $spec The filter associative array where the keys are column names and the values are
151 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
152 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
153 * 'qux']]
154 *
155 * @return Resultset
156 */
157 function deleteWhere(array $spec) {
158 $get = $this->getWhere($spec);
159
160 foreach ($this->data as $k => $row) {
161 $keep = true;
162
163 foreach ($get as $getRow) {
164 if ($row === $getRow) {
165 $keep = false;
166 break;
167 }
168 }
169
170 if (!$keep) {
171 unset($this->data[$k]);
172 }
173 }
174
175 return $this;
176 }
177
178 /**
179 * A clone of getWhere() that returns references to the rows in the dataset instead of a new array
180 * @author Art <a.molcanovas@gmail.com>
181 *
182 * @param array $spec The filter associative array where the keys are column names and the values are
183 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
184 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
185 * 'qux']]
186 *
187 * @return array
188 */
189 protected function &getWhereReferential(array $spec) {
190 $return = [];
191
192 foreach ($this->data as &$row) {
193 $passed = true;
194 foreach ($spec as $specKey => $specItem) {
195 if (!self::compare(get($row[$specKey]), get($specItem[0]), get($specItem[1]))) {
196 $passed = false;
197 break;
198 }
199 }
200
201 if ($passed) {
202 $return[] = &$row;
203 }
204 }
205
206 return $return;
207 }
208
209 /**
210 * Sets the value(s) on columns that match the filter
211 * @author Art <a.molcanovas@gmail.com>
212 *
213 * @param array $sets Array of values to set where the keys are the column names and the values are the
214 * values to set
215 * @param array $where The filter associative array where the keys are column names and the values are
216 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
217 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
218 * 'qux']]
219 * @param int $limit Maximum number of rows to modify
220 *
221 * @return Resultset
222 */
223 function setValue(array $sets, array $where = null, $limit = PHP_INT_MAX) {
224 if ($where) {
225 $get = &$this->getWhereReferential($where, $limit);
226 } else {
227 $get = &$this->data;
228 }
229
230 foreach ($get as &$row) {
231 foreach ($sets as $k => $v) {
232 $row[$k] = $v;
233 }
234 }
235
236 return $this;
237 }
238
239 /**
240 * Applies a callback to the rows matching the filter. This will modify the data in the object.
241 * @author Art <a.molcanovas@gmail.com>
242 *
243 * @param callable $callable The callback function. It should accept the first parameter which is the
244 * array of associative arrays that matches the filter.
245 * @param array $where
246 *
247 * @return bool|mixed false if $callable isn't callable or whatever your callback returns.
248 */
249 function applyCallbackWhere($callable, $where = null) {
250 if (!is_callable($callable)) {
251 phpWarning('The supplied callback isn\'t callable');
252
253 return false;
254 } else {
255 if ($where) {
256 $get = &$this->getWhereReferential($where);
257 } else {
258 $get = &$this->data;
259 }
260
261 return call_user_func($callable, $get);
262 }
263 }
264
265 /**
266 * Appends values in the resultset
267 * @author Art <a.molcanovas@gmail.com>
268 *
269 * @param array $sets Array of values to append where the keys are the column names and the values are
270 * the strings to append
271 * @param array $where The filter associative array where the keys are column names and the values are
272 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
273 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
274 * 'qux']]
275 * @param int $limit Maxumum number of affected rows
276 *
277 * @return Resultset
278 */
279 function appendValue(array $sets, array $where = null, $limit = PHP_INT_MAX) {
280 if ($where) {
281 $get = &$this->getWhereReferential($where, $limit);
282 } else {
283 $get = &$this->data;
284 }
285
286 foreach ($get as &$row) {
287 foreach ($sets as $k => $v) {
288 $row[$k] = $row[$k] . $v;
289 }
290 }
291
292 return $this;
293 }
294
295 /**
296 * Prepends values in the resultset
297 * @author Art <a.molcanovas@gmail.com>
298 *
299 * @param array $sets Array of values to prepend where the keys are the column names and the values are
300 * the strings to prepend
301 * @param array $where The filter associative array where the keys are column names and the values are
302 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
303 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
304 * 'qux']]
305 * @param int $limit Maximum number of affected rows
306 *
307 * @return Resultset
308 */
309 function prependValue(array $sets, array $where = null, $limit = PHP_INT_MAX) {
310 if ($where) {
311 $get = &$this->getWhereReferential($where, $limit);
312 } else {
313 $get = &$this->data;
314 }
315
316 foreach ($get as &$row) {
317 foreach ($sets as $k => $v) {
318 $row[$k] = $v . $row[$k];
319 }
320 }
321
322 return $this;
323 }
324
325 /**
326 * Increments a numeric value(or values) in the resultset
327 * @author Art <a.molcanovas@gmail.com>
328 *
329 * @param array $sets The increment specs, where the keys are the columns to modify and the values are
330 * how much to increment by
331 * @param array $where The filter associative array where the keys are column names and the values are
332 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
333 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
334 * 'qux']]
335 * @param int $limit Maxumum number of affected rows
336 *
337 * @return Resultset
338 */
339 function incrementValue(array $sets, array $where = null, $limit = PHP_INT_MAX) {
340 if ($where) {
341 $get = &$this->getWhereReferential($where, $limit);
342 } else {
343 $get = &$this->data;
344 }
345
346 foreach ($get as &$row) {
347 foreach ($sets as $k => $v) {
348 if (is_numeric($row[$k])) {
349 $row[$k] += $v;
350 }
351 }
352 }
353
354 return $this;
355 }
356
357 /**
358 * Decrements a numeric value(or values) in the resultset
359 * @author Art <a.molcanovas@gmail.com>
360 *
361 * @param array $sets The decrement specs where the keys are the columns to modify and the values are how
362 * much to decrement by
363 * @param array $where The filter associative array where the keys are column names and the values are
364 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
365 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
366 * 'qux']]
367 * @param int $limit Maximum number of affected rows
368 *
369 * @return Resultset
370 */
371 function decrementValue(array $sets, array $where = null, $limit = PHP_INT_MAX) {
372 if ($where) {
373 $get = &$this->getWhereReferential($where, $limit);
374 } else {
375 $get = &$this->data;
376 }
377
378 foreach ($get as &$row) {
379 foreach ($sets as $k => $v) {
380 if (is_numeric($row[$k])) {
381 $row[$k] -= $v;
382 }
383 }
384 }
385
386 return $this;
387 }
388
389 /**
390 * Multiplies numeric values in the resultset
391 * @author Art <a.molcanovas@gmail.com>
392 *
393 * @param array $sets The multiplication specs where the keys are columns to modify and the values are
394 * how much to multiply by
395 * @param array $where The filter associative array where the keys are column names and the values are
396 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
397 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
398 * 'qux']]
399 * @param int $limit Maximum number of affected rows
400 *
401 * @return Resultset
402 */
403 function multiplyValue(array $sets, array $where = null, $limit = PHP_INT_MAX) {
404 if ($where) {
405 $get = &$this->getWhereReferential($where, $limit);
406 } else {
407 $get = &$this->data;
408 }
409
410 foreach ($get as &$row) {
411 foreach ($sets as $k => $v) {
412 if (is_numeric($row[$k])) {
413 $row[$k] *= $v;
414 }
415 }
416 }
417
418 return $this;
419 }
420
421 /**
422 * Divides numeric values in the resultset
423 * @author Art <a.molcanovas@gmail.com>
424 *
425 * @param array $sets The division specs where the keys are columns to modify and the values are how much
426 * divide by
427 * @param array $where The filter associative array where the keys are column names and the values are
428 * [modifier,value]. For example to get rows where the column "foo" is greater than 5
429 * and the column "bar" equals "qux" pass the array ['foo' => ['>',5], 'bar' => ['=',
430 * 'qux']]
431 * @param int $limit Maximum number of affected rows
432 *
433 * @return Resultset
434 */
435 function divideValue(array $sets, array $where = null, $limit = PHP_INT_MAX) {
436 if ($where) {
437 $get = &$this->getWhereReferential($where, $limit);
438 } else {
439 $get = &$this->data;
440 }
441
442 foreach ($get as &$row) {
443 foreach ($sets as $k => $v) {
444 if (is_numeric($row[$k])) {
445 $row[$k] /= $v;
446 }
447 }
448 }
449
450 return $this;
451 }
452 }
453 }
454