Obsolete:Tool:Query service
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)
- commons:Image:AFBEELDING_027.jpg = nl:Image:ZEE.jpg (hash: 1o8vbi0akxd15ouni2o40yytgv7cko1)
- commons:Image:PosizioneComune.png = nl:Image:Stip.png (hash: 2566utsgue754bx60mll7cxm1osbq7z)
- [[:commons:Image:RedDo
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