include/database/SqlsrvManager.php

   1 <?php
   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-2012 SugarCRM Inc.
   6  *
   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.
  13  *
  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
  17  * details.
  18  *
  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
  22  * 02110-1301 USA.
  23  *
  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.
  26  *
  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.
  30  *
  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  ********************************************************************************/
  37
  38 /*********************************************************************************
  39
  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.
  44 *
  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.
  47 *
  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
  55 *
  56 * The field definition is an array with the following keys:
  57 *
  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:
  60 *               int
  61 *               long
  62 *               varchar
  63 *               text
  64 *               date
  65 *               datetime
  66 *               double
  67 *               float
  68 *               uint
  69 *               ulong
  70 *               time
  71 *               short
  72 *               enum
  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.
  84 *
  85 *
  86 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
  87 * All Rights Reserved.
  88 * Contributor(s): ______________________________________..
  89 ********************************************************************************/
  90
  91 include_once('include/database/MssqlManager.php');
  92
  93 /**
  94  * SQL Server (sqlsrv) manager
  95  */
  96 class SqlsrvManager extends MssqlManager
  97 {
  98     public $dbName = 'SQL Server';
  99     public $variant = 'sqlsrv';
 100     public $priority = 10;
 101     public $label = 'LBL_MSSQL_SQLSRV';
 102
 103     protected $capabilities = array(
 104         "affected_rows" => true,
 105         'fulltext' => true,
 106         'limit_subquery' => true,
 107         'create_user' => true,
 108         "create_db" => true,
 109     );
 110
 111     protected $type_map = array(
 112             'int'      => 'int',
 113             'double'   => 'float',
 114             'float'    => 'float',
 115             'uint'     => 'int',
 116             'ulong'    => 'int',
 117             'long'     => 'bigint',
 118             'short'    => 'smallint',
 119             'varchar'  => 'nvarchar',
 120             'text'     => 'nvarchar(max)',
 121             'longtext' => 'nvarchar(max)',
 122             'date'     => 'datetime',
 123             'enum'     => 'nvarchar',
 124             'relate'   => 'nvarchar',
 125             'multienum'=> 'nvarchar(max)',
 126             'html'     => 'nvarchar(max)',
 127             'longhtml' => 'nvarchar(max)',
 128             'datetime' => 'datetime',
 129             'datetimecombo' => 'datetime',
 130             'time'     => 'datetime',
 131             'bool'     => 'bit',
 132             'tinyint'  => 'tinyint',
 133             'char'     => 'char',
 134             'blob'     => 'nvarchar(max)',
 135             'longblob' => 'nvarchar(max)',
 136             'currency' => 'decimal(26,6)',
 137             'decimal'  => 'decimal',
 138             'decimal2' => 'decimal',
 139             'id'       => 'varchar(36)',
 140             'url'      => 'nvarchar',
 141             'encrypt'  => 'nvarchar',
 142             'file'     => 'nvarchar',
 143                 'decimal_tpl' => 'decimal(%d, %d)',
 144     );
 145
 146         /**
 147      * @see DBManager::connect()
 148      */
 149     public function connect(array $configOptions = null, $dieOnError = false)
 150     {
 151         global $sugar_config;
 152
 153         if (is_null($configOptions))
 154             $configOptions = $sugar_config['dbconfig'];
 155
 156         //set the connections parameters
 157         $connect_param = '';
 158         $configOptions['db_host_instance'] = trim($configOptions['db_host_instance']);
 159         if (empty($configOptions['db_host_instance']))
 160             $connect_param = $configOptions['db_host_name'];
 161         else
 162             $connect_param = $configOptions['db_host_name']."\\".$configOptions['db_host_instance'];
 163
 164         /*
 165          * Don't try to specifically use a persistent connection
 166          * since the driver will handle that for us
 167          */
 168         $options = array(
 169                     "UID" => $configOptions['db_user_name'],
 170                     "PWD" => $configOptions['db_password'],
 171                     "CharacterSet" => "UTF-8",
 172                     "ReturnDatesAsStrings" => true,
 173                     "MultipleActiveResultSets" => true,
 174                     );
 175         if(!empty($configOptions['db_name'])) {
 176             $options["Database"] = $configOptions['db_name'];
 177         }
 178         $this->database = sqlsrv_connect($connect_param, $options);
 179         if(empty($this->database)) {
 180             $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].".");
 181             if($dieOnError) {
 182                     if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
 183                         sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
 184                     } else {
 185                         sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
 186                     }
 187             } else {
 188                 return false;
 189             }
 190         }
 191
 192         if($this->checkError('Could Not Connect:', $dieOnError))
 193             $GLOBALS['log']->info("connected to db");
 194
 195         sqlsrv_query($this->database, 'SET DATEFORMAT mdy');
 196
 197         $this->connectOptions = $configOptions;
 198
 199         $GLOBALS['log']->info("Connect:".$this->database);
 200         return true;
 201     }
 202
 203         /**
 204      * @see DBManager::query()
 205          */
 206         public function query($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false)
 207     {
 208         if(is_array($sql)) {
 209             return $this->queryArray($sql, $dieOnError, $msg, $suppress);
 210         }
 211         $sql = $this->_appendN($sql);
 212
 213         $this->countQuery($sql);
 214         $GLOBALS['log']->info('Query:' . $sql);
 215         $this->checkConnection();
 216         $this->query_time = microtime(true);
 217
 218         $result = $suppress?@sqlsrv_query($this->database, $sql):sqlsrv_query($this->database, $sql);
 219
 220         $this->query_time = microtime(true) - $this->query_time;
 221         $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
 222
 223
 224         $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
 225
 226         //suppress non error messages
 227         sqlsrv_configure('WarningsReturnAsErrors',false);
 228
 229         return $result;
 230     }
 231
 232         /**
 233      * @see DBManager::getFieldsArray()
 234      */
 235         public function getFieldsArray($result, $make_lower_case = false)
 236         {
 237         $field_array = array();
 238
 239         if ( !$result ) {
 240                 return false;
 241         }
 242
 243         foreach ( sqlsrv_field_metadata($result) as $fieldMetadata ) {
 244             $key = $fieldMetadata['Name'];
 245             if($make_lower_case==true)
 246                 $key = strtolower($key);
 247
 248             $field_array[] = $key;
 249         }
 250
 251         return $field_array;
 252         }
 253
 254         /**
 255          * @see DBManager::fetchRow()
 256          */
 257         public function fetchRow($result)
 258         {
 259                 if (empty($result))     return false;
 260
 261             $row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC);
 262         if (empty($row)) {
 263             return false;
 264         }
 265
 266         foreach($row as $key => $column) {
 267             // MSSQL returns a space " " when a varchar column is empty ("") and not null.
 268             // We need to strip empty spaces
 269             // notice we only strip if one space is returned.  we do not want to strip
 270             // strings with intentional spaces (" foo ")
 271             if (!empty($column) && $column == " ") {
 272                 $row[$key] = '';
 273             }
 274         }
 275
 276         return $row;
 277         }
 278
 279     /**
 280      * @see DBManager::convert()
 281      */
 282     public function convert($string, $type, array $additional_parameters = array())
 283     {
 284         if ( $type == 'datetime')
 285         // see http://msdn.microsoft.com/en-us/library/ms187928.aspx for details
 286             return "CONVERT(datetime,$string,120)";
 287         else
 288             return parent::convert($string, $type, $additional_parameters);
 289     }
 290
 291         /**
 292      * Compares two vardefs. Overriding 39098  due to bug: 39098 . IN 6.0 we changed the id columns to dbType = 'id'
 293      * for example emails_beans.  In 554 the field email_id was nvarchar but in 6.0 since it id dbType = 'id' we would want to alter
 294      * it to varchar. This code will prevent it.
 295      *
 296      * @param  array  $fielddef1
 297      * @param  array  $fielddef2
 298      * @return bool   true if they match, false if they don't
 299      */
 300     public function compareVarDefs($fielddef1,$fielddef2)
 301     {
 302         if((isset($fielddef2['dbType']) && $fielddef2['dbType'] == 'id') || preg_match('/(_id$|^id$)/', $fielddef2['name'])){
 303             if(isset($fielddef1['type']) && isset($fielddef2['type'])){
 304                 $fielddef2['type'] = $fielddef1['type'];
 305             }
 306         }
 307         return parent::compareVarDefs($fielddef1, $fielddef2);
 308     }
 309
 310     /**
 311      * Disconnects from the database
 312      *
 313      * Also handles any cleanup needed
 314      */
 315     public function disconnect()
 316     {
 317         $GLOBALS['log']->debug('Calling Mssql::disconnect()');
 318         if(!empty($this->database)){
 319             $this->freeResult();
 320             sqlsrv_close($this->database);
 321             $this->database = null;
 322         }
 323     }
 324
 325     /**
 326      * @see DBManager::freeDbResult()
 327      */
 328     protected function freeDbResult($dbResult)
 329     {
 330         if(!empty($dbResult))
 331             sqlsrv_free_stmt($dbResult);
 332     }
 333
 334
 335         /**
 336          * Detect if no clustered index has been created for a table; if none created then just pick the first index and make it that
 337          *
 338          * @see MssqlHelper::indexSQL()
 339      */
 340     public function getConstraintSql($indices, $table)
 341     {
 342         if ( $this->doesTableHaveAClusteredIndexDefined($table) ) {
 343             return parent::getConstraintSql($indices, $table);
 344         }
 345
 346         // check to see if one of the passed in indices is a primary one; if so we can bail as well
 347         foreach ( $indices as $index ) {
 348             if ( $index['type'] == 'primary' ) {
 349                 return parent::getConstraintSql($indices, $table);
 350             }
 351         }
 352
 353         // Change the first index listed to be a clustered one instead ( so we have at least one for the table )
 354         if ( isset($indices[0]) ) {
 355             $indices[0]['type'] = 'clustered';
 356         }
 357
 358         return parent::getConstraintSql($indices, $table);
 359     }
 360
 361     /**
 362      * @see DBManager::get_columns()
 363      */
 364     public function get_columns($tablename)
 365     {
 366         //find all unique indexes and primary keys.
 367         $result = $this->query("sp_columns_90 $tablename");
 368
 369         $columns = array();
 370         while (($row=$this->fetchByAssoc($result)) !=null) {
 371             $column_name = strtolower($row['COLUMN_NAME']);
 372             $columns[$column_name]['name']=$column_name;
 373             $columns[$column_name]['type']=strtolower($row['TYPE_NAME']);
 374             if ( $row['TYPE_NAME'] == 'decimal' ) {
 375                 $columns[$column_name]['len']=strtolower($row['PRECISION']);
 376                 $columns[$column_name]['len'].=','.strtolower($row['SCALE']);
 377             }
 378                         elseif ( in_array($row['TYPE_NAME'],array('nchar','nvarchar')) ) {
 379                                 $columns[$column_name]['len']=strtolower($row['PRECISION']);
 380                                 if ( $row['TYPE_NAME'] == 'nvarchar' && $row['PRECISION'] == '0' ) {
 381                                     $columns[$column_name]['len']='max';
 382                                 }
 383                         }
 384             elseif ( !in_array($row['TYPE_NAME'],array('datetime','text')) ) {
 385                 $columns[$column_name]['len']=strtolower($row['LENGTH']);
 386             }
 387             if ( stristr($row['TYPE_NAME'],'identity') ) {
 388                 $columns[$column_name]['auto_increment'] = '1';
 389                 $columns[$column_name]['type']=str_replace(' identity','',strtolower($row['TYPE_NAME']));
 390             }
 391
 392             if (!empty($row['IS_NULLABLE']) && $row['IS_NULLABLE'] == 'NO' && (empty($row['KEY']) || !stristr($row['KEY'],'PRI')))
 393                 $columns[strtolower($row['COLUMN_NAME'])]['required'] = 'true';
 394
 395             $column_def = 1;
 396             if ( strtolower($tablename) == 'relationships' ) {
 397                 $column_def = $this->getOne("select cdefault from syscolumns where id = object_id('relationships') and name = '$column_name'");
 398             }
 399             if ( $column_def != 0 && ($row['COLUMN_DEF'] != null)) {    // NOTE Not using !empty as an empty string may be a viable default value.
 400                 $matches = array();
 401                 $row['COLUMN_DEF'] = html_entity_decode($row['COLUMN_DEF'],ENT_QUOTES);
 402                 if ( preg_match('/\([\(|\'](.*)[\)|\']\)/i',$row['COLUMN_DEF'],$matches) )
 403                     $columns[$column_name]['default'] = $matches[1];
 404                 elseif ( preg_match('/\(N\'(.*)\'\)/i',$row['COLUMN_DEF'],$matches) )
 405                     $columns[$column_name]['default'] = $matches[1];
 406                 else
 407                     $columns[$column_name]['default'] = $row['COLUMN_DEF'];
 408             }
 409         }
 410         return $columns;
 411     }
 412
 413     /**
 414      * protected function to return true if the given tablename has any clustered indexes defined.
 415      *
 416      * @param  string $tableName
 417      * @return bool
 418      */
 419     protected function doesTableHaveAClusteredIndexDefined($tableName)
 420     {
 421         $query = <<<EOSQL
 422 SELECT IST.TABLE_NAME
 423     FROM INFORMATION_SCHEMA.TABLES IST
 424     WHERE objectProperty(object_id(IST.TABLE_NAME), 'IsUserTable') = 1
 425         AND objectProperty(object_id(IST.TABLE_NAME), 'TableHasClustIndex') = 1
 426         AND IST.TABLE_NAME = '{$tableName}'
 427 EOSQL;
 428
 429         $result = $this->getOne($query);
 430         if ( !$result ) {
 431             return false;
 432         }
 433
 434         return true;
 435     }
 436
 437     /**
 438      * protected function to return true if the given tablename has any fulltext indexes defined.
 439      *
 440      * @param  string $tableName
 441      * @return bool
 442      */
 443     protected function doesTableHaveAFulltextIndexDefined($tableName)
 444     {
 445         $query = <<<EOSQL
 446 SELECT 1
 447     FROM sys.fulltext_indexes i
 448         JOIN sys.objects o ON i.object_id = o.object_id
 449     WHERE o.name = '{$tableName}'
 450 EOSQL;
 451
 452         $result = $this->getOne($query);
 453         if ( !$result ) {
 454             return false;
 455         }
 456
 457         return true;
 458     }
 459
 460     /**
 461      * Override method to add support for detecting and dropping fulltext indices.
 462      *
 463      * @see DBManager::changeColumnSQL()
 464      * @see MssqlHelper::changeColumnSQL()
 465      */
 466     protected function changeColumnSQL($tablename,$fieldDefs, $action, $ignoreRequired = false)
 467     {
 468         $sql = '';
 469         if ( $action == 'drop' && $this->doesTableHaveAFulltextIndexDefined($tablename) ) {
 470             $sql .= "DROP FULLTEXT INDEX ON {$tablename}";
 471         }
 472
 473         $sql .= parent::changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired);
 474
 475         return $sql;
 476     }
 477
 478     /**
 479      * Truncate table
 480      * @param  $name
 481      * @return string
 482      */
 483     public function truncateTableSQL($name)
 484     {
 485         return "TRUNCATE TABLE $name";
 486     }
 487
 488         /**
 489          * (non-PHPdoc)
 490          * @see DBManager::lastDbError()
 491          */
 492     public function lastDbError()
 493     {
 494         $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
 495         if(empty($errors)) return false;
 496         global $app_strings;
 497         if (empty($app_strings)
 498                     or !isset($app_strings['ERR_MSSQL_DB_CONTEXT'])
 499                         or !isset($app_strings['ERR_MSSQL_WARNING']) ) {
 500         //ignore the message from sql-server if $app_strings array is empty. This will happen
 501         //only if connection if made before languge is set.
 502                     return false;
 503         }
 504         $messages = array();
 505         foreach($errors as $error) {
 506             $sqlmsg = $error['message'];
 507             $sqlpos = strpos($sqlmsg, 'Changed database context to');
 508             $sqlpos2 = strpos($sqlmsg, 'Warning:');
 509             $sqlpos3 = strpos($sqlmsg, 'Checking identity information:');
 510             if ( $sqlpos !== false || $sqlpos2 !== false || $sqlpos3 !== false ) {
 511                 continue;
 512             }
 513             $sqlpos = strpos($sqlmsg, $app_strings['ERR_MSSQL_DB_CONTEXT']);
 514             $sqlpos2 = strpos($sqlmsg, $app_strings['ERR_MSSQL_WARNING']);
 515                 if ( $sqlpos !== false || $sqlpos2 !== false) {
 516                     continue;
 517             }
 518             $messages[] = $sqlmsg;
 519         }
 520
 521         if(!empty($messages)) {
 522             return join("\n", $messages);
 523         }
 524         return false;
 525     }
 526
 527     /**
 528      * (non-PHPdoc)
 529      * @see DBManager::getDbInfo()
 530      * @return array
 531      */
 532     public function getDbInfo()
 533     {
 534         $info = array_merge(sqlsrv_client_info(), sqlsrv_server_info());
 535         return $info;
 536     }
 537
 538     /**
 539      * Execute data manipulation statement, then roll it back
 540      * @param  $type
 541      * @param  $table
 542      * @param  $query
 543      * @return string
 544      */
 545     protected function verifyGenericQueryRollback($type, $table, $query)
 546     {
 547         $this->log->debug("verifying $type statement");
 548         if(!sqlsrv_begin_transaction($this->database)) {
 549             return "Failed to create transaction";
 550         }
 551         $this->query($query, false);
 552         $error = $this->lastError();
 553         sqlsrv_rollback($this->database);
 554         return $error;
 555     }
 556
 557     /**
 558      * Tests an INSERT INTO query
 559      * @param string table The table name to get DDL
 560      * @param string query The query to test.
 561      * @return string Non-empty if error found
 562      */
 563     public function verifyInsertInto($table, $query)
 564     {
 565         return $this->verifyGenericQueryRollback("INSERT", $table, $query);
 566     }
 567
 568     /**
 569      * Tests an UPDATE query
 570      * @param string table The table name to get DDL
 571      * @param string query The query to test.
 572      * @return string Non-empty if error found
 573      */
 574     public function verifyUpdate($table, $query)
 575     {
 576         return $this->verifyGenericQueryRollback("UPDATE", $table, $query);
 577     }
 578
 579     /**
 580      * Tests an DELETE FROM query
 581      * @param string table The table name to get DDL
 582      * @param string query The query to test.
 583      * @return string Non-empty if error found
 584      */
 585     public function verifyDeleteFrom($table, $query)
 586     {
 587         return $this->verifyGenericQueryRollback("DELETE", $table, $query);
 588     }
 589
 590     /**
 591      * Select database
 592      * @param string $dbname
 593      */
 594     protected function selectDb($dbname)
 595     {
 596         return $this->query("USE ".$this->quoted($dbname));
 597     }
 598
 599     /**
 600      * Check if this driver can be used
 601      * @return bool
 602      */
 603     public function valid()
 604     {
 605         return function_exists("sqlsrv_connect");
 606     }
 607 }