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