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 * SQL Server (mssql) manager
94 class MssqlManager extends DBManager
97 * @see DBManager::$dbType
99 public $dbType = 'mssql';
100 public $dbName = 'MsSQL';
101 public $variant = 'mssql';
102 public $label = 'LBL_MSSQL';
104 protected $capabilities = array(
105 "affected_rows" => true,
106 "select_rows" => true,
108 'limit_subquery' => true,
109 "fix:expandDatabase" => true, // Support expandDatabase fix
110 "create_user" => true,
115 * Maximum length of identifiers
117 protected $maxNameLengths = array(
124 protected $type_map = array(
131 'short' => 'smallint',
132 'varchar' => 'varchar',
134 'longtext' => 'text',
135 'date' => 'datetime',
137 'relate' => 'varchar',
138 'multienum'=> 'text',
140 'longhtml' => 'text',
141 'datetime' => 'datetime',
142 'datetimecombo' => 'datetime',
143 'time' => 'datetime',
145 'tinyint' => 'tinyint',
148 'longblob' => 'image',
149 'currency' => 'decimal(26,6)',
150 'decimal' => 'decimal',
151 'decimal2' => 'decimal',
152 'id' => 'varchar(36)',
154 'encrypt' => 'varchar',
156 'decimal_tpl' => 'decimal(%d, %d)',
159 protected $connectOptions = null;
162 * @see DBManager::connect()
164 public function connect(array $configOptions = null, $dieOnError = false)
166 global $sugar_config;
168 if (is_null($configOptions))
169 $configOptions = $sugar_config['dbconfig'];
171 //SET DATEFORMAT to 'YYYY-MM-DD''
172 ini_set('mssql.datetimeconvert', '0');
174 //set the text size and textlimit to max number so that blob columns are not truncated
175 ini_set('mssql.textlimit','2147483647');
176 ini_set('mssql.textsize','2147483647');
177 ini_set('mssql.charset','UTF-8');
179 if(!empty($configOptions['db_host_instance'])) {
180 $configOptions['db_host_instance'] = trim($configOptions['db_host_instance']);
182 //set the connections parameters
183 if (empty($configOptions['db_host_instance'])) {
184 $connect_param = $configOptions['db_host_name'];
186 $connect_param = $configOptions['db_host_name']."\\".$configOptions['db_host_instance'];
189 //create persistent connection
190 if ($this->getOption('persistent')) {
191 $this->database =@mssql_pconnect(
193 $configOptions['db_user_name'],
194 $configOptions['db_password']
197 //if no persistent connection created, then create regular connection
198 if(!$this->database){
199 $this->database = mssql_connect(
201 $configOptions['db_user_name'],
202 $configOptions['db_password']
204 if(!$this->database){
205 $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name'].
206 " as ".$configOptions['db_user_name'].".");
208 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
213 if($this->database && $this->getOption('persistent')){
214 $_SESSION['administrator_error'] = "<B>Severe Performance Degradation: Persistent Database Connections "
215 . "not working. Please set \$sugar_config['dbconfigoption']['persistent'] to false in your "
216 . "config.php file</B>";
219 //make sure connection exists
220 if(!$this->database) {
222 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
230 //Adding sleep and retry for mssql connection. We have come across scenarios when
231 //an error is thrown.' Unable to select database'. Following will try to connect to
232 //mssql db maximum number of 5 times at the interval of .2 second. If can not connect
233 //it will throw an Unable to select database message.
235 if(!empty($configOptions['db_name']) && !@mssql_select_db($configOptions['db_name'], $this->database)){
239 if(@mssql_select_db($configOptions['db_name'], $this->database)){
245 $GLOBALS['log']->fatal( "Unable to select database {$configOptions['db_name']}");
247 if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
248 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
250 sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
258 if(!$this->checkError('Could Not Connect', $dieOnError))
259 $GLOBALS['log']->info("connected to db");
261 $this->connectOptions = $configOptions;
263 $GLOBALS['log']->info("Connect:".$this->database);
268 * @see DBManager::version()
270 public function version()
272 return $this->getOne("SELECT @@VERSION as version");
276 * @see DBManager::query()
278 public function query($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false)
281 return $this->queryArray($sql, $dieOnError, $msg, $suppress);
283 // Flag if there are odd number of single quotes
284 if ((substr_count($sql, "'") & 1))
285 $GLOBALS['log']->error("SQL statement[" . $sql . "] has odd number of single quotes.");
287 $sql = $this->_appendN($sql);
289 $GLOBALS['log']->info('Query:' . $sql);
290 $this->checkConnection();
291 $this->countQuery($sql);
292 $this->query_time = microtime(true);
294 // Bug 34892 - Clear out previous error message by checking the @@ERROR global variable
295 @mssql_query("SELECT @@ERROR", $this->database);
297 $result = $suppress?@mssql_query($sql, $this->database):mssql_query($sql, $this->database);
300 // awu Bug 10657: ignoring mssql error message 'Changed database context to' - an intermittent
301 // and difficult to reproduce error. The message is only a warning, and does
302 // not affect the functionality of the query
303 $sqlmsg = mssql_get_last_message();
304 $sqlpos = strpos($sqlmsg, 'Changed database context to');
305 $sqlpos2 = strpos($sqlmsg, 'Warning:');
306 $sqlpos3 = strpos($sqlmsg, 'Checking identity information:');
308 if ($sqlpos !== false || $sqlpos2 !== false || $sqlpos3 !== false) // if sqlmsg has 'Changed database context to', just log it
309 $GLOBALS['log']->debug($sqlmsg . ": " . $sql );
311 $GLOBALS['log']->fatal($sqlmsg . ": " . $sql );
313 sugar_die('SQL Error : ' . $sqlmsg);
315 echo 'SQL Error : ' . $sqlmsg;
319 $this->query_time = microtime(true) - $this->query_time;
320 $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
323 $this->checkError($msg.' Query Failed: ' . $sql, $dieOnError);
329 * This function take in the sql for a union query, the start and offset,
330 * and wraps it around an "mssql friendly" limit query
333 * @param int $start record to start at
334 * @param int $count number of records to retrieve
335 * @return string SQL statement
337 private function handleUnionLimitQuery($sql, $start, $count)
339 //set the start to 0, no negs
343 $GLOBALS['log']->debug(print_r(func_get_args(),true));
345 $this->lastsql = $sql;
347 //change the casing to lower for easier string comparison, and trim whitespaces
348 $sql = strtolower(trim($sql)) ;
351 $limitUnionSQL = $sql;
352 $order_by_str = 'order by';
354 //make array of order by's. substring approach was proving too inconsistent
355 $orderByArray = explode($order_by_str, $sql);
359 //count the number of array elements
360 $unionOrderByCount = count($orderByArray);
363 //process if there are elements
364 if ($unionOrderByCount){
365 //we really want the last order by, so reconstruct string
366 //adding a 1 to count, as we dont wish to process the last element
368 while ($unionOrderByCount>$arr_count+1) {
369 $unionsql .= $orderByArray[$arr_count];
370 $arr_count = $arr_count+1;
371 //add an "order by" string back if we are coming into loop again
372 //remember they were taken out when array was created
373 if ($unionOrderByCount>$arr_count+1) {
374 $unionsql .= "order by";
377 //grab the last order by element, set both order by's'
378 $unionOrderBy = $orderByArray[$arr_count];
379 $rowNumOrderBy = $unionOrderBy;
381 //if last element contains a "select", then this is part of the union query,
382 //and there is no order by to use
383 if (strpos($unionOrderBy, "select")) {
385 //with no guidance on what to use for required order by in rownumber function,
386 //resort to using name column.
387 $rowNumOrderBy = 'id';
392 //there are no order by elements, so just pass back string
394 //with no guidance on what to use for required order by in rownumber function,
395 //resort to using name column.
396 $rowNumOrderBy = 'id';
399 //Unions need the column name being sorted on to match across all queries in Union statement
400 //so we do not want to strip the alias like in other queries. Just add the "order by" string and
401 //pass column name as is
402 if ($unionOrderBy != '') {
403 $unionOrderBy = ' order by ' . $unionOrderBy;
406 //Bug 56560, use top query in conjunction with rownumber() function
407 //to create limit query when paging is needed. Otherwise,
408 //it shows duplicates when paging on activities subpanel.
409 //If not for paging, no need to use rownumber() function
410 if ($count == 1 && $start == 0)
412 $limitUnionSQL = "SELECT TOP $count * FROM (" .$unionsql .") as top_count ".$unionOrderBy;
416 $limitUnionSQL = "SELECT TOP $count * FROM( select ROW_NUMBER() OVER ( order by "
417 .$rowNumOrderBy.") AS row_number, * FROM ("
418 .$unionsql .") As numbered) "
419 . "As top_count_limit WHERE row_number > $start "
423 return $limitUnionSQL;
427 * FIXME: verify and thoroughly test this code, these regexps look fishy
428 * @see DBManager::limitQuery()
430 public function limitQuery($sql, $start, $count, $dieOnError = false, $msg = '', $execute = true)
432 $start = (int)$start;
433 $count = (int)$count;
435 $distinctSQLARRAY = array();
436 if (strpos($sql, "UNION") && !preg_match("/(')(UNION).?(')/i", $sql))
437 $newSQL = $this->handleUnionLimitQuery($sql,$start,$count);
441 $GLOBALS['log']->debug(print_r(func_get_args(),true));
442 $this->lastsql = $sql;
444 preg_match('/^(.*SELECT\b)(.*?\bFROM\b.*\bWHERE\b)(.*)$/isU',$sql, $matches);
445 if (!empty($matches[3])) {
447 $match_two = strtolower($matches[2]);
448 if (!strpos($match_two, "distinct")> 0 && strpos($match_two, "distinct") !==0) {
449 $orderByMatch = array();
450 preg_match('/^(.*)(\bORDER BY\b)(.*)$/is',$matches[3], $orderByMatch);
451 if (!empty($orderByMatch[3])) {
452 $selectPart = array();
453 preg_match('/^(.*)(\bFROM\b.*)$/isU', $matches[2], $selectPart);
454 $newSQL = "SELECT TOP $count * FROM
456 " . $matches[1] . $selectPart[1] . ", ROW_NUMBER()
457 OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number
458 " . $selectPart[2] . $orderByMatch[1]. "
460 WHERE row_number > $start";
463 $newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];
467 $distinct_o = strpos($match_two, "distinct");
468 $up_to_distinct_str = substr($match_two, 0, $distinct_o);
469 //check to see if the distinct is within a function, if so, then proceed as normal
470 if (strpos($up_to_distinct_str,"(")) {
472 $newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];
475 //if distinct is not within a function, then parse
476 //string contains distinct clause, "TOP needs to come after Distinct"
477 //get position of distinct
478 $match_zero = strtolower($matches[0]);
479 $distinct_pos = strpos($match_zero , "distinct");
480 //get position of where
481 $where_pos = strpos($match_zero, "where");
482 //parse through string
483 $beg = substr($matches[0], 0, $distinct_pos+9 );
484 $mid = substr($matches[0], strlen($beg), ($where_pos+5) - (strlen($beg)));
485 $end = substr($matches[0], strlen($beg) + strlen($mid) );
486 //repopulate matches array
487 $matches[1] = $beg; $matches[2] = $mid; $matches[3] = $end;
489 $newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];
493 $orderByMatch = array();
494 preg_match('/^(.*)(\bORDER BY\b)(.*)$/is',$matches[3], $orderByMatch);
496 //if there is a distinct clause, parse sql string as we will have to insert the rownumber
497 //for paging, AFTER the distinct clause
499 $hasDistinct = strpos(strtolower($matches[0]), "distinct");
501 require_once('include/php-sql-parser.php');
502 $parser = new PHPSQLParser();
503 $sqlArray = $parser->parse($sql);
506 $matches_sql = strtolower($matches[0]);
507 //remove reference to distinct and select keywords, as we will use a group by instead
508 //we need to use group by because we are introducing rownumber column which would make every row unique
510 //take out the select and distinct from string so we can reuse in group by
511 $dist_str = 'distinct';
512 preg_match('/\b' . $dist_str . '\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
513 $matches_sql = trim(substr($matches_sql,$matchesPartSQL[0][1] + strlen($dist_str)));
514 //get the position of where and from for further processing
515 preg_match('/\bfrom\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
516 $from_pos = $matchesPartSQL[0][1];
517 preg_match('/\where\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
518 $where_pos = $matchesPartSQL[0][1];
519 //split the sql into a string before and after the from clause
520 //we will use the columns being selected to construct the group by clause
522 $distinctSQLARRAY[0] = substr($matches_sql, 0, $from_pos);
523 $distinctSQLARRAY[1] = substr($matches_sql, $from_pos);
524 //get position of order by (if it exists) so we can strip it from the string
525 $ob_pos = strpos($distinctSQLARRAY[1], "order by");
527 $distinctSQLARRAY[1] = substr($distinctSQLARRAY[1],0,$ob_pos);
530 // strip off last closing parentheses from the where clause
531 $distinctSQLARRAY[1] = preg_replace('/\)\s$/',' ',$distinctSQLARRAY[1]);
535 foreach ($sqlArray['SELECT'] as $record) {
536 if ($record['expr_type'] == 'const') {
539 $grpByStr[] = trim($record['base_expr']);
541 $grpByStr = implode(', ', $grpByStr);
544 if (!empty($orderByMatch[3])) {
545 //if there is a distinct clause, form query with rownumber after distinct
547 $newSQL = "SELECT TOP $count * FROM
550 OVER (ORDER BY " . preg_replace('/^' . $dist_str . '\s+/', '', $this->returnOrderBy($sql, $orderByMatch[3])) . ") AS row_number,
551 count(*) counter, " . $distinctSQLARRAY[0] . "
552 " . $distinctSQLARRAY[1] . "
553 group by " . $grpByStr . "
555 WHERE row_number > $start";
558 $newSQL = "SELECT TOP $count * FROM
560 " . $matches[1] . " ROW_NUMBER()
561 OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number,
562 " . $matches[2] . $orderByMatch[1]. "
564 WHERE row_number > $start";
567 //if there is a distinct clause, form query with rownumber after distinct
569 $newSQL = "SELECT TOP $count * FROM
571 SELECT ROW_NUMBER() OVER (ORDER BY ".$grpByStr.") AS row_number, count(*) counter, " . $distinctSQLARRAY[0] . "
572 " . $distinctSQLARRAY[1] . "
573 group by " . $grpByStr . "
576 WHERE row_number > $start";
579 $newSQL = "SELECT TOP $count * FROM
581 " . $matches[1] . " ROW_NUMBER() OVER (ORDER BY " . $sqlArray['FROM'][0]['alias'] . ".id) AS row_number, " . $matches[2] . $matches[3]. "
584 WHERE row_number > $start";
591 $GLOBALS['log']->debug('Limit Query: ' . $newSQL);
593 $result = $this->query($newSQL, $dieOnError, $msg);
594 $this->dump_slow_queries($newSQL);
603 * Searches for begginning and ending characters. It places contents into
604 * an array and replaces contents in original string. This is used to account for use of
605 * nested functions while aliasing column names
607 * @param string $p_sql SQL statement
608 * @param string $strip_beg Beginning character
609 * @param string $strip_end Ending character
610 * @param string $patt Optional, pattern to
612 private function removePatternFromSQL($p_sql, $strip_beg, $strip_end, $patt = 'patt')
614 //strip all single quotes out
615 $count = substr_count ( $p_sql, $strip_beg);
617 if ($strip_beg != $strip_end)
622 $strip_array = array();
623 while ($i<$count && $offset<strlen($p_sql)) {
624 if ($offset > strlen($p_sql))
629 $beg_sin = strpos($p_sql, $strip_beg, $offset);
634 $sec_sin = strpos($p_sql, $strip_end, $beg_sin+1);
635 $strip_array[$patt.$i] = substr($p_sql, $beg_sin, $sec_sin - $beg_sin +1);
636 if ($increment > 1) {
637 //we are in here because beginning and end patterns are not identical, so search for nesting
638 $exists = strpos($strip_array[$patt.$i], $strip_beg );
640 $nested_pos = (strrpos($strip_array[$patt.$i], $strip_beg ));
641 $strip_array[$patt.$i] = substr($p_sql,$nested_pos+$beg_sin,$sec_sin - ($nested_pos+$beg_sin)+1);
642 $p_sql = substr($p_sql, 0, $nested_pos+$beg_sin) . " ##". $patt.$i."## " . substr($p_sql, $sec_sin+1);
647 $p_sql = substr($p_sql, 0, $beg_sin) . " ##". $patt.$i."## " . substr($p_sql, $sec_sin+1);
649 $offset = $sec_sin+1;
653 $strip_array['sql_string'] = $p_sql;
661 * @param string $token
662 * @param array $pattern_array
665 private function addPatternToSQL($token, array $pattern_array)
667 //strip all single quotes out
668 $pattern_array = array_reverse($pattern_array);
670 foreach ($pattern_array as $key => $replace) {
671 $token = str_replace( " ##".$key."## ", $replace,$token);
678 * gets an alias from the sql statement
681 * @param string $alias
684 private function getAliasFromSQL($sql, $alias)
687 preg_match('/^(.*SELECT)(.*?FROM.*WHERE)(.*)$/isU',$sql, $matches);
688 //parse all single and double quotes out of array
689 $sin_array = $this->removePatternFromSQL($matches[2], "'", "'","sin_");
690 $new_sql = array_pop($sin_array);
691 $dub_array = $this->removePatternFromSQL($new_sql, "\"", "\"","dub_");
692 $new_sql = array_pop($dub_array);
694 //search for parenthesis
695 $paren_array = $this->removePatternFromSQL($new_sql, "(", ")", "par_");
696 $new_sql = array_pop($paren_array);
698 //all functions should be removed now, so split the array on commas
699 $mstr_sql_array = explode(",", $new_sql);
700 foreach($mstr_sql_array as $token ) {
701 if (strpos($token, $alias)) {
702 //found token, add back comments
703 $token = $this->addPatternToSQL($token, $paren_array);
704 $token = $this->addPatternToSQL($token, $dub_array);
705 $token = $this->addPatternToSQL($token, $sin_array);
707 //log and break out of this function
716 * Finds the alias of the order by column, and then return the preceding column name
719 * @param string $orderMatch
722 private function findColumnByAlias($sql, $orderMatch)
724 //change case to lowercase
725 $sql = strtolower($sql);
726 $patt = '/\s+'.trim($orderMatch).'\s*(,|from)/';
728 //check for the alias, it should contain comma, may contain space, \n, or \t
730 preg_match($patt, $sql, $matches, PREG_OFFSET_CAPTURE);
731 $found_in_sql = isset($matches[0][1]) ? $matches[0][1] : false;
734 //set default for found variable
735 $found = $found_in_sql;
737 //if still no match found, then we need to parse through the string
739 //get count of how many times the match exists in string
740 $found_count = substr_count($sql, $orderMatch);
743 $len = strlen($orderMatch);
744 //loop through string as many times as there is a match
745 while ($found_count > $i) {
746 //get the first match
747 $found_in_sql = strpos($sql, $orderMatch,$first_);
748 //make sure there was a match
750 //grab the next 2 individual characters
751 $str_plusone = substr($sql,$found_in_sql + $len,1);
752 $str_plustwo = substr($sql,$found_in_sql + $len+1,1);
753 //if one of those characters is a comma, then we have our alias
754 if ($str_plusone === "," || $str_plustwo === ","){
755 //keep track of this position
756 $found = $found_in_sql;
759 //set the offset and increase the iteration counter
760 $first_ = $found_in_sql+$len;
764 //return $found, defaults have been set, so if no match was found it will be a negative number
770 * Return the order by string to use in case the column has been aliased
773 * @param string $orig_order_match
776 private function returnOrderBy($sql, $orig_order_match)
778 $sql = strtolower($sql);
779 $orig_order_match = trim($orig_order_match);
780 if (strpos($orig_order_match, ".") != 0)
781 //this has a tablename defined, pass in the order match
782 return $orig_order_match;
784 // If there is no ordering direction (ASC/DESC), use ASC by default
785 if (strpos($orig_order_match, " ") === false) {
786 $orig_order_match .= " ASC";
789 //grab first space in order by
790 $firstSpace = strpos($orig_order_match, " ");
792 //split order by into column name and ascending/descending
793 $orderMatch = " " . strtolower(substr($orig_order_match, 0, $firstSpace));
794 $asc_desc = trim(substr($orig_order_match,$firstSpace));
796 //look for column name as an alias in sql string
797 $found_in_sql = $this->findColumnByAlias($sql, $orderMatch);
799 if (!$found_in_sql) {
800 //check if this column needs the tablename prefixed to it
801 $orderMatch = ".".trim($orderMatch);
802 $colMatchPos = strpos($sql, $orderMatch);
803 if ($colMatchPos !== false) {
804 //grab sub string up to column name
805 $containsColStr = substr($sql,0, $colMatchPos);
806 //get position of first space, so we can grab table name
807 $lastSpacePos = strrpos($containsColStr, " ");
808 //use positions of column name, space before name, and length of column to find the correct column name
809 $col_name = substr($sql, $lastSpacePos, $colMatchPos-$lastSpacePos+strlen($orderMatch));
810 //bug 25485. When sorting by a custom field in Account List and then pressing NEXT >, system gives an error
811 $containsCommaPos = strpos($col_name, ",");
812 if($containsCommaPos !== false) {
813 $col_name = substr($col_name, $containsCommaPos+1);
815 //add the "asc/desc" order back
816 $col_name = $col_name. " ". $asc_desc;
821 //break out of here, log this
822 $GLOBALS['log']->debug("No match was found for order by, pass string back untouched as: $orig_order_match");
823 return $orig_order_match;
826 //if found, then parse and return
827 //grab string up to the aliased column
828 $GLOBALS['log']->debug("order by found, process sql string");
830 $psql = (trim($this->getAliasFromSQL($sql, $orderMatch )));
832 $psql = trim(substr($sql, 0, $found_in_sql));
834 //grab the last comma before the alias
835 preg_match('/\s+' . trim($orderMatch). '/', $psql, $match, PREG_OFFSET_CAPTURE);
836 $comma_pos = $match[0][1];
837 //substring between the comma and the alias to find the joined_table alias and column name
838 $col_name = substr($psql,0, $comma_pos);
840 //make sure the string does not have an end parenthesis
841 //and is not part of a function (i.e. "ISNULL(leads.last_name,'') as name" )
842 //this is especially true for unified search from home screen
845 if(strpos($psql, " as "))
846 $alias_beg_pos = strpos($psql, " as ");
848 // Bug # 44923 - This breaks the query and does not properly filter isnull
849 // as there are other functions such as ltrim and rtrim.
850 /* else if (strncasecmp($psql, 'isnull', 6) != 0)
851 $alias_beg_pos = strpos($psql, " "); */
853 if ($alias_beg_pos > 0) {
854 $col_name = substr($psql,0, $alias_beg_pos );
856 //add the "asc/desc" order back
857 $col_name = $col_name. " ". $asc_desc;
859 //pass in new order by
860 $GLOBALS['log']->debug("order by being returned is " . $col_name);
866 * Take in a string of the module and retrieve the correspondent table name
868 * @param string $module_str module name
869 * @param string $sql SQL statement
870 * @return string table name
872 private function getTableNameFromModuleName($module_str, $sql)
875 global $beanList, $beanFiles;
876 $GLOBALS['log']->debug("Module being processed is " . $module_str);
877 //get the right module files
878 //the module string exists in bean list, then process bean for correct table name
879 //note that we exempt the reports module from this, as queries from reporting module should be parsed for
880 //correct table name.
881 if (($module_str != 'Reports' && $module_str != 'SavedReport') && isset($beanList[$module_str]) && isset($beanFiles[$beanList[$module_str]])){
882 //if the class is not already loaded, then load files
883 if (!class_exists($beanList[$module_str]))
884 require_once($beanFiles[$beanList[$module_str]]);
886 //instantiate new bean
887 $module_bean = new $beanList[$module_str]();
888 //get table name from bean
889 $tbl_name = $module_bean->table_name;
890 //make sure table name is not just a blank space, or empty
891 $tbl_name = trim($tbl_name);
893 if(empty($tbl_name)){
894 $GLOBALS['log']->debug("Could not find table name for module $module_str. ");
895 $tbl_name = $module_str;
899 //since the module does NOT exist in beanlist, then we have to parse the string
900 //and grab the table name from the passed in sql
901 $GLOBALS['log']->debug("Could not find table name from module in request, retrieve from passed in sql");
902 $tbl_name = $module_str;
903 $sql = strtolower($sql);
905 // Bug #45625 : Getting Multi-part identifier (reports.id) could not be bound error when navigating to next page in reprots in mssql
906 // there is cases when sql string is multiline string and it we cannot find " from " string in it
907 $sql = str_replace(array("\n", "\r"), " ", $sql);
909 //look for the location of the "from" in sql string
910 $fromLoc = strpos($sql," from " );
912 //found from, substring from the " FROM " string in sql to end
913 $tableEnd = substr($sql, $fromLoc+6);
914 //We know that tablename will be next parameter after from, so
915 //grab the next space after table name.
916 // MFH BUG #14009: Also check to see if there are any carriage returns before the next space so that we don't grab any arbitrary joins or other tables.
917 $carriage_ret = strpos($tableEnd,"\n");
918 $next_space = strpos($tableEnd," " );
919 if ($carriage_ret < $next_space)
920 $next_space = $carriage_ret;
921 if ($next_space > 0) {
922 $tbl_name= substr($tableEnd,0, $next_space);
923 if(empty($tbl_name)){
924 $GLOBALS['log']->debug("Could not find table name sql either, return $module_str. ");
925 $tbl_name = $module_str;
929 //grab the table, to see if it is aliased
930 $aliasTableEnd = trim(substr($tableEnd, $next_space));
931 $alias_space = strpos ($aliasTableEnd, " " );
932 if ($alias_space > 0){
933 $alias_tbl_name= substr($aliasTableEnd,0, $alias_space);
934 strtolower($alias_tbl_name);
935 if(empty($alias_tbl_name)
936 || $alias_tbl_name == "where"
937 || $alias_tbl_name == "inner"
938 || $alias_tbl_name == "left"
939 || $alias_tbl_name == "join"
940 || $alias_tbl_name == "outer"
941 || $alias_tbl_name == "right") {
942 //not aliased, do nothing
944 elseif ($alias_tbl_name == "as") {
945 //the next word is the table name
946 $aliasTableEnd = trim(substr($aliasTableEnd, $alias_space));
947 $alias_space = strpos ($aliasTableEnd, " " );
948 if ($alias_space > 0) {
949 $alias_tbl_name= trim(substr($aliasTableEnd,0, $alias_space));
950 if (!empty($alias_tbl_name))
951 $tbl_name = $alias_tbl_name;
955 //this is table alias
956 $tbl_name = $alias_tbl_name;
962 $GLOBALS['log']->debug("Table name for module $module_str is: ".$tbl_name);
968 * @see DBManager::getFieldsArray()
970 public function getFieldsArray($result, $make_lower_case = false)
972 $field_array = array();
974 if(! isset($result) || empty($result))
978 while ($i < mssql_num_fields($result)) {
979 $meta = mssql_fetch_field($result, $i);
982 if($make_lower_case==true)
983 $meta->name = strtolower($meta->name);
985 $field_array[] = $meta->name;
994 * @see DBManager::getAffectedRowCount()
996 public function getAffectedRowCount()
998 return $this->getOne("SELECT @@ROWCOUNT");
1002 * @see DBManager::fetchRow()
1004 public function fetchRow($result)
1006 if (empty($result)) return false;
1008 $row = mssql_fetch_assoc($result);
1009 //MSSQL returns a space " " when a varchar column is empty ("") and not null.
1010 //We need to iterate through the returned row array and strip empty spaces
1012 foreach($row as $key => $column) {
1013 //notice we only strip if one space is returned. we do not want to strip
1014 //strings with intentional spaces (" foo ")
1015 if (!empty($column) && $column ==" ") {
1024 * @see DBManager::quote()
1026 public function quote($string)
1028 if(is_array($string)) {
1029 return $this->arrayQuote($string);
1031 return str_replace("'","''", $this->quoteInternal($string));
1035 * @see DBManager::tableExists()
1037 public function tableExists($tableName)
1039 $GLOBALS['log']->info("tableExists: $tableName");
1041 $this->checkConnection();
1042 $result = $this->getOne(
1043 "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME=".$this->quoted($tableName));
1045 return !empty($result);
1049 * Get tables like expression
1050 * @param $like string
1053 public function tablesLike($like)
1055 if ($this->getDatabase()) {
1057 $r = $this->query('SELECT TABLE_NAME tn FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=\'BASE TABLE\' AND TABLE_NAME LIKE '.$this->quoted($like));
1059 while ($a = $this->fetchByAssoc($r)) {
1060 $row = array_values($a);
1070 * @see DBManager::getTablesArray()
1072 public function getTablesArray()
1074 $GLOBALS['log']->debug('MSSQL fetching table list');
1076 if($this->getDatabase()) {
1078 $r = $this->query('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES');
1079 if (is_resource($r)) {
1080 while ($a = $this->fetchByAssoc($r))
1081 $tables[] = $a['TABLE_NAME'];
1087 return false; // no database available
1092 * This call is meant to be used during install, when Full Text Search is enabled
1093 * Indexing would always occur after a fresh sql server install, so this code creates
1094 * a catalog and table with full text index.
1096 public function full_text_indexing_setup()
1098 $GLOBALS['log']->debug('MSSQL about to wakeup FTS');
1100 if($this->getDatabase()) {
1101 //create wakeup catalog
1102 $FTSqry[] = "if not exists( select * from sys.fulltext_catalogs where name ='wakeup_catalog' )
1103 CREATE FULLTEXT CATALOG wakeup_catalog
1106 //drop wakeup table if it exists
1107 $FTSqry[] = "IF EXISTS(SELECT 'fts_wakeup' FROM sysobjects WHERE name = 'fts_wakeup' AND xtype='U')
1108 DROP TABLE fts_wakeup
1110 //create wakeup table
1111 $FTSqry[] = "CREATE TABLE fts_wakeup(
1112 id varchar(36) NOT NULL CONSTRAINT pk_fts_wakeup_id PRIMARY KEY CLUSTERED (id ASC ),
1114 kb_index int IDENTITY(1,1) NOT NULL CONSTRAINT wakeup_fts_unique_idx UNIQUE NONCLUSTERED
1117 //create full text index
1118 $FTSqry[] = "CREATE FULLTEXT INDEX ON fts_wakeup
1123 KEY INDEX wakeup_fts_unique_idx ON wakeup_catalog
1124 WITH CHANGE_TRACKING AUTO
1128 $FTSqry[] = "INSERT INTO fts_wakeup (id ,body)
1129 VALUES ('".create_guid()."', 'SugarCRM Rocks' )";
1132 //create queries to stop and restart indexing
1133 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup STOP POPULATION';
1134 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup DISABLE';
1135 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup ENABLE';
1136 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup SET CHANGE_TRACKING MANUAL';
1137 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup START FULL POPULATION';
1138 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup SET CHANGE_TRACKING AUTO';
1140 foreach($FTSqry as $q){
1144 $this->create_default_full_text_catalog();
1147 return false; // no database available
1150 protected $date_formats = array(
1157 * @see DBManager::convert()
1159 public function convert($string, $type, array $additional_parameters = array())
1161 // convert the parameters array into a comma delimited string
1162 if (!empty($additional_parameters)) {
1163 $additional_parameters_string = ','.implode(',',$additional_parameters);
1165 $additional_parameters_string = '';
1167 $all_parameters = $additional_parameters;
1168 if(is_array($string)) {
1169 $all_parameters = array_merge($string, $all_parameters);
1170 } elseif (!is_null($string)) {
1171 array_unshift($all_parameters, $string);
1174 switch (strtolower($type)) {
1178 return "LEFT($string$additional_parameters_string)";
1180 if(!empty($additional_parameters[0]) && $additional_parameters[0][0] == "'") {
1181 $additional_parameters[0] = trim($additional_parameters[0], "'");
1183 if(!empty($additional_parameters) && isset($this->date_formats[$additional_parameters[0]])) {
1184 $len = $this->date_formats[$additional_parameters[0]];
1185 return "LEFT(CONVERT(varchar($len),". $string . ",120),$len)";
1187 return "LEFT(CONVERT(varchar(10),". $string . ",120),10)";
1190 if(empty($additional_parameters_string)) {
1191 $additional_parameters_string = ",''";
1193 return "ISNULL($string$additional_parameters_string)";
1195 return implode("+",$all_parameters);
1197 return "CAST($string AS varchar(8000))";
1199 return "DATENAME(quarter, $string)";
1201 return "LEN($string)";
1203 return "MONTH($string)";
1205 return "DATEADD({$additional_parameters[1]},{$additional_parameters[0]},$string)";
1207 return "DATEADD(hh, {$additional_parameters[0]}, DATEADD(mi, {$additional_parameters[1]}, $string))";
1208 case 'add_tz_offset' :
1209 $getUserUTCOffset = $GLOBALS['timedate']->getUserUTCOffset();
1210 $operation = $getUserUTCOffset < 0 ? '-' : '+';
1211 return 'DATEADD(minute, ' . $operation . abs($getUserUTCOffset) . ', ' . $string. ')';
1218 * @see DBManager::fromConvert()
1220 public function fromConvert($string, $type)
1223 case 'datetimecombo':
1224 case 'datetime': return substr($string, 0,19);
1225 case 'date': return substr($string, 0, 10);
1226 case 'time': return substr($string, 11);
1232 * @see DBManager::createTableSQLParams()
1234 public function createTableSQLParams($tablename, $fieldDefs, $indices)
1236 if (empty($tablename) || empty($fieldDefs))
1239 $columns = $this->columnSQLRep($fieldDefs, false, $tablename);
1240 if (empty($columns))
1243 return "CREATE TABLE $tablename ($columns)";
1247 * Does this type represent text (i.e., non-varchar) value?
1248 * @param string $type
1250 public function isTextType($type)
1252 $type = strtolower($type);
1253 if(!isset($this->type_map[$type])) return false;
1254 return in_array($this->type_map[$type], array('ntext','text','image', 'nvarchar(max)'));
1258 * Return representation of an empty value depending on type
1259 * @param string $type
1261 public function emptyValue($type)
1263 $ctype = $this->getColumnType($type);
1264 if($ctype == "datetime") {
1265 return $this->convert($this->quoted("1970-01-01 00:00:00"), "datetime");
1267 if($ctype == "date") {
1268 return $this->convert($this->quoted("1970-01-01"), "datetime");
1270 if($ctype == "time") {
1271 return $this->convert($this->quoted("00:00:00"), "time");
1273 return parent::emptyValue($type);
1276 public function renameColumnSQL($tablename, $column, $newname)
1278 return "SP_RENAME '$tablename.$column', '$newname', 'COLUMN'";
1282 * Returns the SQL Alter table statment
1284 * MSSQL has a quirky T-SQL alter table syntax. Pay special attention to the
1286 * @param string $action
1288 * @param bool $ignorRequired
1289 * @param string $tablename
1291 protected function alterSQLRep($action, array $def, $ignoreRequired, $tablename)
1295 $f_def=$this->oneColumnSQLRep($def, $ignoreRequired,$tablename,false);
1296 return "ADD " . $f_def;
1299 return "DROP COLUMN " . $def['name'];
1302 //You cannot specify a default value for a column for MSSQL
1303 $f_def = $this->oneColumnSQLRep($def, $ignoreRequired,$tablename, true);
1304 $f_stmt = "ALTER COLUMN ".$f_def['name'].' '.$f_def['colType'].' '.
1305 $f_def['required'].' '.$f_def['auto_increment']."\n";
1306 if (!empty( $f_def['default']))
1307 $f_stmt .= " ALTER TABLE " . $tablename . " ADD ". $f_def['default'] . " FOR " . $def['name'];
1316 * @see DBManager::changeColumnSQL()
1318 * MSSQL uses a different syntax than MySQL for table altering that is
1319 * not quite as simplistic to implement...
1321 protected function changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired = false)
1324 $constraints = $this->get_field_default_constraint_name($tablename);
1326 if ($this->isFieldArray($fieldDefs)) {
1327 foreach ($fieldDefs as $def)
1329 //if the column is being modified drop the default value
1330 //constraint if it exists. alterSQLRep will add the constraint back
1331 if (!empty($constraints[$def['name']])) {
1332 $sql.=" ALTER TABLE " . $tablename . " DROP CONSTRAINT " . $constraints[$def['name']];
1334 //check to see if we need to drop related indexes before the alter
1335 $indices = $this->get_indices($tablename);
1336 foreach ( $indices as $index ) {
1337 if ( in_array($def['name'],$index['fields']) ) {
1338 $sql .= ' ' . $this->add_drop_constraint($tablename,$index,true).' ';
1339 $sql2 .= ' ' . $this->add_drop_constraint($tablename,$index,false).' ';
1343 $columns[] = $this->alterSQLRep($action, $def, $ignoreRequired,$tablename);
1347 //if the column is being modified drop the default value
1348 //constraint if it exists. alterSQLRep will add the constraint back
1349 if (!empty($constraints[$fieldDefs['name']])) {
1350 $sql.=" ALTER TABLE " . $tablename . " DROP CONSTRAINT " . $constraints[$fieldDefs['name']];
1352 //check to see if we need to drop related indexes before the alter
1353 $indices = $this->get_indices($tablename);
1354 foreach ( $indices as $index ) {
1355 if ( in_array($fieldDefs['name'],$index['fields']) ) {
1356 $sql .= ' ' . $this->add_drop_constraint($tablename,$index,true).' ';
1357 $sql2 .= ' ' . $this->add_drop_constraint($tablename,$index,false).' ';
1362 $columns[] = $this->alterSQLRep($action, $fieldDefs, $ignoreRequired,$tablename);
1365 $columns = implode(", ", $columns);
1366 $sql .= " ALTER TABLE $tablename $columns " . $sql2;
1371 protected function setAutoIncrement($table, $field_name)
1373 return "identity(1,1)";
1377 * @see DBManager::setAutoIncrementStart()
1379 public function setAutoIncrementStart($table, $field_name, $start_value)
1381 if($start_value > 1)
1383 $this->query("DBCC CHECKIDENT ('$table', RESEED, $start_value) WITH NO_INFOMSGS");
1388 * @see DBManager::getAutoIncrement()
1390 public function getAutoIncrement($table, $field_name)
1392 $result = $this->getOne("select IDENT_CURRENT('$table') + IDENT_INCR ( '$table' ) as 'Auto_increment'");
1397 * @see DBManager::get_indices()
1399 public function get_indices($tableName)
1401 //find all unique indexes and primary keys.
1403 SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name,
1404 sys.indexes.name as index_name, sys.indexes.is_unique, sys.indexes.is_primary_key
1405 FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
1406 WHERE (sys.tables.object_id = sys.indexes.object_id
1407 AND sys.tables.object_id = sys.index_columns.object_id
1408 AND sys.tables.object_id = sys.columns.object_id
1409 AND sys.indexes.index_id = sys.index_columns.index_id
1410 AND sys.index_columns.column_id = sys.columns.column_id)
1411 AND sys.tables.name = '$tableName'
1413 $result = $this->query($query);
1416 while (($row=$this->fetchByAssoc($result)) != null) {
1417 $index_type = 'index';
1418 if ($row['is_primary_key'] == '1')
1419 $index_type = 'primary';
1420 elseif ($row['is_unique'] == 1 )
1421 $index_type = 'unique';
1422 $name = strtolower($row['index_name']);
1423 $indices[$name]['name'] = $name;
1424 $indices[$name]['type'] = $index_type;
1425 $indices[$name]['fields'][] = strtolower($row['column_name']);
1431 * @see DBManager::get_columns()
1433 public function get_columns($tablename)
1435 //find all unique indexes and primary keys.
1436 $result = $this->query("sp_columns $tablename");
1439 while (($row=$this->fetchByAssoc($result)) !=null) {
1440 $column_name = strtolower($row['COLUMN_NAME']);
1441 $columns[$column_name]['name']=$column_name;
1442 $columns[$column_name]['type']=strtolower($row['TYPE_NAME']);
1443 if ( $row['TYPE_NAME'] == 'decimal' ) {
1444 $columns[$column_name]['len']=strtolower($row['PRECISION']);
1445 $columns[$column_name]['len'].=','.strtolower($row['SCALE']);
1447 elseif ( in_array($row['TYPE_NAME'],array('nchar','nvarchar')) )
1448 $columns[$column_name]['len']=strtolower($row['PRECISION']);
1449 elseif ( !in_array($row['TYPE_NAME'],array('datetime','text')) )
1450 $columns[$column_name]['len']=strtolower($row['LENGTH']);
1451 if ( stristr($row['TYPE_NAME'],'identity') ) {
1452 $columns[$column_name]['auto_increment'] = '1';
1453 $columns[$column_name]['type']=str_replace(' identity','',strtolower($row['TYPE_NAME']));
1456 if (!empty($row['IS_NULLABLE']) && $row['IS_NULLABLE'] == 'NO' && (empty($row['KEY']) || !stristr($row['KEY'],'PRI')))
1457 $columns[strtolower($row['COLUMN_NAME'])]['required'] = 'true';
1460 if ( strtolower($tablename) == 'relationships' ) {
1461 $column_def = $this->getOne("select cdefault from syscolumns where id = object_id('relationships') and name = '$column_name'");
1463 if ( $column_def != 0 && ($row['COLUMN_DEF'] != null)) { // NOTE Not using !empty as an empty string may be a viable default value.
1465 $row['COLUMN_DEF'] = html_entity_decode($row['COLUMN_DEF'],ENT_QUOTES);
1466 if ( preg_match('/\([\(|\'](.*)[\)|\']\)/i',$row['COLUMN_DEF'],$matches) )
1467 $columns[$column_name]['default'] = $matches[1];
1468 elseif ( preg_match('/\(N\'(.*)\'\)/i',$row['COLUMN_DEF'],$matches) )
1469 $columns[$column_name]['default'] = $matches[1];
1471 $columns[$column_name]['default'] = $row['COLUMN_DEF'];
1479 * Get FTS catalog name for current DB
1481 protected function ftsCatalogName()
1483 if(isset($this->connectOptions['db_name'])) {
1484 return $this->connectOptions['db_name']."_fts_catalog";
1486 return 'sugar_fts_catalog';
1490 * @see DBManager::add_drop_constraint()
1492 public function add_drop_constraint($table, $definition, $drop = false)
1494 $type = $definition['type'];
1495 $fields = is_array($definition['fields'])?implode(',',$definition['fields']):$definition['fields'];
1496 $name = $definition['name'];
1502 case 'alternate_key':
1504 $sql = "DROP INDEX {$name} ON {$table}";
1506 $sql = "CREATE INDEX {$name} ON {$table} ({$fields})";
1510 $sql = "DROP INDEX {$name} ON {$table}";
1512 $sql = "CREATE CLUSTERED INDEX $name ON $table ($fields)";
1514 // constraints as indices
1517 $sql = "ALTER TABLE {$table} DROP CONSTRAINT $name";
1519 $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name} UNIQUE ({$fields})";
1523 $sql = "ALTER TABLE {$table} DROP CONSTRAINT {$name}";
1525 $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name} PRIMARY KEY ({$fields})";
1529 $sql = "ALTER TABLE {$table} DROP FOREIGN KEY ({$fields})";
1531 $sql = "ALTER TABLE {$table} ADD CONSTRAINT {$name} FOREIGN KEY ({$fields}) REFERENCES {$definition['foreignTable']}({$definition['foreignFields']})";
1534 if ($this->full_text_indexing_enabled() && $drop) {
1535 $sql = "DROP FULLTEXT INDEX ON {$table}";
1536 } elseif ($this->full_text_indexing_enabled()) {
1537 $catalog_name=$this->ftsCatalogName();
1538 if ( isset($definition['catalog_name']) && $definition['catalog_name'] != 'default')
1539 $catalog_name = $definition['catalog_name'];
1541 $language = "Language 1033";
1542 if (isset($definition['language']) && !empty($definition['language']))
1543 $language = "Language " . $definition['language'];
1545 $key_index = $definition['key_index'];
1547 $change_tracking = "auto";
1548 if (isset($definition['change_tracking']) && !empty($definition['change_tracking']))
1549 $change_tracking = $definition['change_tracking'];
1551 $sql = " CREATE FULLTEXT INDEX ON $table ($fields $language) KEY INDEX $key_index ON $catalog_name WITH CHANGE_TRACKING $change_tracking" ;
1559 * Returns true if Full Text Search is installed
1563 public function full_text_indexing_installed()
1565 $ftsChckRes = $this->getOne("SELECT FULLTEXTSERVICEPROPERTY('IsFulltextInstalled') as fts");
1566 return !empty($ftsChckRes);
1570 * @see DBManager::full_text_indexing_enabled()
1572 protected function full_text_indexing_enabled($dbname = null)
1574 // check to see if we already have install setting in session
1575 if(!isset($_SESSION['IsFulltextInstalled']))
1576 $_SESSION['IsFulltextInstalled'] = $this->full_text_indexing_installed();
1578 // check to see if FTS Indexing service is installed
1579 if(empty($_SESSION['IsFulltextInstalled']))
1582 // grab the dbname if it was not passed through
1583 if (empty($dbname)) {
1584 global $sugar_config;
1585 $dbname = $sugar_config['dbconfig']['db_name'];
1587 //we already know that Indexing service is installed, now check
1588 //to see if it is enabled
1589 $res = $this->getOne("SELECT DATABASEPROPERTY('$dbname', 'IsFulltextEnabled') ftext");
1590 return !empty($res);
1594 * Creates default full text catalog
1596 protected function create_default_full_text_catalog()
1598 if ($this->full_text_indexing_enabled()) {
1599 $catalog = $this->ftsCatalogName();
1600 $GLOBALS['log']->debug("Creating the default catalog for full-text indexing, $catalog");
1602 //drop catalog if exists.
1603 $ret = $this->query("
1606 from sys.fulltext_catalogs
1607 where name ='$catalog'
1609 CREATE FULLTEXT CATALOG $catalog");
1612 $GLOBALS['log']->error("Error creating default full-text catalog, $catalog");
1618 * Function returns name of the constraint automatically generated by sql-server.
1619 * We request this for default, primary key, required
1621 * @param string $table
1622 * @param string $column
1625 private function get_field_default_constraint_name($table, $column = null)
1627 static $results = array();
1629 if ( empty($column) && isset($results[$table]) )
1630 return $results[$table];
1633 select s.name, o.name, c.name dtrt, d.name ctrt
1634 from sys.default_constraints as d
1635 join sys.objects as o
1636 on o.object_id = d.parent_object_id
1637 join sys.columns as c
1638 on c.object_id = o.object_id and c.column_id = d.parent_column_id
1639 join sys.schemas as s
1640 on s.schema_id = o.schema_id
1641 where o.name = '$table'
1643 if ( !empty($column) )
1644 $query .= " and c.name = '$column'";
1645 $res = $this->query($query);
1646 if ( !empty($column) ) {
1647 $row = $this->fetchByAssoc($res);
1649 return $row['ctrt'];
1652 $returnResult = array();
1653 while ( $row = $this->fetchByAssoc($res) )
1654 $returnResult[$row['dtrt']] = $row['ctrt'];
1655 $results[$table] = $returnResult;
1656 return $returnResult;
1663 * @see DBManager::massageFieldDef()
1665 public function massageFieldDef(&$fieldDef, $tablename)
1667 parent::massageFieldDef($fieldDef,$tablename);
1669 if ($fieldDef['type'] == 'int')
1670 $fieldDef['len'] = '4';
1672 if(empty($fieldDef['len']))
1674 switch($fieldDef['type']) {
1676 case 'bool' : $fieldDef['len'] = '1'; break;
1677 case 'smallint' : $fieldDef['len'] = '2'; break;
1678 case 'float' : $fieldDef['len'] = '8'; break;
1681 $fieldDef['len'] = $this->isTextType($fieldDef['dbType']) ? 'max' : '255';
1683 case 'image' : $fieldDef['len'] = '2147483647'; break;
1684 case 'ntext' : $fieldDef['len'] = '2147483646'; break; // Note: this is from legacy code, don't know if this is correct
1687 if($fieldDef['type'] == 'decimal'
1688 && empty($fieldDef['precision'])
1689 && !strpos($fieldDef['len'], ','))
1691 $fieldDef['len'] .= ',0'; // Adding 0 precision if it is not specified
1694 if(empty($fieldDef['default'])
1695 && in_array($fieldDef['type'],array('bit','bool')))
1697 $fieldDef['default'] = '0';
1699 if (isset($fieldDef['required']) && $fieldDef['required'] && !isset($fieldDef['default']) )
1700 $fieldDef['default'] = '';
1701 // if ($fieldDef['type'] == 'bit' && empty($fieldDef['len']) )
1702 // $fieldDef['len'] = '1';
1703 // if ($fieldDef['type'] == 'bool' && empty($fieldDef['len']) )
1704 // $fieldDef['len'] = '1';
1705 // if ($fieldDef['type'] == 'float' && empty($fieldDef['len']) )
1706 // $fieldDef['len'] = '8';
1707 // if ($fieldDef['type'] == 'varchar' && empty($fieldDef['len']) )
1708 // $fieldDef['len'] = '255';
1709 // if ($fieldDef['type'] == 'nvarchar' && empty($fieldDef['len']) )
1710 // $fieldDef['len'] = '255';
1711 // if ($fieldDef['type'] == 'image' && empty($fieldDef['len']) )
1712 // $fieldDef['len'] = '2147483647';
1713 // if ($fieldDef['type'] == 'ntext' && empty($fieldDef['len']) )
1714 // $fieldDef['len'] = '2147483646';
1715 // if ($fieldDef['type'] == 'smallint' && empty($fieldDef['len']) )
1716 // $fieldDef['len'] = '2';
1717 // if ($fieldDef['type'] == 'bit' && empty($fieldDef['default']) )
1718 // $fieldDef['default'] = '0';
1719 // if ($fieldDef['type'] == 'bool' && empty($fieldDef['default']) )
1720 // $fieldDef['default'] = '0';
1725 * @see DBManager::oneColumnSQLRep()
1727 protected function oneColumnSQLRep($fieldDef, $ignoreRequired = false, $table = '', $return_as_array = false)
1730 if(isset($fieldDef['name'])){
1731 $colType = $this->getFieldType($fieldDef);
1732 if(stristr($this->getFieldType($fieldDef), 'decimal') && isset($fieldDef['len'])){
1733 $fieldDef['len'] = min($fieldDef['len'],38);
1735 //bug: 39690 float(8) is interpreted as real and this generates a diff when doing repair
1736 if(stristr($colType, 'float') && isset($fieldDef['len']) && $fieldDef['len'] == 8){
1737 unset($fieldDef['len']);
1741 // always return as array for post-processing
1742 $ref = parent::oneColumnSQLRep($fieldDef, $ignoreRequired, $table, true);
1744 // Bug 24307 - Don't add precision for float fields.
1745 if ( stristr($ref['colType'],'float') )
1746 $ref['colType'] = preg_replace('/(,\d+)/','',$ref['colType']);
1748 if ( $return_as_array )
1751 return "{$ref['name']} {$ref['colType']} {$ref['default']} {$ref['required']} {$ref['auto_increment']}";
1755 * Saves changes to module's audit table
1757 * @param object $bean Sugarbean instance
1758 * @param array $changes changes
1760 public function save_audit_records(SugarBean $bean, $changes)
1762 //Bug 25078 fixed by Martin Hu: sqlserver haven't 'date' type, trim extra "00:00:00"
1763 if($changes['data_type'] == 'date'){
1764 $changes['before'] = str_replace(' 00:00:00','',$changes['before']);
1766 parent::save_audit_records($bean,$changes);
1770 * Disconnects from the database
1772 * Also handles any cleanup needed
1774 public function disconnect()
1776 $GLOBALS['log']->debug('Calling Mssql::disconnect()');
1777 if(!empty($this->database)){
1778 $this->freeResult();
1779 mssql_close($this->database);
1780 $this->database = null;
1785 * @see DBManager::freeDbResult()
1787 protected function freeDbResult($dbResult)
1789 if(!empty($dbResult))
1790 mssql_free_result($dbResult);
1795 * @see DBManager::lastDbError()
1797 public function lastDbError()
1799 $sqlmsg = mssql_get_last_message();
1800 if(empty($sqlmsg)) return false;
1801 global $app_strings;
1802 if (empty($app_strings)
1803 or !isset($app_strings['ERR_MSSQL_DB_CONTEXT'])
1804 or !isset($app_strings['ERR_MSSQL_WARNING']) ) {
1805 //ignore the message from sql-server if $app_strings array is empty. This will happen
1806 //only if connection if made before language is set.
1810 $sqlpos = strpos($sqlmsg, 'Changed database context to');
1811 $sqlpos2 = strpos($sqlmsg, 'Warning:');
1812 $sqlpos3 = strpos($sqlmsg, 'Checking identity information:');
1813 if ( $sqlpos !== false || $sqlpos2 !== false || $sqlpos3 !== false ) {
1816 global $app_strings;
1817 //ERR_MSSQL_DB_CONTEXT: localized version of 'Changed database context to' message
1818 if (empty($app_strings) or !isset($app_strings['ERR_MSSQL_DB_CONTEXT'])) {
1819 //ignore the message from sql-server if $app_strings array is empty. This will happen
1820 //only if connection if made before languge is set.
1821 $GLOBALS['log']->debug("Ignoring this database message: " . $sqlmsg);
1825 $sqlpos = strpos($sqlmsg, $app_strings['ERR_MSSQL_DB_CONTEXT']);
1826 if ( $sqlpos !== false )
1831 if ( strlen($sqlmsg) > 2 ) {
1832 return "SQL Server error: " . $sqlmsg;
1840 * @see DBManager::getDbInfo()
1842 public function getDbInfo()
1844 return array("version" => $this->version());
1849 * @see DBManager::validateQuery()
1851 public function validateQuery($query)
1853 if(!$this->isSelect($query)) {
1856 $this->query("SET SHOWPLAN_TEXT ON");
1857 $res = $this->getOne($query);
1858 $this->query("SET SHOWPLAN_TEXT OFF");
1859 return !empty($res);
1863 * This is a utility function to prepend the "N" character in front of SQL values that are
1864 * surrounded by single quotes.
1866 * @param $sql string SQL statement
1867 * @return string SQL statement with single quote values prepended with "N" character for nvarchar columns
1869 protected function _appendN($sql)
1871 // If there are no single quotes, don't bother, will just assume there is no character data
1872 if (strpos($sql, "'") === false)
1875 // Flag if there are odd number of single quotes, just continue without trying to append N
1876 if ((substr_count($sql, "'") & 1)) {
1877 $GLOBALS['log']->error("SQL statement[" . $sql . "] has odd number of single quotes.");
1881 //The only location of three subsequent ' will be at the beginning or end of a value.
1882 $sql = preg_replace('/(?<!\')(\'{3})(?!\')/', "'<@#@#@PAIR@#@#@>", $sql);
1884 // Remove any remaining '' and do not parse... replace later (hopefully we don't even have any)
1886 $regexp = '/(\'{2})/';
1887 $pair_matches = array();
1888 preg_match_all($regexp, $sql, $pair_matches);
1889 if ($pair_matches) {
1890 foreach (array_unique($pair_matches[0]) as $key=>$value) {
1891 $pairs['<@PAIR-'.$key.'@>'] = $value;
1893 if (!empty($pairs)) {
1894 $sql = str_replace($pairs, array_keys($pairs), $sql);
1898 $regexp = "/(N?'.+?')/is";
1900 preg_match_all($regexp, $sql, $matches);
1902 if (!empty($matches)) {
1903 foreach ($matches[0] as $value) {
1904 // We are assuming that all nvarchar columns are no more than 200 characters in length
1905 // One problem we face is the image column type in reports which cannot accept nvarchar data
1906 if (!empty($value) && !is_numeric(trim(str_replace(array("'", ","), "", $value))) && !preg_match('/^\'[\,]\'$/', $value)) {
1907 $replace[$value] = 'N' . trim($value, "N");
1912 if (!empty($replace))
1913 $sql = str_replace(array_keys($replace), $replace, $sql);
1916 $sql = str_replace(array_keys($pairs), $pairs, $sql);
1918 if(strpos($sql, "<@#@#@PAIR@#@#@>"))
1919 $sql = str_replace(array('<@#@#@PAIR@#@#@>'), array("''"), $sql);
1925 * Quote SQL Server search term
1926 * @param string $term
1929 protected function quoteTerm($term)
1931 $term = str_replace("%", "*", $term); // Mssql wildcard is *
1932 return '"'.str_replace('"', '', $term).'"';
1936 * Generate fulltext query from set of terms
1937 * @param string $fields Field to search against
1938 * @param array $terms Search terms that may be or not be in the result
1939 * @param array $must_terms Search terms that have to be in the result
1940 * @param array $exclude_terms Search terms that have to be not in the result
1942 public function getFulltextQuery($field, $terms, $must_terms = array(), $exclude_terms = array())
1944 $condition = $or_condition = array();
1945 foreach($must_terms as $term) {
1946 $condition[] = $this->quoteTerm($term);
1949 foreach($terms as $term) {
1950 $or_condition[] = $this->quoteTerm($term);
1953 if(!empty($or_condition)) {
1954 $condition[] = "(".join(" | ", $or_condition).")";
1957 foreach($exclude_terms as $term) {
1958 $condition[] = " NOT ".$this->quoteTerm($term);
1960 $condition = $this->quoted(join(" AND ",$condition));
1961 return "CONTAINS($field, $condition)";
1965 * Check if certain database exists
1966 * @param string $dbname
1968 public function dbExists($dbname)
1970 $db = $this->getOne("SELECT name FROM master..sysdatabases WHERE name = N".$this->quoted($dbname));
1976 * @param string $dbname
1978 protected function selectDb($dbname)
1980 return mssql_select_db($dbname);
1984 * Check if certain DB user exists
1985 * @param string $username
1987 public function userExists($username)
1989 $this->selectDb("master");
1990 $user = $this->getOne("select count(*) from sys.sql_logins where name =".$this->quoted($username));
1991 // FIXME: go back to the original DB
1992 return !empty($user);
1997 * @param string $database_name
1998 * @param string $host_name
1999 * @param string $user
2000 * @param string $password
2002 public function createDbUser($database_name, $host_name, $user, $password)
2004 $qpassword = $this->quote($password);
2005 $this->selectDb($database_name);
2006 $this->query("CREATE LOGIN $user WITH PASSWORD = '$qpassword'", true);
2007 $this->query("CREATE USER $user FOR LOGIN $user", true);
2008 $this->query("EXEC sp_addRoleMember 'db_ddladmin ', '$user'", true);
2009 $this->query("EXEC sp_addRoleMember 'db_datareader','$user'", true);
2010 $this->query("EXEC sp_addRoleMember 'db_datawriter','$user'", true);
2015 * @param string $dbname
2017 public function createDatabase($dbname)
2019 return $this->query("CREATE DATABASE $dbname", true);
2024 * @param string $dbname
2026 public function dropDatabase($dbname)
2028 return $this->query("DROP DATABASE $dbname", true);
2032 * Check if this driver can be used
2035 public function valid()
2037 return function_exists("mssql_connect");
2041 * Check if this DB name is valid
2043 * @param string $name
2046 public function isDatabaseNameValid($name)
2048 // No funny chars, does not begin with number
2049 return preg_match('/^[0-9#@]+|[\"\'\*\/\\?\:\\<\>\-\ \&\!\(\)\[\]\{\}\;\,\.\`\~\|\\\\]+/', $name)==0;
2052 public function installConfig()
2055 'LBL_DBCONFIG_MSG3' => array(
2056 "setup_db_database_name" => array("label" => 'LBL_DBCONF_DB_NAME', "required" => true),
2058 'LBL_DBCONFIG_MSG2' => array(
2059 "setup_db_host_name" => array("label" => 'LBL_DBCONF_HOST_NAME', "required" => true),
2060 "setup_db_host_instance" => array("label" => 'LBL_DBCONF_HOST_INSTANCE'),
2062 'LBL_DBCONF_TITLE_USER_INFO' => array(),
2063 'LBL_DBCONFIG_B_MSG1' => array(
2064 "setup_db_admin_user_name" => array("label" => 'LBL_DBCONF_DB_ADMIN_USER', "required" => true),
2065 "setup_db_admin_password" => array("label" => 'LBL_DBCONF_DB_ADMIN_PASSWORD', "type" => "password"),
2071 * Returns a DB specific FROM clause which can be used to select against functions.
2072 * Note that depending on the database that this may also be an empty string.
2075 public function getFromDummyTable()
2081 * Returns a DB specific piece of SQL which will generate GUID (UUID)
2082 * This string can be used in dynamic SQL to do multiple inserts with a single query.
2083 * I.e. generate a unique Sugar id in a sub select of an insert statement.
2087 public function getGuidSQL()