1<?php 2/** 3 * EGroupware API: Database abstraction library 4 * 5 * @link http://www.egroupware.org 6 * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License 7 * @package api 8 * @subpackage db 9 * @author Ralf Becker <RalfBecker-AT-outdoor-training.de> 10 * @copyright (c) 2003-19 by Ralf Becker <RalfBecker-AT-outdoor-training.de> 11 */ 12 13namespace EGroupware\Api; 14 15if(empty($GLOBALS['egw_info']['server']['db_type'])) 16{ 17 $GLOBALS['egw_info']['server']['db_type'] = 'mysql'; 18} 19 20/** 21 * Database abstraction library 22 * 23 * This allows eGroupWare to use multiple database backends via ADOdb or in future with PDO 24 * 25 * You only need to clone the global database object $GLOBALS['egw']->db if: 26 * - you access an application table (non phpgwapi) and you want to call set_app() 27 * 28 * Otherwise you can simply use $GLOBALS['egw']->db or a reference to it. 29 * 30 * a) foreach($db->query("SELECT * FROM $table",__LINE__,__FILE__) as $row) 31 * 32 * b) foreach($db->select($api_table,'*',$where,__LINE__,__FILE__) as $row) 33 * 34 * c) foreach($db->select($table,'*',$where,__LINE__,__FILE__,false,'',$app) as $row) 35 * 36 * To fetch only a single column (of the next row): 37 * $cnt = $db->query("SELECT COUNT(*) FROM ...")->fetchColumn($column_num=0); 38 * 39 * To fetch a next (single) row, you can use: 40 * $row = $db->query("SELECT COUNT(*) FROM ...")->fetch($fetchmod=null); 41 * 42 * Api\Db allows to use exceptions to catch sql-erros, not existing tables or failure to connect to the database, eg.: 43 * try { 44 * $this->db->connect(); 45 * $num_config = $this->db->select(config::TABLE,'COUNT(config_name)',false,__LINE__,__FILE__)->fetchColumn(); 46 * } 47 * catch(Exception $e) { 48 * echo "Connection to DB failed (".$e->getMessage().")!\n"; 49 * } 50 */ 51class Db 52{ 53 /** 54 * Fetchmode to fetch only as associative array with $colname => $value pairs 55 * 56 * Use the FETCH_* constants to be compatible, if we replace ADOdb ... 57 */ 58 const FETCH_ASSOC = ADODB_FETCH_ASSOC; 59 /** 60 * Fetchmode to fetch only as (numeric indexed) array: array($val1,$val2,...) 61 */ 62 const FETCH_NUM = ADODB_FETCH_NUM; 63 /** 64 * Fetchmode to have both numeric and column-name indexes 65 */ 66 const FETCH_BOTH = ADODB_FETCH_BOTH; 67 /** 68 * @var string $type translated database type: mysqlt+mysqli ==> mysql, same for odbc-types 69 */ 70 var $Type = ''; 71 72 /** 73 * @var string $type database type as defined in the header.inc.php, eg. mysqlt 74 */ 75 var $setupType = ''; 76 77 /** 78 * @var string $Host database host to connect to 79 */ 80 var $Host = ''; 81 82 /** 83 * @var string $Port port number of database to connect to 84 */ 85 var $Port = ''; 86 87 /** 88 * @var string $Database name of database to use 89 */ 90 var $Database = ''; 91 92 /** 93 * @var string $User name of database user 94 */ 95 var $User = ''; 96 97 /** 98 * @var string $Password password for database user 99 */ 100 var $Password = ''; 101 102 /** 103 * @var boolean $readonly only allow readonly access to database 104 */ 105 var $readonly = false; 106 107 /** 108 * @var int $Debug enable debuging - 0 no, 1 yes 109 */ 110 var $Debug = 0; 111 112 /** 113 * Log update querys to error_log 114 * 115 * @var boolean 116 */ 117 var $log_updates = false; 118 119 /** 120 * @var array $Record current record 121 */ 122 var $Record = array(); 123 124 /** 125 * @var int row number for current record 126 */ 127 var $Row; 128 129 /** 130 * @var int $Errno internal rdms error number for last error 131 */ 132 var $Errno = 0; 133 134 /** 135 * @var string descriptive text from last error 136 */ 137 var $Error = ''; 138 139 /** 140 * eGW's own query log, independent of the db-type, eg. /tmp/query.log 141 * 142 * @var string 143 */ 144 var $query_log; 145 146 /** 147 * ADOdb connection 148 * 149 * @var ADOConnection 150 */ 151 var $Link_ID = 0; 152 /** 153 * ADOdb connection 154 * 155 * @var boolean 156 */ 157 var $privat_Link_ID = False; // do we use a privat Link_ID or a reference to the global ADOdb object 158 /** 159 * Global ADOdb connection 160 */ 161 static public $ADOdb = null; 162 163 /** 164 * Can be used to transparently convert tablenames, eg. 'mytable' => 'otherdb.othertable' 165 * 166 * Can be set eg. at the *end* of header.inc.php. 167 * Only works with new Api\Db methods (select, insert, update, delete) not query! 168 * 169 * @var array 170 */ 171 static $tablealiases = array(); 172 173 /** 174 * Callback to check if selected node is healty / should be used 175 * 176 * @var callback throwing Db\Exception\Connection, if connected node should NOT be used 177 */ 178 static $health_check; 179 180 /** 181 * db allows sub-queries, true for everything but mysql < 4.1 182 * 183 * use like: if ($db->capabilities[self::CAPABILITY_SUB_QUERIES]) ... 184 */ 185 const CAPABILITY_SUB_QUERIES = 'sub_queries'; 186 /** 187 * db allows union queries, true for everything but mysql < 4.0 188 */ 189 const CAPABILITY_UNION = 'union'; 190 /** 191 * db allows an outer join, will be set eg. for postgres 192 */ 193 const CAPABILITY_OUTER_JOIN = 'outer_join'; 194 /** 195 * db is able to use DISTINCT on text or blob columns 196 */ 197 const CAPABILITY_DISTINCT_ON_TEXT = 'distinct_on_text'; 198 /** 199 * DB is able to use LIKE on text columns 200 */ 201 const CAPABILITY_LIKE_ON_TEXT = 'like_on_text'; 202 /** 203 * DB allows ORDER on text columns 204 * 205 * boolean or string for sprintf for a cast (eg. 'CAST(%s AS varchar) 206 */ 207 const CAPABILITY_ORDER_ON_TEXT = 'order_on_text'; 208 /** 209 * case of returned column- and table-names: upper, lower(pgSql), preserv(MySQL) 210 */ 211 const CAPABILITY_NAME_CASE = 'name_case'; 212 /** 213 * does DB supports a changeable client-encoding 214 */ 215 const CAPABILITY_CLIENT_ENCODING = 'client_encoding'; 216 /** 217 * case insensitiv like statement (in $db->capabilities[self::CAPABILITY_CASE_INSENSITIV_LIKE]), default LIKE, ILIKE for postgres 218 */ 219 const CAPABILITY_CASE_INSENSITIV_LIKE = 'case_insensitive_like'; 220 /** 221 * DB requires varchar columns to be truncated to the max. size (eg. Postgres) 222 */ 223 const CAPABILITY_REQUIRE_TRUNCATE_VARCHAR = 'require_truncate_varchar'; 224 /** 225 * How to cast a column to varchar: CAST(%s AS varchar) 226 * 227 * MySQL requires to use CAST(%s AS char)! 228 * 229 * Use as: $sql = sprintf($GLOBALS['egw']->db->capabilities[self::CAPABILITY_CAST_AS_VARCHAR],$expression); 230 */ 231 const CAPABILITY_CAST_AS_VARCHAR = 'cast_as_varchar'; 232 /** 233 * default capabilities will be changed by method set_capabilities($ado_driver,$db_version) 234 * 235 * should be used with the CAPABILITY_* constants as key 236 * 237 * @var array 238 */ 239 var $capabilities = array( 240 self::CAPABILITY_SUB_QUERIES => true, 241 self::CAPABILITY_UNION => true, 242 self::CAPABILITY_OUTER_JOIN => false, 243 self::CAPABILITY_DISTINCT_ON_TEXT => true, 244 self::CAPABILITY_LIKE_ON_TEXT => true, 245 self::CAPABILITY_ORDER_ON_TEXT => true, 246 self::CAPABILITY_NAME_CASE => 'upper', 247 self::CAPABILITY_CLIENT_ENCODING => false, 248 self::CAPABILITY_CASE_INSENSITIV_LIKE => 'LIKE', 249 self::CAPABILITY_REQUIRE_TRUNCATE_VARCHAR => true, 250 self::CAPABILITY_CAST_AS_VARCHAR => 'CAST(%s AS varchar)', 251 ); 252 253 var $prepared_sql = array(); // sql is the index 254 255 /** 256 * Constructor 257 * 258 * @param array $db_data =null values for keys 'db_name', 'db_host', 'db_port', 'db_user', 'db_pass', 'db_type', 'db_readonly' 259 */ 260 function __construct(array $db_data=null) 261 { 262 if (!is_null($db_data)) 263 { 264 foreach(array( 265 'Database' => 'db_name', 266 'Host' => 'db_host', 267 'Port' => 'db_port', 268 'User' => 'db_user', 269 'Password' => 'db_pass', 270 'Type' => 'db_type', 271 'readonly' => 'db_readonly', 272 ) as $var => $key) 273 { 274 $this->$var = $db_data[$key]; 275 } 276 } 277//if ($GLOBALS['egw_info']['server']['default_domain'] == 'ralfsmacbook.local') $this->query_log = '/tmp/query.log'; 278 } 279 280 /** 281 * @param string $query query to be executed (optional) 282 */ 283 284 function db($query = '') 285 { 286 $this->query($query); 287 } 288 289 /** 290 * @return int current connection id 291 */ 292 function link_id() 293 { 294 return $this->Link_ID; 295 } 296 297 /** 298 * Open a connection to a database 299 * 300 * @param string $Database name of database to use (optional) 301 * @param string $Host database host to connect to (optional) 302 * @param string $Port database port to connect to (optional) 303 * @param string $User name of database user (optional) 304 * @param string $Password password for database user (optional) 305 * @param string $Type type of database (optional) 306 * @throws Db\Exception\Connection 307 * @return ADOConnection 308 */ 309 function connect($Database = NULL, $Host = NULL, $Port = NULL, $User = NULL, $Password = NULL, $Type = NULL) 310 { 311 /* Handle defaults */ 312 if (!is_null($Database) && $Database) 313 { 314 $this->Database = $Database; 315 } 316 if (!is_null($Host) && $Host) 317 { 318 $this->Host = $Host; 319 } 320 if (!is_null($Port) && $Port) 321 { 322 $this->Port = $Port; 323 } 324 if (!is_null($User) && $User) 325 { 326 $this->User = $User; 327 } 328 if (!is_null($Password) && $Password) 329 { 330 $this->Password = $Password; 331 } 332 if (!is_null($Type) && $Type) 333 { 334 $this->Type = $Type; 335 } 336 elseif (!$this->Type) 337 { 338 $this->Type = $GLOBALS['egw_info']['server']['db_type']; 339 } 340 // on connection failure re-try with an other host 341 // remembering in session which host we used last time 342 $use_host_from_session = true; 343 while(($host = $this->get_host(!$use_host_from_session))) 344 { 345 try { 346 //error_log(__METHOD__."() this->Host(s)=$this->Host, n=$n --> host=$host"); 347 $new_connection = !$this->Link_ID || !$this->Link_ID->IsConnected(); 348 $this->_connect($host); 349 // check if connected node is healty 350 if ($new_connection && self::$health_check) 351 { 352 call_user_func(self::$health_check, $this); 353 } 354 //error_log(__METHOD__."() host=$host, new_connection=$new_connection, this->Type=$this->Type, this->Host=$this->Host, wsrep_local_state=".array2string($state)); 355 return $this->Link_ID; 356 } 357 catch(Db\Exception\Connection $e) { 358 //_egw_log_exception($e); 359 $this->disconnect(); // force a new connect 360 $this->Type = $this->setupType; // get set to "mysql" for "mysqli" 361 $use_host_from_session = false; // re-try with next host from list 362 } 363 } 364 if (!isset($e)) 365 { 366 $e = new Db\Exception\Connection('No DB host set!'); 367 } 368 throw $e; 369 } 370 371 /** 372 * Check if just connected Galera cluster node is healthy / fully operational 373 * 374 * A node in state "Donor/Desynced" will block updates at the end of a SST. 375 * Therefore we try to avoid that node, if we have an alternative. 376 * 377 * To enable this check add the following to your header.inc.php: 378 * 379 * require_once(EGW_INCLUDE_ROOT.'/api/src/Db.php'); 380 * EGroupware\Api\Db::$health_check = array('EGroupware\Api\Db', 'galera_cluster_health'); 381 * 382 * @param Api\Db $db already connected Api\Db instance to check 383 * @throws Db\Exception\Connection if node should NOT be used 384 */ 385 static function galera_cluster_health(Db $db) 386 { 387 if (($state = $db->query("SHOW STATUS WHERE Variable_name in ('wsrep_cluster_size','wsrep_local_state','wsrep_local_state_comment')", 388 // GetAssoc in ADOdb 5.20 does not work with our default self::FETCH_BOTH 389 __LINE__, __FILE__, 0, -1, false, self::FETCH_ASSOC)->GetAssoc())) 390 { 391 if ($state['wsrep_local_state_comment'] == 'Synced' || 392 // if we have only 2 nodes (2. one starting), we can only use the donor 393 $state['wsrep_local_state_comment'] == 'Donor/Desynced' && 394 $state['wsrep_cluster_size'] == 2) return; 395 396 throw new Db\Exception\Connection('Node is NOT Synced! '.array2string($state)); 397 } 398 } 399 400 /** 401 * Get one of multiple (semicolon-separated) DB-hosts to use 402 * 403 * Which host to use is cached in session, default is first one. 404 * 405 * @param boolean $next =false true: move to next host 406 * @return boolean|string hostname or false, if already number-of-hosts plus 2 times called with $next == true 407 */ 408 public function get_host($next = false) 409 { 410 $hosts = explode(';', $this->Host[0] == '@' ? getenv(substr($this->Host, 1)) : $this->Host); 411 $num_hosts = count($hosts); 412 $n =& Cache::getSession(__CLASS__, $this->Host); 413 if (!isset($n)) $n = 0; 414 415 if ($next && ++$n >= $num_hosts+2) 416 { 417 $n = 0; // start search again with default on next request 418 $ret = false; 419 } 420 else 421 { 422 $ret = $hosts[$n % $num_hosts]; 423 } 424 //error_log(__METHOD__."(next=".array2string($next).") n=$n returning ".array2string($ret)); 425 return $ret; 426 } 427 428 /** 429 * Connect to given host 430 * 431 * @param string $Host host to connect to 432 * @return ADOConnection 433 * @throws Db\Exception\Connection 434 */ 435 protected function _connect($Host) 436 { 437 if (!$this->Link_ID || $Host != $this->Link_ID->host) 438 { 439 $Database = $User = $Password = $Port = $Type = ''; 440 foreach(array('Database','User','Password','Port','Type') as $name) 441 { 442 $$name = $this->$name; 443 if (${$name}[0] == '@' && $name != 'Password') $$name = getenv(substr($$name, 1)); 444 } 445 $this->setupType = $php_extension = $Type; 446 447 switch($Type) // convert to ADO db-type-names 448 { 449 case 'pgsql': 450 $Type = 'postgres'; // name in ADOdb 451 // create our own pgsql connection-string, to allow unix domain soccets if !$Host 452 $Host = "dbname=$Database".($Host ? " host=$Host".($Port ? " port=$Port" : '') : ''). 453 " user=$User".($Password ? " password='".addslashes($Password)."'" : ''); 454 $User = $Password = $Database = ''; // to indicate $Host is a connection-string 455 break; 456 457 case 'odbc_mssql': 458 $php_extension = 'odbc'; 459 $Type = 'mssql'; 460 // fall through 461 case 'mssql': 462 if ($Port) $Host .= ','.$Port; 463 break; 464 465 case 'odbc_oracle': 466 $php_extension = 'odbc'; 467 $Type = 'oracle'; 468 break; 469 case 'oracle': 470 $php_extension = $Type = 'oci8'; 471 break; 472 473 case 'sapdb': 474 $Type = 'maxdb'; 475 // fall through 476 case 'maxdb': 477 $Type ='sapdb'; // name in ADOdb 478 $php_extension = 'odbc'; 479 break; 480 481 case 'mysqlt': 482 case 'mysql': 483 // if mysqli is available silently switch to it, mysql extension is deprecated and no longer available in php7+ 484 if (check_load_extension('mysqli')) 485 { 486 $php_extension = $Type = 'mysqli'; 487 } 488 else 489 { 490 $php_extension = 'mysql'; // you can use $this->setupType to determine if it's mysqlt or mysql 491 } 492 // fall through 493 case 'mysqli': 494 $this->Type = 'mysql'; // need to be "mysql", so apps can check just for "mysql"! 495 // fall through 496 default: 497 if ($Port) $Host .= ':'.$Port; 498 break; 499 } 500 if (!isset(self::$ADOdb) || // we have no connection so far 501 (is_object($GLOBALS['egw']->db) && // we connect to a different db, then the global one 502 ($this->Type != $GLOBALS['egw']->db->Type || 503 $this->Database != $GLOBALS['egw']->db->Database || 504 $this->User != $GLOBALS['egw']->db->User || 505 $this->Host != $GLOBALS['egw']->db->Host || 506 $this->Port != $GLOBALS['egw']->db->Port))) 507 { 508 if (!check_load_extension($php_extension)) 509 { 510 throw new Db\Exception\Connection("Necessary php database support for $this->Type (".PHP_SHLIB_PREFIX.$php_extension.'.'.PHP_SHLIB_SUFFIX.") not loaded and can't be loaded, exiting !!!"); 511 } 512 $this->Link_ID = ADONewConnection($Type); 513 if (!isset(self::$ADOdb)) // use the global object to store the connection 514 { 515 self::$ADOdb = $this->Link_ID; 516 } 517 else 518 { 519 $this->privat_Link_ID = True; // remember that we use a privat Link_ID for disconnect 520 } 521 if (!$this->Link_ID) 522 { 523 throw new Db\Exception\Connection("No ADOdb support for '$Type' ($this->Type) !!!"); 524 } 525 if ($Type == 'mysqli') 526 { 527 // set a connection timeout of 1 second, to allow quicker failover to other db-nodes (default is 20s) 528 $this->Link_ID->setConnectionParameter(MYSQLI_OPT_CONNECT_TIMEOUT, 1); 529 } 530 $connect = $GLOBALS['egw_info']['server']['db_persistent'] && 531 // do NOT attempt persistent connection, if it is switched off in php.ini (it will only cause a warning) 532 ($Type !== 'mysqli' || ini_get('mysqli.allow_persistent')) ? 533 'PConnect' : 'Connect'; 534 535 if (($Ok = $this->Link_ID->$connect($Host, $User, $Password, $Database))) 536 { 537 $this->ServerInfo = $this->Link_ID->ServerInfo(); 538 $this->set_capabilities($Type,$this->ServerInfo['version']); 539 540 // switch off MySQL 5.7+ ONLY_FULL_GROUP_BY sql_mode 541 if (substr($this->Type, 0, 5) == 'mysql' && $this->ServerInfo['version'] >= 5.7 && $this->ServerInfo['version'] < 10.0) 542 { 543 $this->query("SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))", __LINE__, __FILE__); 544 } 545 } 546 if (!$Ok) 547 { 548 $Host = preg_replace('/password=[^ ]+/','password=$Password',$Host); // eg. postgres dsn contains password 549 throw new Db\Exception\Connection("ADOdb::$connect($Host, $User, \$Password, $Database) failed."); 550 } 551 if ($this->Debug) 552 { 553 echo function_backtrace(); 554 echo "<p>new ADOdb connection to $Type://$Host/$Database: Link_ID".($this->Link_ID === self::$ADOdb ? '===' : '!==')."self::\$ADOdb</p>"; 555 //echo "<p>".print_r($this->Link_ID->ServerInfo(),true)."</p>\n"; 556 _debug_array($this); 557 echo "\$GLOBALS[egw]->db="; _debug_array($GLOBALS[egw]->db); 558 } 559 if ($Type == 'mssql') 560 { 561 // this is the format ADOdb expects 562 $this->Link_ID->Execute('SET DATEFORMAT ymd'); 563 // sets the limit to the maximum 564 ini_set('mssql.textlimit',2147483647); 565 ini_set('mssql.sizelimit',2147483647); 566 } 567 // set our default charset 568 $this->Link_ID->SetCharSet($this->Type == 'mysql' ? 'utf8' : 'utf-8'); 569 570 $new_connection = true; 571 } 572 else 573 { 574 $this->Link_ID = self::$ADOdb; 575 } 576 } 577 if (!$this->Link_ID->isConnected() && !$this->Link_ID->Connect()) 578 { 579 $Host = preg_replace('/password=[^ ]+/','password=$Password',$Host); // eg. postgres dsn contains password 580 throw new Db\Exception\Connection("ADOdb::$connect($Host, $User, \$Password, $Database) reconnect failed."); 581 } 582 // fix due to caching and reusing of connection not correctly set $this->Type == 'mysql' 583 if ($this->Type == 'mysqli') 584 { 585 $this->setupType = $this->Type; 586 $this->Type = 'mysql'; 587 } 588 if ($new_connection) 589 { 590 foreach(get_included_files() as $file) 591 { 592 if (strpos($file,'adodb') !== false && !in_array($file,(array)$_SESSION['egw_required_files'])) 593 { 594 $_SESSION['egw_required_files'][] = $file; 595 //error_log(__METHOD__."() egw_required_files[] = $file"); 596 } 597 } 598 } 599 //echo "<p>".print_r($this->Link_ID->ServerInfo(),true)."</p>\n"; 600 return $this->Link_ID; 601 } 602 603 /** 604 * Magic method to re-connect with the database, if the object get's restored from the session 605 */ 606 function __wakeup() 607 { 608 $this->connect(); // we need to re-connect 609 } 610 611 /** 612 * Magic method called when object get's serialized 613 * 614 * We do NOT store Link_ID and private_Link_ID, as we need to reconnect anyway. 615 * This also ensures reevaluating environment-data or multiple hosts in connection-data! 616 * 617 * @return array 618 */ 619 function __sleep() 620 { 621 if (!empty($this->setupType)) $this->Type = $this->setupType; // restore Type eg. to mysqli 622 623 $vars = get_object_vars($this); 624 unset($vars['Link_ID'], $vars['Query_ID'], $vars['privat_Link_ID']); 625 return array_keys($vars); 626 } 627 628 /** 629 * changes defaults set in class-var $capabilities depending on db-type and -version 630 * 631 * @param string $adodb_driver mysql, postgres, mssql, sapdb, oci8 632 * @param string $db_version version-number of connected db-server, as reported by ServerInfo 633 */ 634 function set_capabilities($adodb_driver,$db_version) 635 { 636 switch($adodb_driver) 637 { 638 case 'mysql': 639 case 'mysqlt': 640 case 'mysqli': 641 $this->capabilities[self::CAPABILITY_SUB_QUERIES] = (float) $db_version >= 4.1; 642 $this->capabilities[self::CAPABILITY_UNION] = (float) $db_version >= 4.0; 643 $this->capabilities[self::CAPABILITY_NAME_CASE] = 'preserv'; 644 $this->capabilities[self::CAPABILITY_CLIENT_ENCODING] = (float) $db_version >= 4.1; 645 $this->capabilities[self::CAPABILITY_CAST_AS_VARCHAR] = 'CAST(%s AS char)'; 646 break; 647 648 case 'postgres': 649 $this->capabilities[self::CAPABILITY_NAME_CASE] = 'lower'; 650 $this->capabilities[self::CAPABILITY_CLIENT_ENCODING] = (float) $db_version >= 7.4; 651 $this->capabilities[self::CAPABILITY_OUTER_JOIN] = true; 652 $this->capabilities[self::CAPABILITY_CASE_INSENSITIV_LIKE] = '::text ILIKE'; 653 $this->capabilities[self::CAPABILITY_REQUIRE_TRUNCATE_VARCHAR] = true; 654 break; 655 656 case 'mssql': 657 $this->capabilities[self::CAPABILITY_DISTINCT_ON_TEXT] = false; 658 $this->capabilities[self::CAPABILITY_ORDER_ON_TEXT] = 'CAST (%s AS varchar)'; 659 break; 660 661 case 'maxdb': // if Lim ever changes it to maxdb ;-) 662 case 'sapdb': 663 $this->capabilities[self::CAPABILITY_DISTINCT_ON_TEXT] = false; 664 $this->capabilities[self::CAPABILITY_LIKE_ON_TEXT] = $db_version >= 7.6; 665 $this->capabilities[self::CAPABILITY_ORDER_ON_TEXT] = false; 666 break; 667 } 668 //echo "db::set_capabilities('$adodb_driver',$db_version)"; _debug_array($this->capabilities); 669 } 670 671 /** 672 * Close a connection to a database 673 */ 674 function disconnect() 675 { 676 if (!$this->privat_Link_ID) 677 { 678 self::$ADOdb = null; 679 } 680 unset($this->Link_ID); 681 $this->Link_ID = 0; 682 683 if (!empty($this->setupType)) $this->Type = $this->setupType; 684 } 685 686 /** 687 * Convert a unix timestamp to a rdms specific timestamp 688 * 689 * @param int unix timestamp 690 * @return string rdms specific timestamp 691 */ 692 function to_timestamp($epoch) 693 { 694 if (!$this->Link_ID && !$this->connect()) 695 { 696 return False; 697 } 698 // the substring is needed as the string is already in quotes 699 return substr($this->Link_ID->DBTimeStamp($epoch),1,-1); 700 } 701 702 /** 703 * Convert a rdms specific timestamp to a unix timestamp 704 * 705 * @param string rdms specific timestamp 706 * @return int unix timestamp 707 */ 708 function from_timestamp($timestamp) 709 { 710 if (!$this->Link_ID && !$this->connect()) 711 { 712 return False; 713 } 714 return $this->Link_ID->UnixTimeStamp($timestamp); 715 } 716 717 /** 718 * convert a rdbms specific boolean value 719 * 720 * @param string $val boolean value in db-specfic notation 721 * @return boolean 722 */ 723 public static function from_bool($val) 724 { 725 return $val && $val[0] !== 'f'; // everthing other then 0 or f[alse] is returned as true 726 } 727 728 /** 729 * Execute a query 730 * 731 * @param string $Query_String the query to be executed 732 * @param int $line the line method was called from - use __LINE__ 733 * @param string $file the file method was called from - use __FILE__ 734 * @param int $offset row to start from, default 0 735 * @param int $num_rows number of rows to return (optional), default -1 = all, 0 will use $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs'] 736 * @param array|boolean $inputarr array for binding variables to parameters or false (default) 737 * @param int $fetchmode =self::FETCH_BOTH self::FETCH_BOTH (default), self::FETCH_ASSOC or self::FETCH_NUM 738 * @param boolean $reconnect =true true: try reconnecting if server closes connection, false: dont (mysql only!) 739 * @return ADORecordSet or false, if the query fails 740 * @throws Db\Exception\InvalidSql with $this->Link_ID->ErrorNo() as code 741 */ 742 function query($Query_String, $line = '', $file = '', $offset=0, $num_rows=-1, $inputarr=false, $fetchmode=self::FETCH_BOTH, $reconnect=true) 743 { 744 unset($line, $file); // not used anymore 745 746 if ($Query_String == '') 747 { 748 return 0; 749 } 750 if (!$this->Link_ID && !$this->connect()) 751 { 752 return False; 753 } 754 755 if ($this->Link_ID->fetchMode != $fetchmode) 756 { 757 $this->Link_ID->SetFetchMode($fetchmode); 758 } 759 if (!$num_rows) 760 { 761 $num_rows = $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs']; 762 } 763 if (($this->readonly || $this->log_updates) && !preg_match('/^\(?(SELECT|SET|SHOW)/i', $Query_String)) 764 { 765 if ($this->log_updates) error_log($Query_String.': '.function_backtrace()); 766 if ($this->readonly) 767 { 768 $this->Error = 'Database is readonly'; 769 $this->Errno = -2; 770 return 0; 771 } 772 } 773 if ($num_rows > 0) 774 { 775 $rs = $this->Link_ID->SelectLimit($Query_String,$num_rows,(int)$offset,$inputarr); 776 } 777 else 778 { 779 $rs = $this->Link_ID->Execute($Query_String,$inputarr); 780 } 781 $this->Row = 0; 782 $this->Errno = $this->Link_ID->ErrorNo(); 783 $this->Error = $this->Link_ID->ErrorMsg(); 784 785 if ($this->query_log && ($f = @fopen($this->query_log,'a+'))) 786 { 787 fwrite($f,'['.(isset($GLOBALS['egw_setup']) ? $GLOBALS['egw_setup']->ConfigDomain : $GLOBALS['egw_info']['user']['domain']).'] '); 788 fwrite($f,date('Y-m-d H:i:s ').$Query_String.($inputarr ? "\n".print_r($inputarr,true) : '')."\n"); 789 if (!$rs) 790 { 791 fwrite($f,"*** Error $this->Errno: $this->Error\n".function_backtrace()."\n"); 792 } 793 fclose($f); 794 } 795 if (!$rs) 796 { 797 if ($reconnect && $this->Type == 'mysql' && $this->Errno == 2006) // Server has gone away 798 { 799 $this->disconnect(); 800 return $this->query($Query_String, $line, $file, $offset, $num_rows, $inputarr, $fetchmode, false); 801 } 802 throw new Db\Exception\InvalidSql("Invalid SQL: ".(is_array($Query_String)?$Query_String[0]:$Query_String). 803 "\n$this->Error ($this->Errno)". 804 ($inputarr ? "\nParameters: '".implode("','",$inputarr)."'":''), $this->Errno); 805 } 806 elseif(empty($rs->sql)) $rs->sql = $Query_String; 807 return $rs; 808 } 809 810 /** 811 * Execute a query with limited result set 812 * 813 * @param string $Query_String the query to be executed 814 * @param int $offset row to start from, default 0 815 * @param int $line the line method was called from - use __LINE__ 816 * @param string $file the file method was called from - use __FILE__ 817 * @param int $num_rows number of rows to return (optional), default -1 = all, 0 will use $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs'] 818 * @param array|boolean $inputarr array for binding variables to parameters or false (default) 819 * @return ADORecordSet or false, if the query fails 820 */ 821 function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = '',$inputarr=false) 822 { 823 return $this->query($Query_String,$line,$file,$offset,$num_rows,$inputarr); 824 } 825 826 /** 827 * Begin Transaction 828 * 829 * @return int/boolean current transaction-id, of false if no connection 830 */ 831 function transaction_begin() 832 { 833 if (!$this->Link_ID && !$this->connect()) 834 { 835 return False; 836 } 837 //return $this->Link_ID->BeginTrans(); 838 return $this->Link_ID->StartTrans(); 839 } 840 841 /** 842 * Complete the transaction 843 * 844 * @return bool True if sucessful, False if fails 845 */ 846 function transaction_commit() 847 { 848 if (!$this->Link_ID && !$this->connect()) 849 { 850 return False; 851 } 852 //return $this->Link_ID->CommitTrans(); 853 return $this->Link_ID->CompleteTrans(); 854 } 855 856 /** 857 * Rollback the current transaction 858 * 859 * @return bool True if sucessful, False if fails 860 */ 861 function transaction_abort() 862 { 863 if (!$this->Link_ID && !$this->connect()) 864 { 865 return False; 866 } 867 //return $this->Link_ID->RollbackTrans(); 868 return $this->Link_ID->FailTrans(); 869 } 870 871 /** 872 * Lock a rows in table 873 * 874 * Will escalate and lock the table if row locking not supported. 875 * Will normally free the lock at the end of the transaction. 876 * 877 * @param string $table name of table to lock 878 * @param string $where ='true' where clause to use, eg: "WHERE row=12". Defaults to lock whole table. 879 * @param string $col ='1 as adodbignore' 880 */ 881 function row_lock($table, $where='true', $col='1 as adodbignore') 882 { 883 if (!$this->Link_ID && !$this->connect()) 884 { 885 return False; 886 } 887 if (self::$tablealiases && isset(self::$tablealiases[$table])) 888 { 889 $table = self::$tablealiases[$table]; 890 } 891 892 return $this->Link_ID->RowLock($table, $where, $col); 893 } 894 895 /** 896 * Commit changed rows in table 897 * 898 * @param string $table 899 * @return boolean 900 */ 901 function commit_lock($table) 902 { 903 if (!$this->Link_ID && !$this->connect()) 904 { 905 return False; 906 } 907 if (self::$tablealiases && isset(self::$tablealiases[$table])) 908 { 909 $table = self::$tablealiases[$table]; 910 } 911 912 return $this->Link_ID->CommitLock($table); 913 } 914 915 /** 916 * Unlock rows in table 917 * 918 * @param string $table 919 * @return boolean 920 */ 921 function rollback_lock($table) 922 { 923 if (!$this->Link_ID && !$this->connect()) 924 { 925 return False; 926 } 927 if (self::$tablealiases && isset(self::$tablealiases[$table])) 928 { 929 $table = self::$tablealiases[$table]; 930 } 931 932 return $this->Link_ID->RollbackLock($table); 933 } 934 935 /** 936 * Find the primary key of the last insertion on the current db connection 937 * 938 * @param string $table name of table the insert was performed on 939 * @param string $field the autoincrement primary key of the table 940 * @return int the id, -1 if fails 941 */ 942 function get_last_insert_id($table, $field) 943 { 944 if (!$this->Link_ID && !$this->connect()) 945 { 946 return False; 947 } 948 if (self::$tablealiases && isset(self::$tablealiases[$table])) 949 { 950 $table = self::$tablealiases[$table]; 951 } 952 $id = $this->Link_ID->PO_Insert_ID($table,$field); // simulates Insert_ID with "SELECT MAX($field) FROM $table" if not native availible 953 954 if ($id === False) // function not supported 955 { 956 echo "<p>db::get_last_insert_id(table='$table',field='$field') not yet implemented for db-type '$this->Type' OR no insert operation before</p>\n"; 957 echo '<p>'.function_backtrace()."</p>\n"; 958 return -1; 959 } 960 return $id; 961 } 962 963 /** 964 * Get the number of rows affected by last update or delete 965 * 966 * @return int number of rows 967 */ 968 function affected_rows() 969 { 970 if ($this->log_updates) return 0; 971 972 if (!$this->Link_ID && !$this->connect()) 973 { 974 return False; 975 } 976 return $this->Link_ID->Affected_Rows(); 977 } 978 979 /** 980 * Get description of a table 981 * 982 * Beside the column-name all other data depends on the db-type !!! 983 * 984 * @param string $table name of table to describe 985 * @param bool $full optional, default False summary information, True full information 986 * @return array table meta data 987 */ 988 function metadata($table='',$full=false) 989 { 990 if (!$this->Link_ID && !$this->connect()) 991 { 992 return False; 993 } 994 $columns = $this->Link_ID->MetaColumns($table); 995 //$columns = $this->Link_ID->MetaColumnsSQL($table); 996 //echo "<b>metadata</b>('$table')=<pre>\n".print_r($columns,True)."</pre>\n"; 997 998 $metadata = array(); 999 $i = 0; 1000 foreach($columns as $column) 1001 { 1002 // for backwards compatibilty (depreciated) 1003 $flags = null; 1004 if($column->auto_increment) $flags .= "auto_increment "; 1005 if($column->primary_key) $flags .= "primary_key "; 1006 if($column->binary) $flags .= "binary "; 1007 1008 $metadata[$i] = array( 1009 'table' => $table, 1010 'name' => $column->name, 1011 'type' => $column->type, 1012 'len' => $column->max_length, 1013 'flags' => $flags, // for backwards compatibilty (depreciated) used by JiNN atm 1014 'not_null' => $column->not_null, 1015 'auto_increment' => $column->auto_increment, 1016 'primary_key' => $column->primary_key, 1017 'binary' => $column->binary, 1018 'has_default' => $column->has_default, 1019 'default' => $column->default_value, 1020 ); 1021 $metadata[$i]['table'] = $table; 1022 if ($full) 1023 { 1024 $metadata['meta'][$column->name] = $i; 1025 } 1026 ++$i; 1027 } 1028 if ($full) 1029 { 1030 $metadata['num_fields'] = $i; 1031 } 1032 return $metadata; 1033 } 1034 1035 /** 1036 * Get a list of table names in the current database 1037 * 1038 * @param boolean $just_name =false true return array of table-names, false return old format 1039 * @return array list of the tables 1040 */ 1041 function table_names($just_name=false) 1042 { 1043 if (!$this->Link_ID) $this->connect(); 1044 if (!$this->Link_ID) 1045 { 1046 return False; 1047 } 1048 $result = array(); 1049 $tables = $this->Link_ID->MetaTables('TABLES'); 1050 if (is_array($tables)) 1051 { 1052 foreach($tables as $table) 1053 { 1054 if ($this->capabilities[self::CAPABILITY_NAME_CASE] == 'upper') 1055 { 1056 $table = strtolower($table); 1057 } 1058 $result[] = $just_name ? $table : array( 1059 'table_name' => $table, 1060 'tablespace_name' => $this->Database, 1061 'database' => $this->Database 1062 ); 1063 } 1064 } 1065 return $result; 1066 } 1067 1068 /** 1069 * Return a list of indexes in current database 1070 * 1071 * @return array list of indexes 1072 */ 1073 function index_names() 1074 { 1075 $indices = array(); 1076 if ($this->Type != 'pgsql') 1077 { 1078 echo "<p>db::index_names() not yet implemented for db-type '$this->Type'</p>\n"; 1079 return $indices; 1080 } 1081 foreach($this->query("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relkind ='i' ORDER BY relname") as $row) 1082 { 1083 $indices[] = array( 1084 'index_name' => $row[0], 1085 'tablespace_name' => $this->Database, 1086 'database' => $this->Database, 1087 ); 1088 } 1089 return $indices; 1090 } 1091 1092 /** 1093 * Returns an array containing column names that are the primary keys of $tablename. 1094 * 1095 * @return array of columns 1096 */ 1097 function pkey_columns($tablename) 1098 { 1099 if (!$this->Link_ID && !$this->connect()) 1100 { 1101 return False; 1102 } 1103 return $this->Link_ID->MetaPrimaryKeys($tablename); 1104 } 1105 1106 /** 1107 * Create a new database 1108 * 1109 * @param string $adminname name of database administrator user (optional) 1110 * @param string $adminpasswd password for the database administrator user (optional) 1111 * @param string $charset default charset for the database 1112 * @param string $grant_host ='localhost' host/ip of the webserver 1113 */ 1114 function create_database($adminname = '', $adminpasswd = '', $charset='', $grant_host='localhost') 1115 { 1116 $currentUser = $this->User; 1117 $currentPassword = $this->Password; 1118 $currentDatabase = $this->Database; 1119 1120 if ($adminname != '') 1121 { 1122 $this->User = $adminname; 1123 $this->Password = $adminpasswd; 1124 $this->Database = $this->Type == 'pgsql' ? 'template1' : 'mysql'; 1125 } 1126 $this->disconnect(); 1127 1128 $sqls = array(); 1129 switch ($this->Type) 1130 { 1131 case 'pgsql': 1132 $sqls[] = "CREATE DATABASE $currentDatabase"; 1133 break; 1134 case 'mysql': 1135 case 'mysqli': 1136 case 'mysqlt': 1137 $create = "CREATE DATABASE `$currentDatabase`"; 1138 if ($charset && isset($this->Link_ID->charset2mysql[$charset]) && (float) $this->ServerInfo['version'] >= 4.1) 1139 { 1140 $create .= ' DEFAULT CHARACTER SET '.$this->Link_ID->charset2mysql[$charset].';'; 1141 } 1142 $sqls[] = $create; 1143 $sqls[] = "CREATE USER $currentUser@'$grant_host' IDENTIFIED BY ".$this->quote($currentPassword); 1144 $sqls[] = "GRANT ALL PRIVILEGES ON `$currentDatabase`.* TO $currentUser@'$grant_host'"; 1145 break; 1146 default: 1147 throw new Exception\WrongParameter(__METHOD__."(user=$adminname, \$pw) not yet implemented for DB-type '$this->Type'"); 1148 } 1149 //error_log(__METHOD__."() this->Type=$this->Type: sqls=".array2string($sqls)); 1150 foreach($sqls as $sql) 1151 { 1152 $this->query($sql,__LINE__,__FILE__); 1153 } 1154 $this->disconnect(); 1155 1156 $this->User = $currentUser; 1157 $this->Password = $currentPassword; 1158 $this->Database = $currentDatabase; 1159 $this->connect(); 1160 } 1161 1162 /** 1163 * Set session timezone, to get automatic timestamps to be in our configured timezone 1164 * 1165 * @param string $timezone 1166 * @return ?boolean 1167 */ 1168 public function setTimeZone($timezone) 1169 { 1170 if (!$this->Link_ID && !$this->connect()) 1171 { 1172 return False; 1173 } 1174 switch ($this->Type) 1175 { 1176 case 'pgsql': 1177 $sql = 'SET TIME ZONE ' . $this->quote($timezone); 1178 break; 1179 case 'mysql': 1180 case 'mysqli': 1181 $sql = 'SET time_zone=' . $this->quote($timezone); 1182 break; 1183 } 1184 if (!empty($timezone) && !empty($sql)) 1185 { 1186 $this->Link_ID->Execute($sql); 1187 return true; 1188 } 1189 } 1190 1191 /** 1192 * concat a variable number of strings together, to be used in a query 1193 * 1194 * Example: $db->concat($db->quote('Hallo '),'username') would return 1195 * for mysql "concat('Hallo ',username)" or "'Hallo ' || username" for postgres 1196 * @param string $str1 already quoted stringliteral or column-name, variable number of arguments 1197 * @return string to be used in a query 1198 */ 1199 function concat(/*$str1, ...*/) 1200 { 1201 $args = func_get_args(); 1202 1203 if (!$this->Link_ID && !$this->connect()) 1204 { 1205 return False; 1206 } 1207 return call_user_func_array(array(&$this->Link_ID,'concat'),$args); 1208 } 1209 1210 /** 1211 * Concat grouped values of an expression with optional order and separator 1212 * 1213 * @param string $expr column-name or expression optional prefixed with "DISTINCT" 1214 * @param string $order_by ='' optional order 1215 * @param string $separator =',' optional separator, default is comma 1216 * @return string|boolean false if not supported by dbms 1217 */ 1218 function group_concat($expr, $order_by='', $separator=',') 1219 { 1220 switch($this->Type) 1221 { 1222 case 'mysqli': 1223 case 'mysql': 1224 $sql = 'GROUP_CONCAT('.$expr; 1225 if ($order_by) $sql .= ' ORDER BY '.$order_by; 1226 if ($separator != ',') $sql .= ' SEPARATOR '.$this->quote($separator); 1227 $sql .= ')'; 1228 break; 1229 1230 case 'pgsql': // requires for Postgresql < 8.4 to have a custom ARRAY_AGG method installed! 1231 if ($this->Type == 'pgsql' && $this->ServerInfo['version'] < 8.4) 1232 { 1233 return false; 1234 } 1235 $sql = 'ARRAY_TO_STRING(ARRAY_AGG('.$expr; 1236 if ($order_by) $sql .= ' ORDER BY '.$order_by; 1237 $sql .= '), '.$this->quote($separator).')'; 1238 break; 1239 1240 default: // probably gives an sql error anyway 1241 return false; 1242 } 1243 return $sql; 1244 } 1245 1246 /** 1247 * SQL returning character (not byte!) positions for $substr in $str 1248 * 1249 * @param string $str 1250 * @param string $substr 1251 * @return string SQL returning character (not byte!) positions for $substr in $str 1252 */ 1253 function strpos($str, $substr) 1254 { 1255 switch($this->Type) 1256 { 1257 case 'mysql': 1258 return "LOCATE($substr,$str)"; 1259 case 'pgsql': 1260 return "STRPOS($str,$substr)"; 1261 case 'mssql': 1262 return "CHARINDEX($substr,$str)"; 1263 } 1264 die(__METHOD__." not implemented for DB type '$this->Type'!"); 1265 } 1266 1267 /** 1268 * Convert a DB specific timestamp in a unix timestamp stored as integer, like MySQL: UNIX_TIMESTAMP(ts) 1269 * 1270 * @param string $expr name of an integer column or integer expression 1271 * @return string SQL expression of type timestamp 1272 */ 1273 function unix_timestamp($expr) 1274 { 1275 switch($this->Type) 1276 { 1277 case 'mysql': 1278 return "UNIX_TIMESTAMP($expr)"; 1279 1280 case 'pgsql': 1281 return "EXTRACT(EPOCH FROM CAST($expr AS TIMESTAMP))"; 1282 1283 case 'mssql': 1284 return "DATEDIFF(second,'1970-01-01',($expr))"; 1285 } 1286 } 1287 1288 /** 1289 * Convert a unix timestamp stored as integer in the db into a db timestamp, like MySQL: FROM_UNIXTIME(ts) 1290 * 1291 * @param string $expr name of an integer column or integer expression 1292 * @return string SQL expression of type timestamp 1293 */ 1294 function from_unixtime($expr) 1295 { 1296 switch($this->Type) 1297 { 1298 case 'mysql': 1299 return "FROM_UNIXTIME($expr)"; 1300 1301 case 'pgsql': 1302 return "(TIMESTAMP WITH TIME ZONE 'epoch' + ($expr) * INTERVAL '1 sec')"; 1303 1304 case 'mssql': // we use date(,0) as we store server-time 1305 return "DATEADD(second,($expr),'".date('Y-m-d H:i:s',0)."')"; 1306 } 1307 return false; 1308 } 1309 1310 /** 1311 * format a timestamp as string, like MySQL: DATE_FORMAT(ts) 1312 * 1313 * Please note: only a subset of the MySQL formats are implemented 1314 * 1315 * @param string $expr name of a timestamp column or timestamp expression 1316 * @param string $format format specifier like '%Y-%m-%d %H:%i:%s' or '%V%X' ('%v%x') weeknumber & year with Sunday (Monday) as first day 1317 * @return string SQL expression of type timestamp 1318 */ 1319 function date_format($expr,$format) 1320 { 1321 switch($this->Type) 1322 { 1323 case 'mysql': 1324 return "DATE_FORMAT($expr,'$format')"; 1325 1326 case 'pgsql': 1327 $format = str_replace( 1328 array('%Y', '%y','%m','%d','%H', '%h','%i','%s','%V','%v','%X', '%x'), 1329 array('YYYY','YY','MM','DD','HH24','HH','MI','SS','IW','IW','YYYY','YYYY'), 1330 $format); 1331 return "TO_CHAR($expr,'$format')"; 1332 1333 case 'mssql': 1334 $from = $to = array(); 1335 foreach(array('%Y'=>'yyyy','%y'=>'yy','%m'=>'mm','%d'=>'dd','%H'=>'hh','%i'=>'mi','%s'=>'ss','%V'=>'wk','%v'=>'wk','%X'=>'yyyy','%x'=>'yyyy') as $f => $t) 1336 { 1337 $from[] = $f; 1338 $to[] = "'+DATEPART($t,($expr))+'"; 1339 } 1340 $from[] = "''+"; $to[] = ''; 1341 $from[] = "+''"; $to[] = ''; 1342 return str_replace($from,$to,$format); 1343 } 1344 return false; 1345 } 1346 1347 /** 1348 * Cast a column or sql expression to integer, necessary at least for postgreSQL or MySQL for sorting 1349 * 1350 * @param string $expr 1351 * @return string 1352 */ 1353 function to_double($expr) 1354 { 1355 switch($this->Type) 1356 { 1357 case 'pgsql': 1358 return $expr.'::double'; 1359 case 'mysql': 1360 return 'CAST('.$expr.' AS DECIMAL(24,3))'; 1361 } 1362 return $expr; 1363 } 1364 1365 /** 1366 * Cast a column or sql expression to integer, necessary at least for postgreSQL 1367 * 1368 * @param string $expr 1369 * @return string 1370 */ 1371 function to_int($expr) 1372 { 1373 switch($this->Type) 1374 { 1375 case 'pgsql': 1376 return $expr.'::integer'; 1377 case 'mysql': 1378 return 'CAST('.$expr.' AS SIGNED)'; 1379 } 1380 return $expr; 1381 } 1382 1383 /** 1384 * Cast a column or sql expression to varchar, necessary at least for postgreSQL 1385 * 1386 * @param string $expr 1387 * @return string 1388 */ 1389 function to_varchar($expr) 1390 { 1391 switch($this->Type) 1392 { 1393 case 'pgsql': 1394 return 'CAST('.$expr.' AS varchar)'; 1395 } 1396 return $expr; 1397 } 1398 1399 /** 1400 * Correctly Quote Identifiers like table- or colmnnames for use in SQL-statements 1401 * 1402 * This is mostly copy & paste from adodb's datadict class 1403 * @param string $_name 1404 * @return string quoted string 1405 */ 1406 function name_quote($_name = NULL) 1407 { 1408 if (!is_string($_name)) 1409 { 1410 return false; 1411 } 1412 1413 $name = trim($_name); 1414 1415 if (!$this->Link_ID && !$this->connect()) 1416 { 1417 return false; 1418 } 1419 1420 $quote = $this->Link_ID->nameQuote; 1421 $type = $this->Type; 1422 1423 // if name is of the form `name`, remove MySQL quotes and leave it to automatic below 1424 if ($name[0] === '`' && substr($name, -1) === '`') 1425 { 1426 $name = substr($name, 1, -1); 1427 } 1428 1429 $quoted = array_map(function($name) use ($quote, $type) 1430 { 1431 // if name contains special characters, quote it 1432 // always quote for postgreSQL, as this is the only way to support mixed case names 1433 if (preg_match('/\W/', $name) || $type == 'pgsql' && preg_match('/[A-Z]+/', $name) || $name == 'index') 1434 { 1435 return $quote . $name . $quote; 1436 } 1437 return $name; 1438 }, explode('.', $name)); 1439 1440 return implode('.', $quoted); 1441 } 1442 1443 /** 1444 * Escape values before sending them to the database - prevents SQL injection and SQL errors ;-) 1445 * 1446 * Please note that the quote function already returns necessary quotes: quote('Hello') === "'Hello'". 1447 * Int and Auto types are casted to int: quote('1','int') === 1, quote('','int') === 0, quote('Hello','int') === 0 1448 * Arrays of id's stored in strings: quote(array(1,2,3),'string') === "'1,2,3'" 1449 * 1450 * @param mixed $value the value to be escaped 1451 * @param string|boolean $type =false string the type of the db-column, default False === varchar 1452 * @param boolean $not_null =true is column NOT NULL, default true, else php null values are written as SQL NULL 1453 * @param int $length =null length of the varchar column, to truncate it if the database requires it (eg. Postgres) 1454 * @param string $glue =',' used to glue array values together for the string type 1455 * @return string escaped sting 1456 */ 1457 function quote($value,$type=False,$not_null=true,$length=null,$glue=',') 1458 { 1459 if ($this->Debug) echo "<p>db::quote(".(is_null($value)?'NULL':"'$value'").",'$type','$not_null')</p>\n"; 1460 1461 if (!$not_null && is_null($value)) // writing unset php-variables and those set to NULL now as SQL NULL 1462 { 1463 return 'NULL'; 1464 } 1465 switch($type) 1466 { 1467 case 'int': 1468 // if DateTime object given, convert it to a unix timestamp (NOT converting the timezone!) 1469 if (is_object($value) && ($value instanceof \DateTime)) 1470 { 1471 return ($value instanceof DateTime) ? $value->format('ts') : DateTime::to($value,'ts'); 1472 } 1473 case 'auto': 1474 // atm. (php5.2) php has only 32bit integers, it converts everything else to float. 1475 // Casting it to int gives a negative number instead of the big 64bit integer! 1476 // There for we have to keep it as float by using round instead the int cast. 1477 return is_float($value) ? round($value) : (int) $value; 1478 case 'bool': 1479 if ($this->Type == 'mysql') // maybe it's not longer necessary with mysql5 1480 { 1481 return $value ? 1 : 0; 1482 } 1483 return $value ? 'true' : 'false'; 1484 case 'float': 1485 case 'decimal': 1486 return (double) $value; 1487 } 1488 if (!$this->Link_ID && !$this->connect()) 1489 { 1490 return False; 1491 } 1492 switch($type) 1493 { 1494 case 'blob': 1495 switch ($this->Link_ID->blobEncodeType) 1496 { 1497 case 'C': // eg. postgres 1498 return "'" . $this->Link_ID->BlobEncode($value) . "'"; 1499 case 'I': 1500 return $this->Link_ID->BlobEncode($value); 1501 } 1502 break; // handled like strings 1503 case 'date': 1504 // if DateTime object given, convert it (NOT converting the timezone!) 1505 if (is_object($value) && ($value instanceof \DateTime)) 1506 { 1507 return $this->Link_ID->qstr($value->format('Y-m-d')); 1508 } 1509 return $this->Link_ID->DBDate($value); 1510 case 'timestamp': 1511 // if DateTime object given, convert it (NOT converting the timezone!) 1512 if (is_object($value) && ($value instanceof \DateTime)) 1513 { 1514 return $this->Link_ID->qstr($value->format('Y-m-d H:i:s')); 1515 } 1516 return $this->Link_ID->DBTimeStamp($value); 1517 } 1518 if (is_array($value)) 1519 { 1520 $value = implode($glue,$value); 1521 } 1522 // truncate to long strings for varchar(X) columns as PostgreSQL and newer MySQL/MariaDB given an error otherwise 1523 if (!is_null($length) && mb_strlen($value) > $length) 1524 { 1525 $value = mb_substr($value, 0, $length); 1526 } 1527 // casting boolean explicitly to string, as ADODB_postgres64::qstr() has an unwanted special handling 1528 // for boolean types, causing it to return "true" or "false" and not a quoted string like "'1'"! 1529 if (is_bool($value)) $value = (string)$value; 1530 1531 // MySQL and MariaDB not 10.1 need 4-byte utf8 chars replaced with our default utf8 charset 1532 // (MariaDB 10.1 does the replacement automatic, 10.0 cuts everything off behind and MySQL gives an error) 1533 // (MariaDB 10.3 gives an error too: Incorrect string value: '\xF0\x9F\x98\x8A\x0AW...') 1534 // Changing charset to utf8mb4 requires schema update, shortening of some indexes and probably have negative impact on performace! 1535 if (substr($this->Type, 0, 5) == 'mysql') 1536 { 1537 $value = preg_replace('/[\x{10000}-\x{10FFFF}]/u', "\xEF\xBF\xBD", $value); 1538 } 1539 1540 // need to cast to string, as ADOdb 5.20 would return NULL instead of '' for NULL, causing us to write that into NOT NULL columns 1541 return $this->Link_ID->qstr((string)$value); 1542 } 1543 1544 /** 1545 * Implodes an array of column-value pairs for the use in sql-querys. 1546 * All data is run through quote (does either addslashes() or (int)) - prevents SQL injunction and SQL errors ;-). 1547 * 1548 * @author RalfBecker<at>outdoor-training.de 1549 * 1550 * @param string $glue in most cases this will be either ',' or ' AND ', depending you your query 1551 * @param array $array column-name / value pairs, if the value is an array all its array-values will be quoted 1552 * according to the type of the column, and the whole array with be formatted like (val1,val2,...) 1553 * If $use_key == True, an ' IN ' instead a '=' is used. Good for category- or user-lists. 1554 * If the key is numerical (no key given in the array-definition) the value is used as is, eg. 1555 * array('visits=visits+1') gives just "visits=visits+1" (no quoting at all !!!) 1556 * @param boolean|string $use_key If $use_key===True a "$key=" prefix each value (default), typically set to False 1557 * or 'VALUES' for insert querys, on 'VALUES' "(key1,key2,...) VALUES (val1,val2,...)" is returned 1558 * @param array|boolean $only if set to an array only colums which are set (as data !!!) are written 1559 * typicaly used to form a WHERE-clause from the primary keys. 1560 * If set to True, only columns from the colum_definitons are written. 1561 * @param array|boolean $column_definitions this can be set to the column-definitions-array 1562 * of your table ($tables_baseline[$table]['fd'] of the setup/tables_current.inc.php file). 1563 * If its set, the column-type-data determinates if (int) or addslashes is used. 1564 * @return string SQL 1565 */ 1566 function column_data_implode($glue,$array,$use_key=True,$only=False,$column_definitions=False) 1567 { 1568 if (!is_array($array)) // this allows to give an SQL-string for delete or update 1569 { 1570 return $array; 1571 } 1572 if (!$column_definitions) 1573 { 1574 $column_definitions = $this->column_definitions; 1575 } 1576 if ($this->Debug) echo "<p>db::column_data_implode('$glue',".print_r($array,True).",'$use_key',".print_r($only,True).",<pre>".print_r($column_definitions,True)."</pre>\n"; 1577 1578 // do we need to truncate varchars to their max length (INSERT and UPDATE on Postgres) 1579 $truncate_varchar = $glue == ',' && $this->capabilities[self::CAPABILITY_REQUIRE_TRUNCATE_VARCHAR]; 1580 1581 $keys = $values = array(); 1582 foreach($array as $key => $data) 1583 { 1584 if (is_int($key) && $use_key !== 'VALUES' || !$only || $only === True && isset($column_definitions[$key]) || 1585 is_array($only) && in_array($key,$only)) 1586 { 1587 $keys[] = $this->name_quote($key); 1588 1589 $col = $key; 1590 // fix "table.column" expressions, to not trigger exception, if column alone would work 1591 if (!is_int($key) && is_array($column_definitions) && !isset($column_definitions[$key])) 1592 { 1593 if (strpos($key, '.') !== false) list(, $col) = explode('.', $key); 1594 if (!isset($column_definitions[$col])) 1595 { 1596 throw new Db\Exception\InvalidSql("db::column_data_implode('$glue',".print_r($array,True).",'$use_key',".print_r($only,True).",<pre>".print_r($column_definitions,True)."</pre><b>nothing known about column '$key'!</b>"); 1597 } 1598 } 1599 $column_type = is_array($column_definitions) ? @$column_definitions[$col]['type'] : False; 1600 $not_null = is_array($column_definitions) && isset($column_definitions[$col]['nullable']) ? !$column_definitions[$col]['nullable'] : false; 1601 1602 $maxlength = null; 1603 if ($truncate_varchar) 1604 { 1605 $maxlength = in_array($column_definitions[$col]['type'], array('varchar','ascii')) ? $column_definitions[$col]['precision'] : null; 1606 } 1607 // dont use IN ( ), if there's only one value, it's slower for MySQL 1608 if (is_array($data) && count($data) <= 1) 1609 { 1610 $data = array_shift($data); 1611 } 1612 // array for SET or VALUES, not WHERE --> automatic store comma-separated 1613 if (is_array($data) && $glue === ',' && in_array($column_type, ['varchar','ascii'])) 1614 { 1615 $data = implode(',', $data); 1616 } 1617 if (is_array($data)) 1618 { 1619 $or_null = ''; 1620 foreach($data as $k => $v) 1621 { 1622 if (!$not_null && $use_key===True && is_null($v)) 1623 { 1624 $or_null = $this->name_quote($key).' IS NULL)'; 1625 unset($data[$k]); 1626 continue; 1627 } 1628 $data[$k] = $this->quote($v,$column_type,$not_null,$maxlength); 1629 } 1630 $values[] = ($or_null?'(':'').(!count($data) ? 1631 // empty array on insert/update, store as NULL, or if not allowed whatever value NULL is casted to 1632 $this->quote(null, $column_type, $not_null) : 1633 ($use_key===True ? $this->name_quote($key).' IN ' : '') . 1634 '('.implode(',',$data).')'.($or_null ? ' OR ' : '')).$or_null; 1635 } 1636 elseif (is_int($key) && $use_key===True) 1637 { 1638 if (empty($data)) continue; // would give SQL error 1639 $values[] = $data; 1640 } 1641 elseif ($glue != ',' && $use_key === True && !$not_null && is_null($data)) 1642 { 1643 $values[] = $this->name_quote($key) .' IS NULL'; 1644 } 1645 else 1646 { 1647 $values[] = ($use_key===True ? $this->name_quote($key) . '=' : '') . $this->quote($data,$column_type,$not_null,$maxlength); 1648 } 1649 } 1650 } 1651 return ($use_key==='VALUES' ? '('.implode(',',$keys).') VALUES (' : ''). 1652 implode($glue,$values) . ($use_key==='VALUES' ? ')' : ''); 1653 } 1654 1655 /** 1656 * Sets the default column-definitions for use with column_data_implode() 1657 * 1658 * @author RalfBecker<at>outdoor-training.de 1659 * 1660 * @param array|boolean $column_definitions this can be set to the column-definitions-array 1661 * of your table ($tables_baseline[$table]['fd'] of the setup/tables_current.inc.php file). 1662 * If its set, the column-type-data determinates if (int) or addslashes is used. 1663 */ 1664 function set_column_definitions($column_definitions=False) 1665 { 1666 $this->column_definitions=$column_definitions; 1667 } 1668 1669 /** 1670 * Application name used by the API 1671 * 1672 */ 1673 const API_APPNAME = 'api'; 1674 /** 1675 * Default app, if no app specified in select, insert, delete, ... 1676 * 1677 * @var string 1678 */ 1679 protected $app=self::API_APPNAME; 1680 1681 /** 1682 * Sets the application in which the db-class looks for table-defintions 1683 * 1684 * Used by table_definitions, insert, update, select, expression and delete. If the app is not set via set_app, 1685 * it need to be set for these functions on every call 1686 * 1687 * @param string $app the app-name 1688 */ 1689 function set_app($app) 1690 { 1691 // ease the transition to api 1692 if ($app == 'phpgwapi') $app = 'api'; 1693 1694 if ($this === $GLOBALS['egw']->db && $app != self::API_APPNAME) 1695 { 1696 // prevent that anyone switches the global db object to an other app 1697 throw new Exception\WrongParameter('You are not allowed to call set_app for $GLOBALS[egw]->db or a refence to it, you have to clone it!'); 1698 } 1699 $this->app = $app; 1700 } 1701 1702 /** 1703 * Data used by (get|set)_table_defintion and get_column_attribute 1704 * 1705 * @var array 1706 */ 1707 protected static $all_app_data = array(); 1708 1709 /** 1710 * Set/changes definition of one table 1711 * 1712 * If you set or change defition of a single table of an app, other tables 1713 * are not loaded from $app/setup/tables_current.inc.php! 1714 * 1715 * @param string $app name of the app $table belongs too 1716 * @param string $table table name 1717 * @param array $definition table definition 1718 */ 1719 public static function set_table_definitions($app, $table, array $definition) 1720 { 1721 self::$all_app_data[$app][$table] = $definition; 1722 } 1723 1724 /** 1725 * reads the table-definitions from the app's setup/tables_current.inc.php file 1726 * 1727 * The already read table-definitions are shared between all db-instances via a static var. 1728 * 1729 * @author RalfBecker<at>outdoor-training.de 1730 * 1731 * @param bool|string $app name of the app or default False to use the app set by db::set_app or the current app, 1732 * true to search the already loaded table-definitions for $table and then search all existing apps for it 1733 * @param bool|string $table if set return only defintions of that table, else return all defintions 1734 * @return mixed array with table-defintions or False if file not found 1735 */ 1736 function get_table_definitions($app=False,$table=False) 1737 { 1738 // ease the transition to api 1739 if ($app === 'phpgwapi') $app = 'api'; 1740 1741 if ($app === true && $table) 1742 { 1743 foreach(self::$all_app_data as $app => &$app_data) 1744 { 1745 if (isset($app_data[$table])) 1746 { 1747 return $app_data[$table]; 1748 } 1749 } 1750 // $table not found in loaded apps, check not yet loaded ones 1751 foreach(scandir(EGW_INCLUDE_ROOT) as $app) 1752 { 1753 if ($app[0] == '.' || !is_dir(EGW_INCLUDE_ROOT.'/'.$app) || isset(self::$all_app_data[$app])) 1754 { 1755 continue; 1756 } 1757 $tables_current = EGW_INCLUDE_ROOT . "/$app/setup/tables_current.inc.php"; 1758 if (!@file_exists($tables_current)) 1759 { 1760 self::$all_app_data[$app] = False; 1761 } 1762 else 1763 { 1764 $phpgw_baseline = null; 1765 include($tables_current); 1766 self::$all_app_data[$app] =& $phpgw_baseline; 1767 unset($phpgw_baseline); 1768 1769 if (isset(self::$all_app_data[$app][$table])) 1770 { 1771 return self::$all_app_data[$app][$table]; 1772 } 1773 } 1774 } 1775 $app = false; 1776 } 1777 if (!$app) 1778 { 1779 $app = $this->app ? $this->app : $GLOBALS['egw_info']['flags']['currentapp']; 1780 } 1781 $app_data =& self::$all_app_data[$app]; 1782 1783 if (!isset($app_data)) 1784 { 1785 $tables_current = EGW_INCLUDE_ROOT . "/$app/setup/tables_current.inc.php"; 1786 if (!@file_exists($tables_current)) 1787 { 1788 return $app_data = False; 1789 } 1790 include($tables_current); 1791 $app_data =& $phpgw_baseline; 1792 unset($phpgw_baseline); 1793 } 1794 if ($table && (!$app_data || !isset($app_data[$table]))) 1795 { 1796 if ($this->Debug) echo "<p>!!!get_table_definitions($app,$table) failed!!!</p>\n"; 1797 return False; 1798 } 1799 if ($this->Debug) echo "<p>get_table_definitions($app,$table) succeeded</p>\n"; 1800 return $table ? $app_data[$table] : $app_data; 1801 } 1802 1803 /** 1804 * Get specified attribute (default comment) of a colum or whole definition (if $attribute === null) 1805 * 1806 * Can be used static, in which case the global db object is used ($GLOBALS['egw']->db) and $app should be specified 1807 * 1808 * @param string $column name of column 1809 * @param string $table name of table 1810 * @param string $app=null app name or NULL to use $this->app, set via self::set_app() 1811 * @param string $attribute='comment' what field to return, NULL for array with all fields, default 'comment' to return the comment 1812 * @return string|array NULL if table or column or attribute not found 1813 */ 1814 /* static */ function get_column_attribute($column,$table,$app=null,$attribute='comment') 1815 { 1816 static $cached_columns=null,$cached_table=null; // some caching 1817 1818 if ($cached_table !== $table || is_null($cached_columns)) 1819 { 1820 $db = isset($this) && is_a($this, __CLASS__) ? $this : $GLOBALS['egw']->db; 1821 $table_def = $db->get_table_definitions($app,$table); 1822 $cached_columns = is_array($table_def) ? $table_def['fd'] : false; 1823 } 1824 if ($cached_columns === false) return null; 1825 1826 return is_null($attribute) ? $cached_columns[$column] : $cached_columns[$column][$attribute]; 1827 } 1828 1829 /** 1830 * Insert a row of data into a table or updates it if $where is given, all data is quoted according to it's type 1831 * 1832 * @author RalfBecker<at>outdoor-training.de 1833 * 1834 * @param string $table name of the table 1835 * @param array $data with column-name / value pairs 1836 * @param mixed $where string with where clause or array with column-name / values pairs to check if a row with that keys already exists, or false for an unconditional insert 1837 * if the row exists db::update is called else a new row with $date merged with $where gets inserted (data has precedence) 1838 * @param int $line line-number to pass to query 1839 * @param string $file file-name to pass to query 1840 * @param string|boolean $app string with name of app or False to use the current-app 1841 * @param bool $use_prepared_statement use a prepared statement 1842 * @param array|bool $table_def use this table definition. If False, the table definition will be read from tables_baseline 1843 * @return ADORecordSet or false, if the query fails 1844 */ 1845 function insert($table,$data,$where,$line,$file,$app=False,$use_prepared_statement=false,$table_def=False) 1846 { 1847 if ($this->Debug) echo "<p>db::insert('$table',".print_r($data,True).",".print_r($where,True).",$line,$file,'$app')</p>\n"; 1848 1849 if (!$table_def) $table_def = $this->get_table_definitions($app,$table); 1850 1851 $sql_append = ''; 1852 $cmd = 'INSERT'; 1853 if (is_array($where) && count($where)) 1854 { 1855 switch($this->Type) 1856 { 1857 case 'sapdb': case 'maxdb': 1858 $sql_append = ' UPDATE DUPLICATES'; 1859 break; 1860 case 'mysql': 1861 // use replace if primary keys are included 1862 if (count(array_intersect(array_keys($where),(array)$table_def['pk'])) == count($table_def['pk'])) 1863 { 1864 $cmd = 'REPLACE'; 1865 break; 1866 } 1867 // fall through !!! 1868 default: 1869 if ($this->select($table,'count(*)',$where,$line,$file)->fetchColumn()) 1870 { 1871 return !!$this->update($table,$data,$where,$line,$file,$app,$use_prepared_statement,$table_def); 1872 } 1873 break; 1874 } 1875 // the checked values need to be inserted too, value in data has precedence, also cant insert sql strings (numerical id) 1876 foreach($where as $column => $value) 1877 { 1878 if (!is_numeric($column) && !isset($data[$column]) && 1879 // skip auto-id of 0 or NULL, as PostgreSQL does NOT create an auto-id, if they are given 1880 !(!$value && count($table_def['pk']) == 1 && $column == $table_def['pk'][0])) 1881 { 1882 $data[$column] = $value; 1883 } 1884 } 1885 } 1886 if (self::$tablealiases && isset(self::$tablealiases[$table])) 1887 { 1888 $table = self::$tablealiases[$table]; 1889 } 1890 $inputarr = false; 1891 if (isset($data[0]) && is_array($data[0])) // multiple data rows 1892 { 1893 if ($where) throw new Exception\WrongParameter('Can NOT use $where together with multiple data rows in $data!'); 1894 1895 $sql = "$cmd INTO $table "; 1896 foreach($data as $k => $d) 1897 { 1898 if (!$k) 1899 { 1900 $sql .= $this->column_data_implode(',',$d,'VALUES',true,$table_def['fd']); 1901 } 1902 else 1903 { 1904 $sql .= ",\n(".$this->column_data_implode(',',$d,false,true,$table_def['fd']).')'; 1905 } 1906 } 1907 $sql .= $sql_append; 1908 } 1909 elseif ($use_prepared_statement && $this->Link_ID->_bindInputArray) // eg. MaxDB 1910 { 1911 $this->Link_ID->Param(false); // reset param-counter 1912 $cols = array_keys($data); 1913 foreach($cols as $k => $col) 1914 { 1915 if (!isset($table_def['fd'][$col])) // ignore columns not in this table 1916 { 1917 unset($cols[$k]); 1918 continue; 1919 } 1920 $params[] = $this->Link_ID->Param($col); 1921 } 1922 $sql = "$cmd INTO $table (".implode(',',$cols).') VALUES ('.implode(',',$params).')'.$sql_append; 1923 // check if we already prepared that statement 1924 if (!isset($this->prepared_sql[$sql])) 1925 { 1926 $this->prepared_sql[$sql] = $this->Link_ID->Prepare($sql); 1927 } 1928 $sql = $this->prepared_sql[$sql]; 1929 $inputarr = &$data; 1930 } 1931 else 1932 { 1933 $sql = "$cmd INTO $table ".$this->column_data_implode(',',$data,'VALUES',true,$table_def['fd']).$sql_append; 1934 } 1935 if ($this->Debug) echo "<p>db::insert('$table',".print_r($data,True).",".print_r($where,True).",$line,$file,'$app') sql='$sql'</p>\n"; 1936 return $this->query($sql,$line,$file,0,-1,$inputarr); 1937 } 1938 1939 /** 1940 * Updates the data of one or more rows in a table, all data is quoted according to it's type 1941 * 1942 * @author RalfBecker<at>outdoor-training.de 1943 * 1944 * @param string $table name of the table 1945 * @param array $data with column-name / value pairs 1946 * @param array $where column-name / values pairs and'ed together for the where clause 1947 * @param int $line line-number to pass to query 1948 * @param string $file file-name to pass to query 1949 * @param string|boolean $app string with name of app or False to use the current-app 1950 * @param bool $use_prepared_statement use a prepared statement 1951 * @param array|bool $table_def use this table definition. If False, the table definition will be read from tables_baseline 1952 * @return ADORecordSet or false, if the query fails 1953 */ 1954 function update($table,$data,$where,$line,$file,$app=False,$use_prepared_statement=false,$table_def=False) 1955 { 1956 if ($this->Debug) echo "<p>db::update('$table',".print_r($data,true).','.print_r($where,true).",$line,$file,'$app')</p>\n"; 1957 if (!$table_def) $table_def = $this->get_table_definitions($app,$table); 1958 1959 $blobs2update = array(); 1960 // SapDB/MaxDB cant update LONG columns / blob's: if a blob-column is included in the update we remember it in $blobs2update 1961 // and remove it from $data 1962 switch ($this->Type) 1963 { 1964 case 'sapdb': 1965 case 'maxdb': 1966 if ($use_prepared_statement) break; 1967 // check if data contains any LONG columns 1968 foreach($data as $col => $val) 1969 { 1970 switch ($table_def['fd'][$col]['type']) 1971 { 1972 case 'text': 1973 case 'longtext': 1974 case 'blob': 1975 $blobs2update[$col] = &$data[$col]; 1976 unset($data[$col]); 1977 break; 1978 } 1979 } 1980 break; 1981 } 1982 $where_str = $this->column_data_implode(' AND ',$where,True,true,$table_def['fd']); 1983 1984 if (self::$tablealiases && isset(self::$tablealiases[$table])) 1985 { 1986 $table = self::$tablealiases[$table]; 1987 } 1988 if (!empty($data)) 1989 { 1990 $inputarr = false; 1991 if ($use_prepared_statement && $this->Link_ID->_bindInputArray) // eg. MaxDB 1992 { 1993 $this->Link_ID->Param(false); // reset param-counter 1994 foreach($data as $col => $val) 1995 { 1996 if (!isset($table_def['fd'][$col])) continue; // ignore columns not in this table 1997 $params[] = $this->name_quote($col).'='.$this->Link_ID->Param($col); 1998 } 1999 $sql = "UPDATE $table SET ".implode(',',$params).' WHERE '.$where_str; 2000 // check if we already prepared that statement 2001 if (!isset($this->prepared_sql[$sql])) 2002 { 2003 $this->prepared_sql[$sql] = $this->Link_ID->Prepare($sql); 2004 } 2005 $sql = $this->prepared_sql[$sql]; 2006 $inputarr = &$data; 2007 } 2008 else 2009 { 2010 $sql = "UPDATE $table SET ". 2011 $this->column_data_implode(',',$data,True,true,$table_def['fd']).' WHERE '.$where_str; 2012 } 2013 $ret = $this->query($sql,$line,$file,0,-1,$inputarr); 2014 if ($this->Debug) echo "<p>db::query('$sql',$line,$file)</p>\n"; 2015 } 2016 // if we have any blobs to update, we do so now 2017 if (($ret || !count($data)) && count($blobs2update)) 2018 { 2019 foreach($blobs2update as $col => $val) 2020 { 2021 $ret = $this->Link_ID->UpdateBlob($table,$col,$val,$where_str,$table_def['fd'][$col]['type'] == 'blob' ? 'BLOB' : 'CLOB'); 2022 if ($this->Debug) echo "<p>adodb::UpdateBlob('$table','$col','$val','$where_str') = '$ret'</p>\n"; 2023 if (!$ret) throw new Db\Exception\InvalidSql("Error in UpdateBlob($table,$col,\$val,$where_str)",$line,$file); 2024 } 2025 } 2026 return $ret; 2027 } 2028 2029 /** 2030 * Deletes one or more rows in table, all data is quoted according to it's type 2031 * 2032 * @author RalfBecker<at>outdoor-training.de 2033 * 2034 * @param string $table name of the table 2035 * @param array $where column-name / values pairs and'ed together for the where clause 2036 * @param int $line line-number to pass to query 2037 * @param string $file file-name to pass to query 2038 * @param string|boolean $app string with name of app or False to use the current-app 2039 * @param array|bool $table_def use this table definition. If False, the table definition will be read from tables_baseline 2040 * @return ADORecordSet or false, if the query fails 2041 */ 2042 function delete($table,$where,$line,$file,$app=False,$table_def=False) 2043 { 2044 if (!$table_def) $table_def = $this->get_table_definitions($app,$table); 2045 2046 if (self::$tablealiases && isset(self::$tablealiases[$table])) 2047 { 2048 $table = self::$tablealiases[$table]; 2049 } 2050 $sql = "DELETE FROM $table WHERE ". 2051 $this->column_data_implode(' AND ',$where,True,False,$table_def['fd']); 2052 2053 return $this->query($sql,$line,$file); 2054 } 2055 2056 /** 2057 * Formats and quotes a sql expression to be used eg. as where-clause 2058 * 2059 * The function has a variable number of arguments, from which the expession gets constructed 2060 * eg. db::expression('my_table','(',array('name'=>"test'ed",'lang'=>'en'),') OR ',array('owner'=>array('',4,10))) 2061 * gives "(name='test\'ed' AND lang='en') OR 'owner' IN (0,4,5,6,10)" if name,lang are strings and owner is an integer 2062 * 2063 * @param string|array $table_def table-name or definition array 2064 * @param mixed $args variable number of arguments of the following types: 2065 * string: get's as is into the result 2066 * array: column-name / value pairs: the value gets quoted according to the type of the column and prefixed 2067 * with column-name=, multiple pairs are AND'ed together, see db::column_data_implode 2068 * bool: If False or is_null($arg): the next 2 (!) arguments gets ignored 2069 * 2070 * Please note: As the function has a variable number of arguments, you CAN NOT add further parameters !!! 2071 * 2072 * @return string the expression generated from the arguments 2073 */ 2074 function expression($table_def/*,$args, ...*/) 2075 { 2076 if (!is_array($table_def)) $table_def = $this->get_table_definitions(true,$table_def); 2077 $sql = ''; 2078 $ignore_next = 0; 2079 foreach(func_get_args() as $n => $arg) 2080 { 2081 if ($n < 1) continue; // table-name 2082 2083 if ($ignore_next) 2084 { 2085 --$ignore_next; 2086 continue; 2087 } 2088 if (is_null($arg)) $arg = False; 2089 2090 switch(gettype($arg)) 2091 { 2092 case 'string': 2093 $sql .= $arg; 2094 break; 2095 case 'boolean': 2096 $ignore_next += !$arg ? 2 : 0; 2097 break; 2098 case 'array': 2099 $sql .= $this->column_data_implode(' AND ',$arg,True,False,$table_def['fd']); 2100 break; 2101 } 2102 } 2103 return $sql; 2104 } 2105 2106 /** 2107 * Selects one or more rows in table depending on where, all data is quoted according to it's type 2108 * 2109 * @author RalfBecker<at>outdoor-training.de 2110 * 2111 * @param string $table name of the table 2112 * @param array|string $cols string or array of column-names / select-expressions 2113 * @param array|string $where string or array with column-name / values pairs AND'ed together for the where clause 2114 * @param int $line line-number to pass to query 2115 * @param string $file file-name to pass to query 2116 * @param int|bool $offset offset for a limited query or False (default) 2117 * @param string $append string to append to the end of the query, eg. ORDER BY ... 2118 * @param string|boolean $app string with name of app or False to use the current-app 2119 * @param int $num_rows number of rows to return if offset set, default 0 = use default in user prefs 2120 * @param string $join =null sql to do a join, added as is after the table-name, eg. ", table2 WHERE x=y" or 2121 * "LEFT JOIN table2 ON (x=y)", Note: there's no quoting done on $join! 2122 * @param array|bool $table_def use this table definition. If False, the table definition will be read from tables_baseline 2123 * @param int $fetchmode =self::FETCH_ASSOC self::FETCH_ASSOC (default), self::FETCH_BOTH or self::FETCH_NUM 2124 * @return ADORecordSet or false, if the query fails 2125 */ 2126 function select($table,$cols,$where,$line,$file,$offset=False,$append='',$app=False,$num_rows=0,$join='',$table_def=False,$fetchmode=self::FETCH_ASSOC) 2127 { 2128 if ($this->Debug) echo "<p>db::select('$table',".print_r($cols,True).",".print_r($where,True).",$line,$file,$offset,'$app',$num_rows,'$join')</p>\n"; 2129 2130 if (!$table_def) $table_def = $this->get_table_definitions($app,$table); 2131 if (is_array($cols)) 2132 { 2133 $cols = implode(',',$cols); 2134 } 2135 if (is_array($where)) 2136 { 2137 $where = $this->column_data_implode(' AND ',$where,True,False,$table_def['fd']); 2138 } 2139 if (self::$tablealiases && isset(self::$tablealiases[$table])) 2140 { 2141 $table = self::$tablealiases[$table]; 2142 } 2143 $sql = "SELECT $cols FROM $table $join"; 2144 2145 // if we have a where clause, we need to add it together with the WHERE statement, if thats not in the join 2146 if ($where) $sql .= (strpos($join,"WHERE")!==false) ? ' AND ('.$where.')' : ' WHERE '.$where; 2147 2148 if ($append) $sql .= ' '.$append; 2149 2150 if ($this->Debug) echo "<p>sql='$sql'</p>"; 2151 2152 if ($line === false && $file === false) // call by union, to return the sql rather then run the query 2153 { 2154 return $sql; 2155 } 2156 return $this->query($sql,$line,$file,$offset,$offset===False ? -1 : (int)$num_rows,false,$fetchmode); 2157 } 2158 2159 /** 2160 * Does a union over multiple selects 2161 * 2162 * @author RalfBecker<at>outdoor-training.de 2163 * 2164 * @param array $selects array of selects, each select is an array with the possible keys/parameters: table, cols, where, append, app, join, table_def 2165 * For further info about parameters see the definition of the select function, beside table, cols and where all other params are optional 2166 * @param int $line line-number to pass to query 2167 * @param string $file file-name to pass to query 2168 * @param string $order_by ORDER BY statement for the union 2169 * @param int|bool $offset offset for a limited query or False (default) 2170 * @param int $num_rows number of rows to return if offset set, default 0 = use default in user prefs 2171 * @param int $fetchmode =self::FETCH_ASSOC self::FETCH_ASSOC (default), self::FETCH_BOTH or self::FETCH_NUM 2172 * @return ADORecordSet or false, if the query fails 2173 */ 2174 function union($selects,$line,$file,$order_by='',$offset=false,$num_rows=0,$fetchmode=self::FETCH_ASSOC) 2175 { 2176 if ($this->Debug) echo "<p>db::union(".print_r($selects,True).",$line,$file,$order_by,$offset,$num_rows)</p>\n"; 2177 2178 $union = array(); 2179 foreach($selects as $select) 2180 { 2181 $union[] = call_user_func_array(array($this,'select'),array( 2182 $select['table'], 2183 $select['cols'], 2184 $select['where'], 2185 false, // line 2186 false, // file 2187 false, // offset 2188 $select['append'], 2189 $select['app'], 2190 0, // num_rows, 2191 $select['join'], 2192 $select['table_def'], 2193 )); 2194 } 2195 $sql = count($union) > 1 ? '(' . implode(")\nUNION\n(",$union).')' : 'SELECT DISTINCT'.substr($union[0],6); 2196 2197 if ($order_by) $sql .= (!stristr($order_by,'ORDER BY') ? "\nORDER BY " : '').$order_by; 2198 2199 if ($this->Debug) echo "<p>sql='$sql'</p>"; 2200 2201 return $this->query($sql,$line,$file,$offset,$offset===False ? -1 : (int)$num_rows,false,$fetchmode); 2202 } 2203 2204 /** 2205 * Strip eg. a prefix from the keys of an array 2206 * 2207 * @param array $arr 2208 * @param string|array $strip 2209 * @return array 2210 */ 2211 static function strip_array_keys($arr,$strip) 2212 { 2213 $keys = array_keys($arr); 2214 2215 return array_walk($keys, function(&$v, $k, $strip) 2216 { 2217 unset($k); // not used, but required by function signature 2218 $v = str_replace($strip, '', $v); 2219 }, $strip) ? 2220 array_combine($keys,$arr) : $arr; 2221 } 2222} 2223