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