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 * MySQL manager implementation for mysql extension
94 class MysqlManager extends DBManager
97 * @see DBManager::$dbType
99 public $dbType = 'mysql';
100 public $variant = 'mysql';
101 public $dbName = 'MySQL';
102 public $label = 'LBL_MYSQL';
104 protected $maxNameLengths = array(
111 protected $type_map = array(
113 'double' => 'double',
115 'uint' => 'int unsigned',
116 'ulong' => 'bigint unsigned',
118 'short' => 'smallint',
119 'varchar' => 'varchar',
121 'longtext' => 'longtext',
124 'relate' => 'varchar',
125 'multienum'=> 'text',
127 'longhtml' => 'longtext',
128 'datetime' => 'datetime',
129 'datetimecombo' => 'datetime',
132 'tinyint' => 'tinyint',
135 'longblob' => 'longblob',
136 'currency' => 'decimal(26,6)',
137 'decimal' => 'decimal',
138 'decimal2' => 'decimal',
141 'encrypt' => 'varchar',
143 'decimal_tpl' => 'decimal(%d, %d)',
147 protected $capabilities = array(
148 "affected_rows" => true,
149 "select_rows" => true,
150 "inline_keys" => true,
151 "create_user" => true,
155 "disable_keys" => true,
159 * Parses and runs queries
161 * @param string $sql SQL Statement to execute
162 * @param bool $dieOnError True if we want to call die if the query returns errors
163 * @param string $msg Message to log if error occurs
164 * @param bool $suppress Flag to suppress all error output unless in debug logging mode.
165 * @param bool $keepResult True if we want to push this result into the $lastResult array.
166 * @return resource result set
168 public function query($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false)
171 return $this->queryArray($sql, $dieOnError, $msg, $suppress);
174 parent::countQuery($sql);
175 $GLOBALS['log']->info('Query:' . $sql);
176 $this->checkConnection();
177 $this->query_time = microtime(true);
178 $this->lastsql = $sql;
179 $result = $suppress?@mysql_query($sql, $this->database):mysql_query($sql, $this->database);
181 $this->query_time = microtime(true) - $this->query_time;
182 $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
186 $this->lastResult = $result;
188 $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
193 * Returns the number of rows affected by the last query
197 public function getAffectedRowCount($result)
199 return mysql_affected_rows($this->getDatabase());
203 * Returns the number of rows returned by the result
205 * This function can't be reliably implemented on most DB, do not use it.
208 * @param resource $result
211 public function getRowCount($result)
213 return mysql_num_rows($result);
217 * Disconnects from the database
219 * Also handles any cleanup needed
221 public function disconnect()
223 $GLOBALS['log']->debug('Calling MySQL::disconnect()');
224 if(!empty($this->database)){
226 mysql_close($this->database);
227 $this->database = null;
232 * @see DBManager::freeDbResult()
234 protected function freeDbResult($dbResult)
236 if(!empty($dbResult))
237 mysql_free_result($dbResult);
243 * Check if query has LIMIT clause
244 * Relevant for now only for Mysql
248 protected function hasLimit($sql)
250 return stripos($sql, " limit ") !== false;
254 * @see DBManager::limitQuery()
256 public function limitQuery($sql, $start, $count, $dieOnError = false, $msg = '', $execute = true)
258 $start = (int)$start;
259 $count = (int)$count;
262 $GLOBALS['log']->debug('Limit Query:' . $sql. ' Start: ' .$start . ' count: ' . $count);
264 $sql = "$sql LIMIT $start,$count";
265 $this->lastsql = $sql;
267 if(!empty($GLOBALS['sugar_config']['check_query'])){
268 $this->checkQuery($sql);
274 return $this->query($sql, $dieOnError, $msg);
279 * @see DBManager::checkQuery()
281 protected function checkQuery($sql)
283 $result = $this->query('EXPLAIN ' . $sql);
285 while ($row = $this->fetchByAssoc($result)) {
286 if (empty($row['table']))
288 $badQuery[$row['table']] = '';
289 if (strtoupper($row['type']) == 'ALL')
290 $badQuery[$row['table']] .= ' Full Table Scan;';
291 if (empty($row['key']))
292 $badQuery[$row['table']] .= ' No Index Key Used;';
293 if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using filesort') > 0)
294 $badQuery[$row['table']] .= ' Using FileSort;';
295 if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using temporary') > 0)
296 $badQuery[$row['table']] .= ' Using Temporary Table;';
299 if ( empty($badQuery) )
302 foreach($badQuery as $table=>$data ){
304 $warning = ' Table:' . $table . ' Data:' . $data;
305 if(!empty($GLOBALS['sugar_config']['check_query_log'])){
306 $GLOBALS['log']->fatal($sql);
307 $GLOBALS['log']->fatal('CHECK QUERY:' .$warning);
310 $GLOBALS['log']->warn('CHECK QUERY:' .$warning);
319 * @see DBManager::get_columns()
321 public function get_columns($tablename)
323 //find all unique indexes and primary keys.
324 $result = $this->query("DESCRIBE $tablename");
327 while (($row=$this->fetchByAssoc($result)) !=null) {
328 $name = strtolower($row['Field']);
329 $columns[$name]['name']=$name;
331 preg_match_all('/(\w+)(?:\(([0-9]+,?[0-9]*)\)|)( unsigned)?/i', $row['Type'], $matches);
332 $columns[$name]['type']=strtolower($matches[1][0]);
333 if ( isset($matches[2][0]) && in_array(strtolower($matches[1][0]),array('varchar','char','varchar2','int','decimal','float')) )
334 $columns[$name]['len']=strtolower($matches[2][0]);
335 if ( stristr($row['Extra'],'auto_increment') )
336 $columns[$name]['auto_increment'] = '1';
337 if ($row['Null'] == 'NO' && !stristr($row['Key'],'PRI'))
338 $columns[$name]['required'] = 'true';
339 if (!empty($row['Default']) )
340 $columns[$name]['default'] = $row['Default'];
346 * @see DBManager::getFieldsArray()
348 public function getFieldsArray($result, $make_lower_case=false)
350 $field_array = array();
355 $fields = mysql_num_fields($result);
356 for ($i=0; $i < $fields; $i++) {
357 $meta = mysql_fetch_field($result, $i);
361 if($make_lower_case == true)
362 $meta->name = strtolower($meta->name);
364 $field_array[] = $meta->name;
371 * @see DBManager::fetchRow()
373 public function fetchRow($result)
375 if (empty($result)) return false;
377 return mysql_fetch_assoc($result);
381 * @see DBManager::getTablesArray()
383 public function getTablesArray()
385 $this->log->debug('Fetching table list');
387 if ($this->getDatabase()) {
389 $r = $this->query('SHOW TABLES');
391 while ($a = $this->fetchByAssoc($r)) {
392 $row = array_values($a);
399 return false; // no database available
403 * @see DBManager::version()
405 public function version()
407 return $this->getOne("SELECT version() version");
411 * @see DBManager::tableExists()
413 public function tableExists($tableName)
415 $this->log->info("tableExists: $tableName");
417 if ($this->getDatabase()) {
418 $result = $this->query("SHOW TABLES LIKE ".$this->quoted($tableName));
419 if(empty($result)) return false;
420 $row = $this->fetchByAssoc($result);
428 * Get tables like expression
429 * @param $like string
432 public function tablesLike($like)
434 if ($this->getDatabase()) {
436 $r = $this->query('SHOW TABLES LIKE '.$this->quoted($like));
438 while ($a = $this->fetchByAssoc($r)) {
439 $row = array_values($a);
449 * @see DBManager::quote()
451 public function quote($string)
453 if(is_array($string)) {
454 return $this->arrayQuote($string);
456 return mysql_real_escape_string($this->quoteInternal($string), $this->getDatabase());
460 * @see DBManager::connect()
462 public function connect(array $configOptions = null, $dieOnError = false)
464 global $sugar_config;
466 if(is_null($configOptions))
467 $configOptions = $sugar_config['dbconfig'];
469 if ($this->getOption('persistent')) {
470 $this->database = @mysql_pconnect(
471 $configOptions['db_host_name'],
472 $configOptions['db_user_name'],
473 $configOptions['db_password']
477 if (!$this->database) {
478 $this->database = mysql_connect(
479 $configOptions['db_host_name'],
480 $configOptions['db_user_name'],
481 $configOptions['db_password']
483 if(empty($this->database)) {
484 $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].":".mysql_error());
486 if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
487 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
489 sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
495 // Do not pass connection information because we have not connected yet
496 if($this->database && $this->getOption('persistent')){
497 $_SESSION['administrator_error'] = "<b>Severe Performance Degradation: Persistent Database Connections "
498 . "not working. Please set \$sugar_config['dbconfigoption']['persistent'] to false "
499 . "in your config.php file</b>";
502 if(!empty($configOptions['db_name']) && !@mysql_select_db($configOptions['db_name'])) {
503 $GLOBALS['log']->fatal( "Unable to select database {$configOptions['db_name']}: " . mysql_error($this->database));
505 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
511 // cn: using direct calls to prevent this from spamming the Logs
512 mysql_query("SET CHARACTER SET utf8", $this->database);
513 $names = "SET NAMES 'utf8'";
514 $collation = $this->getOption('collation');
515 if(!empty($collation)) {
516 $names .= " COLLATE '$collation'";
518 mysql_query($names, $this->database);
520 if(!$this->checkError('Could Not Connect:', $dieOnError))
521 $GLOBALS['log']->info("connected to db");
522 $this->connectOptions = $configOptions;
524 $GLOBALS['log']->info("Connect:".$this->database);
529 * @see DBManager::repairTableParams()
531 * For MySQL, we can write the ALTER TABLE statement all in one line, which speeds things
532 * up quite a bit. So here, we'll parse the returned SQL into a single ALTER TABLE command.
534 public function repairTableParams($tablename, $fielddefs, $indices, $execute = true, $engine = null)
536 $sql = parent::repairTableParams($tablename,$fielddefs,$indices,false,$engine);
541 if ( stristr($sql,'create table') )
544 $msg = "Error creating table: ".$tablename. ":";
545 $this->query($sql,true,$msg);
550 // first, parse out all the comments
552 preg_match_all('!/\*.*?\*/!is', $sql, $match);
553 $commentBlocks = $match[0];
554 $sql = preg_replace('!/\*.*?\*/!is','', $sql);
556 // now, we should only have alter table statements
557 // let's replace the 'alter table name' part with a comma
558 $sql = preg_replace("!alter table $tablename!is",', ', $sql);
560 // re-add it at the beginning
561 $sql = substr_replace($sql,'',strpos($sql,','),1);
562 $sql = str_replace(";","",$sql);
563 $sql = str_replace("\n","",$sql);
564 $sql = "ALTER TABLE $tablename $sql";
567 $this->query($sql,'Error with MySQL repair table');
569 // and re-add the comments at the beginning
570 $sql = implode("\n",$commentBlocks) . "\n". $sql . "\n";
576 * @see DBManager::convert()
578 public function convert($string, $type, array $additional_parameters = array())
580 $all_parameters = $additional_parameters;
581 if(is_array($string)) {
582 $all_parameters = array_merge($string, $all_parameters);
583 } elseif (!is_null($string)) {
584 array_unshift($all_parameters, $string);
586 $all_strings = implode(',', $all_parameters);
588 switch (strtolower($type)) {
592 return "LEFT($all_strings)";
594 if(empty($additional_parameters)) {
595 return "DATE_FORMAT($string,'%Y-%m-%d')";
597 $format = $additional_parameters[0];
598 if($format[0] != "'") {
599 $format = $this->quoted($format);
601 return "DATE_FORMAT($string,$format)";
604 if(empty($additional_parameters) && !strstr($all_strings, ",")) {
605 $all_strings .= ",''";
607 return "IFNULL($all_strings)";
609 return "CONCAT($all_strings)";
611 return "QUARTER($string)";
613 return "LENGTH($string)";
615 return "MONTH($string)";
617 return "DATE_ADD($string, INTERVAL {$additional_parameters[0]} {$additional_parameters[1]})";
619 return "DATE_ADD($string, INTERVAL + CONCAT({$additional_parameters[0]}, ':', {$additional_parameters[1]}) HOUR_MINUTE)";
620 case 'add_tz_offset' :
621 $getUserUTCOffset = $GLOBALS['timedate']->getUserUTCOffset();
622 $operation = $getUserUTCOffset < 0 ? '-' : '+';
623 return $string . ' ' . $operation . ' INTERVAL ' . abs($getUserUTCOffset) . ' MINUTE';
625 return "avg($string)";
633 * @see DBManager::fromConvert()
635 public function fromConvert($string, $type)
641 * Returns the name of the engine to use or null if we are to use the default
643 * @param object $bean SugarBean instance
646 protected function getEngine($bean)
650 if (isset($dictionary[$bean->getObjectName()]['engine'])) {
651 $engine = $dictionary[$bean->getObjectName()]['engine'];
657 * Returns true if the engine given is enabled in the backend
659 * @param string $engine
662 protected function isEngineEnabled($engine)
664 if(!is_string($engine)) return false;
666 $engine = strtoupper($engine);
668 $r = $this->query("SHOW ENGINES");
670 while ( $row = $this->fetchByAssoc($r) )
671 if ( strtoupper($row['Engine']) == $engine )
672 return ($row['Support']=='YES' || $row['Support']=='DEFAULT');
678 * @see DBManager::createTableSQL()
680 public function createTableSQL(SugarBean $bean)
682 $tablename = $bean->getTableName();
683 $fieldDefs = $bean->getFieldDefinitions();
684 $indices = $bean->getIndices();
685 $engine = $this->getEngine($bean);
686 return $this->createTableSQLParams($tablename, $fieldDefs, $indices, $engine);
690 * Generates sql for create table statement for a bean.
692 * @param string $tablename
693 * @param array $fieldDefs
694 * @param array $indices
695 * @param string $engine optional, MySQL engine to use
696 * @return string SQL Create Table statement
698 public function createTableSQLParams($tablename, $fieldDefs, $indices, $engine = null)
700 if ( empty($engine) && isset($fieldDefs['engine']))
701 $engine = $fieldDefs['engine'];
702 if ( !$this->isEngineEnabled($engine) )
705 $columns = $this->columnSQLRep($fieldDefs, false, $tablename);
709 $keys = $this->keysSQL($indices);
713 // cn: bug 9873 - module tables do not get created in utf8 with assoc collation
714 $collation = $this->getOption('collation');
715 if(empty($collation)) {
716 $collation = 'utf8_general_ci';
718 $sql = "CREATE TABLE $tablename ($columns $keys) CHARACTER SET utf8 COLLATE $collation";
721 $sql.= " ENGINE=$engine";
727 * Does this type represent text (i.e., non-varchar) value?
728 * @param string $type
730 public function isTextType($type)
732 $type = $this->getColumnType(strtolower($type));
733 return in_array($type, array('blob','text','longblob', 'longtext'));
737 * @see DBManager::oneColumnSQLRep()
739 protected function oneColumnSQLRep($fieldDef, $ignoreRequired = false, $table = '', $return_as_array = false)
741 // always return as array for post-processing
742 $ref = parent::oneColumnSQLRep($fieldDef, $ignoreRequired, $table, true);
744 if ( $ref['colType'] == 'int' && !empty($fieldDef['len']) ) {
745 $ref['colType'] .= "(".$fieldDef['len'].")";
748 // bug 22338 - don't set a default value on text or blob fields
749 if ( isset($ref['default']) &&
750 in_array($ref['colBaseType'], array('text', 'blob', 'longtext', 'longblob')))
751 $ref['default'] = '';
753 if ( $return_as_array )
756 return "{$ref['name']} {$ref['colType']} {$ref['default']} {$ref['required']} {$ref['auto_increment']}";
760 * @see DBManager::changeColumnSQL()
762 protected function changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired = false)
765 if ($this->isFieldArray($fieldDefs)){
766 foreach ($fieldDefs as $def){
767 if ($action == 'drop')
768 $columns[] = $def['name'];
770 $columns[] = $this->oneColumnSQLRep($def, $ignoreRequired);
773 if ($action == 'drop')
774 $columns[] = $fieldDefs['name'];
776 $columns[] = $this->oneColumnSQLRep($fieldDefs);
779 return "ALTER TABLE $tablename $action COLUMN ".implode(",$action column ", $columns);
783 * Generates SQL for key specification inside CREATE TABLE statement
785 * The passes array is an array of field definitions or a field definition
786 * itself. The keys generated will be either primary, foreign, unique, index
787 * or none at all depending on the setting of the "key" parameter of a field definition
789 * @param array $indices
790 * @param bool $alter_table
791 * @param string $alter_action
792 * @return string SQL Statement
794 protected function keysSQL($indices, $alter_table = false, $alter_action = '')
796 // check if the passed value is an array of fields.
797 // if not, convert it into an array
798 if (!$this->isFieldArray($indices))
799 $indices[] = $indices;
802 foreach ($indices as $index) {
803 if(!empty($index['db']) && $index['db'] != $this->dbType)
805 if (isset($index['source']) && $index['source'] != 'db')
808 $type = $index['type'];
809 $name = $index['name'];
811 if (is_array($index['fields']))
812 $fields = implode(", ", $index['fields']);
814 $fields = $index['fields'];
818 $columns[] = " UNIQUE $name ($fields)";
821 $columns[] = " PRIMARY KEY ($fields)";
826 case 'alternate_key':
828 * @todo here it is assumed that the primary key of the foreign
829 * table will always be named 'id'. It must be noted though
830 * that this can easily be fixed by referring to db dictionary
831 * to find the correct primary field name
834 $columns[] = " INDEX $name ($fields)";
836 $columns[] = " KEY $name ($fields)";
839 if ($this->full_text_indexing_installed())
840 $columns[] = " FULLTEXT ($fields)";
842 $GLOBALS['log']->debug('MYISAM engine is not available/enabled, full-text indexes will be skipped. Skipping:',$name);
846 $columns = implode(", $alter_action ", $columns);
847 if(!empty($alter_action)){
848 $columns = $alter_action . ' '. $columns;
854 * @see DBManager::setAutoIncrement()
856 protected function setAutoIncrement($table, $field_name)
858 return "auto_increment";
862 * Sets the next auto-increment value of a column to a specific value.
864 * @param string $table tablename
865 * @param string $field_name
867 public function setAutoIncrementStart($table, $field_name, $start_value)
869 $start_value = (int)$start_value;
870 return $this->query( "ALTER TABLE $table AUTO_INCREMENT = $start_value;");
874 * Returns the next value for an auto increment
876 * @param string $table tablename
877 * @param string $field_name
880 public function getAutoIncrement($table, $field_name)
882 $result = $this->query("SHOW TABLE STATUS LIKE '$table'");
883 $row = $this->fetchByAssoc($result);
884 if (!empty($row['Auto_increment']))
885 return $row['Auto_increment'];
891 * @see DBManager::get_indices()
893 public function get_indices($tablename)
895 //find all unique indexes and primary keys.
896 $result = $this->query("SHOW INDEX FROM $tablename");
899 while (($row=$this->fetchByAssoc($result)) !=null) {
901 if ($row['Key_name'] =='PRIMARY') {
902 $index_type='primary';
904 elseif ( $row['Non_unique'] == '0' ) {
905 $index_type='unique';
907 $name = strtolower($row['Key_name']);
908 $indices[$name]['name']=$name;
909 $indices[$name]['type']=$index_type;
910 $indices[$name]['fields'][]=strtolower($row['Column_name']);
916 * @see DBManager::add_drop_constraint()
918 public function add_drop_constraint($table, $definition, $drop = false)
920 $type = $definition['type'];
921 $fields = implode(',',$definition['fields']);
922 $name = $definition['name'];
928 case 'alternate_key':
931 $sql = "ALTER TABLE {$table} DROP INDEX {$name} ";
933 $sql = "ALTER TABLE {$table} ADD INDEX {$name} ({$fields})";
935 // constraints as indices
938 $sql = "ALTER TABLE {$table} DROP INDEX $name";
940 $sql = "ALTER TABLE {$table} ADD CONSTRAINT UNIQUE {$name} ({$fields})";
944 $sql = "ALTER TABLE {$table} DROP PRIMARY KEY";
946 $sql = "ALTER TABLE {$table} ADD CONSTRAINT PRIMARY KEY ({$fields})";
950 $sql = "ALTER TABLE {$table} DROP FOREIGN KEY ({$fields})";
952 $sql = "ALTER TABLE {$table} ADD CONSTRAINT FOREIGN KEY {$name} ({$fields}) REFERENCES {$definition['foreignTable']}({$definition['foreignField']})";
959 * Runs a query and returns a single row
961 * @param string $sql SQL Statement to execute
962 * @param bool $dieOnError True if we want to call die if the query returns errors
963 * @param string $msg Message to log if error occurs
964 * @param bool $suppress Message to log if error occurs
965 * @return array single row from the query
967 public function fetchOne($sql, $dieOnError = false, $msg = '', $suppress = false)
969 if(stripos($sql, ' LIMIT ') === false) {
970 // little optimization to just fetch one row
971 $sql .= " LIMIT 0,1";
973 return parent::fetchOne($sql, $dieOnError, $msg, $suppress);
977 * @see DBManager::full_text_indexing_installed()
979 public function full_text_indexing_installed($dbname = null)
981 return $this->isEngineEnabled('MyISAM');
985 * @see DBManager::massageFieldDef()
987 public function massageFieldDef(&$fieldDef, $tablename)
989 parent::massageFieldDef($fieldDef,$tablename);
991 if ( isset($fieldDef['default']) &&
992 ($fieldDef['dbType'] == 'text'
993 || $fieldDef['dbType'] == 'blob'
994 || $fieldDef['dbType'] == 'longtext'
995 || $fieldDef['dbType'] == 'longblob' ))
996 unset($fieldDef['default']);
997 if ($fieldDef['dbType'] == 'uint')
998 $fieldDef['len'] = '10';
999 if ($fieldDef['dbType'] == 'ulong')
1000 $fieldDef['len'] = '20';
1001 if ($fieldDef['dbType'] == 'bool')
1002 $fieldDef['type'] = 'tinyint';
1003 if ($fieldDef['dbType'] == 'bool' && empty($fieldDef['default']) )
1004 $fieldDef['default'] = '0';
1005 if (($fieldDef['dbType'] == 'varchar' || $fieldDef['dbType'] == 'enum') && empty($fieldDef['len']) )
1006 $fieldDef['len'] = '255';
1007 if ($fieldDef['dbType'] == 'uint')
1008 $fieldDef['len'] = '10';
1009 if ($fieldDef['dbType'] == 'int' && empty($fieldDef['len']) )
1010 $fieldDef['len'] = '11';
1012 if($fieldDef['dbType'] == 'decimal') {
1013 if(isset($fieldDef['len'])) {
1014 if(strstr($fieldDef['len'], ",") === false) {
1015 $fieldDef['len'] .= ",0";
1018 $fieldDef['len'] = '10,0';
1024 * Generates SQL for dropping a table.
1026 * @param string $name table name
1027 * @return string SQL statement
1029 public function dropTableNameSQL($name)
1031 return "DROP TABLE IF EXISTS ".$name;
1034 public function dropIndexes($tablename, $indexes, $execute = true)
1037 foreach ($indexes as $index) {
1038 $name =$index['name'];
1040 unset(self::$index_descriptions[$tablename][$name]);
1042 if ($index['type'] == 'primary') {
1043 $sql[] = 'DROP PRIMARY KEY';
1045 $sql[] = "DROP INDEX $name";
1049 $sql = "ALTER TABLE $tablename " . join(",", $sql) . ";";
1059 * List of available collation settings
1062 public function getDefaultCollation()
1064 return "utf8_general_ci";
1068 * List of available collation settings
1071 public function getCollationList()
1073 $q = "SHOW COLLATION LIKE 'utf8%'";
1074 $r = $this->query($q);
1076 while($a = $this->fetchByAssoc($r)) {
1077 $res[] = $a['Collation'];
1084 * @see DBManager::renameColumnSQL()
1086 public function renameColumnSQL($tablename, $column, $newname)
1088 $field = $this->describeField($column, $tablename);
1089 $field['name'] = $newname;
1090 return "ALTER TABLE $tablename CHANGE COLUMN $column ".$this->oneColumnSQLRep($field);
1093 public function emptyValue($type)
1095 $ctype = $this->getColumnType($type);
1096 if($ctype == "datetime") {
1097 return $this->convert($this->quoted("0000-00-00 00:00:00"), "datetime");
1099 if($ctype == "date") {
1100 return $this->convert($this->quoted("0000-00-00"), "date");
1102 if($ctype == "time") {
1103 return $this->convert($this->quoted("00:00:00"), "time");
1105 return parent::emptyValue($type);
1110 * @see DBManager::lastDbError()
1112 public function lastDbError()
1114 if($this->database) {
1115 if(mysql_errno($this->database)) {
1116 return "MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database);
1119 $err = mysql_error();
1128 * Quote MySQL search term
1129 * @param unknown_type $term
1131 protected function quoteTerm($term)
1133 if(strpos($term, ' ') !== false) {
1134 return '"'.$term.'"';
1140 * Generate fulltext query from set of terms
1141 * @param string $fields Field to search against
1142 * @param array $terms Search terms that may be or not be in the result
1143 * @param array $must_terms Search terms that have to be in the result
1144 * @param array $exclude_terms Search terms that have to be not in the result
1146 public function getFulltextQuery($field, $terms, $must_terms = array(), $exclude_terms = array())
1148 $condition = array();
1149 foreach($terms as $term) {
1150 $condition[] = $this->quoteTerm($term);
1152 foreach($must_terms as $term) {
1153 $condition[] = "+".$this->quoteTerm($term);
1155 foreach($exclude_terms as $term) {
1156 $condition[] = "-".$this->quoteTerm($term);
1158 $condition = $this->quoted(join(" ",$condition));
1159 return "MATCH($field) AGAINST($condition IN BOOLEAN MODE)";
1163 * Get list of all defined charsets
1166 protected function getCharsetInfo()
1168 $charsets = array();
1169 $res = $this->query("show variables like 'character\\_set\\_%'");
1170 while($row = $this->fetchByAssoc($res)) {
1171 $charsets[$row['Variable_name']] = $row['Value'];
1176 public function getDbInfo()
1178 $charsets = $this->getCharsetInfo();
1179 $charset_str = array();
1180 foreach($charsets as $name => $value) {
1181 $charset_str[] = "$name = $value";
1184 "MySQL Version" => @mysql_get_client_info(),
1185 "MySQL Host Info" => @mysql_get_host_info($this->database),
1186 "MySQL Server Info" => @mysql_get_server_info($this->database),
1187 "MySQL Client Encoding" => @mysql_client_encoding($this->database),
1188 "MySQL Character Set Settings" => join(", ", $charset_str),
1192 public function validateQuery($query)
1194 $res = $this->query("EXPLAIN $query");
1195 return !empty($res);
1198 protected function makeTempTableCopy($table)
1200 $this->log->debug("creating temp table for [$table]...");
1201 $result = $this->query("SHOW CREATE TABLE {$table}");
1202 if(empty($result)) {
1205 $row = $this->fetchByAssoc($result);
1206 if(empty($row) || empty($row['Create Table'])) {
1209 $create = $row['Create Table'];
1210 // rewrite DDL with _temp name
1211 $tempTableQuery = str_replace("CREATE TABLE `{$table}`", "CREATE TABLE `{$table}__uw_temp`", $create);
1212 $r2 = $this->query($tempTableQuery);
1217 // get sample data into the temp table to test for data/constraint conflicts
1218 $this->log->debug('inserting temp dataset...');
1219 $q3 = "INSERT INTO `{$table}__uw_temp` SELECT * FROM `{$table}` LIMIT 10";
1220 $this->query($q3, false, "Preflight Failed for: {$q3}");
1225 * Tests an ALTER TABLE query
1226 * @param string table The table name to get DDL
1227 * @param string query The query to test.
1228 * @return string Non-empty if error found
1230 protected function verifyAlterTable($table, $query)
1232 $this->log->debug("verifying ALTER TABLE");
1233 // Skipping ALTER TABLE [table] DROP PRIMARY KEY because primary keys are not being copied
1234 // over to the temp tables
1235 if(strpos(strtoupper($query), 'DROP PRIMARY KEY') !== false) {
1236 $this->log->debug("Skipping DROP PRIMARY KEY");
1239 if(!$this->makeTempTableCopy($table)) {
1240 return 'Could not create temp table copy';
1243 // test the query on the test table
1244 $this->log->debug('testing query: ['.$query.']');
1245 $tempTableTestQuery = str_replace("ALTER TABLE `{$table}`", "ALTER TABLE `{$table}__uw_temp`", $query);
1246 if (strpos($tempTableTestQuery, 'idx') === false) {
1247 if(strpos($tempTableTestQuery, '__uw_temp') === false) {
1248 return 'Could not use a temp table to test query!';
1251 $this->log->debug('testing query on temp table: ['.$tempTableTestQuery.']');
1252 $this->query($tempTableTestQuery, false, "Preflight Failed for: {$query}");
1254 // test insertion of an index on a table
1255 $tempTableTestQuery_idx = str_replace("ADD INDEX `idx_", "ADD INDEX `temp_idx_", $tempTableTestQuery);
1256 $this->log->debug('testing query on temp table: ['.$tempTableTestQuery_idx.']');
1257 $this->query($tempTableTestQuery_idx, false, "Preflight Failed for: {$query}");
1259 $mysqlError = $this->getL();
1260 if(!empty($mysqlError)) {
1263 $this->dropTableName("{$table}__uw_temp");
1268 protected function verifyGenericReplaceQuery($querytype, $table, $query)
1270 $this->log->debug("verifying $querytype statement");
1272 if(!$this->makeTempTableCopy($table)) {
1273 return 'Could not create temp table copy';
1275 // test the query on the test table
1276 $this->log->debug('testing query: ['.$query.']');
1277 $tempTableTestQuery = str_replace("$querytype `{$table}`", "$querytype `{$table}__uw_temp`", $query);
1278 if(strpos($tempTableTestQuery, '__uw_temp') === false) {
1279 return 'Could not use a temp table to test query!';
1282 $this->query($tempTableTestQuery, false, "Preflight Failed for: {$query}");
1283 $error = $this->lastError(); // empty on no-errors
1284 $this->dropTableName("{$table}__uw_temp"); // just in case
1289 * Tests a DROP TABLE query
1290 * @param string table The table name to get DDL
1291 * @param string query The query to test.
1292 * @return string Non-empty if error found
1294 public function verifyDropTable($table, $query)
1296 return $this->verifyGenericReplaceQuery("DROP TABLE", $table, $query);
1300 * Tests an INSERT INTO query
1301 * @param string table The table name to get DDL
1302 * @param string query The query to test.
1303 * @return string Non-empty if error found
1305 public function verifyInsertInto($table, $query)
1307 return $this->verifyGenericReplaceQuery("INSERT INTO", $table, $query);
1311 * Tests an UPDATE query
1312 * @param string table The table name to get DDL
1313 * @param string query The query to test.
1314 * @return string Non-empty if error found
1316 public function verifyUpdate($table, $query)
1318 return $this->verifyGenericReplaceQuery("UPDATE", $table, $query);
1322 * Tests an DELETE FROM query
1323 * @param string table The table name to get DDL
1324 * @param string query The query to test.
1325 * @return string Non-empty if error found
1327 public function verifyDeleteFrom($table, $query)
1329 return $this->verifyGenericReplaceQuery("DELETE FROM", $table, $query);
1333 * Check if certain database exists
1334 * @param string $dbname
1336 public function dbExists($dbname)
1338 $db = $this->getOne("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ".$this->quoted($dbname));
1344 * @param string $dbname
1346 protected function selectDb($dbname)
1348 return mysql_select_db($dbname);
1352 * Check if certain DB user exists
1353 * @param string $username
1355 public function userExists($username)
1357 $db = $this->getOne("SELECT DATABASE()");
1358 if(!$this->selectDb("mysql")) {
1361 $user = $this->getOne("select count(*) from user where user = ".$this->quoted($username));
1362 if(!$this->selectDb($db)) {
1363 $this->checkError("Cannot select database $db", true);
1365 return !empty($user);
1370 * @param string $database_name
1371 * @param string $host_name
1372 * @param string $user
1373 * @param string $password
1375 public function createDbUser($database_name, $host_name, $user, $password)
1377 $qpassword = $this->quote($password);
1378 $this->query("GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX
1379 ON `$database_name`.*
1380 TO \"$user\"@\"$host_name\"
1381 IDENTIFIED BY '{$qpassword}';", true);
1383 $this->query("SET PASSWORD FOR \"{$user}\"@\"{$host_name}\" = password('{$qpassword}');", true);
1384 if($host_name != 'localhost') {
1385 $this->createDbUser($database_name, "localhost", $user, $password);
1391 * @param string $dbname
1393 public function createDatabase($dbname)
1395 $this->query("CREATE DATABASE `$dbname` CHARACTER SET utf8 COLLATE utf8_general_ci", true);
1398 public function preInstall()
1400 $db->query("ALTER DATABASE `{$setup_db_database_name}` DEFAULT CHARACTER SET utf8", true);
1401 $db->query("ALTER DATABASE `{$setup_db_database_name}` DEFAULT COLLATE utf8_general_ci", true);
1407 * @param string $dbname
1409 public function dropDatabase($dbname)
1411 return $this->query("DROP DATABASE IF EXISTS `$dbname`", true);
1415 * Check if this driver can be used
1418 public function valid()
1420 return function_exists("mysql_connect");
1425 * @see DBManager::canInstall()
1427 public function canInstall()
1429 $db_version = $this->version();
1430 if(empty($db_version)) {
1431 return array('ERR_DB_VERSION_FAILURE');
1433 if(version_compare($db_version, '4.1.2') < 0) {
1434 return array('ERR_DB_MYSQL_VERSION', $db_version);
1439 public function installConfig()
1442 'LBL_DBCONFIG_MSG3' => array(
1443 "setup_db_database_name" => array("label" => 'LBL_DBCONF_DB_NAME', "required" => true),
1445 'LBL_DBCONFIG_MSG2' => array(
1446 "setup_db_host_name" => array("label" => 'LBL_DBCONF_HOST_NAME', "required" => true),
1448 'LBL_DBCONF_TITLE_USER_INFO' => array(),
1449 'LBL_DBCONFIG_B_MSG1' => array(
1450 "setup_db_admin_user_name" => array("label" => 'LBL_DBCONF_DB_ADMIN_USER', "required" => true),
1451 "setup_db_admin_password" => array("label" => 'LBL_DBCONF_DB_ADMIN_PASSWORD', "type" => "password"),
1457 * Disable keys on the table
1459 * @param string $tableName
1461 public function disableKeys($tableName)
1463 return $this->query('ALTER TABLE '.$tableName.' DISABLE KEYS');
1467 * Re-enable keys on the table
1469 * @param string $tableName
1471 public function enableKeys($tableName)
1473 return $this->query('ALTER TABLE '.$tableName.' ENABLE KEYS');
1477 * Returns a DB specific FROM clause which can be used to select against functions.
1478 * Note that depending on the database that this may also be an empty string.
1481 public function getFromDummyTable()
1487 * Returns a DB specific piece of SQL which will generate GUID (UUID)
1488 * This string can be used in dynamic SQL to do multiple inserts with a single query.
1489 * I.e. generate a unique Sugar id in a sub select of an insert statement.
1493 public function getGuidSQL()