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 * Query Building Class.
14 *
15 * @since 11.3
16 */
17 class JDatabaseQueryPostgresql extends JDatabaseQuery implements JDatabaseQueryLimitable
18 {
19 /**
20 * @var object The FOR UPDATE element used in "FOR UPDATE" lock
21 * @since 11.3
22 */
23 protected $forUpdate = null;
24
25 /**
26 * @var object The FOR SHARE element used in "FOR SHARE" lock
27 * @since 11.3
28 */
29 protected $forShare = null;
30
31 /**
32 * @var object The NOWAIT element used in "FOR SHARE" and "FOR UPDATE" lock
33 * @since 11.3
34 */
35 protected $noWait = null;
36
37 /**
38 * @var object The LIMIT element
39 * @since 11.3
40 */
41 protected $limit = null;
42
43 /**
44 * @var object The OFFSET element
45 * @since 11.3
46 */
47 protected $offset = null;
48
49 /**
50 * @var object The RETURNING element of INSERT INTO
51 * @since 11.3
52 */
53 protected $returning = null;
54
55 /**
56 * Magic function to convert the query to a string, only for postgresql specific query
57 *
58 * @return string The completed query.
59 *
60 * @since 11.3
61 */
62 public function __toString()
63 {
64 $query = '';
65
66 switch ($this->type)
67 {
68 case 'select':
69 if ($this->selectRowNumber && $this->selectRowNumber['native'] === false)
70 {
71 // Workaround for postgresql version less than 8.4.0
72 try
73 {
74 $this->db->setQuery('CREATE TEMP SEQUENCE ROW_NUMBER');
75 $this->db->execute();
76 }
77 catch (JDatabaseExceptionExecuting $e)
78 {
79 // Do nothing, sequence exists
80 }
81
82 $orderBy = $this->selectRowNumber['orderBy'];
83 $orderColumnAlias = $this->selectRowNumber['orderColumnAlias'];
84
85 $columns = "nextval('ROW_NUMBER') - 1 AS $orderColumnAlias";
86
87 if ($this->select === null)
88 {
89 $query = PHP_EOL . "SELECT 1"
90 . (string) $this->from
91 . (string) $this->where;
92 }
93 else
94 {
95 $tmpOffset = $this->offset;
96 $tmpLimit = $this->limit;
97 $this->offset = 0;
98 $this->limit = 0;
99 $tmpOrder = $this->order;
100 $this->order = null;
101 $query = parent::__toString();
102 $columns = "w.*, $columns";
103 $this->order = $tmpOrder;
104 $this->offset = $tmpOffset;
105 $this->limit = $tmpLimit;
106 }
107
108 // Add support for second order by, offset and limit
109 $query = PHP_EOL . "SELECT $columns FROM (" . $query . PHP_EOL . "ORDER BY $orderBy"
110 . PHP_EOL . ") w,(SELECT setval('ROW_NUMBER', 1)) AS r";
111
112 if ($this->order)
113 {
114 $query .= (string) $this->order;
115 }
116
117 break;
118 }
119
120 $query .= (string) $this->select;
121 $query .= (string) $this->from;
122
123 if ($this->join)
124 {
125 // Special case for joins
126 foreach ($this->join as $join)
127 {
128 $query .= (string) $join;
129 }
130 }
131
132 if ($this->where)
133 {
134 $query .= (string) $this->where;
135 }
136
137 if ($this->selectRowNumber)
138 {
139 if ($this->order)
140 {
141 $query .= (string) $this->order;
142 }
143
144 break;
145 }
146
147 if ($this->group)
148 {
149 $query .= (string) $this->group;
150 }
151
152 if ($this->having)
153 {
154 $query .= (string) $this->having;
155 }
156
157 if ($this->order)
158 {
159 $query .= (string) $this->order;
160 }
161
162 if ($this->forUpdate)
163 {
164 $query .= (string) $this->forUpdate;
165 }
166 else
167 {
168 if ($this->forShare)
169 {
170 $query .= (string) $this->forShare;
171 }
172 }
173
174 if ($this->noWait)
175 {
176 $query .= (string) $this->noWait;
177 }
178
179 break;
180
181 case 'update':
182 $query .= (string) $this->update;
183 $query .= (string) $this->set;
184
185 if ($this->join)
186 {
187 $tmpFrom = $this->from;
188 $tmpWhere = $this->where ? clone $this->where : null;
189 $this->from = null;
190
191 // Workaround for special case of JOIN with UPDATE
192 foreach ($this->join as $join)
193 {
194 $joinElem = $join->getElements();
195
196 $joinArray = preg_split('/\sON\s/i', $joinElem[0]);
197
198 if (count($joinArray) > 2)
199 {
200 $condition = array_pop($joinArray);
201 $joinArray = array(implode(' ON ', $joinArray), $condition);
202 }
203
204 $this->from($joinArray[0]);
205
206 if (isset($joinArray[1]))
207 {
208 $this->where($joinArray[1]);
209 }
210 }
211
212 $query .= (string) $this->from;
213
214 if ($this->where)
215 {
216 $query .= (string) $this->where;
217 }
218
219 $this->from = $tmpFrom;
220 $this->where = $tmpWhere;
221 }
222 elseif ($this->where)
223 {
224 $query .= (string) $this->where;
225 }
226
227 break;
228
229 case 'insert':
230 $query .= (string) $this->insert;
231
232 if ($this->values)
233 {
234 if ($this->columns)
235 {
236 $query .= (string) $this->columns;
237 }
238
239 $elements = $this->values->getElements();
240
241 if (!($elements[0] instanceof $this))
242 {
243 $query .= ' VALUES ';
244 }
245
246 $query .= (string) $this->values;
247
248 if ($this->returning)
249 {
250 $query .= (string) $this->returning;
251 }
252 }
253
254 break;
255
256 default:
257 $query = parent::__toString();
258 break;
259 }
260
261 if ($this instanceof JDatabaseQueryLimitable)
262 {
263 $query = $this->processLimit($query, $this->limit, $this->offset);
264 }
265
266 return $query;
267 }
268
269 /**
270 * Clear data from the query or a specific clause of the query.
271 *
272 * @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
273 *
274 * @return JDatabaseQueryPostgresql Returns this object to allow chaining.
275 *
276 * @since 11.3
277 */
278 public function clear($clause = null)
279 {
280 switch ($clause)
281 {
282 case 'limit':
283 $this->limit = null;
284 break;
285
286 case 'offset':
287 $this->offset = null;
288 break;
289
290 case 'forUpdate':
291 $this->forUpdate = null;
292 break;
293
294 case 'forShare':
295 $this->forShare = null;
296 break;
297
298 case 'noWait':
299 $this->noWait = null;
300 break;
301
302 case 'returning':
303 $this->returning = null;
304 break;
305
306 case 'select':
307 case 'update':
308 case 'delete':
309 case 'insert':
310 case 'from':
311 case 'join':
312 case 'set':
313 case 'where':
314 case 'group':
315 case 'having':
316 case 'order':
317 case 'columns':
318 case 'values':
319 parent::clear($clause);
320 break;
321
322 default:
323 $this->type = null;
324 $this->limit = null;
325 $this->offset = null;
326 $this->forUpdate = null;
327 $this->forShare = null;
328 $this->noWait = null;
329 $this->returning = null;
330 parent::clear($clause);
331 break;
332 }
333
334 return $this;
335 }
336
337 /**
338 * Casts a value to a char.
339 *
340 * Ensure that the value is properly quoted before passing to the method.
341 *
342 * Usage:
343 * $query->select($query->castAsChar('a'));
344 * $query->select($query->castAsChar('a', 40));
345 *
346 * @param string $value The value to cast as a char.
347 *
348 * @param string $len The lenght of the char.
349 *
350 * @return string Returns the cast value.
351 *
352 * @since 11.3
353 */
354 public function castAsChar($value, $len = null)
355 {
356 if (!$len)
357 {
358 return $value . '::text';
359 }
360 else
361 {
362 return ' CAST(' . $value . ' AS CHAR(' . $len . '))';
363 }
364 }
365
366 /**
367 * Concatenates an array of column names or values.
368 *
369 * Usage:
370 * $query->select($query->concatenate(array('a', 'b')));
371 *
372 * @param array $values An array of values to concatenate.
373 * @param string $separator As separator to place between each value.
374 *
375 * @return string The concatenated values.
376 *
377 * @since 11.3
378 */
379 public function concatenate($values, $separator = null)
380 {
381 if ($separator)
382 {
383 return implode(' || ' . $this->quote($separator) . ' || ', $values);
384 }
385 else
386 {
387 return implode(' || ', $values);
388 }
389 }
390
391 /**
392 * Gets the current date and time.
393 *
394 * @return string Return string used in query to obtain
395 *
396 * @since 11.3
397 */
398 public function currentTimestamp()
399 {
400 return 'NOW()';
401 }
402
403 /**
404 * Sets the FOR UPDATE lock on select's output row
405 *
406 * @param string $table_name The table to lock
407 * @param string $glue The glue by which to join the conditions. Defaults to ',' .
408 *
409 * @return JDatabaseQueryPostgresql FOR UPDATE query element
410 *
411 * @since 11.3
412 */
413 public function forUpdate($table_name, $glue = ',')
414 {
415 $this->type = 'forUpdate';
416
417 if (is_null($this->forUpdate))
418 {
419 $glue = strtoupper($glue);
420 $this->forUpdate = new JDatabaseQueryElement('FOR UPDATE', 'OF ' . $table_name, "$glue ");
421 }
422 else
423 {
424 $this->forUpdate->append($table_name);
425 }
426
427 return $this;
428 }
429
430 /**
431 * Sets the FOR SHARE lock on select's output row
432 *
433 * @param string $table_name The table to lock
434 * @param string $glue The glue by which to join the conditions. Defaults to ',' .
435 *
436 * @return JDatabaseQueryPostgresql FOR SHARE query element
437 *
438 * @since 11.3
439 */
440 public function forShare($table_name, $glue = ',')
441 {
442 $this->type = 'forShare';
443
444 if (is_null($this->forShare))
445 {
446 $glue = strtoupper($glue);
447 $this->forShare = new JDatabaseQueryElement('FOR SHARE', 'OF ' . $table_name, "$glue ");
448 }
449 else
450 {
451 $this->forShare->append($table_name);
452 }
453
454 return $this;
455 }
456
457 /**
458 * Used to get a string to extract year from date column.
459 *
460 * Usage:
461 * $query->select($query->year($query->quoteName('dateColumn')));
462 *
463 * @param string $date Date column containing year to be extracted.
464 *
465 * @return string Returns string to extract year from a date.
466 *
467 * @since 12.1
468 */
469 public function year($date)
470 {
471 return 'EXTRACT (YEAR FROM ' . $date . ')';
472 }
473
474 /**
475 * Used to get a string to extract month from date column.
476 *
477 * Usage:
478 * $query->select($query->month($query->quoteName('dateColumn')));
479 *
480 * @param string $date Date column containing month to be extracted.
481 *
482 * @return string Returns string to extract month from a date.
483 *
484 * @since 12.1
485 */
486 public function month($date)
487 {
488 return 'EXTRACT (MONTH FROM ' . $date . ')';
489 }
490
491 /**
492 * Used to get a string to extract day from date column.
493 *
494 * Usage:
495 * $query->select($query->day($query->quoteName('dateColumn')));
496 *
497 * @param string $date Date column containing day to be extracted.
498 *
499 * @return string Returns string to extract day from a date.
500 *
501 * @since 12.1
502 */
503 public function day($date)
504 {
505 return 'EXTRACT (DAY FROM ' . $date . ')';
506 }
507
508 /**
509 * Used to get a string to extract hour from date column.
510 *
511 * Usage:
512 * $query->select($query->hour($query->quoteName('dateColumn')));
513 *
514 * @param string $date Date column containing hour to be extracted.
515 *
516 * @return string Returns string to extract hour from a date.
517 *
518 * @since 12.1
519 */
520 public function hour($date)
521 {
522 return 'EXTRACT (HOUR FROM ' . $date . ')';
523 }
524
525 /**
526 * Used to get a string to extract minute from date column.
527 *
528 * Usage:
529 * $query->select($query->minute($query->quoteName('dateColumn')));
530 *
531 * @param string $date Date column containing minute to be extracted.
532 *
533 * @return string Returns string to extract minute from a date.
534 *
535 * @since 12.1
536 */
537 public function minute($date)
538 {
539 return 'EXTRACT (MINUTE FROM ' . $date . ')';
540 }
541
542 /**
543 * Used to get a string to extract seconds from date column.
544 *
545 * Usage:
546 * $query->select($query->second($query->quoteName('dateColumn')));
547 *
548 * @param string $date Date column containing second to be extracted.
549 *
550 * @return string Returns string to extract second from a date.
551 *
552 * @since 12.1
553 */
554 public function second($date)
555 {
556 return 'EXTRACT (SECOND FROM ' . $date . ')';
557 }
558
559 /**
560 * Sets the NOWAIT lock on select's output row
561 *
562 * @return JDatabaseQueryPostgresql NO WAIT query element
563 *
564 * @since 11.3
565 */
566 public function noWait ()
567 {
568 $this->type = 'noWait';
569
570 if (is_null($this->noWait))
571 {
572 $this->noWait = new JDatabaseQueryElement('NOWAIT', null);
573 }
574
575 return $this;
576 }
577
578 /**
579 * Set the LIMIT clause to the query
580 *
581 * @param integer $limit An int of how many row will be returned
582 *
583 * @return JDatabaseQueryPostgresql Returns this object to allow chaining.
584 *
585 * @since 11.3
586 */
587 public function limit($limit = 0)
588 {
589 if (is_null($this->limit))
590 {
591 $this->limit = new JDatabaseQueryElement('LIMIT', (int) $limit);
592 }
593
594 return $this;
595 }
596
597 /**
598 * Set the OFFSET clause to the query
599 *
600 * @param integer $offset An int for skipping row
601 *
602 * @return JDatabaseQueryPostgresql Returns this object to allow chaining.
603 *
604 * @since 11.3
605 */
606 public function offset($offset = 0)
607 {
608 if (is_null($this->offset))
609 {
610 $this->offset = new JDatabaseQueryElement('OFFSET', (int) $offset);
611 }
612
613 return $this;
614 }
615
616 /**
617 * Add the RETURNING element to INSERT INTO statement.
618 *
619 * @param mixed $pkCol The name of the primary key column.
620 *
621 * @return JDatabaseQueryPostgresql Returns this object to allow chaining.
622 *
623 * @since 11.3
624 */
625 public function returning($pkCol)
626 {
627 if (is_null($this->returning))
628 {
629 $this->returning = new JDatabaseQueryElement('RETURNING', $pkCol);
630 }
631
632 return $this;
633 }
634
635 /**
636 * Sets the offset and limit for the result set, if the database driver supports it.
637 *
638 * Usage:
639 * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
640 * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)
641 *
642 * @param integer $limit The limit for the result set
643 * @param integer $offset The offset for the result set
644 *
645 * @return JDatabaseQueryPostgresql Returns this object to allow chaining.
646 *
647 * @since 12.1
648 */
649 public function setLimit($limit = 0, $offset = 0)
650 {
651 $this->limit = (int) $limit;
652 $this->offset = (int) $offset;
653
654 return $this;
655 }
656
657 /**
658 * Method to modify a query already in string format with the needed
659 * additions to make the query limited to a particular number of
660 * results, or start at a particular offset.
661 *
662 * @param string $query The query in string format
663 * @param integer $limit The limit for the result set
664 * @param integer $offset The offset for the result set
665 *
666 * @return string
667 *
668 * @since 12.1
669 */
670 public function processLimit($query, $limit, $offset = 0)
671 {
672 if ($limit > 0)
673 {
674 $query .= ' LIMIT ' . $limit;
675 }
676
677 if ($offset > 0)
678 {
679 $query .= ' OFFSET ' . $offset;
680 }
681
682 return $query;
683 }
684
685 /**
686 * Add to the current date and time in Postgresql.
687 * Usage:
688 * $query->select($query->dateAdd());
689 * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
690 *
691 * @param datetime $date The date to add to
692 * @param string $interval The string representation of the appropriate number of units
693 * @param string $datePart The part of the date to perform the addition on
694 *
695 * @return string The string with the appropriate sql for addition of dates
696 *
697 * @since 13.1
698 * @note Not all drivers support all units. Check appropriate references
699 * @link http://www.postgresql.org/docs/9.0/static/functions-datetime.html.
700 */
701 public function dateAdd($date, $interval, $datePart)
702 {
703 if (substr($interval, 0, 1) != '-')
704 {
705 return "timestamp '" . $date . "' + interval '" . $interval . " " . $datePart . "'";
706 }
707 else
708 {
709 return "timestamp '" . $date . "' - interval '" . ltrim($interval, '-') . " " . $datePart . "'";
710 }
711 }
712
713 /**
714 * Return correct regexp operator for Postgresql.
715 *
716 * Ensure that the regexp operator is Postgresql compatible.
717 *
718 * Usage:
719 * $query->where('field ' . $query->regexp($search));
720 *
721 * @param string $value The regex pattern.
722 *
723 * @return string Returns the regex operator.
724 *
725 * @since 11.3
726 */
727 public function regexp($value)
728 {
729 return ' ~* ' . $value;
730 }
731
732 /**
733 * Return correct rand() function for Postgresql.
734 *
735 * Ensure that the rand() function is Postgresql compatible.
736 *
737 * Usage:
738 * $query->Rand();
739 *
740 * @return string The correct rand function.
741 *
742 * @since 3.5
743 */
744 public function Rand()
745 {
746 return ' RANDOM() ';
747 }
748
749 /**
750 * Return the number of the current row.
751 *
752 * @param string $orderBy An expression of ordering for window function.
753 * @param string $orderColumnAlias An alias for new ordering column.
754 *
755 * @return JDatabaseQuery Returns this object to allow chaining.
756 *
757 * @since 3.7.0
758 * @throws RuntimeException
759 */
760 public function selectRowNumber($orderBy, $orderColumnAlias)
761 {
762 $this->validateRowNumber($orderBy, $orderColumnAlias);
763
764 if (version_compare($this->db->getVersion(), '8.4.0') >= 0)
765 {
766 $this->selectRowNumber['native'] = true;
767 $this->select("ROW_NUMBER() OVER (ORDER BY $orderBy) AS $orderColumnAlias");
768 }
769 else
770 {
771 $this->selectRowNumber['native'] = false;
772 }
773
774 return $this;
775 }
776 }
777