/include/database/MysqlManager.php
PHP | 558 lines | 328 code | 69 blank | 161 comment | 69 complexity | 2fb21197c234df9ffca41ff9ab486a8a MD5 | raw file
Possible License(s): MPL-2.0-no-copyleft-exception, LGPL-2.1, BSD-3-Clause, AGPL-3.0
- <?php
- if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
- /*********************************************************************************
- * SugarCRM Community Edition is a customer relationship management program developed by
- * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
- *
- * This program is free software; you can redistribute it and/or modify it under
- * the terms of the GNU Affero General Public License version 3 as published by the
- * Free Software Foundation with the addition of the following permission added
- * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
- * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
- * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
- *
- * This program is distributed in the hope that it will be useful, but WITHOUT
- * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
- * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
- * details.
- *
- * You should have received a copy of the GNU Affero General Public License along with
- * this program; if not, see http://www.gnu.org/licenses or write to the Free
- * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
- * 02110-1301 USA.
- *
- * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
- * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
- *
- * The interactive user interfaces in modified source and object code versions
- * of this program must display Appropriate Legal Notices, as required under
- * Section 5 of the GNU Affero General Public License version 3.
- *
- * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
- * these Appropriate Legal Notices must retain the display of the "Powered by
- * SugarCRM" logo. If the display of the logo is not reasonably feasible for
- * technical reasons, the Appropriate Legal Notices must display the words
- * "Powered by SugarCRM".
- ********************************************************************************/
- /*********************************************************************************
- * Description: This file handles the Data base functionality for the application.
- * It acts as the DB abstraction layer for the application. It depends on helper classes
- * which generate the necessary SQL. This sql is then passed to PEAR DB classes.
- * The helper class is chosen in DBManagerFactory, which is driven by 'db_type' in 'dbconfig' under config.php.
- *
- * All the functions in this class will work with any bean which implements the meta interface.
- * The passed bean is passed to helper class which uses these functions to generate correct sql.
- *
- * The meta interface has the following functions:
- * getTableName() Returns table name of the object.
- * getFieldDefinitions() Returns a collection of field definitions in order.
- * getFieldDefintion(name) Return field definition for the field.
- * getFieldValue(name) Returns the value of the field identified by name.
- * If the field is not set, the function will return boolean FALSE.
- * getPrimaryFieldDefinition() Returns the field definition for primary key
- *
- * The field definition is an array with the following keys:
- *
- * name This represents name of the field. This is a required field.
- * type This represents type of the field. This is a required field and valid values are:
- * � int
- * � long
- * � varchar
- * � text
- * � date
- * � datetime
- * � double
- * � float
- * � uint
- * � ulong
- * � time
- * � short
- * � enum
- * length This is used only when the type is varchar and denotes the length of the string.
- * The max value is 255.
- * enumvals This is a list of valid values for an enum separated by "|".
- * It is used only if the type is �enum�;
- * required This field dictates whether it is a required value.
- * The default value is �FALSE�.
- * isPrimary This field identifies the primary key of the table.
- * If none of the fields have this flag set to �TRUE�,
- * the first field definition is assume to be the primary key.
- * Default value for this field is �FALSE�.
- * default This field sets the default value for the field definition.
- *
- *
- * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
- * All Rights Reserved.
- * Contributor(s): ______________________________________..
- ********************************************************************************/
- //Technically we can port all the functions in the latest bean to this file
- // that is what PEAR is doing anyways.
- class MysqlManager extends DBManager
- {
- /**
- * @see DBManager::$dbType
- */
- public $dbType = 'mysql';
- /**
- * @see DBManager::$backendFunctions
- */
- protected $backendFunctions = array(
- 'free_result' => 'mysql_free_result',
- 'close' => 'mysql_close',
- 'row_count' => 'mysql_num_rows',
- 'affected_row_count' => 'mysql_affected_rows',
- );
- /**
- * @see DBManager::checkError()
- */
- public function checkError(
- $msg = '',
- $dieOnError = false
- )
- {
- if (parent::checkError($msg, $dieOnError))
- return true;
- if (mysql_errno($this->getDatabase())) {
- if ($this->dieOnError || $dieOnError){
- $GLOBALS['log']->fatal("MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database));
- sugar_die ($GLOBALS['app_strings']['ERR_DB_FAIL']);
- }
- else {
- $this->last_error = $msg."MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database);
- $GLOBALS['log']->error("MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database));
- }
- return true;
- }
- return false;
- }
- /**
- * Parses and runs queries
- *
- * @param string $sql SQL Statement to execute
- * @param bool $dieOnError True if we want to call die if the query returns errors
- * @param string $msg Message to log if error occurs
- * @param bool $suppress Flag to suppress all error output unless in debug logging mode.
- * @param bool $autofree True if we want to push this result into the $lastResult array.
- * @return resource result set
- */
- public function query(
- $sql,
- $dieOnError = false,
- $msg = '',
- $suppress = false,
- $autofree = false
- )
- {
- parent::countQuery($sql);
- $GLOBALS['log']->info('Query:' . $sql);
- $this->checkConnection();
- //$this->freeResult();
- $this->query_time = microtime(true);
- $this->lastsql = $sql;
- if ($suppress==true) {
- }
- else {
- $result = mysql_query($sql, $this->database);
- }
- $this->lastmysqlrow = -1;
- $this->query_time = microtime(true) - $this->query_time;
- $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
- $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
- if($autofree)
- $this->lastResult[] =& $result;
- return $result;
- }
- /**
- * @see DBManager::limitQuery()
- */
- public function limitQuery(
- $sql,
- $start,
- $count,
- $dieOnError = false,
- $msg = '')
- {
- if ($start < 0)
- $start = 0;
- $GLOBALS['log']->debug('Limit Query:' . $sql. ' Start: ' .$start . ' count: ' . $count);
- $sql = "$sql LIMIT $start,$count";
- $this->lastsql = $sql;
- if(!empty($GLOBALS['sugar_config']['check_query'])){
- $this->checkQuery($sql);
- }
- return $this->query($sql, $dieOnError, $msg);
- }
- /**
- * @see DBManager::checkQuery()
- */
- protected function checkQuery(
- $sql
- )
- {
- $result = $this->query('EXPLAIN ' . $sql);
- $badQuery = array();
- while ($row = $this->fetchByAssoc($result)) {
- if (empty($row['table']))
- continue;
- $badQuery[$row['table']] = '';
- if (strtoupper($row['type']) == 'ALL')
- $badQuery[$row['table']] .= ' Full Table Scan;';
- if (empty($row['key']))
- $badQuery[$row['table']] .= ' No Index Key Used;';
- if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using filesort') > 0)
- $badQuery[$row['table']] .= ' Using FileSort;';
- if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using temporary') > 0)
- $badQuery[$row['table']] .= ' Using Temporary Table;';
- }
- if ( empty($badQuery) )
- return true;
- foreach($badQuery as $table=>$data ){
- if(!empty($data)){
- $warning = ' Table:' . $table . ' Data:' . $data;
- if(!empty($GLOBALS['sugar_config']['check_query_log'])){
- $GLOBALS['log']->fatal($sql);
- $GLOBALS['log']->fatal('CHECK QUERY:' .$warning);
- }
- else{
- $GLOBALS['log']->warn('CHECK QUERY:' .$warning);
- }
- }
- }
- return false;
- }
- /**
- * @see DBManager::describeField()
- */
- protected function describeField(
- $name,
- $tablename
- )
- {
- global $table_descriptions;
- if(isset($table_descriptions[$tablename])
- && isset($table_descriptions[$tablename][$name]))
- return $table_descriptions[$tablename][$name];
- $table_descriptions[$tablename] = array();
- $sql = "DESCRIBE $tablename";
- $result = $this->query($sql);
- while ($row = $this->fetchByAssoc($result) ){
- $table_descriptions[$tablename][$row['Field']] = $row;
- if(empty($table_descriptions[$tablename][$row['Field']]['Null']))
- $table_descriptions[$tablename][$row['Field']]['Null'] = 'NO';
- }
- if(isset($table_descriptions[$tablename][$name]))
- return $table_descriptions[$tablename][$name];
- return array();
- }
- /**
- * @see DBManager::getFieldsArray()
- */
- public function getFieldsArray(
- &$result,
- $make_lower_case=false)
- {
- $field_array = array();
- if(! isset($result) || empty($result))
- return 0;
- $i = 0;
- while ($i < mysql_num_fields($result)) {
- $meta = mysql_fetch_field($result, $i);
- if (!$meta)
- return 0;
- if($make_lower_case == true)
- $meta->name = strtolower($meta->name);
- $field_array[] = $meta->name;
- $i++;
- }
- return $field_array;
- }
- /**
- * @see DBManager::fetchByAssoc()
- */
- public function fetchByAssoc(
- &$result,
- $rowNum = -1,
- $encode = true
- )
- {
- if (!$result)
- return false;
- if ($result && $rowNum > -1){
- if ($this->getRowCount($result) > $rowNum)
- mysql_data_seek($result, $rowNum);
- $this->lastmysqlrow = $rowNum;
- }
- $row = mysql_fetch_assoc($result);
- if ($encode && $this->encode && is_array($row))
- return array_map('to_html', $row);
- return $row;
- }
- /**
- * @see DBManager::getTablesArray()
- */
- public function getTablesArray()
- {
- global $sugar_config;
- $GLOBALS['log']->debug('Fetching table list');
- if ($this->getDatabase()) {
- $tables = array();
- $r = $this->query('SHOW TABLES');
- if (is_resource($r) || $r instanceOf mysqli_result ) {
- while ($a = $this->fetchByAssoc($r)) {
- $row = array_values($a);
- $tables[]=$row[0];
- }
- return $tables;
- }
- }
- return false; // no database available
- }
- /**
- * @see DBManager::version()
- */
- public function version()
- {
- return $this->getOne("SELECT version() version");
- }
- /**
- * @see DBManager::tableExists()
- */
- public function tableExists(
- $tableName
- )
- {
- $GLOBALS['log']->info("tableExists: $tableName");
- if ($this->getDatabase()) {
- $result = $this->query("SHOW TABLES LIKE '".$tableName."'");
- return ($this->getRowCount($result) == 0) ? false : true;
- }
- return false;
- }
- /**
- * @see DBManager::quote()
- */
- public function quote(
- $string,
- $isLike = true
- )
- {
- return mysql_real_escape_string(parent::quote($string), $this->getDatabase());
- }
- /**
- * @see DBManager::quoteForEmail()
- */
- public function quoteForEmail(
- $string,
- $isLike = true
- )
- {
- return mysql_real_escape_string($string, $this->getDatabase());
- }
- /**
- * @see DBManager::connect()
- */
- public function connect(
- array $configOptions = null,
- $dieOnError = false
- )
- {
- global $sugar_config;
- if(is_null($configOptions))
- $configOptions = $sugar_config['dbconfig'];
- if ($sugar_config['dbconfigoption']['persistent'] == true) {
- $this->database = @mysql_pconnect(
- $configOptions['db_host_name'],
- $configOptions['db_user_name'],
- $configOptions['db_password']
- );
- }
- if (!$this->database) {
- $this->database = mysql_connect(
- $configOptions['db_host_name'],
- $configOptions['db_user_name'],
- $configOptions['db_password']
- );
- if(empty($this->database)) {
- $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].":".mysql_error());
- sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
- }
- // Do not pass connection information because we have not connected yet
- if($this->database && $sugar_config['dbconfigoption']['persistent'] == true){
- $_SESSION['administrator_error'] = "<b>Severe Performance Degradation: Persistent Database Connections "
- . "not working. Please set \$sugar_config['dbconfigoption']['persistent'] to false "
- . "in your config.php file</b>";
- }
- }
- if(!@mysql_select_db($configOptions['db_name'])) {
- $GLOBALS['log']->fatal( "Unable to select database {$configOptions['db_name']}: " . mysql_error($this->database));
- sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
- }
- // cn: using direct calls to prevent this from spamming the Logs
- $charset = "SET CHARACTER SET utf8";
- if(isset($sugar_config['dbconfigoption']['collation']) && !empty($sugar_config['dbconfigoption']['collation']))
- $charset .= " COLLATE {$sugar_config['dbconfigoption']['collation']}";
- mysql_query($charset, $this->database); // no quotes around "[charset]"
- mysql_query("SET NAMES 'utf8'", $this->database);
- if($this->checkError('Could Not Connect:', $dieOnError))
- $GLOBALS['log']->info("connected to db");
- $GLOBALS['log']->info("Connect:".$this->database);
- }
- /**
- * @see DBManager::repairTableParams()
- *
- * For MySQL, we can write the ALTER TABLE statement all in one line, which speeds things
- * up quite a bit. So here, we'll parse the returned SQL into a single ALTER TABLE command.
- */
- public function repairTableParams(
- $tablename,
- $fielddefs,
- $indices,
- $execute = true,
- $engine = null
- )
- {
- $sql = parent::repairTableParams($tablename,$fielddefs,$indices,false,$engine);
- if ( $sql == '' )
- return '';
- if ( stristr($sql,'create table') )
- {
- if ($execute) {
- $msg = "Error creating table: ".$tablename. ":";
- $this->query($sql,true,$msg);
- }
- return $sql;
- }
- // first, parse out all the comments
- $match = array();
- preg_match_all("!/\*.*?\*/!is", $sql, $match);
- $commentBlocks = $match[0];
- $sql = preg_replace("!/\*.*?\*/!is",'', $sql);
- // now, we should only have alter table statements
- // let's replace the 'alter table name' part with a comma
- $sql = preg_replace("!alter table $tablename!is",', ', $sql);
- // re-add it at the beginning
- $sql = substr_replace($sql,'',strpos($sql,','),1);
- $sql = str_replace(";","",$sql);
- $sql = str_replace("\n","",$sql);
- $sql = "ALTER TABLE $tablename $sql";
- if ( $execute )
- $this->query($sql,'Error with MySQL repair table');
- // and re-add the comments at the beginning
- $sql = implode("\n",$commentBlocks) . "\n". $sql . "\n";
- return $sql;
- }
- /**
- * @see DBManager::convert()
- */
- public function convert(
- $string,
- $type,
- array $additional_parameters = array(),
- array $additional_parameters_oracle_only = array()
- )
- {
- // convert the parameters array into a comma delimited string
- $additional_parameters_string = '';
- if (!empty($additional_parameters))
- $additional_parameters_string = ','.implode(',',$additional_parameters);
- switch ($type) {
- case 'today': return "CURDATE()";
- case 'left': return "LEFT($string".$additional_parameters_string.")";
- case 'date_format': return "DATE_FORMAT($string".$additional_parameters_string.")";
- case 'datetime': return "DATE_FORMAT($string, '%Y-%m-%d %H:%i:%s')";
- case 'IFNULL': return "IFNULL($string".$additional_parameters_string.")";
- case 'CONCAT': return "CONCAT($string,".implode(",",$additional_parameters).")";
- case 'text2char': return "$string";
- }
- return "$string";
- }
- /**
- * @see DBManager::concat()
- */
- public function concat(
- $table,
- array $fields
- )
- {
- $ret = '';
- foreach ( $fields as $index => $field )
- if (empty($ret))
- $ret = "CONCAT(". db_convert($table.".".$field,'IFNULL', array("''"));
- else
- $ret.= ",' ',".db_convert($table.".".$field,'IFNULL', array("''"));
- if (!empty($ret)) {
- $ret = "TRIM($ret))";
- }
- return $ret;
- }
- }