-- 2> /dev/null; date; echo '

/* Files missing a description page

 * http://quarry.wmflabs.org/query/899

 *

 * License: Public Domain

 * Runtime: 15 minutes <SLOW_OK>

 */

SELECT STRAIGHT_JOIN

  CONCAT("[[:File:", REPLACE(img_name, "_", " "), "]]", IF(page_latest=0, " (bad history)", "")) AS "File",

  /*IF(img_media_type IN ("AUDIO", "VIDEO"), "—",CONCAT("<b>[[{{ns:6}}:", REPLACE(img_name, "_", " "), "|1x1px|alt=404|Removed]]</b>")) AS "Status",/*-*/

  DATE_FORMAT(img_timestamp, "%Y-%m-%d") AS "Timestamp",

  CONCAT("{{user|", REPLACE(img_user_text, "_", " "), "}}") AS "Uploader",

  IFNULL(GROUP_CONCAT(log_action ORDER BY log_timestamp SEPARATOR ", "),"") AS "Actions",

  (SELECT COUNT(*) FROM globalimagelinks WHERE gil_to=img_name) AS "Usage"

FROM image

LEFT JOIN page ON page_namespace=6 AND page_title=img_name

LEFT JOIN logging_logindex ON log_namespace=6 AND log_title=img_name

AND log_action NOT IN ("patrol")

WHERE page_title IS NULL

OR page_latest=0 /* Missing revisions */

GROUP BY img_name

;-- ' | mysql -ch commonswiki.labsdb commonswiki_p --html > ~/public_html/logs/zombie_images.html; date;