[Summary view] [Print] [Text view]

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