GIF size check

This query is marked as a draft This query has been published by Dispenser.
SQL

-- 2> /dev/null; date; echo '
/* GIF metadata validity check
 * 
 * License: Public domain
 * Runtime: 5 minutes
 */
CREATE DATABASE IF NOT EXISTS u2029__p;
/* https://noc.wikimedia.org/conf/highlight.php?file=CommonSettings.php */
SET @wgMaxImageArea=10e7, @wgMaxAnimatedGifArea=10e7; 
CREATE TEMPORARY TABLE u2029__p.gif_size (
  img_framecount INT NOT NULL,
  img_looped TINYINT NOT NULL,
  img_duration FLOAT NOT NULL
) ENGINE=MyISAM AS
SELECT /*SLOW_OK*/
  page_id AS img_page,
  img_name, img_size,
  img_width, img_height,
  img_bits,
  img_timestamp, img_user_text,
  SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata, "\"frameCount\";i:", -1), ";", 1) AS img_framecount,
  SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata, "\"looped\";b:",     -1), ";", 1) AS img_looped,
  SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata, "\"duration\";d:",   -1), ";", 1) AS img_duration
FROM image
JOIN page ON page_namespace=6 AND page_title=img_name
WHERE img_major_mime="image" AND img_minor_mime="gif"
AND img_media_type="BITMAP";

SELECT
  CONCAT("align=left | [[:File:", REPLACE(img_name, "_", " "), "]]") AS "File",
  IF(img_framecount>1 AND img_looped=0,CONCAT("No loop, ",CEIL(img_duration),"&nbsp;s"),
  IF(img_duration>120,CONCAT(ROUND(img_duration/60,1)," mins"), "")) AS "Playback",
  IF(img_framecount=0 OR img_width<=0,     "Bad img_metadata",
   IF(img_width*img_height*img_framecount<=@wgMaxAnimatedGifArea, "",
    /* Determine the severity of freezing */
    IF(img_width*img_height>@wgMaxImageArea,"No rendering",
    IF(img_width<=120 AND img_height<=120, "Icons frozen",
    IF(img_width<=220,                     "Galleries frozen",
    IF(img_width<=800 AND img_height<=600, "Thumbnails frozen",
    /* ELSE */                             "Previews frozen"
  )))))) AS "Scaling",
  IF(img_framecount<=1, "", CONCAT(
   CASE TRUNCATE(ROUND(img_duration/img_framecount, 6), 2)
    WHEN 0.000 THEN "style=background:#000;color:#fff;" /* Bad timing */
    WHEN 0.010 THEN "bgcolor=#ff9090" /* Slowdown*/
    WHEN 0.020 THEN "bgcolor=#ffffdd" /* IE <=9 slowdown */
    WHEN 0.030 THEN "bgcolor=#ffffdd"
    WHEN 0.040 THEN "bgcolor=#ffffdd"
    WHEN 0.050 THEN "bgcolor=#ffffdd"
    ELSE ""
   END, "|", ROUND(img_duration/img_framecount, 3))) AS "Avg<br/>frame<br/>(sec)",
  (SELECT COUNT(*) FROM globalimagelinks WHERE gil_to=img_name AND gil_page_namespace_id=0) AS "Articles"
FROM u2029__p.gif_size
LEFT JOIN categorylinks ON cl_from=img_page AND cl_to IN ("True_color_GIFs")
WHERE cl_from IS NULL AND (
  /* [[mw:Manual:$wgMaxImageArea]] */
  img_framecount=1 AND img_width*img_height>@wgMaxImageArea OR 
  /* [[mw:Manual:$wgMaxAnimatedGifArea]] */
  img_framecount>1 AND img_width*img_height*img_framecount>@wgMaxAnimatedGifArea OR
  /* No loops */
  img_framecount>1 AND img_looped=0 OR /*-*/
  /* GIF slowdown, IE9 ended support 12-Jan-2016 */
  img_framecount>1 AND ROUND(img_duration/img_framecount,6)<0.060 AND img_timestamp<="20160112" OR
  img_framecount>1 AND ROUND(img_duration/img_framecount,6)<0.020 AND img_timestamp >"20160112" OR
  /* Long animations */
  img_duration>120 OR
  img_duration<1 AND img_framecount>1200 OR 
  /* Check compression ratio */
  img_size/(img_width*img_height*img_framecount+768*img_framecount+8*1024)>=2.00 OR
  /* Sanity check */
  img_width<=0 OR img_height<=0 OR
  img_duration<0.0 OR img_framecount<=0 OR img_looped NOT IN (0,1)
)
ORDER BY img_framecount=0, img_framecount=1 OR img_looped=1, Scaling DESC,
  LEAST(TRUNCATE(ROUND(img_duration/img_framecount, 6),2), 0.060) ASC, 1 ASC
;-- ' | mysql -ch commonswiki.labsdb commonswiki_p --html > ~/public_html/temp/logs/gif_check.html; date;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
