User:Dispenser/Double extension

From Wikimedia Commons, the free media repository
Jump to: navigation, search
-- 2> /dev/null; date; echo '
/* Double file extensions
 * http://quarry.wmflabs.org/query/894
 * License: Public Domain
 * Run time: 10 minutes <SLOW_OK>
 */
SELECT CONCAT("* {{/link|File:", REPLACE(REPLACE(page_title, "_", " "), "=", "&#61;"), "}}") AS Filename
FROM page
WHERE page_namespace=6 AND page_is_redirect=0
AND CONVERT(page_title USING utf8) REGEXP "\\.(djvu|gif|jpeg|jpg|flac|mid|wav|ogg|oga|ogv|pdf|png|svg|tif[^f]|tiff|xcf|webm)."
AND CONVERT(page_title USING utf8) NOT REGEXP "/|\\.webm[sh]d\\.webm$|\\.oggtheora\\.og[agv]$|\\.oggvorbis\\.ogg$"
ORDER BY
  /* .TIFFany at bottom */
  CONVERT(page_title USING utf8) REGEXP "\\.(djvu|gif|jpe?g|flac|mid|wav|og[agv]|pdf|png|svg|tiff?|xcf|webm)[^[:alpha:]]" DESC,
  /* .svg.png or .jpeg.jpg at top */
  page_title REGEXP "\\.[[:alpha:]]{3,4}\\.[[:alpha:]]{3,4}$" DESC,
  /* Separate R0012131.JPG (8332852801).jpg */
  LENGTH(page_title)>24 AND LOCATE(".", REVERSE(page_title), 6) > 16,
  /* ASCIIbetical sort */
  BINARY page_title
;-- ' | sql commonswiki_p > ~/public_html/logs/dblextension.txt; date;

The following 8,552 files has either a double extension (e.g. .jpg.svg or .JPG".jpg) or an extension in the middle of the name (e.g. File:US Navy 020305-N-9769P-031.JPG Bombs ready on flight deck.jpg). This list needs review for mistakes (e.g. File:Singer.Model27.Tiffany.decal.jpg). Other wikis (like English Wikipedia) can use the grep tool to get similar results.