As an example from my BEACON tool, I want all properties that have a formatter property, with English label. That SQL is now:
SELECT DISTINCT page_title,term_text FROM pagelinks,page,wb_terms WHERE page_namespace=120 AND substr(page_title,2)=term_entity_id and term_entity_type='property' and term_language='en' and term_type='label' and pl_from=page_id and pl_title='P1630' and pl_namespace=120 and pl_from_namespace=120 ORDER BY term_text
Note the "substr". My first attempt was "page_title=concat('Q',term_entity_id)", but that took forever.
If we indeed get a full entity ID=page title column for wb_terms, and for wb_items_per_site etc., that would at least fix the on-the-fly compute. I shall thus wait with code updates until I get the full story, and not just piece-by-piece...
On Thu, Jun 1, 2017 at 3:29 PM Daniel Kinzler daniel.kinzler@wikimedia.de wrote:
Am 01.06.2017 um 16:07 schrieb Magnus Manske:
So I'll be busy finding uses of this table, and changing them, for the
next week
or two...
Note that since now I have to use substring comparisons in queries
(instead of
integer comparisons), for example with wb_terms, SQL queries will run
slower as
a result.
wb_terms will also get a column with the full entity ID, and will lose the numeric ID column. We will announce this separately.
Magnus, can you give some examples of what you use wb_entity_per_page for? If it's for building links, you can now use the IDs directly, without looking at the page table. Are there other things besides the terms table that would use substrings?
Thanks!
-- Daniel Kinzler Principal Platform Engineer
Wikimedia Deutschland Gesellschaft zur Förderung Freien Wissens e.V.