/include/database/DBManager.php
PHP | 3829 lines | 1910 code | 339 blank | 1580 comment | 518 complexity | 9a60fc7b5ac377121f8817b617c566e9 MD5 | raw file
Possible License(s): LGPL-2.1, MPL-2.0-no-copyleft-exception, BSD-3-Clause
Large files files are truncated, but you can click here to view the full file
- <?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-2012 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): ______________________________________..
- ********************************************************************************/
- /**
- * Base database driver implementation
- * @api
- */
- abstract class DBManager
- {
- /**
- * Name of database
- * @var resource
- */
- public $database = null;
- /**
- * Indicates whether we should die when we get an error from the DB
- */
- protected $dieOnError = false;
- /**
- * Indicates whether we should html encode the results from a query by default
- */
- protected $encode = true;
- /**
- * Records the execution time of the last query
- */
- protected $query_time = 0;
- /**
- * Last error message from the DB backend
- */
- protected $last_error = false;
- /**
- * Registry of available result sets
- */
- protected $lastResult;
- /**
- * Current query count
- */
- private static $queryCount = 0;
- /**
- * Query threshold limit
- */
- private static $queryLimit = 0;
- /**
- * Array of prepared statements and their correspoding parsed tokens
- */
- protected $preparedTokens = array();
- /**
- * TimeDate instance
- * @var TimeDate
- */
- protected $timedate;
- /**
- * PHP Logger
- * @var Logger
- */
- protected $log;
- /**
- * Table descriptions
- * @var array
- */
- protected static $table_descriptions = array();
- /**
- * Index descriptions
- * @var array
- */
- protected static $index_descriptions = array();
- /**
- * Maximum length of identifiers
- * @abstract
- * @var array
- */
- protected $maxNameLengths = array(
- 'table' => 64,
- 'column' => 64,
- 'index' => 64,
- 'alias' => 64
- );
- /**
- * DB driver priority
- * Higher priority drivers override lower priority ones
- * @var int
- */
- public $priority = 0;
- /**
- * Driver name label, for install
- * @absrtact
- * @var string
- */
- public $label = '';
- /**
- * Type names map
- * @abstract
- * @var array
- */
- protected $type_map = array();
- /**
- * Type classification into:
- * - int
- * - bool
- * - float
- * - date
- * @abstract
- * @var array
- */
- protected $type_class = array(
- 'int' => 'int',
- 'double' => 'float',
- 'float' => 'float',
- 'uint' => 'int',
- 'ulong' => 'bigint',
- 'long' => 'bigint',
- 'short' => 'int',
- 'date' => 'date',
- 'datetime' => 'date',
- 'datetimecombo' => 'date',
- 'time' => 'time',
- 'bool' => 'bool',
- 'tinyint' => 'int',
- 'currency' => 'float',
- 'decimal' => 'float',
- 'decimal2' => 'float',
- );
- /**
- * Capabilities this DB supports. Supported list:
- * affected_rows Can report query affected rows for UPDATE/DELETE
- * implement getAffectedRowCount()
- * select_rows Can report row count for SELECT
- * implement getRowCount()
- * case_sensitive Supports case-sensitive text columns
- * fulltext Supports fulltext search indexes
- * inline_keys Supports defining keys together with the table
- * auto_increment_sequence Autoincrement support implemented as sequence
- * limit_subquery Supports LIMIT clauses in subqueries
- * create_user Can create users for Sugar
- * create_db Can create databases
- * collation Supports setting collations
- * disable_keys Supports temporarily disabling keys (for upgrades, etc.)
- *
- * @abstract
- * Special cases:
- * fix:expandDatabase - needs expandDatabase fix, see expandDatabase.php
- * TODO: verify if we need these cases
- */
- protected $capabilities = array();
- /**
- * Database options
- * @var array
- */
- protected $options = array();
- /**
- * Create DB Driver
- */
- public function __construct()
- {
- $this->timedate = TimeDate::getInstance();
- $this->log = $GLOBALS['log'];
- $this->helper = $this; // compatibility
- }
- /**
- * Wrapper for those trying to access the private and protected class members directly
- * @param string $p var name
- * @return mixed
- */
- public function __get($p)
- {
- $this->log->info('Call to DBManager::$'.$p.' is deprecated');
- return $this->$p;
- }
- /**
- * Returns the current database handle
- * @return resource
- */
- public function getDatabase()
- {
- $this->checkConnection();
- return $this->database;
- }
- /**
- * Returns this instance's DBHelper
- * Actually now returns $this
- * @deprecated
- * @return DBManager
- */
- public function getHelper()
- {
- return $this;
- }
- /**
- * Checks for error happening in the database
- *
- * @param string $msg message to prepend to the error message
- * @param bool $dieOnError true if we want to die immediately on error
- * @return bool True if there was an error
- */
- public function checkError($msg = '', $dieOnError = false)
- {
- if (empty($this->database)) {
- $this->registerError($msg, "Database Is Not Connected", $dieOnError);
- return true;
- }
- $dberror = $this->lastDbError();
- if($dberror === false) {
- $this->last_error = false;
- return false;
- }
- $this->registerError($msg, $dberror, $dieOnError);
- return true;
- }
- /**
- * Register database error
- * If die-on-error flag is set, logs the message and dies,
- * otherwise sets last_error to the message
- * @param string $userMessage Message from function user
- * @param string $message Message from SQL driver
- * @param bool $dieOnError
- */
- protected function registerError($userMessage, $message, $dieOnError = false)
- {
- if(!empty($message)) {
- if(!empty($userMessage)) {
- $message = "$userMessage: $message";
- }
- if(empty($message)) {
- $message = "Database error";
- }
- $this->log->fatal($message);
- if ($dieOnError || $this->dieOnError) {
- if(isset($GLOBALS['app_strings']['ERR_DB_FAIL'])) {
- sugar_die($GLOBALS['app_strings']['ERR_DB_FAIL']);
- } else {
- sugar_die("Database error. Please check sugarcrm.log for details.");
- }
- } else {
- $this->last_error = $message;
- }
- }
- }
- /**
- * Return DB error message for the last query executed
- * @return string Last error message
- */
- public function lastError()
- {
- return $this->last_error;
- }
- /**
- * This method is called by every method that runs a query.
- * If slow query dumping is turned on and the query time is beyond
- * the time limit, we will log the query. This function may do
- * additional reporting or log in a different area in the future.
- *
- * @param string $query query to log
- * @return boolean true if the query was logged, false otherwise
- */
- protected function dump_slow_queries($query)
- {
- global $sugar_config;
- $do_the_dump = isset($sugar_config['dump_slow_queries'])
- ? $sugar_config['dump_slow_queries'] : false;
- $slow_query_time_msec = isset($sugar_config['slow_query_time_msec'])
- ? $sugar_config['slow_query_time_msec'] : 5000;
- if($do_the_dump) {
- if($slow_query_time_msec < ($this->query_time * 1000)) {
- // Then log both the query and the query time
- $this->log->fatal('Slow Query (time:'.$this->query_time."\n".$query);
- return true;
- }
- }
- return false;
- }
- /**
- * Scans order by to ensure that any field being ordered by is.
- *
- * It will throw a warning error to the log file - fatal if slow query logging is enabled
- *
- * @param string $sql query to be run
- * @param bool $object_name optional, object to look up indices in
- * @return bool true if an index is found false otherwise
- */
- protected function checkQuery($sql, $object_name = false)
- {
- $match = array();
- preg_match_all("'.* FROM ([^ ]*).* ORDER BY (.*)'is", $sql, $match);
- $indices = false;
- if (!empty($match[1][0]))
- $table = $match[1][0];
- else
- return false;
- if (!empty($object_name) && !empty($GLOBALS['dictionary'][$object_name]))
- $indices = $GLOBALS['dictionary'][$object_name]['indices'];
- if (empty($indices)) {
- foreach ( $GLOBALS['dictionary'] as $current ) {
- if ($current['table'] == $table){
- $indices = $current['indices'];
- break;
- }
- }
- }
- if (empty($indices)) {
- $this->log->warn('CHECK QUERY: Could not find index definitions for table ' . $table);
- return false;
- }
- if (!empty($match[2][0])) {
- $orderBys = explode(' ', $match[2][0]);
- foreach ($orderBys as $orderBy){
- $orderBy = trim($orderBy);
- if (empty($orderBy))
- continue;
- $orderBy = strtolower($orderBy);
- if ($orderBy == 'asc' || $orderBy == 'desc')
- continue;
- $orderBy = str_replace(array($table . '.', ','), '', $orderBy);
- foreach ($indices as $index)
- if (empty($index['db']) || $index['db'] == $this->dbType)
- foreach ($index['fields'] as $field)
- if ($field == $orderBy)
- return true;
- $warning = 'Missing Index For Order By Table: ' . $table . ' Order By:' . $orderBy ;
- if (!empty($GLOBALS['sugar_config']['dump_slow_queries']))
- $this->log->fatal('CHECK QUERY:' .$warning);
- else
- $this->log->warn('CHECK QUERY:' .$warning);
- }
- }
- return false;
- }
- /**
- * Returns the time the last query took to execute
- *
- * @return int
- */
- public function getQueryTime()
- {
- return $this->query_time;
- }
- /**
- * Checks the current connection; if it is not connected then reconnect
- */
- public function checkConnection()
- {
- $this->last_error = '';
- if (!isset($this->database))
- $this->connect();
- }
- /**
- * Sets the dieOnError value
- *
- * @param bool $value
- */
- public function setDieOnError($value)
- {
- $this->dieOnError = $value;
- }
- /**
- * Implements a generic insert for any bean.
- *
- * @param SugarBean $bean SugarBean instance
- * @return bool query result
- *
- */
- public function insert(SugarBean $bean)
- {
- $sql = $this->insertSQL($bean);
- $tablename = $bean->getTableName();
- $msg = "Error inserting into table: $tablename:";
- return $this->query($sql,true,$msg);
- }
- /**
- * Insert data into table by parameter definition
- * @param string $table Table name
- * @param array $field_defs Definitions in vardef-like format
- * @param array $data Key/value to insert
- * @param array $field_map Fields map from SugarBean
- * @param bool $execute Execute or return query?
- * @return bool query result
- */
- public function insertParams($table, $field_defs, $data, $field_map = null, $execute = true)
- {
- $values = array();
- foreach ($field_defs as $field => $fieldDef)
- {
- if (isset($fieldDef['source']) && $fieldDef['source'] != 'db') continue;
- //custom fields handle there save seperatley
- if(!empty($field_map) && !empty($field_map[$field]['custom_type'])) continue;
- if(isset($data[$field])) {
- // clean the incoming value..
- $val = from_html($data[$field]);
- } else {
- if(isset($fieldDef['default']) && strlen($fieldDef['default']) > 0) {
- $val = $fieldDef['default'];
- } else {
- $val = null;
- }
- }
- //handle auto increment values here - we may have to do something like nextval for oracle
- if (!empty($fieldDef['auto_increment'])) {
- $auto = $this->getAutoIncrementSQL($table, $fieldDef['name']);
- if(!empty($auto)) {
- $values[$field] = $auto;
- }
- } elseif ($fieldDef['name'] == 'deleted') {
- $values['deleted'] = (int)$val;
- } else {
- // need to do some thing about types of values
- if(!is_null($val) || !empty($fieldDef['required'])) {
- $values[$field] = $this->massageValue($val, $fieldDef);
- }
- }
- }
- if (empty($values))
- return $execute?true:''; // no columns set
- // get the entire sql
- $query = "INSERT INTO $table (".implode(",", array_keys($values)).")
- VALUES (".implode(",", $values).")";
- return $execute?$this->query($query):$query;
- }
- /**
- * Implements a generic update for any bean
- *
- * @param SugarBean $bean Sugarbean instance
- * @param array $where values with the keys as names of fields.
- * If we want to pass multiple values for a name, pass it as an array
- * If where is not passed, it defaults to id of table
- * @return bool query result
- *
- */
- public function update(SugarBean $bean, array $where = array())
- {
- $sql = $this->updateSQL($bean, $where);
- $tablename = $bean->getTableName();
- $msg = "Error updating table: $tablename:";
- return $this->query($sql,true,$msg);
- }
- /**
- * Implements a generic delete for any bean identified by id
- *
- * @param SugarBean $bean Sugarbean instance
- * @param array $where values with the keys as names of fields.
- * If we want to pass multiple values for a name, pass it as an array
- * If where is not passed, it defaults to id of table
- * @return bool query result
- */
- public function delete(SugarBean $bean, array $where = array())
- {
- $sql = $this->deleteSQL($bean, $where);
- $tableName = $bean->getTableName();
- $msg = "Error deleting from table: ".$tableName. ":";
- return $this->query($sql,true,$msg);
- }
- /**
- * Implements a generic retrieve for any bean identified by id
- *
- * If we want to pass multiple values for a name, pass it as an array
- * If where is not passed, it defaults to id of table
- *
- * @param SugarBean $bean Sugarbean instance
- * @param array $where values with the keys as names of fields.
- * @return resource result from the query
- */
- public function retrieve(SugarBean $bean, array $where = array())
- {
- $sql = $this->retrieveSQL($bean, $where);
- $tableName = $bean->getTableName();
- $msg = "Error retriving values from table:".$tableName. ":";
- return $this->query($sql,true,$msg);
- }
- /**
- * Implements a generic retrieve for a collection of beans.
- *
- * These beans will be joined in the sql by the key attribute of field defs.
- * Currently, this function does support outer joins.
- *
- * @param array $beans Sugarbean instance(s)
- * @param array $cols columns to be returned with the keys as names of bean as identified by
- * get_class of bean. Values of this array is the array of fieldDefs to be returned for a bean.
- * If an empty array is passed, all columns are selected.
- * @param array $where values with the keys as names of bean as identified by get_class of bean
- * Each value at the first level is an array of values for that bean identified by name of fields.
- * If we want to pass multiple values for a name, pass it as an array
- * If where is not passed, all the rows will be returned.
- * @return resource
- */
- public function retrieveView(array $beans, array $cols = array(), array $where = array())
- {
- $sql = $this->retrieveViewSQL($beans, $cols, $where);
- $msg = "Error retriving values from View Collection:";
- return $this->query($sql,true,$msg);
- }
- /**
- * Implements creation of a db table for a bean.
- *
- * NOTE: does not handle out-of-table constraints, use createConstraintSQL for that
- * @param SugarBean $bean Sugarbean instance
- */
- public function createTable(SugarBean $bean)
- {
- $sql = $this->createTableSQL($bean);
- $tablename = $bean->getTableName();
- $msg = "Error creating table: $tablename:";
- $this->query($sql,true,$msg);
- if(!$this->supports("inline_keys")) {
- // handle constraints and indices
- $indicesArr = $this->createConstraintSql($bean);
- if (count($indicesArr) > 0)
- foreach ($indicesArr as $indexSql)
- $this->query($indexSql, true, $msg);
- }
- }
- /**
- * returns SQL to create constraints or indices
- *
- * @param SugarBean $bean SugarBean instance
- * @return array list of SQL statements
- */
- protected function createConstraintSql(SugarBean $bean)
- {
- return $this->getConstraintSql($bean->getIndices(), $bean->getTableName());
- }
- /**
- * Implements creation of a db table
- *
- * @param string $tablename
- * @param array $fieldDefs Field definitions, in vardef format
- * @param array $indices Index definitions, in vardef format
- * @param string $engine Engine parameter, used for MySQL engine so far
- * @todo: refactor engine param to be more generic
- * @return bool success value
- */
- public function createTableParams($tablename, $fieldDefs, $indices, $engine = null)
- {
- if (!empty($fieldDefs)) {
- $sql = $this->createTableSQLParams($tablename, $fieldDefs, $indices, $engine);
- $res = true;
- if ($sql) {
- $msg = "Error creating table: $tablename";
- $res = ($res and $this->query($sql,true,$msg));
- }
- if(!$this->supports("inline_keys")) {
- // handle constraints and indices
- $indicesArr = $this->getConstraintSql($indices, $tablename);
- if (count($indicesArr) > 0)
- foreach ($indicesArr as $indexSql)
- $res = ($res and $this->query($indexSql, true, "Error creating indexes"));
- }
- return $res;
- }
- return false;
- }
- /**
- * Implements repair of a db table for a bean.
- *
- * @param SugarBean $bean SugarBean instance
- * @param bool $execute true if we want the action to take place, false if we just want the sql returned
- * @return string SQL statement or empty string, depending upon $execute
- */
- public function repairTable(SugarBean $bean, $execute = true)
- {
- $indices = $bean->getIndices();
- $fielddefs = $bean->getFieldDefinitions();
- $tablename = $bean->getTableName();
- //Clean the indexes to prevent duplicate definitions
- $new_index = array();
- foreach($indices as $ind_def){
- $new_index[$ind_def['name']] = $ind_def;
- }
- //jc: added this for beans that do not actually have a table, namely
- //ForecastOpportunities
- if($tablename == 'does_not_exist' || $tablename == '')
- return '';
- global $dictionary;
- $engine=null;
- if (isset($dictionary[$bean->getObjectName()]['engine']) && !empty($dictionary[$bean->getObjectName()]['engine']) )
- $engine = $dictionary[$bean->getObjectName()]['engine'];
- return $this->repairTableParams($tablename, $fielddefs,$new_index,$execute,$engine);
- }
- /**
- * Can this field be null?
- * Auto-increment and ID fields can not be null
- * @param array $vardef
- * @return bool
- */
- protected function isNullable($vardef)
- {
- if(isset($vardef['isnull']) && (strtolower($vardef['isnull']) == 'false' || $vardef['isnull'] === false)
- && !empty($vardef['required'])) {
- /* required + is_null=false => not null */
- return false;
- }
- if(empty($vardef['auto_increment']) && (empty($vardef['type']) || $vardef['type'] != 'id')
- && (empty($vardef['dbType']) || $vardef['dbType'] != 'id')
- && (empty($vardef['name']) || ($vardef['name'] != 'id' && $vardef['name'] != 'deleted'))
- ) {
- return true;
- }
- return false;
- }
- /**
- * Builds the SQL commands that repair a table structure
- *
- * @param string $tablename
- * @param array $fielddefs Field definitions, in vardef format
- * @param array $indices Index definitions, in vardef format
- * @param bool $execute optional, true if we want the queries executed instead of returned
- * @param string $engine optional, MySQL engine
- * @todo: refactor engine param to be more generic
- * @return string
- */
- public function repairTableParams($tablename, $fielddefs, $indices, $execute = true, $engine = null)
- {
- //jc: had a bug when running the repair if the tablename is blank the repair will
- //fail when it tries to create a repair table
- if ($tablename == '' || empty($fielddefs))
- return '';
- //if the table does not exist create it and we are done
- $sql = "/* Table : $tablename */\n";
- if (!$this->tableExists($tablename)) {
- $createtablesql = $this->createTableSQLParams($tablename,$fielddefs,$indices,$engine);
- if($execute && $createtablesql){
- $this->createTableParams($tablename,$fielddefs,$indices,$engine);
- }
- $sql .= "/* MISSING TABLE: {$tablename} */\n";
- $sql .= $createtablesql . "\n";
- return $sql;
- }
- $compareFieldDefs = $this->get_columns($tablename);
- $compareIndices = $this->get_indices($tablename);
- $take_action = false;
- // do column comparisons
- $sql .= "/*COLUMNS*/\n";
- foreach ($fielddefs as $name => $value) {
- if (isset($value['source']) && $value['source'] != 'db')
- continue;
- // Bug #42406. Skipping breaked vardef without type or name
- if (isset($value['name']) == false || $value['name'] == false)
- {
- $sql .= "/* NAME IS MISSING IN VARDEF $tablename::$name */\n";
- continue;
- }
- else if (isset($value['type']) == false || $value['type'] == false)
- {
- $sql .= "/* TYPE IS MISSING IN VARDEF $tablename::$name */\n";
- continue;
- }
- $name = strtolower($value['name']);
- // add or fix the field defs per what the DB is expected to give us back
- $this->massageFieldDef($value,$tablename);
- $ignorerequired=false;
- //Do not track requiredness in the DB, auto_increment, ID,
- // and deleted fields are always required in the DB, so don't force those
- if ($this->isNullable($value)) {
- $value['required'] = false;
- }
- //Should match the conditions in DBManager::oneColumnSQLRep for DB required fields, type='id' fields will sometimes
- //come into this function as 'type' = 'char', 'dbType' = 'id' without required set in $value. Assume they are correct and leave them alone.
- else if (($name == 'id' || $value['type'] == 'id' || (isset($value['dbType']) && $value['dbType'] == 'id'))
- && (!isset($value['required']) && isset($compareFieldDefs[$name]['required'])))
- {
- $value['required'] = $compareFieldDefs[$name]['required'];
- }
- if ( !isset($compareFieldDefs[$name]) ) {
- // ok we need this field lets create it
- $sql .= "/*MISSING IN DATABASE - $name - ROW*/\n";
- $sql .= $this->addColumnSQL($tablename, $value) . "\n";
- if ($execute)
- $this->addColumn($tablename, $value);
- $take_action = true;
- } elseif ( !$this->compareVarDefs($compareFieldDefs[$name],$value)) {
- //fields are different lets alter it
- $sql .= "/*MISMATCH WITH DATABASE - $name - ROW ";
- foreach($compareFieldDefs[$name] as $rKey => $rValue) {
- $sql .= "[$rKey] => '$rValue' ";
- }
- $sql .= "*/\n";
- $sql .= "/* VARDEF - $name - ROW";
- foreach($value as $rKey => $rValue) {
- $sql .= "[$rKey] => '$rValue' ";
- }
- $sql .= "*/\n";
- //jc: oracle will complain if you try to execute a statement that sets a column to (not) null
- //when it is already (not) null
- if ( isset($value['isnull']) && isset($compareFieldDefs[$name]['isnull']) &&
- $value['isnull'] === $compareFieldDefs[$name]['isnull']) {
- unset($value['required']);
- $ignorerequired=true;
- }
- //dwheeler: Once a column has been defined as null, we cannot try to force it back to !null
- if ((isset($value['required']) && ($value['required'] === true || $value['required'] == 'true' || $value['required'] === 1))
- && (empty($compareFieldDefs[$name]['required']) || $compareFieldDefs[$name]['required'] != 'true'))
- {
- $ignorerequired = true;
- }
- $altersql = $this->alterColumnSQL($tablename, $value,$ignorerequired);
- if(is_array($altersql)) {
- $altersql = join("\n", $altersql);
- }
- $sql .= $altersql . "\n";
- if($execute){
- $this->alterColumn($tablename, $value, $ignorerequired);
- }
- $take_action = true;
- }
- }
- // do index comparisons
- $sql .= "/* INDEXES */\n";
- $correctedIndexs = array();
- $compareIndices_case_insensitive = array();
- // do indices comparisons case-insensitive
- foreach($compareIndices as $k => $value){
- $value['name'] = strtolower($value['name']);
- $compareIndices_case_insensitive[strtolower($k)] = $value;
- }
- $compareIndices = $compareIndices_case_insensitive;
- unset($compareIndices_case_insensitive);
- foreach ($indices as $value) {
- if (isset($value['source']) && $value['source'] != 'db')
- continue;
- $validDBName = $this->getValidDBName($value['name'], true, 'index', true);
- if (isset($compareIndices[$validDBName])) {
- $value['name'] = $validDBName;
- }
- $name = strtolower($value['name']);
- //Don't attempt to fix the same index twice in one pass;
- if (isset($correctedIndexs[$name]))
- continue;
- //don't bother checking primary nothing we can do about them
- if (isset($value['type']) && $value['type'] == 'primary')
- continue;
- //database helpers do not know how to handle full text indices
- if ($value['type']=='fulltext')
- continue;
- if ( in_array($value['type'],array('alternate_key','foreign')) )
- $value['type'] = 'index';
- if ( !isset($compareIndices[$name]) ) {
- //First check if an index exists that doesn't match our name, if so, try to rename it
- $found = false;
- foreach ($compareIndices as $ex_name => $ex_value) {
- if($this->compareVarDefs($ex_value, $value, true)) {
- $found = $ex_name;
- break;
- }
- }
- if ($found) {
- $sql .= "/*MISSNAMED INDEX IN DATABASE - $name - $ex_name */\n";
- $rename = $this->renameIndexDefs($ex_value, $value, $tablename);
- if($execute) {
- $this->query($rename, true, "Cannot rename index");
- }
- $sql .= is_array($rename)?join("\n", $rename). "\n":$rename."\n";
- } else {
- // ok we need this field lets create it
- $sql .= "/*MISSING INDEX IN DATABASE - $name -{$value['type']} ROW */\n";
- $sql .= $this->addIndexes($tablename,array($value), $execute) . "\n";
- }
- $take_action = true;
- $correctedIndexs[$name] = true;
- } elseif ( !$this->compareVarDefs($compareIndices[$name],$value) ) {
- // fields are different lets alter it
- $sql .= "/*INDEX MISMATCH WITH DATABASE - $name - ROW ";
- foreach ($compareIndices[$name] as $n1 => $t1) {
- $sql .= "<$n1>";
- if ( $n1 == 'fields' )
- foreach($t1 as $rKey => $rValue)
- $sql .= "[$rKey] => '$rValue' ";
- else
- $sql .= " $t1 ";
- }
- $sql .= "*/\n";
- $sql .= "/* VARDEF - $name - ROW";
- foreach ($value as $n1 => $t1) {
- $sql .= "<$n1>";
- if ( $n1 == 'fields' )
- foreach ($t1 as $rKey => $rValue)
- $sql .= "[$rKey] => '$rValue' ";
- else
- $sql .= " $t1 ";
- }
- $sql .= "*/\n";
- $sql .= $this->modifyIndexes($tablename,array($value), $execute) . "\n";
- $take_action = true;
- $correctedIndexs[$name] = true;
- }
- }
- return ($take_action === true) ? $sql : '';
- }
- /**
- * Compares two vardefs
- *
- * @param array $fielddef1 This is from the database
- * @param array $fielddef2 This is from the vardef
- * @param bool $ignoreName Ignore name-only differences?
- * @return bool true if they match, false if they don't
- */
- public function compareVarDefs($fielddef1, $fielddef2, $ignoreName = false)
- {
- foreach ( $fielddef1 as $key => $value ) {
- if ( $key == 'name' && ( strtolower($fielddef1[$key]) == strtolower($fielddef2[$key]) || $ignoreName) )
- continue;
- if ( isset($fielddef2[$key]) && $fielddef1[$key] == $fielddef2[$key] )
- continue;
- //Ignore len if its not set in the vardef
- if ($key == 'len' && empty($fielddef2[$key]))
- continue;
- // if the length in db is greather than the vardef, ignore it
- if ($key == 'len' && ($fielddef1[$key] >= $fielddef2[$key])) {
- continue;
- }
- return false;
- }
- return true;
- }
- /**
- * Compare a field in two tables
- * @deprecated
- * @param string $name field name
- * @param string $table1
- * @param string $table2
- * @return array array with keys 'msg','table1','table2'
- */
- public function compareFieldInTables($name, $table1, $table2)
- {
- $row1 = $this->describeField($name, $table1);
- $row2 = $this->describeField($name, $table2);
- $returnArray = array(
- 'table1' => $row1,
- 'table2' => $row2,
- 'msg' => 'error',
- );
- $ignore_filter = array('Key'=>1);
- if ($row1) {
- if (!$row2) {
- // Exists on table1 but not table2
- $returnArray['msg'] = 'not_exists_table2';
- }
- else {
- if (sizeof($row1) != sizeof($row2)) {
- $returnArray['msg'] = 'no_match';
- }
- else {
- $returnArray['msg'] = 'match';
- foreach($row1 as $key => $value){
- //ignore keys when checking we will check them when we do the index check
- if( !isset($ignore_filter[$key]) && (!isset($row2[$key]) || $row1[$key] !== $row2[$key])){
- $returnArray['msg'] = 'no_match';
- }
- }
- }
- }
- }
- else {
- $returnArray['msg'] = 'not_exists_table1';
- }
- return $returnArray;
- }
- //
- // /**
- // * Compare an index in two different tables
- // * @deprecated
- // * @param string $name index name
- // * @param string $table1
- // * @param string $table2
- // * @return array array with keys 'msg','table1','table2'
- // */
- // public function compareIndexInTables($name, $table1, $table2)
- // {
- // $row1 = $this->describeIndex($name, $table1);
- // $row2 = $this->describeIndex($name, $table2);
- // $returnArray = array(
- // 'table1' => $row1,
- // 'table2' => $row2,
- // 'msg' => 'error',
- // );
- // $ignore_filter = array('Table'=>1, 'Seq_in_index'=>1,'Cardinality'=>1, 'Sub_part'=>1, 'Packed'=>1, 'Comment'=>1);
- //
- // if ($row1) {
- // if (!$row2) {
- // //Exists on table1 but not table2
- // $returnArray['msg'] = 'not_exists_table2';
- // }
- // else {
- // if (sizeof($row1) != sizeof($row2)) {
- // $returnArray['msg'] = 'no_match';
- // }
- // else {
- // $returnArray['msg'] = 'match';
- // foreach ($row1 as $fname => $fvalue) {
- // if (!isset($row2[$fname])) {
- // $returnArray['msg'] = 'no_match';
- // }
- // if(!isset($ignore_filter[$fname]) && $row1[$fname] != $row2[$fname]){
- // $returnArray['msg'] = 'no_match';
- // }
- // }
- // }
- // }
- // } else {
- // $returnArray['msg'] = 'not_exists_table1';
- // }
- //
- // return $returnArray;
- // }
- /**
- * Creates an index identified by name on the given fields.
- *
- * @param SugarBean $bean SugarBean instance
- * @param array $fieldDefs Field definitions, in vardef format
- * @param string $name index name
- * @param bool $unique optional, true if we want to create an unique index
- * @return bool query result
- */
- public function createIndex(SugarBean $bean, $fieldDefs, $name, $unique = true)
- {
- $sql = $this->createIndexSQL($bean, $fieldDefs, $name, $unique);
- $tablename = $bean->getTableName();
- $msg = "Error creating index $name on table: $tablename:";
- return $this->query($sql,true,$msg);
- }
- /**
- * returns a SQL query that creates the indices as defined in metadata
- * @param array $indices Assoc array with index definitions from vardefs
- * @param string $table Focus table
- * @return array Array of SQL queries to generate indices
- */
- public function getConstraintSql($indices, $table)
- {
- if (!$this->isFieldArray($indices))
- $indices = array($indices);
- $columns = array();
- foreach ($indices as $index) {
- if(!empty($index['db']) && $index['db'] != $this->dbType)
- continue;
- if (isset($index['source']) && $index['source'] != 'db')
- continue;
- $sql = $this->add_drop_constraint($table, $index);
- if(!empty($sql)) {
- $columns[] = $sql;
- }
- }
- return $columns;
- }
- /**
- * Adds a new indexes
- *
- * @param string $tablename
- * @param array $indexes indexes to add
- * @param bool $execute true if we want to execute the returned sql statement
- * @return string SQL statement
- */
- public function addIndexes($tablename, $indexes, $execute = true)
- {
- $alters = $this->getConstraintSql($indexes, $tablename);
- if ($execute) {
- foreach($alters as $sql) {
- $this->query($sql, true, "Error adding index: ");
- }
- }
- if(!empty($alters)) {
- $sql = join(";\n", $alters).";\n";
- } else {
- $sql = '';
- }
- return $sql;
- }
- /**
- * Drops indexes
- *
- * @param string $tablename
- * @param array $indexes indexes to drop
- * @param bool $execute true if we want to execute the returned sql statement
- * @return string SQL statement
- */
- public function dropIndexes($tablename, $indexes, $execute = true)
- {
- $sqls = array();
- foreach ($indexes as $index) {
- $name =$index['name'];
- $sqls[$name] = $this->add_drop_constraint($tablename,$index,true);
- }
- if (!empty($sqls) && $execute) {
- foreach($sqls as $name => $sql) {
- unset(self::$index_descriptions[$tablename][$name]);
- $this->query($sql);
- }
- }
- if(!empty($sqls)) {
- return join(";\n",$sqls).";";
- } else {
- return '';
- }
- }
- /**
- * Modifies indexes
- *
- * @param string $tablename
- * @param array $indexes indexes to modify
- * @param bool $execute true if we want to execute the returned sql statement
- * @return string SQL statement
- */
- public function modifyIndexes($tablename, $indexes, $execute = true)
- {
- return $this->dropIndexes($tablename, $indexes, $execute)."\n".
- $this->addIndexes($tablename, $indexes, $execute);
- }
- /**
- * Adds a column to table identified by field def.
- *
- * @param string $tablename
- * @param array $fieldDefs
- * @return bool query result
- */
- public function addColumn($tablename, $fieldDefs)
- {
- $sql = $this->addColumnSQL($tablename, $fieldDefs);
- if ($this->isFieldArray($fieldDefs)){
- $columns = array();
- foreach ($fieldDefs as $fieldDef)
- $columns[] = $fieldDef['name'];
- $columns = implode(",", $columns);
- }
- else {
- $columns = $fieldDefs['name'];
- }
- $msg = "Error adding column(s) $columns on table: $tablename:";
- return $this->query($sql,true,$msg);
- }
- /**
- * Alters old column identified by oldFieldDef to new fieldDef.
- *
- * @param string $tablename
- * @param array $newFieldDef
- * @param bool $ignoreRequired optional, true if we are ignoring this being a required field
- * @return bool query result
- */
- public function alterColumn($tablename, $newFieldDef, $ignoreRequired = false)
- {
- $sql = $this->alterColumnSQL($tablename, $newFieldDef,$ignoreRequired);
- if ($this->isFieldArray($newFieldDef)){
- $columns = array();
- foreach ($newFieldDef as $fieldDef) {
- $columns[] = $fieldDef['name'];
- }
- $columns = implode(",", $columns);
- }
- else {
- $columns = $newFieldDef['name'];
- }
- $msg = "Error altering column(s) $columns on table: $tablename:";
- $res = $this->query($sql,true,$msg);
- if($res) {
- $this->getTableDescription($tablename, true); // reload table description after altering
- }
- return $res;
- }
- /**
- * Drops the table associated with a bean
- *
- * @param SugarBean $bean SugarBean instance
- * @return bool query result
- */
- public function dropTable(SugarBean $bean)
- {
- return $this->dropTableName($bean->getTableName());
- }
- /**
- * Drops the table by name
- *
- * @param string $name Table name
- * @return bool query result
- */
- public function dropTableName($name)
- {
- $sql = $this->dropTableNameSQL($name);
- return $this->query($sql,true,"Error dropping table $name:");
- }
- /**
- * Deletes a column identified by fieldDef.
- *
- * @param SugarBean $bean SugarBean containing the field
- * @param array $fieldDefs Vardef definition of the field
- * @return bool query result
- */
- public function deleteColumn(SugarBean $bean, $fieldDefs)
- {
- $tablename = $bean->getTableName();
- $sql = $this->dropColumnSQL($tablename, $fieldDefs);
- $msg = "Error deleting column(s) on table: $tablename:";
- return $this->query($sql,true,$msg);
- }
- /**
- * Generate a set of Insert statements based on the bean given
- *
- * @deprecated
- *
- * @param SugarBean $bean the bean from which table we will generate insert stmts
- * @param string $select_query the query which will give us the set of objects we want to place into our insert statement
- * @param int $start the first row to query
- * @param int $count the number of rows to query
- * @param string $table the table to query from
- * @param bool $is_related_query
- * @return string SQL insert statement
- */
- public function generateInsertSQL(SugarBean $bean, $select_query, $start, $count = -1, $table, $is_related_query = false)
- {
- $this->log->info('call to DBManager::generateInsertSQL() is deprecated');
- global $sugar_config;
- $rows_found = 0;
- $count_query = $bean->create_list_count_query($select_query);
- if(!empty($count_query))
- {
- // We have a count query. Run it and get the results.
- $result = $this->query($count_query, true, "Error running count query for $this->object_name List: ");
- $assoc = $this->fetchByAssoc($result);
- if(!empty($assoc['c']))
- {
- $rows_found = $assoc['c'];
- }
- }
- if($count == -1){
- $count = $sugar_config['list_max_entries_per_page'];
- }
- $next_offset = $start + $count;
- $result = $this->limitQuery($select_query, $start, $count);
- // get basic insert
- $sql = "INSERT INTO ".$table;
- $custom_sql = "INSERT INTO ".$table."_cstm";
- // get field definitions
- $fields = $bean->getFieldDefinitions();
- $custom_fields = array();
- if($bean->hasCustomFields()){
- foreach ($fields as $fieldDef){
- if($fieldDef['source'] == 'custom_fields'){
- $custom_fields[$fieldDef['name']] = $fieldDef['name'];
- }
- }
- if(!empty($custom_fields)){
- $custom_fields['id_c'] = 'id_c';
- $id_field = array('name' => 'id_c', 'custom_type' => 'id',);
- $fields[] = $id_field;
- }
- }
- // get column names and values
- $row_array = array();
- $columns = array();
- $cstm_row_array = array();
- $cstm_columns = array();
- $built_columns = false;
- while(($row = $this->fetchByAssoc($result)) != null)
- {
- $values = array();
- $cstm_values = array();
- if(!$is_related_query){
- foreach ($fields as $fieldDef)
- {
- if(isset($fieldDef['source']) && $fieldDef['source'] != 'db' && $fieldDef['source'] != 'custom_fields') continue;
- $val = $row[$fieldDef['name']];
- //handle auto increment values here only need to do this on insert not create
- if ($fieldDef['name'] == 'deleted'){
- $values['deleted'] = $val;
- if(!$built_columns){
- $columns[] = 'deleted';
- }
- }
- else
- {
- $type = $fieldDef['type'];
- if(!empty($fieldDef['custom_type'])){
- $type = $fieldDef['custom_type'];
- }
- // need to do some thing about types of values
- if($this->dbType == 'mysql' && $val == '' && ($type == 'datetime' || $type == 'date' || $type == 'int' || $type == 'currency' || $type == 'decimal')){
- if(!empty($custom_fields[$fieldDef['name']]))
- $cstm_values[$fieldDef['name']] = 'null';
- else
- $values[$fieldDef['name']] = 'null';
- }else{
- if(isset($type) && $type=='int') {
- if(!empty($custom_fields[$fieldDef['name']]))
- $cstm_values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
- else
- $values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
- } else {
- if(!empty($custom_fields[$fieldDef['name']]))
- $cstm_values[$fieldDef['name']] = "'".$GLOBALS['db']->quote(from_html($val))."'";
- else
- $values[$fieldDef['name']] = "'".$GLOBALS['db']->quote(from_html($val))."'";
- }
- }
- if(!$built_columns){
- if(!empty($custom_fields[$fieldDef['name']]))
- $cstm_columns[] = $fieldDef['name'];
- else
- $columns[] = $fieldDef['name'];
- }
- }
- }
- } else {
- foreach ($row as $key=>$val)
- {
- if($key != 'orc_row'){
- $values[$key] = "'$val'";
- if(!$built_columns){
- $columns[] = $key;
- }
- }
- }
- }
- $built_columns = true;
- if(!empty($values)){
- $row_array[] = $values;
- }
- if(!empty($cstm_values) && !empty($cstm_values['id_c']) && (strlen($cstm_values['id_c']) > 7)){
- $cstm_row_array[] = $cstm_values;
- }
- }
- //if (sizeof ($values) == 0) return ""; // no columns set
- // get the entire sql
- $sql .= "(".implode(",", $columns).") ";
- $sql .= "VALUES";
- for($i = 0; $i < count($row_array); $i++){
- $sql .= " (".implode(",", $row_array[$i]).")";
- if($i < (count($row_array) - 1)){
- $sql .= ", ";
- }
- }
- //custom
- // get the entire sql
- $custom_sql .= "(".implode(",", $cstm_columns).") ";
- $custom_sql .= "VALUES";
- for($i = 0; $i < count($cstm_row_array); $i++){
- $custom_sql .= " (".implode(",", $cstm_row_array[$i]).")";
- if($i < (count($cstm_row_array) - 1)){
- $custom_sql .= ", ";
- }
- }
- return array('data' => $sql, 'cstm_sql' => $custom_sql, /*'result_count' => $row_count, */ 'total_count' => $rows_found, 'next_offset' => $next_offset);
- }
- /**
- * @deprecated
- * Disconnects all instances
- */
- public function disconnectAll()
- {
- DBManagerFactory::disconnectAll();
- }
- /**
- * This function sets the query threshold limit
- *
- * @param int $limit value of query threshold limit
- */
- public static function setQueryLimit($limit)
- {
- //reset the queryCount
- self::$queryCount = 0;
- self::$queryLimit = $limit;
- }
- /**
- * Returns the static queryCount value
- *
- * @return int value of the queryCount static variable
- */
- public static function getQueryCount()
- {
- return self::$queryCount;
- }
- /**
- * Resets the queryCount value to 0
- *
- */
- public static function resetQueryCount()
- {
- self::$queryCount = 0;
- }
- /**
- * This function increments the global $sql_queries variable
- */
- public function countQuery()
- {
- if (self::$queryLimit != 0 && ++self::$queryCount > self::$queryLimit
- &&(empty($GLOBALS['current_user']) || !is_admin($GLOBALS['current_user']))) {
- require_once('include/resource/ResourceManager.php');
- $resourceManager = ResourceManager::getInstance();
- $resourceManager->notifyObservers('ERR_QUERY_LIMIT');
- }
- }
- /**
- * Pre-process string for quoting
- * @internal
- * @param string $string
- * @return string
- */
- protected function quoteInternal($string)
- {
- return from_html($string);
- }
- /**
- * Return string properly quoted with ''
- * @param string $string
- * @return string
- */
- public function quoted($string)
- {
- return "'".$this->quote($string)."'";
- }
- /**
- * Quote value according to type
- * Numerics aren't quoted
- * Dates are converted and quoted
- * Rest is just quoted
- * @param string $type
- * @param string $value
- * @return string Quoted value
- */
- public function quoteType($type, $value)
- {
- if($type == 'date') {
- return $this->convert($this->quoted($value), "date");
- }
- if($type == 'time') {
- return $this->convert($this->quoted($value), "time");
- }
- if(isset($this->type_class[$type]) && $this->type_class[$type] == "date") {
- return $this->convert($this->quoted($value), "datetime");
- }
- if($this->isNumericType($type)) {
- return 0+$value; // ensure it's numeric
- }
- return $this->quoted($value);
- }
- /**
- * Quote the strings of the passed in array
- *
- * The array must only contain strings
- *
- * @param array $array
- * @return array Quoted strings
- */
- public function arrayQuote(array &$array)
- {
- foreach($array as &$val) {
- $val = $this->quote($val);
- }
- return $array;
- }
- /**
- * Frees out previous results
- *
- * @param resource|bool $result optional, pass if you want to free a single result instead of all results
- */
- protected function freeResult($result = false)
- {
- if($result) {
- $this->freeDbResult($result);
- }
- if($this->lastResult) {
- $this->freeDbResult($this->lastResult);
- $this->lastResult = null;
- }
- }
- /**
- * @abstract
- * Check if query has LIMIT clause
- * Relevant for now only for Mysql
- * @param string $sql
- * @return bool
- */
- protected function hasLimit($sql)
- {
- return false;
- }
- /**
- * Runs a query and returns a single row containing single value
- *
- * @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
- * @return array single value from the query
- */
- public function getOne($sql, $dieOnError = false, $msg = '')
- {
- $this->log->info("Get One: |$sql|");
- if(!$this->hasLimit($sql)) {
- $queryresult = $this->limitQuery($sql, 0, 1, $dieOnError, $msg);
- } else {
- // support old code that passes LIMIT to sql
- // works only for mysql, so do not rely on this
- $queryresult = $this->query($sql, $dieOnError, $msg);
- }
- $this->checkError($msg.' Get One Failed:' . $sql, $dieOnError);
- if (!$queryresult) return false;
- $row = $this->fetchByAssoc($queryresult);
- if(!empty($row)) {
- return array_shift($row);
- }
- return false;
- }
- /**
- * Runs a query and returns a single row
- *
- * @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 Message to log if error occurs
- * @return array single row from the query
- */
- public function fetchOne($sql, $dieOnError = false, $msg = '', $suppress = false)
- {
- $this->log->info("Fetch One: |$sql|");
- $this->checkConnection();
- $queryresult = $this->query($sql, $dieOnError, $msg);
- $this->checkError($msg.' Fetch One Failed:' . $sql, $dieOnError);
- if (!$queryresult) return false;
- $row = $this->fetchByAssoc($queryresult);
- if ( !$row ) return false;
- $this->freeResult($queryresult);
- return $row;
- }
- /**
- * Returns the number of rows affected by the last query
- * @abstract
- * See also affected…
Large files files are truncated, but you can click here to view the full file