[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3  *
   4  * @package dbal
   5  * @version $Id$
   6  * @copyright (c) 2005 phpBB Group
   7  * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8  *
   9  */
  10  
  11  /**
  12  * @ignore
  13  */
  14  if (!defined('IN_PHPBB'))
  15  {
  16      exit;
  17  }
  18  
  19  include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
  20  
  21  /**
  22  * MSSQL Database Abstraction Layer
  23  * Minimum Requirement is MSSQL 2000+
  24  * @package dbal
  25  */
  26  class dbal_mssql extends dbal
  27  {
  28      var $connect_error = '';
  29  
  30      /**
  31      * Connect to server
  32      */
  33  	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
  34      {
  35          if (!function_exists('mssql_connect'))
  36          {
  37              $this->connect_error = 'mssql_connect function does not exist, is mssql extension installed?';
  38              return $this->sql_error('');
  39          }
  40  
  41          $this->persistency = $persistency;
  42          $this->user = $sqluser;
  43          $this->dbname = $database;
  44  
  45          $port_delimiter = (defined('PHP_OS') && substr(PHP_OS, 0, 3) === 'WIN') ? ',' : ':';
  46          $this->server = $sqlserver . (($port) ? $port_delimiter . $port : '');
  47  
  48          @ini_set('mssql.charset', 'UTF-8');
  49          @ini_set('mssql.textlimit', 2147483647);
  50          @ini_set('mssql.textsize', 2147483647);
  51  
  52          if (version_compare(PHP_VERSION, '5.1.0', '>=') || (version_compare(PHP_VERSION, '5.0.0-dev', '<=') && version_compare(PHP_VERSION, '4.4.1', '>=')))
  53          {
  54              $this->db_connect_id = ($this->persistency) ? @mssql_pconnect($this->server, $this->user, $sqlpassword, $new_link) : @mssql_connect($this->server, $this->user, $sqlpassword, $new_link);
  55          }
  56          else
  57          {
  58              $this->db_connect_id = ($this->persistency) ? @mssql_pconnect($this->server, $this->user, $sqlpassword) : @mssql_connect($this->server, $this->user, $sqlpassword);
  59          }
  60  
  61          if ($this->db_connect_id && $this->dbname != '')
  62          {
  63              if (!@mssql_select_db($this->dbname, $this->db_connect_id))
  64              {
  65                  @mssql_close($this->db_connect_id);
  66                  return false;
  67              }
  68          }
  69  
  70          return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
  71      }
  72  
  73      /**
  74      * Version information about used database
  75      * @param bool $raw if true, only return the fetched sql_server_version
  76      * @param bool $use_cache If true, it is safe to retrieve the value from the cache
  77      * @return string sql server version
  78      */
  79  	function sql_server_info($raw = false, $use_cache = true)
  80      {
  81          global $cache;
  82  
  83          if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('mssql_version')) === false)
  84          {
  85              $result_id = @mssql_query("SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')", $this->db_connect_id);
  86  
  87              $row = false;
  88              if ($result_id)
  89              {
  90                  $row = @mssql_fetch_assoc($result_id);
  91                  @mssql_free_result($result_id);
  92              }
  93  
  94              $this->sql_server_version = ($row) ? trim(implode(' ', $row)) : 0;
  95  
  96              if (!empty($cache) && $use_cache)
  97              {
  98                  $cache->put('mssql_version', $this->sql_server_version);
  99              }
 100          }
 101  
 102          if ($raw)
 103          {
 104              return $this->sql_server_version;
 105          }
 106  
 107          return ($this->sql_server_version) ? 'MSSQL<br />' . $this->sql_server_version : 'MSSQL';
 108      }
 109  
 110      /**
 111      * SQL Transaction
 112      * @access private
 113      */
 114  	function _sql_transaction($status = 'begin')
 115      {
 116          switch ($status)
 117          {
 118              case 'begin':
 119                  return @mssql_query('BEGIN TRANSACTION', $this->db_connect_id);
 120              break;
 121  
 122              case 'commit':
 123                  return @mssql_query('COMMIT TRANSACTION', $this->db_connect_id);
 124              break;
 125  
 126              case 'rollback':
 127                  return @mssql_query('ROLLBACK TRANSACTION', $this->db_connect_id);
 128              break;
 129          }
 130  
 131          return true;
 132      }
 133  
 134      /**
 135      * Base query method
 136      *
 137      * @param    string    $query        Contains the SQL query which shall be executed
 138      * @param    int        $cache_ttl    Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
 139      * @return    mixed                When casted to bool the returned value returns true on success and false on failure
 140      *
 141      * @access    public
 142      */
 143  	function sql_query($query = '', $cache_ttl = 0)
 144      {
 145          if ($query != '')
 146          {
 147              global $cache;
 148  
 149              // EXPLAIN only in extra debug mode
 150              if (defined('DEBUG_EXTRA'))
 151              {
 152                  $this->sql_report('start', $query);
 153              }
 154  
 155              $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
 156              $this->sql_add_num_queries($this->query_result);
 157  
 158              if ($this->query_result === false)
 159              {
 160                  if (($this->query_result = @mssql_query($query, $this->db_connect_id)) === false)
 161                  {
 162                      $this->sql_error($query);
 163                  }
 164  
 165                  if (defined('DEBUG_EXTRA'))
 166                  {
 167                      $this->sql_report('stop', $query);
 168                  }
 169  
 170                  if ($cache_ttl && method_exists($cache, 'sql_save'))
 171                  {
 172                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 173                      $cache->sql_save($query, $this->query_result, $cache_ttl);
 174                  }
 175                  else if (strpos($query, 'SELECT') === 0 && $this->query_result)
 176                  {
 177                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 178                  }
 179              }
 180              else if (defined('DEBUG_EXTRA'))
 181              {
 182                  $this->sql_report('fromcache', $query);
 183              }
 184          }
 185          else
 186          {
 187              return false;
 188          }
 189  
 190          return $this->query_result;
 191      }
 192  
 193      /**
 194      * Build LIMIT query
 195      */
 196  	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 197      {
 198          $this->query_result = false;
 199  
 200          // Since TOP is only returning a set number of rows we won't need it if total is set to 0 (return all rows)
 201          if ($total)
 202          {
 203              // We need to grab the total number of rows + the offset number of rows to get the correct result
 204              if (strpos($query, 'SELECT DISTINCT') === 0)
 205              {
 206                  $query = 'SELECT DISTINCT TOP ' . ($total + $offset) . ' ' . substr($query, 15);
 207              }
 208              else
 209              {
 210                  $query = 'SELECT TOP ' . ($total + $offset) . ' ' . substr($query, 6);
 211              }
 212          }
 213  
 214          $result = $this->sql_query($query, $cache_ttl);
 215  
 216          // Seek by $offset rows
 217          if ($offset)
 218          {
 219              $this->sql_rowseek($offset, $result);
 220          }
 221  
 222          return $result;
 223      }
 224  
 225      /**
 226      * Return number of affected rows
 227      */
 228  	function sql_affectedrows()
 229      {
 230          return ($this->db_connect_id) ? @mssql_rows_affected($this->db_connect_id) : false;
 231      }
 232  
 233      /**
 234      * Fetch current row
 235      */
 236  	function sql_fetchrow($query_id = false)
 237      {
 238          global $cache;
 239  
 240          if ($query_id === false)
 241          {
 242              $query_id = $this->query_result;
 243          }
 244  
 245          if (isset($cache->sql_rowset[$query_id]))
 246          {
 247              return $cache->sql_fetchrow($query_id);
 248          }
 249  
 250          if ($query_id === false)
 251          {
 252              return false;
 253          }
 254  
 255          $row = @mssql_fetch_assoc($query_id);
 256  
 257          // I hope i am able to remove this later... hopefully only a PHP or MSSQL bug
 258          if ($row)
 259          {
 260              foreach ($row as $key => $value)
 261              {
 262                  $row[$key] = ($value === ' ' || $value === NULL) ? '' : $value;
 263              }
 264          }
 265  
 266          return $row;
 267      }
 268  
 269      /**
 270      * Seek to given row number
 271      * rownum is zero-based
 272      */
 273  	function sql_rowseek($rownum, &$query_id)
 274      {
 275          global $cache;
 276  
 277          if ($query_id === false)
 278          {
 279              $query_id = $this->query_result;
 280          }
 281  
 282          if (isset($cache->sql_rowset[$query_id]))
 283          {
 284              return $cache->sql_rowseek($rownum, $query_id);
 285          }
 286  
 287          return ($query_id !== false) ? @mssql_data_seek($query_id, $rownum) : false;
 288      }
 289  
 290      /**
 291      * Get last inserted id after insert statement
 292      */
 293  	function sql_nextid()
 294      {
 295          $result_id = @mssql_query('SELECT SCOPE_IDENTITY()', $this->db_connect_id);
 296          if ($result_id)
 297          {
 298              if ($row = @mssql_fetch_assoc($result_id))
 299              {
 300                  @mssql_free_result($result_id);
 301                  return $row['computed'];
 302              }
 303              @mssql_free_result($result_id);
 304          }
 305  
 306          return false;
 307      }
 308  
 309      /**
 310      * Free sql result
 311      */
 312  	function sql_freeresult($query_id = false)
 313      {
 314          global $cache;
 315  
 316          if ($query_id === false)
 317          {
 318              $query_id = $this->query_result;
 319          }
 320  
 321          if (isset($cache->sql_rowset[$query_id]))
 322          {
 323              return $cache->sql_freeresult($query_id);
 324          }
 325  
 326          if (isset($this->open_queries[$query_id]))
 327          {
 328              unset($this->open_queries[$query_id]);
 329              return @mssql_free_result($query_id);
 330          }
 331  
 332          return false;
 333      }
 334  
 335      /**
 336      * Escape string used in sql query
 337      */
 338  	function sql_escape($msg)
 339      {
 340          return str_replace(array("'", "\0"), array("''", ''), $msg);
 341      }
 342  
 343      /**
 344      * {@inheritDoc}
 345      */
 346  	function sql_lower_text($column_name)
 347      {
 348          return "LOWER(SUBSTRING($column_name, 1, DATALENGTH($column_name)))";
 349      }
 350  
 351      /**
 352      * Build LIKE expression
 353      * @access private
 354      */
 355  	function _sql_like_expression($expression)
 356      {
 357          return $expression . " ESCAPE '\\'";
 358      }
 359  
 360      /**
 361      * return sql error array
 362      * @access private
 363      */
 364  	function _sql_error()
 365      {
 366          if (function_exists('mssql_get_last_message'))
 367          {
 368              $error = array(
 369                  'message'    => @mssql_get_last_message(),
 370                  'code'        => '',
 371              );
 372  
 373              // Get error code number
 374              $result_id = @mssql_query('SELECT @@ERROR as code', $this->db_connect_id);
 375              if ($result_id)
 376              {
 377                  $row = @mssql_fetch_assoc($result_id);
 378                  $error['code'] = $row['code'];
 379                  @mssql_free_result($result_id);
 380              }
 381  
 382              // Get full error message if possible
 383              $sql = 'SELECT CAST(description as varchar(255)) as message
 384                  FROM master.dbo.sysmessages
 385                  WHERE error = ' . $error['code'];
 386              $result_id = @mssql_query($sql);
 387  
 388              if ($result_id)
 389              {
 390                  $row = @mssql_fetch_assoc($result_id);
 391                  if (!empty($row['message']))
 392                  {
 393                      $error['message'] .= '<br />' . $row['message'];
 394                  }
 395                  @mssql_free_result($result_id);
 396              }
 397          }
 398          else
 399          {
 400              $error = array(
 401                  'message'    => $this->connect_error,
 402                  'code'        => '',
 403              );
 404          }
 405  
 406          return $error;
 407      }
 408  
 409      /**
 410      * Build db-specific query data
 411      * @access private
 412      */
 413  	function _sql_custom_build($stage, $data)
 414      {
 415          return $data;
 416      }
 417  
 418      /**
 419      * Close sql connection
 420      * @access private
 421      */
 422  	function _sql_close()
 423      {
 424          return @mssql_close($this->db_connect_id);
 425      }
 426  
 427      /**
 428      * Build db-specific report
 429      * @access private
 430      */
 431  	function _sql_report($mode, $query = '')
 432      {
 433          switch ($mode)
 434          {
 435              case 'start':
 436                  $html_table = false;
 437                  @mssql_query('SET SHOWPLAN_TEXT ON;', $this->db_connect_id);
 438                  if ($result = @mssql_query($query, $this->db_connect_id))
 439                  {
 440                      @mssql_next_result($result);
 441                      while ($row = @mssql_fetch_row($result))
 442                      {
 443                          $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 444                      }
 445                  }
 446                  @mssql_query('SET SHOWPLAN_TEXT OFF;', $this->db_connect_id);
 447                  @mssql_free_result($result);
 448  
 449                  if ($html_table)
 450                  {
 451                      $this->html_hold .= '</table>';
 452                  }
 453              break;
 454  
 455              case 'fromcache':
 456                  $endtime = explode(' ', microtime());
 457                  $endtime = $endtime[0] + $endtime[1];
 458  
 459                  $result = @mssql_query($query, $this->db_connect_id);
 460                  while ($void = @mssql_fetch_assoc($result))
 461                  {
 462                      // Take the time spent on parsing rows into account
 463                  }
 464                  @mssql_free_result($result);
 465  
 466                  $splittime = explode(' ', microtime());
 467                  $splittime = $splittime[0] + $splittime[1];
 468  
 469                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 470  
 471              break;
 472          }
 473      }
 474  }
 475  
 476  ?>