August 22, 2011

Easing the pain of Database maintenance on android

author one comment

I’ve been working on a couple android apps for the past couple weeks, and have gone through some of the “new platform” growing pains.  Not that Android is the problem, just the changing from a platform/framework that I am familiar with, to something new and exciting.  This post won’t cover how to use a database, however I may get around to writing one as i get more experience with it.  This post will cover more specifically how to create/update SQLite databases on the android platform.

Most examples (in fact ALL the examples I could find ) have you put your DDL statements as string inside a class, typically the class you create to extends the SQLiteOpenHelper.

// Database creation sql statement
	private static final String DATABASE_CREATE = "create table todo (_id integer primary key autoincrement, "
			+ "category text not null, summary text not null, description text not null);";

I’ve found this less than ideal when working with multiple tables, indexes, constraints, etc. I’m used to running through my scripts on a database, running them as a script, so having these “hardcoded” didn’t quite seem right.

After trying a few things out, and some searching for alternatives, I’ve decided to go about this another way. I’ve decided to create some script files (that can easily be tested using sqliteman or any other sqlite db tool. I then save these scripts ( create, alter, etc ) into the assets folder and have my code run them.

public class DBOpenHelper extends SQLiteOpenHelper {

    private final Context context;
    private static final String DATABASE_NAME = "mydb";
    private static final String DATABASE_SCRIPT_CREATE_FILENAME = "create_schema_";
    private static final String DATABASE_SCRIPT_UPDATE_FILENAME = "update_schema_";
    private static final String DATABASE_SCRIPT_INIT_FILENAME = "init_schema_";
    private static final int DATABASE_VERSION = 1;

    public DBOpenHelper( Context context ) {
        super( context, DATABASE_NAME, null, DATABASE_VERSION );
        this.context = context;
    }

    @Override
    public void onCreate( SQLiteDatabase db ) {
        Log.d( "DBOpenHelper", "inside onCreate" );
        String[] items = getSqlItems( DATABASE_SCRIPT_CREATE_FILENAME );
        for ( String item : items ) {
            if ( item.trim().length() != 0 ) {
                Log.d( "DBOpenHelper#execSQL", item );
                db.execSQL( item + ";" );
            }
        }
    }

    @Override
    public void onUpgrade( SQLiteDatabase db, int oldVersion, int newVersion ) {
        String[] items = getSqlItems( DATABASE_SCRIPT_UPDATE_FILENAME + oldVersion + "_" + newVersion + ".sql" );
        for ( String item : items ) {
            if ( item.trim().length() != 0 ) {
                Log.d( "DBOpenHelper#execSQL", item );
                db.execSQL( item + ";" );
            }

        }
    }

    private String[] getSqlItems( String file ) {
        String[] items = null;
        try {
            BufferedReader reader = new BufferedReader( new InputStreamReader( context.getAssets().open( file ,
                    AssetManager.ACCESS_STREAMING ) ) );

            StringBuffer sql = new StringBuffer();
            String line = null;
            while ( ( line = reader.readLine() ) != null ) {
                sql.append( line );
                sql.append( "\n" );
            }
            items = sql.toString().split( ";" );
        } catch ( IOException ioe ) {
            items = new String[] {};
        }
        return items;
    }

Some may say this might be a disclosure risk ( people will see my DB structure ) but since you can access most SQLite db’s from the SD card, the person could grab that and go from there.

The advantage to this, in my opinion is the ability to test your scripts without the code, for upgrade purposes, and you can manage multiple versions easily. i.e. upgrading from version 1, 2,3,4 to 5 can each be managed by their own script.

One thought on “Easing the pain of Database maintenance on android”

  1. Thanks for sharing this. I’ve been trying to find the best way to manage database upgrades and I’m gonna give this a try. The whole hardcoding option bothers me for some reason. I’m thinking part of my reasoning is that the DDL portions may end up growing in size and I’d like that separate from my Java code.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scan to Donate Bitcoin to Tim
Did you like this?
Tip Tim with Bitcoin