PageRenderTime 53ms CodeModel.GetById 39ms app.highlight 9ms RepoModel.GetById 2ms app.codeStats 0ms

/libraries/joomla/database/query/postgresql.php

https://gitlab.com/vitaliylukin91/text
PHP | 641 lines | 295 code | 75 blank | 271 comment | 28 complexity | 616c2d2b5c2ce02107b605a4a636b693 MD5 | raw file
  1<?php
  2/**
  3 * @package     Joomla.Platform
  4 * @subpackage  Database
  5 *
  6 * @copyright   Copyright (C) 2005 - 2015 Open Source Matters, Inc. All rights reserved.
  7 * @license     GNU General Public License version 2 or later; see LICENSE
  8 */
  9
 10defined('JPATH_PLATFORM') or die;
 11
 12/**
 13 * Query Building Class.
 14 *
 15 * @since  11.3
 16 */
 17class 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				$query .= (string) $this->select;
 70				$query .= (string) $this->from;
 71
 72				if ($this->join)
 73				{
 74					// Special case for joins
 75					foreach ($this->join as $join)
 76					{
 77						$query .= (string) $join;
 78					}
 79				}
 80
 81				if ($this->where)
 82				{
 83					$query .= (string) $this->where;
 84				}
 85
 86				if ($this->group)
 87				{
 88					$query .= (string) $this->group;
 89				}
 90
 91				if ($this->having)
 92				{
 93					$query .= (string) $this->having;
 94				}
 95
 96				if ($this->order)
 97				{
 98					$query .= (string) $this->order;
 99				}
