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