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%'
;
```
# Real-Life query samples
TBD