User talk:Dispenser/Wrong Extension

List of files to move

Previously: Mass move .ogg to .ogv (Bot work request)

Can you generate a list with two rows (old name, correct name) then we can mass move it? Thanks. --Steinsplitter (talk) 18:49, 15 March 2017 (UTC)

SELECT img_name AS Cur_name, CONCAT(LEFT(img_name, LENGTH(img_name)-LENGTH(SUBSTRING_INDEX(img_name, ".", -1)) ),
  IF(img_ext IN ("ogv", "opus"    ) AND img_width=0 AND img_media_type="AUDIO" AND img_type="6:\"Vorbis\"",     "oga",
  IF(img_ext IN ("oga","ogg","ogv") AND img_width=0 AND img_media_type="AUDIO" AND img_type="4:\"Opus\"",       "opus",
  IF(img_ext IN ("ogg", "oga"     ) AND img_width>0 AND img_media_type="VIDEO" /*AND img_type="6:\"Theora\""*/, "ogv",
 NULL)))) AS New_name
FROM (SELECT img_name, img_media_type, img_width,
   SUBSTRING_INDEX(LOWER(CONVERT(img_name USING latin1)), ".", -1) AS img_ext,
   IF(img_minor_mime="ogg", SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata,
       "s:4:\"type\";s:", -1), ";", 1), "") AS img_type
  FROM image
  WHERE img_media_type IN ("AUDIO", "VIDEO")
  AND img_major_mime="application" AND img_minor_mime="ogg"
) AS mime_ext
HAVING LOWER(CONVERT(img_name USING utf8mb4))!=LOWER(CONVERT(New_name USING utf8mb4))
   AND New_name IS NOT NULL
   AND LENGTH(New_name) <= 255; /* Max file name length */

@Steinsplitter: That should that about a minute in Quarry. There's a flaw that img_type only has a single type, videos uses type=Theora and type=Vorbis. So I commented the extra check out. Should be fine since we're still checking the width. Also, it uses all lowercase extensions Dispenser (talk) 00:22, 16 March 2017 (UTC)

Perfect, thank! Will do it this days. --Steinsplitter (talk) 11:37, 16 March 2017 (UTC)

| Javelins_Fly_at_Saber_Strike_16_in_Estonia.ogg|Javelins_Fly_at_Saber_Strike_16_in_Estonia.ogv| application | ogg <-- The database tells the file is a ogg? --Steinsplitter (talk) 13:17, 22 March 2017 (UTC)

The MIME for video or audio ogg files is application/ogg. The Media Type (something separate) is VIDEO. Dispenser (talk) 01:25, 23 March 2017 (UTC)

@Dispenser: Done, but i got a number of such errors. --Steinsplitter (talk) 17:31, 31 March 2017 (UTC)

The page was moved in between queries, I've added a check (LOCATE(…) to make sure the original extension is used. Dispenser (talk) 18:12, 31 March 2017 (UTC)

Forgot about uppercase extensions. Anyway, did a little research on a lastIndexOf() in MariaDB and found something that didn't kill performance. Dispenser (talk) 18:36, 31 March 2017 (UTC)

Everything seems moved. --Steinsplitter (talk) 12:55, 1 April 2017 (UTC)

I've reallowed VIDEO for .ogg files since it was burying non-automated items on the list. Dispenser (talk) 03:36, 22 April 2017 (UTC)

Due to T151352: Ogg Opus-File should be classified as AUDIO not MULTIMEDIA-Files being resolved and the datebase updated, I've updated the query as well. Fifteen .oga will be renamed to .opus (keep things consistent) and added code to block names exceeding the max length. Dispenser (talk) 18:38, 25 May 2017 (UTC)

Adding .opus → .oga, because Vorbis files like File:Jose Mari Goenaga (aurkezpena).opus are uploaded. —Dispenser (talk) 03:34, 19 February 2018 (UTC)

Updated query to run on the new analytics database servers. —Dispenser (talk) 01:40, 4 November 2017 (UTC)