The MSSQL schema is now brought in-line with the MySQL schema.
Additionally, various issues that prevented successful installation or
updates via MSSQL were fixed, notably with respect to creating bits of
the database should other bits already exist as well as issues with
previous updater patches not working correctly.
Additional MSSQL bugfixes will come in separate patches, as they are less
related to the schema/install/upgrade process.
Change-Id: If3eea625499d3cb14abba40f528208173067a53a
protected $mBinaryColumnCache = null;
protected $mBitColumnCache = null;
protected $mIgnoreDupKeyErrors = false;
+ protected $mIgnoreErrors = [];
protected $mPort;
$success = (bool)$stmt;
}
+ // make a copy so that anything we add below does not get reflected in future queries
+ $ignoreErrors = $this->mIgnoreErrors;
+
if ( $this->mIgnoreDupKeyErrors ) {
- // ignore duplicate key errors, but nothing else
+ // ignore duplicate key errors
// this emulates INSERT IGNORE in MySQL
- if ( $success === false ) {
- $errors = sqlsrv_errors( SQLSRV_ERR_ERRORS );
- $success = true;
-
- foreach ( $errors as $err ) {
- if ( $err['SQLSTATE'] == '23000' && $err['code'] == '2601' ) {
- continue; // duplicate key error caused by unique index
- } elseif ( $err['SQLSTATE'] == '23000' && $err['code'] == '2627' ) {
- continue; // duplicate key error caused by primary key
- } elseif ( $err['SQLSTATE'] == '01000' && $err['code'] == '3621' ) {
- continue; // generic "the statement has been terminated" error
- }
+ $ignoreErrors[] = '2601'; // duplicate key error caused by unique index
+ $ignoreErrors[] = '2627'; // duplicate key error caused by primary key
+ $ignoreErrors[] = '3621'; // generic "the statement has been terminated" error
+ }
- $success = false; // getting here means we got an error we weren't expecting
- break;
- }
+ if ( $success === false ) {
+ $errors = sqlsrv_errors();
+ $success = true;
- if ( $success ) {
- $this->mAffectedRows = 0;
- return $stmt;
+ foreach ( $errors as $err ) {
+ if ( !in_array( $err['code'], $ignoreErrors ) ) {
+ $success = false;
+ break;
}
}
- }
- if ( $success === false ) {
- return false;
+ if ( $success === false ) {
+ return false;
+ }
}
// remember number of rows affected
$this->mAffectedRows = sqlsrv_rows_affected( $stmt );
$res = $res->result;
}
- return sqlsrv_num_rows( $res );
+ $ret = sqlsrv_num_rows( $res );
+
+ if ( $ret === false ) {
+ // we cannot get an amount of rows from this cursor type
+ // has_rows returns bool true/false if the result has rows
+ $ret = (int)sqlsrv_has_rows( $res );
+ }
+
+ return $ret;
}
/**
# 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 . "'";
+ $sql = "sp_helpindex '" . $this->tableName( $table ) . "'";
$res = $this->query( $sql, $fname );
+
if ( !$res ) {
return null;
}
$row = $ret->fetchObject();
if ( is_object( $row ) ) {
$this->mInsertId = $row->$identity;
+
+ // it seems that mAffectedRows is -1 sometimes when OUTPUT INSERTED.identity is used
+ // if we got an identity back, we know for sure a row was affected, so adjust that here
+ if ( $this->mAffectedRows == -1 ) {
+ $this->mAffectedRows = 1;
+ }
}
}
}
return $table;
}
+ /**
+ * Delete a table
+ * @param string $tableName
+ * @param string $fName
+ * @return bool|ResultWrapper
+ * @since 1.18
+ */
+ public function dropTable( $tableName, $fName = __METHOD__ ) {
+ if ( !$this->tableExists( $tableName, $fName ) ) {
+ return false;
+ }
+
+ // parent function incorrectly appends CASCADE, which we don't want
+ $sql = "DROP TABLE " . $this->tableName( $tableName );
+
+ return $this->query( $sql, $fName );
+ }
+
/**
* Called in the installer and updater.
* Probably doesn't need to be called anywhere else in the codebase.
public function scrollableCursor( $value = null ) {
return wfSetVar( $this->mScrollableCursor, $value );
}
+
+ /**
+ * Called in the installer and updater.
+ * Probably doesn't need to be called anywhere else in the codebase.
+ * @param array|null $value
+ * @return array|null
+ */
+ public function ignoreErrors( array $value = null ) {
+ return wfSetVar( $this->mIgnoreErrors, $value );
+ }
} // end DatabaseMssql class
/**
"CREATE DATABASE " . $conn->addIdentifierQuotes( $dbName ),
__METHOD__
);
- $conn->selectDB( $dbName );
- if ( !$this->schemaExists( $schemaName ) ) {
- $conn->query(
- "CREATE SCHEMA " . $conn->addIdentifierQuotes( $schemaName ),
- __METHOD__
- );
- }
- if ( !$this->catalogExists( $schemaName ) ) {
- $conn->query(
- "CREATE FULLTEXT CATALOG " . $conn->addIdentifierQuotes( $schemaName ),
- __METHOD__
- );
- }
+ }
+ $conn->selectDB( $dbName );
+ if ( !$this->schemaExists( $schemaName ) ) {
+ $conn->query(
+ "CREATE SCHEMA " . $conn->addIdentifierQuotes( $schemaName ),
+ __METHOD__
+ );
+ }
+ if ( !$this->catalogExists( $schemaName ) ) {
+ $conn->query(
+ "CREATE FULLTEXT CATALOG " . $conn->addIdentifierQuotes( $schemaName ),
+ __METHOD__
+ );
}
$this->setupSchemaVars();
[ 'addField', 'mwuser', 'user_password_expires', 'patch-user_password_expires.sql' ],
// 1.24
- [ 'addField', 'page', 'page_lang', 'patch-page-page_lang.sql' ],
+ [ 'addField', 'page', 'page_lang', 'patch-page_page_lang.sql' ],
// 1.25
[ 'dropTable', 'hitcounter' ],
[ 'dropField', 'site_stats', 'ss_total_views', 'patch-drop-ss_total_views.sql' ],
[ 'dropField', 'page', 'page_counter', 'patch-drop-page_counter.sql' ],
- // Constraint updates
- [ 'updateConstraints', 'category_types', 'categorylinks', 'cl_type' ],
- [ 'updateConstraints', 'major_mime', 'filearchive', 'fa_major_mime' ],
- [ 'updateConstraints', 'media_type', 'filearchive', 'fa_media_type' ],
- [ 'updateConstraints', 'major_mime', 'oldimage', 'oi_major_mime' ],
- [ 'updateConstraints', 'media_type', 'oldimage', 'oi_media_type' ],
- [ 'updateConstraints', 'major_mime', 'image', 'img_major_mime' ],
- [ 'updateConstraints', 'media_type', 'image', 'img_media_type' ],
- [ 'updateConstraints', 'media_type', 'uploadstash', 'us_media_type' ],
- // END: Constraint updates
+ // scripts were updated in 1.27 due to SQL errors; retaining old updatekeys so that people
+ // updating from 1.23->1.25->1.27 do not execute these scripts twice even though the
+ // updatekeys no longer make sense as they are.
+ [ 'updateSchema', 'categorylinks', 'cl_type-category_types-ck',
+ 'patch-categorylinks-constraints.sql' ],
+ [ 'updateSchema', 'filearchive', 'fa_major_mime-major_mime-ck',
+ 'patch-filearchive-constraints.sql' ],
+ [ 'updateSchema', 'oldimage', 'oi_major_mime-major_mime-ck',
+ 'patch-oldimage-constraints.sql' ],
+ [ 'updateSchema', 'image', 'img_major_mime-major_mime-ck', 'patch-image-constraints.sql' ],
+ [ 'updateSchema', 'uploadstash', 'us_media_type-media_type-ck',
+ 'patch-uploadstash-constraints.sql' ],
[ 'modifyField', 'image', 'img_major_mime',
'patch-img_major_mime-chemical.sql' ],
[ 'dropTable', 'msg_resource_links' ],
[ 'dropTable', 'msg_resource' ],
[ 'addField', 'watchlist', 'wl_id', 'patch-watchlist-wl_id.sql' ],
+ [ 'dropField', 'mwuser', 'user_options', 'patch-drop-user_options.sql' ],
+ [ 'addTable', 'bot_passwords', 'patch-bot_passwords.sql' ],
+ [ 'addField', 'pagelinks', 'pl_from_namespace', 'patch-pl_from_namespace.sql' ],
+ [ 'addField', 'templatelinks', 'tl_from_namespace', 'patch-tl_from_namespace.sql' ],
+ [ 'addField', 'imagelinks', 'il_from_namespace', 'patch-il_from_namespace.sql' ],
+ [ 'dropIndex', 'categorylinks', 'cl_collation', 'patch-kill-cl_collation_index.sql' ],
+ [ 'addIndex', 'categorylinks', 'cl_collation_ext',
+ 'patch-add-cl_collation_ext_index.sql' ],
+ [ 'dropField', 'recentchanges', 'rc_cur_time', 'patch-drop-rc_cur_time.sql' ],
+ [ 'addField', 'page_props', 'pp_sortkey', 'patch-pp_sortkey.sql' ],
+ [ 'updateSchema', 'oldimage', 'oldimage varchar', 'patch-oldimage-schema.sql' ],
+ [ 'updateSchema', 'filearchive', 'filearchive varchar', 'patch-filearchive-schema.sql' ],
+ [ 'updateSchema', 'image', 'image varchar', 'patch-image-schema.sql' ]
];
}
+ protected function applyPatch( $path, $isFullPath = false, $msg = null ) {
+ $prevScroll = $this->db->scrollableCursor( false );
+ $prevPrep = $this->db->prepareStatements( false );
+ parent::applyPatch( $path, $isFullPath, $msg );
+ $this->db->scrollableCursor( $prevScroll );
+ $this->db->prepareStatements( $prevPrep );
+ }
+
/**
- * Drops unnamed and creates named constraints following the pattern
- * <column>_ckc
+ * General schema update for a table that touches more than one field or requires
+ * destructive actions (such as dropping and recreating the table).
*
- * @param string $constraintType
- * @param string $table Name of the table to which the field belongs
- * @param string $field Name of the field to modify
- * @return bool False if patch is skipped.
+ * @param string $table
+ * @param string $updatekey
+ * @param string $patch
+ * @param bool $fullpath
*/
- protected function updateConstraints( $constraintType, $table, $field ) {
- global $wgDBname, $wgDBmwschema;
-
- if ( !$this->doTable( $table ) ) {
- return true;
- }
-
- $this->output( "...updating constraints on [$table].[$field] ..." );
- $updateKey = "$field-$constraintType-ck";
+ protected function updateSchema( $table, $updatekey, $patch, $fullpath = false ) {
if ( !$this->db->tableExists( $table, __METHOD__ ) ) {
- $this->output( "...$table table does not exist, skipping modify field patch.\n" );
- return true;
- } elseif ( !$this->db->fieldExists( $table, $field, __METHOD__ ) ) {
- $this->output( "...$field field does not exist in $table table, " .
- "skipping modify field patch.\n" );
- return true;
- } elseif ( $this->updateRowExists( $updateKey ) ) {
- $this->output( "...$field in table $table already patched.\n" );
- return true;
- }
-
- # After all checks passed, start the update
- $this->insertUpdateRow( $updateKey );
- $path = 'named_constraints.sql';
- $constraintMap = [
- 'category_types' =>
- "($field in('page', 'subcat', 'file'))",
- 'major_mime' =>
- "($field in('unknown', 'application', 'audio', 'image', 'text', 'video'," .
- " 'message', 'model', 'multipart'))",
- 'media_type' =>
- "($field in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA'," .
- "'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))"
- ];
- $constraint = $constraintMap[$constraintType];
-
- # and hack-in those variables that should be replaced
- # in our template file right now
- $this->db->setSchemaVars( [
- 'tableName' => $table,
- 'fieldName' => $field,
- 'checkConstraint' => $constraint,
- 'wgDBname' => $wgDBname,
- 'wgDBmwschema' => $wgDBmwschema,
- ] );
-
- # Full path from file name
- $path = $this->db->patchPath( $path );
-
- # No need for a cursor allowing result-iteration; just apply a patch
- # store old value for re-setting later
- $wasScrollable = $this->db->scrollableCursor( false );
+ $this->output( "...$table table does not exist, skipping schema update patch.\n" );
+ } elseif ( $this->updateRowExists( $updatekey ) ) {
+ $this->output( "...$table already had schema updated by $patch.\n" );
+ } else {
+ $this->insertUpdateRow( $updatekey );
- # Apply patch
- $this->db->sourceFile( $path );
-
- # Reset DB instance to have original state
- $this->db->setSchemaVars( false );
- $this->db->scrollableCursor( $wasScrollable );
-
- $this->output( "done.\n" );
+ return $this->applyPatch( $patch, $fullpath, "Updating schema of table $table" );
+ }
return true;
}
+++ /dev/null
-DECLARE @fullyQualifiedTableName nvarchar(max),
-@tableName sysname,
-@fieldName sysname,
-@constr sysname,
-@constrNew sysname,
-@sqlcmd nvarchar(max),
-@sqlcreate nvarchar(max)
-
-SET @fullyQualifiedTableName = '/*_*//*$tableName*/'
-SET @tableName = '/*$tableName*/'
-SET @fieldName = '/*$fieldName*/'
-
-SELECT @constr = CONSTRAINT_NAME
-FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
-WHERE TABLE_NAME = @tableName
-AND CONSTRAINT_CATALOG = '/*$wgDBname*/'
-AND CONSTRAINT_SCHEMA = '/*$wgDBmwschema*/'
-AND CONSTRAINT_TYPE = 'CHECK'
-AND CONSTRAINT_NAME LIKE ('CK__' + left(@tableName,9) + '__' + left(@fieldName,5) + '%')
-
-SELECT @constrNew = CONSTRAINT_NAME
-FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
-WHERE TABLE_NAME = @tableName
-AND CONSTRAINT_CATALOG = '/*$wgDBname*/'
-AND CONSTRAINT_SCHEMA = '/*$wgDBmwschema*/'
-AND CONSTRAINT_TYPE = 'CHECK'
-AND CONSTRAINT_NAME = (@fieldName + '_ckc')
-
-IF @constr IS NOT NULL
-BEGIN
- SET @sqlcmd = 'ALTER TABLE ' + @fullyQualifiedTableName + ' DROP CONSTRAINT [' + @constr + ']'
- EXECUTE sp_executesql @sqlcmd
-END
-IF @constrNew IS NULL
-BEGIN
- SET @sqlcreate = 'ALTER TABLE ' + @fullyQualifiedTableName + ' WITH NOCHECK ADD CONSTRAINT ' + @fieldName + '_ckc CHECK /*$checkConstraint*/;'
- EXECUTE sp_executesql @sqlcreate
-END
\ No newline at end of file
--- /dev/null
+-- @since 1.27
+CREATE INDEX /*i*/cl_collation_ext ON /*_*/categorylinks (cl_collation, cl_to, cl_type, cl_from);
--- /dev/null
+--
+-- This table contains a user's bot passwords: passwords that allow access to
+-- the account via the API with limited rights.
+--
+CREATE TABLE /*_*/bot_passwords (
+ bp_user int NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
+ bp_app_id nvarchar(32) NOT NULL,
+ bp_password nvarchar(255) NOT NULL,
+ bp_token nvarchar(255) NOT NULL,
+ bp_restrictions nvarchar(max) NOT NULL,
+ bp_grants nvarchar(max) NOT NULL,
+ PRIMARY KEY (bp_user, bp_app_id)
+);
--- /dev/null
+DECLARE @baseSQL nvarchar(max),
+ @SQL nvarchar(max),
+ @id sysname;--
+
+SET @baseSQL = 'ALTER TABLE /*_*/categorylinks DROP CONSTRAINT ';--
+
+SELECT @id = cc.name
+FROM sys.check_constraints cc
+JOIN sys.columns c
+ ON c.object_id = cc.parent_object_id
+ AND c.column_id = cc.parent_column_id
+WHERE
+ cc.parent_object_id = OBJECT_ID('/*_*/categorylinks')
+ AND c.name = 'cl_type';--
+
+SET @SQL = @baseSQL + @id;--
+
+EXEC sp_executesql @SQL;--
+
+ALTER TABLE /*_*/categorylinks ADD CONSTRAINT cl_type_ckc CHECK (cl_type IN('page', 'subcat', 'file'));
--- /dev/null
+DECLARE @sql nvarchar(max),
+ @id sysname;--
+
+SET @sql = 'ALTER TABLE /*_*/page DROP CONSTRAINT ';--
+
+SELECT @id = df.name
+FROM sys.default_constraints df
+JOIN sys.columns c
+ ON c.object_id = df.parent_object_id
+ AND c.column_id = df.parent_column_id
+WHERE
+ df.parent_object_id = OBJECT_ID('/*_*/page')
+ AND c.name = 'page_counter';--
+
+SET @sql = @sql + @id;--
+
+EXEC sp_executesql @sql;--
+
+ALTER TABLE /*_*/page DROP COLUMN page_counter;
--- /dev/null
+DECLARE @sql nvarchar(max),
+ @id sysname;--
+
+SET @sql = 'ALTER TABLE /*_*/recentchanges DROP CONSTRAINT ';--
+
+SELECT @id = df.name
+FROM sys.default_constraints df
+JOIN sys.columns c
+ ON c.object_id = df.parent_object_id
+ AND c.column_id = df.parent_column_id
+WHERE
+ df.parent_object_id = OBJECT_ID('/*_*/recentchanges')
+ AND c.name = 'rc_cur_time';--
+
+SET @sql = @sql + @id;--
+
+EXEC sp_executesql @sql;--
+
+ALTER TABLE /*_*/recentchanges DROP COLUMN rc_cur_time;
--- /dev/null
+DECLARE @sql nvarchar(max),
+ @id sysname;--
+
+SET @sql = 'ALTER TABLE /*_*/site_stats DROP CONSTRAINT ';--
+
+SELECT @id = df.name
+FROM sys.default_constraints df
+JOIN sys.columns c
+ ON c.object_id = df.parent_object_id
+ AND c.column_id = df.parent_column_id
+WHERE
+ df.parent_object_id = OBJECT_ID('/*_*/site_stats')
+ AND c.name = 'ss_total_views';--
+
+SET @sql = @sql + @id;--
+
+EXEC sp_executesql @sql;--
+
+ALTER TABLE /*_*/site_stats DROP COLUMN ss_total_views;
--- /dev/null
+DECLARE @sql nvarchar(max),
+ @id sysname;--
+
+SET @sql = 'ALTER TABLE /*_*/mwuser DROP CONSTRAINT ';--
+
+SELECT @id = df.name
+FROM sys.default_constraints df
+JOIN sys.columns c
+ ON c.object_id = df.parent_object_id
+ AND c.column_id = df.parent_column_id
+WHERE
+ df.parent_object_id = OBJECT_ID('/*_*/mwuser')
+ AND c.name = 'user_options';--
+
+SET @sql = @sql + @id;--
+
+EXEC sp_executesql @sql;--
+
+ALTER TABLE /*_*/mwuser DROP COLUMN user_options;
--- /dev/null
+DECLARE @baseSQL nvarchar(max),
+ @SQL nvarchar(max),
+ @id sysname;--
+
+SET @baseSQL = 'ALTER TABLE /*_*/filearchive DROP CONSTRAINT ';--
+
+SELECT @id = cc.name
+FROM sys.check_constraints cc
+JOIN sys.columns c
+ ON c.object_id = cc.parent_object_id
+ AND c.column_id = cc.parent_column_id
+WHERE
+ cc.parent_object_id = OBJECT_ID('/*_*/filearchive')
+ AND c.name = 'fa_major_mime';--
+
+SET @SQL = @baseSQL + @id;--
+
+EXEC sp_executesql @SQL;--
+
+SELECT @id = cc.name
+FROM sys.check_constraints cc
+JOIN sys.columns c
+ ON c.object_id = cc.parent_object_id
+ AND c.column_id = cc.parent_column_id
+WHERE
+ cc.parent_object_id = OBJECT_ID('/*_*/filearchive')
+ AND c.name = 'fa_media_type';--
+
+SET @SQL = @baseSQL + @id;--
+
+EXEC sp_executesql @SQL;--
+
+ALTER TABLE /*_*/filearchive ADD CONSTRAINT fa_major_mime_ckc check (fa_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart'));--
+ALTER TABLE /*_*/filearchive ADD CONSTRAINT fa_media_type_ckc check (fa_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'));
--- /dev/null
+-- MediaWiki looks for lines ending with semicolons and sends them as separate queries
+-- However here we *really* need this all to be sent as a single batch. As such, DO NOT
+-- remove the -- from the end of each statement.
+
+DECLARE @temp table (
+ fa_id int,
+ fa_name nvarchar(255),
+ fa_archive_name nvarchar(255),
+ fa_storage_group nvarchar(16),
+ fa_storage_key nvarchar(64),
+ fa_deleted_user int,
+ fa_deleted_timestamp varchar(14),
+ fa_deleted_reason nvarchar(max),
+ fa_size int,
+ fa_width int,
+ fa_height int,
+ fa_metadata nvarchar(max),
+ fa_bits int,
+ fa_media_type varchar(16),
+ fa_major_mime varchar(16),
+ fa_minor_mime nvarchar(100),
+ fa_description nvarchar(255),
+ fa_user int,
+ fa_user_text nvarchar(255),
+ fa_timestamp varchar(14),
+ fa_deleted tinyint,
+ fa_sha1 nvarchar(32)
+);--
+
+INSERT INTO @temp
+SELECT * FROM /*_*/filearchive;--
+
+DROP TABLE /*_*/filearchive;--
+
+CREATE TABLE /*_*/filearchive (
+ fa_id int NOT NULL PRIMARY KEY IDENTITY,
+ 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 varchar(14) default '',
+ fa_deleted_reason nvarchar(max),
+ fa_size int default 0,
+ fa_width int default 0,
+ fa_height int default 0,
+ fa_metadata varbinary(max),
+ fa_bits int default 0,
+ fa_media_type varchar(16) default null,
+ fa_major_mime varchar(16) not null default 'unknown',
+ fa_minor_mime nvarchar(100) default 'unknown',
+ fa_description nvarchar(255),
+ fa_user int default 0 REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
+ fa_user_text nvarchar(255),
+ fa_timestamp varchar(14) default '',
+ fa_deleted tinyint NOT NULL default 0,
+ fa_sha1 nvarchar(32) NOT NULL default '',
+ CONSTRAINT fa_major_mime_ckc check (fa_major_mime in('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
+ CONSTRAINT fa_media_type_ckc check (fa_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
+);--
+
+CREATE INDEX /*i*/fa_name ON /*_*/filearchive (fa_name, fa_timestamp);--
+CREATE INDEX /*i*/fa_storage_group ON /*_*/filearchive (fa_storage_group, fa_storage_key);--
+CREATE INDEX /*i*/fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp);--
+CREATE INDEX /*i*/fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp);--
+CREATE INDEX /*i*/fa_sha1 ON /*_*/filearchive (fa_sha1);--
+
+SET IDENTITY_INSERT /*_*/filearchive ON;--
+
+INSERT INTO /*_*/filearchive
+(
+ fa_id,
+ fa_name,
+ fa_archive_name,
+ fa_storage_group,
+ fa_storage_key,
+ fa_deleted_user,
+ fa_deleted_timestamp,
+ fa_deleted_reason,
+ fa_size,
+ fa_width,
+ fa_height,
+ fa_metadata,
+ fa_bits,
+ fa_media_type,
+ fa_major_mime,
+ fa_minor_mime,
+ fa_description,
+ fa_user,
+ fa_user_text,
+ fa_timestamp,
+ fa_deleted,
+ fa_sha1
+)
+SELECT
+ fa_id,
+ fa_name,
+ fa_archive_name,
+ fa_storage_group,
+ fa_storage_key,
+ fa_deleted_user,
+ fa_deleted_timestamp,
+ fa_deleted_reason,
+ fa_size,
+ fa_width,
+ fa_height,
+ CONVERT(varbinary(max), fa_metadata, 0),
+ fa_bits,
+ fa_media_type,
+ fa_major_mime,
+ fa_minor_mime,
+ fa_description,
+ fa_user,
+ fa_user_text,
+ fa_timestamp,
+ fa_deleted,
+ fa_sha1
+FROM @temp t;--
+
+SET IDENTITY_INSERT /*_*/filearchive OFF;
--- /dev/null
+ALTER TABLE /*_*/imagelinks
+ ADD il_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/il_backlinks_namespace ON /*_*/imagelinks (il_from_namespace,il_to,il_from);
\ No newline at end of file
--- /dev/null
+DECLARE @baseSQL nvarchar(max),
+ @SQL nvarchar(max),
+ @id sysname;--
+
+SET @baseSQL = 'ALTER TABLE /*_*/image DROP CONSTRAINT ';--
+
+SELECT @id = cc.name
+FROM sys.check_constraints cc
+JOIN sys.columns c
+ ON c.object_id = cc.parent_object_id
+ AND c.column_id = cc.parent_column_id
+WHERE
+ cc.parent_object_id = OBJECT_ID('/*_*/image')
+ AND c.name = 'img_major_mime';--
+
+SET @SQL = @baseSQL + @id;--
+
+EXEC sp_executesql @SQL;--
+
+SELECT @id = cc.name
+FROM sys.check_constraints cc
+JOIN sys.columns c
+ ON c.object_id = cc.parent_object_id
+ AND c.column_id = cc.parent_column_id
+WHERE
+ cc.parent_object_id = OBJECT_ID('/*_*/image')
+ AND c.name = 'img_media_type';--
+
+SET @SQL = @baseSQL + @id;--
+
+EXEC sp_executesql @SQL;--
+
+ALTER TABLE /*_*/image ADD CONSTRAINT img_major_mime_ckc check (img_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart'));--
+ALTER TABLE /*_*/image ADD CONSTRAINT img_media_type_ckc check (img_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'));
--- /dev/null
+-- MediaWiki looks for lines ending with semicolons and sends them as separate queries
+-- However here we *really* need this all to be sent as a single batch. As such, DO NOT
+-- remove the -- from the end of each statement.
+
+DECLARE @temp table (
+ img_name varbinary(255),
+ img_size int,
+ img_width int,
+ img_height int,
+ img_metadata varbinary(max),
+ img_bits int,
+ img_media_type varchar(16),
+ img_major_mime varchar(16),
+ img_minor_mime nvarchar(100),
+ img_description nvarchar(255),
+ img_user int,
+ img_user_text nvarchar(255),
+ img_timestamp nvarchar(14),
+ img_sha1 nvarchar(32)
+);--
+
+INSERT INTO @temp
+SELECT * FROM /*_*/image;--
+
+DROP TABLE /*_*/image;--
+
+CREATE TABLE /*_*/image (
+ img_name nvarchar(255) NOT NULL default '' PRIMARY KEY,
+ img_size int NOT NULL default 0,
+ img_width int NOT NULL default 0,
+ img_height int NOT NULL default 0,
+ img_metadata varbinary(max) NOT NULL,
+ img_bits int NOT NULL default 0,
+ img_media_type varchar(16) default null,
+ img_major_mime varchar(16) not null default 'unknown',
+ img_minor_mime nvarchar(100) NOT NULL default 'unknown',
+ img_description nvarchar(255) NOT NULL,
+ img_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
+ img_user_text nvarchar(255) NOT NULL,
+ img_timestamp nvarchar(14) NOT NULL default '',
+ img_sha1 nvarchar(32) NOT NULL default '',
+ CONSTRAINT img_major_mime_ckc check (img_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
+ CONSTRAINT img_media_type_ckc check (img_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
+);--
+
+CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);--
+CREATE INDEX /*i*/img_size ON /*_*/image (img_size);--
+CREATE INDEX /*i*/img_timestamp ON /*_*/image (img_timestamp);--
+CREATE INDEX /*i*/img_sha1 ON /*_*/image (img_sha1);--
+CREATE INDEX /*i*/img_media_mime ON /*_*/image (img_media_type,img_major_mime,img_minor_mime);--
+
+INSERT INTO /*_*/image
+(
+ img_name,
+ img_size,
+ img_width,
+ img_height,
+ img_metadata,
+ img_bits,
+ img_media_type,
+ img_major_mime,
+ img_minor_mime,
+ img_description,
+ img_user,
+ img_user_text,
+ img_timestamp,
+ img_sha1
+)
+SELECT
+ img_name,
+ img_size,
+ img_width,
+ img_height,
+ img_metadata,
+ img_bits,
+ img_media_type,
+ img_major_mime,
+ img_minor_mime,
+ img_description,
+ img_user,
+ img_user_text,
+ img_timestamp,
+ img_sha1
+FROM @temp t;
--- /dev/null
+--
+-- Kill cl_collation index.
+-- @since 1.27
+--
+
+DROP INDEX /*i*/cl_collation ON /*_*/categorylinks;
+
--- /dev/null
+DECLARE @baseSQL nvarchar(max),
+ @SQL nvarchar(max),
+ @id sysname;--
+
+SET @baseSQL = 'ALTER TABLE /*_*/oldimage DROP CONSTRAINT ';--
+
+SELECT @id = cc.name
+FROM sys.check_constraints cc
+JOIN sys.columns c
+ ON c.object_id = cc.parent_object_id
+ AND c.column_id = cc.parent_column_id
+WHERE
+ cc.parent_object_id = OBJECT_ID('/*_*/oldimage')
+ AND c.name = 'oi_major_mime';--
+
+SET @SQL = @baseSQL + @id;--
+
+EXEC sp_executesql @SQL;--
+
+SELECT @id = cc.name
+FROM sys.check_constraints cc
+JOIN sys.columns c
+ ON c.object_id = cc.parent_object_id
+ AND c.column_id = cc.parent_column_id
+WHERE
+ cc.parent_object_id = OBJECT_ID('/*_*/oldimage')
+ AND c.name = 'oi_media_type';--
+
+SET @SQL = @baseSQL + @id;--
+
+EXEC sp_executesql @SQL;--
+
+ALTER TABLE /*_*/oldimage ADD CONSTRAINT oi_major_mime_ckc check (oi_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart'));--
+ALTER TABLE /*_*/oldimage ADD CONSTRAINT oi_media_type_ckc check (oi_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'));
--- /dev/null
+-- MediaWiki looks for lines ending with semicolons and sends them as separate queries
+-- However here we *really* need this all to be sent as a single batch. As such, DO NOT
+-- remove the -- from the end of each statement.
+
+DECLARE @temp table (
+ oi_name varbinary(255),
+ oi_archive_name varbinary(255),
+ oi_size int,
+ oi_width int,
+ oi_height int,
+ oi_bits int,
+ oi_description nvarchar(255),
+ oi_user int,
+ oi_user_text nvarchar(255),
+ oi_timestamp varchar(14),
+ oi_metadata nvarchar(max),
+ oi_media_type varchar(16),
+ oi_major_mime varchar(16),
+ oi_minor_mime nvarchar(100),
+ oi_deleted tinyint,
+ oi_sha1 nvarchar(32)
+);--
+
+INSERT INTO @temp
+SELECT * FROM /*_*/oldimage;--
+
+DROP TABLE /*_*/oldimage;--
+
+CREATE TABLE /*_*/oldimage (
+ oi_name nvarchar(255) NOT NULL default '',
+ oi_archive_name nvarchar(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 int NOT NULL default 0,
+ oi_description nvarchar(255) NOT NULL,
+ oi_user int REFERENCES /*_*/mwuser(user_id),
+ oi_user_text nvarchar(255) NOT NULL,
+ oi_timestamp varchar(14) NOT NULL default '',
+ oi_metadata varbinary(max) NOT NULL,
+ oi_media_type varchar(16) default null,
+ oi_major_mime varchar(16) not null default 'unknown',
+ oi_minor_mime nvarchar(100) NOT NULL default 'unknown',
+ oi_deleted tinyint NOT NULL default 0,
+ oi_sha1 nvarchar(32) NOT NULL default '',
+ CONSTRAINT oi_major_mime_ckc check (oi_major_mime IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message', 'model', 'multipart', 'chemical')),
+ CONSTRAINT oi_media_type_ckc check (oi_media_type IN('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'))
+);--
+
+CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text, oi_timestamp);--
+CREATE INDEX /*i*/oi_name_timestamp ON /*_*/oldimage (oi_name, oi_timestamp);--
+CREATE INDEX /*i*/oi_name_archive_name ON /*_*/oldimage (oi_name, oi_archive_name);--
+CREATE INDEX /*i*/oi_sha1 ON /*_*/oldimage (oi_sha1);--
+
+INSERT INTO /*_*/oldimage
+(
+ oi_name,
+ oi_archive_name,
+ oi_size,
+ oi_width,
+ oi_height,
+ oi_bits,
+ oi_description,
+ oi_user,
+ oi_user_text,
+ oi_timestamp,
+ oi_metadata,
+ oi_media_type,
+ oi_major_mime,
+ oi_minor_mime,
+ oi_deleted,
+ oi_sha1
+)
+SELECT
+ oi_name,
+ oi_archive_name,
+ oi_size,
+ oi_width,
+ oi_height,
+ oi_bits,
+ oi_description,
+ oi_user,
+ oi_user_text,
+ oi_timestamp,
+ CONVERT(varbinary(max), oi_metadata, 0),
+ oi_media_type,
+ oi_major_mime,
+ oi_minor_mime,
+ oi_deleted,
+ oi_sha1
+FROM @temp t;
--- /dev/null
+ALTER TABLE /*_*/pagelinks
+ ADD pl_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks (pl_from_namespace,pl_namespace,pl_title,pl_from);
--- /dev/null
+-- Add a 'sortkey' field to page_props so pages can be efficiently
+-- queried by the numeric value of a property.
+
+ALTER TABLE /*_*/page_props
+ ADD pp_sortkey float DEFAULT NULL;
+
+CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page
+ ON /*_*/page_props ( pp_propname, pp_sortkey, pp_page );
--- /dev/null
+ALTER TABLE /*_*/templatelinks
+ ADD tl_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/tl_backlinks_namespace ON /*_*/templatelinks (tl_from_namespace,tl_namespace,tl_title,tl_from);
--- /dev/null
+DECLARE @baseSQL nvarchar(max),
+ @SQL nvarchar(max),
+ @id sysname;--
+
+SET @baseSQL = 'ALTER TABLE /*_*/uploadstash DROP CONSTRAINT ';--
+
+SELECT @id = cc.name
+FROM sys.check_constraints cc
+JOIN sys.columns c
+ ON c.object_id = cc.parent_object_id
+ AND c.column_id = cc.parent_column_id
+WHERE
+ cc.parent_object_id = OBJECT_ID('/*_*/uploadstash')
+ AND c.name = 'us_media_type';--
+
+SET @SQL = @baseSQL + @id;--
+
+EXEC sp_executesql @SQL;--
+
+ALTER TABLE /*_*/uploadstash ADD CONSTRAINT us_media_type_ckc check (us_media_type in('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE'));
user_newpassword NVARCHAR(255) NOT NULL DEFAULT '',
user_newpass_time varchar(14) NULL DEFAULT NULL,
user_email NVARCHAR(255) NOT NULL DEFAULT '',
- user_options NVARCHAR(MAX) NOT NULL DEFAULT '',
user_touched varchar(14) NOT NULL DEFAULT '',
user_token NCHAR(32) NOT NULL DEFAULT '',
user_email_authenticated varchar(14) DEFAULT NULL,
CREATE UNIQUE CLUSTERED INDEX /*i*/user_properties_user_property ON /*_*/user_properties (up_user,up_property);
CREATE INDEX /*i*/user_properties_property ON /*_*/user_properties (up_property);
+--
+-- This table contains a user's bot passwords: passwords that allow access to
+-- the account via the API with limited rights.
+--
+CREATE TABLE /*_*/bot_passwords (
+ bp_user int NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
+ bp_app_id nvarchar(32) NOT NULL,
+ bp_password nvarchar(255) NOT NULL,
+ bp_token nvarchar(255) NOT NULL,
+ bp_restrictions nvarchar(max) NOT NULL,
+ bp_grants nvarchar(max) NOT NULL,
+ PRIMARY KEY (bp_user, bp_app_id)
+);
+
--
-- Core of the wiki: each page has an entry here which identifies
--
CREATE TABLE /*_*/pagelinks (
pl_from INT NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ pl_from_namespace int NOT NULL DEFAULT 0,
pl_namespace INT NOT NULL DEFAULT 0,
pl_title NVARCHAR(255) NOT NULL DEFAULT '',
);
CREATE UNIQUE INDEX /*i*/pl_from ON /*_*/pagelinks (pl_from,pl_namespace,pl_title);
CREATE UNIQUE INDEX /*i*/pl_namespace ON /*_*/pagelinks (pl_namespace,pl_title,pl_from);
+CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks (pl_from_namespace,pl_namespace,pl_title,pl_from);
--
--
CREATE TABLE /*_*/templatelinks (
tl_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ tl_from_namespace int NOT NULL default 0,
tl_namespace int NOT NULL default 0,
tl_title nvarchar(255) NOT NULL default ''
);
CREATE UNIQUE INDEX /*i*/tl_from ON /*_*/templatelinks (tl_from,tl_namespace,tl_title);
CREATE UNIQUE INDEX /*i*/tl_namespace ON /*_*/templatelinks (tl_namespace,tl_title,tl_from);
+CREATE INDEX /*i*/tl_backlinks_namespace ON /*_*/templatelinks (tl_from_namespace,tl_namespace,tl_title,tl_from);
--
CREATE TABLE /*_*/imagelinks (
-- Key to page_id of the page containing the image / media link.
il_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ il_from_namespace int NOT NULL default 0,
-- Filename of target image.
-- This is also the page_title of the file's description page;
CREATE UNIQUE INDEX /*i*/il_from ON /*_*/imagelinks (il_from,il_to);
CREATE UNIQUE INDEX /*i*/il_to ON /*_*/imagelinks (il_to,il_from);
+CREATE INDEX /*i*/il_backlinks_namespace ON /*_*/imagelinks (il_from_namespace,il_to,il_from);
--
-- Track category inclusions *used inline*
-- Used by the API (and some extensions)
CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
--- FIXME: Not used, delete this
-CREATE INDEX /*i*/cl_collation ON /*_*/categorylinks (cl_collation);
+-- Used when updating collation (e.g. updateCollation.php)
+CREATE INDEX /*i*/cl_collation_ext ON /*_*/categorylinks (cl_collation, cl_to, cl_type, cl_from);
--
-- Track all existing categories. Something is a category if 1) it has an en-
CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from);
CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index);
+-- el_to index intentionally not added; we cannot index nvarchar(max) columns,
+-- but we also cannot restrict el_to to a smaller column size as the external
+-- link may be larger.
--
-- Track interlanguage links
-- Filename.
-- This is also the title of the associated description page,
-- which will be in namespace 6 (NS_FILE).
- img_name varbinary(255) NOT NULL default 0x PRIMARY KEY,
+ img_name nvarchar(255) NOT NULL default '' PRIMARY KEY,
-- File size in bytes.
img_size int NOT NULL default 0,
--
CREATE TABLE /*_*/oldimage (
-- Base filename: key to image.img_name
- oi_name varbinary(255) NOT NULL default 0x REFERENCES /*_*/image(img_name) ON DELETE CASCADE ON UPDATE CASCADE,
+ -- Not a FK because deleting images removes them from image
+ oi_name nvarchar(255) NOT NULL default '',
-- Filename of the archived file.
-- This is generally a timestamp and '!' prepended to the base name.
- oi_archive_name varbinary(255) NOT NULL default 0x,
+ oi_archive_name nvarchar(255) NOT NULL default '',
-- Other fields as in image...
oi_size int NOT NULL default 0,
oi_user_text nvarchar(255) NOT NULL,
oi_timestamp varchar(14) NOT NULL default '',
- oi_metadata nvarchar(max) NOT NULL,
+ oi_metadata varbinary(max) NOT NULL,
oi_media_type varchar(16) default null,
oi_major_mime varchar(16) not null default 'unknown',
oi_minor_mime nvarchar(100) NOT NULL default 'unknown',
CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
CREATE INDEX /*i*/oi_name_timestamp ON /*_*/oldimage (oi_name,oi_timestamp);
--- oi_archive_name truncated to 14 to avoid key length overflow
CREATE INDEX /*i*/oi_name_archive_name ON /*_*/oldimage (oi_name,oi_archive_name);
CREATE INDEX /*i*/oi_sha1 ON /*_*/oldimage (oi_sha1);
fa_size int default 0,
fa_width int default 0,
fa_height int default 0,
- fa_metadata nvarchar(max),
+ fa_metadata varbinary(max),
fa_bits int default 0,
fa_media_type varchar(16) default null,
fa_major_mime varchar(16) not null default 'unknown',
rc_id int NOT NULL PRIMARY KEY IDENTITY,
rc_timestamp varchar(14) not null default '',
- -- This is no longer used
- -- Field kept in database for downgrades
- -- @todo: add drop patch with 1.24
- rc_cur_time varchar(14) NOT NULL default '',
-
-- As in revision
rc_user int NOT NULL default 0 REFERENCES /*_*/mwuser(user_id),
rc_user_text nvarchar(255) NOT NULL,
CREATE TABLE /*_*/page_props (
pp_page int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
pp_propname nvarchar(60) NOT NULL,
- pp_value nvarchar(max) NOT NULL
+ pp_value nvarchar(max) NOT NULL,
+ pp_sortkey float DEFAULT NULL
);
CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props (pp_page,pp_propname);
CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props (pp_propname,pp_page);
+CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page ON /*_*/page_props (pp_propname,pp_sortkey,pp_page);
-- A table to log updates, one text key row per update.
dépôts / lhc / web / wiklou.git / commitdiff