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');
93 class SqlsrvManager extends MssqlManager
96 * @see DBManager::$backendFunctions
98 protected $backendFunctions = array(
99 'free_result' => 'sqlsrv_free_stmt',
100 'close' => 'sqlsrv_close',
104 * cache of the results sets as they are fetched
106 protected $_resultsCache;
109 * cache of the results sets as they are fetched
111 protected $_lastResultsCacheKey = 0;
114 public function __construct()
116 parent::__construct();
117 $this->_resultsCache = new ArrayObject;
121 * @see DBManager::connect()
123 public function connect(
124 array $configOptions = null,
128 global $sugar_config;
130 if (is_null($configOptions))
131 $configOptions = $sugar_config['dbconfig'];
133 //set the connections parameters
135 $configOptions['db_host_instance'] = trim($configOptions['db_host_instance']);
136 if (empty($configOptions['db_host_instance']))
137 $connect_param = $configOptions['db_host_name'];
139 $connect_param = $configOptions['db_host_name']."\\".$configOptions['db_host_instance'];
142 * Don't try to specifically use a persistent connection
143 * since the driver will handle that for us
145 $this->database = sqlsrv_connect(
148 "UID" => $configOptions['db_user_name'],
149 "PWD" => $configOptions['db_password'],
150 "Database" => $configOptions['db_name'],
151 "CharacterSet" => "UTF-8",
152 "ReturnDatesAsStrings" => true,
153 "MultipleActiveResultSets" => true,
156 if(empty($this->database)) {
157 $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].".");
158 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
161 if($this->checkError('Could Not Connect:', $dieOnError))
162 $GLOBALS['log']->info("connected to db");
164 sqlsrv_query($this->database, 'SET DATEFORMAT mdy');
166 $GLOBALS['log']->info("Connect:".$this->database);
170 * @see DBManager::checkError()
172 public function checkError(
177 if (DBManager::checkError($msg, $dieOnError))
180 $sqlmsg = $this->_getLastErrorMessages();
181 $sqlpos = strpos($sqlmsg, 'Changed database context to');
182 $sqlpos2 = strpos($sqlmsg, 'Warning:');
183 $sqlpos3 = strpos($sqlmsg, 'Checking identity information:');
184 if ( $sqlpos !== false || $sqlpos2 !== false || $sqlpos3 !== false )
185 $sqlmsg = ''; // empty out sqlmsg if its something we will ignor
188 //ERR_MSSQL_DB_CONTEXT: localized version of 'Changed database context to' message
189 if (empty($app_strings)
190 or !isset($app_strings['ERR_MSSQL_DB_CONTEXT'])
191 or !isset($app_strings['ERR_MSSQL_WARNING']) ) {
192 //ignore the message from sql-server if $app_strings array is empty. This will happen
193 //only if connection if made before languge is set.
194 $GLOBALS['log']->debug("Ignoring this database message: " . $sqlmsg);
198 $sqlpos = strpos($sqlmsg, $app_strings['ERR_MSSQL_DB_CONTEXT']);
199 $sqlpos2 = strpos($sqlmsg, $app_strings['ERR_MSSQL_WARNING']);
200 if ( $sqlpos !== false || $sqlpos2 !== false)
205 if ( strlen($sqlmsg) > 2 ) {
206 $GLOBALS['log']->fatal("SQL Server error: " . $sqlmsg);
214 * @see DBManager::query()
216 public function query(
225 $sql = $this->_appendN($sql);
227 $this->countQuery($sql);
228 $GLOBALS['log']->info('Query:' . $sql);
229 $this->checkConnection();
230 $this->query_time = microtime(true);
235 $result = @sqlsrv_query($this->database, $sql);
239 // awu Bug 10657: ignoring mssql error message 'Changed database context to' - an intermittent
240 // and difficult to reproduce error. The message is only a warning, and does
241 // not affect the functionality of the query
243 $sqlmsg = $this->_getLastErrorMessages();
244 $sqlpos = strpos($sqlmsg, 'Changed database context to');
245 $sqlpos2 = strpos($sqlmsg, 'Warning:');
246 $sqlpos3 = strpos($sqlmsg, 'Checking identity information:');
247 if ($sqlpos !== false || $sqlpos2 !== false || $sqlpos3 !== false) // if sqlmsg has 'Changed database context to', just log it
248 $GLOBALS['log']->debug($sqlmsg . ": " . $sql );
250 $GLOBALS['log']->fatal($sqlmsg . ": " . $sql );
252 sugar_die('SQL Error : ' . $sqlmsg);
254 echo 'SQL Error : ' . $sqlmsg;
257 $this->lastmysqlrow = -1;
259 $this->query_time = microtime(true) - $this->query_time;
260 $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
263 $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
269 * @see DBManager::getFieldsArray()
271 public function getFieldsArray(
273 $make_lower_case = false
276 $field_array = array();
282 foreach ( sqlsrv_field_metadata($result) as $fieldMetadata ) {
283 $key = $fieldMetadata['Name'];
284 if($make_lower_case==true)
285 $key = strtolower($key);
287 $field_array[] = $key;
294 * @see DBManager::fetchByAssoc()
296 public function fetchByAssoc(
306 $row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC);
311 foreach($row as $key => $column) {
312 // MSSQL returns a space " " when a varchar column is empty ("") and not null.
313 // We need to strip empty spaces
314 // notice we only strip if one space is returned. we do not want to strip
315 // strings with intentional spaces (" foo ")
316 if (!empty($column) && $column ==" ") {
319 // Strip off the extra .000 off of datetime fields
321 preg_match('/^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}).[0-9]{3}$/',$column,$matches);
322 if ( !empty($matches) && !empty($matches[1]) ) {
323 $row[$key] = $matches[1];
325 // HTML encode if needed
326 if($encode && $this->encode) {
327 $row[$key] = to_html($row[$key]);
335 * @see DBManager::getRowCount()
337 public function getRowCount(
341 return $this->getOne('SELECT @@ROWCOUNT');
345 * Emulates old mssql_get_last_message() behavior, giving us any error messages from the previous
348 * @return string error message(s)
350 private function _getLastErrorMessages()
354 if ( ($errors = sqlsrv_errors()) != null)
355 foreach ( $errors as $error )
356 $message .= $error['message'] . '. ';
362 * @see DBManager::convert()
364 public function convert(
367 array $additional_parameters = array(),
368 array $additional_parameters_oracle_only = array()
371 if ( $type == 'datetime')
372 return "CONVERT(varchar(25)," . $string . ",120)";
374 return parent::convert($string, $type, $additional_parameters, $additional_parameters_oracle_only);
378 * This is a utility function to prepend the "N" character in front of SQL values that are
379 * surrounded by single quotes.
381 * @param $sql string SQL statement
382 * @return string SQL statement with single quote values prepended with "N" character for nvarchar columns
384 private function _appendN(
388 // If there are no single quotes, don't bother, will just assume there is no character data
389 if (strpos($sql, "'") === false)
392 // Flag if there are odd number of single quotes, just continue w/o trying to append N
393 if ((substr_count($sql, "'") & 1)) {
394 $GLOBALS['log']->error("SQL statement[" . $sql . "] has odd number of single quotes.");
398 //The only location of three subsequent ' will be at the begning or end of a value.
399 $sql = preg_replace('/(?<!\')(\'{3})(?!\')/', "'<@#@#@PAIR@#@#@>", $sql);
401 // Remove any remaining '' and do not parse... replace later (hopefully we don't even have any)
403 $regexp = '/(\'{2})/';
404 $pair_matches = array();
405 preg_match_all($regexp, $sql, $pair_matches);
407 foreach (array_unique($pair_matches[0]) as $key=>$value) {
408 $pairs['<@PAIR-'.$key.'@>'] = $value;
410 if (!empty($pairs)) {
411 $sql = str_replace($pairs, array_keys($pairs), $sql);
415 $regexp = "/(N?\'.+?\')/is";
417 preg_match_all($regexp, $sql, $matches);
419 if (!empty($matches)) {
420 foreach ($matches[0] as $key=>$value) {
421 // We are assuming that all nvarchar columns are no more than 200 characters in length
422 // One problem we face is the image column type in reports which cannot accept nvarchar data
423 if (!empty($value) && !is_numeric(trim(str_replace(array("'", ","), "", $value))) && !preg_match('/^\'[\,]\'$/', $value)) {
424 $replace[$value] = 'N' . trim($value, "N");
429 if (!empty($replace))
430 $sql = str_replace(array_keys($replace), $replace, $sql);
433 $sql = str_replace(array_keys($pairs), $pairs, $sql);
435 if(strpos($sql, "<@#@#@PAIR@#@#@>"))
436 $sql = str_replace(array('<@#@#@PAIR@#@#@>'), array("''"), $sql);
442 * Compares two vardefs. Overriding 39098 due to bug: 39098 . IN 6.0 we changed the id columns to dbType = 'id'
443 * 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
444 * it to varchar. This code will prevent it.
446 * @param array $fielddef1
447 * @param array $fielddef2
448 * @return bool true if they match, false if they don't
450 public function compareVarDefs($fielddef1,$fielddef2)
452 if((isset($fielddef2['dbType']) && $fielddef2['dbType'] == 'id') || preg_match('/(_id$|^id$)/', $fielddef2['name'])){
453 if(isset($fielddef1['type']) && isset($fielddef2['type'])){
454 $fielddef2['type'] = $fielddef1['type'];
457 return parent::compareVarDefs($fielddef1, $fielddef2);