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 class MssqlManager extends DBManager
94 * @see DBManager::$dbType
96 public $dbType = 'mssql';
99 * @see DBManager::$backendFunctions
101 protected $backendFunctions = array(
102 'free_result' => 'mssql_free_result',
103 'close' => 'mssql_close',
104 'row_count' => 'mssql_num_rows'
109 * @see DBManager::connect()
111 public function connect(
112 array $configOptions = null,
116 global $sugar_config;
118 if (is_null($configOptions))
119 $configOptions = $sugar_config['dbconfig'];
121 //SET DATEFORMAT to 'YYYY-MM-DD''
122 ini_set('mssql.datetimeconvert', '0');
124 //set the text size and textlimit to max number so that blob columns are not truncated
125 ini_set('mssql.textlimit','2147483647');
126 ini_set('mssql.textsize','2147483647');
128 //set the connections parameters
130 $configOptions['db_host_instance'] = trim($configOptions['db_host_instance']);
131 if (empty($configOptions['db_host_instance']))
132 $connect_param = $configOptions['db_host_name'];
134 $connect_param = $configOptions['db_host_name']."\\".$configOptions['db_host_instance'];
136 //create persistent connection
137 if ($sugar_config['dbconfigoption']['persistent'] == true) {
138 $this->database =@mssql_pconnect(
140 $configOptions['db_user_name'],
141 $configOptions['db_password']
144 //if no persistent connection created, then create regular connection
145 if(!$this->database){
146 $this->database = mssql_connect(
148 $configOptions['db_user_name'],
149 $configOptions['db_password']
151 if(!$this->database){
152 $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name'].
153 " as ".$configOptions['db_user_name'].".");
154 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
156 if($this->database && $sugar_config['dbconfigoption']['persistent'] == true){
157 $_SESSION['administrator_error'] = "<B>Severe Performance Degradation: Persistent Database Connections "
158 . "not working. Please set \$sugar_config['dbconfigoption']['persistent'] to false in your "
159 . "config.php file</B>";
162 //make sure connection exists
163 if(!$this->database){
164 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
169 //Adding sleep and retry for mssql connection. We have come across scenarios when
170 //an error is thrown.' Unable to select database'. Following will try to connect to
171 //mssql db maximum number of 5 times at the interval of .2 second. If can not connect
172 //it will throw an Unable to select database message.
174 if(!@mssql_select_db($configOptions['db_name'], $this->database)){
178 if(@mssql_select_db($configOptions['db_name'], $this->database)){
184 $GLOBALS['log']->fatal( "Unable to select database {$configOptions['db_name']}");
185 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
189 if($this->checkError('Could Not Connect', $dieOnError))
190 $GLOBALS['log']->info("connected to db");
192 $GLOBALS['log']->info("Connect:".$this->database);
196 * @see DBManager::version()
198 public function version()
200 return $this->getOne("SELECT @@VERSION as version");
204 * @see DBManager::checkError()
206 public function checkError(
211 if (parent::checkError($msg, $dieOnError))
214 $sqlmsg = mssql_get_last_message();
216 $sqlpos = strpos($sqlmsg, 'Changed database context to');
217 $sqlpos2 = strpos($sqlmsg, 'Warning:');
218 $sqlpos3 = strpos($sqlmsg, 'Checking identity information:');
219 if ( $sqlpos !== false || $sqlpos2 !== false || $sqlpos3 !== false )
220 $sqlmsg = ''; // empty out sqlmsg if its either of the two error messages described above
223 //ERR_MSSQL_DB_CONTEXT: localized version of 'Changed database context to' message
224 if (empty($app_strings) or !isset($app_strings['ERR_MSSQL_DB_CONTEXT'])) {
225 //ignore the message from sql-server if $app_strings array is empty. This will happen
226 //only if connection if made before languge is set.
227 $GLOBALS['log']->debug("Ignoring this database message: " . $sqlmsg);
231 $sqlpos = strpos($sqlmsg, $app_strings['ERR_MSSQL_DB_CONTEXT']);
232 if ( $sqlpos !== false )
237 if ( strlen($sqlmsg) > 2 ) {
238 $GLOBALS['log']->fatal("$msg: SQL Server error: " . $sqlmsg);
246 * @see DBManager::query()
248 public function query(
255 // Flag if there are odd number of single quotes
256 if ((substr_count($sql, "'") & 1))
257 $GLOBALS['log']->error("SQL statement[" . $sql . "] has odd number of single quotes.");
259 $this->countQuery($sql);
260 $GLOBALS['log']->info('Query:' . $sql);
261 $this->checkConnection();
262 $this->query_time = microtime(true);
264 // Bug 34892 - Clear out previous error message by checking the @@ERROR global variable
265 $errorNumberHandle = mssql_query("SELECT @@ERROR",$this->database);
266 $errorNumber = array_shift(mssql_fetch_row($errorNumberHandle));
271 $result = @mssql_query($sql, $this->database);
276 // awu Bug 10657: ignoring mssql error message 'Changed database context to' - an intermittent
277 // and difficult to reproduce error. The message is only a warning, and does
278 // not affect the functionality of the query
279 $sqlmsg = mssql_get_last_message();
280 $sqlpos = strpos($sqlmsg, 'Changed database context to');
281 $sqlpos2 = strpos($sqlmsg, 'Warning:');
282 $sqlpos3 = strpos($sqlmsg, 'Checking identity information:');
284 if ($sqlpos !== false || $sqlpos2 !== false || $sqlpos3 !== false) // if sqlmsg has 'Changed database context to', just log it
285 $GLOBALS['log']->debug($sqlmsg . ": " . $sql );
287 $GLOBALS['log']->fatal($sqlmsg . ": " . $sql );
289 sugar_die('SQL Error : ' . $sqlmsg);
291 echo 'SQL Error : ' . $sqlmsg;
294 $this->lastmysqlrow = -1;
296 $this->query_time = microtime(true) - $this->query_time;
297 $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
300 $this->checkError($msg.' Query Failed: ' . $sql, $dieOnError);
306 * This function take in the sql for a union query, the start and offset,
307 * and wraps it around an "mssql friendly" limit query
310 * @param int $start record to start at
311 * @param int $count number of records to retrieve
312 * @return string SQL statement
314 private function handleUnionLimitQuery(
320 //set the start to 0, no negs
324 $GLOBALS['log']->debug(print_r(func_get_args(),true));
326 $this->lastsql = $sql;
328 //change the casing to lower for easier string comparison, and trim whitespaces
329 $sql = strtolower(trim($sql)) ;
332 $limitUnionSQL = $sql;
333 $order_by_str = 'order by';
335 //make array of order by's. substring approach was proving too inconsistent
336 $orderByArray = explode($order_by_str, $sql);
340 //count the number of array elements
341 $unionOrderByCount = count($orderByArray);
344 //process if there are elements
345 if ($unionOrderByCount){
346 //we really want the last ordery by, so reconstruct string
347 //adding a 1 to count, as we dont wish to process the last element
349 while ($unionOrderByCount>$arr_count+1) {
350 $unionsql .= $orderByArray[$arr_count];
351 $arr_count = $arr_count+1;
352 //add an "order by" string back if we are coming into loop again
353 //remember they were taken out when array was created
354 if ($unionOrderByCount>$arr_count+1) {
355 $unionsql .= "order by";
358 //grab the last order by element, set both order by's'
359 $unionOrderBy = $orderByArray[$arr_count];
360 $rowNumOrderBy = $unionOrderBy;
362 //if last element contains a "select", then this is part of the union query,
363 //and there is no order by to use
364 if (strpos($unionOrderBy, "select")) {
366 //with no guidance on what to use for required order by in rownumber function,
367 //resort to using name column.
368 $rowNumOrderBy = 'id';
373 //there are no order by elements, so just pass back string
375 //with no guidance on what to use for required order by in rownumber function,
376 //resort to using name column.
377 $rowNumOrderBy = 'id';
380 //Unions need the column name being sorted on to match acroos all queries in Union statement
381 //so we do not want to strip the alias like in other queries. Just add the "order by" string and
382 //pass column name as is
383 if ($unionOrderBy != '') {
384 $unionOrderBy = ' order by ' . $unionOrderBy;
387 //if start is 0, then just use a top query
389 $limitUnionSQL = "select top $count * from (" .$unionsql .") as top_count ".$unionOrderBy;
392 //if start is more than 0, then use top query in conjunction
393 //with rownumber() function to create limit query.
394 $limitUnionSQL = "select top $count * from( select ROW_NUMBER() OVER ( order by "
395 .$rowNumOrderBy.") AS row_number, * from ("
396 .$unionsql .") As numbered) "
397 . "As top_count_limit WHERE row_number > $start "
401 return $limitUnionSQL;
405 * @see DBManager::limitQuery()
407 public function limitQuery(
414 $start = (int)$start;
415 $count = (int)$count;
417 $distinctSQLARRAY = array();
418 if (strpos($sql, "UNION") && !preg_match("/(\')(UNION).?(\')/i", $sql))
419 $newSQL = $this->handleUnionLimitQuery($sql,$start,$count);
423 $GLOBALS['log']->debug(print_r(func_get_args(),true));
424 $this->lastsql = $sql;
426 preg_match('/^(.*SELECT )(.*?FROM.*WHERE)(.*)$/isxU',$sql, $matches);
427 if (!empty($matches[3])) {
429 $match_two = strtolower($matches[2]);
430 if (!strpos($match_two, "distinct")> 0 && strpos($match_two, "distinct") !==0) {
432 $newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];
435 $distinct_o = strpos($match_two, "distinct");
436 $up_to_distinct_str = substr($match_two, 0, $distinct_o);
437 //check to see if the distinct is within a function, if so, then proceed as normal
438 if (strpos($up_to_distinct_str,"(")) {
440 $newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];
443 //if distinct is not within a function, then parse
444 //string contains distinct clause, "TOP needs to come after Distinct"
445 //get position of distinct
446 $match_zero = strtolower($matches[0]);
447 $distinct_pos = strpos($match_zero , "distinct");
448 //get position of where
449 $where_pos = strpos($match_zero, "where");
450 //parse through string
451 $beg = substr($matches[0], 0, $distinct_pos+9 );
452 $mid = substr($matches[0], strlen($beg), ($where_pos+5) - (strlen($beg)));
453 $end = substr($matches[0], strlen($beg) + strlen($mid) );
454 //repopulate matches array
455 $matches[1] = $beg; $matches[2] = $mid; $matches[3] = $end;
457 $newSQL = $matches[1] . " TOP $count " . $matches[2] . $matches[3];
462 $orderByMatch = array();
463 preg_match('/^(.*)(ORDER BY)(.*)$/is',$matches[3], $orderByMatch);
465 //if there is a distinct clause, parse sql string as we will have to insert the rownumber
466 //for paging, AFTER the distinct clause
467 $hasDistinct = strpos(strtolower($matches[0]), "distinct");
469 $matches_sql = strtolower($matches[0]);
470 //remove reference to distinct and select keywords, as we will use a group by instead
471 //we need to use group by because we are introducing rownumber column which would make every row unique
473 //take out the select and distinct from string so we can reuse in group by
474 $dist_str = ' distinct ';
475 $distinct_pos = strpos($matches_sql, $dist_str);
476 $matches_sql = substr($matches_sql,$distinct_pos+ strlen($dist_str));
477 //get the position of where and from for further processing
478 $from_pos = strpos($matches_sql , " from ");
479 $where_pos = strpos($matches_sql, "where");
480 //split the sql into a string before and after the from clause
481 //we will use the columns being selected to construct the group by clause
483 $distinctSQLARRAY[0] = substr($matches_sql,0, $from_pos+1);
484 $distinctSQLARRAY[1] = substr($matches_sql,$from_pos+1);
485 //get position of order by (if it exists) so we can strip it from the string
486 $ob_pos = strpos($distinctSQLARRAY[1], "order by");
488 $distinctSQLARRAY[1] = substr($distinctSQLARRAY[1],0,$ob_pos);
491 // strip off last closing parathese from the where clause
492 $distinctSQLARRAY[1] = preg_replace("/\)\s$/"," ",$distinctSQLARRAY[1]);
495 //place group by string into array
496 $grpByArr = explode(',', $distinctSQLARRAY[0]);
499 //remove the aliases for each group by element, sql server doesnt like these in group by.
500 foreach ($grpByArr as $gb) {
503 //clean out the extra stuff added if we are concating first_name and last_name together
504 //this way both fields are added in correctly to the group by
505 $gb = str_replace("isnull(","",$gb);
506 $gb = str_replace("'') + ' ' + ","",$gb);
508 //remove outer reference if they exist
509 if (strpos($gb,"'")!==false){
512 //if there is a space, then an alias exists, remove alias
513 if (strpos($gb,' ')){
514 $gb = substr( $gb, 0,strpos($gb,' '));
517 //if resulting string is not empty then add to new group by string
524 $grpByStr .= ", $gb";
530 if (!empty($orderByMatch[3])) {
531 //if there is a distinct clause, form query with rownumber after distinct
533 $newSQL = "SELECT TOP $count * FROM
536 OVER (ORDER BY ".$this->returnOrderBy($sql, $orderByMatch[3]).") AS row_number,
537 count(*) counter, " . $distinctSQLARRAY[0] . "
538 " . $distinctSQLARRAY[1] . "
539 group by " . $grpByStr . "
541 WHERE row_number > $start";
544 $newSQL = "SELECT TOP $count * FROM
546 " . $matches[1] . " ROW_NUMBER()
547 OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number,
548 " . $matches[2] . $orderByMatch[1]. "
550 WHERE row_number > $start";
553 //bug: 22231 Records in campaigns' subpanel may not come from
554 //table of $_REQUEST['module']. Get it directly from query
555 $upperQuery = strtoupper($matches[2]);
556 if (!strpos($upperQuery,"JOIN")){
557 $from_pos = strpos($upperQuery , "FROM") + 4;
558 $where_pos = strpos($upperQuery, "WHERE");
559 $tablename = trim(substr($upperQuery,$from_pos, $where_pos - $from_pos));
561 $tablename = $this->getTableNameFromModuleName($_REQUEST['module'],$sql);
563 //if there is a distinct clause, form query with rownumber after distinct
565 $newSQL = "SELECT TOP $count * FROM
567 SELECT ROW_NUMBER() OVER (ORDER BY ".$tablename.".id) AS row_number, count(*) counter, " . $distinctSQLARRAY[0] . "
568 " . $distinctSQLARRAY[1] . "
569 group by " . $grpByStr . "
572 WHERE row_number > $start";
575 $newSQL = "SELECT TOP $count * FROM
577 " . $matches[1] . " ROW_NUMBER() OVER (ORDER BY ".$tablename.".id) AS row_number, " . $matches[2] . $matches[3]. "
580 WHERE row_number > $start";
587 $GLOBALS['log']->debug('Limit Query: ' . $newSQL);
588 $result = $this->query($newSQL, $dieOnError, $msg);
589 $this->dump_slow_queries($newSQL);
595 * Searches for begginning and ending characters. It places contents into
596 * an array and replaces contents in original string. This is used to account for use of
597 * nested functions while aliasing column names
599 * @param string $p_sql SQL statement
600 * @param string $strip_beg Beginning character
601 * @param string $strip_end Ending character
602 * @param string $patt Optional, pattern to
604 private function removePatternFromSQL(
610 //strip all single quotes out
613 $count = substr_count ( $p_sql, $strip_beg);
615 if ($strip_beg != $strip_end)
620 $strip_array = array();
621 while ($i<$count && $offset<strlen($p_sql)) {
622 if ($offset > strlen($p_sql))
627 $beg_sin = strpos($p_sql, $strip_beg, $offset);
632 $sec_sin = strpos($p_sql, $strip_end, $beg_sin+1);
633 $strip_array[$patt.$i] = substr($p_sql, $beg_sin, $sec_sin - $beg_sin +1);
634 if ($increment > 1) {
635 //we are in here because beginning and end patterns are not identical, so search for nesting
636 $exists = strpos($strip_array[$patt.$i], $strip_beg );
638 $nested_pos = (strrpos($strip_array[$patt.$i], $strip_beg ));
639 $strip_array[$patt.$i] = substr($p_sql,$nested_pos+$beg_sin,$sec_sin - ($nested_pos+$beg_sin)+1);
640 $p_sql = substr($p_sql, 0, $nested_pos+$beg_sin) . " ##". $patt.$i."## " . substr($p_sql, $sec_sin+1);
642 $beg_sin = $nested_pos;
646 $p_sql = substr($p_sql, 0, $beg_sin) . " ##". $patt.$i."## " . substr($p_sql, $sec_sin+1);
648 $offset = $sec_sin+1;
652 $strip_array['sql_string'] = $p_sql;
660 * @param string $token
661 * @param array $pattern_array
664 private function addPatternToSQL(
669 //strip all single quotes out
670 $pattern_array = array_reverse($pattern_array);
672 foreach ($pattern_array as $key => $replace) {
673 $token = str_replace( "##".$key."##", $replace,$token);
680 * gets an alias from the sql statement
683 * @param string $alias
686 private function getAliasFromSQL(
692 preg_match('/^(.*SELECT)(.*?FROM.*WHERE)(.*)$/isU',$sql, $matches);
693 //parse all single and double quotes out of array
694 $sin_array = $this->removePatternFromSQL($matches[2], "'", "'","sin_");
695 $new_sql = array_pop($sin_array);
696 $dub_array = $this->removePatternFromSQL($new_sql, "\"", "\"","dub_");
697 $new_sql = array_pop($dub_array);
699 //search for parenthesis
700 $paren_array = $this->removePatternFromSQL($new_sql, "(", ")", "par_");
701 $new_sql = array_pop($paren_array);
703 //all functions should be removed now, so split the array on comma's
704 $mstr_sql_array = explode(",", $new_sql);
705 foreach($mstr_sql_array as $token ) {
706 if (strpos($token, $alias)) {
707 //found token, add back comments
708 $token = $this->addPatternToSQL($token, $paren_array);
709 $token = $this->addPatternToSQL($token, $dub_array);
710 $token = $this->addPatternToSQL($token, $sin_array);
712 //log and break out of this function
721 * Finds the alias of the order by column, and then return the preceding column name
724 * @param string $orderMatch
727 private function findColumnByAlias(
732 //change case to lowercase
733 $sql = strtolower($sql);
734 $patt = '/\s+'.trim($orderMatch).'\s*,/';
736 //check for the alias, it should contain comma, may contain space, \n, or \t
738 preg_match($patt, $sql, $matches, PREG_OFFSET_CAPTURE);
739 $found_in_sql = isset($matches[0][1]) ? $matches[0][1] : false;
742 //set default for found variable
743 $found = $found_in_sql;
745 //if still no match found, then we need to parse through the string
747 //get count of how many times the match exists in string
748 $found_count = substr_count($sql, $orderMatch);
751 $len = strlen($orderMatch);
752 //loop through string as many times as there is a match
753 while ($found_count > $i) {
754 //get the first match
755 $found_in_sql = strpos($sql, $orderMatch,$first_);
756 //make sure there was a match
758 //grab the next 2 individual characters
759 $str_plusone = substr($sql,$found_in_sql + $len,1);
760 $str_plustwo = substr($sql,$found_in_sql + $len+1,1);
761 //if one of those characters is a comma, then we have our alias
762 if ($str_plusone === "," || $str_plustwo === ","){
763 //keep track of this position
764 $found = $found_in_sql;
767 //set the offset and increase the iteration counter
768 $first_ = $found_in_sql+$len;
772 //return $found, defaults have been set, so if no match was found it will be a negative number
778 * Return the order by string to use in case the column has been aliased
781 * @param string $orig_order_match
784 private function returnOrderBy(
789 $sql = strtolower($sql);
790 $orig_order_match = trim($orig_order_match);
791 if (strpos($orig_order_match, ".") != 0)
792 //this has a tablename defined, pass in the order match
793 return $orig_order_match;
795 //grab first space in order by
796 $firstSpace = strpos($orig_order_match, " ");
798 //split order by into column name and ascending/descending
799 $orderMatch = " " . strtolower(substr($orig_order_match, 0, $firstSpace));
800 $asc_desc = substr($orig_order_match,$firstSpace);
802 //look for column name as an alias in sql string
803 $found_in_sql = $this->findColumnByAlias($sql, $orderMatch);
805 if (!$found_in_sql) {
806 //check if this column needs the tablename prefixed to it
807 $orderMatch = ".".trim($orderMatch);
808 $colMatchPos = strpos($sql, $orderMatch);
809 if ($colMatchPos !== false) {
810 //grab sub string up to column name
811 $containsColStr = substr($sql,0, $colMatchPos);
812 //get position of first space, so we can grab table name
813 $lastSpacePos = strrpos($containsColStr, " ");
814 //use positions of column name, space before name, and length of column to find the correct column name
815 $col_name = substr($sql, $lastSpacePos, $colMatchPos-$lastSpacePos+strlen($orderMatch));
816 //bug 25485. When sorting by a custom field in Account List and then pressing NEXT >, system gives an error
817 $containsCommaPos = strpos($col_name, ",");
818 if($containsCommaPos !== false) {
819 $col_name = substr($col_name, $containsCommaPos+1);
824 //break out of here, log this
825 $GLOBALS['log']->debug("No match was found for order by, pass string back untouched as: $orig_order_match");
826 return $orig_order_match;
829 //if found, then parse and return
830 //grab string up to the aliased column
831 $GLOBALS['log']->debug("order by found, process sql string");
833 $psql = (trim($this->getAliasFromSQL($sql, $orderMatch )));
835 $psql = trim(substr($sql, 0, $found_in_sql));
837 //grab the last comma before the alias
838 $comma_pos = strrpos($psql, " ");
839 //substring between the comma and the alias to find the joined_table alias and column name
840 $col_name = substr($psql,0, $comma_pos);
842 //make sure the string does not have an end parenthesis
843 //and is not part of a function (i.e. "ISNULL(leads.last_name,'') as name" )
844 //this is especially true for unified search from home screen
847 if(strpos($psql, " as "))
848 $alias_beg_pos = strpos($psql, " as ");
850 // Bug # 44923 - This breaks the query and does not properly filter isnull
851 // as there are other functions such as ltrim and rtrim.
852 /* else if (strncasecmp($psql, 'isnull', 6) != 0)
853 $alias_beg_pos = strpos($psql, " "); */
855 if ($alias_beg_pos > 0) {
856 $col_name = substr($psql,0, $alias_beg_pos );
858 //add the "asc/desc" order back
859 $col_name = $col_name. " ". $asc_desc;
861 //pass in new order by
862 $GLOBALS['log']->debug("order by being returned is " . $col_name);
868 * Take in a string of the module and retrieve the correspondent table name
870 * @param string $module_str module name
871 * @param string $sql SQL statement
872 * @return string table name
874 private function getTableNameFromModuleName(
880 global $beanList, $beanFiles;
881 $GLOBALS['log']->debug("Module being processed is " . $module_str);
882 //get the right module files
883 //the module string exists in bean list, then process bean for correct table name
884 //note that we exempt the reports module from this, as queries from reporting module should be parsed for
885 //correct table name.
886 if (($module_str != 'Reports' && $module_str != 'SavedReport') && isset($beanList[$module_str]) && isset($beanFiles[$beanList[$module_str]])){
887 //if the class is not already loaded, then load files
888 if (!class_exists($beanList[$module_str]))
889 require_once($beanFiles[$beanList[$module_str]]);
891 //instantiate new bean
892 $module_bean = new $beanList[$module_str]();
893 //get table name from bean
894 $tbl_name = $module_bean->table_name;
895 //make sure table name is not just a blank space, or empty
896 $tbl_name = trim($tbl_name);
898 if(empty($tbl_name)){
899 $GLOBALS['log']->debug("Could not find table name for module $module_str. ");
900 $tbl_name = $module_str;
904 //since the module does NOT exist in beanlist, then we have to parse the string
905 //and grab the table name from the passed in sql
906 $GLOBALS['log']->debug("Could not find table name from module in request, retrieve from passed in sql");
907 $tbl_name = $module_str;
908 $sql = strtolower($sql);
910 //look for the location of the "from" in sql string
911 $fromLoc = strpos($sql," from " );
913 //found from, substring from the " FROM " string in sql to end
914 $tableEnd = substr($sql, $fromLoc+6);
915 //We know that tablename will be next parameter after from, so
916 //grab the next space after table name.
917 // 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.
918 $carriage_ret = strpos($tableEnd,"\n");
919 $next_space = strpos($tableEnd," " );
920 if ($carriage_ret < $next_space)
921 $next_space = $carriage_ret;
922 if ($next_space > 0) {
923 $tbl_name= substr($tableEnd,0, $next_space);
924 if(empty($tbl_name)){
925 $GLOBALS['log']->debug("Could not find table name sql either, return $module_str. ");
926 $tbl_name = $module_str;
930 //grab the table, to see if it is aliased
931 $aliasTableEnd = trim(substr($tableEnd, $next_space));
932 $alias_space = strpos ($aliasTableEnd, " " );
933 if ($alias_space > 0){
934 $alias_tbl_name= substr($aliasTableEnd,0, $alias_space);
935 strtolower($alias_tbl_name);
936 if(empty($alias_tbl_name)
937 || $alias_tbl_name == "where"
938 || $alias_tbl_name == "inner"
939 || $alias_tbl_name == "left"
940 || $alias_tbl_name == "join"
941 || $alias_tbl_name == "outer"
942 || $alias_tbl_name == "right") {
943 //not aliased, do nothing
945 elseif ($alias_tbl_name == "as") {
946 //the next word is the table name
947 $aliasTableEnd = trim(substr($aliasTableEnd, $alias_space));
948 $alias_space = strpos ($aliasTableEnd, " " );
949 if ($alias_space > 0) {
950 $alias_tbl_name= trim(substr($aliasTableEnd,0, $alias_space));
951 if (!empty($alias_tbl_name))
952 $tbl_name = $alias_tbl_name;
956 //this is table alias
957 $tbl_name = $alias_tbl_name;
963 $GLOBALS['log']->debug("Table name for module $module_str is: ".$tbl_name);
969 * @see DBManager::getFieldsArray()
971 public function getFieldsArray(
973 $make_lower_case = false
976 $field_array = array();
978 if(! isset($result) || empty($result))
982 while ($i < mssql_num_fields($result)) {
983 $meta = mssql_fetch_field($result, $i);
986 if($make_lower_case==true)
987 $meta->name = strtolower($meta->name);
989 $field_array[] = $meta->name;
998 * @see DBManager::getAffectedRowCount()
1000 public function getAffectedRowCount()
1002 return $this->getOne("SELECT @@ROWCOUNT");
1006 * @see DBManager::describeField()
1008 protected function describeField(
1013 global $table_descriptions;
1014 if(isset($table_descriptions[$tablename]) && isset($table_descriptions[$tablename][$name])){
1015 return $table_descriptions[$tablename][$name];
1017 $table_descriptions[$tablename] = array();
1019 $sql = sprintf( "SELECT COLUMN_NAME AS Field
1020 , DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
1021 THEN '(' + RTRIM(CAST(CHARACTER_MAXIMUM_LENGTH AS CHAR)) + ')'
1022 ELSE '' END as 'Type'
1023 , CHARACTER_MAXIMUM_LENGTH
1024 , IS_NULLABLE AS 'Null'
1025 , CASE WHEN COLUMN_DEFAULT LIKE '((0))' THEN '(''0'')' ELSE COLUMN_DEFAULT END as 'Default'
1026 FROM INFORMATION_SCHEMA.COLUMNS
1027 WHERE TABLE_NAME = '%s'",
1031 $result = $this->query($sql);
1032 while ($row = $this->fetchByAssoc($result) )
1033 $table_descriptions[$tablename][$row['Field']] = $row;
1035 if (isset($table_descriptions[$tablename][$name]))
1036 return $table_descriptions[$tablename][$name];
1044 * @see DBManager::fetchByAssoc()
1046 public function fetchByAssoc(
1055 if ($result && $rowNum < 0) {
1056 $row = mssql_fetch_assoc($result);
1057 //MSSQL returns a space " " when a varchar column is empty ("") and not null.
1058 //We need to iterate through the returned row array and strip empty spaces
1060 foreach($row as $key => $column) {
1061 //notice we only strip if one space is returned. we do not want to strip
1062 //strings with intentional spaces (" foo ")
1063 if (!empty($column) && $column ==" ") {
1069 if($encode && $this->encode&& is_array($row))
1070 return array_map('to_html', $row);
1075 if ($this->getRowCount($result) > $rowNum) {
1076 if ( $rowNum == -1 )
1078 @mssql_data_seek($result, $rowNum);
1081 $this->lastmysqlrow = $rowNum;
1082 $row = @mssql_fetch_assoc($result);
1083 if($encode && $this->encode && is_array($row))
1084 return array_map('to_html', $row);
1089 * @see DBManager::quote()
1091 public function quote(
1096 return $string = str_replace("'","''", parent::quote($string));
1100 * @see DBManager::quoteForEmail()
1102 public function quoteForEmail(
1107 return str_replace("'","''", $string);
1112 * @see DBManager::tableExists()
1114 public function tableExists(
1118 $GLOBALS['log']->info("tableExists: $tableName");
1120 $this->checkConnection();
1121 $result = $this->query(
1122 "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='".$tableName."'");
1124 $rowCount = $this->getRowCount($result);
1125 $this->freeResult($result);
1126 return ($rowCount == 0) ? false : true;
1130 * @see DBManager::addIndexes()
1132 public function addIndexes(
1138 $alters = $this->helper->indexSQL($tablename,array(),$indexes);
1140 $this->query($alters);
1146 * @see DBManager::dropIndexes()
1148 public function dropIndexes(
1155 foreach ($indexes as $index) {
1156 if ( !empty($sql) ) $sql .= ";";
1157 $name = $index['name'];
1159 unset($GLOBALS['table_descriptions'][$tablename]['indexes'][$name]);
1160 if ($index['type'] == 'primary')
1161 $sql .= "ALTER TABLE $tablename DROP CONSTRAINT $name";
1163 $sql .= "DROP INDEX $name on $tablename";
1173 * @see DBManager::checkQuery()
1175 protected function checkQuery(
1183 * @see DBManager::getTablesArray()
1185 public function getTablesArray()
1187 $GLOBALS['log']->debug('MSSQL fetching table list');
1189 if($this->getDatabase()) {
1191 $r = $this->query('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES');
1192 if (is_resource($r)) {
1193 while ($a = $this->fetchByAssoc($r))
1194 $tables[] = $a['TABLE_NAME'];
1200 return false; // no database available
1205 * This call is meant to be used during install, when Full Text Search is enabled
1206 * Indexing would always occur after a fresh sql server install, so this code creates
1207 * a catalog and table with full text index.
1209 public function wakeupFTS()
1211 $GLOBALS['log']->debug('MSSQL about to wakeup FTS');
1213 if($this->getDatabase()) {
1214 //create wakup catalog
1215 $FTSqry[] = "if not exists( select * from sys.fulltext_catalogs where name ='wakeup_catalog' )
1216 CREATE FULLTEXT CATALOG wakeup_catalog
1219 //drop wakeup table if it exists
1220 $FTSqry[] = "IF EXISTS(SELECT 'fts_wakeup' FROM sysobjects WHERE name = 'fts_wakeup' AND xtype='U')
1221 DROP TABLE fts_wakeup
1223 //create wakeup table
1224 $FTSqry[] = "CREATE TABLE fts_wakeup(
1225 id varchar(36) NOT NULL CONSTRAINT pk_fts_wakeup_id PRIMARY KEY CLUSTERED (id ASC ),
1227 kb_index int IDENTITY(1,1) NOT NULL CONSTRAINT wakeup_fts_unique_idx UNIQUE NONCLUSTERED
1230 //create full text index
1231 $FTSqry[] = "CREATE FULLTEXT INDEX ON fts_wakeup
1236 KEY INDEX wakeup_fts_unique_idx ON wakeup_catalog
1237 WITH CHANGE_TRACKING AUTO
1241 $FTSqry[] = "INSERT INTO fts_wakeup (id ,body)
1242 VALUES ('".create_guid()."', 'SugarCRM Rocks' )";
1245 //create queries to stop and restart indexing
1246 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup STOP POPULATION';
1247 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup DISABLE';
1248 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup ENABLE';
1249 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup SET CHANGE_TRACKING MANUAL';
1250 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup START FULL POPULATION';
1251 $FTSqry[] = 'ALTER FULLTEXT INDEX ON fts_wakeup SET CHANGE_TRACKING AUTO';
1253 foreach($FTSqry as $q){
1261 return false; // no database available
1265 * @see DBManager::convert()
1267 public function convert(
1270 array $additional_parameters = array(),
1271 array $additional_parameters_oracle_only = array()
1274 // convert the parameters array into a comma delimited string
1275 $additional_parameters_string = '';
1276 if (!empty($additional_parameters))
1277 $additional_parameters_string = ','.implode(',',$additional_parameters);
1280 case 'today': return "GETDATE()";
1281 case 'left': return "LEFT($string".$additional_parameters_string.")";
1283 if(!empty($additional_parameters) && in_array("'%Y-%m'", $additional_parameters))
1284 return "CONVERT(varchar(7),". $string . ",120)";
1286 return "CONVERT(varchar(10),". $string . ",120)";
1287 case 'IFNULL': return "ISNULL($string".$additional_parameters_string.")";
1288 case 'CONCAT': return "$string+".implode("+",$additional_parameters);
1289 case 'text2char': return "CAST($string AS varchar(8000))";
1296 * @see DBManager::concat()
1298 public function concat(
1305 foreach ( $fields as $index => $field )
1307 $ret = db_convert($table.".".$field,'IFNULL', array("''"));
1309 $ret .= " + ' ' + ".db_convert($table.".".$field,'IFNULL', array("''"));
1311 return empty($ret)?$ret:"LTRIM(RTRIM($ret))";
1315 * @see DBManager::fromConvert()
1317 public function fromConvert(
1322 case 'datetime': return substr($string, 0,19);
1323 case 'date': return substr($string, 0,11);
1324 case 'time': return substr($string, 11);