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.

Help:Option Attribute Values table

From OmegaWiki
Jump to: navigation, search
Help:Index Database layout Option Attribute Values table



The Option Attribute Values table gives the values of the option attributes defined in the Class Attributes table, where the value is one from a list of possible values defined in the Option Attribute Options table.

+-----------------------+---------+------+-----+---------+-------+
| Field                 | Type    | Null | Key | Default | Extra |
+-----------------------+---------+------+-----+---------+-------+
| value_id              | int(11) | NO   |     | 0       |       | 
| object_id             | int(11) | NO   |     | 0       |       | 
| option_id             | int(11) | NO   |     | 0       |       | 
| add_transaction_id    | int(11) | NO   | MUL | 0       |       | 
| remove_transaction_id | int(11) | YES  | MUL | NULL    |       | 
+-----------------------+---------+------+-----+---------+-------+

Fields[edit]

value_id 
An id that identifies the current table entry.
object_id 
A link to an object to which the annotation is associated. Can be a syntrans_sid, defined_meaning_id, etc. Cf. Objects table
option_id 
A link to an option_id from the Option Attribute Options table, which identifies both the attribute (e.g. "part of speech") and the selected value (e.g. "noun").
add_transaction_id 
Indicates when and by who the syntrans was added. See Transactions table.
remove_transaction_id 
Indicates when and by who the syntrans was removed. NULL if the syntrans is still valid.

Sample MySQL queries[edit]

Retrieving the part of speech of a word[edit]

1. Let's imagine that we want the different part of speeches of the word "round" in English. So, when querying the expression table:

  • spelling = round
  • language = 85

If you just want to run the query, without understanding it, you can go directly to step 8. Otherwise, read below for some step-by-step explanations

The corresponding expression_id is obtained by

  • select expression_id from uw_expression where spelling = 'round' and language_id = 85 and remove_transaction_id is null;

which returns:

  • expression_id = 123749


2. First we need to know the DM id of "part of speech". Part of speech is defined at the page DefinedMeaning:part_of_speech_(358760), so

  • DMid = 358760. (for part of speech)


3. We need to get the corresponding attribute_id in the Option Attribute Options table. This is done through the Class Attributes table.

  • select object_id, class_mid from uw_class_attributes where attribute_mid = 358760 and remove_transaction_id is null;

It returns many object_id, because there is one for each language. If you are interested only in one language, let's say English, and you know that the DM of English is 5685 (DefinedMeaning:English_(5685)), then you can restrict it

  • select object_id from uw_class_attributes where attribute_mid = 358760 and class_mid = 5685 and remove_transaction_id is null;

it returns


4. "part of speech" is a syntrans-level annotation. So we need the syntrans(es) corresponding to the expression for which we want the part of speech:

  • select syntrans_sid from uw_syntrans where expression_id = 123749 and remove_transaction_id is null;

which returns:

+--------------+
| syntrans_sid |
+--------------+
|       307404 |
|       314611 |
|       314614 |
|       314618 |
|       854302 |
|       700107 |
|      1092567 |
+--------------+


5. The "syntrans_sid" corresponds to a "object_id" in the uw_option_attribute_values table. So, to retrieve all the option-type syntrans-level annotations of the first syntrans (307404), it would be:

  • select * from uw_option_attribute_values as oav join uw_syntrans as synt on oav.object_id = synt.syntrans_sid where syntrans_sid = 307404 and oav.remove_transaction_id is null and synt.remove_transaction_id is null;


6. To restrict to part of speeches, we need to use the attribute_id retrieved at step 2. The part of speech itself is given by oao.option_mid:

  • select oao.option_mid from uw_option_attribute_values as oav join uw_syntrans as synt on oav.object_id = synt.syntrans_sid join uw_option_attribute_options as oao on oao.option_id = oav.option_id where syntrans_sid = 307404 and oao.attribute_id = 1348853 and oav.remove_transaction_id is null and oao.remove_transaction_id is null and synt.remove_transaction_id is null;

What it returns is in fact a DM_id. In this case, it returns 6102 which is the defined_meaning for adjective (DefinedMeaning:adjective_(6102))


7. But we can have it return an English friendly text instead we can write it without the "IN" - we do that at step 7. :):

  • select spelling as pos from uw_expression as exppos

join uw_syntrans as syntpos on exppos.expression_id = syntpos.expression_id where exppos.language_id = 85 and exppos.remove_transaction_id is null and syntpos.remove_transaction_id is null and syntpos.defined_meaning_id IN ( select oao.option_mid from uw_option_attribute_values as oav join uw_syntrans as synt on oav.object_id = synt.syntrans_sid join uw_option_attribute_options as oao on oao.option_id = oav.option_id where syntrans_sid = 307404 and oao.attribute_id = 1348853 and oav.remove_transaction_id is null and oao.remove_transaction_id is null and synt.remove_transaction_id is null );


8. The all-in-one query

So far, we selected only one syntrans_sid from the list returned as step 4. If we want for all definitions of 'round' in 'English' (cf. step 1), the query is:

select exp.spelling as spelling, exppos.language_id as lang_id, synt.defined_meaning_id as DMid, exppos.spelling as pos from uw_expression as exppos join uw_syntrans as syntpos on exppos.expression_id = syntpos.expression_id join uw_option_attribute_options as oao on oao.option_mid = syntpos.defined_meaning_id join uw_option_attribute_values as oav on oao.option_id = oav.option_id join uw_syntrans as synt on oav.object_id = synt.syntrans_sid join uw_expression as exp on synt.expression_id = exp.expression_id where exp.spelling = 'round' and exp.language_id = 85 and oao.attribute_id = 1348853 and exppos.language_id = 85 and exppos.remove_transaction_id is null and syntpos.remove_transaction_id is null and oav.remove_transaction_id is null and oao.remove_transaction_id is null and exp.remove_transaction_id is null and synt.remove_transaction_id is null group by synt.defined_meaning_id

It returns:

+----------+---------+---------+-----------+
| spelling | lang_id | DMid    | pos       |
+----------+---------+---------+-----------+
| round    |      85 |    5984 | adjective |
| round    |      85 |    6786 | noun      |
| round    |      85 |    6787 | noun      |
| round    |      85 |    6788 | noun      |
| round    |      85 |  436135 | verb      |
| round    |      85 |  700103 | adjective |
| round    |      85 | 1092351 | noun      |
+----------+---------+---------+-----------+
  • note: here, the "attribute_id = 1348853" is restricted to part of speeches of English words (cf. step 3.) You can either adapt that number to the language that you are interested in (as explained in step 3.), or integrate the query of step 3. in that already long query.