Pronoid Android

So a commenter on Android Market suggested I allow users store my app data on their SD card rather than phone memory. Great idea, I thought, and promptly went Googling to look for some example code. Alas, there wasn't any. So I wrote my own; and here it is for those who find themselves in a similar situation.

The basic Android model is for all Activity classes to have an associated DBHelper class. I don't like this at all; you waste all kinds of time writing and instantiating and opening and closing your DBHelpers, for the sake of (in my app) maybe twenty different database calls. So instead I went and created a singleton DB object. Here's the static code:

public class DB {
 private static DB instance;
    SQLiteDatabase mDB;
    DatabaseHelper mDbHelper;
    final Context mCtx;

    public static DB GetFor(Context context) {
        if (instance==null)
            instance = new DB(context);
        if (!instance.isOpen())
            instance.open();
        return instance;
    }
    
    public static void Close() {
         if (instance!=null && instance.isOpen())
             instance.close();
        instance=null;
    }

    /**
     * Database creation sql statement
     */
    static final String MESSAGE_CREATE =
        "create table Message (_id integer primary key autoincrement, "
   + "content text not null, dateCreated datetime not null, dateViewed datetime, "
   + "messageTitle text, messageType integer, sender text, recipient text); ";

//  [...other table definitions go here...]

    static final String DATABASE_NAME = "iTravel";
    static final int DATABASE_VERSION = 1;

(I call DB.Close() in the onDestroy() method of the app's Activities. Now, if you had multiple threads writing to the database simultaneously, this approach would probably get pretty messy in a hurry; fortunately, I don't.)

The Android SDK also includes a SQLiteOpenHelper object, which basically takes a Context object and returns a writeable database. I was a little irritated by the need to pass in a Context - it means you have a Context on hand to access the database at all - but presumed it was just necessary for some mysterious reason.

Not so mysterious at all. The bad news is, if you want your database to live on your phone's SD card, you can't use the SQLiteOpenHelper. The good news is, it's easy to write an SD-card-compatible variant, one that doesn't require any Context at all:

     static class DatabaseHelper{
     private SQLiteDatabase db;
     private Context mCtx;

     private DatabaseHelper(Context context) {
        mCtx=context;
     }

     public void open() {
         File dbDir=null, dbFile=null;
         if (Settings.DoSDDB() && Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {
             dbDir = Environment.getExternalStorageDirectory();
             dbFile = new File(dbDir, "iTravel.sqlite");
         }
         else
             dbFile = mCtx.getDatabasePath("iTravel");

         if (dbFile.exists()) {
             Log.i("SQLiteHelper", "Opening database at "+dbFile);
             db = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
             if (DATABASE_VERSION > db.getVersion())
                 upgrade();
          }
          else {
              Log.i("SQLiteHelper", "Creating database at "+dbFile);
              db = SQLiteDatabase.openOrCreateDatabase(dbFile, null);
              create();
          }         
     }
        
     public void close() {
         db.close();
     }

     public void create() {
         Log.w(""+this, "Creating Database "+db.getPath());
         db.execSQL(MESSAGE_CREATE);
//          ...other tables go here
         db.setVersion(DATABASE_VERSION);
     }

     public void upgrade() {
         Log.w(""+this, "Upgrading database "+db.getPath() +" from version " + db.getVersion() + " to "
                + DATABASE_VERSION + ", which will destroy all old data");
         db.execSQL("DROP TABLE IF EXISTS Message");
//       ...other tables go here
         create();
     }

     public SQLiteDatabase getWritableDatabase() {
        if (db==null)
            open();
        return db;
     }

Turns out the only thing we ever needed that Context for was its "getDatabasePath()" method, as that varies by application; but if you're using the SD card, you don't need that at all. (The above implementation supports both SD-card and phone-memory databases. Just in case.)

So what happens in the DB object's instance(s)? Easy enough -

    public DB(Context ctx) {
        this.mCtx = ctx;     
    }

    public DB open() throws SQLException {
        if (mDbHelper==null)
            mDbHelper = new DatabaseHelper(mCtx);
        if (mDB==null || !mDB.isOpen())
            mDB = mDbHelper.getWritableDatabase();
        return this;
    }

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

    public boolean isOpen() {
        return mDB!=null && mDB.isOpen();
    }

    public boolean deleteAll() {
        mDB.delete("Message", null, null);
//      ...other tables go here
        return true;
    }

    public Message[] fetchMessages(String where, String[] args) throws SQLException {
        Cursor cursor =  mDB.query(true, "Message",
          new String[] {"_id", "messageTitle", "content" },
          where, args, null, null, "dateCreated desc", null);

        Message[] messages = new Message[cursor.getCount()];
        cursor.moveToFirst();
        int i=0;
        while (!cursor.isAfterLast()) {
            messages[i++] = new Message(cursor.getString(1), cursor.getString(2));
            cursor.moveToNext();
        }
        cursor.close();
        return messages;
    }
     
    public void saveMessage(Message message) {
        ContentValues values = new ContentValues();
        values.put("messageTitle", message.getTitle());
        values.put("content", message.getData());
        values.put("messageType", message.getMessageType());
        values.put("dateCreated", System.currentTimeMillis());
        mDB.insert("Message", null, values);
    }

et voila: all your database access in one place, accessed by a simple DB.GetFor(Context context) call from anywhere in your app (and you don't even need the Context if you know the DB will live on the SD card!) and nicely abstracting out the crazy ten-positional-arguments-in-a-row "query" method on SQLiteDatabase.

Labels: Android, card, Context, DB, DBHelper, getDatabasePath, openOrCreateDatabase, SD, SDK, SQLite, SQLiteDatabase, SQLiteOpenHelper