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 = 'DatabaseMssql::select', $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 = 'DatabaseMssql::select', $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 = 'DatabaseMssql::estimateRowCount', $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'] ) ) $rows = $row['EstimateRows'];
 348                 }
 349                 return $rows;
 350         }
 351
 352         /**
 353          * Returns information about an index
 354          * If errors are explicitly ignored, returns NULL on failure
 355          * @return array|bool|null
 356          */
 357         function indexInfo( $table, $index, $fname = 'DatabaseMssql::indexExists' ) {
 358                 # This does not return the same info as MYSQL would, but that's OK because MediaWiki never uses the
 359                 # returned value except to check for the existance of indexes.
 360                 $sql = "sp_helpindex '" . $table . "'";
 361                 $res = $this->query( $sql, $fname );
 362                 if ( !$res ) {
 363                         return null;
 364                 }
 365
 366                 $result = array();
 367                 foreach ( $res as $row ) {
 368                         if ( $row->index_name == $index ) {
 369                                 $row->Non_unique = !stristr( $row->index_description, "unique" );
 370                                 $cols = explode( ", ", $row->index_keys );
 371                                 foreach ( $cols as $col ) {
 372                                         $row->Column_name = trim( $col );
 373                                         $result[] = clone $row;
 374                                 }
 375                         } elseif ( $index == 'PRIMARY' && stristr( $row->index_description, 'PRIMARY' ) ) {
 376                                 $row->Non_unique = 0;
 377                                 $cols = explode( ", ", $row->index_keys );
 378                                 foreach ( $cols as $col ) {
 379                                         $row->Column_name = trim( $col );
 380                                         $result[] = clone $row;
 381                                 }
 382                         }
 383                 }
 384                 return empty( $result ) ? false : $result;
 385         }
 386
 387         /**
 388          * INSERT wrapper, inserts an array into a table
 389          *
 390          * $arrToInsert may be a single associative array, or an array of these with numeric keys, for
 391          * multi-row insert.
 392          *
 393          * Usually aborts on failure
 394          * If errors are explicitly ignored, returns success
 395          * @param string $table
 396          * @param array $arrToInsert
 397          * @param string $fname
 398          * @param array $options
 399          * @throws DBQueryError
 400          * @return bool
 401          */
 402         function insert( $table, $arrToInsert, $fname = 'DatabaseMssql::insert', $options = array() ) {
 403                 # No rows to insert, easy just return now
 404                 if ( !count( $arrToInsert ) ) {
 405                         return true;
 406                 }
 407
 408                 if ( !is_array( $options ) ) {
 409                         $options = array( $options );
 410                 }
 411
 412                 $table = $this->tableName( $table );
 413
 414                 if ( !( isset( $arrToInsert[0] ) && is_array( $arrToInsert[0] ) ) ) {// Not multi row
 415                         $arrToInsert = array( 0 => $arrToInsert );// make everything multi row compatible
 416                 }
 417
 418                 $allOk = true;
 419
 420                 // We know the table we're inserting into, get its identity column
 421                 $identity = null;
 422                 $tableRaw = preg_replace( '#\[([^\]]*)\]#', '$1', $table ); // strip matching square brackets from table name
 423                 $res = $this->doQuery( "SELECT NAME AS idColumn FROM SYS.IDENTITY_COLUMNS WHERE OBJECT_NAME(OBJECT_ID)='{$tableRaw}'" );
 424                 if( $res && $res->numrows() ) {
 425                         // There is an identity for this table.
 426                         $identity = array_pop( $res->fetch( SQLSRV_FETCH_ASSOC ) );
 427                 }
 428                 unset( $res );
 429
 430                 foreach ( $arrToInsert as $a ) {
 431                         // start out with empty identity column, this is so we can return it as a result of the insert logic
 432                         $sqlPre = '';
 433                         $sqlPost = '';
 434                         $identityClause = '';
 435
 436                         // if we have an identity column
 437                         if( $identity ) {
 438                                 // iterate through
 439                                 foreach ( $a as $k => $v ) {
 440                                         if ( $k == $identity ) {
 441                                                 if( !is_null( $v ) ) {
 442                                                         // there is a value being passed to us, we need to turn on and off inserted identity
 443                                                         $sqlPre = "SET IDENTITY_INSERT $table ON;";
 444                                                         $sqlPost = ";SET IDENTITY_INSERT $table OFF;";
 445
 446                                                 } else {
 447                                                         // we can't insert NULL into an identity column, so remove the column from the insert.
 448                                                         unset( $a[$k] );
 449                                                 }
 450                                         }
 451                                 }
 452                                 $identityClause = "OUTPUT INSERTED.$identity "; // we want to output an identity column as result
 453                         }
 454
 455                         $keys = array_keys( $a );
 456
 457                         // INSERT IGNORE is not supported by SQL Server
 458                         // remove IGNORE from options list and set ignore flag to true
 459                         $ignoreClause = false;
 460                         foreach ( $options as $k => $v ) {
 461                                 if ( strtoupper( $v ) == "IGNORE" ) {
 462                                         unset( $options[$k] );
 463                                         $ignoreClause = true;
 464                                 }
 465                         }
 466
 467                         // translate MySQL INSERT IGNORE to something SQL Server can use
 468                         // example:
 469                         // MySQL: INSERT IGNORE INTO user_groups (ug_user,ug_group) VALUES ('1','sysop')
 470                         // MSSQL: IF NOT EXISTS (SELECT * FROM user_groups WHERE ug_user = '1') INSERT INTO user_groups (ug_user,ug_group) VALUES ('1','sysop')
 471                         if ( $ignoreClause ) {
 472                                 $prival = $a[$keys[0]];
 473                                 $sqlPre .= "IF NOT EXISTS (SELECT * FROM $table WHERE $keys[0] = '$prival')";
 474                         }
 475
 476                         // Build the actual query
 477                         $sql = $sqlPre . 'INSERT ' . implode( ' ', $options ) .
 478                                 " INTO $table (" . implode( ',', $keys ) . ") $identityClause VALUES (";
 479
 480                         $first = true;
 481                         foreach ( $a as $value ) {
 482                                 if ( $first ) {
 483                                         $first = false;
 484                                 } else {
 485                                         $sql .= ',';
 486                                 }
 487                                 if ( is_string( $value ) ) {
 488                                         $sql .= $this->addQuotes( $value );
 489                                 } elseif ( is_null( $value ) ) {
 490                                         $sql .= 'null';
 491                                 } elseif ( is_array( $value ) || is_object( $value ) ) {
 492                                         if ( is_object( $value ) && strtolower( get_class( $value ) ) == 'blob' ) {
 493                                                 $sql .= $this->addQuotes( $value );
 494                                         } else {
 495                                                 $sql .= $this->addQuotes( serialize( $value ) );
 496                                         }
 497                                 } else {
 498                                         $sql .= $value;
 499                                 }
 500                         }
 501                         $sql .= ')' . $sqlPost;
 502
 503                         // Run the query
 504                         $ret = sqlsrv_query( $this->mConn, $sql );
 505
 506                         if ( $ret === false ) {
 507                                 throw new DBQueryError( $this, $this->getErrors(), $this->lastErrno(), $sql, $fname );
 508                         } elseif ( $ret != null ) {
 509                                 // remember number of rows affected
 510                                 $this->mAffectedRows = sqlsrv_rows_affected( $ret );
 511                                 if ( !is_null( $identity ) ) {
 512                                         // then we want to get the identity column value we were assigned and save it off
 513                                         $row = sqlsrv_fetch_object( $ret );
 514                                         $this->mInsertId = $row->$identity;
 515                                 }
 516                                 sqlsrv_free_stmt( $ret );
 517                                 continue;
 518                         }
 519                         $allOk = false;
 520                 }
 521                 return $allOk;
 522         }
 523
 524         /**
 525          * INSERT SELECT wrapper
 526          * $varMap must be an associative array of the form array( 'dest1' => 'source1', ...)
 527          * Source items may be literals rather than field names, but strings should be quoted with Database::addQuotes()
 528          * $conds may be "*" to copy the whole table
 529          * srcTable may be an array of tables.
 530          * @param string $destTable
 531          * @param array|string $srcTable
 532          * @param array $varMap
 533          * @param array $conds
 534          * @param string $fname
 535          * @param array $insertOptions
 536          * @param array $selectOptions
 537          * @throws DBQueryError
 538          * @return null|ResultWrapper
 539          */
 540         function insertSelect( $destTable, $srcTable, $varMap, $conds, $fname = 'DatabaseMssql::insertSelect',
 541                 $insertOptions = array(), $selectOptions = array() ) {
 542                 $ret = parent::insertSelect( $destTable, $srcTable, $varMap, $conds, $fname, $insertOptions, $selectOptions );
 543
 544                 if ( $ret === false ) {
 545                         throw new DBQueryError( $this, $this->getErrors(), $this->lastErrno(), /*$sql*/ '', $fname );
 546                 } elseif ( $ret != null ) {
 547                         // remember number of rows affected
 548                         $this->mAffectedRows = sqlsrv_rows_affected( $ret );
 549                         return $ret;
 550                 }
 551                 return null;
 552         }
 553
 554         /**
 555          * Return the next in a sequence, save the value for retrieval via insertId()
 556          * @return
 557          */
 558         function nextSequenceValue( $seqName ) {
 559                 if ( !$this->tableExists( 'sequence_' . $seqName ) ) {
 560                         sqlsrv_query( $this->mConn, "CREATE TABLE [sequence_$seqName] (id INT NOT NULL IDENTITY PRIMARY KEY, junk varchar(10) NULL)" );
 561                 }
 562                 sqlsrv_query( $this->mConn, "INSERT INTO [sequence_$seqName] (junk) VALUES ('')" );
 563                 $ret = sqlsrv_query( $this->mConn, "SELECT TOP 1 id FROM [sequence_$seqName] ORDER BY id DESC" );
 564                 $row = sqlsrv_fetch_array( $ret, SQLSRV_FETCH_ASSOC );// KEEP ASSOC THERE, weird weird bug dealing with the return value if you don't
 565
 566                 sqlsrv_free_stmt( $ret );
 567                 $this->mInsertId = $row['id'];
 568                 return $row['id'];
 569         }
 570
 571         /**
 572          * Return the current value of a sequence. Assumes it has ben nextval'ed in this session.
 573          * @return
 574          */
 575         function currentSequenceValue( $seqName ) {
 576                 $ret = sqlsrv_query( $this->mConn, "SELECT TOP 1 id FROM [sequence_$seqName] ORDER BY id DESC" );
 577                 if ( $ret !== false ) {
 578                         $row = sqlsrv_fetch_array( $ret );
 579                         sqlsrv_free_stmt( $ret );
 580                         return $row['id'];
 581                 } else {
 582                         return $this->nextSequenceValue( $seqName );
 583                 }
 584         }
 585
 586         # Returns the size of a text field, or -1 for "unlimited"
 587         function textFieldSize( $table, $field ) {
 588                 $table = $this->tableName( $table );
 589                 $sql = "SELECT CHARACTER_MAXIMUM_LENGTH,DATA_TYPE FROM INFORMATION_SCHEMA.Columns
 590                         WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'";
 591                 $res = $this->query( $sql );
 592                 $row = $this->fetchRow( $res );
 593                 $size = -1;
 594                 if ( strtolower( $row['DATA_TYPE'] ) != 'text' ) {
 595                         $size = $row['CHARACTER_MAXIMUM_LENGTH'];
 596                 }
 597                 return $size;
 598         }
 599
 600         /**
 601          * Construct a LIMIT query with optional offset
 602          * This is used for query pages
 603          * $sql string SQL query we will append the limit too
 604          * $limit integer the SQL limit
 605          * $offset integer the SQL offset (default false)
 606          * @return mixed|string
 607          */
 608         function limitResult( $sql, $limit, $offset = false ) {
 609                 if ( $offset === false || $offset == 0 ) {
 610                         if ( strpos( $sql, "SELECT" ) === false ) {
 611                                 return "TOP {$limit} " . $sql;
 612                         } else {
 613                                 return preg_replace( '/\bSELECT(\s*DISTINCT)?\b/Dsi', 'SELECT$1 TOP ' . $limit, $sql, 1 );
 614                         }
 615                 } else {
 616                         $sql = '
 617                                 SELECT * FROM (
 618                                         SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3 FROM (
 619                                                 SELECT 1 AS line2, sub1.* FROM (' . $sql . ') AS sub1
 620                                         ) as sub2
 621                                 ) AS sub3
 622                                 WHERE line3 BETWEEN ' . ( $offset + 1 ) . ' AND ' . ( $offset + $limit );
 623                         return $sql;
 624                 }
 625         }
 626
 627         // If there is a limit clause, parse it, strip it, and pass the remaining sql through limitResult()
 628         // with the appropriate parameters. Not the prettiest solution, but better than building a whole new parser.
 629         // This exists becase there are still too many extensions that don't use dynamic sql generation.
 630         function LimitToTopN( $sql ) {
 631                 // Matches: LIMIT {[offset,] row_count | row_count OFFSET offset}
 632                 $pattern = '/\bLIMIT\s+((([0-9]+)\s*,\s*)?([0-9]+)(\s+OFFSET\s+([0-9]+))?)/i';
 633                 if ( preg_match( $pattern, $sql, $matches ) ) {
 634                         // row_count = $matches[4]
 635                         $row_count = $matches[4];
 636                         // offset = $matches[3] OR $matches[6]
 637                         $offset = $matches[3] or
 638                                 $offset = $matches[6] or
 639                                 $offset = false;
 640
 641                         // strip the matching LIMIT clause out
 642                         $sql = str_replace( $matches[0], '', $sql );
 643                         return $this->limitResult( $sql, $row_count, $offset );
 644                 }
 645                 return $sql;
 646         }
 647
 648         function timestamp( $ts = 0 ) {
 649                 return wfTimestamp( TS_ISO_8601, $ts );
 650         }
 651
 652         /**
 653          * @return string wikitext of a link to the server software's web site
 654          */
 655         public static function getSoftwareLink() {
 656                 return "[http://www.microsoft.com/sql/ MS SQL Server]";
 657         }
 658
 659         /**
 660          * @return string Version information from the database
 661          */
 662         function getServerVersion() {
 663                 $server_info = sqlsrv_server_info( $this->mConn );
 664                 $version = 'Error';
 665                 if ( isset( $server_info['SQLServerVersion'] ) ) {
 666                         $version = $server_info['SQLServerVersion'];
 667                 }
 668                 return $version;
 669         }
 670
 671         function tableExists( $table, $fname = __METHOD__, $schema = false ) {
 672                 $res = sqlsrv_query( $this->mConn, "SELECT * FROM information_schema.tables
 673                         WHERE table_type='BASE TABLE' AND table_name = '$table'" );
 674                 if ( $res === false ) {
 675                         print( "Error in tableExists query: " . $this->getErrors() );
 676                         return false;
 677                 }
 678                 if ( sqlsrv_fetch( $res ) ) {
 679                         return true;
 680                 } else {
 681                         return false;
 682                 }
 683         }
 684
 685         /**
 686          * Query whether a given column exists in the mediawiki schema
 687          * @return bool
 688          */
 689         function fieldExists( $table, $field, $fname = 'DatabaseMssql::fieldExists' ) {
 690                 $table = $this->tableName( $table );
 691                 $res = sqlsrv_query( $this->mConn, "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.Columns
 692                         WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" );
 693                 if ( $res === false ) {
 694                         print( "Error in fieldExists query: " . $this->getErrors() );
 695                         return false;
 696                 }
 697                 if ( sqlsrv_fetch( $res ) ) {
 698                         return true;
 699                 } else {
 700                         return false;
 701                 }
 702         }
 703
 704         function fieldInfo( $table, $field ) {
 705                 $table = $this->tableName( $table );
 706                 $res = sqlsrv_query( $this->mConn, "SELECT * FROM INFORMATION_SCHEMA.Columns
 707                         WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" );
 708                 if ( $res === false ) {
 709                         print( "Error in fieldInfo query: " . $this->getErrors() );
 710                         return false;
 711                 }
 712                 $meta = $this->fetchRow( $res );
 713                 if ( $meta ) {
 714                         return new MssqlField( $meta );
 715                 }
 716                 return false;
 717         }
 718
 719         /**
 720          * Begin a transaction, committing any previously open transaction
 721          */
 722         protected function doBegin( $fname = 'DatabaseMssql::begin' ) {
 723                 sqlsrv_begin_transaction( $this->mConn );
 724                 $this->mTrxLevel = 1;
 725         }
 726
 727         /**
 728          * End a transaction
 729          */
 730         protected function doCommit( $fname = 'DatabaseMssql::commit' ) {
 731                 sqlsrv_commit( $this->mConn );
 732                 $this->mTrxLevel = 0;
 733         }
 734
 735         /**
 736          * Rollback a transaction.
 737          * No-op on non-transactional databases.
 738          */
 739         protected function doRollback( $fname = 'DatabaseMssql::rollback' ) {
 740                 sqlsrv_rollback( $this->mConn );
 741                 $this->mTrxLevel = 0;
 742         }
 743
 744         /**
 745          * Escapes a identifier for use inm SQL.
 746          * Throws an exception if it is invalid.
 747          * Reference: http://msdn.microsoft.com/en-us/library/aa224033%28v=SQL.80%29.aspx
 748          * @param $identifier
 749          * @throws MWException
 750          * @return string
 751          */
 752         private function escapeIdentifier( $identifier ) {
 753                 if ( strlen( $identifier ) == 0 ) {
 754                         throw new MWException( "An identifier must not be empty" );
 755                 }
 756                 if ( strlen( $identifier ) > 128 ) {
 757                         throw new MWException( "The identifier '$identifier' is too long (max. 128)" );
 758                 }
 759                 if ( ( strpos( $identifier, '[' ) !== false ) || ( strpos( $identifier, ']' ) !== false ) ) {
 760                         // It may be allowed if you quoted with double quotation marks, but that would break if QUOTED_IDENTIFIER is OFF
 761                         throw new MWException( "You can't use square brackers in the identifier '$identifier'" );
 762                 }
 763                 return "[$identifier]";
 764         }
 765
 766         /**
 767          * Initial setup.
 768          * Precondition: This object is connected as the superuser.
 769          * Creates the database, schema, user and login.
 770          */
 771         function initial_setup( $dbName, $newUser, $loginPassword ) {
 772                 $dbName = $this->escapeIdentifier( $dbName );
 773
 774                 // It is not clear what can be used as a login,
 775                 // From http://msdn.microsoft.com/en-us/library/ms173463.aspx
 776                 // a sysname may be the same as an identifier.
 777                 $newUser = $this->escapeIdentifier( $newUser );
 778                 $loginPassword = $this->addQuotes( $loginPassword );
 779
 780                 $this->doQuery( "CREATE DATABASE $dbName;" );
 781                 $this->doQuery( "USE $dbName;" );
 782                 $this->doQuery( "CREATE SCHEMA $dbName;" );
 783                 $this->doQuery( "
 784                                                 CREATE
 785                                                         LOGIN $newUser
 786                                                 WITH
 787                                                         PASSWORD=$loginPassword
 788                                                 ;
 789                                         " );
 790                 $this->doQuery( "
 791                                                 CREATE
 792                                                         USER $newUser
 793                                                 FOR
 794                                                         LOGIN $newUser
 795                                                 WITH
 796                                                         DEFAULT_SCHEMA=$dbName
 797                                                 ;
 798                                         " );
 799                 $this->doQuery( "
 800                                                 GRANT
 801                                                         BACKUP DATABASE,
 802                                                         BACKUP LOG,
 803                                                         CREATE DEFAULT,
 804                                                         CREATE FUNCTION,
 805                                                         CREATE PROCEDURE,
 806                                                         CREATE RULE,
 807                                                         CREATE TABLE,
 808                                                         CREATE VIEW,
 809                                                         CREATE FULLTEXT CATALOG
 810                                                 ON
 811                                                         DATABASE::$dbName
 812                                                 TO $newUser
 813                                                 ;
 814                                         " );
 815                 $this->doQuery( "
 816                                                 GRANT
 817                                                         CONTROL
 818                                                 ON
 819                                                         SCHEMA::$dbName
 820                                                 TO $newUser
 821                                                 ;
 822                                         " );
 823         }
 824
 825         function encodeBlob( $b ) {
 826         // we can't have zero's and such, this is a simple encoding to make sure we don't barf
 827                 return base64_encode( $b );
 828         }
 829
 830         function decodeBlob( $b ) {
 831         // we can't have zero's and such, this is a simple encoding to make sure we don't barf
 832         return base64_decode( $b );
 833         }
 834
 835         /**
 836          * @private
 837          * @return string
 838          */
 839         function tableNamesWithUseIndexOrJOIN( $tables, $use_index = array(), $join_conds = array() ) {
 840                 $ret = array();
 841                 $retJOIN = array();
 842                 $use_index_safe = is_array( $use_index ) ? $use_index : array();
 843                 $join_conds_safe = is_array( $join_conds ) ? $join_conds : array();
 844                 foreach ( $tables as $table ) {
 845                         // Is there a JOIN and INDEX clause for this table?
 846                         if ( isset( $join_conds_safe[$table] ) && isset( $use_index_safe[$table] ) ) {
 847                                 $tableClause = $join_conds_safe[$table][0] . ' ' . $this->tableName( $table );
 848                                 $tableClause .= ' ' . $this->useIndexClause( implode( ',', (array)$use_index_safe[$table] ) );
 849                                 $tableClause .= ' ON (' . $this->makeList( (array)$join_conds_safe[$table][1], LIST_AND ) . ')';
 850                                 $retJOIN[] = $tableClause;
 851                         // Is there an INDEX clause?
 852                         } elseif ( isset( $use_index_safe[$table] ) ) {
 853                                 $tableClause = $this->tableName( $table );
 854                                 $tableClause .= ' ' . $this->useIndexClause( implode( ',', (array)$use_index_safe[$table] ) );
 855                                 $ret[] = $tableClause;
 856                         // Is there a JOIN clause?
 857                         } elseif ( isset( $join_conds_safe[$table] ) ) {
 858                                 $tableClause = $join_conds_safe[$table][0] . ' ' . $this->tableName( $table );
 859                                 $tableClause .= ' ON (' . $this->makeList( (array)$join_conds_safe[$table][1], LIST_AND ) . ')';
 860                                 $retJOIN[] = $tableClause;
 861                         } else {
 862                                 $tableClause = $this->tableName( $table );
 863                                 $ret[] = $tableClause;
 864                         }
 865                 }
 866                 // We can't separate explicit JOIN clauses with ',', use ' ' for those
 867                 $straightJoins = !empty( $ret ) ? implode( ',', $ret ) : "";
 868                 $otherJoins = !empty( $retJOIN ) ? implode( ' ', $retJOIN ) : "";
 869                 // Compile our final table clause
 870                 return implode( ' ', array( $straightJoins, $otherJoins ) );
 871         }
 872
 873         function strencode( $s ) { # Should not be called by us
 874                 return str_replace( "'", "''", $s );
 875         }
 876
 877         function addQuotes( $s ) {
 878                 if ( $s instanceof Blob ) {
 879                         return "'" . $s->fetch( $s ) . "'";
 880                 } else {
 881                         return parent::addQuotes( $s );
 882                 }
 883         }
 884
 885         public function addIdentifierQuotes( $s ) {
 886                 // http://msdn.microsoft.com/en-us/library/aa223962.aspx
 887                 return '[' . $s . ']';
 888         }
 889
 890         public function isQuotedIdentifier( $name ) {
 891                 return $name[0] == '[' && substr( $name, -1, 1 ) == ']';
 892         }
 893
 894         function selectDB( $db ) {
 895                 return ( $this->query( "SET DATABASE $db" ) !== false );
 896         }
 897
 898         /**
 899          * @private
 900          *
 901          * @param array $options an associative array of options to be turned into
 902          *                 an SQL query, valid keys are listed in the function.
 903          * @return Array
 904          */
 905         function makeSelectOptions( $options ) {
 906                 $tailOpts = '';
 907                 $startOpts = '';
 908
 909                 $noKeyOptions = array();
 910                 foreach ( $options as $key => $option ) {
 911                         if ( is_numeric( $key ) ) {
 912                                 $noKeyOptions[$option] = true;
 913                         }
 914                 }
 915
 916                 $tailOpts .= $this->makeGroupByWithHaving( $options );
 917
 918                 $tailOpts .= $this->makeOrderBy( $options );
 919
 920                 if ( isset( $noKeyOptions['DISTINCT'] ) && isset( $noKeyOptions['DISTINCTROW'] ) ) {
 921                         $startOpts .= 'DISTINCT';
 922                 }
 923
 924                 // we want this to be compatible with the output of parent::makeSelectOptions()
 925                 return array( $startOpts, '', $tailOpts, '' );
 926         }
 927
 928         /**
 929          * Get the type of the DBMS, as it appears in $wgDBtype.
 930          * @return string
 931          */
 932         function getType() {
 933                 return 'mssql';
 934         }
 935
 936         function buildConcat( $stringList ) {
 937                 return implode( ' + ', $stringList );
 938         }
 939
 940         public function getSearchEngine() {
 941                 return "SearchMssql";
 942         }
 943
 944         /**
 945          * Since MSSQL doesn't recognize the infinity keyword, set date manually.
 946          * @todo Remove magic date
 947          * @return string
 948          */
 949         public function getInfinity() {
 950                 return '3000-01-31 00:00:00.000';
 951         }
 952
 953 } // end DatabaseMssql class
 954
 955 /**
 956  * Utility class.
 957  *
 958  * @ingroup Database
 959  */
 960 class MssqlField implements Field {
 961         private $name, $tablename, $default, $max_length, $nullable, $type;
 962         function __construct( $info ) {
 963                 $this->name = $info['COLUMN_NAME'];
 964                 $this->tablename = $info['TABLE_NAME'];
 965                 $this->default = $info['COLUMN_DEFAULT'];
 966                 $this->max_length = $info['CHARACTER_MAXIMUM_LENGTH'];
 967                 $this->nullable = !( strtolower( $info['IS_NULLABLE'] ) == 'no' );
 968                 $this->type = $info['DATA_TYPE'];
 969         }
 970
 971         function name() {
 972                 return $this->name;
 973         }
 974
 975         function tableName() {
 976                 return $this->tableName;
 977         }
 978
 979         function defaultValue() {
 980                 return $this->default;
 981         }
 982
 983         function maxLength() {
 984                 return $this->max_length;
 985         }
 986
 987         function isNullable() {
 988                 return $this->nullable;
 989         }
 990
 991         function type() {
 992                 return $this->type;
 993         }
 994 }
 995
 996 /**
 997  * The MSSQL PHP driver doesn't support sqlsrv_num_rows, so we recall all rows into an array and maintain our
 998  * own cursor index into that array...This is similar to the way the Oracle driver handles this same issue
 999  *
1000  * @ingroup Database
1001  */
1002 class MssqlResult {
1003
1004         public function __construct( $queryresult = false ) {
1005                 $this->mCursor = 0;
1006                 $this->mRows = array();
1007                 $this->mNumFields = sqlsrv_num_fields( $queryresult );
1008                 $this->mFieldMeta = sqlsrv_field_metadata( $queryresult );
1009
1010                 $rows = sqlsrv_fetch_array( $queryresult, SQLSRV_FETCH_ASSOC );
1011
1012                 foreach( $rows as $row ) {
1013                         if ( $row !== null ) {
1014                                 foreach ( $row as $k => $v ) {
1015                                         if ( is_object( $v ) && method_exists( $v, 'format' ) ) {// DateTime Object
1016                                                 $row[$k] = $v->format( "Y-m-d\TH:i:s\Z" );
1017                                         }
1018                                 }
1019                                 $this->mRows[] = $row;// read results into memory, cursors are not supported
1020                         }
1021                 }
1022                 $this->mRowCount = count( $this->mRows );
1023                 sqlsrv_free_stmt( $queryresult );
1024         }
1025
1026         private function array_to_obj( $array, &$obj ) {
1027                 foreach ( $array as $key => $value ) {
1028                         if ( is_array( $value ) ) {
1029                                 $obj->$key = new stdClass();
1030                                 $this->array_to_obj( $value, $obj->$key );
1031                         } else {
1032                                 if ( !empty( $key ) ) {
1033                                         $obj->$key = $value;
1034                                 }
1035                         }
1036                 }
1037                 return $obj;
1038         }
1039
1040         public function fetch( $mode = SQLSRV_FETCH_BOTH, $object_class = 'stdClass' ) {
1041                 if ( $this->mCursor >= $this->mRowCount || $this->mRowCount == 0 ) {
1042                         return false;
1043                 }
1044                 $arrNum = array();
1045                 if ( $mode == SQLSRV_FETCH_NUMERIC || $mode == SQLSRV_FETCH_BOTH ) {
1046                         foreach ( $this->mRows[$this->mCursor] as $value ) {
1047                                 $arrNum[] = $value;
1048                         }
1049                 }
1050                 switch( $mode ) {
1051                         case SQLSRV_FETCH_ASSOC:
1052                                 $ret = $this->mRows[$this->mCursor];
1053                                 break;
1054                         case SQLSRV_FETCH_NUMERIC:
1055                                 $ret = $arrNum;
1056                                 break;
1057                         case 'OBJECT':
1058                                 $o = new $object_class;
1059                                 $ret = $this->array_to_obj( $this->mRows[$this->mCursor], $o );
1060                                 break;
1061                         case SQLSRV_FETCH_BOTH:
1062                         default:
1063                                 $ret = $this->mRows[$this->mCursor] + $arrNum;
1064                                 break;
1065                 }
1066
1067                 $this->mCursor++;
1068                 return $ret;
1069         }
1070
1071         public function get( $pos, $fld ) {
1072                 return $this->mRows[$pos][$fld];
1073         }
1074
1075         public function numrows() {
1076                 return $this->mRowCount;
1077         }
1078
1079         public function seek( $iRow ) {
1080                 $this->mCursor = min( $iRow, $this->mRowCount );
1081         }
1082
1083         public function numfields() {
1084                 return $this->mNumFields;
1085         }
1086
1087         public function fieldname( $nr ) {
1088                 $arrKeys = array_keys( $this->mRows[0] );
1089                 return $arrKeys[$nr];
1090         }
1091
1092         public function fieldtype( $nr ) {
1093                 $i = 0;
1094                 $intType = -1;
1095                 foreach ( $this->mFieldMeta as $meta ) {
1096                         if ( $nr == $i ) {
1097                                 $intType = $meta['Type'];
1098                                 break;
1099                         }
1100                         $i++;
1101                 }
1102                 // http://msdn.microsoft.com/en-us/library/cc296183.aspx contains type table
1103                 switch ( $intType ) {
1104                         case SQLSRV_SQLTYPE_BIGINT:
1105                                 $strType = 'bigint';
1106                                 break;
1107                         case SQLSRV_SQLTYPE_BINARY:
1108                                 $strType = 'binary';
1109                                 break;
1110                         case SQLSRV_SQLTYPE_BIT:
1111                                 $strType = 'bit';
1112                                 break;
1113                         case SQLSRV_SQLTYPE_CHAR:
1114                                 $strType = 'char';
1115                                 break;
1116                         case SQLSRV_SQLTYPE_DATETIME:
1117                                 $strType = 'datetime';
1118                                 break;
1119                         case SQLSRV_SQLTYPE_DECIMAL: // ($precision, $scale)
1120                                 $strType = 'decimal';
1121                                 break;
1122                         case SQLSRV_SQLTYPE_FLOAT:
1123                                 $strType = 'float';
1124                                 break;
1125                         case SQLSRV_SQLTYPE_IMAGE:
1126                                 $strType = 'image';
1127                                 break;
1128                         case SQLSRV_SQLTYPE_INT:
1129                                 $strType = 'int';
1130                                 break;
1131                         case SQLSRV_SQLTYPE_MONEY:
1132                                 $strType = 'money';
1133                                 break;
1134                         case SQLSRV_SQLTYPE_NCHAR: // ($charCount):
1135                                 $strType = 'nchar';
1136                                 break;
1137                         case SQLSRV_SQLTYPE_NUMERIC: // ($precision, $scale):
1138                                 $strType = 'numeric';
1139                                 break;
1140                         case SQLSRV_SQLTYPE_NVARCHAR: // ($charCount)
1141                                 $strType = 'nvarchar';
1142                                 break;
1143                         // case SQLSRV_SQLTYPE_NVARCHAR('max'):
1144                         //       $strType = 'nvarchar(MAX)';
1145                         //       break;
1146                         case SQLSRV_SQLTYPE_NTEXT:
1147                                 $strType = 'ntext';
1148                                 break;
1149                         case SQLSRV_SQLTYPE_REAL:
1150                                 $strType = 'real';
1151                                 break;
1152                         case SQLSRV_SQLTYPE_SMALLDATETIME:
1153                                 $strType = 'smalldatetime';
1154                                 break;
1155                         case SQLSRV_SQLTYPE_SMALLINT:
1156                                 $strType = 'smallint';
1157                                 break;
1158                         case SQLSRV_SQLTYPE_SMALLMONEY:
1159                                 $strType = 'smallmoney';
1160                                 break;
1161                         case SQLSRV_SQLTYPE_TEXT:
1162                                 $strType = 'text';
1163                                 break;
1164                         case SQLSRV_SQLTYPE_TIMESTAMP:
1165                                 $strType = 'timestamp';
1166                                 break;
1167                         case SQLSRV_SQLTYPE_TINYINT:
1168                                 $strType = 'tinyint';
1169                                 break;
1170                         case SQLSRV_SQLTYPE_UNIQUEIDENTIFIER:
1171                                 $strType = 'uniqueidentifier';
1172                                 break;
1173                         case SQLSRV_SQLTYPE_UDT:
1174                                 $strType = 'UDT';
1175                                 break;
1176                         case SQLSRV_SQLTYPE_VARBINARY: // ($byteCount)
1177                                 $strType = 'varbinary';
1178                                 break;
1179                         // case SQLSRV_SQLTYPE_VARBINARY('max'):
1180                         //       $strType = 'varbinary(MAX)';
1181                         //       break;
1182                         case SQLSRV_SQLTYPE_VARCHAR: // ($charCount)
1183                                 $strType = 'varchar';
1184                                 break;
1185                         // case SQLSRV_SQLTYPE_VARCHAR('max'):
1186                         //       $strType = 'varchar(MAX)';
1187                         //       break;
1188                         case SQLSRV_SQLTYPE_XML:
1189                                 $strType = 'xml';
1190                                 break;
1191                         default:
1192                                 $strType = $intType;
1193                 }
1194                 return $strType;
1195         }
1196
1197         public function free() {
1198                 unset( $this->mRows );
1199         }
1200 }