2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM is a customer relationship management program developed by
5 * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
7 * This program is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU Affero General Public License version 3 as published by the
9 * Free Software Foundation with the addition of the following permission added
10 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
11 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
12 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
14 * This program is distributed in the hope that it will be useful, but WITHOUT
15 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
19 * You should have received a copy of the GNU Affero General Public License along with
20 * this program; if not, see http://www.gnu.org/licenses or write to the Free
21 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
24 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
25 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
27 * The interactive user interfaces in modified source and object code versions
28 * of this program must display Appropriate Legal Notices, as required under
29 * Section 5 of the GNU Affero General Public License version 3.
31 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
32 * these Appropriate Legal Notices must retain the display of the "Powered by
33 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
34 * technical reasons, the Appropriate Legal Notices must display the words
35 * "Powered by SugarCRM".
36 ********************************************************************************/
38 /*********************************************************************************
40 * Description: This file handles the Data base functionality for the application.
41 * It acts as the DB abstraction layer for the application. It depends on helper classes
42 * which generate the necessary SQL. This sql is then passed to PEAR DB classes.
43 * The helper class is chosen in DBManagerFactory, which is driven by 'db_type' in 'dbconfig' under config.php.
45 * All the functions in this class will work with any bean which implements the meta interface.
46 * The passed bean is passed to helper class which uses these functions to generate correct sql.
48 * The meta interface has the following functions:
49 * getTableName() Returns table name of the object.
50 * getFieldDefinitions() Returns a collection of field definitions in order.
51 * getFieldDefintion(name) Return field definition for the field.
52 * getFieldValue(name) Returns the value of the field identified by name.
53 * If the field is not set, the function will return boolean FALSE.
54 * getPrimaryFieldDefinition() Returns the field definition for primary key
56 * The field definition is an array with the following keys:
58 * name This represents name of the field. This is a required field.
59 * type This represents type of the field. This is a required field and valid values are:
73 * length This is used only when the type is varchar and denotes the length of the string.
74 * The max value is 255.
75 * enumvals This is a list of valid values for an enum separated by "|".
76 * It is used only if the type is �enum�;
77 * required This field dictates whether it is a required value.
78 * The default value is �FALSE�.
79 * isPrimary This field identifies the primary key of the table.
80 * If none of the fields have this flag set to �TRUE�,
81 * the first field definition is assume to be the primary key.
82 * Default value for this field is �FALSE�.
83 * default This field sets the default value for the field definition.
86 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
87 * All Rights Reserved.
88 * Contributor(s): ______________________________________..
89 ********************************************************************************/
91 //Technically we can port all the functions in the latest bean to this file
92 // that is what PEAR is doing anyways.
94 class MysqlManager extends DBManager
97 * @see DBManager::$dbType
99 public $dbType = 'mysql';
102 * @see DBManager::$backendFunctions
104 protected $backendFunctions = array(
105 'free_result' => 'mysql_free_result',
106 'close' => 'mysql_close',
107 'row_count' => 'mysql_num_rows',
108 'affected_row_count' => 'mysql_affected_rows',
112 * @see DBManager::checkError()
114 public function checkError(
119 if (parent::checkError($msg, $dieOnError))
122 if (mysql_errno($this->getDatabase())) {
123 if ($this->dieOnError || $dieOnError){
124 $GLOBALS['log']->fatal("MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database));
125 sugar_die ($GLOBALS['app_strings']['ERR_DB_FAIL']);
128 $this->last_error = $msg."MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database);
129 $GLOBALS['log']->error("MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database));
138 * Parses and runs queries
140 * @param string $sql SQL Statement to execute
141 * @param bool $dieOnError True if we want to call die if the query returns errors
142 * @param string $msg Message to log if error occurs
143 * @param bool $suppress Flag to suppress all error output unless in debug logging mode.
144 * @param bool $autofree True if we want to push this result into the $lastResult array.
145 * @return resource result set
147 public function query(
155 parent::countQuery($sql);
156 $GLOBALS['log']->info('Query:' . $sql);
157 $this->checkConnection();
158 //$this->freeResult();
159 $this->query_time = microtime(true);
160 $this->lastsql = $sql;
161 if ($suppress==true) {
164 $result = mysql_query($sql, $this->database);
167 $this->lastmysqlrow = -1;
168 $this->query_time = microtime(true) - $this->query_time;
169 $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
172 $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
174 $this->lastResult[] =& $result;
180 * @see DBManager::limitQuery()
182 public function limitQuery(
191 $GLOBALS['log']->debug('Limit Query:' . $sql. ' Start: ' .$start . ' count: ' . $count);
193 $sql = "$sql LIMIT $start,$count";
194 $this->lastsql = $sql;
196 if(!empty($GLOBALS['sugar_config']['check_query'])){
197 $this->checkQuery($sql);
200 return $this->query($sql, $dieOnError, $msg);
205 * @see DBManager::checkQuery()
207 protected function checkQuery(
211 $result = $this->query('EXPLAIN ' . $sql);
213 while ($row = $this->fetchByAssoc($result)) {
214 if (empty($row['table']))
216 $badQuery[$row['table']] = '';
217 if (strtoupper($row['type']) == 'ALL')
218 $badQuery[$row['table']] .= ' Full Table Scan;';
219 if (empty($row['key']))
220 $badQuery[$row['table']] .= ' No Index Key Used;';
221 if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using filesort') > 0)
222 $badQuery[$row['table']] .= ' Using FileSort;';
223 if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using temporary') > 0)
224 $badQuery[$row['table']] .= ' Using Temporary Table;';
227 if ( empty($badQuery) )
230 foreach($badQuery as $table=>$data ){
232 $warning = ' Table:' . $table . ' Data:' . $data;
233 if(!empty($GLOBALS['sugar_config']['check_query_log'])){
234 $GLOBALS['log']->fatal($sql);
235 $GLOBALS['log']->fatal('CHECK QUERY:' .$warning);
238 $GLOBALS['log']->warn('CHECK QUERY:' .$warning);
247 * @see DBManager::describeField()
249 protected function describeField(
254 global $table_descriptions;
255 if(isset($table_descriptions[$tablename])
256 && isset($table_descriptions[$tablename][$name]))
257 return $table_descriptions[$tablename][$name];
259 $table_descriptions[$tablename] = array();
260 $sql = "DESCRIBE $tablename";
261 $result = $this->query($sql);
262 while ($row = $this->fetchByAssoc($result) ){
263 $table_descriptions[$tablename][$row['Field']] = $row;
264 if(empty($table_descriptions[$tablename][$row['Field']]['Null']))
265 $table_descriptions[$tablename][$row['Field']]['Null'] = 'NO';
267 if(isset($table_descriptions[$tablename][$name]))
268 return $table_descriptions[$tablename][$name];
274 * @see DBManager::getFieldsArray()
276 public function getFieldsArray(
278 $make_lower_case=false)
280 $field_array = array();
282 if(! isset($result) || empty($result))
286 while ($i < mysql_num_fields($result)) {
287 $meta = mysql_fetch_field($result, $i);
291 if($make_lower_case == true)
292 $meta->name = strtolower($meta->name);
294 $field_array[] = $meta->name;
304 * @see DBManager::fetchByAssoc()
306 public function fetchByAssoc(
315 if ($result && $rowNum > -1){
316 if ($this->getRowCount($result) > $rowNum)
317 mysql_data_seek($result, $rowNum);
318 $this->lastmysqlrow = $rowNum;
321 $row = mysql_fetch_assoc($result);
323 if ($encode && $this->encode && is_array($row))
324 return array_map('to_html', $row);
330 * @see DBManager::getTablesArray()
332 public function getTablesArray()
334 global $sugar_config;
335 $GLOBALS['log']->debug('Fetching table list');
337 if ($this->getDatabase()) {
339 $r = $this->query('SHOW TABLES');
340 if (is_resource($r) || $r instanceOf mysqli_result ) {
341 while ($a = $this->fetchByAssoc($r)) {
342 $row = array_values($a);
349 return false; // no database available
353 * @see DBManager::version()
355 public function version()
357 return $this->getOne("SELECT version() version");
361 * @see DBManager::tableExists()
363 public function tableExists(
367 $GLOBALS['log']->info("tableExists: $tableName");
369 if ($this->getDatabase()) {
370 $result = $this->query("SHOW TABLES LIKE '".$tableName."'");
371 return ($this->getRowCount($result) == 0) ? false : true;
378 * @see DBManager::quote()
380 public function quote(
385 return mysql_real_escape_string(parent::quote($string), $this->getDatabase());
389 * @see DBManager::quoteForEmail()
391 public function quoteForEmail(
396 return mysql_real_escape_string($string, $this->getDatabase());
400 * @see DBManager::connect()
402 public function connect(
403 array $configOptions = null,
407 global $sugar_config;
409 if(is_null($configOptions))
410 $configOptions = $sugar_config['dbconfig'];
412 if ($sugar_config['dbconfigoption']['persistent'] == true) {
413 $this->database = @mysql_pconnect(
414 $configOptions['db_host_name'],
415 $configOptions['db_user_name'],
416 $configOptions['db_password']
420 if (!$this->database) {
421 $this->database = mysql_connect(
422 $configOptions['db_host_name'],
423 $configOptions['db_user_name'],
424 $configOptions['db_password']
426 if(empty($this->database)) {
427 $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].":".mysql_error());
428 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
430 // Do not pass connection information because we have not connected yet
431 if($this->database && $sugar_config['dbconfigoption']['persistent'] == true){
432 $_SESSION['administrator_error'] = "<b>Severe Performance Degradation: Persistent Database Connections "
433 . "not working. Please set \$sugar_config['dbconfigoption']['persistent'] to false "
434 . "in your config.php file</b>";
437 if(!@mysql_select_db($configOptions['db_name'])) {
438 $GLOBALS['log']->fatal( "Unable to select database {$configOptions['db_name']}: " . mysql_error($this->database));
439 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
442 // cn: using direct calls to prevent this from spamming the Logs
443 $charset = "SET CHARACTER SET utf8";
444 if(isset($sugar_config['dbconfigoption']['collation']) && !empty($sugar_config['dbconfigoption']['collation']))
445 $charset .= " COLLATE {$sugar_config['dbconfigoption']['collation']}";
446 mysql_query($charset, $this->database); // no quotes around "[charset]"
447 mysql_query("SET NAMES 'utf8'", $this->database);
449 if($this->checkError('Could Not Connect:', $dieOnError))
450 $GLOBALS['log']->info("connected to db");
452 $GLOBALS['log']->info("Connect:".$this->database);
456 * @see DBManager::repairTableParams()
458 * For MySQL, we can write the ALTER TABLE statement all in one line, which speeds things
459 * up quite a bit. So here, we'll parse the returned SQL into a single ALTER TABLE command.
461 public function repairTableParams(
469 $sql = parent::repairTableParams($tablename,$fielddefs,$indices,false,$engine);
474 if ( stristr($sql,'create table') )
477 $msg = "Error creating table: ".$tablename. ":";
478 $this->query($sql,true,$msg);
483 // first, parse out all the comments
485 preg_match_all("!/\*.*?\*/!is", $sql, $match);
486 $commentBlocks = $match[0];
487 $sql = preg_replace("!/\*.*?\*/!is",'', $sql);
489 // now, we should only have alter table statements
490 // let's replace the 'alter table name' part with a comma
491 $sql = preg_replace("!alter table $tablename!is",', ', $sql);
493 // re-add it at the beginning
494 $sql = substr_replace($sql,'',strpos($sql,','),1);
495 $sql = str_replace(";","",$sql);
496 $sql = str_replace("\n","",$sql);
497 $sql = "ALTER TABLE $tablename $sql";
500 $this->query($sql,'Error with MySQL repair table');
502 // and re-add the comments at the beginning
503 $sql = implode("\n",$commentBlocks) . "\n". $sql . "\n";
509 * @see DBManager::convert()
511 public function convert(
514 array $additional_parameters = array(),
515 array $additional_parameters_oracle_only = array()
518 // convert the parameters array into a comma delimited string
519 $additional_parameters_string = '';
520 if (!empty($additional_parameters))
521 $additional_parameters_string = ','.implode(',',$additional_parameters);
524 case 'today': return "CURDATE()";
525 case 'left': return "LEFT($string".$additional_parameters_string.")";
526 case 'date_format': return "DATE_FORMAT($string".$additional_parameters_string.")";
527 case 'datetime': return "DATE_FORMAT($string, '%Y-%m-%d %H:%i:%s')";
528 case 'IFNULL': return "IFNULL($string".$additional_parameters_string.")";
529 case 'CONCAT': return "CONCAT($string,".implode(",",$additional_parameters).")";
530 case 'text2char': return "$string";
537 * @see DBManager::concat()
539 public function concat(
546 foreach ( $fields as $index => $field )
548 $ret = "CONCAT(". db_convert($table.".".$field,'IFNULL', array("''"));
550 $ret.= ",' ',".db_convert($table.".".$field,'IFNULL', array("''"));
553 $ret = "TRIM($ret))";