3 * Object Based Database Query Builder and data store
5 * For PHP versions 4,5 and 6
7 * LICENSE: This source file is subject to version 3.01 of the PHP license
8 * that is available through the world-wide-web at the following URI:
9 * http://www.php.net/license/3_01.txt. If you did not receive a copy of
10 * the PHP License and are unable to obtain it through the web, please
11 * send a note to license@php.net so we can mail you a copy immediately.
14 * @package DB_DataObject
15 * @author Alan Knowles <alan@akbkhome.com>
16 * @copyright 1997-2006 The PHP Group
17 * @license http://www.php.net/license/3_01.txt PHP License 3.01
18 * @version CVS: $Id: DataObject.php 320069 2011-11-28 04:34:08Z alan_k $
19 * @link http://pear.php.net/package/DB_DataObject
23 /* ===========================================================================
25 * !!!!!!!!!!!!! W A R N I N G !!!!!!!!!!!
27 * THIS MAY SEGFAULT PHP IF YOU ARE USING THE ZEND OPTIMIZER (to fix it,
28 * just add "define('DB_DATAOBJECT_NO_OVERLOAD',true);" before you include
29 * this file. reducing the optimization level may also solve the segfault.
30 * ===========================================================================
34 * The main "DB_DataObject" class is really a base class for your own tables classes
36 * // Set up the class by creating an ini file (refer to the manual for more details
38 * database = mysql:/username:password@host/database
39 * schema_location = /home/myapplication/database
40 * class_location = /home/myapplication/DBTables/
41 * clase_prefix = DBTables_
44 * //Start and initialize...................... - dont forget the &
45 * $config = parse_ini_file('example.ini',true);
46 * $options = &PEAR::getStaticProperty('DB_DataObject','options');
47 * $options = $config['DB_DataObject'];
49 * // example of a class (that does not use the 'auto generated tables data')
50 * class mytable extends DB_DataObject {
51 * // mandatory - set the table
52 * var $_database_dsn = "mysql://username:password@localhost/database";
53 * var $__table = "mytable";
56 * 'id' => 1, // integer or number
57 * 'name' => 2, // string
65 * // use in the application
70 * $instance = new mytable;
71 * $instance->get("id",12);
72 * echo $instance->somedata;
77 * $instance = new mytable;
78 * $instance->whereAdd("ID > 12");
79 * $instance->whereAdd("ID < 14");
81 * while ($instance->fetch()) {
82 * echo $instance->somedata;
88 * - we use getStaticProperty from PEAR pretty extensively (cant remove it ATM)
91 require_once 'PEAR.php';
94 * We are duping fetchmode constants to be compatible with
97 define('DB_DATAOBJECT_FETCHMODE_ORDERED',1);
98 define('DB_DATAOBJECT_FETCHMODE_ASSOC',2);
105 * these are constants for the get_table array
106 * user to determine what type of escaping is required around the object vars.
108 define('DB_DATAOBJECT_INT', 1); // does not require ''
109 define('DB_DATAOBJECT_STR', 2); // requires ''
111 define('DB_DATAOBJECT_DATE', 4); // is date #TODO
112 define('DB_DATAOBJECT_TIME', 8); // is time #TODO
113 define('DB_DATAOBJECT_BOOL', 16); // is boolean #TODO
114 define('DB_DATAOBJECT_TXT', 32); // is long text #TODO
115 define('DB_DATAOBJECT_BLOB', 64); // is blob type
118 define('DB_DATAOBJECT_NOTNULL', 128); // not null col.
119 define('DB_DATAOBJECT_MYSQLTIMESTAMP' , 256); // mysql timestamps (ignored by update/insert)
121 * Define this before you include DataObjects.php to disable overload - if it segfaults due to Zend optimizer..
123 //define('DB_DATAOBJECT_NO_OVERLOAD',true)
127 * Theses are the standard error codes, most methods will fail silently - and return false
128 * to access the error message either use $table->_lastError
129 * or $last_error = PEAR::getStaticProperty('DB_DataObject','lastError');
130 * the code is $last_error->code, and the message is $last_error->message (a standard PEAR error)
133 define('DB_DATAOBJECT_ERROR_INVALIDARGS', -1); // wrong args to function
134 define('DB_DATAOBJECT_ERROR_NODATA', -2); // no data available
135 define('DB_DATAOBJECT_ERROR_INVALIDCONFIG', -3); // something wrong with the config
136 define('DB_DATAOBJECT_ERROR_NOCLASS', -4); // no class exists
137 define('DB_DATAOBJECT_ERROR_INVALID_CALL' ,-7); // overlad getter/setter failure
140 * Used in methods like delete() and count() to specify that the method should
141 * build the condition only out of the whereAdd's and not the object parameters.
143 define('DB_DATAOBJECT_WHEREADD_ONLY', true);
147 * storage for connection and result objects,
148 * it is done this way so that print_r()'ing the is smaller, and
149 * it reduces the memory size of the object.
150 * -- future versions may use $this->_connection = & PEAR object..
151 * although will need speed tests to see how this affects it.
152 * - includes sub arrays
153 * - connections = md5 sum mapp to pear db object
154 * - results = [id] => map to pear db object
155 * - resultseq = sequence id for results & results field
156 * - resultfields = [id] => list of fields return from query (for use with toArray())
157 * - ini = mapping of database to ini file results
158 * - links = mapping of database to links file
159 * - lasterror = pear error objects for last error event.
160 * - config = aliased view of PEAR::getStaticPropery('DB_DataObject','options') * done for performance.
161 * - array of loaded classes by autoload method - to stop it doing file access request over and over again!
163 $GLOBALS['_DB_DATAOBJECT']['RESULTS'] = array();
164 $GLOBALS['_DB_DATAOBJECT']['RESULTSEQ'] = 1;
165 $GLOBALS['_DB_DATAOBJECT']['RESULTFIELDS'] = array();
166 $GLOBALS['_DB_DATAOBJECT']['CONNECTIONS'] = array();
167 $GLOBALS['_DB_DATAOBJECT']['INI'] = array();
168 $GLOBALS['_DB_DATAOBJECT']['LINKS'] = array();
169 $GLOBALS['_DB_DATAOBJECT']['SEQUENCE'] = array();
170 $GLOBALS['_DB_DATAOBJECT']['LASTERROR'] = null;
171 $GLOBALS['_DB_DATAOBJECT']['CONFIG'] = array();
172 $GLOBALS['_DB_DATAOBJECT']['CACHE'] = array();
173 $GLOBALS['_DB_DATAOBJECT']['OVERLOADED'] = false;
174 $GLOBALS['_DB_DATAOBJECT']['QUERYENDTIME'] = 0;
178 // this will be horrifically slow!!!!
179 // these two are BC/FC handlers for call in PHP4/5
182 if (!defined('DB_DATAOBJECT_NO_OVERLOAD')) {
184 class DB_DataObject_Overload
186 function __call($method,$args)
189 $this->_call($method,$args,$return);
194 return array_keys(get_object_vars($this)) ;
198 class DB_DataObject_Overload {}
209 * @package DB_DataObject
210 * @author Alan Knowles <alan@akbkhome.com>
214 class DB_DataObject extends DB_DataObject_Overload
217 * The Version - use this to check feature changes
222 var $_DB_DataObject_version = "1.11.3";
225 * The Database table (used by table extends)
230 var $__table = ''; // database table
233 * The Number of rows returned from a query
238 var $N = 0; // Number of rows returned from a query
240 /* ============================================================= */
241 /* Major Public Methods */
242 /* (designed to be optionally then called with parent::method()) */
243 /* ============================================================= */
247 * Get a result using key, value.
250 * $object->get("ID",1234);
251 * Returns Number of rows located (usually 1) for success,
252 * and puts all the table columns into this classes variables
254 * see the fetch example on how to extend this.
256 * if no value is entered, it is assumed that $key is a value
257 * and get will then use the first key in keys()
260 * @param string $k column
261 * @param string $v value
263 * @return int No. of rows
265 function get($k = null, $v = null)
267 global $_DB_DATAOBJECT;
268 if (empty($_DB_DATAOBJECT['CONFIG'])) {
269 DB_DataObject::_loadConfig();
275 $keys = $this->keys();
277 $this->raiseError("No Keys available for {$this->tableName()}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
282 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
283 $this->debug("$k $v " .print_r($keys,true), "GET");
287 $this->raiseError("No Value specified for get", DB_DATAOBJECT_ERROR_INVALIDARGS);
291 return $this->find(1);
295 * Get the value of the primary id
297 * While I normally use 'id' as the PRIMARY KEY value, some database use
298 * {table}_id as the column name.
300 * To save a bit of typing,
308 $keys = $this->keys();
310 $this->raiseError("No Keys available for {$this->tableName()}",
311 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
315 if (empty($this->$k)) { // we do not
316 $this->raiseError("pid() called on Object where primary key value not available",
317 DB_DATAOBJECT_ERROR_NODATA);
326 * build the basic select query.
331 function _build_select()
333 global $_DB_DATAOBJECT;
334 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
335 if ($quoteIdentifiers) {
337 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
339 $tn = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName()) ;
340 if (!empty($this->_query['derive_table']) && !empty($this->_query['derive_select']) ) {
342 // this is a derived select..
343 // not much support in the api yet..
346 $this->_query['derive_select']
348 $this->_query['data_select'] . " \n" .
350 $this->_join . " \n" .
351 $this->_query['condition'] . " \n" .
352 $this->_query['group_by'] . " \n" .
353 $this->_query['having'] . " \n" .
354 ') ' . $this->_query['derive_table'];
364 $this->_query['data_select'] . " \n" .
366 $this->_join . " \n" .
367 $this->_query['condition'] . " \n" .
368 $this->_query['group_by'] . " \n" .
369 $this->_query['having'] . " \n";
376 * find results, either normal or crosstable
380 * $object = new mytable();
385 * will set $object->N to number of rows, and expects next command to fetch rows
386 * will return $object->N
388 * if an error occurs $object->N will be set to false and return value will also be false;
389 * if numRows is not supported it will
392 * @param boolean $n Fetch first result
394 * @return mixed (number of rows returned, or true if numRows fetching is not supported)
396 function find($n = false)
398 global $_DB_DATAOBJECT;
399 if ($this->_query === false) {
401 "You cannot do two queries on the same object (copy it before finding)",
402 DB_DATAOBJECT_ERROR_INVALIDARGS);
406 if (empty($_DB_DATAOBJECT['CONFIG'])) {
407 DB_DataObject::_loadConfig();
410 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
411 $this->debug($n, "find",1);
413 if (!$this->__table) {
414 // xdebug can backtrace this!
415 trigger_error("NO \$__table SPECIFIED in class definition",E_USER_ERROR);
418 $query_before = $this->_query;
419 $this->_build_condition($this->table()) ;
423 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
426 $sql = $this->_build_select();
428 foreach ($this->_query['unions'] as $union_ar) {
429 $sql .= $union_ar[1] . $union_ar[0]->_build_select() . " \n";
432 $sql .= $this->_query['order_by'] . " \n";
435 /* We are checking for method modifyLimitQuery as it is PEAR DB specific */
436 if ((!isset($_DB_DATAOBJECT['CONFIG']['db_driver'])) ||
437 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB')) {
438 /* PEAR DB specific */
440 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
441 $sql = $DB->modifyLimitQuery($sql,$this->_query['limit_start'], $this->_query['limit_count']);
444 /* theoretically MDB2! */
445 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
446 $DB->setLimit($this->_query['limit_count'],$this->_query['limit_start']);
451 $err = $this->_query($sql);
452 if (is_a($err,'PEAR_Error')) {
456 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
457 $this->debug("CHECK autofetchd $n", "find", 1);
463 if (!$ret && !empty($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
464 // clear up memory if nothing found!?
465 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
468 if ($n && $this->N > 0 ) {
469 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
470 $this->debug("ABOUT TO AUTOFETCH", "find", 1);
472 $fs = $this->fetch();
473 // if fetch returns false (eg. failed), then the backend doesnt support numRows (eg. ret=true)
474 // - hence find() also returns false..
475 $ret = ($ret === true) ? $fs : $ret;
477 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
478 $this->debug("DONE", "find", 1);
480 $this->_query = $query_before;
485 * fetches next row into this objects var's
487 * returns 1 on success 0 on failure
492 * $object = new mytable();
493 * $object->name = "fred";
496 * while ($object->fetch()) {
498 * $store[] = $object; // builds an array of object lines.
501 * to add features to a fetch
502 * function fetch () {
503 * $ret = parent::fetch();
504 * $this->date_formated = date('dmY',$this->date);
509 * @return boolean on success
514 global $_DB_DATAOBJECT;
515 if (empty($_DB_DATAOBJECT['CONFIG'])) {
516 DB_DataObject::_loadConfig();
518 if (empty($this->N)) {
519 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
520 $this->debug("No data returned from FIND (eg. N is 0)","FETCH", 3);
525 if (empty($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]) ||
526 !is_object($result = $_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]))
528 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
529 $this->debug('fetched on object after fetch completed (no results found)');
535 $array = $result->fetchRow(DB_DATAOBJECT_FETCHMODE_ASSOC);
536 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
537 $this->debug(serialize($array),"FETCH");
540 // fetched after last row..
541 if ($array === null) {
542 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
543 $t= explode(' ',microtime());
545 $this->debug("Last Data Fetch'ed after " .
546 ($t[0]+$t[1]- $_DB_DATAOBJECT['QUERYENDTIME'] ) .
550 // reduce the memory usage a bit... (but leave the id in, so count() works ok on it)
551 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
553 // we need to keep a copy of resultfields locally so toArray() still works
554 // however we dont want to keep it in the global cache..
556 if (!empty($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
557 $this->_resultFields = $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid];
558 unset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]);
560 // this is probably end of data!!
561 //DB_DataObject::raiseError("fetch: no data returned", DB_DATAOBJECT_ERROR_NODATA);
564 // make sure resultFields is always empty..
565 $this->_resultFields = false;
567 if (!isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
568 // note: we dont declare this to keep the print_r size down.
569 $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]= array_flip(array_keys($array));
571 $replace = array('.', ' ');
572 foreach($array as $k=>$v) {
573 // use strpos as str_replace is slow.
574 $kk = (strpos($k, '.') === false && strpos($k, ' ') === false) ?
575 $k : str_replace($replace, '_', $k);
577 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
578 $this->debug("$kk = ". $array[$k], "fetchrow LINE", 3);
580 $this->$kk = $array[$k];
584 $this->_link_loaded=false;
585 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
586 $this->debug("{$this->tableName()} DONE", "fetchrow",2);
588 if (($this->_query !== false) && empty($_DB_DATAOBJECT['CONFIG']['keep_query_after_fetch'])) {
589 $this->_query = false;
596 * fetches all results as an array,
598 * return format is dependant on args.
599 * if selectAdd() has not been called on the object, then it will add the correct columns to the query.
601 * A) Array of values (eg. a list of 'id')
603 * $x = DB_DataObject::factory('mytable');
604 * $x->whereAdd('something = 1')
605 * $ar = $x->fetchAll('id');
606 * -- returns array(1,2,3,4,5)
608 * B) Array of values (not from table)
610 * $x = DB_DataObject::factory('mytable');
611 * $x->whereAdd('something = 1');
613 * $x->selectAdd('distinct(group_id) as group_id');
614 * $ar = $x->fetchAll('group_id');
615 * -- returns array(1,2,3,4,5)
617 * C) A key=>value associative array
619 * $x = DB_DataObject::factory('mytable');
620 * $x->whereAdd('something = 1')
621 * $ar = $x->fetchAll('id','name');
622 * -- returns array(1=>'fred',2=>'blogs',3=> .......
624 * D) array of objects
625 * $x = DB_DataObject::factory('mytable');
626 * $x->whereAdd('something = 1');
627 * $ar = $x->fetchAll();
629 * E) array of arrays (for example)
630 * $x = DB_DataObject::factory('mytable');
631 * $x->whereAdd('something = 1');
632 * $ar = $x->fetchAll(false,false,'toArray');
635 * @param string|false $k key
636 * @param string|false $v value
637 * @param string|false $method method to call on each result to get array value (eg. 'toArray')
639 * @return array format dependant on arguments, may be empty
641 function fetchAll($k= false, $v = false, $method = false)
643 // should it even do this!!!?!?
645 ( // only do this is we have not been explicit..
646 empty($this->_query['data_select']) ||
647 ($this->_query['data_select'] == '*')
651 $this->selectAdd($k);
653 $this->selectAdd($v);
659 while ($this->fetch()) {
661 $ret[$this->$k] = $this->$v;
664 $ret[] = $k === false ?
665 ($method == false ? clone($this) : $this->$method())
674 * Adds a condition to the WHERE statement, defaults to AND
676 * $object->whereAdd(); //reset or cleaer ewhwer
677 * $object->whereAdd("ID > 20");
678 * $object->whereAdd("age > 20","OR");
680 * @param string $cond condition
681 * @param string $logic optional logic "OR" (defaults to "AND")
683 * @return string|PEAR::Error - previous condition or Error when invalid args found
685 function whereAdd($cond = false, $logic = 'AND')
687 // for PHP5.2.3 - there is a bug with setting array properties of an object.
688 $_query = $this->_query;
690 if (!isset($this->_query) || ($_query === false)) {
691 return $this->raiseError(
692 "You cannot do two queries on the same object (clone it before finding)",
693 DB_DATAOBJECT_ERROR_INVALIDARGS);
696 if ($cond === false) {
697 $r = $this->_query['condition'];
698 $_query['condition'] = '';
699 $this->_query = $_query;
700 return preg_replace('/^\s+WHERE\s+/','',$r);
702 // check input...= 0 or ' ' == error!
704 return $this->raiseError("WhereAdd: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
706 $r = $_query['condition'];
707 if ($_query['condition']) {
708 $_query['condition'] .= " {$logic} ( {$cond} )";
709 $this->_query = $_query;
712 $_query['condition'] = " WHERE ( {$cond} ) ";
713 $this->_query = $_query;
718 * Adds a 'IN' condition to the WHERE statement
720 * $object->whereAddIn('id', $array, 'int'); //minimal usage
721 * $object->whereAddIn('price', $array, 'float', 'OR'); // cast to float, and call whereAdd with 'OR'
722 * $object->whereAddIn('name', $array, 'string'); // quote strings
724 * @param string $key key column to match
725 * @param array $list list of values to match
726 * @param string $type string|int|integer|float|bool cast to type.
727 * @param string $logic optional logic to call whereAdd with eg. "OR" (defaults to "AND")
729 * @return string|PEAR::Error - previous condition or Error when invalid args found
731 function whereAddIn($key, $list, $type, $logic = 'AND')
734 if ($key[0] == '!') {
736 $key = substr($key, 1);
738 // fix type for short entry.
739 $type = $type == 'int' ? 'integer' : $type;
741 if ($type == 'string') {
746 foreach($list as $k) {
748 $ar[] = $type == 'string' ? $this->_quote($k) : $k;
752 return $not ? $this->_query['condition'] : $this->whereAdd("1=0");
754 return $this->whereAdd("$key $not IN (". implode(',', $ar). ')', $logic );
760 * Adds a order by condition
762 * $object->orderBy(); //clears order by
763 * $object->orderBy("ID");
764 * $object->orderBy("ID,age");
766 * @param string $order Order
768 * @return none|PEAR::Error - invalid args only
770 function orderBy($order = false)
772 if ($this->_query === false) {
774 "You cannot do two queries on the same object (copy it before finding)",
775 DB_DATAOBJECT_ERROR_INVALIDARGS);
778 if ($order === false) {
779 $this->_query['order_by'] = '';
782 // check input...= 0 or ' ' == error!
784 return $this->raiseError("orderBy: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
787 if (!$this->_query['order_by']) {
788 $this->_query['order_by'] = " ORDER BY {$order} ";
791 $this->_query['order_by'] .= " , {$order}";
795 * Adds a group by condition
797 * $object->groupBy(); //reset the grouping
798 * $object->groupBy("ID DESC");
799 * $object->groupBy("ID,age");
801 * @param string $group Grouping
803 * @return none|PEAR::Error - invalid args only
805 function groupBy($group = false)
807 if ($this->_query === false) {
809 "You cannot do two queries on the same object (copy it before finding)",
810 DB_DATAOBJECT_ERROR_INVALIDARGS);
813 if ($group === false) {
814 $this->_query['group_by'] = '';
817 // check input...= 0 or ' ' == error!
819 return $this->raiseError("groupBy: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
823 if (!$this->_query['group_by']) {
824 $this->_query['group_by'] = " GROUP BY {$group} ";
827 $this->_query['group_by'] .= " , {$group}";
831 * Adds a having clause
833 * $object->having(); //reset the grouping
834 * $object->having("sum(value) > 0 ");
836 * @param string $having condition
838 * @return none|PEAR::Error - invalid args only
840 function having($having = false)
842 if ($this->_query === false) {
844 "You cannot do two queries on the same object (copy it before finding)",
845 DB_DATAOBJECT_ERROR_INVALIDARGS);
848 if ($having === false) {
849 $this->_query['having'] = '';
852 // check input...= 0 or ' ' == error!
853 if (!trim($having)) {
854 return $this->raiseError("Having: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
858 if (!$this->_query['having']) {
859 $this->_query['having'] = " HAVING {$having} ";
862 $this->_query['having'] .= " AND {$having}";
868 * $boject->limit(); // clear limit
869 * $object->limit(12);
870 * $object->limit(12,10);
872 * Note this will emit an error on databases other than mysql/postgress
873 * as there is no 'clean way' to implement it. - you should consider refering to
874 * your database manual to decide how you want to implement it.
876 * @param string $a limit start (or number), or blank to reset
877 * @param string $b number
879 * @return none|PEAR::Error - invalid args only
881 function limit($a = null, $b = null)
883 if ($this->_query === false) {
885 "You cannot do two queries on the same object (copy it before finding)",
886 DB_DATAOBJECT_ERROR_INVALIDARGS);
891 $this->_query['limit_start'] = '';
892 $this->_query['limit_count'] = '';
895 // check input...= 0 or ' ' == error!
896 if ((!is_int($a) && ((string)((int)$a) !== (string)$a))
897 || (($b !== null) && (!is_int($b) && ((string)((int)$b) !== (string)$b)))) {
898 return $this->raiseError("limit: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
900 global $_DB_DATAOBJECT;
902 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
904 $this->_query['limit_start'] = ($b == null) ? 0 : (int)$a;
905 $this->_query['limit_count'] = ($b == null) ? (int)$a : (int)$b;
910 * Adds a select columns
912 * $object->selectAdd(); // resets select to nothing!
913 * $object->selectAdd("*"); // default select
914 * $object->selectAdd("unixtime(DATE) as udate");
915 * $object->selectAdd("DATE");
917 * to prepend distict:
918 * $object->selectAdd('distinct ' . $object->selectAdd());
922 * @return mixed null or old string if you reset it.
924 function selectAdd($k = null)
926 if ($this->_query === false) {
928 "You cannot do two queries on the same object (copy it before finding)",
929 DB_DATAOBJECT_ERROR_INVALIDARGS);
933 $old = $this->_query['data_select'];
934 $this->_query['data_select'] = '';
938 // check input...= 0 or ' ' == error!
940 return $this->raiseError("selectAdd: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
943 if ($this->_query['data_select']) {
944 $this->_query['data_select'] .= ', ';
946 $this->_query['data_select'] .= " $k ";
949 * Adds multiple Columns or objects to select with formating.
951 * $object->selectAs(null); // adds "table.colnameA as colnameA,table.colnameB as colnameB,......"
952 * // note with null it will also clear the '*' default select
953 * $object->selectAs(array('a','b'),'%s_x'); // adds "a as a_x, b as b_x"
954 * $object->selectAs(array('a','b'),'ddd_%s','ccc'); // adds "ccc.a as ddd_a, ccc.b as ddd_b"
955 * $object->selectAdd($object,'prefix_%s'); // calls $object->get_table and adds it all as
956 * objectTableName.colnameA as prefix_colnameA
958 * @param array|object|null the array or object to take column names from.
959 * @param string format in sprintf format (use %s for the colname)
960 * @param string table name eg. if you have joinAdd'd or send $from as an array.
964 function selectAs($from = null,$format = '%s',$tableName=false)
966 global $_DB_DATAOBJECT;
968 if ($this->_query === false) {
970 "You cannot do two queries on the same object (copy it before finding)",
971 DB_DATAOBJECT_ERROR_INVALIDARGS);
975 if ($from === null) {
982 $table = $this->tableName();
983 if (is_object($from)) {
984 $table = $from->tableName();
985 $from = array_keys($from->table());
988 if ($tableName !== false) {
992 if (!empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers'])) {
994 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
995 $s = $DB->quoteIdentifier($s);
996 $format = $DB->quoteIdentifier($format);
998 foreach ($from as $k) {
999 $this->selectAdd(sprintf("{$s}.{$s} as {$format}",$table,$k,$k));
1001 $this->_query['data_select'] .= "\n";
1004 * Insert the current objects variables into the database
1006 * Returns the ID of the inserted element (if auto increment or sequences are used.)
1010 * Designed to be extended
1012 * $object = new mytable();
1013 * $object->name = "fred";
1014 * echo $object->insert();
1017 * @return mixed false on failure, int when auto increment or sequence used, otherwise true on success
1021 global $_DB_DATAOBJECT;
1023 // we need to write to the connection (For nextid) - so us the real
1024 // one not, a copyied on (as ret-by-ref fails with overload!)
1026 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
1030 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1032 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1034 $items = $this->table();
1037 $this->raiseError("insert:No table definition for {$this->tableName()}",
1038 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
1041 $options = $_DB_DATAOBJECT['CONFIG'];
1048 $seqKeys = isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()]) ?
1049 $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] :
1050 $this->sequenceKey();
1052 $key = isset($seqKeys[0]) ? $seqKeys[0] : false;
1053 $useNative = isset($seqKeys[1]) ? $seqKeys[1] : false;
1054 $seq = isset($seqKeys[2]) ? $seqKeys[2] : false;
1056 $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn["phptype"];
1059 // nativeSequences or Sequences..
1061 // big check for using sequences
1063 if (($key !== false) && !$useNative) {
1066 $keyvalue = $DB->nextId($this->tableName());
1068 $f = $DB->getOption('seqname_format');
1069 $DB->setOption('seqname_format','%s');
1070 $keyvalue = $DB->nextId($seq);
1071 $DB->setOption('seqname_format',$f);
1073 if (PEAR::isError($keyvalue)) {
1074 $this->raiseError($keyvalue->toString(), DB_DATAOBJECT_ERROR_INVALIDCONFIG);
1077 $this->$key = $keyvalue;
1080 // if we haven't set disable_null_strings to "full"
1081 $ignore_null = !isset($options['disable_null_strings'])
1082 || !is_string($options['disable_null_strings'])
1083 || strtolower($options['disable_null_strings']) !== 'full' ;
1086 foreach($items as $k => $v) {
1088 // if we are using autoincrement - skip the column...
1089 if ($key && ($k == $key) && $useNative) {
1096 // Ignore variables which aren't set to a value
1097 if ( (!isset($this->$k) || ($v == 1 && $this->$k == ''))
1102 // dont insert data into mysql timestamps
1103 // use query() if you really want to do this!!!!
1104 if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
1113 $leftq .= ($quoteIdentifiers ? ($DB->quoteIdentifier($k) . ' ') : "$k ");
1115 if (is_object($this->$k) && is_a($this->$k,'DB_DataObject_Cast')) {
1116 $value = $this->$k->toString($v,$DB);
1117 if (PEAR::isError($value)) {
1118 $this->raiseError($value->toString() ,DB_DATAOBJECT_ERROR_INVALIDARGS);
1126 if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this,$k)) {
1127 $rightq .= " NULL ";
1130 // DATE is empty... on a col. that can be null..
1131 // note: this may be usefull for time as well..
1133 (($v & DB_DATAOBJECT_DATE) || ($v & DB_DATAOBJECT_TIME)) &&
1134 !($v & DB_DATAOBJECT_NOTNULL)) {
1136 $rightq .= " NULL ";
1141 if ($v & DB_DATAOBJECT_STR) {
1142 $rightq .= $this->_quote((string) (
1143 ($v & DB_DATAOBJECT_BOOL) ?
1144 // this is thanks to the braindead idea of postgres to
1145 // use t/f for boolean.
1146 (($this->$k === 'f') ? 0 : (int)(bool) $this->$k) :
1151 if (is_numeric($this->$k)) {
1152 $rightq .=" {$this->$k} ";
1155 /* flag up string values - only at debug level... !!!??? */
1156 if (is_object($this->$k) || is_array($this->$k)) {
1157 $this->debug('ODD DATA: ' .$k . ' ' . print_r($this->$k,true),'ERROR');
1160 // at present we only cast to integers
1161 // - V2 may store additional data about float/int
1162 $rightq .= ' ' . intval($this->$k) . ' ';
1166 // not sure why we let empty insert here.. - I guess to generate a blank row..
1169 if ($leftq || $useNative) {
1170 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
1173 if (($dbtype == 'pgsql') && empty($leftq)) {
1174 $r = $this->_query("INSERT INTO {$table} DEFAULT VALUES");
1176 $r = $this->_query("INSERT INTO {$table} ($leftq) VALUES ($rightq) ");
1182 if (PEAR::isError($r)) {
1183 $this->raiseError($r);
1192 // now do we have an integer key!
1194 if ($key && $useNative) {
1198 $method = "{$dbtype}_insert_id";
1199 $this->$key = $method(
1200 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->connection
1205 // note this is not really thread safe - you should wrapp it with
1206 // transactions = eg.
1207 // $db->query('BEGIN');
1209 // $db->query('COMMIT');
1210 $db_driver = empty($options['db_driver']) ? 'DB' : $options['db_driver'];
1211 $method = ($db_driver == 'DB') ? 'getOne' : 'queryOne';
1212 $mssql_key = $DB->$method("SELECT @@IDENTITY");
1213 if (PEAR::isError($mssql_key)) {
1214 $this->raiseError($mssql_key);
1217 $this->$key = $mssql_key;
1222 $seq = $DB->getSequenceName(strtolower($this->tableName()));
1224 $db_driver = empty($options['db_driver']) ? 'DB' : $options['db_driver'];
1225 $method = ($db_driver == 'DB') ? 'getOne' : 'queryOne';
1226 $pgsql_key = $DB->$method("SELECT currval('".$seq . "')");
1229 if (PEAR::isError($pgsql_key)) {
1230 $this->raiseError($pgsql_key);
1233 $this->$key = $pgsql_key;
1237 $this->$key = array_shift (
1240 "select DBINFO('sqlca.sqlerrd1') FROM systables where tabid=1",
1241 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->connection,
1253 if (isset($_DB_DATAOBJECT['CACHE'][strtolower(get_class($this))])) {
1254 $this->_clear_cache();
1261 $this->raiseError("insert: No Data specifed for query", DB_DATAOBJECT_ERROR_NODATA);
1266 * Updates current objects variables into the database
1267 * uses the keys() to decide how to update
1268 * Returns the true on success
1272 * $object = DB_DataObject::factory('mytable');
1273 * $object->get("ID",234);
1274 * $object->email="testing@test.com";
1275 * if(!$object->update())
1276 * echo "UPDATE FAILED";
1278 * to only update changed items :
1279 * $dataobject->get(132);
1280 * $original = $dataobject; // clone/copy it..
1281 * $dataobject->setFrom($_POST);
1282 * if ($dataobject->validate()) {
1283 * $dataobject->update($original);
1284 * } // otherwise an error...
1286 * performing global updates:
1287 * $object = DB_DataObject::factory('mytable');
1288 * $object->status = "dead";
1289 * $object->whereAdd('age > 150');
1290 * $object->update(DB_DATAOBJECT_WHEREADD_ONLY);
1292 * @param object dataobject (optional) | DB_DATAOBJECT_WHEREADD_ONLY - used to only update changed items.
1294 * @return int rows affected or false on failure
1296 function update($dataObject = false)
1298 global $_DB_DATAOBJECT;
1299 // connect will load the config!
1303 $original_query = $this->_query;
1305 $items = $this->table();
1307 // only apply update against sequence key if it is set?????
1309 $seq = $this->sequenceKey();
1310 if ($seq[0] !== false) {
1311 $keys = array($seq[0]);
1312 if (!isset($this->{$keys[0]}) && $dataObject !== true) {
1313 $this->raiseError("update: trying to perform an update without
1314 the key set, and argument to update is not
1315 DB_DATAOBJECT_WHEREADD_ONLY
1316 ". print_r(array('seq' => $seq , 'keys'=>$keys), true), DB_DATAOBJECT_ERROR_INVALIDARGS);
1320 $keys = $this->keys();
1325 $this->raiseError("update:No table definition for {$this->tableName()}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
1332 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1333 $dbtype = $DB->dsn["phptype"];
1334 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1335 $options = $_DB_DATAOBJECT['CONFIG'];
1338 $ignore_null = !isset($options['disable_null_strings'])
1339 || !is_string($options['disable_null_strings'])
1340 || strtolower($options['disable_null_strings']) !== 'full' ;
1343 foreach($items as $k => $v) {
1345 if ((!isset($this->$k) || ($v == 1 && $this->$k == ''))
1350 // ignore stuff thats
1352 // dont write things that havent changed..
1353 if (($dataObject !== false) && isset($dataObject->$k) && ($dataObject->$k === $this->$k)) {
1357 // - dont write keys to left.!!!
1358 if (in_array($k,$keys)) {
1362 // dont insert data into mysql timestamps
1363 // use query() if you really want to do this!!!!
1364 if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
1373 $kSql = ($quoteIdentifiers ? $DB->quoteIdentifier($k) : $k);
1375 if (is_object($this->$k) && is_a($this->$k,'DB_DataObject_Cast')) {
1376 $value = $this->$k->toString($v,$DB);
1377 if (PEAR::isError($value)) {
1378 $this->raiseError($value->getMessage() ,DB_DATAOBJECT_ERROR_INVALIDARG);
1381 $settings .= "$kSql = $value ";
1385 // special values ... at least null is handled...
1386 if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this,$k)) {
1387 $settings .= "$kSql = NULL ";
1390 // DATE is empty... on a col. that can be null..
1391 // note: this may be usefull for time as well..
1393 (($v & DB_DATAOBJECT_DATE) || ($v & DB_DATAOBJECT_TIME)) &&
1394 !($v & DB_DATAOBJECT_NOTNULL)) {
1396 $settings .= "$kSql = NULL ";
1401 if ($v & DB_DATAOBJECT_STR) {
1402 $settings .= "$kSql = ". $this->_quote((string) (
1403 ($v & DB_DATAOBJECT_BOOL) ?
1404 // this is thanks to the braindead idea of postgres to
1405 // use t/f for boolean.
1406 (($this->$k === 'f') ? 0 : (int)(bool) $this->$k) :
1411 if (is_numeric($this->$k)) {
1412 $settings .= "$kSql = {$this->$k} ";
1415 // at present we only cast to integers
1416 // - V2 may store additional data about float/int
1417 $settings .= "$kSql = " . intval($this->$k) . ' ';
1421 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1422 $this->debug("got keys as ".serialize($keys),3);
1424 if ($dataObject !== true) {
1425 $this->_build_condition($items,$keys);
1427 // prevent wiping out of data!
1428 if (empty($this->_query['condition'])) {
1429 $this->raiseError("update: global table update not available
1430 do \$do->whereAdd('1=1'); if you really want to do that.
1431 ", DB_DATAOBJECT_ERROR_INVALIDARGS);
1438 // echo " $settings, $this->condition ";
1439 if ($settings && isset($this->_query) && $this->_query['condition']) {
1441 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
1443 $r = $this->_query("UPDATE {$table} SET {$settings} {$this->_query['condition']} ");
1445 // restore original query conditions.
1446 $this->_query = $original_query;
1448 if (PEAR::isError($r)) {
1449 $this->raiseError($r);
1456 $this->_clear_cache();
1459 // restore original query conditions.
1460 $this->_query = $original_query;
1462 // if you manually specified a dataobject, and there where no changes - then it's ok..
1463 if ($dataObject !== false) {
1468 "update: No Data specifed for query $settings , {$this->_query['condition']}",
1469 DB_DATAOBJECT_ERROR_NODATA);
1474 * Deletes items from table which match current objects variables
1476 * Returns the true on success
1480 * Designed to be extended
1482 * $object = new mytable();
1484 * echo $object->delete(); // builds a conditon
1486 * $object = new mytable();
1487 * $object->whereAdd('age > 12');
1488 * $object->limit(1);
1489 * $object->orderBy('age DESC');
1490 * $object->delete(true); // dont use object vars, use the conditions, limit and order.
1492 * @param bool $useWhere (optional) If DB_DATAOBJECT_WHEREADD_ONLY is passed in then
1493 * we will build the condition only using the whereAdd's. Default is to
1494 * build the condition only using the object parameters.
1497 * @return mixed Int (No. of rows affected) on success, false on failure, 0 on no data affected
1499 function delete($useWhere = false)
1501 global $_DB_DATAOBJECT;
1502 // connect will load the config!
1504 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1505 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1507 $extra_cond = ' ' . (isset($this->_query['order_by']) ? $this->_query['order_by'] : '');
1511 $keys = $this->keys();
1512 $this->_query = array(); // as it's probably unset!
1513 $this->_query['condition'] = ''; // default behaviour not to use where condition
1514 $this->_build_condition($this->table(),$keys);
1515 // if primary keys are not set then use data from rest of object.
1516 if (!$this->_query['condition']) {
1517 $this->_build_condition($this->table(),array(),$keys);
1523 // don't delete without a condition
1524 if (($this->_query !== false) && $this->_query['condition']) {
1526 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
1528 // using a joined delete. - with useWhere..
1529 $sql .= (!empty($this->_join) && $useWhere) ?
1530 "{$table} FROM {$table} {$this->_join} " :
1533 $sql .= $this->_query['condition']. $extra_cond;
1537 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
1539 if (!isset($_DB_DATAOBJECT['CONFIG']['db_driver']) ||
1540 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB')) {
1542 $sql = $DB->modifyLimitQuery($sql,$this->_query['limit_start'], $this->_query['limit_count']);
1546 $DB->setLimit( $this->_query['limit_count'],$this->_query['limit_start']);
1552 $r = $this->_query($sql);
1555 if (PEAR::isError($r)) {
1556 $this->raiseError($r);
1562 $this->_clear_cache();
1565 $this->raiseError("delete: No condition specifed for query", DB_DATAOBJECT_ERROR_NODATA);
1571 * fetches a specific row into this object variables
1573 * Not recommended - better to use fetch()
1575 * Returens true on success
1577 * @param int $row row
1579 * @return boolean true on success
1581 function fetchRow($row = null)
1583 global $_DB_DATAOBJECT;
1584 if (empty($_DB_DATAOBJECT['CONFIG'])) {
1585 $this->_loadConfig();
1587 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1588 $this->debug("{$this->tableName()} $row of {$this->N}", "fetchrow",3);
1590 if (!$this->tableName()) {
1591 $this->raiseError("fetchrow: No table", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
1594 if ($row === null) {
1595 $this->raiseError("fetchrow: No row specified", DB_DATAOBJECT_ERROR_INVALIDARGS);
1599 $this->raiseError("fetchrow: No results avaiable", DB_DATAOBJECT_ERROR_NODATA);
1602 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1603 $this->debug("{$this->tableName()} $row of {$this->N}", "fetchrow",3);
1607 $result = $_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid];
1608 $array = $result->fetchrow(DB_DATAOBJECT_FETCHMODE_ASSOC,$row);
1609 if (!is_array($array)) {
1610 $this->raiseError("fetchrow: No results available", DB_DATAOBJECT_ERROR_NODATA);
1613 $replace = array('.', ' ');
1614 foreach($array as $k => $v) {
1615 // use strpos as str_replace is slow.
1616 $kk = (strpos($k, '.') === false && strpos($k, ' ') === false) ?
1617 $k : str_replace($replace, '_', $k);
1619 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1620 $this->debug("$kk = ". $array[$k], "fetchrow LINE", 3);
1622 $this->$kk = $array[$k];
1625 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1626 $this->debug("{$this->tableName()} DONE", "fetchrow", 3);
1632 * Find the number of results from a simple query
1636 * $object = new mytable();
1637 * $object->name = "fred";
1638 * echo $object->count();
1639 * echo $object->count(true); // dont use object vars.
1640 * echo $object->count('distinct mycol'); count distinct mycol.
1641 * echo $object->count('distinct mycol',true); // dont use object vars.
1642 * echo $object->count('distinct'); // count distinct id (eg. the primary key)
1645 * @param bool|string (optional)
1646 * (true|false => see below not on whereAddonly)
1648 * "DISTINCT" => does a distinct count on the tables 'key' column
1649 * otherwise => normally it counts primary keys - you can use
1650 * this to do things like $do->count('distinct mycol');
1652 * @param bool $whereAddOnly (optional) If DB_DATAOBJECT_WHEREADD_ONLY is passed in then
1653 * we will build the condition only using the whereAdd's. Default is to
1654 * build the condition using the object parameters as well.
1659 function count($countWhat = false,$whereAddOnly = false)
1661 global $_DB_DATAOBJECT;
1663 if (is_bool($countWhat)) {
1664 $whereAddOnly = $countWhat;
1668 $items = $t->table();
1670 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1673 if (!isset($t->_query)) {
1675 "You cannot do run count after you have run fetch()",
1676 DB_DATAOBJECT_ERROR_INVALIDARGS);
1680 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1683 if (!$whereAddOnly && $items) {
1684 $t->_build_condition($items);
1686 $keys = $this->keys();
1688 if (empty($keys[0]) && (!is_string($countWhat) || (strtoupper($countWhat) == 'DISTINCT'))) {
1690 "You cannot do run count without keys - use \$do->count('id'), or use \$do->count('distinct id')';",
1691 DB_DATAOBJECT_ERROR_INVALIDARGS,PEAR_ERROR_DIE);
1695 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
1696 $key_col = empty($keys[0]) ? '' : (($quoteIdentifiers ? $DB->quoteIdentifier($keys[0]) : $keys[0]));
1697 $as = ($quoteIdentifiers ? $DB->quoteIdentifier('DATAOBJECT_NUM') : 'DATAOBJECT_NUM');
1699 // support distinct on default keys.
1700 $countWhat = (strtoupper($countWhat) == 'DISTINCT') ?
1701 "DISTINCT {$table}.{$key_col}" : $countWhat;
1703 $countWhat = is_string($countWhat) ? $countWhat : "{$table}.{$key_col}";
1706 "SELECT count({$countWhat}) as $as
1707 FROM $table {$t->_join} {$t->_query['condition']}");
1708 if (PEAR::isError($r)) {
1712 $result = $_DB_DATAOBJECT['RESULTS'][$t->_DB_resultid];
1713 $l = $result->fetchRow(DB_DATAOBJECT_FETCHMODE_ORDERED);
1714 // free the results - essential on oracle.
1716 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1717 $this->debug('Count returned '. $l[0] ,1);
1723 * sends raw query to database
1725 * Since _query has to be a private 'non overwriteable method', this is a relay
1727 * @param string $string SQL Query
1729 * @return void or DB_Error
1731 function query($string)
1733 return $this->_query($string);
1738 * an escape wrapper around DB->escapeSimple()
1739 * can be used when adding manual queries or clauses
1741 * $object->query("select * from xyz where abc like '". $object->escape($_GET['name']) . "'");
1743 * @param string $string value to be escaped
1744 * @param bool $likeEscape escapes % and _ as well. - so like queries can be protected.
1748 function escape($string, $likeEscape=false)
1750 global $_DB_DATAOBJECT;
1752 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1753 // mdb2 uses escape...
1754 $dd = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ? 'DB' : $_DB_DATAOBJECT['CONFIG']['db_driver'];
1755 $ret = ($dd == 'DB') ? $DB->escapeSimple($string) : $DB->escape($string);
1757 $ret = str_replace(array('_','%'), array('\_','\%'), $ret);
1763 /* ==================================================== */
1764 /* Major Private Vars */
1765 /* ==================================================== */
1768 * The Database connection dsn (as described in the PEAR DB)
1769 * only used really if you are writing a very simple application/test..
1770 * try not to use this - it is better stored in configuration files..
1775 var $_database_dsn = '';
1778 * The Database connection id (md5 sum of databasedsn)
1783 var $_database_dsn_md5 = '';
1787 * created in __connection
1792 var $_database = '';
1798 * This replaces alot of the private variables
1799 * used to build a query, it is unset after find() is run.
1806 var $_query = array(
1807 'condition' => '', // the WHERE condition
1808 'group_by' => '', // the GROUP BY condition
1809 'order_by' => '', // the ORDER BY condition
1810 'having' => '', // the HAVING condition
1811 'limit_start' => '', // the LIMIT condition
1812 'limit_count' => '', // the LIMIT condition
1813 'data_select' => '*', // the columns to be SELECTed
1814 'unions' => array(), // the added unions,
1815 'derive_table' => '', // derived table name (BETA)
1816 'derive_select' => '', // derived table select (BETA)
1823 * Database result id (references global $_DB_DataObject[results]
1831 * ResultFields - on the last call to fetch(), resultfields is sent here,
1832 * so we can clean up the memory.
1837 var $_resultFields = false;
1840 /* ============================================================== */
1841 /* Table definition layer (started of very private but 'came out'*/
1842 /* ============================================================== */
1845 * Autoload or manually load the table definitions
1849 * DB_DataObject::databaseStructure( 'databasename',
1850 * parse_ini_file('mydb.ini',true),
1851 * parse_ini_file('mydb.link.ini',true));
1853 * obviously you dont have to use ini files.. (just return array similar to ini files..)
1855 * It should append to the table structure array
1858 * @param optional string name of database to assign / read
1859 * @param optional array structure of database, and keys
1860 * @param optional array table links
1863 * @return true or PEAR:error on wrong paramenters.. or false if no file exists..
1864 * or the array(tablename => array(column_name=>type)) if called with 1 argument.. (databasename)
1866 function databaseStructure()
1869 global $_DB_DATAOBJECT;
1873 if ($args = func_get_args()) {
1875 if (count($args) == 1) {
1877 // this returns all the tables and their structure..
1878 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1879 $this->debug("Loading Generator as databaseStructure called with args",1);
1882 $x = new DB_DataObject;
1883 $x->_database = $args[0];
1885 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1887 $tables = $DB->getListOf('tables');
1888 class_exists('DB_DataObject_Generator') ? '' :
1889 require_once 'DB/DataObject/Generator.php';
1891 foreach($tables as $table) {
1892 $y = new DB_DataObject_Generator;
1893 $y->fillTableSchema($x->_database,$table);
1895 return $_DB_DATAOBJECT['INI'][$x->_database];
1898 $_DB_DATAOBJECT['INI'][$args[0]] = isset($_DB_DATAOBJECT['INI'][$args[0]]) ?
1899 $_DB_DATAOBJECT['INI'][$args[0]] + $args[1] : $args[1];
1901 if (isset($args[1])) {
1902 $_DB_DATAOBJECT['LINKS'][$args[0]] = isset($_DB_DATAOBJECT['LINKS'][$args[0]]) ?
1903 $_DB_DATAOBJECT['LINKS'][$args[0]] + $args[2] : $args[2];
1912 if (!$this->_database) {
1917 // if this table is already loaded this table..
1918 if (!empty($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()])) {
1922 // initialize the ini data.. if empt..
1923 if (empty($_DB_DATAOBJECT['INI'][$this->_database])) {
1924 $_DB_DATAOBJECT['INI'][$this->_database] = array();
1927 if (empty($_DB_DATAOBJECT['CONFIG'])) {
1928 DB_DataObject::_loadConfig();
1931 // we do not have the data for this table yet...
1933 // if we are configured to use the proxy..
1935 if ( !empty($_DB_DATAOBJECT['CONFIG']['proxy']) ) {
1936 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1937 $this->debug("Loading Generator to fetch Schema",1);
1939 class_exists('DB_DataObject_Generator') ? '' :
1940 require_once 'DB/DataObject/Generator.php';
1943 $x = new DB_DataObject_Generator;
1944 $x->fillTableSchema($this->_database,$this->tableName());
1951 // if you supply this with arguments, then it will take those
1952 // as the database and links array...
1954 $schemas = isset($_DB_DATAOBJECT['CONFIG']['schema_location']) ?
1955 array("{$_DB_DATAOBJECT['CONFIG']['schema_location']}/{$this->_database}.ini") :
1958 if (isset($_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"])) {
1959 $schemas = is_array($_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"]) ?
1960 $_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"] :
1961 explode(PATH_SEPARATOR,$_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"]);
1965 $_DB_DATAOBJECT['INI'][$this->_database] = array();
1966 foreach ($schemas as $ini) {
1967 if (file_exists($ini) && is_file($ini)) {
1969 $_DB_DATAOBJECT['INI'][$this->_database] = array_merge(
1970 $_DB_DATAOBJECT['INI'][$this->_database],
1971 parse_ini_file($ini, true)
1974 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1975 if (!is_readable ($ini)) {
1976 $this->debug("ini file is not readable: $ini","databaseStructure",1);
1978 $this->debug("Loaded ini file: $ini","databaseStructure",1);
1982 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1983 $this->debug("Missing ini file: $ini","databaseStructure",1);
1988 // are table name lowecased..
1989 if (!empty($_DB_DATAOBJECT['CONFIG']['portability']) && $_DB_DATAOBJECT['CONFIG']['portability'] & 1) {
1990 foreach($_DB_DATAOBJECT['INI'][$this->_database] as $k=>$v) {
1991 // results in duplicate cols.. but not a big issue..
1992 $_DB_DATAOBJECT['INI'][$this->_database][strtolower($k)] = $v;
1997 // now have we loaded the structure..
1999 if (!empty($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()])) {
2002 // - if not try building it..
2003 if (!empty($_DB_DATAOBJECT['CONFIG']['proxy'])) {
2004 class_exists('DB_DataObject_Generator') ? '' :
2005 require_once 'DB/DataObject/Generator.php';
2007 $x = new DB_DataObject_Generator;
2008 $x->fillTableSchema($this->_database,$this->tableName());
2009 // should this fail!!!???
2012 $this->debug("Cant find database schema: {$this->_database}/{$this->tableName()} \n".
2013 "in links file data: " . print_r($_DB_DATAOBJECT['INI'],true),"databaseStructure",5);
2014 // we have to die here!! - it causes chaos if we dont (including looping forever!)
2015 $this->raiseError( "Unable to load schema for database and table (turn debugging up to 5 for full error message)", DB_DATAOBJECT_ERROR_INVALIDARGS, PEAR_ERROR_DIE);
2025 * Return or assign the name of the current table
2028 * @param string optinal table name to set
2030 * @return string The name of the current table
2032 function tableName()
2034 global $_DB_DATAOBJECT;
2035 $args = func_get_args();
2037 $this->__table = $args[0];
2039 if (!empty($_DB_DATAOBJECT['CONFIG']['portability']) && $_DB_DATAOBJECT['CONFIG']['portability'] & 1) {
2040 return strtolower($this->__table);
2042 return $this->__table;
2046 * Return or assign the name of the current database
2048 * @param string optional database name to set
2050 * @return string The name of the current database
2054 $args = func_get_args();
2056 $this->_database = $args[0];
2061 return $this->_database;
2065 * get/set an associative array of table columns
2068 * @param array key=>type array
2069 * @return array (associative)
2074 // for temporary storage of database fields..
2075 // note this is not declared as we dont want to bloat the print_r output
2076 $args = func_get_args();
2078 $this->_database_fields = $args[0];
2080 if (isset($this->_database_fields)) {
2081 return $this->_database_fields;
2085 global $_DB_DATAOBJECT;
2086 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2090 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()])) {
2091 return $_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()];
2094 $this->databaseStructure();
2098 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()])) {
2099 $ret = $_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()];
2106 * get/set an array of table primary keys
2108 * set usage: $do->keys('id','code');
2110 * This is defined in the table definition if it gets it wrong,
2111 * or you do not want to use ini tables, you can override this.
2112 * @param string optional set the key
2113 * @param * optional set more keys
2119 // for temporary storage of database fields..
2120 // note this is not declared as we dont want to bloat the print_r output
2121 $args = func_get_args();
2123 $this->_database_keys = $args;
2125 if (isset($this->_database_keys)) {
2126 return $this->_database_keys;
2129 global $_DB_DATAOBJECT;
2130 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2133 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()."__keys"])) {
2134 return array_keys($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()."__keys"]);
2136 $this->databaseStructure();
2138 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()."__keys"])) {
2139 return array_keys($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()."__keys"]);
2144 * get/set an sequence key
2146 * by default it returns the first key from keys()
2147 * set usage: $do->sequenceKey('id',true);
2149 * override this to return array(false,false) if table has no real sequence key.
2151 * @param string optional the key sequence/autoinc. key
2152 * @param boolean optional use native increment. default false
2153 * @param false|string optional native sequence name
2155 * @return array (column,use_native,sequence_name)
2157 function sequenceKey()
2159 global $_DB_DATAOBJECT;
2162 if (!$this->_database) {
2166 if (!isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database])) {
2167 $_DB_DATAOBJECT['SEQUENCE'][$this->_database] = array();
2171 $args = func_get_args();
2173 $args[1] = isset($args[1]) ? $args[1] : false;
2174 $args[2] = isset($args[2]) ? $args[2] : false;
2175 $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = $args;
2177 if (isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()])) {
2178 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()];
2180 // end call setting (eg. $do->sequenceKeys(a,b,c); )
2185 $keys = $this->keys();
2187 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()]
2188 = array(false,false,false);
2192 $table = $this->table();
2194 $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'];
2202 if (!empty($_DB_DATAOBJECT['CONFIG']['sequence_'.$this->tableName()])) {
2203 $seqname = $_DB_DATAOBJECT['CONFIG']['sequence_'.$this->tableName()];
2204 if (strpos($seqname,':') !== false) {
2205 list($usekey,$seqname) = explode(':',$seqname);
2210 // if the key is not an integer - then it's not a sequence or native
2211 if (empty($table[$usekey]) || !($table[$usekey] & DB_DATAOBJECT_INT)) {
2212 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array(false,false,false);
2216 if (!empty($_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'])) {
2217 $ignore = $_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'];
2218 if (is_string($ignore) && (strtoupper($ignore) == 'ALL')) {
2219 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array(false,false,$seqname);
2221 if (is_string($ignore)) {
2222 $ignore = $_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'] = explode(',',$ignore);
2224 if (in_array($this->tableName(),$ignore)) {
2225 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array(false,false,$seqname);
2230 $realkeys = $_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()."__keys"];
2232 // if you are using an old ini file - go back to old behaviour...
2233 if (is_numeric($realkeys[$usekey])) {
2234 $realkeys[$usekey] = 'N';
2237 // multiple unique primary keys without a native sequence...
2238 if (($realkeys[$usekey] == 'K') && (count($keys) > 1)) {
2239 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array(false,false,$seqname);
2241 // use native sequence keys...
2242 // technically postgres native here...
2243 // we need to get the new improved tabledata sorted out first.
2245 // support named sequence keys.. - currently postgres only..
2247 if ( in_array($dbtype , array('pgsql')) &&
2248 ($table[$usekey] & DB_DATAOBJECT_INT) &&
2249 isset($realkeys[$usekey]) && strlen($realkeys[$usekey]) > 1) {
2250 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array($usekey,true, $realkeys[$usekey]);
2253 if ( in_array($dbtype , array('pgsql', 'mysql', 'mysqli', 'mssql', 'ifx')) &&
2254 ($table[$usekey] & DB_DATAOBJECT_INT) &&
2255 isset($realkeys[$usekey]) && ($realkeys[$usekey] == 'N')
2257 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array($usekey,true,$seqname);
2261 // if not a native autoinc, and we have not assumed all primary keys are sequence
2262 if (($realkeys[$usekey] != 'N') &&
2263 !empty($_DB_DATAOBJECT['CONFIG']['dont_use_pear_sequences'])) {
2264 return array(false,false,false);
2269 // I assume it's going to try and be a nextval DB sequence.. (not native)
2270 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array($usekey,false,$seqname);
2275 /* =========================================================== */
2276 /* Major Private Methods - the core part! */
2277 /* =========================================================== */
2282 * clear the cache values for this class - normally done on insert/update etc.
2287 function _clear_cache()
2289 global $_DB_DATAOBJECT;
2291 $class = strtolower(get_class($this));
2293 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2294 $this->debug("Clearing Cache for ".$class,1);
2297 if (!empty($_DB_DATAOBJECT['CACHE'][$class])) {
2298 unset($_DB_DATAOBJECT['CACHE'][$class]);
2304 * backend wrapper for quoting, as MDB2 and DB do it differently...
2307 * @return string quoted
2310 function _quote($str)
2312 global $_DB_DATAOBJECT;
2313 return (empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ||
2314 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB'))
2315 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->quoteSmart($str)
2316 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->quote($str);
2321 * connects to the database
2324 * TODO: tidy this up - This has grown to support a number of connection options like
2325 * a) dynamic changing of ini file to change which database to connect to
2326 * b) multi data via the table_{$table} = dsn ini option
2327 * c) session based storage.
2330 * @return true | PEAR::error
2334 global $_DB_DATAOBJECT;
2335 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2336 $this->_loadConfig();
2338 // Set database driver for reference
2339 $db_driver = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ?
2340 'DB' : $_DB_DATAOBJECT['CONFIG']['db_driver'];
2342 // is it already connected ?
2343 if ($this->_database_dsn_md5 && !empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2345 // connection is an error...
2346 if (PEAR::isError($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2347 return $this->raiseError(
2348 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->message,
2349 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->code, PEAR_ERROR_DIE
2354 if (empty($this->_database)) {
2355 $this->_database = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
2356 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
2358 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
2359 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
2360 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
2364 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
2365 && is_file($this->_database)) {
2366 $this->_database = basename($this->_database);
2368 if ($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'ibase') {
2369 $this->_database = substr(basename($this->_database), 0, -4);
2373 // theoretically we have a md5, it's listed in connections and it's not an error.
2374 // so everything is ok!
2379 // it's not currently connected!
2380 // try and work out what to use for the dsn !
2382 $options= $_DB_DATAOBJECT['CONFIG'];
2383 // if the databse dsn dis defined in the object..
2384 $dsn = isset($this->_database_dsn) ? $this->_database_dsn : null;
2387 if (!$this->_database && !empty($this->__table)) {
2388 $this->_database = isset($options["table_{$this->tableName()}"]) ? $options["table_{$this->tableName()}"] : null;
2390 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2391 $this->debug("Checking for database specific ini ('{$this->_database}') : database_{$this->_database} in options","CONNECT");
2394 if ($this->_database && !empty($options["database_{$this->_database}"])) {
2395 $dsn = $options["database_{$this->_database}"];
2396 } else if (!empty($options['database'])) {
2397 $dsn = $options['database'];
2402 // if still no database...
2404 return $this->raiseError(
2405 "No database name / dsn found anywhere",
2406 DB_DATAOBJECT_ERROR_INVALIDCONFIG, PEAR_ERROR_DIE
2412 if (is_string($dsn)) {
2413 $this->_database_dsn_md5 = md5($dsn);
2415 /// support array based dsn's
2416 $this->_database_dsn_md5 = md5(serialize($dsn));
2419 if (!empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2420 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2421 $this->debug("USING CACHED CONNECTION", "CONNECT",3);
2426 if (!$this->_database) {
2428 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
2429 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
2430 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
2431 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
2433 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
2434 && is_file($this->_database))
2436 $this->_database = basename($this->_database);
2441 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2442 $this->debug("NEW CONNECTION TP DATABASE :" .$this->_database , "CONNECT",3);
2443 /* actualy make a connection */
2444 $this->debug(print_r($dsn,true) ." {$this->_database_dsn_md5}", "CONNECT",3);
2447 // Note this is verbose deliberatly!
2449 if ($db_driver == 'DB') {
2451 /* PEAR DB connect */
2453 // this allows the setings of compatibility on DB
2454 $db_options = PEAR::getStaticProperty('DB','options');
2455 require_once 'DB.php';
2457 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = DB::connect($dsn,$db_options);
2459 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = DB::connect($dsn);
2463 /* assumption is MDB2 */
2464 require_once 'MDB2.php';
2465 // this allows the setings of compatibility on MDB2
2466 $db_options = PEAR::getStaticProperty('MDB2','options');
2467 $db_options = is_array($db_options) ? $db_options : array();
2468 $db_options['portability'] = isset($db_options['portability'] )
2469 ? $db_options['portability'] : MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_FIX_CASE;
2470 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = MDB2::connect($dsn,$db_options);
2475 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2476 $this->debug(print_r($_DB_DATAOBJECT['CONNECTIONS'],true), "CONNECT",5);
2478 if (PEAR::isError($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2479 $this->debug($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->toString(), "CONNECT FAILED",5);
2480 return $this->raiseError(
2481 "Connect failed, turn on debugging to 5 see why",
2482 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->code, PEAR_ERROR_DIE
2487 if (empty($this->_database)) {
2488 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
2490 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
2491 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
2492 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
2495 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
2496 && is_file($this->_database))
2498 $this->_database = basename($this->_database);
2502 // Oracle need to optimize for portibility - not sure exactly what this does though :)
2508 * sends query to database - this is the private one that must work
2509 * - internal functions use this rather than $this->query()
2511 * @param string $string
2513 * @return mixed none or PEAR_Error
2515 function _query($string)
2517 global $_DB_DATAOBJECT;
2521 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2523 $options = $_DB_DATAOBJECT['CONFIG'];
2525 $_DB_driver = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ?
2526 'DB': $_DB_DATAOBJECT['CONFIG']['db_driver'];
2528 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2529 $this->debug($string,$log="QUERY");
2534 strtoupper($string) == 'BEGIN' ||
2535 strtoupper($string) == 'START TRANSACTION'
2537 if ($_DB_driver == 'DB') {
2538 $DB->autoCommit(false);
2539 $DB->simpleQuery('BEGIN');
2541 $DB->beginTransaction();
2546 if (strtoupper($string) == 'COMMIT') {
2547 $res = $DB->commit();
2548 if ($_DB_driver == 'DB') {
2549 $DB->autoCommit(true);
2554 if (strtoupper($string) == 'ROLLBACK') {
2556 if ($_DB_driver == 'DB') {
2557 $DB->autoCommit(true);
2563 if (!empty($options['debug_ignore_updates']) &&
2564 (strtolower(substr(trim($string), 0, 6)) != 'select') &&
2565 (strtolower(substr(trim($string), 0, 4)) != 'show') &&
2566 (strtolower(substr(trim($string), 0, 8)) != 'describe')) {
2568 $this->debug('Disabling Update as you are in debug mode');
2569 return $this->raiseError("Disabling Update as you are in debug mode", null) ;
2572 //if (@$_DB_DATAOBJECT['CONFIG']['debug'] > 1) {
2573 // this will only work when PEAR:DB supports it.
2574 //$this->debug($DB->getAll('explain ' .$string,DB_DATAOBJECT_FETCHMODE_ASSOC), $log="sql",2);
2578 $t= explode(' ',microtime());
2579 $_DB_DATAOBJECT['QUERYENDTIME'] = $time = $t[0]+$t[1];
2582 for ($tries = 0;$tries < 3;$tries++) {
2584 if ($_DB_driver == 'DB') {
2586 $result = $DB->query($string);
2588 switch (strtolower(substr(trim($string),0,6))) {
2593 $result = $DB->exec($string);
2597 $result = $DB->query($string);
2602 // see if we got a failure.. - try again a few times..
2603 if (!is_object($result) || !is_a($result,'PEAR_Error')) {
2606 if ($result->getCode() != -14) { // *DB_ERROR_NODBSELECTED
2607 break; // not a connection error..
2609 sleep(1); // wait before retyring..
2610 $DB->connect($DB->dsn);
2614 if (is_object($result) && is_a($result,'PEAR_Error')) {
2615 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2616 $this->debug($result->toString(), "Query Error",1 );
2619 return $this->raiseError($result);
2621 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2622 $t= explode(' ',microtime());
2623 $_DB_DATAOBJECT['QUERYENDTIME'] = $t[0]+$t[1];
2624 $this->debug('QUERY DONE IN '.($t[0]+$t[1]-$time)." seconds", 'query',1);
2626 switch (strtolower(substr(trim($string),0,6))) {
2630 if ($_DB_driver == 'DB') {
2632 return $DB->affectedRows();
2636 if (is_object($result)) {
2637 // lets hope that copying the result object is OK!
2639 $_DB_resultid = $GLOBALS['_DB_DATAOBJECT']['RESULTSEQ']++;
2640 $_DB_DATAOBJECT['RESULTS'][$_DB_resultid] = $result;
2641 $this->_DB_resultid = $_DB_resultid;
2644 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2645 $this->debug(serialize($result), 'RESULT',5);
2647 if (method_exists($result, 'numRows')) {
2648 if ($_DB_driver == 'DB') {
2649 $DB->expectError(DB_ERROR_UNSUPPORTED);
2651 $DB->expectError(MDB2_ERROR_UNSUPPORTED);
2654 $this->N = $result->numRows();
2655 //var_dump($this->N);
2657 if (is_object($this->N) && is_a($this->N,'PEAR_Error')) {
2665 * Builds the WHERE based on the values of of this object
2667 * @param mixed $keys
2668 * @param array $filter (used by update to only uses keys in this filter list).
2669 * @param array $negative_filter (used by delete to prevent deleting using the keys mentioned..)
2673 function _build_condition($keys, $filter = array(),$negative_filter=array())
2675 global $_DB_DATAOBJECT;
2677 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2679 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
2680 $options = $_DB_DATAOBJECT['CONFIG'];
2682 // if we dont have query vars.. - reset them.
2683 if ($this->_query === false) {
2684 $x = new DB_DataObject;
2685 $this->_query= $x->_query;
2689 foreach($keys as $k => $v) {
2690 // index keys is an indexed array
2691 /* these filter checks are a bit suspicious..
2692 - need to check that update really wants to work this way */
2695 if (!in_array($k, $filter)) {
2699 if ($negative_filter) {
2700 if (in_array($k, $negative_filter)) {
2704 if (!isset($this->$k)) {
2708 $kSql = $quoteIdentifiers
2709 ? ( $DB->quoteIdentifier($this->tableName()) . '.' . $DB->quoteIdentifier($k) )
2710 : "{$this->tableName()}.{$k}";
2714 if (is_object($this->$k) && is_a($this->$k,'DB_DataObject_Cast')) {
2715 $dbtype = $DB->dsn["phptype"];
2716 $value = $this->$k->toString($v,$DB);
2717 if (PEAR::isError($value)) {
2718 $this->raiseError($value->getMessage() ,DB_DATAOBJECT_ERROR_INVALIDARG);
2721 if ((strtolower($value) === 'null') && !($v & DB_DATAOBJECT_NOTNULL)) {
2722 $this->whereAdd(" $kSql IS NULL");
2725 $this->whereAdd(" $kSql = $value");
2729 if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this,$k)) {
2730 $this->whereAdd(" $kSql IS NULL");
2735 if ($v & DB_DATAOBJECT_STR) {
2736 $this->whereAdd(" $kSql = " . $this->_quote((string) (
2737 ($v & DB_DATAOBJECT_BOOL) ?
2738 // this is thanks to the braindead idea of postgres to
2739 // use t/f for boolean.
2740 (($this->$k === 'f') ? 0 : (int)(bool) $this->$k) :
2745 if (is_numeric($this->$k)) {
2746 $this->whereAdd(" $kSql = {$this->$k}");
2749 /* this is probably an error condition! */
2750 $this->whereAdd(" $kSql = ".intval($this->$k));
2757 * classic factory method for loading a table class
2758 * usage: $do = DB_DataObject::factory('person')
2759 * WARNING - this may emit a include error if the file does not exist..
2760 * use @ to silence it (if you are sure it is acceptable)
2761 * eg. $do = @DB_DataObject::factory('person')
2763 * table name can bedatabasename/table
2764 * - and allow modular dataobjects to be written..
2765 * (this also helps proxy creation)
2767 * Experimental Support for Multi-Database factory eg. mydatabase.mytable
2770 * @param string $table tablename (use blank to create a new instance of the same class.)
2772 * @return DataObject|PEAR_Error
2777 function factory($table = '')
2779 global $_DB_DATAOBJECT;
2782 // multi-database support.. - experimental.
2785 if (strpos( $table,'/') !== false ) {
2786 list($database,$table) = explode('.',$table, 2);
2790 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2791 DB_DataObject::_loadConfig();
2793 // no configuration available for database
2794 if (!empty($database) && empty($_DB_DATAOBJECT['CONFIG']['database_'.$database])) {
2795 return DB_DataObject::raiseError(
2796 "unable to find database_{$database} in Configuration, It is required for factory with database"
2797 , 0, PEAR_ERROR_DIE );
2802 if ($table === '') {
2803 if (is_a($this,'DB_DataObject') && strlen($this->tableName())) {
2804 $table = $this->tableName();
2806 return DB_DataObject::raiseError(
2807 "factory did not recieve a table name",
2808 DB_DATAOBJECT_ERROR_INVALIDARGS);
2812 // does this need multi db support??
2813 $cp = isset($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
2814 explode(PATH_SEPARATOR, $_DB_DATAOBJECT['CONFIG']['class_prefix']) : '';
2818 // multiprefix support.
2819 $tbl = preg_replace('/[^A-Z0-9]/i','_',ucfirst($table));
2820 if (is_array($cp)) {
2822 foreach($cp as $cpr) {
2823 $ce = substr(phpversion(),0,1) > 4 ? class_exists($cpr . $tbl,false) : class_exists($cpr . $tbl);
2825 $class = $cpr . $tbl;
2828 $class[] = $cpr . $tbl;
2832 $ce = substr(phpversion(),0,1) > 4 ? class_exists($class,false) : class_exists($class);
2836 $rclass = $ce ? $class : DB_DataObject::_autoloadClass($class, $table);
2837 // proxy = full|light
2838 if (!$rclass && isset($_DB_DATAOBJECT['CONFIG']['proxy'])) {
2840 DB_DataObject::debug("FAILED TO Autoload $database.$table - using proxy.","FACTORY",1);
2843 $proxyMethod = 'getProxy'.$_DB_DATAOBJECT['CONFIG']['proxy'];
2844 // if you have loaded (some other way) - dont try and load it again..
2845 class_exists('DB_DataObject_Generator') ? '' :
2846 require_once 'DB/DataObject/Generator.php';
2848 $d = new DB_DataObject;
2850 $d->__table = $table;
2852 $ret = $d->_connect();
2853 if (is_object($ret) && is_a($ret, 'PEAR_Error')) {
2857 $x = new DB_DataObject_Generator;
2858 return $x->$proxyMethod( $d->_database, $table);
2861 if (!$rclass || !class_exists($rclass)) {
2862 return DB_DataObject::raiseError(
2863 "factory could not find class " .
2864 (is_array($class) ? implode(PATH_SEPARATOR, $class) : $class ).
2866 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2869 $ret = new $rclass();
2871 if (!empty($database)) {
2872 DB_DataObject::debug("Setting database to $database","FACTORY",1);
2873 $ret->database($database);
2880 * @param string|array $class Class
2881 * @param string $table Table trying to load.
2883 * @return string classname on Success
2885 function _autoloadClass($class, $table=false)
2887 global $_DB_DATAOBJECT;
2889 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2890 DB_DataObject::_loadConfig();
2892 $class_prefix = empty($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
2893 '' : $_DB_DATAOBJECT['CONFIG']['class_prefix'];
2895 $table = $table ? $table : substr($class,strlen($class_prefix));
2897 // only include the file if it exists - and barf badly if it has parse errors :)
2898 if (!empty($_DB_DATAOBJECT['CONFIG']['proxy']) || empty($_DB_DATAOBJECT['CONFIG']['class_location'])) {
2902 // class_location = mydir/ => maps to mydir/Tablename.php
2903 // class_location = mydir/myfile_%s.php => maps to mydir/myfile_Tablename
2904 // with directory sepr
2905 // class_location = mydir/:mydir2/: => tries all of thes locations.
2906 $cl = $_DB_DATAOBJECT['CONFIG']['class_location'];
2910 case (strpos($cl ,'%s') !== false):
2911 $file = sprintf($cl , preg_replace('/[^A-Z0-9]/i','_',ucfirst($table)));
2914 case (strpos($cl , PATH_SEPARATOR) !== false):
2916 foreach(explode(PATH_SEPARATOR, $cl ) as $p) {
2917 $file[] = $p .'/'.preg_replace('/[^A-Z0-9]/i','_',ucfirst($table)).".php";
2921 $file = $cl .'/'.preg_replace('/[^A-Z0-9]/i','_',ucfirst($table)).".php";
2925 $cls = is_array($class) ? $class : array($class);
2927 if (is_array($file) || !file_exists($file)) {
2930 $file = is_array($file) ? $file : array($file);
2931 $search = implode(PATH_SEPARATOR, $file);
2932 foreach($file as $f) {
2933 foreach(explode(PATH_SEPARATOR, '' . PATH_SEPARATOR . ini_get('include_path')) as $p) {
2934 $ff = empty($p) ? $f : "$p/$f";
2936 if (file_exists($ff)) {
2947 DB_DataObject::raiseError(
2948 "autoload:Could not find class " . implode(',', $cls) .
2949 " using class_location value :" . $search .
2950 " using include_path value :" . ini_get('include_path'),
2951 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2960 foreach($cls as $c) {
2961 $ce = substr(phpversion(),0,1) > 4 ? class_exists($c,false) : class_exists($c);
2968 DB_DataObject::raiseError(
2969 "autoload:Could not autoload " . implode(',', $cls) ,
2970 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2979 * Have the links been loaded?
2980 * if they have it contains a array of those variables.
2983 * @var boolean | array
2985 var $_link_loaded = false;
2988 * Get the links associate array as defined by the links.ini file.
2992 * Should look a bit like
2993 * [local_col_name] => "related_tablename:related_col_name"
2995 * @param array $new_links optional - force update of the links for this table
2996 * You probably want to restore it to it's original state after,
2997 * as modifying here does it for the whole PHP request.
2999 * @return array|null
3000 * array = if there are links defined for this table.
3001 * empty array - if there is a links.ini file, but no links on this table
3002 * false - if no links.ini exists for this database (hence try auto_links).
3004 * @see DB_DataObject::getLinks(), DB_DataObject::getLink()
3009 global $_DB_DATAOBJECT;
3010 if (empty($_DB_DATAOBJECT['CONFIG'])) {
3011 $this->_loadConfig();
3013 // have to connect.. -> otherwise things break later.
3016 // alias for shorter code..
3017 $lcfg = &$_DB_DATAOBJECT['LINKS'];
3018 $cfg = $_DB_DATAOBJECT['CONFIG'];
3020 if ($args = func_get_args()) {
3021 // an associative array was specified, that updates the current
3022 // schema... - be careful doing this
3023 if (empty( $lcfg[$this->_database])) {
3024 $lcfg[$this->_database] = array();
3026 $lcfg[$this->_database][$this->tableName()] = $args[0];
3029 // loaded and available.
3030 if (isset($lcfg[$this->_database][$this->tableName()])) {
3031 return $lcfg[$this->_database][$this->tableName()];
3035 if (isset($lcfg[$this->_database])) {
3036 // either no file, or empty..
3037 return $lcfg[$this->_database] === false ? null : array();
3040 // links are same place as schema by default.
3041 $schemas = isset($cfg['schema_location']) ?
3042 array("{$cfg['schema_location']}/{$this->_database}.ini") :
3045 // if ini_* is set look there instead.
3046 // and support multiple locations.
3047 if (isset($cfg["ini_{$this->_database}"])) {
3048 $schemas = is_array($cfg["ini_{$this->_database}"]) ?
3049 $cfg["ini_{$this->_database}"] :
3050 explode(PATH_SEPARATOR,$cfg["ini_{$this->_database}"]);
3053 // default to not available.
3054 $lcfg[$this->_database] = false;
3056 foreach ($schemas as $ini) {
3058 $links = isset($cfg["links_{$this->_database}"]) ?
3059 $cfg["links_{$this->_database}"] :
3060 str_replace('.ini','.links.ini',$ini);
3062 // file really exists..
3063 if (!file_exists($links) || !is_file($links)) {
3064 if (!empty($cfg['debug'])) {
3065 $this->debug("Missing links.ini file: $links","links",1);
3070 // set to empty array - as we have at least one file now..
3071 $lcfg[$this->_database] = empty($lcfg[$this->_database]) ? array() : $lcfg[$this->_database];
3073 // merge schema file into lcfg..
3074 $lcfg[$this->_database] = array_merge(
3075 $lcfg[$this->_database],
3076 parse_ini_file($links, true)
3080 if (!empty($cfg['debug'])) {
3081 $this->debug("Loaded links.ini file: $links","links",1);
3086 if (!empty($_DB_DATAOBJECT['CONFIG']['portability']) && $_DB_DATAOBJECT['CONFIG']['portability'] & 1) {
3087 foreach($lcfg[$this->_database] as $k=>$v) {
3089 $nk = strtolower($k);
3090 // results in duplicate cols.. but not a big issue..
3091 $lcfg[$this->_database][$nk] = isset($lcfg[$this->_database][$nk])
3092 ? $lcfg[$this->_database][$nk] : array();
3094 foreach($v as $kk =>$vv) {
3095 //var_Dump($vv);exit;
3096 $vv =explode(':', $vv);
3097 $vv[0] = strtolower($vv[0]);
3098 $lcfg[$this->_database][$nk][$kk] = implode(':', $vv);
3104 //echo '<PRE>';print_r($lcfg);exit;
3106 // if there is no link data at all on the file!
3108 if ($lcfg[$this->_database] === false) {
3112 if (isset($lcfg[$this->_database][$this->tableName()])) {
3113 return $lcfg[$this->_database][$this->tableName()];
3121 * generic getter/setter for links
3123 * This is the new 'recommended' way to get get/set linked objects.
3124 * must be used with links.ini
3128 * $obj = $do->link('company_id');
3129 * $obj = $do->link(array('local_col', 'linktable:linked_col'));
3132 * $do->link('company_id',0);
3133 * $do->link('company_id',$obj);
3134 * $do->link('company_id', array($obj));
3138 * function company() {
3139 * $this->link(array('company_id','company:id'), func_get_args());
3144 * @param mixed $link_spec link specification (normally a string)
3145 * uses similar rules to joinAdd() array argument.
3146 * @param mixed $set_value (optional) int, DataObject, or array('set')
3147 * @author Alan Knowles
3149 * @return mixed true or false on setting, object on getting
3151 function link($field, $set_args = array())
3153 require_once 'DB/DataObject/Links.php';
3154 $l = new DB_DataObject_Links($this);
3155 return $l->link($field,$set_args) ;
3160 * load related objects
3162 * Generally not recommended to use this.
3163 * The generator should support creating getter_setter methods which are better suited.
3165 * Relies on <dbname>.links.ini
3167 * Sets properties on the calling dataobject you can change what
3168 * object vars the links are stored in by changeing the format parameter
3171 * @param string format (default _%s) where %s is the table name.
3172 * @author Tim White <tim@cyface.com>
3174 * @return boolean , true on success
3176 function getLinks($format = '_%s')
3178 require_once 'DB/DataObject/Links.php';
3179 $l = new DB_DataObject_Links($this);
3180 return $l->applyLinks($format);
3185 * deprecited : @use link()
3187 function getLink($row, $table = null, $link = false)
3189 require_once 'DB/DataObject/Links.php';
3190 $l = new DB_DataObject_Links($this);
3191 return $l->getLink($row, $table === null ? false: $table, $link);
3198 * Fetch an array of related objects. This should be used in conjunction with a <dbname>.links.ini file configuration (see the introduction on linking for details on this).
3199 * You may also use this with all parameters to specify, the column and related table.
3200 * This is highly dependant on naming columns 'correctly' :)
3201 * using colname = xxxxx_yyyyyy
3202 * xxxxxx = related table; (yyyyy = user defined..)
3203 * looks up table xxxxx, for value id=$this->xxxxx
3204 * stores it in $this->_xxxxx_yyyyy
3207 * @param string $column - either column or column.xxxxx
3208 * @param string $table - name of table to look up value in
3209 * @return array - array of results (empty array on failure)
3211 * Example - Getting the related objects
3213 * $person = new DataObjects_Person;
3215 * $children = $person->getLinkArray('children');
3217 * echo 'There are ', count($children), ' descendant(s):<br />';
3218 * foreach ($children as $child) {
3219 * echo $child->name, '<br />';
3223 function getLinkArray($row, $table = null)
3225 require_once 'DB/DataObject/Links.php';
3226 $l = new DB_DataObject_Links($this);
3227 return $l->getLinkArray($row, $table === null ? false: $table);
3232 * unionAdd - adds another dataobject to this, building a unioned query.
3235 * $doTable1 = DB_DataObject::factory("table1");
3236 * $doTable2 = DB_DataObject::factory("table2");
3238 * $doTable1->selectAdd();
3239 * $doTable1->selectAdd("col1,col2");
3240 * $doTable1->whereAdd("col1 > 100");
3241 * $doTable1->orderBy("col1");
3243 * $doTable2->selectAdd();
3244 * $doTable2->selectAdd("col1, col2");
3245 * $doTable2->whereAdd("col2 = 'v'");
3247 * $doTable1->unionAdd($doTable2);
3248 * $doTable1->find();
3250 * Note: this model may be a better way to implement joinAdd?, eg. do the building in find?
3253 * @param $obj object|false the union object or false to reset
3254 * @param optional $is_all string 'ALL' to do all.
3255 * @returns $obj object|array the added object, or old list if reset.
3258 function unionAdd($obj,$is_all= '')
3260 if ($obj === false) {
3261 $ret = $this->_query['unions'];
3262 $this->_query['unions'] = array();
3265 $this->_query['unions'][] = array($obj, 'UNION ' . $is_all . ' ') ;
3272 * The JOIN condition
3280 * joinAdd - adds another dataobject to this, building a joined query.
3282 * example (requires links.ini to be set up correctly)
3283 * // get all the images for product 24
3284 * $i = new DataObject_Image();
3285 * $pi = new DataObjects_Product_image();
3286 * $pi->product_id = 24; // set the product id to 24
3287 * $i->joinAdd($pi); // add the product_image connectoin
3289 * while ($i->fetch()) {
3292 * // an example with 2 joins
3293 * // get all the images linked with products or productgroups
3294 * $i = new DataObject_Image();
3295 * $pi = new DataObject_Product_image();
3296 * $pgi = new DataObject_Productgroup_image();
3298 * $i->joinAdd($pgi);
3300 * while ($i->fetch()) {
3305 * @param optional $obj object |array the joining object (no value resets the join)
3306 * If you use an array here it should be in the format:
3307 * array('local_column','remotetable:remote_column');
3308 * if remotetable does not have a definition, you should
3309 * use @ to hide the include error message..
3310 * array('local_column', $dataobject , 'remote_column');
3311 * if array has 3 args, then second is assumed to be the linked dataobject.
3313 * @param optional $joinType string | array
3314 * 'LEFT'|'INNER'|'RIGHT'|'' Inner is default, '' indicates
3315 * just select ... from a,b,c with no join and
3316 * links are added as where items.
3318 * If second Argument is array, it is assumed to be an associative
3319 * array with arguments matching below = eg.
3320 * 'joinType' => 'INNER',
3323 * 'useWhereAsOn' => false,
3325 * @param optional $joinAs string if you want to select the table as anther name
3326 * useful when you want to select multiple columsn
3327 * from a secondary table.
3329 * @param optional $joinCol string The column on This objects table to match (needed
3330 * if this table links to the child object in
3331 * multiple places eg.
3332 * user->friend (is a link to another user)
3333 * user->mother (is a link to another user..)
3335 * optional 'useWhereAsOn' bool default false;
3336 * convert the where argments from the object being added
3337 * into ON arguments.
3342 * @author Stijn de Reede <sjr@gmx.co.uk>
3344 function joinAdd($obj = false, $joinType='INNER', $joinAs=false, $joinCol=false)
3346 global $_DB_DATAOBJECT;
3347 if ($obj === false) {
3352 //echo '<PRE>'; print_r(func_get_args());
3353 $useWhereAsOn = false;
3354 // support for 2nd argument as an array of options
3355 if (is_array($joinType)) {
3356 // new options can now go in here... (dont forget to document them)
3357 $useWhereAsOn = !empty($joinType['useWhereAsOn']);
3358 $joinCol = isset($joinType['joinCol']) ? $joinType['joinCol'] : $joinCol;
3359 $joinAs = isset($joinType['joinAs']) ? $joinType['joinAs'] : $joinAs;
3360 $joinType = isset($joinType['joinType']) ? $joinType['joinType'] : 'INNER';
3362 // support for array as first argument
3363 // this assumes that you dont have a links.ini for the specified table.
3364 // and it doesnt exist as am extended dataobject!! - experimental.
3366 $ofield = false; // object field
3367 $tfield = false; // this field
3369 if (is_array($obj)) {
3372 if (count($obj) == 3) {
3376 list($toTable,$ofield) = explode(':',$obj[1]);
3378 $obj = is_string($toTable) ? DB_DataObject::factory($toTable) : $toTable;
3380 if (!$obj || !is_object($obj) || is_a($obj,'PEAR_Error')) {
3381 $obj = new DB_DataObject;
3382 $obj->__table = $toTable;
3386 // set the table items to nothing.. - eg. do not try and match
3387 // things in the child table...???
3391 if (!is_object($obj) || !is_a($obj,'DB_DataObject')) {
3392 return $this->raiseError("joinAdd: called without an object", DB_DATAOBJECT_ERROR_NODATA,PEAR_ERROR_DIE);
3394 /* make sure $this->_database is set. */
3396 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
3399 /// CHANGED 26 JUN 2009 - we prefer links from our local table over the remote one.
3401 /* otherwise see if there are any links from this table to the obj. */
3402 //print_r($this->links());
3403 if (($ofield === false) && ($links = $this->links())) {
3404 // this enables for support for arrays of links in ini file.
3405 // link contains this_column[] = linked_table:linked_column
3407 // link contains this_column = linked_table:linked_column
3408 foreach ($links as $k => $linkVar) {
3410 if (!is_array($linkVar)) {
3411 $linkVar = array($linkVar);
3413 foreach($linkVar as $v) {
3417 /* link contains {this column} = {linked table}:{linked column} */
3418 $ar = explode(':', $v);
3419 // Feature Request #4266 - Allow joins with multiple keys
3420 if (strpos($k, ',') !== false) {
3421 $k = explode(',', $k);
3423 if (strpos($ar[1], ',') !== false) {
3424 $ar[1] = explode(',', $ar[1]);
3427 if ($ar[0] != $obj->tableName()) {
3430 if ($joinCol !== false) {
3431 if ($k == $joinCol) {
3447 /* look up the links for obj table */
3448 //print_r($obj->links());
3449 if (!$ofield && ($olinks = $obj->links())) {
3451 foreach ($olinks as $k => $linkVar) {
3452 /* link contains {this column} = array ( {linked table}:{linked column} )*/
3453 if (!is_array($linkVar)) {
3454 $linkVar = array($linkVar);
3456 foreach($linkVar as $v) {
3458 /* link contains {this column} = {linked table}:{linked column} */
3459 $ar = explode(':', $v);
3461 // Feature Request #4266 - Allow joins with multiple keys
3462 $links_key_array = strpos($k,',');
3463 if ($links_key_array !== false) {
3464 $k = explode(',', $k);
3467 $ar_array = strpos($ar[1],',');
3468 if ($ar_array !== false) {
3469 $ar[1] = explode(',', $ar[1]);
3472 if ($ar[0] != $this->tableName()) {
3476 // you have explictly specified the column
3477 // and the col is listed here..
3478 // not sure if 1:1 table could cause probs here..
3480 if ($joinCol !== false) {
3482 "joinAdd: You cannot target a join column in the " .
3483 "'link from' table ({$obj->__table}). " .
3484 "Either remove the fourth argument to joinAdd() ".
3485 "({$joinCol}), or alter your links.ini file.",
3486 DB_DATAOBJECT_ERROR_NODATA);
3498 // finally if these two table have column names that match do a join by default on them
3500 if (($ofield === false) && $joinCol) {
3505 /* did I find a conneciton between them? */
3507 if ($ofield === false) {
3509 "joinAdd: {$obj->tableName()} has no link with {$this->tableName()}",
3510 DB_DATAOBJECT_ERROR_NODATA);
3513 $joinType = strtoupper($joinType);
3515 // we default to joining as the same name (this is remvoed later..)
3517 if ($joinAs === false) {
3518 $joinAs = $obj->tableName();
3521 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
3522 $options = $_DB_DATAOBJECT['CONFIG'];
3524 // not sure how portable adding database prefixes is..
3525 $objTable = $quoteIdentifiers ?
3526 $DB->quoteIdentifier($obj->tableName()) :
3530 if (strlen($obj->_database) && in_array($DB->dsn['phptype'],array('mysql','mysqli'))) {
3531 $dbPrefix = ($quoteIdentifiers
3532 ? $DB->quoteIdentifier($obj->_database)
3533 : $obj->_database) . '.';
3536 // if they are the same, then dont add a prefix...
3537 if ($obj->_database == $this->_database) {
3540 // as far as we know only mysql supports database prefixes..
3541 // prefixing the database name is now the default behaviour,
3542 // as it enables joining mutiple columns from multiple databases...
3544 // prefix database (quoted if neccessary..)
3545 $objTable = $dbPrefix . $objTable;
3549 // if obj only a dataobject - eg. no extended class has been defined..
3550 // it obvioulsy cant work out what child elements might exist...
3551 // until we get on the fly querying of tables..
3552 // note: we have already checked that it is_a(db_dataobject earlier)
3553 if ( strtolower(get_class($obj)) != 'db_dataobject') {
3555 // now add where conditions for anything that is set in the object
3559 $items = $obj->table();
3560 // will return an array if no items..
3562 // only fail if we where expecting it to work (eg. not joined on a array)
3566 "joinAdd: No table definition for {$obj->__table}",
3567 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
3571 $ignore_null = !isset($options['disable_null_strings'])
3572 || !is_string($options['disable_null_strings'])
3573 || strtolower($options['disable_null_strings']) !== 'full' ;
3576 foreach($items as $k => $v) {
3577 if (!isset($obj->$k) && $ignore_null) {
3581 $kSql = ($quoteIdentifiers ? $DB->quoteIdentifier($k) : $k);
3583 if (DB_DataObject::_is_null($obj,$k)) {
3584 $obj->whereAdd("{$joinAs}.{$kSql} IS NULL");
3588 if ($v & DB_DATAOBJECT_STR) {
3589 $obj->whereAdd("{$joinAs}.{$kSql} = " . $this->_quote((string) (
3590 ($v & DB_DATAOBJECT_BOOL) ?
3591 // this is thanks to the braindead idea of postgres to
3592 // use t/f for boolean.
3593 (($obj->$k === 'f') ? 0 : (int)(bool) $obj->$k) :
3598 if (is_numeric($obj->$k)) {
3599 $obj->whereAdd("{$joinAs}.{$kSql} = {$obj->$k}");
3603 if (is_object($obj->$k) && is_a($obj->$k,'DB_DataObject_Cast')) {
3604 $value = $obj->$k->toString($v,$DB);
3605 if (PEAR::isError($value)) {
3606 $this->raiseError($value->getMessage() ,DB_DATAOBJECT_ERROR_INVALIDARG);
3609 $obj->whereAdd("{$joinAs}.{$kSql} = $value");
3614 /* this is probably an error condition! */
3615 $obj->whereAdd("{$joinAs}.{$kSql} = 0");
3617 if ($this->_query === false) {
3619 "joinAdd can not be run from a object that has had a query run on it,
3620 clone the object or create a new one and use setFrom()",
3621 DB_DATAOBJECT_ERROR_INVALIDARGS);
3626 // and finally merge the whereAdd from the child..
3627 if ($obj->_query['condition']) {
3628 $cond = preg_replace('/^\sWHERE/i','',$obj->_query['condition']);
3630 if (!$useWhereAsOn) {
3631 $this->whereAdd($cond);
3638 // nested (join of joined objects..)
3641 // postgres allows nested queries, with ()'s
3642 // not sure what the results are with other databases..
3643 // may be unpredictable..
3644 if (in_array($DB->dsn["phptype"],array('pgsql'))) {
3645 $objTable = "($objTable {$obj->_join})";
3647 $appendJoin = $obj->_join;
3653 // add the joinee object's conditions to the ON clause instead of the WHERE clause
3654 if ($useWhereAsOn && strlen($cond)) {
3655 $appendJoin = ' AND ' . $cond . ' ' . $appendJoin;
3660 $table = $this->tableName();
3662 if ($quoteIdentifiers) {
3663 $joinAs = $DB->quoteIdentifier($joinAs);
3664 $table = $DB->quoteIdentifier($table);
3665 $ofield = (is_array($ofield)) ? array_map(array($DB, 'quoteIdentifier'), $ofield) : $DB->quoteIdentifier($ofield);
3666 $tfield = (is_array($tfield)) ? array_map(array($DB, 'quoteIdentifier'), $tfield) : $DB->quoteIdentifier($tfield);
3668 // add database prefix if they are different databases
3672 $addJoinAs = ($quoteIdentifiers ? $DB->quoteIdentifier($obj->tableName()) : $obj->tableName()) != $joinAs;
3674 // join table a AS b - is only supported by a few databases and is probably not needed
3675 // , however since it makes the whole Statement alot clearer we are leaving it in
3676 // for those databases.
3677 $fullJoinAs = in_array($DB->dsn["phptype"],array('mysql','mysqli','pgsql')) ? "AS {$joinAs}" : $joinAs;
3680 $joinAs = $dbPrefix . $joinAs;
3684 switch ($joinType) {
3687 case 'RIGHT': // others??? .. cross, left outer, right outer, natural..?
3689 // Feature Request #4266 - Allow joins with multiple keys
3690 $jadd = "\n {$joinType} JOIN {$objTable} {$fullJoinAs}";
3691 //$this->_join .= "\n {$joinType} JOIN {$objTable} {$fullJoinAs}";
3692 if (is_array($ofield)) {
3693 $key_count = count($ofield);
3694 for($i = 0; $i < $key_count; $i++) {
3696 $jadd .= " ON ({$joinAs}.{$ofield[$i]}={$table}.{$tfield[$i]}) ";
3699 $jadd .= " AND {$joinAs}.{$ofield[$i]}={$table}.{$tfield[$i]} ";
3702 $jadd .= ' ' . $appendJoin . ' ';
3704 $jadd .= " ON ({$joinAs}.{$ofield}={$table}.{$tfield}) {$appendJoin} ";
3706 // jadd avaliable for debugging join build.
3708 $this->_join .= $jadd;
3711 case '': // this is just a standard multitable select..
3712 $this->_join .= "\n , {$objTable} {$fullJoinAs} {$appendJoin}";
3713 $this->whereAdd("{$joinAs}.{$ofield}={$table}.{$tfield}");
3722 * autoJoin - using the links.ini file, it builds a query with all the joins
3724 * $x = DB_DataObject::factory('mytable');
3727 * will result in all of the joined data being added to the fetched object..
3729 * $x = DB_DataObject::factory('mytable');
3731 * $ar = $x->fetchAll();
3732 * will result in an array containing all the data from the table, and any joined tables..
3734 * $x = DB_DataObject::factory('mytable');
3735 * $jdata = $x->autoJoin();
3736 * $x->selectAdd(); //reset..
3737 * foreach($_REQUEST['requested_cols'] as $c) {
3738 * if (!isset($jdata[$c])) continue; // ignore columns not available..
3739 * $x->selectAdd( $jdata[$c] . ' as ' . $c);
3741 * $ar = $x->fetchAll();
3742 * will result in only the columns requested being fetched...
3746 * @param array Configuration
3747 * exclude Array of columns to exclude from results (eg. modified_by_id)
3748 * links The equivilant links.ini data for this table eg.
3749 * array( 'person_id' => 'person:id', .... )
3750 * include Array of columns to include
3751 * distinct Array of distinct columns.
3753 * @return array info about joins
3754 * cols => map of resulting {joined_tablename}.{joined_table_column_name}
3755 * join_names => map of resulting {join_name_as}.{joined_table_column_name}
3756 * count => the column to count on.
3759 function autoJoin($cfg = array())
3761 //var_Dump($cfg);exit;
3762 $pre_links = $this->links();
3763 if (!empty($cfg['links'])) {
3764 $this->links(array_merge( $pre_links , $cfg['links']));
3766 $map = $this->links( );
3770 $tabdef = $this->table();
3772 // we need this as normally it's only cleared by an empty selectAs call.
3775 $keys = array_keys($tabdef);
3776 if (!empty($cfg['exclude'])) {
3777 $keys = array_intersect($keys, array_diff($keys, $cfg['exclude']));
3779 if (!empty($cfg['include'])) {
3781 $keys = array_intersect($keys, $cfg['include']);
3784 $selectAs = array();
3786 if (!empty($keys)) {
3787 $selectAs = array(array( $keys , '%s', false));
3792 'join_names' => array(),
3798 $has_distinct = false;
3799 if (!empty($cfg['distinct']) && $keys) {
3801 // reset the columsn?
3804 //echo '<PRE>' ;print_r($xx);exit;
3805 foreach($keys as $c) {
3808 if ( $cfg['distinct'] == $c) {
3809 $has_distinct = 'DISTINCT( ' . $this->tableName() .'.'. $c .') as ' . $c;
3810 $ret['count'] = 'DISTINCT ' . $this->tableName() .'.'. $c .'';
3813 // cols is in our filtered keys...
3817 // apply our filtered version, which excludes the distinct column.
3819 $selectAs = empty($cols) ? array() : array(array(array( $cols) , '%s', false)) ;
3825 foreach($keys as $k) {
3826 $ret['cols'][$k] = $this->tableName(). '.' . $k;
3831 foreach($map as $ocl=>$info) {
3833 list($tab,$col) = explode(':', $info);
3834 // what about multiple joins on the same table!!!
3835 $xx = DB_DataObject::factory($tab);
3836 if (!is_object($xx) || !is_a($xx, 'DB_DataObject')) {
3839 // skip columns that are excluded.
3841 // we ignore include here... - as
3843 // this is borked ... for multiple jions..
3844 $this->joinAdd($xx, 'LEFT', 'join_'.$ocl.'_'. $col, $ocl);
3846 if (!empty($cfg['exclude']) && in_array($ocl, $cfg['exclude'])) {
3850 $tabdef = $xx->table();
3851 $table = $xx->tableName();
3853 $keys = array_keys($tabdef);
3856 if (!empty($cfg['exclude'])) {
3857 $keys = array_intersect($keys, array_diff($keys, $cfg['exclude']));
3859 foreach($keys as $k) {
3860 if (in_array($ocl.'_'.$k, $cfg['exclude'])) {
3861 $keys = array_diff($keys, $k); // removes the k..
3867 if (!empty($cfg['include'])) {
3868 // include will basically be BASECOLNAME_joinedcolname
3870 foreach($keys as $k) {
3871 if (in_array( sprintf($ocl.'_%s', $k), $cfg['include'])) {
3881 // got distinct, and not yet found it..
3882 if (!$has_distinct && !empty($cfg['distinct'])) {
3884 foreach($keys as $c) {
3885 $tn = sprintf($ocl.'_%s', $c);
3887 if ( $tn == $cfg['distinct']) {
3889 $has_distinct = 'DISTINCT( ' . 'join_'.$ocl.'_'.$col.'.'.$c .') as ' . $tn ;
3890 $ret['count'] = 'DISTINCT join_'.$ocl.'_'.$col.'.'.$c;
3891 // var_dump($this->countWhat );
3898 if (!empty($cols)) {
3899 $selectAs[] = array($cols, $ocl.'_%s', 'join_'.$ocl.'_'. $col);
3903 $selectAs[] = array($keys, $ocl.'_%s', 'join_'.$ocl.'_'. $col);
3906 foreach($keys as $k) {
3907 $ret['cols'][sprintf('%s_%s', $ocl, $k)] = $tab.'.'.$k;
3908 $ret['join_names'][sprintf('%s_%s', $ocl, $k)] = sprintf('join_%s_%s.%s',$ocl, $col, $k);
3913 // fill in the select details..
3916 if ($has_distinct) {
3917 $this->selectAdd($has_distinct);
3920 foreach($selectAs as $ar) {
3921 $this->selectAs($ar[0], $ar[1], $ar[2]);
3924 $this->links( $pre_links );
3931 * Factory method for calling DB_DataObject_Cast
3933 * if used with 1 argument DB_DataObject_Cast::sql($value) is called
3935 * if used with 2 arguments DB_DataObject_Cast::$value($callvalue) is called
3936 * valid first arguments are: blob, string, date, sql
3938 * eg. $member->updated = $member->sqlValue('NOW()');
3941 * might handle more arguments for escaping later...
3944 * @param string $value (or type if used with 2 arguments)
3945 * @param string $callvalue (optional) used with date/null etc..
3948 function sqlValue($value)
3951 if (func_num_args() == 2) {
3953 $value = func_get_arg(1);
3955 require_once 'DB/DataObject/Cast.php';
3956 return call_user_func(array('DB_DataObject_Cast', $method), $value);
3962 * Copies items that are in the table definitions from an
3963 * array or object into the current object
3964 * will not override key values.
3967 * @param array | object $from
3968 * @param string $format eg. map xxxx_name to $object->name using 'xxxx_%s' (defaults to %s - eg. name -> $object->name
3969 * @param boolean $skipEmpty (dont assign empty values if a column is empty (eg. '' / 0 etc...)
3971 * @return true on success or array of key=>setValue error message
3973 function setFrom($from, $format = '%s', $skipEmpty=false)
3975 global $_DB_DATAOBJECT;
3976 $keys = $this->keys();
3977 $items = $this->table();
3980 "setFrom:Could not find table definition for {$this->tableName()}",
3981 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
3984 $overload_return = array();
3985 foreach (array_keys($items) as $k) {
3986 if (in_array($k,$keys)) {
3987 continue; // dont overwrite keys
3990 continue; // ignore empty keys!!! what
3993 $chk = is_object($from) &&
3994 (version_compare(phpversion(), "5.1.0" , ">=") ?
3995 property_exists($from, sprintf($format,$k)) : // php5.1
3996 array_key_exists( sprintf($format,$k), get_class_vars($from)) //older
3998 // if from has property ($format($k)
4000 $kk = (strtolower($k) == 'from') ? '_from' : $k;
4001 if (method_exists($this,'set'.$kk)) {
4002 $ret = $this->{'set'.$kk}($from->{sprintf($format,$k)});
4003 if (is_string($ret)) {
4004 $overload_return[$k] = $ret;
4008 $this->$k = $from->{sprintf($format,$k)};
4012 if (is_object($from)) {
4016 if (empty($from[sprintf($format,$k)]) && $skipEmpty) {
4020 if (!isset($from[sprintf($format,$k)]) && !DB_DataObject::_is_null($from, sprintf($format,$k))) {
4024 $kk = (strtolower($k) == 'from') ? '_from' : $k;
4025 if (method_exists($this,'set'. $kk)) {
4026 $ret = $this->{'set'.$kk}($from[sprintf($format,$k)]);
4027 if (is_string($ret)) {
4028 $overload_return[$k] = $ret;
4032 $val = $from[sprintf($format,$k)];
4033 if (is_a($val, 'DB_DataObject_Cast')) {
4037 if (is_object($val) || is_array($val)) {
4040 $ret = $this->fromValue($k,$val);
4041 if ($ret !== true) {
4042 $overload_return[$k] = 'Not A Valid Value';
4044 //$this->$k = $from[sprintf($format,$k)];
4046 if ($overload_return) {
4047 return $overload_return;
4053 * Returns an associative array from the current data
4054 * (kind of oblivates the idea behind DataObjects, but
4055 * is usefull if you use it with things like QuickForms.
4057 * you can use the format to return things like user[key]
4058 * by sending it $object->toArray('user[%s]')
4060 * will also return links converted to arrays.
4062 * @param string sprintf format for array
4063 * @param bool||number [true = elemnts that have a value set],
4064 * [false = table + returned colums] ,
4065 * [0 = returned columsn only]
4068 * @return array of key => value for row
4071 function toArray($format = '%s', $hideEmpty = false)
4073 global $_DB_DATAOBJECT;
4075 // we use false to ignore sprintf.. (speed up..)
4076 $format = $format == '%s' ? false : $format;
4079 $rf = ($this->_resultFields !== false) ? $this->_resultFields :
4080 (isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]) ?
4081 $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid] : false);
4083 $ar = ($rf !== false) ?
4084 (($hideEmpty === 0) ? $rf : array_merge($rf, $this->table())) :
4087 foreach($ar as $k=>$v) {
4089 if (!isset($this->$k)) {
4091 $ret[$format === false ? $k : sprintf($format,$k)] = '';
4095 // call the overloaded getXXXX() method. - except getLink and getLinks
4096 if (method_exists($this,'get'.$k) && !in_array(strtolower($k),array('links','link'))) {
4097 $ret[$format === false ? $k : sprintf($format,$k)] = $this->{'get'.$k}();
4100 // should this call toValue() ???
4101 $ret[$format === false ? $k : sprintf($format,$k)] = $this->$k;
4103 if (!$this->_link_loaded) {
4106 foreach($this->_link_loaded as $k) {
4107 $ret[$format === false ? $k : sprintf($format,$k)] = $this->$k->toArray();
4115 * validate the values of the object (usually prior to inserting/updating..)
4117 * Note: This was always intended as a simple validation routine.
4118 * It lacks understanding of field length, whether you are inserting or updating (and hence null key values)
4120 * This should be moved to another class: DB_DataObject_Validate
4121 * FEEL FREE TO SEND ME YOUR VERSION FOR CONSIDERATION!!!
4124 * if (is_array($ret = $obj->validate())) { ... there are problems with the data ... }
4127 * - defaults to only testing strings/numbers if numbers or strings are the correct type and null values are correct
4128 * - validate Column methods : "validate{ROWNAME}()" are called if they are defined.
4129 * These methods should return
4130 * true = everything ok
4131 * false|object = something is wrong!
4133 * - This method loads and uses the PEAR Validate Class.
4137 * @return array of validation results (where key=>value, value=false|object if it failed) or true (if they all succeeded)
4141 global $_DB_DATAOBJECT;
4142 require_once 'Validate.php';
4143 $table = $this->table();
4145 $seq = $this->sequenceKey();
4146 $options = $_DB_DATAOBJECT['CONFIG'];
4147 foreach($table as $key => $val) {
4150 // call user defined validation always...
4151 $method = "Validate" . ucfirst($key);
4152 if (method_exists($this, $method)) {
4153 $ret[$key] = $this->$method();
4157 // if not null - and it's not set.......
4159 if ($val & DB_DATAOBJECT_NOTNULL && DB_DataObject::_is_null($this, $key)) {
4160 // dont check empty sequence key values..
4161 if (($key == $seq[0]) && ($seq[1] == true)) {
4169 if (DB_DataObject::_is_null($this, $key)) {
4170 if ($val & DB_DATAOBJECT_NOTNULL) {
4171 $this->debug("'null' field used for '$key', but it is defined as NOT NULL", 'VALIDATION', 4);
4178 // ignore things that are not set. ?
4180 if (!isset($this->$key)) {
4184 // if the string is empty.. assume it is ok..
4185 if (!is_object($this->$key) && !is_array($this->$key) && !strlen((string) $this->$key)) {
4189 // dont try and validate cast objects - assume they are problably ok..
4190 if (is_object($this->$key) && is_a($this->$key,'DB_DataObject_Cast')) {
4193 // at this point if you have set something to an object, and it's not expected
4194 // the Validate will probably break!!... - rightly so! (your design is broken,
4195 // so issuing a runtime error like PEAR_Error is probably not appropriate..
4198 // todo: date time.....
4199 case ($val & DB_DATAOBJECT_STR):
4200 $ret[$key] = Validate::string($this->$key, VALIDATE_PUNCTUATION . VALIDATE_NAME);
4202 case ($val & DB_DATAOBJECT_INT):
4203 $ret[$key] = Validate::number($this->$key, array('decimal'=>'.'));
4207 // if any of the results are false or an object (eg. PEAR_Error).. then return the array..
4208 foreach ($ret as $key => $val) {
4209 if ($val !== true) {
4213 return true; // everything is OK.
4217 * Gets the DB object related to an object - so you can use funky peardb stuf with it :)
4220 * @return object The DB connection
4222 function getDatabaseConnection()
4224 global $_DB_DATAOBJECT;
4226 if (($e = $this->_connect()) !== true) {
4229 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
4233 return $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
4238 * Gets the DB result object related to the objects active query
4239 * - so you can use funky pear stuff with it - like pager for example.. :)
4242 * @return object The DB result object
4245 function getDatabaseResult()
4247 global $_DB_DATAOBJECT;
4249 if (!isset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
4253 return $_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid];
4257 * Overload Extension support
4258 * - enables setCOLNAME/getCOLNAME
4259 * if you define a set/get method for the item it will be called.
4260 * otherwise it will just return/set the value.
4261 * NOTE this currently means that a few Names are NO-NO's
4262 * eg. links,link,linksarray, from, Databaseconnection,databaseresult
4265 * - set is automatically called by setFrom.
4266 * - get is automatically called by toArray()
4268 * setters return true on success. = strings on failure
4269 * getters return the value!
4271 * this fires off trigger_error - if any problems.. pear_error,
4272 * has problems with 4.3.2RC2 here
4280 function _call($method,$params,&$return) {
4282 //$this->debug("ATTEMPTING OVERLOAD? $method");
4283 // ignore constructors : - mm
4284 if (strtolower($method) == strtolower(get_class($this))) {
4287 $type = strtolower(substr($method,0,3));
4288 $class = get_class($this);
4289 if (($type != 'set') && ($type != 'get')) {
4295 // deal with naming conflick of setFrom = this is messy ATM!
4297 if (strtolower($method) == 'set_from') {
4298 $return = $this->toValue('from',isset($params[0]) ? $params[0] : null);
4302 $element = substr($method,3);
4304 // dont you just love php's case insensitivity!!!!
4306 $array = array_keys(get_class_vars($class));
4307 /* php5 version which segfaults on 5.0.3 */
4308 if (class_exists('ReflectionClass')) {
4309 $reflection = new ReflectionClass($class);
4310 $array = array_keys($reflection->getdefaultProperties());
4313 if (!in_array($element,$array)) {
4315 foreach($array as $k) {
4316 $case[strtolower($k)] = $k;
4318 if ((substr(phpversion(),0,1) == 5) && isset($case[strtolower($element)])) {
4319 trigger_error("PHP5 set/get calls should match the case of the variable",E_USER_WARNING);
4320 $element = strtolower($element);
4323 // does it really exist?
4324 if (!isset($case[$element])) {
4327 // use the mundged case
4328 $element = $case[$element]; // real case !
4332 if ($type == 'get') {
4333 $return = $this->toValue($element,isset($params[0]) ? $params[0] : null);
4338 $return = $this->fromValue($element, $params[0]);
4347 * standard set* implementation.
4349 * takes data and uses it to set dates/strings etc.
4350 * normally called from __call..
4353 * date = using (standard time format, or unixtimestamp).... so you could create a method :
4354 * function setLastread($string) { $this->fromValue('lastread',strtotime($string)); }
4356 * time = using strtotime
4357 * datetime = using same as date - accepts iso standard or unixtimestamp.
4358 * string = typecast only..
4360 * TODO: add formater:: eg. d/m/Y for date! ???
4362 * @param string column of database
4363 * @param mixed value to assign
4365 * @return true| false (False on error)
4367 * @see DB_DataObject::_call
4371 function fromValue($col,$value)
4373 global $_DB_DATAOBJECT;
4374 $options = $_DB_DATAOBJECT['CONFIG'];
4375 $cols = $this->table();
4376 // dont know anything about this col..
4377 if (!isset($cols[$col]) || is_a($value, 'DB_DataObject_Cast')) {
4378 $this->$col = $value;
4381 //echo "FROM VALUE $col, {$cols[$col]}, $value\n";
4383 // set to null and column is can be null...
4384 case ((!($cols[$col] & DB_DATAOBJECT_NOTNULL)) && DB_DataObject::_is_null($value, false)):
4385 case (is_object($value) && is_a($value,'DB_DataObject_Cast')):
4386 $this->$col = $value;
4389 // fail on setting null on a not null field..
4390 case (($cols[$col] & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($value,false)):
4394 case (($cols[$col] & DB_DATAOBJECT_DATE) && ($cols[$col] & DB_DATAOBJECT_TIME)):
4395 // empty values get set to '' (which is inserted/updated as NULl
4400 if (is_numeric($value)) {
4401 $this->$col = date('Y-m-d H:i:s', $value);
4405 // eak... - no way to validate date time otherwise...
4406 $this->$col = (string) $value;
4409 case ($cols[$col] & DB_DATAOBJECT_DATE):
4410 // empty values get set to '' (which is inserted/updated as NULl
4417 if (is_numeric($value)) {
4418 $this->$col = date('Y-m-d',$value);
4423 require_once 'Date.php';
4424 $x = new Date($value);
4425 $this->$col = $x->format("%Y-%m-%d");
4428 case ($cols[$col] & DB_DATAOBJECT_TIME):
4429 // empty values get set to '' (which is inserted/updated as NULl
4434 $guess = strtotime($value);
4436 $this->$col = date('H:i:s', $guess);
4437 return $return = true;
4439 // otherwise an error in type...
4442 case ($cols[$col] & DB_DATAOBJECT_STR):
4444 $this->$col = (string) $value;
4447 // todo : floats numerics and ints...
4449 $this->$col = $value;
4457 * standard get* implementation.
4460 * supported formaters:
4461 * date/time : %d/%m/%Y (eg. php strftime) or pear::Date
4462 * numbers : %02d (eg. sprintf)
4463 * NOTE you will get unexpected results with times like 0000-00-00 !!!
4467 * @param string column of database
4468 * @param format foramt
4470 * @return true Description
4472 * @see DB_DataObject::_call(),strftime(),Date::format()
4474 function toValue($col,$format = null)
4476 if (is_null($format)) {
4479 $cols = $this->table();
4481 case (($cols[$col] & DB_DATAOBJECT_DATE) && ($cols[$col] & DB_DATAOBJECT_TIME)):
4485 $guess = strtotime($this->$col);
4487 return strftime($format, $guess);
4489 // eak... - no way to validate date time otherwise...
4491 case ($cols[$col] & DB_DATAOBJECT_DATE):
4495 $guess = strtotime($this->$col);
4497 return strftime($format,$guess);
4500 require_once 'Date.php';
4501 $x = new Date($this->$col);
4502 return $x->format($format);
4504 case ($cols[$col] & DB_DATAOBJECT_TIME):
4508 $guess = strtotime($this->$col);
4510 return strftime($format, $guess);
4512 // otherwise an error in type...
4515 case ($cols[$col] & DB_DATAOBJECT_MYSQLTIMESTAMP):
4519 require_once 'Date.php';
4521 $x = new Date($this->$col);
4523 return $x->format($format);
4526 case ($cols[$col] & DB_DATAOBJECT_BOOL):
4528 if ($cols[$col] & DB_DATAOBJECT_STR) {
4530 return ($this->$col === 't');
4532 return (bool) $this->$col;
4536 return sprintf($format,$this->col);
4543 /* ----------------------- Debugger ------------------ */
4546 * Debugger. - use this in your extended classes to output debugging information.
4548 * Uses DB_DataObject::DebugLevel(x) to turn it on
4550 * @param string $message - message to output
4551 * @param string $logtype - bold at start
4552 * @param string $level - output level
4556 function debug($message, $logtype = 0, $level = 1)
4558 global $_DB_DATAOBJECT;
4560 if (empty($_DB_DATAOBJECT['CONFIG']['debug']) ||
4561 (is_numeric($_DB_DATAOBJECT['CONFIG']['debug']) && $_DB_DATAOBJECT['CONFIG']['debug'] < $level)) {
4564 // this is a bit flaky due to php's wonderfull class passing around crap..
4565 // but it's about as good as it gets..
4566 $class = (isset($this) && is_a($this,'DB_DataObject')) ? get_class($this) : 'DB_DataObject';
4568 if (!is_string($message)) {
4569 $message = print_r($message,true);
4571 if (!is_numeric( $_DB_DATAOBJECT['CONFIG']['debug']) && is_callable( $_DB_DATAOBJECT['CONFIG']['debug'])) {
4572 return call_user_func($_DB_DATAOBJECT['CONFIG']['debug'], $class, $message, $logtype, $level);
4575 if (!ini_get('html_errors')) {
4576 echo "$class : $logtype : $message\n";
4580 if (!is_string($message)) {
4581 $message = print_r($message,true);
4583 $colorize = ($logtype == 'ERROR') ? '<font color="red">' : '<font>';
4584 echo "<code>{$colorize}<B>$class: $logtype:</B> ". nl2br(htmlspecialchars($message)) . "</font></code><BR>\n";
4588 * sets and returns debug level
4589 * eg. DB_DataObject::debugLevel(4);
4591 * @param int $v level
4595 function debugLevel($v = null)
4597 global $_DB_DATAOBJECT;
4598 if (empty($_DB_DATAOBJECT['CONFIG'])) {
4599 DB_DataObject::_loadConfig();
4602 $r = isset($_DB_DATAOBJECT['CONFIG']['debug']) ? $_DB_DATAOBJECT['CONFIG']['debug'] : 0;
4603 $_DB_DATAOBJECT['CONFIG']['debug'] = $v;
4606 return isset($_DB_DATAOBJECT['CONFIG']['debug']) ? $_DB_DATAOBJECT['CONFIG']['debug'] : 0;
4610 * Last Error that has occured
4611 * - use $this->_lastError or
4612 * $last_error = PEAR::getStaticProperty('DB_DataObject','lastError');
4615 * @var object PEAR_Error (or false)
4617 var $_lastError = false;
4620 * Default error handling is to create a pear error, but never return it.
4621 * if you need to handle errors you should look at setting the PEAR_Error callback
4622 * this is due to the fact it would wreck havoc on the internal methods!
4624 * @param int $message message
4625 * @param int $type type
4626 * @param int $behaviour behaviour (die or continue!);
4628 * @return error object
4630 function raiseError($message, $type = null, $behaviour = null)
4632 global $_DB_DATAOBJECT;
4634 if ($behaviour == PEAR_ERROR_DIE && !empty($_DB_DATAOBJECT['CONFIG']['dont_die'])) {
4638 $error = &PEAR::getStaticProperty('DB_DataObject','lastError');
4641 // no checks for production here?....... - we log errors before we throw them.
4642 DB_DataObject::debug($message,'ERROR',1);
4645 if (PEAR::isError($message)) {
4648 require_once 'DB/DataObject/Error.php';
4649 $error = PEAR::raiseError($message, $type, $behaviour,
4650 $opts=null, $userinfo=null, 'DB_DataObject_Error'
4653 // this will never work totally with PHP's object model.
4654 // as this is passed on static calls (like staticGet in our case)
4656 $_DB_DATAOBJECT['LASTERROR'] = $error;
4658 if (isset($this) && is_object($this) && is_subclass_of($this,'db_dataobject')) {
4659 $this->_lastError = $error;
4666 * Define the global $_DB_DATAOBJECT['CONFIG'] as an alias to PEAR::getStaticProperty('DB_DataObject','options');
4668 * After Profiling DB_DataObject, I discoved that the debug calls where taking
4669 * considerable time (well 0.1 ms), so this should stop those calls happening. as
4670 * all calls to debug are wrapped with direct variable queries rather than actually calling the funciton
4671 * THIS STILL NEEDS FURTHER INVESTIGATION
4674 * @return object an error object
4676 function _loadConfig()
4678 global $_DB_DATAOBJECT;
4680 $_DB_DATAOBJECT['CONFIG'] = &PEAR::getStaticProperty('DB_DataObject','options');
4685 * Free global arrays associated with this object.
4693 global $_DB_DATAOBJECT;
4695 if (isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
4696 unset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]);
4698 if (isset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
4699 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
4701 // clear the staticGet cache as well.
4702 $this->_clear_cache();
4703 // this is a huge bug in DB!
4704 if (isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
4705 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->num_rows = array();
4708 if (is_array($this->_link_loaded)) {
4709 foreach ($this->_link_loaded as $do) {
4711 !empty($this->{$do}) &&
4712 is_object($this->{$do}) &&
4713 method_exists($this->{$do}, 'free')
4715 $this->{$do}->free();
4723 * Evaluate whether or not a value is set to null, taking the 'disable_null_strings' option into account.
4724 * If the value is a string set to "null" and the "disable_null_strings" option is not set to
4725 * true, then the value is considered to be null.
4726 * If the value is actually a PHP NULL value, and "disable_null_strings" has been set to
4727 * the value "full", then it will also be considered null. - this can not differenticate between not set
4730 * @param object|array $obj_or_ar
4731 * @param string|false $prop prperty
4734 * @return bool object
4736 function _is_null($obj_or_ar , $prop)
4738 global $_DB_DATAOBJECT;
4741 $isset = $prop === false ? isset($obj_or_ar) :
4742 (is_array($obj_or_ar) ? isset($obj_or_ar[$prop]) : isset($obj_or_ar->$prop));
4745 ($prop === false ? $obj_or_ar :
4746 (is_array($obj_or_ar) ? $obj_or_ar[$prop] : $obj_or_ar->$prop))
4751 $options = $_DB_DATAOBJECT['CONFIG'];
4753 $null_strings = !isset($options['disable_null_strings'])
4754 || $options['disable_null_strings'] === false;
4756 $crazy_null = isset($options['disable_null_strings'])
4757 && is_string($options['disable_null_strings'])
4758 && strtolower($options['disable_null_strings'] === 'full');
4760 if ( $null_strings && $isset && is_string($value) && (strtolower($value) === 'null') ) {
4764 if ( $crazy_null && !$isset ) {
4774 * (deprecated - use ::get / and your own caching method)
4776 function staticGet($class, $k, $v = null)
4778 $lclass = strtolower($class);
4779 global $_DB_DATAOBJECT;
4780 if (empty($_DB_DATAOBJECT['CONFIG'])) {
4781 DB_DataObject::_loadConfig();
4790 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
4791 DB_DataObject::debug("$class $key","STATIC GET - TRY CACHE");
4793 if (!empty($_DB_DATAOBJECT['CACHE'][$lclass][$key])) {
4794 return $_DB_DATAOBJECT['CACHE'][$lclass][$key];
4796 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
4797 DB_DataObject::debug("$class $key","STATIC GET - NOT IN CACHE");
4800 $obj = DB_DataObject::factory(substr($class,strlen($_DB_DATAOBJECT['CONFIG']['class_prefix'])));
4801 if (PEAR::isError($obj)) {
4802 DB_DataObject::raiseError("could not autoload $class", DB_DATAOBJECT_ERROR_NOCLASS);
4807 if (!isset($_DB_DATAOBJECT['CACHE'][$lclass])) {
4808 $_DB_DATAOBJECT['CACHE'][$lclass] = array();
4810 if (!$obj->get($k,$v)) {
4811 DB_DataObject::raiseError("No Data return from get $k $v", DB_DATAOBJECT_ERROR_NODATA);
4816 $_DB_DATAOBJECT['CACHE'][$lclass][$key] = $obj;
4817 return $_DB_DATAOBJECT['CACHE'][$lclass][$key];
4821 * autoload Class relating to a table
4822 * (deprecited - use ::factory)
4824 * @param string $table table
4826 * @return string classname on Success
4828 function staticAutoloadTable($table)
4830 global $_DB_DATAOBJECT;
4831 if (empty($_DB_DATAOBJECT['CONFIG'])) {
4832 DB_DataObject::_loadConfig();
4834 $p = isset($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
4835 $_DB_DATAOBJECT['CONFIG']['class_prefix'] : '';
4836 $class = $p . preg_replace('/[^A-Z0-9]/i','_',ucfirst($table));
4838 $ce = substr(phpversion(),0,1) > 4 ? class_exists($class,false) : class_exists($class);
4839 $class = $ce ? $class : DB_DataObject::_autoloadClass($class);
4843 /* ---- LEGACY BC METHODS - NOT DOCUMENTED - See Documentation on New Methods. ---*/
4845 function _get_table() { return $this->table(); }
4846 function _get_keys() { return $this->keys(); }
4852 // technially 4.3.2RC1 was broken!!
4853 // looks like 4.3.3 may have problems too....
4854 if (!defined('DB_DATAOBJECT_NO_OVERLOAD')) {
4856 if ((phpversion() != '4.3.2-RC1') && (version_compare( phpversion(), "4.3.1") > 0)) {
4857 if (version_compare( phpversion(), "5") < 0) {
4858 overload('DB_DataObject');
4860 $GLOBALS['_DB_DATAOBJECT']['OVERLOADED'] = true;