Best practices for creating a SQLite database on Android

Question

I have read through a lot of the posts on copying the database file over from the assets or raw folders to the /data/data/APP/databases folder, but that would leave two copies of the DB on the device taking up valuable space. I am thinking of building a solution with a slightly smaller footprint and allowing for more flexible schema management by storing the database SQL creation text file in the raw folder and using the standard on_create / on_update process in the DBhelper class. However I am a little confused because the examples that copy the database over bypass the on_create and on_update methods.

Is this the recommended way if you are not building the db from strings in the code?

My solution would simulate the running scripts from code method by having the scripts all in one file. The reason I am looking at building the db this way is that my DB will have close to 100 tables when the application is complete, so I need the schema to be manageable.

Any guidance is welcome as I am still learning the best practices and patterns for Android.

Here is an example of my code:

public class DatabaseHelper extends SQLiteOpenHelper {
    private final String DATABASE_NAME = "mydb";
    private final int DATABASE_VERSION = 1;
    private final Context myCtx;
    private String DATABASE_CREATE_SCRIPT = null;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        DATABASE_CREATE_SCRIPT = getLoadFile();

        // Create all tables and populate the lookup tables
        db.execSQL(DATABASE_CREATE_SCRIPT);
        db.execSQL(VIEW_CREATE_V_PERSON);
    }

    private String getLoadFile(){
        InputStream inputStream = myCtx.getResources().openRawResource(resIdofmyfile);

        InputStreamReader inputreader = new InputStreamReader(inputStream);
        BufferedReader buffreader = new BufferedReader(inputreader);
        String line;
        StringBuilder text = new StringBuilder();

        try {
        while (( line = buffreader.readLine()) != null) {
           text.append(line);
           text.append('\n');
         }
        } catch (IOException e) {
           // We have an error to look up
           return null;
        }
        return text.toString();
    } 

    /**
     *  onUpgrade will check the version of the database and update the database 
     *  if a newer version is available.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Will set conditional upgrade checks
        //Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which may destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS CONTEXT_LOAD");
            db.execSQL("DROP TABLE IF EXISTS ISSUES");
            db.execSQL("DROP TABLE IF EXISTS GOALS");
            db.execSQL("DROP TABLE IF EXISTS PERSON");

            db.execSQL("DROP VIEW IF EXISTS V_PERSON");
        onCreate(db);
    }


}

Answer 1

I guess it looks like a good practice. It's not very bad even if you create your database with code completely. Check out google's app for IO schedules it has a fairly big database creation part. It may lead you a way.

Here is the project home page, here is the database part

I use this application as a reference all the time it's like a work of art =)

---

Edit: Google updated the app so the old database link doesn't work.

Answer 2

I'm not really sure what your question is.

If you are asking if it's OK to create your DB from scratch using a text file containing a load of SQL CREATE statements then the answer is "yes".

Not only that but it's more flexible than distributing a pre-built DB within your APK as you could even download the 'schema' file or files from a network location. This allows you to update the schema dynamically at a central point.