Uby SQL for word definition

[Daniela Marcu]

I have downloaded UBY SQL dump and installed it locally on my computer.

I need an SQL query that would result in the following: word, language, partofspeech, phonetic, definition.

Could anyone provide such a query for MySQL?

Thank you in advance,

Daniela

[Peter Schüller]

Dear Daniela,

I try to provide some solution below.

Disclaimer: I am just a user of UBY, so this answer might not be the best way to do it.

Definitions are attached to semantic things like Synset, Sense, SemanticPredicate and SemanticArgument, therefore the query below also includes the sense of the definition, and I added a "index" column which gives a running index over the senses of that written form.

Then you can get everything except phonetic form as follows:

SELECT frl.writtenForm, frl.languageIdentifier, le.partOfSpeech, se._index, trd.writtenText
FROM FormRepresentation_Lemma frl
JOIN LexicalEntry le ON (frl.lemmaId = le.lemmaId)
JOIN Sense se ON (le.lexicalEntryId = se.lexicalEntryId)
JOIN Definition de ON (se.senseId = de.senseId)
JOIN TextRepresentation_Definition trd ON (de.definitionId = trd.definitionId)

WHERE <your condition>

LIMIT <your limit>

If you also want the phonetic form you need to join via WordForm and there might be multiple word forms for a LexicalEntry,

Therefore the following query below returns word form index (wf.idx) and then the phonetic form of that word form, and also sense index (se._index) and the definition of that sense (trd.writtenText)

So you get two dimensions of indices for each writtenForm.

SELECT frl.writtenForm, frl.languageIdentifier, le.partOfSpeech, wf.idx, frw.phoneticForm, se._index, trd.writtenText
FROM FormRepresentation_Lemma frl
JOIN LexicalEntry le ON (frl.lemmaId = le.lemmaId)
JOIN WordForm wf ON (le.lexicalEntryId = wf.lexicalEntryId)
JOIN FormRepresentation_WordForm frw ON (frw.wordFormId = wf.wordFormId)
JOIN Sense se ON (le.lexicalEntryId = se.lexicalEntryId)
JOIN Definition de ON (se.senseId = de.senseId)
JOIN TextRepresentation_Definition trd ON (de.definitionId = trd.definitionId)

WHERE <your condition>

LIMIT <your limit>

Best Regards,
Peter

--
You received this message because you are subscribed to the Google Groups "UBY Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to uby-users+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

http://www.peterschueller.com/

[Daniela Marcu]

Dear Peter,

Thank you so much for your time. I'll test it right away.

Kind regards,

Daniela

[Christian M. Meyer]

Dear Daniela,

one way is using direct SQL queries, e.g., the two excellent ones Peter suggested earlier.

Another option is to access the UBY database in a Java program. To do that, create a Java project following https://code.google.com/p/dkpro-tutorials/wiki/UbyTutorial2013 and use, for example, the following code:
       
        Uby uby = new Uby(db);
        Iterator<LexicalEntry> leIter = uby.getLexicalEntryIterator(null);
        while (leIter.hasNext()) {
            LexicalEntry le = leIter.next();
            System.out.println("lemma: " + le.getLemmaForm());
            System.out.println("language: " + le.getLexicon().getLanguageIdentifier());
            System.out.println("pos: " + le.getPartOfSpeech());
            for (WordForm wf : le.getWordForms()) {
                FormRepresentation fr = wf.getFormRepresentations().get(0);
                String phoneticForm = fr.getPhoneticForm();
                if (phoneticForm != null && !phoneticForm.isEmpty())
                    System.out.println("phonetic: " + fr.getPhoneticForm());
            }
            for (Sense s : le.getSenses())
                System.out.println("definition: " + s.getDefinitionText());
        }

Depending on your further analysis, the Java version might offer you more flexibility whereas the direct SQL query of course does not have the setup and programming overhead.

Best wishes,
Christian

[Daniela Marcu]

Dear Christian,

Thank you very much for your example. It works really great!

Regards,
Daniela