From 267af09d32ff4e4f4da751e83adf97c7022edfce Mon Sep 17 00:00:00 2001 From: Brad Jorsch Date: Wed, 7 Mar 2018 10:40:27 -0500 Subject: [PATCH] DB: Add img_description_id column This begins the process of merging image_comment_temp into the image table by adding the needed column. Iab5f5215 will adjust the code to use it and to add the necessary migration script. Note this patch puts the new schema change in the 1.30 section rather than the 1.31 section. This allows Iab5f5215 to have migrateComments.php migrate the comments directly to the new field instead of having to populate and then depopulate the temporary table. Bug: T188132 Change-Id: I2485c5a758bf03bb2b4991eea920abd9d0d30bda --- includes/installer/MssqlUpdater.php | 3 ++ includes/installer/MysqlUpdater.php | 4 ++ includes/installer/OracleUpdater.php | 3 ++ includes/installer/PostgresUpdater.php | 4 ++ includes/installer/SqliteUpdater.php | 4 ++ .../patch-image-img_description_id.sql | 7 +++ .../patch-image-img_description_id.sql | 6 +++ maintenance/mssql/tables.sql | 1 + .../patch-image-img_description_id.sql | 7 +++ maintenance/oracle/tables.sql | 2 + maintenance/postgres/tables.sql | 1 + .../sqlite/archives/patch-actor-table.sql | 9 ++-- .../patch-image-img_description_id.sql | 47 +++++++++++++++++++ maintenance/tables.sql | 4 +- 14 files changed, 97 insertions(+), 5 deletions(-) create mode 100644 maintenance/archives/patch-image-img_description_id.sql create mode 100644 maintenance/mssql/archives/patch-image-img_description_id.sql create mode 100644 maintenance/oracle/archives/patch-image-img_description_id.sql create mode 100644 maintenance/sqlite/archives/patch-image-img_description_id.sql diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index 38a9ede4b1..b04933fb26 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -108,6 +108,9 @@ class MssqlUpdater extends DatabaseUpdater { // Should have been in 1.30 [ 'addTable', 'comment', 'patch-comment-table.sql' ], + // This field was added in 1.31, but is put here so it can be used by 'migrateComments' + [ 'addField', 'image', 'img_description_id', 'patch-image-img_description_id.sql' ], + // Should have been in 1.30 [ 'migrateComments' ], // 1.31 diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php index bce469053b..8c567eee3c 100644 --- a/includes/installer/MysqlUpdater.php +++ b/includes/installer/MysqlUpdater.php @@ -325,6 +325,10 @@ class MysqlUpdater extends DatabaseUpdater { [ 'renameIndex', 'user_properties', 'user_properties_user_property', 'PRIMARY', false, 'patch-user_properties-fix-pk.sql' ], [ 'addTable', 'comment', 'patch-comment-table.sql' ], + + // This field was added in 1.31, but is put here so it can be used by 'migrateComments' + [ 'addField', 'image', 'img_description_id', 'patch-image-img_description_id.sql' ], + [ 'migrateComments' ], [ 'renameIndex', 'l10n_cache', 'lc_lang_key', 'PRIMARY', false, 'patch-l10n_cache-primary-key.sql' ], diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index 60ac23c1e0..5229a91946 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -129,6 +129,9 @@ class OracleUpdater extends DatabaseUpdater { // Should have been in 1.30 [ 'addTable', 'comment', 'patch-comment-table.sql' ], + // This field was added in 1.31, but is put here so it can be used by 'migrateComments' + [ 'addField', 'image', 'img_description_id', 'patch-image-img_description_id.sql' ], + // Should have been in 1.30 [ 'migrateComments' ], // 1.31 diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 2bfadf4adf..54cbce0cb8 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -481,6 +481,10 @@ class PostgresUpdater extends DatabaseUpdater { [ 'changeNullableField', 'protected_titles', 'pt_reason', 'NOT NULL', true ], [ 'addPgField', 'protected_titles', 'pt_reason_id', 'INTEGER NOT NULL DEFAULT 0' ], [ 'addTable', 'comment', 'patch-comment-table.sql' ], + + // This field was added in 1.31, but is put here so it can be used by 'migrateComments' + [ 'addPgField', 'image', 'img_description_id', 'INTEGER NOT NULL DEFAULT 0' ], + [ 'migrateComments' ], [ 'addIndex', 'site_stats', 'site_stats_pkey', 'patch-site_stats-pk.sql' ], [ 'addTable', 'ip_changes', 'patch-ip_changes.sql' ], diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php index 3a755b6d40..54eaf1215a 100644 --- a/includes/installer/SqliteUpdater.php +++ b/includes/installer/SqliteUpdater.php @@ -190,6 +190,10 @@ class SqliteUpdater extends DatabaseUpdater { [ 'renameIndex', 'user_properties', 'user_properties_user_property', 'PRIMARY', false, 'patch-user_properties-fix-pk.sql' ], [ 'addTable', 'comment', 'patch-comment-table.sql' ], + + // This field was added in 1.31, but is put here so it can be used by 'migrateComments' + [ 'addField', 'image', 'img_description_id', 'patch-image-img_description_id.sql' ], + [ 'migrateComments' ], [ 'renameIndex', 'l10n_cache', 'lc_lang_key', 'PRIMARY', false, 'patch-l10n_cache-primary-key.sql' ], diff --git a/maintenance/archives/patch-image-img_description_id.sql b/maintenance/archives/patch-image-img_description_id.sql new file mode 100644 index 0000000000..d098c80b09 --- /dev/null +++ b/maintenance/archives/patch-image-img_description_id.sql @@ -0,0 +1,7 @@ +-- +-- patch-image-img_description_id.sql +-- +-- T188132. Add `img_description_id` to the `image` table. + +ALTER TABLE /*_*/image + ADD COLUMN img_description_id bigint unsigned NOT NULL DEFAULT 0 AFTER img_description; diff --git a/maintenance/mssql/archives/patch-image-img_description_id.sql b/maintenance/mssql/archives/patch-image-img_description_id.sql new file mode 100644 index 0000000000..bc51b529c2 --- /dev/null +++ b/maintenance/mssql/archives/patch-image-img_description_id.sql @@ -0,0 +1,6 @@ +-- +-- patch-image-img_description_id.sql +-- +-- T188132. Add `img_description_id` to the `image` table. + +ALTER TABLE /*_*/image ADD img_description_id bigint NOT NULL CONSTRAINT DF_img_description_id DEFAULT 0 CONSTRAINT FK_img_description_id FOREIGN KEY REFERENCES /*_*/comment(comment_id); diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 5348c47d9b..b3340da497 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -739,6 +739,7 @@ CREATE TABLE /*_*/image ( -- Description field as entered by the uploader. -- This is displayed in image upload history and logs. img_description nvarchar(255) NOT NULL CONSTRAINT DF_img_description DEFAULT '', + img_description_id bigint NOT NULL CONSTRAINT DF_img_description_id DEFAULT 0 CONSTRAINT FK_img_description_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), -- user_id and user_name of uploader. img_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL, diff --git a/maintenance/oracle/archives/patch-image-img_description_id.sql b/maintenance/oracle/archives/patch-image-img_description_id.sql new file mode 100644 index 0000000000..5995b24281 --- /dev/null +++ b/maintenance/oracle/archives/patch-image-img_description_id.sql @@ -0,0 +1,7 @@ +-- +-- patch-image-img_description_id.sql +-- +-- T188132. Add `img_description_id` to the `image` table. + +ALTER TABLE &mw_prefix.image ADD ( img_description_id NUMBER DEFAULT 0 NOT NULL ); +ALTER TABLE &mw_prefix.image ADD CONSTRAINT &mw_prefix.oldimage_fk2 FOREIGN KEY (img_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 110188d300..b24e0fe226 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -517,6 +517,7 @@ CREATE TABLE &mw_prefix.image ( img_major_mime VARCHAR2(32) DEFAULT 'unknown', img_minor_mime VARCHAR2(100) DEFAULT 'unknown', img_description VARCHAR2(255), + img_description_id NUMBER DEFAULT 0 NOT NULL, img_user NUMBER DEFAULT 0 NOT NULL, img_user_text VARCHAR2(255) NULL, img_actor NUMBER DEFAULT 0 NOT NULL, @@ -525,6 +526,7 @@ CREATE TABLE &mw_prefix.image ( ); ALTER TABLE &mw_prefix.image ADD CONSTRAINT &mw_prefix.image_pk PRIMARY KEY (img_name); ALTER TABLE &mw_prefix.image ADD CONSTRAINT &mw_prefix.image_fk1 FOREIGN KEY (img_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.image ADD CONSTRAINT &mw_prefix.image_fk2 FOREIGN KEY (img_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.image_i01 ON &mw_prefix.image (img_user_text,img_timestamp); CREATE INDEX &mw_prefix.image_i02 ON &mw_prefix.image (img_size); CREATE INDEX &mw_prefix.image_i03 ON &mw_prefix.image (img_timestamp); diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 01177d86b7..8871c81de3 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -421,6 +421,7 @@ CREATE TABLE image ( img_major_mime TEXT DEFAULT 'unknown', img_minor_mime TEXT DEFAULT 'unknown', img_description TEXT NOT NULL DEFAULT '', + img_description_id INTEGER NOT NULL DEFAULT 0, img_user INTEGER NOT NULL DEFAULT 0 REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, img_user_text TEXT NOT NULL DEFAULT '', img_actor INTEGER NOT NULL DEFAULT 0, diff --git a/maintenance/sqlite/archives/patch-actor-table.sql b/maintenance/sqlite/archives/patch-actor-table.sql index bf15a04ca6..19c4d3add3 100644 --- a/maintenance/sqlite/archives/patch-actor-table.sql +++ b/maintenance/sqlite/archives/patch-actor-table.sql @@ -134,6 +134,7 @@ CREATE TABLE /*_*/image_tmp ( img_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown", img_minor_mime varbinary(100) NOT NULL default "unknown", img_description varbinary(767) NOT NULL default '', + img_description_id bigint unsigned NOT NULL DEFAULT 0, img_user int unsigned NOT NULL default 0, img_user_text varchar(255) binary NOT NULL DEFAULT '', img_actor bigint unsigned NOT NULL DEFAULT 0, @@ -143,12 +144,12 @@ CREATE TABLE /*_*/image_tmp ( INSERT OR IGNORE INTO /*_*/image_tmp ( 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) + img_media_type, img_major_mime, img_minor_mime, img_description, + img_description_id, 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 + img_media_type, img_major_mime, img_minor_mime, img_description, + img_description_id, img_user, img_user_text, img_timestamp, img_sha1 FROM /*_*/image; DROP TABLE /*_*/image; diff --git a/maintenance/sqlite/archives/patch-image-img_description_id.sql b/maintenance/sqlite/archives/patch-image-img_description_id.sql new file mode 100644 index 0000000000..dd8959e0a5 --- /dev/null +++ b/maintenance/sqlite/archives/patch-image-img_description_id.sql @@ -0,0 +1,47 @@ +-- +-- patch-image-img_description_id.sql +-- +-- T188132. Add `img_description_id` to the `image` table. + +BEGIN; + +DROP TABLE IF EXISTS /*_*/image_tmp; +CREATE TABLE /*_*/image_tmp ( + img_name varchar(255) binary NOT NULL default '' PRIMARY KEY, + img_size int unsigned NOT NULL default 0, + img_width int NOT NULL default 0, + img_height int NOT NULL default 0, + img_metadata mediumblob NOT NULL, + img_bits int NOT NULL default 0, + img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL, + img_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown", + img_minor_mime varbinary(100) NOT NULL default "unknown", + img_description varbinary(767) NOT NULL default '', + img_description_id bigint unsigned NOT NULL DEFAULT 0, + img_user int unsigned NOT NULL default 0, + img_user_text varchar(255) binary NOT NULL default '', + img_timestamp varbinary(14) NOT NULL default '', + img_sha1 varbinary(32) NOT NULL default '' +) /*$wgDBTableOptions*/; + + +INSERT OR IGNORE INTO /*_*/image_tmp ( + 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 /*_*/image; + +DROP TABLE /*_*/image; +ALTER TABLE /*_*/image_tmp RENAME TO /*_*/image; +CREATE INDEX /*i*/img_user_timestamp ON /*_*/image (img_user,img_timestamp); +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(10)); +CREATE INDEX /*i*/img_media_mime ON /*_*/image (img_media_type,img_major_mime,img_minor_mime); + +COMMIT; diff --git a/maintenance/tables.sql b/maintenance/tables.sql index d633a9c209..47cd75bf3e 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -1144,9 +1144,11 @@ CREATE TABLE /*_*/image ( -- Description field as entered by the uploader. -- This is displayed in image upload history and logs. - -- Deprecated in favor of image_comment_temp.imgcomment_description_id. + -- Deprecated in favor of img_description_id. img_description varbinary(767) NOT NULL default '', + img_description_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that img_description should be used) + -- user_id and user_name of uploader. -- Deprecated in favor of img_actor. img_user int unsigned NOT NULL default 0, -- 2.20.1