What do queries in the new schema look like?
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	• alaa_wmde
	Apr 24 2019, 12:20 PM

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%'
;