User:Ilmari Karonen/Queries/Zombie images

From Wikimedia Commons, the free media repository
Jump to: navigation, search

This is a list of images on Commons with no description page. This page documents (to the displeasure of the Wikimedia Foundation's data integrity division) files that had their metadata spontaneously deleted. If possible please restore from a database dump.

-- 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",
  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 /* Missing page table */
OR    page_latest=0      /* Missing revisions */
GROUP BY img_name
;-- ' | mysql --defaults-file=~/replica.my.cnf -ch commonswiki.labsdb commonswiki_p --html > ~/public_html/logs/zombie_images.html; date;
File Timestamp Uploader Actions Usage
[[:File:]] NULL Example (talk · contribs) 1