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