Using static Database with Android

While working with android , I wanted to use static SQLite DB . I mean a DB that is created earlier on a PC and then copied to the android and while first launch of the application .

For that , we require a SQLite Database Browser which will help in creating a android compatible SqlLite database . For converting some data to SQLite Database you can use different api's available for different languages , for example I used this SQLite api for C# . All that is required to keep in mind is that each of your table should have a column called "_id" which is a Number and which has unique values . 

Ok , so now lets consider that you got a SQLite Database , to use it for android you need to create a new table called "android_metadata" and it should have a column locale which is a TEXT field , as shown in the image below : 

once you have done this , add a field in your locale  as en_US as shown below  : 

Once this is done , your database is ready to be copied into the android database , easy part is over , now comes the tough part , ie copying it to the android database .

For that , take the SQLite file and put it into a assets folder of your project directory . Now , we need a SqlLiteOpenHelper class which will help us in copying the database into the android .

For this , we define a DatabaseHelper class which extends SQLiteOpenHelper as shown below :

package com.tapan.sqlite;

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

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



public class DataBaseHelper extends SQLiteOpenHelper implements Runnable
{

    //The Android's default system path of your application database.
    //DB_PATH should coincide with the [package name]/databases 
 private static String DB_PATH = "/data/data/com.tapan.sqlite/databases/";
    //DB_NAME should be the name of SQLite file
 private static String DB_NAME = "externalcontacts";
    private SQLiteDatabase myDataBase; 
    private final Context myContext;
    public int progress;
    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context 
     */
    public DataBaseHelper(Context context) {

     super(context, DB_NAME, null, 1);
     this.myContext = context;
    } 

  /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{
     //Log.d("Example 1 ","Inside the create DB method ");
     boolean dbExist = checkDataBase();

     if(dbExist){
      //do nothing - database already exist
     }else{

      //By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
         this.getReadableDatabase();

         try {

       copyDataBase();

      } catch (IOException e) {

          throw new Error("Error copying database");

         }
     }

    }

    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase(){

     SQLiteDatabase checkDB = null;

     try{
      String myPath = DB_PATH + DB_NAME;
      checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

     }catch(SQLiteException e){

      //database does't exist yet.

     }

     if(checkDB != null){

      checkDB.close();

     }

     return checkDB != null ? true : false;
    }

    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException{

     //Log.d("Example 1","Trying to copy the new Db");
     
     //Open your local db as the input stream
     InputStream myInput = myContext.getAssets().open(DB_NAME);

     // Path to the just created empty db
     String outFileName = DB_PATH + DB_NAME;
     int total_size=myInput.available();
     total_size=total_size/1024;
     //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);
      progress=(total_size-1)*100/total_size;
     }

     //Close the streams
     myOutput.flush();
     myOutput.close();
     myInput.close();
     progress=100;

    }

    public void openDataBase() throws SQLException{

     //Open the database
        String myPath = DB_PATH + DB_NAME;
     myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }

    @Override
 public synchronized void close() {

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

         super.close();

 }

 @Override
 public void onCreate(SQLiteDatabase db) {

 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

 }

 @Override
 public void run() {
  // TODO Auto-generated method stub
  try {
   createDataBase();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }

        // Add your public helper methods to access and get content from the database.
       // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
       // to you to create adapters for your views.

}

Now , on your main activity call the database helper to copy the files to the android database using the following code :

   
db=new DataBaseHelper(this);
        try {
   db.createDataBase();
  } catch (IOException e) {
   e.printStackTrace();
  }
        finally
        {
         //finish();
        }
        SQLiteDatabase sql=db.getReadableDatabase();
   mCursor = sql.query("text_messages", new String[]{"_id","sms"}, null, null, null, null, null);
        

Please note -

1. This method does not work very well with android 2.1 . According to my experiences , the application stops responding when it is launched for the 1st time (it copies the database into the android internal memory though ) . On second time launch you are able to browse it properly . Probably the problem is that i am using java threading library instead of Async .
2. If you tend to change the database , then please make sure that you delete the previously copied database using the adb shell and then browsing to the database folder . Else , the application considers the database to be present already and thus doesn't try to copy the previous one .
3. The project file can be found HERE . Please mail me if the link is broken .