| [ Index ] |
PHP Cross Reference of vtigercrm-6.1.0 |
[Summary view] [Print] [Text view]
1 <?php 2 /********************************************************************************* 3 * The contents of this file are subject to the SugarCRM Public License Version 1.1.2 4 * ("License"); You may not use this file except in compliance with the 5 * License. You may obtain a copy of the License at http://www.sugarcrm.com/SPL 6 * Software distributed under the License is distributed on an "AS IS" basis, 7 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for 8 * the specific language governing rights and limitations under the License. 9 * The Original Code is: SugarCRM Open Source 10 * The Initial Developer of the Original Code is SugarCRM, Inc. 11 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.; 12 * All Rights Reserved. 13 * Contributor(s): ______________________________________. 14 ********************************************************************************/ 15 16 require_once 'include/logging.php'; 17 include_once 'libraries/adodb/adodb.inc.php'; 18 require_once 'libraries/adodb/adodb-xmlschema.inc.php'; 19 20 $log =& LoggerManager::getLogger('VT'); 21 $logsqltm =& LoggerManager::getLogger('SQLTIME'); 22 23 // Callback class useful to convert PreparedStatement Question Marks to SQL value 24 // See function convertPS2Sql in PearDatabase below 25 class PreparedQMark2SqlValue { 26 // Constructor 27 function PreparedQMark2SqlValue($vals){ 28 $this->ctr = 0; 29 $this->vals = $vals; 30 } 31 function call($matches){ 32 /** 33 * If ? is found as expected in regex used in function convert2sql 34 * /('[^']*')|(\"[^\"]*\")|([?])/ 35 * 36 */ 37 if($matches[3]=='?'){ 38 $this->ctr++; 39 return $this->vals[$this->ctr-1]; 40 }else{ 41 return $matches[0]; 42 } 43 } 44 } 45 46 /** 47 * Performance perference API 48 */ 49 @include_once ('config.performance.php'); // Ignore warning if not present 50 class PerformancePrefs { 51 /** 52 * Get performance parameter configured value or default one 53 */ 54 static function get($key, $defvalue=false) { 55 global $PERFORMANCE_CONFIG; 56 if(isset($PERFORMANCE_CONFIG)){ 57 if(isset($PERFORMANCE_CONFIG[$key])) { 58 return $PERFORMANCE_CONFIG[$key]; 59 } 60 } 61 return $defvalue; 62 } 63 /** Get boolean value */ 64 static function getBoolean($key, $defvalue=false) { 65 return self::get($key, $defvalue); 66 } 67 /** Get Integer value */ 68 static function getInteger($key, $defvalue=false) { 69 return intval(self::get($key, $defvalue)); 70 } 71 } 72 73 class PearDatabase{ 74 var $database = null; 75 var $dieOnError = false; 76 var $dbType = null; 77 var $dbHostName = null; 78 var $dbName = null; 79 var $dbOptions = null; 80 var $userName=null; 81 var $userPassword=null; 82 var $query_time = 0; 83 var $log = null; 84 var $lastmysqlrow = -1; 85 var $enableSQLlog = false; 86 var $continueInstallOnError = true; 87 88 // If you want to avoid executing PreparedStatement, set this to true 89 // PreparedStatement will be converted to normal SQL statement for execution 90 var $avoidPreparedSql = false; 91 92 /** 93 * Performance tunning parameters (can be configured through performance.prefs.php) 94 * See the constructor for initialization 95 */ 96 var $isdb_default_utf8_charset = false; 97 98 /** 99 * Manage instance usage of this class 100 */ 101 static function &getInstance() { 102 global $adb, $log; 103 104 if(!isset($adb)) { 105 $adb = new self(); 106 } 107 return $adb; 108 } 109 // END 110 111 function isMySQL() { return (stripos($this->dbType ,'mysql') === 0);} 112 function isOracle() { return $this->dbType=='oci8'; } 113 function isPostgres() { return $this->dbType=='pgsql'; } 114 115 function println($msg) 116 { 117 require_once ('include/logging.php'); 118 $log1 = LoggerManager::getLogger('VT'); 119 if(is_array($msg)) { 120 $log1->info("PearDatabse ->".print_r($msg,true)); 121 } else { 122 $log1->info("PearDatabase ->".$msg); 123 } 124 return $msg; 125 } 126 127 function setDieOnError($value){ $this->dieOnError = $value; } 128 function setDatabaseType($type){ $this->dbType = $type; } 129 function setUserName($name){ $this->userName = $name; } 130 131 function setOption($name, $value){ 132 if(isset($this->dbOptions)) $this->dbOptions[$name] = $value; 133 if(isset($this->database)) $this->database->setOption($name, $value); 134 } 135 136 function setUserPassword($pass){ $this->userPassword = $pass; } 137 function setDatabaseName($db){ $this->dbName = $db; } 138 function setDatabaseHost($host){ $this->dbHostName = $host; } 139 140 function getDataSourceName(){ 141 return $this->dbType. "://".$this->userName.":".$this->userPassword."@". $this->dbHostName . "/". $this->dbName; 142 } 143 144 function startTransaction() { 145 if($this->isPostgres()) return; 146 $this->checkConnection(); 147 $this->println("TRANS Started"); 148 $this->database->StartTrans(); 149 } 150 151 function completeTransaction() { 152 if($this->isPostgres()) return; 153 if($this->database->HasFailedTrans()) $this->println("TRANS Rolled Back"); 154 else $this->println("TRANS Commited"); 155 156 $this->database->CompleteTrans(); 157 $this->println("TRANS Completed"); 158 } 159 160 function hasFailedTransaction(){ return $this->database->HasFailedTrans(); } 161 162 function checkError($msg='', $dieOnError=false) { 163 if($this->dieOnError || $dieOnError) { 164 $bt = debug_backtrace(); 165 $ut = array(); 166 foreach ($bt as $t) { 167 $ut[] = array('file'=>$t['file'],'line'=>$t['line'],'function'=>$t['function']); 168 } 169 echo '<pre>'; 170 var_export($ut); 171 echo '</pre>'; 172 $this->println("ADODB error ".$msg."->[".$this->database->ErrorNo()."]".$this->database->ErrorMsg()); 173 die ($msg."ADODB error ".$msg."->".$this->database->ErrorMsg()); 174 } else { 175 $this->println("ADODB error ".$msg."->[".$this->database->ErrorNo()."]".$this->database->ErrorMsg()); 176 } 177 return false; 178 } 179 180 function change_key_case($arr) { 181 return is_array($arr)?array_change_key_case($arr):$arr; 182 } 183 184 var $req_flist; 185 function checkConnection(){ 186 global $log; 187 188 if(!isset($this->database)) { 189 $this->println("TRANS creating new connection"); 190 $this->connect(false); 191 } else { 192 //$this->println("checkconnect using old connection"); 193 } 194 } 195 196 /* SQLTime logging */ 197 protected $logSqlTimingID = false; 198 function logSqlTiming($startat, $endat, $sql, $params=false) { 199 if(!PerformancePrefs::getBoolean('SQL_LOG_INCLUDE_CALLER', false)) { 200 return; 201 } 202 203 $today = date('Y-m-d H:i:s'); $logtable = 'vtiger_sqltimelog'; 204 $logsql = 'INSERT INTO '.$logtable.'(id, type, started, ended, data, loggedon) VALUES (?,?,?,?,?,?)'; 205 206 if ($this->logSqlTimingID === false) { 207 $this->logSqlTimingID = $this->getUniqueID($logtable); 208 209 $type = (php_sapi_name() == 'cli') ? 'CLI' : 'REQ'; 210 $data = ''; 211 if (isset($_SERVER['REQUEST_METHOD'])) { 212 $uri = $_SERVER['REQUEST_URI']; 213 $qmarkIndex = strpos($_SERVER['REQUEST_URI'], '?'); 214 if ($qmarkIndex !== false) $uri = substr($uri, 0, $qmarkIndex); 215 $data = $uri . '?'. http_build_query($_SERVER['REQUEST_METHOD'] == 'GET'? $_GET:$_POST); 216 } else if ($argv) { 217 $data = implode(' ', $argv); 218 } 219 220 $this->database->Execute($logsql, array($this->logSqlTimingID, $type, NULL, NULL, $data, $today)); 221 } 222 223 $type = 'SQL'; 224 $data = trim($sql); 225 if (is_array($params) && !empty($params)) { 226 $data .= "\n[" . implode(",", $params) . "]"; 227 } 228 $this->database->Execute($logsql, array($this->logSqlTimingID, $type, $startat, $endat, $data, $today)); 229 230 $type = 'CALLERS'; 231 $data = array(); 232 $callers = debug_backtrace(); 233 for ($calleridx = 0, $callerscount = count($callers); $calleridx < $callerscount; ++$calleridx) { 234 if ($calleridx == 0) { 235 continue; 236 } 237 if ($calleridx < $callerscount) { 238 $callerfunc = $callers[$calleridx+1]['function']; 239 if (!empty($callerfunc)) $callerfunc = " ($callerfunc) "; 240 } 241 $data[] = "CALLER: (" . $callers[$calleridx]['line'] . ') ' . $callers[$calleridx]['file'] . $callerfunc; 242 } 243 $this->database->Execute($logsql, array($this->logSqlTimingID, $type, NULL, NULL, implode("\n", $data), $today)); 244 } 245 246 /** 247 * Execute SET NAMES UTF-8 on the connection based on configuration. 248 */ 249 function executeSetNamesUTF8SQL($force = false) { 250 global $default_charset; 251 static $DEFAULTCHARSET = null; 252 if ($DEFAULTCHARSET === null) $DEFAULTCHARSET = strtoupper($default_charset); 253 254 // Performance Tuning: If database default charset is UTF-8, we don't need this 255 if($DEFAULTCHARSET == 'UTF-8' && ($force || !$this->isdb_default_utf8_charset)) { 256 257 $sql_start_time = microtime(true); 258 259 $setnameSql = "SET NAMES utf8"; 260 $this->database->Execute($setnameSql); 261 $this->logSqlTiming($sql_start_time, microtime(true), $setnameSql); 262 } 263 } 264 265 /** 266 * Execute query in a batch. 267 * 268 * For example: 269 * INSERT INTO TABLE1 VALUES (a,b); 270 * INSERT INTO TABLE1 VALUES (c,d); 271 * 272 * like: INSERT INTO TABLE1 VALUES (a,b), (c,d) 273 */ 274 function query_batch($prefixsql, $valuearray) { 275 if(PerformancePrefs::getBoolean('ALLOW_SQL_QUERY_BATCH')) { 276 $sql = $prefixsql; 277 $suffixsql = $valuearray; 278 if(!is_array($valuearray)) $suffixsql = implode(',', $valuearray); 279 $this->query($prefixsql . $suffixsql); 280 } else { 281 if(is_array($valuearray) && !empty($valuearray)) { 282 foreach($valuearray as $suffixsql) { 283 $this->query($prefixsql . $suffixsql); 284 } 285 } 286 } 287 } 288 289 function query($sql, $dieOnError=false, $msg='') 290 { 291 global $log, $default_charset; 292 293 $log->debug('query being executed : '.$sql); 294 $this->checkConnection(); 295 296 $this->executeSetNamesUTF8SQL(); 297 298 $sql_start_time = microtime(true); 299 $result = & $this->database->Execute($sql); 300 $this->logSqlTiming($sql_start_time, microtime(true), $sql); 301 302 $this->lastmysqlrow = -1; 303 if(!$result)$this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError); 304 305 return $result; 306 } 307 308 309 /** 310 * Convert PreparedStatement to SQL statement 311 */ 312 function convert2Sql($ps, $vals) { 313 if(empty($vals)) { return $ps; } 314 // TODO: Checks need to be added array out of bounds situations 315 for($index = 0; $index < count($vals); $index++) { 316 // Package import pushes data after XML parsing, so type-cast it 317 if(is_a($vals[$index], 'SimpleXMLElement')) { 318 $vals[$index] = (string) $vals[$index]; 319 } 320 if(is_string($vals[$index])) { 321 if($vals[$index] == '') { 322 $vals[$index] = $this->database->Quote($vals[$index]); 323 } 324 else { 325 $vals[$index] = "'".$this->sql_escape_string($vals[$index]). "'"; 326 } 327 } 328 if($vals[$index] === null) { 329 $vals[$index] = "NULL"; 330 } 331 } 332 $sql = preg_replace_callback("/('[^']*')|(\"[^\"]*\")|([?])/", array(new PreparedQMark2SqlValue($vals),"call"), $ps); 333 return $sql; 334 } 335 336 /* ADODB prepared statement Execution 337 * @param $sql -- Prepared sql statement 338 * @param $params -- Parameters for the prepared statement 339 * @param $dieOnError -- Set to true, when query execution fails 340 * @param $msg -- Error message on query execution failure 341 */ 342 function pquery($sql, $params=array(), $dieOnError=false, $msg='') { 343 global $log, $default_charset; 344 $log->debug('Prepared sql query being executed : '.$sql); 345 $this->checkConnection(); 346 347 $this->executeSetNamesUTF8SQL(); 348 349 $sql_start_time = microtime(true); 350 $params = $this->flatten_array($params); 351 if (count($params) > 0) { 352 $log->debug('Prepared sql query parameters : [' . implode(",", $params) . ']'); 353 } 354 355 if($this->avoidPreparedSql || empty($params)) { 356 $sql = $this->convert2Sql($sql, $params); 357 $result = $this->database->Execute($sql); 358 } else { 359 $result = $this->database->Execute($sql, $params); 360 } 361 $sql_end_time = microtime(true); 362 $this->logSqlTiming($sql_start_time, $sql_end_time, $sql, $params); 363 364 $this->lastmysqlrow = -1; 365 if(!$result)$this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError); 366 367 return $result; 368 } 369 370 /** 371 * Flatten the composite array into single value. 372 * Example: 373 * $input = array(10, 20, array(30, 40), array('key1' => '50', 'key2'=>array(60), 70)); 374 * returns array(10, 20, 30, 40, 50, 60, 70); 375 */ 376 function flatten_array($input, $output=null) { 377 if($input == null) return null; 378 if($output == null) $output = array(); 379 foreach($input as $value) { 380 if(is_array($value)) { 381 $output = $this->flatten_array($value, $output); 382 } else { 383 array_push($output, $value); 384 } 385 } 386 return $output; 387 } 388 389 function getEmptyBlob($is_string=true) 390 { 391 //if(dbType=="oci8") return 'empty_blob()'; 392 //else return 'null'; 393 if (is_string) return 'null'; 394 return null; 395 } 396 397 function updateBlob($tablename, $colname, $id, $data) 398 { 399 $this->println("updateBlob t=".$tablename." c=".$colname." id=".$id); 400 $this->checkConnection(); 401 $this->executeSetNamesUTF8SQL(); 402 403 $sql_start_time = microtime(true); 404 $result = $this->database->UpdateBlob($tablename, $colname, $data, $id); 405 $this->logSqlTiming($sql_start_time, microtime(true), "Update Blob $tablename, $colname, $id"); 406 407 $this->println("updateBlob t=".$tablename." c=".$colname." id=".$id." status=".$result); 408 return $result; 409 } 410 411 function updateBlobFile($tablename, $colname, $id, $filename) 412 { 413 $this->println("updateBlobFile t=".$tablename." c=".$colname." id=".$id." f=".$filename); 414 $this->checkConnection(); 415 $this->executeSetNamesUTF8SQL(); 416 417 $sql_start_time = microtime(true); 418 $result = $this->database->UpdateBlobFile($tablename, $colname, $filename, $id); 419 $this->logSqlTiming($sql_start_time, microtime(true), "Update Blob $tablename, $colname, $id"); 420 421 $this->println("updateBlobFile t=".$tablename." c=".$colname." id=".$id." f=".$filename." status=".$result); 422 return $result; 423 } 424 425 function limitQuery($sql,$start,$count, $dieOnError=false, $msg='') 426 { 427 global $log; 428 //$this->println("ADODB limitQuery sql=".$sql." st=".$start." co=".$count); 429 $log->debug(' limitQuery sql = '.$sql .' st = '.$start .' co = '.$count); 430 $this->checkConnection(); 431 432 $this->executeSetNamesUTF8SQL(); 433 434 $sql_start_time = microtime(true); 435 $result =& $this->database->SelectLimit($sql,$count,$start); 436 $this->logSqlTiming($sql_start_time, microtime(true), "$sql LIMIT $count, $start"); 437 438 if(!$result) $this->checkError($msg.' Limit Query Failed:' . $sql . '::', $dieOnError); 439 return $result; 440 } 441 442 function getOne($sql, $dieOnError=false, $msg='') 443 { 444 $this->println("ADODB getOne sql=".$sql); 445 $this->checkConnection(); 446 447 $this->executeSetNamesUTF8SQL(); 448 449 $sql_start_time = microtime(true); 450 $result =& $this->database->GetOne($sql); 451 $this->logSqlTiming($sql_start_time, microtime(true), "$sql GetONE"); 452 453 if(!$result) $this->checkError($msg.' Get one Query Failed:' . $sql . '::', $dieOnError); 454 return $result; 455 } 456 457 function getFieldsDefinition(&$result) 458 { 459 //$this->println("ADODB getFieldsArray"); 460 $field_array = array(); 461 if(! isset($result) || empty($result)) 462 { 463 return 0; 464 } 465 466 $i = 0; 467 $n = $result->FieldCount(); 468 while ($i < $n) 469 { 470 $meta = $result->FetchField($i); 471 if (!$meta) 472 { 473 return 0; 474 } 475 array_push($field_array,$meta); 476 $i++; 477 } 478 479 //$this->println($field_array); 480 return $field_array; 481 } 482 483 function getFieldsArray(&$result) 484 { 485 //$this->println("ADODB getFieldsArray"); 486 $field_array = array(); 487 if(! isset($result) || empty($result)) 488 { 489 return 0; 490 } 491 492 $i = 0; 493 $n = $result->FieldCount(); 494 while ($i < $n) 495 { 496 $meta = $result->FetchField($i); 497 if (!$meta) 498 { 499 return 0; 500 } 501 array_push($field_array,$meta->name); 502 $i++; 503 } 504 505 //$this->println($field_array); 506 return $field_array; 507 } 508 509 function getRowCount(&$result){ 510 global $log; 511 if(isset($result) && !empty($result)) 512 $rows= $result->RecordCount(); 513 return $rows; 514 } 515 516 /* ADODB newly added. replacement for mysql_num_rows */ 517 function num_rows(&$result) { 518 return $this->getRowCount($result); 519 } 520 521 /* ADODB newly added. replacement form mysql_num_fields */ 522 function num_fields(&$result) { 523 return $result->FieldCount(); 524 } 525 526 /* ADODB newly added. replacement for mysql_fetch_array() */ 527 function fetch_array(&$result) { 528 if($result->EOF) { 529 //$this->println("ADODB fetch_array return null"); 530 return NULL; 531 } 532 $arr = $result->FetchRow(); 533 if(is_array($arr)) 534 $arr = array_map('to_html', $arr); 535 return $this->change_key_case($arr); 536 } 537 538 ## adds new functions to the PearDatabase class to come around the whole 539 ## broken query_result() idea 540 ## Code-Contribution given by [email protected] - Starts 541 function run_query_record_html($query) { 542 if (!is_array($rec = $this->run_query_record($query))) 543 return $rec; 544 foreach ($rec as $walk => $cur) 545 $r[$walk] = to_html($cur); 546 return $r; 547 } 548 549 function sql_quote($data) { 550 if (is_array($data)) { 551 switch($data{'type'}) { 552 case 'text': 553 case 'numeric': 554 case 'integer': 555 case 'oid': 556 return $this->quote($data{'value'}); 557 break; 558 case 'timestamp': 559 return $this->formatDate($data{'value'}); 560 break; 561 default: 562 throw new Exception("unhandled type: ".serialize($cur)); 563 } 564 } else 565 return $this->quote($data); 566 } 567 568 function sql_insert_data($table, $data) { 569 if (!$table) 570 throw new Exception("missing table name"); 571 if (!is_array($data)) 572 throw new Exception("data must be an array"); 573 if (!count($table)) 574 throw new Exception("no data given"); 575 576 $sql_fields = ''; 577 $sql_data = ''; 578 foreach($data as $walk => $cur) { 579 $sql_fields .= ($sql_fields?',':'').$walk; 580 $sql_data .= ($sql_data?',':'').$this->sql_quote($cur); 581 } 582 return 'INSERT INTO '.$table.' ('.$sql_fields.') VALUES ('.$sql_data.')'; 583 } 584 585 function run_insert_data($table,$data) { 586 $query = $this->sql_insert_data($table,$data); 587 $res = $this->query($query); 588 $this->query("commit;"); 589 } 590 591 function run_query_record($query) { 592 $result = $this->query($query); 593 if (!$result) 594 return; 595 if (!is_object($result)) 596 throw new Exception("query \"$query\" failed: ".serialize($result)); 597 $res = $result->FetchRow(); 598 $rowdata = $this->change_key_case($res); 599 return $rowdata; 600 } 601 602 function run_query_allrecords($query) { 603 $result = $this->query($query); 604 $records = array(); 605 $sz = $this->num_rows($result); 606 for ($i=0; $i<$sz; $i++) 607 $records[$i] = $this->change_key_case($result->FetchRow()); 608 return $records; 609 } 610 611 function run_query_field($query,$field='') { 612 $rowdata = $this->run_query_record($query); 613 if(isset($field) && $field != '') 614 return $rowdata{$field}; 615 else 616 return array_shift($rowdata); 617 } 618 619 function run_query_list($query,$field){ 620 $records = $this->run_query_allrecords($query); 621 foreach($records as $walk => $cur) 622 $list[] = $cur{$field}; 623 } 624 625 function run_query_field_html($query,$field){ 626 return to_html($this->run_query_field($query,$field)); 627 } 628 629 function result_get_next_record($result){ 630 return $this->change_key_case($result->FetchRow()); 631 } 632 633 // create an IN expression from an array/list 634 function sql_expr_datalist($a) { 635 if (!is_array($a)) 636 throw new Exception("not an array"); 637 if (!count($a)) 638 throw new Exception("empty arrays not allowed"); 639 640 foreach($a as $walk => $cur) 641 $l .= ($l?',':'').$this->quote($cur); 642 return ' ( '.$l.' ) '; 643 } 644 645 // create an IN expression from an record list, take $field within each record 646 function sql_expr_datalist_from_records($a,$field) { 647 if (!is_array($a)) 648 throw new Exception("not an array"); 649 if (!$field) 650 throw new Exception("missing field"); 651 if (!count($a)) 652 throw new Exception("empty arrays not allowed"); 653 654 foreach($a as $walk => $cur) 655 $l .= ($l?',':'').$this->quote($cur{$field}); 656 657 return ' ( '.$l.' ) '; 658 } 659 660 function sql_concat($list) { 661 switch ($this->dbType) { 662 case 'mysql': 663 return 'concat('.implode(',',$list).')'; 664 case 'mysqli': 665 return 'concat('.implode(',',$list).')'; 666 case 'pgsql': 667 return '('.implode('||',$list).')'; 668 default: 669 throw new Exception("unsupported dbtype \"".$this->dbType."\""); 670 } 671 } 672 ## Code-Contribution given by [email protected] - Ends 673 674 /* ADODB newly added. replacement for mysql_result() */ 675 function query_result(&$result, $row, $col=0) { 676 if (!is_object($result)) 677 throw new Exception("result is not an object"); 678 $result->Move($row); 679 $rowdata = $this->change_key_case($result->FetchRow()); 680 //$this->println($rowdata); 681 //Commented strip_selected_tags and added to_html function for HTML tags vulnerability 682 if($col == 'fieldlabel') $coldata = $rowdata[$col]; 683 else $coldata = to_html($rowdata[$col]); 684 return $coldata; 685 } 686 687 // Function to get particular row from the query result 688 function query_result_rowdata(&$result, $row=0) { 689 if (!is_object($result)) 690 throw new Exception("result is not an object"); 691 $result->Move($row); 692 $rowdata = $this->change_key_case($result->FetchRow()); 693 694 foreach($rowdata as $col => $coldata) { 695 if($col != 'fieldlabel') 696 $rowdata[$col] = to_html($coldata); 697 } 698 return $rowdata; 699 } 700 701 /** 702 * Get an array representing a row in the result set 703 * Unlike it's non raw siblings this method will not escape 704 * html entities in return strings. 705 * 706 * The case of all the field names is converted to lower case. 707 * as with the other methods. 708 * 709 * @param &$result The query result to fetch from. 710 * @param $row The row number to fetch. It's default value is 0 711 * 712 */ 713 function raw_query_result_rowdata(&$result, $row=0) { 714 if (!is_object($result)) 715 throw new Exception("result is not an object"); 716 $result->Move($row); 717 $rowdata = $this->change_key_case($result->FetchRow()); 718 return $rowdata; 719 } 720 721 722 723 function getAffectedRowCount(&$result){ 724 global $log; 725 $log->debug('getAffectedRowCount'); 726 $rows =$this->database->Affected_Rows(); 727 $log->debug('getAffectedRowCount rows = '.$rows); 728 return $rows; 729 } 730 731 function requireSingleResult($sql, $dieOnError=false,$msg='', $encode=true) { 732 $result = $this->query($sql, $dieOnError, $msg); 733 734 if($this->getRowCount($result ) == 1) 735 return $result; 736 $this->log->error('Rows Returned:'. $this->getRowCount($result) .' More than 1 row returned for '. $sql); 737 return ''; 738 } 739 /* function which extends requireSingleResult api to execute prepared statment 740 */ 741 742 function requirePsSingleResult($sql, $params, $dieOnError=false,$msg='', $encode=true) { 743 $result = $this->pquery($sql, $params, $dieOnError, $msg); 744 745 if($this->getRowCount($result ) == 1) 746 return $result; 747 $this->log->error('Rows Returned:'. $this->getRowCount($result) .' More than 1 row returned for '. $sql); 748 return ''; 749 } 750 751 function fetchByAssoc(&$result, $rowNum = -1, $encode=true) { 752 if($result->EOF) { 753 $this->println("ADODB fetchByAssoc return null"); 754 return NULL; 755 } 756 if(isset($result) && $rowNum < 0) { 757 $row = $this->change_key_case($result->GetRowAssoc(false)); 758 $result->MoveNext(); 759 if($encode&& is_array($row)) 760 return array_map('to_html', $row); 761 return $row; 762 } 763 764 if($this->getRowCount($result) > $rowNum) { 765 $result->Move($rowNum); 766 } 767 $this->lastmysqlrow = $rowNum; 768 $row = $this->change_key_case($result->GetRowAssoc(false)); 769 $result->MoveNext(); 770 $this->println($row); 771 772 if($encode&& is_array($row)) 773 return array_map('to_html', $row); 774 return $row; 775 } 776 777 function getNextRow(&$result, $encode=true){ 778 global $log; 779 $log->info('getNextRow'); 780 if(isset($result)){ 781 $row = $this->change_key_case($result->FetchRow()); 782 if($row && $encode&& is_array($row)) 783 return array_map('to_html', $row); 784 return $row; 785 } 786 return null; 787 } 788 789 function fetch_row(&$result, $encode=true) { 790 return $this->getNextRow($result); 791 } 792 793 function field_name(&$result, $col) { 794 return $result->FetchField($col); 795 } 796 797 function getQueryTime(){ 798 return $this->query_time; 799 } 800 801 function connect($dieOnError = false) { 802 global $dbconfigoption,$dbconfig; 803 if(!isset($this->dbType)) { 804 $this->println("ADODB Connect : DBType not specified"); 805 return; 806 } 807 $this->database = ADONewConnection($this->dbType); 808 809 $result = $this->database->PConnect($this->dbHostName, $this->userName, $this->userPassword, $this->dbName); 810 if ($result) { 811 $this->database->LogSQL($this->enableSQLlog); 812 813 // 'SET NAMES UTF8' needs to be executed even if database has default CHARSET UTF8 814 // as mysql server might be running with different charset! 815 // We will notice problem reading UTF8 characters otherwise. 816 if($this->isdb_default_utf8_charset) { 817 $this->executeSetNamesUTF8SQL(true); 818 } 819 } 820 } 821 822 /** 823 * Constructor 824 */ 825 function PearDatabase($dbtype='',$host='',$dbname='',$username='',$passwd='') { 826 global $currentModule; 827 $this->log = LoggerManager::getLogger('PearDatabase_'. $currentModule); 828 $this->resetSettings($dbtype,$host,$dbname,$username,$passwd); 829 830 // Initialize performance parameters 831 $this->isdb_default_utf8_charset = PerformancePrefs::getBoolean('DB_DEFAULT_CHARSET_UTF8'); 832 // END 833 834 if(!isset($this->dbType)) 835 { 836 $this->println("ADODB Connect : DBType not specified"); 837 return; 838 } 839 840 } 841 842 function resetSettings($dbtype,$host,$dbname,$username,$passwd){ 843 global $dbconfig, $dbconfigoption; 844 845 if($host == '') { 846 $this->disconnect(); 847 $this->setDatabaseType($dbconfig['db_type']); 848 $this->setUserName($dbconfig['db_username']); 849 $this->setUserPassword($dbconfig['db_password']); 850 $this->setDatabaseHost( $dbconfig['db_hostname']); 851 $this->setDatabaseName($dbconfig['db_name']); 852 $this->dbOptions = $dbconfigoption; 853 if($dbconfig['log_sql']) 854 $this->enableSQLlog = ($dbconfig['log_sql'] == true); 855 } else { 856 $this->disconnect(); 857 $this->setDatabaseType($dbtype); 858 $this->setDatabaseName($dbname); 859 $this->setUserName($username); 860 $this->setUserPassword($passwd); 861 $this->setDatabaseHost( $host); 862 } 863 } 864 865 function quote($string){ 866 return $this->database->qstr($string); 867 } 868 869 function disconnect() { 870 $this->println("ADODB disconnect"); 871 if(isset($this->database)){ 872 if($this->dbType == "mysql"){ 873 mysql_close($this->database->_connectionID); 874 }else if($this->dbType=="mysqli"){ 875 mysqli_close($this->database->_connectionID); 876 } 877 else { 878 $this->database->disconnect(); 879 } 880 unset($this->database); 881 } 882 } 883 884 function setDebug($value) { 885 $this->database->debug = $value; 886 } 887 888 // ADODB newly added methods 889 function createTables($schemaFile, $dbHostName=false, $userName=false, $userPassword=false, $dbName=false, $dbType=false) { 890 $this->println("ADODB createTables ".$schemaFile); 891 if($dbHostName!=false) $this->dbHostName=$dbHostName; 892 if($userName!=false) $this->userName=$userPassword; 893 if($userPassword!=false) $this->userPassword=$userPassword; 894 if($dbName!=false) $this->dbName=$dbName; 895 if($dbType!=false) $this->dbType=$dbType; 896 897 $this->checkConnection(); 898 $db = $this->database; 899 $schema = new adoSchema( $db ); 900 //Debug Adodb XML Schema 901 $schema->XMLS_DEBUG = TRUE; 902 //Debug Adodb 903 $schema->debug = true; 904 $sql = $schema->ParseSchema( $schemaFile ); 905 906 $this->println("--------------Starting the table creation------------------"); 907 $result = $schema->ExecuteSchema( $sql, $this->continueInstallOnError ); 908 if($result) print $db->errorMsg(); 909 // needs to return in a decent way 910 $this->println("ADODB createTables ".$schemaFile." status=".$result); 911 return $result; 912 } 913 914 function createTable($tablename, $flds) { 915 $this->println("ADODB createTable table=".$tablename." flds=".$flds); 916 $this->checkConnection(); 917 $dict = NewDataDictionary($this->database); 918 $sqlarray = $dict->CreateTableSQL($tablename, $flds); 919 $result = $dict->ExecuteSQLArray($sqlarray); 920 $this->println("ADODB createTable table=".$tablename." flds=".$flds." status=".$result); 921 return $result; 922 } 923 924 function alterTable($tablename, $flds, $oper) { 925 $this->println("ADODB alterTableTable table=".$tablename." flds=".$flds." oper=".$oper); 926 $this->checkConnection(); 927 $dict = NewDataDictionary($this->database); 928 929 if($oper == 'Add_Column') { 930 $sqlarray = $dict->AddColumnSQL($tablename, $flds); 931 } else if($oper == 'Delete_Column') { 932 $sqlarray = $dict->DropColumnSQL($tablename, $flds); 933 } 934 $this->println("sqlarray"); 935 $this->println($sqlarray); 936 937 $result = $dict->ExecuteSQLArray($sqlarray); 938 939 $this->println("ADODB alterTableTable table=".$tablename." flds=".$flds." oper=".$oper." status=".$result); 940 return $result; 941 } 942 943 function getColumnNames($tablename) { 944 $this->println("ADODB getColumnNames table=".$tablename); 945 $this->checkConnection(); 946 $adoflds = $this->database->MetaColumns($tablename); 947 $i=0; 948 foreach($adoflds as $fld) { 949 $colNames[$i] = $fld->name; 950 $i++; 951 } 952 return $colNames; 953 } 954 955 function formatString($tablename,$fldname, $str) { 956 $this->checkConnection(); 957 $adoflds = $this->database->MetaColumns($tablename); 958 959 foreach ( $adoflds as $fld ) { 960 if(strcasecmp($fld->name,$fldname)==0) { 961 $fldtype =strtoupper($fld->type); 962 if(strcmp($fldtype,'CHAR')==0 || strcmp($fldtype,'VARCHAR') == 0 || strcmp($fldtype,'VARCHAR2') == 0 || strcmp($fldtype,'LONGTEXT')==0 || strcmp($fldtype,'TEXT')==0) { 963 return $this->database->Quote($str); 964 } else if(strcmp($fldtype,'DATE') ==0 || strcmp($fldtype,'TIMESTAMP')==0) { 965 return $this->formatDate($str); 966 } else { 967 return $str; 968 } 969 } 970 } 971 $this->println("format String Illegal field name ".$fldname); 972 return $str; 973 } 974 975 function formatDate($datetime, $strip_quotes=false) { 976 $this->checkConnection(); 977 $db = &$this->database; 978 $date = $db->DBTimeStamp($datetime); 979 /* remove single quotes to use the date as parameter for Prepared statement */ 980 if($strip_quotes == true) { 981 return trim($date, "'"); 982 } 983 return $date; 984 } 985 986 function getDBDateString($datecolname) { 987 $this->checkConnection(); 988 $db = &$this->database; 989 $datestr = $db->SQLDate("Y-m-d, H:i:s" ,$datecolname); 990 return $datestr; 991 } 992 993 function getUniqueID($seqname) { 994 $this->checkConnection(); 995 return $this->database->GenID($seqname."_seq",1); 996 } 997 998 function get_tables() { 999 $this->checkConnection(); 1000 $result = & $this->database->MetaTables('TABLES'); 1001 $this->println($result); 1002 return $result; 1003 } 1004 1005 //To get a function name with respect to the database type which escapes strings in given text 1006 function sql_escape_string($str) 1007 { 1008 if($this->isMySql()){ 1009 $result_data = ($this->dbType=='mysqli')?mysqli_real_escape_string($this->database->_connectionID,$str):mysql_real_escape_string($str); 1010 } 1011 elseif($this->isPostgres()) 1012 $result_data = pg_escape_string($str); 1013 return $result_data; 1014 } 1015 1016 // Function to get the last insert id based on the type of database 1017 function getLastInsertID($seqname = '') { 1018 if($this->isPostgres()) { 1019 $result = pg_query("SELECT currval('".$seqname."_seq')"); 1020 if($result) 1021 { 1022 $row = pg_fetch_row($result); 1023 $last_insert_id = $row[0]; 1024 } 1025 } else { 1026 $last_insert_id = $this->database->Insert_ID(); 1027 } 1028 return $last_insert_id; 1029 } 1030 1031 // Function to escape the special characters in database name based on database type. 1032 function escapeDbName($dbName='') { 1033 if ($dbName == '') $dbName = $this->dbName; 1034 if($this->isMySql()) { 1035 $dbName = "`{$dbName}`"; 1036 } 1037 return $dbName; 1038 } 1039 1040 function check_db_utf8_support() { 1041 global $db_type; 1042 if($db_type == 'pgsql') 1043 return true; 1044 $dbvarRS = $this->database->Execute("show variables like '%_database' "); 1045 $db_character_set = null; 1046 $db_collation_type = null; 1047 while(!$dbvarRS->EOF) { 1048 $arr = $dbvarRS->FetchRow(); 1049 $arr = array_change_key_case($arr); 1050 switch($arr['variable_name']) { 1051 case 'character_set_database' : $db_character_set = $arr['value']; break; 1052 case 'collation_database' : $db_collation_type = $arr['value']; break; 1053 } 1054 // If we have all the required information break the loop. 1055 if($db_character_set != null && $db_collation_type != null) break; 1056 } 1057 return (stristr($db_character_set, 'utf8') && stristr($db_collation_type, 'utf8')); 1058 } 1059 1060 function get_db_charset() { 1061 global $db_type; 1062 if($db_type == 'pgsql') 1063 return 'UTF8'; 1064 $dbvarRS = $this->database->query("show variables like '%_database' "); 1065 $db_character_set = null; 1066 while(!$dbvarRS->EOF) { 1067 $arr = $dbvarRS->FetchRow(); 1068 $arr = array_change_key_case($arr); 1069 if($arr['variable_name'] == 'character_set_database') { 1070 $db_character_set = $arr['value']; 1071 break; 1072 } 1073 } 1074 return $db_character_set; 1075 } 1076 } /* End of class */ 1077 1078 if(empty($adb)) { 1079 $adb = new PearDatabase(); 1080 $adb->connect(); 1081 } 1082 //$adb->database->setFetchMode(ADODB_FETCH_BOTH); 1083 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:08:37 2014 | Cross-referenced by PHPXref 0.7.1 |