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