phpBB/includes/db/mssql.php

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