DBQ-61 Report the total number of subpages of [[commons:deletion_requests]], total number deleted files
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	JiraBugConverter
	Jan 3 2014, 3:46 PM

Description

This issue was converted from https://jira.toolserver.org/browse/DBQ-61.
Summary: Report the total number of subpages of [[commons:deletion_requests]], total number deleted files
Issue type: Task - A task that needs to be done.
Priority: Major
Status: Done
Assignee: (none)

From: Brianna Laugher <brianna.laugher@gmail.com>

Date: Mon, 13 Apr 2009 16:36:56

I would like to get figures on file deletion at Wikimedia Commons. So

of subpages of [[commons:deletion_requests]] / # of pages whose title starts with this prefix (not sure which would be easier)

and

of pages in file/image namespace deleted, ever.

Please report to http://commons.wikimedia.org/wiki/User_talk:Pfctdayelise or by email.

thanks!

Version: unspecified
Severity: major

Details

Reference: bz59316

Event Timeline

• bzimport raised the priority of this task from to Needs Triage.Nov 22 2014, 2:22 AM

• bzimport added a project: Tool-Database-Queries.

• bzimport set Reference to bz59316.

JiraBugConverter created this task.Jan 3 2014, 3:46 PM

From: MZMcBride <mzmcbride@gmail.com>

Date: Mon, 13 Apr 2009 17:09:49

I selected the count of pages where the page_namespace was Commons: and the page_title was like "Deletion_requests/".

mysql> SELECT COUNT(*) FROM page WHERE page_namespace = 4 AND page_title LIKE "Deletion_requests/%";
+----------+
| COUNT(*) |
+----------+
|    36712 | 
+----------+
1 row in set (56.71 sec)

For the second part, I selected the count from the logging table where the action was "delete" and the page_namespace was 6 (Image: / File: ). Two caveats: with this specific query, you'll have double counting if a file was deleted more than one time. And, some files may have been deleted previously but exist now, which may throw off your data. Both of these things can be adjusted for in a query, but I wasn't sure if that's what you wanted. Feel free to re-open this issue if it is.

mysql> SELECT COUNT(*) FROM logging WHERE log_namespace = 6 AND log_type = "delete" AND log_action = "delete";
+----------+
| COUNT(*) |
+----------+
|   665098 | 
+----------+
1 row in set (23 min 14.18 sec)

This bug was imported as RESOLVED. The original assignee has therefore not been
set, and the original reporters/responders have not been added as CC, to
prevent bugspam.

If you re-open this bug, please consider adding these people to the CC list:
Original assignee: (none)
CC list: brianna.laugher@gmail.com, b@mzmcbride.com

DBQ-61 Report the total number of subpages of [[commons:deletion_requests]], total number deleted filesClosed, ResolvedPublicActions