Android Translator App Using SQLite

  • Replies:6
  • OpenNot stickiedUnanswered

Nov 14, 2011 3:03:37 AM

Hi All, I'm hoping I can get some help/feedback on this, any contributions are appreciated.

I am currently trying to develop an app that will essentially take some text that a user has entered in 'SMS speak' and then search my SQLite3 database and return the same txt in proper English simply by matching and replacing any word for word entries that can be located. If there aren't any matches for certain words then the original SMS wording can remain untouched in the result.

I am developing this in eclipse with the Android SDK plugin and I have set my emulator to the latest version 4.0. I have an SQLite3 database of about 1500-2000 words and I have the adapter class in place (as shown below with template that I will be using as I believe this will suit my purpose when I have entered my own methods?)...

This is my first attempt at creating an Android App and I have been learning largely from youtube videos etc. I have formatted the SQLite database in the correct style for an Android App and it is stored in the assets folder correctly but I am stuck on how to actually implement the method for taking the users entry text and translating it in the way I have previously described. The GUI etc doesn't need to be very sophisticated for now I can deal with that when I have actually got the basics working.

My SQLite file looks like this

_id SMS English
1 *$ Starbucks
2 121 One to One
3 4eva Forever

Any help would be great, thanks!

1package com.edu4java.android;
2
3import java.io.FileOutputStream;
4import java.io.IOException;
5import java.io.InputStream;
6import java.io.OutputStream;
7import java.util.UUID;
8
9import android.content.Context;
10import android.database.Cursor;
11import android.database.SQLException;
12import android.database.sqlite.SQLiteDatabase;
13import android.database.sqlite.SQLiteException;
14import android.database.sqlite.SQLiteOpenHelper;
15import android.util.Log;
16
17public class AnyDBAdapter {
18
19 private static final String TAG = "AnyDBAdapter";
20 private DatabaseHelper mDbHelper;
21 private static SQLiteDatabase mDb;
22
23 //make sure this matches the
24 //package com.MyPackage;
25 //at the top of this file
26 private static String DB_PATH = "/data/data/com.edu4java.android/databases/";
27
28 //make sure this matches your database name in your assets folder
29 // my database file does not have an extension on it
30 // if yours does
31 // add the extention
32 private static final String DATABASE_NAME = "smsenglish.sqlite";
33
34 //Im using an sqlite3 database, I have no clue if this makes a difference or not
35 private static final int DATABASE_VERSION = 3;
36
37 private final Context adapterContext;
38
39 public AnyDBAdapter(Context context) {
40 this.adapterContext = context;
41 }
42
43 public AnyDBAdapter open() throws SQLException {
44 mDbHelper = new DatabaseHelper(adapterContext);
45
46 try {
47 mDbHelper.createDataBase();
48 } catch (IOException ioe) {
49 throw new Error("Unable to create database");
50 }
51
52 try {
53 mDbHelper.openDataBase();
54 } catch (SQLException sqle) {
55 throw sqle;
56 }
57 return this;
58 }
59 //Usage from outside
60 // AnyDBAdapter dba = new AnyDBAdapter(contextObject); //in my case contextObject is a Map
61 // dba.open();
62 // Cursor c = dba.ExampleSelect("Rawr!");
63 // contextObject.startManagingCursor(c);
64 // String s1 = "", s2 = "";
65 // if(c.moveToFirst())
66 // do {
67 // s1 = c.getString(0);
68 // s2 = c.getString(1);
69 // } while (c.moveToNext());
70 // dba.close();
71 public Cursor ExampleSelect(String myVariable)
72 {
73 String query = "SELECT locale, ? FROM android_metadata";
74 return mDb.rawQuery(query, new String[]{myVariable});
75 }
76
77 //Usage
78 // AnyDBAdatper dba = new AnyDBAdapter(contextObjecT);
79 // dba.open();
80 // dba.ExampleCommand("en-CA", "en-GB");
81 // dba.close();
82 public void ExampleCommand(String myVariable1, String myVariable2)
83 {
84 String command = "INSERT INTO android_metadata (locale) SELECT ? UNION ALL SELECT ?";
85 mDb.execSQL(command, new String[]{ myVariable1, myVariable2});
86 }
87
88 public void close() {
89 mDbHelper.close();
90 }
91
92 private static class DatabaseHelper extends SQLiteOpenHelper {
93
94 Context helperContext;
95
96 DatabaseHelper(Context context) {
97 super(context, DATABASE_NAME, null, DATABASE_VERSION);
98 helperContext = context;
99 }
100
101 @Override
102 public void onCreate(SQLiteDatabase db) {
103 }
104
105 @Override
106 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
107 Log.w(TAG, "Upgrading database!!!!!");
108 //db.execSQL("");
109 onCreate(db);
110 }
111
112 public void createDataBase() throws IOException {
113 boolean dbExist = checkDataBase();
114 if (dbExist) {
115 } else {
116
117 //make sure your database has this table already created in it
118 //this does not actually work here
119 /*
120 * db.execSQL("CREATE TABLE IF NOT EXISTS \"android_metadata\" (\"locale\" TEXT DEFAULT 'en_US')"
121 * );
122 * db.execSQL("INSERT INTO \"android_metadata\" VALUES ('en_US')"
123 * );
124 */
125 this.getReadableDatabase();
126 try {
127 copyDataBase();
128 } catch (IOException e) {
129 throw new Error("Error copying database");
130 }
131 }
132 }
133
134 public SQLiteDatabase getDatabase() {
135 String myPath = DB_PATH + DATABASE_NAME;
136 return SQLiteDatabase.openDatabase(myPath, null,
137 SQLiteDatabase.OPEN_READONLY);
138 }
139
140 private boolean checkDataBase() {
141 SQLiteDatabase checkDB = null;
142 try {
143 String myPath = DB_PATH + DATABASE_NAME;
144 checkDB = SQLiteDatabase.openDatabase(myPath, null,
145 SQLiteDatabase.OPEN_READONLY);
146 } catch (SQLiteException e) {
147 }
148 if (checkDB != null) {
149 checkDB.close();
150 }
151 return checkDB != null ? true : false;
152 }
153
154 private void copyDataBase() throws IOException {
155
156 // Open your local db as the input stream
157 InputStream myInput = helperContext.getAssets().open(DATABASE_NAME);
158
159 // Path to the just created empty db
160 String outFileName = DB_PATH + DATABASE_NAME;
161
162 // Open the empty db as the output stream
163 OutputStream myOutput = new FileOutputStream(outFileName);
164
165 // transfer bytes from the inputfile to the outputfile
166 byte[] buffer = new byte[1024];
167 int length;
168 while ((length = myInput.read(buffer)) > 0) {
169 myOutput.write(buffer, 0, length);
170 }
171
172 // Close the streams
173 myOutput.flush();
174 myOutput.close();
175 myInput.close();
176 }
177
178 public void openDataBase() throws SQLException {
179 // Open the database
180 String myPath = DB_PATH + DATABASE_NAME;
181 mDb = SQLiteDatabase.openDatabase(myPath, null,
182 SQLiteDatabase.OPEN_READWRITE);
183 }
184
185 @Override
186 public synchronized void close() {
187
188 if (mDb != null)
189 mDb.close();
190
191 super.close();
192
193 }
194 }
195
196}

