PageRenderTime 57ms CodeModel.GetById 30ms RepoModel.GetById 0ms app.codeStats 0ms

/include/SQL/Query.php

https://github.com/radicaldesigns/amp
PHP | 526 lines | 147 code | 37 blank | 342 comment | 13 complexity | ff596638eeb23eadbc0ff5cc049c9ce1 MD5 | raw file
Possible License(s): LGPL-2.1, GPL-2.0, BSD-3-Clause, LGPL-2.0, CC-BY-SA-3.0, AGPL-1.0
  1. <?php
  2. //
  3. // $Id$
  4. //
  5. require_once 'SQL/Condition.php';
  6. /**
  7. * This class is only responsible for providing an interface to collect all the elements
  8. * of a query, such as the select, where, group, etc. parts. The data are saved into properties.
  9. * There is a reset* and a add* method for each part of the query. The set* method() as
  10. * QueryTool v0.x used to have it is not really needed and does only cause confusion, since you
  11. * can easily mix add* and set* which sometimes only causes debugging effort.
  12. *
  13. * thanks to those guys who wrote this great book
  14. * [1] http://www.oldenbourg.de/frame0.htm?http://www.oldenbourg.de/cgi-bin/rotitel?T=25706
  15. *
  16. * at least the sql92 spec
  17. * [2]http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
  18. *
  19. * @package SQL_Query
  20. * @author Wolfram Kriesing <wk@visionp.de>
  21. * @version 2003/05/23
  22. */
  23. class SQL_Query
  24. {
  25. /**
  26. * @var string the query type, i.e. 'select', 'insert', etc.
  27. */
  28. var $type = 'select';
  29. /**
  30. * @var array contains the strings that are given for selecting, i.e.
  31. * '*' or 'id' or 'name' or 'name AS userName' or 'surname' or
  32. * 'SUBSTRING(name FROM 1 FOR 5 COLLATE whatever) AS partOfName' or 'COUNT(x)'
  33. * every value in the array contains ONE value that needs to be selected
  34. */
  35. var $select = array();
  36. /**
  37. * @var array contains the columns that shall not be selected
  38. */
  39. var $dontSelect = array();
  40. /**
  41. * @var mixed the table, table and alias or the join this class is currently working on,
  42. * note that this class only works on one table, to use joined queries
  43. * pass a join instance
  44. * If this value is an array it is: array(tableName|join-object[,aliasName])
  45. */
  46. var $from = array();
  47. /**
  48. * @var array every value of this array contains one expression
  49. * @todo i am not sure how to do this, since an expression might be quite complex and use parentheses, so there
  50. * are groups ... and how are they connected, via AND or OR, ...
  51. */
  52. var $where = null;
  53. /**
  54. * @var array each value in this array is an array itself,
  55. * it might be either only array(column name), which means it is sorted ascending,
  56. * which is the default. Or if the second element is true it means descending.
  57. * I.e. array('name') or array('name',false) =ascending, or array('name',true) =descending
  58. * and if a collate-clause shall be added it is simply the third field in the array, since
  59. * this is not a very usual case i think we can 'abandon' it there.
  60. * each field has a meaning, this way accessing them directly is easier in using this structure
  61. *
  62. * copied from [2], that's how the order clause is specified. this is only sql92!!!
  63. * I dont know what diff there is to sql99 but i will look one day ... i hope there is none :-)
  64. * <pre>
  65. * &lt;order by clause&gt; ::=
  66. * ORDER BY &lt;sort specification list&gt;
  67. *
  68. * &lt;sort specification list&gt; ::=
  69. * &lt;sort specification&gt; [ { &lt;comma&gt; &lt;sort specification&gt; }... ]
  70. *
  71. * &lt;sort specification&gt; ::=
  72. * &lt;sort key&gt; [ &lt;collate clause &gt; ] [ &lt;ordering specification&gt; ]
  73. *
  74. *
  75. * &lt;sort key&gt; ::=
  76. * &lt;column name&gt;
  77. * | &lt;unsigned integer&gt;
  78. *
  79. * &lt;ordering specification&gt; ::= ASC | DESC
  80. *
  81. * &lt;collate clause&gt; ::= COLLATE &lt;collation name&gt;
  82. * </pre>
  83. */
  84. var $order = array();
  85. /**
  86. * @var array each value of the array contains the column name or expression that shall be added to the group clause
  87. */
  88. var $group = array();
  89. /**
  90. * @var array each value of the array contains the column name or expression that shall be added to the having clause
  91. */
  92. var $having = array();
  93. /**
  94. * @var string the limit clause of the query
  95. */
  96. var $limit = array();
  97. function SQL_Query( $table=null, $type='select')
  98. {
  99. if ($table!=null) {
  100. $this->setFrom($table);
  101. }
  102. $this->setType($type);
  103. }
  104. /**
  105. * Sets the query type, which is either 'select' which is default, 'insert',
  106. * 'delete', etc.
  107. *
  108. * @param string the query type
  109. */
  110. function setType( $type)
  111. {
  112. $this->type = strtolower($type);
  113. }
  114. /**
  115. * Gets the query type, which is either 'select' which is default, 'insert',
  116. * 'delete', etc., all types are always lower case!
  117. *
  118. * @return string the query type
  119. */
  120. function getType()
  121. {
  122. return $this->type;
  123. }
  124. /**
  125. * Set the table or a join, this query is built for.
  126. *
  127. * @param mixed string - the table name, or
  128. * array - the table and its alias, like this: array('table','alias'), or
  129. * join-object - this is an instance of SQL_Query_Join.
  130. * Since there are those multiple options, we can not pass them as reference
  131. * with PHP4, its not nice, but that's how it currently is.
  132. */
  133. function setFrom($from)
  134. {
  135. if (is_array($from)) {
  136. $from = array(current($from),key($from));
  137. }
  138. if (is_string($from)) {
  139. settype($from,'array');
  140. }
  141. $this->from = $from;
  142. }
  143. /**
  144. * Return the FROM part that is set for the query.
  145. *
  146. * @return mixed the from part, see setFrom()
  147. */
  148. function getFrom()
  149. {
  150. return $this->from;
  151. }
  152. /**
  153. * This method only serves the purpose to empty the select part.
  154. *
  155. * @return void
  156. * @access public
  157. */
  158. function resetSelect()
  159. {
  160. $this->select = array();
  161. }
  162. /**
  163. * Add columns or expressions to the select part of the query.
  164. * Passing no value to this method adds a '*' to the select query, which is normally the default.
  165. * Example usage:
  166. * <code>
  167. * $obj->addSelect('id','name','surname'); // select the three given columns OR
  168. * $obj->addSelect(array('id','name','surname')); // select the three given columns
  169. * </code>
  170. *
  171. * @param mixed any parameter passed is added to the select query, you can pass as many parameters
  172. * as you want. To select multiple columns add their names as a parameter each.
  173. */
  174. function addSelect($string='*')
  175. {
  176. if (func_num_args()>1) {
  177. $args = func_get_args();
  178. } else {
  179. $args = is_array($string)?$string:array($string);
  180. }
  181. foreach ($args as $aVal) {
  182. $this->select[] = $aVal;
  183. }
  184. array_unique($this->select);
  185. }
  186. /**
  187. * Return the select part as it is stored internally.
  188. *
  189. * @access public
  190. * @return array this array contains all the parts that were added to the select part
  191. */
  192. function getSelect()
  193. {
  194. return $this->select;
  195. }
  196. /**
  197. * This method only serves the purpose to empty the dontSelect part.
  198. *
  199. * @return void
  200. * @access public
  201. */
  202. function resetDontSelect()
  203. {
  204. $this->dontSelect = array();
  205. }
  206. /**
  207. * Add columns or expressions to the select part of the query.
  208. * Passing no value to this method adds a '*' to the select query, which is normally the default.
  209. * Example usage:
  210. * <code>
  211. * $obj->addSelect('id','name','surname'); // select the three given columns
  212. * </code>
  213. *
  214. * @param mixed strings - any parameter passed is added to the select query, you can pass as many parameters
  215. * as you want. To select multiple columns add their names as a parameter each.
  216. * array - or only one parameter which is an array and contains multiple column names
  217. */
  218. function addDontSelect($string)
  219. {
  220. if (func_num_args()>1) {
  221. $args = func_get_args();
  222. } else {
  223. $args = is_array($string)?$string:array($string);
  224. }
  225. foreach ($args as $aVal) {
  226. $this->dontSelect[] = $aVal;
  227. }
  228. }
  229. /**
  230. * Return the dont-select part as it is stored internally.
  231. *
  232. * @access public
  233. * @return array this array contains all the parts that were added to the dont-select part
  234. */
  235. function getDontSelect()
  236. {
  237. return $this->dontSelect;
  238. }
  239. /**
  240. * This method resets the where part of the query.
  241. *
  242. * @return void
  243. */
  244. function resetWhere()
  245. {
  246. $this->where = null;
  247. }
  248. /**
  249. * This method adds conditions to the where part of the query.
  250. * usage example:
  251. * <code>
  252. * $query->addWhere('name','LIKE','"%any%"');
  253. *
  254. * // to build this where clause: name LIKE "%any%" OR name LIKE '%none%'
  255. * // do it like this:
  256. * $cond1 = $query->condition('name','LIKE','"%any%"');
  257. * $cond2 = $query->condition('name','LIKE','"%none%"');
  258. * $query->addWhere($cond1,'OR',$cond2);
  259. *
  260. * // to build this: (name LIKE 'n%' AND name LIKE 'a%') OR name LIKE 's%'
  261. * // this is the prefered way
  262. * $cond = $query->condition('name','LIKE','"n%"');
  263. * $cond->add('name','LIKE','"a%"','AND');
  264. * $query->addWhere($cond,'OR',$query->condition('name','LIKE','"%s"'));
  265. * // OR like this, it works too
  266. * $c1 = $query->condition('name','LIKE','"n%"');
  267. * $c2 = $query->condition('name','LIKE','"a%"');
  268. * $query->addWhere($query->condition($c1,'AND',$c2),'OR',$query->condition('name','LIKE','"%s"'));
  269. * </code>
  270. * Since the condition parameters can also be strings we can not make them
  271. * accept references only :-( so we must live with copying the condition-objects
  272. * in case they are passed instead of a string. But this seems no big problem
  273. * i think.
  274. *
  275. * @param mixed a
  276. * @return void
  277. */
  278. function addWhere($cond1,$operator=null,$cond2=null,$globalOperator='AND')
  279. {
  280. if (!$this->where) {
  281. $this->where = new SQL_Condition($cond1,$operator,$cond2);
  282. } else {
  283. $this->where->add($cond1,$operator,$cond2,$globalOperator);
  284. }
  285. }
  286. /**
  287. * Return the current where condition.
  288. *
  289. * @return mixed mostly an instance of SQL_Condition, or null if no condition is set
  290. */
  291. function getWhere()
  292. {
  293. return $this->where;
  294. }
  295. /**
  296. * This method is just a shortcut to build conditions easier.
  297. * Example usage:
  298. * <code>
  299. * $cond = $query->condition('name','=','Foo');
  300. * $cond1 = $query->condition('name','=','Bar');
  301. * $query->addWhere($cond,'OR',$cond1);
  302. * // is the same as this
  303. * $cond = new SQL_Condition('name','=','Foo');
  304. * $cond1 = new SQL_Condition('name','=','Foo');
  305. * $query->addWhere($cond,'OR',$cond1);
  306. * </code>
  307. *
  308. */
  309. function &condition($cond1,$operator,$cond2)
  310. {
  311. return new SQL_Condition($cond1,$operator,$cond2);
  312. }
  313. /**
  314. * This method resets the order part of the query.
  315. *
  316. * @return void
  317. */
  318. function resetOrder()
  319. {
  320. $this->order = array();
  321. }
  322. /**
  323. * This method adds one or many pieces to the order part of the query.
  324. * example usage:
  325. * <code>
  326. * $query->addOrder('column'); // simply sort by this column in ascending order
  327. *
  328. * $query->addOrder(array('column',true)); // sort by 'column' descending
  329. *
  330. * // sort by all given cols, but col2 descending
  331. * // this should result in this: ORDER BY col1, col2 DESC
  332. * $query->addOrder('col1',array('col2',true));
  333. *
  334. * // add a collate clause
  335. * // this should become: ORDER BY col COLLATE col1 DESC, col2
  336. * $query->addOrder(array('col',true,'col1'),'col2');
  337. *
  338. * // add a collate clause
  339. * // this should become: ORDER BY col COLLATE col1 ASC, col2
  340. * $query->addOrder(array('col',false,'col1'),'col2');
  341. * </code>
  342. *
  343. * @see order
  344. * @param mixed either a simply string or an array
  345. * @return void
  346. */
  347. function addOrder($order)
  348. {
  349. foreach (func_get_args() as $aArg) {
  350. if (is_array($aArg)) {
  351. $this->order[] = $aArg;
  352. } else {
  353. // set the false, to signalize that it is descending
  354. $this->order[] = array($aArg,false);
  355. }
  356. }
  357. }
  358. /**
  359. * Return the order part of the query.
  360. *
  361. * @return array
  362. */
  363. function getOrder()
  364. {
  365. return $this->order;
  366. }
  367. /**
  368. * This method resets the group part of the query.
  369. *
  370. * @return void
  371. */
  372. function resetGroup()
  373. {
  374. $this->group = array();
  375. }
  376. /**
  377. * This method adds one or many pieces to the group part of the query.
  378. *
  379. * @param string the column name or expression to be added to the group part of the query
  380. * @return void
  381. */
  382. function addGroup($group)
  383. {
  384. foreach (func_get_args() as $v) {
  385. $this->group[] = $v;
  386. }
  387. }
  388. /**
  389. * Return the group part of the query.
  390. *
  391. * @return array
  392. */
  393. function getGroup()
  394. {
  395. return $this->group;
  396. }
  397. /**
  398. * This method resets the having part of the query.
  399. *
  400. * @return void
  401. */
  402. /* function resetHaving()
  403. {
  404. $this->having = array();
  405. }
  406. */
  407. /**
  408. * This method adds one or many pieces to the group part of the query.
  409. *
  410. * @param string the column name or expression to be added to the group part of the query
  411. * @return void
  412. */
  413. /* function addHaving($having)
  414. {
  415. foreach (func_get_args() as $v) {
  416. $this->having[] = $v;
  417. }
  418. }
  419. */
  420. /**
  421. * Return the having part of the query.
  422. *
  423. * @return array
  424. */
  425. /* function getHaving()
  426. {
  427. return $this->having;
  428. }
  429. */
  430. /**
  431. * Reset the limit part of the query.
  432. *
  433. * @access public
  434. * @return void
  435. */
  436. /* function resetLimit()
  437. {
  438. $this->limit = array();
  439. }
  440. */
  441. /**
  442. * Set the limit clause of the array.
  443. *
  444. * @param int the row to start at
  445. * @param int the max. number of rows to return
  446. * @access public
  447. * @return void
  448. */
  449. /* function setLimit($from,$count)
  450. {
  451. $this->limit = array($from,$count);
  452. }
  453. */
  454. /**
  455. * Return the limit clause of the query.
  456. *
  457. * @return array
  458. */
  459. /* function getLimit()
  460. {
  461. return $this->limit;
  462. }
  463. */
  464. /**
  465. * Reset all parts of the query.
  466. * This method simply searches all the class methods, that start with
  467. * 'reset' and calls them.
  468. *
  469. */
  470. function reset()
  471. {
  472. foreach (get_class_methods(__CLASS__) as $method) {
  473. if ($method!='reset' && strpos($method,'reset')===0) {
  474. $this->$method();
  475. }
  476. }
  477. }
  478. /**
  479. * This method returns the hash of this object.
  480. * This is very useful for caching to detect if this query is already cached.
  481. *
  482. *
  483. */
  484. function hashKey()
  485. {
  486. return md5(serialize($this));
  487. }
  488. }
  489. ?>