SQL
x
-- 2> /dev/null; date; echo '
/* GIF metadata validity check
*/
USE commonswiki_p;
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",
img_timestamp,
img_width, img_height,
img_bits,
img_framecount,
img_size,
(img_width*img_height*GREATEST(1,img_framecount) + 280*POW(2,img_bits)*GREATEST(1,img_framecount) + 8*1024) AS est_size,
CAST(img_size AS SIGNED) - (img_width*img_height*GREATEST(1,img_framecount) + 280*POW(2,img_bits)*GREATEST(1,img_framecount) + 8*1024*1024) as diff
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 (
/* Check compression ratio */
img_size/(img_width*img_height*GREATEST(1,img_framecount)+768*GREATEST(1,img_framecount)+8*1024)>=1.00
)
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.