[android-developers] Re: Translation using dictionary offline

For that case you should use an SQLite database that contains a table for your words.
The words table stores the word itself, a language code / identifier, a serial unique id for each word and any additional information you need (like word type, definition text etc).

A minimum would look like:

CREATE TABLE words (
    id INTEGER PRIMARY KEY NOT NULL,
    word TEXT NOT NULL,
    langId INTEGER NOT NULL
);

Then create another "translation" table that links words of different languages as translations of each other. You can create such a table just by specifying a pair of word id numbers:

CREATE TABLE translations (
    word_1 INTEGER NOT NULL,
    word_2 INTEGER NOT NULL,
    UNIQUE(word_1, word_2)
);

You would fill the "words" table with all dictionary entries of both English and German. Then you would link all English words with their German counterparts using the "translations" table by specifying pairs of word id numbers.

For example:

Your words table contains entries like:

  ID  |       Word        |   LangId
-------------------------------------------------------
  1   |  cat              |    1 (= English)
  2   |  dog              |    1
  3   |  car              |    1
  .
  .
  .
 101  |  Katze            |    2 (= German)
 102  |  Hund             |    2
 103  |  Auto             |    2

Your "translations" linking table would look like as follows:

Word_1 | Word_2
----------------
  1    | 101
  2    | 102
  3    | 103

Since searching big database tables for text can be pretty slow your should consider using the FTS3 extension. That's a special SQLite extension that allows for fast text lookups. However things change a bit then. You cannot create an ID serial primary key in your words table if you use that extension. But that does not matter because FTS3 tables always have a "hidden" column called docId which does exactly the same as the ID serial primary key.

Your optimized FTS3 table would be created as follows:

CREATE VIRTUAL TABLE words USING FTS3 (
    word TEXT NOT NULL,
    langId INTEGER NOT NULL
);

In order to use the fast lookup capabilities of FTS3 you need to use the MATCH operator like in following example:

SELECT * FROM words WHERE word MATCH 'dog';

Result:

  docId  |       Word        |   LangId
-------------------------------------------------------
    2    |  dog              |    1


With the retrieved docId value you can query your translations table:

SELECT * FROM translations WHERE word_1 = 2 OR word_2 = 2;

Result:

Word_1 | Word_2
----------------
  2    | 102

You get docId 102 as a result. In order to look up the translated word for "dog" you need to look up the "words" table again:

SELECT * FROM words where docId = 102;

And the result would be:

  ID  |       Word        |   LangId
-------------------------------------------------------
 102  |  Hund             |    2


Look up topics like:

• Android Developers: Storage Options
• SQLite FTS3

Since dictionaries are usually rather big you should pre-create your SQLite database file. That means that you should use SQLite3 on your development PC and create the whole database with all its contents there. Then you would bundle the database file as an asset file with your Android app. On first start your app would copy the dictionary from the assets storage to the file system (private or external storage), then you can open that file using SQLiteDatabase.

On Thursday, January 24, 2013 2:23:00 AM UTC-6, sam jeck wrote:

i am dealing with a mobile application where it should perform a translation of words which works offline-(not online using any api -bing/google). how can we perform any translation of words (like german--->english). just like WORDLENS/any offline translator works.

thanks in advance

sam jeck.

--
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en