Android Translator App Using SQLite

[Jon Snow]

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!


package com.edu4java.android;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.UUID;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class AnyDBAdapter {

private static final String TAG = "AnyDBAdapter";
private DatabaseHelper mDbHelper;
private static SQLiteDatabase mDb;

//make sure this matches the
//package com.MyPackage;
//at the top of this file
private static String DB_PATH = "/data/data/com.edu4java.android/databases/";

//make sure this matches your database name in your assets folder
// my database file does not have an extension on it
// if yours does
// add the extention
private static final String DATABASE_NAME = "smsenglish.sqlite";

//Im using an sqlite3 database, I have no clue if this makes a difference or not
private static final int DATABASE_VERSION = 3;

private final Context adapterContext;

public AnyDBAdapter(Context context) {
this.adapterContext = context;
}

public AnyDBAdapter open() throws SQLException {
mDbHelper = new DatabaseHelper(adapterContext);

try {
mDbHelper.createDataBase();
} catch (IOException ioe) {
throw new Error("Unable to create database");
}

try {
mDbHelper.openDataBase();
} catch (SQLException sqle) {
throw sqle;
}
return this;
}
//Usage from outside
// AnyDBAdapter dba = new AnyDBAdapter(contextObject); //in my case contextObject is a Map
// dba.open();
// Cursor c = dba.ExampleSelect("Rawr!");
// contextObject.startManagingCursor(c);
// String s1 = "", s2 = "";
// if(c.moveToFirst())
// do {
// s1 = c.getString(0);
// s2 = c.getString(1);
// } while (c.moveToNext());
// dba.close();
public Cursor ExampleSelect(String myVariable)
{
String query = "SELECT locale, ? FROM android_metadata";
return mDb.rawQuery(query, new String[]{myVariable});
}

//Usage
// AnyDBAdatper dba = new AnyDBAdapter(contextObjecT);
// dba.open();
// dba.ExampleCommand("en-CA", "en-GB");
// dba.close();
public void ExampleCommand(String myVariable1, String myVariable2)
{
String command = "INSERT INTO android_metadata (locale) SELECT ? UNION ALL SELECT ?";
mDb.execSQL(command, new String[]{ myVariable1, myVariable2});
}

public void close() {
mDbHelper.close();
}

private static class DatabaseHelper extends SQLiteOpenHelper {

Context helperContext;

DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
helperContext = context;
}

@Override
public void onCreate(SQLiteDatabase db) {
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(TAG, "Upgrading database!!!!!");
//db.execSQL("");
onCreate(db);
}

public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();
if (dbExist) {
} else {

//make sure your database has this table already created in it
//this does not actually work here
/*
* db.execSQL("CREATE TABLE IF NOT EXISTS \"android_metadata\" (\"locale\" TEXT DEFAULT 'en_US')"
* );
* db.execSQL("INSERT INTO \"android_metadata\" VALUES ('en_US')"
* );
*/
this.getReadableDatabase();
try {
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}

public SQLiteDatabase getDatabase() {
String myPath = DB_PATH + DATABASE_NAME;
return SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READONLY);
}

private boolean checkDataBase() {
SQLiteDatabase checkDB = null;
try {
String myPath = DB_PATH + DATABASE_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {
}
if (checkDB != null) {
checkDB.close();
}
return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException {

// Open your local db as the input stream
InputStream myInput = helperContext.getAssets().open(DATABASE_NAME);

// Path to the just created empty db
String outFileName = DB_PATH + DATABASE_NAME;

// Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);

// transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}

// Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}

public void openDataBase() throws SQLException {
// Open the database
String myPath = DB_PATH + DATABASE_NAME;
mDb = SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READWRITE);
}

@Override
public synchronized void close() {

if (mDb != null)
mDb.close();

super.close();

}
}

}

[Jon Snow]

P.S I would have put the code in the CODE tags but the alignment went crazy so it looks a lot clearer left as part of the text :)

[Jon Snow]

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...


-(void) viewWillAppear:(BOOL)animated{
[self createEditableCopyOfDatabaseIfNeeded];
}

-(sqlite3 *) getNewDBConnection{
sqlite3 *newDBconnection;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDire ctory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
// Open the database. The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) {
NSLog(@"Database Successfully Opened");
} else {
NSLog(@"Error in opening database");
}
return newDBconnection;
}

-(void)translate{
//take input and break into an array
NSString *clearText = [[NSString alloc] init];
clearText=inputBox.text;
NSArray *words = [[NSArray alloc] init];
words= [clearText componentsSeparatedByString:@" "];
numOfWords=words.count;
NSString *newText=@"";
//open database
sqlite3 *db = [self getNewDBConnection];
//loop through array
for(i=0;i<numOfWords;i++){
sqlite3_stmt *resultStatement = nil;
NSString *res = [NSString stringWithFormat:@"select * from dictionary where plain='%@'",[[words objectAtIndex:i] stringByTrimmingCharactersInSet:whitespaceCharacte rSet]];
if((sqlite3_prepare_v2(db, [res UTF8String], -1, &resultStatement, nil))!=SQLITE_OK){
NSLog(@"Error getting result, maybe word not found\n");
NSLog(@"error: %s", sqlite3_errmsg(db));
}
else{
if(sqlite3_step(resultStatement)==SQLITE_ROW){
//in the line below, 1 is the column number of the replacement word
NSString *add = [[NSString alloc] initWithUTF8String: (char*)sqlite3_column_text(resultStatement,1)]
newText=[newText stringByAppendingString:add];
[add release];
}
}
sqlite3_finalize(resultStatement);
}
//output result
outputBox.text=newText;
sqlite3_close(db);
}

-(void)createEditableCopyOfDatabaseIfNeeded {

// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDire ctory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success) return;
// The writable database does not exist, so copy the default to the appropriate location.
//NSLog(@"Creating editable copy of database");
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"data.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}