| [ Index ] |
|
Code source de eGroupWare 1.2.106-2 |
1 <?php 2 /**************************************************************************\ 3 * eGroupWare API - database support via ADOdb * 4 * ------------------------------------------------------------------------ * 5 * This program is free software; you can redistribute it and/or modify it * 6 * under the terms of the GNU Lesser General Public License as published * 7 * by the Free Software Foundation; either version 2.1 of the License, or * 8 * any later version. * 9 \**************************************************************************/ 10 11 /* $Id: class.egw_db.inc.php 22915 2006-12-08 10:17:15Z ralfbecker $ */ 12 13 /** 14 * Database abstraction library 15 * 16 * This allows eGroupWare to use multiple database backends via ADOdb 17 * 18 * @package api 19 * @subpackage db 20 * @author Ralf Becker <RalfBecker@outdoor-training.de> 21 * @license LGPL 22 */ 23 24 // some constanst for pre php4.3 25 if (!defined('PHP_SHLIB_SUFFIX')) 26 { 27 define('PHP_SHLIB_SUFFIX',strtoupper(substr(PHP_OS, 0,3)) == 'WIN' ? 'dll' : 'so'); 28 } 29 if (!defined('PHP_SHLIB_PREFIX')) 30 { 31 define('PHP_SHLIB_PREFIX',PHP_SHLIB_SUFFIX == 'dll' ? 'php_' : ''); 32 } 33 if(empty($GLOBALS['egw_info']['server']['db_type'])) 34 { 35 $GLOBALS['egw_info']['server']['db_type'] = 'mysql'; 36 } 37 include_once (EGW_API_INC.'/adodb/adodb.inc.php'); 38 39 class egw_db 40 { 41 /** 42 * @var string $type translated database type: mysqlt+mysqli ==> mysql, same for odbc-types 43 */ 44 var $Type = ''; 45 46 /** 47 * @var string $type database type as defined in the header.inc.php, eg. mysqlt 48 */ 49 var $setupType = ''; 50 51 /** 52 * @var string $Host database host to connect to 53 */ 54 var $Host = ''; 55 56 /** 57 * @var string $Port port number of database to connect to 58 */ 59 var $Port = ''; 60 61 /** 62 * @var string $Database name of database to use 63 */ 64 var $Database = ''; 65 66 /** 67 * @var string $User name of database user 68 */ 69 var $User = ''; 70 71 /** 72 * @var string $Password password for database user 73 */ 74 var $Password = ''; 75 76 /** 77 * @var int $Auto_Free automatically free results - 0 no, 1 yes 78 */ 79 var $Auto_Free = 0; 80 81 /** 82 * @var int $Debug enable debuging - 0 no, 1 yes 83 */ 84 var $Debug = 0; 85 86 /** 87 * @var string $Halt_On_Error "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning) 88 */ 89 var $Halt_On_Error = 'yes'; 90 91 /** 92 * @var array $Record current record 93 */ 94 var $Record = array(); 95 96 /** 97 * @var int row number for current record 98 */ 99 var $Row; 100 101 /** 102 * @var int $Errno internal rdms error number for last error 103 */ 104 var $Errno = 0; 105 106 /** 107 * @var string descriptive text from last error 108 */ 109 var $Error = ''; 110 111 //i am not documenting private vars - skwashd :) 112 var $xmlrpc = False; 113 var $soap = False; 114 var $Link_ID = 0; 115 var $privat_Link_ID = False; // do we use a privat Link_ID or a reference to the global ADOdb object 116 var $Query_ID = 0; 117 118 /** 119 * @var array $capabilities, defaults will be changed be method set_capabilities($ado_driver,$db_version) 120 */ 121 var $capabilities = array( 122 'sub_queries' => true, // will be set to false for mysql < 4.1 123 'union' => true, // will be set to false for mysql < 4.0 124 'outer_join' => false, // does the DB has an outer join, will be set eg. for postgres 125 'distinct_on_text' => true, // is the DB able to use DISTINCT with a text or blob column 126 'like_on_text' => true, // is the DB able to use LIKE with text columns 127 'name_case' => 'upper', // case of returned column- and table-names: upper, lower(pgSql), preserv(MySQL) 128 'client_encoding' => false, // db uses a changeable clientencoding 129 'order_on_text' => true, // is the DB able to order by a given text column, boolean or 130 ); // string for sprintf for a cast (eg. 'CAST(%s AS varchar)') 131 132 var $prepared_sql = array(); // sql is the index 133 134 /** 135 * @param string $query query to be executed (optional) 136 */ 137 138 function db($query = '') 139 { 140 $this->query($query); 141 } 142 143 /** 144 * @return int current connection id 145 */ 146 function link_id() 147 { 148 return $this->Link_ID; 149 } 150 151 /** 152 * @return int id of current query 153 */ 154 function query_id() 155 { 156 return $this->Query_ID; 157 } 158 159 /** 160 * Open a connection to a database 161 * 162 * @param string $Database name of database to use (optional) 163 * @param string $Host database host to connect to (optional) 164 * @param string $Port database port to connect to (optional) 165 * @param string $User name of database user (optional) 166 * @param string $Password password for database user (optional) 167 */ 168 function connect($Database = NULL, $Host = NULL, $Port = NULL, $User = NULL, $Password = NULL,$Type = NULL) 169 { 170 /* Handle defaults */ 171 if (!is_null($Database) && $Database) 172 { 173 $this->Database = $Database; 174 } 175 if (!is_null($Host) && $Host) 176 { 177 $this->Host = $Host; 178 } 179 if (!is_null($Port) && $Port) 180 { 181 $this->Port = $Port; 182 } 183 if (!is_null($User) && $User) 184 { 185 $this->User = $User; 186 } 187 if (!is_null($Password) && $Password) 188 { 189 $this->Password = $Password; 190 } 191 if (!is_null($Type) && $Type) 192 { 193 $this->Type = $Type; 194 } 195 elseif (!$this->Type) 196 { 197 $this->Type = $GLOBALS['egw_info']['server']['db_type']; 198 } 199 200 if (!$this->Link_ID) 201 { 202 foreach(array('Host','Database','User','Password') as $name) 203 { 204 $$name = $this->$name; 205 } 206 $this->setupType = $php_extension = $type = $this->Type; 207 208 switch($this->Type) // convert to ADO db-type-names 209 { 210 case 'pgsql': 211 $type = 'postgres'; // name in ADOdb 212 // create our own pgsql connection-string, to allow unix domain soccets if !$Host 213 $Host = "dbname=$this->Database".($this->Host ? " host=$this->Host".($this->Port ? " port=$this->Port" : '') : ''). 214 " user=$this->User".($this->Password ? " password='".addslashes($this->Password)."'" : ''); 215 $User = $Password = $Database = ''; // to indicate $Host is a connection-string 216 break; 217 218 case 'odbc_mssql': 219 $php_extension = 'odbc'; 220 $this->Type = 'mssql'; 221 // fall through 222 case 'mssql': 223 if ($this->Port) $Host .= ','.$this->Port; 224 break; 225 226 case 'odbc_oracle': 227 $php_extension = 'odbc'; 228 $this->Type = 'oracle'; 229 break; 230 case 'oracle': 231 $php_extension = $type = 'oci8'; 232 break; 233 234 case 'sapdb': 235 $this->Type = 'maxdb'; 236 // fall through 237 case 'maxdb': 238 $type ='sapdb'; // name in ADOdb 239 $php_extension = 'odbc'; 240 break; 241 242 case 'mysqlt': 243 $php_extension = 'mysql'; // you can use $this->setupType to determine if it's mysqlt or mysql 244 // fall through 245 case 'mysqli': 246 $this->Type = 'mysql'; 247 // fall through 248 default: 249 if ($this->Port) $Host .= ':'.$this->Port; 250 break; 251 } 252 if (!is_object($GLOBALS['egw']->ADOdb) || // we have no connection so far 253 (is_object($GLOBALS['egw']->db) && // we connect to a different db, then the global one 254 ($this->Type != $GLOBALS['egw']->db->Type || 255 $this->Database != $GLOBALS['egw']->db->Database || 256 $this->User != $GLOBALS['egw']->db->User || 257 $this->Host != $GLOBALS['egw']->db->Host || 258 $this->Port != $GLOBALS['egw']->db->Port))) 259 { 260 if (!extension_loaded($php_extension) && (!function_exists('dl') || 261 !dl(PHP_SHLIB_PREFIX.$php_extension.'.'.PHP_SHLIB_SUFFIX))) 262 { 263 $this->halt("Necessary php database support for $this->Type (".PHP_SHLIB_PREFIX.$php_extension.'.'.PHP_SHLIB_SUFFIX.") not loaded and can't be loaded, exiting !!!"); 264 return 0; // in case error-reporting = 'no' 265 } 266 if (!is_object($GLOBALS['egw']->ADOdb)) // use the global object to store the connection 267 { 268 $this->Link_ID = &$GLOBALS['egw']->ADOdb; 269 } 270 else 271 { 272 $this->privat_Link_ID = True; // remember that we use a privat Link_ID for disconnect 273 } 274 $this->Link_ID = ADONewConnection($type); 275 if (!$this->Link_ID) 276 { 277 $this->halt("No ADOdb support for '$type' ($this->Type) !!!"); 278 return 0; // in case error-reporting = 'no' 279 } 280 $connect = $GLOBALS['egw_info']['server']['db_persistent'] ? 'PConnect' : 'Connect'; 281 if (($Ok = $this->Link_ID->$connect($Host, $User, $Password))) 282 { 283 $this->ServerInfo = $this->Link_ID->ServerInfo(); 284 $this->set_capabilities($type,$this->ServerInfo['version']); 285 $Ok = $this->Link_ID->SelectDB($Database); 286 } 287 if (!$Ok) 288 { 289 $this->halt("ADOdb::$connect($Host, $User, \$Password, $Database) failed."); 290 return 0; // in case error-reporting = 'no' 291 } 292 if ($this->Debug) 293 { 294 echo function_backtrace(); 295 echo "<p>new ADOdb connection to $this->Type://$this->Host/$this->Database: Link_ID".($this->Link_ID === $GLOBALS['egw']->ADOdb ? '===' : '!==')."\$GLOBALS[egw]->ADOdb</p>"; 296 //echo "<p>".print_r($this->Link_ID->ServerInfo(),true)."</p>\n"; 297 _debug_array($this); 298 echo "\$GLOBALS[egw]->db="; _debug_array($GLOBALS[egw]->db); 299 } 300 if ($this->Type == 'mssql') 301 { 302 // this is the format ADOdb expects 303 $this->Link_ID->Execute('SET DATEFORMAT ymd'); 304 // sets the limit to the maximum 305 ini_set('mssql.textlimit',2147483647); 306 ini_set('mssql.sizelimit',2147483647); 307 } 308 } 309 else 310 { 311 $this->Link_ID = &$GLOBALS['egw']->ADOdb; 312 } 313 } 314 // next ADOdb version: if (!$this->Link_ID->isConnected()) $this->Link_ID->Connect(); 315 if (!$this->Link_ID->_connectionID) $this->Link_ID->Connect(); 316 317 //echo "<p>".print_r($this->Link_ID->ServerInfo(),true)."</p>\n"; 318 return $this->Link_ID; 319 } 320 321 /** 322 * changes defaults set in class-var $capabilities depending on db-type and -version 323 * 324 * @param string $ado_driver mysql, postgres, mssql, sapdb, oci8 325 * @param string $db_version version-number of connected db-server, as reported by ServerInfo 326 */ 327 function set_capabilities($adodb_driver,$db_version) 328 { 329 switch($adodb_driver) 330 { 331 case 'mysql': 332 case 'mysqlt': 333 case 'mysqli': 334 $this->capabilities['sub_queries'] = (float) $db_version >= 4.1; 335 $this->capabilities['union'] = (float) $db_version >= 4.0; 336 $this->capabilities['name_case'] = 'preserv'; 337 $this->capabilities['client_encoding'] = (float) $db_version >= 4.1; 338 break; 339 340 case 'postgres': 341 $this->capabilities['name_case'] = 'lower'; 342 $this->capabilities['client_encoding'] = (float) $db_version >= 7.4; 343 $this->capabilities['outer_join'] = true; 344 break; 345 346 case 'mssql': 347 $this->capabilities['distinct_on_text'] = false; 348 $this->capabilities['order_on_text'] = 'CAST (%s AS varchar)'; 349 break; 350 351 case 'maxdb': // if Lim ever changes it to maxdb ;-) 352 case 'sapdb': 353 $this->capabilities['distinct_on_text'] = false; 354 $this->capabilities['like_on_text'] = (float) $db_version >= 7.6; 355 $this->capabilities['order_on_text'] = false; 356 break; 357 } 358 //echo "db::set_capabilities('$adodb_driver',$db_version)"; _debug_array($this->capabilities); 359 } 360 361 /** 362 * Close a connection to a database 363 */ 364 function disconnect() 365 { 366 if (!$this->privat_Link_ID) 367 { 368 unset($GLOBALS['egw']->ADOdb); 369 } 370 unset($this->Link_ID); 371 $this->Link_ID = 0; 372 } 373 374 /** 375 * Escape strings before sending them to the database 376 * 377 * @deprecated use quote($value,$type='') instead 378 * @param string $str the string to be escaped 379 * @return string escaped sting 380 */ 381 function db_addslashes($str) 382 { 383 if (!isset($str) || $str == '') 384 { 385 return ''; 386 } 387 if (!$this->Link_ID && !$this->connect()) 388 { 389 return False; 390 } 391 return $this->Link_ID->addq($str); 392 } 393 394 /** 395 * Convert a unix timestamp to a rdms specific timestamp 396 * 397 * @param int unix timestamp 398 * @return string rdms specific timestamp 399 */ 400 function to_timestamp($epoch) 401 { 402 if (!$this->Link_ID && !$this->connect()) 403 { 404 return False; 405 } 406 // the substring is needed as the string is already in quotes 407 return substr($this->Link_ID->DBTimeStamp($epoch),1,-1); 408 } 409 410 /** 411 * Convert a rdms specific timestamp to a unix timestamp 412 * 413 * @param string rdms specific timestamp 414 * @return int unix timestamp 415 */ 416 function from_timestamp($timestamp) 417 { 418 if (!$this->Link_ID && !$this->connect()) 419 { 420 return False; 421 } 422 return $this->Link_ID->UnixTimeStamp($timestamp); 423 } 424 425 /** 426 * convert a rdbms specific boolean value 427 * 428 * @param string $val boolean value in db-specfic notation 429 * @return boolean 430 */ 431 function from_bool($val) 432 { 433 return $val && $val{0} !== 'f'; // everthing other then 0 or f[alse] is returned as true 434 } 435 436 /** 437 * Discard the current query result 438 */ 439 function free() 440 { 441 unset($this->Query_ID); // else copying of the db-object does not work 442 $this->Query_ID = 0; 443 } 444 445 /** 446 * Execute a query 447 * 448 * @param string $Query_String the query to be executed 449 * @param int $line the line method was called from - use __LINE__ 450 * @param string $file the file method was called from - use __FILE__ 451 * @param int $offset row to start from, default 0 452 * @param int $num_rows number of rows to return (optional), default -1 = all, 0 will use $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs'] 453 * @param array/boolean $inputarr array for binding variables to parameters or false (default) 454 * @return ADORecordSet or false, if the query fails 455 */ 456 function query($Query_String, $line = '', $file = '', $offset=0, $num_rows=-1,$inputarr=false) 457 { 458 if ($Query_String == '') 459 { 460 return 0; 461 } 462 if (!$this->Link_ID && !$this->connect()) 463 { 464 return False; 465 } 466 467 # New query, discard previous result. 468 if ($this->Query_ID) 469 { 470 $this->free(); 471 } 472 if ($this->Link_ID->fetchMode != ADODB_FETCH_BOTH) 473 { 474 $this->Link_ID->SetFetchMode(ADODB_FETCH_BOTH); 475 } 476 if (!$num_rows) 477 { 478 $num_rows = $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs']; 479 } 480 if ($num_rows > 0) 481 { 482 $this->Query_ID = $this->Link_ID->SelectLimit($Query_String,$num_rows,(int)$offset,$inputarr); 483 } 484 else 485 { 486 $this->Query_ID = $this->Link_ID->Execute($Query_String,$inputarr); 487 } 488 $this->Row = 0; 489 $this->Errno = $this->Link_ID->ErrorNo(); 490 $this->Error = $this->Link_ID->ErrorMsg(); 491 492 if (! $this->Query_ID) 493 { 494 $this->halt("Invalid SQL: ".(is_array($Query_String)?$Query_String[0]:$Query_String). 495 ($inputarr ? "<br>Parameters: '".implode("','",$inputarr)."'":''), 496 $line, $file); 497 } 498 return $this->Query_ID; 499 } 500 501 /** 502 * Execute a query with limited result set 503 * 504 * @param string $Query_String the query to be executed 505 * @param int $offset row to start from, default 0 506 * @param int $line the line method was called from - use __LINE__ 507 * @param string $file the file method was called from - use __FILE__ 508 * @param int $num_rows number of rows to return (optional), default -1 = all, 0 will use $GLOBALS['egw_info']['user']['preferences']['common']['maxmatchs'] 509 * @param array/boolean $inputarr array for binding variables to parameters or false (default) 510 * @return ADORecordSet or false, if the query fails 511 */ 512 function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = '',$inputarr=false) 513 { 514 return $this->query($Query_String,$line,$file,$offset,$num_rows,$inputarr); 515 } 516 517 /** 518 * Move to the next row in the results set 519 * 520 * Specifying a fetch_mode only works for newly fetched rows, the first row always gets fetched by query!!! 521 * 522 * @param int $fetch_mode ADODB_FETCH_BOTH = numerical+assoc keys (eGW default), ADODB_FETCH_ASSOC or ADODB_FETCH_NUM 523 * @return bool was another row found? 524 */ 525 function next_record($fetch_mode=ADODB_FETCH_BOTH) 526 { 527 if (!$this->Query_ID) 528 { 529 $this->halt('next_record called with no query pending.'); 530 return 0; 531 } 532 if ($this->Link_ID->fetchMode != $fetch_mode) 533 { 534 $this->Link_ID->SetFetchMode($fetch_mode); 535 } 536 if ($this->Row) // first row is already fetched 537 { 538 $this->Query_ID->MoveNext(); 539 } 540 ++$this->Row; 541 542 $this->Record = $this->Query_ID->fields; 543 544 if ($this->Query_ID->EOF || !$this->Query_ID->RecordCount() || !is_array($this->Record)) 545 { 546 return False; 547 } 548 if ($this->capabilities['name_case'] == 'upper') // maxdb, oracle, ... 549 { 550 switch($fetch_mode) 551 { 552 case ADODB_FETCH_ASSOC: 553 $this->Record = array_change_key_case($this->Record); 554 break; 555 case ADODB_FETCH_NUM: 556 $this->Record = array_values($this->Record); 557 break; 558 default: 559 $this->Record = array_change_key_case($this->Record); 560 if (!isset($this->Record[0])) 561 { 562 $this->Record += array_values($this->Record); 563 } 564 break; 565 } 566 } 567 return True; 568 } 569 570 /** 571 * Move to position in result set 572 * 573 * @param int $pos required row (optional), default first row 574 * @return boolean true if sucessful or false if not found 575 */ 576 function seek($pos = 0) 577 { 578 if (!$this->Query_ID || !$this->Query_ID->Move($this->Row = $pos)) 579 { 580 $this->halt("seek($pos) failed: resultset has " . $this->num_rows() . " rows"); 581 $this->Query_ID->Move( $this->num_rows() ); 582 $this->Row = $this->num_rows(); 583 return False; 584 } 585 return True; 586 } 587 588 /** 589 * Begin Transaction 590 * 591 * @return int/boolean current transaction-id, of false if no connection 592 */ 593 function transaction_begin() 594 { 595 if (!$this->Link_ID && !$this->connect()) 596 { 597 return False; 598 } 599 //return $this->Link_ID->BeginTrans(); 600 return $this->Link_ID->StartTrans(); 601 } 602 603 /** 604 * Complete the transaction 605 * 606 * @return bool True if sucessful, False if fails 607 */ 608 function transaction_commit() 609 { 610 if (!$this->Link_ID && !$this->connect()) 611 { 612 return False; 613 } 614 //return $this->Link_ID->CommitTrans(); 615 return $this->Link_ID->CompleteTrans(); 616 } 617 618 /** 619 * Rollback the current transaction 620 * 621 * @return bool True if sucessful, False if fails 622 */ 623 function transaction_abort() 624 { 625 if (!$this->Link_ID && !$this->connect()) 626 { 627 return False; 628 } 629 //return $this->Link_ID->RollbackTrans(); 630 return $this->Link_ID->FailTrans(); 631 } 632 633 /** 634 * Find the primary key of the last insertion on the current db connection 635 * 636 * @param string $table name of table the insert was performed on 637 * @param string $field the autoincrement primary key of the table 638 * @return int the id, -1 if fails 639 */ 640 function get_last_insert_id($table, $field) 641 { 642 if (!$this->Link_ID && !$this->connect()) 643 { 644 return False; 645 } 646 $id = $this->Link_ID->PO_Insert_ID($table,$field); // simulates Insert_ID with "SELECT MAX($field) FROM $table" if not native availible 647 648 if ($id === False) // function not supported 649 { 650 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"; 651 function_backtrace(); 652 return -1; 653 } 654 return $id; 655 } 656 657 /** 658 * Lock a table 659 * 660 * @deprecated not used anymore as it costs to much performance, use transactions if needed 661 * @param string $table name of table to lock 662 * @param string $mode type of lock required (optional), default write 663 * @return bool True if sucessful, False if fails 664 */ 665 function lock($table, $mode='write') 666 {} 667 668 /** 669 * Unlock a table 670 * 671 * @deprecated not used anymore as it costs to much performance, use transactions if needed 672 * @return bool True if sucessful, False if fails 673 */ 674 function unlock() 675 {} 676 677 /** 678 * Get the number of rows affected by last update or delete 679 * 680 * @return int number of rows 681 */ 682 function affected_rows() 683 { 684 if (!$this->Link_ID && !$this->connect()) 685 { 686 return False; 687 } 688 return $this->Link_ID->Affected_Rows(); 689 } 690 691 /** 692 * Number of rows in current result set 693 * 694 * @return int number of rows 695 */ 696 function num_rows() 697 { 698 return $this->Query_ID ? $this->Query_ID->RecordCount() : False; 699 } 700 701 /** 702 * Number of fields in current row 703 * 704 * @return int number of fields 705 */ 706 function num_fields() 707 { 708 return $this->Query_ID ? $this->Query_ID->FieldCount() : False; 709 } 710 711 /** 712 * @deprecated use num_rows() 713 */ 714 function nf() 715 { 716 return $this->num_rows(); 717 } 718 719 /** 720 * @deprecated use print num_rows() 721 */ 722 function np() 723 { 724 print $this->num_rows(); 725 } 726 727 /** 728 * Return the value of a column 729 * 730 * @param string/integer $Name name of field or positional index starting from 0 731 * @param bool $strip_slashes string escape chars from field(optional), default false 732 * depricated param, as correctly quoted values dont need any stripslashes! 733 * @return string the field value 734 */ 735 function f($Name, $strip_slashes = False) 736 { 737 if ($strip_slashes) 738 { 739 return stripslashes($this->Record[$Name]); 740 } 741 return $this->Record[$Name]; 742 } 743 744 /** 745 * Print the value of a field 746 * 747 * @param string $Name name of field to print 748 * @param bool $strip_slashes string escape chars from field(optional), default false 749 * depricated param, as correctly quoted values dont need any stripslashes! 750 */ 751 function p($Name, $strip_slashes = True) 752 { 753 print $this->f($Name, $strip_slashes); 754 } 755 756 /** 757 * Returns a query-result-row as an associative array (no numerical keys !!!) 758 * 759 * @param bool $do_next_record should next_record() be called or not (default not) 760 * @param string $strip='' string to strip of the column-name, default '' 761 * @return array/bool the associative array or False if no (more) result-row is availible 762 */ 763 function row($do_next_record=False,$strip='') 764 { 765 if ($do_next_record && !$this->next_record(ADODB_FETCH_ASSOC) || !is_array($this->Record)) 766 { 767 return False; 768 } 769 $result = array(); 770 foreach($this->Record as $column => $value) 771 { 772 if (!is_numeric($column)) 773 { 774 if ($strip) $column = str_replace($strip,'',$column); 775 776 $result[$column] = $value; 777 } 778 } 779 return $result; 780 } 781 782 /** 783 * Error handler 784 * 785 * @param string $msg error message 786 * @param int $line line of calling method/function (optional) 787 * @param string $file file of calling method/function (optional) 788 */ 789 function halt($msg, $line = '', $file = '') 790 { 791 if ($this->Link_ID) // only if we have a link, else infinite loop 792 { 793 $this->Error = $this->Link_ID->ErrorMsg(); // need to be BEFORE unlock, 794 $this->Errno = $this->Link_ID->ErrorNo(); // else we get its error or none 795 796 $this->unlock(); /* Just in case there is a table currently locked */ 797 } 798 if ($this->Halt_On_Error == "no") 799 { 800 return; 801 } 802 $this->haltmsg($msg); 803 804 if ($file) 805 { 806 printf("<br /><b>File:</b> %s",$file); 807 } 808 if ($line) 809 { 810 printf("<br /><b>Line:</b> %s",$line); 811 } 812 printf("<br /><b>Function:</b> %s</p>\n",function_backtrace(2)); 813 814 if ($this->Halt_On_Error != "report") 815 { 816 echo "<p><b>Session halted.</b></p>"; 817 if (is_object($GLOBALS['egw']->common)) 818 { 819 $GLOBALS['egw']->common->egw_exit(True); 820 } 821 else // happens eg. in setup 822 { 823 exit(); 824 } 825 } 826 } 827 828 function haltmsg($msg) 829 { 830 printf("<p><b>Database error:</b> %s<br>\n", $msg); 831 if (($this->Errno || $this->Error) && $this->Error != "()") 832 { 833 printf("<b>$this->Type Error</b>: %s (%s)<br>\n",$this->Errno,$this->Error); 834 } 835 } 836 837 /** 838 * Get description of a table 839 * 840 * Beside the column-name all other data depends on the db-type !!! 841 * 842 * @param string $table name of table to describe 843 * @param bool $full optional, default False summary information, True full information 844 * @return array table meta data 845 */ 846 function metadata($table='',$full=false) 847 { 848 if (!$this->Link_ID && !$this->connect()) 849 { 850 return False; 851 } 852 $columns = $this->Link_ID->MetaColumns($table); 853 //$columns = $this->Link_ID->MetaColumnsSQL($table); 854 //echo "<b>metadata</b>('$table')=<pre>\n".print_r($columns,True)."</pre>\n"; 855 856 $metadata = array(); 857 $i = 0; 858 foreach($columns as $column) 859 { 860 // for backwards compatibilty (depreciated) 861 unset($flags); 862 if($column->auto_increment) $flags .= "auto_increment "; 863 if($column->primary_key) $flags .= "primary_key "; 864 if($column->binary) $flags .= "binary "; 865 866 // _debug_array($column); 867 $metadata[$i] = array( 868 'table' => $table, 869 'name' => $column->name, 870 'type' => $column->type, 871 'len' => $column->max_length, 872 'flags' => $flags, // for backwards compatibilty (depreciated) used by JiNN atm 873 'not_null' => $column->not_null, 874 'auto_increment' => $column->auto_increment, 875 'primary_key' => $column->primary_key, 876 'binary' => $column->binary, 877 'has_default' => $column->has_default, 878 'default' => $column->default_value, 879 ); 880 $metadata[$i]['table'] = $table; 881 if ($full) 882 { 883 $metadata['meta'][$column->name] = $i; 884 } 885 ++$i; 886 } 887 if ($full) 888 { 889 $metadata['num_fields'] = $i; 890 } 891 return $metadata; 892 } 893 894 /** 895 * Get a list of table names in the current database 896 * 897 * @return array list of the tables 898 */ 899 function table_names() 900 { 901 if (!$this->Link_ID) $this->connect(); 902 if (!$this->Link_ID) 903 { 904 return False; 905 } 906 $result = array(); 907 $tables = $this->Link_ID->MetaTables('TABLES'); 908 if (is_array($tables)) 909 { 910 foreach($tables as $table) 911 { 912 if ($this->capabilities['name_case'] == 'upper') 913 { 914 $table = strtolower($table); 915 } 916 $result[] = array( 917 'table_name' => $table, 918 'tablespace_name' => $this->Database, 919 'database' => $this->Database 920 ); 921 } 922 } 923 return $result; 924 } 925 926 /** 927 * Return a list of indexes in current database 928 * 929 * @return array list of indexes 930 */ 931 function index_names() 932 { 933 $indices = array(); 934 if ($this->Type != 'pgsql') 935 { 936 echo "<p>db::index_names() not yet implemented for db-type '$this->Type'</p>\n"; 937 return $indices; 938 } 939 $this->query("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relkind ='i' ORDER BY relname"); 940 while ($this->next_record()) 941 { 942 $indices[] = array( 943 'index_name' => $this->f(0), 944 'tablespace_name' => $this->Database, 945 'database' => $this->Database, 946 ); 947 } 948 return $indices; 949 } 950 951 /** 952 * Returns an array containing column names that are the primary keys of $tablename. 953 * 954 * @return array of columns 955 */ 956 function pkey_columns($tablename) 957 { 958 if (!$this->Link_ID && !$this->connect()) 959 { 960 return False; 961 } 962 return $this->Link_ID->MetaPrimaryKeys($tablename); 963 } 964 965 /** 966 * Create a new database 967 * 968 * @param string $adminname name of database administrator user (optional) 969 * @param string $adminpasswd password for the database administrator user (optional) 970 * @param string $charset default charset for the database 971 */ 972 function create_database($adminname = '', $adminpasswd = '', $charset='') 973 { 974 $currentUser = $this->User; 975 $currentPassword = $this->Password; 976 $currentDatabase = $this->Database; 977 978 $extra = array(); 979 $set_charset = ''; 980 switch ($this->Type) 981 { 982 case 'pgsql': 983 $meta_db = 'template1'; 984 break; 985 case 'mysql': 986 if ($charset && isset($this->Link_ID->charset2mysql[$charset]) && (float) $this->ServerInfo['version'] >= 4.1) 987 { 988 $set_charset = ' DEFAULT CHARACTER SET '.$this->Link_ID->charset2mysql[$charset].';'; 989 } 990 $meta_db = 'mysql'; 991 $extra[] = "GRANT ALL ON $currentDatabase.* TO $currentUser@localhost IDENTIFIED BY '$currentPassword'"; 992 break; 993 default: 994 echo "<p>db::create_database(user='$adminname',\$pw) not yet implemented for DB-type '$this->Type'</p>\n"; 995 break; 996 } 997 if ($adminname != '') 998 { 999 $this->User = $adminname; 1000 $this->Password = $adminpasswd; 1001 $this->Database = $meta_db; 1002 } 1003 $this->disconnect(); 1004 $this->query('CREATE DATABASE '.$currentDatabase.$set_charset); 1005 foreach($extra as $sql) 1006 { 1007 $this->query($sql); 1008 } 1009 $this->disconnect(); 1010 1011 $this->User = $currentUser; 1012 $this->Password = $currentPassword; 1013 $this->Database = $currentDatabase; 1014 $this->connect(); 1015 } 1016 1017 /** 1018 * concat a variable number of strings together, to be used in a query 1019 * 1020 * Example: $db->concat($db->quote('Hallo '),'username') would return 1021 * for mysql "concat('Hallo ',username)" or "'Hallo ' || username" for postgres 1022 * @param string $str1 already quoted stringliteral or column-name, variable number of arguments 1023 * @return string to be used in a query 1024 */ 1025 function concat($str1) 1026 { 1027 $args = func_get_args(); 1028 1029 if (!$this->Link_ID && !$this->connect()) 1030 { 1031 return False; 1032 } 1033 return call_user_func_array(array(&$this->Link_ID,'concat'),$args); 1034 } 1035 1036 /** 1037 * Convert a unix timestamp stored as integer in the db into a db timestamp, like MySQL: FROM_UNIXTIME(ts) 1038 * 1039 * @param string $expr name of an integer column or integer expression 1040 * @return string SQL expression of type timestamp 1041 */ 1042 function from_unixtime($expr) 1043 { 1044 switch($this->Type) 1045 { 1046 case 'mysql': 1047 return "FROM_UNIXTIME($expr)"; 1048 1049 case 'pgsql': // we use date(,0) as we store server-time 1050 return "(timestamp '".date('Y-m-d H:i:s',0)."' + ($expr) * interval '1 sec')"; 1051 1052 case 'mssql': // we use date(,0) as we store server-time 1053 return "DATEADD(second,($expr),'".date('Y-m-d H:i:s',0)."')"; 1054 } 1055 return false; 1056 } 1057 1058 /** 1059 * format a timestamp as string, like MySQL: DATE_FORMAT(ts) 1060 * 1061 * Please note: only a subset of the MySQL formats are implemented 1062 * 1063 * @param string $expr name of a timestamp column or timestamp expression 1064 * @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 1065 * @return string SQL expression of type timestamp 1066 */ 1067 function date_format($expr,$format) 1068 { 1069 switch($this->Type) 1070 { 1071 case 'mysql': 1072 return "DATE_FORMAT($expr,'$format')"; 1073 1074 case 'pgsql': 1075 $format = str_replace( 1076 array('%Y', '%y','%m','%d','%H', '%h','%i','%s','%V','%v','%X', '%x'), 1077 array('YYYY','YY','MM','DD','HH24','HH','MI','SS','IW','IW','YYYY','YYYY'), 1078 $format); 1079 return "TO_CHAR($expr,'$format')"; 1080 1081 case 'mssql': 1082 $from = $to = array(); 1083 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) 1084 { 1085 $from[] = $f; 1086 $to[] = "'+DATEPART($t,($expr))+'"; 1087 } 1088 $from[] = "''+"; $to[] = ''; 1089 $from[] = "+''"; $to[] = ''; 1090 return str_replace($from,$to,$format); 1091 } 1092 return false; 1093 } 1094 1095 /** 1096 * Correctly Quote Identifiers like table- or colmnnames for use in SQL-statements 1097 * 1098 * This is mostly copy & paste from adodb's datadict class 1099 * @param $name string 1100 * @return string quoted string 1101 */ 1102 function name_quote($name = NULL) 1103 { 1104 if (!is_string($name)) { 1105 return FALSE; 1106 } 1107 1108 $name = trim($name); 1109 1110 if (!$this->Link_ID && !$this->connect()) 1111 { 1112 return False; 1113 } 1114 1115 $quote = $this->Link_ID->nameQuote; 1116 1117 // if name is of the form `name`, quote it 1118 if ( preg_match('/^`(.+)`$/', $name, $matches) ) { 1119 return $quote . $matches[1] . $quote; 1120 } 1121 1122 // if name contains special characters, quote it 1123 if ( preg_match('/\W/', $name) ) { 1124 return $quote . $name . $quote; 1125 } 1126 1127 return $name; 1128 } 1129 1130 /** 1131 * Escape values before sending them to the database - prevents SQL injunction and SQL errors ;-) 1132 * 1133 * Please note that the quote function already returns necessary quotes: quote('Hello') === "'Hello'". 1134 * Int and Auto types are casted to int: quote('1','int') === 1, quote('','int') === 0, quote('Hello','int') === 0 1135 * 1136 * @param mixed $value the value to be escaped 1137 * @param string/boolean $type string the type of the db-column, default False === varchar 1138 * @param boolean $not_null is column NOT NULL, default true, else php null values are written as SQL NULL 1139 * @return string escaped sting 1140 */ 1141 function quote($value,$type=False,$not_null=true) 1142 { 1143 if ($this->Debug) echo "<p>db::quote(".(is_null($value)?'NULL':"'$value'").",'$type','$not_null')</p>\n"; 1144 1145 if (!$not_null && is_null($value)) // writing unset php-variables and those set to NULL now as SQL NULL 1146 { 1147 return 'NULL'; 1148 } 1149 switch($type) 1150 { 1151 case 'int': 1152 case 'auto': 1153 return (int) $value; 1154 case 'bool': 1155 if ($this->Type == 'mysql') // maybe it's not longer necessary with mysql5 1156 { 1157 return $value ? 1 : 0; 1158 } 1159 return $value ? 'true' : 'false'; 1160 case 'float': 1161 case 'decimal': 1162 return (double) $value; 1163 } 1164 if (!$this->Link_ID && !$this->connect()) 1165 { 1166 return False; 1167 } 1168 switch($type) 1169 { 1170 case 'blob': 1171 switch ($this->Link_ID->blobEncodeType) 1172 { 1173 case 'C': // eg. postgres 1174 return "'" . $this->Link_ID->BlobEncode($value) . "'"; 1175 case 'I': 1176 return $this->Link_ID->BlobEncode($value); 1177 } 1178 break; // handled like strings 1179 case 'date': 1180 return $this->Link_ID->DBDate($value); 1181 case 'timestamp': 1182 return $this->Link_ID->DBTimeStamp($value); 1183 } 1184 return $this->Link_ID->qstr($value); 1185 } 1186 1187 /** 1188 * Implodes an array of column-value pairs for the use in sql-querys. 1189 * All data is run through quote (does either addslashes() or (int)) - prevents SQL injunction and SQL errors ;-). 1190 * 1191 * @author RalfBecker<at>outdoor-training.de 1192 * 1193 * @param string $glue in most cases this will be either ',' or ' AND ', depending you your query 1194 * @param array $array column-name / value pairs, if the value is an array all its array-values will be quoted 1195 * according to the type of the column, and the whole array with be formatted like (val1,val2,...) 1196 * If $use_key == True, an ' IN ' instead a '=' is used. Good for category- or user-lists. 1197 * If the key is numerical (no key given in the array-definition) the value is used as is, eg. 1198 * array('visits=visits+1') gives just "visits=visits+1" (no quoting at all !!!) 1199 * @param boolean/string $use_key If $use_key===True a "$key=" prefix each value (default), typically set to False 1200 * or 'VALUES' for insert querys, on 'VALUES' "(key1,key2,...) VALUES (val1,val2,...)" is returned 1201 * @param array/boolean $only if set to an array only colums which are set (as data !!!) are written 1202 * typicaly used to form a WHERE-clause from the primary keys. 1203 * If set to True, only columns from the colum_definitons are written. 1204 * @param array/boolean $column_definitions this can be set to the column-definitions-array 1205 * of your table ($tables_baseline[$table]['fd'] of the setup/tables_current.inc.php file). 1206 * If its set, the column-type-data determinates if (int) or addslashes is used. 1207 * @return string SQL 1208 */ 1209 function column_data_implode($glue,$array,$use_key=True,$only=False,$column_definitions=False) 1210 { 1211 if (!is_array($array)) // this allows to give an SQL-string for delete or update 1212 { 1213 return $array; 1214 } 1215 if (!$column_definitions) 1216 { 1217 $column_definitions = $this->column_definitions; 1218 } 1219 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"; 1220 1221 $keys = $values = array(); 1222 foreach($array as $key => $data) 1223 { 1224 if (is_int($key) || !$only || $only === True && isset($column_definitions[$key]) || 1225 is_array($only) && in_array($key,$only)) 1226 { 1227 $keys[] = $this->name_quote($key); 1228 1229 if (!is_int($key) && is_array($column_definitions) && !isset($column_definitions[$key])) 1230 { 1231 // give a warning that we have no column-type 1232 $this->halt("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>"); 1233 } 1234 $column_type = is_array($column_definitions) ? @$column_definitions[$key]['type'] : False; 1235 $not_null = is_array($column_definitions) && isset($column_definitions[$key]['nullable']) ? !$column_definitions[$key]['nullable'] : false; 1236 1237 if (is_array($data)) 1238 { 1239 $or_null = ''; 1240 foreach($data as $k => $v) 1241 { 1242 if (!$not_null && $use_key===True && is_null($v)) 1243 { 1244 $or_null = $this->name_quote($key).' IS NULL)'; 1245 unset($data[$k]); 1246 continue; 1247 } 1248 $data[$k] = $this->quote($v,$column_type,$not_null); 1249 } 1250 $values[] = ($or_null?'(':'').(!count($data) ? '' : 1251 ($use_key===True ? $this->name_quote($key).' IN ' : '') . 1252 '('.implode(',',$data).')'.($or_null ? ' OR ' : '')).$or_null; 1253 } 1254 elseif (is_int($key) && $use_key===True) 1255 { 1256 $values[] = $data; 1257 } 1258 elseif ($glue != ',' && $use_key === True && !$not_null && is_null($data)) 1259 { 1260 $values[] = $this->name_quote($key) .' IS NULL'; 1261 } 1262 else 1263 { 1264 $values[] = ($use_key===True ? $this->name_quote($key) . '=' : '') . $this->quote($data,$column_type,$not_null); 1265 } 1266 } 1267 } 1268 return ($use_key==='VALUES' ? '('.implode(',',$keys).') VALUES (' : ''). 1269 implode($glue,$values) . ($use_key==='VALUES' ? ')' : ''); 1270 } 1271 1272 /** 1273 * Sets the default column-definitions for use with column_data_implode() 1274 * 1275 * @author RalfBecker<at>outdoor-training.de 1276 * 1277 * @param array/boolean $column_definitions this can be set to the column-definitions-array 1278 * of your table ($tables_baseline[$table]['fd'] of the setup/tables_current.inc.php file). 1279 * If its set, the column-type-data determinates if (int) or addslashes is used. 1280 */ 1281 function set_column_definitions($column_definitions=False) 1282 { 1283 $this->column_definitions=$column_definitions; 1284 } 1285 1286 /** 1287 * Sets the application in which the db-class looks for table-defintions 1288 * 1289 * Used by table_definitions, insert, update, select, expression and delete. If the app is not set via set_app, 1290 * it need to be set for these functions on every call 1291 * 1292 * @param string $app the app-name 1293 */ 1294 function set_app($app) 1295 { 1296 $this->app = $app; 1297 } 1298 1299 /** 1300 * reads the table-definitions from the app's setup/tables_current.inc.php file 1301 * 1302 * The already read table-definitions are shared between all db-instances via $GLOBALS['egw_info']['apps'][$app]['table_defs'] 1303 * 1304 * @author RalfBecker<at>outdoor-training.de 1305 * 1306 * @param bool/string $app name of the app or default False to use the app set by db::set_app or the current app, 1307 * true to search the already loaded table-definitions for $table 1308 * @param bool/string $table if set return only defintions of that table, else return all defintions 1309 * @return mixed array with table-defintions or False if file not found 1310 */ 1311 function get_table_definitions($app=False,$table=False) 1312 { 1313 if ($app === true && $table && isset($GLOBALS['egw_info']['apps'])) 1314 { 1315 foreach($GLOBALS['egw_info']['apps'] as $app => $app_data) 1316 { 1317 if (isset($data['table_defs'][$table])) 1318 { 1319 return $data['table_defs'][$table]; 1320 } 1321 } 1322 $app = false; 1323 } 1324 if (!$app) 1325 { 1326 $app = $this->app ? $this->app : $GLOBALS['egw_info']['flags']['currentapp']; 1327 } 1328 if (isset($GLOBALS['egw_info']['apps'])) // dont set it, if it does not exist!!! 1329 { 1330 $this->app_data = &$GLOBALS['egw_info']['apps'][$app]; 1331 } 1332 // this happens during the eGW startup or in setup 1333 else 1334 { 1335 $this->app_data =& $this->all_app_data[$app]; 1336 } 1337 if (!isset($this->app_data['table_defs'])) 1338 { 1339 $tables_current = EGW_INCLUDE_ROOT . "/$app/setup/tables_current.inc.php"; 1340 if (!@file_exists($tables_current)) 1341 { 1342 return $this->app_data['table_defs'] = False; 1343 } 1344 include($tables_current); 1345 $this->app_data['table_defs'] =& $phpgw_baseline; 1346 unset($phpgw_baseline); 1347 } 1348 if ($table && (!$this->app_data['table_defs'] || !isset($this->app_data['table_defs'][$table]))) 1349 { 1350 return False; 1351 } 1352 return $table ? $this->app_data['table_defs'][$table] : $this->app_data['table_defs']; 1353 } 1354 1355 /** 1356 * Insert a row of data into a table or updates it if $where is given, all data is quoted according to it's type 1357 * 1358 * @author RalfBecker<at>outdoor-training.de 1359 * 1360 * @param string $table name of the table 1361 * @param array $data with column-name / value pairs 1362 * @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 1363 * if the row exists db::update is called else a new row with $date merged with $where gets inserted (data has precedence) 1364 * @param int $line line-number to pass to query 1365 * @param string $file file-name to pass to query 1366 * @param string/boolean $app string with name of app or False to use the current-app 1367 * @param bool $use_prepared_statement use a prepared statement 1368 * @param array/bool $table_def use this table definition. If False, the table definition will be read from tables_baseline 1369 * @return ADORecordSet or false, if the query fails 1370 */ 1371 function insert($table,$data,$where,$line,$file,$app=False,$use_prepared_statement=false,$table_def=False) 1372 { 1373 if ($this->Debug) echo "<p>db::insert('$table',".print_r($data,True).",".print_r($where,True).",$line,$file,'$app')</p>\n"; 1374 1375 if (!$table_def) $table_def = $this->get_table_definitions($app,$table); 1376 1377 $sql_append = ''; 1378 $cmd = 'INSERT'; 1379 if (is_array($where) && count($where)) 1380 { 1381 switch($this->Type) 1382 { 1383 case 'sapdb': case 'maxdb': 1384 $sql_append = ' UPDATE DUPLICATES'; 1385 break; 1386 case 'mysql': 1387 // use replace if primary keys are included 1388 if (count(array_intersect(array_keys($where),(array)$table_def['pk'])) == count($table_def['pk'])) 1389 { 1390 $cmd = 'REPLACE'; 1391 break; 1392 } 1393 // fall through !!! 1394 default: 1395 $this->select($table,'count(*)',$where,$line,$file); 1396 if ($this->next_record() && $this->f(0)) 1397 { 1398 return !!$this->update($table,$data,$where,$line,$file,$app); 1399 } 1400 break; 1401 } 1402 // the checked values need to be inserted too, value in data has precedence, also cant insert sql strings (numerical id) 1403 foreach($where as $column => $value) 1404 { 1405 if (!is_numeric($column) && !isset($data[$column])) 1406 { 1407 $data[$column] = $value; 1408 } 1409 } 1410 } 1411 $inputarr = false; 1412 if ($use_prepared_statement && $this->Link_ID->_bindInputArray) // eg. MaxDB 1413 { 1414 $this->Link_ID->Param(false); // reset param-counter 1415 $cols = array_keys($data); 1416 foreach($cols as $k => $col) 1417 { 1418 if (!isset($table_def['fd'][$col])) // ignore columns not in this table 1419 { 1420 unset($cols[$k]); 1421 continue; 1422 } 1423 $params[] = $this->Link_ID->Param($col); 1424 } 1425 $sql = "$cmd INTO $table (".implode(',',$cols).') VALUES ('.implode(',',$params).')'.$sql_append; 1426 // check if we already prepared that statement 1427 if (!isset($this->prepared_sql[$sql])) 1428 { 1429 $this->prepared_sql[$sql] = $this->Link_ID->Prepare($sql); 1430 } 1431 $sql = $this->prepared_sql[$sql]; 1432 $inputarr = &$data; 1433 } 1434 else 1435 { 1436 $sql = "$cmd INTO $table ".$this->column_data_implode(',',$data,'VALUES',true,$table_def['fd']).$sql_append; 1437 } 1438 if ($this->Debug) echo "<p>db::insert('$table',".print_r($data,True).",".print_r($where,True).",$line,$file,'$app') sql='$sql'</p>\n"; 1439 return $this->query($sql,$line,$file,0,-1,$inputarr); 1440 } 1441 1442 /** 1443 * Updates the data of one or more rows in a table, all data is quoted according to it's type 1444 * 1445 * @author RalfBecker<at>outdoor-training.de 1446 * 1447 * @param string $table name of the table 1448 * @param array $data with column-name / value pairs 1449 * @param array $where column-name / values pairs and'ed together for the where clause 1450 * @param int $line line-number to pass to query 1451 * @param string $file file-name to pass to query 1452 * @param string/boolean $app string with name of app or False to use the current-app 1453 * @param bool $use_prepared_statement use a prepared statement 1454 * @param array/bool $table_def use this table definition. If False, the table definition will be read from tables_baseline 1455 * @return ADORecordSet or false, if the query fails 1456 */ 1457 function update($table,$data,$where,$line,$file,$app=False,$use_prepared_statement=false,$table_def=False) 1458 { 1459 if ($this->Debug) echo "<p>db::update('$table',".print_r($data,true).','.print_r($where,true).",$line,$file,'$app')</p>\n"; 1460 if (!$table_def) $table_def = $this->get_table_definitions($app,$table); 1461 1462 $blobs2update = array(); 1463 // SapDB/MaxDB cant update LONG columns / blob's: if a blob-column is included in the update we remember it in $blobs2update 1464 // and remove it from $data 1465 switch ($this->Type) 1466 { 1467 case 'sapdb': 1468 case 'maxdb': 1469 if ($use_prepared_statement) break; 1470 // check if data contains any LONG columns 1471 foreach($data as $col => $val) 1472 { 1473 switch ($table_def['fd'][$col]['type']) 1474 { 1475 case 'text': 1476 case 'longtext': 1477 case 'blob': 1478 $blobs2update[$col] = &$data[$col]; 1479 unset($data[$col]); 1480 break; 1481 } 1482 } 1483 break; 1484 } 1485 $where = $this->column_data_implode(' AND ',$where,True,true,$table_def['fd']); 1486 1487 if (count($data)) 1488 { 1489 $inputarr = false; 1490 if ($use_prepared_statement && $this->Link_ID->_bindInputArray) // eg. MaxDB 1491 { 1492 $this->Link_ID->Param(false); // reset param-counter 1493 foreach($data as $col => $val) 1494 { 1495 if (!isset($table_def['fd'][$col])) continue; // ignore columns not in this table 1496 $params[] = $this->name_quote($col).'='.$this->Link_ID->Param($col); 1497 } 1498 $sql = "UPDATE $table SET ".implode(',',$params).' WHERE '.$where; 1499 // check if we already prepared that statement 1500 if (!isset($this->prepared_sql[$sql])) 1501 { 1502 $this->prepared_sql[$sql] = $this->Link_ID->Prepare($sql); 1503 } 1504 $sql = $this->prepared_sql[$sql]; 1505 $inputarr = &$data; 1506 } 1507 else 1508 { 1509 $sql = "UPDATE $table SET ". 1510 $this->column_data_implode(',',$data,True,true,$table_def['fd']).' WHERE '.$where; 1511 } 1512 $ret = $this->query($sql,$line,$file,0,-1,$inputarr); 1513 if ($this->Debug) echo "<p>db::query('$sql',$line,$file) = '$ret'</p>\n"; 1514 } 1515 // if we have any blobs to update, we do so now 1516 if (($ret || !count($data)) && count($blobs2update)) 1517 { 1518 foreach($blobs2update as $col => $val) 1519 { 1520 $ret = $this->Link_ID->UpdateBlob($table,$col,$val,$where,$table_def['fd'][$col]['type'] == 'blob' ? 'BLOB' : 'CLOB'); 1521 if ($this->Debug) echo "<p>adodb::UpdateBlob('$table','$col','$val','$where') = '$ret'</p>\n"; 1522 if (!$ret) $this->halt("Error in UpdateBlob($table,$col,\$val,$where)",$line,$file); 1523 } 1524 } 1525 return $ret; 1526 } 1527 1528 /** 1529 * Deletes one or more rows in table, all data is quoted according to it's type 1530 * 1531 * @author RalfBecker<at>outdoor-training.de 1532 * 1533 * @param string $table name of the table 1534 * @param array $where column-name / values pairs and'ed together for the where clause 1535 * @param int $line line-number to pass to query 1536 * @param string $file file-name to pass to query 1537 * @param string/boolean $app string with name of app or False to use the current-app 1538 * @param array/bool $table_def use this table definition. If False, the table definition will be read from tables_baseline 1539 * @return ADORecordSet or false, if the query fails 1540 */ 1541 function delete($table,$where,$line,$file,$app=False,$table_def=False) 1542 { 1543 if (!$table_def) $table_def = $this->get_table_definitions($app,$table); 1544 $sql = "DELETE FROM $table WHERE ". 1545 $this->column_data_implode(' AND ',$where,True,False,$table_def['fd']); 1546 1547 return $this->query($sql,$line,$file); 1548 } 1549 1550 /** 1551 * Formats and quotes a sql expression to be used eg. as where-clause 1552 * 1553 * The function has a variable number of arguments, from which the expession gets constructed 1554 * eg. db::expression('my_table','(',array('name'=>"test'ed",'lang'=>'en'),') OR ',array('owner'=>array('',4,10))) 1555 * 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 1556 * 1557 * @param string/array $table_def table-name or definition array 1558 * @param mixed $args variable number of arguments of the following types: 1559 * string: get's as is into the result 1560 * array: column-name / value pairs: the value gets quoted according to the type of the column and prefixed 1561 * with column-name=, multiple pairs are AND'ed together, see db::column_data_implode 1562 * bool: If False or is_null($arg): the next 2 (!) arguments gets ignored 1563 * 1564 * Please note: As the function has a variable number of arguments, you CAN NOT add further parameters !!! 1565 * 1566 * @return string the expression generated from the arguments 1567 */ 1568 function expression($table_def,$args) 1569 { 1570 if (!is_array($table_def)) $table_def = $this->get_table_definitions('',$table_def); 1571 $sql = ''; 1572 $ignore_next = 0; 1573 foreach(func_get_args() as $n => $arg) 1574 { 1575 if ($n < 1) continue; // table-name 1576 1577 if ($ignore_next) 1578 { 1579 --$ignore_next; 1580 continue; 1581 } 1582 if (is_null($arg)) $arg = False; 1583 1584 switch(gettype($arg)) 1585 { 1586 case 'string': 1587 $sql .= $arg; 1588 break; 1589 case 'boolean': 1590 $ignore_next += !$arg ? 2 : 0; 1591 break; 1592 case 'array': 1593 $sql .= $this->column_data_implode(' AND ',$arg,True,False,$table_def['fd']); 1594 break; 1595 } 1596 } 1597 if ($this->Debug) echo "<p>db::expression($table,<pre>".print_r(func_get_args(),True)."</pre>) ='$sql'</p>\n"; 1598 return $sql; 1599 } 1600 1601 /** 1602 * Selects one or more rows in table depending on where, all data is quoted according to it's type 1603 * 1604 * @author RalfBecker<at>outdoor-training.de 1605 * 1606 * @param string $table name of the table 1607 * @param array/string $cols string or array of column-names / select-expressions 1608 * @param array/string $where string or array with column-name / values pairs AND'ed together for the where clause 1609 * @param int $line line-number to pass to query 1610 * @param string $file file-name to pass to query 1611 * @param int/bool $offset offset for a limited query or False (default) 1612 * @param string $append string to append to the end of the query, eg. ORDER BY ... 1613 * @param string/boolean $app string with name of app or False to use the current-app 1614 * @param int $num_rows number of rows to return if offset set, default 0 = use default in user prefs 1615 * @param string $join=null sql to do a join, added as is after the table-name, eg. ", table2 WHERE x=y" or 1616 * "LEFT JOIN table2 ON (x=y)", Note: there's no quoting done on $join! 1617 * @param array/bool $table_def use this table definition. If False, the table definition will be read from tables_baseline 1618 * @return ADORecordSet or false, if the query fails 1619 */ 1620 function select($table,$cols,$where,$line,$file,$offset=False,$append='',$app=False,$num_rows=0,$join='',$table_def=False) 1621 { 1622 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"; 1623 1624 if (!$table_def) $table_def = $this->get_table_definitions($app,$table); 1625 if (is_array($cols)) 1626 { 1627 $cols = implode(',',$cols); 1628 } 1629 if (is_array($where)) 1630 { 1631 $where = $this->column_data_implode(' AND ',$where,True,False,$table_def['fd']); 1632 } 1633 $sql = "SELECT $cols FROM $table $join"; 1634 1635 // if we have a where clause, we need to add it together with the WHERE statement, if thats not in the join 1636 if ($where) $sql .= strstr($join,"WHERE") ? ' AND ('.$where.')' : ' WHERE '.$where; 1637 1638 if ($append) $sql .= ' '.$append; 1639 1640 if ($this->Debug) echo "<p>sql='$sql'</p>"; 1641 1642 if ($line === false && $file === false) // call by union, to return the sql rather then run the query 1643 { 1644 return $sql; 1645 } 1646 return $this->query($sql,$line,$file,$offset,$offset===False ? -1 : (int)$num_rows); 1647 } 1648 1649 /** 1650 * Does a union over multiple selects 1651 * 1652 * @author RalfBecker<at>outdoor-training.de 1653 * 1654 * @param array $selects array of selects, each select is an array with the possible keys/parameters: table, cols, where, append, app, join, table_def 1655 * For further info about parameters see the definition of the select function, beside table, cols and where all other params are optional 1656 * @param int $line line-number to pass to query 1657 * @param string $file file-name to pass to query 1658 * @param string $order_by ORDER BY statement for the union 1659 * @param int/bool $offset offset for a limited query or False (default) 1660 * @param int $num_rows number of rows to return if offset set, default 0 = use default in user prefs 1661 * @return ADORecordSet or false, if the query fails 1662 */ 1663 function union($selects,$line,$file,$order_by='',$offset=false,$num_rows=0) 1664 { 1665 if ($this->Debug) echo "<p>db::union(".print_r($selects,True).",$line,$file,$order_by,$offset,$num_rows)</p>\n"; 1666 1667 $sql = array(); 1668 foreach($selects as $select) 1669 { 1670 $sql[] = call_user_func_array(array($this,'select'),array( 1671 $select['table'], 1672 $select['cols'], 1673 $select['where'], 1674 false, // line 1675 false, // file 1676 false, // offset 1677 $select['append'], 1678 $select['app'], 1679 0, // num_rows, 1680 $select['join'], 1681 $select['table_def'], 1682 )); 1683 } 1684 $sql = count($sql) > 1 ? '(' . implode(")\nUNION\n(",$sql).')' : $sql[0]; 1685 1686 if ($order_by) $sql .= (!stristr($order_by,'ORDER BY') ? "\nORDER BY " : '').$order_by; 1687 1688 if ($this->Debug) echo "<p>sql='$sql'</p>"; 1689 1690 return $this->query($sql,$line,$file,$offset,$offset===False ? -1 : (int)$num_rows); 1691 } 1692 }
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 17:20:01 2007 | par Balluche grâce à PHPXref 0.7 |