[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3  *
   4  * This file is part of the phpBB Forum Software package.
   5  *
   6  * @copyright (c) phpBB Limited <https://www.phpbb.com>
   7  * @license GNU General Public License, version 2 (GPL-2.0)
   8  *
   9  * For full copyright and license information, please see
  10  * the docs/CREDITS.txt file.
  11  *
  12  */
  13  
  14  /**
  15  * This is the MS SQL Server Native database abstraction layer.
  16  * PHP mssql native driver required.
  17  * @author Chris Pucci
  18  *
  19  */
  20  
  21  namespace phpbb\db\driver;
  22  
  23  class mssqlnative extends \phpbb\db\driver\mssql_base
  24  {
  25      var $m_insert_id = null;
  26      var $last_query_text = '';
  27      var $query_options = array();
  28      var $connect_error = '';
  29  
  30      /**
  31      * {@inheritDoc}
  32      */
  33  	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
  34      {
  35          // Test for driver support, to avoid suppressed fatal error
  36          if (!function_exists('sqlsrv_connect'))
  37          {
  38              $this->connect_error = 'Native MS SQL Server driver for PHP is missing or needs to be updated. Version 1.1 or later is required to install phpBB3. You can download the driver from: http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx';
  39              return $this->sql_error('');
  40          }
  41  
  42          //set up connection variables
  43          $this->persistency = $persistency;
  44          $this->user = $sqluser;
  45          $this->dbname = $database;
  46          $port_delimiter = (defined('PHP_OS') && substr(PHP_OS, 0, 3) === 'WIN') ? ',' : ':';
  47          $this->server = $sqlserver . (($port) ? $port_delimiter . $port : '');
  48  
  49          //connect to database
  50          $this->db_connect_id = sqlsrv_connect($this->server, array(
  51              'Database' => $this->dbname,
  52              'UID' => $this->user,
  53              'PWD' => $sqlpassword
  54          ));
  55  
  56          return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
  57      }
  58  
  59      /**
  60      * {@inheritDoc}
  61      */
  62  	function sql_server_info($raw = false, $use_cache = true)
  63      {
  64          global $cache;
  65  
  66          if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('mssql_version')) === false)
  67          {
  68              $arr_server_info = sqlsrv_server_info($this->db_connect_id);
  69              $this->sql_server_version = $arr_server_info['SQLServerVersion'];
  70  
  71              if (!empty($cache) && $use_cache)
  72              {
  73                  $cache->put('mssql_version', $this->sql_server_version);
  74              }
  75          }
  76  
  77          if ($raw)
  78          {
  79              return $this->sql_server_version;
  80          }
  81  
  82          return ($this->sql_server_version) ? 'MSSQL<br />' . $this->sql_server_version : 'MSSQL';
  83      }
  84  
  85      /**
  86      * {@inheritDoc}
  87      */
  88  	function sql_buffer_nested_transactions()
  89      {
  90          return true;
  91      }
  92  
  93      /**
  94      * SQL Transaction
  95      * @access private
  96      */
  97  	function _sql_transaction($status = 'begin')
  98      {
  99          switch ($status)
 100          {
 101              case 'begin':
 102                  return sqlsrv_begin_transaction($this->db_connect_id);
 103              break;
 104  
 105              case 'commit':
 106                  return sqlsrv_commit($this->db_connect_id);
 107              break;
 108  
 109              case 'rollback':
 110                  return sqlsrv_rollback($this->db_connect_id);
 111              break;
 112          }
 113          return true;
 114      }
 115  
 116      /**
 117      * {@inheritDoc}
 118      */
 119  	function sql_query($query = '', $cache_ttl = 0)
 120      {
 121          if ($query != '')
 122          {
 123              global $cache;
 124  
 125              // EXPLAIN only in extra debug mode
 126              if (defined('DEBUG'))
 127              {
 128                  $this->sql_report('start', $query);
 129              }
 130              else if (defined('PHPBB_DISPLAY_LOAD_TIME'))
 131              {
 132                  $this->curtime = microtime(true);
 133              }
 134  
 135              $this->last_query_text = $query;
 136              $this->query_result = ($cache && $cache_ttl) ? $cache->sql_load($query) : false;
 137              $this->sql_add_num_queries($this->query_result);
 138  
 139              if ($this->query_result === false)
 140              {
 141                  if (($this->query_result = @sqlsrv_query($this->db_connect_id, $query, array(), $this->query_options)) === false)
 142                  {
 143                      $this->sql_error($query);
 144                  }
 145                  // reset options for next query
 146                  $this->query_options = array();
 147  
 148                  if (defined('DEBUG'))
 149                  {
 150                      $this->sql_report('stop', $query);
 151                  }
 152                  else if (defined('PHPBB_DISPLAY_LOAD_TIME'))
 153                  {
 154                      $this->sql_time += microtime(true) - $this->curtime;
 155                  }
 156  
 157                  if ($cache && $cache_ttl)
 158                  {
 159                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 160                      $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl);
 161                  }
 162                  else if (strpos($query, 'SELECT') === 0 && $this->query_result)
 163                  {
 164                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 165                  }
 166              }
 167              else if (defined('DEBUG'))
 168              {
 169                  $this->sql_report('fromcache', $query);
 170              }
 171          }
 172          else
 173          {
 174              return false;
 175          }
 176          return $this->query_result;
 177      }
 178  
 179      /**
 180      * Build LIMIT query
 181      */
 182  	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 183      {
 184          $this->query_result = false;
 185  
 186          // total == 0 means all results - not zero results
 187          if ($offset == 0 && $total !== 0)
 188          {
 189              if (strpos($query, "SELECT") === false)
 190              {
 191                  $query = "TOP {$total} " . $query;
 192              }
 193              else
 194              {
 195                  $query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP '.$total, $query);
 196              }
 197          }
 198          else if ($offset > 0)
 199          {
 200              $query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(10000000) ', $query);
 201              $query = 'SELECT *
 202                      FROM (SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3
 203                      FROM (SELECT 1 AS line2, sub1.* FROM (' . $query . ') AS sub1) as sub2) AS sub3';
 204  
 205              if ($total > 0)
 206              {
 207                  $query .= ' WHERE line3 BETWEEN ' . ($offset+1) . ' AND ' . ($offset + $total);
 208              }
 209              else
 210              {
 211                  $query .= ' WHERE line3 > ' . $offset;
 212              }
 213          }
 214  
 215          $result = $this->sql_query($query, $cache_ttl);
 216  
 217          return $result;
 218      }
 219  
 220      /**
 221      * {@inheritDoc}
 222      */
 223  	function sql_affectedrows()
 224      {
 225          return ($this->db_connect_id) ? @sqlsrv_rows_affected($this->query_result) : false;
 226      }
 227  
 228      /**
 229      * {@inheritDoc}
 230      */
 231  	function sql_fetchrow($query_id = false)
 232      {
 233          global $cache;
 234  
 235          if ($query_id === false)
 236          {
 237              $query_id = $this->query_result;
 238          }
 239  
 240          if ($cache && $cache->sql_exists($query_id))
 241          {
 242              return $cache->sql_fetchrow($query_id);
 243          }
 244  
 245          if ($query_id === false)
 246          {
 247              return false;
 248          }
 249  
 250          $row = @sqlsrv_fetch_array($query_id, SQLSRV_FETCH_ASSOC);
 251  
 252          if ($row)
 253          {
 254              foreach ($row as $key => $value)
 255              {
 256                  $row[$key] = ($value === ' ' || $value === null) ? '' : $value;
 257              }
 258  
 259              // remove helper values from LIMIT queries
 260              if (isset($row['line2']))
 261              {
 262                  unset($row['line2'], $row['line3']);
 263              }
 264          }
 265          return (sizeof($row)) ? $row : false;
 266      }
 267  
 268      /**
 269      * {@inheritDoc}
 270      */
 271  	function sql_nextid()
 272      {
 273          $result_id = @sqlsrv_query($this->db_connect_id, 'SELECT @@IDENTITY');
 274  
 275          if ($result_id !== false)
 276          {
 277              $row = @sqlsrv_fetch_array($result_id);
 278              $id = $row[0];
 279              @sqlsrv_free_stmt($result_id);
 280              return $id;
 281          }
 282          else
 283          {
 284              return false;
 285          }
 286      }
 287  
 288      /**
 289      * {@inheritDoc}
 290      */
 291  	function sql_freeresult($query_id = false)
 292      {
 293          global $cache;
 294  
 295          if ($query_id === false)
 296          {
 297              $query_id = $this->query_result;
 298          }
 299  
 300          if ($cache && !is_object($query_id) && $cache->sql_exists($query_id))
 301          {
 302              return $cache->sql_freeresult($query_id);
 303          }
 304  
 305          if (isset($this->open_queries[(int) $query_id]))
 306          {
 307              unset($this->open_queries[(int) $query_id]);
 308              return @sqlsrv_free_stmt($query_id);
 309          }
 310  
 311          return false;
 312      }
 313  
 314      /**
 315      * return sql error array
 316      * @access private
 317      */
 318  	function _sql_error()
 319      {
 320          if (function_exists('sqlsrv_errors'))
 321          {
 322              $errors = @sqlsrv_errors(SQLSRV_ERR_ERRORS);
 323              $error_message = '';
 324              $code = 0;
 325  
 326              if ($errors != null)
 327              {
 328                  foreach ($errors as $error)
 329                  {
 330                      $error_message .= "SQLSTATE: " . $error['SQLSTATE'] . "\n";
 331                      $error_message .= "code: " . $error['code'] . "\n";
 332                      $code = $error['code'];
 333                      $error_message .= "message: " . $error['message'] . "\n";
 334                  }
 335                  $this->last_error_result = $error_message;
 336                  $error = $this->last_error_result;
 337              }
 338              else
 339              {
 340                  $error = (isset($this->last_error_result) && $this->last_error_result) ? $this->last_error_result : array();
 341              }
 342  
 343              $error = array(
 344                  'message'    => $error,
 345                  'code'        => $code,
 346              );
 347          }
 348          else
 349          {
 350              $error = array(
 351                  'message'    => $this->connect_error,
 352                  'code'        => '',
 353              );
 354          }
 355  
 356          return $error;
 357      }
 358  
 359      /**
 360      * Close sql connection
 361      * @access private
 362      */
 363  	function _sql_close()
 364      {
 365          return @sqlsrv_close($this->db_connect_id);
 366      }
 367  
 368      /**
 369      * Build db-specific report
 370      * @access private
 371      */
 372  	function _sql_report($mode, $query = '')
 373      {
 374          switch ($mode)
 375          {
 376              case 'start':
 377                  $html_table = false;
 378                  @sqlsrv_query($this->db_connect_id, 'SET SHOWPLAN_TEXT ON;');
 379                  if ($result = @sqlsrv_query($this->db_connect_id, $query))
 380                  {
 381                      @sqlsrv_next_result($result);
 382                      while ($row = @sqlsrv_fetch_array($result))
 383                      {
 384                          $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 385                      }
 386                  }
 387                  @sqlsrv_query($this->db_connect_id, 'SET SHOWPLAN_TEXT OFF;');
 388                  @sqlsrv_free_stmt($result);
 389  
 390                  if ($html_table)
 391                  {
 392                      $this->html_hold .= '</table>';
 393                  }
 394              break;
 395  
 396              case 'fromcache':
 397                  $endtime = explode(' ', microtime());
 398                  $endtime = $endtime[0] + $endtime[1];
 399  
 400                  $result = @sqlsrv_query($this->db_connect_id, $query);
 401                  while ($void = @sqlsrv_fetch_array($result))
 402                  {
 403                      // Take the time spent on parsing rows into account
 404                  }
 405                  @sqlsrv_free_stmt($result);
 406  
 407                  $splittime = explode(' ', microtime());
 408                  $splittime = $splittime[0] + $splittime[1];
 409  
 410                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 411  
 412              break;
 413          }
 414      }
 415  
 416      /**
 417      * Utility method used to retrieve number of rows
 418      * Emulates mysql_num_rows
 419      * Used in acp_database.php -> write_data_mssqlnative()
 420      * Requires a static or keyset cursor to be definde via
 421      * mssqlnative_set_query_options()
 422      */
 423  	function mssqlnative_num_rows($res)
 424      {
 425          if ($res !== false)
 426          {
 427              return sqlsrv_num_rows($res);
 428          }
 429          else
 430          {
 431              return false;
 432          }
 433      }
 434  
 435      /**
 436      * Allows setting mssqlnative specific query options passed to sqlsrv_query as 4th parameter.
 437      */
 438  	function mssqlnative_set_query_options($options)
 439      {
 440          $this->query_options = $options;
 441      }
 442  }