includes/db/DatabaseMssql.php

   1 <?php
   2 /**
   3  * This is the MS SQL Server Native database abstraction layer.
   4  *
   5  * This program is free software; you can redistribute it and/or modify
   6  * it under the terms of the GNU General Public License as published by
   7  * the Free Software Foundation; either version 2 of the License, or
   8  * (at your option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  13  * GNU General Public License for more details.
  14  *
  15  * You should have received a copy of the GNU General Public License along
  16  * with this program; if not, write to the Free Software Foundation, Inc.,
  17  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
  18  * http://www.gnu.org/copyleft/gpl.html
  19  *
  20  * @file
  21  * @ingroup Database
  22  * @author Joel Penner <a-joelpe at microsoft dot com>
  23  * @author Chris Pucci <a-cpucci at microsoft dot com>
  24  * @author Ryan Biesemeyer <v-ryanbi at microsoft dot com>
  25  */
  26
  27 /**
  28  * @ingroup Database
  29  */
  30 class DatabaseMssql extends DatabaseBase {
  31         var $mInsertId = NULL;
  32         var $mLastResult = NULL;
  33         var $mAffectedRows = NULL;
  34
  35         var $mPort;
  36
  37         function cascadingDeletes() {
  38                 return true;
  39         }
  40         function cleanupTriggers() {
  41                 return true;
  42         }
  43         function strictIPs() {
  44                 return true;
  45         }
  46         function realTimestamps() {
  47                 return true;
  48         }
  49         function implicitGroupby() {
  50                 return false;
  51         }
  52         function implicitOrderby() {
  53                 return false;
  54         }
  55         function functionalIndexes() {
  56                 return true;
  57         }
  58         function unionSupportsOrderAndLimit() {
  59                 return false;
  60         }
  61
  62         /**
  63          * Usually aborts on failure
  64          * @return bool|DatabaseBase|null
  65          */
  66         function open( $server, $user, $password, $dbName ) {
  67                 # Test for driver support, to avoid suppressed fatal error
  68                 if ( !function_exists( 'sqlsrv_connect' ) ) {
  69                         throw new DBConnectionError( $this, "MS Sql Server Native (sqlsrv) functions missing. You can download the driver from: http://go.microsoft.com/fwlink/?LinkId=123470\n" );
  70                 }
  71
  72                 global $wgDBport;
  73
  74                 if ( !strlen( $user ) ) { # e.g. the class is being loaded
  75                         return;
  76                 }
  77
  78                 $this->close();
  79                 $this->mServer = $server;
  80                 $this->mPort = $wgDBport;
  81                 $this->mUser = $user;
  82                 $this->mPassword = $password;
  83                 $this->mDBname = $dbName;
  84
  85                 $connectionInfo = array();
  86
  87                 if( $dbName ) {
  88                         $connectionInfo['Database'] = $dbName;
  89                 }
  90
  91                 // Start NT Auth Hack
  92                 // Quick and dirty work around to provide NT Auth designation support.
  93                 // Current solution requires installer to know to input 'ntauth' for both username and password
  94                 // to trigger connection via NT Auth. - ugly, ugly, ugly
  95                 // TO-DO: Make this better and add NT Auth choice to MW installer when SQL Server option is chosen.
  96                 $ntAuthUserTest = strtolower( $user );
  97                 $ntAuthPassTest = strtolower( $password );
  98
  99                 // Decide which auth scenerio to use
 100                 if( $ntAuthPassTest == 'ntauth' && $ntAuthUserTest == 'ntauth' ){
 101                         // Don't add credentials to $connectionInfo
 102                 } else {
 103                         $connectionInfo['UID'] = $user;
 104                         $connectionInfo['PWD'] = $password;
 105                 }
 106                 // End NT Auth Hack
 107
 108                 wfSuppressWarnings();
 109                 $this->mConn = sqlsrv_connect( $server, $connectionInfo );
 110                 wfRestoreWarnings();
 111
 112                 if ( $this->mConn === false ) {
 113                         wfDebug( "DB connection error\n" );
 114                         wfDebug( "Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n" );
 115                         wfDebug( $this->lastError() . "\n" );
 116                         return false;
 117                 }
 118
 119                 $this->mOpened = true;
 120                 return $this->mConn;
 121         }
 122
 123         /**
 124          * Closes a database connection, if it is open
 125          * Returns success, true if already closed
 126          * @return bool
 127          */
 128         protected function closeConnection() {
 129                 return sqlsrv_close( $this->mConn );
 130         }
 131
 132         protected function doQuery( $sql ) {
 133                 wfDebug( "SQL: [$sql]\n" );
 134                 $this->offset = 0;
 135
 136                 // several extensions seem to think that all databases support limits via LIMIT N after the WHERE clause
 137                 // well, MSSQL uses SELECT TOP N, so to catch any of those extensions we'll do a quick check for a LIMIT
 138                 // clause and pass $sql through $this->LimitToTopN() which parses the limit clause and passes the result to
 139                 // $this->limitResult();
 140                 if ( preg_match( '/\bLIMIT\s*/i', $sql ) ) {
 141                         // massage LIMIT -> TopN
 142                         $sql = $this->LimitToTopN( $sql ) ;
 143                 }
 144
 145                 // MSSQL doesn't have EXTRACT(epoch FROM XXX)
 146                 if ( preg_match('#\bEXTRACT\s*?\(\s*?EPOCH\s+FROM\b#i', $sql, $matches ) ) {
 147                         // This is same as UNIX_TIMESTAMP, we need to calc # of seconds from 1970
 148                         $sql = str_replace( $matches[0], "DATEDIFF(s,CONVERT(datetime,'1/1/1970'),", $sql );
 149                 }
 150
 151                 // perform query
 152                 $stmt = sqlsrv_query( $this->mConn, $sql );
 153                 if ( $stmt == false ) {
 154                         $message = "A database error has occurred.  Did you forget to run maintenance/update.php after upgrading?  See: http://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script\n" .
 155                                 "Query: " . htmlentities( $sql ) . "\n" .
 156                                 "Function: " . __METHOD__ . "\n";
 157                         // process each error (our driver will give us an array of errors unlike other providers)
 158                         foreach ( sqlsrv_errors() as $error ) {
 159                                 $message .= $message . "ERROR[" . $error['code'] . "] " . $error['message'] . "\n";
 160                         }
 161
 162                         throw new DBUnexpectedError( $this, $message );
 163                 }
 164                 // remember number of rows affected
 165                 $this->mAffectedRows = sqlsrv_rows_affected( $stmt );
 166
 167                 // if it is a SELECT statement, or an insert with a request to output something we want to return a row.
 168                 if ( ( preg_match( '#\bSELECT\s#i', $sql ) ) ||
 169                         ( preg_match( '#\bINSERT\s#i', $sql ) && preg_match( '#\bOUTPUT\s+INSERTED\b#i', $sql ) ) ) {
 170                         // this is essentially a rowset, but Mediawiki calls these 'result'
 171                         // the rowset owns freeing the statement
 172                         $res = new MssqlResult( $stmt );
 173                 } else {
 174                         // otherwise we simply return it was successful, failure throws an exception
 175                         $res = true;
 176                 }
 177                 return $res;
 178         }
 179
 180         function freeResult( $res ) {
 181                 if ( $res instanceof ResultWrapper ) {
 182                         $res = $res->result;
 183                 }
 184                 $res->free();
 185         }
 186
 187         function fetchObject( $res ) {
 188                 if ( $res instanceof ResultWrapper ) {
 189                         $res = $res->result;
 190                 }
 191                 $row = $res->fetch( 'OBJECT' );
 192                 return $row;
 193         }
 194
 195         function getErrors() {
 196                 $strRet = '';
 197                 $retErrors = sqlsrv_errors( SQLSRV_ERR_ALL );
 198                 if ( $retErrors != null ) {
 199                         foreach ( $retErrors as $arrError ) {
 200                                 $strRet .= "SQLState: " . $arrError[ 'SQLSTATE'] . "\n";
 201                                 $strRet .= "Error Code: " . $arrError[ 'code'] . "\n";
 202                                 $strRet .= "Message: " . $arrError[ 'message'] . "\n";
 203                         }
 204                 } else {
 205                         $strRet = "No errors found";
 206                 }
 207                 return $strRet;
 208         }
 209
 210         function fetchRow( $res ) {
 211                 if ( $res instanceof ResultWrapper ) {
 212                         $res = $res->result;
 213                 }
 214                 $row = $res->fetch( SQLSRV_FETCH_BOTH );
 215                 return $row;
 216         }
 217
 218         function numRows( $res ) {
 219                 if ( $res instanceof ResultWrapper ) {
 220                         $res = $res->result;
 221                 }
 222                 return ( $res ) ? $res->numrows() : 0;
 223         }
 224
 225         function numFields( $res ) {
 226                 if ( $res instanceof ResultWrapper ) {
 227                         $res = $res->result;
 228                 }
 229                 return ( $res ) ? $res->numfields() : 0;
 230         }
 231
 232         function fieldName( $res, $n ) {
 233                 if ( $res instanceof ResultWrapper ) {
 234                         $res = $res->result;
 235                 }
 236                 return ( $res ) ? $res->fieldname( $n ) : 0;
 237         }
 238
 239         /**
 240          * This must be called after nextSequenceVal
 241          * @return null
 242          */
 243         function insertId() {
 244                 return $this->mInsertId;
 245         }
 246
 247         function dataSeek( $res, $row ) {
 248                 if ( $res instanceof ResultWrapper ) {
 249                         $res = $res->result;
 250                 }
 251                 return ( $res ) ? $res->seek( $row ) : false;
 252         }
 253
 254         function lastError() {
 255                 if ( $this->mConn ) {
 256                         return $this->getErrors();
 257                 } else {
 258                         return "No database connection";
 259                 }
 260         }
 261
 262         function lastErrno() {
 263                 $err = sqlsrv_errors( SQLSRV_ERR_ALL );
 264                 if ( $err[0] ) {
 265                         return $err[0]['code'];
 266                 } else {
 267                         return 0;
 268                 }
 269         }
 270
 271         function affectedRows() {
 272                 return $this->mAffectedRows;
 273         }
 274
 275         /**
 276          * SELECT wrapper
 277          *
 278          * @param $table   Mixed: array or string, table name(s) (prefix auto-added)
 279          * @param $vars    Mixed: array or string, field name(s) to be retrieved
 280          * @param $conds   Mixed: array or string, condition(s) for WHERE
 281          * @param $fname   String: calling function name (use __METHOD__) for logs/profiling
 282          * @param $options Array: associative array of options (e.g. array('GROUP BY' => 'page_title')),
 283          *                 see Database::makeSelectOptions code for list of supported stuff
 284          * @param $join_conds Array: Associative array of table join conditions (optional)
 285          *                                                 (e.g. array( 'page' => array('LEFT JOIN','page_latest=rev_id') )
 286          * @return Mixed: database result resource (feed to Database::fetchObject or whatever), or false on failure
 287          */
 288         function select( $table, $vars, $conds = '', $fname = 'DatabaseMssql::select', $options = array(), $join_conds = array() )
 289         {
 290                 $sql = $this->selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds );
 291                 if ( isset( $options['EXPLAIN'] ) ) {
 292                         sqlsrv_query( $this->mConn, "SET SHOWPLAN_ALL ON;" );
 293                         $ret = $this->query( $sql, $fname );
 294                         sqlsrv_query( $this->mConn, "SET SHOWPLAN_ALL OFF;" );
 295                         return $ret;
 296                 }
 297                 return $this->query( $sql, $fname );
 298         }
 299
 300         /**
 301          * SELECT wrapper
 302          *
 303          * @param $table   Mixed:  Array or string, table name(s) (prefix auto-added)
 304          * @param $vars    Mixed:  Array or string, field name(s) to be retrieved
 305          * @param $conds   Mixed:  Array or string, condition(s) for WHERE
 306          * @param $fname   String: Calling function name (use __METHOD__) for logs/profiling
 307          * @param $options Array:  Associative array of options (e.g. array('GROUP BY' => 'page_title')),
 308          *                 see Database::makeSelectOptions code for list of supported stuff
 309          * @param $join_conds Array: Associative array of table join conditions (optional)
 310          *                    (e.g. array( 'page' => array('LEFT JOIN','page_latest=rev_id') )
 311          * @return string, the SQL text
 312          */
 313         function selectSQLText( $table, $vars, $conds = '', $fname = 'DatabaseMssql::select', $options = array(), $join_conds = array() ) {
 314                 if ( isset( $options['EXPLAIN'] ) ) {
 315                         unset( $options['EXPLAIN'] );
 316                 }
 317                 return parent::selectSQLText(  $table, $vars, $conds, $fname, $options, $join_conds );
 318         }
 319
 320         /**
 321          * Estimate rows in dataset
 322          * Returns estimated count, based on SHOWPLAN_ALL output
 323          * This is not necessarily an accurate estimate, so use sparingly
 324          * Returns -1 if count cannot be found
 325          * Takes same arguments as Database::select()
 326          * @return int
 327          */
 328         function estimateRowCount( $table, $vars = '*', $conds = '', $fname = 'DatabaseMssql::estimateRowCount', $options = array() ) {
 329                 $options['EXPLAIN'] = true;// http://msdn2.microsoft.com/en-us/library/aa259203.aspx
 330                 $res = $this->select( $table, $vars, $conds, $fname, $options );
 331
 332                 $rows = -1;
 333                 if ( $res ) {
 334                         $row = $this->fetchRow( $res );
 335                         if ( isset( $row['EstimateRows'] ) ) $rows = $row['EstimateRows'];
 336                 }
 337                 return $rows;
 338         }
 339
 340         /**
 341          * Returns information about an index
 342          * If errors are explicitly ignored, returns NULL on failure
 343          * @return array|bool|null
 344          */
 345         function indexInfo( $table, $index, $fname = 'DatabaseMssql::indexExists' ) {
 346                 # This does not return the same info as MYSQL would, but that's OK because MediaWiki never uses the
 347                 # returned value except to check for the existance of indexes.
 348                 $sql = "sp_helpindex '" . $table . "'";
 349                 $res = $this->query( $sql, $fname );
 350                 if ( !$res ) {
 351                         return NULL;
 352                 }
 353
 354                 $result = array();
 355                 foreach ( $res as $row ) {
 356                         if ( $row->index_name == $index ) {
 357                                 $row->Non_unique = !stristr( $row->index_description, "unique" );
 358                                 $cols = explode( ", ", $row->index_keys );
 359                                 foreach ( $cols as $col ) {
 360                                         $row->Column_name = trim( $col );
 361                                         $result[] = clone $row;
 362                                 }
 363                         } elseif ( $index == 'PRIMARY' && stristr( $row->index_description, 'PRIMARY' ) ) {
 364                                 $row->Non_unique = 0;
 365                                 $cols = explode( ", ", $row->index_keys );
 366                                 foreach ( $cols as $col ) {
 367                                         $row->Column_name = trim( $col );
 368                                         $result[] = clone $row;
 369                                 }
 370                         }
 371                 }
 372                 return empty( $result ) ? false : $result;
 373         }
 374
 375         /**
 376          * INSERT wrapper, inserts an array into a table
 377          *
 378          * $arrToInsert may be a single associative array, or an array of these with numeric keys, for
 379          * multi-row insert.
 380          *
 381          * Usually aborts on failure
 382          * If errors are explicitly ignored, returns success
 383          * @return bool
 384          */
 385         function insert( $table, $arrToInsert, $fname = 'DatabaseMssql::insert', $options = array() ) {
 386                 # No rows to insert, easy just return now
 387                 if ( !count( $arrToInsert ) ) {
 388                         return true;
 389                 }
 390
 391                 if ( !is_array( $options ) ) {
 392                         $options = array( $options );
 393                 }
 394
 395                 $table = $this->tableName( $table );
 396
 397                 if ( !( isset( $arrToInsert[0] ) && is_array( $arrToInsert[0] ) ) ) {// Not multi row
 398                         $arrToInsert = array( 0 => $arrToInsert );// make everything multi row compatible
 399                 }
 400
 401                 $allOk = true;
 402
 403                 // We know the table we're inserting into, get its identity column
 404                 $identity = null;
 405                 $tableRaw = preg_replace( '#\[([^\]]*)\]#', '$1', $table ); // strip matching square brackets from table name
 406                 $res = $this->doQuery( "SELECT NAME AS idColumn FROM SYS.IDENTITY_COLUMNS WHERE OBJECT_NAME(OBJECT_ID)='{$tableRaw}'" );
 407                 if( $res && $res->numrows() ){
 408                         // There is an identity for this table.
 409                         $identity = array_pop( $res->fetch( SQLSRV_FETCH_ASSOC ) );
 410                 }
 411                 unset( $res );
 412
 413                 foreach ( $arrToInsert as $a ) {
 414                         // start out with empty identity column, this is so we can return it as a result of the insert logic
 415                         $sqlPre = '';
 416                         $sqlPost = '';
 417                         $identityClause = '';
 418
 419                         // if we have an identity column
 420                         if( $identity ) {
 421                                 // iterate through
 422                                 foreach ($a as $k => $v ) {
 423                                         if ( $k == $identity ) {
 424                                                 if( !is_null($v) ){
 425                                                         // there is a value being passed to us, we need to turn on and off inserted identity
 426                                                         $sqlPre = "SET IDENTITY_INSERT $table ON;" ;
 427                                                         $sqlPost = ";SET IDENTITY_INSERT $table OFF;";
 428
 429                                                 } else {
 430                                                         // we can't insert NULL into an identity column, so remove the column from the insert.
 431                                                         unset( $a[$k] );
 432                                                 }
 433                                         }
 434                                 }
 435                                 $identityClause = "OUTPUT INSERTED.$identity "; // we want to output an identity column as result
 436                         }
 437
 438                         $keys = array_keys( $a );
 439
 440                         // INSERT IGNORE is not supported by SQL Server
 441                         // remove IGNORE from options list and set ignore flag to true
 442                         $ignoreClause = false;
 443                         foreach ( $options as $k => $v ) {
 444                                 if ( strtoupper( $v ) == "IGNORE" ) {
 445                                         unset( $options[$k] );
 446                                         $ignoreClause = true;
 447                                 }
 448                         }
 449
 450                         // translate MySQL INSERT IGNORE to something SQL Server can use
 451                         // example:
 452                         // MySQL: INSERT IGNORE INTO user_groups (ug_user,ug_group) VALUES ('1','sysop')
 453                         // MSSQL: IF NOT EXISTS (SELECT * FROM user_groups WHERE ug_user = '1') INSERT INTO user_groups (ug_user,ug_group) VALUES ('1','sysop')
 454                         if ( $ignoreClause ) {
 455                                 $prival = $a[$keys[0]];
 456                                 $sqlPre .= "IF NOT EXISTS (SELECT * FROM $table WHERE $keys[0] = '$prival')";
 457                         }
 458
 459                         // Build the actual query
 460                         $sql = $sqlPre . 'INSERT ' . implode( ' ', $options ) .
 461                                 " INTO $table (" . implode( ',', $keys ) . ") $identityClause VALUES (";
 462
 463                         $first = true;
 464                         foreach ( $a as $value ) {
 465                                 if ( $first ) {
 466                                         $first = false;
 467                                 } else {
 468                                         $sql .= ',';
 469                                 }
 470                                 if ( is_string( $value ) ) {
 471                                         $sql .= $this->addQuotes( $value );
 472                                 } elseif ( is_null( $value ) ) {
 473                                         $sql .= 'null';
 474                                 } elseif ( is_array( $value ) || is_object( $value ) ) {
 475                                         if ( is_object( $value ) && strtolower( get_class( $value ) ) == 'blob' ) {
 476                                                 $sql .= $this->addQuotes( $value );
 477                                         }  else {
 478                                                 $sql .= $this->addQuotes( serialize( $value ) );
 479                                         }
 480                                 } else {
 481                                         $sql .= $value;
 482                                 }
 483                         }
 484                         $sql .= ')' . $sqlPost;
 485
 486                         // Run the query
 487                         $ret = sqlsrv_query( $this->mConn, $sql );
 488
 489                         if ( $ret === false ) {
 490                                 throw new DBQueryError( $this, $this->getErrors(), $this->lastErrno(), $sql, $fname );
 491                         } elseif ( $ret != NULL ) {
 492                                 // remember number of rows affected
 493                                 $this->mAffectedRows = sqlsrv_rows_affected( $ret );
 494                                 if ( !is_null($identity) ) {
 495                                         // then we want to get the identity column value we were assigned and save it off
 496                                         $row = sqlsrv_fetch_object( $ret );
 497                                         $this->mInsertId = $row->$identity;
 498                                 }
 499                                 sqlsrv_free_stmt( $ret );
 500                                 continue;
 501                         }
 502                         $allOk = false;
 503                 }
 504                 return $allOk;
 505         }
 506
 507         /**
 508          * INSERT SELECT wrapper
 509          * $varMap must be an associative array of the form array( 'dest1' => 'source1', ...)
 510          * Source items may be literals rather than field names, but strings should be quoted with Database::addQuotes()
 511          * $conds may be "*" to copy the whole table
 512          * srcTable may be an array of tables.
 513          * @return null|\ResultWrapper
 514          */
 515         function insertSelect( $destTable, $srcTable, $varMap, $conds, $fname = 'DatabaseMssql::insertSelect',
 516                 $insertOptions = array(), $selectOptions = array() ) {
 517                 $ret = parent::insertSelect( $destTable, $srcTable, $varMap, $conds, $fname, $insertOptions, $selectOptions );
 518
 519                 if ( $ret === false ) {
 520                         throw new DBQueryError( $this, $this->getErrors(), $this->lastErrno(), /*$sql*/ '', $fname );
 521                 } elseif ( $ret != NULL ) {
 522                         // remember number of rows affected
 523                         $this->mAffectedRows = sqlsrv_rows_affected( $ret );
 524                         return $ret;
 525                 }
 526                 return NULL;
 527         }
 528
 529         /**
 530          * Return the next in a sequence, save the value for retrieval via insertId()
 531          * @return
 532          */
 533         function nextSequenceValue( $seqName ) {
 534                 if ( !$this->tableExists( 'sequence_' . $seqName ) ) {
 535                         sqlsrv_query( $this->mConn, "CREATE TABLE [sequence_$seqName] (id INT NOT NULL IDENTITY PRIMARY KEY, junk varchar(10) NULL)" );
 536                 }
 537                 sqlsrv_query( $this->mConn, "INSERT INTO [sequence_$seqName] (junk) VALUES ('')" );
 538                 $ret = sqlsrv_query( $this->mConn, "SELECT TOP 1 id FROM [sequence_$seqName] ORDER BY id DESC" );
 539                 $row = sqlsrv_fetch_array( $ret, SQLSRV_FETCH_ASSOC );// KEEP ASSOC THERE, weird weird bug dealing with the return value if you don't
 540
 541                 sqlsrv_free_stmt( $ret );
 542                 $this->mInsertId = $row['id'];
 543                 return $row['id'];
 544         }
 545
 546         /**
 547          * Return the current value of a sequence. Assumes it has ben nextval'ed in this session.
 548          * @return
 549          */
 550         function currentSequenceValue( $seqName ) {
 551                 $ret = sqlsrv_query( $this->mConn, "SELECT TOP 1 id FROM [sequence_$seqName] ORDER BY id DESC" );
 552                 if ( $ret !== false ) {
 553                         $row = sqlsrv_fetch_array( $ret );
 554                         sqlsrv_free_stmt( $ret );
 555                         return $row['id'];
 556                 } else {
 557                         return $this->nextSequenceValue( $seqName );
 558                 }
 559         }
 560
 561         # Returns the size of a text field, or -1 for "unlimited"
 562         function textFieldSize( $table, $field ) {
 563                 $table = $this->tableName( $table );
 564                 $sql = "SELECT CHARACTER_MAXIMUM_LENGTH,DATA_TYPE FROM INFORMATION_SCHEMA.Columns
 565                         WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'";
 566                 $res = $this->query( $sql );
 567                 $row = $this->fetchRow( $res );
 568                 $size = -1;
 569                 if ( strtolower( $row['DATA_TYPE'] ) != 'text' ) {
 570                         $size = $row['CHARACTER_MAXIMUM_LENGTH'];
 571                 }
 572                 return $size;
 573         }
 574
 575         /**
 576          * Construct a LIMIT query with optional offset
 577          * This is used for query pages
 578          * $sql string SQL query we will append the limit too
 579          * $limit integer the SQL limit
 580          * $offset integer the SQL offset (default false)
 581          * @return mixed|string
 582          */
 583         function limitResult( $sql, $limit, $offset = false ) {
 584                 if ( $offset === false || $offset == 0 ) {
 585                         if ( strpos( $sql, "SELECT" ) === false ) {
 586                                 return "TOP {$limit} " . $sql;
 587                         } else {
 588                                 return preg_replace( '/\bSELECT(\s*DISTINCT)?\b/Dsi', 'SELECT$1 TOP ' . $limit, $sql, 1 );
 589                         }
 590                 } else {
 591                         $sql = '
 592                                 SELECT * FROM (
 593                                   SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3 FROM (
 594                                         SELECT 1 AS line2, sub1.* FROM (' . $sql . ') AS sub1
 595                                   ) as sub2
 596                                 ) AS sub3
 597                                 WHERE line3 BETWEEN ' . ( $offset + 1 ) . ' AND ' . ( $offset + $limit );
 598                         return $sql;
 599                 }
 600         }
 601
 602         // If there is a limit clause, parse it, strip it, and pass the remaining sql through limitResult()
 603         // with the appropriate parameters. Not the prettiest solution, but better than building a whole new parser.
 604         // This exists becase there are still too many extensions that don't use dynamic sql generation.
 605         function LimitToTopN( $sql ) {
 606                 // Matches: LIMIT {[offset,] row_count | row_count OFFSET offset}
 607                 $pattern = '/\bLIMIT\s+((([0-9]+)\s*,\s*)?([0-9]+)(\s+OFFSET\s+([0-9]+))?)/i';
 608                 if ( preg_match( $pattern, $sql, $matches ) ) {
 609                         // row_count = $matches[4]
 610                         $row_count = $matches[4];
 611                         // offset = $matches[3] OR $matches[6]
 612                         $offset = $matches[3] or
 613                                 $offset = $matches[6] or
 614                                 $offset = false;
 615
 616                         // strip the matching LIMIT clause out
 617                         $sql = str_replace( $matches[0], '', $sql );
 618                         return $this->limitResult( $sql, $row_count, $offset );
 619                 }
 620                 return $sql;
 621         }
 622
 623         // MSSQL does support this, but documentation is too thin to make a generalized
 624         // function for this. Apparently UPDATE TOP (N) works, but the sort order
 625         // may not be what we're expecting so the top n results may be a random selection.
 626         // TODO: Implement properly.
 627         function limitResultForUpdate( $sql, $num ) {
 628                 return $sql;
 629         }
 630
 631         function timestamp( $ts = 0 ) {
 632                 return wfTimestamp( TS_ISO_8601, $ts );
 633         }
 634
 635         /**
 636          * @return string wikitext of a link to the server software's web site
 637          */
 638         public static function getSoftwareLink() {
 639                 return "[http://www.microsoft.com/sql/ MS SQL Server]";
 640         }
 641
 642         /**
 643          * @return string Version information from the database
 644          */
 645         function getServerVersion() {
 646                 $server_info = sqlsrv_server_info( $this->mConn );
 647                 $version = 'Error';
 648                 if ( isset( $server_info['SQLServerVersion'] ) ) {
 649                         $version = $server_info['SQLServerVersion'];
 650                 }
 651                 return $version;
 652         }
 653
 654         function tableExists ( $table, $fname = __METHOD__, $schema = false ) {
 655                 $res = sqlsrv_query( $this->mConn, "SELECT * FROM information_schema.tables
 656                         WHERE table_type='BASE TABLE' AND table_name = '$table'" );
 657                 if ( $res === false ) {
 658                         print( "Error in tableExists query: " . $this->getErrors() );
 659                         return false;
 660                 }
 661                 if ( sqlsrv_fetch( $res ) ) {
 662                         return true;
 663                 } else {
 664                         return false;
 665                 }
 666         }
 667
 668         /**
 669          * Query whether a given column exists in the mediawiki schema
 670          * @return bool
 671          */
 672         function fieldExists( $table, $field, $fname = 'DatabaseMssql::fieldExists' ) {
 673                 $table = $this->tableName( $table );
 674                 $res = sqlsrv_query( $this->mConn, "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.Columns
 675                         WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" );
 676                 if ( $res === false ) {
 677                         print( "Error in fieldExists query: " . $this->getErrors() );
 678                         return false;
 679                 }
 680                 if ( sqlsrv_fetch( $res ) ) {
 681                         return true;
 682                 } else {
 683                         return false;
 684                 }
 685         }
 686
 687         function fieldInfo( $table, $field ) {
 688                 $table = $this->tableName( $table );
 689                 $res = sqlsrv_query( $this->mConn, "SELECT * FROM INFORMATION_SCHEMA.Columns
 690                         WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" );
 691                 if ( $res === false ) {
 692                         print( "Error in fieldInfo query: " . $this->getErrors() );
 693                         return false;
 694                 }
 695                 $meta = $this->fetchRow( $res );
 696                 if ( $meta ) {
 697                         return new MssqlField( $meta );
 698                 }
 699                 return false;
 700         }
 701
 702         /**
 703          * Begin a transaction, committing any previously open transaction
 704          */
 705         function begin( $fname = 'DatabaseMssql::begin' ) {
 706                 sqlsrv_begin_transaction( $this->mConn );
 707                 $this->mTrxLevel = 1;
 708         }
 709
 710         /**
 711          * End a transaction
 712          */
 713         function commit( $fname = 'DatabaseMssql::commit' ) {
 714                 sqlsrv_commit( $this->mConn );
 715                 $this->mTrxLevel = 0;
 716         }
 717
 718         /**
 719          * Rollback a transaction.
 720          * No-op on non-transactional databases.
 721          */
 722         function rollback( $fname = 'DatabaseMssql::rollback' ) {
 723                 sqlsrv_rollback( $this->mConn );
 724                 $this->mTrxLevel = 0;
 725         }
 726
 727         /**
 728          * Escapes a identifier for use inm SQL.
 729          * Throws an exception if it is invalid.
 730          * Reference: http://msdn.microsoft.com/en-us/library/aa224033%28v=SQL.80%29.aspx
 731          * @return string
 732          */
 733         private function escapeIdentifier( $identifier ) {
 734                 if ( strlen( $identifier ) == 0 ) {
 735                         throw new MWException( "An identifier must not be empty" );
 736                 }
 737                 if ( strlen( $identifier ) > 128 ) {
 738                         throw new MWException( "The identifier '$identifier' is too long (max. 128)" );
 739                 }
 740                 if ( ( strpos( $identifier, '[' ) !== false ) || ( strpos( $identifier, ']' ) !== false ) ) {
 741                         // It may be allowed if you quoted with double quotation marks, but that would break if QUOTED_IDENTIFIER is OFF
 742                         throw new MWException( "You can't use square brackers in the identifier '$identifier'" );
 743                 }
 744                 return "[$identifier]";
 745         }
 746
 747         /**
 748          * Initial setup.
 749          * Precondition: This object is connected as the superuser.
 750          * Creates the database, schema, user and login.
 751          */
 752         function initial_setup( $dbName, $newUser, $loginPassword ) {
 753                 $dbName = $this->escapeIdentifier( $dbName );
 754
 755                 // It is not clear what can be used as a login,
 756                 // From http://msdn.microsoft.com/en-us/library/ms173463.aspx
 757                 // a sysname may be the same as an identifier.
 758                 $newUser = $this->escapeIdentifier( $newUser );
 759                 $loginPassword = $this->addQuotes( $loginPassword );
 760
 761                 $this->doQuery("CREATE DATABASE $dbName;");
 762                 $this->doQuery("USE $dbName;");
 763                 $this->doQuery("CREATE SCHEMA $dbName;");
 764                 $this->doQuery("
 765                                                 CREATE
 766                                                         LOGIN $newUser
 767                                                 WITH
 768                                                         PASSWORD=$loginPassword
 769                                                 ;
 770                                         ");
 771                 $this->doQuery("
 772                                                 CREATE
 773                                                         USER $newUser
 774                                                 FOR
 775                                                         LOGIN $newUser
 776                                                 WITH
 777                                                         DEFAULT_SCHEMA=$dbName
 778                                                 ;
 779                                         ");
 780                 $this->doQuery("
 781                                                 GRANT
 782                                                         BACKUP DATABASE,
 783                                                         BACKUP LOG,
 784                                                         CREATE DEFAULT,
 785                                                         CREATE FUNCTION,
 786                                                         CREATE PROCEDURE,
 787                                                         CREATE RULE,
 788                                                         CREATE TABLE,
 789                                                         CREATE VIEW,
 790                                                         CREATE FULLTEXT CATALOG
 791                                                 ON
 792                                                         DATABASE::$dbName
 793                                                 TO $newUser
 794                                                 ;
 795                                         ");
 796                 $this->doQuery("
 797                                                 GRANT
 798                                                         CONTROL
 799                                                 ON
 800                                                         SCHEMA::$dbName
 801                                                 TO $newUser
 802                                                 ;
 803                                         ");
 804
 805
 806         }
 807
 808         function encodeBlob( $b ) {
 809         // we can't have zero's and such, this is a simple encoding to make sure we don't barf
 810                 return base64_encode( $b );
 811         }
 812
 813         function decodeBlob( $b ) {
 814         // we can't have zero's and such, this is a simple encoding to make sure we don't barf
 815         return base64_decode( $b );
 816         }
 817
 818         /**
 819          * @private
 820          * @return string
 821          */
 822         function tableNamesWithUseIndexOrJOIN( $tables, $use_index = array(), $join_conds = array() ) {
 823                 $ret = array();
 824                 $retJOIN = array();
 825                 $use_index_safe = is_array( $use_index ) ? $use_index : array();
 826                 $join_conds_safe = is_array( $join_conds ) ? $join_conds : array();
 827                 foreach ( $tables as $table ) {
 828                         // Is there a JOIN and INDEX clause for this table?
 829                         if ( isset( $join_conds_safe[$table] ) && isset( $use_index_safe[$table] ) ) {
 830                                 $tableClause = $join_conds_safe[$table][0] . ' ' . $this->tableName( $table );
 831                                 $tableClause .= ' ' . $this->useIndexClause( implode( ',', (array)$use_index_safe[$table] ) );
 832                                 $tableClause .= ' ON (' . $this->makeList( (array)$join_conds_safe[$table][1], LIST_AND ) . ')';
 833                                 $retJOIN[] = $tableClause;
 834                         // Is there an INDEX clause?
 835                         } elseif ( isset( $use_index_safe[$table] ) ) {
 836                                 $tableClause = $this->tableName( $table );
 837                                 $tableClause .= ' ' . $this->useIndexClause( implode( ',', (array)$use_index_safe[$table] ) );
 838                                 $ret[] = $tableClause;
 839                         // Is there a JOIN clause?
 840                         } elseif ( isset( $join_conds_safe[$table] ) ) {
 841                                 $tableClause = $join_conds_safe[$table][0] . ' ' . $this->tableName( $table );
 842                                 $tableClause .= ' ON (' . $this->makeList( (array)$join_conds_safe[$table][1], LIST_AND ) . ')';
 843                                 $retJOIN[] = $tableClause;
 844                         } else {
 845                                 $tableClause = $this->tableName( $table );
 846                                 $ret[] = $tableClause;
 847                         }
 848                 }
 849                 // We can't separate explicit JOIN clauses with ',', use ' ' for those
 850                 $straightJoins = !empty( $ret ) ? implode( ',', $ret ) : "";
 851                 $otherJoins = !empty( $retJOIN ) ? implode( ' ', $retJOIN ) : "";
 852                 // Compile our final table clause
 853                 return implode( ' ', array( $straightJoins, $otherJoins ) );
 854         }
 855
 856         function strencode( $s ) { # Should not be called by us
 857                 return str_replace( "'", "''", $s );
 858         }
 859
 860         function addQuotes( $s ) {
 861                 if ( $s instanceof Blob ) {
 862                         return "'" . $s->fetch( $s ) . "'";
 863                 } else {
 864                         return parent::addQuotes( $s );
 865                 }
 866         }
 867
 868         public function addIdentifierQuotes( $s ) {
 869                 // http://msdn.microsoft.com/en-us/library/aa223962.aspx
 870                 return '[' . $s . ']';
 871         }
 872
 873         public function isQuotedIdentifier( $name ) {
 874                 return $name[0] == '[' && substr( $name, -1, 1 ) == ']';
 875         }
 876
 877         function selectDB( $db ) {
 878                 return ( $this->query( "SET DATABASE $db" ) !== false );
 879         }
 880
 881         /**
 882          * @private
 883          *
 884          * @param $options Array: an associative array of options to be turned into
 885          *                 an SQL query, valid keys are listed in the function.
 886          * @return Array
 887          */
 888         function makeSelectOptions( $options ) {
 889                 $tailOpts = '';
 890                 $startOpts = '';
 891
 892                 $noKeyOptions = array();
 893                 foreach ( $options as $key => $option ) {
 894                         if ( is_numeric( $key ) ) {
 895                                 $noKeyOptions[$option] = true;
 896                         }
 897                 }
 898
 899                 if ( isset( $options['GROUP BY'] ) ) {
 900                         $tailOpts .= " GROUP BY {$options['GROUP BY']}";
 901                 }
 902                 if ( isset( $options['HAVING'] ) ) {
 903                         $tailOpts .= " HAVING {$options['GROUP BY']}";
 904                 }
 905                 if ( isset( $options['ORDER BY'] ) ) {
 906                         $tailOpts .= " ORDER BY {$options['ORDER BY']}";
 907                 }
 908
 909                 if ( isset( $noKeyOptions['DISTINCT'] ) && isset( $noKeyOptions['DISTINCTROW'] ) ) {
 910                         $startOpts .= 'DISTINCT';
 911                 }
 912
 913                 // we want this to be compatible with the output of parent::makeSelectOptions()
 914                 return array( $startOpts, '' , $tailOpts, '' );
 915         }
 916
 917         /**
 918          * Get the type of the DBMS, as it appears in $wgDBtype.
 919          * @return string
 920          */
 921         function getType(){
 922                 return 'mssql';
 923         }
 924
 925         function buildConcat( $stringList ) {
 926                 return implode( ' + ', $stringList );
 927         }
 928
 929         public function getSearchEngine() {
 930                 return "SearchMssql";
 931         }
 932
 933         /**
 934          * Since MSSQL doesn't recognize the infinity keyword, set date manually.
 935          * @todo Remove magic date
 936          * @return string
 937          */
 938         public function getInfinity() {
 939                 return '3000-01-31 00:00:00.000';
 940         }
 941
 942 } // end DatabaseMssql class
 943
 944 /**
 945  * Utility class.
 946  *
 947  * @ingroup Database
 948  */
 949 class MssqlField implements Field {
 950         private $name, $tablename, $default, $max_length, $nullable, $type;
 951         function __construct ( $info ) {
 952                 $this->name = $info['COLUMN_NAME'];
 953                 $this->tablename = $info['TABLE_NAME'];
 954                 $this->default = $info['COLUMN_DEFAULT'];
 955                 $this->max_length = $info['CHARACTER_MAXIMUM_LENGTH'];
 956                 $this->nullable = !( strtolower( $info['IS_NULLABLE'] ) == 'no' );
 957                 $this->type = $info['DATA_TYPE'];
 958         }
 959
 960         function name() {
 961                 return $this->name;
 962         }
 963
 964         function tableName() {
 965                 return $this->tableName;
 966         }
 967
 968         function defaultValue() {
 969                 return $this->default;
 970         }
 971
 972         function maxLength() {
 973                 return $this->max_length;
 974         }
 975
 976         function isNullable() {
 977                 return $this->nullable;
 978         }
 979
 980         function type() {
 981                 return $this->type;
 982         }
 983 }
 984
 985 /**
 986  * The MSSQL PHP driver doesn't support sqlsrv_num_rows, so we recall all rows into an array and maintain our
 987  * own cursor index into that array...This is similar to the way the Oracle driver handles this same issue
 988  *
 989  * @ingroup Database
 990  */
 991 class MssqlResult {
 992
 993         public function __construct( $queryresult = false ) {
 994                 $this->mCursor = 0;
 995                 $this->mRows = array();
 996                 $this->mNumFields = sqlsrv_num_fields( $queryresult );
 997                 $this->mFieldMeta = sqlsrv_field_metadata( $queryresult );
 998
 999                 $rows = sqlsrv_fetch_array( $queryresult, SQLSRV_FETCH_ASSOC );
1000
1001                 foreach( $rows as $row ) {
1002                         if ( $row !== null ) {
1003                                 foreach ( $row as $k => $v ) {
1004                                         if ( is_object( $v ) && method_exists( $v, 'format' ) ) {// DateTime Object
1005                                                 $row[$k] = $v->format( "Y-m-d\TH:i:s\Z" );
1006                                         }
1007                                 }
1008                                 $this->mRows[] = $row;// read results into memory, cursors are not supported
1009                         }
1010                 }
1011                 $this->mRowCount = count( $this->mRows );
1012                 sqlsrv_free_stmt( $queryresult );
1013         }
1014
1015         private function array_to_obj( $array, &$obj ) {
1016                 foreach ( $array as $key => $value ) {
1017                         if ( is_array( $value ) ) {
1018                                 $obj->$key = new stdClass();
1019                                 $this->array_to_obj( $value, $obj->$key );
1020                         } else {
1021                                 if ( !empty( $key ) ) {
1022                                         $obj->$key = $value;
1023                                 }
1024                         }
1025                 }
1026                 return $obj;
1027         }
1028
1029         public function fetch( $mode = SQLSRV_FETCH_BOTH, $object_class = 'stdClass' ) {
1030                 if ( $this->mCursor >= $this->mRowCount || $this->mRowCount == 0 ) {
1031                         return false;
1032                 }
1033                 $arrNum = array();
1034                 if ( $mode == SQLSRV_FETCH_NUMERIC || $mode == SQLSRV_FETCH_BOTH ) {
1035                         foreach ( $this->mRows[$this->mCursor] as $value ) {
1036                                 $arrNum[] = $value;
1037                         }
1038                 }
1039                 switch( $mode ) {
1040                         case SQLSRV_FETCH_ASSOC:
1041                                 $ret = $this->mRows[$this->mCursor];
1042                                 break;
1043                         case SQLSRV_FETCH_NUMERIC:
1044                                 $ret = $arrNum;
1045                                 break;
1046                         case 'OBJECT':
1047                                 $o = new $object_class;
1048                                 $ret = $this->array_to_obj( $this->mRows[$this->mCursor], $o );
1049                                 break;
1050                         case SQLSRV_FETCH_BOTH:
1051                         default:
1052                                 $ret = $this->mRows[$this->mCursor] + $arrNum;
1053                                 break;
1054                 }
1055
1056                 $this->mCursor++;
1057                 return $ret;
1058         }
1059
1060         public function get( $pos, $fld ) {
1061                 return $this->mRows[$pos][$fld];
1062         }
1063
1064         public function numrows() {
1065                 return $this->mRowCount;
1066         }
1067
1068         public function seek( $iRow ) {
1069                 $this->mCursor = min( $iRow, $this->mRowCount );
1070         }
1071
1072         public function numfields() {
1073                 return $this->mNumFields;
1074         }
1075
1076         public function fieldname( $nr ) {
1077                 $arrKeys = array_keys( $this->mRows[0] );
1078                 return $arrKeys[$nr];
1079         }
1080
1081         public function fieldtype( $nr ) {
1082                 $i = 0;
1083                 $intType = -1;
1084                 foreach ( $this->mFieldMeta as $meta ) {
1085                         if ( $nr == $i ) {
1086                                 $intType = $meta['Type'];
1087                                 break;
1088                         }
1089                         $i++;
1090                 }
1091                 // http://msdn.microsoft.com/en-us/library/cc296183.aspx contains type table
1092                 switch( $intType ) {
1093                         case SQLSRV_SQLTYPE_BIGINT:             $strType = 'bigint'; break;
1094                         case SQLSRV_SQLTYPE_BINARY:             $strType = 'binary'; break;
1095                         case SQLSRV_SQLTYPE_BIT:                        $strType = 'bit'; break;
1096                         case SQLSRV_SQLTYPE_CHAR:                       $strType = 'char'; break;
1097                         case SQLSRV_SQLTYPE_DATETIME:           $strType = 'datetime'; break;
1098                         case SQLSRV_SQLTYPE_DECIMAL/*($precision, $scale)*/: $strType = 'decimal'; break;
1099                         case SQLSRV_SQLTYPE_FLOAT:                      $strType = 'float'; break;
1100                         case SQLSRV_SQLTYPE_IMAGE:                      $strType = 'image'; break;
1101                         case SQLSRV_SQLTYPE_INT:                        $strType = 'int'; break;
1102                         case SQLSRV_SQLTYPE_MONEY:                      $strType = 'money'; break;
1103                         case SQLSRV_SQLTYPE_NCHAR/*($charCount)*/: $strType = 'nchar'; break;
1104                         case SQLSRV_SQLTYPE_NUMERIC/*($precision, $scale)*/: $strType = 'numeric'; break;
1105                         case SQLSRV_SQLTYPE_NVARCHAR/*($charCount)*/: $strType = 'nvarchar'; break;
1106                         // case SQLSRV_SQLTYPE_NVARCHAR('max'): $strType = 'nvarchar(MAX)'; break;
1107                         case SQLSRV_SQLTYPE_NTEXT:                      $strType = 'ntext'; break;
1108                         case SQLSRV_SQLTYPE_REAL:                       $strType = 'real'; break;
1109                         case SQLSRV_SQLTYPE_SMALLDATETIME:      $strType = 'smalldatetime'; break;
1110                         case SQLSRV_SQLTYPE_SMALLINT:           $strType = 'smallint'; break;
1111                         case SQLSRV_SQLTYPE_SMALLMONEY:         $strType = 'smallmoney'; break;
1112                         case SQLSRV_SQLTYPE_TEXT:                       $strType = 'text'; break;
1113                         case SQLSRV_SQLTYPE_TIMESTAMP:          $strType = 'timestamp'; break;
1114                         case SQLSRV_SQLTYPE_TINYINT:            $strType = 'tinyint'; break;
1115                         case SQLSRV_SQLTYPE_UNIQUEIDENTIFIER: $strType = 'uniqueidentifier'; break;
1116                         case SQLSRV_SQLTYPE_UDT:                        $strType = 'UDT'; break;
1117                         case SQLSRV_SQLTYPE_VARBINARY/*($byteCount)*/: $strType = 'varbinary'; break;
1118                         // case SQLSRV_SQLTYPE_VARBINARY('max'): $strType = 'varbinary(MAX)'; break;
1119                         case SQLSRV_SQLTYPE_VARCHAR/*($charCount)*/: $strType = 'varchar'; break;
1120                         // case SQLSRV_SQLTYPE_VARCHAR('max'): $strType = 'varchar(MAX)'; break;
1121                         case SQLSRV_SQLTYPE_XML:                        $strType = 'xml'; break;
1122                         default: $strType = $intType;
1123                 }
1124                 return $strType;
1125         }
1126
1127         public function free() {
1128                 unset( $this->mRows );
1129                 return;
1130         }
1131 }