Wikipedia:Bots/Requests for approval/DASHBot 10

The following discussion is an archived debate. Please do not modify it. To request review of this BRFA, please start a new section at WT:BRFA. The result of the discussion was

Approved.

DASHBot 10[edit]

Operator: Tim1357 (talk · contribs)

Automatic or Manually assisted: Automatic

Programming language(s): Python

Source code available: Sure, email me.

Function overview: Find orphaned fair use files and tag them for deletion.

Links to relevant discussions (where appropriate):

Edit period(s): Every other night, but thats changeable.

Estimated number of pages affected: A few every few days, after the backlog (currently 4400 files) is cleared.

Exclusion compliant (Y/N): I'll work it in there.

Already has a bot flag (Y/N): Yes, but it wont use one. All edits are major enough to be in the watchlist.

Function details:

Use sql to find all the orphaned images, with the uploading user (I tweaked some of CBM's code). Images must be:
- Not already tagged for deletion.
- Not used in the enwikipedia articlespace
- Older then 48 hours.

- Has no redirects with links either.
Add {{subst:di-orphaned fair use-notice}} ~~~~ to the bottom of the user talk pages of the corresponding uploaders.
Check back every few hours to see if the images have been re-included in the article space. If so, de-tagg. (This is true of all orphaned fair use images, tagged for deletion)
Wait 48 hours.^*
Repeat.

* The bot checks to see if the images were re-linked every few hours.

Discussion[edit]

BRFA Opened 01:27, 8 March 2010 (UTC)

Here is the query I use to generate the list from the server

CREATE TEMPORARY TABLE u_tim1357.foo ( f_title varchar(255), f_user varchar(255));

CREATE TEMPORARY TABLE u_tim1357.bar ( f_bar varchar(255));

INSERT INTO u_tim1357.bar
	SELECT page_title from page
	JOIN categorylinks ON page_id = cl_from AND cl_to = 'All_orphaned_non-free_use_Wikipedia_files'
	WHERE page_namespace=6;

INSERT INTO u_tim1357.foo 
  SELECT ip.page_title, img_user_text
    FROM image, page AS ip 
    JOIN categorylinks ON ip.page_id = cl_from AND cl_to = 'All_non-free_media'
    LEFT JOIN imagelinks ON il_to = ip.page_title 
    WHERE page_namespace = 6 AND NOT EXISTS (SELECT 1 FROM page where page_namespace=0 AND page_id=il_from) AND img_name = ip.page_title AND img_timestamp <= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 48 HOUR),'%Y%m%d%H%i%s') AND NOT EXISTS (SELECT 1 from u_tim1357.bar where f_bar=page_title);

SELECT f_user, f_title AS y FROM u_tim1357.foo AS xx 
WHERE NOT EXISTS 
  (SELECT 1 
    FROM page AS rp 
    JOIN redirect ON rp.page_id = rd_from AND rd_namespace = 6 
    JOIN imagelinks ON il_to = rp.page_title 
    WHERE rd_title = xx.f_title
    AND NOT isnull(il_from));

SELECT f_bar FROM u_tim1357.bar
LEFT JOIN imagelinks ON il_to = f_bar
WHERE EXISTS (SELECT 1 FROM page where page_namespace=0 AND page_id=il_from);

Tim1357 (talk) 01:58, 9 March 2010 (UTC)[reply]

Eyeballing this code I'm concerned that it doesn't do what you think it does. Have you validated any of the returned values in your temporary table? Josh Parris 10:38, 9 March 2010 (UTC)[reply]

Yes, I have chosen a few at random and saw they met the criteria. Ill put a link to the full table here. Tim1357 (talk) 11:35, 9 March 2010 (UTC)[reply]

Josh and I conversed in IRC, and I fixed the error he was talking about. Tim1357 (talk) 04:48, 10 March 2010 (UTC)[reply]

I expanded my code so that the bot will remove deletion templates from files that are tagged as orphans, but have links in the article space. Furthermore, I updated my code so that images without links to the main space are listed, even if they have links to annother space. Tim1357 (talk) 04:12, 11 March 2010 (UTC)[reply]

Let's see how this works for 20 images, no more Approved for trial (2 days). Please provide a link to the relevant contributions and/or diffs when the trial is complete. Josh Parris 22:27, 11 March 2010 (UTC)[reply]

- oh, and this doesn't mean "Discussion over" either. Josh Parris 22:47, 11 March 2010 (UTC)[reply]

Sure thing boss, Ill do 20 images, which means that up to 40 edits will be made (one for tagging the file and the other for notifying the user) Tim1357 (talk) 23:58, 11 March 2010 (UTC)[reply]

Trial complete. 38 edts

10 edits were the bot removing image deletion tags.

15 edits were the bot tagging images for deletion.

13 edits were the bot notifying users. Tim1357 (talk) 01:36, 13 March 2010 (UTC)[reply]

The reason there are less notify edits is because of a silly code error. Fixed! Tim1357 (talk) 01:41, 13 March 2010 (UTC)[reply]
Have the affected users been notified? Josh Parris 01:47, 13 March 2010 (UTC)[reply]

Yes, manually. Tim1357 (talk) 02:04, 13 March 2010 (UTC)[reply]

Another go at 20 images, no more Approved for trial (2 days). Please provide a link to the relevant contributions and/or diffs when the trial is complete. Josh Parris 02:21, 13 March 2010 (UTC)[reply]

Trial complete. Done, ran exactly at planned. It did not notify when it tagged these two images because the user-name ended in 'bot'. Tim1357 (talk) 02:50, 13 March 2010 (UTC)[reply]

Approved. Josh Parris 03:43, 13 March 2010 (UTC)[reply]

The above discussion is preserved as an archive of the debate. Please do not modify it. To request review of this BRFA, please start a new section at WT:BRFA.