As an anonymous user, you can only add new data. If you would like to also modify existing data, please create an account and indicate your languages on your user page.

User:Kipcool/From DefinedMeaning to attributes

From OmegaWiki
Jump to: navigation, search

Attributes in the database[edit]

Starting from DefinedMeaning:bean (7772), we will show how to find the attributes that have been applied to one of its Spanish translation.

Let's start from DefinedMeaning:bean (7772)

The DM number, uw_defined_meaning.defined_meaning_id is 7772.

Starting with some basic queries[edit]

the list of expression_id for this DM is obtained by

SELECT expression_id FROM uw_syntrans WHERE defined_meaning_id=7772 ;
+---------------+
| expression_id |
+---------------+
|        136682 |
|        136683 |
|        136684 |
|        136685 |
|        136686 |
|        136687 |
|        136688 |
|        136689 |
|        136690 |
|        136691 |
|        136692 |
|        136693 |
|        136694 |
|        136695 |
|        136696 |
|        136697 |
|        136698 |
|        136740 |
|        136772 |
|        157491 |
|        335448 |
|        335450 |
|        354797 |
|        449129 |
|        525433 |
|        650018 |
|        852832 |
|        852834 |
|        852836 |
|        852841 |
|        852843 |
|        852845 |
|        772389 |
|        852848 |
|        852850 |
+---------------+

The corresponding translations in a readable form is obtained by

SELECT spelling, language_id, syntrans_sid FROM uw_expression, uw_syntrans
WHERE uw_syntrans.defined_meaning_id=7772 AND uw_syntrans.expression_id=uw_expression.expression_id ;
+------------------------+-------------+--------------+
| spelling               | language_id | syntrans_sid |
+------------------------+-------------+--------------+
| bean                   |          85 |       320598 |
| boon                   |          89 |       320599 |
| bønne                  |         103 |       320600 |
| fazole                 |          90 |       320601 |
| fabo                   |         113 |       320602 |
| papu                   |          99 |       320603 |
| שעועית                 |         106 |       320604 |
| боб                    |          88 |       320605 |
| біб                    |         116 |       320606 |
| فاصوليا            |         120 |       320607 |
| babarrun               |          96 |       320608 |
| 豆                     |         112 |       320609 |
| Bohne                  |         101 |       320610 |
| fazul’a                |          97 |       320611 |
| fižol                  |          92 |       320612 |
| feijão                 |          94 |       320613 |
| fasola                 |          93 |       320614 |
| bab                    |         102 |       320615 |
| haricot                |          86 |       320616 |
| böna                   |          91 |       320617 |
| Bůnn                   |         134 |       335455 |
| Bonn                   |         134 |       335456 |
| bôbovité               |          97 |       354798 |
| judía                  |          87 |       449130 |
| fagiolo                |         100 |       525434 |
| ლობიო                  |         124 |       650019 |
| alubia                 |          87 |       852833 |
| edible bean            |          85 |       852835 |
| poroto                 |          87 |       852837 |
| pocha                  |          87 |       852842 |
| faba                   |          87 |       852844 |
| frijól                 |          87 |       852846 |
| chícharo               |          87 |       852847 |
| caraota                |          87 |       852849 |
| habichuela             |          87 |       852851 |
+------------------------+-------------+--------------+

Let's select judía in Spanish (language_id=87), i.e. syntrans_sid = 449130.

uw_objects[edit]

We can do the following query, but it does not bring information in what we are doing now. It is useful when, starting from an attribute, we want to know in which table the object_id should be looked for.

SELECT * FROM uw_objects where uw_objects.object_id=449130 and uw_objects.table='uw_syntrans';
+-----------+-------------+-------------+--------------------------------------+
| object_id | table       | original_id | UUID                                 |
+-----------+-------------+-------------+--------------------------------------+
|    449130 | uw_syntrans |           0 | f66749dc-0cef-102a-a1d1-00137254351f |
+-----------+-------------+-------------+--------------------------------------+

Hyphenation[edit]

To get the hyphenation, or other simple attributes with 1 DM, 1 text, we have to look in uw_text_attribute_values.

SELECT * FROM uw_text_attribute_values WHERE object_id=449130 ;
+----------+-----------+---------------+------------+--------------------+-----------------------+
| value_id | object_id | attribute_mid | text       | add_transaction_id | remove_transaction_id |
+----------+-----------+---------------+------------+--------------------+-----------------------+
|   852838 |    449130 |        535379 | ju·dí·a    |             229520 |                  NULL |
+----------+-----------+---------------+------------+--------------------+-----------------------+

To know that it is hyphenation, the attribute_mid is in fact a DM id which corresponds to the DM of hyphenation DefinedMeaning:hyphenation (535379). This can be known directly by using the link DefinedMeaning:(535379) (it is red, but has content nevertheless) or with the following query :

SELECT spelling, language_id, syntrans_sid FROM uw_expression, uw_syntrans WHERE uw_syntrans.defined_meaning_id=535379 AND uw_syntrans.expression_id=uw_expression.expression_id;

Wikipedia links[edit]

To get the Wikipedia links, we look in uw_url_attribute_values. It is very similar to the hyphenations above

SELECT * FROM  uw_url_attribute_values WHERE object_id=449130 ;
+----------+-----------+---------------+-------------------------------------------------+--------------------+--------------------+-----------------------+
| value_id | object_id | attribute_mid | url                                             | label              | add_transaction_id | remove_transaction_id |
+----------+-----------+---------------+-------------------------------------------------+--------------------+--------------------+-----------------------+
|   852861 |    449130 |        740663 | http://es.wikipedia.org/wiki/Phaseolus_vulgaris | Phaseolus vulgaris |             229528 |                  NULL |
+----------+-----------+---------------+-------------------------------------------------+--------------------+--------------------+-----------------------+

Attribute_mid is DefinedMeaning:Wikipedia article (740663)

Part of speech and genus[edit]

To get part of speech and genus, i.e. attributes which links two DMs, we have to look in uw_option_attribute_values and uw_option_attribute_options.

SELECT value_id, object_id, uw_option_attribute_values.option_id, attribute_id, option_mid, language_id
FROM uw_option_attribute_values, uw_option_attribute_options
WHERE uw_option_attribute_values.object_id=449130
AND uw_option_attribute_options.option_id = uw_option_attribute_values.option_id ;
+----------+-----------+-----------+--------------+------------+-------------+
| value_id | object_id | option_id | attribute_id | option_mid | language_id |
+----------+-----------+-----------+--------------+------------+-------------+
|   449131 |    449130 |    410503 |       409106 |       5612 |          87 |
|   852852 |    449130 |    735991 |       735957 |     423228 |          87 |
+----------+-----------+-----------+--------------+------------+-------------+

uw_option_attribute_options.option_mid is a DM id.

To know what this attributes are, we have to look in uw_class_attributes where the uw_class_attributes.object_id correspond to the uw_option_attribute_options.attribute_id :

SELECT * FROM uw_class_attributes WHERE object_id in (409106, 735957) ;
+-----------+-----------+-----------+---------------+----------------+--------------------+-----------------------+
| object_id | class_mid | level_mid | attribute_mid | attribute_type | add_transaction_id | remove_transaction_id |
+-----------+-----------+-----------+---------------+----------------+--------------------+-----------------------+
|    409106 |    402295 |    401995 |        358760 | OPTN           |              50257 |                  NULL |
|    735957 |    402295 |    401995 |        423236 | OPTN           |             181513 |                  NULL |
+-----------+-----------+-----------+---------------+----------------+--------------------+-----------------------+