Talk:Quarry/2018

Discussion area for discussion about Quarry itself and help about individual queries.

How many articles have I written?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I have over 350,000 en.Wikipedia edits, so Xtools won't show me how many articles I have created. Can I get a count (or a tool to get a count) here? ~ BD2412 (talk) 00:47, 6 January 2018 (UTC)

Yes, if you know your user_id, you can query like this:

select count(*)

from revision

where rev_user = <<your-user-id>>

and rev_parent_id = 0

When rev_parent_id is 0, that means that's the first revision on the page, so it's the creation of the page. The rev_user field is the editor responsible for the revision. Milimetric (WMF) (talk) 01:45, 6 January 2018 (UTC)

I'll give it a try, thanks. BD2412 (talk) 01:46, 6 January 2018 (UTC)

The query suggested by Milimetric will count all page creations (i.e. not just mainspace). Annoyingly, 'revision' doesn't have a mainspace field so this needs to be gotten from another table. Then we also need to exclude redirects by setting setting a limit on 'rev_len' and hoping that you didn't create many articles with a very small first revision. I'm working on this at https://quarry.wmflabs.org/query/24690 Smartse (talk) 15:49, 10 February 2018 (UTC)

So I think my query there is right, but unfortunately it took > 30 mins to execute so doesn't produce any results. :( Smartse (talk) 16:32, 10 February 2018 (UTC)

I have given up worrying about this. BD2412 (talk) 17:25, 10 February 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Extract the list of pages linked to commons

I want to extract the list of pages linked to commons but i don't know which database to use (enwiki_p or commonswiki_p) and how ?

Thanks in advance! وهراني (talk) 15:20, 15 January 2018 (UTC)

It looks like you can use the iwlinks table from enwiki. E.g. https://quarry.wmflabs.org/query/24176 I think that links to commons show up as "iwl_prefix" of "c". I've not been able to confirm this yet though. Halfak (WMF) (talk) 16:33, 16 January 2018 (UTC)

List of pages in a certain portal

Hi,

How to get a list of pages in a certain portal, example: "Portal:Culture"..

Thanks.. Balligho (talk) 20:59, 28 January 2018 (UTC)

any help please? 37.217.185.87 (talk) 16:34, 29 January 2018 (UTC)

Under "Special pages" you can look up "All pages with prefix" (which takes you to the page Special:PrefixIndex). Specify the namespace ("Portal"), and enter "Culture" prefix field to list all the pages in that namespace starting with that name. Clump (talk) 16:43, 29 January 2018 (UTC)

If you mean on Quarry specifically, you can query the page table. First you have to find the namespace that corresponds with Portal: on your wiki, use that in your query as "where page_namespace = <<the number you found>>", and then add "and page_title like 'Culture%'". Let me know if that doesn't make any sense :) Milimetric (WMF) (talk) 17:21, 29 January 2018 (UTC)

superseded?

I have this query: https://quarry.wmflabs.org/query/24402 which is marked sometimes as superseded (which seems to mean that my last changes are gone). Do not understand the reason why this is happening and how to avoid it.

remark: I forked the query from another user. ISO 3166 Bot (talk) 12:28, 11 February 2018 (UTC)

Superseded means that a newer revision of the query, than the one the browser specified to Quarry, is submitted. It's some technical 'weirdness' and, unless it shows up for a very long time or without anyone submitting any new revisions, there is nothing to worry about. Zhuyifei1999 (talk) 18:09, 11 February 2018 (UTC)

Thx, I've had this behaviour a couple of times last days. I changed something in the script (there is no save button, so I assume that submitting the query will save it, or it will be saved immediately), but when I come back the other day, my changes are gone. AFAIK, the script is mine and nobody can change it (except by making a copy by forking it). So it is not obvious what is going wrong. I'll refresh the browser next time I come to the superseded state. ISO 3166 Bot (talk) 18:49, 11 February 2018 (UTC)

Yeah, the intended behaviour is that submitting the query should save it. Zhuyifei1999 (talk) 23:50, 11 February 2018 (UTC)

Improving performance

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Can anyone suggest a way to improve the performance of https://quarry.wmflabs.org/query/24717 ?

It seems like a relatively simple query - listing the revision lengths and times of a specific article - but while it ran yesterday (very slowly), today it failed completely. Smartse (talk) 20:27, 12 February 2018 (UTC)

Make use of the name_title UNIQUE INDEX wisely: https://quarry.wmflabs.org/query/24742. Zhuyifei1999 (talk) 20:52, 12 February 2018 (UTC)

I don't know what that means but I can see how it's an improvement! It also helps if I use an article that I didn't delete yesterday! Smartse (talk) 21:13, 12 February 2018 (UTC)

Sorry for being an SQL noob, but does this mean that in general it is faster if you use more 'and' statements? I can see how searching for the title in this case might be slow, so does setting "page_namespace=0" mean that the table is quickly shortened and then only that is searched for the title, rather than all 7 million rows? The simpleton in me thought that more statements would be slower. Smartse (talk) 21:20, 12 February 2018 (UTC)

It depends on what you are searching on. In this case, the name_title UNIQUE INDEX means that there can be at most one row that match both the page title and namespace, and finishes the search once a single row is found. There are also special areas / code / storage optimized for searching when both namespace and title are specified. Zhuyifei1999 (talk) 21:39, 12 February 2018 (UTC)

That makes more sense. Thanks for your help. Smartse (talk) 23:00, 12 February 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

A couple of questions

Hello again. I'd like to ask a couple of questions, please.

1. Is there a chance to connect the pilepage as server on quarry query pages? So, if one clicks on "pile" button, the resultset column page_title converts from plain text to hyperlinks to wiki defined in query first line by use statement, in namespace 0 if page_namespace column does not exist, and in proper namespace otherwise? It will be very helpful to have a way to open the result pages on demand.
2. Is there a chance to add an ability to assign temporary tables to variables?
3. I just heared the recursive statement will start working soon. It's amazing. When will it it be?

Thank you very much. IKhitron (talk) 18:01, 28 February 2018 (UTC)

Hi,

1. Could you clarify this, such as by explaining your use case? I don't think I understand the question.
2. Temporary tables by itself as been explicitly declined. If you want to assign the results of a SELECT (multi-row, multi-column; if only a single cell it's much easier) to a temporary table-like name, with upstream higher versions of the database software, that is exactly what WITH statements and CTEs (Common Table Expressions) is designed to do. Unfortunately, that feature is, AFAIK, not in the version of the database we are running on. And when it will be available? I don't know, better to ask a DBA. BTW: I really look forward to this feature. One of my queries would be much simpler if we had this.
3. Recursive statements? Are you referring to the mail thread Recursive Common Table expressions @ Wikimedia? Well, it's still CTE so whatever I said above for CTE also apply to Recursive CTEs Zhuyifei1999 (talk) 02:41, 1 March 2018 (UTC)
4. Do you know what is the "pile" lab tool?
5. What I really need, is one little thing - an ability to query categorylinks for pages in some category recursively. That's all.
6. Yes, and the most tech person on our wiki sent me a mail with a link to this thread, explaining it will be soon on quarry. So I wanted to know how soon. But maybe it's his mistake, if you don't know about this.

Thank you. IKhitron (talk) 11:21, 1 March 2018 (UTC)

1. No, unfortunately. toollabs:pile is also a 404. Do you have a link?
2. Yeah, that should come in the future, along with Recursive CTEs.
3. I am really not the person to ask about how soon some feature will come. I maintain Quarry itself, not the database replicas in which Quarry queries. DBAs know better about this. Zhuyifei1999 (talk) 17:01, 1 March 2018 (UTC)

1. [1].

2., 3. I see, thank you. IKhitron (talk) 17:04, 1 March 2018 (UTC)

What does that pile tool do? Is there an on-wiki documentation or example? Zhuyifei1999 (talk) 17:39, 1 March 2018 (UTC)

Do not know about the documentation. Here is my explanation:

1. Choose a quarry query that has in resultset a column named page_namespace and a column named page_title.
2. Run it.
3. Give to the pile tool the number of quarry query (not of the special run, the query itself).
4. Give to the pile tool the explicit language+site code for the wiki site that includes these pages, for example enwiki.
5. Run the tool.
6. You'll get an html page with permanent link to access it in the future, that contains the list of pages, taken from the resultset, each one is a hyperlink to the concrete page.

As a result, quarry gives page names as text, pile converts them to convenient hyperlinks. IKhitron (talk) 17:47, 1 March 2018 (UTC)

Ah I see. I would really like some documentation for end-users before Quarry start promoting it. Simply linking to pile will likely confuse users of Quarry.

After that, could you file a request on phabricator (and link back to this discussion)? I'll see if I can do it. Zhuyifei1999 (talk) 17:55, 1 March 2018 (UTC)

I'll try to talk with Magnus. But what do you think, will it be possible technically to add the power into quarry, as I described at the first post? Thank you. IKhitron (talk) 17:59, 1 March 2018 (UTC)

It should be. Technical issues are usually much more simpler than design issues, eg. where to place the button, for me. Zhuyifei1999 (talk) 18:12, 1 March 2018 (UTC)

Thank's a lot. I'm going to talk with him. IKhitron (talk) 18:13, 1 March 2018 (UTC)

Hello again, User:Zhuyifei1999. It's [2]. Thank you. IKhitron (talk) 01:04, 21 March 2018 (UTC)

I filed phab:T190242 for this. Zhuyifei1999 (talk) 02:57, 21 March 2018 (UTC)

Thanks a lot! IKhitron (talk) 10:30, 21 March 2018 (UTC)

Recentchanges table getting cleared?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I'm not able to get items from the recentchanges table that have a timestamp value prior to January 2018. Is this table getting cleared? How would I retrieve changes prior to January 2018?

Example COUNT query of recentchanges(last run in 2017): https://quarry.wmflabs.org/query/23105

Returned 136380 rows.

The same query that I ran today: https://quarry.wmflabs.org/query/25408

Returned 1720 rows

Thanks! Abbe98 (talk) 17:20, 10 March 2018 (UTC)

Why do you think it' s called recentchanges table? :)

What you need is Manual:Revision table Edgars2007 (talk) 17:28, 10 March 2018 (UTC)

Thanks! that explains the difference between "revisions" and "recent changes" ;-) Abbe98 (talk) 17:33, 10 March 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Query Reads as "Complete" but no data is visible. Help

