includes/db/DatabaseMssql.php

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