includes/db/DatabaseMssql.php

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