PageRenderTime 28ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 0ms

/atk4/lib/DB/dsql.php

https://github.com/intuititve/lostandfound
PHP | 999 lines | 697 code | 76 blank | 226 comment | 110 complexity | fd8b386a3cf4e9b1a0c6cf72679c0ad4 MD5 | raw file
Possible License(s): AGPL-3.0
  1. <?php // vim:ts=4:sw=4:et:fdm=marker
  2. /**
  3. * Implementation of PDO-compatible dynamic queries
  4. * @link http://agiletoolkit.org/doc/dsql
  5. *
  6. * Use:
  7. * $this->api->dbConnect();
  8. * $query = $this->api->db->dsql();
  9. *
  10. * @license See http://agiletoolkit.org/about/license
  11. *
  12. **/
  13. class DB_dsql extends AbstractModel implements Iterator {
  14. /** Data accumulated by calling Definition methods, which is then used when rendering */
  15. public $args=array();
  16. /** List of PDO parametical arguments for a query. Used only during rendering. */
  17. public $params=array();
  18. /** Manually-specified params */
  19. public $extra_params=array();
  20. /** PDO Statement, if query is prepared. Used by iterator */
  21. public $stmt=null;
  22. /** Expression to use when converting to string */
  23. public $template=null;
  24. /** You can switch mode with select(), insert(), update() commands. Mode is initialized to "select" by default */
  25. public $mode=null;
  26. /** Used to determine main table. */
  27. public $main_table=null;
  28. /** If no fields are defined, this field is used */
  29. public $default_field='*';
  30. public $default_exception='Exception_DB';
  31. /** call $q->debug() to turn on debugging. */
  32. public $debug=false;
  33. /** prefix for all parameteric variables: a, a_2, a_3, etc */
  34. public $param_base='a';
  35. public $sql_templates=array(
  36. 'select'=>"select [options] [field] [from] [table] [join] [where] [group] [having] [order] [limit]",
  37. 'insert'=>"insert [options_insert] into [table_noalias] ([set_fields]) values ([set_values])",
  38. 'replace'=>"replace [options_replace] into [table_noalias] ([set_fields]) values ([set_value])",
  39. 'update'=>"update [table_noalias] set [set] [where]",
  40. 'delete'=>"delete from [table_noalias] [where]",
  41. 'truncate'=>'truncate table [table_noalias]'
  42. );
  43. // {{{ Generic stuff
  44. function _unique(&$array,$desired=null){
  45. $desired=preg_replace('/[^a-zA-Z0-9:]/','_',$desired);
  46. $desired=parent::_unique($array,$desired);
  47. return $desired;
  48. }
  49. function __clone(){
  50. $this->stmt=null;
  51. }
  52. function __toString(){
  53. try {
  54. return $this->render();
  55. }catch(Exception $e){
  56. return "Exception: ".$e->getText();
  57. }
  58. return $this->toString();
  59. if($this->expr)return $this->parseTemplate($this->expr);
  60. return $this->select();
  61. }
  62. /** Explicitly sets template to your query. Remember to change $this->mode if you switch this */
  63. function template($template){
  64. $this->template=$template;
  65. return $this;
  66. }
  67. /** Change prefix for parametric values. Useful if you are combining multiple queries. */
  68. function paramBase($param_base){
  69. $this->param_base=$param_base;
  70. return $this;
  71. }
  72. /** Create new dsql object which can then be used as sub-query. */
  73. function dsql(){
  74. return $this->owner->dsql(get_class($this));
  75. }
  76. /** Converts value into parameter and returns reference. Use only during query rendering. */
  77. function escape($val){
  78. if($val===undefined)return '';
  79. if(is_array($val)){
  80. $out=array();
  81. foreach($val as $v){
  82. $out[]=$this->escape($v);
  83. }
  84. return $out;
  85. }
  86. $name=':'.$this->param_base;
  87. $name=$this->_unique($this->params,$name);
  88. $this->params[$name]=$val;
  89. return $name;
  90. }
  91. /** Recursively renders sub-query or expression, combining parameters */
  92. function consume($dsql,$tick=true){
  93. if($dsql===undefined)return '';
  94. if($dsql===null)return '';
  95. if(is_object($dsql) && $dsql instanceof Field)return $dsql->getExpr();
  96. if(!is_object($dsql) || !$dsql instanceof DB_dsql)return $tick?$this->bt($dsql):$dsql;
  97. $dsql->params = &$this->params;
  98. $ret = $dsql->_render();
  99. if($dsql->mode=='select')$ret='('.$ret.')';
  100. unset($dsql->params);$dsql->params=array();
  101. return $ret;
  102. }
  103. /** Defines a custom template variable. */
  104. function setCustom($template,$value){
  105. $this->args['custom'][$template]=$value;
  106. return $this;
  107. }
  108. /** Removes definition for argument. $q->del('where') */
  109. function del($args){
  110. $this->args[$args]=array();
  111. return $this;
  112. }
  113. /** Removes all definitions. Start from scratch */
  114. function reset(){
  115. $this->args=array();
  116. }
  117. // }}}
  118. // {{{ Dynamic Query Definition methods
  119. // {{{ Generic methods
  120. /** Returns new dynamic query and initializes it to use specific template. */
  121. function expr($expr,$params=array()){
  122. return $this->dsql()->useExpr($expr,$params);
  123. }
  124. /** Shortcut to produce expression which concatinates "where" clauses with "OR" operator */
  125. function orExpr(){
  126. return $this->expr('([orwhere])');
  127. }
  128. /** @private Change template and bind parameters for existing query */
  129. function useExpr($expr,$params=array()){
  130. $this->template=$expr;
  131. $this->extra_params=$params;
  132. return $this;
  133. }
  134. /** Return expression containing a properly escaped field. Use make subquery condition reference parent query */
  135. function getField($fld){
  136. if($this->main_table===false)throw $this->exception('Cannot use getField() when multiple tables are queried');
  137. return $this->expr(
  138. $this->bt($this->main_table).
  139. '.'.
  140. $this->bt($fld)
  141. );
  142. }
  143. // }}}
  144. // {{{ table()
  145. /**
  146. * Specifies which table to use in this dynamic query. You may specify array to perform operation on multiple tables.
  147. *
  148. * @link http://agiletoolkit.org/doc/dsql/where
  149. *
  150. * Examples:
  151. * $q->table('user');
  152. * $q->table('user','u');
  153. * $q->table('user')->table('salary')
  154. * $q->table(array('user','salary'));
  155. * $q->table(array('user','salary'),'user');
  156. * $q->table(array('u'=>'user','s'=>'salary'));
  157. *
  158. * If you specify multiple tables, you still need to make sure to add proper "where" conditions. All the above examples
  159. * return $q (for chaining)
  160. *
  161. * You can also call table without arguments, which will return current table:
  162. *
  163. * echo $q->table();
  164. *
  165. * If multiple tables are used, "false" is returned. Return is not quoted. Please avoid using table() without arguments
  166. * as more tables may be dynamically added later.
  167. **/
  168. function table($table=undefined,$alias=undefined){
  169. if($table===undefined)return $this->main_table;
  170. if(is_array($table)){
  171. foreach($table as $alias=>$t){
  172. if(is_numeric($alias))$alias=undefined;
  173. $this->table($t,$alias);
  174. }
  175. return $this;
  176. }
  177. // main_table tracking allows us to
  178. if($this->main_table===null)$this->main_table=$alias===undefined||!$alias?$table:$alias;
  179. elseif($this->main_table)$this->main_table=false; // query from multiple tables
  180. $this->args['table'][]=array($table,$alias);
  181. return $this;
  182. }
  183. /** Returns template component [table] */
  184. function render_table(){
  185. $ret=array();
  186. if(!is_array($this->args['table']))return;
  187. foreach($this->args['table'] as $row){
  188. list($table,$alias)=$row;
  189. $table=$this->bt($table);
  190. if($alias!==undefined && $alias)$table.=' '.$this->bt($alias);
  191. $ret[]=$table;
  192. }
  193. return join(',',$ret);
  194. }
  195. /** Conditionally returns "from", only if table is specified */
  196. function render_from(){
  197. if($this->args['table'])return 'from';
  198. return '';
  199. }
  200. /** Returns template component [table_noalias] */
  201. function render_table_noalias(){
  202. $ret=array();
  203. foreach($this->args['table'] as $row){
  204. list($table,$alias)=$row;
  205. $table=$this->bt($table);
  206. $ret[]=$table;
  207. }
  208. return join(', ',$ret);
  209. }
  210. // }}}
  211. // {{{ field()
  212. /**
  213. * Adds new column to resulting select by querying $field.
  214. * @link http://agiletoolkit.org/doc/dsql/field
  215. *
  216. * Examples:
  217. * $q->field('name');
  218. *
  219. * Second argument specifies table for regular fields
  220. * $q->field('name','user');
  221. * $q->field('name','user')->field('line1','address');
  222. *
  223. * Array as a first argument will specify mulitple fields, same as calling field() multiple times
  224. * $q->field(array('name','surname'));
  225. *
  226. * Associative array will assume that "key" holds the alias. Value may be object.
  227. * $q->field(array('alias'=>'name','alias2'=>surname'));
  228. * $q->field(array('alias'=>$q->expr(..), 'alias2'=>$q->dsql()->.. ));
  229. *
  230. * You may use array with aliases together with table specifier.
  231. * $q->field(array('alias'=>'name','alias2'=>surname'),'user');
  232. *
  233. * You can specify $q->expr() for calculated fields. Alias is mandatory.
  234. * $q->field( $q->expr('2+2'),'alias'); // must always use alias
  235. *
  236. * You can use $q->dsql() for subqueries. Alias is mandatory.
  237. * $q->field( $q->dsql()->table('x')... , 'alias'); // must always use alias
  238. *
  239. */
  240. function field($field,$table=null,$alias=null) {
  241. if(is_array($field)){
  242. foreach($field as $alias=>$f){
  243. if(is_numeric($alias))$alias=null;
  244. $this->field($f,$table,$alias);
  245. }
  246. return $this;
  247. }elseif(is_string($field)){
  248. $field=explode(',',$field);
  249. if(count($field)>1){
  250. foreach($field as $f){
  251. $this->field($f,$table,$alias);
  252. }
  253. return $this;
  254. }
  255. $field=$field[0];
  256. }
  257. if(is_object($field)){
  258. //if(!$table)throw $this->exception('Specified expression without alias')
  259. // ->addMoreInfo('expr',$field);
  260. $alias=$table;$table=null;
  261. }
  262. $this->args['fields'][]=array($field,$table,$alias);
  263. return $this;
  264. }
  265. function render_field(){
  266. $result=array();
  267. if(!$this->args['fields']){
  268. //if($this->main_table)return '*.'.$this->main_table;
  269. return (string)$this->default_field;
  270. }
  271. foreach($this->args['fields'] as $row){
  272. list($field,$table,$alias)=$row;
  273. if($alias==$field)$alias=undefined;
  274. /**/$this->api->pr->start('dsql/render/field/consume');
  275. $field=$this->consume($field);
  276. /**/$this->api->pr->stop();
  277. if(!$field){
  278. $field=$table;
  279. $table=undefined;
  280. }
  281. if($table && $table!==undefined)$field=$this->bt($table).'.'.$field;
  282. if($alias && $alias!==undefined)$field.=' '.$this->bt($alias);
  283. $result[]=$field;
  284. }
  285. return join(',',$result);
  286. }
  287. // }}}
  288. // {{{ where() and having()
  289. /**
  290. * Adds condition to your query
  291. * @link http://agiletoolkit.org/doc/dsql/where
  292. *
  293. * Examples:
  294. * $q->where('id',1);
  295. *
  296. * Second argument specifies table for regular fields
  297. * $q->where('id>','1');
  298. * $q->where('id','>',1);
  299. *
  300. * You may use expressions
  301. * $q->where($q->expr('a=b'));
  302. * $q->where('date>',$q->expr('now()'));
  303. * $q->where($q->expr('length(password)'),'>',5);
  304. *
  305. * Finally, subqueries can also be used
  306. * $q->where('foo',$q->dsql()->table('foo')->field('name'));
  307. *
  308. * To specify OR conditions
  309. * $q->where($q->orExpr()->where('a',1)->where('b',1));
  310. *
  311. * you can also use the shortcut:
  312. *
  313. * $q->where(array('a is null','b is null'));
  314. */
  315. function where($field,$cond=undefined,$value=undefined,$kind='where'){
  316. if(is_array($field)){
  317. // or conditions
  318. $or=$this->orExpr();
  319. foreach($field as $row){
  320. if(is_array($row)){
  321. $or->where($row[0],
  322. isset($row[1])?$row[1]:undefined,
  323. isset($row[2])?$row[2]:undefined);
  324. }elseif(is_object($row)){
  325. $or->where($row);
  326. }else{
  327. $or->where($or->expr($row));
  328. }
  329. }
  330. $field=$or;
  331. $this->api->x=1;
  332. }
  333. if(is_string($field) && !preg_match('/^[.a-zA-Z0-9_]*$/',$field)){
  334. // field contains non-alphanumeric values. Look for condition
  335. preg_match('/^([^ <>!=]*)([><!=]*|( *(not|is|in|like))*) *$/',$field,$matches);
  336. $value=$cond;
  337. $cond=$matches[2];
  338. if(!$cond){
  339. // IF COMPAT
  340. $matches[1]=$this->expr($field);
  341. if($value && $value!==undefined)$cond='=';else $cond=undefined;
  342. //throw $this->exception('Field is specified incorrectly or condition is not supported')
  343. //->addMoreInfo('field',$field);
  344. }
  345. $field=$matches[1];
  346. }
  347. //if($value==undefined && !is_object($field) && !is_object($cond))throw $this->exception('value is not specified');
  348. $this->args[$kind][]=array($field,$cond,$value);
  349. return $this;
  350. }
  351. function having($field,$cond=undefined,$value=undefined){
  352. return $this->where($field,$cond,$value,'having');
  353. }
  354. function _render_where($kind){
  355. $ret=array();
  356. foreach($this->args[$kind] as $row){
  357. list($field,$cond,$value)=$row;
  358. if(is_object($field)){
  359. // if first argument is object, condition must be explicitly specified
  360. $field=$this->consume($field);
  361. }else{
  362. list($table,$field)=explode('.',$field,2);
  363. if($field){
  364. $field=$this->bt($table).'.'.$this->bt($field);
  365. }else{
  366. $field=$this->bt($table);
  367. }
  368. }
  369. if($value===undefined && $cond===undefined){
  370. $r=$field;
  371. $ret[]=$r;
  372. continue;
  373. }
  374. if($value===undefined){
  375. $value=$cond;
  376. $cond='=';
  377. if(is_array($value))$cond='in';
  378. if(is_object($value) && @$value->mode=='select')$cond='in';
  379. }else{
  380. $cond=trim($cond);
  381. }
  382. if($cond=='in' && is_string($value)){
  383. $value=explode(',',$value);
  384. }
  385. if(is_array($value)){
  386. $v=array();
  387. foreach($value as $vv){
  388. $v[]=$this->escape($vv);
  389. }
  390. $value='('.join(',',$v).')';
  391. $cond='in';
  392. $r=$this->consume($field).' '.$cond.' '.$value;
  393. $ret[]=$r;
  394. continue;
  395. }
  396. if(is_object($value))$value=$this->consume($value);else$value=$this->escape($value);
  397. $r=$field.' '.$cond.' '.$value;
  398. $ret[]=$r;
  399. }
  400. return $ret;
  401. }
  402. function render_where(){
  403. if(!$this->args['where'])return;
  404. return 'where '.join(' and ',$this->_render_where('where'));
  405. }
  406. function render_orwhere(){
  407. if(!$this->args['where'])return;
  408. return join(' or ',$this->_render_where('where'));
  409. }
  410. function render_having(){
  411. if(!$this->args['having'])return;
  412. return 'having '.join(' or ',$this->_render_where('having'));
  413. }
  414. // }}}
  415. // {{{ join()
  416. /**
  417. * Adds condition to your query
  418. * @link http://agiletoolkit.org/doc/dsql/join
  419. *
  420. * Examples:
  421. * $q->join('address');
  422. * $q->join('address.user_id');
  423. * $q->join(array('a'=>'address'));
  424. *
  425. * Second argument may specify the field of the master table
  426. * $q->join('address.code','code');
  427. * $q->join('address.code','user.code');
  428. *
  429. * Third argument may specify which kind of join to use.
  430. * $q->join('address',null,'left');
  431. * $q->join('address.code','user.code','inner');
  432. *
  433. * Using array syntax you can join multiple tables too
  434. * $q->join(array('a'=>'address','p'=>'portfolio'));
  435. */
  436. function join($foreign_table, $master_field=null, $join_kind=null, $_foreign_alias=null){
  437. // Compatibility mode
  438. if(isset($this->api->compat)){
  439. if(strpos($foreign_table,' ')){
  440. list($foreign_table,$alias)=explode(' ',$foreign_table);
  441. $foreign_table=array($alias=>$foreign_table);
  442. }
  443. if(strpos($master_field,'=')){
  444. $master_field=$this->expr($master_field);
  445. }
  446. }
  447. // If array - add recursively
  448. if(is_array($foreign_table)){
  449. foreach ($foreign_table as $alias=>$foreign){
  450. if(is_numeric($alias))$alias=null;
  451. $this->join($foreign,$master_field,$join_kind,$alias);
  452. }
  453. return $this;
  454. }
  455. $j=array();
  456. // Split and deduce fields
  457. list($f1,$f2)=explode('.',$foreign_table,2);
  458. if(is_object($master_field)){
  459. $j['expr']=$master_field;
  460. }else{
  461. // Split and deduce primary table
  462. if(is_null($master_field)){
  463. list($m1,$m2)=array(null,null);
  464. }else{
  465. list($m1,$m2)=explode('.',$master_field,2);
  466. }
  467. if(is_null($m2)){
  468. $m2=$m1; $m1=null;
  469. }
  470. if(is_null($m1))$m1=$this->main_table;
  471. // Identify fields we use for joins
  472. if(is_null($f2) && is_null($m2))$m2=$f1.'_id';
  473. if(is_null($m2))$m2='id';
  474. $j['m1']=$m1;
  475. $j['m2']=$m2;
  476. }
  477. $j['f1']=$f1;
  478. if(is_null($f2))$f2='id';
  479. $j['f2']=$f2;
  480. $j['t']=$join_kind?:'left';
  481. $j['fa']=$_foreign_alias;
  482. $this->args['join'][]=$j;
  483. return $this;
  484. }
  485. function render_join(){
  486. if(!$this->args['join'])return '';
  487. $joins=array();
  488. foreach($this->args['join'] as $j){
  489. $jj='';
  490. $jj.=$j['t'].' join ';
  491. $jj.=$this->bt($j['f1']);
  492. if(!is_null($j['fa']))$jj.=' as '.$this->bt($j['fa']);
  493. $jj.=' on ';
  494. if($j['expr']){
  495. $jj.=$this->consume($j['expr']);
  496. }else{
  497. $jj.=
  498. $this->bt($j['fa']?:$j['f1']).'.'.
  499. $this->bt($j['f2']).' = '.
  500. $this->bt($j['m1']).'.'.
  501. $this->bt($j['m2']);
  502. }
  503. $joins[]=$jj;
  504. }
  505. return implode(' ',$joins);
  506. }
  507. // }}}
  508. // {{{ group()
  509. function group($option){
  510. return $this->_setArray($option,'group');
  511. }
  512. function render_group(){
  513. if(!$this->args['group'])return'';
  514. $x=array();
  515. foreach($this->args['group'] as $arg){
  516. $x[]=$this->bt($arg);
  517. }
  518. return 'group by '.implode(', ',$x);
  519. }
  520. // }}}
  521. // {{{ order()
  522. function order($order,$desc=null){// ,$prepend=null){
  523. if(is_object($order))$order='('.$order.')';
  524. if($desc)$order.=' desc';
  525. return $this->_setArray($order,'order');
  526. }
  527. function render_order(){
  528. if(!$this->args['order'])return'';
  529. $x=array();
  530. foreach($this->args['order'] as $arg){
  531. if(substr($arg,-5)==' desc'){
  532. $x[]=$this->bt(substr($arg,0,-5)).' desc';
  533. }else{
  534. $x[]=$this->bt($arg);
  535. }
  536. }
  537. return 'order by '.implode(', ',$x);
  538. }
  539. // }}}
  540. // {{{ option() and args()
  541. /** Defines query option */
  542. function option($option){
  543. return $this->_setArray($option,'options');
  544. }
  545. function render_options(){
  546. return @implode(' ',$this->args['options']);
  547. }
  548. function option_insert($option){
  549. return $this->_setArray($option,'options_insert');
  550. }
  551. function render_options_insert(){
  552. if(!$this->args['options_insert'])return '';
  553. return implode(' ',$this->args['options_insert']);
  554. }
  555. // }}}
  556. // {{{ args()
  557. /** set arguments for call() */
  558. function args($args){
  559. return $this->_setArray($args,'args',false);
  560. }
  561. function render_args(){
  562. $x=array();
  563. foreach($this->args['args'] as $arg){
  564. $x[]=is_object($arg)?
  565. $this->consume($arg):
  566. $this->escape($arg);
  567. }
  568. return implode(', ',$x);
  569. }
  570. function ignore(){
  571. $this->args['options_insert'][]='ignore';
  572. return $this;
  573. }
  574. /** Check if option was defined */
  575. function hasOption($option){
  576. return @in_array($option,$this->args['options']);
  577. }
  578. function hasInsertOption($option){
  579. return @in_array($option,$this->args['options_insert']);
  580. }
  581. // }}}
  582. // {{{ limit()
  583. /** Limit row result */
  584. function limit($cnt,$shift=0){
  585. $this->args['limit']=array(
  586. 'cnt'=>$cnt,
  587. 'shift'=>$shift
  588. );
  589. return $this;
  590. }
  591. function render_limit(){
  592. if($this->args['limit']){
  593. return 'limit '.
  594. (int)$this->args['limit']['shift'].
  595. ', '.
  596. (int)$this->args['limit']['cnt'];
  597. }
  598. }
  599. // }}}
  600. // {{{ set()
  601. function set($field,$value=undefined){
  602. if($value===false){
  603. throw $this->exception('Value "false" is not supported by SQL');
  604. }
  605. if(is_array($field)){
  606. foreach($field as $key=>$value){
  607. $this->set($key,$value);
  608. }
  609. return $this;
  610. }
  611. if($value===undefined)throw $this->exception('Specify value when calling set()');
  612. $this->args['set'][$field]=$value;
  613. return $this;
  614. }
  615. function render_set(){
  616. $x=array();
  617. if($this->args['set'])foreach($this->args['set'] as $field=>$value){
  618. if(is_object($field))$field=$this->consume($field);else$field=$this->bt($field);
  619. if(is_object($value))$value=$this->consume($value);else$value=$this->escape($value);
  620. $x[]=$field.'='.$value;
  621. }
  622. return join(', ',$x);
  623. }
  624. function render_set_fields(){
  625. $x=array();
  626. if($this->args['set'])foreach($this->args['set'] as $field=>$value){
  627. if(is_object($field))$field=$this->consume($field);else$field=$this->bt($field);
  628. $x[]=$field;
  629. }
  630. return join(',',$x);
  631. }
  632. function render_set_values(){
  633. $x=array();
  634. if($this->args['set'])foreach($this->args['set'] as $field=>$value){
  635. if(is_object($value))$value=$this->consume($value);else$value=$this->escape($value);
  636. $x[]=$value;
  637. }
  638. return join(',',$x);
  639. }
  640. // }}}
  641. // {{{ MISC
  642. /** Backticks will be added around all fields. Set this to '' if you prefer cleaner queries */
  643. public $bt='`';
  644. function bt($s){
  645. if(is_array($s)){
  646. $out=array();
  647. foreach($s as $ss){
  648. $out[]=$this->bt($ss);
  649. }
  650. return $out;
  651. }
  652. if(!$this->bt
  653. || is_object($s)
  654. || $s=='*'
  655. || strpos($s,'.')!==false
  656. || strpos($s,'(')!==false
  657. || strpos($s,$this->bt)!==false
  658. )return $s;
  659. return $this->bt.$s.$this->bt;
  660. }
  661. /* Defines query option */
  662. function _setArray($values,$name,$parse_commas=true){
  663. if(is_string($values) && $parse_commas && strpos($values,','))$values=explode(',',$values);
  664. if(!is_array($values))$values=array($values);
  665. if(!isset($this->args[$name]))$this->args[$name]=array();
  666. $this->args[$name]=array_merge($this->args[$name],$values);
  667. return $this;
  668. }
  669. // }}}
  670. // }}}
  671. // {{{ Statement templates and interfaces
  672. /** Switches to select mode (which is default) for this query */
  673. function SQLTemplate($mode){
  674. $this->mode=$mode;
  675. $this->template=$this->sql_templates[$mode];
  676. return $this;
  677. }
  678. /** Switches to call mode. Use with args() */
  679. function call($fx,$args=null){
  680. $this->mode='call';
  681. $this->args['fx']=$fx;
  682. if(!is_null($args)){
  683. $this->args($args);
  684. }
  685. $this->template="call [fx]([args])";
  686. return $this;
  687. }
  688. function fx($fx,$args=null){
  689. $this->mode='fx';
  690. $this->args['fx']=$fx;
  691. if(!is_null($args)){
  692. $this->args($args);
  693. }
  694. $this->template="[fx]([args])";
  695. return $this;
  696. }
  697. function render_fx(){
  698. return $this->args['fx'];
  699. }
  700. function sum($arg=null){
  701. return $this->expr('sum([sum])')->setCustom('sum',$this->bt($arg));
  702. }
  703. function count($arg=null){
  704. if(is_null($arg))$arg='*';
  705. return $this->expr('count([count])')->setCustom('count',$this->bt($arg));
  706. }
  707. // }}}
  708. // {{{ More complex query generations and specific cases
  709. /** Executes any query query */
  710. function execute(){
  711. try {
  712. /**/$this->api->pr->start('dsql/execute/render');
  713. $q=$this->render();
  714. /**/$this->api->pr->next('dsql/execute/query');
  715. $this->stmt=$this->owner->query($q,$this->params);
  716. $this->template=$this->mode=null;
  717. /**/$this->api->pr->stop();
  718. return $this;
  719. }catch(PDOException $e){
  720. throw $this->exception('Database Query Failed')
  721. ->addPDOException($e)
  722. ->addMoreInfo('mode',$this->mode)
  723. ->addMoreInfo('params',$this->params)
  724. ->addMoreInfo('query',$q)
  725. ->addMoreInfo('template',$this->template)
  726. ;
  727. }
  728. }
  729. /** Executes select query. */
  730. function select(){
  731. return $this->SQLTemplate('select')->execute();
  732. }
  733. /** Executes insert query. Returns ID of new record. */
  734. function insert(){
  735. $this->SQLTemplate('insert')->execute();
  736. return
  737. $this->hasInsertOption('ignore')?null:
  738. $this->owner->lastID();
  739. }
  740. /** Inserts multiple rows of data. Uses ignore option. */
  741. function insertAll($array){
  742. $ids=array();
  743. foreach($array as $hash){
  744. $ids[]=$this->del('set')->set($hash)->insert();
  745. }
  746. return $ids;
  747. }
  748. /** Executes update query */
  749. function update(){
  750. return $this->SQLTemplate('update')->execute();
  751. }
  752. /** Executes replace query */
  753. function replace(){
  754. return $this->SQLTemplate('replace')->execute();
  755. }
  756. /** Executes delete query */
  757. function delete(){
  758. return $this->SQLTemplate('delete')->execute();
  759. }
  760. /** Executes truncate */
  761. function truncate(){
  762. return $this->SQLTemplate('truncate')->execute();
  763. }
  764. /** @obsolete, use select() */
  765. function do_select(){
  766. return $this->select();
  767. }
  768. /** @obsolete, use insert() */
  769. function do_insert(){
  770. return $this->insert();
  771. }
  772. /** @obsolete, use update() */
  773. function do_update(){
  774. return $this->update();
  775. }
  776. /** @obsolete, use replace() */
  777. function do_replace(){
  778. return $this->replace();
  779. }
  780. // }}}
  781. // {{{ Data fetching modes
  782. /** Will execute DSQL query and return all results inside array of hashes */
  783. function get(){
  784. if(!$this->stmt)$this->execute();
  785. $res=$this->stmt->fetchAll(PDO::FETCH_ASSOC);
  786. $this->rewind();
  787. $this->stmt=null;
  788. return $res;
  789. }
  790. /** Will execute DSQL query and return first column of a first row */
  791. function getOne(){
  792. $res=$this->getRow();
  793. $this->rewind();
  794. $this->stmt=null;
  795. return $res[0];
  796. }
  797. /** Will execute DSQL query and return first row as array (not hash) */
  798. function getRow(){
  799. return $this->fetch(PDO::FETCH_NUM);
  800. }
  801. /** Will execute DSQL query and return first row as hash (column=>value) */
  802. function getHash(){
  803. return $this->fetch(PDO::FETCH_ASSOC);
  804. }
  805. /** Will execute the query (if it's not executed already) and return first row */
  806. function fetch($mode=PDO::FETCH_ASSOC){
  807. if(!$this->stmt)$this->execute();
  808. return $this->stmt->fetch($mode);
  809. }
  810. // {{{ Obsolete functions
  811. /** @obsolete. Use get() */
  812. function fetchAll(){
  813. return $this->get();
  814. }
  815. /** @obsolete. Use getQne() */
  816. function do_getOne(){
  817. return $this->getOne();
  818. }
  819. /** @obsolete. Use get() */
  820. function do_getAllHash(){
  821. return $this->get();
  822. }
  823. function do_getAll(){
  824. return $this->get();
  825. }
  826. /** @obsolete. Use get() */
  827. function getAll(){
  828. return $this->get();
  829. }
  830. /** @obsolete. Use getRow() */
  831. function do_getRow(){
  832. return $this->getRow();
  833. }
  834. /** @obsolete. Use getHash() */
  835. function do_getHash(){
  836. return $this->getHash();
  837. }
  838. // }}}
  839. /** Sets flag to hint SQL (if supported) to prepare total number of columns. Use foundRows() to read this afterwards */
  840. function calc_found_rows(){
  841. // add option for row calculation if supported
  842. return $this;
  843. }
  844. /** After fetching data, call this to find out how many rows there were in total. Call calc_found_rows() for better performance */
  845. function foundRows(){
  846. if($this->hasOption('SQL_CALC_FOUND_ROWS')){
  847. return $this->owner->getOne('select found_rows()');
  848. }
  849. /* db-compatibl way: */
  850. $c=clone $this;
  851. $c->del('limit');
  852. $c->del('fields');
  853. $c->field('count(*)');
  854. return $c->getOne();
  855. }
  856. // }}}
  857. // {{{ Iterator support
  858. public $data=false;
  859. public $_iterating=false;
  860. function rewind(){
  861. if($this->_iterating){
  862. $this->stmt=null;
  863. $this->_iterating=false;
  864. }
  865. $this->_iterating=true;
  866. return $this;
  867. }
  868. function next(){
  869. $this->data = $this->fetch();
  870. return $this;
  871. }
  872. function current(){
  873. return $this->data;
  874. }
  875. function key(){
  876. return $this->data['id'];
  877. }
  878. function valid(){
  879. if(!$this->stmt)$this->data = $this->fetch();
  880. return (boolean)$this->data;
  881. }
  882. // }}}
  883. // {{{ Rendering
  884. /** Will set a flag which will output query (echo) as it is being rendered. */
  885. function debug(){
  886. $this->debug=1;
  887. return $this;
  888. }
  889. /** Converts query into string format. This will contain parametric references */
  890. function render(){
  891. $this->params=$this->extra_params;
  892. $r=$this->_render();
  893. if($this->debug){
  894. $d=$r;
  895. $pp=array();
  896. $d=preg_replace('/`([^`]*)`/','`<font color="black">\1</font>`',$d);
  897. foreach(array_reverse($this->params) as $key=>$val){
  898. if(is_string($val))$d=preg_replace('/'.$key.'([^_]|$)/','"<font color="green">'.htmlspecialchars(addslashes($val)).'</font>"\1',$d);
  899. elseif(is_null($val))$d=preg_replace('/'.$key.'([^_]|$)/','<font color="black">NULL</font>\1',$d);
  900. elseif(is_numeric($val))$d=preg_replace('/'.$key.'([^_]|$)/','<font color="red">'.$val.'</font>\1',$d);
  901. else$d=preg_replace('/'.$key.'([^_]|$)/',$val.'\1',$d);
  902. $pp[]=$key;
  903. }
  904. echo "<font color='blue'>".$d."</font> <font color='gray'>[".join(', ',$pp)."]</font><br/>";
  905. }
  906. return $r;
  907. }
  908. function _render(){
  909. /**/$this->api->pr->start('dsql/render');
  910. if(!$this->template)$this->SQLTemplate('select');
  911. $self=$this;
  912. $res= preg_replace_callback('/\[([a-z0-9_]*)\]/',function($matches) use($self){
  913. /**/$self->api->pr->next('dsql/render/'.$matches[1],true);
  914. $fx='render_'.$matches[1];
  915. if($self->hasMethod($fx))return $self->$fx();
  916. elseif(isset($self->args['custom'][$matches[1]]))return $self->consume($self->args['custom'][$matches[1]]);
  917. else return $matches[0];
  918. },$this->template);
  919. /**/$this->api->pr->stop(null,true);
  920. return $res;
  921. }
  922. // }}}
  923. }