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-2013 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 ********************************************************************************/
92 * Base database driver implementation
95 abstract class DBManager
101 public $database = null;
104 * Indicates whether we should die when we get an error from the DB
106 protected $dieOnError = false;
109 * Indicates whether we should html encode the results from a query by default
111 protected $encode = true;
114 * Records the execution time of the last query
116 protected $query_time = 0;
119 * Last error message from the DB backend
121 protected $last_error = false;
124 * Registry of available result sets
126 protected $lastResult;
129 * Current query count
131 private static $queryCount = 0;
134 * Query threshold limit
136 private static $queryLimit = 0;
139 * Array of prepared statements and their correspoding parsed tokens
141 protected $preparedTokens = array();
159 protected static $table_descriptions = array();
165 protected static $index_descriptions = array();
168 * Maximum length of identifiers
172 protected $maxNameLengths = array(
181 * Higher priority drivers override lower priority ones
184 public $priority = 0;
187 * Driver name label, for install
198 protected $type_map = array();
201 * Type classification into:
209 protected $type_class = array(
218 'datetime' => 'date',
219 'datetimecombo' => 'date',
223 'currency' => 'float',
224 'decimal' => 'float',
225 'decimal2' => 'float',
229 * Capabilities this DB supports. Supported list:
230 * affected_rows Can report query affected rows for UPDATE/DELETE
231 * implement getAffectedRowCount()
232 * select_rows Can report row count for SELECT
233 * implement getRowCount()
234 * case_sensitive Supports case-sensitive text columns
235 * fulltext Supports fulltext search indexes
236 * inline_keys Supports defining keys together with the table
237 * auto_increment_sequence Autoincrement support implemented as sequence
238 * limit_subquery Supports LIMIT clauses in subqueries
239 * create_user Can create users for Sugar
240 * create_db Can create databases
241 * collation Supports setting collations
242 * disable_keys Supports temporarily disabling keys (for upgrades, etc.)
246 * fix:expandDatabase - needs expandDatabase fix, see expandDatabase.php
247 * TODO: verify if we need these cases
249 protected $capabilities = array();
255 protected $options = array();
260 public function __construct()
262 $this->timedate = TimeDate::getInstance();
263 $this->log = $GLOBALS['log'];
264 $this->helper = $this; // compatibility
268 * Wrapper for those trying to access the private and protected class members directly
269 * @param string $p var name
272 public function __get($p)
274 $this->log->info('Call to DBManager::$'.$p.' is deprecated');
279 * Returns the current database handle
282 public function getDatabase()
284 $this->checkConnection();
285 return $this->database;
289 * Returns this instance's DBHelper
290 * Actually now returns $this
294 public function getHelper()
300 * Checks for error happening in the database
302 * @param string $msg message to prepend to the error message
303 * @param bool $dieOnError true if we want to die immediately on error
304 * @return bool True if there was an error
306 public function checkError($msg = '', $dieOnError = false)
308 if (empty($this->database)) {
309 $this->registerError($msg, "Database Is Not Connected", $dieOnError);
313 $dberror = $this->lastDbError();
314 if($dberror === false) {
315 $this->last_error = false;
318 $this->registerError($msg, $dberror, $dieOnError);
323 * Register database error
324 * If die-on-error flag is set, logs the message and dies,
325 * otherwise sets last_error to the message
326 * @param string $userMessage Message from function user
327 * @param string $message Message from SQL driver
328 * @param bool $dieOnError
330 protected function registerError($userMessage, $message, $dieOnError = false)
332 if(!empty($message)) {
333 if(!empty($userMessage)) {
334 $message = "$userMessage: $message";
336 if(empty($message)) {
337 $message = "Database error";
339 $this->log->fatal($message);
340 if ($dieOnError || $this->dieOnError) {
341 if(isset($GLOBALS['app_strings']['ERR_DB_FAIL'])) {
342 sugar_die($GLOBALS['app_strings']['ERR_DB_FAIL']);
344 sugar_die("Database error. Please check sugarcrm.log for details.");
347 $this->last_error = $message;
353 * Return DB error message for the last query executed
354 * @return string Last error message
356 public function lastError()
358 return $this->last_error;
362 * This method is called by every method that runs a query.
363 * If slow query dumping is turned on and the query time is beyond
364 * the time limit, we will log the query. This function may do
365 * additional reporting or log in a different area in the future.
367 * @param string $query query to log
368 * @return boolean true if the query was logged, false otherwise
370 protected function dump_slow_queries($query)
372 global $sugar_config;
374 $do_the_dump = isset($sugar_config['dump_slow_queries'])
375 ? $sugar_config['dump_slow_queries'] : false;
376 $slow_query_time_msec = isset($sugar_config['slow_query_time_msec'])
377 ? $sugar_config['slow_query_time_msec'] : 5000;
380 if($slow_query_time_msec < ($this->query_time * 1000)) {
381 // Then log both the query and the query time
382 $this->log->fatal('Slow Query (time:'.$this->query_time."\n".$query);
390 * Scans order by to ensure that any field being ordered by is.
392 * It will throw a warning error to the log file - fatal if slow query logging is enabled
394 * @param string $sql query to be run
395 * @param bool $object_name optional, object to look up indices in
396 * @return bool true if an index is found false otherwise
398 protected function checkQuery($sql, $object_name = false)
401 preg_match_all("'.* FROM ([^ ]*).* ORDER BY (.*)'is", $sql, $match);
403 if (!empty($match[1][0]))
404 $table = $match[1][0];
408 if (!empty($object_name) && !empty($GLOBALS['dictionary'][$object_name]))
409 $indices = $GLOBALS['dictionary'][$object_name]['indices'];
411 if (empty($indices)) {
412 foreach ( $GLOBALS['dictionary'] as $current ) {
413 if ($current['table'] == $table){
414 $indices = $current['indices'];
419 if (empty($indices)) {
420 $this->log->warn('CHECK QUERY: Could not find index definitions for table ' . $table);
423 if (!empty($match[2][0])) {
424 $orderBys = explode(' ', $match[2][0]);
425 foreach ($orderBys as $orderBy){
426 $orderBy = trim($orderBy);
429 $orderBy = strtolower($orderBy);
430 if ($orderBy == 'asc' || $orderBy == 'desc')
433 $orderBy = str_replace(array($table . '.', ','), '', $orderBy);
435 foreach ($indices as $index)
436 if (empty($index['db']) || $index['db'] == $this->dbType)
437 foreach ($index['fields'] as $field)
438 if ($field == $orderBy)
441 $warning = 'Missing Index For Order By Table: ' . $table . ' Order By:' . $orderBy ;
442 if (!empty($GLOBALS['sugar_config']['dump_slow_queries']))
443 $this->log->fatal('CHECK QUERY:' .$warning);
445 $this->log->warn('CHECK QUERY:' .$warning);
452 * Returns the time the last query took to execute
456 public function getQueryTime()
458 return $this->query_time;
462 * Checks the current connection; if it is not connected then reconnect
464 public function checkConnection()
466 $this->last_error = '';
467 if (!isset($this->database))
472 * Sets the dieOnError value
476 public function setDieOnError($value)
478 $this->dieOnError = $value;
482 * Implements a generic insert for any bean.
484 * @param SugarBean $bean SugarBean instance
485 * @return bool query result
488 public function insert(SugarBean $bean)
490 $sql = $this->insertSQL($bean);
491 $tablename = $bean->getTableName();
492 $msg = "Error inserting into table: $tablename:";
493 return $this->query($sql,true,$msg);
497 * Insert data into table by parameter definition
498 * @param string $table Table name
499 * @param array $field_defs Definitions in vardef-like format
500 * @param array $data Key/value to insert
501 * @param array $field_map Fields map from SugarBean
502 * @param bool $execute Execute or return query?
503 * @return bool query result
505 public function insertParams($table, $field_defs, $data, $field_map = null, $execute = true)
508 foreach ($field_defs as $field => $fieldDef)
510 if (isset($fieldDef['source']) && $fieldDef['source'] != 'db') continue;
511 //custom fields handle there save seperatley
512 if(!empty($field_map) && !empty($field_map[$field]['custom_type'])) continue;
514 if(isset($data[$field])) {
515 // clean the incoming value..
516 $val = from_html($data[$field]);
518 if(isset($fieldDef['default']) && strlen($fieldDef['default']) > 0) {
519 $val = $fieldDef['default'];
525 //handle auto increment values here - we may have to do something like nextval for oracle
526 if (!empty($fieldDef['auto_increment'])) {
527 $auto = $this->getAutoIncrementSQL($table, $fieldDef['name']);
529 $values[$field] = $auto;
531 } elseif ($fieldDef['name'] == 'deleted') {
532 $values['deleted'] = (int)$val;
534 // need to do some thing about types of values
535 if(!is_null($val) || !empty($fieldDef['required'])) {
536 $values[$field] = $this->massageValue($val, $fieldDef);
542 return $execute?true:''; // no columns set
544 // get the entire sql
545 $query = "INSERT INTO $table (".implode(",", array_keys($values)).")
546 VALUES (".implode(",", $values).")";
547 return $execute?$this->query($query):$query;
551 * Implements a generic update for any bean
553 * @param SugarBean $bean Sugarbean instance
554 * @param array $where values with the keys as names of fields.
555 * If we want to pass multiple values for a name, pass it as an array
556 * If where is not passed, it defaults to id of table
557 * @return bool query result
560 public function update(SugarBean $bean, array $where = array())
562 $sql = $this->updateSQL($bean, $where);
563 $tablename = $bean->getTableName();
564 $msg = "Error updating table: $tablename:";
565 return $this->query($sql,true,$msg);
569 * Implements a generic delete for any bean identified by id
571 * @param SugarBean $bean Sugarbean instance
572 * @param array $where values with the keys as names of fields.
573 * If we want to pass multiple values for a name, pass it as an array
574 * If where is not passed, it defaults to id of table
575 * @return bool query result
577 public function delete(SugarBean $bean, array $where = array())
579 $sql = $this->deleteSQL($bean, $where);
580 $tableName = $bean->getTableName();
581 $msg = "Error deleting from table: ".$tableName. ":";
582 return $this->query($sql,true,$msg);
586 * Implements a generic retrieve for any bean identified by id
588 * If we want to pass multiple values for a name, pass it as an array
589 * If where is not passed, it defaults to id of table
591 * @param SugarBean $bean Sugarbean instance
592 * @param array $where values with the keys as names of fields.
593 * @return resource result from the query
595 public function retrieve(SugarBean $bean, array $where = array())
597 $sql = $this->retrieveSQL($bean, $where);
598 $tableName = $bean->getTableName();
599 $msg = "Error retriving values from table:".$tableName. ":";
600 return $this->query($sql,true,$msg);
604 * Implements a generic retrieve for a collection of beans.
606 * These beans will be joined in the sql by the key attribute of field defs.
607 * Currently, this function does support outer joins.
609 * @param array $beans Sugarbean instance(s)
610 * @param array $cols columns to be returned with the keys as names of bean as identified by
611 * get_class of bean. Values of this array is the array of fieldDefs to be returned for a bean.
612 * If an empty array is passed, all columns are selected.
613 * @param array $where values with the keys as names of bean as identified by get_class of bean
614 * Each value at the first level is an array of values for that bean identified by name of fields.
615 * If we want to pass multiple values for a name, pass it as an array
616 * If where is not passed, all the rows will be returned.
619 public function retrieveView(array $beans, array $cols = array(), array $where = array())
621 $sql = $this->retrieveViewSQL($beans, $cols, $where);
622 $msg = "Error retriving values from View Collection:";
623 return $this->query($sql,true,$msg);
628 * Implements creation of a db table for a bean.
630 * NOTE: does not handle out-of-table constraints, use createConstraintSQL for that
631 * @param SugarBean $bean Sugarbean instance
633 public function createTable(SugarBean $bean)
635 $sql = $this->createTableSQL($bean);
636 $tablename = $bean->getTableName();
637 $msg = "Error creating table: $tablename:";
638 $this->query($sql,true,$msg);
639 if(!$this->supports("inline_keys")) {
640 // handle constraints and indices
641 $indicesArr = $this->createConstraintSql($bean);
642 if (count($indicesArr) > 0)
643 foreach ($indicesArr as $indexSql)
644 $this->query($indexSql, true, $msg);
649 * returns SQL to create constraints or indices
651 * @param SugarBean $bean SugarBean instance
652 * @return array list of SQL statements
654 protected function createConstraintSql(SugarBean $bean)
656 return $this->getConstraintSql($bean->getIndices(), $bean->getTableName());
660 * Implements creation of a db table
662 * @param string $tablename
663 * @param array $fieldDefs Field definitions, in vardef format
664 * @param array $indices Index definitions, in vardef format
665 * @param string $engine Engine parameter, used for MySQL engine so far
666 * @todo: refactor engine param to be more generic
667 * @return bool success value
669 public function createTableParams($tablename, $fieldDefs, $indices, $engine = null)
671 if (!empty($fieldDefs)) {
672 $sql = $this->createTableSQLParams($tablename, $fieldDefs, $indices, $engine);
675 $msg = "Error creating table: $tablename";
676 $res = ($res and $this->query($sql,true,$msg));
678 if(!$this->supports("inline_keys")) {
679 // handle constraints and indices
680 $indicesArr = $this->getConstraintSql($indices, $tablename);
681 if (count($indicesArr) > 0)
682 foreach ($indicesArr as $indexSql)
683 $res = ($res and $this->query($indexSql, true, "Error creating indexes"));
691 * Implements repair of a db table for a bean.
693 * @param SugarBean $bean SugarBean instance
694 * @param bool $execute true if we want the action to take place, false if we just want the sql returned
695 * @return string SQL statement or empty string, depending upon $execute
697 public function repairTable(SugarBean $bean, $execute = true)
699 $indices = $bean->getIndices();
700 $fielddefs = $bean->getFieldDefinitions();
701 $tablename = $bean->getTableName();
703 //Clean the indexes to prevent duplicate definitions
704 $new_index = array();
705 foreach($indices as $ind_def){
706 $new_index[$ind_def['name']] = $ind_def;
708 //jc: added this for beans that do not actually have a table, namely
709 //ForecastOpportunities
710 if($tablename == 'does_not_exist' || $tablename == '')
715 if (isset($dictionary[$bean->getObjectName()]['engine']) && !empty($dictionary[$bean->getObjectName()]['engine']) )
716 $engine = $dictionary[$bean->getObjectName()]['engine'];
718 return $this->repairTableParams($tablename, $fielddefs,$new_index,$execute,$engine);
722 * Can this field be null?
723 * Auto-increment and ID fields can not be null
724 * @param array $vardef
727 protected function isNullable($vardef)
730 if(isset($vardef['isnull']) && (strtolower($vardef['isnull']) == 'false' || $vardef['isnull'] === false)
731 && !empty($vardef['required'])) {
732 /* required + is_null=false => not null */
735 if(empty($vardef['auto_increment']) && (empty($vardef['type']) || $vardef['type'] != 'id')
736 && (empty($vardef['dbType']) || $vardef['dbType'] != 'id')
737 && (empty($vardef['name']) || ($vardef['name'] != 'id' && $vardef['name'] != 'deleted'))
746 * Builds the SQL commands that repair a table structure
748 * @param string $tablename
749 * @param array $fielddefs Field definitions, in vardef format
750 * @param array $indices Index definitions, in vardef format
751 * @param bool $execute optional, true if we want the queries executed instead of returned
752 * @param string $engine optional, MySQL engine
753 * @todo: refactor engine param to be more generic
756 public function repairTableParams($tablename, $fielddefs, $indices, $execute = true, $engine = null)
758 //jc: had a bug when running the repair if the tablename is blank the repair will
759 //fail when it tries to create a repair table
760 if ($tablename == '' || empty($fielddefs))
763 //if the table does not exist create it and we are done
764 $sql = "/* Table : $tablename */\n";
765 if (!$this->tableExists($tablename)) {
766 $createtablesql = $this->createTableSQLParams($tablename,$fielddefs,$indices,$engine);
767 if($execute && $createtablesql){
768 $this->createTableParams($tablename,$fielddefs,$indices,$engine);
771 $sql .= "/* MISSING TABLE: {$tablename} */\n";
772 $sql .= $createtablesql . "\n";
776 $compareFieldDefs = $this->get_columns($tablename);
777 $compareIndices = $this->get_indices($tablename);
779 $take_action = false;
781 // do column comparisons
782 $sql .= "/*COLUMNS*/\n";
783 foreach ($fielddefs as $name => $value) {
784 if (isset($value['source']) && $value['source'] != 'db')
787 // Bug #42406. Skipping breaked vardef without type or name
788 if (isset($value['name']) == false || $value['name'] == false)
790 $sql .= "/* NAME IS MISSING IN VARDEF $tablename::$name */\n";
793 else if (isset($value['type']) == false || $value['type'] == false)
795 $sql .= "/* TYPE IS MISSING IN VARDEF $tablename::$name */\n";
799 $name = strtolower($value['name']);
800 // add or fix the field defs per what the DB is expected to give us back
801 $this->massageFieldDef($value,$tablename);
803 $ignorerequired=false;
805 //Do not track requiredness in the DB, auto_increment, ID,
806 // and deleted fields are always required in the DB, so don't force those
807 if ($this->isNullable($value)) {
808 $value['required'] = false;
810 //Should match the conditions in DBManager::oneColumnSQLRep for DB required fields, type='id' fields will sometimes
812 //come into this function as 'type' = 'char', 'dbType' = 'id' without required set in $value. Assume they are correct and leave them alone.
813 else if (($name == 'id' || $value['type'] == 'id' || (isset($value['dbType']) && $value['dbType'] == 'id'))
814 && (!isset($value['required']) && isset($compareFieldDefs[$name]['required'])))
816 $value['required'] = $compareFieldDefs[$name]['required'];
819 if ( !isset($compareFieldDefs[$name]) ) {
820 // ok we need this field lets create it
821 $sql .= "/*MISSING IN DATABASE - $name - ROW*/\n";
822 $sql .= $this->addColumnSQL($tablename, $value) . "\n";
824 $this->addColumn($tablename, $value);
826 } elseif ( !$this->compareVarDefs($compareFieldDefs[$name],$value)) {
827 //fields are different lets alter it
828 $sql .= "/*MISMATCH WITH DATABASE - $name - ROW ";
829 foreach($compareFieldDefs[$name] as $rKey => $rValue) {
830 $sql .= "[$rKey] => '$rValue' ";
833 $sql .= "/* VARDEF - $name - ROW";
834 foreach($value as $rKey => $rValue) {
835 $sql .= "[$rKey] => '$rValue' ";
839 //jc: oracle will complain if you try to execute a statement that sets a column to (not) null
840 //when it is already (not) null
841 if ( isset($value['isnull']) && isset($compareFieldDefs[$name]['isnull']) &&
842 $value['isnull'] === $compareFieldDefs[$name]['isnull']) {
843 unset($value['required']);
844 $ignorerequired=true;
847 //dwheeler: Once a column has been defined as null, we cannot try to force it back to !null
848 if ((isset($value['required']) && ($value['required'] === true || $value['required'] == 'true' || $value['required'] === 1))
849 && (empty($compareFieldDefs[$name]['required']) || $compareFieldDefs[$name]['required'] != 'true'))
851 $ignorerequired = true;
853 $altersql = $this->alterColumnSQL($tablename, $value,$ignorerequired);
854 if(is_array($altersql)) {
855 $altersql = join("\n", $altersql);
857 $sql .= $altersql . "\n";
859 $this->alterColumn($tablename, $value, $ignorerequired);
865 // do index comparisons
866 $sql .= "/* INDEXES */\n";
867 $correctedIndexs = array();
869 $compareIndices_case_insensitive = array();
871 // do indices comparisons case-insensitive
872 foreach($compareIndices as $k => $value){
873 $value['name'] = strtolower($value['name']);
874 $compareIndices_case_insensitive[strtolower($k)] = $value;
876 $compareIndices = $compareIndices_case_insensitive;
877 unset($compareIndices_case_insensitive);
879 foreach ($indices as $value) {
880 if (isset($value['source']) && $value['source'] != 'db')
884 $validDBName = $this->getValidDBName($value['name'], true, 'index', true);
885 if (isset($compareIndices[$validDBName])) {
886 $value['name'] = $validDBName;
888 $name = strtolower($value['name']);
890 //Don't attempt to fix the same index twice in one pass;
891 if (isset($correctedIndexs[$name]))
894 //don't bother checking primary nothing we can do about them
895 if (isset($value['type']) && $value['type'] == 'primary')
898 //database helpers do not know how to handle full text indices
899 if ($value['type']=='fulltext')
902 if ( in_array($value['type'],array('alternate_key','foreign')) )
903 $value['type'] = 'index';
905 if ( !isset($compareIndices[$name]) ) {
906 //First check if an index exists that doesn't match our name, if so, try to rename it
908 foreach ($compareIndices as $ex_name => $ex_value) {
909 if($this->compareVarDefs($ex_value, $value, true)) {
915 $sql .= "/*MISSNAMED INDEX IN DATABASE - $name - $ex_name */\n";
916 $rename = $this->renameIndexDefs($ex_value, $value, $tablename);
918 $this->query($rename, true, "Cannot rename index");
920 $sql .= is_array($rename)?join("\n", $rename). "\n":$rename."\n";
923 // ok we need this field lets create it
924 $sql .= "/*MISSING INDEX IN DATABASE - $name -{$value['type']} ROW */\n";
925 $sql .= $this->addIndexes($tablename,array($value), $execute) . "\n";
928 $correctedIndexs[$name] = true;
929 } elseif ( !$this->compareVarDefs($compareIndices[$name],$value) ) {
930 // fields are different lets alter it
931 $sql .= "/*INDEX MISMATCH WITH DATABASE - $name - ROW ";
932 foreach ($compareIndices[$name] as $n1 => $t1) {
934 if ( $n1 == 'fields' )
935 foreach($t1 as $rKey => $rValue)
936 $sql .= "[$rKey] => '$rValue' ";
941 $sql .= "/* VARDEF - $name - ROW";
942 foreach ($value as $n1 => $t1) {
944 if ( $n1 == 'fields' )
945 foreach ($t1 as $rKey => $rValue)
946 $sql .= "[$rKey] => '$rValue' ";
951 $sql .= $this->modifyIndexes($tablename,array($value), $execute) . "\n";
953 $correctedIndexs[$name] = true;
957 return ($take_action === true) ? $sql : '';
961 * Compares two vardefs
963 * @param array $fielddef1 This is from the database
964 * @param array $fielddef2 This is from the vardef
965 * @param bool $ignoreName Ignore name-only differences?
966 * @return bool true if they match, false if they don't
968 public function compareVarDefs($fielddef1, $fielddef2, $ignoreName = false)
970 foreach ( $fielddef1 as $key => $value ) {
971 if ($key == 'name' && $ignoreName)
973 if (isset($fielddef2[$key]))
975 if (!is_array($fielddef1[$key]) && !is_array($fielddef2[$key]))
977 if (strtolower($fielddef1[$key]) == strtolower($fielddef2[$key]))
984 if (array_map('strtolower', $fielddef1[$key]) == array_map('strtolower',$fielddef2[$key]))
990 //Ignore len if its not set in the vardef
991 if ($key == 'len' && empty($fielddef2[$key]))
993 // if the length in db is greather than the vardef, ignore it
994 if ($key == 'len' && ($fielddef1[$key] >= $fielddef2[$key])) {
1004 * Compare a field in two tables
1006 * @param string $name field name
1007 * @param string $table1
1008 * @param string $table2
1009 * @return array array with keys 'msg','table1','table2'
1011 public function compareFieldInTables($name, $table1, $table2)
1013 $row1 = $this->describeField($name, $table1);
1014 $row2 = $this->describeField($name, $table2);
1015 $returnArray = array(
1021 $ignore_filter = array('Key'=>1);
1024 // Exists on table1 but not table2
1025 $returnArray['msg'] = 'not_exists_table2';
1028 if (sizeof($row1) != sizeof($row2)) {
1029 $returnArray['msg'] = 'no_match';
1032 $returnArray['msg'] = 'match';
1033 foreach($row1 as $key => $value){
1034 //ignore keys when checking we will check them when we do the index check
1035 if( !isset($ignore_filter[$key]) && (!isset($row2[$key]) || $row1[$key] !== $row2[$key])){
1036 $returnArray['msg'] = 'no_match';
1043 $returnArray['msg'] = 'not_exists_table1';
1046 return $returnArray;
1050 // * Compare an index in two different tables
1052 // * @param string $name index name
1053 // * @param string $table1
1054 // * @param string $table2
1055 // * @return array array with keys 'msg','table1','table2'
1057 // public function compareIndexInTables($name, $table1, $table2)
1059 // $row1 = $this->describeIndex($name, $table1);
1060 // $row2 = $this->describeIndex($name, $table2);
1061 // $returnArray = array(
1062 // 'table1' => $row1,
1063 // 'table2' => $row2,
1064 // 'msg' => 'error',
1066 // $ignore_filter = array('Table'=>1, 'Seq_in_index'=>1,'Cardinality'=>1, 'Sub_part'=>1, 'Packed'=>1, 'Comment'=>1);
1070 // //Exists on table1 but not table2
1071 // $returnArray['msg'] = 'not_exists_table2';
1074 // if (sizeof($row1) != sizeof($row2)) {
1075 // $returnArray['msg'] = 'no_match';
1078 // $returnArray['msg'] = 'match';
1079 // foreach ($row1 as $fname => $fvalue) {
1080 // if (!isset($row2[$fname])) {
1081 // $returnArray['msg'] = 'no_match';
1083 // if(!isset($ignore_filter[$fname]) && $row1[$fname] != $row2[$fname]){
1084 // $returnArray['msg'] = 'no_match';
1090 // $returnArray['msg'] = 'not_exists_table1';
1093 // return $returnArray;
1098 * Creates an index identified by name on the given fields.
1100 * @param SugarBean $bean SugarBean instance
1101 * @param array $fieldDefs Field definitions, in vardef format
1102 * @param string $name index name
1103 * @param bool $unique optional, true if we want to create an unique index
1104 * @return bool query result
1106 public function createIndex(SugarBean $bean, $fieldDefs, $name, $unique = true)
1108 $sql = $this->createIndexSQL($bean, $fieldDefs, $name, $unique);
1109 $tablename = $bean->getTableName();
1110 $msg = "Error creating index $name on table: $tablename:";
1111 return $this->query($sql,true,$msg);
1115 * returns a SQL query that creates the indices as defined in metadata
1116 * @param array $indices Assoc array with index definitions from vardefs
1117 * @param string $table Focus table
1118 * @return array Array of SQL queries to generate indices
1120 public function getConstraintSql($indices, $table)
1122 if (!$this->isFieldArray($indices))
1123 $indices = array($indices);
1127 foreach ($indices as $index) {
1128 if(!empty($index['db']) && $index['db'] != $this->dbType)
1130 if (isset($index['source']) && $index['source'] != 'db')
1133 $sql = $this->add_drop_constraint($table, $index);
1144 * Adds a new indexes
1146 * @param string $tablename
1147 * @param array $indexes indexes to add
1148 * @param bool $execute true if we want to execute the returned sql statement
1149 * @return string SQL statement
1151 public function addIndexes($tablename, $indexes, $execute = true)
1153 $alters = $this->getConstraintSql($indexes, $tablename);
1155 foreach($alters as $sql) {
1156 $this->query($sql, true, "Error adding index: ");
1159 if(!empty($alters)) {
1160 $sql = join(";\n", $alters).";\n";
1170 * @param string $tablename
1171 * @param array $indexes indexes to drop
1172 * @param bool $execute true if we want to execute the returned sql statement
1173 * @return string SQL statement
1175 public function dropIndexes($tablename, $indexes, $execute = true)
1178 foreach ($indexes as $index) {
1179 $name =$index['name'];
1180 $sqls[$name] = $this->add_drop_constraint($tablename,$index,true);
1182 if (!empty($sqls) && $execute) {
1183 foreach($sqls as $name => $sql) {
1184 unset(self::$index_descriptions[$tablename][$name]);
1189 return join(";\n",$sqls).";";
1198 * @param string $tablename
1199 * @param array $indexes indexes to modify
1200 * @param bool $execute true if we want to execute the returned sql statement
1201 * @return string SQL statement
1203 public function modifyIndexes($tablename, $indexes, $execute = true)
1205 return $this->dropIndexes($tablename, $indexes, $execute)."\n".
1206 $this->addIndexes($tablename, $indexes, $execute);
1210 * Adds a column to table identified by field def.
1212 * @param string $tablename
1213 * @param array $fieldDefs
1214 * @return bool query result
1216 public function addColumn($tablename, $fieldDefs)
1218 $sql = $this->addColumnSQL($tablename, $fieldDefs);
1219 if ($this->isFieldArray($fieldDefs)){
1221 foreach ($fieldDefs as $fieldDef)
1222 $columns[] = $fieldDef['name'];
1223 $columns = implode(",", $columns);
1226 $columns = $fieldDefs['name'];
1228 $msg = "Error adding column(s) $columns on table: $tablename:";
1229 return $this->query($sql,true,$msg);
1233 * Alters old column identified by oldFieldDef to new fieldDef.
1235 * @param string $tablename
1236 * @param array $newFieldDef
1237 * @param bool $ignoreRequired optional, true if we are ignoring this being a required field
1238 * @return bool query result
1240 public function alterColumn($tablename, $newFieldDef, $ignoreRequired = false)
1242 $sql = $this->alterColumnSQL($tablename, $newFieldDef,$ignoreRequired);
1243 if ($this->isFieldArray($newFieldDef)){
1245 foreach ($newFieldDef as $fieldDef) {
1246 $columns[] = $fieldDef['name'];
1248 $columns = implode(",", $columns);
1251 $columns = $newFieldDef['name'];
1254 $msg = "Error altering column(s) $columns on table: $tablename:";
1255 $res = $this->query($sql,true,$msg);
1257 $this->getTableDescription($tablename, true); // reload table description after altering
1263 * Drops the table associated with a bean
1265 * @param SugarBean $bean SugarBean instance
1266 * @return bool query result
1268 public function dropTable(SugarBean $bean)
1270 return $this->dropTableName($bean->getTableName());
1274 * Drops the table by name
1276 * @param string $name Table name
1277 * @return bool query result
1279 public function dropTableName($name)
1281 $sql = $this->dropTableNameSQL($name);
1282 return $this->query($sql,true,"Error dropping table $name:");
1286 * Deletes a column identified by fieldDef.
1288 * @param SugarBean $bean SugarBean containing the field
1289 * @param array $fieldDefs Vardef definition of the field
1290 * @return bool query result
1292 public function deleteColumn(SugarBean $bean, $fieldDefs)
1294 $tablename = $bean->getTableName();
1295 $sql = $this->dropColumnSQL($tablename, $fieldDefs);
1296 $msg = "Error deleting column(s) on table: $tablename:";
1297 return $this->query($sql,true,$msg);
1301 * Generate a set of Insert statements based on the bean given
1305 * @param SugarBean $bean the bean from which table we will generate insert stmts
1306 * @param string $select_query the query which will give us the set of objects we want to place into our insert statement
1307 * @param int $start the first row to query
1308 * @param int $count the number of rows to query
1309 * @param string $table the table to query from
1310 * @param bool $is_related_query
1311 * @return string SQL insert statement
1313 public function generateInsertSQL(SugarBean $bean, $select_query, $start, $count = -1, $table, $is_related_query = false)
1315 $this->log->info('call to DBManager::generateInsertSQL() is deprecated');
1316 global $sugar_config;
1319 $count_query = $bean->create_list_count_query($select_query);
1320 if(!empty($count_query))
1322 // We have a count query. Run it and get the results.
1323 $result = $this->query($count_query, true, "Error running count query for $this->object_name List: ");
1324 $assoc = $this->fetchByAssoc($result);
1325 if(!empty($assoc['c']))
1327 $rows_found = $assoc['c'];
1331 $count = $sugar_config['list_max_entries_per_page'];
1333 $next_offset = $start + $count;
1335 $result = $this->limitQuery($select_query, $start, $count);
1337 $sql = "INSERT INTO ".$table;
1338 $custom_sql = "INSERT INTO ".$table."_cstm";
1340 // get field definitions
1341 $fields = $bean->getFieldDefinitions();
1342 $custom_fields = array();
1344 if($bean->hasCustomFields()){
1345 foreach ($fields as $fieldDef){
1346 if($fieldDef['source'] == 'custom_fields'){
1347 $custom_fields[$fieldDef['name']] = $fieldDef['name'];
1350 if(!empty($custom_fields)){
1351 $custom_fields['id_c'] = 'id_c';
1352 $id_field = array('name' => 'id_c', 'custom_type' => 'id',);
1353 $fields[] = $id_field;
1357 // get column names and values
1358 $row_array = array();
1360 $cstm_row_array = array();
1361 $cstm_columns = array();
1362 $built_columns = false;
1363 while(($row = $this->fetchByAssoc($result)) != null)
1366 $cstm_values = array();
1367 if(!$is_related_query){
1368 foreach ($fields as $fieldDef)
1370 if(isset($fieldDef['source']) && $fieldDef['source'] != 'db' && $fieldDef['source'] != 'custom_fields') continue;
1371 $val = $row[$fieldDef['name']];
1373 //handle auto increment values here only need to do this on insert not create
1374 if ($fieldDef['name'] == 'deleted'){
1375 $values['deleted'] = $val;
1376 if(!$built_columns){
1377 $columns[] = 'deleted';
1382 $type = $fieldDef['type'];
1383 if(!empty($fieldDef['custom_type'])){
1384 $type = $fieldDef['custom_type'];
1386 // need to do some thing about types of values
1387 if($this->dbType == 'mysql' && $val == '' && ($type == 'datetime' || $type == 'date' || $type == 'int' || $type == 'currency' || $type == 'decimal')){
1388 if(!empty($custom_fields[$fieldDef['name']]))
1389 $cstm_values[$fieldDef['name']] = 'null';
1391 $values[$fieldDef['name']] = 'null';
1393 if(isset($type) && $type=='int') {
1394 if(!empty($custom_fields[$fieldDef['name']]))
1395 $cstm_values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
1397 $values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
1399 if(!empty($custom_fields[$fieldDef['name']]))
1400 $cstm_values[$fieldDef['name']] = "'".$GLOBALS['db']->quote(from_html($val))."'";
1402 $values[$fieldDef['name']] = "'".$GLOBALS['db']->quote(from_html($val))."'";
1405 if(!$built_columns){
1406 if(!empty($custom_fields[$fieldDef['name']]))
1407 $cstm_columns[] = $fieldDef['name'];
1409 $columns[] = $fieldDef['name'];
1415 foreach ($row as $key=>$val)
1417 if($key != 'orc_row'){
1418 $values[$key] = "'$val'";
1419 if(!$built_columns){
1425 $built_columns = true;
1426 if(!empty($values)){
1427 $row_array[] = $values;
1429 if(!empty($cstm_values) && !empty($cstm_values['id_c']) && (strlen($cstm_values['id_c']) > 7)){
1430 $cstm_row_array[] = $cstm_values;
1434 //if (sizeof ($values) == 0) return ""; // no columns set
1436 // get the entire sql
1437 $sql .= "(".implode(",", $columns).") ";
1439 for($i = 0; $i < count($row_array); $i++){
1440 $sql .= " (".implode(",", $row_array[$i]).")";
1441 if($i < (count($row_array) - 1)){
1446 // get the entire sql
1447 $custom_sql .= "(".implode(",", $cstm_columns).") ";
1448 $custom_sql .= "VALUES";
1450 for($i = 0; $i < count($cstm_row_array); $i++){
1451 $custom_sql .= " (".implode(",", $cstm_row_array[$i]).")";
1452 if($i < (count($cstm_row_array) - 1)){
1453 $custom_sql .= ", ";
1456 return array('data' => $sql, 'cstm_sql' => $custom_sql, /*'result_count' => $row_count, */ 'total_count' => $rows_found, 'next_offset' => $next_offset);
1461 * Disconnects all instances
1463 public function disconnectAll()
1465 DBManagerFactory::disconnectAll();
1469 * This function sets the query threshold limit
1471 * @param int $limit value of query threshold limit
1473 public static function setQueryLimit($limit)
1475 //reset the queryCount
1476 self::$queryCount = 0;
1477 self::$queryLimit = $limit;
1481 * Returns the static queryCount value
1483 * @return int value of the queryCount static variable
1485 public static function getQueryCount()
1487 return self::$queryCount;
1492 * Resets the queryCount value to 0
1495 public static function resetQueryCount()
1497 self::$queryCount = 0;
1501 * This function increments the global $sql_queries variable
1503 public function countQuery()
1505 if (self::$queryLimit != 0 && ++self::$queryCount > self::$queryLimit
1506 &&(empty($GLOBALS['current_user']) || !is_admin($GLOBALS['current_user']))) {
1507 require_once('include/resource/ResourceManager.php');
1508 $resourceManager = ResourceManager::getInstance();
1509 $resourceManager->notifyObservers('ERR_QUERY_LIMIT');
1514 * Pre-process string for quoting
1516 * @param string $string
1519 protected function quoteInternal($string)
1521 return from_html($string);
1525 * Return string properly quoted with ''
1526 * @param string $string
1529 public function quoted($string)
1531 return "'".$this->quote($string)."'";
1535 * Quote value according to type
1536 * Numerics aren't quoted
1537 * Dates are converted and quoted
1538 * Rest is just quoted
1539 * @param string $type
1540 * @param string $value
1541 * @return string Quoted value
1543 public function quoteType($type, $value)
1545 if($type == 'date') {
1546 return $this->convert($this->quoted($value), "date");
1548 if($type == 'time') {
1549 return $this->convert($this->quoted($value), "time");
1551 if(isset($this->type_class[$type]) && $this->type_class[$type] == "date") {
1552 return $this->convert($this->quoted($value), "datetime");
1554 if($this->isNumericType($type)) {
1555 return 0+$value; // ensure it's numeric
1558 return $this->quoted($value);
1562 * Quote the strings of the passed in array
1564 * The array must only contain strings
1566 * @param array $array
1567 * @return array Quoted strings
1569 public function arrayQuote(array &$array)
1571 foreach($array as &$val) {
1572 $val = $this->quote($val);
1578 * Frees out previous results
1580 * @param resource|bool $result optional, pass if you want to free a single result instead of all results
1582 protected function freeResult($result = false)
1585 $this->freeDbResult($result);
1587 if($this->lastResult) {
1588 $this->freeDbResult($this->lastResult);
1589 $this->lastResult = null;
1595 * Check if query has LIMIT clause
1596 * Relevant for now only for Mysql
1597 * @param string $sql
1600 protected function hasLimit($sql)
1606 * Runs a query and returns a single row containing single value
1608 * @param string $sql SQL Statement to execute
1609 * @param bool $dieOnError True if we want to call die if the query returns errors
1610 * @param string $msg Message to log if error occurs
1611 * @return array single value from the query
1613 public function getOne($sql, $dieOnError = false, $msg = '')
1615 $this->log->info("Get One: |$sql|");
1616 if(!$this->hasLimit($sql)) {
1617 $queryresult = $this->limitQuery($sql, 0, 1, $dieOnError, $msg);
1619 // support old code that passes LIMIT to sql
1620 // works only for mysql, so do not rely on this
1621 $queryresult = $this->query($sql, $dieOnError, $msg);
1623 $this->checkError($msg.' Get One Failed:' . $sql, $dieOnError);
1624 if (!$queryresult) return false;
1625 $row = $this->fetchByAssoc($queryresult);
1627 return array_shift($row);
1633 * Runs a query and returns a single row
1635 * @param string $sql SQL Statement to execute
1636 * @param bool $dieOnError True if we want to call die if the query returns errors
1637 * @param string $msg Message to log if error occurs
1638 * @param bool $suppress Message to log if error occurs
1639 * @return array single row from the query
1641 public function fetchOne($sql, $dieOnError = false, $msg = '', $suppress = false)
1643 $this->log->info("Fetch One: |$sql|");
1644 $this->checkConnection();
1645 $queryresult = $this->query($sql, $dieOnError, $msg);
1646 $this->checkError($msg.' Fetch One Failed:' . $sql, $dieOnError);
1648 if (!$queryresult) return false;
1650 $row = $this->fetchByAssoc($queryresult);
1651 if ( !$row ) return false;
1653 $this->freeResult($queryresult);
1658 * Returns the number of rows affected by the last query
1660 * See also affected_rows capability, will return 0 unless the DB supports it
1661 * @param resource $result query result resource
1664 public function getAffectedRowCount($result)
1670 * Returns the number of rows returned by the result
1672 * This function can't be reliably implemented on most DB, do not use it.
1675 * @param resource $result
1678 public function getRowCount($result)
1684 * Get table description
1685 * @param string $tablename
1686 * @param bool $reload true means load from DB, false allows using cache
1687 * @return array Vardef-format table description
1690 public function getTableDescription($tablename, $reload = false)
1692 if($reload || empty(self::$table_descriptions[$tablename])) {
1693 self::$table_descriptions[$tablename] = $this->get_columns($tablename);
1695 return self::$table_descriptions[$tablename];
1699 * Returns the field description for a given field in table
1701 * @param string $name
1702 * @param string $tablename
1705 protected function describeField($name, $tablename)
1707 $table = $this->getTableDescription($tablename);
1708 if(!empty($table) && isset($table[$name]))
1709 return $table[$name];
1711 $table = $this->getTableDescription($tablename, true);
1713 if(isset($table[$name]))
1714 return $table[$name];
1720 * Returns the index description for a given index in table
1722 * @param string $name
1723 * @param string $tablename
1726 protected function describeIndex($name, $tablename)
1728 if(isset(self::$index_descriptions[$tablename]) && isset(self::$index_descriptions[$tablename]) && isset(self::$index_descriptions[$tablename][$name])){
1729 return self::$index_descriptions[$tablename][$name];
1732 self::$index_descriptions[$tablename] = $this->get_indices($tablename);
1734 if(isset(self::$index_descriptions[$tablename][$name])){
1735 return self::$index_descriptions[$tablename][$name];
1742 * Truncates a string to a given length
1744 * @param string $string
1745 * @param int $len length to trim to
1749 public function truncate($string, $len)
1751 if ( is_numeric($len) && $len > 0)
1753 $string = mb_substr($string,0,(int) $len, "UTF-8");
1759 * Returns the database string needed for concatinating multiple database strings together
1761 * @param string $table table name of the database fields to concat
1762 * @param array $fields fields in the table to concat together
1763 * @param string $space Separator between strings, default is single space
1766 public function concat($table, array $fields, $space = ' ')
1768 if(empty($fields)) return '';
1770 $space = $this->quoted($space);
1771 foreach ( $fields as $field ) {
1772 if(!empty($elems)) $elems[] = $space;
1773 $elems[] = $this->convert("$table.$field", 'IFNULL', array("''"));
1775 $first = array_shift($elems);
1776 return "LTRIM(RTRIM(".$this->convert($first, 'CONCAT', $elems)."))";
1780 * Given a sql stmt attempt to parse it into the sql and the tokens. Then return the index of this prepared statement
1781 * Tokens can come in the following forms:
1782 * ? - a scalar which will be quoted
1783 * ! - a literal which will not be quoted
1784 * & - binary data to read from a file
1786 * @param string $sql The sql to parse
1787 * @return int index of the prepared statement to be used with execute
1789 public function prepareQuery($sql)
1791 //parse out the tokens
1792 $tokens = preg_split('/((?<!\\\)[&?!])/', $sql, -1, PREG_SPLIT_DELIM_CAPTURE);
1794 //maintain a count of the actual tokens for quick reference in execute
1798 foreach ($tokens as $key => $val) {
1808 //escape any special characters
1809 $tokens[$key] = preg_replace('/\\\([&?!])/', "\\1", $val);
1810 $sqlStr .= $tokens[$key];
1815 $this->preparedTokens[] = array('tokens' => $tokens, 'tokenCount' => $count, 'sqlString' => $sqlStr);
1816 end($this->preparedTokens);
1817 return key($this->preparedTokens);
1821 * Takes a prepared stmt index and the data to replace and creates the query and runs it.
1823 * @param int $stmt The index of the prepared statement from preparedTokens
1824 * @param array $data The array of data to replace the tokens with.
1825 * @return resource result set or false on error
1827 public function executePreparedQuery($stmt, $data = array())
1829 if(!empty($this->preparedTokens[$stmt])){
1830 if(!is_array($data)){
1831 $data = array($data);
1834 $pTokens = $this->preparedTokens[$stmt];
1836 //ensure that the number of data elements matches the number of replacement tokens
1837 //we found in prepare().
1838 if(count($data) != $pTokens['tokenCount']){
1839 //error the data count did not match the token count
1845 $tokens = $pTokens['tokens'];
1846 foreach ($tokens as $val) {
1849 $query .= $this->quote($data[$dataIndex++]);
1852 $filename = $data[$dataIndex++];
1853 $query .= file_get_contents($filename);
1856 $query .= $data[$dataIndex++];
1863 return $this->query($query);
1870 * Run both prepare and execute without the client having to run both individually.
1872 * @param string $sql The sql to parse
1873 * @param array $data The array of data to replace the tokens with.
1874 * @return resource result set or false on error
1876 public function pQuery($sql, $data = array())
1878 $stmt = $this->prepareQuery($sql);
1879 return $this->executePreparedQuery($stmt, $data);
1882 /********************** SQL FUNCTIONS ****************************/
1884 * Generates sql for create table statement for a bean.
1886 * NOTE: does not handle out-of-table constraints, use createConstraintSQL for that
1887 * @param SugarBean $bean SugarBean instance
1888 * @return string SQL Create Table statement
1890 public function createTableSQL(SugarBean $bean)
1892 $tablename = $bean->getTableName();
1893 $fieldDefs = $bean->getFieldDefinitions();
1894 $indices = $bean->getIndices();
1895 return $this->createTableSQLParams($tablename, $fieldDefs, $indices);
1899 * Generates SQL for insert statement.
1901 * @param SugarBean $bean SugarBean instance
1902 * @return string SQL Create Table statement
1904 public function insertSQL(SugarBean $bean)
1906 // get column names and values
1907 $sql = $this->insertParams($bean->getTableName(), $bean->getFieldDefinitions(), get_object_vars($bean),
1908 isset($bean->field_name_map)?$bean->field_name_map:null, false);
1913 * Generates SQL for update statement.
1915 * @param SugarBean $bean SugarBean instance
1916 * @param array $where Optional, where conditions in an array
1917 * @return string SQL Create Table statement
1919 public function updateSQL(SugarBean $bean, array $where = array())
1921 $primaryField = $bean->getPrimaryFieldDefinition();
1923 $fields = $bean->getFieldDefinitions();
1924 // get column names and values
1925 foreach ($fields as $field => $fieldDef) {
1926 if (isset($fieldDef['source']) && $fieldDef['source'] != 'db') continue;
1927 // Do not write out the id field on the update statement.
1928 // We are not allowed to change ids.
1929 if ($fieldDef['name'] == $primaryField['name']) continue;
1931 // If the field is an auto_increment field, then we shouldn't be setting it. This was added
1932 // specially for Bugs and Cases which have a number associated with them.
1933 if (!empty($bean->field_name_map[$field]['auto_increment'])) continue;
1935 //custom fields handle their save separately
1936 if(isset($bean->field_name_map) && !empty($bean->field_name_map[$field]['custom_type'])) continue;
1938 // no need to clear deleted since we only update not deleted records anyway
1939 if($fieldDef['name'] == 'deleted' && empty($bean->deleted)) continue;
1941 if(isset($bean->$field)) {
1942 $val = from_html($bean->$field);
1947 if(!empty($fieldDef['type']) && $fieldDef['type'] == 'bool'){
1948 $val = $bean->getFieldValue($field);
1951 if(strlen($val) == 0) {
1952 if(isset($fieldDef['default']) && strlen($fieldDef['default']) > 0) {
1953 $val = $fieldDef['default'];
1959 if(!empty($val) && !empty($fieldDef['len']) && strlen($val) > $fieldDef['len']) {
1960 $val = $this->truncate($val, $fieldDef['len']);
1963 if(!is_null($val) || !empty($fieldDef['required'])) {
1964 $columns[] = "{$fieldDef['name']}=".$this->massageValue($val, $fieldDef);
1965 } elseif($this->isNullable($fieldDef)) {
1966 $columns[] = "{$fieldDef['name']}=NULL";
1968 $columns[] = "{$fieldDef['name']}=".$this->emptyValue($fieldDef['type']);
1972 if ( sizeof($columns) == 0 )
1973 return ""; // no columns set
1975 // build where clause
1976 $where = $this->getWhereClause($bean, $this->updateWhereArray($bean, $where));
1977 if(isset($fields['deleted'])) {
1978 $where .= " AND deleted=0";
1981 return "UPDATE ".$bean->getTableName()."
1982 SET ".implode(",", $columns)."
1987 * This method returns a where array so that it has id entry if
1988 * where is not an array or is empty
1990 * @param SugarBean $bean SugarBean instance
1991 * @param array $where Optional, where conditions in an array
1994 protected function updateWhereArray(SugarBean $bean, array $where = array())
1996 if (count($where) == 0) {
1997 $fieldDef = $bean->getPrimaryFieldDefinition();
1998 $primaryColumn = $fieldDef['name'];
2000 $val = $bean->getFieldValue($fieldDef['name']);
2002 $where[$primaryColumn] = $val;
2010 * Returns a where clause without the 'where' key word
2012 * The clause returned does not have an 'and' at the beginning and the columns
2013 * are joined by 'and'.
2015 * @param string $table table name
2016 * @param array $whereArray Optional, where conditions in an array
2019 protected function getColumnWhereClause($table, array $whereArray = array())
2022 foreach ($whereArray as $name => $val) {
2024 if (is_array($val)) {
2027 foreach ($val as $tval){
2028 $temp[] = $this->quoted($tval);
2030 $val = implode(",", $temp);
2033 $val = $this->quoted($val);
2036 $where[] = " $table.$name $op $val";
2040 return implode(" AND ", $where);
2046 * This method returns a complete where clause built from the
2047 * where values specified.
2049 * @param SugarBean $bean SugarBean that describes the table
2050 * @param array $whereArray Optional, where conditions in an array
2053 protected function getWhereClause(SugarBean $bean, array $whereArray=array())
2055 return " WHERE " . $this->getColumnWhereClause($bean->getTableName(), $whereArray);
2059 * Outputs a correct string for the sql statement according to value
2062 * @param array $fieldDef field definition
2065 public function massageValue($val, $fieldDef)
2067 $type = $this->getFieldType($fieldDef);
2069 if(isset($this->type_class[$type])) {
2070 // handle some known types
2071 switch($this->type_class[$type]) {
2074 if (!empty($fieldDef['required']) && $val == ''){
2075 if (isset($fieldDef['default'])){
2076 return $fieldDef['default'];
2080 return intval($val);
2083 if (!empty($fieldDef['required']) && $val == false){
2084 if (isset($fieldDef['default'])){
2085 return $fieldDef['default'];
2091 if (!empty($fieldDef['required']) && $val == ''){
2092 if (isset($fieldDef['default'])){
2093 return $fieldDef['default'];
2097 return floatval($val);
2100 // empty date can't be '', so convert it to either NULL or empty date value
2102 if (!empty($fieldDef['required'])) {
2103 if (isset($fieldDef['default'])) {
2104 return $fieldDef['default'];
2106 return $this->emptyValue($type);
2113 if(!empty($val) && !empty($fieldDef['len']) && strlen($val) > $fieldDef['len']) {
2114 $val = $this->truncate($val, $fieldDef['len']);
2118 if ( is_null($val) ) {
2119 if(!empty($fieldDef['required'])) {
2120 if (isset($fieldDef['default'])){
2121 return $fieldDef['default'];
2123 return $this->emptyValue($type);
2128 if($type == "datetimecombo") {
2131 return $this->convert($this->quoted($val), $type);
2135 * Massages the field defintions to fill in anything else the DB backend may add
2137 * @param array $fieldDef
2138 * @param string $tablename
2141 public function massageFieldDef(&$fieldDef, $tablename)
2143 if ( !isset($fieldDef['dbType']) ) {
2144 if ( isset($fieldDef['dbtype']) )
2145 $fieldDef['dbType'] = $fieldDef['dbtype'];
2147 $fieldDef['dbType'] = $fieldDef['type'];
2149 $type = $this->getColumnType($fieldDef['dbType'],$fieldDef['name'],$tablename);
2151 // len can be a number or a string like 'max', for example, nvarchar(max)
2152 preg_match_all('/(\w+)(?:\(([0-9]+,?[0-9]*|\w+)\)|)/i', $type, $matches);
2153 if ( isset($matches[1][0]) )
2154 $fieldDef['type'] = $matches[1][0];
2155 if ( isset($matches[2][0]) && empty($fieldDef['len']) )
2156 $fieldDef['len'] = $matches[2][0];
2157 if ( !empty($fieldDef['precision']) && is_numeric($fieldDef['precision']) && !strstr($fieldDef['len'],',') )
2158 $fieldDef['len'] .= ",{$fieldDef['precision']}";
2159 if (!empty($fieldDef['required']) || ($fieldDef['name'] == 'id' && !isset($fieldDef['required'])) ) {
2160 $fieldDef['required'] = 'true';
2165 * Take an SQL statement and produce a list of fields used in that select
2166 * @param string $selectStatement
2169 public function getSelectFieldsFromQuery($selectStatement)
2171 $selectStatement = trim($selectStatement);
2172 if (strtoupper(substr($selectStatement, 0, 6)) == "SELECT")
2173 $selectStatement = trim(substr($selectStatement, 6));
2175 //Due to sql functions existing in many selects, we can't use php explode
2179 $strLen = strlen($selectStatement);
2180 for($i = 0; $i < $strLen; $i++)
2182 $char = $selectStatement[$i];
2184 if ($char == "," && $level == 0)
2186 $field = $this->getFieldNameFromSelect(trim($selectField));
2187 $fields[$field] = $selectField;
2190 else if ($char == "("){
2192 $selectField .= $char;
2194 else if($char == ")"){
2196 $selectField .= $char;
2200 $selectField .= $char;
2204 $fields[$this->getFieldNameFromSelect($selectField)] = $selectField;
2209 * returns the field name used in a select
2210 * @param string $string SELECT query
2213 protected function getFieldNameFromSelect($string)
2215 if(strncasecmp($string, "DISTINCT ", 9) == 0) {
2216 $string = substr($string, 9);
2218 if (stripos($string, " as ") !== false)
2219 //"as" used for an alias
2220 return trim(substr($string, strripos($string, " as ") + 4));
2221 else if (strrpos($string, " ") != 0)
2222 //Space used as a delimiter for an alias
2223 return trim(substr($string, strrpos($string, " ")));
2224 else if (strpos($string, ".") !== false)
2225 //No alias, but a table.field format was used
2226 return substr($string, strpos($string, ".") + 1);
2228 //Give up and assume the whole thing is the field name
2233 * Generates SQL for delete statement identified by id.
2235 * @param SugarBean $bean SugarBean instance
2236 * @param array $where where conditions in an array
2237 * @return string SQL Update Statement
2239 public function deleteSQL(SugarBean $bean, array $where)
2241 $where = $this->getWhereClause($bean, $this->updateWhereArray($bean, $where));
2242 return "UPDATE ".$bean->getTableName()." SET deleted=1 $where";
2246 * Generates SQL for select statement for any bean identified by id.
2248 * @param SugarBean $bean SugarBean instance
2249 * @param array $where where conditions in an array
2250 * @return string SQL Select Statement
2252 public function retrieveSQL(SugarBean $bean, array $where)
2254 $where = $this->getWhereClause($bean, $this->updateWhereArray($bean, $where));
2255 return "SELECT * FROM ".$bean->getTableName()." $where AND deleted=0";
2259 * This method implements a generic sql for a collection of beans.
2261 * Currently, this function does not support outer joins.
2263 * @param array $beans Array of values returned by get_class method as the keys and a bean as
2264 * the value for that key. These beans will be joined in the sql by the key
2265 * attribute of field defs.
2266 * @param array $cols Optional, columns to be returned with the keys as names of bean
2267 * as identified by get_class of bean. Values of this array is the array of fieldDefs
2268 * to be returned for a bean. If an empty array is passed, all columns are selected.
2269 * @param array $whereClause Optional, values with the keys as names of bean as identified
2270 * by get_class of bean. Each value at the first level is an array of values for that
2271 * bean identified by name of fields. If we want to pass multiple values for a name,
2272 * pass it as an array. If where is not passed, all the rows will be returned.
2274 * @return string SQL Select Statement
2276 public function retrieveViewSQL(array $beans, array $cols = array(), array $whereClause = array())
2278 $relations = array(); // stores relations between tables as they are discovered
2279 $where = $select = array();
2280 foreach ($beans as $beanID => $bean) {
2281 $tableName = $bean->getTableName();
2282 $beanTables[$beanID] = $tableName;
2285 $tables[$table] = $tableName;
2286 $aliases[$tableName][] = $table;
2288 // build part of select for this table
2289 if (is_array($cols[$beanID]))
2290 foreach ($cols[$beanID] as $def) $select[] = $table.".".$def['name'];
2292 // build part of where clause
2293 if (is_array($whereClause[$beanID])){
2294 $where[] = $this->getColumnWhereClause($table, $whereClause[$beanID]);
2296 // initialize so that it can be used properly in form clause generation
2297 $table_used_in_from[$table] = false;
2299 $indices = $bean->getIndices();
2300 foreach ($indices as $index){
2301 if ($index['type'] == 'foreign') {
2302 $relationship[$table][] = array('foreignTable'=> $index['foreignTable']
2303 ,'foreignColumn'=>$index['foreignField']
2304 ,'localColumn'=> $index['fields']
2308 $where[] = " $table.deleted = 0";
2311 // join these clauses
2312 $select = !empty($select) ? implode(",", $select) : "*";
2313 $where = implode(" AND ", $where);
2315 // generate the from clause. Use relations array to generate outer joins
2316 // all the rest of the tables will be used as a simple from
2317 // relations table define relations between table1 and table2 through column on table 1
2318 // table2 is assumed to joining through primary key called id
2320 $from = ''; $table_used_in_from = array();
2321 foreach ($relations as $table1 => $rightsidearray){
2322 if ($table_used_in_from[$table1]) continue; // table has been joined
2324 $from .= $separator." ".$table1;
2325 $table_used_in_from[$table1] = true;
2326 foreach ($rightsidearray as $tablearray){
2327 $table2 = $tablearray['foreignTable']; // get foreign table
2328 $tableAlias = $aliases[$table2]; // get a list of aliases for this table
2329 foreach ($tableAlias as $table2) {
2330 //choose first alias that does not match
2331 // we are doing this because of self joins.
2332 // in case of self joins, the same table will have many aliases.
2333 if ($table2 != $table1) break;
2336 $col = $tablearray['foreingColumn'];
2337 $name = $tablearray['localColumn'];
2338 $from .= " LEFT JOIN $table on ($table1.$name = $table2.$col)";
2339 $table_used_in_from[$table2] = true;
2344 return "SELECT $select FROM $from WHERE $where";
2348 * Generates SQL for create index statement for a bean.
2350 * @param SugarBean $bean SugarBean instance
2351 * @param array $fields fields used in the index
2352 * @param string $name index name
2353 * @param bool $unique Optional, set to true if this is an unique index
2354 * @return string SQL Select Statement
2356 public function createIndexSQL(SugarBean $bean, array $fields, $name, $unique = true)
2358 $unique = ($unique) ? "unique" : "";
2359 $tablename = $bean->getTableName();
2362 foreach ($fields as $fieldDef)
2363 $columns[] = $fieldDef['name'];
2365 if (empty($columns))
2368 $columns = implode(",", $columns);
2370 return "CREATE $unique INDEX $name ON $tablename ($columns)";
2374 * Returns the type of the variable in the field
2376 * @param array $fieldDef Vardef-format field def
2379 public function getFieldType($fieldDef)
2381 // get the type for db type. if that is not set,
2382 // get it from type. This is done so that
2383 // we do not have change a lot of existing code
2384 // and add dbtype where type is being used for some special
2385 // purposes like referring to foreign table etc.
2386 if(!empty($fieldDef['dbType']))
2387 return $fieldDef['dbType'];
2388 if(!empty($fieldDef['dbtype']))
2389 return $fieldDef['dbtype'];
2390 if (!empty($fieldDef['type']))
2391 return $fieldDef['type'];
2392 if (!empty($fieldDef['Type']))
2393 return $fieldDef['Type'];
2394 if (!empty($fieldDef['data_type']))
2395 return $fieldDef['data_type'];
2401 * retrieves the different components from the passed column type as it is used in the type mapping and vardefs
2402 * type format: <baseType>[(<len>[,<scale>])]
2403 * @param string $type Column type
2404 * @return array|bool array containing the different components of the passed in type or false in case the type contains illegal characters
2406 public function getTypeParts($type)
2408 if(preg_match("#(?P<type>\w+)\s*(?P<arg>\((?P<len>\w+)\s*(,\s*(?P<scale>\d+))*\))*#", $type, $matches))
2410 $return = array(); // Not returning matches array as such as we don't want to expose the regex make up on the interface
2411 $return['baseType'] = $matches['type'];
2412 if( isset($matches['arg'])) {
2413 $return['arg'] = $matches['arg'];
2415 if( isset($matches['len'])) {
2416 $return['len'] = $matches['len'];
2418 if( isset($matches['scale'])) {
2419 $return['scale'] = $matches['scale'];
2428 * Returns the defintion for a single column
2430 * @param array $fieldDef Vardef-format field def
2431 * @param bool $ignoreRequired Optional, true if we should ignore this being a required field
2432 * @param string $table Optional, table name
2433 * @param bool $return_as_array Optional, true if we should return the result as an array instead of sql
2434 * @return string or array if $return_as_array is true
2436 protected function oneColumnSQLRep($fieldDef, $ignoreRequired = false, $table = '', $return_as_array = false)
2438 $name = $fieldDef['name'];
2439 $type = $this->getFieldType($fieldDef);
2440 $colType = $this->getColumnType($type);
2442 if($parts = $this->getTypeParts($colType))
2444 $colBaseType = $parts['baseType'];
2445 $defLen = isset($parts['len']) ? $parts['len'] : '255'; // Use the mappings length (precision) as default if it exists
2448 if(!empty($fieldDef['len'])) {
2449 if (in_array($colBaseType, array( 'nvarchar', 'nchar', 'varchar', 'varchar2', 'char',
2450 'clob', 'blob', 'text'))) {
2451 $colType = "$colBaseType(${fieldDef['len']})";
2452 } elseif(($colBaseType == 'decimal' || $colBaseType == 'float')){
2453 if(!empty($fieldDef['precision']) && is_numeric($fieldDef['precision']))
2454 if(strpos($fieldDef['len'],',') === false){
2455 $colType = $colBaseType . "(".$fieldDef['len'].",".$fieldDef['precision'].")";
2457 $colType = $colBaseType . "(".$fieldDef['len'].")";
2460 $colType = $colBaseType . "(".$fieldDef['len'].")";
2463 if (in_array($colBaseType, array( 'nvarchar', 'nchar', 'varchar', 'varchar2', 'char'))) {
2464 $colType = "$colBaseType($defLen)";
2470 // Bug #52610 We should have ability don't add DEFAULT part to query for boolean fields
2471 if (!empty($fieldDef['no_default']))
2475 elseif (isset($fieldDef['default']) && strlen($fieldDef['default']) > 0)
2477 $default = " DEFAULT ".$this->quoted($fieldDef['default']);
2479 elseif (!isset($default) && $type == 'bool')
2481 $default = " DEFAULT 0 ";
2484 $auto_increment = '';
2485 if(!empty($fieldDef['auto_increment']) && $fieldDef['auto_increment'])
2486 $auto_increment = $this->setAutoIncrement($table , $fieldDef['name']);
2488 $required = 'NULL'; // MySQL defaults to NULL, SQL Server defaults to NOT NULL -- must specify
2489 //Starting in 6.0, only ID and auto_increment fields will be NOT NULL in the DB.
2490 if ((empty($fieldDef['isnull']) || strtolower($fieldDef['isnull']) == 'false') &&
2491 (!empty($auto_increment) || $name == 'id' || ($fieldDef['type'] == 'id' && !empty($fieldDef['required'])))) {
2492 $required = "NOT NULL";
2494 // If the field is marked both required & isnull=>false - alwqys make it not null
2495 // Use this to ensure primary key fields never defined as null
2496 if(isset($fieldDef['isnull']) && (strtolower($fieldDef['isnull']) == 'false' || $fieldDef['isnull'] === false)
2497 && !empty($fieldDef['required'])) {
2498 $required = "NOT NULL";
2500 if ($ignoreRequired)
2503 if ( $return_as_array ) {
2506 'colType' => $colType,
2507 'colBaseType' => $colBaseType, // Adding base type for easier processing in derived classes
2508 'default' => $default,
2509 'required' => $required,
2510 'auto_increment' => $auto_increment,
2511 'full' => "$name $colType $default $required $auto_increment",
2514 return "$name $colType $default $required $auto_increment";
2519 * Returns SQL defintions for all columns in a table
2521 * @param array $fieldDefs Vardef-format field def
2522 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
2523 * @param string $tablename Optional, table name
2524 * @return string SQL column definitions
2526 protected function columnSQLRep($fieldDefs, $ignoreRequired = false, $tablename)
2530 if ($this->isFieldArray($fieldDefs)) {
2531 foreach ($fieldDefs as $fieldDef) {
2532 if(!isset($fieldDef['source']) || $fieldDef['source'] == 'db') {
2533 $columns[] = $this->oneColumnSQLRep($fieldDef,false, $tablename);
2536 $columns = implode(",", $columns);
2539 $columns = $this->oneColumnSQLRep($fieldDefs,$ignoreRequired, $tablename);
2546 * Returns the next value for an auto increment
2548 * @param string $table Table name
2549 * @param string $field_name Field name
2552 public function getAutoIncrement($table, $field_name)
2558 * Returns the sql for the next value in a sequence
2560 * @param string $table Table name
2561 * @param string $field_name Field name
2564 public function getAutoIncrementSQL($table, $field_name)
2570 * Either creates an auto increment through queries or returns sql for auto increment
2571 * that can be appended to the end of column defination (mysql)
2573 * @param string $table Table name
2574 * @param string $field_name Field name
2577 protected function setAutoIncrement($table, $field_name)
2579 $this->deleteAutoIncrement($table, $field_name);
2584 * Sets the next auto-increment value of a column to a specific value.
2586 * @param string $table Table name
2587 * @param string $field_name Field name
2588 * @param int $start_value Starting autoincrement value
2592 public function setAutoIncrementStart($table, $field_name, $start_value)
2598 * Deletes an auto increment
2600 * @param string $table tablename
2601 * @param string $field_name
2603 public function deleteAutoIncrement($table, $field_name)
2609 * This method generates sql for adding a column to table identified by field def.
2611 * @param string $tablename
2612 * @param array $fieldDefs
2613 * @return string SQL statement
2615 public function addColumnSQL($tablename, $fieldDefs)
2617 return $this->changeColumnSQL($tablename, $fieldDefs, 'add');
2621 * This method genrates sql for altering old column identified by oldFieldDef to new fieldDef.
2623 * @param string $tablename
2624 * @param array $newFieldDefs
2625 * @param bool $ignorerequired Optional, true if we should ignor this being a required field
2626 * @return string|array SQL statement(s)
2628 public function alterColumnSQL($tablename, $newFieldDefs, $ignorerequired = false)
2630 return $this->changeColumnSQL($tablename, $newFieldDefs, 'modify', $ignorerequired);
2634 * Generates SQL for dropping a table.
2636 * @param SugarBean $bean Sugarbean instance
2637 * @return string SQL statement
2639 public function dropTableSQL(SugarBean $bean)
2641 return $this->dropTableNameSQL($bean->getTableName());
2645 * Generates SQL for dropping a table.
2647 * @param string $name table name
2648 * @return string SQL statement
2650 public function dropTableNameSQL($name)
2652 return "DROP TABLE ".$name;
2656 * Generates SQL for truncating a table.
2657 * @param string $name table name
2660 public function truncateTableSQL($name)
2662 return "TRUNCATE $name";
2666 * This method generates sql that deletes a column identified by fieldDef.
2668 * @param SugarBean $bean Sugarbean instance
2669 * @param array $fieldDefs
2670 * @return string SQL statement
2672 public function deleteColumnSQL(SugarBean $bean, $fieldDefs)
2674 return $this->dropColumnSQL($bean->getTableName(), $fieldDefs);
2678 * This method generates sql that drops a column identified by fieldDef.
2679 * Designed to work like the other addColumnSQL() and alterColumnSQL() functions
2681 * @param string $tablename
2682 * @param array $fieldDefs
2683 * @return string SQL statement
2685 public function dropColumnSQL($tablename, $fieldDefs)
2687 return $this->changeColumnSQL($tablename, $fieldDefs, 'drop');
2691 * Return a version of $proposed that can be used as a column name in any of our supported databases
2692 * Practically this means no longer than 25 characters as the smallest identifier length for our supported DBs is 30 chars for Oracle plus we add on at least four characters in some places (for indicies for example)
2693 * @param string|array $name Proposed name for the column
2694 * @param bool|string $ensureUnique Ensure the name is unique
2695 * @param string $type Name type (table, column)
2696 * @param bool $force Force new name
2697 * @return string|array Valid column name trimmed to right length and with invalid characters removed
2699 public function getValidDBName($name, $ensureUnique = false, $type = 'column', $force = false)
2701 if(is_array($name)) {
2703 foreach($name as $field) {
2704 $result[] = $this->getValidDBName($field, $ensureUnique, $type);
2708 if(strchr($name, ".")) {
2709 // this is a compound name with dots, handle separately
2710 $parts = explode(".", $name);
2711 if(count($parts) > 2) {
2712 // some weird name, cut to table.name
2713 array_splice($parts, 0, count($parts)-2);
2715 $parts = $this->getValidDBName($parts, $ensureUnique, $type, $force);
2716 return join(".", $parts);
2718 // first strip any invalid characters - all but word chars (which is alphanumeric and _)
2719 $name = preg_replace( '/[^\w]+/i', '', $name ) ;
2720 $len = strlen( $name ) ;
2721 $maxLen = empty($this->maxNameLengths[$type]) ? $this->maxNameLengths[$type]['column'] : $this->maxNameLengths[$type];
2722 if ($len <= $maxLen && !$force) {
2723 return strtolower($name);
2725 if ($ensureUnique) {
2726 $md5str = md5($name);
2727 $tail = substr ( $name, -11) ;
2728 $temp = substr($md5str , strlen($md5str)-4 );
2729 $result = substr( $name, 0, 10) . $temp . $tail ;
2731 $result = substr( $name, 0, 11) . substr( $name, 11 - $maxLen);
2734 return strtolower( $result ) ;
2739 * Returns the valid type for a column given the type in fieldDef
2741 * @param string $type field type
2742 * @return string valid type for the given field
2744 public function getColumnType($type)
2746 return isset($this->type_map[$type])?$this->type_map[$type]:$type;
2750 * Checks to see if passed array is truely an array of defitions
2752 * Such an array may have type as a key but it will point to an array
2753 * for a true array of definitions an to a col type for a definition only
2755 * @param mixed $defArray
2758 public function isFieldArray($defArray)
2760 if ( !is_array($defArray) )
2763 if ( isset($defArray['type']) ){
2764 // type key exists. May be an array of defs or a simple definition
2765 return is_array($defArray['type']); // type is not an array => definition else array
2768 // type does not exist. Must be array of definitions
2773 * returns true if the type can be mapped to a valid column type
2775 * @param string $type
2778 protected function validColumnType($type)
2780 $type = $this->getColumnType($type);
2781 return !empty($type);
2785 * Generate query for audit table
2786 * @param SugarBean $bean SugarBean that was changed
2787 * @param array $changes List of changes, contains 'before' and 'after'
2788 * @return string Audit table INSERT query
2790 protected function auditSQL(SugarBean $bean, $changes)
2792 global $current_user;
2793 $sql = "INSERT INTO ".$bean->get_audit_table_name();
2794 //get field defs for the audit table.
2795 require('metadata/audit_templateMetaData.php');
2796 $fieldDefs = $dictionary['audit']['fields'];
2799 $values['id'] = $this->massageValue(create_guid(), $fieldDefs['id']);
2800 $values['parent_id']= $this->massageValue($bean->id, $fieldDefs['parent_id']);
2801 $values['field_name']= $this->massageValue($changes['field_name'], $fieldDefs['field_name']);
2802 $values['data_type'] = $this->massageValue($changes['data_type'], $fieldDefs['data_type']);
2803 if ($changes['data_type']=='text') {
2804 $values['before_value_text'] = $this->massageValue($changes['before'], $fieldDefs['before_value_text']);
2805 $values['after_value_text'] = $this->massageValue($changes['after'], $fieldDefs['after_value_text']);
2807 $values['before_value_string'] = $this->massageValue($changes['before'], $fieldDefs['before_value_string']);
2808 $values['after_value_string'] = $this->massageValue($changes['after'], $fieldDefs['after_value_string']);
2810 $values['date_created'] = $this->massageValue(TimeDate::getInstance()->nowDb(), $fieldDefs['date_created'] );
2811 $values['created_by'] = $this->massageValue($current_user->id, $fieldDefs['created_by']);
2813 $sql .= "(".implode(",", array_keys($values)).") ";
2814 $sql .= "VALUES(".implode(",", $values).")";
2819 * Saves changes to module's audit table
2821 * @param SugarBean $bean Sugarbean instance that was changed
2822 * @param array $changes List of changes, contains 'before' and 'after'
2823 * @return bool query result
2826 public function save_audit_records(SugarBean $bean, $changes)
2828 return $this->query($this->auditSQL($bean, $changes));
2832 * Finds fields whose value has changed.
2833 * The before and after values are stored in the bean.
2834 * Uses $bean->fetched_row && $bean->fetched_rel_row to compare
2836 * @param SugarBean $bean Sugarbean instance that was changed
2837 * @param array|null $field_filter Array of filter names to be inspected (NULL means all fields)
2840 public function getDataChanges(SugarBean &$bean, array $field_filter = null)
2842 $changed_values=array();
2844 $fetched_row = array();
2845 if (is_array($bean->fetched_row))
2847 $fetched_row = array_merge($bean->fetched_row, $bean->fetched_rel_row);
2852 $field_defs = $bean->field_defs;
2854 if (is_array($field_filter)) {
2855 $field_defs = array_intersect_key($field_defs, array_flip($field_filter));
2858 // remove fields which do not present in fetched row
2859 $field_defs = array_intersect_key($field_defs, $fetched_row);
2861 // remove fields which do not exist as bean property
2862 $field_defs = array_intersect_key($field_defs, (array) $bean);
2864 foreach ($field_defs as $field => $properties) {
2865 $before_value = $fetched_row[$field];
2866 $after_value=$bean->$field;
2867 if (isset($properties['type'])) {
2868 $field_type=$properties['type'];
2870 if (isset($properties['dbType'])) {
2871 $field_type=$properties['dbType'];
2873 else if(isset($properties['data_type'])) {
2874 $field_type=$properties['data_type'];
2877 $field_type=$properties['dbtype'];
2881 //Because of bug #25078(sqlserver haven't 'date' type, trim extra "00:00:00" when insert into *_cstm table).
2882 // so when we read the audit datetime field from sqlserver, we have to replace the extra "00:00:00" again.
2883 if(!empty($field_type) && $field_type == 'date'){
2884 $before_value = $this->fromConvert($before_value , $field_type);
2886 //if the type and values match, do nothing.
2887 if (!($this->_emptyValue($before_value,$field_type) && $this->_emptyValue($after_value,$field_type))) {
2888 if (trim($before_value) !== trim($after_value)) {
2889 // Bug #42475: Don't directly compare numeric values, instead do the subtract and see if the comparison comes out to be "close enough", it is necessary for floating point numbers.
2890 // Manual merge of fix 95727f2eed44852f1b6bce9a9eccbe065fe6249f from DBHelper
2891 // This fix also fixes Bug #44624 in a more generic way and therefore eliminates the need for fix 0a55125b281c4bee87eb347709af462715f33d2d in DBHelper
2892 if (!($this->isNumericType($field_type) &&
2894 2*((trim($before_value)+0)-(trim($after_value)+0))/((trim($before_value)+0)+(trim($after_value)+0)) // Using relative difference so that it also works for other numerical types besides currencies
2895 )<0.0000000001)) { // Smaller than 10E-10
2896 if (!($this->isBooleanType($field_type) && ($this->_getBooleanValue($before_value)== $this->_getBooleanValue($after_value)))) {
2897 $changed_values[$field]=array('field_name'=>$field,
2898 'data_type'=>$field_type,
2899 'before'=>$before_value,
2900 'after'=>$after_value);
2907 return $changed_values;
2911 * Uses the audit enabled fields array to find fields whose value has changed.
2912 * The before and after values are stored in the bean.
2913 * Uses $bean->fetched_row && $bean->fetched_rel_row to compare
2915 * @param SugarBean $bean Sugarbean instance that was changed
2918 public function getAuditDataChanges(SugarBean $bean)
2920 $audit_fields = $bean->getAuditEnabledFieldDefinitions();
2921 return $this->getDataChanges($bean, array_keys($audit_fields));
2928 public function full_text_indexing_setup()
2930 // Most DBs have nothing to setup, so provide default empty function
2934 * Quotes a string for storing in the database
2936 * Return value will be not surrounded by quotes
2938 * @param string $string
2941 public function escape_quote($string)
2943 return $this->quote($string);
2947 * Quotes a string for storing in the database
2949 * Return value will be not surrounded by quotes
2951 * @param string $string
2954 public function quoteFormEmail($string)
2956 return $this->quote($string);
2960 * Renames an index using fields definition
2962 * @param array $old_definition
2963 * @param array $new_definition
2964 * @param string $table_name
2965 * @return string SQL statement
2967 public function renameIndexDefs($old_definition, $new_definition, $table_name)
2969 return array($this->add_drop_constraint($table_name,$old_definition,true),
2970 $this->add_drop_constraint($table_name,$new_definition), false);
2974 * Check if type is boolean
2975 * @param string $type
2978 public function isBooleanType($type)
2980 return 'bool' == $type;
2984 * Get truth value for boolean type
2985 * Allows 'off' to mean false, along with all 'empty' values
2989 protected function _getBooleanValue($val)
2991 //need to put the === sign here otherwise true == 'non empty string'
2992 if (empty($val) or $val==='off')
2999 * Check if type is a number
3000 * @param string $type
3003 public function isNumericType($type)
3005 if(isset($this->type_class[$type]) && ($this->type_class[$type] == 'int' || $this->type_class[$type] == 'float')) {
3012 * Check if the value is empty value for this type
3013 * @param mixed $val Value
3014 * @param string $type Type (one of vardef types)
3015 * @return bool true if the value if empty
3017 protected function _emptyValue($val, $type)
3022 if($this->emptyValue($type) == $val) {
3037 if ($val == '0000-00-00')
3049 * Does this type represent text (i.e., non-varchar) value?
3050 * @param string $type
3053 public function isTextType($type)
3059 * Check if this DB supports certain capability
3060 * See $this->capabilities for the list
3061 * @param string $cap
3064 public function supports($cap)
3066 return !empty($this->capabilities[$cap]);
3070 * Create ORDER BY clause for ENUM type field
3071 * @param string $order_by Field name
3072 * @param array $values Possible enum value
3073 * @param string $order_dir Order direction, ASC or DESC
3076 public function orderByEnum($order_by, $values, $order_dir)
3079 $order_by_arr = array();
3080 foreach ($values as $key => $value) {
3082 $order_by_arr[] = "WHEN ($order_by='' OR $order_by IS NULL) THEN $i";
3084 $order_by_arr[] = "WHEN $order_by=".$this->quoted($key)." THEN $i";
3088 return "CASE ".implode("\n", $order_by_arr)." ELSE $i END $order_dir\n";
3092 * Return representation of an empty value depending on type
3093 * The value is fully quoted, converted, etc.
3094 * @param string $type
3095 * @return mixed Empty value
3097 public function emptyValue($type)
3099 if(isset($this->type_class[$type]) && ($this->type_class[$type] == 'bool' || $this->type_class[$type] == 'int' || $this->type_class[$type] == 'float')) {
3107 * List of available collation settings
3111 public function getDefaultCollation()
3117 * List of available collation settings
3121 public function getCollationList()
3127 * Returns the number of columns in a table
3129 * @param string $table_name
3132 public function number_of_columns($table_name)
3134 $table = $this->getTableDescription($table_name);
3135 return count($table);
3139 * Return limit query based on given query
3140 * @param string $sql
3143 * @param bool $dieOnError
3144 * @param string $msg
3145 * @return resource|bool query result
3146 * @see DBManager::limitQuery()
3148 public function limitQuerySql($sql, $start, $count, $dieOnError=false, $msg='')
3150 return $this->limitQuery($sql,$start,$count,$dieOnError,$msg,false);
3154 * Return current time in format fit for insertion into DB (with quotes)
3157 public function now()
3159 return $this->convert($this->quoted(TimeDate::getInstance()->nowDb()), "datetime");
3163 * Check if connecting user has certain privilege
3164 * @param string $privilege
3165 * @return bool Privilege allowed?
3167 public function checkPrivilege($privilege)
3169 switch($privilege) {
3170 case "CREATE TABLE":
3171 $this->query("CREATE TABLE temp (id varchar(36))");
3174 $sql = $this->dropTableNameSQL("temp");
3178 $this->query("INSERT INTO temp (id) VALUES ('abcdef0123456789abcdef0123456789abcd')");
3181 $this->query("UPDATE temp SET id = '100000000000000000000000000000000000' WHERE id = 'abcdef0123456789abcdef0123456789abcd'");
3184 return $this->getOne('SELECT id FROM temp WHERE id=\'100000000000000000000000000000000000\'', false);
3186 $this->query("DELETE FROM temp WHERE id = '100000000000000000000000000000000000'");
3189 $test = array("test" => array("name" => "test", "type" => "varchar", "len" => 50));
3190 $sql = $this->changeColumnSQL("temp", $test, "add");
3193 case "CHANGE COLUMN":
3194 $test = array("test" => array("name" => "test", "type" => "varchar", "len" => 100));
3195 $sql = $this->changeColumnSQL("temp", $test, "modify");
3199 $test = array("test" => array("name" => "test", "type" => "varchar", "len" => 100));
3200 $sql = $this->changeColumnSQL("temp", $test, "drop");
3206 if($this->checkError("Checking privileges")) {
3213 * Check if the query is a select query
3214 * @param string $query
3215 * @return bool Is query SELECT?
3217 protected function isSelect($query)
3219 $query = trim($query);
3220 $select_check = strpos(strtolower($query), strtolower("SELECT"));
3221 //Checks to see if there is union select which is valid
3222 $select_check2 = strpos(strtolower($query), strtolower("(SELECT"));
3223 if($select_check==0 || $select_check2==0){
3224 //Returning false means query is ok!
3231 * Parse fulltext search query with mysql syntax:
3232 * terms quoted by ""
3233 * + means the term must be included
3234 * - means the term must be excluded
3235 * * or % at the end means wildcard
3236 * @param string $query
3237 * @return array of 3 elements - query terms, mandatory terms and excluded terms
3239 public function parseFulltextQuery($query)
3241 /* split on space or comma, double quotes with \ for escape */
3242 if(strpbrk($query, " ,")) {
3243 // ("([^"]*?)"|[^" ,]+)((, )+)?
3244 // '/([^" ,]+|".*?[^\\\\]")(,|\s)\s*/'
3245 if(!preg_match_all('/("([^"]*?)"|[^"\s,]+)((,\s)+)?/', $query, $m)) {
3250 $qterms = array($query);
3252 $terms = $must_terms = $not_terms = array();
3253 foreach($qterms as $item) {
3254 if($item[0] == '"') {
3255 $item = trim($item, '"');
3257 if($item[0] == '+') {
3258 if (strlen($item) > 1) {
3259 $must_terms[] = substr($item, 1);
3263 if($item[0] == '-') {
3264 if (strlen($item) > 1) {
3265 $not_terms[] = substr($item, 1);
3271 return array($terms, $must_terms, $not_terms);
3274 // Methods to check respective queries
3275 protected $standardQueries = array(
3276 'ALTER TABLE' => 'verifyAlterTable',
3277 'DROP TABLE' => 'verifyDropTable',
3278 'CREATE TABLE' => 'verifyCreateTable',
3279 'INSERT INTO' => 'verifyInsertInto',
3280 'UPDATE' => 'verifyUpdate',
3281 'DELETE FROM' => 'verifyDeleteFrom',
3286 * Extract table name from a query
3287 * @param string $query SQL query
3290 protected function extractTableName($query)
3292 $query = preg_replace('/[^A-Za-z0-9_\s]/', "", $query);
3293 $query = trim(str_replace(array_keys($this->standardQueries), '', $query));
3295 $firstSpc = strpos($query, " ");
3296 $end = ($firstSpc > 0) ? $firstSpc : strlen($query);
3297 $table = substr($query, 0, $end);
3303 * Verify SQl statement using per-DB verification function
3304 * provided the function exists
3305 * @param string $query Query to verify
3306 * @param array $skipTables List of blacklisted tables that aren't checked
3309 public function verifySQLStatement($query, $skipTables)
3311 $query = trim($query);
3312 foreach($this->standardQueries as $qstart => $check) {
3313 if(strncasecmp($qstart, $query, strlen($qstart)) == 0) {
3314 if(is_callable(array($this, $check))) {
3315 $table = $this->extractTableName($query);
3316 if(!in_array($table, $skipTables)) {
3317 return call_user_func(array($this, $check), $table, $query);
3319 $this->log->debug("Skipping table $table as blacklisted");
3322 $this->log->debug("No verification for $qstart on {$this->dbType}");
3331 * Tests an CREATE TABLE query
3332 * @param string $table The table name to get DDL
3333 * @param string $query The query to test.
3334 * @return string Non-empty if error found
3336 protected function verifyCreateTable($table, $query)
3338 $this->log->debug('verifying CREATE statement...');
3340 // rewrite DDL with _temp name
3341 $this->log->debug('testing query: ['.$query.']');
3342 $tempname = $table."__uw_temp";
3343 $tempTableQuery = str_replace("CREATE TABLE {$table}", "CREATE TABLE $tempname", $query);
3345 if(strpos($tempTableQuery, '__uw_temp') === false) {
3346 return 'Could not use a temp table to test query!';
3349 $this->query($tempTableQuery, false, "Preflight Failed for: {$query}");
3351 $error = $this->lastError(); // empty on no-errors
3352 if(!empty($error)) {
3356 // check if table exists
3357 $this->log->debug('testing for table: '.$table);
3358 if(!$this->tableExists($tempname)) {
3359 return "Failed to create temp table!";
3362 $this->dropTableName($tempname);
3367 * Execute multiple queries one after another
3368 * @param array $sqls Queries
3369 * @param bool $dieOnError Die on error, passed to query()
3370 * @param string $msg Error message, passed to query()
3371 * @param bool $suppress Supress errors, passed to query()
3372 * @return resource|bool result set or success/failure bool
3374 public function queryArray(array $sqls, $dieOnError = false, $msg = '', $suppress = false)
3377 foreach($sqls as $sql) {
3378 if(!($last = $this->query($sql, $dieOnError, $msg, $suppress))) {
3386 * Fetches the next row in the query result into an associative array
3388 * @param resource $result
3389 * @param bool $encode Need to HTML-encode the result?
3390 * @return array returns false if there are no more rows available to fetch
3392 public function fetchByAssoc($result, $encode = true)
3394 if (empty($result)) return false;
3396 if(is_int($encode) && func_num_args() == 3) {
3397 // old API: $result, $rowNum, $encode
3398 $GLOBALS['log']->deprecated("Using row number in fetchByAssoc is not portable and no longer supported. Please fix your code.");
3399 $encode = func_get_arg(2);
3401 $row = $this->fetchRow($result);
3402 if (!empty($row) && $encode && $this->encode) {
3403 return array_map('to_html', $row);
3410 * Get DB driver name used for install/upgrade scripts
3413 public function getScriptName()
3415 // Usually the same name as dbType
3416 return $this->dbType;
3420 * Set database options
3421 * Options are usually db-dependant and derive from $config['dbconfigoption']
3422 * @param array $options
3425 public function setOptions($options)
3427 $this->options = $options;
3435 public function getOptions()
3437 return $this->options;
3441 * Get DB option by name
3442 * @param string $option Option name
3443 * @return mixed Option value or null if doesn't exist
3445 public function getOption($option)
3447 if(isset($this->options[$option])) {
3448 return $this->options[$option];
3454 * Commits pending changes to the database when the driver is setup to support transactions.
3455 * Note that the default implementation is applicable for transaction-less or auto commit scenarios.
3457 * @return bool true if commit succeeded, false if it failed
3459 public function commit()
3461 $this->log->info("DBManager.commit() stub");
3466 * Rollsback pending changes to the database when the driver is setup to support transactions.
3467 * Note that the default implementation is applicable for transaction-less or auto commit scenarios.
3468 * Since rollbacks cannot be done, this implementation always returns false.
3470 * @return bool true if rollback succeeded, false if it failed
3472 public function rollback()
3474 $this->log->info("DBManager.rollback() stub");
3479 * Check if this DB name is valid
3481 * @param string $name
3484 public function isDatabaseNameValid($name)
3486 // Generic case - no slashes, no dots
3487 return preg_match('#[/.\\\\]#', $name)==0;
3491 * Check special requirements for DB installation.
3493 * If everything is OK, return true.
3494 * If something's wrong, return array of error code and parameters
3497 public function canInstall()
3504 * Code run on new database before installing
3506 public function preInstall()
3512 * Code run on new database after installing
3514 public function postInstall()
3519 * Disable keys on the table
3521 * @param string $tableName
3523 public function disableKeys($tableName)
3528 * Re-enable keys on the table
3530 * @param string $tableName
3532 public function enableKeys($tableName)
3537 * Quote string in DB-specific manner
3538 * @param string $string
3541 abstract public function quote($string);
3544 * Use when you need to convert a database string to a different value; this function does it in a
3545 * database-backend aware way
3546 * Supported conversions:
3547 * today return current date
3548 * left Take substring from the left
3549 * date_format Format date as string, supports %Y-%m-%d, %Y-%m, %Y
3550 * time_format Format time as string
3551 * date Convert date string to datetime value
3552 * time Convert time string to datetime value
3553 * datetime Convert datetime string to datetime value
3554 * ifnull If var is null, use default value
3555 * concat Concatenate strings
3556 * quarter Quarter number of the date
3557 * length Length of string
3558 * month Month number of the date
3559 * add_date Add specified interval to a date
3560 * add_time Add time interval to a date
3561 * text2char Convert text field to varchar
3563 * @param string $string database string to convert
3564 * @param string $type type of conversion to do
3565 * @param array $additional_parameters optional, additional parameters to pass to the db function
3568 abstract public function convert($string, $type, array $additional_parameters = array());
3571 * Converts from Database data to app data
3580 * @param string $string database string to convert
3581 * @param string $type type of conversion to do
3584 abstract public function fromConvert($string, $type);
3587 * Parses and runs queries
3589 * @param string $sql SQL Statement to execute
3590 * @param bool $dieOnError True if we want to call die if the query returns errors
3591 * @param string $msg Message to log if error occurs
3592 * @param bool $suppress Flag to suppress all error output unless in debug logging mode.
3593 * @param bool $keepResult Keep query result in the object?
3594 * @return resource|bool result set or success/failure bool
3596 abstract public function query($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false);
3599 * Runs a limit query: one where we specify where to start getting records and how many to get
3601 * @param string $sql SELECT query
3602 * @param int $start Starting row
3603 * @param int $count How many rows
3604 * @param boolean $dieOnError True if we want to call die if the query returns errors
3605 * @param string $msg Message to log if error occurs
3606 * @param bool $execute Execute or return SQL?
3607 * @return resource query result
3609 abstract function limitQuery($sql, $start, $count, $dieOnError = false, $msg = '', $execute = true);
3613 * Free Database result
3614 * @param resource $dbResult
3616 abstract protected function freeDbResult($dbResult);
3619 * Rename column in the DB
3620 * @param string $tablename
3621 * @param string $column
3622 * @param string $newname
3624 abstract function renameColumnSQL($tablename, $column, $newname);
3627 * Returns definitions of all indies for passed table.
3629 * return will is a multi-dimensional array that
3630 * categorizes the index definition by types, unique, primary and index.
3634 * 'index1'=> array (
3635 * 'name' => 'index1',
3636 * 'type' => 'primary',
3637 * 'fields' => array('field1','field2')
3642 * This format is similar to how indicies are defined in vardef file.
3644 * @param string $tablename
3647 abstract public function get_indices($tablename);
3650 * Returns definitions of all indies for passed table.
3652 * return will is a multi-dimensional array that
3653 * categorizes the index definition by types, unique, primary and index.
3657 * 'field1'=> array (
3658 * 'name' => 'field1',
3659 * 'type' => 'varchar',
3665 * This format is similar to how indicies are defined in vardef file.
3667 * @param string $tablename
3670 abstract public function get_columns($tablename);
3673 * Generates alter constraint statement given a table name and vardef definition.
3675 * Supports both adding and droping a constraint.
3677 * @param string $table tablename
3678 * @param array $definition field definition
3679 * @param bool $drop true if we are dropping the constraint, false if we are adding it
3680 * @return string SQL statement
3682 abstract public function add_drop_constraint($table, $definition, $drop = false);
3685 * Returns the description of fields based on the result
3687 * @param resource $result
3688 * @param boolean $make_lower_case
3689 * @return array field array
3691 abstract public function getFieldsArray($result, $make_lower_case = false);
3694 * Returns an array of tables for this database
3696 * @return array|false an array of with table names, false if no tables found
3698 abstract public function getTablesArray();
3701 * Return's the version of the database
3705 abstract public function version();
3708 * Checks if a table with the name $tableName exists
3709 * and returns true if it does or false otherwise
3711 * @param string $tableName
3714 abstract public function tableExists($tableName);
3717 * Fetches the next row in the query result into an associative array
3719 * @param resource $result
3720 * @return array returns false if there are no more rows available to fetch
3722 abstract public function fetchRow($result);
3725 * Connects to the database backend
3727 * Takes in the database settings and opens a database connection based on those
3728 * will open either a persistent or non-persistent connection.
3729 * If a persistent connection is desired but not available it will defualt to non-persistent
3731 * configOptions must include
3732 * db_host_name - server ip
3733 * db_user_name - database user name
3734 * db_password - database password
3736 * @param array $configOptions
3737 * @param boolean $dieOnError
3739 abstract public function connect(array $configOptions = null, $dieOnError = false);
3742 * Generates sql for create table statement for a bean.
3744 * @param string $tablename
3745 * @param array $fieldDefs
3746 * @param array $indices
3747 * @return string SQL Create Table statement
3749 abstract public function createTableSQLParams($tablename, $fieldDefs, $indices);
3752 * Generates the SQL for changing columns
3754 * @param string $tablename
3755 * @param array $fieldDefs
3756 * @param string $action
3757 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
3758 * @return string|array
3760 abstract protected function changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired = false);
3763 * Disconnects from the database
3765 * Also handles any cleanup needed
3767 abstract public function disconnect();
3770 * Get last database error
3771 * This function should return last error as reported by DB driver
3772 * and should return false if no error condition happened
3773 * @return string|false Error message or false if no error happened
3775 abstract public function lastDbError();
3778 * Check if this query is valid
3779 * Validates only SELECT queries
3780 * @param string $query
3783 abstract public function validateQuery($query);
3786 * Check if this driver can be used
3789 abstract public function valid();
3792 * Check if certain database exists
3793 * @param string $dbname
3795 abstract public function dbExists($dbname);
3798 * Get tables like expression
3799 * @param string $like Expression describing tables
3802 abstract public function tablesLike($like);
3806 * @param string $dbname
3808 abstract public function createDatabase($dbname);
3812 * @param string $dbname
3814 abstract public function dropDatabase($dbname);
3817 * Get database configuration information (DB-dependent)
3818 * @return array|null
3820 abstract public function getDbInfo();
3823 * Check if certain DB user exists
3824 * @param string $username
3826 abstract public function userExists($username);
3830 * @param string $database_name
3831 * @param string $host_name
3832 * @param string $user
3833 * @param string $password
3835 abstract public function createDbUser($database_name, $host_name, $user, $password);
3838 * Check if the database supports fulltext indexing
3839 * Note that database driver can be capable of supporting FT (see supports('fulltext))
3840 * but particular instance can still have it disabled
3843 abstract public function full_text_indexing_installed();
3846 * Generate fulltext query from set of terms
3847 * @param string $field Field to search against
3848 * @param array $terms Search terms that may be or not be in the result
3849 * @param array $must_terms Search terms that have to be in the result
3850 * @param array $exclude_terms Search terms that have to be not in the result
3852 abstract public function getFulltextQuery($field, $terms, $must_terms = array(), $exclude_terms = array());
3855 * Get install configuration for this DB
3858 abstract public function installConfig();
3861 * Returns a DB specific FROM clause which can be used to select against functions.
3862 * Note that depending on the database that this may also be an empty string.
3866 abstract public function getFromDummyTable();
3869 * Returns a DB specific piece of SQL which will generate GUID (UUID)
3870 * This string can be used in dynamic SQL to do multiple inserts with a single query.
3871 * I.e. generate a unique Sugar id in a sub select of an insert statement.
3875 abstract public function getGuidSQL();