100
101				if ($this->forUpdate)
102				{
103					$query .= (string) $this->forUpdate;
104				}
105				else
106				{
107					if ($this->forShare)
108					{
109						$query .= (string) $this->forShare;
110					}
111				}
112
113				if ($this->noWait)
114				{
115					$query .= (string) $this->noWait;
116				}
117
118				break;
119
120			case 'update':
121				$query .= (string) $this->update;
122				$query .= (string) $this->set;
123
124				if ($this->join)
125				{
126					$onWord = ' ON ';
127
128					// Workaround for special case of JOIN with UPDATE
129					foreach ($this->join as $join)
130					{
131						$joinElem = $join->getElements();
132
133						$joinArray = explode($onWord, $joinElem[0]);
134
135						$this->from($joinArray[0]);
136						$this->where($joinArray[1]);
137					}
138
139					$query .= (string) $this->from;
140				}
141
142				if ($this->where)
143				{
144					$query .= (string) $this->where;
145				}
146
147				break;
148
149			case 'insert':
150				$query .= (string) $this->insert;
151
152				if ($this->values)
153				{
154					if ($this->columns)
155					{
156						$query .= (string) $this->columns;
157					}
158
159					$elements = $this->values->getElements();
160
161					if (!($elements[0] instanceof $this))
162					{
163						$query .= ' VALUES ';
164					}
165
166					$query .= (string) $this->values;
167
168					if ($this->returning)
169					{
170						$query .= (string) $this->returning;
171					}
172				}
173
174				break;
175
176			default:
177				$query = parent::__toString();
178				break;
179		}
180
181		if ($this instanceof JDatabaseQueryLimitable)
182		{
183			$query = $this->processLimit($query, $this->limit, $this->offset);
184		}
185
186		return $query;
187	}
188
189	/**
190	 * Clear data from the query or a specific clause of the query.
191	 *
192	 * @param   string  $clause  Optionally, the name of the clause to clear, or nothing to clear the whole query.
193	 *
194	 * @return  JDatabaseQueryPostgresql  Returns this object to allow chaining.
195	 *
196	 * @since   11.3
197	 */
198	public function clear($clause = null)
199	{
200		switch ($clause)
201		{
202			case 'limit':
203				$this->limit = null;
204				break;
205
206			case 'offset':
207				$this->offset = null;
208				break;
209
210			case 'forUpdate':
211				$this->forUpdate = null;
212				break;
213
214			case 'forShare':
215				$this->forShare = null;
216				break;
217
218			case 'noWait':
219				$this->noWait = null;
220				break;
221
222			case 'returning':
223				$this->returning = null;
224				break;
225
226			case 'select':
227			case 'update':
228			case 'delete':
229			case 'insert':
230			case 'from':
231			case 'join':
232			case 'set':
233			case 'where':
234			case 'group':
235			case 'having':
236			case 'order':
237			case 'columns':
238			case 'values':
239				parent::clear($clause);
240				break;
241
242			default:
243				$this->type = null;
244				$this->limit = null;
245				$this->offset = null;
246				$this->forUpdate = null;
247				$this->forShare = null;
248				$this->noWait = null;
249				$this->returning = null;
250				parent::clear($clause);
251				break;
252		}
253
254		return $this;
255	}
256
257	/**
258	 * Casts a value to a char.
259	 *
260	 * Ensure that the value is properly quoted before passing to the method.
261	 *
262	 * Usage:
263	 * $query->select($query->castAsChar('a'));
264	 *
265	 * @param   string  $value  The value to cast as a char.
266	 *
267	 * @return  string  Returns the cast value.
268	 *
269	 * @since   11.3
270	 */
271	public function castAsChar($value)
272	{
273		return $value . '::text';
274	}
275
276	/**
277	 * Concatenates an array of column names or values.
278	 *
279	 * Usage:
280	 * $query->select($query->concatenate(array('a', 'b')));
281	 *
282	 * @param   array   $values     An array of values to concatenate.
283	 * @param   string  $separator  As separator to place between each value.
284	 *
285	 * @return  string  The concatenated values.
286	 *
287	 * @since   11.3
288	 */
289	public function concatenate($values, $separator = null)
290	{
291		if ($separator)
292		{
293			return implode(' || ' . $this->quote($separator) . ' || ', $values);
294		}
295		else
296		{
297			return implode(' || ', $values);
298		}
299	}
300
301	/**
302	 * Gets the current date and time.
303	 *
304	 * @return  string  Return string used in query to obtain
305	 *
306	 * @since   11.3
307	 */
308	public function currentTimestamp()
309	{
310		return 'NOW()';
311	}
312
313	/**
314	 * Sets the FOR UPDATE lock on select's output row
315	 *
316	 * @param   string  $table_name  The table to lock
317	 * @param   string  $glue        The glue by which to join the conditions. Defaults to ',' .
318	 *
319	 * @return  JDatabaseQueryPostgresql  FOR UPDATE query element
320	 *
321	 * @since   11.3
322	 */
323	public function forUpdate($table_name, $glue = ',')
324	{
325		$this->type = 'forUpdate';
326
327		if (is_null($this->forUpdate))
328		{
329			$glue            = strtoupper($glue);
330			$this->forUpdate = new JDatabaseQueryElement('FOR UPDATE', 'OF ' . $table_name, "$glue ");
331		}
332		else
333		{
334			$this->forUpdate->append($table_name);
335		}
336
337		return $this;
338	}
339
340	/**
341	 * Sets the FOR SHARE lock on select's output row
342	 *
343	 * @param   string  $table_name  The table to lock
344	 * @param   string  $glue        The glue by which to join the conditions. Defaults to ',' .
345	 *
346	 * @return  JDatabaseQueryPostgresql  FOR SHARE query element
347	 *
348	 * @since   11.3
349	 */
350	public function forShare($table_name, $glue = ',')
351	{
352		$this->type = 'forShare';
353
354		if (is_null($this->forShare))
355		{
356			$glue           = strtoupper($glue);
357			$this->forShare = new JDatabaseQueryElement('FOR SHARE', 'OF ' . $table_name, "$glue ");
358		}
359		else
360		{
361			$this->forShare->append($table_name);
362		}
363
364		return $this;
365	}
366
367	/**
368	 * Used to get a string to extract year from date column.
369	 *
370	 * Usage:
371	 * $query->select($query->year($query->quoteName('dateColumn')));
372	 *
373	 * @param   string  $date  Date column containing year to be extracted.
374	 *
375	 * @return  string  Returns string to extract year from a date.
376	 *
377	 * @since   12.1
378	 */
379	public function year($date)
380	{
381		return 'EXTRACT (YEAR FROM ' . $date . ')';
382	}
383
384	/**
385	 * Used to get a string to extract month from date column.
386	 *
387	 * Usage:
388	 * $query->select($query->month($query->quoteName('dateColumn')));
389	 *
390	 * @param   string  $date  Date column containing month to be extracted.
391	 *
392	 * @return  string  Returns string to extract month from a date.
393	 *
394	 * @since   12.1
395	 */
396	public function month($date)
397	{
398		return 'EXTRACT (MONTH FROM ' . $date . ')';
399	}
400
401	/**
402	 * Used to get a string to extract day from date column.
403	 *
404	 * Usage:
405	 * $query->select($query->day($query->quoteName('dateColumn')));
406	 *
407	 * @param   string  $date  Date column containing day to be extracted.
408	 *
409	 * @return  string  Returns string to extract day from a date.
410	 *
411	 * @since   12.1
412	 */
413	public function day($date)
414	{
415		return 'EXTRACT (DAY FROM ' . $date . ')';
416	}
417
418	/**
419	 * Used to get a string to extract hour from date column.
420	 *
421	 * Usage:
422	 * $query->select($query->hour($query->quoteName('dateColumn')));
423	 *
424	 * @param   string  $date  Date column containing hour to be extracted.
425	 *
426	 * @return  string  Returns string to extract hour from a date.
427	 *
428	 * @since   12.1
429	 */
430	public function hour($date)
431	{
432		return 'EXTRACT (HOUR FROM ' . $date . ')';
433	}
434
435	/**
436	 * Used to get a string to extract minute from date column.
437	 *
438	 * Usage:
439	 * $query->select($query->minute($query->quoteName('dateColumn')));
440	 *
441	 * @param   string  $date  Date column containing minute to be extracted.
442	 *
443	 * @return  string  Returns string to extract minute from a date.
444	 *
445	 * @since   12.1
446	 */
447	public function minute($date)
448	{
449		return 'EXTRACT (MINUTE FROM ' . $date . ')';
450	}
451
452	/**
453	 * Used to get a string to extract seconds from date column.
454	 *
455	 * Usage:
456	 * $query->select($query->second($query->quoteName('dateColumn')));
457	 *
458	 * @param   string  $date  Date column containing second to be extracted.
459	 *
460	 * @return  string  Returns string to extract second from a date.
461	 *
462	 * @since   12.1
463	 */
464	public function second($date)
465	{
466		return 'EXTRACT (SECOND FROM ' . $date . ')';
467	}
468
469	/**
470	 * Sets the NOWAIT lock on select's output row
471	 *
472	 * @return  JDatabaseQueryPostgresql  NO WAIT query element
473	 *
474	 * @since   11.3
475	 */
476	public function noWait ()
477	{
478		$this->type = 'noWait';
479
480		if (is_null($this->noWait))
481		{
482			$this->noWait = new JDatabaseQueryElement('NOWAIT', null);
483		}
484
485		return $this;
486	}
487
488	/**
489	 * Set the LIMIT clause to the query
490	 *
491	 * @param   integer  $limit  An int of how many row will be returned
492	 *
493	 * @return  JDatabaseQueryPostgresql  Returns this object to allow chaining.
494	 *
495	 * @since   11.3
496	 */
497	public function limit($limit = 0)
498	{
499		if (is_null($this->limit))
500		{
501			$this->limit = new JDatabaseQueryElement('LIMIT', (int) $limit);
502		}
503
504		return $this;
505	}
506
507	/**
508	 * Set the OFFSET clause to the query
509	 *
510	 * @param   integer  $offset  An int for skipping row
511	 *
512	 * @return  JDatabaseQueryPostgresql  Returns this object to allow chaining.
513	 *
514	 * @since   11.3
515	 */
516	public function offset($offset = 0)
517	{
518		if (is_null($this->offset))
519		{
520			$this->offset = new JDatabaseQueryElement('OFFSET', (int) $offset);
521		}
522
523		return $this;
524	}
525
526	/**
527	 * Add the RETURNING element to INSERT INTO statement.
528	 *
529	 * @param   mixed  $pkCol  The name of the primary key column.
530	 *
531	 * @return  JDatabaseQueryPostgresql  Returns this object to allow chaining.
532	 *
533	 * @since   11.3
534	 */
535	public function returning($pkCol)
536	{
537		if (is_null($this->returning))
538		{
539			$this->returning = new JDatabaseQueryElement('RETURNING', $pkCol);
540		}
541
542		return $this;
543	}
544
545	/**
546	 * Sets the offset and limit for the result set, if the database driver supports it.
547	 *
548	 * Usage:
549	 * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
550	 * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)
551	 *
552	 * @param   integer  $limit   The limit for the result set
553	 * @param   integer  $offset  The offset for the result set
554	 *
555	 * @return  JDatabaseQueryPostgresql  Returns this object to allow chaining.
556	 *
557	 * @since   12.1
558	 */
559	public function setLimit($limit = 0, $offset = 0)
560	{
561		$this->limit  = (int) $limit;
562		$this->offset = (int) $offset;
563
564		return $this;
565	}
566
567	/**
568	 * Method to modify a query already in string format with the needed
569	 * additions to make the query limited to a particular number of
570	 * results, or start at a particular offset.
571	 *
572	 * @param   string   $query   The query in string format
573	 * @param   integer  $limit   The limit for the result set
574	 * @param   integer  $offset  The offset for the result set
575	 *
576	 * @return  string
577	 *
578	 * @since   12.1
579	 */
580	public function processLimit($query, $limit, $offset = 0)
581	{
582		if ($limit > 0)
583		{
584			$query .= ' LIMIT ' . $limit;
585		}
586
587		if ($offset > 0)
588		{
589			$query .= ' OFFSET ' . $offset;
590		}
591
592		return $query;
593	}
594
595	/**
596	 * Add to the current date and time in Postgresql.
597	 * Usage:
598	 * $query->select($query->dateAdd());
599	 * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
600	 *
601	 * @param   datetime  $date      The date to add to
602	 * @param   string    $interval  The string representation of the appropriate number of units
603	 * @param   string    $datePart  The part of the date to perform the addition on
604	 *
605	 * @return  string  The string with the appropriate sql for addition of dates
606	 *
607	 * @since   13.1
608	 * @note    Not all drivers support all units. Check appropriate references
609	 * @link    http://www.postgresql.org/docs/9.0/static/functions-datetime.html.
610	 */
611	public function dateAdd($date, $interval, $datePart)
612	{
613		if (substr($interval, 0, 1) != '-')
614		{
615			return "timestamp '" . $date . "' + interval '" . $interval . " " . $datePart . "'";
616		}
617		else
618		{
619			return "timestamp '" . $date . "' - interval '" . ltrim($interval, '-') . " " . $datePart . "'";
620		}
621	}
622
623	/**
624	 * Return correct regexp operator for Postgresql.
625	 *
626	 * Ensure that the regexp operator is Postgresql compatible.
627	 *
628	 * Usage:
629	 * $query->where('field ' . $query->regexp($search));
630	 *
631	 * @param   string  $value  The regex pattern.
632	 *
633	 * @return  string  Returns the regex operator.
634	 *
635	 * @since   11.3
636	 */
637	public function regexp($value)
638	{
639		return ' ~* ' . $value;
640	}
641}