2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM Community Edition is a customer relationship management program developed by
5 * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
7 * This program is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU Affero General Public License version 3 as published by the
9 * Free Software Foundation with the addition of the following permission added
10 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
11 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
12 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
14 * This program is distributed in the hope that it will be useful, but WITHOUT
15 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
19 * You should have received a copy of the GNU Affero General Public License along with
20 * this program; if not, see http://www.gnu.org/licenses or write to the Free
21 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
24 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
25 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
27 * The interactive user interfaces in modified source and object code versions
28 * of this program must display Appropriate Legal Notices, as required under
29 * Section 5 of the GNU Affero General Public License version 3.
31 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
32 * these Appropriate Legal Notices must retain the display of the "Powered by
33 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
34 * technical reasons, the Appropriate Legal Notices must display the words
35 * "Powered by SugarCRM".
36 ********************************************************************************/
38 /*********************************************************************************
40 * Description: This file handles the Data base functionality for the application.
41 * It acts as the DB abstraction layer for the application. It depends on helper classes
42 * which generate the necessary SQL. This sql is then passed to PEAR DB classes.
43 * The helper class is chosen in DBManagerFactory, which is driven by 'db_type' in 'dbconfig' under config.php.
45 * All the functions in this class will work with any bean which implements the meta interface.
46 * The passed bean is passed to helper class which uses these functions to generate correct sql.
48 * The meta interface has the following functions:
49 * getTableName() Returns table name of the object.
50 * getFieldDefinitions() Returns a collection of field definitions in order.
51 * getFieldDefintion(name) Return field definition for the field.
52 * getFieldValue(name) Returns the value of the field identified by name.
53 * If the field is not set, the function will return boolean FALSE.
54 * getPrimaryFieldDefinition() Returns the field definition for primary key
56 * The field definition is an array with the following keys:
58 * name This represents name of the field. This is a required field.
59 * type This represents type of the field. This is a required field and valid values are:
73 * length This is used only when the type is varchar and denotes the length of the string.
74 * The max value is 255.
75 * enumvals This is a list of valid values for an enum separated by "|".
76 * It is used only if the type is ?enum?;
77 * required This field dictates whether it is a required value.
78 * The default value is ?FALSE?.
79 * isPrimary This field identifies the primary key of the table.
80 * If none of the fields have this flag set to ?TRUE?,
81 * the first field definition is assume to be the primary key.
82 * Default value for this field is ?FALSE?.
83 * default This field sets the default value for the field definition.
86 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
87 * All Rights Reserved.
88 * Contributor(s): ______________________________________..
89 ********************************************************************************/
91 include_once('include/database/MssqlManager.php');
94 * SQL Server (sqlsrv) manager
96 class SqlsrvManager extends MssqlManager
98 public $dbName = 'SQL Server';
99 public $variant = 'sqlsrv';
100 public $priority = 10;
101 public $label = 'LBL_MSSQL_SQLSRV';
103 protected $capabilities = array(
104 "affected_rows" => true,
106 'limit_subquery' => true,
107 'create_user' => true,
111 protected $type_map = array(
118 'short' => 'smallint',
119 'varchar' => 'nvarchar',
120 'text' => 'nvarchar(max)',
121 'longtext' => 'nvarchar(max)',
122 'date' => 'datetime',
123 'enum' => 'nvarchar',
124 'relate' => 'nvarchar',
125 'multienum'=> 'nvarchar(max)',
126 'html' => 'nvarchar(max)',
127 'datetime' => 'datetime',
128 'datetimecombo' => 'datetime',
129 'time' => 'datetime',
131 'tinyint' => 'tinyint',
133 'blob' => 'nvarchar(max)',
134 'longblob' => 'nvarchar(max)',
135 'currency' => 'decimal(26,6)',
136 'decimal' => 'decimal',
137 'decimal2' => 'decimal',
138 'id' => 'varchar(36)',
140 'encrypt' => 'nvarchar',
141 'file' => 'nvarchar',
142 'decimal_tpl' => 'decimal(%d, %d)',
146 * @see DBManager::connect()
148 public function connect(array $configOptions = null, $dieOnError = false)
150 global $sugar_config;
152 if (is_null($configOptions))
153 $configOptions = $sugar_config['dbconfig'];
155 //set the connections parameters
157 $configOptions['db_host_instance'] = trim($configOptions['db_host_instance']);
158 if (empty($configOptions['db_host_instance']))
159 $connect_param = $configOptions['db_host_name'];
161 $connect_param = $configOptions['db_host_name']."\\".$configOptions['db_host_instance'];
164 * Don't try to specifically use a persistent connection
165 * since the driver will handle that for us
168 "UID" => $configOptions['db_user_name'],
169 "PWD" => $configOptions['db_password'],
170 "CharacterSet" => "UTF-8",
171 "ReturnDatesAsStrings" => true,
172 "MultipleActiveResultSets" => true,
174 if(!empty($configOptions['db_name'])) {
175 $options["Database"] = $configOptions['db_name'];
177 $this->database = sqlsrv_connect($connect_param, $options);
178 if(empty($this->database)) {
179 $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].".");
181 if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
182 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
184 sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
191 if($this->checkError('Could Not Connect:', $dieOnError))
192 $GLOBALS['log']->info("connected to db");
194 sqlsrv_query($this->database, 'SET DATEFORMAT mdy');
196 $this->connectOptions = $configOptions;
198 $GLOBALS['log']->info("Connect:".$this->database);
203 * @see DBManager::query()
205 public function query($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false)
208 return $this->queryArray($sql, $dieOnError, $msg, $suppress);
210 $sql = $this->_appendN($sql);
212 $this->countQuery($sql);
213 $GLOBALS['log']->info('Query:' . $sql);
214 $this->checkConnection();
215 $this->query_time = microtime(true);
217 $result = $suppress?@sqlsrv_query($this->database, $sql):sqlsrv_query($this->database, $sql);
219 $this->query_time = microtime(true) - $this->query_time;
220 $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
223 $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
225 //suppress non error messages
226 sqlsrv_configure('WarningsReturnAsErrors',false);
232 * @see DBManager::getFieldsArray()
234 public function getFieldsArray($result, $make_lower_case = false)
236 $field_array = array();
242 foreach ( sqlsrv_field_metadata($result) as $fieldMetadata ) {
243 $key = $fieldMetadata['Name'];
244 if($make_lower_case==true)
245 $key = strtolower($key);
247 $field_array[] = $key;
254 * @see DBManager::fetchRow()
256 public function fetchRow($result)
258 if (empty($result)) return false;
260 $row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC);
265 foreach($row as $key => $column) {
266 // MSSQL returns a space " " when a varchar column is empty ("") and not null.
267 // We need to strip empty spaces
268 // notice we only strip if one space is returned. we do not want to strip
269 // strings with intentional spaces (" foo ")
270 if (!empty($column) && $column == " ") {
279 * @see DBManager::convert()
281 public function convert($string, $type, array $additional_parameters = array())
283 if ( $type == 'datetime')
284 // see http://msdn.microsoft.com/en-us/library/ms187928.aspx for details
285 return "CONVERT(datetime,$string,120)";
287 return parent::convert($string, $type, $additional_parameters);
291 * Compares two vardefs. Overriding 39098 due to bug: 39098 . IN 6.0 we changed the id columns to dbType = 'id'
292 * for example emails_beans. In 554 the field email_id was nvarchar but in 6.0 since it id dbType = 'id' we would want to alter
293 * it to varchar. This code will prevent it.
295 * @param array $fielddef1
296 * @param array $fielddef2
297 * @return bool true if they match, false if they don't
299 public function compareVarDefs($fielddef1,$fielddef2)
301 if((isset($fielddef2['dbType']) && $fielddef2['dbType'] == 'id') || preg_match('/(_id$|^id$)/', $fielddef2['name'])){
302 if(isset($fielddef1['type']) && isset($fielddef2['type'])){
303 $fielddef2['type'] = $fielddef1['type'];
306 return parent::compareVarDefs($fielddef1, $fielddef2);
310 * Disconnects from the database
312 * Also handles any cleanup needed
314 public function disconnect()
316 $GLOBALS['log']->debug('Calling Mssql::disconnect()');
317 if(!empty($this->database)){
319 sqlsrv_close($this->database);
320 $this->database = null;
325 * @see DBManager::freeDbResult()
327 protected function freeDbResult($dbResult)
329 if(!empty($dbResult))
330 sqlsrv_free_stmt($dbResult);
335 * Detect if no clustered index has been created for a table; if none created then just pick the first index and make it that
337 * @see MssqlHelper::indexSQL()
339 public function getConstraintSql($indices, $table)
341 if ( $this->doesTableHaveAClusteredIndexDefined($table) ) {
342 return parent::getConstraintSql($indices, $table);
345 // check to see if one of the passed in indices is a primary one; if so we can bail as well
346 foreach ( $indices as $index ) {
347 if ( $index['type'] == 'primary' ) {
348 return parent::getConstraintSql($indices, $table);
352 // Change the first index listed to be a clustered one instead ( so we have at least one for the table )
353 if ( isset($indices[0]) ) {
354 $indices[0]['type'] = 'clustered';
357 return parent::getConstraintSql($indices, $table);
361 * @see DBManager::get_columns()
363 public function get_columns($tablename)
365 //find all unique indexes and primary keys.
366 $result = $this->query("sp_columns_90 $tablename");
369 while (($row=$this->fetchByAssoc($result)) !=null) {
370 $column_name = strtolower($row['COLUMN_NAME']);
371 $columns[$column_name]['name']=$column_name;
372 $columns[$column_name]['type']=strtolower($row['TYPE_NAME']);
373 if ( $row['TYPE_NAME'] == 'decimal' ) {
374 $columns[$column_name]['len']=strtolower($row['PRECISION']);
375 $columns[$column_name]['len'].=','.strtolower($row['SCALE']);
377 elseif ( in_array($row['TYPE_NAME'],array('nchar','nvarchar')) ) {
378 $columns[$column_name]['len']=strtolower($row['PRECISION']);
379 if ( $row['TYPE_NAME'] == 'nvarchar' && $row['PRECISION'] == '0' ) {
380 $columns[$column_name]['len']='max';
383 elseif ( !in_array($row['TYPE_NAME'],array('datetime','text')) ) {
384 $columns[$column_name]['len']=strtolower($row['LENGTH']);
386 if ( stristr($row['TYPE_NAME'],'identity') ) {
387 $columns[$column_name]['auto_increment'] = '1';
388 $columns[$column_name]['type']=str_replace(' identity','',strtolower($row['TYPE_NAME']));
391 if (!empty($row['IS_NULLABLE']) && $row['IS_NULLABLE'] == 'NO' && (empty($row['KEY']) || !stristr($row['KEY'],'PRI')))
392 $columns[strtolower($row['COLUMN_NAME'])]['required'] = 'true';
395 if ( strtolower($tablename) == 'relationships' ) {
396 $column_def = $this->getOne("select cdefault from syscolumns where id = object_id('relationships') and name = '$column_name'");
398 if ( $column_def != 0 && ($row['COLUMN_DEF'] != null)) { // NOTE Not using !empty as an empty string may be a viable default value.
400 $row['COLUMN_DEF'] = html_entity_decode($row['COLUMN_DEF'],ENT_QUOTES);
401 if ( preg_match('/\([\(|\'](.*)[\)|\']\)/i',$row['COLUMN_DEF'],$matches) )
402 $columns[$column_name]['default'] = $matches[1];
403 elseif ( preg_match('/\(N\'(.*)\'\)/i',$row['COLUMN_DEF'],$matches) )
404 $columns[$column_name]['default'] = $matches[1];
406 $columns[$column_name]['default'] = $row['COLUMN_DEF'];
413 * @see DBManager::get_indices()
415 public function get_indices($tableName)
417 //find all unique indexes and primary keys.
419 SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name,
420 sys.indexes.name as index_name, sys.indexes.is_unique, sys.indexes.is_primary_key
421 FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
422 WHERE (sys.tables.object_id = sys.indexes.object_id
423 AND sys.tables.object_id = sys.index_columns.object_id
424 AND sys.tables.object_id = sys.columns.object_id
425 AND sys.indexes.index_id = sys.index_columns.index_id
426 AND sys.index_columns.column_id = sys.columns.column_id)
427 AND sys.tables.name = '$tableName'
429 $result = $this->query($query);
432 while (($row=$this->fetchByAssoc($result)) != null) {
433 $index_type = 'index';
434 if ($row['is_primary_key'] == '1')
435 $index_type = 'primary';
436 elseif ($row['is_unique'] == 1 )
437 $index_type = 'unique';
438 $name = strtolower($row['index_name']);
439 $indices[$name]['name'] = $name;
440 $indices[$name]['type'] = $index_type;
441 $indices[$name]['fields'][] = strtolower($row['column_name']);
447 * protected function to return true if the given tablename has any clustered indexes defined.
449 * @param string $tableName
452 protected function doesTableHaveAClusteredIndexDefined($tableName)
455 SELECT IST.TABLE_NAME
456 FROM INFORMATION_SCHEMA.TABLES IST
457 WHERE objectProperty(object_id(IST.TABLE_NAME), 'IsUserTable') = 1
458 AND objectProperty(object_id(IST.TABLE_NAME), 'TableHasClustIndex') = 1
459 AND IST.TABLE_NAME = '{$tableName}'
462 $result = $this->getOne($query);
471 * protected function to return true if the given tablename has any fulltext indexes defined.
473 * @param string $tableName
476 protected function doesTableHaveAFulltextIndexDefined($tableName)
480 FROM sys.fulltext_indexes i
481 JOIN sys.objects o ON i.object_id = o.object_id
482 WHERE o.name = '{$tableName}'
485 $result = $this->getOne($query);
494 * Override method to add support for detecting and dropping fulltext indices.
496 * @see DBManager::changeColumnSQL()
497 * @see MssqlHelper::changeColumnSQL()
499 protected function changeColumnSQL($tablename,$fieldDefs, $action, $ignoreRequired = false)
502 if ( $action == 'drop' && $this->doesTableHaveAFulltextIndexDefined($tablename) ) {
503 $sql .= "DROP FULLTEXT INDEX ON {$tablename}";
506 $sql .= parent::changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired);
514 * @see DBManager::lastDbError()
516 public function lastDbError()
518 $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
519 if(empty($errors)) return false;
521 if (empty($app_strings)
522 or !isset($app_strings['ERR_MSSQL_DB_CONTEXT'])
523 or !isset($app_strings['ERR_MSSQL_WARNING']) ) {
524 //ignore the message from sql-server if $app_strings array is empty. This will happen
525 //only if connection if made before languge is set.
529 foreach($errors as $error) {
530 $sqlmsg = $error['message'];
531 $sqlpos = strpos($sqlmsg, 'Changed database context to');
532 $sqlpos2 = strpos($sqlmsg, 'Warning:');
533 $sqlpos3 = strpos($sqlmsg, 'Checking identity information:');
534 if ( $sqlpos !== false || $sqlpos2 !== false || $sqlpos3 !== false ) {
537 $sqlpos = strpos($sqlmsg, $app_strings['ERR_MSSQL_DB_CONTEXT']);
538 $sqlpos2 = strpos($sqlmsg, $app_strings['ERR_MSSQL_WARNING']);
539 if ( $sqlpos !== false || $sqlpos2 !== false) {
542 $messages[] = $sqlmsg;
545 if(!empty($messages)) {
546 return join("\n", $messages);
553 * @see DBManager::getDbInfo()
556 public function getDbInfo()
558 $info = array_merge(sqlsrv_client_info(), sqlsrv_server_info());
563 * Execute data manipulation statement, then roll it back
569 protected function verifyGenericQueryRollback($type, $table, $query)
571 $this->log->debug("verifying $type statement");
572 if(!sqlsrv_begin_transaction($this->database)) {
573 return "Failed to create transaction";
575 $this->query($query, false);
576 $error = $this->lastError();
577 sqlsrv_rollback($this->database);
582 * Tests an INSERT INTO query
583 * @param string table The table name to get DDL
584 * @param string query The query to test.
585 * @return string Non-empty if error found
587 public function verifyInsertInto($table, $query)
589 return $this->verifyGenericQueryRollback("INSERT", $table, $query);
593 * Tests an UPDATE query
594 * @param string table The table name to get DDL
595 * @param string query The query to test.
596 * @return string Non-empty if error found
598 public function verifyUpdate($table, $query)
600 return $this->verifyGenericQueryRollback("UPDATE", $table, $query);
604 * Tests an DELETE FROM query
605 * @param string table The table name to get DDL
606 * @param string query The query to test.
607 * @return string Non-empty if error found
609 public function verifyDeleteFrom($table, $query)
611 return $this->verifyGenericQueryRollback("DELETE", $table, $query);
616 * @param string $dbname
618 protected function selectDb($dbname)
620 return $this->query("USE ".$this->quoted($dbname));
624 * Check if this driver can be used
627 public function valid()
629 return function_exists("sqlsrv_connect");