Nova Resource:Tools/Tools/Query service

From Wikitech
Jump to: navigation, search

Often, people on Wikimedia projects have a question they can't get an answer to from MediaWiki, but which can be easily answered by asking the database directly. The query service aims to help Wikimedians get the answers they need. It's simple:

  • If you want someone to run a SQL query for you, please file a request on jira:DBQ
    • Note: Filing a request on JIRA requires creating an account and logging in.
    • After doing so, go to https://jira.toolserver.org/browse/DBQ and select "Create new issue"; then select "Next>>"
    • Please be as detailed as possible about what data you need, in what order, in what format
    • If you can, provide the SQL query
  • If you are a Toolserver user and are willing to run a query every now and then, please look at jira:DBQ and take whatever task you like.
    • Before running the query, make sure it's feasible. EXPLAIN the query first.
    • Please consider using batch job scheduling for your query.
    • Post the result as a plain file on the Toolserver, or to a wiki page, if that was requested
    • Please record the exact query you used for future reference, ideally in a comment to the JIRA ticket.

Note: we now have the report tool as well; if you expect to need this query more than once, you can request it be added to the tool, where it will be updated automatically.

If you are interested in handling requests for database queries regularly, ask a root to assign you to the DBQ JIRA group. You will then be notified of any new requests for queries filed in JIRA. You can also create a JIRA filter (using 'find issues'), to find new queries in this project, and mail them to you periodically, or produce an RSS feed.

The person who runs the database query will add it to the wiki (below) if necessary.

Past JIRA requests

Note: Only the person running the query should add it to this list. If you want to request a new query, file a request DBQ on JIRA.

Begriffsklärungsseiten

Description Top linked disambiguation pages on the German Wikipedia and the Dutch Wikipedia.
Assignee Bryan
JIRA bug DBQ-1
Source dewiki_dab.sh / nlwiki_dab.sh
Results Wikipedia:WikiProjekt Begriffsklärungsseiten/Top-BKS / Wikipedia:Links naar doorverwijspagina's/data
Approximate run time 40 minutes
Interval weekly


This page was moved from toolserver:en:Query service/1. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/1/edithistory

Image hash collisions

Description List of images with same SHA1 hashes between two wikis.
Assignee Siebrand
JIRA bug DBQ-5
Source Query service/image hash collisions#Query
Results Query service/image hash collisions#Results
Approximate run time below one minute on commons vs. nlwiki
Interval

<noinclude>

Query

<source lang="sql"> select

concat(

 '* [[:commons:Image:'

, db1.img_name , ']] = [[:nl:Image:' , db2.img_name , ']] (hash: ' , db1.img_sha1 , ')' ) as collision

from commonswiki_p.image db1

join nlwiki_p.image db2

 on db1.img_sha1 = db2.img_sha1

where db1.img_sha1 <> ; </source>

Change <tt>commonswiki_p</tt> and <tt>nlwiki_p</tt> to run against different wikis.

Results

Last run: 21:09, 15 December 2007 (UTC)

This page was moved from toolserver:en:Query service/5. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/5/edithistory

Short pages enwiki

Description List of short pages with a single author (excluding user pages and redirects)
Assignee Duesentrieb
JIRA bug DBQ-7
Source Query service/short pages enwiki#Query
Results http://tools.wikimedia.de/~daniel/misc/shortpages-enwiki.txt
Approximate run time not sure, perhaps an hour or so
Interval


This page was moved from toolserver:en:Query service/7. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/7/edithistory

Unused templates enwiki

Description List of unused templates (including redirects)
Assignee Duesentrieb
JIRA bug DBQ-8
Source Query service/unused templates#Query
Results http://tools.wikimedia.de/~daniel/misc/unusedtemplates-enwiki.txt (for enwiki)
Approximate run time a couple of hours
Interval


This page was moved from toolserver:en:Query service/8. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/8/edithistory

Temporary Wikipedian userpages

Description List of en:Category:Temporary Wikipedian userpages
Assignee Misza13
JIRA bug DBQ-9
Source Query service/Temporary Wikipedian userpages#Query
Results http://tools.wikimedia.de/~misza13/cat_temp.txt
Approximate run time several minutes
Interval


This page was moved from toolserver:en:Query service/9. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/9/edithistory

Long audio files on Commons

Description list of all audio files larger than 1MB on Commons.
Assignee Duesentrieb
JIRA bug DBQ-10
Source Query service/long audio files#Query
Results http://tools.wikimedia.de/~daniel/misc/commonswiki-long-audio.txt
Approximate run time several hours (because there is no index in img_media_type)
Interval


This page was moved from toolserver:en:Query service/10. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/10/edithistory

Talk page redirects without incoming links

Description Number of and listing of unreferenced talkpage redirects on enwiki.
Assignee ST47, Kylu
JIRA bug DBQ-16
Source This page
Results This page, subpage
Approximate run time count: 5 minutes, listing:
Interval on demand


Broken image links

Description list references to images that are not present locally nor on Commons (nowiki)
Assignee Duesentrieb
JIRA bug DBQ-24
Source http://tools.wikimedia.de/~daniel/misc/nowiki-brokenimages.sql
Results http://tools.wikimedia.de/~daniel/misc/nowiki-brokenimages.txt
Approximate run time couple of minutes
Interval


Description list inclusions of templates that do not exist (nowiki)
Assignee Duesentrieb
JIRA bug DBQ-24
Source http://tools.wikimedia.de/~daniel/misc/nowiki-brokentemplates.sql
Results http://tools.wikimedia.de/~daniel/misc/nowiki-brokentemplates.txt
Approximate run time under one minute
Interval

Lista de Páginas órfãs

Description Lista de Páginas órfãs
Assignee
JIRA bug n/a
Source
Results http://pt.wikipedia.org/wiki/Especial:P%C3%A1ginas_%C3%B3rf%C3%A3s
Approximate run time Limita em 5000, podem ser adicionadas as outras que ficam ocultas?
Interval


This page was moved from toolserver:en:Query service/25. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/25/edithistory

Count of NASA pictures on Wikimedia Commons, amount of them featured or valued

Description Count of NASA pictures on Wikimedia Commons, amount of them featured or valued.
Assignee Dereckson
JIRA bug DBQ-103
Source Jira ticket #DBQ-103, comment 16111
Results Commons:User:Dereckson/NASA Statistics
Approximate run time 4 minutes
Interval monthly


This page was moved from toolserver:en:Query service/103. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/103/edithistory

Past non-JIRA requests

WantedTemplates cswiki

Description wanted templates on cswiki (usage of non-existing templates).
Assignee Duesentrieb
JIRA bug n/a
Source http://tools.wikimedia.de/~daniel/misc/cswiki-wantedtemplates.sql
Results http://tools.wikimedia.de/~daniel/misc/cswiki-wantedtemplates.txt
Approximate run time ~20 sec
Interval n/a


This page was moved from toolserver:en:Query service/WantedTemplates cswiki. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/WantedTemplates cswiki/edithistory

Inter-wiki parser

Description sql function to convert a string used as interwiki link into a pair of target wiki namespace and page title (as in pages table).
Assignee
JIRA bug n/a
Source
Results
Approximate run time
Interval


This page was moved from toolserver:en:Query service/WantedInterwiki parser. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/WantedInterwiki parser/edithistory

This page was moved from toolserver:en:Query service. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/edithistory