Lots of time you have an already created and populated sqlite database file that you want to use with your android application. SQLiteOpenHelper provides some useful functionality that we don't want to loose. So, we are going to modify the behavior of SQLiteOpenHelper to do the job.

What we need..

  1. copy the database file from assets on first run.
  2. in case we upgraded the database, we need to copy the new database file.
  3. we need to let the SQLiteOpenHelper do it's job seamlessly.

Down to the Code

First we write our class Repository that extends SQLiteOpenHelper.

public class Repository extends SQLiteOpenHelper {  
  private static final int VERSION = 1;
  private static final String DATABASE_NAME = "data.sqlite";
  private static final File DATABASE_FILE;

  // This is an indicator if we need to copy the
  // database file.
  private boolean mInvalidDatabaseFile = false;
  private Context mContext;

  private Repository(Context context) {
    super(context, DATABASE_NAME, null, VERSION);
    this.mContext = context;
    // This will set the DATABASE_FILE to the path used by
    // SQLiteOpenHelper when it creates database files.
    DATABASE_FILE = context.getDatabasePath(DATABASE_NAME);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {

  }

  @Override
  public void onUpgrade(SQLiteDatabase database,
    int old_version, int new_version) {

  }
}

We, now, have the skeleton of our class.
SQLiteOpenHelper creates (or upgrades) the database upon the first call to getReadableDatabase(). In this method, if it didn't find the database file it calls onCreate(). If it finds that the database needs to be upgraded (more on that later), it calls onUpgrade(). So, it makes sense to write our code that copies the database file in onCreate and onUpgrade. BUT, THIS WILL FAIL!

Under the Hood

when you call getReadableDatabase(), SQLiteOpenHelper checks if the database file exists. If it does not exist, it:

  1. creates the file.
  2. opens a transaction
  3. calls your onCreate() method
  4. closes the transaction
  5. returns an opened database connection

So, if we copied our database file in onCreate we are overriding an already created file that SQLiteOpenHelper is already using to open a connection and returning that connection. This will miss up it's workflow.

What we will do is ..

let SQLiteOpenHelper do it's checking as normal. We will only set the nInvalidDatabaseFile flag in our implementation of onCreate and onUpgrade.

@Override
public void onCreate(SQLiteDatabase db) {  
  mInvalidDatabaseFile = true;
}

@Override
public void onUpgrade(SQLiteDatabase database,  
    int old_version, int new_version) {
  mInvalidDatabaseFile = true;
}

Then in our constructor, we will append the following:

SQLiteDatabase db = null;  
try {  
  db = getReadableDatabase();
  if (db != null) {
    db.close();
  }
  if (mInvalidDatabaseFile) {
    copyDatabase();
  }
} catch (SQLiteException e) {
} finally {
  if (db != null && db.isOpen()) {
    db.close();
  }
}

First we call getReadableDatabase() to let SQLiteOpenHelper do it's checking and setting our flag as appropriate and then close the database connection. Then we check our flag, mInvalidDatabaseFile flag. if set, we are clear to copy over our database file.

Copying the database file

Now the copyDatabase() method.

public void copyDatabase() {  
  AssetManager assetManager = mContext.getResources().getAssets();
  InputStream in = null;
  OutputStream out = null;
  try {
    in = assetManager.open(DATABASE_NAME);
    out = new FileOutputStream(DATABASE_FILE);
    byte[] buffer = new byte[1024];
    int read = 0;
    while ((read = in.read(buffer)) != -1) {
      out.write(buffer, 0, read);
    }
  } catch (IOException e) {
  } finally {
    if (in != null) {
      try {
        in.close();
      } catch (IOException e) {
      }
    }
    if (out != null) {
      try {
        out.close();
      } catch (IOException e) {
      }
    }
  }
  setDatabaseVersion();
  mInvalidDatabaseFile = false;
}

Nothing new here. Just copying a file from the assets. The only trick here is the setDatabaseVersion() call.

Database Versioning

SQLiteOpenHelper sets the database version to the number you pass to it's constructor. We assigned our desired version to the VERSION member variable. SQLite has a PRAGMA called user_version which SQLiteOpenHelper uses to save the database version. So we need to do the same. SQLiteOpenHelper relies on this PRAGMA user_version value to check if the database needs to be upgraded or not.
Thus:

private void setDatabaseVersion() {  
  SQLiteDatabase db = null;
  try {
    db = SQLiteDatabase.openDatabase(DATABASE_FILE.getAbsolutePath(), null,
        SQLiteDatabase.OPEN_READWRITE);
    db.execSQL("PRAGMA user_version = " + VERSION);
  } catch (SQLiteException e ) {
  } finally {
    if (db != null && db.isOpen()) {
      db.close();
    }
  }
}

Now, we can use our Repository safely.

To Use it ...

Just use it normally as you would use an ordinary SQLiteOpenHelper object.

Repository repo = Repository.getInstance( context );  
SQLiteDatabase db = repo.getWritableDatabase();  
...

Here the complete code: