Page MenuHomePhabricator

What do queries in the new schema look like?
Open, Needs TriagePublic

Description

Below are two sections of sample queries.
First one is only general examples that do not map exactly to real-life use-cases, but are a sort of intro to how new schema is designed and how queries would change.
The second one will contain real-life queries executed on wb_terms, take from our logs, and converted to equivalent queries in the new schema.


General examples

Looking up all terms of a property/item

[OLD] wb_terms

## For a property with id P123

SELECT term_language, term_type, term_text
FROM wb_terms
WHERE term_full_entity_id = 'P123'


## For an item with id Q321

SELECT term_language, term_type, term_text, term_language
FROM wb_terms
WHERE term_full_entity_id = 'Q321'

[NEW] new schema

## For a property with id P123

SELECT
	wbxl_language as term_language,
	wby_name as term_type,
	wbx_text as term_text
FROM wbt_property_terms
	INNER JOIN wbt_term_in_lang ON wbpt_term_in_lang_id = wbtl_id
	INNER JOIN wbt_type ON wbtl_type_id = wby_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
	wbpt_property_id = 123
;


## For an item with id Q321

SELECT
	wbxl_language as term_language,
	wby_name as term_type,
	wbx_text as term_text
FROM wbt_item_terms
	INNER JOIN wbt_term_in_lang ON wbit_term_in_lang_id = wbtl_id
	INNER JOIN wbt_type ON wbtl_type_id = wby_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
	wbit_item_id = 321
;

Retrieve labels of entity in a specific language

[OLD] wb_terms

SQL

## For a property with id P123, in 'en-gb' language

SELECT term_language, term_type, term_text
FROM wb_terms
WHERE
    term_full_entity_id = 'P123'
    AND term_type       = 'label'
    AND term_language   = 'en-gb'
;

[New] new schema

SQL

## For a property with id P123, in 'en-gb' language

SELECT
    wbxl_language   as term_language,
    wby_name        as term_type,
    wbx_text        as term_text
FROM wbt_property_terms
    INNER JOIN wbt_term_in_lang ON wbpt_term_in_lang_id = wbtl_id
    INNER JOIN wbt_type         ON wbtl_type_id = wby_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
    wbpt_property_id    = 123
    AND wby_name        = 'label'
    AND wbxl_language   = 'en-gb'
;

Lookup entity ids by label or alias

[OLD]

## Get property ids of properties with a label or alias starting with 'hell' across languages

SELECT DISTINCT term_full_entity_id
FROM wb_terms
WHERE
    term_entity_type     = 'property'
    AND term_type       IN ( 'label', 'alias' )
    AND term_text     LIKE 'hell%'
;

[NEW]

## Get property ids of properties with a label or alias starting with 'hell' across languages

SELECT DISTINCT ( CONCAT( 'P', wbpt_property_id ) ) as term_full_entity_id
FROM wbt_property_terms
    INNER JOIN wbt_term_in_lang ON wbpt_term_in_lang_id = wbtl_id
    INNER JOIN wbt_type         ON wbtl_type_id = wby_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
    wby_name       IN ( 'label', 'alias' )
    AND wbx_text LIKE 'hell%'
;

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 24 2019, 12:20 PM
Reedy renamed this task from How do queries in new schema look like? to What do queries in the new schema look like?.Apr 24 2019, 9:10 PM
Addshore updated the task description. (Show Details)Mar 23 2020, 3:14 PM
Addshore updated the task description. (Show Details)
This comment was removed by Mahir256.
Jony added a subscriber: Jony.Apr 1 2020, 6:52 PM