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

diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php
index 38a9ede..b04933f 100644 (file)
--- 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 bce4690..8c567ee 100644 (file)
--- 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 60ac23c..5229a91 100644 (file)
--- 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 2bfadf4..54cbce0 100644 (file)
--- 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 3a755b6..54eaf12 100644 (file)
--- 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 (file)
index 0000000..d098c80
--- /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 (file)
index 0000000..bc51b52
--- /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 5348c47..b3340da 100644 (file)
--- 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 (file)
index 0000000..5995b24
--- /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 110188d..b24e0fe 100644 (file)
--- 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 01177d8..8871c81 100644 (file)
--- 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 bf15a04..19c4d3a 100644 (file)
--- 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 (file)
index 0000000..dd8959e
--- /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 d633a9c..47cd75b 100644 (file)
--- 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,