PageRenderTime 45ms CodeModel.GetById 12ms RepoModel.GetById 1ms app.codeStats 0ms

/src/Joomla/Database/DatabaseQuery.php

https://github.com/dianaprajescu/joomla-framework
PHP | 1683 lines | 692 code | 185 blank | 806 comment | 54 complexity | 2ac2c63e6918e44eb2315cb5ded2d393 MD5 | raw file
Possible License(s): GPL-2.0, LGPL-2.1
  1. <?php
  2. /**
  3. * Part of the Joomla Framework Database Package
  4. *
  5. * @copyright Copyright (C) 2005 - 2013 Open Source Matters, Inc. All rights reserved.
  6. * @license GNU General Public License version 2 or later; see LICENSE
  7. */
  8. namespace Joomla\Database;
  9. /**
  10. * Query Building Class.
  11. *
  12. * @since 1.0
  13. *
  14. * @method string q() q($text, $escape = true) Alias for quote method
  15. * @method string qn() qn($name, $as = null) Alias for quoteName method
  16. * @method string e() e($text, $extra = false) Alias for escape method
  17. */
  18. abstract class DatabaseQuery
  19. {
  20. /**
  21. * The database driver.
  22. *
  23. * @var DatabaseDriver
  24. * @since 1.0
  25. */
  26. protected $db = null;
  27. /**
  28. * The SQL query (if a direct query string was provided).
  29. *
  30. * @var string
  31. * @since 1.0
  32. */
  33. protected $sql = null;
  34. /**
  35. * The query type.
  36. *
  37. * @var string
  38. * @since 1.0
  39. */
  40. protected $type = '';
  41. /**
  42. * The query element for a generic query (type = null).
  43. *
  44. * @var Query\QueryElement
  45. * @since 1.0
  46. */
  47. protected $element = null;
  48. /**
  49. * The select element.
  50. *
  51. * @var Query\QueryElement
  52. * @since 1.0
  53. */
  54. protected $select = null;
  55. /**
  56. * The delete element.
  57. *
  58. * @var Query\QueryElement
  59. * @since 1.0
  60. */
  61. protected $delete = null;
  62. /**
  63. * The update element.
  64. *
  65. * @var Query\QueryElement
  66. * @since 1.0
  67. */
  68. protected $update = null;
  69. /**
  70. * The insert element.
  71. *
  72. * @var Query\QueryElement
  73. * @since 1.0
  74. */
  75. protected $insert = null;
  76. /**
  77. * The from element.
  78. *
  79. * @var Query\QueryElement
  80. * @since 1.0
  81. */
  82. protected $from = null;
  83. /**
  84. * The join element.
  85. *
  86. * @var Query\QueryElement
  87. * @since 1.0
  88. */
  89. protected $join = null;
  90. /**
  91. * The set element.
  92. *
  93. * @var Query\QueryElement
  94. * @since 1.0
  95. */
  96. protected $set = null;
  97. /**
  98. * The where element.
  99. *
  100. * @var Query\QueryElement
  101. * @since 1.0
  102. */
  103. protected $where = null;
  104. /**
  105. * The group by element.
  106. *
  107. * @var Query\QueryElement
  108. * @since 1.0
  109. */
  110. protected $group = null;
  111. /**
  112. * The having element.
  113. *
  114. * @var Query\QueryElement
  115. * @since 1.0
  116. */
  117. protected $having = null;
  118. /**
  119. * The column list for an INSERT statement.
  120. *
  121. * @var Query\QueryElement
  122. * @since 1.0
  123. */
  124. protected $columns = null;
  125. /**
  126. * The values list for an INSERT statement.
  127. *
  128. * @var Query\QueryElement
  129. * @since 1.0
  130. */
  131. protected $values = null;
  132. /**
  133. * The order element.
  134. *
  135. * @var Query\QueryElement
  136. * @since 1.0
  137. */
  138. protected $order = null;
  139. /**
  140. * The auto increment insert field element.
  141. *
  142. * @var object
  143. * @since 1.0
  144. */
  145. protected $autoIncrementField = null;
  146. /**
  147. * The call element.
  148. *
  149. * @var Query\QueryElement
  150. * @since 1.0
  151. */
  152. protected $call = null;
  153. /**
  154. * The exec element.
  155. *
  156. * @var Query\QueryElement
  157. * @since 1.0
  158. */
  159. protected $exec = null;
  160. /**
  161. * The union element.
  162. *
  163. * @var Query\QueryElement
  164. * @since 1.0
  165. */
  166. protected $union = null;
  167. /**
  168. * Magic method to provide method alias support for quote() and quoteName().
  169. *
  170. * @param string $method The called method.
  171. * @param array $args The array of arguments passed to the method.
  172. *
  173. * @return string The aliased method's return value or null.
  174. *
  175. * @since 1.0
  176. */
  177. public function __call($method, $args)
  178. {
  179. if (empty($args))
  180. {
  181. return;
  182. }
  183. switch ($method)
  184. {
  185. case 'q':
  186. return $this->quote($args[0], isset($args[1]) ? $args[1] : true);
  187. break;
  188. case 'qn':
  189. return $this->quoteName($args[0], isset($args[1]) ? $args[1] : null);
  190. break;
  191. case 'e':
  192. return $this->escape($args[0], isset($args[1]) ? $args[1] : false);
  193. break;
  194. }
  195. }
  196. /**
  197. * Class constructor.
  198. *
  199. * @param DatabaseDriver $db The database driver.
  200. *
  201. * @since 1.0
  202. */
  203. public function __construct(DatabaseDriver $db = null)
  204. {
  205. $this->db = $db;
  206. }
  207. /**
  208. * Magic function to convert the query to a string.
  209. *
  210. * @return string The completed query.
  211. *
  212. * @since 1.0
  213. */
  214. public function __toString()
  215. {
  216. $query = '';
  217. if ($this->sql)
  218. {
  219. return $this->sql;
  220. }
  221. switch ($this->type)
  222. {
  223. case 'element':
  224. $query .= (string) $this->element;
  225. break;
  226. case 'select':
  227. $query .= (string) $this->select;
  228. $query .= (string) $this->from;
  229. if ($this->join)
  230. {
  231. // Special case for joins
  232. foreach ($this->join as $join)
  233. {
  234. $query .= (string) $join;
  235. }
  236. }
  237. if ($this->where)
  238. {
  239. $query .= (string) $this->where;
  240. }
  241. if ($this->group)
  242. {
  243. $query .= (string) $this->group;
  244. }
  245. if ($this->having)
  246. {
  247. $query .= (string) $this->having;
  248. }
  249. if ($this->order)
  250. {
  251. $query .= (string) $this->order;
  252. }
  253. break;
  254. case 'union':
  255. $query .= (string) $this->union;
  256. break;
  257. case 'delete':
  258. $query .= (string) $this->delete;
  259. $query .= (string) $this->from;
  260. if ($this->join)
  261. {
  262. // Special case for joins
  263. foreach ($this->join as $join)
  264. {
  265. $query .= (string) $join;
  266. }
  267. }
  268. if ($this->where)
  269. {
  270. $query .= (string) $this->where;
  271. }
  272. break;
  273. case 'update':
  274. $query .= (string) $this->update;
  275. if ($this->join)
  276. {
  277. // Special case for joins
  278. foreach ($this->join as $join)
  279. {
  280. $query .= (string) $join;
  281. }
  282. }
  283. $query .= (string) $this->set;
  284. if ($this->where)
  285. {
  286. $query .= (string) $this->where;
  287. }
  288. break;
  289. case 'insert':
  290. $query .= (string) $this->insert;
  291. // Set method
  292. if ($this->set)
  293. {
  294. $query .= (string) $this->set;
  295. }
  296. elseif ($this->values)
  297. // Columns-Values method
  298. {
  299. if ($this->columns)
  300. {
  301. $query .= (string) $this->columns;
  302. }
  303. $elements = $this->values->getElements();
  304. if (!($elements[0] instanceof $this))
  305. {
  306. $query .= ' VALUES ';
  307. }
  308. $query .= (string) $this->values;
  309. }
  310. break;
  311. case 'call':
  312. $query .= (string) $this->call;
  313. break;
  314. case 'exec':
  315. $query .= (string) $this->exec;
  316. break;
  317. }
  318. if ($this instanceof Query\LimitableInterface)
  319. {
  320. $query = $this->processLimit($query, $this->limit, $this->offset);
  321. }
  322. return $query;
  323. }
  324. /**
  325. * Magic function to get protected variable value
  326. *
  327. * @param string $name The name of the variable.
  328. *
  329. * @return mixed
  330. *
  331. * @since 1.0
  332. */
  333. public function __get($name)
  334. {
  335. return isset($this->$name) ? $this->$name : null;
  336. }
  337. /**
  338. * Add a single column, or array of columns to the CALL clause of the query.
  339. *
  340. * Note that you must not mix insert, update, delete and select method calls when building a query.
  341. * The call method can, however, be called multiple times in the same query.
  342. *
  343. * Usage:
  344. * $query->call('a.*')->call('b.id');
  345. * $query->call(array('a.*', 'b.id'));
  346. *
  347. * @param mixed $columns A string or an array of field names.
  348. *
  349. * @return DatabaseQuery Returns this object to allow chaining.
  350. *
  351. * @since 1.0
  352. */
  353. public function call($columns)
  354. {
  355. $this->type = 'call';
  356. if (is_null($this->call))
  357. {
  358. $this->call = new Query\QueryElement('CALL', $columns);
  359. }
  360. else
  361. {
  362. $this->call->append($columns);
  363. }
  364. return $this;
  365. }
  366. /**
  367. * Casts a value to a char.
  368. *
  369. * Ensure that the value is properly quoted before passing to the method.
  370. *
  371. * Usage:
  372. * $query->select($query->castAsChar('a'));
  373. *
  374. * @param string $value The value to cast as a char.
  375. *
  376. * @return string Returns the cast value.
  377. *
  378. * @since 1.0
  379. */
  380. public function castAsChar($value)
  381. {
  382. return $value;
  383. }
  384. /**
  385. * Gets the number of characters in a string.
  386. *
  387. * Note, use 'length' to find the number of bytes in a string.
  388. *
  389. * Usage:
  390. * $query->select($query->charLength('a'));
  391. *
  392. * @param string $field A value.
  393. * @param string $operator Comparison operator between charLength integer value and $condition
  394. * @param string $condition Integer value to compare charLength with.
  395. *
  396. * @return string The required char length call.
  397. *
  398. * @since 1.0
  399. */
  400. public function charLength($field, $operator = null, $condition = null)
  401. {
  402. return 'CHAR_LENGTH(' . $field . ')' . (isset($operator) && isset($condition) ? ' ' . $operator . ' ' . $condition : '');
  403. }
  404. /**
  405. * Clear data from the query or a specific clause of the query.
  406. *
  407. * @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
  408. *
  409. * @return DatabaseQuery Returns this object to allow chaining.
  410. *
  411. * @since 1.0
  412. */
  413. public function clear($clause = null)
  414. {
  415. $this->sql = null;
  416. switch ($clause)
  417. {
  418. case 'select':
  419. $this->select = null;
  420. $this->type = null;
  421. break;
  422. case 'delete':
  423. $this->delete = null;
  424. $this->type = null;
  425. break;
  426. case 'update':
  427. $this->update = null;
  428. $this->type = null;
  429. break;
  430. case 'insert':
  431. $this->insert = null;
  432. $this->type = null;
  433. $this->autoIncrementField = null;
  434. break;
  435. case 'from':
  436. $this->from = null;
  437. break;
  438. case 'join':
  439. $this->join = null;
  440. break;
  441. case 'set':
  442. $this->set = null;
  443. break;
  444. case 'where':
  445. $this->where = null;
  446. break;
  447. case 'group':
  448. $this->group = null;
  449. break;
  450. case 'having':
  451. $this->having = null;
  452. break;
  453. case 'order':
  454. $this->order = null;
  455. break;
  456. case 'columns':
  457. $this->columns = null;
  458. break;
  459. case 'values':
  460. $this->values = null;
  461. break;
  462. case 'exec':
  463. $this->exec = null;
  464. $this->type = null;
  465. break;
  466. case 'call':
  467. $this->call = null;
  468. $this->type = null;
  469. break;
  470. case 'limit':
  471. $this->offset = 0;
  472. $this->limit = 0;
  473. break;
  474. case 'union':
  475. $this->union = null;
  476. break;
  477. default:
  478. $this->type = null;
  479. $this->select = null;
  480. $this->delete = null;
  481. $this->update = null;
  482. $this->insert = null;
  483. $this->from = null;
  484. $this->join = null;
  485. $this->set = null;
  486. $this->where = null;
  487. $this->group = null;
  488. $this->having = null;
  489. $this->order = null;
  490. $this->columns = null;
  491. $this->values = null;
  492. $this->autoIncrementField = null;
  493. $this->exec = null;
  494. $this->call = null;
  495. $this->union = null;
  496. $this->offset = 0;
  497. $this->limit = 0;
  498. break;
  499. }
  500. return $this;
  501. }
  502. /**
  503. * Adds a column, or array of column names that would be used for an INSERT INTO statement.
  504. *
  505. * @param mixed $columns A column name, or array of column names.
  506. *
  507. * @return DatabaseQuery Returns this object to allow chaining.
  508. *
  509. * @since 1.0
  510. */
  511. public function columns($columns)
  512. {
  513. if (is_null($this->columns))
  514. {
  515. $this->columns = new Query\QueryElement('()', $columns);
  516. }
  517. else
  518. {
  519. $this->columns->append($columns);
  520. }
  521. return $this;
  522. }
  523. /**
  524. * Concatenates an array of column names or values.
  525. *
  526. * Usage:
  527. * $query->select($query->concatenate(array('a', 'b')));
  528. *
  529. * @param array $values An array of values to concatenate.
  530. * @param string $separator As separator to place between each value.
  531. *
  532. * @return string The concatenated values.
  533. *
  534. * @since 1.0
  535. */
  536. public function concatenate($values, $separator = null)
  537. {
  538. if ($separator)
  539. {
  540. return 'CONCATENATE(' . implode(' || ' . $this->quote($separator) . ' || ', $values) . ')';
  541. }
  542. else
  543. {
  544. return 'CONCATENATE(' . implode(' || ', $values) . ')';
  545. }
  546. }
  547. /**
  548. * Gets the current date and time.
  549. *
  550. * Usage:
  551. * $query->where('published_up < '.$query->currentTimestamp());
  552. *
  553. * @return string
  554. *
  555. * @since 1.0
  556. */
  557. public function currentTimestamp()
  558. {
  559. return 'CURRENT_TIMESTAMP()';
  560. }
  561. /**
  562. * Returns a PHP date() function compliant date format for the database driver.
  563. *
  564. * This method is provided for use where the query object is passed to a function for modification.
  565. * If you have direct access to the database object, it is recommended you use the getDateFormat method directly.
  566. *
  567. * @return string The format string.
  568. *
  569. * @since 1.0
  570. * @throws \RuntimeException
  571. */
  572. public function dateFormat()
  573. {
  574. if (!($this->db instanceof DatabaseDriver))
  575. {
  576. throw new \RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
  577. }
  578. return $this->db->getDateFormat();
  579. }
  580. /**
  581. * Creates a formatted dump of the query for debugging purposes.
  582. *
  583. * Usage:
  584. * echo $query->dump();
  585. *
  586. * @return string
  587. *
  588. * @since 1.0
  589. */
  590. public function dump()
  591. {
  592. return '<pre class="jdatabasequery">' . str_replace('#__', $this->db->getPrefix(), $this) . '</pre>';
  593. }
  594. /**
  595. * Add a table name to the DELETE clause of the query.
  596. *
  597. * Note that you must not mix insert, update, delete and select method calls when building a query.
  598. *
  599. * Usage:
  600. * $query->delete('#__a')->where('id = 1');
  601. *
  602. * @param string $table The name of the table to delete from.
  603. *
  604. * @return DatabaseQuery Returns this object to allow chaining.
  605. *
  606. * @since 1.0
  607. */
  608. public function delete($table = null)
  609. {
  610. $this->type = 'delete';
  611. $this->delete = new Query\QueryElement('DELETE', null);
  612. if (!empty($table))
  613. {
  614. $this->from($table);
  615. }
  616. return $this;
  617. }
  618. /**
  619. * Method to escape a string for usage in an SQL statement.
  620. *
  621. * This method is provided for use where the query object is passed to a function for modification.
  622. * If you have direct access to the database object, it is recommended you use the escape method directly.
  623. *
  624. * Note that 'e' is an alias for this method as it is in JDatabaseDatabaseDriver.
  625. *
  626. * @param string $text The string to be escaped.
  627. * @param boolean $extra Optional parameter to provide extra escaping.
  628. *
  629. * @return string The escaped string.
  630. *
  631. * @since 1.0
  632. * @throws \RuntimeException if the internal db property is not a valid object.
  633. */
  634. public function escape($text, $extra = false)
  635. {
  636. if (!($this->db instanceof DatabaseDriver))
  637. {
  638. throw new \RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
  639. }
  640. return $this->db->escape($text, $extra);
  641. }
  642. /**
  643. * Add a single column, or array of columns to the EXEC clause of the query.
  644. *
  645. * Note that you must not mix insert, update, delete and select method calls when building a query.
  646. * The exec method can, however, be called multiple times in the same query.
  647. *
  648. * Usage:
  649. * $query->exec('a.*')->exec('b.id');
  650. * $query->exec(array('a.*', 'b.id'));
  651. *
  652. * @param mixed $columns A string or an array of field names.
  653. *
  654. * @return DatabaseQuery Returns this object to allow chaining.
  655. *
  656. * @since 1.0
  657. */
  658. public function exec($columns)
  659. {
  660. $this->type = 'exec';
  661. if (is_null($this->exec))
  662. {
  663. $this->exec = new Query\QueryElement('EXEC', $columns);
  664. }
  665. else
  666. {
  667. $this->exec->append($columns);
  668. }
  669. return $this;
  670. }
  671. /**
  672. * Add a table to the FROM clause of the query.
  673. *
  674. * Note that while an array of tables can be provided, it is recommended you use explicit joins.
  675. *
  676. * Usage:
  677. * $query->select('*')->from('#__a');
  678. *
  679. * @param mixed $tables A string or array of table names.
  680. * This can be a JDatabaseQuery object (or a child of it) when used
  681. * as a subquery in FROM clause along with a value for $subQueryAlias.
  682. * @param string $subQueryAlias Alias used when $tables is a JDatabaseQuery.
  683. *
  684. * @return DatabaseQuery Returns this object to allow chaining.
  685. *
  686. * @since 1.0
  687. * @throws \RuntimeException
  688. */
  689. public function from($tables, $subQueryAlias = null)
  690. {
  691. if (is_null($this->from))
  692. {
  693. if ($tables instanceof $this)
  694. {
  695. if (is_null($subQueryAlias))
  696. {
  697. throw new \RuntimeException('JLIB_DATABASE_ERROR_NULL_SUBQUERY_ALIAS');
  698. }
  699. $tables = '( ' . (string) $tables . ' ) AS ' . $this->quoteName($subQueryAlias);
  700. }
  701. $this->from = new Query\QueryElement('FROM', $tables);
  702. }
  703. else
  704. {
  705. $this->from->append($tables);
  706. }
  707. return $this;
  708. }
  709. /**
  710. * Used to get a string to extract year from date column.
  711. *
  712. * Usage:
  713. * $query->select($query->year($query->quoteName('dateColumn')));
  714. *
  715. * @param string $date Date column containing year to be extracted.
  716. *
  717. * @return string Returns string to extract year from a date.
  718. *
  719. * @since 1.0
  720. */
  721. public function year($date)
  722. {
  723. return 'YEAR(' . $date . ')';
  724. }
  725. /**
  726. * Used to get a string to extract month from date column.
  727. *
  728. * Usage:
  729. * $query->select($query->month($query->quoteName('dateColumn')));
  730. *
  731. * @param string $date Date column containing month to be extracted.
  732. *
  733. * @return string Returns string to extract month from a date.
  734. *
  735. * @since 1.0
  736. */
  737. public function month($date)
  738. {
  739. return 'MONTH(' . $date . ')';
  740. }
  741. /**
  742. * Used to get a string to extract day from date column.
  743. *
  744. * Usage:
  745. * $query->select($query->day($query->quoteName('dateColumn')));
  746. *
  747. * @param string $date Date column containing day to be extracted.
  748. *
  749. * @return string Returns string to extract day from a date.
  750. *
  751. * @since 1.0
  752. */
  753. public function day($date)
  754. {
  755. return 'DAY(' . $date . ')';
  756. }
  757. /**
  758. * Used to get a string to extract hour from date column.
  759. *
  760. * Usage:
  761. * $query->select($query->hour($query->quoteName('dateColumn')));
  762. *
  763. * @param string $date Date column containing hour to be extracted.
  764. *
  765. * @return string Returns string to extract hour from a date.
  766. *
  767. * @since 1.0
  768. */
  769. public function hour($date)
  770. {
  771. return 'HOUR(' . $date . ')';
  772. }
  773. /**
  774. * Used to get a string to extract minute from date column.
  775. *
  776. * Usage:
  777. * $query->select($query->minute($query->quoteName('dateColumn')));
  778. *
  779. * @param string $date Date column containing minute to be extracted.
  780. *
  781. * @return string Returns string to extract minute from a date.
  782. *
  783. * @since 1.0
  784. */
  785. public function minute($date)
  786. {
  787. return 'MINUTE(' . $date . ')';
  788. }
  789. /**
  790. * Used to get a string to extract seconds from date column.
  791. *
  792. * Usage:
  793. * $query->select($query->second($query->quoteName('dateColumn')));
  794. *
  795. * @param string $date Date column containing second to be extracted.
  796. *
  797. * @return string Returns string to extract second from a date.
  798. *
  799. * @since 1.0
  800. */
  801. public function second($date)
  802. {
  803. return 'SECOND(' . $date . ')';
  804. }
  805. /**
  806. * Add a grouping column to the GROUP clause of the query.
  807. *
  808. * Usage:
  809. * $query->group('id');
  810. *
  811. * @param mixed $columns A string or array of ordering columns.
  812. *
  813. * @return DatabaseQuery Returns this object to allow chaining.
  814. *
  815. * @since 1.0
  816. */
  817. public function group($columns)
  818. {
  819. if (is_null($this->group))
  820. {
  821. $this->group = new Query\QueryElement('GROUP BY', $columns);
  822. }
  823. else
  824. {
  825. $this->group->append($columns);
  826. }
  827. return $this;
  828. }
  829. /**
  830. * A conditions to the HAVING clause of the query.
  831. *
  832. * Usage:
  833. * $query->group('id')->having('COUNT(id) > 5');
  834. *
  835. * @param mixed $conditions A string or array of columns.
  836. * @param string $glue The glue by which to join the conditions. Defaults to AND.
  837. *
  838. * @return DatabaseQuery Returns this object to allow chaining.
  839. *
  840. * @since 1.0
  841. */
  842. public function having($conditions, $glue = 'AND')
  843. {
  844. if (is_null($this->having))
  845. {
  846. $glue = strtoupper($glue);
  847. $this->having = new Query\QueryElement('HAVING', $conditions, " $glue ");
  848. }
  849. else
  850. {
  851. $this->having->append($conditions);
  852. }
  853. return $this;
  854. }
  855. /**
  856. * Add an INNER JOIN clause to the query.
  857. *
  858. * Usage:
  859. * $query->innerJoin('b ON b.id = a.id')->innerJoin('c ON c.id = b.id');
  860. *
  861. * @param string $condition The join condition.
  862. *
  863. * @return DatabaseQuery Returns this object to allow chaining.
  864. *
  865. * @since 1.0
  866. */
  867. public function innerJoin($condition)
  868. {
  869. $this->join('INNER', $condition);
  870. return $this;
  871. }
  872. /**
  873. * Add a table name to the INSERT clause of the query.
  874. *
  875. * Note that you must not mix insert, update, delete and select method calls when building a query.
  876. *
  877. * Usage:
  878. * $query->insert('#__a')->set('id = 1');
  879. * $query->insert('#__a')->columns('id, title')->values('1,2')->values('3,4');
  880. * $query->insert('#__a')->columns('id, title')->values(array('1,2', '3,4'));
  881. *
  882. * @param mixed $table The name of the table to insert data into.
  883. * @param boolean $incrementField The name of the field to auto increment.
  884. *
  885. * @return DatabaseQuery Returns this object to allow chaining.
  886. *
  887. * @since 1.0
  888. */
  889. public function insert($table, $incrementField=false)
  890. {
  891. $this->type = 'insert';
  892. $this->insert = new Query\QueryElement('INSERT INTO', $table);
  893. $this->autoIncrementField = $incrementField;
  894. return $this;
  895. }
  896. /**
  897. * Add a JOIN clause to the query.
  898. *
  899. * Usage:
  900. * $query->join('INNER', 'b ON b.id = a.id);
  901. *
  902. * @param string $type The type of join. This string is prepended to the JOIN keyword.
  903. * @param string $conditions A string or array of conditions.
  904. *
  905. * @return DatabaseQuery Returns this object to allow chaining.
  906. *
  907. * @since 1.0
  908. */
  909. public function join($type, $conditions)
  910. {
  911. if (is_null($this->join))
  912. {
  913. $this->join = array();
  914. }
  915. $this->join[] = new Query\QueryElement(strtoupper($type) . ' JOIN', $conditions);
  916. return $this;
  917. }
  918. /**
  919. * Add a LEFT JOIN clause to the query.
  920. *
  921. * Usage:
  922. * $query->leftJoin('b ON b.id = a.id')->leftJoin('c ON c.id = b.id');
  923. *
  924. * @param string $condition The join condition.
  925. *
  926. * @return DatabaseQuery Returns this object to allow chaining.
  927. *
  928. * @since 1.0
  929. */
  930. public function leftJoin($condition)
  931. {
  932. $this->join('LEFT', $condition);
  933. return $this;
  934. }
  935. /**
  936. * Get the length of a string in bytes.
  937. *
  938. * Note, use 'charLength' to find the number of characters in a string.
  939. *
  940. * Usage:
  941. * query->where($query->length('a').' > 3');
  942. *
  943. * @param string $value The string to measure.
  944. *
  945. * @return integer
  946. *
  947. * @since 1.0
  948. */
  949. public function length($value)
  950. {
  951. return 'LENGTH(' . $value . ')';
  952. }
  953. /**
  954. * Get the null or zero representation of a timestamp for the database driver.
  955. *
  956. * This method is provided for use where the query object is passed to a function for modification.
  957. * If you have direct access to the database object, it is recommended you use the nullDate method directly.
  958. *
  959. * Usage:
  960. * $query->where('modified_date <> '.$query->nullDate());
  961. *
  962. * @param boolean $quoted Optionally wraps the null date in database quotes (true by default).
  963. *
  964. * @return string Null or zero representation of a timestamp.
  965. *
  966. * @since 1.0
  967. * @throws \RuntimeException
  968. */
  969. public function nullDate($quoted = true)
  970. {
  971. if (!($this->db instanceof DatabaseDriver))
  972. {
  973. throw new \RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
  974. }
  975. $result = $this->db->getNullDate($quoted);
  976. if ($quoted)
  977. {
  978. return $this->db->quote($result);
  979. }
  980. return $result;
  981. }
  982. /**
  983. * Add a ordering column to the ORDER clause of the query.
  984. *
  985. * Usage:
  986. * $query->order('foo')->order('bar');
  987. * $query->order(array('foo','bar'));
  988. *
  989. * @param mixed $columns A string or array of ordering columns.
  990. *
  991. * @return DatabaseQuery Returns this object to allow chaining.
  992. *
  993. * @since 1.0
  994. */
  995. public function order($columns)
  996. {
  997. if (is_null($this->order))
  998. {
  999. $this->order = new Query\QueryElement('ORDER BY', $columns);
  1000. }
  1001. else
  1002. {
  1003. $this->order->append($columns);
  1004. }
  1005. return $this;
  1006. }
  1007. /**
  1008. * Add an OUTER JOIN clause to the query.
  1009. *
  1010. * Usage:
  1011. * $query->outerJoin('b ON b.id = a.id')->outerJoin('c ON c.id = b.id');
  1012. *
  1013. * @param string $condition The join condition.
  1014. *
  1015. * @return DatabaseQuery Returns this object to allow chaining.
  1016. *
  1017. * @since 1.0
  1018. */
  1019. public function outerJoin($condition)
  1020. {
  1021. $this->join('OUTER', $condition);
  1022. return $this;
  1023. }
  1024. /**
  1025. * Method to quote and optionally escape a string to database requirements for insertion into the database.
  1026. *
  1027. * This method is provided for use where the query object is passed to a function for modification.
  1028. * If you have direct access to the database object, it is recommended you use the quote method directly.
  1029. *
  1030. * Note that 'q' is an alias for this method as it is in DatabaseDriver.
  1031. *
  1032. * Usage:
  1033. * $query->quote('fulltext');
  1034. * $query->q('fulltext');
  1035. * $query->q(array('option', 'fulltext'));
  1036. *
  1037. * @param mixed $text A string or an array of strings to quote.
  1038. * @param boolean $escape True to escape the string, false to leave it unchanged.
  1039. *
  1040. * @return string The quoted input string.
  1041. *
  1042. * @since 1.0
  1043. * @throws \RuntimeException if the internal db property is not a valid object.
  1044. */
  1045. public function quote($text, $escape = true)
  1046. {
  1047. if (!($this->db instanceof DatabaseDriver))
  1048. {
  1049. throw new \RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
  1050. }
  1051. return $this->db->quote($text, $escape);
  1052. }
  1053. /**
  1054. * Wrap an SQL statement identifier name such as column, table or database names in quotes to prevent injection
  1055. * risks and reserved word conflicts.
  1056. *
  1057. * This method is provided for use where the query object is passed to a function for modification.
  1058. * If you have direct access to the database object, it is recommended you use the quoteName method directly.
  1059. *
  1060. * Note that 'qn' is an alias for this method as it is in DatabaseDriver.
  1061. *
  1062. * Usage:
  1063. * $query->quoteName('#__a');
  1064. * $query->qn('#__a');
  1065. *
  1066. * @param mixed $name The identifier name to wrap in quotes, or an array of identifier names to wrap in quotes.
  1067. * Each type supports dot-notation name.
  1068. * @param mixed $as The AS query part associated to $name. It can be string or array, in latter case it has to be
  1069. * same length of $name; if is null there will not be any AS part for string or array element.
  1070. *
  1071. * @return mixed The quote wrapped name, same type of $name.
  1072. *
  1073. * @since 1.0
  1074. * @throws \RuntimeException if the internal db property is not a valid object.
  1075. */
  1076. public function quoteName($name, $as = null)
  1077. {
  1078. if (!($this->db instanceof DatabaseDriver))
  1079. {
  1080. throw new \RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT');
  1081. }
  1082. return $this->db->quoteName($name, $as);
  1083. }
  1084. /**
  1085. * Add a RIGHT JOIN clause to the query.
  1086. *
  1087. * Usage:
  1088. * $query->rightJoin('b ON b.id = a.id')->rightJoin('c ON c.id = b.id');
  1089. *
  1090. * @param string $condition The join condition.
  1091. *
  1092. * @return DatabaseQuery Returns this object to allow chaining.
  1093. *
  1094. * @since 1.0
  1095. */
  1096. public function rightJoin($condition)
  1097. {
  1098. $this->join('RIGHT', $condition);
  1099. return $this;
  1100. }
  1101. /**
  1102. * Add a single column, or array of columns to the SELECT clause of the query.
  1103. *
  1104. * Note that you must not mix insert, update, delete and select method calls when building a query.
  1105. * The select method can, however, be called multiple times in the same query.
  1106. *
  1107. * Usage:
  1108. * $query->select('a.*')->select('b.id');
  1109. * $query->select(array('a.*', 'b.id'));
  1110. *
  1111. * @param mixed $columns A string or an array of field names.
  1112. *
  1113. * @return DatabaseQuery Returns this object to allow chaining.
  1114. *
  1115. * @since 1.0
  1116. */
  1117. public function select($columns)
  1118. {
  1119. $this->type = 'select';
  1120. if (is_null($this->select))
  1121. {
  1122. $this->select = new Query\QueryElement('SELECT', $columns);
  1123. }
  1124. else
  1125. {
  1126. $this->select->append($columns);
  1127. }
  1128. return $this;
  1129. }
  1130. /**
  1131. * Add a single condition string, or an array of strings to the SET clause of the query.
  1132. *
  1133. * Usage:
  1134. * $query->set('a = 1')->set('b = 2');
  1135. * $query->set(array('a = 1', 'b = 2');
  1136. *
  1137. * @param mixed $conditions A string or array of string conditions.
  1138. * @param string $glue The glue by which to join the condition strings. Defaults to ,.
  1139. * Note that the glue is set on first use and cannot be changed.
  1140. *
  1141. * @return DatabaseQuery Returns this object to allow chaining.
  1142. *
  1143. * @since 1.0
  1144. */
  1145. public function set($conditions, $glue = ',')
  1146. {
  1147. if (is_null($this->set))
  1148. {
  1149. $glue = strtoupper($glue);
  1150. $this->set = new Query\QueryElement('SET', $conditions, PHP_EOL . "\t$glue ");
  1151. }
  1152. else
  1153. {
  1154. $this->set->append($conditions);
  1155. }
  1156. return $this;
  1157. }
  1158. /**
  1159. * Allows a direct query to be provided to the database
  1160. * driver's setQuery() method, but still allow queries
  1161. * to have bounded variables.
  1162. *
  1163. * Usage:
  1164. * $query->setQuery('select * from #__users');
  1165. *
  1166. * @param mixed $sql An SQL Query
  1167. *
  1168. * @return DatabaseQuery Returns this object to allow chaining.
  1169. *
  1170. * @since 1.0
  1171. */
  1172. public function setQuery($sql)
  1173. {
  1174. $this->sql = $sql;
  1175. return $this;
  1176. }
  1177. /**
  1178. * Add a table name to the UPDATE clause of the query.
  1179. *
  1180. * Note that you must not mix insert, update, delete and select method calls when building a query.
  1181. *
  1182. * Usage:
  1183. * $query->update('#__foo')->set(...);
  1184. *
  1185. * @param string $table A table to update.
  1186. *
  1187. * @return DatabaseQuery Returns this object to allow chaining.
  1188. *
  1189. * @since 1.0
  1190. */
  1191. public function update($table)
  1192. {
  1193. $this->type = 'update';
  1194. $this->update = new Query\QueryElement('UPDATE', $table);
  1195. return $this;
  1196. }
  1197. /**
  1198. * Adds a tuple, or array of tuples that would be used as values for an INSERT INTO statement.
  1199. *
  1200. * Usage:
  1201. * $query->values('1,2,3')->values('4,5,6');
  1202. * $query->values(array('1,2,3', '4,5,6'));
  1203. *
  1204. * @param string $values A single tuple, or array of tuples.
  1205. *
  1206. * @return DatabaseQuery Returns this object to allow chaining.
  1207. *
  1208. * @since 1.0
  1209. */
  1210. public function values($values)
  1211. {
  1212. if (is_null($this->values))
  1213. {
  1214. $this->values = new Query\QueryElement('()', $values, '),(');
  1215. }
  1216. else
  1217. {
  1218. $this->values->append($values);
  1219. }
  1220. return $this;
  1221. }
  1222. /**
  1223. * Add a single condition, or an array of conditions to the WHERE clause of the query.
  1224. *
  1225. * Usage:
  1226. * $query->where('a = 1')->where('b = 2');
  1227. * $query->where(array('a = 1', 'b = 2'));
  1228. *
  1229. * @param mixed $conditions A string or array of where conditions.
  1230. * @param string $glue The glue by which to join the conditions. Defaults to AND.
  1231. * Note that the glue is set on first use and cannot be changed.
  1232. *
  1233. * @return DatabaseQuery Returns this object to allow chaining.
  1234. *
  1235. * @since 1.0
  1236. */
  1237. public function where($conditions, $glue = 'AND')
  1238. {
  1239. if (is_null($this->where))
  1240. {
  1241. $glue = strtoupper($glue);
  1242. $this->where = new Query\QueryElement('WHERE', $conditions, " $glue ");
  1243. }
  1244. else
  1245. {
  1246. $this->where->append($conditions);
  1247. }
  1248. return $this;
  1249. }
  1250. /**
  1251. * Method to provide deep copy support to nested objects and
  1252. * arrays when cloning.
  1253. *
  1254. * @return void
  1255. *
  1256. * @since 1.0
  1257. */
  1258. public function __clone()
  1259. {
  1260. foreach ($this as $k => $v)
  1261. {
  1262. if ($k === 'db')
  1263. {
  1264. continue;
  1265. }
  1266. if (is_object($v) || is_array($v))
  1267. {
  1268. $this->{$k} = unserialize(serialize($v));
  1269. }
  1270. }
  1271. }
  1272. /**
  1273. * Add a query to UNION with the current query.
  1274. * Multiple unions each require separate statements and create an array of unions.
  1275. *
  1276. * Usage:
  1277. * $query->union('SELECT name FROM #__foo')
  1278. * $query->union('SELECT name FROM #__foo','distinct')
  1279. * $query->union(array('SELECT name FROM #__foo', 'SELECT name FROM #__bar'))
  1280. *
  1281. * @param mixed $query The Query object or string to union.
  1282. * @param boolean $distinct True to only return distinct rows from the union.
  1283. * @param string $glue The glue by which to join the conditions.
  1284. *
  1285. * @return mixed The Query object on success or boolean false on failure.
  1286. *
  1287. * @since 1.0
  1288. */
  1289. public function union($query, $distinct = false, $glue = '')
  1290. {
  1291. // Clear any ORDER BY clause in UNION query
  1292. // See http://dev.mysql.com/doc/refman/5.0/en/union.html
  1293. if (!is_null($this->order))
  1294. {
  1295. $this->clear('order');
  1296. }
  1297. // Set up the DISTINCT flag, the name with parentheses, and the glue.
  1298. if ($distinct)
  1299. {
  1300. $name = 'UNION DISTINCT ()';
  1301. $glue = ')' . PHP_EOL . 'UNION DISTINCT (';
  1302. }
  1303. else
  1304. {
  1305. $glue = ')' . PHP_EOL . 'UNION (';
  1306. $name = 'UNION ()';
  1307. }
  1308. // Get the Query\QueryElement if it does not exist
  1309. if (is_null($this->union))
  1310. {
  1311. $this->union = new Query\QueryElement($name, $query, "$glue");
  1312. }
  1313. else
  1314. // Otherwise append the second UNION.
  1315. {
  1316. $this->union->append($query);
  1317. }
  1318. return $this;
  1319. }
  1320. /**
  1321. * Add a query to UNION DISTINCT with the current query. Simply a proxy to Union with the Distinct clause.
  1322. *
  1323. * Usage:
  1324. * $query->unionDistinct('SELECT name FROM #__foo')
  1325. *
  1326. * @param mixed $query The Query object or string to union.
  1327. * @param string $glue The glue by which to join the conditions.
  1328. *
  1329. * @return mixed The Query object on success or boolean false on failure.
  1330. *
  1331. * @since 1.0
  1332. */
  1333. public function unionDistinct($query, $glue = '')
  1334. {
  1335. $distinct = true;
  1336. // Apply the distinct flag to the union.
  1337. return $this->union($query, $distinct, $glue);
  1338. }
  1339. /**
  1340. * Find and replace sprintf-like tokens in a format string.
  1341. * Each token takes one of the following forms:
  1342. * %% - A literal percent character.
  1343. * %[t] - Where [t] is a type specifier.
  1344. * %[n]$[x] - Where [n] is an argument specifier and [t] is a type specifier.
  1345. *
  1346. * Types:
  1347. * a - Numeric: Replacement text is coerced to a numeric type but not quoted or escaped.
  1348. * e - Escape: Replacement text is passed to $this->escape().
  1349. * E - Escape (extra): Replacement text is passed to $this->escape() with true as the second argument.
  1350. * n - Name Quote: Replacement text is passed to $this->quoteName().
  1351. * q - Quote: Replacement text is passed to $this->quote().
  1352. * Q - Quote (no escape): Replacement text is passed to $this->quote() with false as the second argument.
  1353. * r - Raw: Replacement text is used as-is. (Be careful)
  1354. *
  1355. * Date Types:
  1356. * - Replacement text automatically quoted (use uppercase for Name Quote).
  1357. * - Replacement text should be a string in date format or name of a date column.
  1358. * y/Y - Year
  1359. * m/M - Month
  1360. * d/D - Day
  1361. * h/H - Hour
  1362. * i/I - Minute
  1363. * s/S - Second
  1364. *
  1365. * Invariable Types:
  1366. * - Takes no argument.
  1367. * - Argument index not incremented.
  1368. * t - Replacement text is the result of $this->currentTimestamp().
  1369. * z - Replacement text is the result of $this->nullDate(false).
  1370. * Z - Replacement text is the result of $this->nullDate(true).
  1371. *
  1372. * Usage:
  1373. * $query->format('SELECT %1$n FROM %2$n WHERE %3$n = %4$a', 'foo', '#__foo', 'bar', 1);
  1374. * Returns: SELECT `foo` FROM `#__foo` WHERE `bar` = 1
  1375. *
  1376. * Notes:
  1377. * The argument specifier is optional but recommended for clarity.
  1378. * The argument index used for unspecified tokens is incremented only when used.
  1379. *
  1380. * @param string $format The formatting string.
  1381. *
  1382. * @return string Returns a string produced according to the formatting string.
  1383. *
  1384. * @since 1.0
  1385. */
  1386. public function format($format)
  1387. {
  1388. $query = $this;
  1389. $args = array_slice(func_get_args(), 1);
  1390. array_unshift($args, null);
  1391. $i = 1;
  1392. $func = function ($match) use ($query, $args, &$i)
  1393. {
  1394. if (isset($match[6]) && $match[6] == '%')
  1395. {
  1396. return '%';
  1397. }
  1398. // No argument required, do not increment the argument index.
  1399. switch ($match[5])
  1400. {
  1401. case 't':
  1402. return $query->currentTimestamp();
  1403. break;
  1404. case 'z':
  1405. return $query->nullDate(false);
  1406. break;
  1407. case 'Z':
  1408. return $query->nullDate(true);
  1409. break;
  1410. }
  1411. // Increment the argument index only if argument specifier not provided.
  1412. $index = is_numeric($match[4]) ? (int) $match[4] : $i++;
  1413. if (!$index || !isset($args[$index]))
  1414. {
  1415. // TODO - What to do? sprintf() throws a Warning in these cases.
  1416. $replacement = '';
  1417. }
  1418. else
  1419. {
  1420. $replacement = $args[$index];
  1421. }
  1422. switch ($match[5])
  1423. {
  1424. case 'a':
  1425. return 0 + $replacement;
  1426. break;
  1427. case 'e':
  1428. return $query->escape($replacement);
  1429. break;
  1430. case 'E':
  1431. return $query->escape($replacement, true);
  1432. break;
  1433. case 'n':
  1434. return $query->quoteName($replacement);
  1435. break;
  1436. case 'q':
  1437. return $query->quote($replacement);
  1438. break;
  1439. case 'Q':
  1440. return $query->quote($replacement, false);
  1441. break;
  1442. case 'r':
  1443. return $replacement;
  1444. break;
  1445. // Dates
  1446. case 'y':
  1447. return $query->year($query->quote($replacement));
  1448. break;
  1449. case 'Y':
  1450. return $query->year($query->quoteName($replacement));
  1451. break;
  1452. case 'm':
  1453. return $query->month($query->quote($replacement));
  1454. break;
  1455. case 'M':
  1456. return $query->month($query->quoteName($replacement));
  1457. break;
  1458. case 'd':
  1459. return $query->day($query->quote($replacement));
  1460. break;
  1461. case 'D':
  1462. return $query->day($query->quoteName($replacement));
  1463. break;
  1464. case 'h':
  1465. return $query->hour($query->quote($replacement));
  1466. break;
  1467. case 'H':
  1468. return $query->hour($query->quoteName($replacement));
  1469. break;
  1470. case 'i':
  1471. return $query->minute($query->quote($replacement));
  1472. break;
  1473. case 'I':
  1474. return $query->minute($query->quoteName($replacement));
  1475. break;
  1476. case 's':
  1477. return $query->second($query->quote($replacement));
  1478. break;
  1479. case 'S':
  1480. return $query->second($query->quoteName($replacement));
  1481. break;
  1482. }
  1483. return '';
  1484. };
  1485. /**
  1486. * Regexp to find an replace all tokens.
  1487. * Matched fields:
  1488. * 0: Full token
  1489. * 1: Everything following '%'
  1490. * 2: Everything following '%' unless '%'
  1491. * 3: Argument specifier and '$'
  1492. * 4: Argument specifier
  1493. * 5: Type specifier
  1494. * 6: '%' if full token is '%%'
  1495. */
  1496. return preg_replace_callback('#%(((([\d]+)\$)?([aeEnqQryYmMdDhHiIsStzZ]))|(%))#', $func, $format);
  1497. }
  1498. }