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.

Achim55 helped me

Hi, Xaoflux gently made me a quarry a year ago (quarry:query/33757), but now I would like to find all french translation pages on Meta with a "·" in its content. And where could I find an index of the different databases replicas of commons, outreach, species, mv, fr.wikt... to fork the quary for different wikis ?

Thank you for the help

For a list see query/278 (shows now 916 entries) and https://noc.wikimedia.org/conf/dblists/all.dblist . If you want to search for "·" not only in titles but in real content of the page you will have to use the API.

Thank you but how would I do such thing ?

Can you give an example of a page you want to be found? Did a simple search for intitle:/fr insource:/·/ fit your needs?

I didn't know the "insource" parameter in the research ! It perfectly resolve my problem ! Thank you !

Hello, how the added text of a revision could be requested?

Hi, you can't. These "replicas" databases only contains metadata (everything public except text or diffs). Use m:Dumps or Api (ie. Pywikibot) in case you really want some text. First is downloading file to process offline on your computer, second is to query each single page you need from the server. Both requires you to write a script in some programming language.

Is info available on the database table links to other tables? I saw the new schema (very nice) here on MW, and studied the field names but couldn't figure out the links as I have been able. Was there a redesign of the table links in the past couple of years?

What do you mean with "links"? On Quarry, you can use SHOW TABLES; to display all available tables and DESCRIBE some_table; to see the list of columns.

You are right. I was used to the old schema where the related fields were graphically linked.

P.S: I found what I was looking for but it was for version 1.20

While I'm more used to the "command" interface, there is also "Database tables" tab on the top the pages on Quarry which provides a link to the page with "Explore database schema" and also to https://sql-optimizer.toolforge.org/ which includes "Schema browser" feature.

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.

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.

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;

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!

Exactly same behavior as 8 months ago, see Topic:Vb3o5oh0qg0wosgo when the web node ran out of disk space.

Ah, now it runs again

For the record, that was phab:T257453

Hello all,

I would like to search for Wikidata items with inordinately long descriptions (say, more than 250 characters). I have already tried this query with no success. (Unfortunately, my knowledge of SQL is minimal.)

USE wikidatawiki_p;
SELECT wbit_item_id, wbxl_language, wbx_text
FROM wbt_item_terms
INNER JOIN wbt_term_in_lang ON wbit_term_in_lang_id = wbtl_id
INNER JOIN wbt_text_in_lang ON wbtl_text_in_lang_id = wbxl_id
INNER JOIN wbt_text ON wbxl_text_id = wbx_id
WHERE wbtl_type_id = 2
-- AND wbxl_language = ''
AND LENGTH(wbx_text) > 250

You can also filter by language (as hinted). You'll probably want to add some limit.

Hello,

I wonder if my research is possible.

I'm just trying to get a list of the names of all the 'new' persons that have been entered in wiki in the last two months (so I should find there Stella Morris, George Floyd, Derek Chauvin etc... and a lot of people who were unknown 2 months ago).

I suppose I should adapt this query but I don't know how.

Thanks for your help if you can

USE wikidatawiki_p;

SELECT rev_id AS first_edit FROM revision WHERE rev_timestamp BETWEEN "20200501" AND "20200624" ORDER BY RAND() LIMIT 5000;

Hi.

Where can I learn about these tables

wb_changes;

wb_changes_dispatch;

wb_changes_subscription;

wb_id_counters;

wb_items_per_site;

wb_property_info;

wb_terms;

wbc_entity_usage;

wbqc_constraints;

wbs_propertypairs;

wbt_item_terms;

wbt_property_terms;

wbt_term_in_lang;

wbt_text;

wbt_text_in_lang;

wbt_type;

For example, we have this category for MediaWiki Database tables. Thanks ^_^

See mw:Category:Wikibase schema but some of the tables are missing and even if there is a page for the database table, the table is often poorly documented.

Examples for a subset for them is in phab:T221767.

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/