PageRenderTime 56ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 1ms

/atk4/lib/DB/dsql.php

https://github.com/mahimarathore/mahi
PHP | 1902 lines | 1034 code | 129 blank | 739 comment | 145 complexity | fd65fdc2f084d9d791dadb9ae38ad314 MD5 | raw file
Possible License(s): AGPL-3.0, MPL-2.0-no-copyleft-exception

Large files files are truncated, but you can click here to view the full file

  1. <?php // vim:ts=4:sw=4:et:fdm=marker
  2. /**
  3. This file is part of Agile Toolkit 4 http://agiletoolkit.org/
  4. (c) 2008-2013 Agile Toolkit Limited <info@agiletoolkit.org>
  5. Distributed under Affero General Public License v3 and
  6. commercial license.
  7. See LICENSE or LICENSE_COM for more information
  8. */
  9. /**
  10. * Implementation of SQL Query Abstraction Layer for Agile Toolkit
  11. *
  12. * @link http://agiletoolkit.org/doc/dsql
  13. */
  14. class DB_dsql extends AbstractModel implements Iterator {
  15. /**
  16. * Data accumulated by calling Definition methods, which is then
  17. * used when rendering
  18. */
  19. public $args=array();
  20. /** List of PDO parametical arguments for a query. Used only during rendering. */
  21. public $params=array();
  22. /** Manually-specified params */
  23. public $extra_params=array();
  24. /** PDO Statement, if query is prepared. Used by iterator */
  25. public $stmt=null;
  26. /** Expression to use when converting to string */
  27. public $template=null;
  28. /**
  29. * You can switch mode with select(), insert(), update() commands.
  30. * Mode is initialized to "select" by default
  31. */
  32. public $mode=null;
  33. /** Used to determine main table. */
  34. public $main_table=null;
  35. /** If no fields are defined, this field is used */
  36. public $default_field='*';
  37. public $default_exception='Exception_DB';
  38. /** call $q->debug() to turn on debugging. */
  39. public $debug=false;
  40. /** prefix for all parameteric variables: a, a_2, a_3, etc */
  41. public $param_base='a';
  42. /** When you convert this object to string, the following happens: */
  43. public $output_mode='getOne';
  44. /** Backtics are added around all fields. Set this to blank string to avoid */
  45. public $bt='`';
  46. /**
  47. * Templates are used to construct most common queries. Templates may be
  48. * changed in vendor-specific implementation of dsql (extending this class)
  49. */
  50. public $sql_templates=array(
  51. 'select'=>"select [options] [field] [from] [table] [join] [where] [group] [having] [order] [limit]",
  52. 'insert'=>"insert [options_insert] into [table_noalias] ([set_fields]) values ([set_values])",
  53. 'replace'=>"replace [options_replace] into [table_noalias] ([set_fields]) values ([set_value])",
  54. 'update'=>"update [table_noalias] set [set] [where]",
  55. 'delete'=>"delete from [table_noalias] [where]",
  56. 'truncate'=>'truncate table [table_noalias]'
  57. );
  58. /** required for non-id based tables */
  59. public $id_field;
  60. // {{{ Generic routines
  61. function _unique(&$array,$desired=null)
  62. {
  63. $desired=preg_replace('/[^a-zA-Z0-9:]/', '_', $desired);
  64. $desired=parent::_unique($array, $desired);
  65. return $desired;
  66. }
  67. function __clone()
  68. {
  69. $this->stmt=null;
  70. }
  71. function __toString()
  72. {
  73. try {
  74. if ($this->output_mode==='render') {
  75. return $this->render();
  76. } else {
  77. return (string)$this->getOne();
  78. }
  79. } catch (Exception $e) {
  80. $this->api->caughtException($e);
  81. //return "Exception: ".$e->getMessage();
  82. }
  83. return $this->toString();
  84. if ($this->expr) {
  85. return $this->parseTemplate($this->expr);
  86. }
  87. return $this->select();
  88. }
  89. /**
  90. * Explicitly sets template to your query. Remember to change
  91. * $this->mode if you switch this
  92. *
  93. * @param string $template New template to use by render
  94. *
  95. * @return DB_dsql $this
  96. */
  97. function template($template)
  98. {
  99. $this->template=$template;
  100. return $this;
  101. }
  102. /**
  103. * Change prefix for parametric values. Not really useful.
  104. *
  105. * @param string $param_base prefix to use for param names
  106. *
  107. * @return DB_dsql $this
  108. */
  109. function paramBase($param_base)
  110. {
  111. $this->param_base=$param_base;
  112. return $this;
  113. }
  114. /**
  115. * Create new dsql object linked with the same database connection and
  116. * bearing same data-type. You can use this method to create sub-queries.
  117. *
  118. * @return DB_dsql Empty query for same database
  119. */
  120. function dsql()
  121. {
  122. return $this->owner->dsql(get_class($this));
  123. }
  124. /**
  125. * Converts value into parameter and returns reference. Use only during
  126. * query rendering. Consider using `consume()` instead.
  127. *
  128. * @param string $val String literal containing input data
  129. *
  130. * @return string Safe and escapeed string
  131. */
  132. function escape($val)
  133. {
  134. if ($val===UNDEFINED) {
  135. return '';
  136. }
  137. if (is_array($val)) {
  138. $out=array();
  139. foreach ($val as $v) {
  140. $out[]=$this->escape($v);
  141. }
  142. return $out;
  143. }
  144. $name=':'.$this->param_base;
  145. $name=$this->_unique($this->params, $name);
  146. $this->params[$name]=$val;
  147. return $name;
  148. }
  149. /**
  150. * Recursively renders sub-query or expression, combining parameters.
  151. * If the argument is more likely to be a field, use tick=true
  152. *
  153. * @param object|string $dsql Expression
  154. * @param boolean $tick Preferred quoted style
  155. *
  156. * @return string Quoted expression
  157. */
  158. function consume($dsql, $tick = true)
  159. {
  160. if ($dsql===UNDEFINED) {
  161. return '';
  162. }
  163. if ($dsql===null) {
  164. return '';
  165. }
  166. if (is_object($dsql) && $dsql instanceof Field) {
  167. $dsql=$dsql->getExpr();
  168. }
  169. if (!is_object($dsql) || !$dsql instanceof DB_dsql) {
  170. return $tick?$this->bt($dsql):$dsql;
  171. }
  172. $dsql->params = &$this->params;
  173. $ret = $dsql->_render();
  174. if ($dsql->mode==='select') {
  175. $ret='('.$ret.')';
  176. }
  177. unset($dsql->params);
  178. $dsql->params=array();
  179. return $ret;
  180. }
  181. /**
  182. * Defines a custom tag variable. WARNING: always backtick / escaped
  183. * argument if it's unsafe
  184. *
  185. * @param string $tag Corresponds to [tag] inside template
  186. * @param string|object $value Value for the template tag
  187. *
  188. * @return DB_dsql $this
  189. */
  190. function setCustom($tag, $value = null)
  191. {
  192. if (is_array($tag)) {
  193. foreach ($tag as $key => $val) {
  194. $this->setCustom($key, $val);
  195. }
  196. return $this;
  197. }
  198. $this->args['custom'][$tag]=$value;
  199. return $this;
  200. }
  201. /**
  202. * Removes definition for argument. $q->del('where'), $q->del('fields') etc.
  203. *
  204. * @param string $args Could be 'field', 'where', 'order', 'limit', etc
  205. *
  206. * @return DB_dsql $this
  207. */
  208. function del($args)
  209. {
  210. $this->args[$args]=array();
  211. return $this;
  212. }
  213. /**
  214. * Removes all definitions. Start from scratch
  215. *
  216. * @return DB_dsql $this
  217. */
  218. function reset()
  219. {
  220. $this->args=array();
  221. return $this;
  222. }
  223. // }}}
  224. // {{{ Dynamic Query Definition methods
  225. // {{{ Generic methods
  226. /**
  227. * Returns new dynamic query and initializes it to use specific template.
  228. *
  229. * @param string $expr SQL Expression. Don't pass unverified input
  230. * @param array $tags Array of tags and values. @see setCustom()
  231. *
  232. * @return DB_dsql New dynamic query, won't affect $this
  233. */
  234. function expr($expr, $tags = array())
  235. {
  236. return $this->dsql()->useExpr($expr, $tags);
  237. }
  238. /**
  239. * Change template of existing query instead of creating new one. If unsure
  240. * use expr()
  241. *
  242. * @param string $expr SQL Expression. Don't pass unverified input
  243. * @param array $tags Obsolete, use templates / setCustom()
  244. *
  245. * @return DB_dsql $this
  246. */
  247. function useExpr($expr, $tags = array())
  248. {
  249. foreach ($tags as $key => $value) {
  250. if ($key[0] == ':') {
  251. $this->extra_params[$key] = $value;
  252. continue;
  253. }
  254. $this->args['custom'][$key]=$value;
  255. }
  256. $this->template=$expr;
  257. if ($tags) {
  258. $this->setCustom($tags);
  259. }
  260. $this->output_mode='render';
  261. return $this;
  262. }
  263. /**
  264. * Shortcut to produce expression which concatinates "where" clauses with
  265. * "OR" operator
  266. *
  267. * @return DB_dsql New dynamic query, won't affect $this
  268. */
  269. function orExpr()
  270. {
  271. return $this->expr('([orwhere])');
  272. }
  273. /**
  274. * Shortcut to produce expression for series of conditions concatinated
  275. * with "and". Useful to be passed inside where() or join()
  276. *
  277. * @return DB_dsql New dynamic query, won't affect $this
  278. */
  279. function andExpr()
  280. {
  281. return $this->expr('([andwhere])');
  282. }
  283. /**
  284. * Return expression containing a properly escaped field. Use make
  285. * subquery condition reference parent query
  286. *
  287. * @param string $fld Field in SQL table
  288. *
  289. * @return DB_dsql Expression pointing to specified field
  290. */
  291. function getField($fld)
  292. {
  293. if ($this->main_table===false ){
  294. throw $this->exception(
  295. 'Cannot use getField() when multiple tables are queried'
  296. );
  297. }
  298. return $this->expr(
  299. $this->bt($this->main_table).
  300. '.'.
  301. $this->bt($fld)
  302. );
  303. }
  304. // }}}
  305. // {{{ table()
  306. /**
  307. * Specifies which table to use in this dynamic query. You may specify
  308. * array to perform operation on multiple tables.
  309. *
  310. * Examples:
  311. * $q->table('user');
  312. * $q->table('user','u');
  313. * $q->table('user')->table('salary')
  314. * $q->table(array('user','salary'));
  315. * $q->table(array('user','salary'),'user');
  316. * $q->table(array('u'=>'user','s'=>'salary'));
  317. *
  318. * If you specify multiple tables, you still need to make sure to add
  319. * proper "where" conditions. All the above examples return $q (for chaining)
  320. *
  321. * You can also call table without arguments, which will return current table:
  322. *
  323. * echo $q->table();
  324. *
  325. * If multiple tables are used, "false" is returned. Return is not quoted.
  326. * Please avoid using table() without arguments as more tables may be
  327. * dynamically added later.
  328. *
  329. * @param string $table Specify table to use
  330. * @param string $alias Specify alias for the table
  331. *
  332. * @return DB_dsql $this
  333. **/
  334. function table($table = UNDEFINED, $alias = UNDEFINED)
  335. {
  336. if ($table===UNDEFINED) {
  337. return $this->main_table;
  338. }
  339. if (is_array($table)) {
  340. foreach ($table as $alias => $t) {
  341. if (is_numeric($alias)) {
  342. $alias=UNDEFINED;
  343. }
  344. $this->table($t, $alias);
  345. }
  346. return $this;
  347. }
  348. // main_table tracking allows us to
  349. if ($this->main_table===null) {
  350. $this->main_table=$alias===UNDEFINED||!$alias?$table:$alias;
  351. } elseif ($this->main_table) {
  352. $this->main_table=false; // query from multiple tables
  353. }
  354. $this->args['table'][]=array($table,$alias);
  355. return $this;
  356. }
  357. /**
  358. * Renders part of the template: [table]
  359. * Do not call directly
  360. *
  361. * @return string Parsed template chunk
  362. */
  363. function render_table()
  364. {
  365. $ret=array();
  366. if (!is_array($this->args['table'])) {
  367. return;
  368. }
  369. foreach ($this->args['table'] as $row) {
  370. list($table, $alias)=$row;
  371. $table=$this->bt($table);
  372. if ($alias!==UNDEFINED && $alias) {
  373. $table.=' '.$this->bt($alias);
  374. }
  375. $ret[]=$table;
  376. }
  377. return join(',', $ret);
  378. }
  379. /**
  380. * Conditionally returns "from", only if table is Specified
  381. * Do not call directly
  382. *
  383. * @return string Parsed template chunk
  384. */
  385. function render_from()
  386. {
  387. if ($this->args['table']) {
  388. return 'from';
  389. }
  390. return '';
  391. }
  392. /**
  393. * Returns template component [table_noalias]
  394. *
  395. * @return string Parsed template chunk
  396. */
  397. function render_table_noalias()
  398. {
  399. $ret=array();
  400. foreach ($this->args['table'] as $row) {
  401. list($table, $alias)=$row;
  402. $table=$this->bt($table);
  403. $ret[]=$table;
  404. }
  405. return join(', ', $ret);
  406. }
  407. // }}}
  408. // {{{ field()
  409. /**
  410. * Adds new column to resulting select by querying $field.
  411. *
  412. * Examples:
  413. * $q->field('name');
  414. *
  415. * Second argument specifies table for regular fields
  416. * $q->field('name','user');
  417. * $q->field('name','user')->field('line1','address');
  418. *
  419. * Array as a first argument will specify mulitple fields, same as calling field() multiple times
  420. * $q->field(array('name','surname'));
  421. *
  422. * Associative array will assume that "key" holds the alias. Value may be object.
  423. * $q->field(array('alias'=>'name','alias2'=>surname'));
  424. * $q->field(array('alias'=>$q->expr(..), 'alias2'=>$q->dsql()->.. ));
  425. *
  426. * You may use array with aliases together with table specifier.
  427. * $q->field(array('alias'=>'name','alias2'=>surname'),'user');
  428. *
  429. * You can specify $q->expr() for calculated fields. Alias is mandatory.
  430. * $q->field( $q->expr('2+2'),'alias'); // must always use alias
  431. *
  432. * You can use $q->dsql() for subqueries. Alias is mandatory.
  433. * $q->field( $q->dsql()->table('x')... , 'alias'); // must always use alias
  434. *
  435. * @param string|array $field Specifies field to select
  436. * @param string $table Specify if not using primary table
  437. * @param string $alias Specify alias for this field
  438. *
  439. * @return DB_dsql $this
  440. */
  441. function field($field, $table = null, $alias = null)
  442. {
  443. if (is_array($field)) {
  444. foreach ($field as $alias => $f) {
  445. if (is_numeric($alias)) {
  446. $alias=null;
  447. }
  448. $this->field($f, $table, $alias);
  449. }
  450. return $this;
  451. } elseif (is_string($field)) {
  452. $field=explode(',', $field);
  453. if (count($field) > 1) {
  454. foreach ($field as $f) {
  455. $this->field($f, $table, $alias);
  456. }
  457. return $this;
  458. }
  459. $field=$field[0];
  460. }
  461. if (is_object($field)) {
  462. $alias=$table;
  463. $table=null;
  464. }
  465. $this->args['fields'][]=array($field, $table, $alias);
  466. return $this;
  467. }
  468. /**
  469. * Removes all field definitions and returns only field you specify
  470. * as parameter to this method. Original query is not affected ($this)
  471. * Same as for field() syntax
  472. *
  473. * @param string|array $field Specifies field to select
  474. * @param string $table Specify if not using primary table
  475. * @param string $alias Specify alias for this field
  476. *
  477. * @return DB_dsql Clone of $this with only one field
  478. */
  479. function fieldQuery($field, $table = null, $alias = null)
  480. {
  481. $q=clone $this;
  482. return $q->del('fields')->field($field, $table, $alias);
  483. }
  484. /**
  485. * Returns template component [field]
  486. *
  487. * @return string Parsed template chunk
  488. */
  489. function render_field()
  490. {
  491. $result=array();
  492. if (!$this->args['fields']) {
  493. if ($this->default_field instanceof DB_dsql) {
  494. return $this->consume($this->default_field);
  495. }
  496. return (string)$this->default_field;
  497. }
  498. foreach ($this->args['fields'] as $row) {
  499. list($field,$table,$alias)=$row;
  500. if ($alias===$field) {
  501. $alias=UNDEFINED;
  502. }
  503. /**/$this->api->pr->start('dsql/render/field/consume');
  504. $field=$this->consume($field);
  505. /**/$this->api->pr->stop();
  506. if (!$field) {
  507. $field=$table;
  508. $table=UNDEFINED;
  509. }
  510. if ($table && $table!==UNDEFINED) {
  511. $field=$this->bt($table).'.'.$field;
  512. }
  513. if ($alias && $alias!==UNDEFINED) {
  514. $field.=' '.$this->bt($alias);
  515. }
  516. $result[]=$field;
  517. }
  518. return join(',', $result);
  519. }
  520. // }}}
  521. // {{{ where() and having()
  522. /**
  523. * Adds condition to your query
  524. *
  525. * Examples:
  526. * $q->where('id',1);
  527. *
  528. * Second argument specifies table for regular fields
  529. * $q->where('id>','1');
  530. * $q->where('id','>',1);
  531. *
  532. * You may use expressions
  533. * $q->where($q->expr('a=b'));
  534. * $q->where('date>',$q->expr('now()'));
  535. * $q->where($q->expr('length(password)'),'>',5);
  536. *
  537. * Finally, subqueries can also be used
  538. * $q->where('foo',$q->dsql()->table('foo')->field('name'));
  539. *
  540. * To specify OR conditions
  541. * $q->where($q->orExpr()->where('a',1)->where('b',1));
  542. *
  543. * you can also use the shortcut:
  544. *
  545. * $q->where(array('a is null','b is null'));
  546. *
  547. * @param mixed $field Field, array for OR or Expression
  548. * @param string $cond Condition such as '=', '>' or 'is not'
  549. * @param string $value Value. Will be quoted unless you pass expression
  550. * @param string $kind Do not use directly. Use having()
  551. *
  552. * @return DB_dsql $this
  553. */
  554. function where($field, $cond = UNDEFINED, $value = UNDEFINED, $kind = 'where')
  555. {
  556. if (is_array($field)) {
  557. // or conditions
  558. $or=$this->orExpr();
  559. foreach ($field as $row) {
  560. if (is_array($row)) {
  561. $or->where(
  562. $row[0],
  563. isset($row[1])?$row[1]:UNDEFINED,
  564. isset($row[2])?$row[2]:UNDEFINED
  565. );
  566. } elseif (is_object($row)) {
  567. $or->where($row);
  568. } else {
  569. $or->where($or->expr($row));
  570. }
  571. }
  572. $field=$or;
  573. $this->api->x=1;
  574. }
  575. if (is_string($field) && !preg_match('/^[.a-zA-Z0-9_]*$/', $field)) {
  576. // field contains non-alphanumeric values. Look for condition
  577. preg_match(
  578. '/^([^ <>!=]*)([><!=]*|( *(not|is|in|like))*) *$/',
  579. $field,
  580. $matches
  581. );
  582. $value=$cond;
  583. $cond=$matches[2];
  584. if (!$cond) {
  585. // IF COMPAT
  586. $matches[1]=$this->expr($field);
  587. if ($value && $value!==UNDEFINED) {
  588. $cond='=';
  589. } else {
  590. $cond=UNDEFINED;
  591. }
  592. }
  593. $field=$matches[1];
  594. }
  595. $this->args[$kind][]=array($field,$cond,$value);
  596. return $this;
  597. }
  598. /**
  599. * Same syntax as where()
  600. *
  601. * @param mixed $field Field, array for OR or Expression
  602. * @param string $cond Condition such as '=', '>' or 'is not'
  603. * @param string $value Value. Will be quoted unless you pass expression
  604. *
  605. * @return DB_dsql $this
  606. */
  607. function having($field, $cond = UNDEFINED, $value = UNDEFINED)
  608. {
  609. return $this->where($field, $cond, $value, 'having');
  610. }
  611. /**
  612. * Subroutine which renders either [where] or [having]
  613. *
  614. * @param string $kind 'where' or 'having'
  615. *
  616. * @return string Parsed chunk of query
  617. */
  618. function _render_where($kind)
  619. {
  620. $ret=array();
  621. foreach ($this->args[$kind] as $row) {
  622. list($field,$cond,$value)=$row;
  623. if (is_object($field)) {
  624. // if first argument is object, condition must be explicitly
  625. // specified
  626. $field=$this->consume($field);
  627. } else {
  628. list($table, $field)=explode('.', $field, 2);
  629. if ($field) {
  630. $field=$this->bt($table).'.'.$this->bt($field);
  631. } else {
  632. $field=$this->bt($table);
  633. }
  634. }
  635. if ($value===UNDEFINED && $cond===UNDEFINED) {
  636. $r=$field;
  637. $ret[]=$r;
  638. continue;
  639. }
  640. if ($value===UNDEFINED) {
  641. $value=$cond;
  642. $cond='=';
  643. if (is_array($value)) {
  644. $cond='in';
  645. }
  646. if (is_object($value) && @$value->mode==='select') {
  647. $cond='in';
  648. }
  649. } else {
  650. $cond=trim($cond);
  651. }
  652. if ($cond==='=' && $value===null) {
  653. $cond='is';
  654. }
  655. if ($cond==='in' && is_string($value)) {
  656. $value=explode(',', $value);
  657. }
  658. if (is_array($value)) {
  659. $v=array();
  660. foreach ($value as $vv) {
  661. $v[]=$this->escape($vv);
  662. }
  663. $value='('.join(',', $v).')';
  664. $cond='in';
  665. $r=$this->consume($field).' '.$cond.' '.$value;
  666. $ret[]=$r;
  667. continue;
  668. }
  669. if (is_object($value)) {
  670. $value=$this->consume($value);
  671. } else {
  672. $value=$this->escape($value);
  673. }
  674. $r=$field.' '.$cond.' '.$value;
  675. $ret[]=$r;
  676. }
  677. return $ret;
  678. }
  679. /**
  680. * Renders [where]
  681. *
  682. * @return string rendered SQL chunk
  683. */
  684. function render_where()
  685. {
  686. if (!$this->args['where']) {
  687. return;
  688. }
  689. return 'where '.join(' and ', $this->_render_where('where'));
  690. }
  691. /**
  692. * Renders [orwhere]
  693. *
  694. * @return string rendered SQL chunk
  695. */
  696. function render_orwhere()
  697. {
  698. if (!$this->args['where']) {
  699. return;
  700. }
  701. return join(' or ', $this->_render_where('where'));
  702. }
  703. /**
  704. * Renders [andwhere]
  705. *
  706. * @return string rendered SQL chunk
  707. */
  708. function render_andwhere()
  709. {
  710. if (!$this->args['where']) {
  711. return;
  712. }
  713. return join(' and ', $this->_render_where('where'));
  714. }
  715. /**
  716. * Renders [having]
  717. *
  718. * @return string rendered SQL chunk
  719. */
  720. function render_having()
  721. {
  722. if (!$this->args['having']) {
  723. return;
  724. }
  725. return 'having '.join(' and ', $this->_render_where('having'));
  726. }
  727. // }}}
  728. // {{{ join()
  729. /**
  730. * Joins your query with another table
  731. *
  732. * Examples:
  733. * $q->join('address'); // on user.address_id=address.id
  734. * $q->join('address.user_id'); // on address.user_id=user.id
  735. * $q->join('address a'); // With alias
  736. * $q->join(array('a'=>'address')); // Also alias
  737. *
  738. * Second argument may specify the field of the master table
  739. * $q->join('address', 'billing_id');
  740. * $q->join('address.code', 'code');
  741. * $q->join('address.code', 'user.code');
  742. *
  743. * Third argument may specify which kind of join to use.
  744. * $q->join('address', null, 'left');
  745. * $q->join('address.code', 'user.code', 'inner');
  746. *
  747. * Using array syntax you can join multiple tables too
  748. * $q->join(array('a'=>'address', 'p'=>'portfolio'));
  749. *
  750. * You can use expression for more complex joins
  751. * $q->join('address',
  752. * $q->exprOrExpr()
  753. * ->where('user.billing_id=address.id')
  754. * ->where('user.technical_id=address.id')
  755. * )
  756. *
  757. * @param string $foreign_table Table to join with
  758. * @param string $master_field Field in master table
  759. * @param string $join_kind 'left' or 'inner', etc
  760. * @param string $_foreign_alias Internal, don't use
  761. *
  762. * @return DB_dsql $this
  763. */
  764. function join(
  765. $foreign_table,
  766. $master_field = null,
  767. $join_kind = null,
  768. $_foreign_alias = null
  769. ) {
  770. // Compatibility mode
  771. if (isset($this->api->compat)) {
  772. if (strpos($foreign_table, ' ')) {
  773. list($foreign_table, $alias)=explode(' ', $foreign_table);
  774. $foreign_table=array($alias => $foreign_table);
  775. }
  776. if (strpos($master_field, '=')) {
  777. $master_field=$this->expr($master_field);
  778. }
  779. }
  780. // If array - add recursively
  781. if (is_array($foreign_table)) {
  782. foreach ($foreign_table as $alias => $foreign) {
  783. if (is_numeric($alias)) {
  784. $alias=null;
  785. }
  786. $this->join($foreign, $master_field, $join_kind, $alias);
  787. }
  788. return $this;
  789. }
  790. $j=array();
  791. // Split and deduce fields
  792. list($f1, $f2)=explode('.', $foreign_table, 2);
  793. if (is_object($master_field)) {
  794. $j['expr']=$master_field;
  795. } else {
  796. // Split and deduce primary table
  797. if (is_null($master_field)) {
  798. list($m1, $m2)=array(null, null);
  799. } else {
  800. list($m1, $m2)=explode('.', $master_field, 2);
  801. }
  802. if (is_null($m2)) {
  803. $m2=$m1;
  804. $m1=null;
  805. }
  806. if (is_null($m1)) {
  807. $m1=$this->main_table;
  808. }
  809. // Identify fields we use for joins
  810. if (is_null($f2) && is_null($m2)) {
  811. $m2=$f1.'_id';
  812. }
  813. if (is_null($m2)) {
  814. $m2='id';
  815. }
  816. $j['m1']=$m1;
  817. $j['m2']=$m2;
  818. }
  819. $j['f1']=$f1;
  820. if (is_null($f2)) {
  821. $f2='id';
  822. }
  823. $j['f2']=$f2;
  824. $j['t']=$join_kind?:'left';
  825. $j['fa']=$_foreign_alias;
  826. $this->args['join'][]=$j;
  827. return $this;
  828. }
  829. /**
  830. * Renders [join]
  831. *
  832. * @return string rendered SQL chunk
  833. */
  834. function render_join()
  835. {
  836. if (!$this->args['join']) {
  837. return '';
  838. }
  839. $joins=array();
  840. foreach ($this->args['join'] as $j) {
  841. $jj='';
  842. $jj.=$j['t'].' join ';
  843. $jj.=$this->bt($j['f1']);
  844. if (!is_null($j['fa'])) {
  845. $jj.=' as '.$this->bt($j['fa']);
  846. }
  847. $jj.=' on ';
  848. if ($j['expr']) {
  849. $jj.=$this->consume($j['expr']);
  850. } else {
  851. $jj.=
  852. $this->bt($j['fa']?:$j['f1']).'.'.
  853. $this->bt($j['f2']).' = '.
  854. $this->bt($j['m1']).'.'.
  855. $this->bt($j['m2']);
  856. }
  857. $joins[]=$jj;
  858. }
  859. return implode(' ', $joins);
  860. }
  861. // }}}
  862. // {{{ group()
  863. /**
  864. * Implemens GROUP BY functionality. Simply pass either string field
  865. * or expression
  866. *
  867. * @param string|object $group Group by this
  868. *
  869. * @return DB_dsql $this
  870. */
  871. function group($group)
  872. {
  873. return $this->_setArray($group, 'group');
  874. }
  875. /**
  876. * Renders [group]
  877. *
  878. * @return string rendered SQL chunk
  879. */
  880. function render_group()
  881. {
  882. if (!$this->args['group']) {
  883. return'';
  884. }
  885. $x=array();
  886. foreach ($this->args['group'] as $arg) {
  887. $x[]=$this->consume($arg);
  888. }
  889. return 'group by '.implode(', ', $x);
  890. }
  891. // }}}
  892. // {{{ order()
  893. /**
  894. * Orders results by field or Expression. See documentation for full
  895. * list of possible arguments
  896. *
  897. * $q->order('name');
  898. * $q->order('name desc');
  899. * $q->order('name desc, id asc')
  900. * $q->order('name',true);
  901. *
  902. * @param string $order Order by
  903. * @param string $desc true to sort descending
  904. *
  905. * @return DB_dsql $this
  906. */
  907. function order($order, $desc = null)
  908. {
  909. // Case with comma-separated fields or first argument being an array
  910. if (is_string($order) && strpos($order, ',')!==false) {
  911. // Check for multiple
  912. $order=explode(',', $order);
  913. }
  914. if (is_array($order)) {
  915. if (!is_null($desc)) {
  916. throw $this->exception(
  917. 'If first argument is array, second argument must not be used'
  918. );
  919. }
  920. foreach (array_reverse($order) as $o) {
  921. $this->order($o);
  922. }
  923. return $this;
  924. }
  925. // First argument may contain space, to divide field and keyword
  926. if (is_null($desc) && is_string($order) && strpos($order, ' ')!==false) {
  927. list($order, $desc)=array_map('trim', explode(' ', trim($order), 2));
  928. }
  929. if (is_string($order) && strpos($order, '.')!==false) {
  930. $order=join('.', $this->bt(explode('.', $order)));
  931. }
  932. if (is_bool($desc)) {
  933. $desc=$desc?'desc':'';
  934. } elseif (strtolower($desc)==='asc') {
  935. $desc='';
  936. } elseif ($desc && strtolower($desc)!='desc') {
  937. throw $this->exception('Incorrect ordering keyword')
  938. ->addMoreInfo('order by', $desc);
  939. }
  940. // TODO:
  941. /*
  942. if (isset($this->args['order'][0]) and (
  943. $this->args['order'][0] === array($order,$desc))) {
  944. }
  945. */
  946. $this->args['order'][]=array($order,$desc);
  947. return $this;
  948. }
  949. /**
  950. * Renders [order]
  951. *
  952. * @return string rendered SQL chunk
  953. */
  954. function render_order()
  955. {
  956. if (!$this->args['order']) {
  957. return'';
  958. }
  959. $x=array();
  960. foreach ($this->args['order'] as $tmp) {
  961. list($arg,$desc)=$tmp;
  962. $x[]=$this->consume($arg).($desc?(' '.$desc):'');
  963. }
  964. return 'order by '.implode(', ', array_reverse($x));
  965. }
  966. // }}}
  967. // {{{ option() and args()
  968. /**
  969. * Defines query option, such as DISTINCT
  970. *
  971. * @param string|expresion $option Option to put after SELECT
  972. *
  973. * @return DB_dsql $this
  974. */
  975. function option($option)
  976. {
  977. return $this->_setArray($option, 'options');
  978. }
  979. /**
  980. * Renders [options]
  981. *
  982. * @return string rendered SQL chunk
  983. */
  984. function render_options()
  985. {
  986. return @implode(' ', $this->args['options']);
  987. }
  988. /**
  989. * Defines insert query option, such as IGNORE
  990. *
  991. * @param string|expresion $option Option to put after SELECT
  992. *
  993. * @return DB_dsql $this
  994. */
  995. function option_insert($option)
  996. {
  997. return $this->_setArray($option, 'options_insert');
  998. }
  999. /**
  1000. * Renders [options_insert]
  1001. *
  1002. * @return string rendered SQL chunk
  1003. */
  1004. function render_options_insert()
  1005. {
  1006. if (!$this->args['options_insert']) {
  1007. return '';
  1008. }
  1009. return implode(' ', $this->args['options_insert']);
  1010. }
  1011. // }}}
  1012. // {{{ call() and function execution
  1013. /**
  1014. * Sets a template for a user-defined method call with specified arguments
  1015. *
  1016. * @param string $fx Name of the user defined method
  1017. * @param array $args Arguments in mixed form
  1018. *
  1019. * @return DB_dsql $this
  1020. */
  1021. function call($fx, $args = null)
  1022. {
  1023. $this->mode='call';
  1024. $this->args['fx']=$fx;
  1025. if (!is_null($args)) {
  1026. $this->args($args);
  1027. }
  1028. $this->template="call [fx]([args])";
  1029. return $this;
  1030. }
  1031. /**
  1032. * Executes a standard function with arguments, such as IF
  1033. *
  1034. * $q->fx('if', array($condition, $if_true, $if_false));
  1035. *
  1036. * @param string $fx Name of the built-in method
  1037. * @param array $args Arguments
  1038. *
  1039. * @return DB_dsql $this
  1040. */
  1041. function fx($fx, $args = null)
  1042. {
  1043. $this->mode='fx';
  1044. $this->args['fx']=$fx;
  1045. if (!is_null($args)) {
  1046. $this->args($args);
  1047. }
  1048. $this->template="[fx]([args])";
  1049. return $this;
  1050. }
  1051. /**
  1052. * set arguments for call(). Used by fx() and call() but you can use This
  1053. * with ->expr("in ([args])")->args($values);
  1054. *
  1055. * @param array $args Array with mixed arguments
  1056. *
  1057. * @return DB_dsql $this
  1058. */
  1059. function args($args)
  1060. {
  1061. return $this->_setArray($args, 'args', false);
  1062. }
  1063. /**
  1064. * Renders [args]
  1065. *
  1066. * @return string rendered SQL chunk
  1067. */
  1068. function render_args()
  1069. {
  1070. $x=array();
  1071. foreach ($this->args['args'] as $arg) {
  1072. $x[]=is_object($arg)?
  1073. $this->consume($arg):
  1074. $this->escape($arg);
  1075. }
  1076. return implode(', ', $x);
  1077. }
  1078. /**
  1079. * Sets IGNORE option
  1080. *
  1081. * @return DB_dsql $this
  1082. */
  1083. function ignore()
  1084. {
  1085. $this->args['options_insert'][]='ignore';
  1086. return $this;
  1087. }
  1088. /**
  1089. * Check if specified option was previously added
  1090. *
  1091. * @param string $option Which option to check?
  1092. *
  1093. * @return boolean
  1094. */
  1095. function hasOption($option)
  1096. {
  1097. return @in_array($option, $this->args['options']);
  1098. }
  1099. /**
  1100. * Check if specified insert option was previously added
  1101. *
  1102. * @param string $option Which option to check?
  1103. *
  1104. * @return boolean
  1105. */
  1106. function hasInsertOption($option)
  1107. {
  1108. return @in_array($option, $this->args['options_insert']);
  1109. }
  1110. // }}}
  1111. // {{{ limit()
  1112. /**
  1113. * Limit how many rows will be returned
  1114. *
  1115. * @param int $cnt Number of rows to return
  1116. * @param int $shift Offset, how many rows to skip
  1117. *
  1118. * @return DB_dsql $this
  1119. */
  1120. function limit($cnt, $shift = 0)
  1121. {
  1122. $this->args['limit']=array(
  1123. 'cnt'=>$cnt,
  1124. 'shift'=>$shift
  1125. );
  1126. return $this;
  1127. }
  1128. /**
  1129. * Renders [limit]
  1130. *
  1131. * @return string rendered SQL chunk
  1132. */
  1133. function render_limit()
  1134. {
  1135. if ($this->args['limit']) {
  1136. return 'limit '.
  1137. (int)$this->args['limit']['shift'].
  1138. ', '.
  1139. (int)$this->args['limit']['cnt'];
  1140. }
  1141. }
  1142. // }}}
  1143. // {{{ set()
  1144. /**
  1145. * Sets field value for INSERT or UPDATE statements
  1146. *
  1147. * @param string $field Name of the field
  1148. * @param mixed $value Value of the field
  1149. *
  1150. * @return DB_dsql $this
  1151. */
  1152. function set($field, $value = UNDEFINED)
  1153. {
  1154. if ($value===false) {
  1155. throw $this->exception('Value "false" is not supported by SQL');
  1156. }
  1157. if (is_array($field)) {
  1158. foreach ($field as $key => $value) {
  1159. $this->set($key, $value);
  1160. }
  1161. return $this;
  1162. }
  1163. if ($value===UNDEFINED) {
  1164. throw $this->exception('Specify value when calling set()');
  1165. }
  1166. $this->args['set'][$field]=$value;
  1167. return $this;
  1168. }
  1169. /**
  1170. * Renders [set] for UPDATE query
  1171. *
  1172. * @return string rendered SQL chunk
  1173. */
  1174. function render_set()
  1175. {
  1176. $x=array();
  1177. if ($this->args['set']) {
  1178. foreach($this->args['set'] as $field=>$value){
  1179. if (is_object($field)) {
  1180. $field=$this->consume($field);
  1181. } else {
  1182. $field=$this->bt($field);
  1183. }
  1184. if (is_object($value)) {
  1185. $value=$this->consume($value);
  1186. } else {
  1187. $value=$this->escape($value);
  1188. }
  1189. $x[]=$field.'='.$value;
  1190. }
  1191. }
  1192. return join(', ', $x);
  1193. }
  1194. /**
  1195. * Renders [set_fields] for INSERT
  1196. *
  1197. * @return string rendered SQL chunk
  1198. */
  1199. function render_set_fields()
  1200. {
  1201. $x=array();
  1202. if ($this->args['set']) {
  1203. foreach ($this->args['set'] as $field => $value) {
  1204. if (is_object($field)) {
  1205. $field=$this->consume($field);
  1206. } else {
  1207. $field=$this->bt($field);
  1208. }
  1209. $x[]=$field;
  1210. }
  1211. }
  1212. return join(',', $x);
  1213. }
  1214. /**
  1215. * Renders [set_values] for INSERT
  1216. *
  1217. * @return string rendered SQL chunk
  1218. */
  1219. function render_set_values()
  1220. {
  1221. $x=array();
  1222. if ($this->args['set']) {
  1223. foreach ($this->args['set'] as $field => $value) {
  1224. if (is_object($value)) {
  1225. $value=$this->consume($value);
  1226. } else {
  1227. $value=$this->escape($value);
  1228. }
  1229. $x[]=$value;
  1230. }
  1231. }
  1232. return join(',', $x);
  1233. }
  1234. // }}}
  1235. // {{{ Miscelanious
  1236. /**
  1237. * Adds backtics around argument. This will allow you to use reserved
  1238. * SQL words as table or field names such as "table"
  1239. *
  1240. * @param string $s any string
  1241. *
  1242. * @return string Quoted string
  1243. */
  1244. function bt($s)
  1245. {
  1246. if (is_array($s)) {
  1247. $out=array();
  1248. foreach ($s as $ss) {
  1249. $out[]=$this->bt($ss);
  1250. }
  1251. return $out;
  1252. }
  1253. if (!$this->bt
  1254. || is_object($s)
  1255. || $s==='*'
  1256. || strpos($s, '.')!==false
  1257. || strpos($s, '(')!==false
  1258. || strpos($s, $this->bt)!==false
  1259. ) {
  1260. return $s;
  1261. }
  1262. return $this->bt.$s.$this->bt;
  1263. }
  1264. /**
  1265. * Internal method which can be used by simple param-giving methods such
  1266. * as option(), group(), etc
  1267. *
  1268. * @param string $values hm
  1269. * @param string $name hm
  1270. * @param boolean $parse_commas hm
  1271. *
  1272. * @private
  1273. * @return DB_dsql $this
  1274. */
  1275. function _setArray($values, $name, $parse_commas = true)
  1276. {
  1277. if (is_string($values) && $parse_commas && strpos($values, ',')) {
  1278. $values=explode(',', $values);
  1279. }
  1280. if (!is_array($values)) {
  1281. $values=array($values);
  1282. }
  1283. if (!isset($this->args[$name])) {
  1284. $this->args[$name]=array();
  1285. }
  1286. $this->args[$name]=array_merge($this->args[$name], $values);
  1287. return $this;
  1288. }
  1289. // }}}
  1290. // }}}
  1291. // {{{ Statement templates and interfaces
  1292. /**
  1293. * Switch template for this query. Determines what would be done
  1294. * on execute.
  1295. *
  1296. * By default it is in SELECT mode
  1297. *
  1298. * @param string $mode A key for $this->sql_templates
  1299. *
  1300. * @return DB_dsql $this
  1301. */
  1302. function SQLTemplate($mode)
  1303. {
  1304. $this->mode=$mode;
  1305. $this->template=$this->sql_templates[$mode];
  1306. return $this;
  1307. }
  1308. /**
  1309. * Return expression for concatinating multiple values
  1310. * Accepts variable number of arguments, all of them would be
  1311. * escaped
  1312. *
  1313. * @return DB_dsql clone of $this
  1314. */
  1315. function concat()
  1316. {
  1317. $t=clone $this;
  1318. return $t->fx('concat', func_get_args());
  1319. }
  1320. /**
  1321. * Creates a query for listing tables in databse-specific form
  1322. * Agile Toolkit DSQL does not pretend to know anything about model
  1323. * structure, so result parsing is up to you
  1324. *
  1325. * @param string $table Table
  1326. *
  1327. * @return DB_dsql clone of $this
  1328. */
  1329. function describe($table)
  1330. {
  1331. return $this->expr('desc [desc_table]')
  1332. ->setCustom('desc_table', $this->bt($table));
  1333. }
  1334. /**
  1335. * Renders [fx]
  1336. *
  1337. * @return string rendered SQL chunk
  1338. */
  1339. function render_fx()
  1340. {
  1341. return $this->args['fx'];
  1342. }
  1343. /**
  1344. * Creates expression for SUM()
  1345. *
  1346. * @param string|object $arg Typically an expression of a sub-query
  1347. *
  1348. * @return DB_dsql clone of $this
  1349. */
  1350. function sum($arg = null)
  1351. {
  1352. if (is_null($arg)) {
  1353. $arg='*';
  1354. }
  1355. return $this->expr('sum([sum])')->setCustom('sum', $this->bt($arg));
  1356. }
  1357. /**
  1358. * Creates expression for COUNT()
  1359. *
  1360. * @param string|object $arg Typically an expression of a sub-query
  1361. *
  1362. * @return DB_dsql clone of $this
  1363. */
  1364. function count($arg = null)
  1365. {
  1366. if (is_null($arg)) {
  1367. $arg='*';
  1368. }
  1369. return $this->expr('count([count])')->setCustom('count', $this->bt($arg));
  1370. }
  1371. /**
  1372. * Returns method for generating random numbers. This is used for ordering
  1373. * table in random order
  1374. *
  1375. * @return DB_dsql clone of $this
  1376. */
  1377. function random()
  1378. {
  1379. return $this->expr('rand()');
  1380. }
  1381. // }}}
  1382. // {{{ More complex query generations and specific cases
  1383. /**
  1384. * Executes current query
  1385. *
  1386. * @return DB_dsql $this
  1387. */
  1388. function execute()
  1389. {
  1390. try {
  1391. /**/$this->api->pr->start('dsql/execute/render');
  1392. $q=$this->render();
  1393. /**/$this->api->pr->next('dsql/execute/query');
  1394. $this->stmt=$this->owner->query($q, $this->params);
  1395. $this->template=$this->mode=null;
  1396. /**/$this->api->pr->stop();
  1397. return $this;
  1398. } catch (PDOException $e) {
  1399. throw $this->exception('Database Query Failed')
  1400. ->addPDOException($e)
  1401. ->addMoreInfo('mode', $this->mode)
  1402. ->addMoreInfo('params', $this->params)
  1403. ->addMoreInfo('query', $q)
  1404. ->addMoreInfo('template', $this->template)
  1405. ;
  1406. }
  1407. }
  1408. /**
  1409. * Executes select query.
  1410. *
  1411. * @return DB_dsql() $this
  1412. */
  1413. function select()
  1414. {
  1415. return $this->SQLTemplate('select')->execute();
  1416. }
  1417. /**
  1418. * Executes insert query. Returns ID of new record.
  1419. *
  1420. * @return int new record ID (from last_id)
  1421. */
  1422. function insert()
  1423. {
  1424. $this->SQLTemplate('insert')->execute();
  1425. return
  1426. $this->hasInsertOption('ignore')?null:
  1427. $this->owner->lastID();
  1428. }
  1429. /**
  1430. * Inserts multiple rows of data. Uses ignore option
  1431. * AVOID using this, might not be implemented correctly
  1432. *
  1433. * @param array $array Insert multiple rows into table with one query
  1434. *
  1435. * @return array List of IDs
  1436. */
  1437. function insertAll($array)
  1438. {
  1439. $ids=array();
  1440. foreach ($array as $hash) {
  1441. $ids[]=$this->del('set')->set($hash)->insert();
  1442. }
  1443. return $ids;
  1444. }
  1445. /**
  1446. * Executes update query
  1447. *
  1448. * @return DB_dsql $this
  1449. */
  1450. function update()
  1451. {
  1452. return $this->SQLTemplate('update')->execute();
  1453. }
  1454. /**
  1455. * Executes replace query
  1456. *
  1457. * @return DB_dsql $this
  1458. */
  1459. function replace()
  1460. {
  1461. return $this->SQLTemplate('replace')->execute();
  1462. }
  1463. /**
  1464. * Executes delete query
  1465. *
  1466. * @return DB_dsql $this
  1467. */
  1468. function delete()
  1469. {
  1470. return $this->SQLTemplate('delete')->execute();
  1471. }
  1472. /**
  1473. * Executes truncate query
  1474. *
  1475. * @return DB_dsql $this
  1476. */
  1477. function truncate()
  1478. {
  1479. return $this->SQLTemplate('truncate')->execute();
  1480. }
  1481. /** @obsolete, use select() */
  1482. function do_select(){
  1483. return $this->select();
  1484. }
  1485. /** @obsolete, use insert() */
  1486. function do_insert(){
  1487. return $this->insert();
  1488. }
  1489. /** @obsolete, use update() */
  1490. function do_update(){
  1491. return $this->update();
  1492. }
  1493. /** @obsolete, use replace() */
  1494. function do_replace(){
  1495. return $this->replace();
  1496. }
  1497. // }}}
  1498. // {{{ Data fetching modes
  1499. /**
  1500. * Will execute DSQL query and return all results inside array of hashes
  1501. *
  1502. * @return array Array of associative arrays
  1503. */
  1504. function get()
  1505. {
  1506. if (!$this->stmt) {
  1507. $this->execute();
  1508. }
  1509. $res=$this->stmt->fetchAll(PDO::FETCH_ASSOC);
  1510. $this->rewind();
  1511. $this->stmt=null;
  1512. return $res;
  1513. }
  1514. /**
  1515. * Will execute DSQL query and return first column of a first row
  1516. *
  1517. * You can also simply cast your DSQL into string to get this value
  1518. *
  1519. * echo $dsql;
  1520. *
  1521. * @return string Value of first column in first row
  1522. */
  1523. function getOne()
  1524. {
  1525. $res=$this->getRow();
  1526. $this->rewind();
  1527. $this->stmt=null;
  1528. return $res[0];
  1529. }
  1530. /**
  1531. * Will execute DSQL query and return first row as array (not hash). If
  1532. * you call several times will return subsequent rows
  1533. *
  1534. * @return array Next row of your data (not hash)
  1535. */
  1536. function getRow()
  1537. {
  1538. return $this->fetch(PDO::FETCH_NUM);
  1539. }
  1540. /**
  1541. * Will execute DSQL query and return first row as hash (column=>value)
  1542. *
  1543. * @return array Hash of next row in data stream
  1544. */
  1545. function getHash()
  1546. {
  1547. return $this->fetch(PDO::FETCH_ASSOC);
  1548. }
  1549. /**
  1550. * Will execute the query (if it's not executed already) and return
  1551. * first row
  1552. *
  1553. * @param int $mode PDO fetch mode
  1554. *
  1555. * @return mixed return result of PDO::fetch
  1556. */
  1557. function fetch($mode = PDO::FETCH_ASSOC)
  1558. {
  1559. if (!$this->stmt) {
  1560. $this->execute();
  1561. }
  1562. return $this->stmt->fetch($mode);
  1563. }
  1564. // {{{ Obsolete functions
  1565. /** @obsolete. Use get() */
  1566. function fetchAll(){
  1567. return $this->get();
  1568. }
  1569. /** @obsolete. Use getQne() */
  1570. function do_getOne(){
  1571. return $this->getOne();
  1572. }
  1573. /** @obsolete. Use get() */
  1574. function do_getAllHash(){
  1575. return $this->get();
  1576. }
  1577. function do_getAll(){
  1578. return $this->get();
  1579. }
  1580. /** @obsolete. Use get() */
  1581. function getAll(){
  1582. return $this->get();
  1583. }
  1584. /** @obsolete. Use getRow() */
  1585. function do_getRow(){
  1586. return $this->getRow();
  1587. }
  1588. /** @obsolete. Use getHash() */
  1589. function do_getHash(){
  1590. return $this->getHash();
  1591. }
  1592. // }}}
  1593. /**
  1594. * Sets flag to hint SQL (if supported) to prepare total number of columns.
  1595. * Use foundRows() to read this afterwards
  1596. *
  1597. * @return DB_dsql $this
  1598. */
  1599. function calcFoundRows(){
  1600. return $this;
  1601. }
  1602. /**
  1603. * Obsolete - naming bug
  1604. */
  1605. function calc_found_rows()
  1606. {
  1607. return $this->calcFoundRows();
  1608. }
  1609. /**
  1610. * After fetching data, call this to find out how many rows there were in
  1611. * total. Call calcFoundRows() for better performance
  1612. *
  1613. * @return string number of results
  1614. */
  1615. function foundRows()
  1616. {
  1617. if ($this->hasOption('SQL_CALC_FOUND_ROWS')) {
  1618. return $this->owner->getOne('select found_rows()');
  1619. }
  1620. /* db-compatibl way: */
  1621. $c=clone $this;
  1622. $c->del('limit');
  1623. $c->fieldQuery('count(*)');
  1624. return $c->getOne();
  1625. }
  1626. // }}}
  1627. // {{{ Iterator support
  1628. public $data=false;
  1629. public $_iterating=false;
  1630. public $preexec=false;
  1631. /**
  1632. * Execute query faster, but don't fetch data until iterating started. This
  1633. * can be done if you need to know foundRows() before fetching data
  1634. *
  1635. * @return DB_dsql $this
  1636. */
  1637. function preexec()
  1638. {
  1639. $this->execute();
  1640. $this->preexec=true;
  1641. return $this;
  1642. }
  1643. function rewind()
  1644. {
  1645. if($this->_iterating){
  1646. $this->stmt=null;
  1647. $this->_iterating=false;
  1648. }
  1649. $this->_iterating=true;
  1650. return $this;
  1651. }
  1652. function next()
  1653. {
  1654. $this->data = $this->fetch();
  1655. return $this;
  1656. }
  1657. function current()
  1658. {
  1659. return $this->data;
  1660. }
  1661. function key()
  1662. {
  1663. return $this->data[$this->id_field];
  1664. }
  1665. function valid()
  1666. {
  1667. if(!$this->stmt || $this->preexec){
  1668. $this->preexec=false;
  1669. $this->data = $this->fetch();
  1670. }
  1671. return (boolean)$this->data;
  1672. }
  1673. // }}}
  1674. // {{{ Rendering
  1675. /**
  1676. * Will set a flag which will output query (echo) as it is being rendered.
  1677. *
  1678. * @return DB_dsql $this
  1679. */
  1680. function debug()
  1681. {
  1682. $this->debug=1;
  1683. return $this;
  1684. }
  1685. /**
  1686. * Return formatted debug output
  1687. *
  1688. * @param string $r Rendered material
  1689. *
  1690. * @return string debug of the query
  1691. */
  1692. function getDebugQuery($r = null)
  1693. {
  1694. if (!$r) {
  1695. $r=$this->_render();
  1696. }
  1697. $d=$r;
  1698. $pp=array();
  1699. $d=preg_replace('/`([^`]*)`/', '`<font color="black">\1</font>`', $d);
  1700. foreach (array_reverse($this->params) as $key => $val) {
  1701. if (is_string($val)) {
  1702. $d=preg_replace('/'.$key.'([^_]|$)/', '"<font color="green">'.
  1703. htmlspecialchars(addslashes($val)).'</font>"\1', $d);
  1704. } elseif (is_null($val)) {
  1705. $d=preg_replace(
  1706. '/'.$key.'([^_]|$)/',
  1707. '<font color="black">NULL</font>\1',
  1708. $d
  1709. );
  1710. } elseif (is_numeric($val)) {
  1711. $d=preg_replace(
  1712. '/'.$key.'([^_]|$)/',
  1713. '<font color="red">'.$val.'</font>\1',
  1714. $d
  1715. );
  1716. } else {
  1717. $d=preg_replace('/'.$key.'([^_]|$)/', $val.'\1', $d);
  1718. }
  1719. $pp[]=$key;
  1720. }
  1721. return "<font color='blue'>".$d."</font> <font color='gray'>[".
  1722. join(', ', $pp)."]</font><br/>";
  1723. }
  1724. /**
  1725. * Converts query into string format. This will contain parametric
  1726. * references
  1727. *
  1728. * @return string resulting query
  1729. */
  1730. function render()
  1731. {
  1732. $this->params=$this->extra_params;
  1733. $r=$this->_render();

Large files files are truncated, but you can click here to view the full file