2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM Community Edition is a customer relationship management program developed by
5 * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
7 * This program is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU Affero General Public License version 3 as published by the
9 * Free Software Foundation with the addition of the following permission added
10 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
11 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
12 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
14 * This program is distributed in the hope that it will be useful, but WITHOUT
15 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
19 * You should have received a copy of the GNU Affero General Public License along with
20 * this program; if not, see http://www.gnu.org/licenses or write to the Free
21 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
24 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
25 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
27 * The interactive user interfaces in modified source and object code versions
28 * of this program must display Appropriate Legal Notices, as required under
29 * Section 5 of the GNU Affero General Public License version 3.
31 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
32 * these Appropriate Legal Notices must retain the display of the "Powered by
33 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
34 * technical reasons, the Appropriate Legal Notices must display the words
35 * "Powered by SugarCRM".
36 ********************************************************************************/
38 /*********************************************************************************
40 * Description: This file handles the Data base functionality for the application specific
41 * to oracle database. It is called by the DBManager class to generate various sql statements.
43 * All the functions in this class will work with any bean which implements the meta interface.
44 * Please refer the DBManager documentation for the details.
46 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
47 * All Rights Reserved.
48 * Contributor(s): ______________________________________..
49 ********************************************************************************/
50 require_once('include/database/DBHelper.php');
52 class MysqlHelper extends DBHelper
55 * Maximum length of identifiers
57 protected $maxNameLengths = array(
65 * @see DBHelper::createTableSQL()
67 public function createTableSQL(
71 $tablename = $bean->getTableName();
72 $fieldDefs = $bean->getFieldDefinitions();
73 $indices = $bean->getIndices();
74 $engine = $this->getEngine($bean);
75 return $this->createTableSQLParams($tablename, $fieldDefs, $indices, $engine);
79 * Generates sql for create table statement for a bean.
81 * @param string $tablename
82 * @param array $fieldDefs
83 * @param array $indices
84 * @param string $engine optional, MySQL engine to use
85 * @return string SQL Create Table statement
87 public function createTableSQLParams(
94 if ( empty($engine) && isset($fieldDefs['engine']))
95 $engine = $fieldDefs['engine'];
96 if ( !$this->isEngineEnabled($engine) )
99 $sql = parent::createTableSQLParams($tablename,$fieldDefs,$indices);
101 $sql.= " ENGINE=$engine";
107 * Returns the name of the engine to use or null if we are to use the default
109 * @param object $bean SugarBean instance
112 private function getEngine($bean)
116 if (isset($dictionary[$bean->getObjectName()]['engine'])) {
117 $engine = $dictionary[$bean->getObjectName()]['engine'];
123 * Returns true if the engine given is enabled in the backend
125 * @param string $engine
128 private function isEngineEnabled(
132 $engine = strtoupper($engine);
134 $r = $this->db->query("SHOW ENGINES");
136 while ( $row = $this->db->fetchByAssoc($r) )
137 if ( strtoupper($row['Engine']) == $engine )
138 return ($row['Support']=='YES' || $row['Support']=='DEFAULT');
144 * @see DBHelper::getColumnType()
146 public function getColumnType(
154 'double' => 'double',
156 'uint' => 'int unsigned',
157 'ulong' => 'bigint unsigned',
159 'short' => 'smallint',
160 'varchar' => 'varchar',
162 'longtext' => 'longtext',
165 'relate' => 'varchar',
166 'multienum'=> 'text',
168 'datetime' => 'datetime',
169 'datetimecombo' => 'datetime',
172 'tinyint' => 'tinyint',
175 'longblob' => 'longblob',
176 'currency' => 'decimal(26,6)',
177 'decimal' => 'decimal',
178 'decimal2' => 'decimal',
181 'encrypt'=>'varchar',
185 // Bug 44291 - If requested type is in array, returns it. Otherwise return requested type, so devs could see exactly what went wrong in log.
186 if (isset($map[$type]))
193 * @see DBHelper::oneColumnSQLRep()
195 protected function oneColumnSQLRep(
197 $ignoreRequired = false,
199 $return_as_array = false
202 $ref = parent::oneColumnSQLRep($fieldDef, $ignoreRequired, $table, true);
204 if ( $ref['colType'] == 'int'
205 && !empty($fieldDef['len']) )
206 $ref['colType'] .= "(".$fieldDef['len'].")";
208 // bug 22338 - don't set a default value on text or blob fields
209 if ( isset($ref['default']) &&
210 ($ref['colType'] == 'text' || $ref['colType'] == 'blob'
211 || $ref['colType'] == 'longtext' || $ref['colType'] == 'longblob' ))
212 $ref['default'] = '';
214 if ( $return_as_array )
217 return "{$ref['name']} {$ref['colType']} {$ref['default']} {$ref['required']} {$ref['auto_increment']}";
221 * @see DBHelper::changeColumnSQL()
223 protected function changeColumnSQL(
227 $ignoreRequired = false
230 if ($this->isFieldArray($fieldDefs)){
231 foreach ($fieldDefs as $def){
232 if ($action == 'drop')
233 $columns[] = $def['name'];
235 $columns[] = $this->oneColumnSQLRep($def, $ignoreRequired);
238 if ($action == 'drop')
239 $columns[] = $fieldDefs['name'];
241 $columns[] = $this->oneColumnSQLRep($fieldDefs);
244 return "alter table $tablename $action column ".implode(",$action column ", $columns);
248 * @see DBHelper::deleteColumnSQL()
250 public function deleteColumnSQL(
255 if ($this->isFieldArray($fieldDefs))
256 foreach ($fieldDefs as $fieldDef)
257 $columns[] = $fieldDef['name'];
259 $columns[] = $fieldDefs['name'];
261 return "alter table ".$bean->getTableName()." drop column ".implode(", drop column ", $columns);
265 * @see DBHelper::keysSQL
267 public function keysSQL(
269 $alter_table = false,
273 // check if the passed value is an array of fields.
274 // if not, convert it into an array
275 if (!$this->isFieldArray($indices))
276 $indices[] = $indices;
279 foreach ($indices as $index) {
280 if(!empty($index['db']) && $index['db'] != 'mysql')
282 if (isset($index['source']) && $index['source'] != 'db')
285 $type = $index['type'];
286 $name = $index['name'];
288 if (is_array($index['fields']))
289 $fields = implode(", ", $index['fields']);
291 $fields = $index['fields'];
295 $columns[] = " UNIQUE $name ($fields)";
298 $columns[] = " PRIMARY KEY ($fields)";
303 case 'alternate_key':
305 * @todo here it is assumed that the primary key of the foreign
306 * table will always be named 'id'. It must be noted though
307 * that this can easily be fixed by referring to db dictionary
308 * to find the correct primary field name
311 $columns[] = " INDEX $name ($fields)";
313 $columns[] = " KEY $name ($fields)";
316 if ($this->full_text_indexing_enabled())
317 $columns[] = " FULLTEXT ($fields)";
319 $GLOBALS['log']->debug('MYISAM engine is not available/enabled, full-text indexes will be skipped. Skipping:',$name);
323 $columns = implode(", $alter_action ", $columns);
324 if(!empty($alter_action)){
325 $columns = $alter_action . ' '. $columns;
331 * @see DBHelper::setAutoIncrement()
333 protected function setAutoIncrement(
338 return "auto_increment";
342 * Sets the next auto-increment value of a column to a specific value.
344 * @param string $table tablename
345 * @param string $field_name
347 public function setAutoIncrementStart(
353 $this->db->query( "ALTER TABLE $table AUTO_INCREMENT = $start_value;");
359 * Returns the next value for an auto increment
361 * @param string $table tablename
362 * @param string $field_name
365 public function getAutoIncrement(
371 $result = $this->db->query("SHOW TABLE STATUS LIKE '$table'");
372 $row = $this->db->fetchByAssoc($result);
373 if (!empty($row['Auto_increment']))
374 return $row['Auto_increment'];
380 * @see DBHelper::get_indices()
382 public function get_indices(
386 //find all unique indexes and primary keys.
387 $result = $this->db->query("SHOW INDEX FROM $tablename");
390 while (($row=$this->db->fetchByAssoc($result)) !=null) {
392 if ($row['Key_name'] =='PRIMARY') {
393 $index_type='primary';
395 elseif ( $row['Non_unique'] == '0' ) {
396 $index_type='unique';
398 $name = strtolower($row['Key_name']);
399 $indices[$name]['name']=$name;
400 $indices[$name]['type']=$index_type;
401 $indices[$name]['fields'][]=strtolower($row['Column_name']);
407 * @see DBHelper::get_columns()
409 public function get_columns(
413 //find all unique indexes and primary keys.
414 $result = $this->db->query("DESCRIBE $tablename");
417 while (($row=$this->db->fetchByAssoc($result)) !=null) {
418 $name = strtolower($row['Field']);
419 $columns[$name]['name']=$name;
421 preg_match_all("/(\w+)(?:\(([0-9]+,?[0-9]*)\)|)( unsigned)?/i", $row['Type'], $matches);
422 $columns[$name]['type']=strtolower($matches[1][0]);
423 if ( isset($matches[2][0]) && in_array(strtolower($matches[1][0]),array('varchar','char','varchar2','int','decimal','float')) )
424 $columns[$name]['len']=strtolower($matches[2][0]);
425 if ( stristr($row['Extra'],'auto_increment') )
426 $columns[$name]['auto_increment'] = '1';
427 if ($row['Null'] == 'NO' && !stristr($row['Key'],'PRI'))
428 $columns[$name]['required'] = 'true';
429 if (!empty($row['Default']) )
430 $columns[$name]['default'] = $row['Default'];
436 * @see DBHelper::add_drop_constraint()
438 public function add_drop_constraint(
444 $type = $definition['type'];
445 $fields = implode(',',$definition['fields']);
446 $name = $definition['name'];
447 $foreignTable = isset($definition['foreignTable']) ? $definition['foreignTable'] : array();
453 case 'alternate_key':
455 $sql = "DROP INDEX {$name} ";
457 $sql = "CREATE INDEX {$name} ON {$table} ({$fields})";
459 // constraints as indices
462 $sql = "ALTER TABLE {$table} DROP INDEX $name";
464 $sql = "ALTER TABLE {$table} ADD CONSTRAINT UNIQUE {$name} ({$fields})";
468 $sql = "ALTER TABLE {$table} DROP PRIMARY KEY";
470 $sql = "ALTER TABLE {$table} ADD CONSTRAINT PRIMARY KEY ({$fields})";
474 $sql = "ALTER TABLE {$table} DROP FOREIGN KEY ({$fields})";
476 $sql = "ALTER TABLE {$table} ADD CONSTRAINT FOREIGN KEY {$name} ({$fields}) REFERENCES {$foreignTable}({$foreignfields})";
483 * @see DBHelper::number_of_columns()
485 public function number_of_columns(
489 $result = $this->db->query("DESCRIBE $table_name");
491 return ($this->db->getRowCount($result));
495 * @see DBHelper::full_text_indexing_enabled()
497 protected function full_text_indexing_enabled(
501 return $this->isEngineEnabled('MyISAM');
505 * @see DBHelper::massageFieldDef()
507 public function massageFieldDef(
512 DBHelper::massageFieldDef($fieldDef,$tablename);
514 if ( isset($fieldDef['default']) &&
515 ($fieldDef['dbType'] == 'text'
516 || $fieldDef['dbType'] == 'blob'
517 || $fieldDef['dbType'] == 'longtext'
518 || $fieldDef['dbType'] == 'longblob' ))
519 unset($fieldDef['default']);
520 if ($fieldDef['dbType'] == 'uint')
521 $fieldDef['len'] = '10';
522 if ($fieldDef['dbType'] == 'ulong')
523 $fieldDef['len'] = '20';
524 if ($fieldDef['dbType'] == 'bool')
525 $fieldDef['type'] = 'tinyint';
526 if ($fieldDef['dbType'] == 'bool' && empty($fieldDef['default']) )
527 $fieldDef['default'] = '0';
528 if (($fieldDef['dbType'] == 'varchar' || $fieldDef['dbType'] == 'enum') && empty($fieldDef['len']) )
529 $fieldDef['len'] = '255';
530 if ($fieldDef['dbType'] == 'uint')
531 $fieldDef['len'] = '10';
532 if ($fieldDef['dbType'] == 'int' && empty($fieldDef['len']) )
533 $fieldDef['len'] = '11';