[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3  *
   4  * @package dbal
   5  * @version $Id$
   6  * @copyright (c) 2010 phpBB Group
   7  * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8  *
   9  * This is the MS SQL Server Native database abstraction layer.
  10  * PHP mssql native driver required.
  11  * @author Chris Pucci
  12  *
  13  */
  14  
  15  /**
  16  * @ignore
  17  */
  18  if (!defined('IN_PHPBB'))
  19  {
  20      exit;
  21  }
  22  
  23  include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
  24  
  25  /**
  26   * Prior to version 1.1 the SQL Server Native PHP driver didn't support sqlsrv_num_rows, or cursor based seeking so we recall all rows into an array
  27   * and maintain our own cursor index into that array.
  28   */
  29  class result_mssqlnative
  30  {
  31  	public function result_mssqlnative($queryresult = false)
  32      {
  33          $this->m_cursor = 0;
  34          $this->m_rows = array();
  35          $this->m_num_fields = sqlsrv_num_fields($queryresult);
  36          $this->m_field_meta = sqlsrv_field_metadata($queryresult);
  37  
  38          while ($row = sqlsrv_fetch_array($queryresult, SQLSRV_FETCH_ASSOC))
  39          {
  40              if ($row !== null)
  41              {
  42                  foreach($row as $k => $v)
  43                  {
  44                      if (is_object($v) && method_exists($v, 'format'))
  45                      {
  46                          $row[$k] = $v->format("Y-m-d\TH:i:s\Z");
  47                      }
  48                  }
  49                  $this->m_rows[] = $row;//read results into memory, cursors are not supported
  50              }
  51          }
  52  
  53          $this->m_row_count = sizeof($this->m_rows);
  54      }
  55  
  56  	private function array_to_obj($array, &$obj)
  57      {
  58          foreach ($array as $key => $value)
  59          {
  60              if (is_array($value))
  61              {
  62                  $obj->$key = new stdClass();
  63                  array_to_obj($value, $obj->$key);
  64              }
  65              else
  66              {
  67                  $obj->$key = $value;
  68              }
  69          }
  70          return $obj;
  71      }
  72  
  73  	public function fetch($mode = SQLSRV_FETCH_BOTH, $object_class = 'stdClass')
  74      {
  75          if ($this->m_cursor >= $this->m_row_count || $this->m_row_count == 0)
  76          {
  77              return false;
  78          }
  79  
  80          $ret = false;
  81          $arr_num = array();
  82  
  83          if ($mode == SQLSRV_FETCH_NUMERIC || $mode == SQLSRV_FETCH_BOTH)
  84          {
  85              foreach($this->m_rows[$this->m_cursor] as $key => $value)
  86              {
  87                  $arr_num[] = $value;
  88              }
  89          }
  90  
  91          switch ($mode)
  92          {
  93              case SQLSRV_FETCH_ASSOC:
  94                  $ret = $this->m_rows[$this->m_cursor];
  95              break;
  96              case SQLSRV_FETCH_NUMERIC:
  97                  $ret = $arr_num;
  98              break;
  99              case 'OBJECT':
 100                  $ret = $this->array_to_obj($this->m_rows[$this->m_cursor], $o = new $object_class);
 101              break;
 102              case SQLSRV_FETCH_BOTH:
 103              default:
 104                  $ret = $this->m_rows[$this->m_cursor] + $arr_num;
 105              break;
 106          }
 107          $this->m_cursor++;
 108          return $ret;
 109      }
 110  
 111  	public function get($pos, $fld)
 112      {
 113          return $this->m_rows[$pos][$fld];
 114      }
 115  
 116  	public function num_rows()
 117      {
 118          return $this->m_row_count;
 119      }
 120  
 121  	public function seek($iRow)
 122      {
 123          $this->m_cursor = min($iRow, $this->m_row_count);
 124      }
 125  
 126  	public function num_fields()
 127      {
 128          return $this->m_num_fields;
 129      }
 130  
 131  	public function field_name($nr)
 132      {
 133          $arr_keys = array_keys($this->m_rows[0]);
 134          return $arr_keys[$nr];
 135      }
 136  
 137  	public function field_type($nr)
 138      {
 139          $i = 0;
 140          $int_type = -1;
 141          $str_type = '';
 142  
 143          foreach ($this->m_field_meta as $meta)
 144          {
 145              if ($nr == $i)
 146              {
 147                  $int_type = $meta['Type'];
 148                  break;
 149              }
 150              $i++;
 151          }
 152  
 153          //http://msdn.microsoft.com/en-us/library/cc296183.aspx contains type table
 154          switch ($int_type)
 155          {
 156              case SQLSRV_SQLTYPE_BIGINT:         $str_type = 'bigint'; break;
 157              case SQLSRV_SQLTYPE_BINARY:         $str_type = 'binary'; break;
 158              case SQLSRV_SQLTYPE_BIT:             $str_type = 'bit'; break;
 159              case SQLSRV_SQLTYPE_CHAR:             $str_type = 'char'; break;
 160              case SQLSRV_SQLTYPE_DATETIME:         $str_type = 'datetime'; break;
 161              case SQLSRV_SQLTYPE_DECIMAL/*($precision, $scale)*/: $str_type = 'decimal'; break;
 162              case SQLSRV_SQLTYPE_FLOAT:             $str_type = 'float'; break;
 163              case SQLSRV_SQLTYPE_IMAGE:             $str_type = 'image'; break;
 164              case SQLSRV_SQLTYPE_INT:             $str_type = 'int'; break;
 165              case SQLSRV_SQLTYPE_MONEY:             $str_type = 'money'; break;
 166              case SQLSRV_SQLTYPE_NCHAR/*($charCount)*/: $str_type = 'nchar'; break;
 167              case SQLSRV_SQLTYPE_NUMERIC/*($precision, $scale)*/: $str_type = 'numeric'; break;
 168              case SQLSRV_SQLTYPE_NVARCHAR/*($charCount)*/: $str_type = 'nvarchar'; break;
 169              case SQLSRV_SQLTYPE_NTEXT:             $str_type = 'ntext'; break;
 170              case SQLSRV_SQLTYPE_REAL:             $str_type = 'real'; break;
 171              case SQLSRV_SQLTYPE_SMALLDATETIME:     $str_type = 'smalldatetime'; break;
 172              case SQLSRV_SQLTYPE_SMALLINT:         $str_type = 'smallint'; break;
 173              case SQLSRV_SQLTYPE_SMALLMONEY:     $str_type = 'smallmoney'; break;
 174              case SQLSRV_SQLTYPE_TEXT:             $str_type = 'text'; break;
 175              case SQLSRV_SQLTYPE_TIMESTAMP:         $str_type = 'timestamp'; break;
 176              case SQLSRV_SQLTYPE_TINYINT:         $str_type = 'tinyint'; break;
 177              case SQLSRV_SQLTYPE_UNIQUEIDENTIFIER: $str_type = 'uniqueidentifier'; break;
 178              case SQLSRV_SQLTYPE_UDT:             $str_type = 'UDT'; break;
 179              case SQLSRV_SQLTYPE_VARBINARY/*($byteCount)*/: $str_type = 'varbinary'; break;
 180              case SQLSRV_SQLTYPE_VARCHAR/*($charCount)*/: $str_type = 'varchar'; break;
 181              case SQLSRV_SQLTYPE_XML:             $str_type = 'xml'; break;
 182              default: $str_type = $int_type;
 183          }
 184          return $str_type;
 185      }
 186  
 187  	public function free()
 188      {
 189          unset($this->m_rows);
 190          return;
 191      }
 192  }
 193  
 194  /**
 195  * @package dbal
 196  */
 197  class dbal_mssqlnative extends dbal
 198  {
 199      var $m_insert_id = NULL;
 200      var $last_query_text = '';
 201      var $query_options = array();
 202      var $connect_error = '';
 203  
 204      /**
 205      * Connect to server
 206      */
 207  	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
 208      {
 209          // Test for driver support, to avoid suppressed fatal error
 210          if (!function_exists('sqlsrv_connect'))
 211          {
 212              $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';
 213              return $this->sql_error('');
 214          }
 215  
 216          //set up connection variables
 217          $this->persistency = $persistency;
 218          $this->user = $sqluser;
 219          $this->dbname = $database;
 220          $port_delimiter = (defined('PHP_OS') && substr(PHP_OS, 0, 3) === 'WIN') ? ',' : ':';
 221          $this->server = $sqlserver . (($port) ? $port_delimiter . $port : '');
 222  
 223          //connect to database
 224          $this->db_connect_id = sqlsrv_connect($this->server, array(
 225              'Database' => $this->dbname,
 226              'UID' => $this->user,
 227              'PWD' => $sqlpassword
 228          ));
 229  
 230          return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
 231      }
 232  
 233      /**
 234      * Version information about used database
 235      * @param bool $raw if true, only return the fetched sql_server_version
 236      * @param bool $use_cache If true, it is safe to retrieve the value from the cache
 237      * @return string sql server version
 238      */
 239  	function sql_server_info($raw = false, $use_cache = true)
 240      {
 241          global $cache;
 242  
 243          if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('mssql_version')) === false)
 244          {
 245              $arr_server_info = sqlsrv_server_info($this->db_connect_id);
 246              $this->sql_server_version = $arr_server_info['SQLServerVersion'];
 247  
 248              if (!empty($cache) && $use_cache)
 249              {
 250                  $cache->put('mssql_version', $this->sql_server_version);
 251              }
 252          }
 253  
 254          if ($raw)
 255          {
 256              return $this->sql_server_version;
 257          }
 258  
 259          return ($this->sql_server_version) ? 'MSSQL<br />' . $this->sql_server_version : 'MSSQL';
 260      }
 261  
 262      /**
 263      * {@inheritDoc}
 264      */
 265  	function sql_buffer_nested_transactions()
 266      {
 267          return true;
 268      }
 269  
 270      /**
 271      * SQL Transaction
 272      * @access private
 273      */
 274  	function _sql_transaction($status = 'begin')
 275      {
 276          switch ($status)
 277          {
 278              case 'begin':
 279                  return sqlsrv_begin_transaction($this->db_connect_id);
 280              break;
 281  
 282              case 'commit':
 283                  return sqlsrv_commit($this->db_connect_id);
 284              break;
 285  
 286              case 'rollback':
 287                  return sqlsrv_rollback($this->db_connect_id);
 288              break;
 289          }
 290          return true;
 291      }
 292  
 293      /**
 294      * Base query method
 295      *
 296      * @param    string    $query        Contains the SQL query which shall be executed
 297      * @param    int        $cache_ttl    Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
 298      * @return    mixed                When casted to bool the returned value returns true on success and false on failure
 299      *
 300      * @access    public
 301      */
 302  	function sql_query($query = '', $cache_ttl = 0)
 303      {
 304          if ($query != '')
 305          {
 306              global $cache;
 307  
 308              // EXPLAIN only in extra debug mode
 309              if (defined('DEBUG_EXTRA'))
 310              {
 311                  $this->sql_report('start', $query);
 312              }
 313  
 314              $this->last_query_text = $query;
 315              $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
 316              $this->sql_add_num_queries($this->query_result);
 317  
 318              if ($this->query_result === false)
 319              {
 320                  if (($this->query_result = @sqlsrv_query($this->db_connect_id, $query, array(), $this->query_options)) === false)
 321                  {
 322                      $this->sql_error($query);
 323                  }
 324                  // reset options for next query
 325                  $this->query_options = array();
 326  
 327                  if (defined('DEBUG_EXTRA'))
 328                  {
 329                      $this->sql_report('stop', $query);
 330                  }
 331  
 332                  if ($cache_ttl && method_exists($cache, 'sql_save'))
 333                  {
 334                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 335                      $cache->sql_save($query, $this->query_result, $cache_ttl);
 336                  }
 337                  else if (strpos($query, 'SELECT') === 0 && $this->query_result)
 338                  {
 339                      $this->open_queries[(int) $this->query_result] = $this->query_result;
 340                  }
 341              }
 342              else if (defined('DEBUG_EXTRA'))
 343              {
 344                  $this->sql_report('fromcache', $query);
 345              }
 346          }
 347          else
 348          {
 349              return false;
 350          }
 351          return $this->query_result;
 352      }
 353  
 354      /**
 355      * Build LIMIT query
 356      */
 357  	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 358      {
 359          $this->query_result = false;
 360  
 361          // total == 0 means all results - not zero results
 362          if ($offset == 0 && $total !== 0)
 363          {
 364              if (strpos($query, "SELECT") === false)
 365              {
 366                  $query = "TOP {$total} " . $query;
 367              }
 368              else
 369              {
 370                  $query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP '.$total, $query);
 371              }
 372          }
 373          else if ($offset > 0)
 374          {
 375              $query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(10000000) ', $query);
 376              $query = 'SELECT *
 377                      FROM (SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3
 378                      FROM (SELECT 1 AS line2, sub1.* FROM (' . $query . ') AS sub1) as sub2) AS sub3';
 379  
 380              if ($total > 0)
 381              {
 382                  $query .= ' WHERE line3 BETWEEN ' . ($offset+1) . ' AND ' . ($offset + $total);
 383              }
 384              else
 385              {
 386                  $query .= ' WHERE line3 > ' . $offset;
 387              }
 388          }
 389  
 390          $result = $this->sql_query($query, $cache_ttl);
 391  
 392          return $result;
 393      }
 394  
 395      /**
 396      * Return number of affected rows
 397      */
 398  	function sql_affectedrows()
 399      {
 400          return (!empty($this->query_result)) ? @sqlsrv_rows_affected($this->query_result) : false;
 401      }
 402  
 403      /**
 404      * Fetch current row
 405      */
 406  	function sql_fetchrow($query_id = false)
 407      {
 408          global $cache;
 409  
 410          if ($query_id === false)
 411          {
 412              $query_id = $this->query_result;
 413          }
 414  
 415          if (isset($cache->sql_rowset[$query_id]))
 416          {
 417              return $cache->sql_fetchrow($query_id);
 418          }
 419  
 420          if ($query_id === false)
 421          {
 422              return false;
 423          }
 424  
 425          $row = @sqlsrv_fetch_array($query_id, SQLSRV_FETCH_ASSOC);
 426  
 427          if ($row)
 428          {
 429              foreach ($row as $key => $value)
 430              {
 431                  $row[$key] = ($value === ' ' || $value === NULL) ? '' : $value;
 432              }
 433  
 434              // remove helper values from LIMIT queries
 435              if (isset($row['line2']))
 436              {
 437                  unset($row['line2'], $row['line3']);
 438              }
 439          }
 440          return (sizeof($row)) ? $row : false;
 441      }
 442  
 443      /**
 444      * Get last inserted id after insert statement
 445      */
 446  	function sql_nextid()
 447      {
 448          $result_id = @sqlsrv_query($this->db_connect_id, 'SELECT @@IDENTITY');
 449  
 450          if ($result_id !== false)
 451          {
 452              $row = @sqlsrv_fetch_array($result_id);
 453              $id = $row[0];
 454              @sqlsrv_free_stmt($result_id);
 455              return $id;
 456          }
 457          else
 458          {
 459              return false;
 460          }
 461      }
 462  
 463      /**
 464      * Free sql result
 465      */
 466  	function sql_freeresult($query_id = false)
 467      {
 468          global $cache;
 469  
 470          if ($query_id === false)
 471          {
 472              $query_id = $this->query_result;
 473          }
 474  
 475          if (isset($cache->sql_rowset[$query_id]))
 476          {
 477              return $cache->sql_freeresult($query_id);
 478          }
 479  
 480          if (isset($this->open_queries[$query_id]))
 481          {
 482              unset($this->open_queries[$query_id]);
 483              return @sqlsrv_free_stmt($query_id);
 484          }
 485          return false;
 486      }
 487  
 488      /**
 489      * Escape string used in sql query
 490      */
 491  	function sql_escape($msg)
 492      {
 493          return str_replace(array("'", "\0"), array("''", ''), $msg);
 494      }
 495  
 496      /**
 497      * {@inheritDoc}
 498      */
 499  	function sql_lower_text($column_name)
 500      {
 501          return "LOWER(SUBSTRING($column_name, 1, DATALENGTH($column_name)))";
 502      }
 503  
 504      /**
 505      * Build LIKE expression
 506      * @access private
 507      */
 508  	function _sql_like_expression($expression)
 509      {
 510          return $expression . " ESCAPE '\\'";
 511      }
 512  
 513      /**
 514      * return sql error array
 515      * @access private
 516      */
 517  	function _sql_error()
 518      {
 519          if (function_exists('sqlsrv_errors'))
 520          {
 521              $errors = @sqlsrv_errors(SQLSRV_ERR_ERRORS);
 522              $error_message = '';
 523              $code = 0;
 524  
 525              if ($errors != null)
 526              {
 527                  foreach ($errors as $error)
 528                  {
 529                      $error_message .= "SQLSTATE: " . $error[ 'SQLSTATE'] . "\n";
 530                      $error_message .= "code: " . $error[ 'code'] . "\n";
 531                      $code = $error['code'];
 532                      $error_message .= "message: " . $error[ 'message'] . "\n";
 533                  }
 534                  $this->last_error_result = $error_message;
 535                  $error = $this->last_error_result;
 536              }
 537              else
 538              {
 539                  $error = (isset($this->last_error_result) && $this->last_error_result) ? $this->last_error_result : array();
 540              }
 541  
 542              $error = array(
 543                  'message'    => $error,
 544                  'code'        => $code,
 545              );
 546          }
 547          else
 548          {
 549              $error = array(
 550                  'message'    => $this->connect_error,
 551                  'code'        => '',
 552              );
 553          }
 554  
 555          return $error;
 556      }
 557  
 558      /**
 559      * Build db-specific query data
 560      * @access private
 561      */
 562  	function _sql_custom_build($stage, $data)
 563      {
 564          return $data;
 565      }
 566  
 567      /**
 568      * Close sql connection
 569      * @access private
 570      */
 571  	function _sql_close()
 572      {
 573          return @sqlsrv_close($this->db_connect_id);
 574      }
 575  
 576      /**
 577      * Build db-specific report
 578      * @access private
 579      */
 580  	function _sql_report($mode, $query = '')
 581      {
 582          switch ($mode)
 583          {
 584              case 'start':
 585                  $html_table = false;
 586                  @sqlsrv_query($this->db_connect_id, 'SET SHOWPLAN_TEXT ON;');
 587                  if ($result = @sqlsrv_query($this->db_connect_id, $query))
 588                  {
 589                      @sqlsrv_next_result($result);
 590                      while ($row = @sqlsrv_fetch_array($result))
 591                      {
 592                          $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 593                      }
 594                  }
 595                  @sqlsrv_query($this->db_connect_id, 'SET SHOWPLAN_TEXT OFF;');
 596                  @sqlsrv_free_stmt($result);
 597  
 598                  if ($html_table)
 599                  {
 600                      $this->html_hold .= '</table>';
 601                  }
 602              break;
 603  
 604              case 'fromcache':
 605                  $endtime = explode(' ', microtime());
 606                  $endtime = $endtime[0] + $endtime[1];
 607  
 608                  $result = @sqlsrv_query($this->db_connect_id, $query);
 609                  while ($void = @sqlsrv_fetch_array($result))
 610                  {
 611                      // Take the time spent on parsing rows into account
 612                  }
 613                  @sqlsrv_free_stmt($result);
 614  
 615                  $splittime = explode(' ', microtime());
 616                  $splittime = $splittime[0] + $splittime[1];
 617  
 618                  $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 619  
 620              break;
 621          }
 622      }
 623  
 624      /**
 625      * Utility method used to retrieve number of rows
 626      * Emulates mysql_num_rows
 627      * Used in acp_database.php -> write_data_mssqlnative()
 628      * Requires a static or keyset cursor to be definde via
 629      * mssqlnative_set_query_options()
 630      */
 631  	function mssqlnative_num_rows($res)
 632      {
 633          if ($res !== false)
 634          {
 635              return sqlsrv_num_rows($res);
 636          }
 637          else
 638          {
 639              return false;
 640          }
 641      }
 642  
 643      /**
 644      * Allows setting mssqlnative specific query options passed to sqlsrv_query as 4th parameter.
 645      */
 646  	function mssqlnative_set_query_options($options)
 647      {
 648          $this->query_options = $options;
 649      }
 650  }
 651  
 652  ?>