PageRenderTime 64ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 0ms

/lib/php/MDB/QueryTool/Query.php

https://bitbucket.org/adarshj/convenient_website
PHP | 2347 lines | 982 code | 239 blank | 1126 comment | 167 complexity | 1e53c930c32ce4bcaafe073ab15e206e MD5 | raw file
Possible License(s): Apache-2.0, MPL-2.0-no-copyleft-exception, LGPL-2.1, BSD-2-Clause, GPL-2.0, LGPL-3.0

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

  1. <?php
  2. /* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
  3. /**
  4. * Contains the MDB_QueryTool_Query class
  5. *
  6. * PHP versions 4 and 5
  7. *
  8. * LICENSE: This source file is subject to version 3.0 of the PHP license
  9. * that is available through the world-wide-web at the following URI:
  10. * http://www.php.net/license/3_0.txt. If you did not receive a copy of
  11. * the PHP License and are unable to obtain it through the web, please
  12. * send a note to license@php.net so we can mail you a copy immediately.
  13. *
  14. * @category Database
  15. * @package MDB_QueryTool
  16. * @author Lorenzo Alberton <l dot alberton at quipo dot it>
  17. * @copyright 2004-2005 Lorenzo Alberton
  18. * @license http://www.php.net/license/3_0.txt PHP License 3.0
  19. * @version CVS: $Id: Query.php,v 1.45 2005/02/27 19:13:03 quipo Exp $
  20. * @link http://pear.php.net/package/MDB_QueryTool
  21. */
  22. /**
  23. * require the PEAR and MDB classes
  24. */
  25. require_once 'PEAR.php';
  26. require_once 'MDB.php';
  27. /**
  28. * MDB_QueryTool_Query class
  29. *
  30. * This class should be extended
  31. *
  32. * @category Database
  33. * @package MDB_QueryTool
  34. * @author Lorenzo Alberton <l dot alberton at quipo dot it>
  35. * @copyright 2004-2005 Lorenzo Alberton
  36. * @license http://www.php.net/license/3_0.txt PHP License 3.0
  37. * @link http://pear.php.net/package/MDB_QueryTool
  38. */
  39. class MDB_QueryTool_Query
  40. {
  41. // {{{ class vars
  42. /**
  43. * @var string the name of the primary column
  44. */
  45. var $primaryCol = 'id';
  46. /**
  47. * @var string the current table the class works on
  48. */
  49. var $table = '';
  50. /**
  51. * @var string the name of the sequence for this table
  52. */
  53. var $sequenceName = null;
  54. /**
  55. * @var object the db-object, a PEAR::Mdb-object instance
  56. */
  57. var $db = null;
  58. /**
  59. * @var string the where condition
  60. * @access private
  61. */
  62. var $_where = '';
  63. /**
  64. * @var string the order condition
  65. * @access private
  66. */
  67. var $_order = '';
  68. /**
  69. * @var string the having definition
  70. * @access private
  71. */
  72. var $_having = '';
  73. /**
  74. * @var array contains the join content
  75. * the key is the join type, for now we have 'default' and 'left'
  76. * inside each key 'table' contains the table
  77. * key 'where' contains the where clause for the join
  78. * @access private
  79. */
  80. var $_join = array();
  81. /**
  82. * @var string which column to index the result by
  83. * @access private
  84. */
  85. var $_index = null;
  86. /**
  87. * @var string the group-by clause
  88. * @access private
  89. */
  90. var $_group = '';
  91. /**
  92. * @var array the limit
  93. * @access private
  94. */
  95. var $_limit = array();
  96. /**
  97. * @var boolean if to use the MDB_QueryTool_Result as a result or not
  98. * @access private
  99. */
  100. var $_resultType = 'none';
  101. /**
  102. * @var array the metadata temporary saved
  103. * @access private
  104. */
  105. var $_metadata = array();
  106. /**
  107. * @var string (?)
  108. * @access private
  109. */
  110. var $_lastQuery = null;
  111. /**
  112. * @var string the rows that shall be selected
  113. * @access private
  114. */
  115. var $_select = '*';
  116. /**
  117. * @var string the rows that shall not be selected
  118. * @access private
  119. */
  120. var $_dontSelect = '';
  121. /**
  122. * @var array this array saves different modes in which this class works
  123. * i.e. 'raw' means no quoting before saving/updating data
  124. * @access private
  125. */
  126. var $options = array( 'raw' => false,
  127. 'verbose' => true, // set this to false in a productive environment
  128. // it will produce error-logs if set to true
  129. 'useCache' => false,
  130. 'logFile' => false
  131. );
  132. /**
  133. * this array contains information about the tables
  134. * those are
  135. * 'name' - the real table name
  136. * 'shortName' - the short name used, so that when moving the table i.e.
  137. * onto a provider's db and u have to rename the tables to longer names
  138. * this name will be relevant, i.e. when autoJoining, i.e. a table name
  139. * on your local machine is: 'user' but online it has to be 'applName_user'
  140. * then the shortName will be used to determine if a column refers to another
  141. * table, if the colName is 'user_id', it knows the shortName 'user' refers to the table
  142. * 'applName_user'
  143. */
  144. var $tableSpec = array();
  145. /**
  146. * this is the regular expression that shall be used to find a table's shortName
  147. * in a column name, the string found by using this regular expression will be removed
  148. * from the column name and it will be checked if it is a table name
  149. * i.e. the default '/_id$/' would find the table name 'user' from the column name 'user_id'
  150. *
  151. * @access private
  152. */
  153. var $_tableNameToShortNamePreg = '/^.*_/';
  154. /**
  155. * var array this array caches queries that have already been built once
  156. * to reduce the execution time
  157. * @access private
  158. */
  159. var $_queryCache = array();
  160. /**
  161. * The object that contains the log-instance
  162. * @access private
  163. */
  164. var $_logObject = null;
  165. /**
  166. * Some internal data the logging needs
  167. * @access private
  168. */
  169. var $_logData = array();
  170. // }}}
  171. // {{{ __construct
  172. /**
  173. * this is the constructor, as it will be implemented in ZE2 (php5)
  174. *
  175. * @param object db-object
  176. * @param array options array
  177. * @access public
  178. */
  179. /*
  180. function __construct($dsn=false, $options=array())
  181. {
  182. if (!isset($options['autoConnect'])) {
  183. $autoConnect = true;
  184. } else {
  185. $autoConnect = $options['autoConnect'];
  186. }
  187. if (isset($options['errorCallback'])) {
  188. $this->setErrorCallback($options['errorCallback']);
  189. }
  190. if (isset($options['errorSetCallback'])) {
  191. $this->setErrorSetCallback($options['errorSetCallback']);
  192. }
  193. if (isset($options['errorLogCallback'])) {
  194. $this->setErrorLogCallback($options['errorLogCallback']);
  195. }
  196. if ($autoConnect && $dsn) {
  197. $this->connect($dsn, $options);
  198. }
  199. if (is_null($this->sequenceName)) {
  200. $this->sequenceName = $this->table;
  201. }
  202. }
  203. */
  204. // }}}
  205. // {{{ MDB_QueryTool_Query()
  206. /**
  207. * @param mixed $dsn DSN string, DSN array or MDB object
  208. * @param array $options
  209. * @access public
  210. */
  211. function MDB_QueryTool_Query($dsn=false, $options=array())
  212. {
  213. //$this->__construct($dsn, $options);
  214. if (!isset($options['autoConnect'])) {
  215. $autoConnect = true;
  216. } else {
  217. $autoConnect = $options['autoConnect'];
  218. unset($options['autoConnect']);
  219. }
  220. if (isset($options['errorCallback'])) {
  221. $this->setErrorCallback($options['errorCallback']);
  222. unset($options['errorCallback']);
  223. }
  224. if (isset($options['errorSetCallback'])) {
  225. $this->setErrorSetCallback($options['errorSetCallback']);
  226. unset($options['errorSetCallback']);
  227. }
  228. if (isset($options['errorLogCallback'])) {
  229. $this->setErrorLogCallback($options['errorLogCallback']);
  230. unset($options['errorLogCallback']);
  231. }
  232. if ($autoConnect && $dsn) {
  233. $this->connect($dsn, $options);
  234. }
  235. if (is_null($this->sequenceName)) {
  236. $this->sequenceName = $this->table;
  237. }
  238. }
  239. // }}}
  240. // {{{ connect()
  241. /**
  242. * use this method if you want to connect manually
  243. * @param mixed $dsn DSN string, DSN array or MDB object
  244. * @param array $options
  245. */
  246. function connect($dsn, $options=array())
  247. {
  248. if (is_object($dsn)) {
  249. $res = $this->db =& $dsn;
  250. } else {
  251. $res = $this->db = &MDB::connect($dsn, $options);
  252. }
  253. if (MDB::isError($res)) {
  254. // FIXXME what shall we do here?
  255. $this->_errorLog($res->getUserInfo());
  256. } else {
  257. $this->db->setFetchMode(MDB_FETCHMODE_ASSOC);
  258. }
  259. }
  260. // }}}
  261. // {{{ getDbInstance()
  262. /**
  263. * @return object MDB Object
  264. * @access public
  265. */
  266. function &getDbInstance()
  267. {
  268. return $this->db;
  269. }
  270. // }}}
  271. // {{{ setDbInstance()
  272. /**
  273. * Setup using an existing connection.
  274. * this also sets the MDB_FETCHMODE_ASSOC since this class
  275. * needs this to be set!
  276. *
  277. * @param object a reference to an existing DB-object
  278. * @return void
  279. */
  280. function setDbInstance(&$dbh)
  281. {
  282. $this->db =& $dbh;
  283. $this->db->setFetchMode(MDB_FETCHMODE_ASSOC);
  284. }
  285. // }}}
  286. // {{{ get()
  287. /**
  288. * get the data of a single entry
  289. * if the second parameter is only one column the result will be returned
  290. * directly not as an array!
  291. *
  292. * @param integer the id of the element to retrieve
  293. * @param string if this is given only one row shall be returned,
  294. * directly, not an array
  295. * @return mixed (1) an array of the retrieved data
  296. * (2) if the second parameter is given and its only one column,
  297. * only this column's data will be returned
  298. * (3) false in case of failure
  299. * @access public
  300. */
  301. function get($id, $column='')
  302. {
  303. $id = trim($id);
  304. $column = trim($column);
  305. $table = $this->table;
  306. $getMethod = 'getRow';
  307. if ($column && !strpos($column, ',')) { // if only one column shall be selected
  308. $getMethod = 'getOne';
  309. }
  310. // we dont use 'setSelect' here, since this changes the setup of the class, we
  311. // build the query directly
  312. // if $column is '' then _buildSelect selects '*' anyway, so that's the same behaviour as before
  313. $query['select'] = $this->_buildSelect($column);
  314. $query['where'] = $this->_buildWhere($this->table.'.'.$this->primaryCol.'='.$this->_quote($id));
  315. $queryString = $this->_buildSelectQuery($query);
  316. return $this->returnResult($this->execute($queryString, $getMethod));
  317. }
  318. // }}}
  319. // {{{ getMultiple()
  320. /**
  321. * gets the data of the given ids
  322. *
  323. * @param array this is an array of ids to retreive
  324. * @param string the column to search in for
  325. * @return mixed an array of the retreived data, or false in case of failure
  326. * when failing an error is set in $this->_error
  327. * @access public
  328. */
  329. function getMultiple($ids, $column='')
  330. {
  331. $col = $this->primaryCol;
  332. if ($column) {
  333. $col = $column;
  334. }
  335. // FIXXME if $ids has no table.col syntax and we are using joins, the table better be put in front!!!
  336. $ids = $this->_quoteArray($ids);
  337. $query['where'] = $this->_buildWhere($col.' IN ('.implode(',', $ids).')');
  338. $queryString = $this->_buildSelectQuery($query);
  339. return $this->returnResult($this->execute($queryString));
  340. }
  341. // }}}
  342. // {{{ getAll()
  343. /**
  344. * get all entries from the DB
  345. * for sorting use setOrder!!!, the last 2 parameters are deprecated
  346. *
  347. * @param int to start from
  348. * @param int the number of rows to show
  349. * @return mixed an array of the retreived data, or false in case of failure
  350. * when failing an error is set in $this->_error
  351. * @access public
  352. */
  353. function getAll($from=0, $count=0, $method='getAll')
  354. {
  355. $query = array();
  356. if ($count) {
  357. $query = array('limit' => array($from, $count));
  358. }
  359. return $this->returnResult($this->execute($this->_buildSelectQuery($query), $method));
  360. }
  361. // }}}
  362. // {{{ getCol()
  363. /**
  364. * this method only returns one column, so the result will be a one dimensional array
  365. * this does also mean that using setSelect() should be set to *one* column, the one you want to
  366. * have returned a most common use case for this could be:
  367. * $table->setSelect('id');
  368. * $ids = $table->getCol();
  369. * OR
  370. * $ids = $table->getCol('id');
  371. * so ids will be an array with all the id's
  372. *
  373. * @param string the column that shall be retreived
  374. * @param int to start from
  375. * @param int the number of rows to show
  376. * @return mixed an array of the retreived data, or false in case of failure
  377. * when failing an error is set in $this->_error
  378. * @access public
  379. */
  380. function getCol($column=null, $from=0, $count=0)
  381. {
  382. $query = array();
  383. if ($column != null) {
  384. // by using _buildSelect() I can be sure that the table name will not be ambiguous
  385. // i.e. in a join, where all the joined tables have a col 'id'
  386. // _buildSelect() will put the proper table name in front in case there is none
  387. $query['select'] = $this->_buildSelect(trim($column));
  388. }
  389. if ($count) {
  390. $query['limit'] = array($from, $count);
  391. }
  392. $res = $this->returnResult($this->execute($this->_buildSelectQuery($query), 'getCol'));
  393. return ($res === false) ? array() : $res;
  394. }
  395. // }}}
  396. // {{{ getCount()
  397. /**
  398. * get the number of entries
  399. *
  400. * @return mixed an array of the retreived data, or false in case of failure
  401. * when failing an error is set in $this->_error
  402. * @access public
  403. */
  404. function getCount()
  405. {
  406. /* the following query works on mysql
  407. SELECT count(DISTINCT image.id) FROM image2tree
  408. RIGHT JOIN image ON image.id = image2tree.image_id
  409. the reason why this is needed - i just wanted to get the number of rows that do exist if the result is grouped by image.id
  410. the following query is what i tried first, but that returns the number of rows that have been grouped together
  411. for each image.id
  412. SELECT count(*) FROM image2tree
  413. RIGHT JOIN image ON image.id = image2tree.image_id GROUP BY image.id
  414. so that's why we do the following, i am not sure if that is standard SQL and absolutley correct!!!
  415. */
  416. //FIXXME see comment above if this is absolutely correct!!!
  417. if ($group = $this->_buildGroup()) {
  418. $query['select'] = 'COUNT(DISTINCT '.$group.')';
  419. $query['group'] = '';
  420. } else {
  421. $query['select'] = 'COUNT(*)';
  422. }
  423. $query['order'] = ''; // order is not of importance and might freak up the special group-handling up there, since the order-col is not be known
  424. /*# FIXXME use the following line, but watch out, then it has to be used in every method, or this
  425. # value will be used always, simply try calling getCount and getAll afterwards, getAll will return the count :-)
  426. # if getAll doesn't use setSelect!!!
  427. */
  428. //$this->setSelect('count(*)');
  429. $queryString = $this->_buildSelectQuery($query, true);
  430. return($res=$this->execute($queryString, 'getOne')) ? $res : 0;
  431. }
  432. // }}}
  433. // {{{ getDefaultValues()
  434. /**
  435. * return an empty element where all the array elements do already exist
  436. * corresponding to the columns in the DB
  437. *
  438. * @return array an empty, or pre-initialized element
  439. * @access public
  440. */
  441. function getDefaultValues()
  442. {
  443. $ret = array();
  444. // here we read all the columns from the DB and initialize them
  445. // with '' to prevent PHP-warnings in case we use error_reporting=E_ALL
  446. foreach ($this->metadata() as $aCol => $x) {
  447. $ret[$aCol] = '';
  448. }
  449. return $ret;
  450. }
  451. // }}}
  452. // {{{ getEmptyElement()
  453. /**
  454. * this is just for BC
  455. * @deprecated
  456. */
  457. function getEmptyElement()
  458. {
  459. $this->getDefaultValues();
  460. }
  461. // }}}
  462. // {{{ getQueryString()
  463. /**
  464. * Render the current query and return it as a string.
  465. *
  466. * @return string the current query
  467. */
  468. function getQueryString()
  469. {
  470. $ret = $this->_buildSelectQuery();
  471. if (is_string($ret)) {
  472. $ret = trim($ret);
  473. }
  474. return $ret;
  475. }
  476. // }}}
  477. // {{{ save()
  478. /**
  479. * save data, calls either update or add
  480. * if the primaryCol is given in the data this method knows that the
  481. * data passed to it are meant to be updated (call 'update'), otherwise it will
  482. * call the method 'add'.
  483. * If you dont like this behaviour simply stick with the methods 'add'
  484. * and 'update' and ignore this one here.
  485. * This method is very useful when you have validation checks that have to
  486. * be done for both adding and updating, then you can simply overwrite this
  487. * method and do the checks in here, and both cases will be validated first.
  488. *
  489. * @param array contains the new data that shall be saved in the DB
  490. * @return mixed the data returned by either add or update-method
  491. * @access public
  492. */
  493. function save($data)
  494. {
  495. if (isset($data[$this->primaryCol]) && $data[$this->primaryCol]) {
  496. return $this->update($data);
  497. }
  498. return $this->add($data);
  499. }
  500. // }}}
  501. // {{{ update()
  502. /**
  503. * update the member data of a data set
  504. *
  505. * @param array contains the new data that shall be saved in the DB
  506. * the id has to be given in the field with the key 'ID'
  507. * @return mixed true on success, or false otherwise
  508. * @access public
  509. */
  510. function update($newData)
  511. {
  512. $query = array();
  513. // do only set the 'where' part in $query, if a primary column is given
  514. // if not the default 'where' clause is used
  515. if (isset($newData[$this->primaryCol])) {
  516. //$this->_errorSet('Error updating the new member.');
  517. //return false;
  518. $query['where'] = $this->primaryCol.'='.$this->_quote($newData[$this->primaryCol]);
  519. }
  520. $newData = $this->_checkColumns($newData, 'update');
  521. $values = array();
  522. foreach ($newData as $key => $aData) { // quote the data
  523. //$values[] = "{$this->table}.$key=" . $this->_quote($aData);
  524. $values[] = "$key=" . $this->_quote($aData);
  525. }
  526. $query['set'] = implode(',', $values);
  527. $updateString = $this->_buildUpdateQuery($query);
  528. return $this->execute($updateString, 'query') ? true : false;
  529. }
  530. // }}}
  531. // {{{ add()
  532. /**
  533. * add a new member in the DB
  534. *
  535. * @param array contains the new data that shall be saved in the DB
  536. * @return mixed the inserted id on success, or false otherwise
  537. * @access public
  538. */
  539. function add($newData)
  540. {
  541. // if no primary col is given, get next sequence value
  542. if (empty($newData[$this->primaryCol])) {
  543. if ($this->primaryCol) {
  544. // do only use the sequence if a primary column is given
  545. // otherwise the data are written as given
  546. $id = (int)$this->db->nextId($this->sequenceName);
  547. $newData[$this->primaryCol] = $id;
  548. } else {
  549. // if no primary col is given return true on success
  550. $id = true;
  551. }
  552. } else {
  553. $id = $newData[$this->primaryCol];
  554. }
  555. //unset($newData[$this->primaryCol]);
  556. $newData = $this->_checkColumns($newData, 'add');
  557. $newData = $this->_quoteArray($newData);
  558. $query = sprintf('INSERT INTO %s (%s) VALUES (%s)',
  559. $this->table,
  560. implode(', ', array_keys($newData)),
  561. implode(', ', $newData)
  562. );
  563. return $this->execute($query, 'query') ? $id : false;
  564. }
  565. // }}}
  566. // {{{ addMultiple()
  567. /**
  568. * adds multiple new members in the DB
  569. *
  570. * @param array contains an array of new data that shall be saved in the DB
  571. * the key-value pairs have to be the same for all the data!!!
  572. * @return mixed the inserted ids on success, or false otherwise
  573. * @access public
  574. */
  575. function addMultiple($data)
  576. {
  577. if (!sizeof($data)) {
  578. return false;
  579. }
  580. // the inserted ids which will be returned or if no primaryCol is given
  581. // we return true by default
  582. $retIds = $this->primaryCol ? array() : true;
  583. $allData = array(); // each row that will be inserted
  584. foreach ($data as $key => $aData) {
  585. $aData = $this->_checkColumns($aData,'add');
  586. $aData = $this->_quoteArray($aData);
  587. if (empty($aData[$this->primaryCol])) {
  588. if ($this->primaryCol) {
  589. // do only use the sequence if a primary column is given
  590. // otherwise the data are written as given
  591. $retIds[] = $id = (int)$this->db->nextId($this->sequenceName);
  592. $aData[$this->primaryCol] = $id;
  593. }
  594. } else {
  595. $retIds[] = $aData[$this->primaryCol];
  596. }
  597. $allData[] = '('.implode(', ', $aData).')';
  598. }
  599. $query = sprintf( 'INSERT INTO %s (%s) VALUES %s',
  600. $this->table ,
  601. implode(', ', array_keys($aData)) ,
  602. implode(', ', $allData)
  603. );
  604. return $this->execute($query, 'query') ? $retIds : false;
  605. }
  606. // }}}
  607. // {{{ remove()
  608. /**
  609. * removes a member from the DB
  610. *
  611. * @param mixed integer/string - the value of the column that shall be removed
  612. * array - multiple columns that shall be matched, the second parameter will be ignored
  613. * @param string the column to match the data against, only if $data is not an array
  614. * @return boolean
  615. * @access public
  616. */
  617. function remove($data, $whereCol='')
  618. {
  619. //$raw = $this->getOption('raw');
  620. if (is_array($data)) {
  621. //FIXXME check $data if it only contains columns that really exist in the table
  622. $wheres = array();
  623. foreach ($data as $key => $val) {
  624. $wheres[] = $key .'='. $this->_quote($val);
  625. }
  626. $whereClause = implode(' AND ', $wheres);
  627. } else {
  628. if ($whereCol=='') {
  629. $whereCol = $this->primaryCol;
  630. }
  631. $whereClause = $whereCol.'='. $this->_quote($data);
  632. }
  633. $query = sprintf( 'DELETE FROM %s WHERE %s',
  634. $this->table,
  635. $whereClause
  636. );
  637. return $this->execute($query, 'query') ? true : false;
  638. // i think this method should return the ID's that it removed, this way we could simply use the result
  639. // for further actions that depend on those id ... or? make stuff easier, see ignaz::imail::remove
  640. }
  641. // }}}
  642. // {{{ removeAll()
  643. /**
  644. * empty a table
  645. *
  646. * @return resultSet or false on error [execute() result]
  647. * @access public
  648. */
  649. function removeAll()
  650. {
  651. $query = 'DELETE FROM ' . $this->table;
  652. return $this->execute($query, 'query') ? true : false;
  653. }
  654. // }}}
  655. // {{{ removeMultiple()
  656. /**
  657. * remove the datasets with the given ids
  658. *
  659. * @param array the ids to remove
  660. * @return resultSet or false on error [execute() result]
  661. * @access public
  662. */
  663. function removeMultiple($ids, $colName='')
  664. {
  665. if ($colName == '') {
  666. $colName = $this->primaryCol;
  667. }
  668. $ids = $this->_quoteArray($ids);
  669. $query = sprintf(
  670. 'DELETE FROM %s WHERE %s IN (%s)',
  671. $this->table,
  672. $colName,
  673. implode(',', $ids)
  674. );
  675. return $this->execute($query, 'query') ? true : false;
  676. }
  677. // }}}
  678. // {{{ removePrimary()
  679. /**
  680. * removes a member from the DB and calls the remove methods of the given objects
  681. * so all rows in another table that refer to this table are erased too
  682. *
  683. * @param integer the value of the primary key
  684. * @param string the column name of the tables with the foreign keys
  685. * @param object just for convinience, so nobody forgets to call this method
  686. * with at least one object as a parameter
  687. * @return boolean
  688. * @access public
  689. */
  690. function removePrimary($id, $colName, $atLeastOneObject)
  691. {
  692. $argCounter = 2; // we have 2 parameters that need to be given at least
  693. // func_get_arg returns false and a warning if there are no more parameters, so
  694. // we suppress the warning and check for false
  695. while ($object = @func_get_arg($argCounter++)) {
  696. //FIXXXME let $object also simply be a table name
  697. if (!$object->remove($id, $colName)) {
  698. //FIXXXME do this better
  699. $this->_errorSet("Error removing '$colName=$id' from table {$object->table}.");
  700. return false;
  701. }
  702. }
  703. if (!$this->remove($id)) {
  704. return false;
  705. }
  706. return true;
  707. }
  708. // }}}
  709. // {{{ setLimit()
  710. /**
  711. * sets query limits
  712. *
  713. * @param integer $from start index
  714. * @param integer $count number of results
  715. * @access public
  716. */
  717. function setLimit($from=0, $count=0)
  718. {
  719. if ($from==0 && $count==0) {
  720. $this->_limit = array();
  721. } else {
  722. $this->_limit = array($from, $count);
  723. }
  724. }
  725. // }}}
  726. // {{{ getLimit()
  727. /**
  728. * gets query limits
  729. *
  730. * @return array (start index, number of results)
  731. * @access public
  732. */
  733. function getLimit()
  734. {
  735. return $this->_limit;
  736. }
  737. // }}}
  738. // {{{ setWhere()
  739. /**
  740. * Sets the where condition which is used for the current instance
  741. *
  742. * @param string the where condition, this can be complete like 'X=7 AND Y=8'
  743. * @access public
  744. */
  745. function setWhere($whereCondition='')
  746. {
  747. $this->_where = $whereCondition;
  748. //FIXXME parse the where condition and replace ambigious column names, such as "name='Deutschland'" with "country.name='Deutschland'"
  749. // then the users dont have to write that explicitly and can use the same name as in the setOrder i.e. setOrder('name,_net_name,_netPrefix_prefix');
  750. }
  751. // }}}
  752. // {{{ getWhere()
  753. /**
  754. * Gets the where condition which is used for the current instance
  755. *
  756. * @return string the where condition, this can be complete like 'X=7 AND Y=8'
  757. * @access public
  758. */
  759. function getWhere()
  760. {
  761. return $this->_where;
  762. }
  763. // }}}
  764. // {{{ addWhere()
  765. /**
  766. * Only adds a string to the where clause
  767. *
  768. * @param string the where clause to add to the existing one
  769. * @param string the condition for how to concatenate the new where clause
  770. * to the existing one
  771. * @access public
  772. */
  773. function addWhere($where, $condition='AND')
  774. {
  775. if ($this->getWhere()) {
  776. $where = $this->getWhere().' '.$condition.' '.$where;
  777. }
  778. $this->setWhere($where);
  779. }
  780. // }}}
  781. // {{{ addWhereSearch()
  782. /**
  783. * add a where-like clause which works like a search for the given string
  784. * i.e. calling it like this:
  785. * $this->addWhereSearch('name', 'otto hans')
  786. * produces a where clause like this one
  787. * UPPER(name) LIKE "%OTTO%HANS%"
  788. * so the search finds the given string
  789. *
  790. * @param string the column to search in for
  791. * @param string the string to search for
  792. * @param string the condition
  793. * @access public
  794. */
  795. function addWhereSearch($column, $string, $condition='AND')
  796. {
  797. // if the column doesn't contain a tablename use the current table name
  798. // in case it is a defined column to prevent ambiguous rows
  799. if (strpos($column, '.') === false) {
  800. $meta = $this->metadata();
  801. if (isset($meta[$column])) {
  802. $column = $this->table .'.'. trim($column);
  803. }
  804. }
  805. //ibase doesn't have the LOWER() function, so using UPPER() here...
  806. $string = $this->_quote('%'.str_replace(' ', '%', strtoupper($string)).'%');
  807. $this->addWhere("UPPER($column) LIKE $string", $condition);
  808. }
  809. // }}}
  810. // {{{ setOrder()
  811. /**
  812. * sets the order condition which is used for the current instance
  813. *
  814. * @param string the where condition, this can be complete like 'X=7 AND Y=8'
  815. * @param boolean sorting order (TRUE => ASC, FALSE => DESC)
  816. * @access public
  817. */
  818. function setOrder($orderCondition='', $desc=false)
  819. {
  820. $this->_order = $orderCondition . ($desc ? ' DESC' : '');
  821. }
  822. // }}}
  823. // {{{ addOrder()
  824. /**
  825. * Add a order parameter to the query.
  826. *
  827. * @param string the where condition, this can be complete like 'X=7 AND Y=8'
  828. * @param boolean sorting order (TRUE => ASC, FALSE => DESC)
  829. * @access public
  830. */
  831. function addOrder($orderCondition='', $desc=false)
  832. {
  833. $order = $orderCondition . ($desc ? ' DESC' : '');
  834. if ($this->_order) {
  835. $this->_order = $this->_order .','. $order;
  836. } else {
  837. $this->_order = $order;
  838. }
  839. }
  840. // }}}
  841. // {{{ getOrder()
  842. /**
  843. * gets the order condition which is used for the current instance
  844. *
  845. * @return string the order condition, this can be complete like 'ID,TIMESTAMP DESC'
  846. * @access public
  847. */
  848. function getOrder()
  849. {
  850. return $this->_order;
  851. }
  852. // }}}
  853. // {{{ setHaving()
  854. /**
  855. * sets the having definition
  856. *
  857. * @param string the having definition
  858. * @access public
  859. */
  860. function setHaving($having='')
  861. {
  862. $this->_having = $having;
  863. }
  864. // }}}
  865. // {{{ getHaving()
  866. /**
  867. * gets the having definition which is used for the current instance
  868. *
  869. * @return string the having definition
  870. * @access public
  871. */
  872. function getHaving()
  873. {
  874. return $this->_having;
  875. }
  876. // }}}
  877. // {{{ addHaving()
  878. /**
  879. * Extend the current having clause. This is very useful, when you are building
  880. * this clause from different places and don't want to overwrite the currently
  881. * set having clause, but extend it.
  882. *
  883. * @param string this is a having clause, i.e. 'column' or 'table.column' or 'MAX(column)'
  884. * @param string the connection string, which usually stays the default, which is ',' (a comma)
  885. * @access public
  886. */
  887. function addHaving($what='*', $connectString=' AND ')
  888. {
  889. if ($this->_having) {
  890. $this->_having = $this->_having . $connectString . $what;
  891. } else {
  892. $this->_having = $what;
  893. }
  894. }
  895. // }}}
  896. // {{{ setJoin()
  897. /**
  898. * sets a join-condition
  899. *
  900. * @param mixed either a string or an array that contains
  901. * the table(s) to join on the current table
  902. * @param string the where clause for the join
  903. * @access public
  904. */
  905. function setJoin($table=null, $where=null, $joinType='default')
  906. {
  907. //FIXXME make it possible to pass a table name as a string like this too 'user u' where u is the string that can be used
  908. // to refer to this table in a where/order or whatever condition
  909. // this way it will be possible to join tables with itself, like setJoin(array('user u', 'user u1'))
  910. // this wouldnt work yet, but for doing so we would need to change the _build methods too!!!
  911. // because they use getJoin('tables') and this simply returns all the tables in use but dont take care of the mentioned syntax
  912. if (is_null($table) || is_null($where)) { // remove the join if not sufficient parameters are given
  913. $this->_join[$joinType] = array();
  914. return;
  915. }
  916. /* this causes problems if we use the order-by, since it doenst know the name to order it by ... :-)
  917. // replace the table names with the internal name used for the join
  918. // this way we can also join one table multiple times if it will be implemented one day
  919. $this->_join[$table] = preg_replace('/'.$table.'/','j1',$where);
  920. */
  921. $this->_join[$joinType][$table] = $where;
  922. }
  923. // }}}
  924. // {{{ setLeftJoin()
  925. /**
  926. * if you do a left join on $this->table you will get all entries
  927. * from $this->table, also if there are no entries for them in the joined table
  928. * if both parameters are not given the left-join will be removed
  929. * NOTE: be sure to only use either a right or a left join
  930. *
  931. * @param string the table(s) to be left-joined
  932. * @param string the where clause for the join
  933. * @access public
  934. */
  935. function setLeftJoin($table=null, $where=null)
  936. {
  937. $this->setJoin($table, $where, 'left');
  938. }
  939. // }}}
  940. // {{{ addLeftJoin()
  941. /**
  942. *
  943. * @param string the table(s) to be left-joined
  944. * @param string the where clause for the join
  945. * @param string join type
  946. * @access public
  947. */
  948. function addLeftJoin($table, $where, $type='left')
  949. {
  950. // init value, to prevent E_ALL-warning
  951. if (!isset($this->_join[$type]) || !$this->_join[$type]) {
  952. $this->_join[$type] = array();
  953. }
  954. $this->_join[$type][$table] = $where;
  955. }
  956. // }}}
  957. // {{{ setRightJoin()
  958. /**
  959. * see setLeftJoin for further explaination on what a left/right join is
  960. * NOTE: be sure to only use either a right or a left join
  961. //FIXXME check if the above sentence is necessary and if sql doesnt allow the use of both
  962. *
  963. * @param string the table(s) to be right-joined
  964. * @param string the where clause for the join
  965. * @see setLeftJoin()
  966. * @access public
  967. */
  968. function setRightJoin($table=null, $where=null)
  969. {
  970. $this->setJoin($table, $where, 'right');
  971. }
  972. // }}}
  973. // {{{ getJoin()
  974. /**
  975. * gets the join-condition
  976. *
  977. * @access public
  978. * @param string [null|''|'table'|'tables'|'right'|'left']
  979. * @return array gets the join parameters
  980. */
  981. function getJoin($what=null)
  982. {
  983. // if the user requests all the join data or if the join is empty, return it
  984. if (is_null($what) || empty($this->_join)) {
  985. return $this->_join;
  986. }
  987. $ret = array();
  988. switch (strtolower($what)) {
  989. case 'table':
  990. case 'tables':
  991. foreach ($this->_join as $aJoin) {
  992. if (count($aJoin)) {
  993. $ret = array_merge($ret, array_keys($aJoin));
  994. }
  995. }
  996. break;
  997. case 'right': // return right-join data only
  998. case 'left': // return left join data only
  999. if (count($this->_join[$what])) {
  1000. $ret = array_merge($ret, $this->_join[$what]);
  1001. }
  1002. break;
  1003. }
  1004. return $ret;
  1005. }
  1006. // }}}
  1007. // {{{ addJoin()
  1008. /**
  1009. * adds a table and a where clause that shall be used for the join
  1010. * instead of calling
  1011. * setJoin(array(table1,table2),'<where clause1> AND <where clause2>')
  1012. * you can also call
  1013. * setJoin(table1,'<where clause1>')
  1014. * addJoin(table2,'<where clause2>')
  1015. * or where it makes more sense is to build a query which is build out of a
  1016. * left join and a standard join
  1017. * setLeftJoin(table1,'<where clause1>')
  1018. * // results in ... FROM $this->table LEFT JOIN table ON <where clause1>
  1019. * addJoin(table2,'<where clause2>')
  1020. * // results in ... FROM $this->table,table2 LEFT JOIN table ON <where clause1> WHERE <where clause2>
  1021. *
  1022. * @param array $table
  1023. * @param string $where
  1024. * @param string $type
  1025. * @access public
  1026. */
  1027. function addJoin($table, $where, $type='default')
  1028. {
  1029. if ($table == $this->table) {
  1030. return; //skip. Self joins are not supported.
  1031. }
  1032. // init value, to prevent E_ALL-warning
  1033. if (!array_key_exists($type, $this->_join)) {
  1034. $this->_join[$type] = array();
  1035. }
  1036. $this->_join[$type][$table] = $where;
  1037. }
  1038. // }}}
  1039. // {{{ setTable()
  1040. /**
  1041. * sets the table this class is currently working on
  1042. *
  1043. * @param string the table name
  1044. * @access public
  1045. */
  1046. function setTable($table)
  1047. {
  1048. $this->table = $table;
  1049. }
  1050. // }}}
  1051. // {{{ getTable()
  1052. /**
  1053. * gets the table this class is currently working on
  1054. *
  1055. * @return string the table name
  1056. * @access public
  1057. */
  1058. function getTable()
  1059. {
  1060. return $this->table;
  1061. }
  1062. // }}}
  1063. // {{{ setGroup()
  1064. /**
  1065. * sets the group-by condition
  1066. *
  1067. * @param string the group condition
  1068. * @access public
  1069. */
  1070. function setGroup($group='')
  1071. {
  1072. $this->_group = $group;
  1073. //FIXXME parse the condition and replace ambigious column names, such as "name='Deutschland'" with "country.name='Deutschland'"
  1074. // then the users dont have to write that explicitly and can use the same name as in the setOrder i.e. setOrder('name,_net_name,_netPrefix_prefix');
  1075. }
  1076. // }}}
  1077. // {{{ getGroup()
  1078. /**
  1079. * gets the group condition which is used for the current instance
  1080. *
  1081. * @return string the group condition
  1082. * @access public
  1083. */
  1084. function getGroup()
  1085. {
  1086. return $this->_group;
  1087. }
  1088. // }}}
  1089. // {{{ setSelect()
  1090. /**
  1091. * limit the result to return only the columns given in $what
  1092. * @access public
  1093. */
  1094. function setSelect($what='*')
  1095. {
  1096. $this->_select = $what;
  1097. }
  1098. // }}}
  1099. // {{{ addSelect()
  1100. /**
  1101. * add a string to the select part of the query
  1102. *
  1103. * add a string to the select-part of the query and connects it to an existing
  1104. * string using the $connectString, which by default is a comma.
  1105. * (SELECT xxx FROM - xxx is the select-part of a query)
  1106. *
  1107. * @param string the string that shall be added to the select-part
  1108. * @param string the string to connect the new string with the existing one
  1109. * @return void
  1110. * @access public
  1111. */
  1112. function addSelect($what='*', $connectString=',')
  1113. {
  1114. // if the select string is not empty add the string, otherwise simply set it
  1115. if ($this->_select) {
  1116. $this->_select = $this->_select . $connectString . $what;
  1117. } else {
  1118. $this->_select = $what;
  1119. }
  1120. }
  1121. // }}}
  1122. // {{{ getSelect()
  1123. /**
  1124. * @access public
  1125. */
  1126. function getSelect()
  1127. {
  1128. return $this->_select;
  1129. }
  1130. // }}}
  1131. // {{{ setDontSelect()
  1132. /**
  1133. * @access public
  1134. */
  1135. function setDontSelect($what='')
  1136. {
  1137. $this->_dontSelect = $what;
  1138. }
  1139. // }}}
  1140. // {{{ getDontSelect()
  1141. /**
  1142. * @access public
  1143. */
  1144. function getDontSelect()
  1145. {
  1146. return $this->_dontSelect;
  1147. }
  1148. // }}}
  1149. // {{{ reset()
  1150. /**
  1151. * reset all the set* settings, with no parameter given it resets them all.
  1152. *
  1153. * @return void
  1154. * @access public
  1155. */
  1156. function reset($what=array())
  1157. {
  1158. if (!sizeof($what)) {
  1159. $what = array('select',
  1160. 'dontSelect',
  1161. 'group',
  1162. 'having',
  1163. 'limit',
  1164. 'where',
  1165. 'index',
  1166. 'order',
  1167. 'join',
  1168. 'leftJoin',
  1169. 'rightJoin');
  1170. }
  1171. foreach ($what as $aReset) {
  1172. $this->{'set'.ucfirst($aReset)}();
  1173. }
  1174. }
  1175. // }}}
  1176. // {{{ setOption()
  1177. /**
  1178. * set mode the class shall work in
  1179. * currently we have the modes:
  1180. * 'raw' does not quote the data before building the query
  1181. *
  1182. * @param string the mode to be set
  1183. * @param mixed the value of the mode
  1184. * @return void
  1185. * @access public
  1186. */
  1187. function setOption($option, $value)
  1188. {
  1189. $this->options[strtolower($option)] = $value;
  1190. }
  1191. // }}}
  1192. // {{{ getOption()
  1193. /**
  1194. * @param string name of the option to retrieve
  1195. * @access public
  1196. */
  1197. function getOption($option)
  1198. {
  1199. return $this->options[strtolower($option)];
  1200. }
  1201. // }}}
  1202. // {{{ _quoteArray()
  1203. /**
  1204. * quotes all the data in this array if we are not in raw mode!
  1205. * @param array $data
  1206. * @return array
  1207. * @access private
  1208. */
  1209. function _quoteArray($data)
  1210. {
  1211. if (!$this->getOption('raw')) { //check added for gain in speed if $this->raw==true
  1212. foreach ($data as $key => $val) {
  1213. $data[$key] = $this->_quote($val);
  1214. }
  1215. }
  1216. return $data;
  1217. }
  1218. // }}}
  1219. // {{{ _quote()
  1220. /**
  1221. * quotes all the data in this array if we are not in raw mode!
  1222. * @param mixed $data
  1223. * @return mixed
  1224. * @access private
  1225. */
  1226. function _quote($data)
  1227. {
  1228. if ($this->getOption('raw')) {
  1229. return $data;
  1230. } else {
  1231. switch(gettype($data)) {
  1232. case 'array':
  1233. return $this->_quoteArray($data);
  1234. case 'boolean':
  1235. return $this->db->getBooleanValue($data);
  1236. case 'double':
  1237. return $this->db->getFloatValue($data);
  1238. case 'integer':
  1239. return $this->db->getIntegerValue($data);
  1240. case 'string': //if 'string' or 'unknown', quote as text
  1241. default:
  1242. return $this->db->getTextValue($data);
  1243. }
  1244. }
  1245. }
  1246. // }}}
  1247. // {{{ _checkColumns()
  1248. /**
  1249. * checks if the columns which are given as the array's indexes really exist
  1250. * if not it will be unset anyway
  1251. *
  1252. * @access public
  1253. * @param string the actual message, first word should always be the method name,
  1254. * to build the message like this: className::methodname
  1255. * @param integer the line number
  1256. */
  1257. function _checkColumns($newData, $method='unknown')
  1258. {
  1259. if (!$meta = $this->metadata()) {
  1260. // if no metadata available, return data as given
  1261. return $newData;
  1262. }
  1263. foreach ($newData as $colName => $x) {
  1264. if (!isset($meta[$colName])) {
  1265. $this->_errorLog("$method, column {$this->table}.$colName doesnt exist, value was removed before '$method'",__LINE__);
  1266. unset($newData[$colName]);
  1267. } else {
  1268. // if the current column exists, check the length too, not to write content that is too long
  1269. // prevent DB-errors here
  1270. // do only check the data length if this field is given
  1271. if (isset($meta[$colName]['len']) && ($meta[$colName]['len'] != -1) &&
  1272. ($oldLength=strlen($newData[$colName])) > $meta[$colName]['len'])
  1273. {
  1274. $this->_errorLog("_checkColumns, had to trim column '$colName' from $oldLength to ".
  1275. $meta[$colName]['len'].' characters.',__LINE__);
  1276. $newData[$colName] = substr($newData[$colName],0,$meta[$colName]['len']);
  1277. }
  1278. }
  1279. }
  1280. return $newData;
  1281. }
  1282. // }}}
  1283. // {{{ debug()
  1284. /**
  1285. * overwrite this method and i.e. print the query $string
  1286. * to see the final query
  1287. *
  1288. * @param string the query mostly
  1289. * @access public
  1290. */
  1291. function debug($string) {}
  1292. //
  1293. //
  1294. // ONLY ORACLE SPECIFIC, not very nice since it is DB dependent, but we need it!!!
  1295. //
  1296. //
  1297. // }}}
  1298. // {{{ metadata()
  1299. /**
  1300. * !!!! query COPIED FROM db_oci8.inc - from PHPLIB !!!!
  1301. *
  1302. * @see db_oci8.inc - PHPLIB
  1303. * @param string $table
  1304. * @return resultSet or false on error
  1305. * @access public
  1306. */
  1307. function metadata($table='')
  1308. {
  1309. // is there an alias in the table name, then we have something like this: 'user ua'
  1310. // cut of the alias and return the table name
  1311. if (strpos($table, ' ') !== false) {
  1312. $split = explode(' ', trim($table));
  1313. $table = $split[0];
  1314. }
  1315. $full = false;
  1316. if (empty($table)) {
  1317. $table = $this->table;
  1318. }
  1319. // to prevent multiple selects for the same metadata
  1320. if (isset($this->_metadata[$table])) {
  1321. return $this->_metadata[$table];
  1322. }
  1323. // FIXXXME use oci8 implementation of newer PEAR::DB-version
  1324. if ($this->db->phptype=='oci8') {
  1325. $count = 0;
  1326. $id = 0;
  1327. $res = array();
  1328. //# This is a RIGHT OUTER JOIN: "(+)", if you want to see, what
  1329. //# this query results try the following:
  1330. //// $table = new Table; $this->db = new my_DB_Sql; // you have to make
  1331. //// // your own class
  1332. //// $table->show_results($this->db->query(see query vvvvvv))
  1333. ////
  1334. $res = $this->db->getAll("SELECT T.column_name,T.table_name,T.data_type,".
  1335. "T.data_length,T.data_precision,T.data_scale,T.nullable,".
  1336. "T.char_col_decl_length,I.index_name".
  1337. " FROM ALL_TAB_COLUMNS T,ALL_IND_COLUMNS I".
  1338. " WHERE T.column_name=I.column_name (+)".
  1339. " AND T.table_name=I.table_name (+)".
  1340. " AND T.table_name=UPPER('$table') ORDER BY T.column_id");
  1341. if (MDB::isError($res)) {
  1342. //$this->_errorSet( $res->getMessage() );
  1343. // i think we only need to log here, since this method is never used
  1344. // directly for the user's functionality, which means if it fails it
  1345. // is most probably an appl error
  1346. $this->_errorLog($this->db->getUserInfo($res));
  1347. return false;
  1348. }
  1349. foreach ($res as $key=>$val) {
  1350. $res[$key]['name'] = $val['COLUMN_NAME'];
  1351. }
  1352. } else {
  1353. if (!is_object($this->db)) {
  1354. return false;
  1355. }
  1356. $res = $this->db->tableInfo($table);
  1357. if (MDB::isError($res)) {
  1358. $this->_errorSet($res->getUserInfo());
  1359. return false;
  1360. }
  1361. }
  1362. $ret = array();
  1363. foreach ($res as $key => $val) {
  1364. $ret[$val['name']] = $val;
  1365. }
  1366. if ($this->db->options['optimize'] == 'portability') {
  1367. $ret = array_change_key_case($ret, CASE_LOWER);
  1368. }
  1369. $this->_metadata[$table] = $ret;
  1370. return $ret;
  1371. }
  1372. //
  1373. // methods for building the query
  1374. //
  1375. // }}}
  1376. // {{{ _buildFrom()
  1377. /**
  1378. * build the from string
  1379. *
  1380. * @return string the string added after FROM
  1381. * @access private
  1382. */
  1383. function _buildFrom()
  1384. {
  1385. $from = $this->table;
  1386. $join = $this->getJoin();
  1387. if (!$join) { // no join set
  1388. return $from;
  1389. }
  1390. // handle the standard join thingy
  1391. if (isset($join['default']) && count($join['default'])) {
  1392. $from .= ','.implode(',',array_keys($join['default']));
  1393. }
  1394. // handle left/right joins
  1395. foreach (array('left', 'right') as $joinType) {
  1396. if (isset($join[$joinType]) && count($join[$joinType])) {
  1397. foreach($join[$joinType] as $table => $condition) {
  1398. // replace the _TABLENAME_COLUMNNAME by TABLENAME.COLUMNNAME
  1399. // since oracle doesnt work with the _TABLENAME_COLUMNN…

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