I could be missing something in theory, but I've tried Chrome, FF, Edge, and even IE to see if it was a browser compatibility issue and nothing so far. I can see the data for other queries but this one never seems to show up. I'm wondering if it has to do with the size of the data set that the query returns. Any ideas? Ian S. Mills (talk) 18:07, 29 March 2018 (UTC)

Could you link to the query in question? Zhuyifei1999 (talk) 19:21, 29 March 2018 (UTC)

It's this one. Why it ever did complete? Edgars2007 (talk) 04:32, 30 March 2018 (UTC)

It did complete as far as the query runners are concerned. It takes more time for a browser to download the results (seriously, 1 million rows?!) than the timeout threshold and the connection will get killed before you get the complete results downloaded and rendered.

And no, I do not like it being completed at all. See phab:T188564. Zhuyifei1999 (talk) 15:39, 30 March 2018 (UTC)

Any way to get the 'current time' as part of a result output?

I would like to get the time when a quarry was ran last as a part of the result output (either as a column or at header row or just as some element somewhere in the result set). Is there any way to accomplish this? I tried the GETDATE() or CURRENTTIME() SQL functions, but the server would instantly deny executing such functions. :( Viswaprabha (talk) 22:56, 2 April 2018 (UTC)

Try SELECT now(); or did I misunderstand? Achim (talk) 13:17, 3 April 2018 (UTC)

Great! That's the one!

Thanks, User:Achim55.

One more: Pls see this: [https://quarry.wmflabs.org/query/8677] Is there anyway I can cut this code into smaller size? Thanks in advance. Viswaprabha (talk) 02:10, 4 April 2018 (UTC)

How to find old pages created beyond the time horizon of the revision table?

I'm trying to find all pages created by a bot many years ago, and am limited to only those pages that happen to have been edited recently (~3 months from what I gather, like the recent changes table). [https://tools.wmflabs.org/sigma/created.py Pages created search] might do the trick, but there are so many pages that it would have to be a series of automated grabs, which I haven't been able to do in AWB via regular GetHTML'ing. (please ping)   ~ Tom.Reding (talk ⋅dgaf)  12:51, 5 April 2018 (UTC)

revision table is not limited by time (unless the pages were deleted since). Matěj Suchánek (talk) 14:36, 5 April 2018 (UTC)

Manual:revision table is ambiguous about that ("Items in the recentchanges table are periodically purged; those in the revision table are typically kept longer or indefinitely"). The evidence I have for this is that I ran a query sometime before 14 March 2018, and again sometime after 14 March 2018, and en:Diplothrix showed up in the new query, but not in the old, because the last time it was edited prior to 14 March 2018 was 19 June 2017.   ~ Tom.Reding (talk ⋅dgaf)  15:02, 5 April 2018 (UTC)

Creating new genus with Diplothrix legatus in it. is matched by neither '%congress%', '%fjc.gov%' nor '%list of judges%'. (quarry:query/26168) Matěj Suchánek (talk) 15:16, 5 April 2018 (UTC)

I never said it did. I ran a different query, https://quarry.wmflabs.org/query/25510.   ~ Tom.Reding (talk ⋅dgaf)  15:23, 5 April 2018 (UTC)

Silly attempt, I admit. Anyway, everything looks correct to me: quarry:query/26169. Matěj Suchánek (talk) 15:34, 5 April 2018 (UTC)

Once the page is edited it will show up in the revision table for some period (< 9 months in the case of en:Diplothrix). Showing that it exists in the table now is meaningless. If that's the best input you can provide, I suggest letting someone more knowledgeable & willing to help respond.   ~ Tom.Reding (talk ⋅dgaf)  15:38, 5 April 2018 (UTC)

I'm sorry if it looks as if I'm not willing to help you (but I am). Is there any solid evidence that I could actually see? I'm sure revision rows are only purged when the page is deleted. (You can run SELECT * FROM revision WHERE rev_timestamp < 20170705154900 LIMIT 25 to see that even older rows are in the database.) Matěj Suchánek (talk) 15:50, 5 April 2018 (UTC)

I can't be any more clear than in my original post and my first response.   ~ Tom.Reding (talk ⋅dgaf)  15:59, 5 April 2018 (UTC)

@Tom.Reding, I'm a little late here, but just in case others see this:

• When a page is deleted, all of its revisions are moved from the revision table into the archive table.
• When a page is restored, its revisions are moved back from the archive table into the revision table
• The revision_userindex view is faster to query than the revision table (better indices).
• So it's definitely possible for you to find revisions in the revision table, have them disappear on delete, and reappear on restore. It's more rare, but it's possible. Nevertheless, records are never deleted from the revision table for no good reason. If you want to get a complete picture, take a look at the archive table. By the way, you can also use archive_userindex for speed.

Here's a query that shows early creations for that user and uses revision_userindex as an example:

https://quarry.wmflabs.org/query/26524 Milimetric (WMF) (talk) 14:58, 18 April 2018 (UTC)

And by the way I agree that the Manual help text is ambiguous, we should really update that. Milimetric (WMF) (talk) 14:59, 18 April 2018 (UTC)

Tracking Wikidata redirects

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I have a list of 250 Wikidata Item ID which I suspect of being redirects. What would be the easiest way to check if they are and to what page they are redirecting. For example for Q817614 I would like to get Q15123417 Jarekt (talk) 12:01, 6 April 2018 (UTC)

Something like this:

USE wikidatawiki_p;
SELECT page_title, rd_title FROM page
LEFT JOIN redirect ON rd_from = page_id
WHERE page_namespace = 0
AND page_title IN (
 "Q1", "Q2", # ...
)

Matěj Suchánek (talk) 16:41, 6 April 2018 (UTC)

Excellent, That worked great. Thanks Jarekt (talk) 03:43, 7 April 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Stuck queries

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I've got two queries stuck for 20 hours. They show up as "running", but clicking "explain" shows "Hmm... Is the SQL actually running?!". These are: query:26180 and query:25311. MarioGom (talk) 15:34, 6 April 2018 (UTC)

I didn't check the fist query, but the second has 11298269 rows in set, 3816 warnings (11 min 55.68 sec). Quarry simply can't store so many results. Zhuyifei1999 (talk) 17:55, 6 April 2018 (UTC)

Thanks! I'll fix the number of records problem by adding a HAVING clause. What about the warnings? Are they related to any error in the query? MarioGom (talk) 19:13, 6 April 2018 (UTC)

Nevermind. There were multiple errors in the query. These are fixed now. MarioGom (talk) 21:37, 6 April 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Re-running a query automatically

Is there an accepted programmatic way to keep a query updated? I have a query that I'd like to be able to keep updated automatically on an hourly or daily basis, but it doesn't seem like there's an easy way to ask Quarry to re-run a query programmatically. I could call the <code>/api/query/run</code> endpoint from my script, but that seems really hacky—that would require that my script a) re-upload the entire SQL statement every time, and b) be authenticated. Is there a better way I could be doing this? Thanks. IagoQnsi (talk) 14:04, 10 April 2018 (UTC)

I would suggest getting Toolforge account. Edgars2007 (talk) 15:51, 10 April 2018 (UTC)

It's been almost three years, but we still don't know how to implement it in Quarry. Zhuyifei1999 (talk) 16:22, 10 April 2018 (UTC)

Inaccurate results

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi.

This query was giving correct results before, and now it is showing items that already have labels in Arabic as well as those who doesn't. What changed? and can any one help fixing it? Thanks. FShbib (talk) 14:09, 11 April 2018 (UTC)

You shouldn't query for term_entity_id anymore. Use term_full_entity_id instead. Matěj Suchánek (talk) 15:23, 11 April 2018 (UTC)

@Matěj Suchánek any idea of how to fix it? :) FShbib (talk) 23:18, 12 April 2018 (UTC)

https://quarry.wmflabs.org/query/26411 Matěj Suchánek (talk) 07:59, 13 April 2018 (UTC)

Thank you @Matěj Suchánek. :) FShbib (talk) 20:00, 14 April 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Get Wikidata Page's Related changes

