In not as many words as others, here's my MySQL query to extract details on images stored in the MediaWiki CMS system (v1.14).
Why?
Prior to a migration and just after another change freeze, I had sent all Wiki articles modified since the last export but then needed to send all images that had also been either added/modified since.
How?
Using MySQL, the following query lists the image name, size, user who uploaded, timestamp and the path. Remember that the paths are determined using the MD5 Hash of the filename:
copyraw
Note that I've commented out the lines which specify images modified since the 22nd of September 2011. I've left it in there for my own benefit as it's unlikely I'm asked for an entire export.select img_name, img_size, img_user_text, img_timestamp, CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2)) AS img_path, CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2), '/', img_name) AS img_path_with_file from wikimedia_image -- where -- SUBSTRING(img_timestamp, 1, 8)>=20110922 ORDER BY img_timestamp DESC
- select
- img_name,
- img_size,
- img_user_text,
- img_timestamp,
- CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2)) AS img_path,
- CONCAT(SUBSTR(MD5(img_name), 1, 1), '/', SUBSTR(MD5(img_name), 1, 2), '/', img_name) AS img_path_with_file
- from
- wikimedia_image
- -- where
- -- SUBSTRING(img_timestamp, 1, 8)>=20110922
- ORDER BY
- img_timestamp DESC
Should yield something like:
copyraw
img_name img_size img_user_text img_timestamp img_path img_path_with_file ============== ============== ============== ============== ============== ================== Pic_3.jpg 50242 User1 20111129115343 e/e8 e/e8/Pic_3.jpg Pic_2.jpg 38624 User1 20111129115335 8/87 8/87/Pic_2.jpg Pic_1.jpg 41197 User1 20111129115325 e/eb e/eb/Pic_1.jpg Ebl07.jpg 321307 User2 20111129101544 6/66 6/66/Ebl07.jpg Ebl06.jpg 270863 User2 20111129101521 7/74 7/74/Ebl06.jpg Ebl05.jpg 463948 User2 20111129101512 b/b5 b/b5/Ebl05.jpg Ebl03.jpg 318017 User2 20111129101502 1/13 1/13/Ebl03.jpg
- img_name img_size img_user_text img_timestamp img_path img_path_with_file
- ============== ============== ============== ============== ============== ==================
- Pic_3.jpg 50242 User1 20111129115343 e/e8 e/e8/Pic_3.jpg
- Pic_2.jpg 38624 User1 20111129115335 8/87 8/87/Pic_2.jpg
- Pic_1.jpg 41197 User1 20111129115325 e/eb e/eb/Pic_1.jpg
- Ebl07.jpg 321307 User2 20111129101544 6/66 6/66/Ebl07.jpg
- Ebl06.jpg 270863 User2 20111129101521 7/74 7/74/Ebl06.jpg
- Ebl05.jpg 463948 User2 20111129101512 b/b5 b/b5/Ebl05.jpg
- Ebl03.jpg 318017 User2 20111129101502 1/13 1/13/Ebl03.jpg
Category: MediaWiki :: Article: 406
