From 9db190c7e736ec8d063187d4241b59feaf7dc2d1 Mon Sep 17 00:00:00 2001 From: Pierre Schmitz Date: Wed, 22 Jun 2011 11:28:20 +0200 Subject: update to MediaWiki 1.17.0 --- includes/db/DatabaseMssql.php | 1684 ++++++++++++++++++++++++----------------- 1 file changed, 971 insertions(+), 713 deletions(-) (limited to 'includes/db/DatabaseMssql.php') diff --git a/includes/db/DatabaseMssql.php b/includes/db/DatabaseMssql.php index 6b1206b0..41ba2d08 100644 --- a/includes/db/DatabaseMssql.php +++ b/includes/db/DatabaseMssql.php @@ -1,968 +1,1226 @@ + * @author Chris Pucci + * @author Ryan Biesemeyer */ /** * @ingroup Database */ class DatabaseMssql extends DatabaseBase { + var $mInsertId = NULL; + var $mLastResult = NULL; + var $mAffectedRows = NULL; - var $mAffectedRows; - var $mLastResult; - var $mLastError; - var $mLastErrorNo; - var $mDatabaseFile; - - /** - * Constructor - */ - function __construct($server = false, $user = false, $password = false, $dbName = false, - $failFunction = false, $flags = 0, $tablePrefix = 'get from global') { - - global $wgOut, $wgDBprefix, $wgCommandLineMode; - if (!isset($wgOut)) $wgOut = null; # Can't get a reference if it hasn't been set yet - $this->mOut =& $wgOut; - $this->mFailFunction = $failFunction; - $this->mFlags = $flags; - - if ( $this->mFlags & DBO_DEFAULT ) { - if ( $wgCommandLineMode ) { - $this->mFlags &= ~DBO_TRX; - } else { - $this->mFlags |= DBO_TRX; - } - } - - /** Get the default table prefix*/ - $this->mTablePrefix = $tablePrefix == 'get from global' ? $wgDBprefix : $tablePrefix; - - if ($server) $this->open($server, $user, $password, $dbName); - + function cascadingDeletes() { + return true; } - - function getType() { - return 'mssql'; + function cleanupTriggers() { + return true; + } + function strictIPs() { + return true; + } + function realTimestamps() { + return true; + } + function implicitGroupby() { + return false; + } + function implicitOrderby() { + return false; + } + function functionalIndexes() { + return true; + } + function unionSupportsOrderAndLimit() { + return false; } - /** - * todo: check if these should be true like parent class - */ - function implicitGroupby() { return false; } - function implicitOrderby() { return false; } - - static function newFromParams($server, $user, $password, $dbName, $failFunction = false, $flags = 0) { - return new DatabaseMssql($server, $user, $password, $dbName, $failFunction, $flags); + static function newFromParams( $server, $user, $password, $dbName, $flags = 0 ) { + return new DatabaseMssql( $server, $user, $password, $dbName, $flags ); } - /** Open an MSSQL database and return a resource handle to it - * NOTE: only $dbName is used, the other parameters are irrelevant for MSSQL databases + /** + * Usually aborts on failure */ - function open($server,$user,$password,$dbName) { - wfProfileIn(__METHOD__); - - # Test for missing mysql.so - # First try to load it - if (!@extension_loaded('mssql')) { - @dl('mssql.so'); + function open( $server, $user, $password, $dbName ) { + # Test for driver support, to avoid suppressed fatal error + if ( !function_exists( 'sqlsrv_connect' ) ) { + 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" ); } - # Fail now - # Otherwise we get a suppressed fatal error, which is very hard to track down - if (!function_exists( 'mssql_connect')) { - throw new DBConnectionError( $this, "MSSQL functions missing, have you compiled PHP with the --with-mssql option?\n" ); + global $wgDBport; + + if ( !strlen( $user ) ) { # e.g. the class is being loaded + return; } $this->close(); - $this->mServer = $server; - $this->mUser = $user; + $this->mServer = $server; + $this->mPort = $wgDBport; + $this->mUser = $user; $this->mPassword = $password; - $this->mDBname = $dbName; - - wfProfileIn("dbconnect-$server"); - - # Try to connect up to three times - # The kernel's default SYN retransmission period is far too slow for us, - # so we use a short timeout plus a manual retry. - $this->mConn = false; - $max = 3; - for ( $i = 0; $i < $max && !$this->mConn; $i++ ) { - if ( $i > 1 ) { - usleep( 1000 ); - } - if ($this->mFlags & DBO_PERSISTENT) { - @/**/$this->mConn = mssql_pconnect($server, $user, $password); - } else { - # Create a new connection... - @/**/$this->mConn = mssql_connect($server, $user, $password, true); - } + $this->mDBname = $dbName; + + $connectionInfo = array(); + + if( $dbName ) { + $connectionInfo['Database'] = $dbName; } - - wfProfileOut("dbconnect-$server"); - - if ($dbName != '') { - if ($this->mConn !== false) { - $success = @/**/mssql_select_db($dbName, $this->mConn); - if (!$success) { - $error = "Error selecting database $dbName on server {$this->mServer} " . - "from client host " . wfHostname() . "\n"; - wfLogDBError(" Error selecting database $dbName on server {$this->mServer} \n"); - wfDebug( $error ); - } - } else { - wfDebug("DB connection error\n"); - wfDebug("Server: $server, User: $user, Password: ".substr($password, 0, 3)."...\n"); - $success = false; - } + + // Start NT Auth Hack + // Quick and dirty work around to provide NT Auth designation support. + // Current solution requires installer to know to input 'ntauth' for both username and password + // to trigger connection via NT Auth. - ugly, ugly, ugly + // TO-DO: Make this better and add NT Auth choice to MW installer when SQL Server option is chosen. + $ntAuthUserTest = strtolower( $user ); + $ntAuthPassTest = strtolower( $password ); + + // Decide which auth scenerio to use + if( ( $ntAuthPassTest == 'ntauth' && $ntAuthUserTest == 'ntauth' ) ){ + // Don't add credentials to $connectionInfo } else { - # Delay USE query - $success = (bool)$this->mConn; + $connectionInfo['UID'] = $user; + $connectionInfo['PWD'] = $password; + } + // End NT Auth Hack + + $this->mConn = @sqlsrv_connect( $server, $connectionInfo ); + + if ( $this->mConn === false ) { + wfDebug( "DB connection error\n" ); + wfDebug( "Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n" ); + wfDebug( $this->lastError() . "\n" ); + return false; } - if (!$success) $this->reportConnectionError(); - $this->mOpened = $success; - wfProfileOut(__METHOD__); - return $success; + $this->mOpened = true; + return $this->mConn; } /** - * Close an MSSQL database + * Closes a database connection, if it is open + * Returns success, true if already closed */ function close() { $this->mOpened = false; - if ($this->mConn) { - if ($this->trxLevel()) $this->commit(); - return mssql_close($this->mConn); - } else return true; + if ( $this->mConn ) { + return sqlsrv_close( $this->mConn ); + } else { + return true; + } } - /** - * - MSSQL doesn't seem to do buffered results - * - the trasnaction syntax is modified here to avoid having to replicate - * Database::query which uses BEGIN, COMMIT, ROLLBACK - */ - function doQuery($sql) { - if ($sql == 'BEGIN' || $sql == 'COMMIT' || $sql == 'ROLLBACK') return true; # $sql .= ' TRANSACTION'; - $sql = preg_replace('|[^\x07-\x7e]|','?',$sql); # TODO: need to fix unicode - just removing it here while testing - $ret = mssql_query($sql, $this->mConn); - if ($ret === false) { - $err = mssql_get_last_message(); - if ($err) $this->mlastError = $err; - $row = mssql_fetch_row(mssql_query('select @@ERROR')); - if ($row[0]) $this->mlastErrorNo = $row[0]; - } else $this->mlastErrorNo = false; - return $ret; + function doQuery( $sql ) { + wfDebug( "SQL: [$sql]\n" ); + $this->offset = 0; + + // several extensions seem to think that all databases support limits via LIMIT N after the WHERE clause + // well, MSSQL uses SELECT TOP N, so to catch any of those extensions we'll do a quick check for a LIMIT + // clause and pass $sql through $this->LimitToTopN() which parses the limit clause and passes the result to + // $this->limitResult(); + if ( preg_match( '/\bLIMIT\s*/i', $sql ) ) { + // massage LIMIT -> TopN + $sql = $this->LimitToTopN( $sql ) ; + } + + // MSSQL doesn't have EXTRACT(epoch FROM XXX) + if ( preg_match('#\bEXTRACT\s*?\(\s*?EPOCH\s+FROM\b#i', $sql, $matches ) ) { + // This is same as UNIX_TIMESTAMP, we need to calc # of seconds from 1970 + $sql = str_replace( $matches[0], "DATEDIFF(s,CONVERT(datetime,'1/1/1970'),", $sql ); + } + + // perform query + $stmt = sqlsrv_query( $this->mConn, $sql ); + if ( $stmt == false ) { + $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" . + "Query: " . htmlentities( $sql ) . "\n" . + "Function: " . __METHOD__ . "\n"; + // process each error (our driver will give us an array of errors unlike other providers) + foreach ( sqlsrv_errors() as $error ) { + $message .= $message . "ERROR[" . $error['code'] . "] " . $error['message'] . "\n"; + } + + throw new DBUnexpectedError( $this, $message ); + } + // remember number of rows affected + $this->mAffectedRows = sqlsrv_rows_affected( $stmt ); + + // if it is a SELECT statement, or an insert with a request to output something we want to return a row. + if ( ( preg_match( '#\bSELECT\s#i', $sql ) ) || + ( preg_match( '#\bINSERT\s#i', $sql ) && preg_match( '#\bOUTPUT\s+INSERTED\b#i', $sql ) ) ) { + // this is essentially a rowset, but Mediawiki calls these 'result' + // the rowset owns freeing the statement + $res = new MssqlResult( $stmt ); + } else { + // otherwise we simply return it was successful, failure throws an exception + $res = true; + } + return $res; } - /** - * Free a result object - */ function freeResult( $res ) { if ( $res instanceof ResultWrapper ) { $res = $res->result; } - if ( !@/**/mssql_free_result( $res ) ) { - throw new DBUnexpectedError( $this, "Unable to free MSSQL result" ); - } + $res->free(); } - /** - * Fetch the next row from the given result object, in object form. - * Fields can be retrieved with $row->fieldname, with fields acting like - * member variables. - * - * @param $res SQL result object as returned from Database::query(), etc. - * @return MySQL row object - * @throws DBUnexpectedError Thrown if the database returns an error - */ function fetchObject( $res ) { if ( $res instanceof ResultWrapper ) { $res = $res->result; } - @/**/$row = mssql_fetch_object( $res ); - if ( $this->lastErrno() ) { - throw new DBUnexpectedError( $this, 'Error in fetchObject(): ' . htmlspecialchars( $this->lastError() ) ); - } + $row = $res->fetch( 'OBJECT' ); return $row; } - /** - * Fetch the next row from the given result object, in associative array - * form. Fields are retrieved with $row['fieldname']. - * - * @param $res SQL result object as returned from Database::query(), etc. - * @return MySQL row object - * @throws DBUnexpectedError Thrown if the database returns an error - */ - function fetchRow( $res ) { + function getErrors() { + $strRet = ''; + $retErrors = sqlsrv_errors( SQLSRV_ERR_ALL ); + if ( $retErrors != null ) { + foreach ( $retErrors as $arrError ) { + $strRet .= "SQLState: " . $arrError[ 'SQLSTATE'] . "\n"; + $strRet .= "Error Code: " . $arrError[ 'code'] . "\n"; + $strRet .= "Message: " . $arrError[ 'message'] . "\n"; + } + } else { + $strRet = "No errors found"; + } + return $strRet; + } + + function fetchRow( $res ) { if ( $res instanceof ResultWrapper ) { $res = $res->result; } - @/**/$row = mssql_fetch_array( $res ); - if ( $this->lastErrno() ) { - throw new DBUnexpectedError( $this, 'Error in fetchRow(): ' . htmlspecialchars( $this->lastError() ) ); - } + $row = $res->fetch( SQLSRV_FETCH_BOTH ); return $row; } - /** - * Get the number of rows in a result object - */ function numRows( $res ) { if ( $res instanceof ResultWrapper ) { $res = $res->result; } - @/**/$n = mssql_num_rows( $res ); - if ( $this->lastErrno() ) { - throw new DBUnexpectedError( $this, 'Error in numRows(): ' . htmlspecialchars( $this->lastError() ) ); - } - return $n; + return ( $res ) ? $res->numrows() : 0; } - /** - * Get the number of fields in a result object - * See documentation for mysql_num_fields() - * @param $res SQL result object as returned from Database::query(), etc. - */ function numFields( $res ) { if ( $res instanceof ResultWrapper ) { $res = $res->result; } - return mssql_num_fields( $res ); + return ( $res ) ? $res->numfields() : 0; } - /** - * Get a field name in a result object - * See documentation for mysql_field_name(): - * http://www.php.net/mysql_field_name - * @param $res SQL result object as returned from Database::query(), etc. - * @param $n Int - */ function fieldName( $res, $n ) { if ( $res instanceof ResultWrapper ) { $res = $res->result; } - return mssql_field_name( $res, $n ); + return ( $res ) ? $res->fieldname( $n ) : 0; } /** - * Get the inserted value of an auto-increment row - * - * The value inserted should be fetched from nextSequenceValue() - * - * Example: - * $id = $dbw->nextSequenceValue('page_page_id_seq'); - * $dbw->insert('page',array('page_id' => $id)); - * $id = $dbw->insertId(); + * This must be called after nextSequenceVal */ function insertId() { - $row = mssql_fetch_row(mssql_query('select @@IDENTITY')); - return $row[0]; + return $this->mInsertId; } - /** - * Change the position of the cursor in a result object - * See mysql_data_seek() - * @param $res SQL result object as returned from Database::query(), etc. - * @param $row Database row - */ function dataSeek( $res, $row ) { if ( $res instanceof ResultWrapper ) { $res = $res->result; } - return mssql_data_seek( $res, $row ); + return ( $res ) ? $res->seek( $row ) : false; } - /** - * Get the last error number - */ - function lastErrno() { - return $this->mlastErrorNo; + function lastError() { + if ( $this->mConn ) { + return $this->getErrors(); + } + else { + return "No database connection"; + } } - /** - * Get a description of the last error - */ - function lastError() { - return $this->mlastError; + function lastErrno() { + $err = sqlsrv_errors( SQLSRV_ERR_ALL ); + if ( $err[0] ) return $err[0]['code']; + else return 0; } - /** - * Get the number of rows affected by the last write query - */ function affectedRows() { - return mssql_rows_affected( $this->mConn ); + return $this->mAffectedRows; } /** - * Simple UPDATE wrapper - * Usually aborts on failure - * If errors are explicitly ignored, returns success + * SELECT wrapper * - * This function exists for historical reasons, Database::update() has a more standard - * calling convention and feature set + * @param $table Mixed: array or string, table name(s) (prefix auto-added) + * @param $vars Mixed: array or string, field name(s) to be retrieved + * @param $conds Mixed: array or string, condition(s) for WHERE + * @param $fname String: calling function name (use __METHOD__) for logs/profiling + * @param $options Array: associative array of options (e.g. array('GROUP BY' => 'page_title')), + * see Database::makeSelectOptions code for list of supported stuff + * @param $join_conds Array: Associative array of table join conditions (optional) + * (e.g. array( 'page' => array('LEFT JOIN','page_latest=rev_id') ) + * @return Mixed: database result resource (feed to Database::fetchObject or whatever), or false on failure */ - function set( $table, $var, $value, $cond, $fname = 'Database::set' ) + function select( $table, $vars, $conds = '', $fname = 'DatabaseMssql::select', $options = array(), $join_conds = array() ) { - if ($value == "NULL") $value = "''"; # see comments in makeListWithoutNulls() - $table = $this->tableName( $table ); - $sql = "UPDATE $table SET $var = '" . - $this->strencode( $value ) . "' WHERE ($cond)"; - return (bool)$this->query( $sql, $fname ); - } - - /** - * Simple SELECT wrapper, returns a single field, input must be encoded - * Usually aborts on failure - * If errors are explicitly ignored, returns FALSE on failure - */ - function selectField( $table, $var, $cond='', $fname = 'Database::selectField', $options = array() ) { - if ( !is_array( $options ) ) { - $options = array( $options ); - } - $options['LIMIT'] = 1; - - $res = $this->select( $table, $var, $cond, $fname, $options ); - if ( $res === false || !$this->numRows( $res ) ) { - return false; - } - $row = $this->fetchRow( $res ); - if ( $row !== false ) { - $this->freeResult( $res ); - return $row[0]; - } else { - return false; - } - } - - /** - * Returns an optional USE INDEX clause to go after the table, and a - * string to go at the end of the query - * - * @private - * - * @param $options Array: an associative array of options to be turned into - * an SQL query, valid keys are listed in the function. - * @return array - */ - function makeSelectOptions( $options ) { - $preLimitTail = $postLimitTail = ''; - $startOpts = ''; - - $noKeyOptions = array(); - foreach ( $options as $key => $option ) { - if ( is_numeric( $key ) ) { - $noKeyOptions[$option] = true; - } - } - - if ( isset( $options['GROUP BY'] ) ) $preLimitTail .= " GROUP BY {$options['GROUP BY']}"; - if ( isset( $options['HAVING'] ) ) $preLimitTail .= " HAVING {$options['HAVING']}"; - if ( isset( $options['ORDER BY'] ) ) $preLimitTail .= " ORDER BY {$options['ORDER BY']}"; - - //if (isset($options['LIMIT'])) { - // $tailOpts .= $this->limitResult('', $options['LIMIT'], - // isset($options['OFFSET']) ? $options['OFFSET'] - // : false); - //} - - if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $postLimitTail .= ' FOR UPDATE'; - if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $postLimitTail .= ' LOCK IN SHARE MODE'; - if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT'; - - # Various MySQL extensions - if ( isset( $noKeyOptions['STRAIGHT_JOIN'] ) ) $startOpts .= ' /*! STRAIGHT_JOIN */'; - if ( isset( $noKeyOptions['HIGH_PRIORITY'] ) ) $startOpts .= ' HIGH_PRIORITY'; - if ( isset( $noKeyOptions['SQL_BIG_RESULT'] ) ) $startOpts .= ' SQL_BIG_RESULT'; - if ( isset( $noKeyOptions['SQL_BUFFER_RESULT'] ) ) $startOpts .= ' SQL_BUFFER_RESULT'; - if ( isset( $noKeyOptions['SQL_SMALL_RESULT'] ) ) $startOpts .= ' SQL_SMALL_RESULT'; - if ( isset( $noKeyOptions['SQL_CALC_FOUND_ROWS'] ) ) $startOpts .= ' SQL_CALC_FOUND_ROWS'; - if ( isset( $noKeyOptions['SQL_CACHE'] ) ) $startOpts .= ' SQL_CACHE'; - if ( isset( $noKeyOptions['SQL_NO_CACHE'] ) ) $startOpts .= ' SQL_NO_CACHE'; - - if ( isset( $options['USE INDEX'] ) && ! is_array( $options['USE INDEX'] ) ) { - $useIndex = $this->useIndexClause( $options['USE INDEX'] ); - } else { - $useIndex = ''; + $sql = $this->selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); + if ( isset( $options['EXPLAIN'] ) ) { + sqlsrv_query( $this->mConn, "SET SHOWPLAN_ALL ON;" ); + $ret = $this->query( $sql, $fname ); + sqlsrv_query( $this->mConn, "SET SHOWPLAN_ALL OFF;" ); + return $ret; } - - return array( $startOpts, $useIndex, $preLimitTail, $postLimitTail ); + return $this->query( $sql, $fname ); } /** * SELECT wrapper * - * @param $table Mixed: Array or string, table name(s) (prefix auto-added) - * @param $vars Mixed: Array or string, field name(s) to be retrieved - * @param $conds Mixed: Array or string, condition(s) for WHERE + * @param $table Mixed: Array or string, table name(s) (prefix auto-added) + * @param $vars Mixed: Array or string, field name(s) to be retrieved + * @param $conds Mixed: Array or string, condition(s) for WHERE * @param $fname String: Calling function name (use __METHOD__) for logs/profiling - * @param $options Array: Associative array of options (e.g. array('GROUP BY' => 'page_title')), - * see Database::makeSelectOptions code for list of supported stuff - * @return mixed Database result resource (feed to Database::fetchObject or whatever), or false on failure + * @param $options Array: Associative array of options (e.g. array('GROUP BY' => 'page_title')), + * see Database::makeSelectOptions code for list of supported stuff + * @param $join_conds Array: Associative array of table join conditions (optional) + * (e.g. array( 'page' => array('LEFT JOIN','page_latest=rev_id') ) + * @return string, the SQL text */ - function select( $table, $vars, $conds='', $fname = 'Database::select', $options = array() ) - { - if( is_array( $vars ) ) { - $vars = implode( ',', $vars ); - } - if( !is_array( $options ) ) { - $options = array( $options ); - } - if( is_array( $table ) ) { - if ( isset( $options['USE INDEX'] ) && is_array( $options['USE INDEX'] ) ) - $from = ' FROM ' . $this->tableNamesWithUseIndex( $table, $options['USE INDEX'] ); - else - $from = ' FROM ' . implode( ',', array_map( array( &$this, 'tableName' ), $table ) ); - } elseif ($table!='') { - if ($table{0}==' ') { - $from = ' FROM ' . $table; - } else { - $from = ' FROM ' . $this->tableName( $table ); - } - } else { - $from = ''; - } - - list( $startOpts, $useIndex, $preLimitTail, $postLimitTail ) = $this->makeSelectOptions( $options ); - - if( !empty( $conds ) ) { - if ( is_array( $conds ) ) { - $conds = $this->makeList( $conds, LIST_AND ); - } - $sql = "SELECT $startOpts $vars $from $useIndex WHERE $conds $preLimitTail"; - } else { - $sql = "SELECT $startOpts $vars $from $useIndex $preLimitTail"; - } - - if (isset($options['LIMIT'])) - $sql = $this->limitResult($sql, $options['LIMIT'], - isset($options['OFFSET']) ? $options['OFFSET'] : false); - $sql = "$sql $postLimitTail"; - - if (isset($options['EXPLAIN'])) { - $sql = 'EXPLAIN ' . $sql; + function selectSQLText( $table, $vars, $conds = '', $fname = 'DatabaseMssql::select', $options = array(), $join_conds = array() ) { + if ( isset( $options['EXPLAIN'] ) ) { + unset( $options['EXPLAIN'] ); } - return $this->query( $sql, $fname ); + return parent::selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); } /** - * Determines whether a field exists in a table - * Usually aborts on failure - * If errors are explicitly ignored, returns NULL on failure + * Estimate rows in dataset + * Returns estimated count, based on SHOWPLAN_ALL output + * This is not necessarily an accurate estimate, so use sparingly + * Returns -1 if count cannot be found + * Takes same arguments as Database::select() */ - function fieldExists( $table, $field, $fname = 'Database::fieldExists' ) { - $table = $this->tableName( $table ); - $sql = "SELECT TOP 1 * FROM $table"; - $res = $this->query( $sql, 'Database::fieldExists' ); - - $found = false; - while ( $row = $this->fetchArray( $res ) ) { - if ( isset($row[$field]) ) { - $found = true; - break; - } + function estimateRowCount( $table, $vars = '*', $conds = '', $fname = 'DatabaseMssql::estimateRowCount', $options = array() ) { + $options['EXPLAIN'] = true;// http://msdn2.microsoft.com/en-us/library/aa259203.aspx + $res = $this->select( $table, $vars, $conds, $fname, $options ); + + $rows = -1; + if ( $res ) { + $row = $this->fetchRow( $res ); + if ( isset( $row['EstimateRows'] ) ) $rows = $row['EstimateRows']; } - - $this->freeResult( $res ); - return $found; + return $rows; } + /** - * Get information about an index into an object - * Returns false if the index does not exist + * Returns information about an index + * If errors are explicitly ignored, returns NULL on failure */ - function indexInfo( $table, $index, $fname = 'Database::indexInfo' ) { - - throw new DBUnexpectedError( $this, 'Database::indexInfo called which is not supported yet' ); - return null; - - $table = $this->tableName( $table ); - $sql = 'SHOW INDEX FROM '.$table; + function indexInfo( $table, $index, $fname = 'DatabaseMssql::indexExists' ) { + # This does not return the same info as MYSQL would, but that's OK because MediaWiki never uses the + # returned value except to check for the existance of indexes. + $sql = "sp_helpindex '" . $table . "'"; $res = $this->query( $sql, $fname ); if ( !$res ) { - return null; + return NULL; } $result = array(); - while ( $row = $this->fetchObject( $res ) ) { - if ( $row->Key_name == $index ) { - $result[] = $row; - } - } - $this->freeResult($res); - - return empty($result) ? false : $result; - } - - /** - * Query whether a given table exists - */ - function tableExists( $table ) { - $table = $this->tableName( $table ); - $res = $this->query( "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$table'" ); - $exist = ($res->numRows() > 0); - $this->freeResult($res); - return $exist; - } - - /** - * mysql_fetch_field() wrapper - * Returns false if the field doesn't exist - * - * @param $table - * @param $field - */ - function fieldInfo( $table, $field ) { - $table = $this->tableName( $table ); - $res = $this->query( "SELECT TOP 1 * FROM $table" ); - $n = mssql_num_fields( $res->result ); - for( $i = 0; $i < $n; $i++ ) { - $meta = mssql_fetch_field( $res->result, $i ); - if( $field == $meta->name ) { - return new MSSQLField($meta); + foreach ( $res as $row ) { + if ( $row->index_name == $index ) { + $row->Non_unique = !stristr( $row->index_description, "unique" ); + $cols = explode( ", ", $row->index_keys ); + foreach ( $cols as $col ) { + $row->Column_name = trim( $col ); + $result[] = clone $row; + } + } else if ( $index == 'PRIMARY' && stristr( $row->index_description, 'PRIMARY' ) ) { + $row->Non_unique = 0; + $cols = explode( ", ", $row->index_keys ); + foreach ( $cols as $col ) { + $row->Column_name = trim( $col ); + $result[] = clone $row; + } } } - return false; - } - - /** - * mysql_field_type() wrapper - */ - function fieldType( $res, $index ) { - if ( $res instanceof ResultWrapper ) { - $res = $res->result; - } - return mssql_field_type( $res, $index ); + return empty( $result ) ? false : $result; } /** * INSERT wrapper, inserts an array into a table * - * $a may be a single associative array, or an array of these with numeric keys, for + * $arrToInsert may be a single associative array, or an array of these with numeric keys, for * multi-row insert. * * Usually aborts on failure * If errors are explicitly ignored, returns success - * - * Same as parent class implementation except that it removes primary key from column lists - * because MSSQL doesn't support writing nulls to IDENTITY (AUTO_INCREMENT) columns */ - function insert( $table, $a, $fname = 'Database::insert', $options = array() ) { + function insert( $table, $arrToInsert, $fname = 'DatabaseMssql::insert', $options = array() ) { # No rows to insert, easy just return now - if ( !count( $a ) ) { + if ( !count( $arrToInsert ) ) { return true; } - $table = $this->tableName( $table ); + if ( !is_array( $options ) ) { $options = array( $options ); } - - # todo: need to record primary keys at table create time, and remove NULL assignments to them - if ( isset( $a[0] ) && is_array( $a[0] ) ) { - $multi = true; - $keys = array_keys( $a[0] ); -# if (ereg('_id$',$keys[0])) { - foreach ($a as $i) { - if (is_null($i[$keys[0]])) unset($i[$keys[0]]); # remove primary-key column from multiple insert lists if empty value - } -# } - $keys = array_keys( $a[0] ); - } else { - $multi = false; - $keys = array_keys( $a ); -# if (ereg('_id$',$keys[0]) && empty($a[$keys[0]])) unset($a[$keys[0]]); # remove primary-key column from insert list if empty value - if (is_null($a[$keys[0]])) unset($a[$keys[0]]); # remove primary-key column from insert list if empty value - $keys = array_keys( $a ); + + $table = $this->tableName( $table ); + + if ( !( isset( $arrToInsert[0] ) && is_array( $arrToInsert[0] ) ) ) {// Not multi row + $arrToInsert = array( 0 => $arrToInsert );// make everything multi row compatible } - # handle IGNORE option - # example: - # MySQL: INSERT IGNORE INTO user_groups (ug_user,ug_group) VALUES ('1','sysop') - # MSSQL: IF NOT EXISTS (SELECT * FROM user_groups WHERE ug_user = '1') INSERT INTO user_groups (ug_user,ug_group) VALUES ('1','sysop') - $ignore = in_array('IGNORE',$options); - - # remove IGNORE from options list - if ($ignore) { - $oldoptions = $options; - $options = array(); - foreach ($oldoptions as $o) if ($o != 'IGNORE') $options[] = $o; - } - - $keylist = implode(',', $keys); - $sql = 'INSERT '.implode(' ', $options)." INTO $table (".implode(',', $keys).') VALUES '; - if ($multi) { - if ($ignore) { - # If multiple and ignore, then do each row as a separate conditional insert - foreach ($a as $row) { - $prival = $row[$keys[0]]; - $sql = "IF NOT EXISTS (SELECT * FROM $table WHERE $keys[0] = '$prival') $sql"; - if (!$this->query("$sql (".$this->makeListWithoutNulls($row).')', $fname)) return false; + $allOk = true; + + + // We know the table we're inserting into, get its identity column + $identity = null; + $tableRaw = preg_replace( '#\[([^\]]*)\]#', '$1', $table ); // strip matching square brackets from table name + $res = $this->doQuery( "SELECT NAME AS idColumn FROM SYS.IDENTITY_COLUMNS WHERE OBJECT_NAME(OBJECT_ID)='{$tableRaw}'" ); + if( $res && $res->numrows() ){ + // There is an identity for this table. + $identity = array_pop( $res->fetch( SQLSRV_FETCH_ASSOC ) ); + } + unset( $res ); + + foreach ( $arrToInsert as $a ) { + // start out with empty identity column, this is so we can return it as a result of the insert logic + $sqlPre = ''; + $sqlPost = ''; + $identityClause = ''; + + // if we have an identity column + if( $identity ) { + // iterate through + foreach ($a as $k => $v ) { + if ( $k == $identity ) { + if( !is_null($v) ){ + // there is a value being passed to us, we need to turn on and off inserted identity + $sqlPre = "SET IDENTITY_INSERT $table ON;" ; + $sqlPost = ";SET IDENTITY_INSERT $table OFF;"; + + } else { + // we can't insert NULL into an identity column, so remove the column from the insert. + unset( $a[$k] ); + } + } } - return true; - } else { - $first = true; - foreach ($a as $row) { - if ($first) $first = false; else $sql .= ','; - $sql .= '('.$this->makeListWithoutNulls($row).')'; + $identityClause = "OUTPUT INSERTED.$identity "; // we want to output an identity column as result + } + + $keys = array_keys( $a ); + + + // INSERT IGNORE is not supported by SQL Server + // remove IGNORE from options list and set ignore flag to true + $ignoreClause = false; + foreach ( $options as $k => $v ) { + if ( strtoupper( $v ) == "IGNORE" ) { + unset( $options[$k] ); + $ignoreClause = true; } } - } else { - if ($ignore) { + + // translate MySQL INSERT IGNORE to something SQL Server can use + // example: + // MySQL: INSERT IGNORE INTO user_groups (ug_user,ug_group) VALUES ('1','sysop') + // MSSQL: IF NOT EXISTS (SELECT * FROM user_groups WHERE ug_user = '1') INSERT INTO user_groups (ug_user,ug_group) VALUES ('1','sysop') + if ( $ignoreClause == true ) { $prival = $a[$keys[0]]; - $sql = "IF NOT EXISTS (SELECT * FROM $table WHERE $keys[0] = '$prival') $sql"; + $sqlPre .= "IF NOT EXISTS (SELECT * FROM $table WHERE $keys[0] = '$prival')"; + } + + // Build the actual query + $sql = $sqlPre . 'INSERT ' . implode( ' ', $options ) . + " INTO $table (" . implode( ',', $keys ) . ") $identityClause VALUES ("; + + $first = true; + foreach ( $a as $value ) { + if ( $first ) { + $first = false; + } else { + $sql .= ','; + } + if ( is_string( $value ) ) { + $sql .= $this->addIdentifierQuotes( $value ); + } elseif ( is_null( $value ) ) { + $sql .= 'null'; + } elseif ( is_array( $value ) || is_object( $value ) ) { + if ( is_object( $value ) && strtolower( get_class( $value ) ) == 'blob' ) { + $sql .= $this->addIdentifierQuotes( $value->fetch() ); + } else { + $sql .= $this->addIdentifierQuotes( serialize( $value ) ); + } + } else { + $sql .= $value; + } } - $sql .= '('.$this->makeListWithoutNulls($a).')'; + $sql .= ')' . $sqlPost; + + // Run the query + $ret = sqlsrv_query( $this->mConn, $sql ); + + if ( $ret === false ) { + throw new DBQueryError( $this, $this->getErrors(), $this->lastErrno(), $sql, $fname ); + } elseif ( $ret != NULL ) { + // remember number of rows affected + $this->mAffectedRows = sqlsrv_rows_affected( $ret ); + if ( !is_null($identity) ) { + // then we want to get the identity column value we were assigned and save it off + $row = sqlsrv_fetch_object( $ret ); + $this->mInsertId = $row->$identity; + } + sqlsrv_free_stmt( $ret ); + continue; + } + $allOk = false; } - return (bool)$this->query( $sql, $fname ); + return $allOk; } /** - * MSSQL doesn't allow implicit casting of NULL's into non-null values for NOT NULL columns - * for now I've just converted the NULL's in the lists for updates and inserts into empty strings - * which get implicitly casted to 0 for numeric columns - * NOTE: the set() method above converts NULL to empty string as well but not via this method + * INSERT SELECT wrapper + * $varMap must be an associative array of the form array( 'dest1' => 'source1', ...) + * Source items may be literals rather than field names, but strings should be quoted with Database::addQuotes() + * $conds may be "*" to copy the whole table + * srcTable may be an array of tables. */ - function makeListWithoutNulls($a, $mode = LIST_COMMA) { - return str_replace("NULL","''",$this->makeList($a,$mode)); + function insertSelect( $destTable, $srcTable, $varMap, $conds, $fname = 'DatabaseMssql::insertSelect', + $insertOptions = array(), $selectOptions = array() ) + { + $ret = parent::insertSelect( $destTable, $srcTable, $varMap, $conds, $fname, $insertOptions, $selectOptions ); + + if ( $ret === false ) { + throw new DBQueryError( $this, $this->getErrors(), $this->lastErrno(), $sql, $fname ); + } elseif ( $ret != NULL ) { + // remember number of rows affected + $this->mAffectedRows = sqlsrv_rows_affected( $ret ); + return $ret; + } + return NULL; } /** - * UPDATE wrapper, takes a condition array and a SET array + * Format a table name ready for use in constructing an SQL query + * + * This does two important things: it brackets table names which as necessary, + * and it adds a table prefix if there is one. * - * @param $table String: The table to UPDATE - * @param $values Array: An array of values to SET - * @param $conds Array: An array of conditions (WHERE). Use '*' to update all rows. - * @param $fname String: The Class::Function calling this function - * (for the log) - * @param $options Array: An array of UPDATE options, can be one or - * more of IGNORE, LOW_PRIORITY - * @return bool + * All functions of this object which require a table name call this function + * themselves. Pass the canonical name to such functions. This is only needed + * when calling query() directly. + * + * @param $name String: database table name */ - function update( $table, $values, $conds, $fname = 'Database::update', $options = array() ) { - $table = $this->tableName( $table ); - $opts = $this->makeUpdateOptions( $options ); - $sql = "UPDATE $opts $table SET " . $this->makeListWithoutNulls( $values, LIST_SET ); - if ( $conds != '*' ) { - $sql .= " WHERE " . $this->makeList( $conds, LIST_AND ); + function tableName( $name ) { + global $wgSharedDB; + # Skip quoted literals + if ( $name != '' && $name { 0 } != '[' ) { + if ( $this->mTablePrefix !== '' && strpos( '.', $name ) === false ) { + $name = "{$this->mTablePrefix}$name"; + } + if ( isset( $wgSharedDB ) && "{$this->mTablePrefix}user" == $name ) { + $name = "[$wgSharedDB].[$name]"; + } else { + # Standard quoting + if ( $name != '' ) $name = "[$name]"; + } } - return $this->query( $sql, $fname ); + return $name; } /** - * Make UPDATE options for the Database::update function - * - * @private - * @param $options Array: The options passed to Database::update - * @return string + * Return the next in a sequence, save the value for retrieval via insertId() */ - function makeUpdateOptions( $options ) { - if( !is_array( $options ) ) { - $options = array( $options ); + function nextSequenceValue( $seqName ) { + if ( !$this->tableExists( 'sequence_' . $seqName ) ) { + sqlsrv_query( $this->mConn, "CREATE TABLE [sequence_$seqName] (id INT NOT NULL IDENTITY PRIMARY KEY, junk varchar(10) NULL)" ); } - $opts = array(); - if ( in_array( 'LOW_PRIORITY', $options ) ) - $opts[] = $this->lowPriorityOption(); - if ( in_array( 'IGNORE', $options ) ) - $opts[] = 'IGNORE'; - return implode(' ', $opts); - } + sqlsrv_query( $this->mConn, "INSERT INTO [sequence_$seqName] (junk) VALUES ('')" ); + $ret = sqlsrv_query( $this->mConn, "SELECT TOP 1 id FROM [sequence_$seqName] ORDER BY id DESC" ); + $row = sqlsrv_fetch_array( $ret, SQLSRV_FETCH_ASSOC );// KEEP ASSOC THERE, weird weird bug dealing with the return value if you don't - /** - * Change the current database - */ - function selectDB( $db ) { - $this->mDBname = $db; - return mssql_select_db( $db, $this->mConn ); + sqlsrv_free_stmt( $ret ); + $this->mInsertId = $row['id']; + return $row['id']; } /** - * MSSQL has a problem with the backtick quoting, so all this does is ensure the prefix is added exactly once + * Return the current value of a sequence. Assumes it has ben nextval'ed in this session. */ - function tableName($name) { - return strpos($name, $this->mTablePrefix) === 0 ? $name : "{$this->mTablePrefix}$name"; + function currentSequenceValue( $seqName ) { + $ret = sqlsrv_query( $this->mConn, "SELECT TOP 1 id FROM [sequence_$seqName] ORDER BY id DESC" ); + if ( $ret !== false ) { + $row = sqlsrv_fetch_array( $ret ); + sqlsrv_free_stmt( $ret ); + return $row['id']; + } else { + return $this->nextSequenceValue( $seqName ); + } } - /** - * MSSQL doubles quotes instead of escaping them - * @param $s String to be slashed. - * @return string slashed string. - */ - function strencode($s) { - return str_replace("'","''",$s); - } - /** - * REPLACE query wrapper - * PostgreSQL simulates this with a DELETE followed by INSERT - * $row is the row to insert, an associative array - * $uniqueIndexes is an array of indexes. Each element may be either a - * field name or an array of field names - * - * It may be more efficient to leave off unique indexes which are unlikely to collide. - * However if you do this, you run the risk of encountering errors which wouldn't have - * occurred in MySQL - * - * @todo migrate comment to phodocumentor format - */ - function replace( $table, $uniqueIndexes, $rows, $fname = 'Database::replace' ) { + # REPLACE query wrapper + # MSSQL simulates this with a DELETE followed by INSERT + # $row is the row to insert, an associative array + # $uniqueIndexes is an array of indexes. Each element may be either a + # field name or an array of field names + # + # It may be more efficient to leave off unique indexes which are unlikely to collide. + # However if you do this, you run the risk of encountering errors which wouldn't have + # occurred in MySQL + function replace( $table, $uniqueIndexes, $rows, $fname = 'DatabaseMssql::replace' ) { $table = $this->tableName( $table ); + if ( count( $rows ) == 0 ) { + return; + } + # Single row case if ( !is_array( reset( $rows ) ) ) { $rows = array( $rows ); } - $sql = "REPLACE INTO $table (" . implode( ',', array_keys( $rows[0] ) ) .') VALUES '; - $first = true; foreach ( $rows as $row ) { - if ( $first ) { - $first = false; - } else { - $sql .= ','; + # Delete rows which collide + if ( $uniqueIndexes ) { + $sql = "DELETE FROM $table WHERE "; + $first = true; + foreach ( $uniqueIndexes as $index ) { + if ( $first ) { + $first = false; + $sql .= "("; + } else { + $sql .= ') OR ('; + } + if ( is_array( $index ) ) { + $first2 = true; + foreach ( $index as $col ) { + if ( $first2 ) { + $first2 = false; + } else { + $sql .= ' AND '; + } + $sql .= $col . '=' . $this->addQuotes( $row[$col] ); + } + } else { + $sql .= $index . '=' . $this->addQuotes( $row[$index] ); + } + } + $sql .= ')'; + $this->query( $sql, $fname ); } - $sql .= '(' . $this->makeList( $row ) . ')'; + + # Now insert the row + $sql = "INSERT INTO $table (" . $this->makeList( array_keys( $row ), LIST_NAMES ) . ') VALUES (' . + $this->makeList( $row, LIST_COMMA ) . ')'; + $this->query( $sql, $fname ); } - return $this->query( $sql, $fname ); } - /** - * DELETE where the condition is a join - * MySQL does this with a multi-table DELETE syntax, PostgreSQL does it with sub-selects - * - * For safety, an empty $conds will not delete everything. If you want to delete all rows where the - * join condition matches, set $conds='*' - * - * DO NOT put the join condition in $conds - * - * @param $delTable String: The table to delete from. - * @param $joinTable String: The other table. - * @param $delVar String: The variable to join on, in the first table. - * @param $joinVar String: The variable to join on, in the second table. - * @param $conds Array: Condition array of field names mapped to variables, ANDed together in the WHERE clause - * @param $fname String: Calling function name - */ - function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = 'Database::deleteJoin' ) { + # DELETE where the condition is a join + function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = "DatabaseMssql::deleteJoin" ) { if ( !$conds ) { - throw new DBUnexpectedError( $this, 'Database::deleteJoin() called with empty $conds' ); + throw new DBUnexpectedError( $this, 'DatabaseMssql::deleteJoin() called with empty $conds' ); } $delTable = $this->tableName( $delTable ); $joinTable = $this->tableName( $joinTable ); - $sql = "DELETE $delTable FROM $delTable, $joinTable WHERE $delVar=$joinVar "; + $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable "; if ( $conds != '*' ) { - $sql .= ' AND ' . $this->makeList( $conds, LIST_AND ); + $sql .= 'WHERE ' . $this->makeList( $conds, LIST_AND ); } + $sql .= ')'; - return $this->query( $sql, $fname ); + $this->query( $sql, $fname ); } - /** - * Returns the size of a text field, or -1 for "unlimited" - */ + # Returns the size of a text field, or -1 for "unlimited" function textFieldSize( $table, $field ) { $table = $this->tableName( $table ); - $sql = "SELECT TOP 1 * FROM $table;"; - $res = $this->query( $sql, 'Database::textFieldSize' ); - $row = $this->fetchObject( $res ); - $this->freeResult( $res ); - - $m = array(); - if ( preg_match( '/\((.*)\)/', $row->Type, $m ) ) { - $size = $m[1]; + $sql = "SELECT CHARACTER_MAXIMUM_LENGTH,DATA_TYPE FROM INFORMATION_SCHEMA.Columns + WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'"; + $res = $this->query( $sql ); + $row = $this->fetchRow( $res ); + $size = -1; + if ( strtolower( $row['DATA_TYPE'] ) != 'text' ) $size = $row['CHARACTER_MAXIMUM_LENGTH']; + return $size; + } + + /** + * Construct a LIMIT query with optional offset + * This is used for query pages + * $sql string SQL query we will append the limit too + * $limit integer the SQL limit + * $offset integer the SQL offset (default false) + */ + function limitResult( $sql, $limit, $offset = false ) { + if ( $offset === false || $offset == 0 ) { + if ( strpos( $sql, "SELECT" ) === false ) { + return "TOP {$limit} " . $sql; + } else { + return preg_replace( '/\bSELECT(\s*DISTINCT)?\b/Dsi', 'SELECT$1 TOP ' . $limit, $sql, 1 ); + } } else { - $size = -1; + $sql = ' + SELECT * FROM ( + SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3 FROM ( + SELECT 1 AS line2, sub1.* FROM (' . $sql . ') AS sub1 + ) as sub2 + ) AS sub3 + WHERE line3 BETWEEN ' . ( $offset + 1 ) . ' AND ' . ( $offset + $limit ); + return $sql; } - return $size; + } + + // If there is a limit clause, parse it, strip it, and pass the remaining sql through limitResult() + // with the appropriate parameters. Not the prettiest solution, but better than building a whole new parser. + // This exists becase there are still too many extensions that don't use dynamic sql generation. + function LimitToTopN( $sql ) { + // Matches: LIMIT {[offset,] row_count | row_count OFFSET offset} + $pattern = '/\bLIMIT\s+((([0-9]+)\s*,\s*)?([0-9]+)(\s+OFFSET\s+([0-9]+))?)/i'; + if ( preg_match( $pattern, $sql, $matches ) ) { + // row_count = $matches[4] + $row_count = $matches[4]; + // offset = $matches[3] OR $matches[6] + $offset = $matches[3] or + $offset = $matches[6] or + $offset = false; + + // strip the matching LIMIT clause out + $sql = str_replace( $matches[0], '', $sql ); + return $this->limitResult( $sql, $row_count, $offset ); + } + return $sql; + } + + // MSSQL does support this, but documentation is too thin to make a generalized + // function for this. Apparently UPDATE TOP (N) works, but the sort order + // may not be what we're expecting so the top n results may be a random selection. + // TODO: Implement properly. + function limitResultForUpdate( $sql, $num ) { + return $sql; + } + + + function timestamp( $ts = 0 ) { + return wfTimestamp( TS_ISO_8601, $ts ); } /** - * @return string Returns the text of the low priority option if it is supported, or a blank string otherwise + * @return string wikitext of a link to the server software's web site */ - function lowPriorityOption() { - return 'LOW_PRIORITY'; + public static function getSoftwareLink() { + return "[http://www.microsoft.com/sql/ MS SQL Server]"; } /** - * INSERT SELECT wrapper - * $varMap must be an associative array of the form array( 'dest1' => 'source1', ...) - * Source items may be literals rather than field names, but strings should be quoted with Database::addQuotes() - * $conds may be "*" to copy the whole table - * srcTable may be an array of tables. + * @return string Version information from the database */ - function insertSelect( $destTable, $srcTable, $varMap, $conds, $fname = 'Database::insertSelect', - $insertOptions = array(), $selectOptions = array() ) - { - $destTable = $this->tableName( $destTable ); - if ( is_array( $insertOptions ) ) { - $insertOptions = implode( ' ', $insertOptions ); - } - if( !is_array( $selectOptions ) ) { - $selectOptions = array( $selectOptions ); - } - list( $startOpts, $useIndex, $tailOpts ) = $this->makeSelectOptions( $selectOptions ); - if( is_array( $srcTable ) ) { - $srcTable = implode( ',', array_map( array( &$this, 'tableName' ), $srcTable ) ); - } else { - $srcTable = $this->tableName( $srcTable ); - } - $sql = "INSERT $insertOptions INTO $destTable (" . implode( ',', array_keys( $varMap ) ) . ')' . - " SELECT $startOpts " . implode( ',', $varMap ) . - " FROM $srcTable $useIndex "; - if ( $conds != '*' ) { - $sql .= ' WHERE ' . $this->makeList( $conds, LIST_AND ); + function getServerVersion() { + $server_info = sqlsrv_server_info( $this->mConn ); + $version = 'Error'; + if ( isset( $server_info['SQLServerVersion'] ) ) $version = $server_info['SQLServerVersion']; + return $version; + } + + function tableExists ( $table, $schema = false ) { + $res = sqlsrv_query( $this->mConn, "SELECT * FROM information_schema.tables + WHERE table_type='BASE TABLE' AND table_name = '$table'" ); + if ( $res === false ) { + print( "Error in tableExists query: " . $this->getErrors() ); + return false; } - $sql .= " $tailOpts"; - return $this->query( $sql, $fname ); + if ( sqlsrv_fetch( $res ) ) + return true; + else + return false; } /** - * Construct a LIMIT query with optional offset - * This is used for query pages - * $sql string SQL query we will append the limit to - * $limit integer the SQL limit - * $offset integer the SQL offset (default false) + * Query whether a given column exists in the mediawiki schema */ - function limitResult($sql, $limit, $offset=false) { - if( !is_numeric($limit) ) { - throw new DBUnexpectedError( $this, "Invalid non-numeric limit passed to limitResult()\n" ); + function fieldExists( $table, $field, $fname = 'DatabaseMssql::fieldExists' ) { + $table = $this->tableName( $table ); + $res = sqlsrv_query( $this->mConn, "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.Columns + WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" ); + if ( $res === false ) { + print( "Error in fieldExists query: " . $this->getErrors() ); + return false; } - if ($offset) { - throw new DBUnexpectedError( $this, 'Database::limitResult called with non-zero offset which is not supported yet' ); - } else { - $sql = ereg_replace("^SELECT", "SELECT TOP $limit", $sql); + if ( sqlsrv_fetch( $res ) ) + return true; + else + return false; + } + + function fieldInfo( $table, $field ) { + $table = $this->tableName( $table ); + $res = sqlsrv_query( $this->mConn, "SELECT * FROM INFORMATION_SCHEMA.Columns + WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" ); + if ( $res === false ) { + print( "Error in fieldInfo query: " . $this->getErrors() ); + return false; } - return $sql; + $meta = $this->fetchRow( $res ); + if ( $meta ) { + return new MssqlField( $meta ); + } + return false; + } + + public function unixTimestamp( $field ) { + return "DATEDIFF(s,CONVERT(datetime,'1/1/1970'),$field)"; } /** - * Should determine if the last failure was due to a deadlock - * @return bool + * Begin a transaction, committing any previously open transaction */ - function wasDeadlock() { - return $this->lastErrno() == 1205; + function begin( $fname = 'DatabaseMssql::begin' ) { + sqlsrv_begin_transaction( $this->mConn ); + $this->mTrxLevel = 1; } /** - * Return MW-style timestamp used for MySQL schema + * End a transaction */ - function timestamp( $ts=0 ) { - return wfTimestamp(TS_MW,$ts); + function commit( $fname = 'DatabaseMssql::commit' ) { + sqlsrv_commit( $this->mConn ); + $this->mTrxLevel = 0; } /** - * Local database timestamp format or null + * Rollback a transaction. + * No-op on non-transactional databases. */ - function timestampOrNull( $ts = null ) { - if( is_null( $ts ) ) { - return null; - } else { - return $this->timestamp( $ts ); + function rollback( $fname = 'DatabaseMssql::rollback' ) { + sqlsrv_rollback( $this->mConn ); + $this->mTrxLevel = 0; + } + + function setup_database() { + global $wgDBuser; + + // Make sure that we can write to the correct schema + $ctest = "mediawiki_test_table"; + if ( $this->tableExists( $ctest ) ) { + $this->doQuery( "DROP TABLE $ctest" ); + } + $SQL = "CREATE TABLE $ctest (a int)"; + $res = $this->doQuery( $SQL ); + if ( !$res ) { + print "FAILED. Make sure that the user " . htmlspecialchars( $wgDBuser ) . " can write to the database\n"; + dieout( ); } + $this->doQuery( "DROP TABLE $ctest" ); + + $res = $this->sourceFile( "../maintenance/mssql/tables.sql" ); + if ( $res !== true ) { + echo " FAILED"; + dieout( htmlspecialchars( $res ) ); + } + + # Avoid the non-standard "REPLACE INTO" syntax + $f = fopen( "../maintenance/interwiki.sql", 'r' ); + if ( $f == false ) { + dieout( "
  • Could not find the interwiki.sql file" ); + } + # We simply assume it is already empty as we have just created it + $SQL = "INSERT INTO interwiki(iw_prefix,iw_url,iw_local) VALUES "; + while ( ! feof( $f ) ) { + $line = fgets( $f, 1024 ); + $matches = array(); + if ( !preg_match( '/^\s*(\(.+?),(\d)\)/', $line, $matches ) ) { + continue; + } + $this->query( "$SQL $matches[1],$matches[2])" ); + } + print " (table interwiki successfully populated)...\n"; + + $this->commit(); } /** - * @return string wikitext of a link to the server software's web site + * Escapes a identifier for use inm SQL. + * Throws an exception if it is invalid. + * Reference: http://msdn.microsoft.com/en-us/library/aa224033%28v=SQL.80%29.aspx */ - function getSoftwareLink() { - return "[http://www.microsoft.com/sql/default.mspx Microsoft SQL Server 2005 Home]"; + private function escapeIdentifier( $identifier ) { + if ( strlen( $identifier ) == 0 ) { + throw new MWException( "An identifier must not be empty" ); + } + if ( strlen( $identifier ) > 128 ) { + throw new MWException( "The identifier '$identifier' is too long (max. 128)" ); + } + if ( ( strpos( $identifier, '[' ) !== false ) || ( strpos( $identifier, ']' ) !== false ) ) { + // It may be allowed if you quoted with double quotation marks, but that would break if QUOTED_IDENTIFIER is OFF + throw new MWException( "You can't use square brackers in the identifier '$identifier'" ); + } + return "[$identifier]"; } /** - * @return string Version information from the database + * Initial setup. + * Precondition: This object is connected as the superuser. + * Creates the database, schema, user and login. */ - function getServerVersion() { - $row = mssql_fetch_row(mssql_query('select @@VERSION')); - return ereg("^(.+[0-9]+\\.[0-9]+\\.[0-9]+) ",$row[0],$m) ? $m[1] : $row[0]; + function initial_setup( $dbName, $newUser, $loginPassword ) { + $dbName = $this->escapeIdentifier( $dbName ); + + // It is not clear what can be used as a login, + // From http://msdn.microsoft.com/en-us/library/ms173463.aspx + // a sysname may be the same as an identifier. + $newUser = $this->escapeIdentifier( $newUser ); + $loginPassword = $this->addQuotes( $loginPassword ); + + $this->doQuery("CREATE DATABASE $dbName;"); + $this->doQuery("USE $dbName;"); + $this->doQuery("CREATE SCHEMA $dbName;"); + $this->doQuery(" + CREATE + LOGIN $newUser + WITH + PASSWORD=$loginPassword + ; + "); + $this->doQuery(" + CREATE + USER $newUser + FOR + LOGIN $newUser + WITH + DEFAULT_SCHEMA=$dbName + ; + "); + $this->doQuery(" + GRANT + BACKUP DATABASE, + BACKUP LOG, + CREATE DEFAULT, + CREATE FUNCTION, + CREATE PROCEDURE, + CREATE RULE, + CREATE TABLE, + CREATE VIEW, + CREATE FULLTEXT CATALOG + ON + DATABASE::$dbName + TO $newUser + ; + "); + $this->doQuery(" + GRANT + CONTROL + ON + SCHEMA::$dbName + TO $newUser + ; + "); + + } - function limitResultForUpdate($sql, $num) { - return $sql; + function encodeBlob( $b ) { + // we can't have zero's and such, this is a simple encoding to make sure we don't barf + return base64_encode( $b ); + } + + function decodeBlob( $b ) { + // we can't have zero's and such, this is a simple encoding to make sure we don't barf + return base64_decode( $b ); } /** - * How lagged is this slave? + * @private */ - public function getLag() { - return 0; + function tableNamesWithUseIndexOrJOIN( $tables, $use_index = array(), $join_conds = array() ) { + $ret = array(); + $retJOIN = array(); + $use_index_safe = is_array( $use_index ) ? $use_index : array(); + $join_conds_safe = is_array( $join_conds ) ? $join_conds : array(); + foreach ( $tables as $table ) { + // Is there a JOIN and INDEX clause for this table? + if ( isset( $join_conds_safe[$table] ) && isset( $use_index_safe[$table] ) ) { + $tableClause = $join_conds_safe[$table][0] . ' ' . $this->tableName( $table ); + $tableClause .= ' ' . $this->useIndexClause( implode( ',', (array)$use_index_safe[$table] ) ); + $tableClause .= ' ON (' . $this->makeList( (array)$join_conds_safe[$table][1], LIST_AND ) . ')'; + $retJOIN[] = $tableClause; + // Is there an INDEX clause? + } else if ( isset( $use_index_safe[$table] ) ) { + $tableClause = $this->tableName( $table ); + $tableClause .= ' ' . $this->useIndexClause( implode( ',', (array)$use_index_safe[$table] ) ); + $ret[] = $tableClause; + // Is there a JOIN clause? + } else if ( isset( $join_conds_safe[$table] ) ) { + $tableClause = $join_conds_safe[$table][0] . ' ' . $this->tableName( $table ); + $tableClause .= ' ON (' . $this->makeList( (array)$join_conds_safe[$table][1], LIST_AND ) . ')'; + $retJOIN[] = $tableClause; + } else { + $tableClause = $this->tableName( $table ); + $ret[] = $tableClause; + } + } + // We can't separate explicit JOIN clauses with ',', use ' ' for those + $straightJoins = !empty( $ret ) ? implode( ',', $ret ) : ""; + $otherJoins = !empty( $retJOIN ) ? implode( ' ', $retJOIN ) : ""; + // Compile our final table clause + return implode( ' ', array( $straightJoins, $otherJoins ) ); + } + + function strencode( $s ) { # Should not be called by us + return str_replace( "'", "''", $s ); + } + + function addQuotes( $s ) { + if ( $s instanceof Blob ) { + return "'" . $s->fetch( $s ) . "'"; + } else { + return parent::addQuotes( $s ); + } + } + + function selectDB( $db ) { + return ( $this->query( "SET DATABASE $db" ) !== false ); } /** - * Called by the installer script - * - this is the same way as DatabasePostgresql.php, MySQL reads in tables.sql and interwiki.sql using dbsource (which calls db->sourceFile) + * @private + * + * @param $options Array: an associative array of options to be turned into + * an SQL query, valid keys are listed in the function. + * @return Array */ - public function setup_database() { - global $IP,$wgDBTableOptions; - $wgDBTableOptions = ''; - $mysql_tmpl = "$IP/maintenance/tables.sql"; - $mysql_iw = "$IP/maintenance/interwiki.sql"; - $mssql_tmpl = "$IP/maintenance/mssql/tables.sql"; - - # Make an MSSQL template file if it doesn't exist (based on the same one MySQL uses to create a new wiki db) - if (!file_exists($mssql_tmpl)) { # todo: make this conditional again - $sql = file_get_contents($mysql_tmpl); - $sql = preg_replace('/^\s*--.*?$/m','',$sql); # strip comments - $sql = preg_replace('/^\s*(UNIQUE )?(INDEX|KEY|FULLTEXT).+?$/m', '', $sql); # These indexes should be created with a CREATE INDEX query - $sql = preg_replace('/(\sKEY) [^\(]+\(/is', '$1 (', $sql); # "KEY foo (foo)" should just be "KEY (foo)" - $sql = preg_replace('/(varchar\([0-9]+\))\s+binary/i', '$1', $sql); # "varchar(n) binary" cannot be followed by "binary" - $sql = preg_replace('/(var)?binary\(([0-9]+)\)/ie', '"varchar(".strlen(pow(2,$2)).")"', $sql); # use varchar(chars) not binary(bits) - $sql = preg_replace('/ (var)?binary/i', ' varchar', $sql); # use varchar not binary - $sql = preg_replace('/(varchar\([0-9]+\)(?! N))/', '$1 NULL', $sql); # MSSQL complains if NULL is put into a varchar - #$sql = preg_replace('/ binary/i',' varchar',$sql); # MSSQL binary's can't be assigned with strings, so use varchar's instead - #$sql = preg_replace('/(binary\([0-9]+\) (NOT NULL )?default) [\'"].*?[\'"]/i','$1 0',$sql); # binary default cannot be string - $sql = preg_replace('/[a-z]*(blob|text)([ ,])/i', 'text$2', $sql); # no BLOB types in MSSQL - $sql = preg_replace('/\).+?;/',');', $sql); # remove all table options - $sql = preg_replace('/ (un)?signed/i', '', $sql); - $sql = preg_replace('/ENUM\(.+?\)/','TEXT',$sql); # Make ENUM's into TEXT's - $sql = str_replace(' bool ', ' bit ', $sql); - $sql = str_replace('auto_increment', 'IDENTITY(1,1)', $sql); - #$sql = preg_replace('/NOT NULL(?! IDENTITY)/', 'NULL', $sql); # Allow NULL's for non IDENTITY columns - - # Tidy up and write file - $sql = preg_replace('/,\s*\)/s', "\n)", $sql); # Remove spurious commas left after INDEX removals - $sql = preg_replace('/^\s*^/m', '', $sql); # Remove empty lines - $sql = preg_replace('/;$/m', ";\n", $sql); # Separate each statement with an empty line - file_put_contents($mssql_tmpl, $sql); - } - - # Parse the MSSQL template replacing inline variables such as /*$wgDBprefix*/ - $err = $this->sourceFile($mssql_tmpl); - if ($err !== true) $this->reportQueryError($err,0,$sql,__FUNCTION__); - - # Use DatabasePostgres's code to populate interwiki from MySQL template - $f = fopen($mysql_iw,'r'); - if ($f == false) dieout("
  • Could not find the interwiki.sql file"); - $sql = "INSERT INTO {$this->mTablePrefix}interwiki(iw_prefix,iw_url,iw_local) VALUES "; - while (!feof($f)) { - $line = fgets($f,1024); - $matches = array(); - if (!preg_match('/^\s*(\(.+?),(\d)\)/', $line, $matches)) continue; - $this->query("$sql $matches[1],$matches[2])"); + function makeSelectOptions( $options ) { + $tailOpts = ''; + $startOpts = ''; + + $noKeyOptions = array(); + foreach ( $options as $key => $option ) { + if ( is_numeric( $key ) ) { + $noKeyOptions[$option] = true; + } } + + if ( isset( $options['GROUP BY'] ) ) $tailOpts .= " GROUP BY {$options['GROUP BY']}"; + if ( isset( $options['HAVING'] ) ) $tailOpts .= " HAVING {$options['GROUP BY']}"; + if ( isset( $options['ORDER BY'] ) ) $tailOpts .= " ORDER BY {$options['ORDER BY']}"; + + if ( isset( $noKeyOptions['DISTINCT'] ) && isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT'; + + // we want this to be compatible with the output of parent::makeSelectOptions() + return array( $startOpts, '' , $tailOpts, '' ); + } + + /** + * Get the type of the DBMS, as it appears in $wgDBtype. + */ + function getType(){ + return 'mssql'; } - + + function buildConcat( $stringList ) { + return implode( ' + ', $stringList ); + } + public function getSearchEngine() { - return "SearchEngineDummy"; + return "SearchMssql"; + } + +} // end DatabaseMssql class + +/** + * Utility class. + * + * @ingroup Database + */ +class MssqlField implements Field { + private $name, $tablename, $default, $max_length, $nullable, $type; + function __construct ( $info ) { + $this->name = $info['COLUMN_NAME']; + $this->tablename = $info['TABLE_NAME']; + $this->default = $info['COLUMN_DEFAULT']; + $this->max_length = $info['CHARACTER_MAXIMUM_LENGTH']; + $this->nullable = ( strtolower( $info['IS_NULLABLE'] ) == 'no' ) ? false:true; + $this->type = $info['DATA_TYPE']; + } + function name() { + return $this->name; + } + + function tableName() { + return $this->tableName; + } + + function defaultValue() { + return $this->default; + } + + function maxLength() { + return $this->max_length; + } + + function isNullable() { + return $this->nullable; + } + + function type() { + return $this->type; } } /** + * The MSSQL PHP driver doesn't support sqlsrv_num_rows, so we recall all rows into an array and maintain our + * own cursor index into that array...This is similar to the way the Oracle driver handles this same issue + * * @ingroup Database */ -class MSSQLField extends MySQLField { +class MssqlResult { + + public function __construct( $queryresult = false ) { + $this->mCursor = 0; + $this->mRows = array(); + $this->mNumFields = sqlsrv_num_fields( $queryresult ); + $this->mFieldMeta = sqlsrv_field_metadata( $queryresult ); + while ( $row = sqlsrv_fetch_array( $queryresult, SQLSRV_FETCH_ASSOC ) ) { + if ( $row !== null ) { + foreach ( $row as $k => $v ) { + if ( is_object( $v ) && method_exists( $v, 'format' ) ) {// DateTime Object + $row[$k] = $v->format( "Y-m-d\TH:i:s\Z" ); + } + } + $this->mRows[] = $row;// read results into memory, cursors are not supported + } + } + $this->mRowCount = count( $this->mRows ); + sqlsrv_free_stmt( $queryresult ); + } + + private function array_to_obj( $array, &$obj ) { + foreach ( $array as $key => $value ) { + if ( is_array( $value ) ) { + $obj->$key = new stdClass(); + $this->array_to_obj( $value, $obj->$key ); + } else { + if ( !empty( $key ) ) { + $obj->$key = $value; + } + } + } + return $obj; + } - function __construct() { + public function fetch( $mode = SQLSRV_FETCH_BOTH, $object_class = 'stdClass' ) { + if ( $this->mCursor >= $this->mRowCount || $this->mRowCount == 0 ) { + return false; + } + $arrNum = array(); + if ( $mode == SQLSRV_FETCH_NUMERIC || $mode == SQLSRV_FETCH_BOTH ) { + foreach ( $this->mRows[$this->mCursor] as $value ) { + $arrNum[] = $value; + } + } + switch( $mode ) { + case SQLSRV_FETCH_ASSOC: + $ret = $this->mRows[$this->mCursor]; + break; + case SQLSRV_FETCH_NUMERIC: + $ret = $arrNum; + break; + case 'OBJECT': + $o = new $object_class; + $ret = $this->array_to_obj( $this->mRows[$this->mCursor], $o ); + break; + case SQLSRV_FETCH_BOTH: + default: + $ret = $this->mRows[$this->mCursor] + $arrNum; + break; } - static function fromText($db, $table, $field) { - $n = new MSSQLField; - $n->name = $field; - $n->tablename = $table; - return $n; + $this->mCursor++; + return $ret; + } + + public function get( $pos, $fld ) { + return $this->mRows[$pos][$fld]; + } + + public function numrows() { + return $this->mRowCount; + } + + public function seek( $iRow ) { + $this->mCursor = min( $iRow, $this->mRowCount ); + } + + public function numfields() { + return $this->mNumFields; + } + + public function fieldname( $nr ) { + $arrKeys = array_keys( $this->mRows[0] ); + return $arrKeys[$nr]; + } + + public function fieldtype( $nr ) { + $i = 0; + $intType = -1; + foreach ( $this->mFieldMeta as $meta ) { + if ( $nr == $i ) { + $intType = $meta['Type']; + break; + } + $i++; } + // http://msdn.microsoft.com/en-us/library/cc296183.aspx contains type table + switch( $intType ) { + case SQLSRV_SQLTYPE_BIGINT: $strType = 'bigint'; break; + case SQLSRV_SQLTYPE_BINARY: $strType = 'binary'; break; + case SQLSRV_SQLTYPE_BIT: $strType = 'bit'; break; + case SQLSRV_SQLTYPE_CHAR: $strType = 'char'; break; + case SQLSRV_SQLTYPE_DATETIME: $strType = 'datetime'; break; + case SQLSRV_SQLTYPE_DECIMAL/*($precision, $scale)*/: $strType = 'decimal'; break; + case SQLSRV_SQLTYPE_FLOAT: $strType = 'float'; break; + case SQLSRV_SQLTYPE_IMAGE: $strType = 'image'; break; + case SQLSRV_SQLTYPE_INT: $strType = 'int'; break; + case SQLSRV_SQLTYPE_MONEY: $strType = 'money'; break; + case SQLSRV_SQLTYPE_NCHAR/*($charCount)*/: $strType = 'nchar'; break; + case SQLSRV_SQLTYPE_NUMERIC/*($precision, $scale)*/: $strType = 'numeric'; break; + case SQLSRV_SQLTYPE_NVARCHAR/*($charCount)*/: $strType = 'nvarchar'; break; + // case SQLSRV_SQLTYPE_NVARCHAR('max'): $strType = 'nvarchar(MAX)'; break; + case SQLSRV_SQLTYPE_NTEXT: $strType = 'ntext'; break; + case SQLSRV_SQLTYPE_REAL: $strType = 'real'; break; + case SQLSRV_SQLTYPE_SMALLDATETIME: $strType = 'smalldatetime'; break; + case SQLSRV_SQLTYPE_SMALLINT: $strType = 'smallint'; break; + case SQLSRV_SQLTYPE_SMALLMONEY: $strType = 'smallmoney'; break; + case SQLSRV_SQLTYPE_TEXT: $strType = 'text'; break; + case SQLSRV_SQLTYPE_TIMESTAMP: $strType = 'timestamp'; break; + case SQLSRV_SQLTYPE_TINYINT: $strType = 'tinyint'; break; + case SQLSRV_SQLTYPE_UNIQUEIDENTIFIER: $strType = 'uniqueidentifier'; break; + case SQLSRV_SQLTYPE_UDT: $strType = 'UDT'; break; + case SQLSRV_SQLTYPE_VARBINARY/*($byteCount)*/: $strType = 'varbinary'; break; + // case SQLSRV_SQLTYPE_VARBINARY('max'): $strType = 'varbinary(MAX)'; break; + case SQLSRV_SQLTYPE_VARCHAR/*($charCount)*/: $strType = 'varchar'; break; + // case SQLSRV_SQLTYPE_VARCHAR('max'): $strType = 'varchar(MAX)'; break; + case SQLSRV_SQLTYPE_XML: $strType = 'xml'; break; + default: $strType = $intType; + } + return $strType; + } -} // end DatabaseMssql class + public function free() { + unset( $this->mRows ); + return; + } +} -- cgit v1.2.2