Hello, Please help me. I want to Related changes from 2018-01-26 00 hours to 2018-01-27  23:59 of User:Titodutta/Events/2018 Republic Day of India. like https://www.wikidata.org/wiki/Special:RecentChangesLinked?hidebots=1&hidecategorization=1&target=User%3ATitodutta%2FEvents%2F2018_Republic_Day_of_India&limit=500&days=30&urlversion=2 but from 2018-01-26 00 hours to 2018-01-27  23:59. Jayprakash12345 (talk) 20:40, 12 April 2018 (UTC)

Remind me, does "RelatedChanges" get edits to all pages that link to "User:Titodutta/Events/2018 Republic Day of India"? Halfak (WMF) (talk) 20:52, 12 April 2018 (UTC)

I think this gets it: https://quarry.wmflabs.org/query/26400 It looks like there we no edits during that timespan. Halfak (WMF) (talk) 21:08, 12 April 2018 (UTC)

Hello, User:Halfak (WMF)

a) Yes, "RelatedChanges" get edits to all pages that link to a page. For example, kindly see Special:RecentChangesLinked/MediaWiki

b) Yes, edits were made on the 2 days. There were a small edit-a-thon as well. Titodutta (talk) 05:56, 13 April 2018 (UTC)

BETWEEN "2018012600" AND "2018012824" seems to be incorrect. I'd say you need to provide all values, including minutes and seconds. Matěj Suchánek (talk) 08:24, 13 April 2018 (UTC)

It turns out you don't need to provide all of the values. You just need the prefixes. This is due to how string comparison works. I've updated my query to show that it works for a wider date range. See: https://quarry.wmflabs.org/query/26400 Halfak (WMF) (talk) 15:10, 16 April 2018 (UTC)

Issue with displaying results

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hello,

This query finds parent and child categories of a particular article. It works properly when displaying either child or parent in different runs (by editing the 3rd line), but fails to display both in one run and gets stuck. Any idea what is wrong? Thanks.

P.S: I noticed that as the header it writes "cl_to" (and not "chld.cl_to"). Does it give a hint? Saeidpourbabak (talk) 13:05, 14 April 2018 (UTC)

The problem is solved by adding titles to the headings in line 3:

SELECT page_title, chld.cl_to, prnt.cl_to > SELECT page_title page, chld.cl_to child_category, prnt.cl_to parent_category Saeidpourbabak (talk) 00:44, 3 June 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Timestamp for Barnstars

Hey,

I'm trying to fetch the timestamp of when each Barnstar was given to a specific user.

Do you know of any way to do it? I know which Barnstars each user has from "templatelinks" table but there is obviously no timestamp attached.

Trying to use "revision" table also doesn't work, as I can get all edits done on a certain user page but I found no way understanding which edits belong to Branstars..

I would appriciate any help.

Thanks. Bsdla (talk) 07:33, 28 April 2018 (UTC)

Not really, unfortunantely. At least with SQL. Only if those barnstar giving edits has some good summaries, that you can use for searching. Edgars2007 (talk) 07:05, 1 May 2018 (UTC)

Thanks Edgars.

Also, while running a query on templatelinks table the amount of Branstars I found were not compatible with statistics I found.

This made me think where are all the Barnstars recorded if not in templates? how does wikiLove work? where is the data stored? Bsdla (talk) 10:20, 1 May 2018 (UTC)

Oh, the barnstars are recorded in a log table, called wikilove_log. I just checked enwiki and it doesn't seem to be redacted from what's in production:

select * from wikilove_log order by wll_timestamp desc limit 10;

In general if you're looking for something, try checking what tables are available, like this: https://quarry.wmflabs.org/query/27018 Milimetric (WMF) (talk) 12:56, 15 May 2018 (UTC)

Need help in defining an SQL statement

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I would like to extract from enwikisource_p, a list of grouped editors' names and their count, where the pages are named ('Popular_Science_Monthly_Volume_%') and the volumes' numbers are 1 to 87. This is from the start of the project about 10-11 years ago. Unfortunately, I have no clue where such information is stored, which table stores the needed information. and how they are joined.

Your help would be greatly appreciated. — Ineuw talk 00:05, 18 May 2018 (UTC)

What about this?

USE enwikisource_p;
SELECT rev_user_text, COUNT(*) AS number
FROM revision JOIN page ON page_id = rev_page
WHERE page_namespace = 0 AND page_title LIKE "Popular&_Science&_Monthly/Volume&_%" ESCAPE "&"
GROUP BY rev_user_text;

Matěj Suchánek (talk) 08:00, 19 May 2018 (UTC)

Matěj Suchánek Thank you very much. It worked fine. You are a prince! — Ineuw talk 22:54, 19 May 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Search newcomers

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I need find active newcomers (2-50 editcount, who made edit recently), whose talk page not exists and never been delete. And whose account never been blocked.

My query (based on that) not working, what problem is? Thanks!

P.s.: sorry for my English. Iluvatar (talk) 11:59, 26 May 2018 (UTC)