Nov 14, 2011 3:42:32 AM

This code below is for an iPhone app but it seems to be the same sort of thing i'm going for... i.e 1) take the string from the input field, 2) apply componentsSeparatedByString: to it to have it broken into an array, then, 3) take each item in the array, search my sqlite DB for it, return the equivalent word, and append that to a string (and a space)... 4) when i'm done with the array, output the resulting string to the 'output' box.

I don't know how to apply this to my Android app..

1-(void) viewWillAppear:(BOOL)animated{
2[self createEditableCopyOfDatabaseIfNeeded];
3}
4
5-(sqlite3 *) getNewDBConnection{
6 sqlite3 *newDBconnection;
7 NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
8 NSString *documentsDirectory = [paths objectAtIndex:0];
9 NSString *path = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
10 // Open the database. The database was prepared outside the application.
11 if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) {
12 NSLog(@"Database Successfully Opened");
13 } else {
14 NSLog(@"Error in opening database");
15 }
16 return newDBconnection;
17 }
18
19-(void)translate{
20 //take input and break into an array
21 NSString *clearText = [[NSString alloc] init];
22 clearText=inputBox.text;
23 NSArray *words = [[NSArray alloc] init];
24 words= [clearText componentsSeparatedByString:@" "];
25 numOfWords=words.count;
26 NSString *newText=@"";
27 //open database
28 sqlite3 *db = [self getNewDBConnection];
29 //loop through array
30 for(i=0;i<numOfWords;i++){
31 sqlite3_stmt *resultStatement = nil;
32 NSString *res = [NSString stringWithFormat:@"select * from dictionary where plain='%@'",[[words objectAtIndex:i] stringByTrimmingCharactersInSet:whitespaceCharacterSet]];
33 if((sqlite3_prepare_v2(db, [res UTF8String], -1, &resultStatement, nil))!=SQLITE_OK){
34 NSLog(@"Error getting result, maybe word not found\n");
35 NSLog(@"error: %s", sqlite3_errmsg(db));
36 }
37 else{
38 if(sqlite3_step(resultStatement)==SQLITE_ROW){
39 //in the line below, 1 is the column number of the replacement word
40 NSString *add = [[NSString alloc] initWithUTF8String: (char*)sqlite3_column_text(resultStatement,1)]
41 newText=[newText stringByAppendingString:add];
42 [add release];
43 }
44 }
45 sqlite3_finalize(resultStatement);
46 }
47 //output result
48 outputBox.text=newText;
49 sqlite3_close(db);
50 }
51
52-(void)createEditableCopyOfDatabaseIfNeeded {
53
54 // First, test for existence.
55 BOOL success;
56 NSFileManager *fileManager = [NSFileManager defaultManager];
57 NSError *error;
58 NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
59 NSString *documentsDirectory = [paths objectAtIndex:0];
60 NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
61 success = [fileManager fileExistsAtPath:writableDBPath];
62 if (success) return;
63 // The writable database does not exist, so copy the default to the appropriate location.
64 //NSLog(@"Creating editable copy of database");
65 NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"data.sqlite"];
66 success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
67 if (!success) {
68 NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
69 }
70}

Nov 24, 2011 4:38:00 AM

You can use the split method in Java's String class to get an array of words separated by space.

Then iterate over each element of the array and look it up in the database. If there is a match use the translation found in the database.

If you are new to coding, try one thing at a time. For example, try looking up the database separately, then try splitting strings and playing with arrays and strings, etc. Then combine all that knowledge together.

Nov 24, 2011 10:35:08 AM

Hi Harshad,

Thanks very much for your suggestions (and Aaron for moving me to the correct forum section). I'll be working on this all day so if and undoubtedly when I get stuck i'll post and update. I'll get straight on to looking at the split method.

Thanks!

Nov 10, 2012 8:06:50 PM

Hi, Jon Snow, may i ask you where the youtube or tutorial to do an android translator??? It is because i want to do a translator which can be using in offline platform for my final year project... So, i decided to do the android translator by using sqlite but i dunno how to implement it??? So, can you tell me how to do it or how to implement it???...Thank you very much....