2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM Community Edition 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 ********************************************************************************/
94 abstract class DBManager
97 * DBHelper object instance for this class
102 * Name of database table we are dealing with
104 protected $tableName;
109 public $database = null;
112 * Indicates whether we should die when we get an error from the DB
114 protected $dieOnError = false;
117 * Indicates whether we should html encode the results from a query by default
119 protected $encode = true;
122 * Records the execution time of the last query
124 protected $query_time = 0;
127 * Number of the last row fetched from the query result set
129 protected $lastmysqlrow = -1;
132 * Last error message from the DB backend
134 protected $last_error = '';
137 * Registry of available result sets
139 protected $lastResult = array();
142 * Current query count
144 private static $queryCount = 0;
147 * Query threshold limit
149 private static $queryLimit = 0;
152 * Array of common backend functions and what the PHP they map to is
154 protected $backendFunctions = array();
157 * Array of prepared statements and their correspoding parsed tokens
159 protected $preparedTokens = array();
162 * Wrapper for those trying to access the private and protected class members directly
164 public function __get($p)
166 $GLOBALS['log']->info('call to DBManagerFactory::$'.$p.' is deprecated');
170 public function __construct()
175 * Returns the current tablename
179 public function getTableName()
181 return $this->tableName;
185 * Returns the current database handle
189 public function getDatabase()
191 $this->checkConnection();
192 return $this->database;
196 * Returns this instance's DBHelper
198 * @return object DBHelper instance
200 public function getHelper()
202 if ( !($this->helper instanceof DBHelper) ) {
203 global $sugar_config;
205 switch ( $sugar_config['dbconfig']['db_type'] ) {
207 $my_db_helper = 'MysqlHelper';
208 if ( (!isset($sugar_config['mysqli_disabled'])
209 || $sugar_config['mysqli_disabled'] == false)
210 && function_exists('mysqli_connect') )
211 $my_db_helper = 'MysqliHelper';
214 if ( function_exists('sqlsrv_connect')
215 && (empty($config['db_mssql_force_driver']) || $config['db_mssql_force_driver'] == 'sqlsrv' ))
216 $my_db_helper = 'SqlsrvHelper';
218 && (empty($config['db_mssql_force_driver']) || $config['db_mssql_force_driver'] == 'freetds' ))
219 $my_db_helper = 'FreeTDSHelper';
221 $my_db_helper = 'MssqlHelper';
224 $my_db_helper = 'MysqlHelper';
226 $GLOBALS['log']->info("using $my_db_helper DBHelper backend");
227 require_once("include/database/{$my_db_helper}.php");
228 $this->helper = new $my_db_helper();
229 $this->helper->db = $this;
232 return $this->helper;
236 * Checks for database not being connected
238 * @param string $msg message to prepend to the error message
239 * @param bool $dieOnError true if we want to die immediately on error
242 public function checkError(
246 $userMsg = inDeveloperMode()?"$msg: ":"";
248 if (!isset($this->database)) {
249 $GLOBALS['log']->error("Database Is Not Connected");
250 if($this->dieOnError || $dieOnError)
251 sugar_die ($userMsg."Database Is Not Connected");
253 $this->last_error = $userMsg."Database Is Not Connected";
260 * This method is called by every method that runs a query.
261 * If slow query dumping is turned on and the query time is beyond
262 * the time limit, we will log the query. This function may do
263 * additional reporting or log in a different area in the future.
265 * @param string $query query to log
266 * @return boolean true if the query was logged, false otherwise
268 protected function dump_slow_queries(
272 global $sugar_config;
274 $do_the_dump = isset($sugar_config['dump_slow_queries'])
275 ? $sugar_config['dump_slow_queries'] : false;
276 $slow_query_time_msec = isset($sugar_config['slow_query_time_msec'])
277 ? $sugar_config['slow_query_time_msec'] : 5000;
280 if($slow_query_time_msec < ($this->query_time * 1000)) {
281 // Then log both the query and the query time
282 $GLOBALS['log']->fatal('Slow Query (time:'.$this->query_time."\n".$query);
290 * Scans order by to ensure that any field being ordered by is.
292 * It will throw a warning error to the log file - fatal if slow query logging is enabled
294 * @param string $sql query to be run
295 * @param bool $object_name optional, object to look up indices in
296 * @return bool true if an index is found false otherwise
298 protected function checkQuery(
304 preg_match_all("'.* FROM ([^ ]*).* ORDER BY (.*)'is", $sql, $match);
306 if (!empty($match[1][0]))
307 $table = $match[1][0];
311 if (!empty($object_name) && !empty($GLOBALS['dictionary'][$object_name]))
312 $indices = $GLOBALS['dictionary'][$object_name]['indices'];
314 if (empty($indices)) {
315 foreach ( $GLOBALS['dictionary'] as $current ) {
316 if ($current['table'] == $table){
317 $indices = $current['indices'];
322 if (empty($indices)) {
323 $GLOBALS['log']->warn('CHECK QUERY: Could not find index definitions for table ' . $table);
326 if (!empty($match[2][0])) {
327 $orderBys = explode(' ', $match[2][0]);
328 foreach ($orderBys as $orderBy){
329 $orderBy = trim($orderBy);
332 $orderBy = strtolower($orderBy);
333 if ($orderBy == 'asc' || $orderBy == 'desc')
336 $orderBy = str_replace(array($table . '.', ','), '', $orderBy);
338 foreach ($indices as $index)
339 if (empty($index['db']) || $index['db'] == $this->dbType)
340 foreach ($index['fields'] as $field)
341 if ($field == $orderBy)
344 $warning = 'Missing Index For Order By Table: ' . $table . ' Order By:' . $orderBy ;
345 if (!empty($GLOBALS['sugar_config']['dump_slow_queries']))
346 $GLOBALS['log']->fatal('CHECK QUERY:' .$warning);
348 $GLOBALS['log']->warn('CHECK QUERY:' .$warning);
356 * Returns the time the last query took to execute
360 public function getQueryTime()
362 return $this->query_time;
366 * Checks the current connection; if it is not connected then reconnect
368 public function checkConnection()
370 $this->last_error = '';
371 if (!isset($this->database))
376 * Sets the dieOnError value
380 public function setDieOnError(
384 $this->dieOnError = $value;
388 * Implements a generic insert for any bean.
390 * @param object $bean SugarBean instance
392 public function insert(
396 $sql = $this->getHelper()->insertSQL($bean);
397 $this->tableName = $bean->getTableName();
398 $msg = "Error inserting into table: ".$this->tableName;
399 $this->query($sql,true,$msg);
403 * Implements a generic update for any bean
405 * @param object $bean Sugarbean instance
406 * @param array $where values with the keys as names of fields.
407 * If we want to pass multiple values for a name, pass it as an array
408 * If where is not passed, it defaults to id of table
410 public function update(
412 array $where = array()
415 $sql = $this->getHelper()->updateSQL($bean, $where);
416 $this->tableName = $bean->getTableName();
417 $msg = "Error updating table: ".$this->tableName. ":";
418 $this->query($sql,true,$msg);
422 * Implements a generic delete for any bean identified by id
424 * @param object $bean Sugarbean instance
425 * @param array $where values with the keys as names of fields.
426 * If we want to pass multiple values for a name, pass it as an array
427 * If where is not passed, it defaults to id of table
429 public function delete(
431 array $where = array()
434 $sql = $this->getHelper()->deleteSQL($bean, $where);
435 $this->tableName = $bean->getTableName();
436 $msg = "Error deleting from table: ".$this->tableName. ":";
437 $this->query($sql,true,$msg);
441 * Implements a generic retrieve for any bean identified by id
443 * If we want to pass multiple values for a name, pass it as an array
444 * If where is not passed, it defaults to id of table
446 * @param object $bean Sugarbean instance
447 * @param array $where values with the keys as names of fields.
448 * @return resource result from the query
450 public function retrieve(
452 array $where = array()
455 $sql = $this->getHelper()->retrieveSQL($bean, $where);
456 $this->tableName = $bean->getTableName();
457 $msg = "Error retriving values from table:".$this->tableName. ":";
458 return $this->query($sql,true,$msg);
462 * Implements a generic retrieve for a collection of beans.
464 * These beans will be joined in the sql by the key attribute of field defs.
465 * Currently, this function does support outer joins.
467 * @param array $beans Sugarbean instance(s)
468 * @param array $cols columns to be returned with the keys as names of bean as identified by
469 * get_class of bean. Values of this array is the array of fieldDefs to be returned for a bean.
470 * If an empty array is passed, all columns are selected.
471 * @param array $where values with the keys as names of bean as identified by get_class of bean
472 * Each value at the first level is an array of values for that bean identified by name of fields.
473 * If we want to pass multiple values for a name, pass it as an array
474 * If where is not passed, all the rows will be returned.
477 public function retrieveView(
479 array $cols = array(),
480 array $where = array()
483 $sql = $this->getHelper()->retrieveViewSQL($beans, $cols, $where);
484 $this->tableName = "View Collection"; // just use this string for msg
485 $msg = "Error retriving values from table:".$this->tableName. ":";
486 $this->query($sql,true,$msg);
491 * Implements creation of a db table for a bean.
493 * @param object $bean Sugarbean instance
495 public function createTable(
499 $sql = $this->getHelper()->createTableSQL($bean);
500 $this->tableName = $bean->getTableName();
501 $msg = "Error creating table: ".$this->tableName. ":";
502 $this->query($sql,true,$msg);
506 * Implements creation of a db table
508 * @param string $tablename
509 * @param array $fieldDefs
510 * @param array $indices
511 * @param string $engine MySQL engine to use
513 public function createTableParams(
520 if (!empty($fieldDefs)) {
521 $sql = $this->getHelper()
522 ->createTableSQLParams($tablename, $fieldDefs, $indices,$engine);
523 $this->tableName = $tablename;
525 $msg = "Error creating table: ".$this->tableName. ":";
526 $this->query($sql,true,$msg);
532 * Implements repair of a db table for a bean.
534 * @param object $bean SugarBean instance
535 * @param bool $execute true if we want the action to take place, false if we just want the sql returned
536 * @return string SQL statement or empty string, depending upon $execute
538 public function repairTable(SugarBean $bean, $execute = true)
540 $indices = $bean->getIndices();
541 $fielddefs = $bean->getFieldDefinitions();
542 $tablename = $bean->getTableName();
544 //Clean the indicies to prevent duplicate definitions
545 $new_Indecies = array();
546 foreach($indices as $ind_def){
547 $new_Indecies[$ind_def['name']] = $ind_def;
549 //jc: added this for beans that do not actually have a table, namely
550 //ForecastOpportunities
551 if($tablename == 'does_not_exist' || $tablename == '')
556 if (isset($dictionary[$bean->getObjectName()]['engine']) && !empty($dictionary[$bean->getObjectName()]['engine']) )
557 $engine = $dictionary[$bean->getObjectName()]['engine'];
559 return $this->repairTableParams($tablename, $fielddefs,$new_Indecies,$execute,$engine);
563 * Builds the SQL commands that repair a table structure
565 * @param string $tablename
566 * @param array $fielddefs
567 * @param array $indices
568 * @param bool $execute optional, true if we want the queries executed instead of returned
569 * @param string $engine optional, MySQL engine
571 public function repairTableParams(
579 global $table_descriptions;
581 //jc: had a bug when running the repair if the tablename is blank the repair will
582 //fail when it tries to create a repair table
583 if ($tablename == '')
585 if (empty($fielddefs))
588 //if the table does not exist create it and we are done
589 $sql = "/* Table : $tablename */\n";
590 if (!$this->tableExists($tablename)){
592 $createtablesql = $this->getHelper()
593 ->createTableSQLParams($tablename,$fielddefs,$indices,$engine);
594 if($execute && $createtablesql){
595 $this->createTableParams($tablename,$fielddefs,$indices,$engine);
598 $sql .= "/* MISSING TABLE: {$tablename} */\n";
599 $sql .= $createtablesql . "\n";
603 $compareFieldDefs = $this->getHelper()->get_columns($tablename);
604 $compareIndices = $this->getHelper()->get_indices($tablename);
606 $take_action = false;
608 // do column comparisions
609 $sql .= "/*COLUMNS*/\n";
610 foreach ($fielddefs as $value) {
611 if (isset($value['source']) && $value['source'] != 'db')
614 $name = strtolower($value['name']);
615 // add or fix the field defs per what the DB is expected to give us back
616 $this->getHelper()->massageFieldDef($value,$tablename);
618 $ignorerequired=false;
620 //Do not track requiredness in the DB, auto_increment, ID, and deleted fields are always required in the DB, so don't force those
621 if (empty($value['auto_increment']) && !isset($value['isnull'])
622 && (empty($value['type']) || $value['type'] != 'id')
623 && (empty($value['dbType']) || $value['dbType'] != 'id')
624 && (empty($value['name']) || ($value['name'] != 'id' && $value['name'] != 'deleted'))
626 $value['required'] = false;
628 //Should match the conditions in DBHelper::oneColumnSQLRep for DB required fields, type='id' fields will sometimes
629 //come into this function as 'type' = 'char', 'dbType' = 'id' without required set in $value. Assume they are correct and leave them alone.
630 else if (($name == 'id' || $value['type'] == 'id' || (isset($value['dbType']) && $value['dbType'] == 'id'))
631 && (!isset($value['required']) && isset($compareFieldDefs[$name]['required'])))
633 $value['required'] = $compareFieldDefs[$name]['required'];
636 if ( !isset($compareFieldDefs[$name]) ) {
637 // ok we need this field lets create it
638 $sql .= "/*MISSING IN DATABASE - $name - ROW*/\n";
639 $sql .= $this->getHelper()->addColumnSQL($tablename, $value) . "\n";
641 $this->addColumn($tablename, $value);
644 elseif ( !$this->compareVarDefs($compareFieldDefs[$name],$value)) {
645 //fields are different lets alter it
646 $sql .= "/*MISMATCH WITH DATABASE - $name - ROW ";
647 foreach($compareFieldDefs[$name] as $rKey => $rValue)
648 $sql .= "[$rKey] => '$rValue' ";
650 $sql .= "/* VARDEF - $name - ROW";
651 foreach($value as $rKey => $rValue)
652 $sql .= "[$rKey] => '$rValue' ";
655 //jc: oracle will complain if you try to execute a statement that sets a column to (not) null
656 //when it is already (not) null
657 if ( isset($value['isnull']) && isset($compareFieldDefs[$name]['isnull']) ) {
658 if ($value['isnull'] === $compareFieldDefs[$name]['isnull']) {
659 unset($value['required']);
660 $ignorerequired=true;
664 //dwheeler: Once a column has been defined as null, we cannot try to force it back to !null
665 if ((isset($value['required']) && ($value['required'] === true || $value['required'] == 'true' || $value['required'] === 1))
666 && (empty($compareFieldDefs[$name]['required']) || $compareFieldDefs[$name]['required'] != 'true'))
668 $ignorerequired = true;
671 $sql .= $this->getHelper()->alterColumnSQL($tablename, $value,$ignorerequired) . "\n";
673 $this->alterColumn($tablename, $value,$ignorerequired);
679 // do index comparisions
680 $sql .= "/* INDEXES */\n";
681 $correctedIndexs = array();
683 // do indicies comparisons case-insensitive
684 foreach($compareIndices as $k => $value){
685 $value['name'] = strtolower($value['name']);
686 $compareIndices_case_insensitive[strtolower($k)] = $value;
688 $compareIndices = $compareIndices_case_insensitive;
689 unset($compareIndices_case_insensitive);
691 foreach ($indices as $value) {
692 if (isset($value['source']) && $value['source'] != 'db')
695 $validDBName = $this->helper->getValidDBName($name, true, 'index', true);
696 if (isset($compareIndices[$validDBName])) {
697 $value['name'] = $validDBName;
700 $name = strtolower($value['name']);
702 //Don't attempt to fix the same index twice in one pass;
703 if (isset($correctedIndexs[$name]))
706 //don't bother checking primary nothing we can do about them
707 if (isset($value['type']) && $value['type'] == 'primary')
710 //database helpers do not know how to handle full text indices
711 if ($value['type']=='fulltext')
714 if ( in_array($value['type'],array('alternate_key','foreign')) )
715 $value['type'] = 'index';
717 if ( !isset($compareIndices[$name]) ) {
718 //First check if an index exists that doens't match our name, if so, try to rename it
720 foreach ($compareIndices as $ex_name => $ex_value)
722 if($this->compareVarDefs($ex_value, $value, true))
730 $sql .= "/*MISSNAMED INDEX IN DATABASE - $name - $ex_name */\n";
731 $sql .= $this->renameIndex($tablename, $ex_name, $name, $execute) . "\n";
735 // ok we need this field lets create it
736 $sql .= "/*MISSING INDEX IN DATABASE - $name -{$value['type']} ROW */\n";
737 $sql .= $this->addIndexes($tablename,array($value), $execute) . "\n";
740 $correctedIndexs[$name] = true;
742 elseif ( !$this->compareVarDefs($compareIndices[$name],$value) ) {
743 // fields are different lets alter it
744 $sql .= "/*INDEX MISMATCH WITH DATABASE - $name - ROW ";
745 foreach ($compareIndices[$name] as $n1 => $t1) {
747 if ( $n1 == 'fields' )
748 foreach($t1 as $rKey => $rValue)
749 $sql .= "[$rKey] => '$rValue' ";
754 $sql .= "/* VARDEF - $name - ROW";
755 foreach ($value as $n1 => $t1) {
757 if ( $n1 == 'fields' )
758 foreach ($t1 as $rKey => $rValue)
759 $sql .= "[$rKey] => '$rValue' ";
764 $sql .= $this->modifyIndexes($tablename,array($value), $execute) . "\n";
766 $correctedIndexs[$name] = true;
770 return ($take_action === true) ? $sql : "";
774 * Compares two vardefs
776 * @param array $fielddef1 This is from the database
777 * @param array $fielddef2 This is from the vardef
778 * @return bool true if they match, false if they don't
780 public function compareVarDefs(
786 foreach ( $fielddef1 as $key => $value ) {
787 if ( $key == 'name' && ( strtolower($fielddef1[$key]) == strtolower($fielddef2[$key]) || $ignoreName) )
789 if ( isset($fielddef2[$key]) && $fielddef1[$key] == $fielddef2[$key] )
791 //Ignore len if its not set in the vardef
792 if ($key == 'len' && empty($fielddef2[$key]))
801 * Compare a field in two tables
803 * @param string $name field name
804 * @param string $table1
805 * @param string $table2
806 * @return array array with keys 'msg','table1','table2'
808 public function compareFieldInTables(
814 $row1 = $this->describeField($name, $table1);
815 $row2 = $this->describeField($name, $table2);
816 $returnArray = array(
822 $ignore_filter = array('Key'=>1);
825 // Exists on table1 but not table2
826 $returnArray['msg'] = 'not_exists_table2';
829 if (sizeof($row1) != sizeof($row2)) {
830 $returnArray['msg'] = 'no_match';
833 $returnArray['msg'] = 'match';
834 foreach($row1 as $key => $value){
835 //ignore keys when checking we will check them when we do the index check
836 if( !isset($ignore_filter[$key]) && $row1[$key] !== $row2[$key]){
837 $returnArray['msg'] = 'no_match';
844 $returnArray['msg'] = 'not_exists_table1';
851 * Compare an index in two different tables
853 * @param string $name index name
854 * @param string $table1
855 * @param string $table2
856 * @return array array with keys 'msg','table1','table2'
858 public function compareIndexInTables(
864 $row1 = $this->describeIndex($name, $table1);
865 $row2 = $this->describeIndex($name, $table2);
866 $returnArray = array(
871 $ignore_filter = array('Table'=>1, 'Seq_in_index'=>1,'Cardinality'=>1, 'Sub_part'=>1, 'Packed'=>1, 'Comment'=>1);
875 //Exists on table1 but not table2
876 $returnArray['msg'] = 'not_exists_table2';
879 if (sizeof($row1) != sizeof($row2)) {
880 $returnArray['msg'] = 'no_match';
883 $returnArray['msg'] = 'match';
884 foreach ($row1 as $fname => $fvalue) {
885 if (!isset($row2[$fname])) {
886 $returnArray['msg'] = 'no_match';
888 foreach($fvalue as $key => $value) {
889 //ignore keys when checking we will check them when we do the index check
890 if(!isset($ignore_filter[$key]) && $row1[$fname][$key] != $row2[$fname][$key]){
891 $returnArray['msg'] = 'no_match';
899 $returnArray['msg'] = 'not_exists_table1';
907 * Creates an index identified by name on the given fields.
909 * @param object $bean SugarBean instance
910 * @param array $fieldDefs
911 * @param string $name index name
912 * @param bool $unique optional, true if we want to create an unique index
914 public function createIndex(
921 $sql = $this->getHelper()->createIndexSQL($bean, $fieldDefs, $name, $unique);
922 $this->tableName = $bean->getTableName();
923 $msg = "Error creating index $name on table: ".$this->tableName. ":";
924 $this->query($sql,true,$msg);
930 * @param string $tablename
931 * @param array $indexes indexes to add
932 * @param bool $execute true if we want to execute the returned sql statement
933 * @return string SQL statement
935 public function addIndexes(
941 $alters = $this->getHelper()->keysSQL($indexes,true,'ADD');
942 $sql = "ALTER TABLE $tablename $alters";
948 public function renameIndex($tablename, $oldName, $newName, $execute = true)
956 * @param string $tablename
957 * @param array $indexes indexes to drop
958 * @param bool $execute true if we want to execute the returned sql statement
959 * @return string SQL statement
961 public function dropIndexes(
968 foreach ($indexes as $index) {
969 $name =$index['name'];
971 unset($GLOBALS['table_descriptions'][$tablename]['indexes'][$name]);
972 if ($index['type'] == 'primary')
973 $name = 'PRIMARY KEY';
975 $name = "INDEX $name";
977 $sql .= " DROP $name ";
979 $sql .= ", DROP $name ";
982 $sql = "ALTER TABLE $tablename $sql";
992 * @param string $tablename
993 * @param array $indexes indexes to modify
994 * @param bool $execute true if we want to execute the returned sql statement
995 * @return string SQL statement
997 public function modifyIndexes(
1003 return $this->dropIndexes($tablename, $indexes, $execute)."\n".
1004 $this->addIndexes($tablename, $indexes, $execute);
1008 * Adds a column to table identified by field def.
1010 * @param string $tablename
1011 * @param array $fieldDefs
1013 public function addColumn(
1018 $this->tableName = $tablename;
1019 $sql = $this->getHelper()->addColumnSQL($this->tableName, $fieldDefs);
1020 if ($this->getHelper()->isFieldArray($fieldDefs)){
1021 foreach ($fieldDefs as $fieldDef) $columns[] = $fieldDef['name'];
1022 $columns = implode(",", $columns);
1025 $columns = $fieldDefs['name'];
1027 $msg = "Error adding column(s) ".$columns." on table: ".$this->tableName. ":";
1028 $this->query($sql,true,$msg);
1032 * Alters old column identified by oldFieldDef to new fieldDef.
1034 * @param string $tablename
1035 * @param array $newFieldDef
1036 * @param bool $ignoreRequired optional, true if we are ignoring this being a required field
1038 public function alterColumn(
1041 $ignoreRequired = false
1044 $this->tableName = $tablename;
1045 $sql = $this->getHelper()->alterColumnSQL($this->tableName, $newFieldDef,$ignoreRequired);
1046 if ($this->getHelper()->isFieldArray($newFieldDef)){
1047 foreach ($newFieldDef as $fieldDef) {
1048 unset($GLOBALS['table_descriptions'][$tablename][$fieldDef['name']]);
1049 $columns[] = $fieldDef['name'];
1051 $columns = implode(",", $columns);
1054 unset($GLOBALS['table_descriptions'][$tablename][$newFieldDef['name']]);
1055 $columns = $newFieldDef['name'];
1058 $msg = "Error altering column(s) ".$columns." on table: ".$this->tableName. ":";
1059 $this->query($sql,true,$msg);
1063 * Drops the table associated with a bean
1065 * @param object $bean SugarBean instance
1067 public function dropTable(
1071 $this->tableName = $bean->getTableName();
1072 $this->dropTableName( $this->tableName);
1076 * Drops the table by name
1078 * @param string $name SugarBean instance
1080 public function dropTableName(
1084 $sql = $this->getHelper()->dropTableNameSQL($name);
1085 $msg = "Error dropping table ".$this->tableName. ":";
1086 $this->query($sql,true,$msg);
1090 * Deletes a column identified by fieldDef.
1092 * @param string $name SugarBean instance
1093 * @param array $fieldDefs
1095 public function deleteColumn(
1100 $sql = $this->getHelper()->deleteColumnSQL($bean, $fieldDefs);
1101 $this->tableName = $bean->getTableName();
1102 $msg = "Error deleting column(s) ".$columns." on table: ".$this->tableName. ":";
1103 $this->query($sql,true,$msg);
1107 * Fetches all the rows for a select query. Returns FALSE if query failed and
1108 * DB_OK for all other queries
1112 * @param resource $result
1113 * @return array All rows in result set
1115 private function setResult(
1119 $GLOBALS['log']->info('call to DBManager::setResult() is deprecated');
1120 if (PEAR::isError($result) === true) {
1121 $GLOBALS['log']->error($msg);
1124 elseif ($result != DB_OK) {
1126 $GLOBALS['log']->fatal("setResult:".print_r($result,true));
1129 while (ocifetchinto($result, $row, OCI_ASSOC|OCI_RETURN_NULLS|OCI_RETURN_LOBS)){
1130 $err = ocierror($result);
1131 if ($err == false) $rows[] = $row;
1136 $GLOBALS['log']->fatal("setResult: returning rows from setResult");
1141 * Private function to handle most of the sql statements which go as queries
1145 * @param string $sql
1148 * @param boolean $dieOnError
1149 * @param string $msg
1150 * @return array All rows in result set
1152 public function executeLimitQuery(
1156 $dieOnError = false,
1159 $GLOBALS['log']->info('call to DBManager::executeLimitQuery() is deprecated');
1160 $result = $this->limitQuery($query,$start,$count, $dieOnError, $msg);
1161 return $this->setResult($result);
1165 * Private function to handle most of the sql statements which go as queries
1169 * @param string $query
1170 * @param string $msg
1171 * @param bool $getRows
1172 * @return array All rows in result set
1174 public function executeQuery(
1180 $GLOBALS['log']->info('call to DBManager::executeQuery() is deprecated');
1181 $result = $this->query($query,true,$msg);
1183 return $this->setResult($result);
1184 // dd not get rows. Simply go on.
1188 * Given a db_type return the correct DBHelper
1192 * @param string $db_type the type of database being used
1193 * @return object DBHelper instance corresponding to the db_type
1195 private function configureHelper(
1199 $GLOBALS['log']->info('call to DBManager::configureHelper() is deprecated');
1200 global $sugar_config;
1202 $my_db_helper = 'MysqlHelper';
1203 if( $sugar_config['dbconfig']['db_type'] == "mysql" ) {
1204 if (!isset($sugar_config['mysqli_disabled']) or $sugar_config['mysqli_disabled']==false) {
1205 if (function_exists('mysqli_connect')) {
1206 $my_db_helper = 'MysqliHelper';
1211 if($db_type == "oci8" ){
1212 }else if($db_type == "mssql"){
1213 require_once('include/database/MssqlHelper.php');
1214 $my_db_helper = 'MssqlHelper';
1216 if($my_db_helper == 'MysqlHelper'){
1217 require_once('include/database/MysqlHelper.php');
1219 return new $my_db_helper();
1223 * Generate a set of Insert statements based on the bean given
1227 * @param object $bean the bean from which table we will generate insert stmts
1228 * @param string $select_query the query which will give us the set of objects we want to place into our insert statement
1229 * @param int $start the first row to query
1230 * @param int $count the number of rows to query
1231 * @param string $table the table to query from
1232 * @param string $db_type the client db type
1233 * @return string SQL insert statement
1235 public function generateInsertSQL(
1242 $is_related_query = false
1245 $GLOBALS['log']->info('call to DBManager::generateInsertSQL() is deprecated');
1246 global $sugar_config;
1248 $count_query = $bean->create_list_count_query($select_query);
1249 if(!empty($count_query))
1251 // We have a count query. Run it and get the results.
1252 $result = $this->query($count_query, true, "Error running count query for $this->object_name List: ");
1253 $assoc = $this->fetchByAssoc($result);
1254 if(!empty($assoc['c']))
1256 $rows_found = $assoc['c'];
1260 $count = $sugar_config['list_max_entries_per_page'];
1262 $next_offset = $start + $count;
1264 $result = $this->limitQuery($select_query, $start, $count);
1265 $row_count = $this->getRowCount($result);
1267 $sql = "INSERT INTO ".$table;
1268 $custom_sql = "INSERT INTO ".$table."_cstm";
1270 // get field definitions
1271 $fields = $bean->getFieldDefinitions();
1272 $custom_fields = array();
1274 if($bean->hasCustomFields()){
1275 foreach ($fields as $fieldDef){
1276 if($fieldDef['source'] == 'custom_fields'){
1277 $custom_fields[$fieldDef['name']] = $fieldDef['name'];
1280 if(!empty($custom_fields)){
1281 $custom_fields['id_c'] = 'id_c';
1282 $id_field = array('name' => 'id_c', custom_type => 'id',);
1283 $fields[] = $id_field;
1287 // get column names and values
1288 $row_array = array();
1290 $cstm_row_array = array();
1291 $cstm_columns = array();
1292 $built_columns = false;
1293 //configure client helper
1294 $dbHelper = $this->configureHelper($db_type);
1295 while(($row = $this->fetchByAssoc($result)) != null)
1298 $cstm_values = array();
1299 if(!$is_related_query){
1300 foreach ($fields as $fieldDef)
1302 if(isset($fieldDef['source']) && $fieldDef['source'] != 'db' && $fieldDef['source'] != 'custom_fields') continue;
1303 $val = $row[$fieldDef['name']];
1305 //handle auto increment values here only need to do this on insert not create
1306 if ($fieldDef['name'] == 'deleted'){
1307 $values['deleted'] = $val;
1308 if(!$built_columns){
1309 $columns[] = 'deleted';
1314 $type = $fieldDef['type'];
1315 if(!empty($fieldDef['custom_type'])){
1316 $type = $fieldDef['custom_type'];
1318 // need to do some thing about types of values
1319 if($db_type == 'mysql' && $val == '' && ($type == 'datetime' || $type == 'date' || $type == 'int' || $type == 'currency' || $type == 'decimal')){
1320 if(!empty($custom_fields[$fieldDef['name']]))
1321 $cstm_values[$fieldDef['name']] = 'null';
1323 $values[$fieldDef['name']] = 'null';
1325 if(isset($type) && $type=='int') {
1326 if(!empty($custom_fields[$fieldDef['name']]))
1327 $cstm_values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
1329 $values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
1331 if(!empty($custom_fields[$fieldDef['name']]))
1332 $cstm_values[$fieldDef['name']] = "'".$GLOBALS['db']->quote(from_html($val))."'";
1334 $values[$fieldDef['name']] = "'".$GLOBALS['db']->quote(from_html($val))."'";
1337 if(!$built_columns){
1338 if(!empty($custom_fields[$fieldDef['name']]))
1339 $cstm_columns[] = $fieldDef['name'];
1341 $columns[] = $fieldDef['name'];
1347 foreach ($row as $key=>$val)
1349 if($key != 'orc_row'){
1350 $values[$key] = "'$val'";
1351 if(!$built_columns){
1357 $built_columns = true;
1358 if(!empty($values)){
1359 $row_array[] = $values;
1361 if(!empty($cstm_values) && !empty($cstm_values['id_c']) && (strlen($cstm_values['id_c']) > 7)){
1362 $cstm_row_array[] = $cstm_values;
1366 //if (sizeof ($values) == 0) return ""; // no columns set
1368 // get the entire sql
1369 $sql .= "(".implode(",", $columns).") ";
1371 for($i = 0; $i < count($row_array); $i++){
1372 $sql .= " (".implode(",", $row_array[$i]).")";
1373 if($i < (count($row_array) - 1)){
1378 // get the entire sql
1379 $custom_sql .= "(".implode(",", $cstm_columns).") ";
1380 $custom_sql .= "VALUES";
1382 for($i = 0; $i < count($cstm_row_array); $i++){
1383 $custom_sql .= " (".implode(",", $cstm_row_array[$i]).")";
1384 if($i < (count($cstm_row_array) - 1)){
1385 $custom_sql .= ", ";
1388 return array('data' => $sql, 'cstm_sql' => $custom_sql, 'result_count' => $row_count, 'total_count' => $rows_found, 'next_offset' => $next_offset);
1392 * Disconnects all instances
1394 public function disconnectAll()
1396 global $dbinstances;
1398 if (!empty($dbinstances)) {
1399 $cur = current($dbinstances);
1402 $cur = next($dbinstances);
1409 * This function sets the query threshold limit
1411 * @param int $limit value of query threshold limit
1413 public static function setQueryLimit($limit){
1414 //reset the queryCount
1415 self::$queryCount = 0;
1417 self::$queryLimit = $limit;
1421 * Returns the static queryCount value
1423 * @return int value of the queryCount static variable
1425 public static function getQueryCount()
1427 return self::$queryCount;
1432 * Resets the queryCount value to 0
1435 public static function resetQueryCount() {
1436 self::$queryCount = 0;
1440 * This function increments the global $sql_queries variable
1442 * @param $sql The SQL statement being counted
1444 public function countQuery(
1448 if (self::$queryLimit != 0 && ++self::$queryCount > self::$queryLimit
1449 &&(empty($GLOBALS['current_user']) || !is_admin($GLOBALS['current_user']))) {
1450 require_once('include/resource/ResourceManager.php');
1451 $resourceManager = ResourceManager::getInstance();
1452 $resourceManager->notifyObservers('ERR_QUERY_LIMIT');
1457 * Returns a string properly quoted for this database
1459 * @param string $string
1460 * @param bool $isLike
1462 public function quote(
1467 return from_html($string);
1471 * Returns a string properly quoted for this database that is an email
1473 * @param string $string
1474 * @param bool $isLike
1476 abstract public function quoteforEmail(
1482 * Quote the strings of the passed in array
1484 * The array must only contain strings
1486 * @param array $array
1487 * @param bool $isLike
1489 public function arrayQuote(
1494 for($i = 0; $i < count($array); $i++){
1495 $array[$i] = $this->quote($array[$i], $isLike);
1499 * Parses and runs queries
1501 * @param string $sql SQL Statement to execute
1502 * @param bool $dieOnError True if we want to call die if the query returns errors
1503 * @param string $msg Message to log if error occurs
1504 * @param bool $suppress Flag to suppress all error output unless in debug logging mode.
1505 * @return resource result set
1507 abstract public function query(
1509 $dieOnError = false,
1515 * Runs a limit query: one where we specify where to start getting records and how many to get
1517 * @param string $sql
1520 * @param boolean $dieOnError
1521 * @param string $msg
1522 * @return resource query result
1524 abstract function limitQuery(
1528 $dieOnError = false,
1532 * Frees out previous results
1534 * @param resource $result optional, pass if you want to free a single result instead of all results
1536 protected function freeResult(
1540 $free_result = $this->backendFunctions['free_result'];
1541 if(!$result && $this->lastResult){
1542 $result = current($this->lastResult);
1544 $free_result($result);
1545 $result = next($this->lastResult);
1547 $this->lastResult = array();
1550 $free_result($result);
1555 * Runs a query and returns a single row
1557 * @param string $sql SQL Statement to execute
1558 * @param bool $dieOnError True if we want to call die if the query returns errors
1559 * @param string $msg Message to log if error occurs
1560 * @param bool $suppress Message to log if error occurs
1561 * @return array single row from the query
1563 public function getOne(
1565 $dieOnError = false,
1570 $GLOBALS['log']->info("Get One: . |$sql|");
1571 $this->checkConnection();
1572 if(!($this instanceof MysqlManager) || stripos($sql, ' LIMIT ') === false) {
1573 $queryresult = $this->limitQuery($sql, 0, 1, $dieOnError, $msg);
1575 // backward compatibility with queries having LIMIT
1576 $queryresult = $this->query($sql, $dieOnError, $msg);
1581 $row = $this->fetchByAssoc($queryresult);
1585 $this->checkError($msg.' Get One Failed:' . $sql, $dieOnError);
1587 $this->freeResult($queryresult);
1588 return array_shift($row);
1592 * will return the associative array of the row for a query or false if more than one row was returned
1596 * @param string $sql
1597 * @param bool $dieonerror
1598 * @param string $msg
1599 * @param bool $encode
1600 * @return array associative array of the row or false
1602 public function requireSingleRow(
1604 $dieOnError = false,
1609 $GLOBALS['log']->info('call to DBManager::requireSingleRow() is deprecated');
1610 $result = $this->limitQuery($sql,0,2, $dieOnError, $msg);
1613 while ($row = $this->fetchByAssoc($result)){
1614 if(!$firstRow)$firstRow = $row;
1625 * Returns the description of fields based on the result
1627 * @param resource $result
1628 * @param boolean $make_lower_case
1629 * @return array field array
1631 abstract public function getFieldsArray(
1633 $make_lower_case = false);
1636 * Returns the number of rows returned by the result
1638 * @param resource $result
1641 public function getRowCount(
1645 $row_count = $this->backendFunctions['row_count'];
1646 if(isset($result) && !empty($result)){
1647 return $row_count($result);
1653 * Returns the number of rows affected by the last query
1657 public function getAffectedRowCount()
1659 $affected_row_count = $this->backendFunctions['affected_row_count'];
1660 return $affected_row_count($this->getDatabase());
1664 * Fetches the next row in the query result into an associative array
1666 * @param resource $result
1667 * @param int $rowNum optional, specify a certain row to return
1668 * @param bool $encode optional, true if we want html encode the resulting array
1669 * @return array returns false if there are no more rows available to fetch
1671 abstract public function fetchByAssoc(
1677 * Connects to the database backend
1679 * Takes in the database settings and opens a database connection based on those
1680 * will open either a persistent or non-persistent connection.
1681 * If a persistent connection is desired but not available it will defualt to non-persistent
1683 * configOptions must include
1684 * db_host_name - server ip
1685 * db_user_name - database user name
1686 * db_password - database password
1688 * @param array $configOptions
1689 * @param boolean $dieOnError
1691 abstract public function connect(
1692 array $configOptions = null,
1697 * Disconnects from the database
1699 * Also handles any cleanup needed
1701 public function disconnect()
1703 $GLOBALS['log']->debug('Calling DBManager::disconnect()');
1704 $close = $this->backendFunctions['close'];
1705 if(isset($this->database)){
1706 $this->freeResult();
1707 if ( is_resource($this->database) || is_object($this->database) )
1708 $close($this->database);
1709 unset($this->database);
1714 * Returns the field description for a given field in table
1716 * @param string $name
1717 * @param string $tablename
1720 protected function describeField(
1725 global $table_descriptions;
1726 if(isset($table_descriptions[$tablename])
1727 && isset($table_descriptions[$tablename][$name]))
1728 return $table_descriptions[$tablename][$name];
1730 $table_descriptions[$tablename] = array();
1731 $table_descriptions[$tablename][$name] = $this->helper->get_columns($tablename);
1733 if(isset($table_descriptions[$tablename][$name]))
1734 return $table_descriptions[$tablename][$name];
1740 * Returns the index description for a given index in table
1742 * @param string $name
1743 * @param string $tablename
1746 protected function describeIndex(
1751 global $table_descriptions;
1752 if(isset($table_descriptions[$tablename]) && isset($table_descriptions[$tablename]['indexes']) && isset($table_descriptions[$tablename]['indexes'][$name])){
1753 return $table_descriptions[$tablename]['indexes'][$name];
1756 $table_descriptions[$tablename]['indexes'] = array();
1758 $result = $this->helper->get_indices($tablename);
1760 foreach($result as $index_name => $row) {
1761 if(!isset($table_descriptions[$tablename]['indexes'][$index_name])){
1762 $table_descriptions[$tablename]['indexes'][$index_name] = array();
1764 $table_descriptions[$tablename]['indexes'][$index_name]['Column_name'] = $row;
1767 if(isset($table_descriptions[$tablename]['indexes'][$name])){
1768 return $table_descriptions[$tablename]['indexes'][$name];
1775 * Returns an array of table for this database
1777 * @return $tables an array of with table names
1778 * @return false if no tables found
1780 abstract public function getTablesArray();
1783 * Return's the version of the database
1787 abstract public function version();
1790 * Checks if a table with the name $tableName exists
1791 * and returns true if it does or false otherwise
1793 * @param string $tableName
1796 abstract public function tableExists($tableName);
1799 * Truncates a string to a given length
1801 * @param string $string
1802 * @param int $len length to trim to
1805 public function truncate(
1810 if ( is_numeric($len) && $len > 0)
1812 $string = mb_substr($string,0,(int) $len, "UTF-8");
1819 * Given a sql stmt attempt to parse it into the sql and the tokens. Then return the index of this prepared statement
1820 * Tokens can come in the following forms:
1821 * ? - a scalar which will be quoted
1822 * ! - a literal which will not be quoted
1823 * & - binary data to read from a file
1825 * @param string $sql The sql to parse
1826 * @return int index of the prepared statement to be used with execute
1828 public function prepareQuery($sql){
1829 //parse out the tokens
1830 $tokens = preg_split('/((?<!\\\)[&?!])/', $sql, -1, PREG_SPLIT_DELIM_CAPTURE);
1832 //maintain a count of the actual tokens for quick reference in execute
1836 foreach ($tokens as $key => $val) {
1846 //escape any special characters
1847 $tokens[$key] = preg_replace('/\\\([&?!])/', "\\1", $val);
1848 $sqlStr .= $tokens[$key];
1853 $this->preparedTokens[] = array('tokens' => $tokens, 'tokenCount' => $count, 'sqlString' => $sqlStr);
1854 end($this->preparedTokens);
1855 return key($this->preparedTokens);
1859 * Takes a prepared stmt index and the data to replace and creates the query and runs it.
1861 * @param int $stmt The index of the prepared statement from preparedTokens
1862 * @param array $data The array of data to replace the tokens with.
1863 * @return resource result set or false on error
1865 public function executePreparedQuery($stmt, $data = array()){
1866 if(!empty($this->preparedTokens[$stmt])){
1867 if(!is_array($data)){
1868 $data = array($data);
1871 $pTokens = $this->preparedTokens[$stmt];
1873 //ensure that the number of data elements matches the number of replacement tokens
1874 //we found in prepare().
1875 if(count($data) != $pTokens['tokenCount']){
1876 //error the data count did not match the token count
1882 $tokens = $pTokens['tokens'];
1883 foreach ($tokens as $val) {
1886 $query .= $this->quote($data[$dataIndex++]);
1889 $filename = $data[$dataIndex++];
1890 $query .= sugar_file_get_contents($filename);
1893 $query .= $data[$dataIndex++];
1900 return $this->query($query);
1907 * Run both prepare and execute without the client having to run both individually.
1909 * @param string $sql The sql to parse
1910 * @param array $data The array of data to replace the tokens with.
1911 * @return resource result set or false on error
1913 public function pQuery($sql, $data = array()){
1914 $stmt = $this->prepareQuery($sql);
1915 return $this->executePreparedQuery($stmt, $data);
1919 * Use when you need to convert a database string to a different value; this function does it in a
1920 * database-backend aware way
1922 * @param string $string database string to convert
1923 * @param string $type type of conversion to do
1924 * @param array $additional_parameters optional, additional parameters to pass to the db function
1925 * @param array $additional_parameters_oracle_only optional, additional parameters to pass to the db function on oracle only
1928 public function convert(
1931 array $additional_parameters = array(),
1932 array $additional_parameters_oracle_only = array()
1939 * Returns the database string needed for concatinating multiple database strings together
1941 * @param string $table table name of the database fields to concat
1942 * @param array $fields fields in the table to concat together
1945 abstract public function concat(
1951 * Undoes database conversion
1953 * @param string $string database string to convert
1954 * @param string $type type of conversion to do
1957 public function fromConvert(