Are you sure about AND NOT EXISTS (SELECT COUNT(*) ...? I think you want SELECT 1 here as well. Matěj Suchánek (talk) 18:55, 26 May 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Indexed views

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I've seen a few good-looking answers here such as "The revision_userindex view is faster to query than the revision table". I suspect that some of my queries could benefit from these indexed views. Is there a list of them anywhere? Certes (talk) 00:49, 9 June 2018 (UTC)

wikitech:Help:MySQL_queries#Alternative_Views Zhuyifei1999 (talk) 12:36, 9 June 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Varchar

Hi. Is there a reason that page_id is defined as varchar and not unsigned, so order by does not work? Thank you. IKhitron (talk) 15:40, 9 June 2018 (UTC)

Or, maybe, quarry casts it. Because I added casts everywhere, and it still does not work. IKhitron (talk) 15:48, 9 June 2018 (UTC)

Example of a broken query is welcome. Zhuyifei1999 (talk) 15:51, 9 June 2018 (UTC)

Sure. See the first commented query in 3338. Thank you. IKhitron (talk) 15:54, 9 June 2018 (UTC)

https://quarry.wmflabs.org/query/27527. LGTM?

If something is not right, please explain the expected result. Zhuyifei1999 (talk) 15:58, 9 June 2018 (UTC)

I expected the minimum id1 to be 7, not 5724. IKhitron (talk) 16:02, 9 June 2018 (UTC)

MariaDB [ruwiki_p]>       select
    ->         page_title,
    ->         cast(page_id as unsigned) as id
    ->       from page
    ->       where page_namespace = 0
    ->         and not page_is_redirect
    ->       order by cast(page_id as unsigned) asc
    -> limit 10;
+---------------------------+----+
| page_title                | id |
+---------------------------+----+
| Литва                     |  7 |
| Россия                    |  9 |
| Слоновые                  | 10 |
| Мамонты                   | 11 |
| Красная_книга             | 15 |
| Соционика                 | 16 |
| Школа                     | 18 |
| Лингвистика               | 20 |
| Социология                | 21 |
| Киевская_Русь             | 27 |
+---------------------------+----+
10 rows in set (14.25 sec)
MariaDB [ruwiki_p]>     select 
    ->       page_title as article, 
    ->       cast(id as unsigned) as id0
    ->     from (
    ->       select
    ->         page_title,
    ->         cast(page_id as unsigned) as id
    ->       from page
    ->       where page_namespace = 0
    ->         and not page_is_redirect
    ->       order by cast(page_id as unsigned) asc
    ->     )f
    -> limit 10;
+------------------------------------------------------------------------------+---------+
| article                                                                      | id0     |
+------------------------------------------------------------------------------+---------+
| !                                                                            |  452225 |
| !!!                                                                          | 1301215 |
| !!_(значения)                                                                | 6062007 |
| !Action_Pact!                                                                |  892368 |
| !T.O.O.H.!                                                                   | 1340518 |
| !_(альбом)                                                                   | 6005448 |
| !ФЕСТ                                                                        | 3682212 |
| "Нурлы"                                                                      | 7419278 |
| $                                                                            | 3607625 |
| $pringfield_(Or,_How_I_Learned_to_Stop_Worrying_and_Love_Legalized_Gambling) | 3321107 |
+------------------------------------------------------------------------------+---------+
10 rows in set (0.00 sec)

Don't expect the ordering in a subquery (especially without limit) to affect the parent query. Zhuyifei1999 (talk) 16:21, 9 June 2018 (UTC)

You mean it's a bug in MySql? IKhitron (talk) 16:27, 9 June 2018 (UTC)

Whether it's a bug or intended behavior I cannot say, but yes that is the bahavior of MariaDB. Zhuyifei1999 (talk) 16:42, 9 June 2018 (UTC)

Weird. By the way, I succeeded to do this, still using subqueries. Thank you. IKhitron (talk) 17:06, 9 June 2018 (UTC)

Query still running?

https://quarry.wmflabs.org/query/24254 claims to be still running after several days. Should we do something to get rid of it, or was it actually killed after 30 minutes?

Also, please can anyone suggest how to improve the performance? It looks as if it should be efficient, but it doesn't seem to access the page table by the obvious index of namespace+title. Certes (talk) 00:59, 15 June 2018 (UTC)

The query could not store the results because of phab:T170464. The query might not be running slow. Zhuyifei1999 (talk) 05:20, 15 June 2018 (UTC)

Recent changes on talk pages

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi, I am looking for a substitute for the talk page tool by DrTrigon (not working since a couple of month).It should list the names of all talk pages of the corresponding Wikipedia articles (e.g. in dewiki) that belong to a specified article category (including a depth of subcategorys that can also be scepicified, e.g. 10) and where the talk pages have been changend in the last e.g. 14 days. Is this possible with a query? Invisigoth67 (talk) 12:41, 15 June 2018 (UTC)

Deep category search with SQL is very difficult unless the depth of the category is one or two. Matěj Suchánek (talk) 13:20, 15 June 2018 (UTC)

So if I use Petscan to give me a list of all sub categorys and the list is below the 65K query length limit and I paste it into the query (e.g. "...where category in ('American 3D films','Australian 3D films','Brazilian 3D films‎'...", will there be a way? Invisigoth67 (talk) 15:49, 15 June 2018 (UTC)

Probably yes:

USE enwiki_p;
SELECT rc_title FROM recentchanges JOIN categorylinks ON cl_from = rc_cur_id
WHERE rc_namespace = 1
AND cl_to IN ('American_3D_films', 'Australian_3D_films', 'Brazilian_3D_films‎', ...)
AND rc_timestamp >= DATE_ADD(NOW(), INTERVAL -14 DAY)
ORDER BY rc_timestamp;

Matěj Suchánek (talk) 16:49, 15 June 2018 (UTC)

Thanks for the query, but I get null results where there should be some. Query 27673, dewiki, article "S-Bahn Wien" in category "Bahnstrecke in Wien" had talk page contributions one week ago. Invisigoth67 (talk) 17:23, 15 June 2018 (UTC)

I forgot to highlight (and wanted to do so) that you need to include underscores (_) instead of spaces in category names. Matěj Suchánek (talk) 18:16, 15 June 2018 (UTC)

I tried it both with underscores and spaces, but still no results, please see Query 27673. Invisigoth67 (talk) 18:24, 15 June 2018 (UTC)

Uh, I misunderstood your wish and assumed that the talk pages should be in the category, not the articles. This query should work:

USE dewiki_p;
SELECT rc_title, rc_timestamp FROM recentchanges
JOIN page ON rc_namespace = 1 AND rc_title = page_title AND page_namespace = 0
JOIN categorylinks ON cl_from = page_id
WHERE cl_to IN ('Bahnstrecke_in_Wien', 'Musiker_(Wien)')
AND rc_timestamp >= DATE_ADD(NOW(), INTERVAL -14 DAY)
ORDER BY rc_timestamp;

Matěj Suchánek (talk) 18:32, 15 June 2018 (UTC)

Just tested the new query, this is exactly what I was looking for. Thank you very, very much! Invisigoth67 (talk) 18:34, 15 June 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

LOWER/UPPER function not working on page_title

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi,

Can somebody tell me why lower/upper function works properly on a given string (like 'ABC' or 'abc') but not on page_title?

Thanks Saeidpourbabak (talk) 17:27, 21 June 2018 (UTC)

The column is binary. You need to decode it with utf8/utf8mb4 first. quarry:query/27769 Zhuyifei1999 (talk) 17:31, 21 June 2018 (UTC)

Thanks for the quick and helpful reply. It works perfectly. Saeidpourbabak (talk) 17:44, 21 June 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

page URL?

Is page URL stored separately somewhere or is it derived from page_title somehow? I am trying to write query that returns URL's instead of page_titles. Jarekt (talk) 18:16, 22 June 2018 (UTC)

No, I don't think so (that there are page URLs) Edgars2007 (talk) 18:19, 22 June 2018 (UTC)

What is wrong with my Query

I can not get https://quarry.wmflabs.org/query/27897 to work, which is trying to find files uploaded without corresponding description page. I run into some new uploads like that (File:Muzeum Walki i Męczeństwa w Treblince 2018e.jpg) and would like to see if there are more. Jarekt (talk) 16:13, 28 June 2018 (UTC)

Append page_namespace = 6 to it. This will make your query use the unique index and will be a lot faster. Matěj Suchánek (talk) 16:56, 28 June 2018 (UTC)

Disambiguation pages with incoming links

I once had a Quarry that would list all pages with the {{Dp}} template on it, ordered by number of incoming links. Unfortunately I lost the code/query. Right now I create it with a looped python script, but that runs for 15-16 hours to complete, where quarry would take about a minute.

Can someone recreate it for me, as my tries failed all together until now. Edoderoo (talk) 15:50, 10 July 2018 (UTC)

Hi,

https://quarry.wmflabs.org/query/28134 should do the job. It gives DAB pages with the number of receiving links in front, including those trough templates, unfortunately. Saeidpourbabak (talk) 16:40, 10 July 2018 (UTC)

SELECT page_title, COUNT(pl_from) AS backlinks
FROM templatelinks
JOIN page ON page_id = tl_from
JOIN pagelinks ON pl_title = page_title AND pl_namespace = page_namespace
WHERE tl_title = 'Dp' AND tl_namespace = 10
GROUP BY tl_from
ORDER BY backlinks DESC;

Matěj Suchánek (talk) 16:05, 10 July 2018 (UTC)

The first one seems to give decent results, the second one has quite some false positives at first sight. Anyhow, thanks to both of you, I'm gonna work that out after my holidays! Edoderoo (talk) 20:26, 10 July 2018 (UTC)

Help to query Quarry

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi, I'm newbie at Quarry and I wanted to make a query for gl.wiki but I don't know how make it, could somebody help me?

At gl.wiki we wanted to get the number of female writers of category of writers of Galicia, https://gl.wikipedia.org/wiki/Categor%C3%ADa:Escritores_de_Galicia, and subcategories. Note that an article could appear at one or more subcategories, that's a the problem. Elisardojm (talk) 10:05, 16 July 2018 (UTC)

Deep category search with SQL is very difficult unless the depth of the category is one or two. Use PetScan instead. Matěj Suchánek (talk) 10:08, 16 July 2018 (UTC)

Thanks! Elisardojm (talk) 10:13, 16 July 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Automating table of Overlong Stub Categories on WP:Stub Sorting

Hi, I'm looking for help to build 2 queries that can generate tables that can meet the criteria of the following two tables https://en.wikipedia.org/wiki/Wikipedia:WikiProject_Stub_sorting/To_do#Oversized_stub_categories

1. The first table lists all stub cats with 6 or more listings pages (with # of pages)
2. The second table lists all stub cats with 5 listings pages (with # of pages)

I hope this could be a useful tool for directing stub diffusion work. Eventually, I'd like to take the generated queries and try to build a script that can regularly run the query (monthly or weekly). Furicorn (talk) 17:43, 16 July 2018 (UTC)

I think this is what you want: https://quarry.wmflabs.org/query/28347

For the second query just comment line 5 and uncomment line 4. HenriqueCrang (talk) 10:16, 18 July 2018 (UTC)

Thanks, I really appreciate this work! I think I worded my request a little confusingly - the query column called "cat_pages" definitely fulfills the original table's " Articles" column. However, I was trying to say something slightly different with "number of pages," but I don't have a better word, sorry. Hopefully I can do a better job communicating this, and furthermore it is something doable.

By number of pages, I'm referring to how a category with 400 articles might have 2 pages (only word I know to describe this, sorry!) in the category, but a category with 500 might have 3 pages (~200 articles/entries per page). I don't know if this information is easily retrievable, but I'm hoping it is.

So the final query would ideally have 3 columns

1. Category (cat_title, but output w/o underscores if this is easy)
2. Pages (# of pages in the sense described in paragraph 2, and a net new column)
3. Articles (currently titled cat_pages)|

Thanks again for any help. Furicorn (talk) 07:06, 21 July 2018 (UTC)

Hi,

If I understood you correctly, replacing the first line with "select cat_title Category, ceil(cat_pages/200) Pages, cat_pages Article" should do the job. Saeidpourbabak (talk) 02:22, 22 July 2018 (UTC)

Thanks to both of you, I was able to put together something that I think meets my needs https://quarry.wmflabs.org/query/28451 Furicorn (talk) 07:28, 22 July 2018 (UTC)

Wikidata

Since a table was deleted, how does one get the page_id for a qid efficiently?

page properties doesn't seem to work well. Jura1 (talk) 16:26, 22 August 2018 (UTC)

JOIN page ON page_namespace = 0 AND page_title = ... Matěj Suchánek (talk) 16:48, 22 August 2018 (UTC)

Thanks. Way too easy :) Jura1 (talk) 16:50, 22 August 2018 (UTC)

Perhaps I shouldn't have been so brief. wb_items_per_site still contains numbers, so you need to do either:

JOIN page ON page_namespace = 0 AND page_title = CONCAT('Q', ips_item_id)

or

JOIN wb_items_per_site ON REPLACE('Q', '', page_title) = ips_item_id

This will make the query use indexes and therefore will be fast. Matěj Suchánek (talk) 17:02, 22 August 2018 (UTC)

No index for revision table?

I'm running what looks like a very simple query:

select count(*) from revision where rev_user = 130326;

It's taking forever to complete. According to Manual:Revision table, rev_user should be the first part of the user_timestamp key, so this should be a fast indexed query. But, if I describe the table

describe revision;

I don't see any keys listed. RoySmith (talk) 09:55, 8 September 2018 (UTC)

MariaDB [enwiki_p]> select count(*) from revision_userindex where rev_user = 130326;
+----------+
| count(*) |
+----------+
|    23549 |
+----------+
1 row in set (3.81 sec)

See wikitech:Help:Toolforge/Database#Tables_for_revision_or_logging_queries_involving_user_names_and_IDs & wikitech:Help:MySQL_queries#Alternative_Views Zhuyifei1999 (talk) 15:31, 8 September 2018 (UTC)

Even quicker (the count process is a bit different, takes care for things like log events)

MariaDB [enwiki_p]> select user_editcount from user where user_id = 130326;
+----------------+
| user_editcount |
+----------------+
|          24197 |
+----------------+
1 row in set (0.01 sec)

Framawiki (talk) 07:32, 9 September 2018 (UTC)

Interesting. There's no table "revision_userindex" listed in Category:MediaWiki database tables. Is that just not up to date? I do see it when I run "show tables", and the query you suggest does indeed work.

So, the next obvious question is, why do we have two copies of the table, one with the index and another that's missing it? RoySmith (talk) 16:08, 8 September 2018 (UTC)

Because Category:MediaWiki database tables lists the tables that built-in to MediaWiki. These tables can contain highly sensitive data, such as hashes of passwords, tokens, IP addresses, revision-deleted or oversighted revision comments, etc. Therefore, when these databases are replicated to wiki replicas, which Quarry queries from, some tables like revision must redact some cells from a raw when a certain criteria is met.

We implement some of the redaction by not replication some tables at all, and some other redaction by using views. As for your query, the relevant redaction in view (not table) revision is if((`enwiki`.`revision`.`rev_deleted` & 4),NULL,`enwiki`.`revision`.`rev_user`) AS `rev_user`, which makes rev_user NULL when rev_deleted has bit 2^2 (for revdel-ed username) set. As a side effect of using this if, indexes in the table revision for rev_user cannot be used.

Since unusable indexes is a serious performance problem, the DBAs made alternative views that redact the rows entirely. This is the case of view revision_userindex, where the definition includes and `enwiki`.`revision`.`rev_user` AS `rev_user` and where ((`enwiki`.`revision`.`rev_deleted` & 4) = 0). rev_user is now a simple passthrough, allowing use of indexes on that column; the bit condition in rev_deleted is now an implicit but enforced where condition to the underlying revision table that we cannot query directly.

So no, we don't have two copies of the table, but two views of the same table. Zhuyifei1999 (talk) 00:03, 9 September 2018 (UTC)

Ah, cool. Thanks. That all makes perfect sense.

Maybe the "Database tables" tab in Quarry could include this information? I imagine I'm not the first person to stub my toe on this. RoySmith (talk) 00:20, 9 September 2018 (UTC)

Good point. Though, what link text and target do you suggest? cc @Framawiki: Zhuyifei1999 (talk) 00:22, 9 September 2018 (UTC)

It would be great to have a wikitech page that includes the map of replicas table like the mediawiki one [3] Currently a thing like this doesn't exist as I know.

See also phab:T139289 Framawiki (talk) 07:35, 9 September 2018 (UTC)

I realize this is a comment from 2018, but I've had the same issue today, and I'll second the request for a wikitech page that lists these tables. JPxG (talk) 23:25, 18 October 2021 (UTC)

Seeking list of Wikimedia user accounts with blocks

Thanks for any help anyone can provide. If it is easy then I would appreciate a query. If it is more complicated then I would appreciate general instructions.

I need to learn how to list all English Wikipedia user accounts which are blocked.

I am looking at Manual:Database layout trying to understand which property in this scheme matches to a user account block. After I learn how to query for blocks I want to query for accounts with other userrights, but I am not seeing how this is mapped. If anyone has advice then I would appreciate it. Thanks. Blue Rasberry (talk) 14:00, 11 September 2018 (UTC)

USE enwiki_p;
SELECT ipb_address FROM ipblocks WHERE ipb_user <> 0;
SELECT ipb_address FROM ipblocks
JOIN user_groups ON ug_user = ipb_user
WHERE ipb_user <> 0
AND ug_group = 'user_group_identifier';

Matěj Suchánek (talk) 14:10, 11 September 2018 (UTC)

user rename

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

How does one request a username change for quarry? Vexations (talk) 12:54, 16 September 2018 (UTC)

@Framawiki: Can I merge gerrit:398711 ? Zhuyifei1999 (talk) 21:05, 16 September 2018 (UTC)

@Vexations: You should get renamed after your next login to Quarry after your SUL rename. Zhuyifei1999 (talk) 14:33, 18 September 2018 (UTC)

@Vexations: can you confirm that the problem is solved ? Framawiki (talk) 07:03, 23 September 2018 (UTC)

Yes, it has been solved, thanks, Vexations (talk) 10:53, 23 September 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Is externallinks not available for the English Wikipedia?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

I was hoping to do a query to find Flickr images on enwiki (these need to be moved to Commons). I get an error. Thanks.

View 'enwiki_p.externallinks' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them B (talk) 18:49, 17 September 2018 (UTC)

https://quarry.wmflabs.org/query/29759 Edgars2007 (talk) 12:42, 18 September 2018 (UTC)

No, you should be able to run that query. Maybe related to phab:T202588, user:Zhuyifei1999? Edgars2007 (talk) 12:43, 18 September 2018 (UTC)

Not related. The underlying wiki replicas seems to deny the access.

MariaDB [enwiki_p]> SELECT el_from FROM externallinks limit 5;
ERROR 1356 (HY000): View 'enwiki_p.externallinks' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Could you file a bug under #data-services? Zhuyifei1999 (talk) 14:32, 18 September 2018 (UTC)

Any news? B (talk) 19:34, 20 September 2018 (UTC)

Could you file a bug under #data-services?

Zhuyifei1999 (talk) 21:21, 20 September 2018 (UTC)

https://phabricator.wikimedia.org/T205112 Edgars2007 (talk) 15:53, 21 September 2018 (UTC)

Thanks B (talk) 17:37, 21 September 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Important lack/bug discovered?

After these major updates in the Quarry, I have found a lack/bug?

If you use this: AND page_title IN ('phrase X', 'phrase Y', 'phrase ...') (test template here: https://quarry.wmflabs.org/query/29938) then there you can only put fewer than 750 phrases. Before these major updates the maximum number was about 2200 phrases. Any suggestions? Estopedist1 (talk) 08:47, 25 September 2018 (UTC)

I tested a little bit. It seems that important is the sum of characters, not the sum of lines. It seems that the limit is about 13 000 characters. And if your quarry consists of, for example, 14 000 characters it cannot be done.

If I am right, then why earlier the limit was much higher than 13 000 characters? Estopedist1 (talk) 12:02, 25 September 2018 (UTC)

For some reason max_allowed_packet is 1024. It's supposed to be 16M... Zhuyifei1999 (talk) 16:37, 25 September 2018 (UTC)

Arturo made a patch for this: gerrit:462769, thanks. Zhuyifei1999 (talk) 17:06, 25 September 2018 (UTC)

@Estopedist1 The patch has now been merged and applied. Could you confirm it works now? Zhuyifei1999 (talk) 13:25, 26 September 2018 (UTC)

Yes, it is more powerful now. About 36 000 characters. I think earlier it was more powerful than now, but no problem for me. I guess it probable consumes very much energy (I mean servers, hardware) to rise the max_allowed_packet. Estopedist1 (talk) 17:51, 26 September 2018 (UTC)

Namespace string?

How do I map from page.page_namespace to a string ("Draft", "Talk", etc)? RoySmith (talk) 03:14, 4 October 2018 (UTC)

I would like to know as well if there is a shortcut for this. But meanwhile I use this

SELECT page_namespace, (case

when page_namespace = 0 then 'Main'

when page_namespace = 1 then 'Talk'

when page_namespace = 2 then 'User'

when page_namespace = 3 then 'User talk'

when page_namespace = 4 then 'Wikipedia'

when page_namespace = 5 then 'Wikipedia talk'

when page_namespace = 6 then 'File'

when page_namespace = 7 then 'File talk'

when page_namespace = 8 then 'MediaWiki'

when page_namespace = 9 then 'MediaWiki talk'

when page_namespace = 10 then 'Template'

when page_namespace = 11 then 'Template talk'

when page_namespace = 12 then 'Help'

when page_namespace = 13 then 'Help talk'

when page_namespace = 14 then 'Category'

when page_namespace = 15 then 'Category talk'

when page_namespace = 100 then 'Portal'

when page_namespace = 101 then 'Portal talk'

when page_namespace = 828 then 'Module'

when page_namespace = 829 then 'Module talk'

end) as namespace_name Tohaomg (talk) 13:29, 4 October 2018 (UTC)

I've been playing around with this, but can't get it to work. Unfortunately, my SQL is kind of weak. When I run https://quarry.wmflabs.org/query/30504, I get Unknown column 'page_namespace' in 'field list'.

Can you give an example of how this would be used in a real query? RoySmith (talk) 14:30, 20 October 2018 (UTC)

The namespace label varies by wiki (depending on installed extensions) and UI language. See phab:T180558 for some discussion of a user provided table on ToolsDB that was once available for this sort of lookup. Unfortunately even if that tool was fixed there is currently no way to connect to ToolsDB from Quarry per phab:T151158 BDavis (WMF) (talk) 01:03, 5 October 2018 (UTC)

We download this periodically to make use of it in Analytics, this is the script we use:

https://github.com/wikimedia/analytics-refinery/blob/master/bin/download-project-namespace-map

You can store the results in a table and join to that if you like. Milimetric (WMF) (talk) 20:37, 5 October 2018 (UTC)

user activity data

Im looking for a query that can give me user id/name alongwith all activity associated with the user such as comments/interaction with other users as well as the text of edit made on pages 199.111.226.142 (talk) 02:41, 5 October 2018 (UTC)

even the table names that need the join will be good to know. I'll write the query by myself 199.111.226.142 (talk) 02:41, 5 October 2018 (UTC)

The text has to be fetched from the API, it's not queryable. But the metadata associated with users is in the user table. Their contributions are in the revision table, where "rev_user" is the user id. Take a look at the schema documentation: https://www.mediawiki.org/wiki/Manual:Database_layout

I'm not sure what you're working on, but it sounds like the Detox research project might be of interest: https://meta.wikimedia.org/wiki/Research:Detox Milimetric (WMF) (talk) 20:34, 5 October 2018 (UTC)

Hey @Milimetric

Thanks a ton for your reply! Yes i am trying to do something along the lines of the Detox research. Have a couple of doubts. Would be great if you have any insight into this -

1. The database layout page says that there's a table called text which contains the "text content" pertaining to the revisions made. But when i try to access that table using Quarry , i get an error saying that the table doesn't exist. Manual:Text table so it does seem like the text should exist in the table format but i wonder why it says that the table doesn't exist. Any idea? It would be ideal to get the data from a sql like table format, my last resort would be to parse through the XML dump....
2. What would be the best way to extract conversations made on article or user talk spaces? I'm interested in analyzing user conversations and link that to the probability of a user getting blocked in the future.... Cr29uva (talk) 22:08, 11 October 2018 (UTC)

@Cr29uva, yes, the text table isn't replicated to these databases because it's too big and hard to strip private parts out of it. The XML dumps are your best bet for now. We're close to releasing a new dataset that's more analytics-oriented, that may be of interest to you in the future. It will have user blocks as applied over the history of each user (so, for example, if someone's blocked five times, it will have those periods with a start and end). It won't have text initially either, but we're working on adding that over the next year. Milimetric (WMF) (talk) 14:55, 16 October 2018 (UTC)

user conversations data

What would be the best way to extract conversations made on article or user talk spaces?

If someone could point me towards the XML dump to be used for this or a table in the database that can give me the text...

I'm interested in analyzing user conversations and link that to the probability of a user getting blocked in the future... Cr29uva (talk) 22:10, 11 October 2018 (UTC)

The dump you need is, for example for simplewiki, https://dumps.wikimedia.org/simplewiki/20181001/. You want the sets that have the full revision history to recreate conversations. When you go through it you can filter down to just the Talk and User_talk namespaces. Milimetric (WMF) (talk) 17:14, 17 October 2018 (UTC)

yes, i was able to figure out that i need the xml dump of pages-revision-meta-history.

Thanks a ton for your replies @Milimetric (WMF) ! Cr29uva (talk) 18:12, 17 October 2018 (UTC)

Is there some way of automagic update/re-execute quarries from tools-Servers?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

For a few of my quarries it might be a good idea to refresh the output from time to time (eg. weekly) since they could be used a list to fix some inconsistencies. Is there any way of doing this by a cronjob or script or so? Wurgl (talk) 16:17, 18 October 2018 (UTC)

Not yet. There is a ticket for this somewhere in phab:project/view/800/ but can't find it. Framawiki (talk) 17:50, 25 October 2018 (UTC)

A ticket is fine! Maybe you mean T203791 ? Wurgl (talk) 18:08, 25 October 2018 (UTC)

Found ! phab:T101835 Framawiki (talk) 19:11, 25 October 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Not able to download output

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi, I have THIS quarry with 20422 rows results. When I try to download the output, I get only 1070 rows in the CSV file, and for the XLSX I get an empty (0 KB) file.

Any idea what is wrong? Saeidpourbabak (talk) 15:49, 6 November 2018 (UTC)

It's something about having surrogates (CC @Framawiki: )

Nov 06 18:09:26 quarry-web-01 uwsgi-quarry-web[3030]: Traceback (most recent call last):
Nov 06 18:09:26 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/werkzeug/wsgi.py", line 870, in __next__
Nov 06 18:09:26 quarry-web-01 uwsgi-quarry-web[3030]:     return self._next()
Nov 06 18:09:26 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/werkzeug/wrappers.py", line 84, in _iter_encoded
Nov 06 18:09:26 quarry-web-01 uwsgi-quarry-web[3030]:     yield item.encode(charset)
Nov 06 18:09:26 quarry-web-01 uwsgi-quarry-web[3030]: UnicodeEncodeError: 'utf-8' codec can't encode character '\udcd9' in position 385: surrogates not allowed

Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]: Traceback (most recent call last):
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/werkzeug/wsgi.py", line 870, in __next__
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     return self._next()
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/werkzeug/wrappers.py", line 82, in _iter_encoded
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     for item in iterable:
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/werkzeug/contrib/iterio.py", line 183, in __new__
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     rv = g.switch()
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/werkzeug/contrib/iterio.py", line 178, in run
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     func(stream)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "./quarry/web/output.py", line 152, in respond
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     if (worksheet.write(row_num, col_num, cell) < -1 and
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/xlsxwriter/worksheet.py", line 67, in cell_wrapper
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     return method(self, *args, **kwargs)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/xlsxwriter/worksheet.py", line 406, in write
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     return self._write(row, col, *args)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/xlsxwriter/worksheet.py", line 431, in _write
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     return self._write_token_as_string(token, row, col, *args)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/xlsxwriter/worksheet.py", line 387, in _write_token_as_string
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     return self._write_string(row, col, *args)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/xlsxwriter/worksheet.py", line 522, in _write_string
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     self._write_single_row(row)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/xlsxwriter/worksheet.py", line 5432, in _write_single_row
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     self._write_cell(row_num, col_num, col_ref)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/xlsxwriter/worksheet.py", line 5610, in _write_cell
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     self._xml_inline_string(string, preserve, attributes)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/srv/quarry/venv/lib/python3.5/site-packages/xlsxwriter/xmlwriter.py", line 167, in _xml_inline_string
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     (attr, t_attr, string))
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/usr/lib/python3.5/codecs.py", line 718, in write
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     return self.writer.write(data)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:   File "/usr/lib/python3.5/codecs.py", line 376, in write
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]:     data, consumed = self.encode(object, self.errors)
Nov 06 18:09:46 quarry-web-01 uwsgi-quarry-web[3030]: UnicodeEncodeError: 'utf-8' codec can't encode character '\udcd9' in position 39: surrogates not allowed

Zhuyifei1999 (talk) 18:11, 6 November 2018 (UTC)

>>> surrogate = b'\xc3'.decode('utf8','surrogateescape')
>>> surrogate
'\udcc3'
>>> surrogate.encode('utf-8')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
UnicodeEncodeError: 'utf-8' codec can't encode character '\udcc3' in position 0: surrogates not allowed
>>> surrogate.encode('utf-8', 'surrogateescape')
b'\xc3'

Zhuyifei1999 (talk) 18:14, 6 November 2018 (UTC)

This is bad, but I don't know of any other way to get control of the error handler used in _iter_encoded:

>>> import codecs
>>> codecs.register_error('strict', codecs.lookup_error('surrogateescape'))
>>> 
>>> surrogate = b'\xc3'.decode('utf8','surrogateescape')
>>> surrogate.encode('utf-8')
b'\xc3'
>>> codecs.lookup_error('surrogateescape')
<built-in function surrogateescape>

@Framawiki: Any objections to applying this 'hack'? Zhuyifei1999 (talk) 18:23, 6 November 2018 (UTC)

I also broke the worker while playing with surrogates:

Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]: [2018-11-06 18:33:00,947: ERROR/ForkPoolWorker-25] Task worker.run_query[6a7ebdd0-fa08-4732-aa2a-2deafb5d7892] raised unexpected: UnicodeDecodeError('utf-8', b'\xed\xb3\x83', 0, 1, 'invalid continuation byte')
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]: Traceback (most recent call last):
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/celery/app/trace.py", line 382, in trace_task
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     R = retval = fun(*args, **kwargs)
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/celery/app/trace.py", line 641, in __protected_call__
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     return self.run(*args, **kwargs)
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/quarry/web/worker.py", line 74, in run_query
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     while cur.nextset():
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/pymysql/cursors.py", line 107, in nextset
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     return self._nextset(False)
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/pymysql/cursors.py", line 102, in _nextset
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     conn.next_result(unbuffered=unbuffered)
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/pymysql/connections.py", line 520, in next_result
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/pymysql/connections.py", line 727, in _read_query_result
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     result.read()
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/pymysql/connections.py", line 1073, in read
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     self._read_result_packet(first_packet)
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/pymysql/connections.py", line 1143, in _read_result_packet
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     self._read_rowdata_packet()
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/pymysql/connections.py", line 1181, in _read_rowdata_packet
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     rows.append(self._read_row_from_packet(packet))
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:   File "/srv/quarry/venv/lib/python3.5/site-packages/pymysql/connections.py", line 1197, in _read_row_from_packet
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]:     data = data.decode(encoding)
Nov 06 18:33:00 quarry-worker-02 celery-quarry-worker[2512]: UnicodeDecodeError: 'utf-8' codec can't decode byte 0xed in position 0: invalid continuation byte

Zhuyifei1999 (talk) 18:39, 6 November 2018 (UTC)

The patch is gerrit:472377 Zhuyifei1999 (talk) 22:17, 11 November 2018 (UTC)

Hello and thanks for taking action.

Do I have to do something or I just need to wait? I'm afraid that your replies and gerrit link is too technical for me. Saeidpourbabak (talk) 12:35, 2 December 2018 (UTC)

Hi Saeidpourbabak and thanks for the ping. We'll try to fix the site soon and will contact you when it's done. Framawiki (talk) 20:56, 2 December 2018 (UTC)

This should be fixed now:

$ curl -s https://quarry.wmflabs.org/run/310305/output/0/csv | wc -l
20423
$ wget -O /dev/null https://quarry.wmflabs.org/run/310305/output/0/xlsx
--2018-12-02 15:40:35--  https://quarry.wmflabs.org/run/310305/output/0/xlsx
Resolving quarry.wmflabs.org... 208.80.155.156
Connecting to quarry.wmflabs.org|208.80.155.156|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/vnd.openxmlformats-officedocument.spreadsheetml.sheet]
Saving to: ‘/dev/null’
/dev/null                               [   <=>                                                            ] 454.11K  1.05MB/s    in 0.4s    
2018-12-02 15:40:38 (1.05 MB/s) - ‘/dev/null’ saved [465013]

