Talk:Quarry

Previous discussion was archived at Talk:Quarry/Archive 1 on 2015-04-17. Discussion area for discussion about Quarry itself and help about individual queries.

Some queries of Wikidata can not be made in SPARQL, for example I would like to query a time of creation of a specific property of specific item (Lets try P31 of Q1). I could use help with the query itself and with documentation of Wikidata SQL schema. Also is there an easy way of searching Quarry for Wikidata queries?

yes https://www.wikidata.org/wiki/Wikidata:Quarry

I used a few queries to do some wiki gnoming so I usually just submit the same query again to get an updated list of results. I noticed since a few days back the results are not updated at all. It still showed the same results even though it should be lesser now.

Is there a caching issue or something else?

Might be caused by the huge replag of 30+ hours: https://replag.toolforge.org/

These two queries are stuck in running mode for months. can someone delete them please?

• 4215 old psm images and their categories
• 22557 old psm images and their categories

Hi, perhaps they were already fixed by another admin, but they are no longer shown as Running.

Note that seeing Running for a long time likely just means status was not updated, due to an error. Query owner can just click on Run button again.

Thanks. That's what I thought, but it remained running and when I copied the sql statement into another query, it repeated the same.

See Manual:Database layout and Manual:Database layout/diagram for MediaWiki core schema documentation.

Is there an updated database schema because in this schemahttps://upload.wikimedia.org/wikipedia/commons/thumb/4/42/MediaWiki_1.20_%2844edaa2%29_database_schema.svg/2500px-MediaWiki_1.20_%2844edaa2%29_database_schema.svg.png

the revision table in enwikisource changed. At least one field no longer exists.

There is some information on the Actor migration changes and how they affected the views exposed on the Wiki Replica servers at wikitech:News/Actor storage changes on the Wiki Replicas. There are likely quite a number of other changes which have happened since the MediaWiki 1.20 schema that is documented in that diagram. A similarly major one would be Manual:Comment table.

Thanks for the info. It's much appreciated. My interest is limited to the English Wikisource. I am flying blind trying to extract data. Perhaps there is current list of the table structures used by Wikisource?

The most up to date information is generally the schema documentation in the MediaWiki source tree: maintenance/tables.sql. Manual:Database layout and Manual:Database layout/diagram are generally updated soon after major changes are introduced.

Thanks again. The Manual:Database layout/diagram is excellent. That is what I was looking for

Maybe i'm missing something but take this example:

Page "Westplex" redirects to "St._Charles_County,_Missouri": https://quarry.wmflabs.org/query/43707

It apparently does so since 2018: https://en.wikipedia.org/w/index.php?title=Westplex&type=revision&diff=828554258&oldid=827301936

Yet in pagelinks: https://quarry.wmflabs.org/query/43706

The redirect page contains some navboxes, the links seem to come from those...

Ah right, I missed those. Thanks for clearing that up!

On a related note: the redirect table is said to only track redirects that have been changed after 2007. Without deeper knowledge of the subject, I wouldn't necessarily assume that old redirects on Wikipedia have been touched since then. That's why I opted to reconstruct them from the pagelinks+page tables instead. Someone on IRC said they think every page will have experienced an automated update of some sort since then, prompting them to be tracked.

Do you know more about this? Can I rely on the redirect table tracking all (relevant) redirects the way the pagelinks table does?

This is news for me. But we can quickly check if all redirects have an entry:

SELECT page.* FROM page
LEFT JOIN redirect ON rd_from = page_id
WHERE page_is_redirect = 1 AND rd_title IS NULL

The <code>wb_terms</code> table has been deprecated. See d:Wikidata:PC#Important for tool maintainers: last steps of wb terms table and phab:T221767 for more details.

Hi. I use this quarry to find pages that don't have Arabic label at Wikidata, but it's started showing them all, and not filtering items that already have labels. Any idea why, and how to fix it? Thanks in advance. @Edgars2007 :)

wb_terms is not updated anymore. See d:Wikidata:PC#Important for tool maintainers: last steps of wb terms table migration.

Thank you @Matěj Suchánek, is there a replacement for this quarry that can do the same task?

@FShbib phab:T221767 may have some ideas for you on how to adjust your queries. That would be a reasonable place to ask the Wikidata folks for help if you don't find what you need too.

Can someone pls help me with a query that gets name(page name), industry and founded from wiki pages for companies. Can be limited to US, if it improves performance.

Ex: https://en.wikipedia.org/wiki/Microsoft

Company Name/Page Name: Microsoft

Industry: Software developement (Can list all or one industry)

Founded: April 4 1975 (Year is fine)

Need this list for al us based companies.

Data in wiki pages cannot be queried (except for categories). For querying Wikidata, d:Wikidata:Request a query is the place to ask for queries.

Thanks a lot for quick response and appropriate link.

Hello, my query https://quarry.wmflabs.org/query/34500 it says "running" for 4 hours, probably because of large output size, please help kill it!

It'd probably dead already. No status being updated is a consequence of quarry running out of memory while trying to store results.

Would someone be able to create a script that could bring back a list of users on the page https://en.wikipedia.org/wiki/Wikipedia:WikiProject_Apple_Inc./Subscribe that haven't Edited in the last 5 years. If it could remove them, that would be helpful but not needed.

Just to clarify, do you mean:

Let the list of people on that page be L. You want to find this set:

{user | user <- L and user has not made any edits on this wiki in 5 years}

What about other wikis? Also, what do you mean by "remove them", like remove them from the list?

To find users that haven't made any edits in one wiki you could do:

 select user_id
   from user
  where user_id in (... your list ...) 
    and user_id not in
        (select distinct rev_user
           from revision
          where rev_timestamp > '20140301000000'
        )
;

But that subquery would be impossibly slow on enwiki, so this is a heavy query. Not sure how to optimize it off the top of my head. Might just be better to make a temporary table somewhere out of the users that did edit in the past 5 years and query that.

Editors in last 5 years would also work. That query failed (https://quarry.wmflabs.org/query/34487). Just enwiki is what we need. We need to end up with a list of users who have/have not edited in the past 5 years and are on that page.

you substituted "https://en.wikipedia.org/wiki/Wikipedia:WikiProject_Apple_Inc./Subscribe" where I have (... your list ...) in the query, but that's not how Quarry works. Quarry just executes SQL, it doesn't now how to read the wiki and parse the user ids out of it. You'd have to do that manually, or build a query to get a list of user_ids based on user names. You can also make a list of user names that looks like this:

('user name 1', 'user name 2', ...) and pass that in the query above as `user_name in ('user name 1', 'user name 2')`.

Sorry for the late reply, The query was done in the end via production for us

Hi,

This simple quarry should return some pages in enwiki which have no fa interwiki. It works for articles, but not for templates (the results for templates have fa interwiki). Can somebody help?

I couldn't find an example in the results. Note that there may be a problem in the database, like a deferred update.

Hello,

The first item in the results (en:template:Gmina Istebna) has fa interwiki.

Looks like Wikidata doesn't do database updates properly. I did a null edit on that page and now the entry is in the database. Unfortunately, you will need to do this on every page if you want accurate results.

Thank you, but isn't there any more efficient and standard way to fix the problem? The quarry I referred to was just an example I made to show the issue. The quarry that I used and encountered problem with has more than 13k results, which performing null edit on that number of pages is impractical.

Well, to "fix the problem" you need to somehow trigger a new database update. And as a casual user, the only way (if you're lucky) is really performing a null edit. I will make a bug report on this shortly.

Task: phab:T220794