Recherche par type de contenu MIME (fr)

SQL

-- 2> /dev/null; date; echo '
/* MIME type mismatches file extension
 * 
 * License: Public domain
 * Run time: 25 minutes <SLOW_OK>
 */
USE frwiki_p;
CREATE DATABASE IF NOT EXISTS u2029_p;
CREATE TEMPORARY TABLE u2029_p.mime_ext (
  img_page INT(8) UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=MyISAM AS 
SELECT page_id AS img_page, img_width, img_height,
  SUBSTRING_INDEX(LOWER(CONVERT(img_name USING latin1)), ".", -1) AS img_ext,
  img_media_type, img_major_mime, img_minor_mime
FROM image
JOIN page ON page_namespace=6 AND page_title=img_name
GROUP BY page_id;
 
SELECT
  CONCAT("[[:File:", REPLACE(page_title, "_", " "), "]]") AS "File",
  img_ext AS "Ext",
  CONCAT(img_major_mime, "/", img_minor_mime) AS "MIME",
  (SELECT COUNT(DISTINCT oi_minor_mime) FROM oldimage WHERE oi_name=page_title AND oi_minor_mime!=img_minor_mime) AS Conflicts,
  (SELECT COUNT(*) FROM globalimagelinks WHERE gil_to=page_title) AS "Global usage"
FROM u2029_p.mime_ext
JOIN page ON page_id=img_page
WHERE NOT (
   (img_ext="djvu" AND img_major_mime="image" AND img_minor_mime="vnd.djvu" AND img_width>0)
OR (img_ext="gif"  AND img_major_mime="image" AND img_minor_mime="gif"  AND img_width>0)
OR (img_ext="jpeg" AND img_major_mime="image" AND img_minor_mime="jpeg" AND img_width>0)
OR (img_ext="jpg"  AND img_major_mime="image" AND img_minor_mime="jpeg" AND img_width>0)
OR (img_ext="flac" AND img_major_mime="audio" AND img_minor_mime="x-flac" AND img_width=0)
OR (img_ext="mid"  AND img_major_mime="audio" AND img_minor_mime="midi" AND img_width=0)
OR (img_ext="wav"  AND img_major_mime="audio" AND img_minor_mime="wav"  AND img_width=0)
OR (img_ext="ogg"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_media_type="AUDIO" AND img_width=0)
OR (img_ext="ogg"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_media_type="VIDEO" AND img_width>0)
OR (img_ext="oga"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_width=0)
OR (img_ext="ogv"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_width>0)
OR (img_ext="pdf"  AND img_major_mime="application" AND img_minor_mime="pdf" AND img_width>0)
OR (img_ext="png"  AND img_major_mime="image" AND img_minor_mime="png" AND img_width>0)
OR (img_ext="svg"  AND img_major_mime="image" AND img_minor_mime="svg+xml" AND img_width>0)
OR (img_ext="tif"  AND img_major_mime="image" AND img_minor_mime="tiff" AND img_width>0)
OR (img_ext="tiff" AND img_major_mime="image" AND img_minor_mime="tiff" AND img_width>0)
OR (img_ext="xcf"  AND img_major_mime="image" AND img_minor_mime="x-xcf" AND img_width>0)
OR (img_ext="webm" AND img_major_mime="video" AND img_minor_mime="webm" AND img_width>0)
)
ORDER BY 3, 2, 1
;-- ' | mysql -ch commonswiki.labsdb commonswiki_p --html > ~/public_html/logs/wrongext-commonswiki.html; date
SQL

Checking query status...