Zhuyifei1999 (talk) 21:41, 2 December 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Accessing text table

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

According to mw:Manual:Text_table the actual wikitext of pages is stored in text table However when I try to search it for specific string in https://quarry.wmflabs.org/query/31044 I get error "Table 'commonswiki_p.text' doesn't exist". So is there a way to access that table? Jarekt (talk) 18:29, 7 November 2018 (UTC)

No. As far as I understand, the table, if it were available, would just be pointers to the text in 'external storage' and be pretty much meaningless anyways without access to this 'external storage'. You can however query the text with Special:Search, or use indirect queries like with categorylinks or externallinks to get the features of the text. Zhuyifei1999 (talk) 18:38, 7 November 2018 (UTC)

Doesn't the text table also contain information about deleted revisions which shouldn't be publicly available?

The documentation states that the text may be in various formats (gzipped, stored elsewhere etc.) so a simple search query might not work. Besides, with the LIKE operator used in quarry:31044, the execution time would be proportional to the total number of text revisions on Commons, which would maybe be too slow. Stefan2 (talk) 20:09, 7 November 2018 (UTC)

Ah. this search query shall do :) Zhuyifei1999 (talk) 02:18, 8 November 2018 (UTC)

Thank you both. I never fully trusted regular search, but it seems like in this case it is better than SQL query. Jarekt (talk) 14:13, 8 November 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Need help with finding most transcluded unprotected pages

