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