From 5e1efc3144d4af3a907d3c79f1ea6b8975ddf453 Mon Sep 17 00:00:00 2001 From: Chad Horohoe Date: Fri, 15 Nov 2013 10:52:16 -0800 Subject: [PATCH] Remove unsupported and mostly non-functional Mssql support Change-Id: Id0a688865a5b7b6bb363f7b118fad4f455d2b3b6 --- RELEASE-NOTES-1.23 | 1 + docs/database.txt | 1 - includes/AutoLoader.php | 5 - includes/db/Database.php | 1 - includes/db/DatabaseMssql.php | 1202 -------------------------- includes/search/SearchMssql.php | 256 ------ maintenance/dictionary/mediawiki.dic | 2 - maintenance/mssql/tables.sql | 732 ---------------- 8 files changed, 1 insertion(+), 2199 deletions(-) delete mode 100644 includes/db/DatabaseMssql.php delete mode 100644 includes/search/SearchMssql.php delete mode 100644 maintenance/mssql/tables.sql diff --git a/RELEASE-NOTES-1.23 b/RELEASE-NOTES-1.23 index 8ef5acdb33..dbdc453479 100644 --- a/RELEASE-NOTES-1.23 +++ b/RELEASE-NOTES-1.23 @@ -73,6 +73,7 @@ changes to languages because of Bugzilla reports. ** The rc_type field of recentchanges will be deprecated in a future point release. * The global variable $wgArticle has been removed after a lengthy deprecation. +* Experimental and unsupported Mssql support has been removed == Compatibility == diff --git a/docs/database.txt b/docs/database.txt index 65a597b345..c33b5f183f 100644 --- a/docs/database.txt +++ b/docs/database.txt @@ -180,7 +180,6 @@ MediaWiki does support the following other DBMSs to varying degrees. * PostgreSQL * SQLite * Oracle -* MSSQL More information can be found about each of these databases (known issues, level of support, extra configuration) in the "databases" subdirectory in diff --git a/includes/AutoLoader.php b/includes/AutoLoader.php index c94c46be45..2c8f05f808 100644 --- a/includes/AutoLoader.php +++ b/includes/AutoLoader.php @@ -438,7 +438,6 @@ $wgAutoloadLocalClasses = array( 'ChronologyProtector' => 'includes/db/ChronologyProtector.php', 'CloneDatabase' => 'includes/db/CloneDatabase.php', 'DatabaseBase' => 'includes/db/Database.php', - 'DatabaseMssql' => 'includes/db/DatabaseMssql.php', 'DatabaseMysql' => 'includes/db/DatabaseMysql.php', 'DatabaseMysqlBase' => 'includes/db/DatabaseMysqlBase.php', 'DatabaseMysqli' => 'includes/db/DatabaseMysqli.php', @@ -471,8 +470,6 @@ $wgAutoloadLocalClasses = array( 'LoadMonitor' => 'includes/db/LoadMonitor.php', 'LoadMonitor_MySQL' => 'includes/db/LoadMonitor.php', 'LoadMonitor_Null' => 'includes/db/LoadMonitor.php', - 'MssqlField' => 'includes/db/DatabaseMssql.php', - 'MssqlResult' => 'includes/db/DatabaseMssql.php', 'MySQLField' => 'includes/db/DatabaseMysqlBase.php', 'MySQLMasterPos' => 'includes/db/DatabaseMysqlBase.php', 'ORAField' => 'includes/db/DatabaseOracle.php', @@ -871,14 +868,12 @@ $wgAutoloadLocalClasses = array( 'RevisionDeleteUser' => 'includes/revisiondelete/RevisionDeleteUser.php', # includes/search - 'MssqlSearchResultSet' => 'includes/search/SearchMssql.php', 'MySQLSearchResultSet' => 'includes/search/SearchMySQL.php', 'PostgresSearchResult' => 'includes/search/SearchPostgres.php', 'PostgresSearchResultSet' => 'includes/search/SearchPostgres.php', 'SearchEngine' => 'includes/search/SearchEngine.php', 'SearchEngineDummy' => 'includes/search/SearchEngine.php', 'SearchHighlighter' => 'includes/search/SearchEngine.php', - 'SearchMssql' => 'includes/search/SearchMssql.php', 'SearchMySQL' => 'includes/search/SearchMySQL.php', 'SearchNearMatchResultSet' => 'includes/search/SearchEngine.php', 'SearchOracle' => 'includes/search/SearchOracle.php', diff --git a/includes/db/Database.php b/includes/db/Database.php index c677d7437a..c54c309bf4 100644 --- a/includes/db/Database.php +++ b/includes/db/Database.php @@ -788,7 +788,6 @@ abstract class DatabaseBase implements IDatabase, DatabaseType { 'postgres' => array(), 'sqlite' => array(), 'oracle' => array(), - 'mssql' => array(), ); $driver = false; diff --git a/includes/db/DatabaseMssql.php b/includes/db/DatabaseMssql.php deleted file mode 100644 index 240a097ce2..0000000000 --- a/includes/db/DatabaseMssql.php +++ /dev/null @@ -1,1202 +0,0 @@ - - * @author Chris Pucci - * @author Ryan Biesemeyer - */ - -/** - * @ingroup Database - */ -class DatabaseMssql extends DatabaseBase { - var $mInsertId = null; - var $mLastResult = null; - var $mAffectedRows = null; - - var $mPort; - - function cascadingDeletes() { - return true; - } - - 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; - } - - /** - * Usually aborts on failure - * @param string $server - * @param string $user - * @param string $password - * @param string $dbName - * @throws DBConnectionError - * @return bool|DatabaseBase|null - */ - 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" ); - } - - global $wgDBport; - - if ( !strlen( $user ) ) { # e.g. the class is being loaded - return; - } - - $this->close(); - $this->mServer = $server; - $this->mPort = $wgDBport; - $this->mUser = $user; - $this->mPassword = $password; - $this->mDBname = $dbName; - - $connectionInfo = array(); - - if ( $dbName ) { - $connectionInfo['Database'] = $dbName; - } - - // 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 { - $connectionInfo['UID'] = $user; - $connectionInfo['PWD'] = $password; - } - // End NT Auth Hack - - wfSuppressWarnings(); - $this->mConn = sqlsrv_connect( $server, $connectionInfo ); - wfRestoreWarnings(); - - 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; - } - - $this->mOpened = true; - return $this->mConn; - } - - /** - * Closes a database connection, if it is open - * Returns success, true if already closed - * @return bool - */ - protected function closeConnection() { - return sqlsrv_close( $this->mConn ); - } - - protected 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; - } - - function freeResult( $res ) { - if ( $res instanceof ResultWrapper ) { - $res = $res->result; - } - $res->free(); - } - - function fetchObject( $res ) { - if ( $res instanceof ResultWrapper ) { - $res = $res->result; - } - $row = $res->fetch( 'OBJECT' ); - return $row; - } - - 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 = $res->fetch( SQLSRV_FETCH_BOTH ); - return $row; - } - - function numRows( $res ) { - if ( $res instanceof ResultWrapper ) { - $res = $res->result; - } - return ( $res ) ? $res->numrows() : 0; - } - - function numFields( $res ) { - if ( $res instanceof ResultWrapper ) { - $res = $res->result; - } - return ( $res ) ? $res->numfields() : 0; - } - - function fieldName( $res, $n ) { - if ( $res instanceof ResultWrapper ) { - $res = $res->result; - } - return ( $res ) ? $res->fieldname( $n ) : 0; - } - - /** - * This must be called after nextSequenceVal - * @return null - */ - function insertId() { - return $this->mInsertId; - } - - function dataSeek( $res, $row ) { - if ( $res instanceof ResultWrapper ) { - $res = $res->result; - } - return ( $res ) ? $res->seek( $row ) : false; - } - - function lastError() { - if ( $this->mConn ) { - return $this->getErrors(); - } else { - return "No database connection"; - } - } - - function lastErrno() { - $err = sqlsrv_errors( SQLSRV_ERR_ALL ); - if ( $err[0] ) { - return $err[0]['code']; - } else { - return 0; - } - } - - function affectedRows() { - return $this->mAffectedRows; - } - - /** - * 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 $fname String: calling function name (use __METHOD__) for logs/profiling - * @param array $options 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 select( $table, $vars, $conds = '', $fname = __METHOD__, $options = array(), $join_conds = array() ) - { - $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 $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 $fname String: Calling function name (use __METHOD__) for logs/profiling - * @param array $options 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 selectSQLText( $table, $vars, $conds = '', $fname = __METHOD__, $options = array(), $join_conds = array() ) { - if ( isset( $options['EXPLAIN'] ) ) { - unset( $options['EXPLAIN'] ); - } - return parent::selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); - } - - /** - * 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() - * @return int - */ - function estimateRowCount( $table, $vars = '*', $conds = '', $fname = __METHOD__, $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']; - } - } - return $rows; - } - - /** - * Returns information about an index - * If errors are explicitly ignored, returns NULL on failure - * @return array|bool|null - */ - function indexInfo( $table, $index, $fname = __METHOD__ ) { - # 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; - } - - $result = array(); - 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; - } - } elseif ( $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 empty( $result ) ? false : $result; - } - - /** - * INSERT wrapper, inserts an array into a table - * - * $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 - * @param string $table - * @param array $arrToInsert - * @param string $fname - * @param array $options - * @throws DBQueryError - * @return bool - */ - function insert( $table, $arrToInsert, $fname = __METHOD__, $options = array() ) { - # No rows to insert, easy just return now - if ( !count( $arrToInsert ) ) { - return true; - } - - if ( !is_array( $options ) ) { - $options = array( $options ); - } - - $table = $this->tableName( $table ); - - if ( !( isset( $arrToInsert[0] ) && is_array( $arrToInsert[0] ) ) ) {// Not multi row - $arrToInsert = array( 0 => $arrToInsert );// make everything multi row compatible - } - - $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] ); - } - } - } - $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; - } - } - - // 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 ) { - $prival = $a[$keys[0]]; - $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->addQuotes( $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->addQuotes( $value ); - } else { - $sql .= $this->addQuotes( serialize( $value ) ); - } - } else { - $sql .= $value; - } - } - $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 $allOk; - } - - /** - * 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. - * @param string $destTable - * @param array|string $srcTable - * @param array $varMap - * @param array $conds - * @param string $fname - * @param array $insertOptions - * @param array $selectOptions - * @throws DBQueryError - * @return null|ResultWrapper - */ - function insertSelect( $destTable, $srcTable, $varMap, $conds, $fname = __METHOD__, - $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; - } - - /** - * Return the next in a sequence, save the value for retrieval via insertId() - * @return - */ - 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)" ); - } - 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 - - sqlsrv_free_stmt( $ret ); - $this->mInsertId = $row['id']; - return $row['id']; - } - - /** - * Return the current value of a sequence. Assumes it has ben nextval'ed in this session. - * @return - */ - 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 ); - } - } - - # Returns the size of a text field, or -1 for "unlimited" - function textFieldSize( $table, $field ) { - $table = $this->tableName( $table ); - $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) - * @return mixed|string - */ - 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 { - $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; - } - } - - // 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; - } - - function timestamp( $ts = 0 ) { - return wfTimestamp( TS_ISO_8601, $ts ); - } - - /** - * @return string wikitext of a link to the server software's web site - */ - public function getSoftwareLink() { - return "[http://www.microsoft.com/sql/ MS SQL Server]"; - } - - /** - * @return string Version information from the database - */ - 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, $fname = __METHOD__, $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; - } - if ( sqlsrv_fetch( $res ) ) { - return true; - } else { - return false; - } - } - - /** - * Query whether a given column exists in the mediawiki schema - * @return bool - */ - function fieldExists( $table, $field, $fname = __METHOD__ ) { - $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 ( 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; - } - $meta = $this->fetchRow( $res ); - if ( $meta ) { - return new MssqlField( $meta ); - } - return false; - } - - /** - * Begin a transaction, committing any previously open transaction - */ - protected function doBegin( $fname = __METHOD__ ) { - sqlsrv_begin_transaction( $this->mConn ); - $this->mTrxLevel = 1; - } - - /** - * End a transaction - */ - protected function doCommit( $fname = __METHOD__ ) { - sqlsrv_commit( $this->mConn ); - $this->mTrxLevel = 0; - } - - /** - * Rollback a transaction. - * No-op on non-transactional databases. - */ - protected function doRollback( $fname = __METHOD__ ) { - sqlsrv_rollback( $this->mConn ); - $this->mTrxLevel = 0; - } - - /** - * 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 - * @param $identifier - * @throws MWException - * @return string - */ - 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]"; - } - - /** - * Initial setup. - * Precondition: This object is connected as the superuser. - * Creates the database, schema, user and login. - */ - 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 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 ); - } - - /** - * @private - * @return string - */ - 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? - } elseif ( 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? - } elseif ( 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 ); - } - } - - public function addIdentifierQuotes( $s ) { - // http://msdn.microsoft.com/en-us/library/aa223962.aspx - return '[' . $s . ']'; - } - - public function isQuotedIdentifier( $name ) { - return $name[0] == '[' && substr( $name, -1, 1 ) == ']'; - } - - function selectDB( $db ) { - return ( $this->query( "SET DATABASE $db" ) !== false ); - } - - /** - * @private - * - * @param array $options an associative array of options to be turned into - * an SQL query, valid keys are listed in the function. - * @return Array - */ - function makeSelectOptions( $options ) { - $tailOpts = ''; - $startOpts = ''; - - $noKeyOptions = array(); - foreach ( $options as $key => $option ) { - if ( is_numeric( $key ) ) { - $noKeyOptions[$option] = true; - } - } - - $tailOpts .= $this->makeGroupByWithHaving( $options ); - - $tailOpts .= $this->makeOrderBy( $options ); - - 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. - * @return string - */ - function getType() { - return 'mssql'; - } - - function buildConcat( $stringList ) { - return implode( ' + ', $stringList ); - } - - public function getSearchEngine() { - return "SearchMssql"; - } - - /** - * Since MSSQL doesn't recognize the infinity keyword, set date manually. - * @todo Remove magic date - * @return string - */ - public function getInfinity() { - return '3000-01-31 00:00:00.000'; - } - -} // 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' ); - $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 MssqlResult { - - public function __construct( $queryresult = false ) { - $this->mCursor = 0; - $this->mRows = array(); - $this->mNumFields = sqlsrv_num_fields( $queryresult ); - $this->mFieldMeta = sqlsrv_field_metadata( $queryresult ); - - $rows = sqlsrv_fetch_array( $queryresult, SQLSRV_FETCH_ASSOC ); - - foreach ( $rows as $row ) { - 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; - } - - 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; - } - - $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; - } - - public function free() { - unset( $this->mRows ); - } -} diff --git a/includes/search/SearchMssql.php b/includes/search/SearchMssql.php deleted file mode 100644 index cbc1a7a7f1..0000000000 --- a/includes/search/SearchMssql.php +++ /dev/null @@ -1,256 +0,0 @@ -db->resultObject( $this->db->query( $this->getQuery( $this->filter( $term ), true ) ) ); - return new MssqlSearchResultSet( $resultSet, $this->searchTerms ); - } - - /** - * Perform a title-only search query and return a result set. - * - * @param string $term raw search term - * @return MssqlSearchResultSet - * @access public - */ - function searchTitle( $term ) { - $resultSet = $this->db->resultObject( $this->db->query( $this->getQuery( $this->filter( $term ), false ) ) ); - return new MssqlSearchResultSet( $resultSet, $this->searchTerms ); - } - - /** - * Return a partial WHERE clause to exclude redirects, if so set - * - * @return String - * @private - */ - function queryRedirect() { - if ( $this->showRedirects ) { - return ''; - } else { - return 'AND page_is_redirect=0'; - } - } - - /** - * Return a partial WHERE clause to limit the search to the given namespaces - * - * @return String - * @private - */ - function queryNamespaces() { - $namespaces = implode( ',', $this->namespaces ); - if ( $namespaces == '' ) { - $namespaces = '0'; - } - return 'AND page_namespace IN (' . $namespaces . ')'; - } - - /** - * Return a LIMIT clause to limit results on the query. - * - * @param $sql string - * - * @return String - */ - function queryLimit( $sql ) { - return $this->db->limitResult( $sql, $this->limit, $this->offset ); - } - - /** - * Does not do anything for generic search engine - * subclasses may define this though - * - * @return String - */ - function queryRanking( $filteredTerm, $fulltext ) { - return ' ORDER BY ftindex.[RANK] DESC'; // return ' ORDER BY score(1)'; - } - - /** - * Construct the full SQL query to do the search. - * The guts shoulds be constructed in queryMain() - * - * @param $filteredTerm String - * @param $fulltext Boolean - * @return String - */ - function getQuery( $filteredTerm, $fulltext ) { - return $this->queryLimit( $this->queryMain( $filteredTerm, $fulltext ) . ' ' . - $this->queryRedirect() . ' ' . - $this->queryNamespaces() . ' ' . - $this->queryRanking( $filteredTerm, $fulltext ) . ' ' ); - } - - /** - * Picks which field to index on, depending on what type of query. - * - * @param $fulltext Boolean - * @return string - */ - function getIndexField( $fulltext ) { - return $fulltext ? 'si_text' : 'si_title'; - } - - /** - * Get the base part of the search query. - * - * @param $filteredTerm String - * @param $fulltext Boolean - * @return String - * @private - */ - function queryMain( $filteredTerm, $fulltext ) { - $match = $this->parseQuery( $filteredTerm, $fulltext ); - $page = $this->db->tableName( 'page' ); - $searchindex = $this->db->tableName( 'searchindex' ); - - return 'SELECT page_id, page_namespace, page_title, ftindex.[RANK]' . - "FROM $page,FREETEXTTABLE($searchindex , $match, LANGUAGE 'English') as ftindex " . - 'WHERE page_id=ftindex.[KEY] '; - } - - /** @todo document - * @return string - */ - function parseQuery( $filteredText, $fulltext ) { - global $wgContLang; - $lc = SearchEngine::legalSearchChars(); - $this->searchTerms = array(); - - # @todo FIXME: This doesn't handle parenthetical expressions. - $m = array(); - $q = array(); - - if ( preg_match_all( '/([-+<>~]?)(([' . $lc . ']+)(\*?)|"[^"]*")/', - $filteredText, $m, PREG_SET_ORDER ) ) { - foreach ( $m as $terms ) { - $q[] = $terms[1] . $wgContLang->normalizeForSearch( $terms[2] ); - - if ( !empty( $terms[3] ) ) { - $regexp = preg_quote( $terms[3], '/' ); - if ( $terms[4] ) { - $regexp .= "[0-9A-Za-z_]+"; - } - } else { - $regexp = preg_quote( str_replace( '"', '', $terms[2] ), '/' ); - } - $this->searchTerms[] = $regexp; - } - } - - $searchon = $this->db->strencode( join( ',', $q ) ); - $field = $this->getIndexField( $fulltext ); - return "$field, '$searchon'"; - } - - /** - * Create or update the search index record for the given page. - * Title and text should be pre-processed. - * - * @param $id Integer - * @param $title String - * @param $text String - * @return bool|ResultWrapper - */ - function update( $id, $title, $text ) { - // We store the column data as UTF-8 byte order marked binary stream - // because we are invoking the plain text IFilter on it so that, and we want it - // to properly decode the stream as UTF-8. SQL doesn't support UTF8 as a data type - // but the indexer will correctly handle it by this method. Since all we are doing - // is passing this data to the indexer and never retrieving it via PHP, this will save space - $table = $this->db->tableName( 'searchindex' ); - $utf8bom = '0xEFBBBF'; - $si_title = $utf8bom . bin2hex( $title ); - $si_text = $utf8bom . bin2hex( $text ); - $sql = "DELETE FROM $table WHERE si_page = $id;"; - $sql .= "INSERT INTO $table (si_page, si_title, si_text) VALUES ($id, $si_title, $si_text)"; - return $this->db->query( $sql, 'SearchMssql::update' ); - } - - /** - * Update a search index record's title only. - * Title should be pre-processed. - * - * @param $id Integer - * @param $title String - * @return bool|ResultWrapper - */ - function updateTitle( $id, $title ) { - $table = $this->db->tableName( 'searchindex' ); - - // see update for why we are using the utf8bom - $utf8bom = '0xEFBBBF'; - $si_title = $utf8bom . bin2hex( $title ); - $sql = "DELETE FROM $table WHERE si_page = $id;"; - $sql .= "INSERT INTO $table (si_page, si_title, si_text) VALUES ($id, $si_title, 0x00)"; - return $this->db->query( $sql, 'SearchMssql::updateTitle' ); - } -} - -/** - * @ingroup Search - */ -class MssqlSearchResultSet extends SearchResultSet { - function __construct( $resultSet, $terms ) { - $this->mResultSet = $resultSet; - $this->mTerms = $terms; - } - - function termMatches() { - return $this->mTerms; - } - - function numRows() { - return $this->mResultSet->numRows(); - } - - function next() { - $row = $this->mResultSet->fetchObject(); - if ( $row === false ) { - return false; - } - return new SearchResult( $row ); - } -} diff --git a/maintenance/dictionary/mediawiki.dic b/maintenance/dictionary/mediawiki.dic index 164b5b05ec..cf39523d81 100644 --- a/maintenance/dictionary/mediawiki.dic +++ b/maintenance/dictionary/mediawiki.dic @@ -188,7 +188,6 @@ Mostlinkedcategories Mostlinkedtemplates Mostrevisions Move -Mssql Mwstore Myuploads NEWPAGE @@ -2442,7 +2441,6 @@ msgsmall msgtext msie msmetafile -mssql msvideo msword mtime diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql deleted file mode 100644 index 7356c38f75..0000000000 --- a/maintenance/mssql/tables.sql +++ /dev/null @@ -1,732 +0,0 @@ --- Experimental table definitions for Microsoft SQL Server with --- content-holding fields switched to explicit BINARY charset. --- ------------------------------------------------------------ - --- SQL to create the initial tables for the MediaWiki database. --- This is read and executed by the install script; you should --- not have to run it by itself unless doing a manual install. - --- --- General notes: --- --- The comments in this and other files are --- replaced with the defined table prefix by the installer --- and updater scripts. If you are installing or running --- updates manually, you will need to manually insert the --- table prefix if any when running these scripts. --- - - --- --- The user table contains basic account information, --- authentication keys, etc. --- --- Some multi-wiki sites may share a single central user table --- between separate wikis using the $wgSharedDB setting. --- --- Note that when a external authentication plugin is used, --- user table entries still need to be created to store --- preferences and to key tracking information in the other --- tables. - --- LINE:53 -CREATE TABLE /*$wgDBprefix*/user ( - user_id INT NOT NULL PRIMARY KEY IDENTITY(0,1), - user_name NVARCHAR(255) NOT NULL UNIQUE DEFAULT '', - user_real_name NVARCHAR(255) NOT NULL DEFAULT '', - user_password NVARCHAR(255) NOT NULL DEFAULT '', - user_newpassword NVARCHAR(255) NOT NULL DEFAULT '', - user_newpass_time DATETIME NULL, - user_email NVARCHAR(255) NOT NULL DEFAULT '', - user_options NVARCHAR(MAX) NOT NULL DEFAULT '', - user_touched DATETIME NOT NULL DEFAULT GETDATE(), - user_token NCHAR(32) NOT NULL DEFAULT '', - user_email_authenticated DATETIME DEFAULT NULL, - user_email_token NCHAR(32) DEFAULT '', - user_email_token_expires DATETIME DEFAULT NULL, - user_registration DATETIME DEFAULT NULL, - user_editcount INT NULL -); -CREATE INDEX /*$wgDBprefix*/user_email_token ON /*$wgDBprefix*/[user](user_email_token); -CREATE UNIQUE INDEX /*$wgDBprefix*/[user_name] ON /*$wgDBprefix*/[user]([user_name]); -; - --- --- User permissions have been broken out to a separate table; --- this allows sites with a shared user table to have different --- permissions assigned to a user in each project. --- --- This table replaces the old user_rights field which used a --- comma-separated blob. -CREATE TABLE /*$wgDBprefix*/user_groups ( - ug_user INT NOT NULL REFERENCES /*$wgDBprefix*/[user](user_id) ON DELETE CASCADE, - ug_group NVARCHAR(16) NOT NULL DEFAULT '', -); -CREATE UNIQUE clustered INDEX /*$wgDBprefix*/user_groups_unique ON /*$wgDBprefix*/user_groups(ug_user, ug_group); -CREATE INDEX /*$wgDBprefix*/user_group ON /*$wgDBprefix*/user_groups(ug_group); - --- Stores notifications of user talk page changes, for the display --- of the "you have new messages" box --- Changed user_id column to mwuser_id to avoid clashing with user_id function -CREATE TABLE /*$wgDBprefix*/user_newtalk ( - user_id INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/[user](user_id) ON DELETE CASCADE, - user_ip NVARCHAR(40) NOT NULL DEFAULT '', - user_last_timestamp DATETIME NOT NULL DEFAULT '', -); -CREATE INDEX /*$wgDBprefix*/user_group_id ON /*$wgDBprefix*/user_newtalk([user_id]); -CREATE INDEX /*$wgDBprefix*/user_ip ON /*$wgDBprefix*/user_newtalk(user_ip); - --- --- User preferences and other fun stuff --- replaces old user.user_options BLOB --- -CREATE TABLE /*$wgDBprefix*/user_properties ( - up_user INT NOT NULL, - up_property NVARCHAR(32) NOT NULL, - up_value NVARCHAR(MAX), -); -CREATE UNIQUE clustered INDEX /*$wgDBprefix*/user_props_user_prop ON /*$wgDBprefix*/user_properties(up_user, up_property); -CREATE INDEX /*$wgDBprefix*/user_props_prop ON /*$wgDBprefix*/user_properties(up_property); - - --- --- Core of the wiki: each page has an entry here which identifies --- it by title and contains some essential metadata. --- -CREATE TABLE /*$wgDBprefix*/page ( - page_id INT NOT NULL PRIMARY KEY clustered IDENTITY, - page_namespace INT NOT NULL, - page_title NVARCHAR(255) NOT NULL, - page_restrictions NVARCHAR(255) NULL, - page_counter BIGINT NOT NULL DEFAULT 0, - page_is_redirect BIT NOT NULL DEFAULT 0, - page_is_new BIT NOT NULL DEFAULT 0, - page_random NUMERIC(15,14) NOT NULL DEFAULT RAND(), - page_touched DATETIME NOT NULL DEFAULT GETDATE(), - page_latest INT NOT NULL, - page_len INT NOT NULL, -); -CREATE UNIQUE INDEX /*$wgDBprefix*/page_unique_name ON /*$wgDBprefix*/page(page_namespace, page_title); -CREATE INDEX /*$wgDBprefix*/page_random_idx ON /*$wgDBprefix*/page(page_random); -CREATE INDEX /*$wgDBprefix*/page_len_idx ON /*$wgDBprefix*/page(page_len); -; - --- --- Every edit of a page creates also a revision row. --- This stores metadata about the revision, and a reference --- to the TEXT storage backend. --- -CREATE TABLE /*$wgDBprefix*/revision ( - rev_id INT NOT NULL UNIQUE IDENTITY, - rev_page INT NOT NULL, - rev_text_id INT NOT NULL, - rev_comment NVARCHAR(max) NOT NULL, - rev_user INT NOT NULL DEFAULT 0 /*REFERENCES [user](user_id)*/, - rev_user_text NVARCHAR(255) NOT NULL DEFAULT '', - rev_timestamp DATETIME NOT NULL DEFAULT GETDATE(), - rev_minor_edit BIT NOT NULL DEFAULT 0, - rev_deleted BIT NOT NULL DEFAULT 0, - rev_len INT, - rev_parent_id INT DEFAULT NULL, - -); -CREATE UNIQUE clustered INDEX /*$wgDBprefix*/revision_unique ON /*$wgDBprefix*/revision(rev_page, rev_id); -CREATE UNIQUE INDEX /*$wgDBprefix*/rev_id ON /*$wgDBprefix*/revision(rev_id); -CREATE INDEX /*$wgDBprefix*/rev_timestamp ON /*$wgDBprefix*/revision(rev_timestamp); -CREATE INDEX /*$wgDBprefix*/page_timestamp ON /*$wgDBprefix*/revision(rev_page, rev_timestamp); -CREATE INDEX /*$wgDBprefix*/user_timestamp ON /*$wgDBprefix*/revision(rev_user, rev_timestamp); -CREATE INDEX /*$wgDBprefix*/usertext_timestamp ON /*$wgDBprefix*/revision(rev_user_text, rev_timestamp); -; - --- --- Holds TEXT of individual page revisions. --- --- Field names are a holdover from the 'old' revisions table in --- MediaWiki 1.4 and earlier: an upgrade will transform that --- table INTo the 'text' table to minimize unnecessary churning --- and downtime. If upgrading, the other fields will be left unused. -CREATE TABLE /*$wgDBprefix*/text ( - old_id INT NOT NULL PRIMARY KEY clustered IDENTITY, - old_text TEXT NOT NULL, - old_flags NVARCHAR(255) NOT NULL, -); - --- --- Holding area for deleted articles, which may be viewed --- or restored by admins through the Special:Undelete interface. --- The fields generally correspond to the page, revision, and text --- fields, with several caveats. --- Cannot reasonably create views on this table, due to the presence of TEXT --- columns. -CREATE TABLE /*$wgDBprefix*/archive ( - ar_id NOT NULL PRIMARY KEY clustered IDENTITY, - ar_namespace SMALLINT NOT NULL DEFAULT 0, - ar_title NVARCHAR(255) NOT NULL DEFAULT '', - ar_text NVARCHAR(MAX) NOT NULL, - ar_comment NVARCHAR(255) NOT NULL, - ar_user INT NULL REFERENCES /*$wgDBprefix*/[user](user_id) ON DELETE SET NULL, - ar_user_text NVARCHAR(255) NOT NULL, - ar_timestamp DATETIME NOT NULL DEFAULT GETDATE(), - ar_minor_edit BIT NOT NULL DEFAULT 0, - ar_flags NVARCHAR(255) NOT NULL, - ar_rev_id INT, - ar_text_id INT, - ar_deleted BIT NOT NULL DEFAULT 0, - ar_len INT DEFAULT NULL, - ar_page_id INT NULL, - ar_parent_id INT NULL, -); -CREATE INDEX /*$wgDBprefix*/ar_name_title_timestamp ON /*$wgDBprefix*/archive(ar_namespace,ar_title,ar_timestamp); -CREATE INDEX /*$wgDBprefix*/ar_usertext_timestamp ON /*$wgDBprefix*/archive(ar_user_text,ar_timestamp); -CREATE INDEX /*$wgDBprefix*/ar_user_text ON /*$wgDBprefix*/archive(ar_user_text); - - --- --- Track page-to-page hyperlinks within the wiki. --- -CREATE TABLE /*$wgDBprefix*/pagelinks ( - pl_from INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE, - pl_namespace SMALLINT NOT NULL DEFAULT 0, - pl_title NVARCHAR(255) NOT NULL DEFAULT '', -); -CREATE UNIQUE INDEX /*$wgDBprefix*/pl_from ON /*$wgDBprefix*/pagelinks(pl_from,pl_namespace,pl_title); -CREATE UNIQUE INDEX /*$wgDBprefix*/pl_namespace ON /*$wgDBprefix*/pagelinks(pl_namespace,pl_title,pl_from); - --- --- Track template inclusions. --- -CREATE TABLE /*$wgDBprefix*/templatelinks ( - tl_from INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE, - tl_namespace SMALLINT NOT NULL DEFAULT 0, - tl_title NVARCHAR(255) NOT NULL DEFAULT '', -); -CREATE UNIQUE INDEX /*$wgDBprefix*/tl_from ON /*$wgDBprefix*/templatelinks(tl_from,tl_namespace,tl_title); -CREATE UNIQUE INDEX /*$wgDBprefix*/tl_namespace ON /*$wgDBprefix*/templatelinks(tl_namespace,tl_title,tl_from); - --- --- Track links to images *used inline* --- We don't distinguish live from broken links here, so --- they do not need to be changed ON upload/removal. --- -CREATE TABLE /*$wgDBprefix*/imagelinks ( - il_from INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE, - il_to NVARCHAR(255) NOT NULL DEFAULT '', - CONSTRAINT /*$wgDBprefix*/il_from PRIMARY KEY(il_from,il_to), -); -CREATE UNIQUE INDEX /*$wgDBprefix*/il_from_to ON /*$wgDBprefix*/imagelinks(il_from,il_to); -CREATE UNIQUE INDEX /*$wgDBprefix*/il_to_from ON /*$wgDBprefix*/imagelinks(il_to,il_from); - --- --- Track category inclusions *used inline* --- This tracks a single level of category membership --- (folksonomic tagging, really). --- -CREATE TABLE /*$wgDBprefix*/categorylinks ( - cl_from INT NOT NULL DEFAULT 0, - cl_to NVARCHAR(255) NOT NULL DEFAULT '', - cl_sortkey NVARCHAR(150) NOT NULL DEFAULT '', - cl_timestamp DATETIME NOT NULL DEFAULT GETDATE(), - CONSTRAINT /*$wgDBprefix*/cl_from PRIMARY KEY(cl_from, cl_to), -); -CREATE UNIQUE INDEX /*$wgDBprefix*/cl_from_to ON /*$wgDBprefix*/categorylinks(cl_from,cl_to); --- We always sort within a given category... -CREATE INDEX /*$wgDBprefix*/cl_sortkey ON /*$wgDBprefix*/categorylinks(cl_to,cl_sortkey); --- Not really used? -CREATE INDEX /*$wgDBprefix*/cl_timestamp ON /*$wgDBprefix*/categorylinks(cl_to,cl_timestamp); ---; - --- --- Track all existing categories. Something is a category if 1) it has an en- --- try somewhere in categorylinks, or 2) it once did. Categories might not --- have corresponding pages, so they need to be tracked separately. --- -CREATE TABLE /*$wgDBprefix*/category ( - cat_id int NOT NULL IDENTITY(1,1), - cat_title nvarchar(255) NOT NULL, - cat_pages int NOT NULL default 0, - cat_subcats int NOT NULL default 0, - cat_files int NOT NULL default 0, - cat_hidden tinyint NOT NULL default 0, -); - -CREATE UNIQUE INDEX /*$wgDBprefix*/cat_title ON /*$wgDBprefix*/category(cat_title); --- For Special:Mostlinkedcategories -CREATE INDEX /*$wgDBprefix*/cat_pages ON /*$wgDBprefix*/category(cat_pages); - - -CREATE TABLE /*$wgDBprefix*/change_tag ( - ct_rc_id int NOT NULL default 0, - ct_log_id int NOT NULL default 0, - ct_rev_id int NOT NULL default 0, - ct_tag varchar(255) NOT NULL, - ct_params varchar(255) NOT NULL, -); -CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_rc_tag ON /*$wgDBprefix*/change_tag(ct_rc_id,ct_tag); -CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_log_tag ON /*$wgDBprefix*/change_tag(ct_log_id,ct_tag); -CREATE UNIQUE INDEX /*$wgDBprefix*/change_tag_rev_tag ON /*$wgDBprefix*/change_tag(ct_rev_id,ct_tag); -CREATE INDEX /*$wgDBprefix*/change_tag_tag_id ON /*$wgDBprefix*/change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id); - -CREATE TABLE /*$wgDBprefix*/tag_summary ( - ts_rc_id INT NOT NULL default 0, - ts_log_id INT NOT NULL default 0, - ts_rev_id INT NOT NULL default 0, - ts_tags varchar(255) NOT NULL -); -CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_rc_id ON /*$wgDBprefix*/tag_summary(ts_rc_id); -CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_log_id ON /*$wgDBprefix*/tag_summary(ts_log_id); -CREATE UNIQUE INDEX /*$wgDBprefix*/tag_summary_rev_id ON /*$wgDBprefix*/tag_summary(ts_rev_id); - -CREATE TABLE /*$wgDBprefix*/valid_tag ( - vt_tag varchar(255) NOT NULL PRIMARY KEY -); - --- --- Table for storing localisation data --- -CREATE TABLE /*$wgDBprefix*/l10n_cache ( - -- language code - lc_lang NVARCHAR(32) NOT NULL, - - -- cache key - lc_key NVARCHAR(255) NOT NULL, - - -- Value - lc_value TEXT NOT NULL DEFAULT '', -); -CREATE INDEX /*$wgDBprefix*/lc_lang_key ON /*$wgDBprefix*/l10n_cache (lc_lang, lc_key); - --- --- Track links to external URLs --- IE >= 4 supports no more than 2083 characters in a URL -CREATE TABLE /*$wgDBprefix*/externallinks ( - el_id INT NOT NULL PRIMARY KEY clustered IDENTITY, - el_from INT NOT NULL DEFAULT '0', - el_to VARCHAR(2083) NOT NULL, - el_index VARCHAR(896) NOT NULL, -); --- Maximum key length ON SQL Server is 900 bytes -CREATE INDEX /*$wgDBprefix*/externallinks_index ON /*$wgDBprefix*/externallinks(el_index); - --- --- Track INTerlanguage links --- -CREATE TABLE /*$wgDBprefix*/langlinks ( - ll_from INT NOT NULL DEFAULT 0, - ll_lang NVARCHAR(20) NOT NULL DEFAULT '', - ll_title NVARCHAR(255) NOT NULL DEFAULT '', - CONSTRAINT /*$wgDBprefix*/langlinks_pk PRIMARY KEY(ll_from, ll_lang), -); -CREATE UNIQUE INDEX /*$wgDBprefix*/langlinks_reverse_key ON /*$wgDBprefix*/langlinks(ll_lang,ll_title); - --- --- Track inline interwiki links --- -CREATE TABLE /*$wgDBprefix*/iwlinks ( - -- page_id of the referring page - iwl_from INT NOT NULL DEFAULT 0, - - -- Interwiki prefix code of the target - iwl_prefix NVARCHAR(20) NOT NULL DEFAULT '', - - -- Title of the target, including namespace - iwl_title NVARCHAR(255) NOT NULL DEFAULT '', -); - -CREATE UNIQUE INDEX /*$wgDBprefix*/iwl_from ON /*$wgDBprefix*/iwlinks(iwl_from,iwl_prefix,iwl_title); -CREATE UNIQUE INDEX /*$wgDBprefix*/iwl_prefix ON /*$wgDBprefix*/iwlinks(iwl_prefix,iwl_title); - - --- --- Contains a single row with some aggregate info --- ON the state of the site. --- -CREATE TABLE /*$wgDBprefix*/site_stats ( - ss_row_id INT NOT NULL DEFAULT 1 PRIMARY KEY, - ss_total_views BIGINT DEFAULT 0, - ss_total_edits BIGINT DEFAULT 0, - ss_good_articles BIGINT DEFAULT 0, - ss_total_pages BIGINT DEFAULT -1, - ss_users BIGINT DEFAULT -1, - ss_active_users BIGINT DEFAULT -1, - ss_admins INT DEFAULT -1, - ss_images INT DEFAULT 0, -); - --- INSERT INTO site_stats DEFAULT VALUES; - --- --- Stores an ID for every time any article is visited; --- depending ON $wgHitcounterUpdateFreq, it is --- periodically cleared and the page_counter column --- in the page table updated for the all articles --- that have been visited.) --- -CREATE TABLE /*$wgDBprefix*/hitcounter ( - hc_id BIGINT NOT NULL -); - --- --- The Internet is full of jerks, alas. Sometimes it's handy --- to block a vandal or troll account. --- -CREATE TABLE /*$wgDBprefix*/ipblocks ( - ipb_id INT NOT NULL PRIMARY KEY, - ipb_address NVARCHAR(255) NOT NULL, - ipb_user INT NOT NULL DEFAULT 0, - ipb_by INT NOT NULL DEFAULT 0, - ipb_by_text NVARCHAR(255) NOT NULL DEFAULT '', - ipb_reason NVARCHAR(255) NOT NULL, - ipb_timestamp DATETIME NOT NULL DEFAULT GETDATE(), - ipb_auto BIT NOT NULL DEFAULT 0, - ipb_anon_only BIT NOT NULL DEFAULT 0, - ipb_create_account BIT NOT NULL DEFAULT 1, - ipb_enable_autoblock BIT NOT NULL DEFAULT 1, - ipb_expiry DATETIME NOT NULL DEFAULT GETDATE(), - ipb_range_start NVARCHAR(32) NOT NULL DEFAULT '', - ipb_range_end NVARCHAR(32) NOT NULL DEFAULT '', - ipb_deleted BIT NOT NULL DEFAULT 0, - ipb_block_email BIT NOT NULL DEFAULT 0, - ipb_allow_usertalk BIT NOT NULL DEFAULT 0, - ipb_parent_block_id INT DEFAULT NULL, -); --- Unique index to support "user already blocked" messages --- Any new options which prevent collisions should be included ---UNIQUE INDEX ipb_address (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only), -CREATE UNIQUE INDEX /*$wgDBprefix*/ipb_address ON /*$wgDBprefix*/ipblocks(ipb_address, ipb_user, ipb_auto, ipb_anon_only); -CREATE INDEX /*$wgDBprefix*/ipb_user ON /*$wgDBprefix*/ipblocks(ipb_user); -CREATE INDEX /*$wgDBprefix*/ipb_range ON /*$wgDBprefix*/ipblocks(ipb_range_start, ipb_range_end); -CREATE INDEX /*$wgDBprefix*/ipb_timestamp ON /*$wgDBprefix*/ipblocks(ipb_timestamp); -CREATE INDEX /*$wgDBprefix*/ipb_expiry ON /*$wgDBprefix*/ipblocks(ipb_expiry); -; - --- --- Uploaded images and other files. -CREATE TABLE /*$wgDBprefix*/image ( - img_name varchar(255) NOT NULL default '', - img_size INT NOT NULL DEFAULT 0, - img_width INT NOT NULL DEFAULT 0, - img_height INT NOT NULL DEFAULT 0, - img_metadata TEXT NOT NULL, -- was MEDIUMBLOB - img_bits SMALLINT NOT NULL DEFAULT 0, - img_media_type NVARCHAR(MAX) DEFAULT 'UNKNOWN', - img_major_mime NVARCHAR(MAX) DEFAULT 'UNKNOWN', - img_minor_mime NVARCHAR(MAX) NOT NULL DEFAULT 'unknown', - img_description NVARCHAR(MAX) NOT NULL, - img_user INT NOT NULL DEFAULT 0, - img_user_text VARCHAR(255) NOT NULL DEFAULT '', - img_timestamp DATETIME NOT NULL DEFAULT GETDATE(), - img_sha1 VARCHAR(255) NOT NULL default '', -); --- Used by Special:Imagelist for sort-by-size -CREATE INDEX /*$wgDBprefix*/img_size ON /*$wgDBprefix*/[image](img_size); --- Used by Special:Newimages and Special:Imagelist -CREATE INDEX /*$wgDBprefix*/img_timestamp ON /*$wgDBprefix*/[image](img_timestamp) -CREATE INDEX /*$wgDBprefix*/[img_sha1] ON /*wgDBprefix*/[image](img_sha1) - --- --- Previous revisions of uploaded files. --- Awkwardly, image rows have to be moved into --- this table at re-upload time. --- -CREATE TABLE /*$wgDBprefix*/oldimage ( - oi_name VARCHAR(255) NOT NULL DEFAULT '', - oi_archive_name VARCHAR(255) NOT NULL DEFAULT '', - oi_size INT NOT NULL DEFAULT 0, - oi_width INT NOT NULL DEFAULT 0, - oi_height INT NOT NULL DEFAULT 0, - oi_bits SMALLINT NOT NULL DEFAULT 0, - oi_description NVARCHAR(MAX) NOT NULL, - oi_user INT NOT NULL DEFAULT 0, - oi_user_text VARCHAR(255) NOT NULL DEFAULT '', - oi_timestamp DATETIME NOT NULL DEFAULT GETDATE(), - oi_metadata TEXT NOT NULL, - oi_media_type NVARCHAR(MAX) DEFAULT 'UNKNOWN', - oi_major_mime NVARCHAR(MAX) NOT NULL DEFAULT 'UNKNOWN', - oi_minor_mime NVARCHAR(MAX) NOT NULL DEFAULT 'unknown', - oi_deleted BIT NOT NULL default 0, - oi_sha1 VARCHAR(255) NOT NULL default '', -); -CREATE INDEX /*$wgDBprefix*/oi_usertext_timestamp ON /*$wgDBprefix*/oldimage(oi_user_text,oi_timestamp); -CREATE INDEX /*$wgDBprefix*/oi_name_timestamp ON /*$wgDBprefix*/oldimage(oi_name, oi_timestamp); -CREATE INDEX /*$wgDBprefix*/oi_name_archive_name ON /*$wgDBprefix*/oldimage(oi_name,oi_archive_name); -CREATE INDEX /*$wgDBprefix*/[oi_sha1] ON /*$wgDBprefix*/oldimage(oi_sha1); - --- --- Record of deleted file data --- -CREATE TABLE /*$wgDBprefix*/filearchive ( - fa_id INT NOT NULL PRIMARY KEY, - fa_name NVARCHAR(255) NOT NULL DEFAULT '', - fa_archive_name NVARCHAR(255) DEFAULT '', - fa_storage_group NVARCHAR(16), - fa_storage_key NVARCHAR(64) DEFAULT '', - fa_deleted_user INT, - fa_deleted_timestamp NVARCHAR(14) DEFAULT NULL, - fa_deleted_reason NVARCHAR(255), - fa_size SMALLINT DEFAULT 0, - fa_width SMALLINT DEFAULT 0, - fa_height SMALLINT DEFAULT 0, - fa_metadata NVARCHAR(MAX), -- was mediumblob - fa_bits SMALLINT DEFAULT 0, - fa_media_type NVARCHAR(11) DEFAULT NULL, - fa_major_mime NVARCHAR(11) DEFAULT 'unknown', - fa_minor_mime NVARCHAR(32) DEFAULT 'unknown', - fa_description NVARCHAR(255), - fa_user INT DEFAULT 0, - fa_user_text NVARCHAR(255) DEFAULT '', - fa_timestamp DATETIME DEFAULT GETDATE(), - fa_deleted BIT NOT NULL DEFAULT 0, -); --- Pick by image name -CREATE INDEX /*$wgDBprefix*/filearchive_name ON /*$wgDBprefix*/filearchive(fa_name,fa_timestamp); --- Pick by dupe files -CREATE INDEX /*$wgDBprefix*/filearchive_dupe ON /*$wgDBprefix*/filearchive(fa_storage_group,fa_storage_key); --- Pick by deletion time -CREATE INDEX /*$wgDBprefix*/filearchive_time ON /*$wgDBprefix*/filearchive(fa_deleted_timestamp); --- Pick by deleter -CREATE INDEX /*$wgDBprefix*/filearchive_user ON /*$wgDBprefix*/filearchive(fa_deleted_user); - --- --- Primarily a summary table for Special:Recentchanges, --- this table contains some additional info on edits from --- the last few days, see Article::editUpdates() --- -CREATE TABLE /*$wgDBprefix*/recentchanges ( - rc_id INT NOT NULL, - rc_timestamp DATETIME DEFAULT GETDATE(), - rc_cur_time DATETIME DEFAULT GETDATE(), - rc_user INT DEFAULT 0, - rc_user_text NVARCHAR(255) DEFAULT '', - rc_namespace SMALLINT DEFAULT 0, - rc_title NVARCHAR(255) DEFAULT '', - rc_comment NVARCHAR(255) DEFAULT '', - rc_minor BIT DEFAULT 0, - rc_bot BIT DEFAULT 0, - rc_new BIT DEFAULT 0, - rc_cur_id INT DEFAULT 0, - rc_this_oldid INT DEFAULT 0, - rc_last_oldid INT DEFAULT 0, - rc_type tinyint DEFAULT 0, - rc_patrolled BIT DEFAULT 0, - rc_ip NCHAR(40) DEFAULT '', - rc_old_len INT DEFAULT 0, - rc_new_len INT DEFAULT 0, - rc_deleted BIT DEFAULT 0, - rc_logid INT DEFAULT 0, - rc_log_type NVARCHAR(255) NULL DEFAULT NULL, - rc_log_action NVARCHAR(255) NULL DEFAULT NULL, - rc_params NVARCHAR(MAX) DEFAULT '', -); -CREATE INDEX /*$wgDBprefix*/rc_timestamp ON /*$wgDBprefix*/recentchanges(rc_timestamp); -CREATE INDEX /*$wgDBprefix*/rc_namespace_title ON /*$wgDBprefix*/recentchanges(rc_namespace, rc_title); -CREATE INDEX /*$wgDBprefix*/rc_cur_id ON /*$wgDBprefix*/recentchanges(rc_cur_id); -CREATE INDEX /*$wgDBprefix*/new_name_timestamp ON /*$wgDBprefix*/recentchanges(rc_new,rc_namespace,rc_timestamp); -CREATE INDEX /*$wgDBprefix*/rc_ip ON /*$wgDBprefix*/recentchanges(rc_ip); -CREATE INDEX /*$wgDBprefix*/rc_ns_usertext ON /*$wgDBprefix*/recentchanges(rc_namespace, rc_user_text); -CREATE INDEX /*$wgDBprefix*/rc_user_text ON /*$wgDBprefix*/recentchanges(rc_user_text, rc_timestamp); -; - -CREATE TABLE /*$wgDBprefix*/watchlist ( - wl_user INT NOT NULL, - wl_namespace SMALLINT NOT NULL DEFAULT 0, - wl_title NVARCHAR(255) NOT NULL DEFAULT '', - wl_notificationtimestamp NVARCHAR(14) DEFAULT NULL, - -); -CREATE UNIQUE INDEX /*$wgDBprefix*/namespace_title ON /*$wgDBprefix*/watchlist(wl_namespace,wl_title); - --- Needs fulltext index. -CREATE TABLE /*$wgDBprefix*/searchindex ( - si_page INT NOT NULL unique REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE, - si_title varbinary(max) NOT NULL, - si_text varbinary(max) NOT NULL, - si_ext CHAR(4) NOT NULL DEFAULT '.txt', -); -CREATE FULLTEXT CATALOG wikidb AS DEFAULT; -CREATE UNIQUE CLUSTERED INDEX searchindex_page ON searchindex (si_page); -CREATE FULLTEXT INDEX on searchindex (si_title TYPE COLUMN si_ext, si_text TYPE COLUMN si_ext) -KEY INDEX searchindex_page -; - --- This table is not used unless profiling is turned on -CREATE TABLE profiling ( - pf_count INTEGER NOT NULL DEFAULT 0, - pf_time NUMERIC(18,10) NOT NULL DEFAULT 0, - pf_name NVARCHAR(200) NOT NULL, - pf_server NVARCHAR(200) NULL -); -CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server); - --- --- Recognized INTerwiki link prefixes --- -CREATE TABLE /*$wgDBprefix*/interwiki ( - iw_prefix NCHAR(32) NOT NULL PRIMARY KEY, - iw_url NCHAR(127) NOT NULL, - iw_api TEXT NOT NULL DEFAULT '', - iw_wikiid NVARCHAR(64) NOT NULL DEFAULT '', - iw_local BIT NOT NULL, - iw_trans BIT NOT NULL DEFAULT 0, -); - --- --- Used for caching expensive grouped queries --- -CREATE TABLE /*$wgDBprefix*/querycache ( - qc_type NCHAR(32) NOT NULL, - qc_value INT NOT NULL DEFAULT '0', - qc_namespace SMALLINT NOT NULL DEFAULT 0, - qc_title NCHAR(255) NOT NULL DEFAULT '', - CONSTRAINT /*$wgDBprefix*/qc_pk PRIMARY KEY (qc_type,qc_value) -); - --- --- For a few generic cache operations if not using Memcached --- -CREATE TABLE /*$wgDBprefix*/objectcache ( - keyname NCHAR(255) NOT NULL DEFAULT '', - [value] NVARCHAR(MAX), -- IMAGE, - exptime DATETIME, -- This is treated as a DATETIME -); -CREATE CLUSTERED INDEX /*$wgDBprefix*/[objectcache_time] ON /*$wgDBprefix*/objectcache(exptime); -CREATE UNIQUE INDEX /*$wgDBprefix*/[objectcache_PK] ON /*wgDBprefix*/objectcache(keyname); --- --- Cache of INTerwiki transclusion --- -CREATE TABLE /*$wgDBprefix*/transcache ( - tc_url NVARCHAR(255) NOT NULL PRIMARY KEY, - tc_contents NVARCHAR(MAX), - tc_time INT NOT NULL, -); - -CREATE TABLE /*$wgDBprefix*/logging ( - log_id INT PRIMARY KEY IDENTITY, - log_type NCHAR(10) NOT NULL DEFAULT '', - log_action NCHAR(10) NOT NULL DEFAULT '', - log_timestamp DATETIME NOT NULL DEFAULT GETDATE(), - log_user INT NOT NULL DEFAULT 0, - log_user_text NVARCHAR(255) NOT NULL DEFAULT '', - log_namespace INT NOT NULL DEFAULT 0, - log_title NVARCHAR(255) NOT NULL DEFAULT '', - log_page INT NULL DEFAULT NULL, - log_comment NVARCHAR(255) NOT NULL DEFAULT '', - log_params NVARCHAR(MAX) NOT NULL, - log_deleted BIT NOT NULL DEFAULT 0, -); -CREATE INDEX /*$wgDBprefix*/type_time ON /*$wgDBprefix*/logging (log_type, log_timestamp); -CREATE INDEX /*$wgDBprefix*/user_time ON /*$wgDBprefix*/logging (log_user, log_timestamp); -CREATE INDEX /*$wgDBprefix*/page_time ON /*$wgDBprefix*/logging (log_namespace, log_title, log_timestamp); -CREATE INDEX /*$wgDBprefix*/times ON /*$wgDBprefix*/logging (log_timestamp); -CREATE INDEX /*$wgDBprefix*/log_user_type_time ON /*$wgDBprefix*/logging (log_user, log_type, log_timestamp); -CREATE INDEX /*$wgDBprefix*/log_page_id_time ON /*$wgDBprefix*/logging (log_page,log_timestamp); - -CREATE TABLE /*$wgDBprefix*/log_search ( - -- The type of ID (rev ID, log ID, rev timestamp, username) - ls_field NVARCHAR(32) NOT NULL, - -- The value of the ID - ls_value NVARCHAR(255) NOT NULL, - -- Key to log_id - ls_log_id INT NOT NULL default 0, -); -CREATE UNIQUE INDEX /*$wgDBprefix*/ls_field_val ON /*$wgDBprefix*/log_search (ls_field,ls_value,ls_log_id); -CREATE INDEX /*$wgDBprefix*/ls_log_id ON /*$wgDBprefix*/log_search (ls_log_id); - - --- Jobs performed by parallel apache threads or a command-line daemon -CREATE TABLE /*$wgDBprefix*/job ( - job_id INT NOT NULL PRIMARY KEY, - job_cmd NVARCHAR(200) NOT NULL DEFAULT '', - job_namespace INT NOT NULL, - job_title NVARCHAR(200) NOT NULL, - job_params NVARCHAR(255) NOT NULL, -); -CREATE INDEX /*$wgDBprefix*/job_idx ON /*$wgDBprefix*/job(job_cmd,job_namespace,job_title); - --- Details of updates to cached special pages -CREATE TABLE /*$wgDBprefix*/querycache_info ( - qci_type NVARCHAR(32) NOT NULL DEFAULT '' PRIMARY KEY, - qci_timestamp NVARCHAR(14) NOT NULL DEFAULT '19700101000000', -); - --- For each redirect, this table contains exactly one row defining its target -CREATE TABLE /*$wgDBprefix*/redirect ( - rd_from INT NOT NULL DEFAULT 0 REFERENCES /*$wgDBprefix*/[page](page_id) ON DELETE CASCADE, - rd_namespace SMALLINT NOT NULL DEFAULT '0', - rd_title NVARCHAR(255) NOT NULL DEFAULT '', - rd_interwiki NVARCHAR(32) DEFAULT NULL, - rd_fragment NVARCHAR(255) DEFAULT NULL, -); -CREATE UNIQUE INDEX /*$wgDBprefix*/rd_ns_title ON /*$wgDBprefix*/redirect(rd_namespace,rd_title,rd_from); - --- Used for caching expensive grouped queries that need two links (for example double-redirects) -CREATE TABLE /*$wgDBprefix*/querycachetwo ( - qcc_type NCHAR(32) NOT NULL, - qcc_value INT NOT NULL DEFAULT 0, - qcc_namespace INT NOT NULL DEFAULT 0, - qcc_title NCHAR(255) NOT NULL DEFAULT '', - qcc_namespacetwo INT NOT NULL DEFAULT 0, - qcc_titletwo NCHAR(255) NOT NULL DEFAULT '', - CONSTRAINT /*$wgDBprefix*/qcc_type PRIMARY KEY(qcc_type,qcc_value), -); -CREATE UNIQUE INDEX /*$wgDBprefix*/qcc_title ON /*$wgDBprefix*/querycachetwo(qcc_type,qcc_namespace,qcc_title); -CREATE UNIQUE INDEX /*$wgDBprefix*/qcc_titletwo ON /*$wgDBprefix*/querycachetwo(qcc_type,qcc_namespacetwo,qcc_titletwo); - - ---- Used for storing page restrictions (i.e. protection levels) -CREATE TABLE /*$wgDBprefix*/page_restrictions ( - pr_page INT NOT NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE, - pr_type NVARCHAR(200) NOT NULL, - pr_level NVARCHAR(200) NOT NULL, - pr_cascade SMALLINT NOT NULL, - pr_user INT NULL, - pr_expiry DATETIME NULL, - pr_id INT UNIQUE IDENTITY, - CONSTRAINT /*$wgDBprefix*/pr_pagetype PRIMARY KEY(pr_page,pr_type), -); -CREATE INDEX /*$wgDBprefix*/pr_page ON /*$wgDBprefix*/page_restrictions(pr_page); -CREATE INDEX /*$wgDBprefix*/pr_typelevel ON /*$wgDBprefix*/page_restrictions(pr_type,pr_level); -CREATE INDEX /*$wgDBprefix*/pr_pagelevel ON /*$wgDBprefix*/page_restrictions(pr_level); -CREATE INDEX /*$wgDBprefix*/pr_cascade ON /*$wgDBprefix*/page_restrictions(pr_cascade); -; - --- Protected titles - nonexistent pages that have been protected -CREATE TABLE /*$wgDBprefix*/protected_titles ( - pt_namespace int NOT NULL, - pt_title NVARCHAR(255) NOT NULL, - pt_user int NOT NULL, - pt_reason NVARCHAR(3555), - pt_timestamp DATETIME NOT NULL, - pt_expiry DATETIME NOT NULL default '', - pt_create_perm NVARCHAR(60) NOT NULL, - PRIMARY KEY (pt_namespace,pt_title), -); -CREATE INDEX /*$wgDBprefix*/pt_timestamp ON /*$wgDBprefix*/protected_titles(pt_timestamp); -; - --- Name/value pairs indexed by page_id -CREATE TABLE /*$wgDBprefix*/page_props ( - pp_page int NOT NULL, - pp_propname NVARCHAR(60) NOT NULL, - pp_value NVARCHAR(MAX) NOT NULL, - PRIMARY KEY (pp_page,pp_propname) -); - --- A table to log updates, one text key row per update. -CREATE TABLE /*$wgDBprefix*/updatelog ( - ul_key NVARCHAR(255) NOT NULL, - PRIMARY KEY (ul_key) -); - --- NOTE To enable full text indexing on SQL 2008 you need to create an account FDH$MSSQLSERVER --- AND assign a password for the FDHOST process to run under --- Once you have assigned a password to that account, you need to run the following stored procedure --- replacing XXXXX with the password you used. --- EXEC sp_fulltext_resetfdhostaccount @username = 'FDH$MSSQLSERVER', @password = 'XXXXXX' ; - - ---- Add the full-text capabilities, depricated in SQL Server 2005, FTS is enabled on all user created tables by default unless you are using SQL Server 2005 Express ---sp_fulltext_database 'enable'; ---sp_fulltext_catalog 'WikiCatalog', 'create' ---sp_fulltext_table ---sp_fulltext_column ---sp_fulltext_table 'Articles', 'activate' -- 2.20.1