I am trying to make sure some of the most transcluded pages (hopefully templates and modules) on Commons are protected. The first step would be to find some number (lets say 100) most transcluded pages and than look up their protection level, So I started with


use commonswiki_p;
SELECT tl_namespace, tl_title, count(*) as tot
FROM templatelinks
group by tl_namespace, tl_title
order by tot desc
limit 100


Which was killed after half an hour (see https://quarry.wmflabs.org/query/8653). Anybody knows what is wrong with this query and how to fix it? The results should mirror c:Special:MostTranscludedPages. Jarekt (talk) 17:23, 9 November 2018 (UTC)

Trying the query out in the sql-optimizer tool shows that there are 1,605,698,124 (yes, 1.6 billion!) rows to process. The group by and order by clauses force the database server to copy all of the data into temporary storage and then perform a file sort on that storage. The limit clause will only change the data returned to your client, so all the rows have to be grouped and then the counts that were computed for each group all have to be sorted and finally only the first 100 results will be returned.

Your best best for getting this sort of data is to use the Action API's query action to get the same list you see on Special:MostTranscludedPages in a machine readable format: https://commons.wikimedia.org/wiki/Special:ApiSandbox#action=query&format=json&list=querypage&utf8=1&formatversion=2&qppage=Mostlinkedtemplates BDavis (WMF) (talk) 21:27, 9 November 2018 (UTC)

Yes sorting 1.6 billion pages is a big job. My plan was to use that query as a piece of larger query where I than look up protection level of each page. Maybe I should be counting transclusions of unprotected templates (and latter modules) and show the ones with over some number (1M or 500k). So limit the scope first and than count. Jarekt (talk) 03:47, 10 November 2018 (UTC)

phab:T65782 would have helped, so one can join the results of the special page query directly.

FWIW, the given 'killed' query is not that different from what MediaWiki uses internally Zhuyifei1999 (talk) 05:58, 10 November 2018 (UTC)

quarry:query/31088 would be a query that checks the transclusion count from all templates, using a dependent subquery on templatelinks table. Don't think it'll work though. Zhuyifei1999 (talk) 06:34, 10 November 2018 (UTC)

quarry:query/31088 is quite tricky, as far as I understand it you go through all the templates, count number of transclusions for each, keep only the one with over a million counts, and sort them at the end. I restricted my query to templates only and it also worked. See https://quarry.wmflabs.org/query/8653. 31088 Is however much better as a starting point for looking up pages that need to be protected. See https://quarry.wmflabs.org/query/31129 and https://quarry.wmflabs.org/query/31131. Jarekt (talk) 04:07, 12 November 2018 (UTC)

Wow, 31131 is so much faster than I would have anticipated. Nice. I guess most of the time it's counting the transclusions of protected templates, and the time of joining and filtering on page_restrictions is minimal. Zhuyifei1999 (talk) 06:57, 12 November 2018 (UTC)

Watchlist?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Can I query my watchlist in quarry? I get:

Table 'enwiki_p.watchlist' doesn't exist RoySmith (talk) 14:59, 17 November 2018 (UTC)

No, you can't. Personal information etc. Edgars2007 (talk) 16:52, 17 November 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Is site_stats protected now?

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Since my statistics bot failed to run today, I noticed that I can't access the site_stats table on any wiki.

Here's a simple query that doesn't work anymore as of today:

USE rowiki_p;

DESCRIBE site_stats;

This yields the following error: View 'rowiki_p.site_stats' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

If I use SHOW TABLES FROM rowiki_p;, I can see that the table still exists. I was using it to get the total number of edits on rowiki.

Any idea why it's suddenly inaccessible? Andreyyshore (talk) 23:01, 22 November 2018 (UTC)

Hi, that is not limited to Quarry but to the whole wmf labs replicas, and the restriction was not intentional. See phab:T210481. Framawiki (talk) 18:04, 27 November 2018 (UTC)

Thanks for clarifying where the problem was.

Like the other commenters, I can also confirm that the problem is gone now. Andreyyshore (talk) 22:00, 27 November 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

revision table rev_comment missing

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Hi, It seems to me that the revision table is not working properly, namely rev_comment data is missing. I know I have tons of edits with a specific summary, but now I find none. For example see these two quarries, one from 3 months ago and one from today. Thanks. Saeidpourbabak (talk) 12:27, 2 December 2018 (UTC)

Hi, no, that's not a bug :) Since recently (phab:T166733) comments are available in a new specific table. See Manual:Revision table#rev comment for the depreciation message. You'll need to use a join between revision and comment tables. Framawiki (talk) 20:58, 2 December 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Not able to perform query on itwiki_p

The following discussion is closed. Please do not modify it. Subsequent comments should be made on the appropriate discussion page. No further edits should be made to this discussion.

---

Until yesterday evening I used "https://quarry.wmflabs.org/query/26129" and "https://quarry.wmflabs.org/query/31383" to perform some query for lint errors. This morning I'm unable to perform that query on itwiki (i try the same query on enwiki_t without problem). The page return this error "View 'itwiki_p.page' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" Lucarosty (talk) 11:40, 3 December 2018 (UTC)

This is phab:T210978. We are looking into it. Zhuyifei1999 (talk) 15:33, 3 December 2018 (UTC)

Perfect now is working! Thanks Lucarosty (talk) 16:53, 3 December 2018 (UTC)

The discussion above is closed. Please do not modify it. No further edits should be made to this discussion.

Number of pages in DjVu files

To count number of one page PDF file one can use select count(*) from image where img_minor_mime='pdf' and img_metadata like '%\"Pages\";s:1:\"1";%'. I am looking for something similar for DjVu files. Are there better ways for counting pages in multipage files? Jarekt (talk) 21:18, 12 December 2018 (UTC)

It seems that DjVu handler retrieves page count from img_metadata as well. It does so by parsing the xml, finding the 'metatree' element and then counting the amount of objects contained within it.

$count = count( $metatree->xpath( '//OBJECT' ) );

https://github.com/wikimedia/mediawiki/blob/master/includes/media/DjVuHandler.php#L292

A better way might be to use the api here, which has defined accessors for this (it's in the dimensions properties of images/media):

https://commons.wikimedia.org/w/api.php?action=query&titles=File:H.M.S.%20Pinafore.djvu&prop=imageinfo&iiprop=timestamp%7Cuser%7Curl%7Cdimensions

—TheDJ (Not WMF) (talk • contribs) 09:44, 14 December 2018 (UTC)

I know I can access number of pages from lua and I see I can also use API, but I was looking for a way to build SQL query to detect single page DjVu files. I think I got it: quarry:query/32028 seems to work. Jarekt (talk) 13:32, 14 December 2018 (UTC)

This does not seem to work for all DjVu files. Many seems to have as metadata only:

{

  "blobs": {

   "data": "tt:609532023",

   "text": "tt:609532024"

  },

  "data": []

}

Which are pointers into text table, but it is not really possible to access text table? At least not for me as I am working through SQL dumps and it seems it is not available there. Mitar (talk) 21:18, 4 February 2022 (UTC)

Note that there was recently a major change to how djvu metadata is stored and parsed for MediaWiki. See also recent changes in https://phabricator.wikimedia.org/T275268 and https://phabricator.wikimedia.org/T192866

Depending on when your SQL dump originates, this might effect what you see —TheDJ (Not WMF) (talk • contribs) 23:27, 4 February 2022 (UTC)

Yes, I have noticed this. As I said, metadata is now moved to blob storage. But there are no dumps of blob storage? So how does one access metadata now in offline mode? Mitar (talk) 23:39, 4 February 2022 (UTC)

Which file in the Wikipedia dumps corresponds to the "revision" table?

I need to download the entire revision history of English Wikipedia (the metadata for each edit, not the content itself). On Quarry, that's the "revision" table. However I can't download it from Quarry because it's too big. I looked in the Wikipedia dumps site:

https://dumps.wikimedia.org/enwiki/20181201/

And I wasn't sure which file or set of files I need. I can see files corresponding to some of the tables I can query (e.g. user_groups.sql, redirect.sql, etc) but I can't find one named revision.sql or similar.

There are two collections of files, both of which have "These files contain no page text, only revision metadata." in the subheading. Is one of these the one I need? If so, which one? I don't want to download many gigabytes of files only to find I chose the wrong one ..

Thankyou. Gatherinfer (talk) 21:02, 19 December 2018 (UTC)

This question is probably better asked on the XmlDataDumps mailing list. BDavis (WMF) (talk) 16:27, 20 December 2018 (UTC)

Dynamic Quarry

I have custom Query to get required data for given page_title. Since one of the parameter title is dynamic, I can't download the results in json from static link given by Quarry. is there any work around to pass the Query dynamically and get the result in json? Neechalkaran (talk) 19:50, 24 December 2018 (UTC)

Hi, if by "dynamic" you want to execute queries with a parameter that changes, no it's not possible. It would be probably better to create a tool with a webpage : wikitech:Portal:Toolforge. Framawiki (talk) 14:23, 26 December 2018 (UTC)

Thank you. Now I understand the limitation. Atleast can I get latest results in json when I call it? It seems https://quarry.wmflabs.org/run/317046/output/0/json is static and it did not get update unless, the query was triggered. Neechalkaran (talk) 20:26, 27 December 2018 (UTC)

The link you provided allows to download the result of a query run (number 317046). It is static results. If you want to have updated ones, you have to execute the query again. You should really take a look to toolforge, that offers direct access to db servers, so the ability to execute your query as needed. Framawiki (talk) 14:26, 29 December 2018 (UTC)