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 284150 2009-07-15 23:27:59Z 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 // NOTE: Overload SEGFAULTS ON PHP4 + Zend Optimizer (see define before..)
180 // these two are BC/FC handlers for call in PHP4/5
182 if ( substr(phpversion(),0,1) == 5) {
183 class DB_DataObject_Overload
185 function __call($method,$args)
188 $this->_call($method,$args,$return);
193 return array_keys(get_object_vars($this)) ;
197 if (version_compare(phpversion(),'4.3.10','eq') && !defined('DB_DATAOBJECT_NO_OVERLOAD')) {
199 "overload does not work with PHP4.3.10, either upgrade
200 (snaps.php.net) or more recent version
201 or define DB_DATAOBJECT_NO_OVERLOAD as per the manual.
205 if (!function_exists('clone')) {
206 // emulate clone - as per php_compact, slow but really the correct behaviour..
207 eval('function clone($t) { $r = $t; if (method_exists($r,"__clone")) { $r->__clone(); } return $r; }');
210 class DB_DataObject_Overload {
211 function __call($method,$args,&$return) {
212 return $this->_call($method,$args,$return);
225 * @package DB_DataObject
226 * @author Alan Knowles <alan@akbkhome.com>
230 class DB_DataObject extends DB_DataObject_Overload
233 * The Version - use this to check feature changes
238 var $_DB_DataObject_version = "1.8.12";
241 * The Database table (used by table extends)
246 var $__table = ''; // database table
249 * The Number of rows returned from a query
254 var $N = 0; // Number of rows returned from a query
256 /* ============================================================= */
257 /* Major Public Methods */
258 /* (designed to be optionally then called with parent::method()) */
259 /* ============================================================= */
263 * Get a result using key, value.
266 * $object->get("ID",1234);
267 * Returns Number of rows located (usually 1) for success,
268 * and puts all the table columns into this classes variables
270 * see the fetch example on how to extend this.
272 * if no value is entered, it is assumed that $key is a value
273 * and get will then use the first key in keys()
276 * @param string $k column
277 * @param string $v value
279 * @return int No. of rows
281 function get($k = null, $v = null)
283 global $_DB_DATAOBJECT;
284 if (empty($_DB_DATAOBJECT['CONFIG'])) {
285 DB_DataObject::_loadConfig();
291 $keys = $this->keys();
293 $this->raiseError("No Keys available for {$this->__table}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
298 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
299 $this->debug("$k $v " .print_r($keys,true), "GET");
303 $this->raiseError("No Value specified for get", DB_DATAOBJECT_ERROR_INVALIDARGS);
307 return $this->find(1);
311 * An autoloading, caching static get method using key, value (based on get)
314 * $object = DB_DataObject::staticGet("DbTable_mytable",12);
316 * $object = DB_DataObject::staticGet("DbTable_mytable","name","fred");
318 * or write it into your extended class:
319 * function &staticGet($k,$v=NULL) { return DB_DataObject::staticGet("This_Class",$k,$v); }
321 * @param string $class class name
322 * @param string $k column (or value if using keys)
323 * @param string $v value (optional)
327 function &staticGet($class, $k, $v = null)
329 $lclass = strtolower($class);
330 global $_DB_DATAOBJECT;
331 if (empty($_DB_DATAOBJECT['CONFIG'])) {
332 DB_DataObject::_loadConfig();
341 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
342 DB_DataObject::debug("$class $key","STATIC GET - TRY CACHE");
344 if (!empty($_DB_DATAOBJECT['CACHE'][$lclass][$key])) {
345 return $_DB_DATAOBJECT['CACHE'][$lclass][$key];
347 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
348 DB_DataObject::debug("$class $key","STATIC GET - NOT IN CACHE");
351 $obj = DB_DataObject::factory(substr($class,strlen($_DB_DATAOBJECT['CONFIG']['class_prefix'])));
352 if (PEAR::isError($obj)) {
353 DB_DataObject::raiseError("could not autoload $class", DB_DATAOBJECT_ERROR_NOCLASS);
358 if (!isset($_DB_DATAOBJECT['CACHE'][$lclass])) {
359 $_DB_DATAOBJECT['CACHE'][$lclass] = array();
361 if (!$obj->get($k,$v)) {
362 DB_DataObject::raiseError("No Data return from get $k $v", DB_DATAOBJECT_ERROR_NODATA);
367 $_DB_DATAOBJECT['CACHE'][$lclass][$key] = $obj;
368 return $_DB_DATAOBJECT['CACHE'][$lclass][$key];
372 * find results, either normal or crosstable
376 * $object = new mytable();
381 * will set $object->N to number of rows, and expects next command to fetch rows
382 * will return $object->N
384 * @param boolean $n Fetch first result
386 * @return mixed (number of rows returned, or true if numRows fetching is not supported)
388 function find($n = false)
390 global $_DB_DATAOBJECT;
391 if ($this->_query === false) {
393 "You cannot do two queries on the same object (copy it before finding)",
394 DB_DATAOBJECT_ERROR_INVALIDARGS);
398 if (empty($_DB_DATAOBJECT['CONFIG'])) {
399 DB_DataObject::_loadConfig();
402 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
403 $this->debug($n, "find",1);
405 if (!$this->__table) {
406 // xdebug can backtrace this!
407 trigger_error("NO \$__table SPECIFIED in class definition",E_USER_ERROR);
410 $query_before = $this->_query;
411 $this->_build_condition($this->table()) ;
413 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
415 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
417 /* We are checking for method modifyLimitQuery as it is PEAR DB specific */
419 $this->_query['data_select'] . " \n" .
420 ' FROM ' . ($quoteIdentifiers ? $DB->quoteIdentifier($this->__table) : $this->__table) . " \n" .
421 $this->_join . " \n" .
422 $this->_query['condition'] . " \n" .
423 $this->_query['group_by'] . " \n" .
424 $this->_query['having'] . " \n" .
425 $this->_query['order_by'] . " \n";
427 if ((!isset($_DB_DATAOBJECT['CONFIG']['db_driver'])) ||
428 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB')) {
429 /* PEAR DB specific */
431 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
432 $sql = $DB->modifyLimitQuery($sql,$this->_query['limit_start'], $this->_query['limit_count']);
435 /* theoretically MDB2! */
436 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
437 $DB->setLimit($this->_query['limit_count'],$this->_query['limit_start']);
444 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
445 $this->debug("CHECK autofetchd $n", "find", 1);
451 if (!$ret && !empty($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
452 // clear up memory if nothing found!?
453 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
456 if ($n && $this->N > 0 ) {
457 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
458 $this->debug("ABOUT TO AUTOFETCH", "find", 1);
460 $fs = $this->fetch();
461 // if fetch returns false (eg. failed), then the backend doesnt support numRows (eg. ret=true)
462 // - hence find() also returns false..
463 $ret = ($ret === true) ? $fs : $ret;
465 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
466 $this->debug("DONE", "find", 1);
468 $this->_query = $query_before;
473 * fetches next row into this objects var's
475 * returns 1 on success 0 on failure
480 * $object = new mytable();
481 * $object->name = "fred";
484 * while ($object->fetch()) {
486 * $store[] = $object; // builds an array of object lines.
489 * to add features to a fetch
490 * function fetch () {
491 * $ret = parent::fetch();
492 * $this->date_formated = date('dmY',$this->date);
497 * @return boolean on success
502 global $_DB_DATAOBJECT;
503 if (empty($_DB_DATAOBJECT['CONFIG'])) {
504 DB_DataObject::_loadConfig();
506 if (empty($this->N)) {
507 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
508 $this->debug("No data returned from FIND (eg. N is 0)","FETCH", 3);
513 if (empty($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]) ||
514 !is_object($result = &$_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]))
516 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
517 $this->debug('fetched on object after fetch completed (no results found)');
523 $array = $result->fetchRow(DB_DATAOBJECT_FETCHMODE_ASSOC);
524 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
525 $this->debug(serialize($array),"FETCH");
528 // fetched after last row..
529 if ($array === null) {
530 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
531 $t= explode(' ',microtime());
533 $this->debug("Last Data Fetch'ed after " .
534 ($t[0]+$t[1]- $_DB_DATAOBJECT['QUERYENDTIME'] ) .
538 // reduce the memory usage a bit... (but leave the id in, so count() works ok on it)
539 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
541 // we need to keep a copy of resultfields locally so toArray() still works
542 // however we dont want to keep it in the global cache..
544 if (!empty($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
545 $this->_resultFields = $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid];
546 unset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]);
548 // this is probably end of data!!
549 //DB_DataObject::raiseError("fetch: no data returned", DB_DATAOBJECT_ERROR_NODATA);
552 // make sure resultFields is always empty..
553 $this->_resultFields = false;
555 if (!isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
556 // note: we dont declare this to keep the print_r size down.
557 $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]= array_flip(array_keys($array));
560 foreach($array as $k=>$v) {
561 $kk = str_replace(".", "_", $k);
562 $kk = str_replace(" ", "_", $kk);
563 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
564 $this->debug("$kk = ". $array[$k], "fetchrow LINE", 3);
566 $this->$kk = $array[$k];
570 $this->_link_loaded=false;
571 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
572 $this->debug("{$this->__table} DONE", "fetchrow",2);
574 if (($this->_query !== false) && empty($_DB_DATAOBJECT['CONFIG']['keep_query_after_fetch'])) {
575 $this->_query = false;
581 * Adds a condition to the WHERE statement, defaults to AND
583 * $object->whereAdd(); //reset or cleaer ewhwer
584 * $object->whereAdd("ID > 20");
585 * $object->whereAdd("age > 20","OR");
587 * @param string $cond condition
588 * @param string $logic optional logic "OR" (defaults to "AND")
590 * @return string|PEAR::Error - previous condition or Error when invalid args found
592 function whereAdd($cond = false, $logic = 'AND')
594 // for PHP5.2.3 - there is a bug with setting array properties of an object.
595 $_query = $this->_query;
597 if (!isset($this->_query) || ($_query === false)) {
598 return $this->raiseError(
599 "You cannot do two queries on the same object (clone it before finding)",
600 DB_DATAOBJECT_ERROR_INVALIDARGS);
603 if ($cond === false) {
604 $r = $this->_query['condition'];
605 $_query['condition'] = '';
606 $this->_query = $_query;
607 return preg_replace('/^\s+WHERE\s+/','',$r);
609 // check input...= 0 or ' ' == error!
611 return $this->raiseError("WhereAdd: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
613 $r = $_query['condition'];
614 if ($_query['condition']) {
615 $_query['condition'] .= " {$logic} ( {$cond} )";
616 $this->_query = $_query;
619 $_query['condition'] = " WHERE ( {$cond} ) ";
620 $this->_query = $_query;
625 * Adds a order by condition
627 * $object->orderBy(); //clears order by
628 * $object->orderBy("ID");
629 * $object->orderBy("ID,age");
631 * @param string $order Order
633 * @return none|PEAR::Error - invalid args only
635 function orderBy($order = false)
637 if ($this->_query === false) {
639 "You cannot do two queries on the same object (copy it before finding)",
640 DB_DATAOBJECT_ERROR_INVALIDARGS);
643 if ($order === false) {
644 $this->_query['order_by'] = '';
647 // check input...= 0 or ' ' == error!
649 return $this->raiseError("orderBy: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
652 if (!$this->_query['order_by']) {
653 $this->_query['order_by'] = " ORDER BY {$order} ";
656 $this->_query['order_by'] .= " , {$order}";
660 * Adds a group by condition
662 * $object->groupBy(); //reset the grouping
663 * $object->groupBy("ID DESC");
664 * $object->groupBy("ID,age");
666 * @param string $group Grouping
668 * @return none|PEAR::Error - invalid args only
670 function groupBy($group = false)
672 if ($this->_query === false) {
674 "You cannot do two queries on the same object (copy it before finding)",
675 DB_DATAOBJECT_ERROR_INVALIDARGS);
678 if ($group === false) {
679 $this->_query['group_by'] = '';
682 // check input...= 0 or ' ' == error!
684 return $this->raiseError("groupBy: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
688 if (!$this->_query['group_by']) {
689 $this->_query['group_by'] = " GROUP BY {$group} ";
692 $this->_query['group_by'] .= " , {$group}";
696 * Adds a having clause
698 * $object->having(); //reset the grouping
699 * $object->having("sum(value) > 0 ");
701 * @param string $having condition
703 * @return none|PEAR::Error - invalid args only
705 function having($having = false)
707 if ($this->_query === false) {
709 "You cannot do two queries on the same object (copy it before finding)",
710 DB_DATAOBJECT_ERROR_INVALIDARGS);
713 if ($having === false) {
714 $this->_query['having'] = '';
717 // check input...= 0 or ' ' == error!
718 if (!trim($having)) {
719 return $this->raiseError("Having: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
723 if (!$this->_query['having']) {
724 $this->_query['having'] = " HAVING {$having} ";
727 $this->_query['having'] .= " AND {$having}";
733 * $boject->limit(); // clear limit
734 * $object->limit(12);
735 * $object->limit(12,10);
737 * Note this will emit an error on databases other than mysql/postgress
738 * as there is no 'clean way' to implement it. - you should consider refering to
739 * your database manual to decide how you want to implement it.
741 * @param string $a limit start (or number), or blank to reset
742 * @param string $b number
744 * @return none|PEAR::Error - invalid args only
746 function limit($a = null, $b = null)
748 if ($this->_query === false) {
750 "You cannot do two queries on the same object (copy it before finding)",
751 DB_DATAOBJECT_ERROR_INVALIDARGS);
756 $this->_query['limit_start'] = '';
757 $this->_query['limit_count'] = '';
760 // check input...= 0 or ' ' == error!
761 if ((!is_int($a) && ((string)((int)$a) !== (string)$a))
762 || (($b !== null) && (!is_int($b) && ((string)((int)$b) !== (string)$b)))) {
763 return $this->raiseError("limit: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
765 global $_DB_DATAOBJECT;
767 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
769 $this->_query['limit_start'] = ($b == null) ? 0 : (int)$a;
770 $this->_query['limit_count'] = ($b == null) ? (int)$a : (int)$b;
775 * Adds a select columns
777 * $object->selectAdd(); // resets select to nothing!
778 * $object->selectAdd("*"); // default select
779 * $object->selectAdd("unixtime(DATE) as udate");
780 * $object->selectAdd("DATE");
782 * to prepend distict:
783 * $object->selectAdd('distinct ' . $object->selectAdd());
787 * @return mixed null or old string if you reset it.
789 function selectAdd($k = null)
791 if ($this->_query === false) {
793 "You cannot do two queries on the same object (copy it before finding)",
794 DB_DATAOBJECT_ERROR_INVALIDARGS);
798 $old = $this->_query['data_select'];
799 $this->_query['data_select'] = '';
803 // check input...= 0 or ' ' == error!
805 return $this->raiseError("selectAdd: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
808 if ($this->_query['data_select']) {
809 $this->_query['data_select'] .= ', ';
811 $this->_query['data_select'] .= " $k ";
814 * Adds multiple Columns or objects to select with formating.
816 * $object->selectAs(null); // adds "table.colnameA as colnameA,table.colnameB as colnameB,......"
817 * // note with null it will also clear the '*' default select
818 * $object->selectAs(array('a','b'),'%s_x'); // adds "a as a_x, b as b_x"
819 * $object->selectAs(array('a','b'),'ddd_%s','ccc'); // adds "ccc.a as ddd_a, ccc.b as ddd_b"
820 * $object->selectAdd($object,'prefix_%s'); // calls $object->get_table and adds it all as
821 * objectTableName.colnameA as prefix_colnameA
823 * @param array|object|null the array or object to take column names from.
824 * @param string format in sprintf format (use %s for the colname)
825 * @param string table name eg. if you have joinAdd'd or send $from as an array.
829 function selectAs($from = null,$format = '%s',$tableName=false)
831 global $_DB_DATAOBJECT;
833 if ($this->_query === false) {
835 "You cannot do two queries on the same object (copy it before finding)",
836 DB_DATAOBJECT_ERROR_INVALIDARGS);
840 if ($from === null) {
847 $table = $this->__table;
848 if (is_object($from)) {
849 $table = $from->__table;
850 $from = array_keys($from->table());
853 if ($tableName !== false) {
857 if (!empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers'])) {
859 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
860 $s = $DB->quoteIdentifier($s);
861 $format = $DB->quoteIdentifier($format);
863 foreach ($from as $k) {
864 $this->selectAdd(sprintf("{$s}.{$s} as {$format}",$table,$k,$k));
866 $this->_query['data_select'] .= "\n";
869 * Insert the current objects variables into the database
871 * Returns the ID of the inserted element (if auto increment or sequences are used.)
875 * Designed to be extended
877 * $object = new mytable();
878 * $object->name = "fred";
879 * echo $object->insert();
882 * @return mixed false on failure, int when auto increment or sequence used, otherwise true on success
886 global $_DB_DATAOBJECT;
888 // we need to write to the connection (For nextid) - so us the real
889 // one not, a copyied on (as ret-by-ref fails with overload!)
891 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
895 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
897 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
899 $items = isset($_DB_DATAOBJECT['INI'][$this->_database][$this->__table]) ?
900 $_DB_DATAOBJECT['INI'][$this->_database][$this->__table] : $this->table();
903 $this->raiseError("insert:No table definition for {$this->__table}",
904 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
907 $options = &$_DB_DATAOBJECT['CONFIG'];
914 $seqKeys = isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table]) ?
915 $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] :
916 $this->sequenceKey();
918 $key = isset($seqKeys[0]) ? $seqKeys[0] : false;
919 $useNative = isset($seqKeys[1]) ? $seqKeys[1] : false;
920 $seq = isset($seqKeys[2]) ? $seqKeys[2] : false;
922 $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn["phptype"];
925 // nativeSequences or Sequences..
927 // big check for using sequences
929 if (($key !== false) && !$useNative) {
932 $keyvalue = $DB->nextId($this->__table);
934 $f = $DB->getOption('seqname_format');
935 $DB->setOption('seqname_format','%s');
936 $keyvalue = $DB->nextId($seq);
937 $DB->setOption('seqname_format',$f);
939 if (PEAR::isError($keyvalue)) {
940 $this->raiseError($keyvalue->toString(), DB_DATAOBJECT_ERROR_INVALIDCONFIG);
943 $this->$key = $keyvalue;
948 foreach($items as $k => $v) {
950 // if we are using autoincrement - skip the column...
951 if ($key && ($k == $key) && $useNative) {
956 if (!isset($this->$k)) {
959 // dont insert data into mysql timestamps
960 // use query() if you really want to do this!!!!
961 if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
970 $leftq .= ($quoteIdentifiers ? ($DB->quoteIdentifier($k) . ' ') : "$k ");
972 if (is_a($this->$k,'DB_DataObject_Cast')) {
973 $value = $this->$k->toString($v,$DB);
974 if (PEAR::isError($value)) {
975 $this->raiseError($value->toString() ,DB_DATAOBJECT_ERROR_INVALIDARGS);
984 if (!isset($options['disable_null_strings']) && is_string($this->$k) && (strtolower($this->$k) === 'null') && !($v & DB_DATAOBJECT_NOTNULL)) {
988 // DATE is empty... on a col. that can be null..
989 // note: this may be usefull for time as well..
991 (($v & DB_DATAOBJECT_DATE) || ($v & DB_DATAOBJECT_TIME)) &&
992 !($v & DB_DATAOBJECT_NOTNULL)) {
999 if ($v & DB_DATAOBJECT_STR) {
1000 $rightq .= $this->_quote((string) (
1001 ($v & DB_DATAOBJECT_BOOL) ?
1002 // this is thanks to the braindead idea of postgres to
1003 // use t/f for boolean.
1004 (($this->$k === 'f') ? 0 : (int)(bool) $this->$k) :
1009 if (is_numeric($this->$k)) {
1010 $rightq .=" {$this->$k} ";
1013 /* flag up string values - only at debug level... !!!??? */
1014 if (is_object($this->$k) || is_array($this->$k)) {
1015 $this->debug('ODD DATA: ' .$k . ' ' . print_r($this->$k,true),'ERROR');
1018 // at present we only cast to integers
1019 // - V2 may store additional data about float/int
1020 $rightq .= ' ' . intval($this->$k) . ' ';
1024 // not sure why we let empty insert here.. - I guess to generate a blank row..
1027 if ($leftq || $useNative) {
1028 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->__table) : $this->__table);
1031 if (($dbtype == 'pgsql') && empty($leftq)) {
1032 $r = $this->_query("INSERT INTO {$table} DEFAULT VALUES");
1034 $r = $this->_query("INSERT INTO {$table} ($leftq) VALUES ($rightq) ");
1040 if (PEAR::isError($r)) {
1041 $this->raiseError($r);
1050 // now do we have an integer key!
1052 if ($key && $useNative) {
1056 $method = "{$dbtype}_insert_id";
1057 $this->$key = $method(
1058 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->connection
1063 // note this is not really thread safe - you should wrapp it with
1064 // transactions = eg.
1065 // $db->query('BEGIN');
1067 // $db->query('COMMIT');
1068 $db_driver = empty($options['db_driver']) ? 'DB' : $options['db_driver'];
1069 $method = ($db_driver == 'DB') ? 'getOne' : 'queryOne';
1070 $mssql_key = $DB->$method("SELECT @@IDENTITY");
1071 if (PEAR::isError($mssql_key)) {
1072 $this->raiseError($mssql_key);
1075 $this->$key = $mssql_key;
1080 $seq = $DB->getSequenceName(strtolower($this->__table));
1082 $db_driver = empty($options['db_driver']) ? 'DB' : $options['db_driver'];
1083 $method = ($db_driver == 'DB') ? 'getOne' : 'queryOne';
1084 $pgsql_key = $DB->$method("SELECT currval('".$seq . "')");
1087 if (PEAR::isError($pgsql_key)) {
1088 $this->raiseError($pgsql_key);
1091 $this->$key = $pgsql_key;
1095 $this->$key = array_shift (
1098 "select DBINFO('sqlca.sqlerrd1') FROM systables where tabid=1",
1099 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->connection,
1111 if (isset($_DB_DATAOBJECT['CACHE'][strtolower(get_class($this))])) {
1112 $this->_clear_cache();
1119 $this->raiseError("insert: No Data specifed for query", DB_DATAOBJECT_ERROR_NODATA);
1124 * Updates current objects variables into the database
1125 * uses the keys() to decide how to update
1126 * Returns the true on success
1130 * $object = DB_DataObject::factory('mytable');
1131 * $object->get("ID",234);
1132 * $object->email="testing@test.com";
1133 * if(!$object->update())
1134 * echo "UPDATE FAILED";
1136 * to only update changed items :
1137 * $dataobject->get(132);
1138 * $original = $dataobject; // clone/copy it..
1139 * $dataobject->setFrom($_POST);
1140 * if ($dataobject->validate()) {
1141 * $dataobject->update($original);
1142 * } // otherwise an error...
1144 * performing global updates:
1145 * $object = DB_DataObject::factory('mytable');
1146 * $object->status = "dead";
1147 * $object->whereAdd('age > 150');
1148 * $object->update(DB_DATAOBJECT_WHEREADD_ONLY);
1150 * @param object dataobject (optional) | DB_DATAOBJECT_WHEREADD_ONLY - used to only update changed items.
1152 * @return int rows affected or false on failure
1154 function update($dataObject = false)
1156 global $_DB_DATAOBJECT;
1157 // connect will load the config!
1161 $original_query = $this->_query;
1163 $items = isset($_DB_DATAOBJECT['INI'][$this->_database][$this->__table]) ?
1164 $_DB_DATAOBJECT['INI'][$this->_database][$this->__table] : $this->table();
1166 // only apply update against sequence key if it is set?????
1168 $seq = $this->sequenceKey();
1169 if ($seq[0] !== false) {
1170 $keys = array($seq[0]);
1171 if (empty($this->{$keys[0]}) && $dataObject !== true) {
1172 $this->raiseError("update: trying to perform an update without
1173 the key set, and argument to update is not
1174 DB_DATAOBJECT_WHEREADD_ONLY
1175 ", DB_DATAOBJECT_ERROR_INVALIDARGS);
1179 $keys = $this->keys();
1184 $this->raiseError("update:No table definition for {$this->__table}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
1191 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1192 $dbtype = $DB->dsn["phptype"];
1193 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1194 $options = $_DB_DATAOBJECT['CONFIG'];
1197 foreach($items as $k => $v) {
1198 if (!isset($this->$k)) {
1201 // ignore stuff thats
1203 // dont write things that havent changed..
1204 if (($dataObject !== false) && isset($dataObject->$k) && ($dataObject->$k === $this->$k)) {
1208 // - dont write keys to left.!!!
1209 if (in_array($k,$keys)) {
1213 // dont insert data into mysql timestamps
1214 // use query() if you really want to do this!!!!
1215 if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
1224 $kSql = ($quoteIdentifiers ? $DB->quoteIdentifier($k) : $k);
1226 if (is_a($this->$k,'DB_DataObject_Cast')) {
1227 $value = $this->$k->toString($v,$DB);
1228 if (PEAR::isError($value)) {
1229 $this->raiseError($value->getMessage() ,DB_DATAOBJECT_ERROR_INVALIDARG);
1232 $settings .= "$kSql = $value ";
1236 // special values ... at least null is handled...
1237 if (!isset($options['disable_null_strings']) && (strtolower($this->$k) === 'null') && !($v & DB_DATAOBJECT_NOTNULL)) {
1238 $settings .= "$kSql = NULL ";
1241 // DATE is empty... on a col. that can be null..
1242 // note: this may be usefull for time as well..
1244 (($v & DB_DATAOBJECT_DATE) || ($v & DB_DATAOBJECT_TIME)) &&
1245 !($v & DB_DATAOBJECT_NOTNULL)) {
1247 $settings .= "$kSql = NULL ";
1252 if ($v & DB_DATAOBJECT_STR) {
1253 $settings .= "$kSql = ". $this->_quote((string) (
1254 ($v & DB_DATAOBJECT_BOOL) ?
1255 // this is thanks to the braindead idea of postgres to
1256 // use t/f for boolean.
1257 (($this->$k === 'f') ? 0 : (int)(bool) $this->$k) :
1262 if (is_numeric($this->$k)) {
1263 $settings .= "$kSql = {$this->$k} ";
1266 // at present we only cast to integers
1267 // - V2 may store additional data about float/int
1268 $settings .= "$kSql = " . intval($this->$k) . ' ';
1272 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1273 $this->debug("got keys as ".serialize($keys),3);
1275 if ($dataObject !== true) {
1276 $this->_build_condition($items,$keys);
1278 // prevent wiping out of data!
1279 if (empty($this->_query['condition'])) {
1280 $this->raiseError("update: global table update not available
1281 do \$do->whereAdd('1=1'); if you really want to do that.
1282 ", DB_DATAOBJECT_ERROR_INVALIDARGS);
1289 // echo " $settings, $this->condition ";
1290 if ($settings && isset($this->_query) && $this->_query['condition']) {
1292 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->__table) : $this->__table);
1294 $r = $this->_query("UPDATE {$table} SET {$settings} {$this->_query['condition']} ");
1296 // restore original query conditions.
1297 $this->_query = $original_query;
1299 if (PEAR::isError($r)) {
1300 $this->raiseError($r);
1307 $this->_clear_cache();
1310 // restore original query conditions.
1311 $this->_query = $original_query;
1313 // if you manually specified a dataobject, and there where no changes - then it's ok..
1314 if ($dataObject !== false) {
1319 "update: No Data specifed for query $settings , {$this->_query['condition']}",
1320 DB_DATAOBJECT_ERROR_NODATA);
1325 * Deletes items from table which match current objects variables
1327 * Returns the true on success
1331 * Designed to be extended
1333 * $object = new mytable();
1335 * echo $object->delete(); // builds a conditon
1337 * $object = new mytable();
1338 * $object->whereAdd('age > 12');
1339 * $object->limit(1);
1340 * $object->orderBy('age DESC');
1341 * $object->delete(true); // dont use object vars, use the conditions, limit and order.
1343 * @param bool $useWhere (optional) If DB_DATAOBJECT_WHEREADD_ONLY is passed in then
1344 * we will build the condition only using the whereAdd's. Default is to
1345 * build the condition only using the object parameters.
1348 * @return mixed Int (No. of rows affected) on success, false on failure, 0 on no data affected
1350 function delete($useWhere = false)
1352 global $_DB_DATAOBJECT;
1353 // connect will load the config!
1355 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1356 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1358 $extra_cond = ' ' . (isset($this->_query['order_by']) ? $this->_query['order_by'] : '');
1362 $keys = $this->keys();
1363 $this->_query = array(); // as it's probably unset!
1364 $this->_query['condition'] = ''; // default behaviour not to use where condition
1365 $this->_build_condition($this->table(),$keys);
1366 // if primary keys are not set then use data from rest of object.
1367 if (!$this->_query['condition']) {
1368 $this->_build_condition($this->table(),array(),$keys);
1374 // don't delete without a condition
1375 if (($this->_query !== false) && $this->_query['condition']) {
1377 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->__table) : $this->__table);
1379 // using a joined delete. - with useWhere..
1380 $sql .= (!empty($this->_join) && $useWhere) ?
1381 "{$table} FROM {$table} {$this->_join} " :
1384 $sql .= $this->_query['condition']. $extra_cond;
1388 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
1390 if (!isset($_DB_DATAOBJECT['CONFIG']['db_driver']) ||
1391 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB')) {
1393 $sql = $DB->modifyLimitQuery($sql,$this->_query['limit_start'], $this->_query['limit_count']);
1397 $DB->setLimit( $this->_query['limit_count'],$this->_query['limit_start']);
1403 $r = $this->_query($sql);
1406 if (PEAR::isError($r)) {
1407 $this->raiseError($r);
1413 $this->_clear_cache();
1416 $this->raiseError("delete: No condition specifed for query", DB_DATAOBJECT_ERROR_NODATA);
1422 * fetches a specific row into this object variables
1424 * Not recommended - better to use fetch()
1426 * Returens true on success
1428 * @param int $row row
1430 * @return boolean true on success
1432 function fetchRow($row = null)
1434 global $_DB_DATAOBJECT;
1435 if (empty($_DB_DATAOBJECT['CONFIG'])) {
1436 $this->_loadConfig();
1438 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1439 $this->debug("{$this->__table} $row of {$this->N}", "fetchrow",3);
1441 if (!$this->__table) {
1442 $this->raiseError("fetchrow: No table", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
1445 if ($row === null) {
1446 $this->raiseError("fetchrow: No row specified", DB_DATAOBJECT_ERROR_INVALIDARGS);
1450 $this->raiseError("fetchrow: No results avaiable", DB_DATAOBJECT_ERROR_NODATA);
1453 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1454 $this->debug("{$this->__table} $row of {$this->N}", "fetchrow",3);
1458 $result = &$_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid];
1459 $array = $result->fetchrow(DB_DATAOBJECT_FETCHMODE_ASSOC,$row);
1460 if (!is_array($array)) {
1461 $this->raiseError("fetchrow: No results available", DB_DATAOBJECT_ERROR_NODATA);
1465 foreach($array as $k => $v) {
1466 $kk = str_replace(".", "_", $k);
1467 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1468 $this->debug("$kk = ". $array[$k], "fetchrow LINE", 3);
1470 $this->$kk = $array[$k];
1473 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1474 $this->debug("{$this->__table} DONE", "fetchrow", 3);
1480 * Find the number of results from a simple query
1484 * $object = new mytable();
1485 * $object->name = "fred";
1486 * echo $object->count();
1487 * echo $object->count(true); // dont use object vars.
1488 * echo $object->count('distinct mycol'); count distinct mycol.
1489 * echo $object->count('distinct mycol',true); // dont use object vars.
1490 * echo $object->count('distinct'); // count distinct id (eg. the primary key)
1493 * @param bool|string (optional)
1494 * (true|false => see below not on whereAddonly)
1496 * "DISTINCT" => does a distinct count on the tables 'key' column
1497 * otherwise => normally it counts primary keys - you can use
1498 * this to do things like $do->count('distinct mycol');
1500 * @param bool $whereAddOnly (optional) If DB_DATAOBJECT_WHEREADD_ONLY is passed in then
1501 * we will build the condition only using the whereAdd's. Default is to
1502 * build the condition using the object parameters as well.
1507 function count($countWhat = false,$whereAddOnly = false)
1509 global $_DB_DATAOBJECT;
1511 if (is_bool($countWhat)) {
1512 $whereAddOnly = $countWhat;
1516 $items = $t->table();
1518 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1521 if (!isset($t->_query)) {
1523 "You cannot do run count after you have run fetch()",
1524 DB_DATAOBJECT_ERROR_INVALIDARGS);
1528 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1531 if (!$whereAddOnly && $items) {
1532 $t->_build_condition($items);
1534 $keys = $this->keys();
1536 if (empty($keys[0]) && (!is_string($countWhat) || (strtoupper($countWhat) == 'DISTINCT'))) {
1538 "You cannot do run count without keys - use \$do->count('id'), or use \$do->count('distinct id')';",
1539 DB_DATAOBJECT_ERROR_INVALIDARGS,PEAR_ERROR_DIE);
1543 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->__table) : $this->__table);
1544 $key_col = empty($keys[0]) ? '' : (($quoteIdentifiers ? $DB->quoteIdentifier($keys[0]) : $keys[0]));
1545 $as = ($quoteIdentifiers ? $DB->quoteIdentifier('DATAOBJECT_NUM') : 'DATAOBJECT_NUM');
1547 // support distinct on default keys.
1548 $countWhat = (strtoupper($countWhat) == 'DISTINCT') ?
1549 "DISTINCT {$table}.{$key_col}" : $countWhat;
1551 $countWhat = is_string($countWhat) ? $countWhat : "{$table}.{$key_col}";
1554 "SELECT count({$countWhat}) as $as
1555 FROM $table {$t->_join} {$t->_query['condition']}");
1556 if (PEAR::isError($r)) {
1560 $result = &$_DB_DATAOBJECT['RESULTS'][$t->_DB_resultid];
1561 $l = $result->fetchRow(DB_DATAOBJECT_FETCHMODE_ORDERED);
1562 // free the results - essential on oracle.
1569 * sends raw query to database
1571 * Since _query has to be a private 'non overwriteable method', this is a relay
1573 * @param string $string SQL Query
1575 * @return void or DB_Error
1577 function query($string)
1579 return $this->_query($string);
1584 * an escape wrapper around DB->escapeSimple()
1585 * can be used when adding manual queries or clauses
1587 * $object->query("select * from xyz where abc like '". $object->escape($_GET['name']) . "'");
1589 * @param string $string value to be escaped
1590 * @param bool $likeEscape escapes % and _ as well. - so like queries can be protected.
1594 function escape($string, $likeEscape=false)
1596 global $_DB_DATAOBJECT;
1598 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1599 // mdb2 uses escape...
1600 $dd = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ? 'DB' : $_DB_DATAOBJECT['CONFIG']['db_driver'];
1601 $ret = ($dd == 'DB') ? $DB->escapeSimple($string) : $DB->escape($string);
1603 $ret = str_replace(array('_','%'), array('\_','\%'), $ret);
1609 /* ==================================================== */
1610 /* Major Private Vars */
1611 /* ==================================================== */
1614 * The Database connection dsn (as described in the PEAR DB)
1615 * only used really if you are writing a very simple application/test..
1616 * try not to use this - it is better stored in configuration files..
1621 var $_database_dsn = '';
1624 * The Database connection id (md5 sum of databasedsn)
1629 var $_database_dsn_md5 = '';
1633 * created in __connection
1638 var $_database = '';
1644 * This replaces alot of the private variables
1645 * used to build a query, it is unset after find() is run.
1652 var $_query = array(
1653 'condition' => '', // the WHERE condition
1654 'group_by' => '', // the GROUP BY condition
1655 'order_by' => '', // the ORDER BY condition
1656 'having' => '', // the HAVING condition
1657 'limit_start' => '', // the LIMIT condition
1658 'limit_count' => '', // the LIMIT condition
1659 'data_select' => '*', // the columns to be SELECTed
1666 * Database result id (references global $_DB_DataObject[results]
1674 * ResultFields - on the last call to fetch(), resultfields is sent here,
1675 * so we can clean up the memory.
1680 var $_resultFields = false;
1683 /* ============================================================== */
1684 /* Table definition layer (started of very private but 'came out'*/
1685 /* ============================================================== */
1688 * Autoload or manually load the table definitions
1692 * DB_DataObject::databaseStructure( 'databasename',
1693 * parse_ini_file('mydb.ini',true),
1694 * parse_ini_file('mydb.link.ini',true));
1696 * obviously you dont have to use ini files.. (just return array similar to ini files..)
1698 * It should append to the table structure array
1701 * @param optional string name of database to assign / read
1702 * @param optional array structure of database, and keys
1703 * @param optional array table links
1706 * @return true or PEAR:error on wrong paramenters.. or false if no file exists..
1707 * or the array(tablename => array(column_name=>type)) if called with 1 argument.. (databasename)
1709 function databaseStructure()
1712 global $_DB_DATAOBJECT;
1716 if ($args = func_get_args()) {
1718 if (count($args) == 1) {
1720 // this returns all the tables and their structure..
1721 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1722 $this->debug("Loading Generator as databaseStructure called with args",1);
1725 $x = new DB_DataObject;
1726 $x->_database = $args[0];
1728 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1730 $tables = $DB->getListOf('tables');
1731 class_exists('DB_DataObject_Generator') ? '' :
1732 require_once 'DB/DataObject/Generator.php';
1734 foreach($tables as $table) {
1735 $y = new DB_DataObject_Generator;
1736 $y->fillTableSchema($x->_database,$table);
1738 return $_DB_DATAOBJECT['INI'][$x->_database];
1741 $_DB_DATAOBJECT['INI'][$args[0]] = isset($_DB_DATAOBJECT['INI'][$args[0]]) ?
1742 $_DB_DATAOBJECT['INI'][$args[0]] + $args[1] : $args[1];
1744 if (isset($args[1])) {
1745 $_DB_DATAOBJECT['LINKS'][$args[0]] = isset($_DB_DATAOBJECT['LINKS'][$args[0]]) ?
1746 $_DB_DATAOBJECT['LINKS'][$args[0]] + $args[2] : $args[2];
1755 if (!$this->_database) {
1760 if (!empty($_DB_DATAOBJECT['INI'][$this->_database])) {
1762 // database loaded - but this is table is not available..
1764 empty($_DB_DATAOBJECT['INI'][$this->_database][$this->__table])
1765 && !empty($_DB_DATAOBJECT['CONFIG']['proxy'])
1767 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1768 $this->debug("Loading Generator to fetch Schema",1);
1770 class_exists('DB_DataObject_Generator') ? '' :
1771 require_once 'DB/DataObject/Generator.php';
1774 $x = new DB_DataObject_Generator;
1775 $x->fillTableSchema($this->_database,$this->__table);
1781 if (empty($_DB_DATAOBJECT['CONFIG'])) {
1782 DB_DataObject::_loadConfig();
1785 // if you supply this with arguments, then it will take those
1786 // as the database and links array...
1788 $schemas = isset($_DB_DATAOBJECT['CONFIG']['schema_location']) ?
1789 array("{$_DB_DATAOBJECT['CONFIG']['schema_location']}/{$this->_database}.ini") :
1792 if (isset($_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"])) {
1793 $schemas = is_array($_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"]) ?
1794 $_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"] :
1795 explode(PATH_SEPARATOR,$_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"]);
1800 foreach ($schemas as $ini) {
1801 if (file_exists($ini) && is_file($ini)) {
1802 $_DB_DATAOBJECT['INI'][$this->_database] = parse_ini_file($ini, true);
1803 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1804 if (!is_readable ($ini)) {
1805 $this->debug("ini file is not readable: $ini","databaseStructure",1);
1807 $this->debug("Loaded ini file: $ini","databaseStructure",1);
1811 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1812 $this->debug("Missing ini file: $ini","databaseStructure",1);
1817 // now have we loaded the structure..
1819 if (!empty($_DB_DATAOBJECT['INI'][$this->_database][$this->__table])) {
1822 // - if not try building it..
1823 if (!empty($_DB_DATAOBJECT['CONFIG']['proxy'])) {
1824 class_exists('DB_DataObject_Generator') ? '' :
1825 require_once 'DB/DataObject/Generator.php';
1827 $x = new DB_DataObject_Generator;
1828 $x->fillTableSchema($this->_database,$this->__table);
1829 // should this fail!!!???
1832 $this->debug("Cant find database schema: {$this->_database}/{$this->__table} \n".
1833 "in links file data: " . print_r($_DB_DATAOBJECT['INI'],true),"databaseStructure",5);
1834 // we have to die here!! - it causes chaos if we dont (including looping forever!)
1835 $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);
1845 * Return or assign the name of the current table
1848 * @param string optinal table name to set
1850 * @return string The name of the current table
1852 function tableName()
1854 $args = func_get_args();
1856 $this->__table = $args[0];
1858 return $this->__table;
1862 * Return or assign the name of the current database
1864 * @param string optional database name to set
1866 * @return string The name of the current database
1870 $args = func_get_args();
1872 $this->_database = $args[0];
1874 return $this->_database;
1878 * get/set an associative array of table columns
1881 * @param array key=>type array
1882 * @return array (associative)
1887 // for temporary storage of database fields..
1888 // note this is not declared as we dont want to bloat the print_r output
1889 $args = func_get_args();
1891 $this->_database_fields = $args[0];
1893 if (isset($this->_database_fields)) {
1894 return $this->_database_fields;
1898 global $_DB_DATAOBJECT;
1899 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
1903 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->__table])) {
1904 return $_DB_DATAOBJECT['INI'][$this->_database][$this->__table];
1907 $this->databaseStructure();
1911 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->__table])) {
1912 $ret = $_DB_DATAOBJECT['INI'][$this->_database][$this->__table];
1919 * get/set an array of table primary keys
1921 * set usage: $do->keys('id','code');
1923 * This is defined in the table definition if it gets it wrong,
1924 * or you do not want to use ini tables, you can override this.
1925 * @param string optional set the key
1926 * @param * optional set more keys
1932 // for temporary storage of database fields..
1933 // note this is not declared as we dont want to bloat the print_r output
1934 $args = func_get_args();
1936 $this->_database_keys = $args;
1938 if (isset($this->_database_keys)) {
1939 return $this->_database_keys;
1942 global $_DB_DATAOBJECT;
1943 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
1946 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->__table."__keys"])) {
1947 return array_keys($_DB_DATAOBJECT['INI'][$this->_database][$this->__table."__keys"]);
1949 $this->databaseStructure();
1951 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->__table."__keys"])) {
1952 return array_keys($_DB_DATAOBJECT['INI'][$this->_database][$this->__table."__keys"]);
1957 * get/set an sequence key
1959 * by default it returns the first key from keys()
1960 * set usage: $do->sequenceKey('id',true);
1962 * override this to return array(false,false) if table has no real sequence key.
1964 * @param string optional the key sequence/autoinc. key
1965 * @param boolean optional use native increment. default false
1966 * @param false|string optional native sequence name
1968 * @return array (column,use_native,sequence_name)
1970 function sequenceKey()
1972 global $_DB_DATAOBJECT;
1975 if (!$this->_database) {
1979 if (!isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database])) {
1980 $_DB_DATAOBJECT['SEQUENCE'][$this->_database] = array();
1984 $args = func_get_args();
1986 $args[1] = isset($args[1]) ? $args[1] : false;
1987 $args[2] = isset($args[2]) ? $args[2] : false;
1988 $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] = $args;
1990 if (isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table])) {
1991 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table];
1993 // end call setting (eg. $do->sequenceKeys(a,b,c); )
1998 $keys = $this->keys();
2000 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table]
2001 = array(false,false,false);
2005 $table = isset($_DB_DATAOBJECT['INI'][$this->_database][$this->__table]) ?
2006 $_DB_DATAOBJECT['INI'][$this->_database][$this->__table] : $this->table();
2008 $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'];
2016 if (!empty($_DB_DATAOBJECT['CONFIG']['sequence_'.$this->__table])) {
2017 $usekey = $_DB_DATAOBJECT['CONFIG']['sequence_'.$this->__table];
2018 if (strpos($usekey,':') !== false) {
2019 list($usekey,$seqname) = explode(':',$usekey);
2024 // if the key is not an integer - then it's not a sequence or native
2025 if (empty($table[$usekey]) || !($table[$usekey] & DB_DATAOBJECT_INT)) {
2026 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] = array(false,false,false);
2030 if (!empty($_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'])) {
2031 $ignore = $_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'];
2032 if (is_string($ignore) && (strtoupper($ignore) == 'ALL')) {
2033 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] = array(false,false,$seqname);
2035 if (is_string($ignore)) {
2036 $ignore = $_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'] = explode(',',$ignore);
2038 if (in_array($this->__table,$ignore)) {
2039 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] = array(false,false,$seqname);
2044 $realkeys = $_DB_DATAOBJECT['INI'][$this->_database][$this->__table."__keys"];
2046 // if you are using an old ini file - go back to old behaviour...
2047 if (is_numeric($realkeys[$usekey])) {
2048 $realkeys[$usekey] = 'N';
2051 // multiple unique primary keys without a native sequence...
2052 if (($realkeys[$usekey] == 'K') && (count($keys) > 1)) {
2053 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] = array(false,false,$seqname);
2055 // use native sequence keys...
2056 // technically postgres native here...
2057 // we need to get the new improved tabledata sorted out first.
2059 if ( in_array($dbtype , array('pgsql', 'mysql', 'mysqli', 'mssql', 'ifx')) &&
2060 ($table[$usekey] & DB_DATAOBJECT_INT) &&
2061 isset($realkeys[$usekey]) && ($realkeys[$usekey] == 'N')
2063 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] = array($usekey,true,$seqname);
2065 // if not a native autoinc, and we have not assumed all primary keys are sequence
2066 if (($realkeys[$usekey] != 'N') &&
2067 !empty($_DB_DATAOBJECT['CONFIG']['dont_use_pear_sequences'])) {
2068 return array(false,false,false);
2070 // I assume it's going to try and be a nextval DB sequence.. (not native)
2071 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->__table] = array($usekey,false,$seqname);
2076 /* =========================================================== */
2077 /* Major Private Methods - the core part! */
2078 /* =========================================================== */
2083 * clear the cache values for this class - normally done on insert/update etc.
2088 function _clear_cache()
2090 global $_DB_DATAOBJECT;
2092 $class = strtolower(get_class($this));
2094 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2095 $this->debug("Clearing Cache for ".$class,1);
2098 if (!empty($_DB_DATAOBJECT['CACHE'][$class])) {
2099 unset($_DB_DATAOBJECT['CACHE'][$class]);
2105 * backend wrapper for quoting, as MDB2 and DB do it differently...
2108 * @return string quoted
2111 function _quote($str)
2113 global $_DB_DATAOBJECT;
2114 return (empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ||
2115 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB'))
2116 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->quoteSmart($str)
2117 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->quote($str);
2122 * connects to the database
2125 * TODO: tidy this up - This has grown to support a number of connection options like
2126 * a) dynamic changing of ini file to change which database to connect to
2127 * b) multi data via the table_{$table} = dsn ini option
2128 * c) session based storage.
2131 * @return true | PEAR::error
2135 global $_DB_DATAOBJECT;
2136 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2137 $this->_loadConfig();
2139 // Set database driver for reference
2140 $db_driver = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ?
2141 'DB' : $_DB_DATAOBJECT['CONFIG']['db_driver'];
2143 // is it already connected ?
2144 if ($this->_database_dsn_md5 && !empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2146 // connection is an error...
2147 if (PEAR::isError($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2148 return $this->raiseError(
2149 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->message,
2150 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->code, PEAR_ERROR_DIE
2155 if (empty($this->_database)) {
2156 $this->_database = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
2157 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
2159 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
2160 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
2161 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
2165 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
2166 && is_file($this->_database)) {
2167 $this->_database = basename($this->_database);
2169 if ($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'ibase') {
2170 $this->_database = substr(basename($this->_database), 0, -4);
2174 // theoretically we have a md5, it's listed in connections and it's not an error.
2175 // so everything is ok!
2180 // it's not currently connected!
2181 // try and work out what to use for the dsn !
2183 $options= &$_DB_DATAOBJECT['CONFIG'];
2184 // if the databse dsn dis defined in the object..
2185 $dsn = isset($this->_database_dsn) ? $this->_database_dsn : null;
2188 if (!$this->_database) {
2189 $this->_database = isset($options["table_{$this->__table}"]) ? $options["table_{$this->__table}"] : null;
2191 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2192 $this->debug("Checking for database specific ini ('{$this->_database}') : database_{$this->_database} in options","CONNECT");
2195 if ($this->_database && !empty($options["database_{$this->_database}"])) {
2196 $dsn = $options["database_{$this->_database}"];
2197 } else if (!empty($options['database'])) {
2198 $dsn = $options['database'];
2203 // if still no database...
2205 return $this->raiseError(
2206 "No database name / dsn found anywhere",
2207 DB_DATAOBJECT_ERROR_INVALIDCONFIG, PEAR_ERROR_DIE
2213 if (is_string($dsn)) {
2214 $this->_database_dsn_md5 = md5($dsn);
2216 /// support array based dsn's
2217 $this->_database_dsn_md5 = md5(serialize($dsn));
2220 if (!empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2221 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2222 $this->debug("USING CACHED CONNECTION", "CONNECT",3);
2227 if (!$this->_database) {
2229 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
2230 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
2231 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
2232 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
2234 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
2235 && is_file($this->_database))
2237 $this->_database = basename($this->_database);
2242 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2243 $this->debug("NEW CONNECTION TP DATABASE :" .$this->_database , "CONNECT",3);
2244 /* actualy make a connection */
2245 $this->debug(print_r($dsn,true) ." {$this->_database_dsn_md5}", "CONNECT",3);
2248 // Note this is verbose deliberatly!
2250 if ($db_driver == 'DB') {
2252 /* PEAR DB connect */
2254 // this allows the setings of compatibility on DB
2255 $db_options = PEAR::getStaticProperty('DB','options');
2256 require_once 'DB.php';
2258 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = &DB::connect($dsn,$db_options);
2260 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = &DB::connect($dsn);
2264 /* assumption is MDB2 */
2265 require_once 'MDB2.php';
2266 // this allows the setings of compatibility on MDB2
2267 $db_options = PEAR::getStaticProperty('MDB2','options');
2268 $db_options = is_array($db_options) ? $db_options : array();
2269 $db_options['portability'] = isset($db_options['portability'] )
2270 ? $db_options['portability'] : MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_FIX_CASE;
2271 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = &MDB2::connect($dsn,$db_options);
2276 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2277 $this->debug(serialize($_DB_DATAOBJECT['CONNECTIONS']), "CONNECT",5);
2279 if (PEAR::isError($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2280 $this->debug($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->toString(), "CONNECT FAILED",5);
2281 return $this->raiseError(
2282 "Connect failed, turn on debugging to 5 see why",
2283 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->code, PEAR_ERROR_DIE
2288 if (empty($this->_database)) {
2289 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
2291 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
2292 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
2293 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
2296 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
2297 && is_file($this->_database))
2299 $this->_database = basename($this->_database);
2303 // Oracle need to optimize for portibility - not sure exactly what this does though :)
2304 $c = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2310 * sends query to database - this is the private one that must work
2311 * - internal functions use this rather than $this->query()
2313 * @param string $string
2315 * @return mixed none or PEAR_Error
2317 function _query($string)
2319 global $_DB_DATAOBJECT;
2323 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2325 $options = &$_DB_DATAOBJECT['CONFIG'];
2327 $_DB_driver = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ?
2328 'DB': $_DB_DATAOBJECT['CONFIG']['db_driver'];
2330 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2331 $this->debug($string,$log="QUERY");
2335 if (strtoupper($string) == 'BEGIN') {
2336 if ($_DB_driver == 'DB') {
2337 $DB->autoCommit(false);
2339 $DB->beginTransaction();
2341 // db backend adds begin anyway from now on..
2344 if (strtoupper($string) == 'COMMIT') {
2345 $res = $DB->commit();
2346 if ($_DB_driver == 'DB') {
2347 $DB->autoCommit(true);
2352 if (strtoupper($string) == 'ROLLBACK') {
2354 if ($_DB_driver == 'DB') {
2355 $DB->autoCommit(true);
2361 if (!empty($options['debug_ignore_updates']) &&
2362 (strtolower(substr(trim($string), 0, 6)) != 'select') &&
2363 (strtolower(substr(trim($string), 0, 4)) != 'show') &&
2364 (strtolower(substr(trim($string), 0, 8)) != 'describe')) {
2366 $this->debug('Disabling Update as you are in debug mode');
2367 return $this->raiseError("Disabling Update as you are in debug mode", null) ;
2370 //if (@$_DB_DATAOBJECT['CONFIG']['debug'] > 1) {
2371 // this will only work when PEAR:DB supports it.
2372 //$this->debug($DB->getAll('explain ' .$string,DB_DATAOBJECT_FETCHMODE_ASSOC), $log="sql",2);
2376 $t= explode(' ',microtime());
2377 $_DB_DATAOBJECT['QUERYENDTIME'] = $time = $t[0]+$t[1];
2380 for ($tries = 0;$tries < 3;$tries++) {
2382 if ($_DB_driver == 'DB') {
2384 $result = $DB->query($string);
2386 switch (strtolower(substr(trim($string),0,6))) {
2391 $result = $DB->exec($string);
2395 $result = $DB->query($string);
2400 // see if we got a failure.. - try again a few times..
2401 if (!is_a($result,'PEAR_Error')) {
2404 if ($result->getCode() != -14) { // *DB_ERROR_NODBSELECTED
2405 break; // not a connection error..
2407 sleep(1); // wait before retyring..
2408 $DB->connect($DB->dsn);
2412 if (is_a($result,'PEAR_Error')) {
2413 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2414 $this->debug($result->toString(), "Query Error",1 );
2416 return $this->raiseError($result);
2418 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2419 $t= explode(' ',microtime());
2420 $_DB_DATAOBJECT['QUERYENDTIME'] = $t[0]+$t[1];
2421 $this->debug('QUERY DONE IN '.($t[0]+$t[1]-$time)." seconds", 'query',1);
2423 switch (strtolower(substr(trim($string),0,6))) {
2427 if ($_DB_driver == 'DB') {
2429 return $DB->affectedRows();
2433 if (is_object($result)) {
2434 // lets hope that copying the result object is OK!
2436 $_DB_resultid = $GLOBALS['_DB_DATAOBJECT']['RESULTSEQ']++;
2437 $_DB_DATAOBJECT['RESULTS'][$_DB_resultid] = $result;
2438 $this->_DB_resultid = $_DB_resultid;
2441 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2442 $this->debug(serialize($result), 'RESULT',5);
2444 if (method_exists($result, 'numrows')) {
2445 if ($_DB_driver == 'DB') {
2446 $DB->expectError(DB_ERROR_UNSUPPORTED);
2448 $DB->expectError(MDB2_ERROR_UNSUPPORTED);
2450 $this->N = $result->numrows();
2451 if (is_a($this->N,'PEAR_Error')) {
2459 * Builds the WHERE based on the values of of this object
2461 * @param mixed $keys
2462 * @param array $filter (used by update to only uses keys in this filter list).
2463 * @param array $negative_filter (used by delete to prevent deleting using the keys mentioned..)
2467 function _build_condition($keys, $filter = array(),$negative_filter=array())
2469 global $_DB_DATAOBJECT;
2471 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2473 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
2474 $options = $_DB_DATAOBJECT['CONFIG'];
2476 // if we dont have query vars.. - reset them.
2477 if ($this->_query === false) {
2478 $x = new DB_DataObject;
2479 $this->_query= $x->_query;
2482 foreach($keys as $k => $v) {
2483 // index keys is an indexed array
2484 /* these filter checks are a bit suspicious..
2485 - need to check that update really wants to work this way */
2488 if (!in_array($k, $filter)) {
2492 if ($negative_filter) {
2493 if (in_array($k, $negative_filter)) {
2497 if (!isset($this->$k)) {
2501 $kSql = $quoteIdentifiers
2502 ? ( $DB->quoteIdentifier($this->__table) . '.' . $DB->quoteIdentifier($k) )
2503 : "{$this->__table}.{$k}";
2507 if (is_a($this->$k,'DB_DataObject_Cast')) {
2508 $dbtype = $DB->dsn["phptype"];
2509 $value = $this->$k->toString($v,$DB);
2510 if (PEAR::isError($value)) {
2511 $this->raiseError($value->getMessage() ,DB_DATAOBJECT_ERROR_INVALIDARG);
2514 if ((strtolower($value) === 'null') && !($v & DB_DATAOBJECT_NOTNULL)) {
2515 $this->whereAdd(" $kSql IS NULL");
2518 $this->whereAdd(" $kSql = $value");
2522 if (!isset($options['disable_null_strings']) && (strtolower($this->$k) === 'null') && !($v & DB_DATAOBJECT_NOTNULL)) {
2523 $this->whereAdd(" $kSql IS NULL");
2528 if ($v & DB_DATAOBJECT_STR) {
2529 $this->whereAdd(" $kSql = " . $this->_quote((string) (
2530 ($v & DB_DATAOBJECT_BOOL) ?
2531 // this is thanks to the braindead idea of postgres to
2532 // use t/f for boolean.
2533 (($this->$k === 'f') ? 0 : (int)(bool) $this->$k) :
2538 if (is_numeric($this->$k)) {
2539 $this->whereAdd(" $kSql = {$this->$k}");
2542 /* this is probably an error condition! */
2543 $this->whereAdd(" $kSql = ".intval($this->$k));
2548 * autoload Class relating to a table
2549 * (depreciated - use ::factory)
2551 * @param string $table table
2553 * @return string classname on Success
2555 function staticAutoloadTable($table)
2557 global $_DB_DATAOBJECT;
2558 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2559 DB_DataObject::_loadConfig();
2561 $p = isset($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
2562 $_DB_DATAOBJECT['CONFIG']['class_prefix'] : '';
2563 $class = $p . preg_replace('/[^A-Z0-9]/i','_',ucfirst($table));
2565 $ce = substr(phpversion(),0,1) > 4 ? class_exists($class,false) : class_exists($class);
2566 $class = $ce ? $class : DB_DataObject::_autoloadClass($class);
2572 * classic factory method for loading a table class
2573 * usage: $do = DB_DataObject::factory('person')
2574 * WARNING - this may emit a include error if the file does not exist..
2575 * use @ to silence it (if you are sure it is acceptable)
2576 * eg. $do = @DB_DataObject::factory('person')
2578 * table name can bedatabasename/table
2579 * - and allow modular dataobjects to be written..
2580 * (this also helps proxy creation)
2582 * Experimental Support for Multi-Database factory eg. mydatabase.mytable
2585 * @param string $table tablename (use blank to create a new instance of the same class.)
2587 * @return DataObject|PEAR_Error
2592 function factory($table = '') {
2593 global $_DB_DATAOBJECT;
2596 // multi-database support.. - experimental.
2599 if (strpos( $table,'/') !== false ) {
2600 list($database,$table) = explode('.',$table, 2);
2604 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2605 DB_DataObject::_loadConfig();
2607 // no configuration available for database
2608 if (!empty($database) && empty($_DB_DATAOBJECT['CONFIG']['database_'.$database])) {
2609 return DB_DataObject::raiseError(
2610 "unable to find database_{$database} in Configuration, It is required for factory with database"
2611 , 0, PEAR_ERROR_DIE );
2616 if ($table === '') {
2617 if (is_a($this,'DB_DataObject') && strlen($this->__table)) {
2618 $table = $this->__table;
2620 return DB_DataObject::raiseError(
2621 "factory did not recieve a table name",
2622 DB_DATAOBJECT_ERROR_INVALIDARGS);
2626 // does this need multi db support??
2627 $p = isset($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
2628 $_DB_DATAOBJECT['CONFIG']['class_prefix'] : '';
2629 $class = $p . preg_replace('/[^A-Z0-9]/i','_',ucfirst($table));
2630 $ce = substr(phpversion(),0,1) > 4 ? class_exists($class,false) : class_exists($class);
2632 $class = $ce ? $class : DB_DataObject::_autoloadClass($class);
2634 // proxy = full|light
2635 if (!$class && isset($_DB_DATAOBJECT['CONFIG']['proxy'])) {
2637 DB_DataObject::debug("FAILED TO Autoload $database.$table - using proxy.","FACTORY",1);
2640 $proxyMethod = 'getProxy'.$_DB_DATAOBJECT['CONFIG']['proxy'];
2641 // if you have loaded (some other way) - dont try and load it again..
2642 class_exists('DB_DataObject_Generator') ? '' :
2643 require_once 'DB/DataObject/Generator.php';
2645 $d = new DB_DataObject;
2647 $d->__table = $table;
2648 if (is_a($ret = $d->_connect(), 'PEAR_Error')) {
2652 $x = new DB_DataObject_Generator;
2653 return $x->$proxyMethod( $d->_database, $table);
2657 return DB_DataObject::raiseError(
2658 "factory could not find class $class from $table",
2659 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2662 if (!empty($database)) {
2663 DB_DataObject::debug("Setting database to $database","FACTORY",1);
2664 $ret->database($database);
2671 * @param string $class Class
2673 * @return string classname on Success
2675 function _autoloadClass($class)
2677 global $_DB_DATAOBJECT;
2679 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2680 DB_DataObject::_loadConfig();
2682 $class_prefix = empty($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
2683 '' : $_DB_DATAOBJECT['CONFIG']['class_prefix'];
2685 $table = substr($class,strlen($class_prefix));
2687 // only include the file if it exists - and barf badly if it has parse errors :)
2688 if (!empty($_DB_DATAOBJECT['CONFIG']['proxy']) || empty($_DB_DATAOBJECT['CONFIG']['class_location'])) {
2693 if (strpos($_DB_DATAOBJECT['CONFIG']['class_location'],'%s') !== false) {
2694 $file = sprintf($_DB_DATAOBJECT['CONFIG']['class_location'], preg_replace('/[^A-Z0-9]/i','_',ucfirst($table)));
2696 $file = $_DB_DATAOBJECT['CONFIG']['class_location'].'/'.preg_replace('/[^A-Z0-9]/i','_',ucfirst($table)).".php";
2699 if (!file_exists($file)) {
2701 foreach(explode(PATH_SEPARATOR, ini_get('include_path')) as $p) {
2702 if (file_exists("$p/$file")) {
2709 DB_DataObject::raiseError(
2710 "autoload:Could not find class {$class} using class_location value",
2711 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2719 $ce = substr(phpversion(),0,1) > 4 ? class_exists($class,false) : class_exists($class);
2722 DB_DataObject::raiseError(
2723 "autoload:Could not autoload {$class}",
2724 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2733 * Have the links been loaded?
2734 * if they have it contains a array of those variables.
2737 * @var boolean | array
2739 var $_link_loaded = false;
2742 * Get the links associate array as defined by the links.ini file.
2746 * Should look a bit like
2747 * [local_col_name] => "related_tablename:related_col_name"
2750 * @return array|null
2751 * array = if there are links defined for this table.
2752 * empty array - if there is a links.ini file, but no links on this table
2753 * null - if no links.ini exists for this database (hence try auto_links).
2755 * @see DB_DataObject::getLinks(), DB_DataObject::getLink()
2760 global $_DB_DATAOBJECT;
2761 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2762 $this->_loadConfig();
2764 // have to connect.. -> otherwise things break later.
2767 if (isset($_DB_DATAOBJECT['LINKS'][$this->_database][$this->__table])) {
2768 return $_DB_DATAOBJECT['LINKS'][$this->_database][$this->__table];
2775 // attempt to load links file here..
2777 if (!isset($_DB_DATAOBJECT['LINKS'][$this->_database])) {
2778 $schemas = isset($_DB_DATAOBJECT['CONFIG']['schema_location']) ?
2779 array("{$_DB_DATAOBJECT['CONFIG']['schema_location']}/{$this->_database}.ini") :
2782 if (isset($_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"])) {
2783 $schemas = is_array($_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"]) ?
2784 $_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"] :
2785 explode(PATH_SEPARATOR,$_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"]);
2790 foreach ($schemas as $ini) {
2793 isset($_DB_DATAOBJECT['CONFIG']["links_{$this->_database}"]) ?
2794 $_DB_DATAOBJECT['CONFIG']["links_{$this->_database}"] :
2795 str_replace('.ini','.links.ini',$ini);
2797 if (empty($_DB_DATAOBJECT['LINKS'][$this->_database]) && file_exists($links) && is_file($links)) {
2798 /* not sure why $links = ... here - TODO check if that works */
2799 $_DB_DATAOBJECT['LINKS'][$this->_database] = parse_ini_file($links, true);
2800 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2801 $this->debug("Loaded links.ini file: $links","links",1);
2804 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2805 $this->debug("Missing links.ini file: $links","links",1);
2812 // if there is no link data at all on the file!
2814 if (!isset($_DB_DATAOBJECT['LINKS'][$this->_database])) {
2818 if (isset($_DB_DATAOBJECT['LINKS'][$this->_database][$this->__table])) {
2819 return $_DB_DATAOBJECT['LINKS'][$this->_database][$this->__table];
2825 * load related objects
2827 * There are two ways to use this, one is to set up a <dbname>.links.ini file
2828 * into a static property named <dbname>.links and specifies the table joins,
2829 * the other highly dependent on naming columns 'correctly' :)
2830 * using colname = xxxxx_yyyyyy
2831 * xxxxxx = related table; (yyyyy = user defined..)
2832 * looks up table xxxxx, for value id=$this->xxxxx
2833 * stores it in $this->_xxxxx_yyyyy
2834 * you can change what object vars the links are stored in by
2835 * changeing the format parameter
2838 * @param string format (default _%s) where %s is the table name.
2839 * @author Tim White <tim@cyface.com>
2841 * @return boolean , true on success
2843 function getLinks($format = '_%s')
2846 // get table will load the options.
2847 if ($this->_link_loaded) {
2850 $this->_link_loaded = false;
2851 $cols = $this->table();
2852 $links = $this->links();
2857 foreach($links as $key => $match) {
2858 list($table,$link) = explode(':', $match);
2859 $k = sprintf($format, str_replace('.', '_', $key));
2860 // makes sure that '.' is the end of the key;
2861 if ($p = strpos($key,'.')) {
2862 $key = substr($key, 0, $p);
2865 $this->$k = $this->getLink($key, $table, $link);
2867 if (is_object($this->$k)) {
2871 $this->_link_loaded = $loaded;
2874 // this is the autonaming stuff..
2875 // it sends the column name down to getLink and lets that sort it out..
2876 // if there is a links file then it is not used!
2877 // IT IS DEPRECIATED!!!! - USE
2878 if (!is_null($links)) {
2883 foreach (array_keys($cols) as $key) {
2884 if (!($p = strpos($key, '_'))) {
2887 // does the table exist.
2888 $k =sprintf($format, $key);
2889 $this->$k = $this->getLink($key);
2890 if (is_object($this->$k)) {
2894 $this->_link_loaded = $loaded;
2899 * return name from related object
2901 * There are two ways to use this, one is to set up a <dbname>.links.ini file
2902 * into a static property named <dbname>.links and specifies the table joins,
2903 * the other is highly dependant on naming columns 'correctly' :)
2905 * NOTE: the naming convention is depreciated!!! - use links.ini
2907 * using colname = xxxxx_yyyyyy
2908 * xxxxxx = related table; (yyyyy = user defined..)
2909 * looks up table xxxxx, for value id=$this->xxxxx
2910 * stores it in $this->_xxxxx_yyyyy
2912 * you can also use $this->getLink('thisColumnName','otherTable','otherTableColumnName')
2915 * @param string $row either row or row.xxxxx
2916 * @param string $table name of table to look up value in
2917 * @param string $link name of column in other table to match
2918 * @author Tim White <tim@cyface.com>
2920 * @return mixed object on success
2922 function getLink($row, $table = null, $link = false)
2926 // GUESS THE LINKED TABLE.. (if found - recursevly call self)
2928 if ($table === null) {
2929 $links = $this->links();
2931 if (is_array($links)) {
2934 list($table,$link) = explode(':', $links[$row]);
2935 if ($p = strpos($row,".")) {
2936 $row = substr($row,0,$p);
2938 return $this->getLink($row,$table,$link);
2943 "getLink: $row is not defined as a link (normally this is ok)",
2944 DB_DATAOBJECT_ERROR_NODATA);
2947 return $r;// technically a possible error condition?
2950 // use the old _ method - this shouldnt happen if called via getLinks()
2951 if (!($p = strpos($row, '_'))) {
2955 $table = substr($row, 0, $p);
2956 return $this->getLink($row, $table);
2963 if (!isset($this->$row)) {
2964 $this->raiseError("getLink: row not set $row", DB_DATAOBJECT_ERROR_NODATA);
2968 // check to see if we know anything about this table..
2970 $obj = $this->factory($table);
2972 if (!is_a($obj,'DB_DataObject')) {
2974 "getLink:Could not find class for row $row, table $table",
2975 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2979 if ($obj->get($link, $this->$row)) {
2986 if ($obj->get($this->$row)) {
2995 * IS THIS SUPPORTED/USED ANYMORE????
2996 *return a list of options for a linked table
2998 * This is highly dependant on naming columns 'correctly' :)
2999 * using colname = xxxxx_yyyyyy
3000 * xxxxxx = related table; (yyyyy = user defined..)
3001 * looks up table xxxxx, for value id=$this->xxxxx
3002 * stores it in $this->_xxxxx_yyyyy
3005 * @return array of results (empty array on failure)
3007 function &getLinkArray($row, $table = null)
3012 $links = $this->links();
3014 if (is_array($links)) {
3015 if (!isset($links[$row])) {
3019 list($table,$link) = explode(':',$links[$row]);
3021 if (!($p = strpos($row,'_'))) {
3024 $table = substr($row,0,$p);
3028 $c = $this->factory($table);
3030 if (!is_a($c,'DB_DataObject')) {
3032 "getLinkArray:Could not find class for row $row, table $table",
3033 DB_DATAOBJECT_ERROR_INVALIDCONFIG
3038 // if the user defined method list exists - use it...
3039 if (method_exists($c, 'listFind')) {
3040 $c->listFind($this->id);
3044 while ($c->fetch()) {
3051 * The JOIN condition
3059 * joinAdd - adds another dataobject to this, building a joined query.
3061 * example (requires links.ini to be set up correctly)
3062 * // get all the images for product 24
3063 * $i = new DataObject_Image();
3064 * $pi = new DataObjects_Product_image();
3065 * $pi->product_id = 24; // set the product id to 24
3066 * $i->joinAdd($pi); // add the product_image connectoin
3068 * while ($i->fetch()) {
3071 * // an example with 2 joins
3072 * // get all the images linked with products or productgroups
3073 * $i = new DataObject_Image();
3074 * $pi = new DataObject_Product_image();
3075 * $pgi = new DataObject_Productgroup_image();
3077 * $i->joinAdd($pgi);
3079 * while ($i->fetch()) {
3084 * @param optional $obj object |array the joining object (no value resets the join)
3085 * If you use an array here it should be in the format:
3086 * array('local_column','remotetable:remote_column');
3087 * if remotetable does not have a definition, you should
3088 * use @ to hide the include error message..
3091 * @param optional $joinType string | array
3092 * 'LEFT'|'INNER'|'RIGHT'|'' Inner is default, '' indicates
3093 * just select ... from a,b,c with no join and
3094 * links are added as where items.
3096 * If second Argument is array, it is assumed to be an associative
3097 * array with arguments matching below = eg.
3098 * 'joinType' => 'INNER',
3101 * 'useWhereAsOn' => false,
3103 * @param optional $joinAs string if you want to select the table as anther name
3104 * useful when you want to select multiple columsn
3105 * from a secondary table.
3107 * @param optional $joinCol string The column on This objects table to match (needed
3108 * if this table links to the child object in
3109 * multiple places eg.
3110 * user->friend (is a link to another user)
3111 * user->mother (is a link to another user..)
3113 * optional 'useWhereAsOn' bool default false;
3114 * convert the where argments from the object being added
3115 * into ON arguments.
3120 * @author Stijn de Reede <sjr@gmx.co.uk>
3122 function joinAdd($obj = false, $joinType='INNER', $joinAs=false, $joinCol=false)
3124 global $_DB_DATAOBJECT;
3125 if ($obj === false) {
3130 //echo '<PRE>'; print_r(func_get_args());
3131 $useWhereAsOn = false;
3132 // support for 2nd argument as an array of options
3133 if (is_array($joinType)) {
3134 // new options can now go in here... (dont forget to document them)
3135 $useWhereAsOn = !empty($joinType['useWhereAsOn']);
3136 $joinCol = isset($joinType['joinCol']) ? $joinType['joinCol'] : $joinCol;
3137 $joinAs = isset($joinType['joinAs']) ? $joinType['joinAs'] : $joinAs;
3138 $joinType = isset($joinType['joinType']) ? $joinType['joinType'] : 'INNER';
3140 // support for array as first argument
3141 // this assumes that you dont have a links.ini for the specified table.
3142 // and it doesnt exist as am extended dataobject!! - experimental.
3144 $ofield = false; // object field
3145 $tfield = false; // this field
3147 if (is_array($obj)) {
3149 list($toTable,$ofield) = explode(':',$obj[1]);
3150 $obj = DB_DataObject::factory($toTable);
3152 if (!$obj || is_a($obj,'PEAR_Error')) {
3153 $obj = new DB_DataObject;
3154 $obj->__table = $toTable;
3157 // set the table items to nothing.. - eg. do not try and match
3158 // things in the child table...???
3162 if (!is_object($obj) || !is_a($obj,'DB_DataObject')) {
3163 return $this->raiseError("joinAdd: called without an object", DB_DATAOBJECT_ERROR_NODATA,PEAR_ERROR_DIE);
3165 /* make sure $this->_database is set. */
3167 $DB = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
3170 /// CHANGED 26 JUN 2009 - we prefer links from our local table over the remote one.
3172 /* otherwise see if there are any links from this table to the obj. */
3173 //print_r($this->links());
3174 if (($ofield === false) && ($links = $this->links())) {
3175 foreach ($links as $k => $v) {
3176 /* link contains {this column} = {linked table}:{linked column} */
3177 $ar = explode(':', $v);
3178 // Feature Request #4266 - Allow joins with multiple keys
3179 if (strpos($k, ',') !== false) {
3180 $k = explode(',', $k);
3182 if (strpos($ar[1], ',') !== false) {
3183 $ar[1] = explode(',', $ar[1]);
3186 if ($ar[0] == $obj->__table) {
3187 if ($joinCol !== false) {
3188 if ($k == $joinCol) {
3203 /* look up the links for obj table */
3204 //print_r($obj->links());
3205 if (!$ofield && ($olinks = $obj->links())) {
3207 foreach ($olinks as $k => $v) {
3208 /* link contains {this column} = {linked table}:{linked column} */
3209 $ar = explode(':', $v);
3211 // Feature Request #4266 - Allow joins with multiple keys
3213 $links_key_array = strpos($k,',');
3214 if ($links_key_array !== false) {
3215 $k = explode(',', $k);
3218 $ar_array = strpos($ar[1],',');
3219 if ($ar_array !== false) {
3220 $ar[1] = explode(',', $ar[1]);
3223 if ($ar[0] == $this->__table) {
3225 // you have explictly specified the column
3226 // and the col is listed here..
3227 // not sure if 1:1 table could cause probs here..
3229 if ($joinCol !== false) {
3231 "joinAdd: You cannot target a join column in the " .
3232 "'link from' table ({$obj->__table}). " .
3233 "Either remove the fourth argument to joinAdd() ".
3234 "({$joinCol}), or alter your links.ini file.",
3235 DB_DATAOBJECT_ERROR_NODATA);
3246 // finally if these two table have column names that match do a join by default on them
3248 if (($ofield === false) && $joinCol) {
3253 /* did I find a conneciton between them? */
3255 if ($ofield === false) {
3257 "joinAdd: {$obj->__table} has no link with {$this->__table}",
3258 DB_DATAOBJECT_ERROR_NODATA);
3261 $joinType = strtoupper($joinType);
3263 // we default to joining as the same name (this is remvoed later..)
3265 if ($joinAs === false) {
3266 $joinAs = $obj->__table;
3269 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
3270 $options = $_DB_DATAOBJECT['CONFIG'];
3272 // not sure how portable adding database prefixes is..
3273 $objTable = $quoteIdentifiers ?
3274 $DB->quoteIdentifier($obj->__table) :
3278 if (strlen($obj->_database) && in_array($DB->dsn['phptype'],array('mysql','mysqli'))) {
3279 $dbPrefix = ($quoteIdentifiers
3280 ? $DB->quoteIdentifier($obj->_database)
3281 : $obj->_database) . '.';
3284 // if they are the same, then dont add a prefix...
3285 if ($obj->_database == $this->_database) {
3288 // as far as we know only mysql supports database prefixes..
3289 // prefixing the database name is now the default behaviour,
3290 // as it enables joining mutiple columns from multiple databases...
3292 // prefix database (quoted if neccessary..)
3293 $objTable = $dbPrefix . $objTable;
3297 // if obj only a dataobject - eg. no extended class has been defined..
3298 // it obvioulsy cant work out what child elements might exist...
3299 // until we get on the fly querying of tables..
3300 // note: we have already checked that it is_a(db_dataobject earlier)
3301 if ( strtolower(get_class($obj)) != 'db_dataobject') {
3303 // now add where conditions for anything that is set in the object
3307 $items = $obj->table();
3308 // will return an array if no items..
3310 // only fail if we where expecting it to work (eg. not joined on a array)
3314 "joinAdd: No table definition for {$obj->__table}",
3315 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
3319 foreach($items as $k => $v) {
3320 if (!isset($obj->$k)) {
3324 $kSql = ($quoteIdentifiers ? $DB->quoteIdentifier($k) : $k);
3327 if ($v & DB_DATAOBJECT_STR) {
3328 $obj->whereAdd("{$joinAs}.{$kSql} = " . $this->_quote((string) (
3329 ($v & DB_DATAOBJECT_BOOL) ?
3330 // this is thanks to the braindead idea of postgres to
3331 // use t/f for boolean.
3332 (($obj->$k === 'f') ? 0 : (int)(bool) $obj->$k) :
3337 if (is_numeric($obj->$k)) {
3338 $obj->whereAdd("{$joinAs}.{$kSql} = {$obj->$k}");
3342 if (is_a($obj->$k,'DB_DataObject_Cast')) {
3343 $value = $obj->$k->toString($v,$DB);
3344 if (PEAR::isError($value)) {
3345 $this->raiseError($value->getMessage() ,DB_DATAOBJECT_ERROR_INVALIDARG);
3348 if (!isset($options['disable_null_strings']) && strtolower($value) === 'null') {
3349 $obj->whereAdd("{$joinAs}.{$kSql} IS NULL");
3352 $obj->whereAdd("{$joinAs}.{$kSql} = $value");
3358 /* this is probably an error condition! */
3359 $obj->whereAdd("{$joinAs}.{$kSql} = 0");
3361 if ($this->_query === false) {
3363 "joinAdd can not be run from a object that has had a query run on it,
3364 clone the object or create a new one and use setFrom()",
3365 DB_DATAOBJECT_ERROR_INVALIDARGS);
3370 // and finally merge the whereAdd from the child..
3371 if ($obj->_query['condition']) {
3372 $cond = preg_replace('/^\sWHERE/i','',$obj->_query['condition']);
3374 if (!$useWhereAsOn) {
3375 $this->whereAdd($cond);
3382 // nested (join of joined objects..)
3385 // postgres allows nested queries, with ()'s
3386 // not sure what the results are with other databases..
3387 // may be unpredictable..
3388 if (in_array($DB->dsn["phptype"],array('pgsql'))) {
3389 $objTable = "($objTable {$obj->_join})";
3391 $appendJoin = $obj->_join;
3397 // add the joinee object's conditions to the ON clause instead of the WHERE clause
3398 if ($useWhereAsOn && strlen($cond)) {
3399 $appendJoin = ' AND ' . $cond . ' ' . $appendJoin;
3404 $table = $this->__table;
3406 if ($quoteIdentifiers) {
3407 $joinAs = $DB->quoteIdentifier($joinAs);
3408 $table = $DB->quoteIdentifier($table);
3409 $ofield = (is_array($ofield)) ? array_map(array($DB, 'quoteIdentifier'), $ofield) : $DB->quoteIdentifier($ofield);
3410 $tfield = (is_array($tfield)) ? array_map(array($DB, 'quoteIdentifier'), $tfield) : $DB->quoteIdentifier($tfield);
3412 // add database prefix if they are different databases
3416 $addJoinAs = ($quoteIdentifiers ? $DB->quoteIdentifier($obj->__table) : $obj->__table) != $joinAs;
3418 // join table a AS b - is only supported by a few databases and is probably not needed
3419 // , however since it makes the whole Statement alot clearer we are leaving it in
3420 // for those databases.
3421 $fullJoinAs = in_array($DB->dsn["phptype"],array('mysql','mysqli','pgsql')) ? "AS {$joinAs}" : $joinAs;
3424 $joinAs = $dbPrefix . $joinAs;
3428 switch ($joinType) {
3431 case 'RIGHT': // others??? .. cross, left outer, right outer, natural..?
3433 // Feature Request #4266 - Allow joins with multiple keys
3434 $jadd = "\n {$joinType} JOIN {$objTable} {$fullJoinAs}";
3435 //$this->_join .= "\n {$joinType} JOIN {$objTable} {$fullJoinAs}";
3436 if (is_array($ofield)) {
3437 $key_count = count($ofield);
3438 for($i = 0; $i < $key_count; $i++) {
3440 $jadd .= " ON ({$joinAs}.{$ofield[$i]}={$table}.{$tfield[$i]}) ";
3443 $jadd .= " AND {$joinAs}.{$ofield[$i]}={$table}.{$tfield[$i]} ";
3446 $jadd .= ' ' . $appendJoin . ' ';
3448 $jadd .= " ON ({$joinAs}.{$ofield}={$table}.{$tfield}) {$appendJoin} ";
3450 // jadd avaliable for debugging join build.
3452 $this->_join .= $jadd;
3455 case '': // this is just a standard multitable select..
3456 $this->_join .= "\n , {$objTable} {$fullJoinAs} {$appendJoin}";
3457 $this->whereAdd("{$joinAs}.{$ofield}={$table}.{$tfield}");
3466 * Copies items that are in the table definitions from an
3467 * array or object into the current object
3468 * will not override key values.
3471 * @param array | object $from
3472 * @param string $format eg. map xxxx_name to $object->name using 'xxxx_%s' (defaults to %s - eg. name -> $object->name
3473 * @param boolean $skipEmpty (dont assign empty values if a column is empty (eg. '' / 0 etc...)
3475 * @return true on success or array of key=>setValue error message
3477 function setFrom($from, $format = '%s', $skipEmpty=false)
3479 global $_DB_DATAOBJECT;
3480 $keys = $this->keys();
3481 $items = $this->table();
3484 "setFrom:Could not find table definition for {$this->__table}",
3485 DB_DATAOBJECT_ERROR_INVALIDCONFIG);
3488 $overload_return = array();
3489 foreach (array_keys($items) as $k) {
3490 if (in_array($k,$keys)) {
3491 continue; // dont overwrite keys
3494 continue; // ignore empty keys!!! what
3496 if (is_object($from) && isset($from->{sprintf($format,$k)})) {
3497 $kk = (strtolower($k) == 'from') ? '_from' : $k;
3498 if (method_exists($this,'set'.$kk)) {
3499 $ret = $this->{'set'.$kk}($from->{sprintf($format,$k)});
3500 if (is_string($ret)) {
3501 $overload_return[$k] = $ret;
3505 $this->$k = $from->{sprintf($format,$k)};
3509 if (is_object($from)) {
3513 if (empty($from[sprintf($format,$k)]) && $skipEmpty) {
3517 if (!isset($from[sprintf($format,$k)])) {
3521 $kk = (strtolower($k) == 'from') ? '_from' : $k;
3522 if (method_exists($this,'set'. $kk)) {
3523 $ret = $this->{'set'.$kk}($from[sprintf($format,$k)]);
3524 if (is_string($ret)) {
3525 $overload_return[$k] = $ret;
3529 if (is_object($from[sprintf($format,$k)])) {
3532 if (is_array($from[sprintf($format,$k)])) {
3535 $ret = $this->fromValue($k,$from[sprintf($format,$k)]);
3536 if ($ret !== true) {
3537 $overload_return[$k] = 'Not A Valid Value';
3539 //$this->$k = $from[sprintf($format,$k)];
3541 if ($overload_return) {
3542 return $overload_return;
3548 * Returns an associative array from the current data
3549 * (kind of oblivates the idea behind DataObjects, but
3550 * is usefull if you use it with things like QuickForms.
3552 * you can use the format to return things like user[key]
3553 * by sending it $object->toArray('user[%s]')
3555 * will also return links converted to arrays.
3557 * @param string sprintf format for array
3558 * @param bool empty only return elemnts that have a value set.
3561 * @return array of key => value for row
3564 function toArray($format = '%s', $hideEmpty = false)
3566 global $_DB_DATAOBJECT;
3568 $rf = ($this->_resultFields !== false) ? $this->_resultFields :
3569 (isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]) ? $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid] : false);
3570 $ar = ($rf !== false) ?
3571 array_merge($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid],$this->table()) :
3574 foreach($ar as $k=>$v) {
3576 if (!isset($this->$k)) {
3578 $ret[sprintf($format,$k)] = '';
3582 // call the overloaded getXXXX() method. - except getLink and getLinks
3583 if (method_exists($this,'get'.$k) && !in_array(strtolower($k),array('links','link'))) {
3584 $ret[sprintf($format,$k)] = $this->{'get'.$k}();
3587 // should this call toValue() ???
3588 $ret[sprintf($format,$k)] = $this->$k;
3590 if (!$this->_link_loaded) {
3593 foreach($this->_link_loaded as $k) {
3594 $ret[sprintf($format,$k)] = $this->$k->toArray();
3602 * validate the values of the object (usually prior to inserting/updating..)
3604 * Note: This was always intended as a simple validation routine.
3605 * It lacks understanding of field length, whether you are inserting or updating (and hence null key values)
3607 * This should be moved to another class: DB_DataObject_Validate
3608 * FEEL FREE TO SEND ME YOUR VERSION FOR CONSIDERATION!!!
3611 * if (is_array($ret = $obj->validate())) { ... there are problems with the data ... }
3614 * - defaults to only testing strings/numbers if numbers or strings are the correct type and null values are correct
3615 * - validate Column methods : "validate{ROWNAME}()" are called if they are defined.
3616 * These methods should return
3617 * true = everything ok
3618 * false|object = something is wrong!
3620 * - This method loads and uses the PEAR Validate Class.
3624 * @return array of validation results (where key=>value, value=false|object if it failed) or true (if they all succeeded)
3628 global $_DB_DATAOBJECT;
3629 require_once 'Validate.php';
3630 $table = $this->table();
3632 $seq = $this->sequenceKey();
3633 $options = $_DB_DATAOBJECT['CONFIG'];
3634 foreach($table as $key => $val) {
3637 // call user defined validation always...
3638 $method = "Validate" . ucfirst($key);
3639 if (method_exists($this, $method)) {
3640 $ret[$key] = $this->$method();
3644 // if not null - and it's not set.......
3646 if (!isset($this->$key) && ($val & DB_DATAOBJECT_NOTNULL)) {
3647 // dont check empty sequence key values..
3648 if (($key == $seq[0]) && ($seq[1] == true)) {
3656 if (!isset($options['disable_null_strings']) && is_string($this->$key) && (strtolower($this->$key) == 'null')) {
3657 if ($val & DB_DATAOBJECT_NOTNULL) {
3658 $this->debug("'null' field used for '$key', but it is defined as NOT NULL", 'VALIDATION', 4);
3665 // ignore things that are not set. ?
3667 if (!isset($this->$key)) {
3671 // if the string is empty.. assume it is ok..
3672 if (!is_object($this->$key) && !is_array($this->$key) && !strlen((string) $this->$key)) {
3676 // dont try and validate cast objects - assume they are problably ok..
3677 if (is_object($this->$key) && is_a($this->$key,'DB_DataObject_Cast')) {
3680 // at this point if you have set something to an object, and it's not expected
3681 // the Validate will probably break!!... - rightly so! (your design is broken,
3682 // so issuing a runtime error like PEAR_Error is probably not appropriate..
3685 // todo: date time.....
3686 case ($val & DB_DATAOBJECT_STR):
3687 $ret[$key] = Validate::string($this->$key, VALIDATE_PUNCTUATION . VALIDATE_NAME);
3689 case ($val & DB_DATAOBJECT_INT):
3690 $ret[$key] = Validate::number($this->$key, array('decimal'=>'.'));
3694 // if any of the results are false or an object (eg. PEAR_Error).. then return the array..
3695 foreach ($ret as $key => $val) {
3696 if ($val !== true) {
3700 return true; // everything is OK.
3704 * Gets the DB object related to an object - so you can use funky peardb stuf with it :)
3707 * @return object The DB connection
3709 function &getDatabaseConnection()
3711 global $_DB_DATAOBJECT;
3713 if (($e = $this->_connect()) !== true) {
3716 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
3720 return $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
3725 * Gets the DB result object related to the objects active query
3726 * - so you can use funky pear stuff with it - like pager for example.. :)
3729 * @return object The DB result object
3732 function &getDatabaseResult()
3734 global $_DB_DATAOBJECT;
3736 if (!isset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
3740 return $_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid];
3744 * Overload Extension support
3745 * - enables setCOLNAME/getCOLNAME
3746 * if you define a set/get method for the item it will be called.
3747 * otherwise it will just return/set the value.
3748 * NOTE this currently means that a few Names are NO-NO's
3749 * eg. links,link,linksarray, from, Databaseconnection,databaseresult
3752 * - set is automatically called by setFrom.
3753 * - get is automatically called by toArray()
3755 * setters return true on success. = strings on failure
3756 * getters return the value!
3758 * this fires off trigger_error - if any problems.. pear_error,
3759 * has problems with 4.3.2RC2 here
3767 function _call($method,$params,&$return) {
3769 //$this->debug("ATTEMPTING OVERLOAD? $method");
3770 // ignore constructors : - mm
3771 if (strtolower($method) == strtolower(get_class($this))) {
3774 $type = strtolower(substr($method,0,3));
3775 $class = get_class($this);
3776 if (($type != 'set') && ($type != 'get')) {
3782 // deal with naming conflick of setFrom = this is messy ATM!
3784 if (strtolower($method) == 'set_from') {
3785 $return = $this->toValue('from',isset($params[0]) ? $params[0] : null);
3789 $element = substr($method,3);
3791 // dont you just love php's case insensitivity!!!!
3793 $array = array_keys(get_class_vars($class));
3794 /* php5 version which segfaults on 5.0.3 */
3795 if (class_exists('ReflectionClass')) {
3796 $reflection = new ReflectionClass($class);
3797 $array = array_keys($reflection->getdefaultProperties());
3800 if (!in_array($element,$array)) {
3802 foreach($array as $k) {
3803 $case[strtolower($k)] = $k;
3805 if ((substr(phpversion(),0,1) == 5) && isset($case[strtolower($element)])) {
3806 trigger_error("PHP5 set/get calls should match the case of the variable",E_USER_WARNING);
3807 $element = strtolower($element);
3810 // does it really exist?
3811 if (!isset($case[$element])) {
3814 // use the mundged case
3815 $element = $case[$element]; // real case !
3819 if ($type == 'get') {
3820 $return = $this->toValue($element,isset($params[0]) ? $params[0] : null);
3825 $return = $this->fromValue($element, $params[0]);
3834 * standard set* implementation.
3836 * takes data and uses it to set dates/strings etc.
3837 * normally called from __call..
3840 * date = using (standard time format, or unixtimestamp).... so you could create a method :
3841 * function setLastread($string) { $this->fromValue('lastread',strtotime($string)); }
3843 * time = using strtotime
3844 * datetime = using same as date - accepts iso standard or unixtimestamp.
3845 * string = typecast only..
3847 * TODO: add formater:: eg. d/m/Y for date! ???
3849 * @param string column of database
3850 * @param mixed value to assign
3852 * @return true| false (False on error)
3854 * @see DB_DataObject::_call
3858 function fromValue($col,$value)
3860 global $_DB_DATAOBJECT;
3861 $options = $_DB_DATAOBJECT['CONFIG'];
3862 $cols = $this->table();
3863 // dont know anything about this col..
3864 if (!isset($cols[$col])) {
3865 $this->$col = $value;
3868 //echo "FROM VALUE $col, {$cols[$col]}, $value\n";
3870 // set to null and column is can be null...
3871 case (!isset($options['disable_null_strings']) && (strtolower($value) == 'null') && (!($cols[$col] & DB_DATAOBJECT_NOTNULL))):
3872 case (is_object($value) && is_a($value,'DB_DataObject_Cast')):
3873 $this->$col = $value;
3876 // fail on setting null on a not null field..
3877 case (!isset($options['disable_null_strings']) && (strtolower($value) == 'null') && ($cols[$col] & DB_DATAOBJECT_NOTNULL)):
3880 case (($cols[$col] & DB_DATAOBJECT_DATE) && ($cols[$col] & DB_DATAOBJECT_TIME)):
3881 // empty values get set to '' (which is inserted/updated as NULl
3886 if (is_numeric($value)) {
3887 $this->$col = date('Y-m-d H:i:s', $value);
3891 // eak... - no way to validate date time otherwise...
3892 $this->$col = (string) $value;
3895 case ($cols[$col] & DB_DATAOBJECT_DATE):
3896 // empty values get set to '' (which is inserted/updated as NULl
3903 if (is_numeric($value)) {
3904 $this->$col = date('Y-m-d',$value);
3909 require_once 'Date.php';
3910 $x = new Date($value);
3911 $this->$col = $x->format("%Y-%m-%d");
3914 case ($cols[$col] & DB_DATAOBJECT_TIME):
3915 // empty values get set to '' (which is inserted/updated as NULl
3920 $guess = strtotime($value);
3922 $this->$col = date('H:i:s', $guess);
3923 return $return = true;
3925 // otherwise an error in type...
3928 case ($cols[$col] & DB_DATAOBJECT_STR):
3930 $this->$col = (string) $value;
3933 // todo : floats numerics and ints...
3935 $this->$col = $value;
3943 * standard get* implementation.
3946 * supported formaters:
3947 * date/time : %d/%m/%Y (eg. php strftime) or pear::Date
3948 * numbers : %02d (eg. sprintf)
3949 * NOTE you will get unexpected results with times like 0000-00-00 !!!
3953 * @param string column of database
3954 * @param format foramt
3956 * @return true Description
3958 * @see DB_DataObject::_call(),strftime(),Date::format()
3960 function toValue($col,$format = null)
3962 if (is_null($format)) {
3965 $cols = $this->table();
3967 case (($cols[$col] & DB_DATAOBJECT_DATE) && ($cols[$col] & DB_DATAOBJECT_TIME)):
3971 $guess = strtotime($this->$col);
3973 return strftime($format, $guess);
3975 // eak... - no way to validate date time otherwise...
3977 case ($cols[$col] & DB_DATAOBJECT_DATE):
3981 $guess = strtotime($this->$col);
3983 return strftime($format,$guess);
3986 require_once 'Date.php';
3987 $x = new Date($this->$col);
3988 return $x->format($format);
3990 case ($cols[$col] & DB_DATAOBJECT_TIME):
3994 $guess = strtotime($this->$col);
3996 return strftime($format, $guess);
3998 // otherwise an error in type...
4001 case ($cols[$col] & DB_DATAOBJECT_MYSQLTIMESTAMP):
4005 require_once 'Date.php';
4007 $x = new Date($this->$col);
4009 return $x->format($format);
4012 case ($cols[$col] & DB_DATAOBJECT_BOOL):
4014 if ($cols[$col] & DB_DATAOBJECT_STR) {
4016 return ($this->$col === 't');
4018 return (bool) $this->$col;
4022 return sprintf($format,$this->col);
4029 /* ----------------------- Debugger ------------------ */
4032 * Debugger. - use this in your extended classes to output debugging information.
4034 * Uses DB_DataObject::DebugLevel(x) to turn it on
4036 * @param string $message - message to output
4037 * @param string $logtype - bold at start
4038 * @param string $level - output level
4042 function debug($message, $logtype = 0, $level = 1)
4044 global $_DB_DATAOBJECT;
4046 if (empty($_DB_DATAOBJECT['CONFIG']['debug']) ||
4047 (is_numeric($_DB_DATAOBJECT['CONFIG']['debug']) && $_DB_DATAOBJECT['CONFIG']['debug'] < $level)) {
4050 // this is a bit flaky due to php's wonderfull class passing around crap..
4051 // but it's about as good as it gets..
4052 $class = (isset($this) && is_a($this,'DB_DataObject')) ? get_class($this) : 'DB_DataObject';
4054 if (!is_string($message)) {
4055 $message = print_r($message,true);
4057 if (!is_numeric( $_DB_DATAOBJECT['CONFIG']['debug']) && is_callable( $_DB_DATAOBJECT['CONFIG']['debug'])) {
4058 return call_user_func($_DB_DATAOBJECT['CONFIG']['debug'], $class, $message, $logtype, $level);
4061 if (!ini_get('html_errors')) {
4062 echo "$class : $logtype : $message\n";
4066 if (!is_string($message)) {
4067 $message = print_r($message,true);
4069 $colorize = ($logtype == 'ERROR') ? '<font color="red">' : '<font>';
4070 echo "<code>{$colorize}<B>$class: $logtype:</B> ". nl2br(htmlspecialchars($message)) . "</font></code><BR>\n";
4074 * sets and returns debug level
4075 * eg. DB_DataObject::debugLevel(4);
4077 * @param int $v level
4081 function debugLevel($v = null)
4083 global $_DB_DATAOBJECT;
4084 if (empty($_DB_DATAOBJECT['CONFIG'])) {
4085 DB_DataObject::_loadConfig();
4088 $r = isset($_DB_DATAOBJECT['CONFIG']['debug']) ? $_DB_DATAOBJECT['CONFIG']['debug'] : 0;
4089 $_DB_DATAOBJECT['CONFIG']['debug'] = $v;
4092 return isset($_DB_DATAOBJECT['CONFIG']['debug']) ? $_DB_DATAOBJECT['CONFIG']['debug'] : 0;
4096 * Last Error that has occured
4097 * - use $this->_lastError or
4098 * $last_error = &PEAR::getStaticProperty('DB_DataObject','lastError');
4101 * @var object PEAR_Error (or false)
4103 var $_lastError = false;
4106 * Default error handling is to create a pear error, but never return it.
4107 * if you need to handle errors you should look at setting the PEAR_Error callback
4108 * this is due to the fact it would wreck havoc on the internal methods!
4110 * @param int $message message
4111 * @param int $type type
4112 * @param int $behaviour behaviour (die or continue!);
4114 * @return error object
4116 function raiseError($message, $type = null, $behaviour = null)
4118 global $_DB_DATAOBJECT;
4120 if ($behaviour == PEAR_ERROR_DIE && !empty($_DB_DATAOBJECT['CONFIG']['dont_die'])) {
4123 $error = &PEAR::getStaticProperty('DB_DataObject','lastError');
4125 // this will never work totally with PHP's object model.
4126 // as this is passed on static calls (like staticGet in our case)
4128 if (isset($this) && is_object($this) && is_subclass_of($this,'db_dataobject')) {
4129 $this->_lastError = $error;
4132 $_DB_DATAOBJECT['LASTERROR'] = $error;
4134 // no checks for production here?....... - we log errors before we throw them.
4135 DB_DataObject::debug($message,'ERROR',1);
4138 if (PEAR::isError($message)) {
4141 require_once 'DB/DataObject/Error.php';
4142 $error = PEAR::raiseError($message, $type, $behaviour,
4143 $opts=null, $userinfo=null, 'DB_DataObject_Error'
4151 * Define the global $_DB_DATAOBJECT['CONFIG'] as an alias to PEAR::getStaticProperty('DB_DataObject','options');
4153 * After Profiling DB_DataObject, I discoved that the debug calls where taking
4154 * considerable time (well 0.1 ms), so this should stop those calls happening. as
4155 * all calls to debug are wrapped with direct variable queries rather than actually calling the funciton
4156 * THIS STILL NEEDS FURTHER INVESTIGATION
4159 * @return object an error object
4161 function _loadConfig()
4163 global $_DB_DATAOBJECT;
4165 $_DB_DATAOBJECT['CONFIG'] = &PEAR::getStaticProperty('DB_DataObject','options');
4170 * Free global arrays associated with this object.
4178 global $_DB_DATAOBJECT;
4180 if (isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
4181 unset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]);
4183 if (isset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
4184 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
4186 // clear the staticGet cache as well.
4187 $this->_clear_cache();
4188 // this is a huge bug in DB!
4189 if (isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
4190 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->num_rows = array();
4196 /* ---- LEGACY BC METHODS - NOT DOCUMENTED - See Documentation on New Methods. ---*/
4198 function _get_table() { return $this->table(); }
4199 function _get_keys() { return $this->keys(); }
4205 // technially 4.3.2RC1 was broken!!
4206 // looks like 4.3.3 may have problems too....
4207 if (!defined('DB_DATAOBJECT_NO_OVERLOAD')) {
4209 if ((phpversion() != '4.3.2-RC1') && (version_compare( phpversion(), "4.3.1") > 0)) {
4210 if (version_compare( phpversion(), "5") < 0) {
4211 overload('DB_DataObject');
4213 $GLOBALS['_DB_DATAOBJECT']['OVERLOADED'] = true;