Last modified: 2014-01-03 15:21:38 UTC
This issue was converted from https://jira.toolserver.org/browse/DBQ-2. Summary: list of commons image with no licence template Issue type: Task - A task that needs to be done. Priority: Major Status: Done Assignee: Dan Collins <EN.WP.ST47@gmail.com> ------------------------------------------------------------------------------- From: Darkoneko <koneko@tiscali.fr> Date: Sun, 28 Oct 2007 11:32:52 ------------------------------------------------------------------------------- The goal be to have a list of commons: images having no licence information To simplify the ask (since there are numerous licence template), the request is to have the list of all images having either *no template *only infobox% templates output format should be put on a file, with the form CONCAT("image:", page_title, "\n") The used tables will be "page" and "templatelinks". Unfortunatly, my SQL knowledge wasn't enough to make a working query for that, so I had to ask for help here ![][1] thanks in advance [1]: https://jira.toolserver.org/images/icons/emoticons/smile.gif
------------------------------------------------------------------------------- From: Bryan Tong Minh <bryan@tools.wikimedia.de> Date: Sun, 28 Oct 2007 11:40:10 ------------------------------------------------------------------------------- That would be something like USE commonswiki_p; SELECT CONCAT('* Image:', page_title) FROM image, page LEFT JOIN templatelinks ON tl_from = page_id WHERE (page_namespace, page_title) = (6, img_name) AND (tl_namespace, tl_title) <> (10, 'Information') AND tl_from IS NULL; It would be much easier if all Commons license tags would embed Category:License_tags aside from the sub cats of License_tags.
------------------------------------------------------------------------------- From: Dan Collins <EN.WP.ST47@gmail.com> Date: Sun, 28 Oct 2007 11:42:00 ------------------------------------------------------------------------------- I just did this for en - let's see if I can do it here too.
------------------------------------------------------------------------------- From: Dan Collins <EN.WP.ST47@gmail.com> Date: Sun, 28 Oct 2007 13:06:39 ------------------------------------------------------------------------------- select page_title from page, image where page_namespace = 6 and page_title=img_name and page_id not in (select tl_from from templatelinks); select img_name from image where img_name not in (select page_title from page where page_namespace = 6 ); and results are at [[commons:User:ST47/NT]]
------------------------------------------------------------------------------- From: Daniel Kinzler <daniel@brightbyte.de> Date: Sun, 28 Oct 2007 15:11:56 ------------------------------------------------------------------------------- For future reference: where ... not in (select ...) is rather slow if you do it on a lot of entries. rather use left join ... where x is null. Anyway, the above query will only find pages without any template. there's a lot of non-license templates. I have built a list of license templates, and a view that is like templatelinks, but only for license templates - that would give a more accurate result I hope. Oh well, maybe next time ![][1] hm, perhaps i should make this stuff available somewhere, somehow. maybe create a commonswiki_aux_p database containing "auxiliary" data? [1]: https://jira.toolserver.org/images/icons/emoticons/smile.gif
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: EN.WP.ST47@gmail.com CC list: koneko@tiscali.fr, Bryan.TongMinh@Gmail.com, daniel.kinzler@wikimedia.de, EN.WP.ST47@gmail.com
