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 336751 2015-05-12 04:39:50Z 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 * ===========================================================================
35 * - we use getStaticProperty from PEAR pretty extensively (cant remove it ATM)
38 require_once 'PEAR.php';
41 * We are duping fetchmode constants to be compatible with
44 define('DB_DATAOBJECT_FETCHMODE_ORDERED', 1);
45 define('DB_DATAOBJECT_FETCHMODE_ASSOC', 2);
49 * these are constants for the get_table array
50 * user to determine what type of escaping is required around the object vars.
52 define('DB_DATAOBJECT_INT', 1); // does not require ''
53 define('DB_DATAOBJECT_STR', 2); // requires ''
55 define('DB_DATAOBJECT_DATE', 4); // is date #TODO
56 define('DB_DATAOBJECT_TIME', 8); // is time #TODO
57 define('DB_DATAOBJECT_BOOL', 16); // is boolean #TODO
58 define('DB_DATAOBJECT_TXT', 32); // is long text #TODO
59 define('DB_DATAOBJECT_BLOB', 64); // is blob type
62 define('DB_DATAOBJECT_NOTNULL', 128); // not null col.
63 define('DB_DATAOBJECT_MYSQLTIMESTAMP', 256); // mysql timestamps (ignored by update/insert)
65 * Define this before you include DataObjects.php to disable overload - if it segfaults due to Zend optimizer..
67 //define('DB_DATAOBJECT_NO_OVERLOAD',true)
71 * Theses are the standard error codes, most methods will fail silently - and return false
72 * to access the error message either use $table->_lastError
73 * or $last_error = PEAR::getStaticProperty('DB_DataObject','lastError');
74 * the code is $last_error->code, and the message is $last_error->message (a standard PEAR error)
77 define('DB_DATAOBJECT_ERROR_INVALIDARGS', -1); // wrong args to function
78 define('DB_DATAOBJECT_ERROR_NODATA', -2); // no data available
79 define('DB_DATAOBJECT_ERROR_INVALIDCONFIG', -3); // something wrong with the config
80 define('DB_DATAOBJECT_ERROR_NOCLASS', -4); // no class exists
81 define('DB_DATAOBJECT_ERROR_INVALID_CALL', -7); // overlad getter/setter failure
84 * Used in methods like delete() and count() to specify that the method should
85 * build the condition only out of the whereAdd's and not the object parameters.
87 define('DB_DATAOBJECT_WHEREADD_ONLY', true);
91 * storage for connection and result objects,
92 * it is done this way so that print_r()'ing the is smaller, and
93 * it reduces the memory size of the object.
94 * -- future versions may use $this->_connection = & PEAR object..
95 * although will need speed tests to see how this affects it.
96 * - includes sub arrays
97 * - connections = md5 sum mapp to pear db object
98 * - results = [id] => map to pear db object
99 * - resultseq = sequence id for results & results field
100 * - resultfields = [id] => list of fields return from query (for use with toArray())
101 * - ini = mapping of database to ini file results
102 * - links = mapping of database to links file
103 * - lasterror = pear error objects for last error event.
104 * - config = aliased view of PEAR::getStaticPropery('DB_DataObject','options') * done for performance.
105 * - array of loaded classes by autoload method - to stop it doing file access request over and over again!
107 $GLOBALS['_DB_DATAOBJECT']['RESULTS'] = array();
108 $GLOBALS['_DB_DATAOBJECT']['RESULTSEQ'] = 1;
109 $GLOBALS['_DB_DATAOBJECT']['RESULTFIELDS'] = array();
110 $GLOBALS['_DB_DATAOBJECT']['CONNECTIONS'] = array();
111 $GLOBALS['_DB_DATAOBJECT']['INI'] = array();
112 $GLOBALS['_DB_DATAOBJECT']['LINKS'] = array();
113 $GLOBALS['_DB_DATAOBJECT']['SEQUENCE'] = array();
114 $GLOBALS['_DB_DATAOBJECT']['LASTERROR'] = null;
115 $GLOBALS['_DB_DATAOBJECT']['CONFIG'] = array();
116 $GLOBALS['_DB_DATAOBJECT']['CACHE'] = array();
117 $GLOBALS['_DB_DATAOBJECT']['OVERLOADED'] = false;
118 $GLOBALS['_DB_DATAOBJECT']['QUERYENDTIME'] = 0;
121 // this will be horrifically slow!!!!
122 // these two are BC/FC handlers for call in PHP4/5
125 if (!defined('DB_DATAOBJECT_NO_OVERLOAD')) {
126 class DB_DataObject_Overload
128 public function __call($method, $args)
131 $this->_call($method, $args, $return);
135 public function __sleep()
137 return array_keys(get_object_vars($this));
141 class DB_DataObject_Overload
149 * @package DB_DataObject
150 * @author Alan Knowles <alan@akbkhome.com>
154 class DB_DataObject extends DB_DataObject_Overload
157 * The Version - use this to check feature changes
162 public $_DB_DataObject_version = "1.11.3";
165 * The Database table (used by table extends)
170 public $__table = ''; // database table
173 * The Number of rows returned from a query
178 public $N = 0; // Number of rows returned from a query
180 /* ============================================================= */
181 /* Major Public Methods */
182 /* (designed to be optionally then called with parent::method()) */
183 /* ============================================================= */
185 * The Database connection dsn (as described in the PEAR DB)
186 * only used really if you are writing a very simple application/test..
187 * try not to use this - it is better stored in configuration files..
192 public $_database_dsn = '';
194 * The Database connection id (md5 sum of databasedsn)
199 public $_database_dsn_md5 = '';
202 * created in __connection
207 public $_database = '';
210 * This replaces alot of the private variables
211 * used to build a query, it is unset after find() is run.
218 public $_query = array(
219 'condition' => '', // the WHERE condition
220 'group_by' => '', // the GROUP BY condition
221 'order_by' => '', // the ORDER BY condition
222 'having' => '', // the HAVING condition
223 'useindex' => '', // the USE INDEX condition
224 'limit_start' => '', // the LIMIT condition
225 'limit_count' => '', // the LIMIT condition
226 'data_select' => '*', // the columns to be SELECTed
227 'unions' => array(), // the added unions,
228 'derive_table' => '', // derived table name (BETA)
229 'derive_select' => '', // derived table select (BETA)
232 * Database result id (references global $_DB_DataObject[results]
237 public $_DB_resultid;
239 * ResultFields - on the last call to fetch(), resultfields is sent here,
240 * so we can clean up the memory.
245 public $_resultFields = false;
247 * Have the links been loaded?
248 * if they have it contains a array of those variables.
251 * @var boolean | array
253 public $_link_loaded = false;
262 * Last Error that has occured
263 * - use $this->_lastError or
264 * $last_error = PEAR::getStaticProperty('DB_DataObject','lastError');
267 * @var object PEAR_Error (or false)
269 public $_lastError = false;
272 * sets and returns debug level
273 * eg. DB_DataObject::debugLevel(4);
275 * @param int $v level
279 public static function debugLevel($v = null)
281 global $_DB_DATAOBJECT;
282 if (empty($_DB_DATAOBJECT['CONFIG'])) {
283 (new DB_DataObject)->_loadConfig();
286 $r = isset($_DB_DATAOBJECT['CONFIG']['debug']) ? $_DB_DATAOBJECT['CONFIG']['debug'] : 0;
287 $_DB_DATAOBJECT['CONFIG']['debug'] = $v;
290 return isset($_DB_DATAOBJECT['CONFIG']['debug']) ? $_DB_DATAOBJECT['CONFIG']['debug'] : 0;
294 * Define the global $_DB_DATAOBJECT['CONFIG'] as an alias to PEAR::getStaticProperty('DB_DataObject','options');
296 * After Profiling DB_DataObject, I discoved that the debug calls where taking
297 * considerable time (well 0.1 ms), so this should stop those calls happening. as
298 * all calls to debug are wrapped with direct variable queries rather than actually calling the funciton
299 * THIS STILL NEEDS FURTHER INVESTIGATION
302 * @return void an error object
304 public function _loadConfig()
306 global $_DB_DATAOBJECT;
308 $_DB_DATAOBJECT['CONFIG'] = &(new PEAR)->getStaticProperty('DB_DataObject', 'options');
313 * (deprecated - use ::get / and your own caching method)
319 public static function staticGet($class, $k, $v = null)
321 $lclass = strtolower($class);
322 global $_DB_DATAOBJECT;
323 if (empty($_DB_DATAOBJECT['CONFIG'])) {
324 (new DB_DataObject)->_loadConfig();
332 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
333 (new DB_DataObject)->debug("$class $key", "STATIC GET - TRY CACHE");
335 if (!empty($_DB_DATAOBJECT['CACHE'][$lclass][$key])) {
336 return $_DB_DATAOBJECT['CACHE'][$lclass][$key];
338 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
339 (new DB_DataObject)->debug("$class $key", "STATIC GET - NOT IN CACHE");
342 $obj = DB_DataObject::factory(substr($class, strlen($_DB_DATAOBJECT['CONFIG']['class_prefix'])));
343 if ((new PEAR)->isError($obj)) {
344 $dor = new DB_DataObject();
345 $dor->raiseError("could not autoload $class", DB_DATAOBJECT_ERROR_NOCLASS);
350 if (!isset($_DB_DATAOBJECT['CACHE'][$lclass])) {
351 $_DB_DATAOBJECT['CACHE'][$lclass] = array();
353 if (!$obj->get($k, $v)) {
354 $dor = new DB_DataObject();
355 $dor->raiseError("No Data return from get $k $v", DB_DATAOBJECT_ERROR_NODATA);
360 $_DB_DATAOBJECT['CACHE'][$lclass][$key] = $obj;
361 return $_DB_DATAOBJECT['CACHE'][$lclass][$key];
365 * Debugger. - use this in your extended classes to output debugging information.
367 * Uses DB_DataObject::DebugLevel(x) to turn it on
369 * @param string $message - message to output
370 * @param int $logtype - bold at start
371 * @param int $level - output level
375 public function debug($message, $logtype = 0, $level = 1)
377 global $_DB_DATAOBJECT;
379 if (empty($_DB_DATAOBJECT['CONFIG']['debug']) ||
380 (is_numeric($_DB_DATAOBJECT['CONFIG']['debug']) && $_DB_DATAOBJECT['CONFIG']['debug'] < $level)) {
383 // this is a bit flaky due to php's wonderfull class passing around crap..
384 // but it's about as good as it gets..
385 $class = (isset($this) && is_a($this, 'DB_DataObject')) ? get_class($this) : 'DB_DataObject';
387 if (!is_string($message)) {
388 $message = print_r($message, true);
390 if (!is_numeric($_DB_DATAOBJECT['CONFIG']['debug']) && is_callable($_DB_DATAOBJECT['CONFIG']['debug'])) {
391 return call_user_func($_DB_DATAOBJECT['CONFIG']['debug'], $class, $message, $logtype, $level);
394 if (!ini_get('html_errors')) {
395 echo "$class : $logtype : $message\n";
399 if (!is_string($message)) {
400 $message = print_r($message, true);
402 $colorize = ($logtype == 'ERROR') ? '<font color="red">' : '<font>';
403 echo "<code>{$colorize}<B>$class: $logtype:</B> " . nl2br(htmlspecialchars($message)) . "</font></code><BR>\n";
407 * classic factory method for loading a table class
408 * usage: $do = DB_DataObject::factory('person')
409 * WARNING - this may emit a include error if the file does not exist..
410 * use @ to silence it (if you are sure it is acceptable)
411 * eg. $do = @DB_DataObject::factory('person')
413 * table name can bedatabasename/table
414 * - and allow modular dataobjects to be written..
415 * (this also helps proxy creation)
417 * Experimental Support for Multi-Database factory eg. mydatabase.mytable
420 * @param string $table tablename (use blank to create a new instance of the same class.)
422 * @return DataObject|PEAR|PEAR_Error|true
426 public static function factory($table = '')
428 global $_DB_DATAOBJECT;
431 // multi-database support.. - experimental.
434 if (strpos($table, '/') !== false) {
435 list($database, $table) = explode('.', $table, 2);
438 if (empty($_DB_DATAOBJECT['CONFIG'])) {
439 (new DB_DataObject)->_loadConfig();
441 // no configuration available for database
442 if (!empty($database) && empty($_DB_DATAOBJECT['CONFIG']['database_' . $database])) {
443 $do = new DB_DataObject();
445 "unable to find database_{$database} in Configuration, It is required for factory with database",
454 if (is_a($this,'DB_DataObject') && strlen($this->tableName())) {
455 $table = $this->tableName();
457 return DB_DataObject::raiseError(
458 "factory did not recieve a table name",
459 DB_DATAOBJECT_ERROR_INVALIDARGS);
464 // does this need multi db support??
465 $cp = isset($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
466 explode(PATH_SEPARATOR, $_DB_DATAOBJECT['CONFIG']['class_prefix']) : '';
470 // multiprefix support.
471 $tbl = preg_replace('/[^A-Z0-9]/i', '_', ucfirst($table));
474 foreach ($cp as $cpr) {
475 $ce = substr(phpversion(), 0, 1) > 4 ? class_exists($cpr . $tbl, false) : class_exists($cpr . $tbl);
477 $class = $cpr . $tbl;
480 $class[] = $cpr . $tbl;
484 $ce = substr(phpversion(), 0, 1) > 4 ? class_exists($class, false) : class_exists($class);
488 $rclass = $ce ? $class : (new DB_DataObject)->_autoloadClass($class, $table);
489 // proxy = full|light
490 if (!$rclass && isset($_DB_DATAOBJECT['CONFIG']['proxy'])) {
491 (new DB_DataObject)->debug("FAILED TO Autoload $database.$table - using proxy.", "FACTORY", 1);
494 $proxyMethod = 'getProxy' . $_DB_DATAOBJECT['CONFIG']['proxy'];
495 // if you have loaded (some other way) - dont try and load it again..
496 class_exists('DB_DataObject_Generator') ? '' :
497 //require_once 'DB/DataObject/Generator.php';
498 require_once 'Generator.php';
500 $d = new DB_DataObject;
502 $d->__table = $table;
504 $ret = $d->_connect();
505 if (is_object($ret) && is_a($ret, 'PEAR_Error')) {
509 $x = new DB_DataObject_Generator;
510 return $x->$proxyMethod($d->_database, $table);
513 if (!$rclass || !class_exists($rclass)) {
514 $dor = new DB_DataObject();
515 return $dor->raiseError(
516 "factory could not find class " .
517 (is_array($class) ? implode(PATH_SEPARATOR, $class) : $class) .
519 DB_DATAOBJECT_ERROR_INVALIDCONFIG
523 $ret = new $rclass();
525 if (!empty($database)) {
526 (new DB_DataObject)->debug("Setting database to $database", "FACTORY", 1);
527 $ret->database($database);
533 * Default error handling is to create a pear error, but never return it.
534 * if you need to handle errors you should look at setting the PEAR_Error callback
535 * this is due to the fact it would wreck havoc on the internal methods!
537 * @param int $message message
538 * @param int $type type
539 * @param int $behaviour behaviour (die or continue!);
541 * @return error|int|object
543 public function raiseError($message, $type = null, $behaviour = null)
545 global $_DB_DATAOBJECT;
547 if ($behaviour == PEAR_ERROR_DIE && !empty($_DB_DATAOBJECT['CONFIG']['dont_die'])) {
551 $error = &(new PEAR)->getStaticProperty('DB_DataObject', 'lastError');
554 // no checks for production here?....... - we log errors before we throw them.
555 DB_DataObject::debug($message, 'ERROR', 1);
558 if ((new PEAR)->isError($message)) {
561 require_once 'DB/DataObject/Error.php';
563 $error = $dor->raiseError(
569 'DB_DataObject_Error'
572 // this will never work totally with PHP's object model.
573 // as this is passed on static calls (like staticGet in our case)
575 $_DB_DATAOBJECT['LASTERROR'] = $error;
577 if (isset($this) && is_object($this) && is_subclass_of($this, 'db_dataobject')) {
578 $this->_lastError = $error;
587 * @param string|array $class Class
588 * @param bool $table Table trying to load.
589 * @return string classname on Success
592 public function _autoloadClass($class, $table = false)
594 global $_DB_DATAOBJECT;
596 if (empty($_DB_DATAOBJECT['CONFIG'])) {
597 DB_DataObject::_loadConfig();
599 $class_prefix = empty($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
600 '' : $_DB_DATAOBJECT['CONFIG']['class_prefix'];
602 $table = $table ? $table : substr($class, strlen($class_prefix));
604 // only include the file if it exists - and barf badly if it has parse errors :)
605 if (!empty($_DB_DATAOBJECT['CONFIG']['proxy']) || empty($_DB_DATAOBJECT['CONFIG']['class_location'])) {
609 // class_location = mydir/ => maps to mydir/Tablename.php
610 // class_location = mydir/myfile_%s.php => maps to mydir/myfile_Tablename
611 // with directory sepr
612 // class_location = mydir/:mydir2/: => tries all of thes locations.
613 $cl = $_DB_DATAOBJECT['CONFIG']['class_location'];
617 case (strpos($cl, '%s') !== false):
618 $file = sprintf($cl, preg_replace('/[^A-Z0-9]/i', '_', ucfirst($table)));
621 case (strpos($cl, PATH_SEPARATOR) !== false):
623 foreach (explode(PATH_SEPARATOR, $cl) as $p) {
624 $file[] = $p . '/' . preg_replace('/[^A-Z0-9]/i', '_', ucfirst($table)) . ".php";
628 $file = $cl . '/' . preg_replace('/[^A-Z0-9]/i', '_', ucfirst($table)) . ".php";
632 $cls = is_array($class) ? $class : array($class);
634 if (is_array($file) || !file_exists($file)) {
637 $file = is_array($file) ? $file : array($file);
638 $search = implode(PATH_SEPARATOR, $file);
639 foreach ($file as $f) {
640 foreach (explode(PATH_SEPARATOR, '' . PATH_SEPARATOR . ini_get('include_path')) as $p) {
641 $ff = empty($p) ? $f : "$p/$f";
643 if (file_exists($ff)) {
654 $dor = new DB_DataObject();
656 "autoload:Could not find class " . implode(',', $cls) .
657 " using class_location value :" . $search .
658 " using include_path value :" . ini_get('include_path'),
659 DB_DATAOBJECT_ERROR_INVALIDCONFIG
669 foreach ($cls as $c) {
670 $ce = substr(phpversion(), 0, 1) > 4 ? class_exists($c, false) : class_exists($c);
677 $dor = new DB_DataObject();
679 "autoload:Could not autoload " . implode(',', $cls),
680 DB_DATAOBJECT_ERROR_INVALIDCONFIG
688 * connects to the database
691 * TODO: tidy this up - This has grown to support a number of connection options like
692 * a) dynamic changing of ini file to change which database to connect to
693 * b) multi data via the table_{$table} = dsn ini option
694 * c) session based storage.
697 * @return error|PEAR|true
699 public function _connect()
701 global $_DB_DATAOBJECT;
702 if (empty($_DB_DATAOBJECT['CONFIG'])) {
703 $this->_loadConfig();
705 // Set database driver for reference
706 $db_driver = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ?
707 'DB' : $_DB_DATAOBJECT['CONFIG']['db_driver'];
709 // is it already connected ?
710 if ($this->_database_dsn_md5 && !empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
712 // connection is an error...
713 if ((new PEAR)->isError($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
714 return $this->raiseError(
715 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->message,
716 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->code,
721 if (empty($this->_database)) {
722 $this->_database = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
723 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
725 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
726 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
727 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
730 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
731 && is_file($this->_database)) {
732 $this->_database = basename($this->_database);
734 if ($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'ibase') {
735 $this->_database = substr(basename($this->_database), 0, -4);
738 // theoretically we have a md5, it's listed in connections and it's not an error.
739 // so everything is ok!
743 // it's not currently connected!
744 // try and work out what to use for the dsn !
746 $options = $_DB_DATAOBJECT['CONFIG'];
747 // if the databse dsn dis defined in the object..
748 $dsn = isset($this->_database_dsn) ? $this->_database_dsn : null;
751 if (!$this->_database && !strlen($this->tableName())) {
752 $this->_database = isset($options["table_{$this->tableName()}"]) ? $options["table_{$this->tableName()}"] : null;
754 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
755 $this->debug("Checking for database specific ini ('{$this->_database}') : database_{$this->_database} in options", "CONNECT");
758 if ($this->_database && !empty($options["database_{$this->_database}"])) {
759 $dsn = $options["database_{$this->_database}"];
760 } elseif (!empty($options['database'])) {
761 $dsn = $options['database'];
765 // if still no database...
767 return $this->raiseError(
768 "No database name / dsn found anywhere",
769 DB_DATAOBJECT_ERROR_INVALIDCONFIG,
775 if (is_string($dsn)) {
776 $this->_database_dsn_md5 = md5($dsn);
778 /// support array based dsn's
779 $this->_database_dsn_md5 = md5(serialize($dsn));
782 if (!empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
783 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
784 $this->debug("USING CACHED CONNECTION", "CONNECT", 3);
788 if (!$this->_database) {
789 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
790 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
791 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
792 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
794 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
795 && is_file($this->_database)) {
796 $this->_database = basename($this->_database);
801 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
802 $this->debug("NEW CONNECTION TP DATABASE :" . $this->_database, "CONNECT", 3);
803 /* actualy make a connection */
804 $this->debug(print_r($dsn, true) . " {$this->_database_dsn_md5}", "CONNECT", 3);
807 // Note this is verbose deliberatly!
809 if ($db_driver == 'DB') {
811 /* PEAR DB connect */
813 // this allows the setings of compatibility on DB
814 $db_options = (new PEAR)->getStaticProperty('DB', 'options');
815 require_once 'DB.php';
817 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = DB::connect($dsn, $db_options);
819 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = DB::connect($dsn);
822 /* assumption is MDB2 */
823 require_once 'MDB2.php';
824 // this allows the setings of compatibility on MDB2
825 $db_options = (new PEAR)->getStaticProperty('MDB2', 'options');
826 $db_options = is_array($db_options) ? $db_options : array();
827 $db_options['portability'] = isset($db_options['portability'])
828 ? $db_options['portability'] : MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_FIX_CASE;
829 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5] = MDB2::connect($dsn, $db_options);
833 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
834 $this->debug(print_r($_DB_DATAOBJECT['CONNECTIONS'], true), "CONNECT", 5);
836 if ((new PEAR)->isError($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
837 $this->debug($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->toString(), "CONNECT FAILED", 5);
838 return $this->raiseError(
839 "Connect failed, turn on debugging to 5 see why",
840 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->code,
845 if (empty($this->_database)) {
846 $hasGetDatabase = method_exists($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5], 'getDatabase');
848 $this->_database = ($db_driver != 'DB' && $hasGetDatabase)
849 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->getDatabase()
850 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['database'];
853 if (($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'] == 'sqlite')
854 && is_file($this->_database)) {
855 $this->_database = basename($this->_database);
859 // Oracle need to optimize for portibility - not sure exactly what this does though :)
865 * Return or assign the name of the current table
868 * @param string optinal table name to set
870 * @return string The name of the current table
872 public function tableName()
874 global $_DB_DATAOBJECT;
875 $args = func_get_args();
877 $this->__table = $args[0];
879 if (empty($this->__table)) {
882 if (!empty($_DB_DATAOBJECT['CONFIG']['portability']) && $_DB_DATAOBJECT['CONFIG']['portability'] & 1) {
883 return strtolower($this->__table);
885 return $this->__table;
889 * Get a result using key, value.
892 * $object->get("ID",1234);
893 * Returns Number of rows located (usually 1) for success,
894 * and puts all the table columns into this classes variables
896 * see the fetch example on how to extend this.
898 * if no value is entered, it is assumed that $key is a value
899 * and get will then use the first key in keys()
902 * @param string $k column
903 * @param string $v value
905 * @return int No. of rows
907 public function get($k = null, $v = null)
909 global $_DB_DATAOBJECT;
910 if (empty($_DB_DATAOBJECT['CONFIG'])) {
911 DB_DataObject::_loadConfig();
917 $keys = $this->keys();
919 $this->raiseError("No Keys available for {$this->tableName()}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
924 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
925 $this->debug("$k $v " . print_r($keys, true), "GET");
929 $this->raiseError("No Value specified for get", DB_DATAOBJECT_ERROR_INVALIDARGS);
933 return $this->find(1);
937 * get/set an array of table primary keys
939 * set usage: $do->keys('id','code');
941 * This is defined in the table definition if it gets it wrong,
942 * or you do not want to use ini tables, you can override this.
943 * @param string optional set the key
944 * @param * optional set more keys
948 public function keys()
950 // for temporary storage of database fields..
951 // note this is not declared as we dont want to bloat the print_r output
952 $args = func_get_args();
954 $this->_database_keys = $args;
956 if (isset($this->_database_keys)) {
957 return $this->_database_keys;
960 global $_DB_DATAOBJECT;
961 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
964 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName() . "__keys"])) {
965 return array_keys($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName() . "__keys"]);
967 $this->databaseStructure();
969 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName() . "__keys"])) {
970 return array_keys($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName() . "__keys"]);
976 * Autoload or manually load the table definitions
980 * DB_DataObject::databaseStructure( 'databasename',
981 * parse_ini_file('mydb.ini',true),
982 * parse_ini_file('mydb.link.ini',true));
984 * obviously you dont have to use ini files.. (just return array similar to ini files..)
986 * It should append to the table structure array
989 * @param optional string name of database to assign / read
990 * @param optional array structure of database, and keys
991 * @param optional array table links
994 * @return true or PEAR:error on wrong paramenters.. or false if no file exists..
995 * or the array(tablename => array(column_name=>type)) if called with 1 argument.. (databasename)
997 public function databaseStructure()
999 global $_DB_DATAOBJECT;
1003 if ($args = func_get_args()) {
1004 if (count($args) == 1) {
1006 // this returns all the tables and their structure..
1007 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1008 $this->debug("Loading Generator as databaseStructure called with args", 1);
1011 $x = new DB_DataObject;
1012 $x->_database = $args[0];
1014 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1016 $tables = $DB->getListOf('tables');
1017 class_exists('DB_DataObject_Generator') ? '' :
1018 //require_once 'DB/DataObject/Generator.php';
1019 require_once 'Generator.php';
1021 foreach ($tables as $table) {
1022 $y = new DB_DataObject_Generator;
1023 $y->fillTableSchema($x->_database, $table);
1025 return $_DB_DATAOBJECT['INI'][$x->_database];
1027 $_DB_DATAOBJECT['INI'][$args[0]] = isset($_DB_DATAOBJECT['INI'][$args[0]]) ?
1028 $_DB_DATAOBJECT['INI'][$args[0]] + $args[1] : $args[1];
1030 if (isset($args[1])) {
1031 $_DB_DATAOBJECT['LINKS'][$args[0]] = isset($_DB_DATAOBJECT['LINKS'][$args[0]]) ?
1032 $_DB_DATAOBJECT['LINKS'][$args[0]] + $args[2] : $args[2];
1039 if (!$this->_database) {
1044 // if this table is already loaded this table..
1045 if (!empty($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()])) {
1049 // initialize the ini data.. if empt..
1050 if (empty($_DB_DATAOBJECT['INI'][$this->_database])) {
1051 $_DB_DATAOBJECT['INI'][$this->_database] = array();
1054 if (empty($_DB_DATAOBJECT['CONFIG'])) {
1055 DB_DataObject::_loadConfig();
1058 // we do not have the data for this table yet...
1060 // if we are configured to use the proxy..
1062 if (!empty($_DB_DATAOBJECT['CONFIG']['proxy'])) {
1063 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1064 $this->debug("Loading Generator to fetch Schema", 1);
1066 class_exists('DB_DataObject_Generator') ? '' :
1067 //require_once 'DB/DataObject/Generator.php';
1068 require_once 'Generator.php';
1071 $x = new DB_DataObject_Generator;
1072 $x->fillTableSchema($this->_database, $this->tableName());
1077 // if you supply this with arguments, then it will take those
1078 // as the database and links array...
1080 $schemas = isset($_DB_DATAOBJECT['CONFIG']['schema_location']) ?
1081 array("{$_DB_DATAOBJECT['CONFIG']['schema_location']}/{$this->_database}.ini") :
1084 if (isset($_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"])) {
1085 $schemas = is_array($_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"]) ?
1086 $_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"] :
1087 explode(PATH_SEPARATOR, $_DB_DATAOBJECT['CONFIG']["ini_{$this->_database}"]);
1091 $_DB_DATAOBJECT['INI'][$this->_database] = array();
1092 foreach ($schemas as $ini) {
1093 if (file_exists($ini) && is_file($ini)) {
1094 $_DB_DATAOBJECT['INI'][$this->_database] = array_merge(
1095 $_DB_DATAOBJECT['INI'][$this->_database],
1096 parse_ini_file($ini, true)
1099 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1100 if (!is_readable($ini)) {
1101 $this->debug("ini file is not readable: $ini", "databaseStructure", 1);
1103 $this->debug("Loaded ini file: $ini", "databaseStructure", 1);
1107 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1108 $this->debug("Missing ini file: $ini", "databaseStructure", 1);
1112 // are table name lowecased..
1113 if (!empty($_DB_DATAOBJECT['CONFIG']['portability']) && $_DB_DATAOBJECT['CONFIG']['portability'] & 1) {
1114 foreach ($_DB_DATAOBJECT['INI'][$this->_database] as $k => $v) {
1115 // results in duplicate cols.. but not a big issue..
1116 $_DB_DATAOBJECT['INI'][$this->_database][strtolower($k)] = $v;
1121 // now have we loaded the structure..
1123 if (!empty($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()])) {
1126 // - if not try building it..
1127 if (!empty($_DB_DATAOBJECT['CONFIG']['proxy'])) {
1128 class_exists('DB_DataObject_Generator') ? '' :
1129 //require_once 'DB/DataObject/Generator.php';
1130 require_once 'Generator.php';
1132 $x = new DB_DataObject_Generator;
1133 $x->fillTableSchema($this->_database, $this->tableName());
1134 // should this fail!!!???
1137 $this->debug("Cant find database schema: {$this->_database}/{$this->tableName()} \n" .
1138 "in links file data: " . print_r($_DB_DATAOBJECT['INI'], true), "databaseStructure", 5);
1139 // we have to die here!! - it causes chaos if we dont (including looping forever!)
1140 $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);
1145 * find results, either normal or crosstable
1149 * $object = new mytable();
1154 * will set $object->N to number of rows, and expects next command to fetch rows
1155 * will return $object->N
1157 * if an error occurs $object->N will be set to false and return value will also be false;
1158 * if numRows is not supported it will
1161 * @param boolean $n Fetch first result
1163 * @return mixed (number of rows returned, or true if numRows fetching is not supported)
1165 public function find($n = false)
1167 global $_DB_DATAOBJECT;
1168 if ($this->_query === false) {
1170 "You cannot do two queries on the same object (copy it before finding)",
1171 DB_DATAOBJECT_ERROR_INVALIDARGS
1176 if (empty($_DB_DATAOBJECT['CONFIG'])) {
1177 DB_DataObject::_loadConfig();
1180 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1181 $this->debug($n, "find", 1);
1183 if (!strlen($this->tableName())) {
1184 // xdebug can backtrace this!
1185 trigger_error("NO \$__table SPECIFIED in class definition", E_USER_ERROR);
1188 $query_before = $this->_query;
1189 $this->_build_condition($this->table());
1193 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1196 $sql = $this->_build_select();
1198 foreach ($this->_query['unions'] as $union_ar) {
1199 $sql .= $union_ar[1] . $union_ar[0]->_build_select() . " \n";
1202 $sql .= $this->_query['order_by'] . " \n";
1205 /* We are checking for method modifyLimitQuery as it is PEAR DB specific */
1206 if ((!isset($_DB_DATAOBJECT['CONFIG']['db_driver'])) ||
1207 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB')) {
1208 /* PEAR DB specific */
1210 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
1211 $sql = $DB->modifyLimitQuery($sql, $this->_query['limit_start'], $this->_query['limit_count']);
1214 /* theoretically MDB2! */
1215 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
1216 $DB->setLimit($this->_query['limit_count'], $this->_query['limit_start']);
1221 $err = $this->_query($sql);
1222 if (is_a($err, 'PEAR_Error')) {
1226 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1227 $this->debug("CHECK autofetchd $n", "find", 1);
1233 if (!$ret && !empty($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
1234 // clear up memory if nothing found!?
1235 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
1238 if ($n && $this->N > 0) {
1239 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1240 $this->debug("ABOUT TO AUTOFETCH", "find", 1);
1242 $fs = $this->fetch();
1243 // if fetch returns false (eg. failed), then the backend doesnt support numRows (eg. ret=true)
1244 // - hence find() also returns false..
1245 $ret = ($ret === true) ? $fs : $ret;
1247 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1248 $this->debug("DONE", "find", 1);
1250 $this->_query = $query_before;
1254 /* ==================================================== */
1255 /* Major Private Vars */
1256 /* ==================================================== */
1259 * Builds the WHERE based on the values of of this object
1261 * @param mixed $keys
1262 * @param array $filter (used by update to only uses keys in this filter list).
1263 * @param array $negative_filter (used by delete to prevent deleting using the keys mentioned..)
1267 public function _build_condition($keys, $filter = array(), $negative_filter = array())
1269 global $_DB_DATAOBJECT;
1271 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1273 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1274 $options = $_DB_DATAOBJECT['CONFIG'];
1276 // if we dont have query vars.. - reset them.
1277 if ($this->_query === false) {
1278 $x = new DB_DataObject;
1279 $this->_query = $x->_query;
1283 foreach ($keys as $k => $v) {
1284 // index keys is an indexed array
1285 /* these filter checks are a bit suspicious..
1286 - need to check that update really wants to work this way */
1289 if (!in_array($k, $filter)) {
1293 if ($negative_filter) {
1294 if (in_array($k, $negative_filter)) {
1298 if (!isset($this->$k)) {
1302 $kSql = $quoteIdentifiers
1303 ? ($DB->quoteIdentifier($this->tableName()) . '.' . $DB->quoteIdentifier($k))
1304 : "{$this->tableName()}.{$k}";
1307 if (is_object($this->$k) && is_a($this->$k, 'DB_DataObject_Cast')) {
1308 $dbtype = $DB->dsn["phptype"];
1309 $value = $this->$k->toString($v, $DB);
1310 if ((new PEAR)->isError($value)) {
1311 $this->raiseError($value->getMessage(), DB_DATAOBJECT_ERROR_INVALIDARG);
1314 if ((strtolower($value) === 'null') && !($v & DB_DATAOBJECT_NOTNULL)) {
1315 $this->whereAdd(" $kSql IS NULL");
1318 $this->whereAdd(" $kSql = $value");
1322 if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this, $k)) {
1323 $this->whereAdd(" $kSql IS NULL");
1328 if ($v & DB_DATAOBJECT_STR) {
1329 $this->whereAdd(" $kSql = " . $this->_quote((string)(
1330 ($v & DB_DATAOBJECT_BOOL) ?
1331 // this is thanks to the braindead idea of postgres to
1332 // use t/f for boolean.
1333 (($this->$k === 'f') ? 0 : (int)(bool)$this->$k) :
1338 if (is_numeric($this->$k)) {
1339 $this->whereAdd(" $kSql = {$this->$k}");
1342 /* this is probably an error condition! */
1343 $this->whereAdd(" $kSql = " . intval($this->$k));
1349 * Adds a condition to the WHERE statement, defaults to AND
1351 * $object->whereAdd(); //reset or cleaer ewhwer
1352 * $object->whereAdd("ID > 20");
1353 * $object->whereAdd("age > 20","OR");
1355 * @param bool $cond condition
1356 * @param string $logic optional logic "OR" (defaults to "AND")
1357 * @return string|PEAR::Error - previous condition or Error when invalid args found
1360 public function whereAdd($cond = false, $logic = 'AND')
1362 // for PHP5.2.3 - there is a bug with setting array properties of an object.
1363 $_query = $this->_query;
1365 if (!isset($this->_query) || ($_query === false)) {
1366 return $this->raiseError(
1367 "You cannot do two queries on the same object (clone it before finding)",
1368 DB_DATAOBJECT_ERROR_INVALIDARGS
1372 if ($cond === false) {
1373 $r = $this->_query['condition'];
1374 $_query['condition'] = '';
1375 $this->_query = $_query;
1376 return preg_replace('/^\s+WHERE\s+/', '', $r);
1378 // check input...= 0 or ' ' == error!
1380 return $this->raiseError("WhereAdd: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
1382 $r = $_query['condition'];
1383 if ($_query['condition']) {
1384 $_query['condition'] .= " {$logic} ( {$cond} )";
1385 $this->_query = $_query;
1388 $_query['condition'] = " WHERE ( {$cond} ) ";
1389 $this->_query = $_query;
1394 * Evaluate whether or not a value is set to null, taking the 'disable_null_strings' option into account.
1395 * If the value is a string set to "null" and the "disable_null_strings" option is not set to
1396 * true, then the value is considered to be null.
1397 * If the value is actually a PHP NULL value, and "disable_null_strings" has been set to
1398 * the value "full", then it will also be considered null. - this can not differenticate between not set
1401 * @param object|array $obj_or_ar
1402 * @param string|false $prop prperty
1404 * @return bool object
1406 public function _is_null($obj_or_ar, $prop)
1408 global $_DB_DATAOBJECT;
1411 $isset = $prop === false ? isset($obj_or_ar) :
1412 (is_array($obj_or_ar) ? isset($obj_or_ar[$prop]) : isset($obj_or_ar->$prop));
1415 ($prop === false ? $obj_or_ar :
1416 (is_array($obj_or_ar) ? $obj_or_ar[$prop] : $obj_or_ar->$prop))
1420 $options = $_DB_DATAOBJECT['CONFIG'];
1422 $null_strings = !isset($options['disable_null_strings'])
1423 || $options['disable_null_strings'] === false;
1425 $crazy_null = isset($options['disable_null_strings'])
1426 && is_string($options['disable_null_strings'])
1427 && strtolower($options['disable_null_strings'] === 'full');
1429 if ($null_strings && $isset && is_string($value) && (strtolower($value) === 'null')) {
1433 if ($crazy_null && !$isset) {
1441 * backend wrapper for quoting, as MDB2 and DB do it differently...
1445 * @return string quoted
1448 public function _quote($str)
1450 global $_DB_DATAOBJECT;
1451 return (empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ||
1452 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB'))
1453 ? $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->quoteSmart($str)
1454 : $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->quote($str);
1458 * get/set an associative array of table columns
1461 * @param array key=>type array
1462 * @return array (associative)
1464 public function table()
1467 // for temporary storage of database fields..
1468 // note this is not declared as we dont want to bloat the print_r output
1469 $args = func_get_args();
1471 $this->_database_fields = $args[0];
1473 if (isset($this->_database_fields)) {
1474 return $this->_database_fields;
1478 global $_DB_DATAOBJECT;
1479 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
1483 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()])) {
1484 return $_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()];
1487 $this->databaseStructure();
1491 if (isset($_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()])) {
1492 $ret = $_DB_DATAOBJECT['INI'][$this->_database][$this->tableName()];
1499 * build the basic select query.
1504 public function _build_select()
1506 global $_DB_DATAOBJECT;
1507 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
1508 if ($quoteIdentifiers) {
1510 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1512 $tn = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
1513 if (!empty($this->_query['derive_table']) && !empty($this->_query['derive_select'])) {
1515 // this is a derived select..
1516 // not much support in the api yet..
1519 $this->_query['derive_select']
1520 . ' FROM ( SELECT' .
1521 $this->_query['data_select'] . " \n" .
1522 " FROM $tn " . $this->_query['useindex'] . " \n" .
1523 $this->_join . " \n" .
1524 $this->_query['condition'] . " \n" .
1525 $this->_query['group_by'] . " \n" .
1526 $this->_query['having'] . " \n" .
1527 ') ' . $this->_query['derive_table'];
1534 $this->_query['data_select'] . " \n" .
1535 " FROM $tn " . $this->_query['useindex'] . " \n" .
1536 $this->_join . " \n" .
1537 $this->_query['condition'] . " \n" .
1538 $this->_query['group_by'] . " \n" .
1539 $this->_query['having'] . " \n";
1545 /* ============================================================== */
1546 /* Table definition layer (started of very private but 'came out'*/
1547 /* ============================================================== */
1550 * sends query to database - this is the private one that must work
1551 * - internal functions use this rather than $this->query()
1553 * @param string $string
1555 * @return mixed none or PEAR_Error
1557 public function _query($string)
1559 global $_DB_DATAOBJECT;
1563 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
1565 $options = $_DB_DATAOBJECT['CONFIG'];
1567 $_DB_driver = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ?
1568 'DB' : $_DB_DATAOBJECT['CONFIG']['db_driver'];
1570 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1571 $this->debug($string, $log = "QUERY");
1575 strtoupper($string) == 'BEGIN' ||
1576 strtoupper($string) == 'START TRANSACTION'
1578 if ($_DB_driver == 'DB') {
1579 $DB->autoCommit(false);
1580 $DB->simpleQuery('BEGIN');
1582 $DB->beginTransaction();
1587 if (strtoupper($string) == 'COMMIT') {
1588 $res = $DB->commit();
1589 if ($_DB_driver == 'DB') {
1590 $DB->autoCommit(true);
1595 if (strtoupper($string) == 'ROLLBACK') {
1597 if ($_DB_driver == 'DB') {
1598 $DB->autoCommit(true);
1604 if (!empty($options['debug_ignore_updates']) &&
1605 (strtolower(substr(trim($string), 0, 6)) != 'select') &&
1606 (strtolower(substr(trim($string), 0, 4)) != 'show') &&
1607 (strtolower(substr(trim($string), 0, 8)) != 'describe')) {
1608 $this->debug('Disabling Update as you are in debug mode');
1609 return $this->raiseError("Disabling Update as you are in debug mode", null);
1611 //if (@$_DB_DATAOBJECT['CONFIG']['debug'] > 1) {
1612 // this will only work when PEAR:DB supports it.
1613 //$this->debug($DB->getAll('explain ' .$string,DB_DATAOBJECT_FETCHMODE_ASSOC), $log="sql",2);
1617 $t = explode(' ', microtime());
1618 $_DB_DATAOBJECT['QUERYENDTIME'] = $time = $t[0] + $t[1];
1621 for ($tries = 0; $tries < 3; $tries++) {
1622 if ($_DB_driver == 'DB') {
1623 $result = $DB->query($string);
1625 switch (strtolower(substr(trim($string), 0, 6))) {
1630 $result = $DB->exec($string);
1634 $result = $DB->query($string);
1639 // see if we got a failure.. - try again a few times..
1640 if (!is_object($result) || !is_a($result, 'PEAR_Error')) {
1643 if ($result->getCode() != -14) { // *DB_ERROR_NODBSELECTED
1644 break; // not a connection error..
1646 sleep(1); // wait before retyring..
1647 $DB->connect($DB->dsn);
1651 if (is_object($result) && is_a($result, 'PEAR_Error')) {
1652 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1653 $this->debug($result->toString(), "Query Error", 1);
1656 return $this->raiseError($result);
1658 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1659 $t = explode(' ', microtime());
1660 $_DB_DATAOBJECT['QUERYENDTIME'] = $t[0] + $t[1];
1661 $this->debug('QUERY DONE IN ' . ($t[0] + $t[1] - $time) . " seconds", 'query', 1);
1663 switch (strtolower(substr(trim($string), 0, 6))) {
1667 if ($_DB_driver == 'DB') {
1669 return $DB->affectedRows();
1673 if (is_object($result)) {
1674 // lets hope that copying the result object is OK!
1676 $_DB_resultid = $GLOBALS['_DB_DATAOBJECT']['RESULTSEQ']++;
1677 $_DB_DATAOBJECT['RESULTS'][$_DB_resultid] = $result;
1678 $this->_DB_resultid = $_DB_resultid;
1681 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1682 $this->debug(serialize($result), 'RESULT', 5);
1684 if (method_exists($result, 'numRows')) {
1685 if ($_DB_driver == 'DB') {
1686 $DB->expectError(DB_ERROR_UNSUPPORTED);
1688 $DB->expectError(MDB2_ERROR_UNSUPPORTED);
1691 $this->N = $result->numRows();
1692 //var_dump($this->N);
1694 if (is_object($this->N) && is_a($this->N, 'PEAR_Error')) {
1703 * fetches next row into this objects var's
1705 * returns 1 on success 0 on failure
1710 * $object = new mytable();
1711 * $object->name = "fred";
1714 * while ($object->fetch()) {
1716 * $store[] = $object; // builds an array of object lines.
1719 * to add features to a fetch
1720 * function fetch () {
1721 * $ret = parent::fetch();
1722 * $this->date_formated = date('dmY',$this->date);
1727 * @return boolean on success
1729 public function fetch()
1731 global $_DB_DATAOBJECT;
1732 if (empty($_DB_DATAOBJECT['CONFIG'])) {
1733 DB_DataObject::_loadConfig();
1735 if (empty($this->N)) {
1736 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1737 $this->debug("No data returned from FIND (eg. N is 0)", "FETCH", 3);
1742 if (empty($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]) ||
1743 !is_object($result = $_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
1744 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1745 $this->debug('fetched on object after fetch completed (no results found)');
1751 $array = $result->fetchRow(DB_DATAOBJECT_FETCHMODE_ASSOC);
1752 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1753 $this->debug(serialize($array), "FETCH");
1756 // fetched after last row..
1757 if ($array === null) {
1758 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1759 $t = explode(' ', microtime());
1762 "Last Data Fetch'ed after " .
1763 ($t[0] + $t[1] - $_DB_DATAOBJECT['QUERYENDTIME']) .
1769 // reduce the memory usage a bit... (but leave the id in, so count() works ok on it)
1770 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
1772 // we need to keep a copy of resultfields locally so toArray() still works
1773 // however we dont want to keep it in the global cache..
1775 if (!empty($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
1776 $this->_resultFields = $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid];
1777 unset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]);
1779 // this is probably end of data!!
1780 //DB_DataObject::raiseError("fetch: no data returned", DB_DATAOBJECT_ERROR_NODATA);
1783 // make sure resultFields is always empty..
1784 $this->_resultFields = false;
1786 if (!isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
1787 // note: we dont declare this to keep the print_r size down.
1788 $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid] = array_flip(array_keys($array));
1790 $replace = array('.', ' ');
1791 foreach ($array as $k => $v) {
1792 // use strpos as str_replace is slow.
1793 $kk = (strpos($k, '.') === false && strpos($k, ' ') === false) ?
1794 $k : str_replace($replace, '_', $k);
1796 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1797 $this->debug("$kk = " . $array[$k], "fetchrow LINE", 3);
1799 $this->$kk = $array[$k];
1803 $this->_link_loaded = false;
1804 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
1805 $this->debug("{$this->tableName()} DONE", "fetchrow", 2);
1807 if (($this->_query !== false) && empty($_DB_DATAOBJECT['CONFIG']['keep_query_after_fetch'])) {
1808 $this->_query = false;
1814 * Get the value of the primary id
1816 * While I normally use 'id' as the PRIMARY KEY value, some database use
1817 * {table}_id as the column name.
1819 * To save a bit of typing,
1825 public function pid()
1827 $keys = $this->keys();
1830 "No Keys available for {$this->tableName()}",
1831 DB_DATAOBJECT_ERROR_INVALIDCONFIG
1836 if (empty($this->$k)) { // we do not
1838 "pid() called on Object where primary key value not available",
1839 DB_DATAOBJECT_ERROR_NODATA
1847 * fetches all results as an array,
1849 * return format is dependant on args.
1850 * if selectAdd() has not been called on the object, then it will add the correct columns to the query.
1852 * A) Array of values (eg. a list of 'id')
1854 * $x = DB_DataObject::factory('mytable');
1855 * $x->whereAdd('something = 1')
1856 * $ar = $x->fetchAll('id');
1857 * -- returns array(1,2,3,4,5)
1859 * B) Array of values (not from table)
1861 * $x = DB_DataObject::factory('mytable');
1862 * $x->whereAdd('something = 1');
1864 * $x->selectAdd('distinct(group_id) as group_id');
1865 * $ar = $x->fetchAll('group_id');
1866 * -- returns array(1,2,3,4,5)
1868 * C) A key=>value associative array
1870 * $x = DB_DataObject::factory('mytable');
1871 * $x->whereAdd('something = 1')
1872 * $ar = $x->fetchAll('id','name');
1873 * -- returns array(1=>'fred',2=>'blogs',3=> .......
1875 * D) array of objects
1876 * $x = DB_DataObject::factory('mytable');
1877 * $x->whereAdd('something = 1');
1878 * $ar = $x->fetchAll();
1880 * E) array of arrays (for example)
1881 * $x = DB_DataObject::factory('mytable');
1882 * $x->whereAdd('something = 1');
1883 * $ar = $x->fetchAll(false,false,'toArray');
1886 * @param string|false $k key
1887 * @param string|false $v value
1888 * @param string|false $method method to call on each result to get array value (eg. 'toArray')
1890 * @return array format dependant on arguments, may be empty
1892 public function fetchAll($k = false, $v = false, $method = false)
1894 // should it even do this!!!?!?
1896 ( // only do this is we have not been explicit..
1897 empty($this->_query['data_select']) ||
1898 ($this->_query['data_select'] == '*')
1902 $this->selectAdd($k);
1904 $this->selectAdd($v);
1910 while ($this->fetch()) {
1912 $ret[$this->$k] = $this->$v;
1915 $ret[] = $k === false ?
1916 ($method == false ? clone($this) : $this->$method())
1923 * Adds a select columns
1925 * $object->selectAdd(); // resets select to nothing!
1926 * $object->selectAdd("*"); // default select
1927 * $object->selectAdd("unixtime(DATE) as udate");
1928 * $object->selectAdd("DATE");
1930 * to prepend distict:
1931 * $object->selectAdd('distinct ' . $object->selectAdd());
1935 * @return mixed null or old string if you reset it.
1937 public function selectAdd($k = null)
1939 if ($this->_query === false) {
1941 "You cannot do two queries on the same object (copy it before finding)",
1942 DB_DATAOBJECT_ERROR_INVALIDARGS
1947 $old = $this->_query['data_select'];
1948 $this->_query['data_select'] = '';
1952 // check input...= 0 or ' ' == error!
1954 return $this->raiseError("selectAdd: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
1957 if ($this->_query['data_select']) {
1958 $this->_query['data_select'] .= ', ';
1960 $this->_query['data_select'] .= " $k ";
1965 * Adds a 'IN' condition to the WHERE statement
1967 * $object->whereAddIn('id', $array, 'int'); //minimal usage
1968 * $object->whereAddIn('price', $array, 'float', 'OR'); // cast to float, and call whereAdd with 'OR'
1969 * $object->whereAddIn('name', $array, 'string'); // quote strings
1971 * @param string $key key column to match
1972 * @param array $list list of values to match
1973 * @param string $type string|int|integer|float|bool cast to type.
1974 * @param string $logic optional logic to call whereAdd with eg. "OR" (defaults to "AND")
1976 * @return string|PEAR::Error - previous condition or Error when invalid args found
1978 public function whereAddIn($key, $list, $type, $logic = 'AND')
1981 if ($key[0] == '!') {
1983 $key = substr($key, 1);
1985 // fix type for short entry.
1986 $type = $type == 'int' ? 'integer' : $type;
1988 if ($type == 'string') {
1993 foreach ($list as $k) {
1995 $ar[] = $type == 'string' ? $this->_quote($k) : $k;
1999 return $not ? $this->_query['condition'] : $this->whereAdd("1=0");
2001 return $this->whereAdd("$key $not IN (" . implode(',', $ar) . ')', $logic);
2006 /* =========================================================== */
2007 /* Major Private Methods - the core part! */
2008 /* =========================================================== */
2011 * Adds a order by condition
2013 * $object->orderBy(); //clears order by
2014 * $object->orderBy("ID");
2015 * $object->orderBy("ID,age");
2017 * @param bool $order Order
2018 * @return bool|error|none|PEAR
2021 public function orderBy($order = false)
2023 if ($this->_query === false) {
2025 "You cannot do two queries on the same object (copy it before finding)",
2026 DB_DATAOBJECT_ERROR_INVALIDARGS
2030 if ($order === false) {
2031 $this->_query['order_by'] = '';
2034 // check input...= 0 or ' ' == error!
2035 if (!trim($order)) {
2036 return $this->raiseError("orderBy: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
2039 if (!$this->_query['order_by']) {
2040 $this->_query['order_by'] = " ORDER BY {$order} ";
2043 $this->_query['order_by'] .= " , {$order}";
2048 * Adds a group by condition
2050 * $object->groupBy(); //reset the grouping
2051 * $object->groupBy("ID DESC");
2052 * $object->groupBy("ID,age");
2054 * @param bool $group Grouping
2055 * @return bool|none|PEAR
2058 public function groupBy($group = false)
2060 if ($this->_query === false) {
2062 "You cannot do two queries on the same object (copy it before finding)",
2063 DB_DATAOBJECT_ERROR_INVALIDARGS
2067 if ($group === false) {
2068 $this->_query['group_by'] = '';
2071 // check input...= 0 or ' ' == error!
2072 if (!trim($group)) {
2073 return $this->raiseError("groupBy: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
2077 if (!$this->_query['group_by']) {
2078 $this->_query['group_by'] = " GROUP BY {$group} ";
2081 $this->_query['group_by'] .= " , {$group}";
2086 * Adds a having clause
2088 * $object->having(); //reset the grouping
2089 * $object->having("sum(value) > 0 ");
2091 * @param bool $having condition
2092 * @return bool|none|PEAR
2095 public function having($having = false)
2097 if ($this->_query === false) {
2099 "You cannot do two queries on the same object (copy it before finding)",
2100 DB_DATAOBJECT_ERROR_INVALIDARGS
2104 if ($having === false) {
2105 $this->_query['having'] = '';
2108 // check input...= 0 or ' ' == error!
2109 if (!trim($having)) {
2110 return $this->raiseError("Having: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
2114 if (!$this->_query['having']) {
2115 $this->_query['having'] = " HAVING {$having} ";
2118 $this->_query['having'] .= " AND {$having}";
2123 * Adds a using Index
2125 * $object->useIndex(); //reset the use Index
2126 * $object->useIndex("some_index");
2128 * Note do not put unfiltered user input into theis method.
2129 * This is mysql specific at present? - might need altering to support other databases.
2131 * @param bool $index index or indexes to use.
2132 * @return bool|none|PEAR
2135 public function useIndex($index = false)
2137 if ($this->_query === false) {
2139 "You cannot do two queries on the same object (copy it before finding)",
2140 DB_DATAOBJECT_ERROR_INVALIDARGS
2144 if ($index === false) {
2145 $this->_query['useindex'] = '';
2148 // check input...= 0 or ' ' == error!
2149 if ((is_string($index) && !trim($index)) || (is_array($index) && !count($index))) {
2150 return $this->raiseError("Having: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
2152 $index = is_array($index) ? implode(', ', $index) : $index;
2154 if (!$this->_query['useindex']) {
2155 $this->_query['useindex'] = " USE INDEX ({$index}) ";
2158 $this->_query['useindex'] = substr($this->_query['useindex'], 0, -2) . ", {$index}) ";
2165 * $boject->limit(); // clear limit
2166 * $object->limit(12);
2167 * $object->limit(12,10);
2169 * Note this will emit an error on databases other than mysql/postgress
2170 * as there is no 'clean way' to implement it. - you should consider refering to
2171 * your database manual to decide how you want to implement it.
2173 * @param string $a limit start (or number), or blank to reset
2174 * @param string $b number
2175 * @return bool|none|PEAR
2178 public function limit($a = null, $b = null)
2180 if ($this->_query === false) {
2182 "You cannot do two queries on the same object (copy it before finding)",
2183 DB_DATAOBJECT_ERROR_INVALIDARGS
2189 $this->_query['limit_start'] = '';
2190 $this->_query['limit_count'] = '';
2193 // check input...= 0 or ' ' == error!
2194 if ((!is_int($a) && ((string)((int)$a) !== (string)$a))
2195 || (($b !== null) && (!is_int($b) && ((string)((int)$b) !== (string)$b)))) {
2196 return $this->raiseError("limit: No Valid Arguments", DB_DATAOBJECT_ERROR_INVALIDARGS);
2198 global $_DB_DATAOBJECT;
2200 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2202 $this->_query['limit_start'] = ($b == null) ? 0 : (int)$a;
2203 $this->_query['limit_count'] = ($b == null) ? (int)$a : (int)$b;
2208 * Insert the current objects variables into the database
2210 * Returns the ID of the inserted element (if auto increment or sequences are used.)
2214 * Designed to be extended
2216 * $object = new mytable();
2217 * $object->name = "fred";
2218 * echo $object->insert();
2221 * @return mixed false on failure, int when auto increment or sequence used, otherwise true on success
2223 public function insert()
2225 global $_DB_DATAOBJECT;
2227 // we need to write to the connection (For nextid) - so us the real
2228 // one not, a copyied on (as ret-by-ref fails with overload!)
2230 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
2234 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
2236 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2238 $items = $this->table();
2242 "insert:No table definition for {$this->tableName()}",
2243 DB_DATAOBJECT_ERROR_INVALIDCONFIG
2247 $options = $_DB_DATAOBJECT['CONFIG'];
2254 $seqKeys = isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()]) ?
2255 $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] :
2256 $this->sequenceKey();
2258 $key = isset($seqKeys[0]) ? $seqKeys[0] : false;
2259 $useNative = isset($seqKeys[1]) ? $seqKeys[1] : false;
2260 $seq = isset($seqKeys[2]) ? $seqKeys[2] : false;
2262 $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn["phptype"];
2265 // nativeSequences or Sequences..
2267 // big check for using sequences
2269 if (($key !== false) && !$useNative) {
2271 $keyvalue = $DB->nextId($this->tableName());
2273 $f = $DB->getOption('seqname_format');
2274 $DB->setOption('seqname_format', '%s');
2275 $keyvalue = $DB->nextId($seq);
2276 $DB->setOption('seqname_format', $f);
2278 if ((new PEAR)->isError($keyvalue)) {
2279 $this->raiseError($keyvalue->toString(), DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2282 $this->$key = $keyvalue;
2285 // if we haven't set disable_null_strings to "full"
2286 $ignore_null = !isset($options['disable_null_strings'])
2287 || !is_string($options['disable_null_strings'])
2288 || strtolower($options['disable_null_strings']) !== 'full';
2291 foreach ($items as $k => $v) {
2293 // if we are using autoincrement - skip the column...
2294 if ($key && ($k == $key) && $useNative) {
2299 // Ignore INTEGERS which aren't set to a value - or empty string..
2300 if ((!isset($this->$k) || ($v == 1 && $this->$k === ''))
2305 // dont insert data into mysql timestamps
2306 // use query() if you really want to do this!!!!
2307 if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
2316 $leftq .= ($quoteIdentifiers ? ($DB->quoteIdentifier($k) . ' ') : "$k ");
2318 if (is_object($this->$k) && is_a($this->$k, 'DB_DataObject_Cast')) {
2319 $value = $this->$k->toString($v, $DB);
2320 if ((new PEAR)->isError($value)) {
2321 $this->raiseError($value->toString(), DB_DATAOBJECT_ERROR_INVALIDARGS);
2329 if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this, $k)) {
2330 $rightq .= " NULL ";
2333 // DATE is empty... on a col. that can be null..
2334 // note: this may be usefull for time as well..
2336 (($v & DB_DATAOBJECT_DATE) || ($v & DB_DATAOBJECT_TIME)) &&
2337 !($v & DB_DATAOBJECT_NOTNULL)) {
2338 $rightq .= " NULL ";
2343 if ($v & DB_DATAOBJECT_STR) {
2344 $rightq .= $this->_quote((string)(
2345 ($v & DB_DATAOBJECT_BOOL) ?
2346 // this is thanks to the braindead idea of postgres to
2347 // use t/f for boolean.
2348 (($this->$k === 'f') ? 0 : (int)(bool)$this->$k) :
2353 if (is_numeric($this->$k)) {
2354 $rightq .= " {$this->$k} ";
2357 /* flag up string values - only at debug level... !!!??? */
2358 if (is_object($this->$k) || is_array($this->$k)) {
2359 $this->debug('ODD DATA: ' . $k . ' ' . print_r($this->$k, true), 'ERROR');
2362 // at present we only cast to integers
2363 // - V2 may store additional data about float/int
2364 $rightq .= ' ' . intval($this->$k) . ' ';
2367 // not sure why we let empty insert here.. - I guess to generate a blank row..
2370 if ($leftq || $useNative) {
2371 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
2374 if (($dbtype == 'pgsql') && empty($leftq)) {
2375 $r = $this->_query("INSERT INTO {$table} DEFAULT VALUES");
2377 $r = $this->_query("INSERT INTO {$table} ($leftq) VALUES ($rightq) ");
2381 if ((new PEAR)->isError($r)) {
2382 $this->raiseError($r);
2391 // now do we have an integer key!
2393 if ($key && $useNative) {
2397 $method = "{$dbtype}_insert_id";
2398 $this->$key = $method(
2399 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->connection
2404 // note this is not really thread safe - you should wrapp it with
2405 // transactions = eg.
2406 // $db->query('BEGIN');
2408 // $db->query('COMMIT');
2409 $db_driver = empty($options['db_driver']) ? 'DB' : $options['db_driver'];
2410 $method = ($db_driver == 'DB') ? 'getOne' : 'queryOne';
2411 $mssql_key = $DB->$method("SELECT @@IDENTITY");
2412 if ((new PEAR)->isError($mssql_key)) {
2413 $this->raiseError($mssql_key);
2416 $this->$key = $mssql_key;
2421 $seq = $DB->getSequenceName(strtolower($this->tableName()));
2423 $db_driver = empty($options['db_driver']) ? 'DB' : $options['db_driver'];
2424 $method = ($db_driver == 'DB') ? 'getOne' : 'queryOne';
2425 $pgsql_key = $DB->$method("SELECT currval('" . $seq . "')");
2428 if ((new PEAR)->isError($pgsql_key)) {
2429 $this->raiseError($pgsql_key);
2432 $this->$key = $pgsql_key;
2436 $this->$key = array_shift(
2439 "select DBINFO('sqlca.sqlerrd1') FROM systables where tabid=1",
2440 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->connection,
2451 if (isset($_DB_DATAOBJECT['CACHE'][strtolower(get_class($this))])) {
2452 $this->_clear_cache();
2459 $this->raiseError("insert: No Data specifed for query", DB_DATAOBJECT_ERROR_NODATA);
2464 * get/set an sequence key
2466 * by default it returns the first key from keys()
2467 * set usage: $do->sequenceKey('id',true);
2469 * override this to return array(false,false) if table has no real sequence key.
2471 * @param string optional the key sequence/autoinc. key
2472 * @param boolean optional use native increment. default false
2473 * @param false|string optional native sequence name
2475 * @return array (column,use_native,sequence_name)
2477 public function sequenceKey()
2479 global $_DB_DATAOBJECT;
2482 if (!$this->_database) {
2486 if (!isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database])) {
2487 $_DB_DATAOBJECT['SEQUENCE'][$this->_database] = array();
2491 $args = func_get_args();
2493 $args[1] = isset($args[1]) ? $args[1] : false;
2494 $args[2] = isset($args[2]) ? $args[2] : false;
2495 $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = $args;
2497 if (isset($_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()])) {
2498 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()];
2500 // end call setting (eg. $do->sequenceKeys(a,b,c); )
2503 $keys = $this->keys();
2505 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()]
2506 = array(false, false, false);
2510 $table = $this->table();
2512 $dbtype = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->dsn['phptype'];
2519 if (!empty($_DB_DATAOBJECT['CONFIG']['sequence_' . $this->tableName()])) {
2520 $seqname = $_DB_DATAOBJECT['CONFIG']['sequence_' . $this->tableName()];
2521 if (strpos($seqname, ':') !== false) {
2522 list($usekey, $seqname) = explode(':', $seqname);
2527 // if the key is not an integer - then it's not a sequence or native
2528 if (empty($table[$usekey]) || !($table[$usekey] & DB_DATAOBJECT_INT)) {
2529 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array(false, false, false);
2533 if (!empty($_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'])) {
2534 $ignore = $_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'];
2535 if (is_string($ignore) && (strtoupper($ignore) == 'ALL')) {
2536 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array(false, false, $seqname);
2538 if (is_string($ignore)) {
2539 $ignore = $_DB_DATAOBJECT['CONFIG']['ignore_sequence_keys'] = explode(',', $ignore);
2541 if (in_array($this->tableName(), $ignore)) {
2542 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array(false, false, $seqname);
2547 $realkeys = $_DB_DATAOBJECT['INI'][$this->_database][$this->tableName() . "__keys"];
2549 // if you are using an old ini file - go back to old behaviour...
2550 if (is_numeric($realkeys[$usekey])) {
2551 $realkeys[$usekey] = 'N';
2554 // multiple unique primary keys without a native sequence...
2555 if (($realkeys[$usekey] == 'K') && (count($keys) > 1)) {
2556 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array(false, false, $seqname);
2558 // use native sequence keys...
2559 // technically postgres native here...
2560 // we need to get the new improved tabledata sorted out first.
2562 // support named sequence keys.. - currently postgres only..
2564 if (in_array($dbtype, array('pgsql')) &&
2565 ($table[$usekey] & DB_DATAOBJECT_INT) &&
2566 isset($realkeys[$usekey]) && strlen($realkeys[$usekey]) > 1) {
2567 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array($usekey, true, $realkeys[$usekey]);
2570 if (in_array($dbtype, array('pgsql', 'mysql', 'mysqli', 'mssql', 'ifx')) &&
2571 ($table[$usekey] & DB_DATAOBJECT_INT) &&
2572 isset($realkeys[$usekey]) && ($realkeys[$usekey] == 'N')
2574 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array($usekey, true, $seqname);
2578 // if not a native autoinc, and we have not assumed all primary keys are sequence
2579 if (($realkeys[$usekey] != 'N') &&
2580 !empty($_DB_DATAOBJECT['CONFIG']['dont_use_pear_sequences'])) {
2581 return array(false, false, false);
2585 // I assume it's going to try and be a nextval DB sequence.. (not native)
2586 return $_DB_DATAOBJECT['SEQUENCE'][$this->_database][$this->tableName()] = array($usekey, false, $seqname);
2590 * clear the cache values for this class - normally done on insert/update etc.
2595 public function _clear_cache()
2597 global $_DB_DATAOBJECT;
2599 $class = strtolower(get_class($this));
2601 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2602 $this->debug("Clearing Cache for " . $class, 1);
2605 if (!empty($_DB_DATAOBJECT['CACHE'][$class])) {
2606 unset($_DB_DATAOBJECT['CACHE'][$class]);
2611 * Updates current objects variables into the database
2612 * uses the keys() to decide how to update
2613 * Returns the true on success
2617 * $object = DB_DataObject::factory('mytable');
2618 * $object->get("ID",234);
2619 * $object->email="testing@test.com";
2620 * if(!$object->update())
2621 * echo "UPDATE FAILED";
2623 * to only update changed items :
2624 * $dataobject->get(132);
2625 * $original = $dataobject; // clone/copy it..
2626 * $dataobject->setFrom($_POST);
2627 * if ($dataobject->validate()) {
2628 * $dataobject->update($original);
2629 * } // otherwise an error...
2631 * performing global updates:
2632 * $object = DB_DataObject::factory('mytable');
2633 * $object->status = "dead";
2634 * $object->whereAdd('age > 150');
2635 * $object->update(DB_DATAOBJECT_WHEREADD_ONLY);
2637 * @param bool $dataObject
2638 * @return int rows affected or false on failure
2641 public function update($dataObject = false)
2643 global $_DB_DATAOBJECT;
2644 // connect will load the config!
2648 $original_query = $this->_query;
2650 $items = $this->table();
2652 // only apply update against sequence key if it is set?????
2654 $seq = $this->sequenceKey();
2655 if ($seq[0] !== false) {
2656 $keys = array($seq[0]);
2657 if (!isset($this->{$keys[0]}) && $dataObject !== true) {
2658 $this->raiseError("update: trying to perform an update without
2659 the key set, and argument to update is not
2660 DB_DATAOBJECT_WHEREADD_ONLY
2661 " . print_r(array('seq' => $seq, 'keys' => $keys), true), DB_DATAOBJECT_ERROR_INVALIDARGS);
2665 $keys = $this->keys();
2670 $this->raiseError("update:No table definition for {$this->tableName()}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2677 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2678 $dbtype = $DB->dsn["phptype"];
2679 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
2680 $options = $_DB_DATAOBJECT['CONFIG'];
2683 $ignore_null = !isset($options['disable_null_strings'])
2684 || !is_string($options['disable_null_strings'])
2685 || strtolower($options['disable_null_strings']) !== 'full';
2688 foreach ($items as $k => $v) {
2690 // I think this is ignoring empty vlalues
2691 if ((!isset($this->$k) || ($v == 1 && $this->$k === ''))
2696 // ignore stuff thats
2698 // dont write things that havent changed..
2699 if (($dataObject !== false) && isset($dataObject->$k) && ($dataObject->$k === $this->$k)) {
2703 // - dont write keys to left.!!!
2704 if (in_array($k, $keys)) {
2708 // dont insert data into mysql timestamps
2709 // use query() if you really want to do this!!!!
2710 if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
2719 $kSql = ($quoteIdentifiers ? $DB->quoteIdentifier($k) : $k);
2721 if (is_object($this->$k) && is_a($this->$k, 'DB_DataObject_Cast')) {
2722 $value = $this->$k->toString($v, $DB);
2723 if ((new PEAR)->isError($value)) {
2724 $this->raiseError($value->getMessage(), DB_DATAOBJECT_ERROR_INVALIDARG);
2727 $settings .= "$kSql = $value ";
2731 // special values ... at least null is handled...
2732 if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this, $k)) {
2733 $settings .= "$kSql = NULL ";
2736 // DATE is empty... on a col. that can be null..
2737 // note: this may be usefull for time as well..
2739 (($v & DB_DATAOBJECT_DATE) || ($v & DB_DATAOBJECT_TIME)) &&
2740 !($v & DB_DATAOBJECT_NOTNULL)) {
2741 $settings .= "$kSql = NULL ";
2746 if ($v & DB_DATAOBJECT_STR) {
2747 $settings .= "$kSql = " . $this->_quote((string)(
2748 ($v & DB_DATAOBJECT_BOOL) ?
2749 // this is thanks to the braindead idea of postgres to
2750 // use t/f for boolean.
2751 (($this->$k === 'f') ? 0 : (int)(bool)$this->$k) :
2756 if (is_numeric($this->$k)) {
2757 $settings .= "$kSql = {$this->$k} ";
2760 // at present we only cast to integers
2761 // - V2 may store additional data about float/int
2762 $settings .= "$kSql = " . intval($this->$k) . ' ';
2766 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2767 $this->debug("got keys as " . serialize($keys), 3);
2769 if ($dataObject !== true) {
2770 $this->_build_condition($items, $keys);
2772 // prevent wiping out of data!
2773 if (empty($this->_query['condition'])) {
2774 $this->raiseError("update: global table update not available
2775 do \$do->whereAdd('1=1'); if you really want to do that.
2776 ", DB_DATAOBJECT_ERROR_INVALIDARGS);
2782 // echo " $settings, $this->condition ";
2783 if ($settings && isset($this->_query) && $this->_query['condition']) {
2784 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
2786 $r = $this->_query("UPDATE {$table} SET {$settings} {$this->_query['condition']} ");
2788 // restore original query conditions.
2789 $this->_query = $original_query;
2791 if ((new PEAR)->isError($r)) {
2792 $this->raiseError($r);
2799 $this->_clear_cache();
2802 // restore original query conditions.
2803 $this->_query = $original_query;
2805 // if you manually specified a dataobject, and there where no changes - then it's ok..
2806 if ($dataObject !== false) {
2811 "update: No Data specifed for query $settings , {$this->_query['condition']}",
2812 DB_DATAOBJECT_ERROR_NODATA
2818 * Deletes items from table which match current objects variables
2820 * Returns the true on success
2824 * Designed to be extended
2826 * $object = new mytable();
2828 * echo $object->delete(); // builds a conditon
2830 * $object = new mytable();
2831 * $object->whereAdd('age > 12');
2832 * $object->limit(1);
2833 * $object->orderBy('age DESC');
2834 * $object->delete(true); // dont use object vars, use the conditions, limit and order.
2836 * @param bool $useWhere (optional) If DB_DATAOBJECT_WHEREADD_ONLY is passed in then
2837 * we will build the condition only using the whereAdd's. Default is to
2838 * build the condition only using the object parameters.
2841 * @return mixed Int (No. of rows affected) on success, false on failure, 0 on no data affected
2843 public function delete($useWhere = false)
2845 global $_DB_DATAOBJECT;
2846 // connect will load the config!
2848 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2849 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
2851 $extra_cond = ' ' . (isset($this->_query['order_by']) ? $this->_query['order_by'] : '');
2854 $keys = $this->keys();
2855 $this->_query = array(); // as it's probably unset!
2856 $this->_query['condition'] = ''; // default behaviour not to use where condition
2857 $this->_build_condition($this->table(), $keys);
2858 // if primary keys are not set then use data from rest of object.
2859 if (!$this->_query['condition']) {
2860 $this->_build_condition($this->table(), array(), $keys);
2866 // don't delete without a condition
2867 if (($this->_query !== false) && $this->_query['condition']) {
2868 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
2870 // using a joined delete. - with useWhere..
2871 $sql .= (!empty($this->_join) && $useWhere) ?
2872 "{$table} FROM {$table} {$this->_join} " :
2875 $sql .= $this->_query['condition'] . $extra_cond;
2879 if (isset($this->_query['limit_start']) && strlen($this->_query['limit_start'] . $this->_query['limit_count'])) {
2880 if (!isset($_DB_DATAOBJECT['CONFIG']['db_driver']) ||
2881 ($_DB_DATAOBJECT['CONFIG']['db_driver'] == 'DB')) {
2883 $sql = $DB->modifyLimitQuery($sql, $this->_query['limit_start'], $this->_query['limit_count']);
2886 $DB->setLimit($this->_query['limit_count'], $this->_query['limit_start']);
2891 $r = $this->_query($sql);
2894 if ((new PEAR)->isError($r)) {
2895 $this->raiseError($r);
2901 $this->_clear_cache();
2904 $this->raiseError("delete: No condition specifed for query", DB_DATAOBJECT_ERROR_NODATA);
2910 * fetches a specific row into this object variables
2912 * Not recommended - better to use fetch()
2914 * Returens true on success
2916 * @param int $row row
2918 * @return boolean true on success
2920 public function fetchRow($row = null)
2922 global $_DB_DATAOBJECT;
2923 if (empty($_DB_DATAOBJECT['CONFIG'])) {
2924 $this->_loadConfig();
2926 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2927 $this->debug("{$this->tableName()} $row of {$this->N}", "fetchrow", 3);
2929 if (!$this->tableName()) {
2930 $this->raiseError("fetchrow: No table", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
2933 if ($row === null) {
2934 $this->raiseError("fetchrow: No row specified", DB_DATAOBJECT_ERROR_INVALIDARGS);
2938 $this->raiseError("fetchrow: No results avaiable", DB_DATAOBJECT_ERROR_NODATA);
2941 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2942 $this->debug("{$this->tableName()} $row of {$this->N}", "fetchrow", 3);
2946 $result = $_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid];
2947 $array = $result->fetchrow(DB_DATAOBJECT_FETCHMODE_ASSOC, $row);
2948 if (!is_array($array)) {
2949 $this->raiseError("fetchrow: No results available", DB_DATAOBJECT_ERROR_NODATA);
2952 $replace = array('.', ' ');
2953 foreach ($array as $k => $v) {
2954 // use strpos as str_replace is slow.
2955 $kk = (strpos($k, '.') === false && strpos($k, ' ') === false) ?
2956 $k : str_replace($replace, '_', $k);
2958 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2959 $this->debug("$kk = " . $array[$k], "fetchrow LINE", 3);
2961 $this->$kk = $array[$k];
2964 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
2965 $this->debug("{$this->tableName()} DONE", "fetchrow", 3);
2971 * Find the number of results from a simple query
2975 * $object = new mytable();
2976 * $object->name = "fred";
2977 * echo $object->count();
2978 * echo $object->count(true); // dont use object vars.
2979 * echo $object->count('distinct mycol'); count distinct mycol.
2980 * echo $object->count('distinct mycol',true); // dont use object vars.
2981 * echo $object->count('distinct'); // count distinct id (eg. the primary key)
2984 * @param bool|string (optional)
2985 * (true|false => see below not on whereAddonly)
2987 * "DISTINCT" => does a distinct count on the tables 'key' column
2988 * otherwise => normally it counts primary keys - you can use
2989 * this to do things like $do->count('distinct mycol');
2991 * @param bool $whereAddOnly (optional) If DB_DATAOBJECT_WHEREADD_ONLY is passed in then
2992 * we will build the condition only using the whereAdd's. Default is to
2993 * build the condition using the object parameters as well.
2998 public function count($countWhat = false, $whereAddOnly = false)
3000 global $_DB_DATAOBJECT;
3002 if (is_bool($countWhat)) {
3003 $whereAddOnly = $countWhat;
3007 $items = $t->table();
3009 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
3012 if (!isset($t->_query)) {
3014 "You cannot do run count after you have run fetch()",
3015 DB_DATAOBJECT_ERROR_INVALIDARGS
3020 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
3023 if (!$whereAddOnly && $items) {
3024 $t->_build_condition($items);
3026 $keys = $this->keys();
3028 if (empty($keys[0]) && (!is_string($countWhat) || (strtoupper($countWhat) == 'DISTINCT'))) {
3030 "You cannot do run count without keys - use \$do->count('id'), or use \$do->count('distinct id')';",
3031 DB_DATAOBJECT_ERROR_INVALIDARGS,
3036 $table = ($quoteIdentifiers ? $DB->quoteIdentifier($this->tableName()) : $this->tableName());
3037 $key_col = empty($keys[0]) ? '' : (($quoteIdentifiers ? $DB->quoteIdentifier($keys[0]) : $keys[0]));
3038 $as = ($quoteIdentifiers ? $DB->quoteIdentifier('DATAOBJECT_NUM') : 'DATAOBJECT_NUM');
3040 // support distinct on default keys.
3041 $countWhat = (strtoupper($countWhat) == 'DISTINCT') ?
3042 "DISTINCT {$table}.{$key_col}" : $countWhat;
3044 $countWhat = is_string($countWhat) ? $countWhat : "{$table}.{$key_col}";
3047 "SELECT count({$countWhat}) as $as
3048 FROM $table {$t->_join} {$t->_query['condition']}"
3050 if ((new PEAR)->isError($r)) {
3054 $result = $_DB_DATAOBJECT['RESULTS'][$t->_DB_resultid];
3055 $l = $result->fetchRow(DB_DATAOBJECT_FETCHMODE_ORDERED);
3056 // free the results - essential on oracle.
3058 if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
3059 $this->debug('Count returned ' . $l[0], 1);
3065 * Free global arrays associated with this object.
3071 public function free()
3073 global $_DB_DATAOBJECT;
3075 if (isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid])) {
3076 unset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]);
3078 if (isset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
3079 unset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid]);
3081 // clear the staticGet cache as well.
3082 $this->_clear_cache();
3083 // this is a huge bug in DB!
3084 if (isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
3085 $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5]->num_rows = array();
3088 if (is_array($this->_link_loaded)) {
3089 foreach ($this->_link_loaded as $do) {
3091 !empty($this->{$do}) &&
3092 is_object($this->{$do}) &&
3093 method_exists($this->{$do}, 'free')
3095 $this->{$do}->free();
3103 * sends raw query to database
3105 * Since _query has to be a private 'non overwriteable method', this is a relay
3107 * @param string $string SQL Query
3109 * @return void or DB_Error
3111 public function query($string)
3113 return $this->_query($string);
3117 * an escape wrapper around DB->escapeSimple()
3118 * can be used when adding manual queries or clauses
3120 * $object->query("select * from xyz where abc like '". $object->escape($_GET['name']) . "'");
3122 * @param string $string value to be escaped
3123 * @param bool $likeEscape escapes % and _ as well. - so like queries can be protected.
3127 public function escape($string, $likeEscape = false)
3129 global $_DB_DATAOBJECT;
3131 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
3132 // mdb2 uses escape...
3133 $dd = empty($_DB_DATAOBJECT['CONFIG']['db_driver']) ? 'DB' : $_DB_DATAOBJECT['CONFIG']['db_driver'];
3134 $ret = ($dd == 'DB') ? $DB->escapeSimple($string) : $DB->escape($string);
3136 $ret = str_replace(array('_', '%'), array('\_', '\%'), $ret);
3142 * Return or assign the name of the current database
3144 * @param string optional database name to set
3146 * @return string The name of the current database
3148 public function database()
3150 $args = func_get_args();
3152 $this->_database = $args[0];
3157 return $this->_database;
3161 * generic getter/setter for links
3163 * This is the new 'recommended' way to get get/set linked objects.
3164 * must be used with links.ini
3168 * $obj = $do->link('company_id');
3169 * $obj = $do->link(array('local_col', 'linktable:linked_col'));
3172 * $do->link('company_id',0);
3173 * $do->link('company_id',$obj);
3174 * $do->link('company_id', array($obj));
3178 * function company() {
3179 * $this->link(array('company_id','company:id'), func_get_args());
3185 * @param array $set_args
3186 * @return mixed true or false on setting, object on getting
3187 * @author Alan Knowles
3190 public function link($field, $set_args = array())
3192 //require_once 'DB/DataObject/Links.php';
3193 require_once 'Links.php';
3194 $l = new DB_DataObject_Links($this);
3195 return $l->link($field, $set_args);
3199 * load related objects
3201 * Generally not recommended to use this.
3202 * The generator should support creating getter_setter methods which are better suited.
3204 * Relies on <dbname>.links.ini
3206 * Sets properties on the calling dataobject you can change what
3207 * object vars the links are stored in by changeing the format parameter
3210 * @param string format (default _%s) where %s is the table name.
3211 * @return boolean , true on success
3212 * @author Tim White <tim@cyface.com>
3215 public function getLinks($format = '_%s')
3217 //require_once 'DB/DataObject/Links.php';
3218 require_once 'Links.php';
3219 $l = new DB_DataObject_Links($this);
3220 return $l->applyLinks($format);
3224 * deprecited : @use link()
3226 * @param null $table
3230 public function getLink($row, $table = null, $link = false)
3232 //require_once 'DB/DataObject/Links.php';
3233 require_once 'Links.php';
3234 $l = new DB_DataObject_Links($this);
3235 return $l->getLink($row, $table === null ? false : $table, $link);
3240 * Fetch an array of related objects. This should be used in conjunction with a <dbname>.links.ini file configuration (see the introduction on linking for details on this).
3241 * You may also use this with all parameters to specify, the column and related table.
3242 * This is highly dependant on naming columns 'correctly' :)
3243 * using colname = xxxxx_yyyyyy
3244 * xxxxxx = related table; (yyyyy = user defined..)
3245 * looks up table xxxxx, for value id=$this->xxxxx
3246 * stores it in $this->_xxxxx_yyyyy
3250 * @param string $table - name of table to look up value in
3251 * @return array - array of results (empty array on failure)
3253 * Example - Getting the related objects
3255 * $person = new DataObjects_Person;
3257 * $children = $person->getLinkArray('children');
3259 * echo 'There are ', count($children), ' descendant(s):<br />';
3260 * foreach ($children as $child) {
3261 * echo $child->name, '<br />';
3264 public function getLinkArray($row, $table = null)
3266 //require_once 'DB/DataObject/Links.php';
3267 require_once 'Links.php';
3268 $l = new DB_DataObject_Links($this);
3269 return $l->getLinkArray($row, $table === null ? false : $table);
3273 * unionAdd - adds another dataobject to this, building a unioned query.
3276 * $doTable1 = DB_DataObject::factory("table1");
3277 * $doTable2 = DB_DataObject::factory("table2");
3279 * $doTable1->selectAdd();
3280 * $doTable1->selectAdd("col1,col2");
3281 * $doTable1->whereAdd("col1 > 100");
3282 * $doTable1->orderBy("col1");
3284 * $doTable2->selectAdd();
3285 * $doTable2->selectAdd("col1, col2");
3286 * $doTable2->whereAdd("col2 = 'v'");
3288 * $doTable1->unionAdd($doTable2);
3289 * $doTable1->find();
3291 * Note: this model may be a better way to implement joinAdd?, eg. do the building in find?
3294 * @param $obj object|false the union object or false to reset
3295 * @param string $is_all string 'ALL' to do all.
3296 * @return false|mixed|object
3299 public function unionAdd($obj, $is_all = '')
3301 if ($obj === false) {
3302 $ret = $this->_query['unions'];
3303 $this->_query['unions'] = array();
3306 $this->_query['unions'][] = array($obj, 'UNION ' . $is_all . ' ');
3311 * autoJoin - using the links.ini file, it builds a query with all the joins
3313 * $x = DB_DataObject::factory('mytable');
3316 * will result in all of the joined data being added to the fetched object..
3318 * $x = DB_DataObject::factory('mytable');
3320 * $ar = $x->fetchAll();
3321 * will result in an array containing all the data from the table, and any joined tables..
3323 * $x = DB_DataObject::factory('mytable');
3324 * $jdata = $x->autoJoin();
3325 * $x->selectAdd(); //reset..
3326 * foreach($_REQUEST['requested_cols'] as $c) {
3327 * if (!isset($jdata[$c])) continue; // ignore columns not available..
3328 * $x->selectAdd( $jdata[$c] . ' as ' . $c);
3330 * $ar = $x->fetchAll();
3331 * will result in only the columns requested being fetched...
3335 * @param array Configuration
3336 * exclude Array of columns to exclude from results (eg. modified_by_id)
3337 * links The equivilant links.ini data for this table eg.
3338 * array( 'person_id' => 'person:id', .... )
3339 * include Array of columns to include
3340 * distinct Array of distinct columns.
3342 * @return array info about joins
3343 * cols => map of resulting {joined_tablename}.{joined_table_column_name}
3344 * join_names => map of resulting {join_name_as}.{joined_table_column_name}
3345 * count => the column to count on.
3348 public function autoJoin($cfg = array())
3350 global $_DB_DATAOBJECT;
3351 //var_Dump($cfg);exit;
3352 $pre_links = $this->links();
3353 if (!empty($cfg['links'])) {
3354 $this->links(array_merge($pre_links, $cfg['links']));
3356 $map = $this->links();
3358 $this->databaseStructure();
3359 $dbstructure = $_DB_DATAOBJECT['INI'][$this->_database];
3361 $tabdef = $this->table();
3363 // we need this as normally it's only cleared by an empty selectAs call.
3366 $keys = array_keys($tabdef);
3367 if (!empty($cfg['exclude'])) {
3368 $keys = array_intersect($keys, array_diff($keys, $cfg['exclude']));
3370 if (!empty($cfg['include'])) {
3371 $keys = array_intersect($keys, $cfg['include']);
3374 $selectAs = array();
3376 if (!empty($keys)) {
3377 $selectAs = array(array($keys, '%s', false));
3382 'join_names' => array(),
3387 $has_distinct = false;
3388 if (!empty($cfg['distinct']) && $keys) {
3390 // reset the columsn?
3393 //echo '<PRE>' ;print_r($xx);exit;
3394 foreach ($keys as $c) {
3397 if ($cfg['distinct'] == $c) {
3398 $has_distinct = 'DISTINCT( ' . $this->tableName() . '.' . $c . ') as ' . $c;
3399 $ret['count'] = 'DISTINCT ' . $this->tableName() . '.' . $c . '';
3402 // cols is in our filtered keys...
3405 // apply our filtered version, which excludes the distinct column.
3407 $selectAs = empty($cols) ? array() : array(array(array($cols), '%s', false));
3410 foreach ($keys as $k) {
3411 $ret['cols'][$k] = $this->tableName() . '.' . $k;
3415 foreach ($map as $ocl => $info) {
3416 list($tab, $col) = explode(':', $info);
3417 // what about multiple joins on the same table!!!
3419 // if links point to a table that does not exist - ignore.
3420 if (!isset($dbstructure[$tab])) {
3424 $xx = DB_DataObject::factory($tab);
3425 if (!is_object($xx) || !is_a($xx, 'DB_DataObject')) {
3428 // skip columns that are excluded.
3430 // we ignore include here... - as
3432 // this is borked ... for multiple jions..
3433 $this->joinAdd($xx, 'LEFT', 'join_' . $ocl . '_' . $col, $ocl);
3435 if (!empty($cfg['exclude']) && in_array($ocl, $cfg['exclude'])) {
3439 $tabdef = $xx->table();
3440 $table = $xx->tableName();
3442 $keys = array_keys($tabdef);
3445 if (!empty($cfg['exclude'])) {
3446 $keys = array_intersect($keys, array_diff($keys, $cfg['exclude']));
3448 foreach ($keys as $k) {
3449 if (in_array($ocl . '_' . $k, $cfg['exclude'])) {
3450 $keys = array_diff($keys, $k); // removes the k..
3455 if (!empty($cfg['include'])) {
3456 // include will basically be BASECOLNAME_joinedcolname
3458 foreach ($keys as $k) {
3459 if (in_array(sprintf($ocl . '_%s', $k), $cfg['include'])) {
3469 // got distinct, and not yet found it..
3470 if (!$has_distinct && !empty($cfg['distinct'])) {
3472 foreach ($keys as $c) {
3473 $tn = sprintf($ocl . '_%s', $c);
3475 if ($tn == $cfg['distinct']) {
3476 $has_distinct = 'DISTINCT( ' . 'join_' . $ocl . '_' . $col . '.' . $c . ') as ' . $tn;
3477 $ret['count'] = 'DISTINCT join_' . $ocl . '_' . $col . '.' . $c;
3478 // var_dump($this->countWhat );
3484 if (!empty($cols)) {
3485 $selectAs[] = array($cols, $ocl . '_%s', 'join_' . $ocl . '_' . $col);
3488 $selectAs[] = array($keys, $ocl . '_%s', 'join_' . $ocl . '_' . $col);
3491 foreach ($keys as $k) {
3492 $ret['cols'][sprintf('%s_%s', $ocl, $k)] = $tab . '.' . $k;
3493 $ret['join_names'][sprintf('%s_%s', $ocl, $k)] = sprintf('join_%s_%s.%s', $ocl, $col, $k);
3497 // fill in the select details..
3500 if ($has_distinct) {
3501 $this->selectAdd($has_distinct);
3504 foreach ($selectAs as $ar) {
3505 $this->selectAs($ar[0], $ar[1], $ar[2]);
3508 $this->links($pre_links);
3514 * Get the links associate array as defined by the links.ini file.
3518 * Should look a bit like
3519 * [local_col_name] => "related_tablename:related_col_name"
3521 * @return array|null
3522 * array = if there are links defined for this table.
3523 * empty array - if there is a links.ini file, but no links on this table
3524 * false - if no links.ini exists for this database (hence try auto_links).
3526 * @see DB_DataObject::getLinks(), DB_DataObject::getLink()
3529 public function links()
3531 global $_DB_DATAOBJECT;
3532 if (empty($_DB_DATAOBJECT['CONFIG'])) {
3533 $this->_loadConfig();
3535 // have to connect.. -> otherwise things break later.
3538 // alias for shorter code..
3539 $lcfg = &$_DB_DATAOBJECT['LINKS'];
3540 $cfg = $_DB_DATAOBJECT['CONFIG'];
3542 if ($args = func_get_args()) {
3543 // an associative array was specified, that updates the current
3544 // schema... - be careful doing this
3545 if (empty($lcfg[$this->_database])) {
3546 $lcfg[$this->_database] = array();
3548 $lcfg[$this->_database][$this->tableName()] = $args[0];
3550 // loaded and available.
3551 if (isset($lcfg[$this->_database][$this->tableName()])) {
3552 return $lcfg[$this->_database][$this->tableName()];
3556 if (isset($lcfg[$this->_database])) {
3557 // either no file, or empty..
3558 return $lcfg[$this->_database] === false ? null : array();
3561 // links are same place as schema by default.
3562 $schemas = isset($cfg['schema_location']) ?
3563 array("{$cfg['schema_location']}/{$this->_database}.ini") :
3566 // if ini_* is set look there instead.
3567 // and support multiple locations.
3568 if (isset($cfg["ini_{$this->_database}"])) {
3569 $schemas = is_array($cfg["ini_{$this->_database}"]) ?
3570 $cfg["ini_{$this->_database}"] :
3571 explode(PATH_SEPARATOR, $cfg["ini_{$this->_database}"]);
3574 // default to not available.
3575 $lcfg[$this->_database] = false;
3577 foreach ($schemas as $ini) {
3578 $links = isset($cfg["links_{$this->_database}"]) ?
3579 $cfg["links_{$this->_database}"] :
3580 str_replace('.ini', '.links.ini', $ini);
3582 // file really exists..
3583 if (!file_exists($links) || !is_file($links)) {
3584 if (!empty($cfg['debug'])) {
3585 $this->debug("Missing links.ini file: $links", "links", 1);
3590 // set to empty array - as we have at least one file now..
3591 $lcfg[$this->_database] = empty($lcfg[$this->_database]) ? array() : $lcfg[$this->_database];
3593 // merge schema file into lcfg..
3594 $lcfg[$this->_database] = array_merge(
3595 $lcfg[$this->_database],
3596 parse_ini_file($links, true)
3600 if (!empty($cfg['debug'])) {
3601 $this->debug("Loaded links.ini file: $links", "links", 1);
3605 if (!empty($_DB_DATAOBJECT['CONFIG']['portability']) && $_DB_DATAOBJECT['CONFIG']['portability'] & 1) {
3606 foreach ($lcfg[$this->_database] as $k => $v) {
3607 $nk = strtolower($k);
3608 // results in duplicate cols.. but not a big issue..
3609 $lcfg[$this->_database][$nk] = isset($lcfg[$this->_database][$nk])
3610 ? $lcfg[$this->_database][$nk] : array();
3612 foreach ($v as $kk => $vv) {
3613 //var_Dump($vv);exit;
3614 $vv = explode(':', $vv);
3615 $vv[0] = strtolower($vv[0]);
3616 $lcfg[$this->_database][$nk][$kk] = implode(':', $vv);
3620 //echo '<PRE>';print_r($lcfg);exit;
3622 // if there is no link data at all on the file!
3624 if ($lcfg[$this->_database] === false) {
3628 if (isset($lcfg[$this->_database][$this->tableName()])) {
3629 return $lcfg[$this->_database][$this->tableName()];
3636 * joinAdd - adds another dataobject to this, building a joined query.
3638 * example (requires links.ini to be set up correctly)
3639 * // get all the images for product 24
3640 * $i = new DataObject_Image();
3641 * $pi = new DataObjects_Product_image();
3642 * $pi->product_id = 24; // set the product id to 24
3643 * $i->joinAdd($pi); // add the product_image connectoin
3645 * while ($i->fetch()) {
3648 * // an example with 2 joins
3649 * // get all the images linked with products or productgroups
3650 * $i = new DataObject_Image();
3651 * $pi = new DataObject_Product_image();
3652 * $pgi = new DataObject_Productgroup_image();
3654 * $i->joinAdd($pgi);
3656 * while ($i->fetch()) {
3661 * @param bool $obj object |array the joining object (no value resets the join)
3662 * If you use an array here it should be in the format:
3663 * array('local_column','remotetable:remote_column');
3664 * if remotetable does not have a definition, you should
3665 * use @ to hide the include error message..
3666 * array('local_column', $dataobject , 'remote_column');
3667 * if array has 3 args, then second is assumed to be the linked dataobject.
3669 * @param string $joinType string | array
3670 * 'LEFT'|'INNER'|'RIGHT'|'' Inner is default, '' indicates
3671 * just select ... from a,b,c with no join and
3672 * links are added as where items.
3674 * If second Argument is array, it is assumed to be an associative
3675 * array with arguments matching below = eg.
3676 * 'joinType' => 'INNER',
3679 * 'useWhereAsOn' => false,
3681 * @param bool $joinAs string if you want to select the table as anther name
3682 * useful when you want to select multiple columsn
3683 * from a secondary table.
3684 * @param bool $joinCol string The column on This objects table to match (needed
3685 * if this table links to the child object in
3686 * multiple places eg.
3687 * user->friend (is a link to another user)
3688 * user->mother (is a link to another user..)
3690 * optional 'useWhereAsOn' bool default false;
3691 * convert the where argments from the object being added
3692 * into ON arguments.
3695 * @return error|none
3697 * @author Stijn de Reede <sjr@gmx.co.uk>
3699 public function joinAdd($obj = false, $joinType = 'INNER', $joinAs = false, $joinCol = false)
3701 global $_DB_DATAOBJECT;
3702 if ($obj === false) {
3707 //echo '<PRE>'; print_r(func_get_args());
3708 $useWhereAsOn = false;
3709 // support for 2nd argument as an array of options
3710 if (is_array($joinType)) {
3711 // new options can now go in here... (dont forget to document them)
3712 $useWhereAsOn = !empty($joinType['useWhereAsOn']);
3713 $joinCol = isset($joinType['joinCol']) ? $joinType['joinCol'] : $joinCol;
3714 $joinAs = isset($joinType['joinAs']) ? $joinType['joinAs'] : $joinAs;
3715 $joinType = isset($joinType['joinType']) ? $joinType['joinType'] : 'INNER';
3717 // support for array as first argument
3718 // this assumes that you dont have a links.ini for the specified table.
3719 // and it doesnt exist as am extended dataobject!! - experimental.
3721 $ofield = false; // object field
3722 $tfield = false; // this field
3724 if (is_array($obj)) {
3727 if (count($obj) == 3) {
3731 list($toTable, $ofield) = explode(':', $obj[1]);
3733 $obj = is_string($toTable) ? DB_DataObject::factory($toTable) : $toTable;
3735 if (!$obj || !is_object($obj) || is_a($obj, 'PEAR_Error')) {
3736 $obj = new DB_DataObject;
3737 $obj->__table = $toTable;
3741 // set the table items to nothing.. - eg. do not try and match
3742 // things in the child table...???
3746 if (!is_object($obj) || !is_a($obj, 'DB_DataObject')) {
3747 return $this->raiseError("joinAdd: called without an object", DB_DATAOBJECT_ERROR_NODATA, PEAR_ERROR_DIE);
3749 /* make sure $this->_database is set. */
3751 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
3754 /// CHANGED 26 JUN 2009 - we prefer links from our local table over the remote one.
3756 /* otherwise see if there are any links from this table to the obj. */
3757 //print_r($this->links());
3758 if (($ofield === false) && ($links = $this->links())) {
3759 // this enables for support for arrays of links in ini file.
3760 // link contains this_column[] = linked_table:linked_column
3762 // link contains this_column = linked_table:linked_column
3763 foreach ($links as $k => $linkVar) {
3764 if (!is_array($linkVar)) {
3765 $linkVar = array($linkVar);
3767 foreach ($linkVar as $v) {
3770 /* link contains {this column} = {linked table}:{linked column} */
3771 $ar = explode(':', $v);
3772 // Feature Request #4266 - Allow joins with multiple keys
3773 if (strpos($k, ',') !== false) {
3774 $k = explode(',', $k);
3776 if (strpos($ar[1], ',') !== false) {
3777 $ar[1] = explode(',', $ar[1]);
3780 if ($ar[0] != $obj->tableName()) {
3783 if ($joinCol !== false) {
3784 if ($k == $joinCol) {
3798 /* look up the links for obj table */
3799 //print_r($obj->links());
3800 if (!$ofield && ($olinks = $obj->links())) {
3801 foreach ($olinks as $k => $linkVar) {
3802 /* link contains {this column} = array ( {linked table}:{linked column} )*/
3803 if (!is_array($linkVar)) {
3804 $linkVar = array($linkVar);
3806 foreach ($linkVar as $v) {
3808 /* link contains {this column} = {linked table}:{linked column} */
3809 $ar = explode(':', $v);
3811 // Feature Request #4266 - Allow joins with multiple keys
3812 $links_key_array = strpos($k, ',');
3813 if ($links_key_array !== false) {
3814 $k = explode(',', $k);
3817 $ar_array = strpos($ar[1], ',');
3818 if ($ar_array !== false) {
3819 $ar[1] = explode(',', $ar[1]);
3822 if ($ar[0] != $this->tableName()) {
3826 // you have explictly specified the column
3827 // and the col is listed here..
3828 // not sure if 1:1 table could cause probs here..
3830 if ($joinCol !== false) {
3832 "joinAdd: You cannot target a join column in the " .
3833 "'link from' table ({$obj->tableName()}). " .
3834 "Either remove the fourth argument to joinAdd() " .
3835 "({$joinCol}), or alter your links.ini file.",
3836 DB_DATAOBJECT_ERROR_NODATA
3848 // finally if these two table have column names that match do a join by default on them
3850 if (($ofield === false) && $joinCol) {
3854 /* did I find a conneciton between them? */
3856 if ($ofield === false) {
3858 "joinAdd: {$obj->tableName()} has no link with {$this->tableName()}",
3859 DB_DATAOBJECT_ERROR_NODATA
3863 $joinType = strtoupper($joinType);
3865 // we default to joining as the same name (this is remvoed later..)
3867 if ($joinAs === false) {
3868 $joinAs = $obj->tableName();
3871 $quoteIdentifiers = !empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers']);
3872 $options = $_DB_DATAOBJECT['CONFIG'];
3874 // not sure how portable adding database prefixes is..
3875 $objTable = $quoteIdentifiers ?
3876 $DB->quoteIdentifier($obj->tableName()) :
3880 if (strlen($obj->_database) && in_array($DB->dsn['phptype'], array('mysql', 'mysqli'))) {
3881 $dbPrefix = ($quoteIdentifiers
3882 ? $DB->quoteIdentifier($obj->_database)
3883 : $obj->_database) . '.';
3886 // if they are the same, then dont add a prefix...
3887 if ($obj->_database == $this->_database) {
3890 // as far as we know only mysql supports database prefixes..
3891 // prefixing the database name is now the default behaviour,
3892 // as it enables joining mutiple columns from multiple databases...
3894 // prefix database (quoted if neccessary..)
3895 $objTable = $dbPrefix . $objTable;
3899 // if obj only a dataobject - eg. no extended class has been defined..
3900 // it obvioulsy cant work out what child elements might exist...
3901 // until we get on the fly querying of tables..
3902 // note: we have already checked that it is_a(db_dataobject earlier)
3903 if (strtolower(get_class($obj)) != 'db_dataobject') {
3905 // now add where conditions for anything that is set in the object
3908 $items = $obj->table();
3909 // will return an array if no items..
3911 // only fail if we where expecting it to work (eg. not joined on a array)
3915 "joinAdd: No table definition for {$obj->tableName()}",
3916 DB_DATAOBJECT_ERROR_INVALIDCONFIG
3921 $ignore_null = !isset($options['disable_null_strings'])
3922 || !is_string($options['disable_null_strings'])
3923 || strtolower($options['disable_null_strings']) !== 'full';
3926 foreach ($items as $k => $v) {
3927 if (!isset($obj->$k) && $ignore_null) {
3931 $kSql = ($quoteIdentifiers ? $DB->quoteIdentifier($k) : $k);
3933 if (DB_DataObject::_is_null($obj, $k)) {
3934 $obj->whereAdd("{$joinAs}.{$kSql} IS NULL");
3938 if ($v & DB_DATAOBJECT_STR) {
3939 $obj->whereAdd("{$joinAs}.{$kSql} = " . $this->_quote((string)(
3940 ($v & DB_DATAOBJECT_BOOL) ?
3941 // this is thanks to the braindead idea of postgres to
3942 // use t/f for boolean.
3943 (($obj->$k === 'f') ? 0 : (int)(bool)$obj->$k) :
3948 if (is_numeric($obj->$k)) {
3949 $obj->whereAdd("{$joinAs}.{$kSql} = {$obj->$k}");
3953 if (is_object($obj->$k) && is_a($obj->$k, 'DB_DataObject_Cast')) {
3954 $value = $obj->$k->toString($v, $DB);
3955 if ((new PEAR)->isError($value)) {
3956 $this->raiseError($value->getMessage(), DB_DATAOBJECT_ERROR_INVALIDARG);
3959 $obj->whereAdd("{$joinAs}.{$kSql} = $value");
3964 /* this is probably an error condition! */
3965 $obj->whereAdd("{$joinAs}.{$kSql} = 0");
3967 if ($this->_query === false) {
3969 "joinAdd can not be run from a object that has had a query run on it,
3970 clone the object or create a new one and use setFrom()",
3971 DB_DATAOBJECT_ERROR_INVALIDARGS
3977 // and finally merge the whereAdd from the child..
3978 if ($obj->_query['condition']) {
3979 $cond = preg_replace('/^\sWHERE/i', '', $obj->_query['condition']);
3981 if (!$useWhereAsOn) {
3982 $this->whereAdd($cond);
3987 // nested (join of joined objects..)
3990 // postgres allows nested queries, with ()'s
3991 // not sure what the results are with other databases..
3992 // may be unpredictable..
3993 if (in_array($DB->dsn["phptype"], array('pgsql'))) {
3994 $objTable = "($objTable {$obj->_join})";
3996 $appendJoin = $obj->_join;
4002 // add the joinee object's conditions to the ON clause instead of the WHERE clause
4003 if ($useWhereAsOn && strlen($cond)) {
4004 $appendJoin = ' AND ' . $cond . ' ' . $appendJoin;
4008 $table = $this->tableName();
4010 if ($quoteIdentifiers) {
4011 $joinAs = $DB->quoteIdentifier($joinAs);
4012 $table = $DB->quoteIdentifier($table);
4013 $ofield = (is_array($ofield)) ? array_map(array($DB, 'quoteIdentifier'), $ofield) : $DB->quoteIdentifier($ofield);
4014 $tfield = (is_array($tfield)) ? array_map(array($DB, 'quoteIdentifier'), $tfield) : $DB->quoteIdentifier($tfield);
4016 // add database prefix if they are different databases
4020 $addJoinAs = ($quoteIdentifiers ? $DB->quoteIdentifier($obj->tableName()) : $obj->tableName()) != $joinAs;
4022 // join table a AS b - is only supported by a few databases and is probably not needed
4023 // , however since it makes the whole Statement alot clearer we are leaving it in
4024 // for those databases.
4025 $fullJoinAs = in_array($DB->dsn["phptype"], array('mysql', 'mysqli', 'pgsql')) ? "AS {$joinAs}" : $joinAs;
4028 $joinAs = $dbPrefix . $joinAs;
4032 switch ($joinType) {
4035 case 'RIGHT': // others??? .. cross, left outer, right outer, natural..?
4037 // Feature Request #4266 - Allow joins with multiple keys
4038 $jadd = "\n {$joinType} JOIN {$objTable} {$fullJoinAs}";
4039 //$this->_join .= "\n {$joinType} JOIN {$objTable} {$fullJoinAs}";
4040 if (is_array($ofield)) {
4041 $key_count = count($ofield);
4042 for ($i = 0; $i < $key_count; $i++) {
4044 $jadd .= " ON ({$joinAs}.{$ofield[$i]}={$table}.{$tfield[$i]}) ";
4046 $jadd .= " AND {$joinAs}.{$ofield[$i]}={$table}.{$tfield[$i]} ";
4049 $jadd .= ' ' . $appendJoin . ' ';
4051 $jadd .= " ON ({$joinAs}.{$ofield}={$table}.{$tfield}) {$appendJoin} ";
4053 // jadd avaliable for debugging join build.
4055 $this->_join .= $jadd;
4058 case '': // this is just a standard multitable select..
4059 $this->_join .= "\n , {$objTable} {$fullJoinAs} {$appendJoin}";
4060 $this->whereAdd("{$joinAs}.{$ofield}={$table}.{$tfield}");
4068 * Adds multiple Columns or objects to select with formating.
4070 * $object->selectAs(null); // adds "table.colnameA as colnameA,table.colnameB as colnameB,......"
4071 * // note with null it will also clear the '*' default select
4072 * $object->selectAs(array('a','b'),'%s_x'); // adds "a as a_x, b as b_x"
4073 * $object->selectAs(array('a','b'),'ddd_%s','ccc'); // adds "ccc.a as ddd_a, ccc.b as ddd_b"
4074 * $object->selectAdd($object,'prefix_%s'); // calls $object->get_table and adds it all as
4075 * objectTableName.colnameA as prefix_colnameA
4077 * @param array|object|null the array or object to take column names from.
4078 * @param string $format
4079 * @param bool $tableName
4083 public function selectAs($from = null, $format = '%s', $tableName = false)
4085 global $_DB_DATAOBJECT;
4087 if ($this->_query === false) {
4089 "You cannot do two queries on the same object (copy it before finding)",
4090 DB_DATAOBJECT_ERROR_INVALIDARGS
4095 if ($from === null) {
4102 $table = $this->tableName();
4103 if (is_object($from)) {
4104 $table = $from->tableName();
4105 $from = array_keys($from->table());
4108 if ($tableName !== false) {
4109 $table = $tableName;
4112 if (!empty($_DB_DATAOBJECT['CONFIG']['quote_identifiers'])) {
4114 $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
4115 $s = $DB->quoteIdentifier($s);
4116 $format = $DB->quoteIdentifier($format);
4118 foreach ($from as $k) {
4119 $this->selectAdd(sprintf("{$s}.{$s} as {$format}", $table, $k, $k));
4121 $this->_query['data_select'] .= "\n";
4125 * Factory method for calling DB_DataObject_Cast
4127 * if used with 1 argument DB_DataObject_Cast::sql($value) is called
4129 * if used with 2 arguments DB_DataObject_Cast::$value($callvalue) is called
4130 * valid first arguments are: blob, string, date, sql
4132 * eg. $member->updated = $member->sqlValue('NOW()');
4135 * might handle more arguments for escaping later...
4138 * @param string $value (or type if used with 2 arguments)
4142 public function sqlValue($value)
4145 if (func_num_args() == 2) {
4147 $value = func_get_arg(1);
4149 //require_once 'DB/DataObject/Cast.php';
4150 require_once 'Cast.php';
4151 return call_user_func(array('DB_DataObject_Cast', $method), $value);
4155 /* ----------------------- Debugger ------------------ */
4158 * Copies items that are in the table definitions from an
4159 * array or object into the current object
4160 * will not override key values.
4163 * @param array | object $from
4164 * @param string $format eg. map xxxx_name to $object->name using 'xxxx_%s' (defaults to %s - eg. name -> $object->name
4165 * @param boolean $skipEmpty (dont assign empty values if a column is empty (eg. '' / 0 etc...)
4167 * @return array|true
4169 public function setFrom($from, $format = '%s', $skipEmpty = false)
4171 global $_DB_DATAOBJECT;
4172 $keys = $this->keys();
4173 $items = $this->table();
4177 "setFrom:Could not find table definition for {$this->tableName()}",
4178 DB_DATAOBJECT_ERROR_INVALIDCONFIG
4182 $overload_return = array();
4183 foreach (array_keys($items) as $k) {
4184 if (in_array($k, $keys)) {
4185 continue; // dont overwrite keys
4188 continue; // ignore empty keys!!! what
4191 $chk = is_object($from) &&
4193 version_compare(phpversion(), "5.1.0", ">=") ?
4194 property_exists($from, sprintf($format, $k)) : // php5.1
4195 array_key_exists(sprintf($format, $k), get_class_vars($from)) //older
4197 // if from has property ($format($k)
4199 $kk = (strtolower($k) == 'from') ? '_from' : $k;
4200 if (method_exists($this, 'set' . $kk)) {
4201 $ret = $this->{'set' . $kk}($from->{sprintf($format, $k)});
4202 if (is_string($ret)) {
4203 $overload_return[$k] = $ret;
4207 $this->$k = $from->{sprintf($format, $k)};
4211 if (is_object($from)) {
4215 if (empty($from[sprintf($format, $k)]) && $skipEmpty) {
4219 if (!isset($from[sprintf($format, $k)]) && !DB_DataObject::_is_null($from, sprintf($format, $k))) {
4223 $kk = (strtolower($k) == 'from') ? '_from' : $k;
4224 if (method_exists($this, 'set' . $kk)) {
4225 $ret = $this->{'set' . $kk}($from[sprintf($format, $k)]);
4226 if (is_string($ret)) {
4227 $overload_return[$k] = $ret;
4231 $val = $from[sprintf($format, $k)];
4232 if (is_a($val, 'DB_DataObject_Cast')) {
4236 if (is_object($val) || is_array($val)) {
4239 $ret = $this->fromValue($k, $val);
4240 if ($ret !== true) {
4241 $overload_return[$k] = 'Not A Valid Value';
4243 //$this->$k = $from[sprintf($format,$k)];
4245 if ($overload_return) {
4246 return $overload_return;
4252 * standard set* implementation.
4254 * takes data and uses it to set dates/strings etc.
4255 * normally called from __call..
4258 * date = using (standard time format, or unixtimestamp).... so you could create a method :
4259 * function setLastread($string) { $this->fromValue('lastread',strtotime($string)); }
4261 * time = using strtotime
4262 * datetime = using same as date - accepts iso standard or unixtimestamp.
4263 * string = typecast only..
4265 * TODO: add formater:: eg. d/m/Y for date! ???
4267 * @param string column of database
4268 * @param mixed value to assign
4270 * @return true| false (False on error)
4272 * @see DB_DataObject::_call
4276 public function fromValue($col, $value)
4278 global $_DB_DATAOBJECT;
4279 $options = $_DB_DATAOBJECT['CONFIG'];
4280 $cols = $this->table();
4281 // dont know anything about this col..
4282 if (!isset($cols[$col]) || is_a($value, 'DB_DataObject_Cast')) {
4283 $this->$col = $value;
4286 //echo "FROM VALUE $col, {$cols[$col]}, $value\n";
4288 // set to null and column is can be null...
4289 case ((!($cols[$col] & DB_DATAOBJECT_NOTNULL)) && DB_DataObject::_is_null($value, false)):
4290 case (is_object($value) && is_a($value, 'DB_DataObject_Cast')):
4291 $this->$col = $value;
4294 // fail on setting null on a not null field..
4295 case (($cols[$col] & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($value, false)):
4299 case (($cols[$col] & DB_DATAOBJECT_DATE) && ($cols[$col] & DB_DATAOBJECT_TIME)):
4300 // empty values get set to '' (which is inserted/updated as NULl
4305 if (is_numeric($value)) {
4306 $this->$col = date('Y-m-d H:i:s', $value);
4310 // eak... - no way to validate date time otherwise...
4311 $this->$col = (string)$value;
4314 case ($cols[$col] & DB_DATAOBJECT_DATE):
4315 // empty values get set to '' (which is inserted/updated as NULl
4322 if (is_numeric($value)) {
4323 $this->$col = date('Y-m-d', $value);
4328 require_once 'Date.php';
4329 $x = new Date($value);
4330 $this->$col = $x->format("%Y-%m-%d");
4333 case ($cols[$col] & DB_DATAOBJECT_TIME):
4334 // empty values get set to '' (which is inserted/updated as NULl
4339 $guess = strtotime($value);
4341 $this->$col = date('H:i:s', $guess);
4342 return $return = true;
4344 // otherwise an error in type...
4347 case ($cols[$col] & DB_DATAOBJECT_STR):
4349 $this->$col = (string)$value;
4352 // todo : floats numerics and ints...
4354 $this->$col = $value;
4360 * Returns an associative array from the current data
4361 * (kind of oblivates the idea behind DataObjects, but
4362 * is usefull if you use it with things like QuickForms.
4364 * you can use the format to return things like user[key]
4365 * by sending it $object->toArray('user[%s]')
4367 * will also return links converted to arrays.
4369 * @param string sprintf format for array
4370 * @param bool||number [true = elemnts that have a value set],
4371 * [false = table + returned colums] ,
4372 * [0 = returned columsn only]
4375 * @return array of key => value for row
4378 public function toArray($format = '%s', $hideEmpty = false)
4380 global $_DB_DATAOBJECT;
4382 // we use false to ignore sprintf.. (speed up..)
4383 $format = $format == '%s' ? false : $format;
4386 $rf = ($this->_resultFields !== false) ? $this->_resultFields :
4387 (isset($_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]) ?
4388 $_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid] : false);
4390 $ar = ($rf !== false) ?
4391 (($hideEmpty === 0) ? $rf : array_merge($rf, $this->table())) :
4394 foreach ($ar as $k => $v) {
4395 if (!isset($this->$k)) {
4397 $ret[$format === false ? $k : sprintf($format, $k)] = '';
4401 // call the overloaded getXXXX() method. - except getLink and getLinks
4402 if (method_exists($this, 'get' . $k) && !in_array(strtolower($k), array('links', 'link'))) {
4403 $ret[$format === false ? $k : sprintf($format, $k)] = $this->{'get' . $k}();
4406 // should this call toValue() ???
4407 $ret[$format === false ? $k : sprintf($format, $k)] = $this->$k;
4409 if (!$this->_link_loaded) {
4412 foreach ($this->_link_loaded as $k) {
4413 $ret[$format === false ? $k : sprintf($format, $k)] = $this->$k->toArray();
4420 * validate the values of the object (usually prior to inserting/updating..)
4422 * Note: This was always intended as a simple validation routine.
4423 * It lacks understanding of field length, whether you are inserting or updating (and hence null key values)
4425 * This should be moved to another class: DB_DataObject_Validate
4426 * FEEL FREE TO SEND ME YOUR VERSION FOR CONSIDERATION!!!
4429 * if (is_array($ret = $obj->validate())) { ... there are problems with the data ... }
4432 * - defaults to only testing strings/numbers if numbers or strings are the correct type and null values are correct
4433 * - validate Column methods : "validate{ROWNAME}()" are called if they are defined.
4434 * These methods should return
4435 * true = everything ok
4436 * false|object = something is wrong!
4438 * - This method loads and uses the PEAR Validate Class.
4442 * @return array|bool
4444 public function validate()
4446 global $_DB_DATAOBJECT;
4447 require_once 'Validate.php';
4448 $table = $this->table();
4450 $seq = $this->sequenceKey();
4451 $options = $_DB_DATAOBJECT['CONFIG'];
4452 foreach ($table as $key => $val) {
4455 // call user defined validation always...
4456 $method = "Validate" . ucfirst($key);
4457 if (method_exists($this, $method)) {
4458 $ret[$key] = $this->$method();
4462 // if not null - and it's not set.......
4464 if ($val & DB_DATAOBJECT_NOTNULL && DB_DataObject::_is_null($this, $key)) {
4465 // dont check empty sequence key values..
4466 if (($key == $seq[0]) && ($seq[1] == true)) {
4474 if (DB_DataObject::_is_null($this, $key)) {
4475 if ($val & DB_DATAOBJECT_NOTNULL) {
4476 $this->debug("'null' field used for '$key', but it is defined as NOT NULL", 'VALIDATION', 4);
4483 // ignore things that are not set. ?
4485 if (!isset($this->$key)) {
4489 // if the string is empty.. assume it is ok..
4490 if (!is_object($this->$key) && !is_array($this->$key) && !strlen((string)$this->$key)) {
4494 // dont try and validate cast objects - assume they are problably ok..
4495 if (is_object($this->$key) && is_a($this->$key, 'DB_DataObject_Cast')) {
4498 // at this point if you have set something to an object, and it's not expected
4499 // the Validate will probably break!!... - rightly so! (your design is broken,
4500 // so issuing a runtime error like PEAR_Error is probably not appropriate..
4503 // todo: date time.....
4504 case ($val & DB_DATAOBJECT_STR):
4505 $ret[$key] = (new Validate)->string($this->$key, VALIDATE_PUNCTUATION . VALIDATE_NAME);
4507 case ($val & DB_DATAOBJECT_INT):
4508 $ret[$key] = (new Validate)->number($this->$key, array('decimal' => '.'));
4512 // if any of the results are false or an object (eg. PEAR_Error).. then return the array..
4513 foreach ($ret as $key => $val) {
4514 if ($val !== true) {
4518 return true; // everything is OK.
4522 * Gets the DB object related to an object - so you can use funky peardb stuf with it :)
4525 * @return bool|object
4527 public function getDatabaseConnection()
4529 global $_DB_DATAOBJECT;
4531 if (($e = $this->_connect()) !== true) {
4534 if (!isset($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
4538 return $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
4542 * Gets the DB result object related to the objects active query
4543 * - so you can use funky pear stuff with it - like pager for example.. :)
4546 * @return bool|object
4549 public function getDatabaseResult()
4551 global $_DB_DATAOBJECT;
4553 if (!isset($_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid])) {
4557 return $_DB_DATAOBJECT['RESULTS'][$this->_DB_resultid];
4561 * Overload Extension support
4562 * - enables setCOLNAME/getCOLNAME
4563 * if you define a set/get method for the item it will be called.
4564 * otherwise it will just return/set the value.
4565 * NOTE this currently means that a few Names are NO-NO's
4566 * eg. links,link,linksarray, from, Databaseconnection,databaseresult
4569 * - set is automatically called by setFrom.
4570 * - get is automatically called by toArray()
4572 * setters return true on success. = strings on failure
4573 * getters return the value!
4575 * this fires off trigger_error - if any problems.. pear_error,
4576 * has problems with 4.3.2RC2 here
4583 * @throws ReflectionException
4588 public function _call($method, $params, &$return)
4591 //$this->debug("ATTEMPTING OVERLOAD? $method");
4592 // ignore constructors : - mm
4593 if (strtolower($method) == strtolower(get_class($this))) {
4596 $type = strtolower(substr($method, 0, 3));
4597 $class = get_class($this);
4598 if (($type != 'set') && ($type != 'get')) {
4603 // deal with naming conflick of setFrom = this is messy ATM!
4605 if (strtolower($method) == 'set_from') {
4606 $return = $this->toValue('from', isset($params[0]) ? $params[0] : null);
4610 $element = substr($method, 3);
4612 // dont you just love php's case insensitivity!!!!
4614 $array = array_keys(get_class_vars($class));
4615 /* php5 version which segfaults on 5.0.3 */
4616 if (class_exists('ReflectionClass')) {
4617 $reflection = new ReflectionClass($class);
4618 $array = array_keys($reflection->getdefaultProperties());
4621 if (!in_array($element, $array)) {
4623 foreach ($array as $k) {
4624 $case[strtolower($k)] = $k;
4626 if ((substr(phpversion(), 0, 1) == 5) && isset($case[strtolower($element)])) {
4627 trigger_error("PHP5 set/get calls should match the case of the variable", E_USER_WARNING);
4628 $element = strtolower($element);
4631 // does it really exist?
4632 if (!isset($case[$element])) {
4635 // use the mundged case
4636 $element = $case[$element]; // real case !
4640 if ($type == 'get') {
4641 $return = $this->toValue($element, isset($params[0]) ? $params[0] : null);
4646 $return = $this->fromValue($element, $params[0]);
4652 * standard get* implementation.
4655 * supported formaters:
4656 * date/time : %d/%m/%Y (eg. php strftime) or pear::Date
4657 * numbers : %02d (eg. sprintf)
4658 * NOTE you will get unexpected results with times like 0000-00-00 !!!
4662 * @param string column of database
4663 * @param format foramt
4665 * @return string|true
4667 * @see DB_DataObject::_call(),strftime(),Date::format()
4669 public function toValue($col, $format = null)
4671 if (is_null($format)) {
4674 $cols = $this->table();
4676 case (($cols[$col] & DB_DATAOBJECT_DATE) && ($cols[$col] & DB_DATAOBJECT_TIME)):
4680 $guess = strtotime($this->$col);
4682 return strftime($format, $guess);
4684 // eak... - no way to validate date time otherwise...
4686 case ($cols[$col] & DB_DATAOBJECT_DATE):
4690 $guess = strtotime($this->$col);
4692 return strftime($format, $guess);
4695 require_once 'Date.php';
4696 $x = new Date($this->$col);
4697 return $x->format($format);
4699 case ($cols[$col] & DB_DATAOBJECT_TIME):
4703 $guess = strtotime($this->$col);
4705 return strftime($format, $guess);
4707 // otherwise an error in type...
4710 case ($cols[$col] & DB_DATAOBJECT_MYSQLTIMESTAMP):
4714 require_once 'Date.php';
4716 $x = new Date($this->$col);
4718 return $x->format($format);
4721 case ($cols[$col] & DB_DATAOBJECT_BOOL):
4723 if ($cols[$col] & DB_DATAOBJECT_STR) {
4725 return ($this->$col === 't');
4727 return (bool)$this->$col;
4731 return sprintf($format, $this->col);
4736 * autoload Class relating to a table
4737 * (deprecited - use ::factory)
4739 * @param string $table table
4741 * @return string classname on Success
4743 public function staticAutoloadTable($table)
4745 global $_DB_DATAOBJECT;
4746 if (empty($_DB_DATAOBJECT['CONFIG'])) {
4747 DB_DataObject::_loadConfig();
4749 $p = isset($_DB_DATAOBJECT['CONFIG']['class_prefix']) ?
4750 $_DB_DATAOBJECT['CONFIG']['class_prefix'] : '';
4751 $class = $p . preg_replace('/[^A-Z0-9]/i', '_', ucfirst($table));
4753 $ce = substr(phpversion(), 0, 1) > 4 ? class_exists($class, false) : class_exists($class);
4754 $class = $ce ? $class : DB_DataObject::_autoloadClass($class);
4758 /* ---- LEGACY BC METHODS - NOT DOCUMENTED - See Documentation on New Methods. ---*/
4760 public function _get_table()
4762 return $this->table();
4765 public function _get_keys()
4767 return $this->keys();
4771 // technially 4.3.2RC1 was broken!!
4772 // looks like 4.3.3 may have problems too....
4773 if (!defined('DB_DATAOBJECT_NO_OVERLOAD')) {
4774 if ((phpversion() != '4.3.2-RC1') && (version_compare(phpversion(), "4.3.1") > 0)) {
4775 if (version_compare(phpversion(), "5") < 0) {
4776 overload('DB_DataObject');
4778 $GLOBALS['_DB_DATAOBJECT']['OVERLOADED'] = true;