[ Index ] |
PHP Cross Reference of Joomla 4.2.2 documentation |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * Part of the Joomla Framework Database Package 4 * 5 * @copyright Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved. 6 * @license GNU General Public License version 2 or later; see LICENSE 7 */ 8 9 namespace Joomla\Database\Query; 10 11 /** 12 * Trait for PostgreSQL Query Building. 13 * 14 * @since 2.0.0 15 */ 16 trait PostgresqlQueryBuilder 17 { 18 /** 19 * The FOR UPDATE element used in "FOR UPDATE" lock 20 * 21 * @var QueryElement 22 * @since 2.0.0 23 */ 24 protected $forUpdate; 25 26 /** 27 * The FOR SHARE element used in "FOR SHARE" lock 28 * 29 * @var QueryElement 30 * @since 2.0.0 31 */ 32 protected $forShare; 33 34 /** 35 * The NOWAIT element used in "FOR SHARE" and "FOR UPDATE" lock 36 * 37 * @var QueryElement 38 * @since 2.0.0 39 */ 40 protected $noWait; 41 42 /** 43 * The LIMIT element 44 * 45 * @var QueryElement 46 * @since 2.0.0 47 */ 48 protected $limit; 49 50 /** 51 * The OFFSET element 52 * 53 * @var QueryElement 54 * @since 2.0.0 55 */ 56 protected $offset; 57 58 /** 59 * The RETURNING element of INSERT INTO 60 * 61 * @var QueryElement 62 * @since 2.0.0 63 */ 64 protected $returning; 65 66 /** 67 * Magic function to convert the query to a string, only for PostgreSQL specific queries 68 * 69 * @return string The completed query. 70 * 71 * @since 2.0.0 72 */ 73 public function __toString() 74 { 75 $query = ''; 76 77 switch ($this->type) 78 { 79 case 'select': 80 $query .= (string) $this->select; 81 $query .= (string) $this->from; 82 83 if ($this->join) 84 { 85 // Special case for joins 86 foreach ($this->join as $join) 87 { 88 $query .= (string) $join; 89 } 90 } 91 92 if ($this->where) 93 { 94 $query .= (string) $this->where; 95 } 96 97 if ($this->selectRowNumber) 98 { 99 if ($this->order) 100 { 101 $query .= (string) $this->order; 102 } 103 104 break; 105 } 106 107 if ($this->group) 108 { 109 $query .= (string) $this->group; 110 } 111 112 if ($this->having) 113 { 114 $query .= (string) $this->having; 115 } 116 117 if ($this->merge) 118 { 119 // Special case for merge 120 foreach ($this->merge as $element) 121 { 122 $query .= (string) $element; 123 } 124 } 125 126 if ($this->order) 127 { 128 $query .= (string) $this->order; 129 } 130 131 if ($this->forUpdate) 132 { 133 $query .= (string) $this->forUpdate; 134 } 135 else 136 { 137 if ($this->forShare) 138 { 139 $query .= (string) $this->forShare; 140 } 141 } 142 143 if ($this->noWait) 144 { 145 $query .= (string) $this->noWait; 146 } 147 148 $query = $this->processLimit($query, $this->limit, $this->offset); 149 150 break; 151 152 case 'update': 153 $query .= (string) $this->update; 154 $query .= (string) $this->set; 155 156 if ($this->join) 157 { 158 $tmpFrom = $this->from; 159 $tmpWhere = $this->where ? clone $this->where : null; 160 $this->from = null; 161 162 // Workaround for special case of JOIN with UPDATE 163 foreach ($this->join as $join) 164 { 165 $joinElem = $join->getElements(); 166 167 $this->from($joinElem[0]); 168 169 if (isset($joinElem[1])) 170 { 171 $this->where($joinElem[1]); 172 } 173 } 174 175 $query .= (string) $this->from; 176 177 if ($this->where) 178 { 179 $query .= (string) $this->where; 180 } 181 182 $this->from = $tmpFrom; 183 $this->where = $tmpWhere; 184 } 185 elseif ($this->where) 186 { 187 $query .= (string) $this->where; 188 } 189 190 $query = $this->processLimit($query, $this->limit, $this->offset); 191 192 break; 193 194 case 'insert': 195 $query .= (string) $this->insert; 196 197 if ($this->values) 198 { 199 if ($this->columns) 200 { 201 $query .= (string) $this->columns; 202 } 203 204 $elements = $this->values->getElements(); 205 206 if (!($elements[0] instanceof $this)) 207 { 208 $query .= ' VALUES '; 209 } 210 211 $query .= (string) $this->values; 212 213 if ($this->returning) 214 { 215 $query .= (string) $this->returning; 216 } 217 } 218 219 $query = $this->processLimit($query, $this->limit, $this->offset); 220 221 break; 222 223 default: 224 $query = parent::__toString(); 225 226 break; 227 } 228 229 if ($this->type === 'select' && $this->alias !== null) 230 { 231 $query = '(' . $query . ') AS ' . $this->alias; 232 } 233 234 return $query; 235 } 236 237 /** 238 * Clear data from the query or a specific clause of the query. 239 * 240 * @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query. 241 * 242 * @return $this 243 * 244 * @since 2.0.0 245 */ 246 public function clear($clause = null) 247 { 248 switch ($clause) 249 { 250 case 'limit': 251 $this->limit = null; 252 253 break; 254 255 case 'offset': 256 $this->offset = null; 257 258 break; 259 260 case 'forUpdate': 261 $this->forUpdate = null; 262 263 break; 264 265 case 'forShare': 266 $this->forShare = null; 267 268 break; 269 270 case 'noWait': 271 $this->noWait = null; 272 273 break; 274 275 case 'returning': 276 $this->returning = null; 277 278 break; 279 280 case 'select': 281 case 'update': 282 case 'delete': 283 case 'insert': 284 case 'querySet': 285 case 'from': 286 case 'join': 287 case 'set': 288 case 'where': 289 case 'group': 290 case 'having': 291 case 'merge': 292 case 'order': 293 case 'columns': 294 case 'values': 295 parent::clear($clause); 296 297 break; 298 299 default: 300 $this->forUpdate = null; 301 $this->forShare = null; 302 $this->noWait = null; 303 $this->returning = null; 304 305 parent::clear($clause); 306 307 break; 308 } 309 310 return $this; 311 } 312 313 /** 314 * Casts a value to a char. 315 * 316 * Ensure that the value is properly quoted before passing to the method. 317 * 318 * Usage: 319 * $query->select($query->castAs('CHAR', 'a')); 320 * 321 * @param string $type The type of string to cast as. 322 * @param string $value The value to cast as a char. 323 * @param string $length The value to cast as a char. 324 * 325 * @return string SQL statement to cast the value as a char type. 326 * 327 * @since 1.0 328 */ 329 public function castAs(string $type, string $value, ?string $length = null) 330 { 331 switch (strtoupper($type)) 332 { 333 case 'CHAR': 334 if (!$length) 335 { 336 return $value . '::text'; 337 } 338 else 339 { 340 return 'CAST(' . $value . ' AS CHAR(' . $length . '))'; 341 } 342 343 case 'INT': 344 return 'CAST(' . $value . ' AS INTEGER)'; 345 } 346 347 return parent::castAs($type, $value, $length); 348 } 349 350 /** 351 * Concatenates an array of column names or values. 352 * 353 * Usage: 354 * $query->select($query->concatenate(array('a', 'b'))); 355 * 356 * @param string[] $values An array of values to concatenate. 357 * @param string|null $separator As separator to place between each value. 358 * 359 * @return string The concatenated values. 360 * 361 * @since 2.0.0 362 */ 363 public function concatenate($values, $separator = null) 364 { 365 if ($separator !== null) 366 { 367 return implode(' || ' . $this->quote($separator) . ' || ', $values); 368 } 369 370 return implode(' || ', $values); 371 } 372 373 /** 374 * Gets the current date and time. 375 * 376 * @return string Return string used in query to obtain 377 * 378 * @since 2.0.0 379 */ 380 public function currentTimestamp() 381 { 382 return 'NOW()'; 383 } 384 385 /** 386 * Sets the FOR UPDATE lock on select's output row 387 * 388 * @param string $tableName The table to lock 389 * @param string $glue The glue by which to join the conditions. Defaults to ',' . 390 * 391 * @return $this 392 * 393 * @since 2.0.0 394 */ 395 public function forUpdate($tableName, $glue = ',') 396 { 397 $this->type = 'forUpdate'; 398 399 if ($this->forUpdate === null) 400 { 401 $glue = strtoupper($glue); 402 $this->forUpdate = new QueryElement('FOR UPDATE', 'OF ' . $tableName, "$glue "); 403 } 404 else 405 { 406 $this->forUpdate->append($tableName); 407 } 408 409 return $this; 410 } 411 412 /** 413 * Sets the FOR SHARE lock on select's output row 414 * 415 * @param string $tableName The table to lock 416 * @param string $glue The glue by which to join the conditions. Defaults to ',' . 417 * 418 * @return $this 419 * 420 * @since 2.0.0 421 */ 422 public function forShare($tableName, $glue = ',') 423 { 424 $this->type = 'forShare'; 425 426 if ($this->forShare === null) 427 { 428 $glue = strtoupper($glue); 429 $this->forShare = new QueryElement('FOR SHARE', 'OF ' . $tableName, "$glue "); 430 } 431 else 432 { 433 $this->forShare->append($tableName); 434 } 435 436 return $this; 437 } 438 439 /** 440 * Aggregate function to get input values concatenated into a string, separated by delimiter 441 * 442 * Usage: 443 * $query->groupConcat('id', ','); 444 * 445 * @param string $expression The expression to apply concatenation to, this may be a column name or complex SQL statement. 446 * @param string $separator The delimiter of each concatenated value 447 * 448 * @return string Input values concatenated into a string, separated by delimiter 449 * 450 * @since 2.0.0 451 */ 452 public function groupConcat($expression, $separator = ',') 453 { 454 return 'string_agg(' . $expression . ', ' . $this->quote($separator) . ')'; 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 2.0.0 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 2.0.0 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 2.0.0 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 2.0.0 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 2.0.0 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 2.0.0 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 $this 563 * 564 * @since 2.0.0 565 */ 566 public function noWait() 567 { 568 $this->type = 'noWait'; 569 570 if ($this->noWait === null) 571 { 572 $this->noWait = new QueryElement('NOWAIT', null); 573 } 574 575 return $this; 576 } 577 578 /** 579 * Set the LIMIT clause to the query 580 * 581 * @param integer $limit Number of rows to return 582 * 583 * @return $this 584 * 585 * @since 2.0.0 586 */ 587 public function limit($limit = 0) 588 { 589 if ($this->limit === null) 590 { 591 $this->limit = new QueryElement('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 integer for skipping rows 601 * 602 * @return $this 603 * 604 * @since 2.0.0 605 */ 606 public function offset($offset = 0) 607 { 608 if ($this->offset === null) 609 { 610 $this->offset = new QueryElement('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 $this 622 * 623 * @since 2.0.0 624 */ 625 public function returning($pkCol) 626 { 627 if ($this->returning === null) 628 { 629 $this->returning = new QueryElement('RETURNING', $pkCol); 630 } 631 632 return $this; 633 } 634 635 /** 636 * Method to modify a query already in string format with the needed additions to make the query limited to a particular number of 637 * results, or start at a particular offset. 638 * 639 * @param string $query The query in string format 640 * @param integer $limit The limit for the result set 641 * @param integer $offset The offset for the result set 642 * 643 * @return string 644 * 645 * @since 2.0.0 646 */ 647 public function processLimit($query, $limit, $offset = 0) 648 { 649 if ($limit > 0) 650 { 651 $query .= ' LIMIT ' . $limit; 652 } 653 654 if ($offset > 0) 655 { 656 $query .= ' OFFSET ' . $offset; 657 } 658 659 return $query; 660 } 661 662 /** 663 * Add to the current date and time. 664 * 665 * Usage: 666 * $query->select($query->dateAdd()); 667 * 668 * Prefixing the interval with a - (negative sign) will cause subtraction to be used. 669 * 670 * @param string $date The db quoted string representation of the date to add to 671 * @param string $interval The string representation of the appropriate number of units 672 * @param string $datePart The part of the date to perform the addition on 673 * 674 * @return string The string with the appropriate sql for addition of dates 675 * 676 * @since 2.0.0 677 * @link http://www.postgresql.org/docs/9.0/static/functions-datetime.html. 678 */ 679 public function dateAdd($date, $interval, $datePart) 680 { 681 if (substr($interval, 0, 1) !== '-') 682 { 683 return 'timestamp ' . $date . " + interval '" . $interval . ' ' . $datePart . "'"; 684 } 685 686 return 'timestamp ' . $date . " - interval '" . ltrim($interval, '-') . ' ' . $datePart . "'"; 687 } 688 689 /** 690 * Get the regular expression operator 691 * 692 * Usage: 693 * $query->where('field ' . $query->regexp($search)); 694 * 695 * @param string $value The regex pattern. 696 * 697 * @return string 698 * 699 * @since 2.0.0 700 */ 701 public function regexp($value) 702 { 703 return ' ~* ' . $value; 704 } 705 706 /** 707 * Get the function to return a random floating-point value 708 * 709 * Usage: 710 * $query->rand(); 711 * 712 * @return string 713 * 714 * @since 2.0.0 715 */ 716 public function rand() 717 { 718 return ' RANDOM() '; 719 } 720 721 /** 722 * Find a value in a varchar used like a set. 723 * 724 * Ensure that the value is an integer before passing to the method. 725 * 726 * Usage: 727 * $query->findInSet((int) $parent->id, 'a.assigned_cat_ids') 728 * 729 * @param string $value The value to search for. 730 * @param string $set The set of values. 731 * 732 * @return string A representation of the MySQL find_in_set() function for the driver. 733 * 734 * @since 2.0.0 735 */ 736 public function findInSet($value, $set) 737 { 738 return " $value = ANY (string_to_array($set, ',')::integer[]) "; 739 } 740 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
Generated: Wed Sep 7 05:41:13 2022 | Chilli.vc Blog - For Webmaster,Blog-Writer,System Admin and Domainer |