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 Mssql 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): ___RPS___________________________________..
49 ********************************************************************************/
51 include_once('include/database/DBHelper.php');
53 class MssqlHelper extends DBHelper
56 * Maximum length of identifiers
58 protected $maxNameLengths = array(
66 * @see DBHelper::getColumnType()
68 public function getColumnType(
81 'short' => 'smallint',
82 'varchar' => 'varchar',
87 'relate' => 'varchar',
90 'datetime' => 'datetime',
91 'datetimecombo' => 'datetime',
94 'tinyint' => 'tinyint',
97 'longblob' => 'image',
98 'currency' => 'decimal(26,6)',
99 'decimal' => 'decimal',
100 'decimal2' => 'decimal',
101 'id' => 'varchar(36)',
103 'encrypt'=>'varchar',
107 // Bug 44291 - If requested type is in array, returns it. Otherwise return requested type, so devs could see exactly what went wrong in log.
108 if (isset($map[$type]))
115 * @see DBHelper::dropTableNameSQL()
117 public function dropTableNameSQL(
121 return "DROP TABLE ".$name;
125 * Returns the SQL Alter table statment
127 * MSSQL has a quirky T-SQL alter table syntax. Pay special attention to the
129 * @param string $action
131 * @param bool $ignorRequired
132 * @param string $tablename
134 private function alterSQLRep(
143 $f_def=$this->oneColumnSQLRep($def, $ignoreRequired,$tablename,false);
144 return "ADD " . $f_def;
147 return "DROP COLUMN " . $def['name'];
150 //You cannot specify a default value for a column for MSSQL
151 $f_def = $this->oneColumnSQLRep($def, $ignoreRequired,$tablename, true);
152 $f_stmt = "ALTER COLUMN ".$f_def['name'].' '.$f_def['colType'].' '.
153 $f_def['required'].' '.$f_def['auto_increment']."\n";
154 if (!empty( $f_def['default']))
155 $f_stmt .= " ALTER TABLE " . $tablename . " ADD ". $f_def['default'] . " FOR " . $def['name'];
164 * @see DBHelper::changeColumnSQL()
166 * MSSQL uses a different syntax than MySQL for table altering that is
167 * not quite as simplistic to implement...
169 protected function changeColumnSQL(
173 $ignoreRequired = false
177 $constraints = $this->get_field_default_constraint_name($tablename);
178 if ($this->isFieldArray($fieldDefs)) {
179 foreach ($fieldDefs as $def)
181 //if the column is being modified drop the default value
182 //constraint if it exists. alterSQLRep will add the constraint back
183 if (!empty($constraints[$def['name']])) {
184 $sql.=" ALTER TABLE " . $tablename . " DROP CONSTRAINT " . $constraints[$def['name']];
186 //check to see if we need to drop related indexes before the alter
187 $indices = $this->get_indices($tablename);
188 foreach ( $indices as $index ) {
189 if ( in_array($def['name'],$index['fields']) ) {
190 $sql .= ' ' . $this->add_drop_constraint($tablename,$index,true).' ';
191 $sql2 .= ' ' . $this->add_drop_constraint($tablename,$index,false).' ';
195 $columns[] = $this->alterSQLRep($action, $def, $ignoreRequired,$tablename);
199 //if the column is being modified drop the default value
200 //constraint if it exists. alterSQLRep will add the constraint back
201 if (!empty($constraints[$fieldDefs['name']])) {
202 $sql.=" ALTER TABLE " . $tablename . " DROP CONSTRAINT " . $constraints[$fieldDefs['name']];
204 //check to see if we need to drop related indexes before the alter
205 $indices = $this->get_indices($tablename);
206 foreach ( $indices as $index ) {
207 if ( in_array($fieldDefs['name'],$index['fields']) ) {
208 $sql .= ' ' . $this->add_drop_constraint($tablename,$index,true).' ';
209 $sql2 .= ' ' . $this->add_drop_constraint($tablename,$index,false).' ';
214 $columns[] = $this->alterSQLRep($action, $fieldDefs, $ignoreRequired,$tablename);
217 $columns = implode(", ", $columns);
218 $sql .= " ALTER TABLE $tablename $columns " . $sql2;
224 * @see DBHelper::deleteColumnSQL()
226 public function deleteColumnSQL(
231 if ($this->isFieldArray($fieldDefs))
232 foreach ($fieldDefs as $fieldDef)
233 $columns[] = $fieldDef['name'];
235 $columns[] = $fieldDefs['name'];
237 return "ALTER TABLE ".$bean->getTableName()." DROP COLUMN ".implode(", DROP COLUMN ", $columns);
241 * returns an alter table statement to build the list of indices
243 * @param string $tableName
244 * @param array $fieldDefs
245 * @param array $indices
246 * @return string SQL statement
248 public function indexSQL(
254 // check if the passed value is an array of fields.
255 // if not, convert it into an array
256 if (!$this->isFieldArray($indices))
257 $indices[] = $indices;
260 foreach ($indices as $index) {
261 if(!empty($index['db']) && $index['db'] != 'mssql')
263 if (isset($index['source']) && $index['source'] != 'db')
266 $type = $index['type'];
267 $name = $index['name'];
269 if (is_array($index['fields']))
270 $fields = implode(", ", $index['fields']);
272 $fields = $index['fields'];
276 // SQL server requires primary key constraints to be created with
277 // key word "PRIMARY KEY". Cannot default to index as synonym
278 $columns[] = "ALTER TABLE $tableName ADD CONSTRAINT pk_$tableName PRIMARY KEY ($fields)";
281 $columns[] = "ALTER TABLE $tableName ADD CONSTRAINT " . $index['name'] . " UNIQUE ($fields)";
284 $columns[] = "CREATE CLUSTERED INDEX $name ON $tableName ( $fields )";
287 case 'alternate_key':
289 $columns[] = "CREATE INDEX $name ON $tableName ( $fields )";
292 if ($this->full_text_indexing_enabled()) {
293 $catalog_name="sugar_fts_catalog";
294 if ( isset($index['catalog_name'])
295 && $index['catalog_name'] != 'default')
296 $catalog_name = $index['catalog_name'];
298 $language = "Language 1033";
299 if (isset($index['language']) && !empty($index['language']))
300 $language = "Language " . $index['language'];
302 $key_index = $index['key_index'];;
304 $change_tracking = "auto";
305 if (isset($index['change_tracking'])
306 && !empty($index['change_tracking']))
307 $change_tracking = $index['change_tracking'];
309 $columns[] = " CREATE FULLTEXT INDEX ON $tableName($fields $language) KEY INDEX $key_index ON $catalog_name WITH CHANGE_TRACKING $change_tracking" ;
315 $columns = implode(" ", $columns);
320 protected function setAutoIncrement(
325 return "identity(1,1)";
329 * @see DBHelper::setAutoIncrementStart()
331 public function setAutoIncrementStart(
339 $this->db->query("DBCC CHECKIDENT ('$table', RESEED, $start_value)");
344 * @see DBHelper::getAutoIncrement()
346 public function getAutoIncrement(
353 $result = $this->db->query("select IDENT_CURRENT('$table') + IDENT_INCR ( '$table' ) as 'Auto_increment'");
354 $row = $this->db->fetchByAssoc($result);
355 if (!empty($row['Auto_increment']))
356 return $row['Auto_increment'];
362 * @see DBHelper::createTableSQLParams()
364 public function createTableSQLParams(
371 if (empty($tablename) || empty($fieldDefs))
375 $columns = $this->columnSQLRep($fieldDefs, false, $tablename);
379 return "CREATE TABLE $tablename ($columns ) " .
380 $this->indexSQL($tablename, $fieldDefs, $indices);
384 * @see DBHelper::get_indices()
386 public function get_indices(
390 //find all unique indexes and primary keys.
392 SELECT LEFT(so.[name], 30) TableName,
393 LEFT(si.[name], 50) 'Key_name',
394 LEFT(sik.[keyno], 30) Sequence,
395 LEFT(sc.[name], 30) Column_name,
397 WHEN si.status & 2 = 2 AND so.xtype != 'PK' THEN 1
401 INNER JOIN sysindexkeys sik
402 ON (si.[id] = sik.[id] AND si.indid = sik.indid)
403 INNER JOIN sysobjects so
405 INNER JOIN syscolumns sc
406 ON (so.[id] = sc.[id] AND sik.colid = sc.colid)
407 INNER JOIN sysfilegroups sfg
408 ON si.groupid = sfg.groupid
409 WHERE so.[name] = '$tablename'
410 ORDER BY Key_name, Sequence, Column_name
412 $result = $this->db->query($query);
415 while (($row=$this->db->fetchByAssoc($result)) != null) {
416 $index_type = 'index';
417 if ($row['Key_name'] == 'PRIMARY')
418 $index_type = 'primary';
419 elseif ($row['isunique'] == 1 )
420 $index_type = 'unique';
421 $name = strtolower($row['Key_name']);
422 $indices[$name]['name'] = $name;
423 $indices[$name]['type'] = $index_type;
424 $indices[$name]['fields'][] = strtolower($row['Column_name']);
430 * @see DBHelper::get_columns()
432 public function get_columns(
436 //find all unique indexes and primary keys.
437 $result = $this->db->query("sp_columns $tablename");
440 while (($row=$this->db->fetchByAssoc($result)) !=null) {
441 $column_name = strtolower($row['COLUMN_NAME']);
442 $columns[$column_name]['name']=$column_name;
443 $columns[$column_name]['type']=strtolower($row['TYPE_NAME']);
444 if ( $row['TYPE_NAME'] == 'decimal' ) {
445 $columns[$column_name]['len']=strtolower($row['PRECISION']);
446 $columns[$column_name]['len'].=','.strtolower($row['SCALE']);
448 elseif ( in_array($row['TYPE_NAME'],array('nchar','nvarchar')) )
449 $columns[$column_name]['len']=strtolower($row['PRECISION']);
450 elseif ( !in_array($row['TYPE_NAME'],array('datetime','text','bit')) )
451 $columns[$column_name]['len']=strtolower($row['LENGTH']);
452 if ( stristr($row['TYPE_NAME'],'identity') ) {
453 $columns[$column_name]['auto_increment'] = '1';
454 $columns[$column_name]['type']=str_replace(' identity','',strtolower($row['TYPE_NAME']));
457 if (!empty($row['IS_NULLABLE']) && $row['IS_NULLABLE'] == 'NO' && (empty($row['KEY']) || !stristr($row['KEY'],'PRI')))
458 $columns[strtolower($row['COLUMN_NAME'])]['required'] = 'true';
461 if ( strtolower($tablename) == 'relationships' ) {
462 $column_def = $this->db->getOne("select cdefault from syscolumns where id = object_id('relationships') and name = '$column_name'");
464 if ( $column_def != 0 ) {
466 $row['COLUMN_DEF'] = html_entity_decode($row['COLUMN_DEF'],ENT_QUOTES);
467 if ( preg_match("/\([\(|'](.*)[\)|']\)/i",$row['COLUMN_DEF'],$matches) )
468 $columns[$column_name]['default'] = $matches[1];
469 elseif ( preg_match("/\(N'(.*)'\)/i",$row['COLUMN_DEF'],$matches) )
470 $columns[$column_name]['default'] = $matches[1];
472 $columns[$column_name]['default'] = $row['COLUMN_DEF'];
479 * @see DBHelper::add_drop_constraint()
481 public function add_drop_constraint(
487 $type = $definition['type'];
488 $fields = implode(',',$definition['fields']);
489 $name = $definition['name'];
490 $foreignTable = isset($definition['foreignTable']) ? $definition['foreignTable'] : array();
496 case 'alternate_key':
498 $sql = "DROP INDEX {$name} ON {$table}";
500 $sql = "CREATE INDEX {$name} ON {$table} ({$fields})";
502 // constraints as indices
505 $sql = "ALTER TABLE {$table} DROP CONSTRAINT $name";
507 $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name} UNIQUE ({$fields})";
511 $sql = "ALTER TABLE {$table} DROP PRIMARY KEY";
513 $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name} PRIMARY KEY ({$fields})";
517 $sql = "ALTER TABLE {$table} DROP FOREIGN KEY ({$fields})";
519 $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name} FOREIGN KEY ({$fields}) REFERENCES {$foreignTable}({$foreignfields})";
522 if ($this->full_text_indexing_enabled() && $drop)
523 $sql = "DROP FULLTEXT INDEX ON {$table}";
524 elseif ($this->full_text_indexing_enabled()) {
525 $catalog_name="sugar_fts_catalog";
526 if ( isset($index['catalog_name']) && $index['catalog_name'] != 'default')
527 $catalog_name = $index['catalog_name'];
529 $language = "Language 1033";
530 if (isset($index['language']) && !empty($index['language']))
531 $language = "Language " . $index['language'];
533 $key_index = $index['key_index'];
535 $change_tracking = "auto";
536 if (isset($index['change_tracking']) && !empty($index['change_tracking']))
537 $change_tracking = $index['change_tracking'];
539 $columns[] = " CREATE FULLTEXT INDEX ON $table ($fields $language) KEY INDEX $key_index ON $catalog_name WITH CHANGE_TRACKING $change_tracking" ;
547 * @see DBHelper::number_of_columns()
549 public function number_of_columns(
553 $def_query = <<<EOSQL
554 SELECT count(*) as cols
555 FROM sys.columns col join sys.types col_type
556 on col.user_type_id=col_type.user_type_id
557 where col.object_id = (
558 select object_id(sys.schemas.name + '.' + sys.tables.name)
559 from sys.tables join sys.schemas
560 on sys.schemas.schema_id = sys.tables.schema_id
561 where sys.tables.name='$table_name'
565 * @TODO test the similarities of the above the query against all system tables vs the query below against
566 * the information_schema view in terms of results and efficiency. suspician is provided the two produce
567 * the same information the latter will be slightly faster.
570 * $def_query = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$table_name'";
575 $result = $this->db->query($def_query);
576 $row = $this->db->fetchByAssoc($result);
584 * Returns true if Full Text Search is installed
588 protected function full_text_indexing_installed()
590 $ftsChckRes = $this->db->query(
591 "SELECT FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') as fts");
592 $row = $this->db->fetchByAssoc($ftsChckRes);
594 return (isset($row) && isset($row['fts']) && ($row['fts'] == 1 || $row['fts'] == '1'));
598 * @see DBHelper::full_text_indexing_enabled()
600 protected function full_text_indexing_enabled(
604 // check to see if we already have install setting in session
605 if(!isset($_SESSION['IsFulltextInstalled']))
606 $_SESSION['IsFulltextInstalled'] = $this->full_text_indexing_installed();
608 // check to see if FTS Indexing service is installed
609 if(empty($_SESSION['IsFulltextInstalled'])
610 || $_SESSION['IsFulltextInstalled'] === false)
613 // grab the dbname if it was not passed through
614 if (empty($dbname)) {
615 global $sugar_config;
616 $dbname = $sugar_config['dbconfig']['db_name'];
618 //we already know that Indexing service is installed, now check
619 //to see if it is enabled
620 $res = $this->db->query(
621 "SELECT DATABASEPROPERTY('$dbname', 'IsFulltextEnabled') ftext");
622 $row = $GLOBALS['db']->fetchByAssoc($res);
624 return (isset($row['ftext']) && $row['ftext'] == 1);
628 * Creates default full text catalog
630 public function create_default_full_text_catalog()
632 if ($this->full_text_indexing_enabled()) {
633 $GLOBALS['log']->debug('Creating the default catalog for full-text indexing, sugar_fts_catalog');
635 //drop catalog if exists.
636 $ret = $this->db->query("
639 from sys.fulltext_catalogs
640 where name ='sugar_fts_catalog'
642 CREATE FULLTEXT CATALOG sugar_fts_catalog");
645 $GLOBALS['log']->error('Error creating default full-text catalog, sugar_fts_catalog');
651 * Function returns name of the constraint automatically generated by sql-server.
652 * We request this for default, primary key, required
654 * @param string $table
655 * @param string $column
658 private function get_field_default_constraint_name(
663 static $results = array();
665 if ( empty($column) && isset($results[$table]) )
666 return $results[$table];
669 select s.name, o.name, c.name dtrt, d.name ctrt
670 from sys.default_constraints as d
671 join sys.objects as o
672 on o.object_id = d.parent_object_id
673 join sys.columns as c
674 on c.object_id = o.object_id and c.column_id = d.parent_column_id
675 join sys.schemas as s
676 on s.schema_id = o.schema_id
677 where o.name = '$table'
679 if ( !empty($column) )
680 $query .= " and c.name = '$column'";
681 $res = $this->db->query($query);
682 if ( !empty($column) ) {
683 $row = $this->db->fetchByAssoc($res);
688 $returnResult = array();
689 while ( $row = $this->db->fetchByAssoc($res) )
690 $returnResult[$row['dtrt']] = $row['ctrt'];
691 $results[$table] = $returnResult;
692 return $returnResult;
699 * @see DBHelper::massageFieldDef()
701 public function massageFieldDef(
706 parent::massageFieldDef($fieldDef,$tablename);
708 if ($fieldDef['type'] == 'int')
709 $fieldDef['len'] = '4';
710 if ($fieldDef['type'] == 'bit' && empty($fieldDef['len']) )
711 $fieldDef['len'] = '1';
712 if ($fieldDef['type'] == 'bool' && empty($fieldDef['len']) )
713 $fieldDef['len'] = '1';
714 if ($fieldDef['type'] == 'float' && empty($fieldDef['len']) )
715 $fieldDef['len'] = '8';
716 if ($fieldDef['type'] == 'varchar' && empty($fieldDef['len']) )
717 $fieldDef['len'] = '255';
718 if ($fieldDef['type'] == 'nvarchar' && empty($fieldDef['len']) )
719 $fieldDef['len'] = '255';
720 if ($fieldDef['type'] == 'bit' && empty($fieldDef['default']) )
721 $fieldDef['default'] = '0';
722 if ($fieldDef['type'] == 'bool' && empty($fieldDef['default']) )
723 $fieldDef['default'] = '0';
724 if ($fieldDef['type'] == 'image' && empty($fieldDef['len']) )
725 $fieldDef['len'] = '2147483647';
726 if ($fieldDef['type'] == 'ntext' && empty($fieldDef['len']) )
727 $fieldDef['len'] = '2147483646';
728 if ($fieldDef['type'] == 'smallint' && empty($fieldDef['len']) )
729 $fieldDef['len'] = '2';
730 if (isset($fieldDef['required']) && $fieldDef['required'] && !isset($fieldDef['default']) )
731 $fieldDef['default'] = '';
735 * @see DBHelper::oneColumnSQLRep()
737 protected function oneColumnSQLRep(
739 $ignoreRequired = false,
741 $return_as_array = false
745 if(isset($fieldDef['name'])){
746 $name = $fieldDef['name'];
747 $type = $this->getFieldType($fieldDef);
748 $colType = $this->getColumnType($type, $name, $table);
749 if(stristr($colType, 'decimal')){
750 $fieldDef['len'] = isset($fieldDef['len'])? min($fieldDef['len'],38) : 38;
752 //bug: 39690 float(8) is interpreted as real and this generates a diff when doing repair
753 if(stristr($colType, 'float')){
754 if(isset($fieldDef['len']) && $fieldDef['len'] == 8){
755 unset($fieldDef['len']);
760 $ref = parent::oneColumnSQLRep($fieldDef, $ignoreRequired, $table, true);
762 // Bug 24307 - Don't add precision for float fields.
763 if ( stristr($ref['colType'],'float') )
764 $ref['colType'] = preg_replace('/(,\d+)/','',$ref['colType']);
766 if ( $return_as_array )
769 return "{$ref['name']} {$ref['colType']} {$ref['default']} {$ref['required']} {$ref['auto_increment']}";
773 * Saves changes to module's audit table
775 * @param object $bean Sugarbean instance
776 * @param array $changes changes
777 * @see DBHelper::getDataChanges()
779 public function save_audit_records(
784 //Bug 25078 fixed by Martin Hu: sqlserver haven't 'date' type, trim extra "00:00:00"
785 if($changes['data_type'] == 'date'){
786 $changes['before'] = str_replace(' 00:00:00','',$changes['before']);
788 parent::save_audit_records($bean,$changes);