Rillke has uploaded a new change for review.
https://gerrit.wikimedia.org/r/135714
Change subject: Name implicitly created CHECK constraints
......................................................................
Name implicitly created CHECK constraints
to ease future maintenance work. The pattern for catching the right
CHECK constraint is admittedly vague but it doesn't really matter
which auto-created CHECK constraint of a table is removed at which
time assuming the update is always completed before another update
is applied. If updates are run through Update.php, that should be
always the case.
Furthermore, eliminating a bug that caused all patches would be
applied more than once.
Bug: 65757
Bug: 65813
Change-Id: Ic7fc3bd836241dce8f296237bbd80ed3e4d1ee0d
---
M RELEASE-NOTES-1.24
M includes/installer/DatabaseUpdater.php
M includes/installer/MssqlUpdater.php
A maintenance/mssql/archives/named_constraints.sql
M maintenance/mssql/tables.sql
5 files changed, 130 insertions(+), 13 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core
refs/changes/14/135714/1
diff --git a/RELEASE-NOTES-1.24 b/RELEASE-NOTES-1.24
index f83ed46..2ee1193 100644
--- a/RELEASE-NOTES-1.24
+++ b/RELEASE-NOTES-1.24
@@ -62,6 +62,8 @@
* (bug 52587) Maintenance script deleteBatch.php no longer follows redirects
in the file namespace and delete the file on the target page. It will still
however delete the redirect page.
+* (bug 65757) MSSQL: Make sure the update script was able to drop all unnamed
+ constraints to be prepared for future updates.
=== Web API changes in 1.24 ===
* action=parse API now supports prop=modules, which provides the list of
diff --git a/includes/installer/DatabaseUpdater.php
b/includes/installer/DatabaseUpdater.php
old mode 100644
new mode 100755
index 82a358e..a69be8a
--- a/includes/installer/DatabaseUpdater.php
+++ b/includes/installer/DatabaseUpdater.php
@@ -476,7 +476,7 @@
public function updateRowExists( $key ) {
$row = $this->db->selectRow(
'updatelog',
- '1',
+ '1 AS X',
array( 'ul_key' => $key ),
__METHOD__
);
diff --git a/includes/installer/MssqlUpdater.php
b/includes/installer/MssqlUpdater.php
old mode 100644
new mode 100755
index f9c4287..7bfd3f5
--- a/includes/installer/MssqlUpdater.php
+++ b/includes/installer/MssqlUpdater.php
@@ -35,12 +35,90 @@
*/
protected $db;
+ /**
+ * Drops unnamed and creates named constraints following the pattern
+ * CKC__<TableName>_<Col>
+ *
+ * @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.
+ */
+ protected function updateConstraints( $constraintType, $table, $field )
{
+ if ( !$this->doTable( $table ) ) {
+ return true;
+ }
+
+ $this->output( "...updating constraints on [$table].[$field]
..." );
+ $updateKey = "$table-$field-ck$constraintType";
+ 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 = array(
+ '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( array(
+ 'tableName' => "$table",
+ 'fieldName' => "$field",
+ 'checkConstraint' => "$constraint"
+ ) );
+
+ # 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 );
+
+ # 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 true;
+ }
+
protected function getCoreUpdateList() {
return array(
// 1.23
array( 'addField', 'mwuser', 'user_password_expires',
'patch-user_password_expires.sql' ),
// 1.24
+ // Constraint updates
+ array( 'updateConstraints', 'category_types',
'categorylinks', 'cl_type' ),
+ array( 'updateConstraints', 'major_mime',
'filearchive', 'fa_major_mime' ),
+ array( 'updateConstraints', 'media_type',
'filearchive', 'fa_media_type' ),
+ array( 'updateConstraints', 'major_mime', 'oldimage',
'oi_major_mime' ),
+ array( 'updateConstraints', 'media_type', 'oldimage',
'oi_media_type' ),
+ array( 'updateConstraints', 'major_mime', 'image',
'img_major_mime' ),
+ array( 'updateConstraints', 'media_type', 'image',
'img_media_type' ),
+ array( 'updateConstraints', 'media_type',
'uploadstash', 'us_media_type' ),
+ // END: Constraint updates
);
}
}
diff --git a/maintenance/mssql/archives/named_constraints.sql
b/maintenance/mssql/archives/named_constraints.sql
new file mode 100755
index 0000000..0275bf8
--- /dev/null
+++ b/maintenance/mssql/archives/named_constraints.sql
@@ -0,0 +1,26 @@
+DECLARE @fullyQualifiedTableName nvarchar(max),
+@tableName sysname,
+@fieldName sysname,
+@constr 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_TYPE = 'CHECK'
+AND CONSTRAINT_NAME LIKE ('CK__' + left(@tableName,9) + '__' +
left(@fieldName,5) + '%')
+
+IF NOT @constr IS NULL
+BEGIN
+ SELECT @sqlcmd = 'ALTER TABLE ' + @fullyQualifiedTableName + ' DROP
CONSTRAINT [' + @constr + ']'
+ EXECUTE sp_executesql @sqlcmd
+END
+BEGIN
+ SELECT @sqlcreate = 'ALTER TABLE ' + @fullyQualifiedTableName + ' WITH
NOCHECK ADD CONSTRAINT CKC__' + @tableName + '_' + @fieldName + ' CHECK
/*$checkConstraint*/;'
+ EXECUTE sp_executesql @sqlcreate
+END
\ No newline at end of file
diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql
old mode 100644
new mode 100755
index fb8db08..78e2930
--- a/maintenance/mssql/tables.sql
+++ b/maintenance/mssql/tables.sql
@@ -298,8 +298,9 @@
-- paginate the three categories separately. This never has to be updated
-- after the page is created, since none of these page types can be moved to
-- any other.
+ cl_type varchar(10) NOT NULL default 'page',
-- SQL server doesn't have enums, so we approximate with this
- cl_type varchar(10) NOT NULL default 'page' CHECK (cl_type IN('page',
'subcat', 'file'))
+ CONSTRAINT CKC__categorylinks_cl_type CHECK (cl_type IN('page', 'subcat',
'file'))
);
CREATE UNIQUE INDEX /*i*/cl_from ON /*_*/categorylinks (cl_from,cl_to);
@@ -566,11 +567,11 @@
img_bits int NOT NULL default 0,
-- Media type as defined by the MEDIATYPE_xxx constants
- img_media_type varchar(16) default null check (img_media_type in('UNKNOWN',
'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT',
'EXECUTABLE', 'ARCHIVE')),
+ img_media_type varchar(16) default null,
-- major part of a MIME media type as defined by IANA
-- see http://www.iana.org/assignments/media-types/
- img_major_mime varchar(16) not null default 'unknown' check (img_major_mime
IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message',
'model', 'multipart')),
+ img_major_mime varchar(16) not null default 'unknown',
-- minor part of a MIME media type as defined by IANA
-- the minor parts are not required to adher to any standard
@@ -590,7 +591,10 @@
img_timestamp nvarchar(14) NOT NULL default '',
-- SHA-1 content hash in base-36
- img_sha1 nvarchar(32) NOT NULL default ''
+ img_sha1 nvarchar(32) NOT NULL default '',
+
+ CONSTRAINT CKC__image_img_major_mime check (img_major_mime IN('unknown',
'application', 'audio', 'image', 'text', 'video', 'message', 'model',
'multipart')),
+ CONSTRAINT CKC__image_img_media_type 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);
@@ -628,11 +632,14 @@
oi_timestamp varchar(14) NOT NULL default '',
oi_metadata nvarchar(max) NOT NULL,
- oi_media_type varchar(16) default null check (oi_media_type IN('UNKNOWN',
'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT',
'EXECUTABLE', 'ARCHIVE')),
- oi_major_mime varchar(16) not null default 'unknown' check (oi_major_mime
IN('unknown', 'application', 'audio', 'image', 'text', 'video', 'message',
'model', 'multipart')),
+ 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 ''
+ oi_sha1 nvarchar(32) NOT NULL default '',
+
+ CONSTRAINT CKC__oldimage_oi_major_mime check (oi_major_mime IN('unknown',
'application', 'audio', 'image', 'text', 'video', 'message', 'model',
'multipart')),
+ CONSTRAINT CKC__oldimage_oi_media_type 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);
@@ -678,8 +685,8 @@
fa_height int default 0,
fa_metadata nvarchar(max),
fa_bits int default 0,
- fa_media_type varchar(16) default null check (fa_media_type in('UNKNOWN',
'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT',
'EXECUTABLE', 'ARCHIVE')),
- fa_major_mime varchar(16) not null default 'unknown' check (fa_major_mime
in('unknown', 'application', 'audio', 'image', 'text', 'video', 'message',
'model', 'multipart')),
+ 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,
@@ -690,7 +697,10 @@
fa_deleted tinyint NOT NULL default 0,
-- sha1 hash of file content
- fa_sha1 nvarchar(32) NOT NULL default ''
+ fa_sha1 nvarchar(32) NOT NULL default '',
+
+ CONSTRAINT CKC__filearchive_fa_major_mime check (fa_major_mime in('unknown',
'application', 'audio', 'image', 'text', 'video', 'message', 'model',
'multipart')),
+ CONSTRAINT CKC__filearchive_fa_media_type check (fa_media_type in('UNKNOWN',
'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT',
'EXECUTABLE', 'ARCHIVE'))
);
-- pick out by image name
@@ -745,12 +755,13 @@
us_sha1 nvarchar(31) NOT NULL,
us_mime nvarchar(255),
-- Media type as defined by the MEDIATYPE_xxx constants, should duplicate
definition in the image table
- us_media_type varchar(16) default null check (us_media_type in('UNKNOWN',
'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT',
'EXECUTABLE', 'ARCHIVE')),
+ us_media_type varchar(16) default null,
-- image-specific properties
us_image_width int,
us_image_height int,
- us_image_bits smallint
+ us_image_bits smallint,
+ CONSTRAINT CKC__uploadstash_us_media_type check (us_media_type in('UNKNOWN',
'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT',
'EXECUTABLE', 'ARCHIVE'))
);
-- sometimes there's a delete for all of a user's stuff.
--
To view, visit https://gerrit.wikimedia.org/r/135714
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ic7fc3bd836241dce8f296237bbd80ed3e4d1ee0d
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: Rillke <ril...@wikipedia.de>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
